MySQL 中, 可以为某张表指定多个索引, 但在语句具体执行时, 选用哪个索引是由 MySQL 中执行器确定的. 那么执行器选择索引的原则是什么, 以及会不会出现选错索引的情况呢?
先看这样一个例子:
创建表 Y, 设置两个普通索引, 创建一个存储过程用于插入数据.
MySQL: 5.7.27, 隔离级别: RR
- CREATE TABLE `Y` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `a` int(11) DEFAULT NULL,
- `b` int(11) DEFAULT NULL,
- PRIMARY KEY (`id`),
- KEY `a` (`a`),
- KEY `b` (`b`)
- ) ENGINE=InnoDB;
- delimiter ;;
- create procedure idata()
- begin
- declare i int;
- set i=1;
- while(i<=100000)do
- insert into Y (`a`,`b`) values(i, i);
- set i=i+1;
- end while;
- end;;
- delimiter ;
- call idata();
查看如下事务:
Session A | Session B |
---|---|
start transaction with consistent snapshot; | |
delete from t; | |
call idata(); | |
explain select * from Y where a between 10000 and 20000; | |
explain select * from Y force index(a) where a between 10000 and 20000; | |
commit; |
如果单独执行 Session B 中 select * from Y where a between 10000 and 20000;, 毫无疑问会选择 a 这个索引.
但如果安装 Session A,Session B 的顺序执行, 发现索引的选择如下:
可以发现, 在 Session B 的场景下, 执行器却没有选择 a 所在的索引, 而是选择基于主键索引的全表扫描.
set long_query_time=0;
将慢查询日志打开, 并将阙值设为 0. 在记录的日志中,
可以发现 MySQL 并没有选择 a 所在的索引, 同时花费了更长的时间.
这样看, MySQL 的优化器不一定每次都能选择合适的索引. 想要理解出现该现象的原因, 就要从优化器的选择逻辑说起.
优化器
MySQL 中优化器的目的就是找到一个最优的执行方案, 从而用最小的代价去执行语句.
优化器在选择索引时, 主要会考虑如下的因素:
扫描的行数: 扫描的行数越少, 就证明访问磁盘数据的次数越少, 消耗的 CPU 资源就越少.
有没有涉及到临时表
排序
关于扫描行数的确定
计算索引的基数
MySQL 在执行语句前, 其实并不能准确的计算出扫描的行数, 而是通过数学统计信息来估算记录数. 这个统计信息被称为索引的 "区分度", 在索引上不同的值越多, 区分度就越高. 在一个索引上不同值的个数, 称为 "基数". 基数越大, 索引的区分度越好.
这里的 Cardinality 就是索引的基数, 但基数并不是完全准确的. MySQL 是在获取基数时, 实际上是采用采样统计的方式.
计算时, 会选择 N 个数据页, 并统计这些页面上的不同值, 得到一个平均值, 然后乘以该索引的页面数, 然后得到的就是索引的基数.
在 MySQL 中, 有两种存储索引的方式, 可通过设置 innodb_stats_persistent 来切换:
on 时: 表示统计信息会持久化存储, 默认 N 为 20,M 为 10.
off 时, 统计信息仅会存储在内存中, 默认 N 为 8,M 为 16.
由于表中数据是不断变化的, 所以当更新的值超过 1/M 时, 会自动触发索引统计.
但需要注意的是, 由于是采样统计, 所以基数的值不是准确的.
预估扫描行数的错误
之前看到, 执行 Select * from Y where a between 10000 and 20000 预估的行数是 100015, 这个是能理解的, 因为走的是全表扫描.
之后执行 select * from Y force index(a) where a between 10000 and 20000 预估的行数是 37116, 这个就不能理解了, 理想的情况下应该是 10001 行 (需要遍历到 20001).
而且更奇怪的是, 虽然 37116 行的预估行数不太合理, 但也远小于全表扫描的 100015, 为什么优化器还是选择全表扫描呢?
首先先看第二个问题, 选择 100015 的原因是因为如果使用索引 a 的话, 除了需要在 a 索引扫描外, 还需要回表, 主键索引上的查询代价, 优化器也需要算进去, 所以选择了全表扫描.
这时再看第一个问题, 为什么没有得到正确的行数. 这个就和一致性视图有关了, 首先 Session A 中, 开启了一致性视图, 并没有提交. 之后的 Session 清空了 Y 表后, 又重新创建了相同的数据, 这时每行数据都有两个版本, 旧版本是 delete 前的数据, 新版本是标记为删除的数据. 所以索引 a 上的数据其实有两份. 也就造成了行数的预估错误.
MySQL 是通过标记删除的方法来删除记录的, 并不是在索引和数据文件中真正的删除. 而且由于一致性读的保证, 不能删除 delete 的空间, 再加上 insert 的空间. 导致统计信息有误.
选用错误索引的解决办法
对于行数预估错误的情况, 可采用如下的方法:
如果遇到 EXPLAIN 和预估的行数, 数值相差较大时, 可以通过 analyze table 来重新统计索引信息.
直接通过 force index 强制指定需要使用的索引, 不让优化器进行判断. 但使用 force 也可能带来一些问题:
迁移数据库时, 语法不支持
不容易变更并且不太方便, 因为选错索引的情况一般不会经常发生, 在生产环境出现问题后, 才需要改代码, 但还需要重新进行上线测试, 部署.
优化 SQL 语句, 引导优化器使用正确的索引
再看一个类似的例子:
先来看一下这句 SQL select * from Y where a between 1 and 1000 and b between5000 100000 order by b limit 1;
在执行这句话时, 可以选索引 a, 也可以选索引 b. 我们知道, 每个索引对应了一颗 B + 树. 这里由于取得是 a 和 b 的交集, 如果选用索引 a 的话, 需要遍历 1 - 10001 行. 选用索引 b 需要遍历 50000 - 100001 行. 理论上来说, 应该选择 a 作为索引, 可以优化器又偏偏选择了 b 作为索引.
这里选择 b 作为索引的原因, 是因为优化器看到了后面的 order by 语句, 由于要排序, 而 B+ 树本身就是有序的, 省去了排序的过程, 所以选择了 b 作为索引.
但从实际的执行时间来看, 索引 a 执行时间更短, 所以这里 MySQL 又选择了错误的索引.
我们可以将上述语句中 order by b limit 改为 order by b,a limit 1 这时由于 a,b 索引都要排序, 扫描的行数就成为执行器主要参考的条件, 引导选择正确的索引.
这样做的前提一定要保证执行的逻辑结果是一致的, 比如在 limit 1 的情况下, order by b,a 和 order by b 的结果一致, 如果换成 limit 100 就不一定了.
还有一种改法, select * from (select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 100)alias limit 1;
现在可以看到, 优化器选择了合适的索引. 原因在于 limit 100 让优化器认为, 使用索引 b 的代价较高, 进而选择索引 a. 其实就是通过 limit 100 诱导优化器做出选择.
调整索引
能否找到更优, 更合适的索引, 或者利用索引的原则, 删除一些不必要的索引.
总结
现在我们知道, MySQL 在选择索引时, 是会出现错误的情况的. 优化器选择索引的原则主要有三个, 扫描的行数, 是否存在临时表, 以及排序. 行数的扫描, 主要和基数有关, 而基数的统计则是通过统计抽样决定的, 进而预估的行数可能会是不准确的.
在遇到扫描的行数不正确时, 可以通过 analyze table 来重新统计表的信息, 通过 force index 强制指定索引, 或通过手动改变 sql 的语义, 诱导优化器做出正确的选择.
来源: https://www.cnblogs.com/michael9/p/12929775.html