mysql索引选择错误的几种情况 mysql之优化器选择索引|天天热门

文章目录

(相关资料图)

前言索引选择错误的几种情况统计异常排序导致的异常 扫描行数数的统计总结

前言

之前文章，把索引和日志串起来介绍了一下，介绍了什么时候时候用普通索引，什么时候用唯一索引。还说明了，为什么要用普通索引，为什么要用唯一索引。和使用的原因。今天我们把索引和第一章介绍的mysql大致组成串联起来，来介绍下优化器的索引选择。

索引选择错误的几种情况

统计异常

首先创建一个表

CREATE TABLE `t` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `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）到（100000，100000，100000），然后执行下面这个sql

select * from t where a between 10000 and 20000;

这个时候呢，优化器会选择那个索引呢。结果看下面 他会选择a索引。 但是同样的查询，如果是下面的这个查询过程 他会是什么结果呢。它会走主键索引进行全表扫描。那为什么放着索引a不走，非得走主键索引呢。有两个原因 1.主键索引和普通索引扫描行数相差不大得情况下，他会选择主键索引，这是因为走普通索引需要回表（这个之前得章节有介绍，这里不再介绍了）。优化器会认为，回表的时间会大于多扫描的那几行。 2.索引统计错误，就是比如现在这个，主键索引是100000多行，普通索引是30000多行，相差这么大。它为什么还要走主键索引呢，这个就是它在统计索引时出现了异常，可能统计普通索引a可能需要扫描9万多行，或者10万多行。这个时候就是第一种的情况了。 统计索引出现异常怎么办呢。有两种方案 1.analyze table t 命令，可以用来重新统计索引信息。 2.force index(a) 强行指定索引

排序导致的异常

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

看上面这个语句，走a的话只要扫面1000多行，走b的话需要扫面5000多行，正常来说应该是走a快，事实也是走a快，但是优化器却选择了走b，这个是为什么呢。首先这个表已经矫正过索引的信息了，不会出现统计异常的情况了。那是什么原因呢。 那是因为order by b，结果需要根据b排序，走索引b 的话不需要排序了，b直接是有序的。优化器认为排序时间会大于多扫描那些行数的时间。所以会选择走b。那有什么解决方案呢。 1.将sql的order by b改为 order by a，b，这样的话系统会认为a和b都需要排序，这样它就会选择扫描行数少的。 2.创建一个更好的索引，或者删除一些索引。比如上面这个就可以直接删除索引b，这样它就会走索引a了。 3.force index(a) 强行指定索引，上面统计异常也可以这么干。

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

扫描行数数的统计

上面说了那么多，基本都是基于扫描行数为基准的，那么扫描行数是怎么统计的。首先，在开始扫描之前，优化器是不会知道，具体的扫描行数的。而是根据统计信息估算出扫描行数。统计信息又是什么呢。统计信息是一个索引的区分度，也就是一个索引上不同值得多少。而一个索引上不同值得个数，被称为统计基数。所以统计基数越大，也就是区分度越好。 那么怎么得到统计基数呢，首先一行一行扫描不现实，虽然精确，但是太耗费时间。所以mysql采用了采样统计得方式，因为mysql数据在磁盘中是以数据页得形式存储的。mysql会选择n个数据页，算出每个数据也的基数平均值，最后乘以这个索引的数据页数，得到这个索引的基数。而优化器就是根据这个基数进行索引选择的，虽然有一定的偏差，但是基本不会影响正确索引的选择。

总结

今天我们介绍了，一些常见的索引选择错误的情况，大致就两种。然后还介绍了每种的情况所对应的解决方案有哪些，方案很多，大家可以自信选择。比较通用的是指定索引，但是不见建议大家这么干。因为指标不治本，不太好。最后就是介绍优化器选择的关键，扫描行数的怕判定。