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

2024-08-01 12:36:10

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 A	Session B
start transaction with consistent snapshot;
	delete from t;
	call idata();
	explain select * from Y where a between 10000 and 20000;
	explain select * from Y force index(a) where a between 10000 and 20000;
commit;

如果单独执行 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 是在获取基数时，实际上是采用采样统计的方式。

计算时，会选择 N 个数据页，并统计这些页面上的不同值，得到一个平均值，然后乘以该索引的页面数，然后得到的就是索引的基数。

在 MySQL 中，有两种存储索引的方式，可通过设置 innodb_stats_persistent 来切换：

on 时：表示统计信息会持久化存储，默认 N 为 20，M 为 10.
off 时，统计信息仅会存储在内存中，默认 N 为 8，M 为 16.

由于表中数据是不断变化的，所以当更新的值超过 1/M 时，会自动触发索引统计。

但需要注意的是，由于是采样统计，所以基数的值不是准确的。

预估扫描行数的错误

之前看到，执行 Select * from Y where a between 10000 and 20000 预估的行数是 100015，这个是能理解的，因为走的是全表扫描。

之后执行 select * from Y force index(a) where a between 10000 and 20000 预估的行数是 37116，这个就不能理解了，理想的情况下应该是 10001 行 (需要遍历到 20001)。

而且更奇怪的是，虽然 37116 行的预估行数不太合理，但也远小于全表扫描的 100015，为什么优化器还是选择全表扫描呢？

首先先看第二个问题，选择 100015 的原因是因为如果使用索引 a 的话，除了需要在 a 索引扫描外，还需要回表，主键索引上的查询代价，优化器也需要算进去，所以选择了全表扫描。

这时再看第一个问题，为什么没有得到正确的行数。这个就和一致性视图有关了，首先 Session A 中，开启了一致性视图，并没有提交。之后的 Session 清空了 Y 表后，又重新创建了相同的数据，这时每行数据都有两个版本，旧版本是 delete 前的数据，新版本是标记为删除的数据。所以索引 a 上的数据其实有两份。也就造成了行数的预估错误。

mysql 是通过标记删除的方法来删除记录的，并不是在索引和数据文件中真正的删除。而且由于一致性读的保证，不能删除 delete 的空间，再加上 insert 的空间。导致统计信息有误。

选用错误索引的解决办法

对于行数预估错误的情况，可采用如下的方法：

如果遇到 EXPLAIN 和预估的行数，数值相差较大时，可以通过analyze table 来重新统计索引信息。

直接通过 force index 强制指定需要使用的索引，不让优化器进行判断。但使用 force 也可能带来一些问题：

迁移数据库时，语法不支持
不容易变更并且不太方便，因为选错索引的情况一般不会经常发生，在生产环境出现问题后，才需要改代码，但还需要重新进行上线测试，部署。

优化 SQL 语句，引导优化器使用正确的索引

再看一个类似的例子：

先来看一下这句

SQL select * from Y where a between 1 and 1000 and b between5000 100000 order by b limit 1;

在执行这句话时，可以选索引 a，也可以选索引 b. 我们知道，每个索引对应了一颗B+树。这里由于取得是 a 和 b 的交集，如果选用索引 a 的话，需要遍历 1 - 10001 行。选用索引 b 需要遍历 50000 - 100001 行。理论上来说，应该选择 a 作为索引，可以优化器又偏偏选择了 b 作为索引。

这里选择 b 作为索引的原因，是因为优化器看到了后面的 order by 语句，由于要排序，而 B+ 树本身就是有序的，省去了排序的过程，所以选择了 b 作为索引。

但从实际的执行时间来看，索引 a 执行时间更短，所以这里 MySQL 又选择了错误的索引。

我们可以将上述语句中 order by b limit 改为 order by b,a limit 1 这时由于 a，b 索引都要排序，扫描的行数就成为执行器主要参考的条件，引导选择正确的索引。

这样做的前提一定要保证执行的逻辑结果是一致的，比如在 limit 1 的情况下，order by b,a 和 order by b 的结果一致，如果换成 limit 100 就不一定了。

还有一种改发

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

现在可以看到，优化器选择了合适的索引。原因在于 limit 100 让优化器认为，使用索引 b 的代价较高，进而选择索引 a. 其实就是通过 limit 100 诱导优化器做出选择。

调整索引

能否找到更优，更合适的索引，或者利用索引的原则，删除一些不必要的索引。

总结

现在我们知道，MySQL 在选择索引时，是会出现错误的情况的。优化器选择索引的原则主要有三个，扫描的行数，是否存在临时表，以及排序。行数的扫描，主要和基数有关，而基数的统计则是通过统计抽样决定的，进而预估的行数可能会是不准确的。

在遇到扫描的行数不正确时，可以通过 analyze table 来重新统计表的信息，通过 force index 强制指定索引，或通过手动改变 sql 的语义，诱导优化器做出正确的选择。

以上就是MySQL选错索引的原因以及解决方案的详细内容，更多关于MySQL 索引的资料请关注我们其它相关文章！

MySQL性能优化之如何高效正确的使用索引

实践是检验真理的唯一途径,本篇只是站在索引使用的全局来定位的,你只需要通读全篇并结合具体的例子,或回忆以往使用过的地方,对整体有个全面认识,并理解索引是如何工作的,就可以了.在后续使用索引,或者优化索引时,可以从这些方面出发,进一步来加深对索引正确高效的使用. 一.索引失效索引失效,是一个老生常谈的话题了.只要提到数据库优化.使用索引,都能一口气说出一大堆索引失效的场景,什么不能用.什么不该用这类的话,在此,我就不再一一罗列啰嗦了. 索引失效,是指表中有字段创建了索引,由于sql语句书写不当导
导致MySQL索引失效的一些常见写法总结

前言最近一直忙着处理原来老项目遗留的一些SQL优化问题,由于当初表的设计以及字段设计的问题,随着业务的增长,出现了大量的慢SQL,导致MySQL的CPU资源飙升,基于此,给大家简单分享下这些比较使用的易于学习和使用的经验. 这次的话简单说下如何防止你的索引失效. 再说之前我先根据我最近的经验说下我对索引的看法,我觉得并不是所以的表都需要去建立索引,对于一些业务数据,可能量比较大了,查询数据已经有了一点压力,那么最简单.快速的办法就是建立合适的索引,但是有些业务可能表里就没多少数据,或者表的使用
MySql索引提高查询速度常用方法代码示例

使用索引提高查询速度 1.前言在web开发中,业务模版,业务逻辑(包括缓存.连接池)和数据库这三个部分,数据库在其中负责执行SQL查询并返回查询结果,是影响网站速度最重要的性能瓶颈.本文主要针对Mysql数据库,在淘宝的去IOE(I 代表IBM的缩写,即去IBM的存储设备和小型机:O是代表Oracle的缩写,去Oracle数据库,采用Mysql和Hadoop代替:E是代表EMC2,去EMC2的设备性,用PC server代替EMC2),大量使用Mysql集群!而优化数据的重要一步就是索引的建立
MySQL索引的基本语法

索引是排好序的数据结构!可以用在 where 条件查找的字段,和order by 排序的字段,有了索引,便可以快速地定位数据所在的物理地址并找出来. 索引的分类 1.普通索引(normal):没有任何约束,主要用于提高查询效率 2.唯一索引(UNIQUE):在普通索引的基础上增加了数据唯一性的约束,可以有多个 3.主键索引(primary key):主键索引在唯一索引的基础上增加了不为空的约束,也就是 NOT NULL+UNIQUE,只能有一个 4.全文索引(FULLTEXT):MySQL 自带
MySQL索引的各种类型

什么是索引? 索引是数据库存储引擎用于快速查找到指定数据的一种数据结构. 可以用新华字典做类比:如果新华字典中对每个字的详细解释是数据库中表的记录,那么按部首或拼音等排序的目录就是索引,使用它可以让我们快速查找的某一个字详细解释的位置. 在MySQL中,存储引擎也是用了类似的方法,先在索引中找到对应的值,然后再根据匹配的索引值找到对应表中记录的位置. 面试中为什么问索引? 之所以在索引在面试中经常被问到,就是因为:索引是数据库的良好性能表现的关键,也是对查询能优化最有效的手段.索引能够轻易地把查
Mysql之组合索引方法详解

对于任何DBMS,索引都是进行优化的最主要的因素.对于少量的数据,没有合适的索引影响不是很大,但是,当随着数据量的增加,性能会急剧下降. 如果对多列进行索引(组合索引),列的顺序非常重要,MySQL仅能对索引最左边的前缀进行有效的查找.例如: 假设存在组合索引(c1,c2),查询语句select * from t1 where c1=1 and c2=2能够使用该索引.查询语句select * from t1 where c1=1也能够使用该索引.但是,查询语句select * from t1
MySQL索引失效的几种情况汇总

一.索引不存储null值更准确的说,单列索引不存储null值,复合索引不存储全为null的值.索引不能存储Null,所以对这列采用is null条件时,因为索引上根本没Null值,不能利用到索引,只能全表扫描. 为什么索引列不能存Null值? 将索引列值进行建树,其中必然涉及到诸多的比较操作.Null值的特殊性就在于参与的运算大多取值为null. 这样的话,null值实际上是不能参与进建索引的过程.也就是说,null值不会像其他取值一样出现在索引树的叶子节点上. 二.不适合键值较少的列(重复
Mysql索引性能优化问题解决方案

mysql 创建的优化就是加索引,可是有时候会遇到加索引都没法达到想要的效果的情况, 加上了所以,却还是搜索的全数据,原因是sql EXPLAIN SELECT cs.sid, -- c.courseFrontTitle, -- c.imgBig, cs.studyStatus, coi.fee, -- act.PROC_INST_ID_ AS processId, cs.createDTM, cs.payStatus, cs.isCompleted, cs.saleChannel, cs.is
详解MySQL 聚簇索引与非聚簇索引

1.聚集索引表数据按照索引的顺序来存储的,也就是说索引项的顺序与表中记录的物理顺序一致.对于聚集索引,叶子结点即存储了真实的数据行,不再有另外单独的数据页. 在一张表上最多只能创建一个聚集索引,因为真实数据的物理顺序只能有一种. 从物理文件也可以看出 InnoDB(聚集索引)的数据文件只有数据结构文件.frm和数据文件.idb 其中.idb中存放的是数据和索引信息是存放在一起的. 2.非聚集索引表数据存储顺序与索引顺序无关.对于非聚集索引,叶结点包含索引字段值及指向数据页数据行的逻辑指针,
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) DE
浅谈MySQL为什么会选错索引

目录 1.引例 2.优化器的逻辑 3.解决办法 1.引例首先创建一张表,并对字段a,b分别建立索引: 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; 然后往表中,插入十万行数据,值按整数递增:(1,1,1).(2,2,2).(3,3,3)… delimiter ;;
mysql or走索引加索引及慢查询的作用

目录前言一概述二实验表结构声明三 Mysql不走索引归类以及详细解析 1. 查询条件在索引列上使用函数操作,或者运算的情况 2. 查询条件字符串和数字之间的隐式转换 3. 特殊修饰符 %%, Or 将不走索引 4. 索引优化器选择最优的索引四总结以及实际应用前言小白白跑去鹅厂面试,面试官提出了一个很实际的问题: mysql增加索引,那些情况会失效呢?谈一下实际工作中遇到的情况.我们的小白白又抛出了白氏秘籍:用不用索引,找DBA小姐姐!啊?这是你面试哈,还是DBA小姐姐面试呀.
MySQL报错1040'Too many connections'的原因以及解决方案

目录报错原因: 解决办法总结 MySQL 报错1040 ‘Too many connections’ 报错原因: 实际连接数超过了mysql 允许的最大连接数,访问量过高,MySQL服务器抗不住. 解决办法 1.修改max_connections,如果这个值已经很大,2.这个时候就要考虑增加从服务器分散读压力: Windows 找到mysql.ini(Linux 修改/etc/my.cnf文件,在[mysqld]中新增max_connections=N).修改允许最大连接数max_conne
MySQL死锁的产生原因以及解决方案

数据库和操作系统一样,是一个多用户使用的共享资源.当多个用户并发地存取数据时,在数据库中就会产生多个事务同时存取同一数据的情况.若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性.加锁是实现数据库并发控制的一个非常重要的技术.在实际应用中经常会遇到的与锁相关的异常情况,当两个事务需要一组有冲突的锁,而不能将事务继续下去的话,就会出现死锁,严重影响应用的正常执行. 在数据库中有两种基本的锁类型:排它锁(Exclusive Locks,即X锁)和共享锁(Share Lock
MySQL主从复制延迟原因以及解决方案

来源:公众号「神谕的暗影长廊」在异步或半同步的复制结构中,从库出现延迟是一件十分正常的事. 虽出现延迟正常,但是否需要关注,则一般是由业务来评估. 如:从库上有需要较高一致性的读业务,并且要求延迟小于某个值,那么则需要关注. 简单概述一下复制逻辑: 1.主库将对数据库实例的变更记录到binlog中. 2.主库会有binlog dump线程实时监测binlog的变更并将这些新的events推给从库(Master has sent all binlog to slave; waiting for
MySQL索引失效场景及解决方案

目录一.前言二.最左前缀匹配原则三.MySQL逻辑架构和优化器四.索引失效场景以及为何会失效五.总结一.前言在对SQL语句进行索引查询时会遇到索引失效的时候,对于该语句的可行性以及性能效率方面有至关重要的影响,本篇剖析索引为何失效,有哪些情况会导致索引失效以及对于索引失效时的优化解决方案,其中着重介绍最左前缀匹配原则.MySQL逻辑架构和优化器.索引失效场景以及为何会失效. 二.最左前缀匹配原则之前有写了一篇关于MySQL添加索引特点及优化问题方面的文章,下面将介绍索引失效的相关
mysql 报错This function has none of DETERMINISTIC解决方案

本文章向朋友们介绍开启bin-log日志mysql报错:This function has none of DETERMINISTIC, NO SQL解决办法, 创建存储过程时出错信息: ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want t
Spring Boot应用上传文件时报错的原因及解决方案

问题描述 Spring Boot应用(使用默认的嵌入式Tomcat)在上传文件时,偶尔会出现上传失败的情况,后台报错日志信息如下:"The temporary upload location is not valid". 原因追踪这个问题的根本原因是Tomcat的文件上传机制引起的! Tomcat在处理文件上传时,会将客户端上传的文件写入临时目录,这个临时目录默认在/tmp路径下,如:"/tmp/tomcat.6574404581312272268.18333/work/T
记一次Mysql不走日期字段索引的原因小结

目录背景探索总结背景在一个表中,dataTime字段设置是varchar类型,存入的数据是日期格式的数据,并且为该字段设置了索引.但是在日志记录中,有一条关于该表的慢查询.查询语句为: select * from digitaltwin_meteorological where dataTime > '2021-10-15'; explain分析sql语句,发现sql语句执行了全表扫描.为何sql中用了dataTime索引列,为啥还走全表扫描呢? 探索一:起初,认为是dataTime

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

相关推荐

随机推荐