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 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 列上的索引来查找订单，这个查问应用了索引优化的办法，能够更高效地执行。
MySQL profiling
# 查看是否开启了 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 数据的条数
个别 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;本文由 mdnice 多平台公布