DBA MySQL执行计划

功能概述

​ 执行计划是DBA常用的分析SQL语句的一种手段。

​ 使用执行计划能够拿到优化器选择完成后，其认为代价最小的一种执行方式。

​ 在语句执行之前，拿到执行计划可以预防因SQL语句编写不妥当带来的性能问题，同时也能够对慢查询语句进行评估和优化。

使用语法

​ 使用执行计划有两种方式，语法格式如下所示：

# 语法1：
EXPLAIN SQL语句;

# 语法2：
DESC SQL语句;

​ 示例演示：

M > DESC SELECT * FROM userInfo\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: userInfo
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL

​ 重要参数说明：

参数	描述
table	查询的表
type	查询类型
possible_keys	可能走的索引
key	走的索引名
key_len	应用索引的长度
Extra	额外的信息

type释义

​ 查看type信息，了解该语句性能，type可能出现的值有6种。

​ 从左往右性能依次变好，生产中一般都要达到RANGE标准才可：

ALL -   INDEX --   RANGE --   REF --   EQ_REF --   CONST(system) --   NULL

​ 用例子说明一切可能出现的情况，表结构展示如下，name为辅助索引列，id为聚集索引列：

M > DESC userinfo;
+--------+--------------------------------+------+-----+---------+----------------+
| Field  | Type                           | Null | Key | Default | Extra          |
+--------+--------------------------------+------+-----+---------+----------------+
| id     | int(11)                        | NO   | PRI | NULL    | auto_increment |
| name   | char(32)                       | NO   | MUL | NULL    |                |
| age    | tinyint(3) unsigned            | NO   |     | 0       |                |
| gender | enum('MALE','FEMALE','UNKNOW') | NO   |     | UNKNOW  |                |
+--------+--------------------------------+------+-----+---------+----------------+

​ ALL，全表扫描，不走索引，以下语句可能导致该级别的出现：

# 1.查询时没有使用索引进行查询
DESC SELECT * FROM userInfo LIMIT 1;

# 2.非PRIMARY KEY(聚集索引)中的
# != >= NOT IN IN LIKE(%开头) OR 
DESC SELECT * FROM userInfo WHERE name != "Jack";
DESC SELECT * FROM userInfo WHERE name >= "Jack";
DESC SELECT * FROM userInfo WHERE name IN ("Jack","Tom");
DESC SELECT * FROM userInfo WHERE name NOT IN ("Jack","Tom");
DESC SELECT * FROM userInfo WHERE name LIKE "%y";
DESC SELECT * FROM userInfo WHERE name = "Jack" OR name = "Ken";

# 3.在聚集索引中使用NOT IN
DESC SELECT * FROM userInfo WHERE id NOT IN(1,2,3);

# 特别的，对非聚集索引中的OR可进行优化调整，达到ref级别：
DESC SELECT * FROM userInfo WHERE name = "JACK"
UNION
SELECT * FROM userInfo WHERE name = "TOM";

​ INDEX，全索引扫描，以下语句可能导致该级别的出现：

# 1. 查询需要获取整个索引树的值时
DESC SELECT name FROM userInfo;
DESC SELECT id FROM userInfo;

# 2. 联合索引中，任意一个非最左列作为查询条件
# 联合索引(name,age)
DESC SELECT * FROM userInfo WHERE age = 18;

​ RANGE，索引范围扫描，以下语句可能导致该级别的出现：

# 1.在辅助索引中使用 < > <= LIKE(非%开头)
DESC SELECT * FROM userInfo WHERE name < "Jack";
DESC SELECT * FROM userInfo WHERE name > "Jack";
DESC SELECT * FROM userInfo WHERE name <= "Jack";
DESC SELECT * FROM userInfo WHERE name LIKE "y%";

# 2.在聚集索引中使用 < > <= >= != IN OR
DESC SELECT * FROM userInfo WHERE id > 1;
DESC SELECT * FROM userInfo WHERE id <= 1;
DESC SELECT * FROM userInfo WHERE id >= 1;
DESC SELECT * FROM userInfo WHERE id != 1;
DESC SELECT * FROM userInfo WHERE id IN(1,2,3);
DESC SELECT * FROM userInfo WHERE id = 1 OR id = 2;

​ REF，非唯一索引的等值查询，以下语句可能导致该级别的出现：

# 1. 在辅助索引（非唯一）中使用 =
DESC SELECT * FROM userInfo WHERE name = "Jack";

​ EQ_REF，多表连接查询时，ON的连接条件为唯一索引（UNIQUE KEY或者PRIMARY KEY），以下语句可能导致该级别的出现：

# userinfo.fk_leval字段也必须设置UNIQUE约束，即一对一表关系

DESC SELECT * FROM

userInfo JOIN userLeval

ON userInfo.fk_leval = userLeval.id;

​ CONST(system)，唯一索引的等值查询，以下语句可能导致该级别的出现：

# 使用唯一辅助索引或聚集索引(PRIMARY KEY)进行 = 查询
DESC SELECT * FROM userInfo WHERE id = 1;

Key_len

​ key_len是一个值得注意关注的点，对于联合索引来说它应该越长越好。

​ 而对于单列索引来说它应该越短越好。

​ 不同的数据类型会造成不同的key_len，比如CHAR和VARCHAR类型：

ch = CHAR(4)
vc = VARCHAR(4)

# 使用执行计划可以看见ch索引长度key_len是16个字节。utf8mb4下4*4=16
M > DESC SELECT ch FROM temp WHERE ch="";
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+

+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | temp  | NULL       | ref  | cidx          | cidx | 16      | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+

# 使用执行计划可以看见vc的key_len是18个字节。这是因为除了utf8mb4下4*4=16之外，还需要加上开始和结束位置，占2字节
M > DESC SELECT vc FROM temp WHERE vc="";
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+

+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | temp  | NULL       | ref  | vidx          | vidx | 18      | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+

​ 所以这也是为什么推荐建表时使用CHAR类型而不是VARCHAR类型。

Extra

​ Extra中可能出现的提示信息：

Using filesort

​ 当出现该提示信息后，说明在查询中有关排序的条件列没有合理的应用索引，检测语句中下列地方并加以改进：

ORDER BY
GROUP BY
DISTINCT()
UNION