掘金 后端 ( ) • 2024-03-06 18:19
Mysql性能优化：探秘索引失效问题和实践分析
索引 性能

mysql索引原理

Mysql中常用的存储引擎InnoDB， 是在 MySQL 5.5 之后成为默认的 MySQL 存储引擎，B+Tree 索引类型也是 MySQL 存储引擎采用最多的索引类型。索引按照物理存储分类，可分为聚簇索引（索引结构和数据一起存放），非聚簇索引（索引结构和数据分开存放）。

在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为索引：

  • 如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
  • 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
  • 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；

其它索引都属于非聚簇索引，也被称为二级索引。创建的主键索引和二级索引默认使用的是 B+Tree 索引。

B+Tree 是一种多叉树，叶子节点才存放数据，非叶子节点只存放索引，而且每个节点里的数据是按主键顺序存放的。每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包括了所有的索引值信息，并且每一个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双向链表。

主键索引的 B+Tree 和二级索引的 B+Tree 区别如下：

  • 主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
  • 二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

对于使用主键索引的查询，只需要从B+Tree自定向下逐层遍历进行查找，数据库的索引和数据都是存储在硬盘的，我们可以把读取一个节点当作一次磁盘 I/O 操作。那么上面的整个查询过程一共经历了 3 个节点，也就是进行了 3 次 I/O 操作。

对于使用二级索引查询，需要先检索二级索引找到对应的叶子节点，获取主键值，再通过主键索引查找对应的叶子节点获取数据。这个过程叫回表，也就是说要查询两个B+Tree才能查找到数据。不过当查询的数据在二级索引中就能找到，就不用再使用主键索引查询，这种二级索引也叫覆盖索引。

mysql索引执行情况

如何查看查看我们的查询sql中索引查询是否生效，以及性能如何呢？可以使用EXPLAIN语句分析查询计划。

通过在SQL查询前添加EXPLAIN关键字，可以查看该查询如何使用索引以及表数据访问方式。

EXPLAIN SELECT ... FROM your_table WHERE ... ORDER BY ... ;

结果中的key列会显示用于查找行的索引名。如果为NULL，则表示没有使用索引。type列则指示了MySQL如何进行数据检索，性能从高到低如：

  • system：引擎支持表行统计是精确的（如MyISAM）,且表只有一条记录；使const的特例
  • const：表中最多只有一行匹配记录，主键或唯一索引
  • eq_ref：连表，前表的行在后表中只有一行对应，主键或唯一索引作为连表条件
  • ref：普通索引查询
  • index_merge：查询条件有多个索引（标识开启了Index Merge优化），key列出实际使用的索引
  • range：索引范围查询，key为实际使用索引
  • index：查询遍历了整棵索引树，与All类似，索引一般在内存中，更快
  • ALL：全表扫描，性能最差

另外，参数Extra也可以作为参考依据：

  • Using filesort：排序时用了外部索引排序，未使用表内索引排序；尽量避免
  • Using temporary：创建临时表存储查询结果，常见于order by和group by；尽量避免
  • Using index：使用了覆盖索引，不用回表，查询效率高
  • Using index condition：查询优化器使用了索引下推特性
  • Using where：使用了where字句条件过滤；在未使用索引时出现
  • Using join buffer；连表查询，在被驱动表未使用索引时，会将驱动表读取放到join buffer中，再遍历被驱动表和来驱动表查询

索引失效情况有哪些？

知道了如何查看索引是否失效，那索引失效的场景又有哪些呢？以下复现一些常见的索引失效场景，在这之前我们准备先准备一些数据。

数据准备

为了测试索引，使用存储过程创建数据量为1000万左右的test表。对test表中的字段做以下说明：

  • 其中id为自增主键，int类型；
  • num1和num2的值和id一样，num1是int类型，num2是varchar类型，分别创建索引；
  • type1和type2为int类型，type1值是id值对5000取模，type2值是id值对500取模，创建联合索引type（type1，type2）；
  • str1和str2为varchar类型，值为随机md5值，str1创建索引，str2没有建立索引，且可以为null。
-- 创建测试数据表
DROP TABLE IF EXISTS test;
CREATE TABLE test
(
    id    int(11)      NOT NULL,
    num1  int(11)      NOT NULL DEFAULT 0,
    num2  varchar(11)  NOT NULL DEFAULT '',
    type1 int(4)       NOT NULL DEFAULT 0,
    type2 int(4)       NOT NULL DEFAULT 0,
    str1  varchar(100) NOT NULL DEFAULT '',
    str2  varchar(100)          DEFAULT NULL,
    PRIMARY KEY (id),
    KEY num1 (num1),
    KEY num2 (num2),
    KEY type (type1, type2),
    KEY str1 (str1)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;
-- 创建存储过程
DROP PROCEDURE IF EXISTS pre_test;
DELIMITER //
CREATE PROCEDURE pre_test()
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    WHILE i < 10000000
        DO
            SET i = i + 1;
            SET @str1 = SUBSTRING(MD5(RAND()), 1, 10);
            IF i % 100 = 0 THEN
                SET @str2 = NULL;
            ELSE
                SET @str2 = @str1;
            END IF;
            INSERT INTO test (id, num1, num2,
                              type1, type2, str1, str2)
            VALUES (CONCAT('', i), CONCAT('', i),
                    CONCAT('', i), i % 5000, i % 500, @str1, @str2);
            -- 事务优化，每一万条数据提交一次事务
            IF i % 10000 = 0 THEN
                COMMIT;
            END IF;
        END WHILE;
END;
// DELIMITER ;
-- 执行存储过程
CALL pre_test();

数据的插入需要一定时间，我执行存储过程使用十多分钟，具体看个人使用的服务器配置如何；

image.png 结果如下图：

image.png

对索引使用左匹配

在使用条件查询时，使用like %xx或者like %xx%会造成索引失效。通过上面test表str1字段来测试，执行以下三条sql：

SELECT * FROM test WHERE str1 like '12356%';
SELECT * FROM test WHERE str1 like '%5692db2';
SELECT * FROM test WHERE str1 like '%5692db%';

从执行结果可以看出，分别执行的时间为145ms，4s 932ms，4s 929s，查询效率根本不是一个级别，基本可以判定后两项没有走索引。

image.png

我们通过执行计划可以看出type值，第一个为range即使用索引走了范围查询，后两个为All即走了全表扫描；其实从Extra值也可以看出。

image-20240306112642564.png

因为B+树是按照索引值有序排列存储的，只能根据前缀进行比较。

对索引使用函数

有时候我们需要通过Mysql提供的函数来得到我们想要的结果，如果查询条件中对索引字段使用函数就会导致索引失效。比如我们使用SUBSTRING()函数来举例：

image.png

从执行结果可以看出花费了4s 836ms的时间，再次通过查看执行计划可以看出走了全局扫描。

explain select * from test where SUBSTRING(str1, 1,6) = '235692'

image.png

因为索引保存的是所以字段的原始值，而经过计算后的值就不能走索引了。

对索引使用表达式

如果在条件查询中对索引进行表达式计算，也是不能走索引的。

比例下面在这个例子，对索引字段num1进行计算，从结果来看也是没有走索引的。

explain select * from test where num1 / 2 = 5

image.png

如果我们换一种写法，可以看出type使ref，标识是走了索引查询的。

explain select * from test where num1 = 2 * 5

image.png

原因和索引使用函数差不多，所以mysql并没有对这种计算进行处理，也正是这样，我们自己在写sql的时候不要用表达式计算。

对索引隐式类型转换

在解释隐式转换之前，我们先看看下面的几个sql例子，从上面准备的数据可以知道，字段num1和num2都建立了索引，不过前者是int类型，后者是varchar类型；

SELECT * FROM test WHERE num1 = 100000;
SELECT * FROM test WHERE num1 = '100000';
SELECT * FROM test WHERE num2 = 100000;
SELECT * FROM test WHERE num2 = '100000';

可以猜想一下，上面四条sql哪些会走索引，哪些不会走？我们先看看执行结果：

image.png

可以看出第三条查询为4秒多，其他都是毫秒级别。第一条和第四条可以理解，对应正确的类型是可以走索引的。但是第二条sql的查询条件也是做了隐式转换的，为什么就可以走索引呢？

我们先看看执行计划，可以看出第三条确实没有走索引，而第二条是走了索引的。

image-20240306153039817.png

其实在Mysql中，遇到字符串和数字比较的时候，会自动把字符串转换为数字，然后在进行比较。所以第二条sql，查询条件右侧的字符串会转换为数字，所以可以走索引。而第三天查询条件左侧字段为字符串，需要转换为数字，也就是查询sql其实等价于下面的sql，通过上面的结论，对索引使用函数是不会走索引查询的。

SELECT * FROM test WHERE CAST(num2 as signed int ) = '100000'

联合索引非最左匹配

我们知道，联合索引要能正确使用必须遵循最左匹配原则，从上面准备的数据中，我们对test表中的type1和type2建立了联合索引(type1,type2)，我们看看下面的sql哪些会走索引？

select * from test Where type1  =  500;
select * from test where type2 = 200;
select * from test where type2 = 200 and type1 = 500;

通过执行计划可以看出，第二条sql没有走索引，因为type2字段在联合索引中处于右侧，所以没走索引。

image.png

原因是，在联合索引的情况下，数据是按照索引第一列进行排序，第一列数据相同时才会按照第二列排序。

所以，如果我们想使用联合索引中尽可能多的列，查询条件中的各个列必须是联合索引中从最左边开始连续的列。如果我们仅仅按照第二列搜索，肯定无法走索引。

Where 字句中的OR

如果在WHERE查询条件中，OR的一侧是索引查询，一侧不是索引查询，也是会导致索引失效的，我么看几个例子：

select * from test WHERE str1 = '0747184ae5' or str2 =  '84bdb10f56';
select * from test WHERE str2 =  '84bdb10f56' or str1 = '0747184ae5';
select * from test WHERE str1 = '0747184ae5' or str1 =  '84bdb10f56';
select * from test WHERE str1 = '0747184ae5' or num1 =  500;

str1和num1是建立了索引的，而str2则没有，从结果可以论证上述结论。而第四条sql中，or两侧是不同的索引，type值为index_merge表示查询条件有多个索引。

image-20240306161259719.png

总结

其实，我们明白了索引结构的原理，也就很容易理解以上介绍了6种索引失效的场景：

  • 对索引使用左匹配，是指like查询条件使用like '%xx'或like '%xx%'会让索引失效。
  • 对索引使用函数，是指在查询条件中对索引字段使用mysql自带的函数进行计算会导致索引失效。
  • 对索引使用表达是，是指在查询条件中对索引字段进行计算会导致索引失效。
  • 对索引隐式转换，遇到字符串和数字进行比较时，mysql会将字符串转换为数字，索引在条件左侧为字符串。时，使用了Cast函数对索引字段转换，索引会导致索引失效。
  • 联合索引非最左匹配，在联合索引的使用中，需要遵循最左匹配原则，否则也是导致索引失效。
  • Where条件中使用OR查询，在其中一侧不是索引字段查询条件时，会导致另一侧的索引失效。
相关内容
MySQL索引（六）索引优化补充，分页查询、多表查询、统计查询
【MySQL系列】一条SQL，我怎么知道它有没使用到索引？
MySQL索引（五）索引优化分析工具
MySQL索引（四）常见的索引优化手段
你管这叫数据库索引？
在PostgreSQL中管理索引
JAVA都这么卷了，MySQL索引你还不了解吗？一文带你熟悉MySQL索引。
无索引的join关联的表，你如何优化？
Mysql篇——索引
数据库管理系统基础-第二篇：索引