14个必知的 MySQL 索引失效场景,别再踩坑了!(上)

发表于:2022-3-01 09:49

字体: | 上一篇 | 下一篇 | 我要投稿

 作者:二师兄    来源:程序新视界

#
MySQL
分享:
  背景
  无论你是技术大佬,还是刚入行的小白,时不时都会踩到Mysql数据库不走索引的坑。常见的现象就是:明明在字段上添加了索引,但却并未生效。
  前些天就遇到一个稍微特殊的场景,同一条SQL语句,在某些参数下生效,在某些参数下不生效,这是为什么呢?
  另外,无论是面试或是日常,Mysql索引失效的通常情况都应该了解和学习。
  为了方便学习和记忆,这篇文件将常见的14种不走索引情况进行汇总,并以实例展示,帮助大家更好地避免踩坑。建议收藏,以备不时之需。
  数据库及索引准备
  创建表结构
  为了逐项验证索引的使用情况,我们先准备一张表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构成的普通索引;
  初始化数据
  初始化数据分两部分:基础数据和批量导入数据。
  基础数据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);

  关于存储过程的创建和存储,可暂时不执行,当用到时再执行。
  数据库版本及执行计划
  查看当前数据库的版本:
  select version();
  8.0.18

  上述为本人测试的数据库版本:8.0.18。当然,以下的所有示例,大家可在其他版本进行执行验证。
  查看SQL语句执行计划,一般我们都采用explain关键字,通过执行结果来判断索引使用情况。
  执行示例:
  explain select * from t_user where id = 1;
  执行结果:
explain

  可以看到上述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-01

  通过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结果:
explain-02

  很显然,依旧走了union_idx索引,根据上面key_len的分析,大胆猜测,在使用索引时,不仅使用了id_no列,还使用了username列。
  示例三:
  explain select * from t_user where id_no = '1002' and age = 12;

  explain结果:
explain-03

  走了union_idx索引,但跟示例一一样,只用到了id_no列。
  当然,还有三列都在查询条件中的情况,就不再举例了。上面都是走索引的正向例子,也就是满足最左匹配原则的例子,下面来看看,不满足该原则的反向例子。
  反向示例:
  explain select * from t_user where username = 'Tom2' and age = 12;

  explain结果:
explain-04

  此时,可以看到未走任何索引,也就是说索引失效了。
  同样的,下面只要没出现最左条件的组合,索引也是失效的:
  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 ;

  explain结果:
索引列计算

  可以看到,即便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使用方式有:
  ·方式一:like '%abc';
  · 方式二:like 'abc%';
  · 方式三:like '%abc%';
  其中方式一和方式三,由于占位符出现在首部,导致无法走索引。这种情况不做索引的原因很容易理解,索引本身就相当于目录,从左到右逐个排序。而条件的左侧使用了占位符,导致无法按照正常的目录进行匹配,导致索引失效就很正常了。
  第五种索引失效情况:模糊查询时(like语句),模糊匹配的占位符位于条件的首部。

  本文内容不用于商业目的,如涉及知识产权问题,请权利人联系51Testing小编(021-64471599-8017),我们将立即处理
《2023软件测试行业现状调查报告》独家发布~

关注51Testing

联系我们

快捷面板 站点地图 联系我们 广告服务 关于我们 站长统计 发展历程

法律顾问:上海兰迪律师事务所 项棋律师
版权所有 上海博为峰软件技术股份有限公司 Copyright©51testing.com 2003-2024
投诉及意见反馈:webmaster@51testing.com; 业务联系:service@51testing.com 021-64471599-8017

沪ICP备05003035号

沪公网安备 31010102002173号