MySQL选错索引的原因以及解决方案

MySQL 中，可以为某张表指定多个索引，但在语句具体执行时，选用哪个索引是由 MySQL 中执行器确定的。那么执行器选择索引的原则是什么，以及会不会出现选错索引的情况呢？

先看这样一个例子：

创建表 Y，设置两个普通索引, 创建一个存储过程用于插入数据。

MySQL: 5.7.27, 隔离级别: RR

CREATE TABLE `Y` (
 `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；

delimiter ;;
create procedure idata()
begin
 declare i int;
 set i=1;
 while(i<=100000)do
   insert into Y (`a`,`b`) values(i, i);
  set i=i+1;
 end while;
end;;
delimiter ;
call idata();

查看如下事务：

如果单独执行 Session B 中 select * from Y where a between 10000 and 20000;，毫无疑问会选择 a 这个索引。

但如果安装 Session A，Session B 的顺序执行，发现索引的选择如下：

可以发现，在 Session B 的场景下，执行器却没有选择 a 所在的索引，而是选择基于主键索引的全表扫描。

set long_query_time=0;
--将慢查询日志打开，并将阙值设为 0. 在记录的日志中，可以发现 MySQL 并没有选择 a 所在的索引，同时花费了更长的时间。

这样看，MySQL 的优化器不一定每次都能选择合适的索引。想要理解出现该现象的原因，就要从优化器的选择逻辑说起。

优化器

MySQL 中优化器的目的就是找到一个最优的执行方案，从而用最小的代价去执行语句。

优化器在选择索引时，主要会考虑如下的因素：

• 扫描的行数：扫描的行数越少，就证明访问磁盘数据的次数越少，消耗的 CPU 资源就越少。
• 有没有涉及到临时表
• 排序

关于扫描行数的确定

计算索引的基数

MySQL 在执行语句前，其实并不能准确的计算出扫描的行数，而是通过数学统计信息来估算记录数。这个统计信息被称为索引的“区分度”，在索引上不同的值越多，区分度就越高。在一个索引上不同值的个数，称为“基数”。基数越大，索引的区分度越好。

这里的 Cardinality 就是索引的基数，但基数并不是完全准确的。MySQL 是在获取基数时，实际上是采用采样统计的方式。

---

搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]‍，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：MySQL选错索引的原因以及解决方案

喜欢 (0)赏

[搞代码]

分享 (0)