执行计划分析

执行计划的作用

将优化器选择的执行计划截取出来，便于管理员判断语句的执行效率

获取执行计划

mysql> DESC SELECT * FROM user_gender WHERE user_gender = 1;
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | user_gender | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 997566 |    10.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

分析执行计划

• table

• type

  • 全表扫描

    • ALL （不走索引）

  • 索引扫描

    • INDEX 全索引扫描（遍历整个索引树）

      • DESC SELECT ID FROM city;

    • RANGE 索引范围扫描（ > >= < <= , BETWEEN AND , OR , IN , LIKE 说明：受BTREE索引双向指针的优化，> >= < <= LIKE 的效率高于 BETWEEN AND , OR , IN ）

      • DESC SELECT ID FROM city WHERE ID > 1000;

    • REF 辅助索引等值查询

      • DESC SELECT * FROM city WHERE CountryCode = 'CHN';

    • EQ_REF （多表连接时，子表使用主键或唯一键作为连接条件）

      • DESC SELECT city.Name, country.Code, city.Population FROM city JOIN country ON city.CountryCode = country.Code WHERE city.Population < 100\G;

    • CONST / SYSTEM 主键或唯一键的等值查询

      • DESC SELECT * FROM city WHERE ID = 100;

    • NULL

• possible_keys 可能用到的索引

• key 真正选择的索引

• key_len 索引覆盖长度（覆盖的是最大预留长度）

  • varchar(2) 在使用 utf8mb4 编码时

    • 能存储2个 任意字符

    • 无论存储的是中文英文数字符号，一个字符的都预留4个字节

    • 在实际存储中一个中文占4个字节，一个英文字母或数字占1个字节

    • 索引的覆盖长度是 2 x 4 + 1 + 2 = 11字节，其中后边的1为是否为空标记 ，2为变长字符首尾标记，可使用 LENGTH() 函数查看列所占实际字节数

• Extra

实战分析

没有索引的情况下的查询结果：

mysql> SELECT * FROM user_gender WHERE user_gender = 1;
+---------+-----------------+-------------+
| user_id | user_name       | user_gender |
+---------+-----------------+-------------+
|  110000 | student_100000  |           1 |
|  210000 | student_200000  |           1 |
|  310000 | student_300000  |           1 |
|  410000 | student_400000  |           1 |
|  510000 | student_500000  |           1 |
|  610000 | student_600000  |           1 |
|  710000 | student_700000  |           1 |
|  810000 | student_800000  |           1 |
|  910000 | student_900000  |           1 |
| 1010000 | student_1000000 |           1 |
+---------+-----------------+-------------+
10 rows in set (1.32 sec)

mysql> DESC SELECT * FROM user_gender WHERE user_gender = 1;
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | user_gender | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 997566 |    10.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

# type: ALL
# 走全表查询

有索引的情况下的查询结果：

mysql> SELECT * FROM user_gender WHERE user_gender = 1;
+---------+-----------------+-------------+
| user_id | user_name       | user_gender |
+---------+-----------------+-------------+
|  110000 | student_100000  |           1 |
|  210000 | student_200000  |           1 |
|  310000 | student_300000  |           1 |
|  410000 | student_400000  |           1 |
|  510000 | student_500000  |           1 |
|  610000 | student_600000  |           1 |
|  710000 | student_700000  |           1 |
|  810000 | student_800000  |           1 |
|  910000 | student_900000  |           1 |
| 1010000 | student_1000000 |           1 |
+---------+-----------------+-------------+
10 rows in set (0.00 sec)

mysql> DESC SELECT * FROM user_gender WHERE user_gender = 1;
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user_gender | NULL       | ref  | IDX_USER_GENDER | IDX_USER_GENDER | 1       | const |   10 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

# type: ref
# 走辅助索引等值查询