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 进行查询优化的例子:

EXPLAIN SELECT *FROM usersJOIN orders ON users.id = orders.user_idWHERE users.age > 18ORDER BY orders.created_at DESCLIMIT 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

# 查看是否开启了 profilingshow variables like '%profiling%';# 开启 profilingset profiling = 1;# 执行查询select * from big_tables where id >= (    select id from big_tables limit 10000000, 1) limit 0, 1;# 查看所有查询的性能数据show profiles;# 查看某条查询的详细性能数据show profile for query 1;# 查看 cpu, io, memory, block io 等性能数据show profile cpu, io, memory, block io for query 1;# 关闭 profilingset profiling = 0;

使用示例:

mysql> # 查看所有查询的性能数据show profiles;+----------+------------+---------------------------------------------------------------------------------------------------+| Query_ID | Duration   | Query                                                                                             |+----------+------------+---------------------------------------------------------------------------------------------------+|        1 | 0.00568250 | show variables like '%profiling%'                                                                 ||        2 | 1.41488150 | select * from big_tables where id >= (    select id from big_tables limit 10000000, 1) limit 0, 1 ||        3 | 0.00040300 | purge profiles                                                                                    ||        4 | 0.00016575 | # 清理所有profiling 数据FLUSH STATEMENT ANALYSIS                                                  ||        5 | 0.00014875 | FLUSH STATEMENT ANALYSIS                                                                          ||        6 | 1.41070725 | select * from big_tables where id >= (    select id from big_tables limit 10000000, 1) limit 0, 1 |+----------+------------+---------------------------------------------------------------------------------------------------+6 rows in set (0.10 sec)mysql> # 查看某条查询的详细性能数据show profile for query 6;+--------------------------------+----------+| Status                         | Duration |+--------------------------------+----------+| starting                       | 0.000098 || Executing hook on transaction  | 0.000034 || starting                       | 0.000030 || checking permissions           | 0.000009 || checking permissions           | 0.000005 || Opening tables                 | 0.000059 || init                           | 0.000027 || System lock                    | 0.000015 || optimizing                     | 0.000010 || statistics                     | 0.000024 || optimizing                     | 0.000004 || statistics                     | 0.000008 || preparing                      | 0.000016 || executing                      | 1.410089 || preparing                      | 0.000041 || executing                      | 0.000037 || end                            | 0.000006 || query end                      | 0.000042 || waiting for handler commit     | 0.000016 || closing tables                 | 0.000014 || freeing items                  | 0.000110 || cleaning up                    | 0.000019 |+--------------------------------+----------+mysql> # 查看 cpu, io, memory, block io 等性能数据show profile cpu, block io for query 6;+--------------------------------+----------+----------+------------+--------------+---------------+| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |+--------------------------------+----------+----------+------------+--------------+---------------+| starting                       | 0.000098 | 0.000072 | 0.000025   |            0 |             0 || Executing hook on transaction  | 0.000034 | 0.000026 | 0.000009   |            0 |             0 || starting                       | 0.000030 | 0.000022 | 0.000007   |            0 |             0 || checking permissions           | 0.000009 | 0.000006 | 0.000002   |            0 |             0 || checking permissions           | 0.000005 | 0.000004 | 0.000002   |            0 |             0 || Opening tables                 | 0.000059 | 0.000044 | 0.000015   |            0 |             0 || init                           | 0.000027 | 0.000020 | 0.000007   |            0 |             0 || System lock                    | 0.000015 | 0.000010 | 0.000003   |            0 |             0 || optimizing                     | 0.000010 | 0.000008 | 0.000003   |            0 |             0 || statistics                     | 0.000024 | 0.000018 | 0.000006   |            0 |             0 || optimizing                     | 0.000004 | 0.000002 | 0.000001   |            0 |             0 || statistics                     | 0.000008 | 0.000006 | 0.000002   |            0 |             0 || preparing                      | 0.000016 | 0.000012 | 0.000004   |            0 |             0 || executing                      | 1.410089 | 1.412984 | 0.000000   |            0 |             0 || preparing                      | 0.000041 | 0.000038 | 0.000000   |            0 |             0 || executing                      | 0.000037 | 0.000037 | 0.000000   |            0 |             0 || end                            | 0.000006 | 0.000005 | 0.000000   |            0 |             0 || query end                      | 0.000042 | 0.000042 | 0.000000   |            0 |             0 || waiting for handler commit     | 0.000016 | 0.000016 | 0.000000   |            0 |             0 || closing tables                 | 0.000014 | 0.000014 | 0.000000   |            0 |             0 || freeing items                  | 0.000110 | 0.000109 | 0.000000   |            0 |             0 || cleaning up                    | 0.000019 | 0.000019 | 0.000000   |            0 |             0 |+--------------------------------+----------+----------+------------+--------------+---------------+22 rows in set (0.17 sec)

拓展: profiling 数据的条数

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

mysql> show variables like '%profiling%';+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| have_profiling         | YES   || profiling              | ON    || profiling_history_size | 15    |+------------------------+-------+3 rows in set (0.10 sec)mysql> set global profiling_history_size=20;