《丁奇-MySQL45讲-10》之归纳总结

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

个人感觉这篇文章完全是在介绍案例，然后通过案例进行分析与解决，没有涉及到太多的原理性内容。

• 一张表通常会有很多索引，那具体选择哪个索引是由优化器决定的，那优化器如何选择最优的执行方案：

1. 扫描的行数，在执行语句前MySQL并不知道符合条件的记录有多少条，所以一开始先根据统计信息进行估算，接着explain具体语句时优化器还会判断rows字段（预计扫描多少行）。

2. 是否使用临时表。

3. 是否排序。

• 索引的基数：因为MySQL不可能去读整张表，所以只能根据统计信息来估算数量，而这个统计信息就是索引的“区分度”，索引上不同的值越多，这个索引的区分度就越好，索引上不同的值的个数，我们称之为“基数”（cardinality），也就是说，这个基数越大，索引的区分度越好。

• MySQL如何计算基数：默认选择N个数据页，统计这些数据页上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了索引的基数。

• analyze table t可以用来重新统计索引信息，如果出现explain SQL后发现rows字段扫描的行数错误，可以使用这个命令。

• 避免选择错误的索引：

1. 使用force index强制使用指定索引，缺点就是万一索引名改了后就麻烦了。

2. 考虑修改SQL语句，前提是必须保证执行逻辑是一样。

3. 尝试新增索引或者删掉误用的索引。

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

由于sessionA开启了事务并没有提交，所以sessionB之前的数据是不能删掉的，这样子在索引a上就有包含之前的数据版本，也包含最新的数据版本。主键上的记录也不应该删除，命令看到的扫描行数为什么还是 100000 左右？因为主键是直接按照表的行数来估计的，而表的行数，优化器直接用的是 show table status 的值。在没有sessionA的情况下，数据并没有直接删除，而是打上删除标记，这些待删除的数据会由purge线程统一去删除，如果这些空间还未被回收，那么插入的数据会直接沿用之前待删除的空间，如果已经删除了，那么会直接写入到新的空间上（个人理解）。