手机网站工具,wordpress网站修改域名,做网站是前端还是后端,如何设计一个自己的网页第十讲 MySQL为什么有时候会选错索引#xff1f;
一、问题引入
在 MySQL 中#xff0c;索引选择由优化器负责#xff0c;其目标是以最小代价执行语句#xff0c;但有时会选错索引#xff0c;导致执行速度变慢。
二、案例分析
案例一
建表与数据插入 创建表t#xff…第十讲 MySQL为什么有时候会选错索引
一、问题引入
在 MySQL 中索引选择由优化器负责其目标是以最小代价执行语句但有时会选错索引导致执行速度变慢。
二、案例分析
案例一
建表与数据插入 创建表t包含id、a、b字段分别建立主键索引和a、b字段的普通索引。使用存储过程插入 10 万行数据取值按整数递增。 初始查询情况 执行select * from t where a between 10000 and 20000;优化器选择索引a扫描行数 10001 行执行情况符合预期。 数据变更后的问题 在session A开启事务session B删除数据后重新插入 10 万行数据。此时session B再次执行上述查询未选择索引a而是走了全表扫描扫描 10 万行执行时间 40 毫秒。而使用force index(a)强制使用索引a时扫描 10001 行执行 21 毫秒。 原因分析 MySQL 通过索引的 “区分度”基数估算扫描行数基数越大区分度越好但采样统计方法导致基数不准。优化器预估使用索引a时每次从索引a取值后要回主键索引查整行数据计算代价后认为直接扫描主键索引更快然而实际执行时间表明此选择并非最优。
案例二
查询语句 select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1; 预期与实际索引选择 从条件分析使用索引a扫描行数少应更快但优化器选择了索引b扫描行数预估为 50198 行。 原因分析 优化器认为使用索引b可避免排序b本身有序即使扫描行数多也判定代价更小。
三、索引选择异常的处理方法
1. 使用force index强行选择索引
优点能直接指定索引使优化器不再评估其他索引执行代价。缺点写法不优美索引改名时语句需修改可能不兼容其他数据库且变更及时性差通常在问题出现后才修改 SQL 语句添加测试发布过程不够敏捷。
2. 修改语句引导优化器
例如将order by b limit 1改为order by b,a limit 1使扫描行数成为影响决策的主要条件诱导优化器选择索引a。但这种修改需根据数据特征和语句逻辑不具备通用性。
3. 新建或删除索引
新建更合适索引或删除误用索引但在某些案例中可能难以找到合适的新增索引方法也可能出现删除不必要索引后优化器重新选择正确索引的情况。
四、索引统计更新机制及解决索引统计不准确问题
索引统计方式 MySQL 采用采样统计方法InnoDB 默认选择N个数据页统计不同值取平均值后乘以索引页面数得到基数。当变更数据行数超过1/M时自动触发重新统计。可通过设置innodb_stats_persistent参数选择统计信息存储方式on表示持久化存储默认N 20M 10off表示只存储在内存中默认N 8M 16。 解决索引统计不准确问题 使用analyze table命令重新统计索引信息可解决因索引统计不准确导致的优化器选错索引问题但优化器还会综合其他因素判断。
五、总结与思考
优化器可能因索引统计信息不准确或其他因素选错索引对于索引统计问题可用analyze table解决对于其他误判情况可采用force index、修改语句或调整索引等方法。
