mysql优化器索引选择

本文会讲述优化器是如何选择索引的，例如有十万行数据，表结构如下：

正常来说，如果执行select * from t where a between 10000 and 20000，那么优化器选择的是索引a，

如果又选择执行如下两个事务。

分别看以下两个个语句选择的是哪个索引

• select * from t where a between 10000 and 20000; /*Q1*/
• select * from t force index(a) where a between 10000 and 20000;/*Q2*/

第1句，Q1是session B原来的查询；

第2句，Q2是加了force index(a)来和session B原来的查询语句执行情况对比。

Q1扫描了10万行，显然是走了全表扫描，执行时间是40毫秒。Q2扫描了10001行，执行了21毫秒。也就是说，我们在没有使用force index的时候，MySQL用错了索引，导致了更长的执行时间。

优化器的逻辑

选择索引是优化器的工作，优化器会选择一个最优的执行方案，依据有很多，比如扫描的行数、是否使用临时表、是否排序等等。

在上面的语句中，只涉及到扫描行数，来看看是怎么判断的。扫描的行数是根据基数判断的，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。

基数的确定，mqsql不可能把所有的数据都统计，这样子代价太大了，那么它就会进行抽样的选择。InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过1/M的时候，会自动触发重新做一次索引统计。

以上的抽样会有不准的时候，我们用explain来看看。

看Q1的结果是符合预期的，但是Q2的结果就差了，本来应该是10000行的，这个误差可能会误导优化器的判断。

但是，这个不是关键，问题在于，如果以a为索引，即使扫描的行数是37000多，显然少于100000，为啥优化器选择的是100000的执行方案，而不选择a为索引呢。

这个是因为，如果选择索引a，那么每次从a上拿到一个值，还要回表，这个也是代价的，而选择扫描100000行，那么就是直接用唯一主键扫描的，没有额外代价。所以优化器选择了10W行，虽然这个实际上更慢。

当然，如果执行了analyze table t，重新计算索引，那么就会变得精确：

这个时候，优化器可能就会选择a为索引了。

 

再看看另外一条语句，select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1.先看看a，b两个索引的结构：

看a只用扫描1000行，b要扫描50000行，那么明显选择a为索引会快很多，那么看看实际的结果。

优化器选择了b，原因主要为，语句最后的结果是要排序的，如果选择了b的为索引，它本身就是有序的，避免了排序，所以代价更小。那么如果我们改成order by b，a，按照b，a排序，既然两边都要排序，那么和优化器就会选择扫描行数为1000更小的a为索引。

总的来说，优化索引的方法有：

1. 使用force index 强行选择。

2. 重新写mysql语句，如上面的order b，a

3. 新建一个更适合的索引，或者删除误用的索引，如直接删除索引b。

 

回过头来，看看为啥我们在第一个例子中，加了A,B两个属于，选择的索引会不一样？

delete 语句删掉了所有的数据，然后再插入了10万行数据，看上去是覆盖了原来的10万行。但是，session A开启了事务并没有提交，所以之前插入的10万行数据是不能删除的。这样，之前的数据每一行数据都有两个版本，旧版本是delete之前的数据，新版本是标记为deleted的数据。这样，索引a上的数据就必须保留两份，可以看上一行可重复读的MVCC的逻辑，当然这个情况实现的前提是使用了可重复读的隔离级别。

而主键上的数据虽然也不能删，但是没有翻倍，因为主键是直接按照表的行数来估计的。而表的行数，是实际上现有的10W行，优化器直接用的是show table status的值。