必知的 MySQL 索引失效场景【包括实践验证】,别再踩坑了!(上)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 必知的 MySQL 索引失效场景【包括实践验证】,别再踩坑了!

一、背景

我们经常都会踩到Mysql数据库不走索引的坑。常见的现象就是:明明在字段上添加了索引,但却并未生效。

另外,无论是面试或是日常,Mysql索引失效的通常情况都应该了解和学习。

为了方便学习和记忆,这篇文件将常见的15种不走索引情况进行汇总,并以实例展示,帮助大家更好地避免踩坑。建议收藏,以备不时之需。

二、数据库及索引准备

2.1 创建表结构

为了逐项验证索引的使用情况,我们先准备一张表t_user:

CREATE TABLE `t_user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `id_no` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '身份编号',
  `username` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `union_idx` (`id_no`,`username`,`age`),
  KEY `create_time_idx` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

在上述表结构中有三个索引:

  • id:为数据库主键;
  • union_idx:为id_no、username、age构成的联合索引;
  • create_time_idx:是由create_time构成的普通索引;

2.2 初始化数据

初始化数据分两部分:基础数据和批量导入数据。

基础数据insert了4条数据,其中第4条数据的创建时间为未来的时间,用于后续特殊场景的验证:

INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1001', 'Tom1', 11, '2022-02-27 09:04:23');
INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1002', 'Tom2', 12, '2022-02-26 09:04:23');
INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1003', 'Tom3', 13, '2022-02-25 09:04:23');
INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1004', 'Tom4', 14, '2023-02-25 09:04:23');

除了基础数据,还有一条存储过程及其调用的SQL,方便批量插入数据,用来验证数据比较多的场景:

-- 删除历史存储过程
DROP PROCEDURE IF EXISTS `insert_t_user`

-- 创建存储过程
delimiter $

CREATE PROCEDURE insert_t_user(IN limit_num int)
BEGIN
 DECLARE i INT DEFAULT 10;
    DECLARE id_no varchar(18) ;
    DECLARE username varchar(32) ;
    DECLARE age TINYINT DEFAULT 1;
    WHILE i < limit_num DO
        SET id_no = CONCAT("NO", i);
        SET username = CONCAT("Tom",i);
        SET age = FLOOR(10 + RAND()*2);
        INSERT INTO `t_user` VALUES (NULL, id_no, username, age, NOW());
        SET i = i + 1;
    END WHILE;

END $
-- 调用存储过程
call insert_t_user(100);

关于存储过程的创建和存储,可暂时不执行,当用到时再执行。

2.3 数据库版本及执行计划

查看当前数据库的版本:

select version();
8.0.18


上述为本人测试的数据库版本:8.0.18。当然,以下的所有示例,大家可在其他版本进行执行验证。


查看SQL语句执行计划,一般我们都采用explain关键字,通过执行结果来判断索引使用情况。

执行示例:

explain select * from t_user where id = 1;

执行结果:

可以看到上述SQL语句使用了主键索引(PRIMARY),key_len为4;

其中key_len的含义为:表示索引使用的字节数,根据这个值可以判断索引的使用情况,特别是在组合索引的时候,判断该索引有多少部分被使用到非常重要。

三、索引失效的情况

做好以上数据及知识的准备,下面就开始讲解具体索引失效的实例了。

1. 联合索引不满足最左匹配原则

联合索引遵从最左匹配原则,顾名思义,在联合索引中,最左侧的字段优先匹配。 因此,在创建联合索引时,where子句中使用最频繁的字段放在组合索引的最左侧。

而在查询时,要想让查询条件走索引,则需满足:最左边的字段要出现在查询条件中

实例中,union_idx联合索引组成:

KEY `union_idx (`id_no`,`username`,`age`)

最左边的字段为id_no,一般情况下,只要保证id_no出现在查询条件中,则会走该联合索引。

示例一:
explain select * from t_user where id_no = '1002';

explain结果:

通过explain执行结果可以看出,上述SQL语句走了union_idx这条索引。

这里再普及一下key_len的计算

  • id_no 类型为varchar(18),字符集为utf8mb4_bin,也就是使用4个字节来表示一个完整的UTF-8。此时,key_len = 18* 4 = 72;
  • 由于该字段类型varchar为变长数据类型,需要再额外添加2个字节。此时,key_len = 72 + 2 = 74;
  • 由于该字段运行为NULL(default NULL),需要再添加1个字节。此时,key_len = 74 + 1 = 75;

上面演示了key_len一种情况的计算过程,后续不再进行逐一推演,知道基本组成和原理即可,更多情况大家可自行查看。

示例二:
explain select * from t_user where id_no = '1002' and username = 'Tom2';

explain结果:

很显然,依旧走了union_idx索引,根据上面key_len的分析,大胆猜测,在使用索引时,不仅使用了id_no列,还使用了username列。

示例三:
explain select * from t_user where id_no = '1002' and age = 12;

explain结果:

走了union_idx索引,但跟示例一一样,只用到了id_no列。

当然,还有三列都在查询条件中的情况,就不再举例了。上面都是走索引的正向例子,也就是满足最左匹配原则的例子,下面来看看,不满足该原则的反向例子。

反向示例:
explain select * from t_user where username = 'Tom2' and age = 12;

explain结果:

此时,可以看到未走任何索引,也就是说索引失效了。

同样的,下面只要没出现最左条件的组合,索引也是失效的:

explain select * from t_user where age = 12;
explain select * from t_user where username = 'Tom2';

那么,第一种索引失效的场景就是:在联合索引的场景下,查询条件不满足最左匹配原则。

2. 使用了select *

在《阿里巴巴开发手册》的ORM映射章节中有一条【强制】的规范:

【强制】在表查询中,一律不要使用 * 作为查询的字段列表,需要哪些字段必须明确写明。说明:

1)增加查询分析器解析成本。

2)增减字段容易与 resultMap 配置不一致。

3)无用字段增加网络 消耗,尤其是 text 类型的字段。

虽然在规范手册中没有提到索引方面的问题,但禁止使用select * 语句可能会带来的附带好处就是:某些情况下可以走覆盖索引

比如,在上面的联合索引中,如果查询条件是age或username,当使用了select * ,肯定是不会走索引的。

但如果希望根据username查询出id_no、username、age这三个结果(均为索引字段),明确查询结果字段,是可以走覆盖索引的:

explain select id_no, username, age from t_user where username = 'Tom2';
explain select id_no, username, age from t_user where age = 12;

explain结果:

无论查询条件是username还是age,都走了索引,根据key_len可以看出使用了索引的所有列。

第二种索引失效场景:在联合索引下,尽量使用明确的查询列来趋向于走覆盖索引;

这一条不走索引的情况属于优化项,如果业务场景满足,则进来促使SQL语句走索引。至于阿里巴巴开发手册中的规范,只不过是两者撞到一起了,规范本身并不是为这条索引规则而定的。

3. 索引列参与运算

直接来看示例:

explain select * from t_user where id + 1 = 2 ;

可以看到,即便id列有索引,由于进行了计算处理,导致无法正常走索引。


针对这种情况,其实不单单是索引的问题,还会增加数据库的计算负担。就以上述SQL语句为例,数据库需要全表扫描出所有的id字段值,然后对其计算,计算之后再与参数值进行比较。如果每次执行都经历上述步骤,性能损耗可想而知。

建议的使用方式是:先在内存中进行计算好预期的值,或者在SQL语句条件的右侧进行参数值的计算。

针对上述示例的优化如下:

-- 内存计算,得知要查询的id为1
explain select * from t_user where id = 1 ;
-- 参数侧计算
explain select * from t_user where id = 2 - 1 ;

第三种索引失效情况:索引列参与了运算,会导致全表扫描,索引失效。

4. 索引列参使用了函数示例:

explain select * from t_user where SUBSTR(id_no,1,3) = '100';

explain结果:

上述示例中,索引列使用了函数(SUBSTR,字符串截取),导致索引失效。

此时,索引失效的原因与第三种情况一样,都是因为数据库要先进行全表扫描,获得数据之后再进行截取、计算,导致索引索引失效。同时,还伴随着性能问题。


示例中只列举了SUBSTR函数,像CONCAT等类似的函数,也都会出现类似的情况。解决方案可参考第三种场景,可考虑先通过内存计算或其他方式减少数据库来进行内容的处理。

第四种索引失效情况:索引列参与了函数处理,会导致全表扫描,索引失效。

5. 错误的Like使用

示例:

explain select * from t_user where id_no like '%00%';

explain结果:

针对like的使用非常频繁,但使用不当往往会导致不走索引。常见的like使用方式有:

方式一:like ‘%abc’;

方式二:like ‘abc%’;

方式三:like ‘%abc%’;

其中方式一和方式三,由于占位符出现在首部,导致无法走索引。这种情况不做索引的原因很容易理解,索引本身就相当于目录,从左到右逐个排序。而条件的左侧使用了占位符,导致无法按照正常的目录进行匹配,导致索引失效就很正常了。

第五种索引失效情况:模糊查询时(like语句),模糊匹配的占位符位于条件的首部。

必知的 MySQL 索引失效场景【包括实践验证】,别再踩坑了!(下):https://developer.aliyun.com/article/1509769

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1天前
|
存储 SQL 关系型数据库
初识mysql索引 - 小白篇
初识mysql索引 - 小白篇
|
4天前
|
SQL 缓存 关系型数据库
MySQL慢查询优化实践问答
MySQL慢查询优化实践问答
|
4天前
|
SQL 存储 关系型数据库
MySQL数据库进阶第二篇(索引,SQL性能分析,使用规则)
MySQL数据库进阶第二篇(索引,SQL性能分析,使用规则)
|
6天前
|
关系型数据库 MySQL 数据库
mysql索引优化
【6月更文挑战第16天】mysql索引优化
9 2
|
5天前
|
存储 关系型数据库 MySQL
MySQL索引18连问,谁能顶住
MySQL索引18问概览: 1. 索引是提升查询速度的数据结构,如书的目录。 2. 索引类型包括B+tree、Hash、Full-text、R-Tree等,B+tree擅长范围查询,Hash擅长等值比较。 3. 主键索引唯一且不可为空,每表只能一个;唯一索引允许唯一值,可为空。 4. 聚簇索引数据与索引顺序一致,非聚簇索引存储指针。
179 0
MySQL索引18连问,谁能顶住
|
1天前
|
SQL NoSQL 关系型数据库
Mysql锁及适用场景
Mysql锁及适用场景
7 0
|
2天前
|
存储 关系型数据库 MySQL
索引的威力--记一次MySQL存储过程优化
在MySQL存储过程中,一个`INSERT INTO SELECT`语句起初执行超过130秒,优化后,执行时间降低到1秒内,实现了100倍的性能提升。问题在于`NOT IN`子查询导致的慢查询,最终通过创建单列索引获得了最佳效果。文章还介绍了索引创建的基本语法,并讨论了单列索引与组合索引的优缺点。作者强调,随着数据量增加,索引对于查询性能的重要性,计划未来采用读写分离来进一步优化处理大量插入和查询的场景。
|
1天前
|
存储 关系型数据库 MySQL
关系型数据库mysql日志和临时文件
【6月更文挑战第15天】
17 4
|
1天前
|
存储 关系型数据库 MySQL
关系型数据库mysql数据文件存储
【6月更文挑战第15天】
8 4
|
1天前
|
存储 关系型数据库 MySQL
关系型数据库mysql文件系统支持
【6月更文挑战第15天】
18 4

热门文章

最新文章