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 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 索引的资料请关注我们其它相关文章！

赞 (0)

相关推荐

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

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

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

  • 详解MySQL 聚簇索引与非聚簇索引

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

  • Mysql之组合索引方法详解

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

  • MySQL索引的基本语法

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

  • 导致MySQL索引失效的一些常见写法总结

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

  • MySQL索引的各种类型

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

  • 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 or走索引加索引及慢查询的作用

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

  • MySQL报错1040'Too many connections'的原因以及解决方案

    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索引失效场景及解决方案

    目录 一.前言 二.最左前缀匹配原则 三.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不走日期字段索引的原因小结

    记一次Mysql不走日期字段索引的原因小结

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

随机推荐