MySQL explain 和 profiling 详解

孟凡胜

MySQL explain 和 profiling 详解

mysql explain

MySQL 的 EXPLAIN 是一个用于查询优化的工具，它可以显示 MySQL 数据库如何执行查询。它返回一组关于查询执行计划的信息，包括用到的索引，表的连接顺序以及 MySQL 使用的查询类型。下面是 EXPLAIN 返回的列及其含义：
id

id：查询中每个 SELECT 子句或者操作的唯一标识符。如果 id 相同，那么这些操作在同一个查询中。
select_type

• select_type：查询的类型，有以下几种类型：
  
  
  
  • SIMPLE：简单 SELECT 查询，不使用 UNION 或子查询等。
    
  • PRIMARY：最外层的查询，即包含了子查询的查询。
    
  • UNION：UNION 查询的第二个或后续查询语句，不包括第一个查询语句。
    
  • DEPENDENT UNION：UNION 查询中的第二个或后续查询语句，依赖于外部查询的结果。
    
  • UNION RESULT：UNION 的结果集。
    
  • SUBQUERY：子查询中的第一个 SELECT 语句，结果用于外部查询。
    
  • DEPENDENT SUBQUERY：子查询中的第一个 SELECT 语句，依赖于外部查询的结果。
    
  • DERIVED：派生表的 SELECT，MySQL 会将其存储在临时表中。
    
  • MATERIALIZED：派生表的 SELECT，MySQL 会将其存储在临时表中。
    
  • UNCACHEABLE SUBQUERY：子查询不可缓存。
    
  
  
• table：显示查询的表名。
  
• partitions：匹配到查询的分区列表。
  
• type：表访问的类型，性能从好到坏依次是：
  
  
  
  • system：仅有一行记录的表。
    
  • const：基于索引进行的等值查询。
    
  • eq_ref：对于每个查询，使用了索引查找符合条件的一行。
    
  • ref：非唯一性索引查找，返回匹配某个单独值的所有行。
    
  • range：使用索引查找一定范围内的行。
    
  • index：使用索引扫描全表，一般用于ORDER BY和GROUP BY操作。
    
  • all：全表扫描。
    
  
  
• possible_keys：可能使用的索引列表。
  
• key：实际使用的索引名称。
  
• key_len：使用索引的长度。
  
• ref：显示索引的哪一列或常量与表列进行比较。
  
• rows：估算的行数。
  
• filtered：过滤器过滤的行数百分比。
  
• Extra：关于 MySQL 如何解析查询的额外信息，包括以下信息：
  
  
  
  • Using index：表示查询中使用了覆盖索引。
    
  • Using where：表示 MySQL 使用了 WHERE 子句来过滤数据。
    
  • Using temporary：表示 MySQL 使用了临时表来存储结果集，通常是 GROUP BY 和 ORDER BY 操作的结果。
    
  • Using filesort：表示 MySQL 使用了文件排序来排序结果集。
    
  • Using join buffer：表示
    
  • Using join buffer：表示 MySQL 使用了 join buffer 来处理连接操作。
    
  • Using sort_union：表示 MySQL 使用了 UNION 查询中的排序优化。
    
  • Using intersect：表示 MySQL 使用了 UNION 查询中的交集优化。
    
  • Using index condition：表示 MySQL 使用了索引中的条件进行过滤，这意味着 MySQL 能够在索引中解决查询条件，而无需查找表中的行。
    
  • Using where with pushed condition：表示 MySQL 将过滤条件下推到了联接前的表中，这可能会更有效地利用索引。
    
  • Using index for group-by：表示 MySQL 使用了索引来处理 GROUP BY 操作，这通常是一种优化。
    
  • Using filesort for group-by：表示 MySQL 使用了文件排序来处理 GROUP BY 操作，这通常是不太理想的。
    
  • Using temporary for filesort：表示 MySQL 使用了临时表来处理 ORDER BY 或 GROUP BY 操作的结果。
    
  • Using filesort for order by：表示 MySQL 使用了文件排序来处理 ORDER BY 操作，这通常是不太理想的。
    
  
  

下面是一个使用 EXPLAIN 进行查询优化的例子：

1. EXPLAIN SELECT *
2. FROM users
3. JOIN orders ON users.id = orders.user_id
4. WHERE users.age > 18
5. ORDER BY orders.created_at DESC
6. LIMIT 10;

复制代码

执行上面的语句后，会返回类似如下的输出：
sql
Copy code
+----+-------------+--------+------------+--------+---------------------+---------+---------+------------------------+------+----------+--------------------------+
| id | select_type | table  | partitions |  type  |    possible_keys    |   key   | key_len |          ref           | rows | filtered |          Extra           |
+----+-------------+--------+------------+--------+---------------------+---------+---------+------------------------+------+----------+--------------------------+
|  1 | SIMPLE      | users  | NULL       | range  | PRIMARY,age         | age     | 4       | NULL                   |    4 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | orders | NULL       | ref    | user_id,created_at  | user_id | 4       | test_db.users.id       |  199 |   100.00 | Using index              |
+----+-------------+--------+------------+--------+---------------------+---------+---------+------------------------+------+----------+--------------------------+
这个查询通过 JOIN 将用户表和订单表连接起来，只返回年龄大于 18 岁的用户的前 10 个订单，按照订单创建时间倒序排序。通过分析输出结果，我们可以看到 MySQL 使用了用户表的 age 列上的索引来过滤数据，然后使用了订单表的 user_id 列上的索引来查找订单，这个查询使用了索引优化的方法，可以更高效地执行。
MySQL profiling

1. # 查看是否开启了 profiling
2. show variables like '%profiling%';
3. # 开启 profiling
4. set profiling = 1;
5. # 执行查询
7. select * from big_tables where id >= (
8.     select id from big_tables limit 10000000, 1
9. ) limit 0, 1;
11. # 查看所有查询的性能数据
12. show profiles;
13. # 查看某条查询的详细性能数据
14. show profile for query 1;
15. # 查看 cpu, io, memory, block io 等性能数据
16. show profile cpu, io, memory, block io for query 1;
18. # 关闭 profiling
19. set profiling = 0;

复制代码

使用示例:

1. mysql> # 查看所有查询的性能数据
2. show profiles;
3. +----------+------------+---------------------------------------------------------------------------------------------------+
4. | Query_ID | Duration   | Query                                                                                             |
5. +----------+------------+---------------------------------------------------------------------------------------------------+
6. |        1 | 0.00568250 | show variables like '%profiling%'                                                                 |
7. |        2 | 1.41488150 | select * from big_tables where id >= (
8.     select id from big_tables limit 10000000, 1
9. ) limit 0, 1 |
10. |        3 | 0.00040300 | purge profiles                                                                                    |
11. |        4 | 0.00016575 | # 清理所有profiling 数据
12. FLUSH STATEMENT ANALYSIS                                                  |
13. |        5 | 0.00014875 | FLUSH STATEMENT ANALYSIS                                                                          |
14. |        6 | 1.41070725 | select * from big_tables where id >= (
15.     select id from big_tables limit 10000000, 1
16. ) limit 0, 1 |
17. +----------+------------+---------------------------------------------------------------------------------------------------+
18. 6 rows in set (0.10 sec)
19. mysql> # 查看某条查询的详细性能数据
20. show profile for query 6;
21. +--------------------------------+----------+
22. | Status                         | Duration |
23. +--------------------------------+----------+
24. | starting                       | 0.000098 |
25. | Executing hook on transaction  | 0.000034 |
26. | starting                       | 0.000030 |
27. | checking permissions           | 0.000009 |
28. | checking permissions           | 0.000005 |
29. | Opening tables                 | 0.000059 |
30. | init                           | 0.000027 |
31. | System lock                    | 0.000015 |
32. | optimizing                     | 0.000010 |
33. | statistics                     | 0.000024 |
34. | optimizing                     | 0.000004 |
35. | statistics                     | 0.000008 |
36. | preparing                      | 0.000016 |
37. | executing                      | 1.410089 |
38. | preparing                      | 0.000041 |
39. | executing                      | 0.000037 |
40. | end                            | 0.000006 |
41. | query end                      | 0.000042 |
42. | waiting for handler commit     | 0.000016 |
43. | closing tables                 | 0.000014 |
44. | freeing items                  | 0.000110 |
45. | cleaning up                    | 0.000019 |
46. +--------------------------------+----------+
47. mysql> # 查看 cpu, io, memory, block io 等性能数据
48. show profile cpu, block io for query 6;
49. +--------------------------------+----------+----------+------------+--------------+---------------+
50. | Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
51. +--------------------------------+----------+----------+------------+--------------+---------------+
52. | starting                       | 0.000098 | 0.000072 | 0.000025   |            0 |             0 |
53. | Executing hook on transaction  | 0.000034 | 0.000026 | 0.000009   |            0 |             0 |
54. | starting                       | 0.000030 | 0.000022 | 0.000007   |            0 |             0 |
55. | checking permissions           | 0.000009 | 0.000006 | 0.000002   |            0 |             0 |
56. | checking permissions           | 0.000005 | 0.000004 | 0.000002   |            0 |             0 |
57. | Opening tables                 | 0.000059 | 0.000044 | 0.000015   |            0 |             0 |
58. | init                           | 0.000027 | 0.000020 | 0.000007   |            0 |             0 |
59. | System lock                    | 0.000015 | 0.000010 | 0.000003   |            0 |             0 |
60. | optimizing                     | 0.000010 | 0.000008 | 0.000003   |            0 |             0 |
61. | statistics                     | 0.000024 | 0.000018 | 0.000006   |            0 |             0 |
62. | optimizing                     | 0.000004 | 0.000002 | 0.000001   |            0 |             0 |
63. | statistics                     | 0.000008 | 0.000006 | 0.000002   |            0 |             0 |
64. | preparing                      | 0.000016 | 0.000012 | 0.000004   |            0 |             0 |
65. | executing                      | 1.410089 | 1.412984 | 0.000000   |            0 |             0 |
66. | preparing                      | 0.000041 | 0.000038 | 0.000000   |            0 |             0 |
67. | executing                      | 0.000037 | 0.000037 | 0.000000   |            0 |             0 |
68. | end                            | 0.000006 | 0.000005 | 0.000000   |            0 |             0 |
69. | query end                      | 0.000042 | 0.000042 | 0.000000   |            0 |             0 |
70. | waiting for handler commit     | 0.000016 | 0.000016 | 0.000000   |            0 |             0 |
71. | closing tables                 | 0.000014 | 0.000014 | 0.000000   |            0 |             0 |
72. | freeing items                  | 0.000110 | 0.000109 | 0.000000   |            0 |             0 |
73. | cleaning up                    | 0.000019 | 0.000019 | 0.000000   |            0 |             0 |
74. +--------------------------------+----------+----------+------------+--------------+---------------+
75. 22 rows in set (0.17 sec)

复制代码

拓展: profiling 数据的条数

一般 profiling 只保留最近 15 条查询的性能数据, 如果需要保留更多的数据, 可以修改 profiling_history_size 变量:

1. mysql> show variables like '%profiling%';
2. +------------------------+-------+
3. | Variable_name          | Value |
4. +------------------------+-------+
5. | have_profiling         | YES   |
6. | profiling              | ON    |
7. | profiling_history_size | 15    |
8. +------------------------+-------+
9. 3 rows in set (0.10 sec)
10. mysql> set global profiling_history_size=20;

复制代码

来源:https://www.cnblogs.com/bmft/p/17290555.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！