MySQL 笔记整理（10） --MySQL为什么有时会选错索引？

笔记记录自林晓斌（丁奇）老师的《mysql实战45讲》

（本篇内图片均来自丁奇老师的讲解，如有侵权，请联系我删除）

10） --mysql为什么有时会选错索引？

　　mysql中的一张表上可以支持多个索引的，但是，往往你写sql语句的时候不会去主动指定使用哪个索引。也就是说，使用哪个索引是由mysql来确定的。而mysql有时会选择不恰当的索引，我们举一个例子来说明这种情况。

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；

　　然后向表中插入10万行记录，取值按整数递增，即(1,1,1),(2,2,2),(3,3,3)直到(100000,100000,100000)。我们来分析一条sql语句：

mysql> select * from t where a between 10000 and 20000;

　　这条语句很简单，想必你也想到了这条语句会使用索引a，事实上也确实使用了索引a。不过别急，这个例子没有这么简单，我们继续来看：

　　

　　其中 call idata()是执行mysql的存储过程，用来插入数据。需要注意的是，这里session b就不会再使用索引a了。为了对比结果，可以使用force index(a)来让优化器强制使用索引a，下面三条语句就是实验过程：

set long_query_time=0;
select * from t where a between 10000 and 20000; /*q1*/
select * from t force index(a) where a between 10000 and 20000;/*q2*/

• 首先把慢查询日志的阈值设为0，表示这个线程接下来的语句都会进入慢查询日志中。
• q1是session b原来的查询；
• q2是seesion b 强制使用索引a的查询。

　　对比结果如下：

　　

　　很容易看出第一行查询了10w行，并没有利用到索引a。为什么会这样的，我们从优化器的逻辑谈起

优化器的逻辑：

　　优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库中，扫描行数是影响执行代价的因素之一。我们的优化器就是在判断扫描行数的时候出了问题。那么问题就是，扫描行数是怎么判断的呢？而在真正的执行语句之前，并不能精确地知道满足这个条件的记录有多少条。而只能根据统计信息来进行估算。这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好，而一个索引上不同值的个数，我们也称之为“基数”(cardinlity)。mysql是通过采样统计的方式来获得区分度的，统计时时会选择n个数据页来统计。因此这个值是不精确的，当数据表持续变化时，当变更的数据行数超过1/m的时候，会自动触发重新做一次统计。在mysql中有两种存储索引统计的方式，可以通过参数innodb_stats_persistent的值来进行选择 。

• 设置为on的时候，表示统计信息会持久化存储，默认的m是10，n是20
• 设置为off的时候，表示统计信息值存储在内存中，此时，默认的m是16，n是8.

　　mysql选择错误的索引就是因为这个统计信息不准造成的。你可以通过analyze table t命令来进行修正 。

索引的选择异常和处理：

　　其实大多数时候mysql的优化器都会选择到正确的索引，但一旦真的发生这种情况，你可以有别的方式来修正。一是刚才提到的，使用force index强行选择一个索引。一旦使用了force index命令，优化器就不会再去评估其他的索引了。但这个方式一来代码不够优雅，二来一旦有索引的改动还需再额外修改代码。第二种方式呢，可以考虑修改语句，引导mysql使用我们期望的索引。例如在order by相关的语句中，适当调整order by后面跟的条件，可以引导优化器找到正确的索引。三是，在某些场景下，我们可以新建一个更合适的索引。

上期问题：

　　change buffer一开始是写内存的，那么如果这个时候及其掉电重启，会不会导致change buffer丢失呢？change buffer丢失可不是小事，因为丢失以后就无法再进行merge了，等于是数据丢失了，会不会出现这种情况呢？

　　答案是不会丢失，虽然只是更新内存，但在事务提交的时候，我们把change buffer的操作也记录到redo log里面去了，所以崩溃的时候change buffer也能找回来。

问题：

　　本篇前面的例子中，如果没有session a的配合，只是单独执行 delete from t; call idata()； explain这三条语句，会看到explain结果中rows字段其实还是再10000左右，即使用了索引，这是为什么呢？