MySQL实战45讲——10|MySQL为什么有时候会选错索引？

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, 10000)

这里采用存储过程：

delimiter ;;
create procedure idate()
begin
	declare i int;
	set i = 1;
	while (i <= 100000) do
		insert into t value(i, i, i);
		set i = i + 1;
	end while;
end;;
delimiter;
call idate();

那么当我们查询的时候：

select * from t where a between 10000 and 20000

你肯定会觉得显而易见，a上有索引，因此使用a的索引进行查询，事实也的确如此，可以使用explain命令查看这条语句执行的情况

那么，再执行如下操作：

session A	session B
start transaction with consistent snapshot;
	delete from t; call idata();
	explain select * from t where a between 10000 and 20000
commit;

session A的操作是开启一个事务，随后sessionB把数据都删除，又调用存储过程，插入了10万行数据

这时候，select B的查询语句select * from t where a between 10000 and 20000就不会选择索引a了，很奇怪，对吧？

优化器的逻辑

选择索引是优化器的工作，而优化器选择索引的目的是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库中，扫描行数是影响执行的因素之一，扫描的行数越少，意味着IO次数越少，消耗的cpu资源越少

当然，扫描行数并不是唯一的标准，优化器还会结合是否使用临时表，是否排序等因素进行综合判断

上面的例子显然没有涉及到临时表，所以MySQL肯定是判断错扫描行数了

那么，问题在于，优化器是如何判断扫描行数的？

MySQL在真正执行语句之前，并不能精确的知道这个条件的记录有多少条，而只能根据统计信息来估算行数，这个统计信息就是索引的区分度，我们称之为基数，也就是说，基数越大，索引的区分度越好

可以使用show index看到一个索引的基数，但是MySQL是如何得到索引的基数呢？

MySQL是采样统计的方法，为什么要采样统计呢？因为把整章表取出来一行行统计，虽然可以得到精确的结果，但是代价太大了，所以只能选择采样统计。

采样统计的时候，InnoDB默认会选择N个数据页，统计这些页上的不同值，得到一个平均值，然后乘这个索引的页面数，就得到了索引的基数

而数据表是持续更新的，统计索引信息页不会固定不变，所以变更的数据行数超过1/ M的时候，就会自动触发重新做一次索引统计

在MySQL中，有两种存储索引统计方式，可以设置参数innodb_stats_persisiten的值来选择

• 为on的时候，表示统计信息会持久化存储，这时，默认的N是20，M是10
• 为off的时候，表示统计信息只存储在内存中，这时，默认的N是8，M是16

由于是采样统计，不管N是20还是8，这个基数都是很容易不准了

但，这还不是全部，选错索引还有别的原因

其实索引统计只是一个输入，对于一个具体的语句来说，优化器还要判断，执行这个语句本身要扫描多少行

如果没有指明使用索引a，那么会明显减少需要扫描的行数，但是为啥不使用a呢？因为优化器还会把回到主键上查出整行数据算进去，也就是说，优化器任何回表的代价更大

那么有了答案，只需要使用命令：analyze命令就可以了

再看另一个例子：

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

从条件上看，这个查询没有符合的，因此会返回空集

在执行之前，如果让你来选择索引，你会怎么选择？

如果是索引a，那么就是扫描索引a的前1000个值，然后取到对应的id，再回表，然后根据字段b来过滤，显然要扫描1000行

如果使用索引b进行查询，那么就是扫描索引b的最后50001的值，与上面的执行过程相同，也是需要回到主键索引上取值判断，所以需要扫描50001行，也就是说，使用a更好一些

但是如果你实际执行代码，会发现，优化器估算的行数失效了，并且又选错了索引

索引选择异常和处理

解决办法是：采用force index，强行指定一个索引，MySQL会根据词法解析的结果分析出可能可以使用的索引作为候选项，然后在候选列表中依次判断每个索引需要扫描多少行。如果force index指定的索引在候选索引列表中，那么就直接使用这个所i你，不再评估其他索引的代价

方法二就是：修改语句，引导MySQL使用我们期望的索引，比如说上面的sql语句的order by b limit 1改成order by b, a limit 1，逻辑是相同的

那么之前优化器选择索引b，因为b可以避免排序，(b本身是索引，已经是有序的了，如果选择b则不再需要排序，只需要遍历即可)。所以即使扫描行数多，也会被判定为代价小

现在的写法是按照b， a排序就意味着两个索引都需要排序，因此，扫描行数成了影响决策的主要条件，于是优化器就选择了索引a

方法三：新建一个更适合的索引，来提供给优化器，或者删除掉误用的索引