当前位置：首页 > article >正文

小知识(9) MySQL慢查询sql分析与优化之explain解析

article 2024/10/28 17:32:14

前言

EXPLAIN 是 MySQL 中用于分析查询执行计划的关键字。
通过 EXPLAIN，可以了解 MySQL 如何执行 SQL 查询，包括表的读取顺序、数据行的读取方式以及使用的索引等信息。
这对于慢查询sql分析与优化非常有帮助。

Explain语法

explain select … from … [where …]

示例:

explain select * from t_user where user_id=1;

+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_user | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Navicat中可点击解释查看
在这里插入图片描述

各字段解释

id：SELECT查询标志位，每个SELECT都会自动分配一个唯一标识符。数字越大越先执行，如果说数字一样大，那么就从上往下依次执行，id列为null的就表示这是一个结果集，不需要使用它来进行查询。
select_type：select_type就是select的类型，可以有以下几种：
- SIMPLE：简单SELECT(不使用UNION或子查询等)
- PRIMARY：最外面的SELECT
- UNION：UNION中的第二个或后面的SELECT语句
- DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询
- UNION RESULT：UNION的结果。
- SUBQUERY：子查询中的第一个SELECT
- DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询
- DERIVED：导出表的SELECT(FROM子句的子查询)
table：显示这一行的数据是关于哪张表的
type：这列最重要，显示了连接使用了哪种类别,有无使用索引，是使用Explain命令分析性能瓶颈的关键项之一
结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。
- system: const 的一种特例，表中只有一行数据
- const: 针对主键或唯一性索引的等值查询扫描，最多只返回一行数据
- eq_ref: 通常出现在多表的join查询，表示针对于前表的每一个结果，都只能匹配到后表的一行结果
- ref: 通常出现在多表的join查询，针对非主键索引或非唯一索引或使用了最左前缀匹配的索引查询（ ex:通过普通索引查询匹配很多行时）
- fulltext: 全文索引
- ref_or_null: 跟 ref 类似的效果，不过多一个列不能 null 的条件
- index_merge: 此连接类型表示使用了索引合并优化。在这种情况下，输出行中的 key 列包含使用的索引列表，key_len包含所用索引的最长 key 部分列表
- unique_subquery: 在使用 in 查询的情况下会取代 eq_ref
- range: 使用索引范围查询，一个有限制的索引扫描。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符，用常量比较关键字列时,可以使用 range
  当 type 是 range 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个
- index: 全索引扫描（full index scan），扫描所有的索引, 而不扫描数据。
  只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。如在Extra列看到Using index，说明正在使用覆盖索引，只扫描索引的数据，它比按索引次序全表扫描的开销要小很多
- ALL: 全表扫描
possible_keys：在查询时，能够使用到的索引
key：在当前查询时真正使用到的索引。如果没有选择索引，键是NULL
key_len：显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好
- 字符串
  - char(n): n 字节长度
  - varchar(n): 如果是 utf8 编码, 则是 3 n + 2字节; 如果是 utf8mb4 编码, 则是 4 n + 2 字节。
- 数值类型:
  - TINYINT: 1字节
  - SMALLINT: 2字节
  - MEDIUMINT: 3字节
  - INT: 4字节
  - BIGINT: 8字节
- 时间类型
  - DATE: 3字节
  - TIMESTAMP: 4字节
  - DATETIME: 8字节
- 字段属性: NULL 属性占用一个字节. 如果一个字段是 NOT NULL 的, 则没有此属性。
ref：显示使用哪个列或常数与key一起从表中选择行。
rows：MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数。
Extra：包含MySQL解决查询的详细信息，也是关键参考项之一。
- Using filesort：MySQL需额外的排序操作,不能通过索引顺序达到排序效果.一般有 Using filesort,都建议优化去掉,因为这样的查询CPU资源消耗大。
- Using temporary：查询有使用临时表,一般出现于排序,分组和多表join的情况(不是GROUP BY上), 查询效率不高, 建议优化。
- Using index：使用覆盖索引
- Using where：在查找使用索引的情况下，需要回表去查询所需的数据
- Using index condition：查找使用了索引，但是需要回表查询数据
- Using where; Using index => 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
- Distinct：一旦MYSQL找到了与行相联合匹配的行，就不再搜索了
- Not exists：MYSQL 优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了
- Range checked for each
- Record（index map:#）没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一

当type 显示为 “index” 时，并且Extra显示为“Using Index”，表明使用了覆盖索引。

示例

假设有一个表 users，结构如下：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    created_at DATETIME
);

示例查询

EXPLAIN SELECT * FROM users WHERE email = 'xxx';

解释输出

+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1  | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL  | 1000 | 10.00    | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+

id: 1，表示这是第一个查询。
select_type: SIMPLE，表示这是一个简单的查询。
table: users，表示操作的表是 users。
partitions: NULL，表示没有使用分区。
type: ALL，表示全表扫描。
possible_keys: NULL，表示没有可能使用的索引。
key: NULL，表示没有使用索引。
key_len: NULL，表示没有使用索引长度。
ref: NULL，表示没有引用的列或常量。
rows: 1000，表示估计需要检查 1000 行。
filtered: 10.00，表示按表条件过滤的行百分比为 10%。
Extra: Using where，表示使用了 WHERE 条件进行过滤。

优化建议