MySQL 的 EXPLAIN 是一个用于查询优化的工具,它可以显示 MySQL 数据库如何执行查询。它返回一组关于查询执行计划的信息,包括用到的索引,表的连接顺序以及 MySQL 使用的查询类型。下面是 EXPLAIN 返回的列及其含义:
id:查询中每个 SELECT 子句或者操作的唯一标识符。如果 id 相同,那么这些操作在同一个查询中。
下面是一个使用 EXPLAIN 进行查询优化的例子:
EXPLAIN SELECT * FROM users JOIN orders ON users.id = orders.user_id WHERE users.age > 18 ORDER BY orders.created_at DESC 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 列上的索引来查找订单,这个查询使用了索引优化的方法,可以更高效地执行。
# 查看是否开启了 profiling show variables like '%profiling%'; # 开启 profiling set 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; # 关闭 profiling set 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 只保留最近 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;