mysql——索引（三）

发表于 | 分类于

mysql为什么有时候选错索引

在mysql中一张表可以支持多个索引，但是你在写sql的时候并没有主动选择使用哪个索引，那是因为mysql的优化器帮你选择了。竟然这样，mysql的优化器在选择时是否会出错呢？答案是 —— 会。

选错索引的例子1

让我们一起来看以下的例子，假如有一个简单的表，表里有a、b两个字段，并分别建上索引：

1
2
3
4
5
6
7
8
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB;

然后，我们往表t中插入10万行记录，取值按整数递增，即：(1,1,1)，(2,2,2)，(3,3,3) 直到(100000,100000,100000)，插入的代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

接下来我们来分析这个sql：select * from t where a between 10000 and 20000; 你一定会说，这个语句还用分析吗，很简单呀，a上有索引，肯定是要使用索引a的，使用explain进行分析也确实如你所说：
索引_选错索引.png
在刚才的基础上我们再进行如下操作，模拟不断地删除历史数据和新增数据的场景：
索引_选错索引2.png
遗憾的是，笔者按照以上的方式并没有复现出选错索引的场景。例子来源参考10讲MySQL为什么有时候会选错索引

如果你复现了以上的场景，最终会发现在session B中explain显示rows大概为100000，即扫描了全表。

选错索引例子1补充

如果你能复现出上面选错索引的场景，你执行：explain select * from t where a between 10000 and 20000;及explain select * from t force index(a) where a between 10000 and 20000;会显示：
索引_强制索引使用.png
其中force index(a)表示让sql语句强制使用索引a。这时你一定会产生疑问：mysql为何放着扫描3w+行的方案不执行，反而去执行扫描10w+的方案呢？

这是因为优化器觉得如果使用索引a，还要算上回表的代价，权衡之下优化器选择直接去主键id索引上执行。但是在还没有模拟不断地删除历史数据和新增数据之前，优化器是选择了索引a的（参考第一张图），这里是因为索引基数不准确导致的预计扫描行数不准确，引起的优化器选错了索引（从语句上看应该是扫描10001行，但最终显示了37116行）。

在知道因为索引基数不对导致选错索引的情况下，可以执行：analyze table t;重新统计索引基数：
索引_重新统计索引基数.png

以上的说明都是基于你已经复现出了mysql选错索引例子1的场景。

优化器逻辑

选择索引是优化器的工作，优化器选择索引的目的，是为了找到一个最优的执行方案，并用最小的代价去执行语句。其中扫描行数是影响选择的因素之一。

那么扫描行数是如何得到的呢？

mysql在执行sql的时候，并不能精确地知道具体有多少行，只能根据统计的信息来估计记录数。这个统计的信息就是索引的区分度：一个索引上不同的值越多，这个索引的区分度就越好，而索引上不同值的个数就是索引基数，即基数越大区分度越好。

mysql如何统计索引基数的？
如果将表一行行读取出来进行统计，虽然能获得精确的数据，但是这代价太大了，所以mysql使用的是采样统计。

采样统计时，InnoDB会选取N个数据页，统计这些页上的不同值，获得一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。（所以页数增多会引起基数变大）

到此就可以解释另一个问题：模拟不断地删除历史数据和新增数据后，索引统计的基数为何增加了？

mysql是使用标记删除来删除记录的,并不从索引和数据文件中真正的删除。
在REPEATABLE-READ（可重复读）事务隔离级别下，session B要支持session A的可重复读，所以不能占用之前被删除的数据的空间，新插入的数据只能重新申请数据页空间，所以导致索引页面数的增加，导致统计的基数增大。

执行：show index from t; 可以显示表所有索引的基数：
索引_区分度.png

当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断，这里不再展开。

选错索引的例子2

这里再补充一个因为排序引起的选错索引的场景：explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
索引_选错索引4.png
从你的角度看，优化器应该选择索引a，这样只需要扫描1000行就能查出结果了。但是上图明确指明了优化器偏偏选择了索引b，这里就是因为后面要对b进行排序，优化器认为选择索引b可以避免排序（因为B+树的有序性，索引b上逐个搜索出来的数据是自带排序的）。

这里将sql改写一下：explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b,a limit 1;告诉优化器a也是需要排序的，这样扫描行数成了决策的唯一因素，优化器又重新选择了索引a：
索引_选错索引5.png

索引选择异常和处理

1.像我们第一个例子一样，采用force index强行选择一个索引。
但是这样会侵入我们的代码，如果索引修改了，代码没有做相应的修改，sql执行会报错：ERROR 1176 (42000): Key ‘a’ doesn’t exist in table ‘t’。

2.我们可以考虑修改语句，引导MySQL使用我们期望的索引，比如选错索引例子2。

3.如果那个误选择的索引确实不需要，可以删除，当然这需要从业务上进行分析。

explain使用

在验证上面的例子时，我们都通过explain命令来判断sql执行是否符合我们的预期。explain是mysql中用来分析sql语句执行效率的重要命令。
索引_explain.png
从上图可以看到explain展示的信息很多，其中比较重要的是type，key以及Extra，可以作为判断sql执行是否高效的指标。

对于type，官方文档中有如下的效率排序：NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。
一般只要掌握其中常用的10种即可：NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL。

key代表的是sql执行时最终选择的索引，根据这个你可以判断mysql是否选错了索引。

Extra表示一些额外信息，比如是否用到临时内存，排序时是否用到临时文件等等。

暂时从缺，还没有研究透。

SQL语句逻辑相同，性能却差异巨大

有时候会遇到这样的情况：这个表明明有索引，但是执行sql时并没有按照我们预期的通过索引树进行查找。
这里举例了3个在日常中容易被忽略的例子。

对条件字段进行了函数操作

假如有表t1，并且在字符字段a上建立了索引：

1
2
3
4
5
6
7
8
CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `a` varchar(64) DEFAULT NULL,
  `b` varchar(64) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`)
) ENGINE=InnoDB;

有时候为了满足业务的需求，我们可能会对查询字段做函数操作，理所当然的认为这样的查询方式也会走索引。

比如查询出a的前3位等于’100’的记录，explain结果如下：
索引上应用函数.png
从上图可以知道sql并没有走 idx_a 索引，而是进行了全表扫描。因为当你对查询条件应用函数时，mysql优化器认为substr(a,1,3)得出的结果在idx_a索引树上是无序的，因此放弃了走索引。

隐式类型转换

有时候你的DBA或者组长告诉你：对字符串进行搜索时加上单引号，而不要单纯地写个数字。

这里涉及到隐式类型转换：和java一样，当mysql发现where条件中字段的类型和条件值类型不符合时，会将其中一边进行类型转换。还是以表t1为例，分别执行以下sql：
隐式类型转换.png
从上图中可以知道，mysql将字符串转换成了数字，再进行了比较。对于优化器而言：执行select * from t1 where a = 100;就相当于执行了select * from t1 where cast(a AS int) = 100; 正好符合了对条件字段进行了函数操作。

接下来对同一个表中的字段c增加索引，并且执行：explain select * from t1 where c = ‘100’;
隐式类型转换2.png
从上图得到了不同的结果，优化器正确地选择了索引c。此时对于优化器而言相当于执行了select * from t1 where c = cast(‘100’ AS int); 函数时应用在条件值上的。所以：
1.函数应用在where条件值上是没有问题的，但切忌应用在条件字段上
2.int类型字段where条件使用字符串比较也是可以的

隐式字符编码转换

使用join的方式，驱动表字段使用utf8，被驱动表使用utf8mb4。
比如 select b.* from a,b where a.t = b.k and a.id = 10; 表a有索引t，表b有索引k，t是utf8，k是utf8mb4
暂时没有复现出来

显示 Gitment 评论