10-02 | MySQL为什么有时候会选错索引？

一、优化器的逻辑
🏁：1.优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。那么影响执行代价的重要因素是什么？

扫描行数。为什么呢？扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。
优化器还会结合是否使用临时表
是否排序等因素进行综合判断

🏁：2.什么是索引的区分度？什么是基数？什么命令能看到一个表索引的区分度？

一个索引上不同的值的占比情况成为区分度。
一个索引上不同的值的个数，我们称之为“基数”（cardinality）
show index from t;

🏁：3.MySQL 是怎样得到索引的基数的呢？基数会发生变化么？什么时候发生变化？基数的统计中N和M的值是在什么命令中体现的？

抽样统计。InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数
会
当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计（M是指基数）
MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。

知晓了优化器统计的方式后我们接着上一节的例子看一个情况（PS：上一节是引子）
在这里插入图片描述
🏁：4.rows 这个字段表示的是预计扫描行数。优化器为什么放着扫描 37000 行的执行计划不用，却选择了扫描行数是 100000 的执行计划呢？
因为，如果使用索引 a，每次从索引 a 上拿到一个值，都要回到主键索引上查出整行数据，这个代价优化器也要算进去的。而如果选择扫描 10 万行，是直接在主键索引上扫描的，没有额外的代价。

⚠️：重点重点重点
上图中为什么删除后又新增，强制使用索引，扫描数变成了37116呢？
答：因为另一个事务仍未提交，因此删除的数据只是标记删除，数据仍然在数据页中，后插入的数据需要找新的空位插入，这样查询时会扫描删除的数据+后插入的数据，同时算上回表扫描主键索引，因此扫描行数达到3万7千行

⭐️：5.如何修正重新统计索引信息
使用 analyze table table_name 命令，重新统计索引信息,解决采样导致的扫描行数出错的问题。
⚠️：如果只是索引统计不准确，通过 analyze 命令***可以***解决很多问题

🤔️ 又一个问题正在蹦来～

mysql> select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

在开始执行这条语句之前，你可以先设想一下，如果你来选择索引，会选择哪一个呢？
如果用a索引，扫描索引 a 的前 1000 个值，回表根据b过滤，扫描1001
如果用b索引，扫描索引 b 的最后 50001 个值，回表根据b过滤，扫描50001
如果我们来选，用索引a一定会快！但是explain看下
在这里插入图片描述
❗️这次优化器选择了索引 b，而 rows 字段显示需要扫描的行数是 50198。

又双有错了

🏁：5.如果像这种碰到索引选择异常了我们如何做呢？

一种方法是，像我们第一个例子一样，采用 force index 强行选择一个索引。
第二种方法就是，我们可以考虑修改语句，引导 MySQL 使用我们期望的索引。
第三种方法是，在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。

🏁：5.1.使用 force index缺点是什么？
一来这么写不优美，二来如果索引改了名字，这个语句也得改，对于生产系统来说，这个过程不够敏捷。
👋：5.2.如何修改上述语句引导MySQL？
把“order by b limit 1” 改成 “order by b,a limit 1”。
解释：
之前优化器选择使用索引 b，是因为它认为使用索引 b 可以避免排序（b 本身是索引，已经是有序的了，如果选择索引 b 的话，不需要再做排序，只需要遍历），所以即使扫描行数多，也判定为代价更小。现在 order by b,a 这种写法，要求按照 b,a 排序，就意味着使用这两个索引都需要排序。因此，扫描行数成了影响决策的主要条件，于是此时优化器选了只需要扫描 1000 行的索引 a。

二、总结

对于由于索引统计信息不准确导致的问题，你可以用 analyze table 来解决。
而对于其他优化器误判的情况，你可以在应用端用 force index 来强行指定索引，也可以通过修改语句来引导优化器，还可以通过增加或者删除索引来绕过这个问题。

三、思考题
通过 session A 的配合，让 session B 删除数据后又重新插入了一遍数据，然后就发现 explain 结果中，rows 字段从 10001 变成 37000 多。
而如果没有 session A 的配合，只是单独执行 delete from t 、call idata()、explain 这三句话，会看到 rows 字段其实还是 10000 左右。你可以自己验证一下这个结果。这是什么原因呢？

答案：
delete 语句删掉了所有的数据，然后再通过 call idata() 插入了 10 万行数据，看上去是覆盖了原来的 10 万行。但是，session A 开启了事务并没有提交，所以之前插入的 10 万行数据是不能删除的。这样，之前的数据每一行数据都有两个版本，旧版本是 delete 之前的数据，新版本是标记为 deleted 的数据。这样，索引 a 上的数据其实就有两份。然后你会说，不对啊，主键上的数据也不能删，那没有使用 force index 的语句，使用 explain 命令看到的扫描行数为什么还是 100000 左右？（潜台词，如果这个也翻倍，也许优化器还会认为选字段 a 作为索引更合适）是的，不过这个是主键，主键是直接按照表的行数来估计的。而表的行数，优化器直接用的是 show table status 的值。