首页 实用干货 🌋

一、前言

在日常开发中,我们往往会给表加各种索引,来提高 MySQL 的检索效率。
但我们有时会遇到明明给字段加了索引,并没有走索引的Case。 进而导致 MySQL 产生慢查询。
严重场景下,甚至出现主从延迟、数据库拖垮的极端事故。

本文梳理出索引失效的几种常见场景给大家参考。

二、技术基础

Explain 命令使用

只要我们在 SQL 前加上 explain,就可以分析出,当前环境下 MySQL 的“查询方式”以及“索引选择”。

首先大致看下每个字段的含义:

列名 含义
id 每个select操作的唯一标识
select_type 查询的类型,我们可以根据该字段判断查询的性质,包括查询是简单/复杂查询类型
table 查询访问表的别名
type 关联的类型,mysql把查询过程都视为关联,不管是单表/多表。这个字段也是衡量查询性能的关键字段之一
possible_keys 查询可能会使用哪些索引,这列是基于查询访问的列来判断的
key mysql最终决定使用哪个索引(这个索引不一定出现在possible_keys中)
key_len mysql在索引里使用的字节数,我们可以根据它推断具体使用了索引中的哪些字段
ref 查找所用的列/常量
rows mysql估算的预计扫描行数,这个数字和实际扫描的行数可能相差甚远,包括limit语句对于这个估算值也是不起作用的
filtered 表里符合条件的记录数的百分比的估计,我们可以用这个字段大致估计表关联时关联的记录数
extra 包含一些额外信息,也是我们优化时需要重点关注的字段

Type(重点看)

type 列表示了 MySQL 关联的类型,它代表了mysql是如何在表里找数据的。

下面按性能从高到低的顺序介绍type类型:
以下四种类型,说明 “性能很好,一般无需优化”

  • system:表里就一条数据
  • const:一般是针对主键/唯一键的等值查询,mysql可以把这类查询优化为一个常量表达式
  • eq_ref:一般出现在多表join时,针对主键/唯一键的等值查询,mysql知道只需要返回一条记录
  • ref:多表 join 时,针对索引字段的查询

以下几种类型,需要 “看具体情况,决定是否要优化”

  • fulltext:关联使用了全文索引
  • ref_or_null:查询走了索引,但是除此之外还要判断字段是不是null,如果出现这种类型,可以考虑这个字段是否有为空的必要
  • index_merge:使用了索引合并优化,如果高频出现,可以考虑是不是索引设计有问题。
  • unique_subquery:in 子句中的子查询,如果只访问主键/唯一键可能会出现这种 type,并不常见
  • index_subquery:同样是 in 里的子查询,访问了索引列,并不常见
  • range:对索引字段的范围扫描,一般出现在带有比较的查询语句中,一些in和or的查询也会导致这种类型的扫描

以下两种类型,需要 “优化 & 避免出现”

  • index:按索引进行全表扫描,如果查询不是覆盖索引的,可能会产生很大量的随机IO
  • all:全表扫描

三、准备工作

  1. 建一张 user
CREATE TABLE `user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint(20) NOT NULL COMMENT '用户uuid',
  `user_name` varchar(64) DEFAULT '' COMMENT '用户昵称',
  `email` varchar(64) DEFAULT '' COMMENT '邮箱',
  `age` tinyint(4) DEFAULT '1' COMMENT '年龄',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建日期',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新日期',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_userid` (`user_id`),
  KEY `idx_username_email_age` (`user_name`,`email`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
  1. 初始化一些数据
-- 创建存储过程
delimiter $

CREATE PROCEDURE insert_user(IN limit_num int)
BEGIN
 DECLARE i INT DEFAULT 10;
    DECLARE user_id bigint(20) ;
    DECLARE username varchar(64) ;
    DECLARE email varchar(64) ;    
    DECLARE age TINYINT(4) DEFAULT 1;
    WHILE i < limit_num DO
        SET user_id =  FLOOR(RAND() * 100000000);
        SET username = CONCAT("647-",i);
        SET email = CONCAT(username,"@163.com");
        SET age = FLOOR(RAND() * 100);
        INSERT INTO `user` VALUES (NULL, user_id, username, email, age, NOW(), NOW());
        SET i = i + 1;
    END WHILE;

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

四、几种常见的索引失效场景

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

  • 错误示例:

    explain select * from user where age = 20 and email = "647@163.com";
    
  • 分析结果:

1

  • 优化思路:

根据业务场景,合理的建立相应的联合索引。

2. 范围查询,数量级过大,默认走全表扫描

一般来说,MySQL 判断数量级返回超过全数的 10% ~ 30%(或者达到某个阈值),默认会走全表扫描。

  • 错误示例:

    explain select * from user where user_id > 10;
    
  • 分析结果:

2

  • 产生原因:MySQL 优化器判断走索引&回表带来的消耗,比走全表还要多。因此,会走全表扫描。

  • 优化思路:

    根据业务场景,预估返回数量级。如果数量级过大,可以分批拉取。
    反之,可以加 limit 或者 force index 走索引。

3. 索引列参与运算

  • 错误示例:

    explain select * from user where id + 1 = 2;
    
  • 分析结果:

image.png

  • 优化思路:

不要用数据库做运算,不浪费宝贵的数据库资源。

4. 索引列使用了函数

  • 错误示例:

    explain select * from user where SUBSTR(user_id,1,3) = '100';
    
  • 分析结果:

4

  • 优化思路:

不要用数据库做函数运算,不浪费宝贵的数据库资源。

5. 错误的 like 使用

  • 错误示例:

    explain select * from user where user_name like '%00%';
    
  • 分析结果:

5

  • 优化思路:

严禁使用左%匹配,要用只能用右%匹配。
如果实在有业务场景,可以使用 ES 做。

6. 隐式类型转换

  • 错误示例:

    explain select * from user where user_name = 647;
    
  • 分析结果:

6-1

user_namevarchar 类型,传入 INT 比较,会产生 INT -> varchar 的隐式类型转换导致索引失效。

  • 特殊 Case:
explain select * from user where user_id = "647";
  • 分析结果:

6-2

user_idbigint 类型,如果传入字符串比较。虽然产生隐式转换,但不会导致索引失效。

  • 优化思路:

注意字段类型,避免隐式转换。

7. OR 使用不当

  • 错误示例:
explain select * from user where user_name = "647" or email = "647@163.com";
  • 分析结果:

7

  • 优化思路:

确保 or 的两边都要有索引。

8. 两个索引列做比较

  • 错误示例:
explain select * from user where user_id > id;
  • 分析结果:

8

  • 优化思路:

不要对两个列做比较。

9. 非主键列,加上 not,索引失效

in 会走索引,not in 不会走索引
exists 会走索引,not exists 不会走索引
is null 会走索引,is not null 不会走索引

  • 错误示例:
explain select * from user where user_id not in (647)
  • 分析结果:

9

  • 优化思路:

不要用 not。



文章评论

未显示?请点击刷新