发表更新MySQL系列 / MySQL优化8 分钟读完 (大约1190个字)

MySQL系列(26)： EXPLAIN,Schema,索引,慢查询,优化器

数据库的性能取决于许多因素，主要是查询，schema，配置项，硬件。

  • 检查查询计划：EXPLAIN FORMAT
  • 分析查询执行：EXPLAIN ANALYZE
  • 了解计划选择：OPTIMIZER TRACE

执行计划

EXPLAIN

MySQL 执行查询的方式是影响数据库性能的主要因素之一。可以使用 Explain命令来验证MySQL的执行计划。

从 MySQL 5.7.2 开始，可以使用 EXPLAIN 命令来检查当前在其他会话中执行的查询。

可以使用 EXPLAIN FORMAT = JSON 命令来输出详细信息。

EXPLAIN 执行计划提供了关于 查询优化器 如何执行查询的信息，只需将 EXPLAIN 关键字作为 SQL 语句的前缀执行即可。详细可参考官方文档 8.8.2 EXPLAIN Output Format。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> EXPLAIN SELECT
 * 
FROM
 film f
 JOIN film_actor fa ON fa.film_id = f.film_id
 JOIN actor a ON fa.actor_id = a.actor_id 
WHERE
 a.first_name = 'BETTE';
+----+-------------+-------+------------+--------+------------------------+---------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys          | key     | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+------------------------+---------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | ALL    | PRIMARY                | NULL    | NULL    | NULL              |  200 |    10.00 | Using where |
|  1 | SIMPLE      | fa    | NULL       | ref    | PRIMARY,idx_fk_film_id | PRIMARY | 2       | sakila.a.actor_id |   27 |   100.00 | NULL        |
|  1 | SIMPLE      | f     | NULL       | eq_ref | PRIMARY                | PRIMARY | 2       | sakila.fa.film_id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+------------------------+---------+---------+-------------------+------+----------+-------------+
3 rows in set (0.08 sec)

EXPLAIN FORMAT

EXPLAIN FORMAT=JSON

以 JSON 格式输出 EXPLAIN 计划，能提供有关查询执行情况的完整信息：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
mysql> EXPLAIN FORMAT=JSON SELECT 
* 
FROM 
 film f
 JOIN film_actor fa ON fa.film_id = f.film_id
 JOIN actor a ON fa.actor_id = a.actor_id 
WHERE 
 a.first_name = 'BETTE';


{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "696.91"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "a",
          "access_type": "ALL",
          "possible_keys": [
            "PRIMARY"
          ],
          "rows_examined_per_scan": 200,
          "rows_produced_per_join": 20,
          "filtered": "10.00",
          "cost_info": {
            "read_cost": "19.00",
            "eval_cost": "2.00",
            "prefix_cost": "21.00",
            "data_read_per_join": "5K"
          },
          "used_columns": [
            "actor_id",
            "first_name",
            "last_name",
            "last_update",
            "status"
          ],
          "attached_condition": "(`sakila`.`a`.`first_name` = 'BETTE')"
        }
      },
      {
        "table": {
          "table_name": "fa",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY",
            "idx_fk_film_id"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "actor_id"
          ],
          "key_length": "2",
          "ref": [
            "sakila.a.actor_id"
          ],
          "rows_examined_per_scan": 27,
          "rows_produced_per_join": 546,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "20.47",
            "eval_cost": "54.62",
            "prefix_cost": "96.09",
            "data_read_per_join": "8K"
          },
          "used_columns": [
            "actor_id",
            "film_id",
            "last_update"
          ]
        }
      },
      {
        "table": {
          "table_name": "f",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "film_id"
          ],
          "key_length": "2",
          "ref": [
            "sakila.fa.film_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 546,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "546.20",
            "eval_cost": "54.62",
            "prefix_cost": "696.91",
            "data_read_per_join": "426K"
          },
          "used_columns": [
            "film_id",
            "title",
            "description",
            "release_year",
            "language_id",
            "original_language_id",
            "rental_duration",
            "rental_rate",
            "length",
            "replacement_cost",
            "rating",
            "special_features",
            "last_update"
          ]
        }
      }
    ]
  }
} 
1 row in set (0.11 sec)

EXPLAIN FORMAT=TREE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> EXPLAIN FORMAT=TREE SELECT 
* 
FROM 
 film f
 JOIN film_actor fa ON fa.film_id = f.film_id
 JOIN actor a ON fa.actor_id = a.actor_id 
WHERE 
 a.first_name = 'BETTE';

| -> Nested loop inner join  (cost=285.11 rows=546)
    -> Nested loop inner join  (cost=93.94 rows=546)
        -> Filter: (a.first_name = 'BETTE')  (cost=20.25 rows=20)
            -> Table scan on a  (cost=20.25 rows=200)
        -> Index lookup on fa using PRIMARY (actor_id=a.actor_id)  (cost=1.09 rows=27)
    -> Single-row index lookup on f using PRIMARY (film_id=fa.film_id)  (cost=0.25 rows=1)
 |
1 row in set (0.06 sec)

EXPLAIN ANALYZE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> EXPLAIN ANALYZE SELECT 
* 
FROM 
 film f
 JOIN film_actor fa ON fa.film_id = f.film_id
 JOIN actor a ON fa.actor_id = a.actor_id 
WHERE 
 a.first_name = 'BETTE';

| -> Nested loop inner join  (cost=285.11 rows=546) (actual time=0.101..0.244 rows=20 loops=1)
    -> Nested loop inner join  (cost=93.94 rows=546) (actual time=0.087..0.164 rows=20 loops=1)
        -> Filter: (a.first_name = 'BETTE')  (cost=20.25 rows=20) (actual time=0.067..0.136 rows=1 loops=1)
            -> Table scan on a  (cost=20.25 rows=200) (actual time=0.061..0.119 rows=200 loops=1)
        -> Index lookup on fa using PRIMARY (actor_id=a.actor_id)  (cost=1.09 rows=27) (actual time=0.019..0.025 rows=20 loops=1)
    -> Single-row index lookup on f using PRIMARY (film_id=fa.film_id)  (cost=0.25 rows=1) (actual time=0.004..0.004 rows=1 loops=20)
 |
    
1 row in set (0.06 sec)

EXPLAIN 会话

可以为正在运行的会话执行 Explain 计划，但要指定会话ID（connection ID）。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 获取当前连接的会话ID
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|            6965 |
+-----------------+
1 row in set (0.00 sec)
-- 会话执行计划只支持 SELECT/UPDATE/INSERT/DELETE/REPLACE
mysql> explain for connection 6965;
ERROR 3012 (HY000): EXPLAIN FOR CONNECTION command is supported only for SELECT/UPDATE/INSERT/DELETE/REPLACE

mysql> explain for connection 6968;
Query OK, 0 rows affected (0.00 sec)
-- JSON格式输出
mysql> explain format=json for connection 6968;
Query OK, 0 rows affected (0.00 sec)

基准查询

EXPLAIN 给出的是 SQL 的执行计划分析结果，并没有明确给出 SQL 的执行耗时。

如果要评估某一条SQL的执行耗时，使用 pager grep rows命令，只返回结果行数和耗时，精确到 100 ms。

1
2
3
4
mysql> pager grep rows;
PAGER set to 'grep rows'
mysql> SELECT  *  FROM  film f  JOIN film_actor fa ON fa.film_id = f.film_id  JOIN actor a ON fa.actor_id = a.actor_id  WHERE  a.first_name = 'BETTE';
20 rows in set (0.00 sec)

模拟负载

可以使用 mysqlslap 模拟客户端负载，并在多个迭代中并行运行某条SQL，可以得出平均耗时。

查看优化后的SQL

查看经过优化器优化后真正执行的 SQL， 在 EXPLAIN 的扩展警告信息(SHOW WARNINGS)中可以明显看到被重写的查询语句。

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 9;
+----+-------------+-------+------------+-------+---------------+-----+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-----+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | gc            | gc  | 5       | NULL |    2 |      100 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----+---------+------+------+----------+-----------------------+

mysql> SHOW WARNINGS;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                       |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `sakila`.`t1`.`f1` AS `f1`,`sakila`.`t1`.`gc` AS `gc` from `sakila`.`t1` where (`sakila`.`t1`.`gc` > 9) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------+

注意：EXPLAIN EXTENDED已弃用。

相关参考

  1. EXPLAIN FORMAT=json和EXPLAIN ANALYZE查询计划解读

MySQL系列(26)： EXPLAIN,Schema,索引,慢查询,优化器

http://blog.gxitsky.com/2021/03/03/MySQL-26-Optimizing-tools/

作者

光星

发布于

2021-03-03

更新于

2023-03-06

许可协议

#

喜欢这篇文章？打赏一下作者吧

评论

关注我
×