专栏首页谭小谭听说mysql还会选错索引

听说mysql还会选错索引

大家都知道,mysql 一个表中可以创建多个索引,但是在执行一条查询语句的时候,mysql 只能选一个索引,如果我们没有指定 mysql 使用某个索引,那么就是由 mysql 的优化器来决定要使用哪个索引了,然而,mysql 也是会有选错的时候。

前面的文章,我们有介绍过执行一条查询 sql 语句分别会经历那些过程,执行一条sql语句都经历了什么? 存在多个索引的情况下,优化器一般会通过比较扫描行数、是否需要临时表以及是否需要排序等,来作为选择索引的判断依据。

我们先来新建一个表,创建两个普通索引。

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;

这里我们使用存储过程往表里插入 10w 测试数据,如果对 mysql 的存储过程不熟悉,请看我在代码中的注释,应该能看得懂得。

#定义分割符号,mysql 默认分割符为分号;,这里定义为 //
#分隔符的作用主要是告诉mysql遇到下一个 // 符号即执行上面这一整段sql语句
delimiter //

#创建一个存储过程,并命名为 testData
create procedure testData() 

#下面这段就是表示循环往表里插入10w条数据
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i, i, i);
    set i=i+1;
  end while;
end //  #这里遇到//符号,即执行上面一整段sql语句

delimiter ; #恢复mysql分隔符为;

call testData(); #调用存储过程

数据插入完成后,我们来看下面这条 sql 语句。

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

由于主键id、a、b 三个字段的值其实都是一样的,所以其实这条 sql 语句的结果集为空,没有符合条件的记录。

我们来看看 mysql 该是怎么选择索引的,这里有三个索引可用,分别是主键索引、索引a、索引b。

如果选择主键索引虽然可以减少回表过程,但是只能走全表扫描,需要扫描 10w 条记录。

如果选择索引 a,则只需在 a 索引上扫描 1k 条记录,然后回到主键索引上过滤掉不满足 b 条件的记录,最后再按 b 排序即可。

如果选择索引 b,则需要在 b 索引上扫描 5w 条记录,然后同样回到主键索引上过滤掉不满足 a 条件的记录,因为索引有序,所以使用 b 索引不需要额外排序。

我们来使用执行计划看下 mysql 究竟会选择哪个索引。

mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
+----+-------------+-------+-------+---------------+------+---------+------+-------+------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows  | Extra                              |
+----+-------------+-------+-------+---------------+------+---------+------+-------+------------------------------------+
|  1 | SIMPLE      | t     | range | a,b           | b    | 5       | NULL | 50128 | Using index condition; Using where |
+----+-------------+-------+-------+---------------+------+---------+------+-------+------------------------------------+
1 row in set (0.12 sec)

可以看出 mysql 是选择使用索引 b,虽然扫描行数要多一些,但因为索引本身是有序的,使用索引 b 可以避免排序,mysql 认为这个排序的代价高于扫描行数。

上面这个选择是 mysql 优化器内部的分析,那么实际情况又如何呢,我们可以分别执行一下 sql 语句,使用 force index(a) 强制使用索引 a 来对比下,看下两者具体花费的时间。

mysql> select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
Empty set (0.65 sec)

mysql> select * from t force index(a) where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
Empty set (0.05 sec)

从结果可以看到其实使用索引 a 显然速度会更快,所以这就是属于 mysql 选错了索引的情况,那我们怎么避免这种情况呢,我们可以把 sql 语句改成下面这样的,即把 order by b 改成 order by b,a 。

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

这样的话,在 mysql 看来无论是使用索引 a 还是索引 b 都需要排序了,那就只能选择扫描行更少的索引了,所以 mysql 会选择索引 a,从而达到避免 mysql 选错索引的目的,我们可以看下优化后的这条 sql 的执行计划。

mysql> explain select * from t where a between 1 and 1000 and b between 50000 and 100000 order by b,a limit 1;
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | t     | range | a,b           | a    | 5       | NULL |  999 | Using index condition; Using where; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------------+
1 row in set (0.00 sec)

大多数情况下,mysql 都会选择正确的索引,选错索引算是比较少见的特殊情况了,文中的例子也是个特例,仅是给大家提供一个分析思路,当你遇到一些已经使用了索引但依然比较慢的 sql 语句的时候,可以尝试分析是否是 mysql 选错了索引的原因。

其实还有一些情况,会导致 mysql 选错索引,就是 mysql 预估扫描行的数据不够准确,而这个不准确通常是数据表有频繁的删除或更新操作导致的数据空洞造成的,关于这个原因,我会在后面再详细讲。

这篇文章如果对你有些启发,不妨点在在看吧,感谢支持,当然如果对文中有不太明白的地方,欢迎留言。另外,不知道大家对 explain 这个命令熟悉不,如果不熟悉的话,我考虑再单独写一篇关于 explain 使用的文章。

推荐文章:

如何让mysql索引更快一点

mysql索引为啥要选择B+树 (下)

本文分享自微信公众号 - 谭小谭(tanstory),作者:谭小谭

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-05-09

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • mysql为什么加索引就能快

    平时我们要优化 mysql 查询效率的时候,最常见的就是给表加上合适的索引了,那今天就来聊聊为什么加了索引就快了呢。

    谭小谭
  • 如何让mysql索引更快一点

    在 InnoDB 中,从二级索引回到主键索引查询数据,这个过程称作回表过程,而且这个回表过程是可以被优化的,这个优化就是利用覆盖索引。

    谭小谭
  • mysql为什么建议使用自增主键

    前面我写了几篇关于 mysql 索引的文章,索引是 mysql 非常重要的一部分。你也可能经常会看到一些关于 mysql 军规、mysql 查询优化的文章,其实...

    谭小谭
  • Mysql索引和性能优化

    使用索引的原则 1. 如果没有唯一性要求,可以选择普通索引 2. 如果列上有唯一性要求,可以选择唯一索引 3. 如果是需要模糊搜索,建议选择全文索引 4. 如果...

    wangxl
  • 真无线蓝牙耳机:如何在这场白热化的竞争中胜出?

    刚刚过去的2019年,是耳机厂商在真无线领域竞争白热化的一年,各品牌纷纷推出自己的真无线耳机产品,希望能够在这场没有硝烟的战斗中拔得头筹。转眼间2020年又已经...

    司想君
  • 第16章 Spring Boot + Kotlin: 下一代 Java 服务端开发

    2017-11-22 11:55:17.205 INFO 14721 --- [ main] org.hibernate.Version ...

    一个会写诗的程序员
  • 离线安装支持Intel MKL的R-3.6

    R是一个常用于统计学问题和画图的免费软件(https://www.r-project.org/)。在线安装十分简单,本文主要介绍离线安装,且让R使用Intel编...

    用户7592569
  • MyBatis-Plus的使用

    Mybatis-Plus(简称MP)是一个 Mybatis 的增强工具,在 Mybatis 的基础上只做增强不做改变,为简化开发、提高效率而生。这是官方给的定义...

    后端码匠
  • Redis基本入门

    1.Redis简介     Redis 是一种基于内存亦可持久化的日志型,Key-Value 数据库。可持久在于他的部分数据是存放在内存上,而当数据库重启以后他...

    lwen
  • 速读原著-Android应用开发入门教程(HelloActivity程序的运行)

    在软件开发的最初阶段,通常使用一个 Hello World 程序作为最简单的示例,本部分介绍一个 Android 中最简单应用程序,通过这部分内容可以了解到 A...

    cwl_java

扫码关注云+社区

领取腾讯云代金券