mysql8 查询性能_查询性能分析 - 非官方 MySQL 8.0 优化指南 - 学习笔记

EXPLAIN只展示了查询代价的预见，不提供有关执行查询的更多统计信息，而这些信息可以展示更完整的景象。优化器不能根据索引来评估所有行(在 EXPLAIN 结果中添加了 attached_condition 表明要接触的行)，它不知道有多少行需要被评估。在连表查询时，因为从表可能存在很多或很少的查找，不去评估行就像是一种 “涓滴效应”，不照顾可能较慢的评估，带来更好的整体评估性能。

MySQL 支持通过performance_schema在每次执行查询时对时间花费初步分析，这可以替代已过时的 SHOW PROFILES 指令。

例子34：通过系统性能表，分析查询

CALL sys.enable_profiling();

CALL sys.show_profiles;

*************************** 1. row ***************************

Event_ID: 22

Duration: 495.02 us

Query: SELECT * FROM Country WHERE co ... Asia' and population > 5000000

1 row in set (0.00 sec)

CALL sys.show_profile_for_event_id(22);

+----------------------+-----------+

| Status | Duration |

+----------------------+-----------+

| starting | 64.82 us |

| checking permissions | 4.10 us |

| Opening tables | 11.87 us |

| init | 29.74 us |

| System lock | 5.63 us |

| optimizing | 8.74 us |

| statistics | 139.38 us |

| preparing | 11.94 us |

| executing | 348.00 ns |

| Sending data | 192.59 us |

| end | 1.17 us |

| query end | 4.60 us |

| closing tables | 4.07 us |

| freeing items | 13.60 us |

| cleaning up | 734.00 ns |

+----------------------+-----------+

15 rows in set (0.00 sec)

以上时间都是很小的数字，但可以使用SLEEP()方法去找到值得考量的步骤花的时间。在下面的查询中，MySQL 在找到匹配的行时睡眠 5 秒：

SELECT * FROM Country WHERE Continent='Antarctica' and SLEEP(5);

CALL sys.show_profiles();

CALL sys.show_profile_for_event_id();

+----------------------+-----------+

| Status | Duration |

+----------------------+-----------+

| starting | 103.89 us |

| checking permissions | 4.48 us |

| Opening tables | 17.78 us |

| init | 45.75 us |

| System lock | 8.37 us |

| optimizing | 11.98 us |

| statistics | 144.78 us |

| preparing | 15.78 us |

| executing | 634.00 ns |

| Sending data | 116.15 us |

| User sleep | 5.00 s | # 对于每个匹配行，在这一步都睡眠了5s

| User sleep | 5.00 s | # -

| User sleep | 5.00 s | # -

| User sleep | 5.00 s | # -

| User sleep | 5.00 s | # -

| end | 2.05 us |

| query end | 5.63 us |

| closing tables | 7.30 us |

| freeing items | 20.19 us |

| cleaning up | 1.20 us |

+----------------------+-----------+

你可能发现输出的性能分析并不每次都是细粒度展示各个过程的。例如，Sending data这一步就意味着从储存引擎传输行到服务端。重要的临时表和排序的执行时间就没有出现。

SELECT region, count(*) as c FROM Country GROUP BY region;

CALL sys.show_profiles();

CALL sys.show_profile_for_event_id();

+----------------------+-----------+

| Status | Duration |

+----------------------+-----------+

| starting | 87.43 us |

| checking permissions | 4.93 us |

| Opening tables | 17.35 us |

| init | 25.81 us |

| System lock | 9.04 us |

| optimizing | 3.37 us |

| statistics | 18.31 us |

| preparing | 10.94 us |

| Creating tmp table | 35.57 us | # 创建临时表

| Sorting result | 2.38 us | # 排序

| executing | 741.00 ns |

| Sending data | 446.03 us | # 传输数据到服务端

| Creating sort index | 49.45 us | # 创建排序索引

| end | 1.71 us |

| query end | 4.85 us |

| removing tmp table | 4.71 us |

| closing tables | 6.12 us |

| freeing items | 17.17 us |

| cleaning up | 1.00 us |

+----------------------+-----------+

出了通过分析信息展示出来，performance_schema还提供了额外的信息表明需要排序的、发送的行数。

SELECT * FROM performance_schema.events_statements_history_long

WHERE event_id=

*************************** 1. row ***************************

THREAD_ID: 3062

EVENT_ID: 1566

END_EVENT_ID: 1585

EVENT_NAME: statement/sql/select

SOURCE: init_net_server_extension.cc:80

TIMER_START: 588883869566277000

TIMER_END: 588883870317683000

TIMER_WAIT: 751406000

LOCK_TIME: 132000000

SQL_TEXT: SELECT region, count(*) as c FROM Country GROUP BY region

DIGEST: d3a04b346fe48da4f1f5c2e06628a245

DIGEST_TEXT: SELECT `region` , COUNT ( * ) AS `c` FROM `Country` GROUP BY `region`

CURRENT_SCHEMA: world

OBJECT_TYPE: NULL

OBJECT_SCHEMA: NULL

OBJECT_NAME: NULL

OBJECT_INSTANCE_BEGIN: NULL

MYSQL_ERRNO: 0

RETURNED_SQLSTATE: NULL

MESSAGE_TEXT: NULL

ERRORS: 0

WARNINGS: 0

ROWS_AFFECTED: 0

ROWS_SENT: 25 # 发送行数

ROWS_EXAMINED: 289 # 访问过的行数

CREATED_TMP_DISK_TABLES: 0

CREATED_TMP_TABLES: 1

SELECT_FULL_JOIN: 0

SELECT_FULL_RANGE_JOIN: 0

SELECT_RANGE: 0

SELECT_RANGE_CHECK: 0

SELECT_SCAN: 1

SORT_MERGE_PASSES: 0

SORT_RANGE: 0

SORT_ROWS: 25 # 排序行数

SORT_SCAN: 1

NO_INDEX_USED: 1

NO_GOOD_INDEX_USED: 0

NESTING_EVENT_ID: NULL

NESTING_EVENT_TYPE: NULL

NESTING_EVENT_LEVEL: 0

执行层的分析，是对 EXPLAIN 预执行信息的一种补全。