MySQL 如何优化慢查询？

青旅半醒 2023-09-23 18:13 162阅读 0赞

# 一、前言 #

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

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

# 二、技术基础 #

## Explain 命令使用 ##

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

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

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

### 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";
    复制代码

*  分析结果：

![format_png][]

*  优化思路：

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

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

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

*  错误示例：

explain select * from user where user_id > 10;
    复制代码

*  分析结果：

![format_png 1][]

*  产生原因：MySQL 优化器判断走索引&回表带来的消耗，比走全表还要多。因此，会走全表扫描。
 *  优化思路：

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

## 3. 索引列参与运算 ##

*  错误示例：

explain select * from user where id + 1 = 2;
    复制代码

*  分析结果：

![format_png 2][]

*  优化思路：

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

## 4. 索引列使用了函数 ##

*  错误示例：

explain select * from user where SUBSTR(user_id,1,3) = '100';
    复制代码

*  分析结果：

![format_png 3][]

*  优化思路：

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

## 5. 错误的 like 使用 ##

*  错误示例：

explain select * from user where user_name like '%00%';
    复制代码

*  分析结果：

![format_png 4][]

*  优化思路：

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

## 6. 隐式类型转换 ##

*  错误示例：

explain select * from user where user_name = 647;
    复制代码

*  分析结果：

![format_png 5][]

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

*  特殊 Case：

explain select * from user where user_id = "647";
    复制代码

*  分析结果：

![format_png 6][]

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

*  优化思路：

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

## 7. OR 使用不当 ##

*  错误示例：

explain select * from user where user_name = "647" or email = "647@163.com";
    复制代码

*  分析结果：

![format_png 7][]

*  优化思路：

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

## 8. 两个索引列做比较 ##

*  错误示例：

explain select * from user where user_id > id;
    复制代码

*  分析结果：

![format_png 8][]

*  优化思路：

> 不要对两个列做比较。

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

`in` 会走索引，`not in` 不会走索引 `exists` 会走索引，`not exists` 不会走索引 `is null` 会走索引，`is not null` 不会走索引

*  错误示例：

explain select * from user where user_id not in (647)
    复制代码

*  分析结果：

![format_png 9][]

*  优化思路：

> 不要用 not。

## 10. 非主键列，order by 可能导致索引失效 ##

具体是否失效，和使用的 MySQL 版本也有一定关系。 具体需要根据 explain 分析。

如果 MySQL 版本支持，需要注意满足“最左原则”。

*  错误示例：

explain select * from user order by user_id;
    复制代码

*  分析结果：

![format_png 10][]

*  优化思路：

> 非主键列，尽量不要用 order by。实在要用，需要先用 explain 分析是否可以走索引。  
> 如果条件允许，可以用 ES 代替。

[format_png]: https://img-blog.csdnimg.cn/img_convert/1d817952c3de9914e41d9f059f0f7039.webp?x-oss-process=image/format,png
[format_png 1]: https://img-blog.csdnimg.cn/img_convert/e2d8a070a1d7a3cf8eae66d84163d760.webp?x-oss-process=image/format,png
[format_png 2]: https://img-blog.csdnimg.cn/img_convert/1bc0375e5b92ada55385794cfa2e58b9.webp?x-oss-process=image/format,png
[format_png 3]: https://img-blog.csdnimg.cn/img_convert/909fc00d2239428585515475a8f5d416.webp?x-oss-process=image/format,png
[format_png 4]: https://img-blog.csdnimg.cn/img_convert/3a482be8d8da8ceae827a3a56d25aa56.webp?x-oss-process=image/format,png
[format_png 5]: https://img-blog.csdnimg.cn/img_convert/91137fad9758be99f7914a53f9c6fa2c.webp?x-oss-process=image/format,png
[format_png 6]: https://img-blog.csdnimg.cn/img_convert/bcc373b4e51a11b80d2bcf6f4c4de594.webp?x-oss-process=image/format,png
[format_png 7]: https://img-blog.csdnimg.cn/img_convert/2aaa1fbb31258cdb3dda859f8545c5d2.webp?x-oss-process=image/format,png
[format_png 8]: https://img-blog.csdnimg.cn/img_convert/bc9c1c6197a06ba78f8aac9830d08c8b.webp?x-oss-process=image/format,png
[format_png 9]: https://img-blog.csdnimg.cn/img_convert/a415b74882154c287714ea8cafe113ec.webp?x-oss-process=image/format,png
[format_png 10]: https://img-blog.csdnimg.cn/img_convert/133fa2c7454f83d58e7cf4254c8d54a1.webp?x-oss-process=image/format,png