mysql8查询性能_查询性能分析-⾮官⽅MySQL8.0优化指南-学 习笔记
EXPLAIN只展⽰了查询代价的预见，不提供有关执⾏查询的更多统计信息，⽽这些信息可以展⽰更完整的景象。优化器不能根据索引来评估所有⾏(在 EXPLAIN 结果中添加了 attached_condition 表明要接触的⾏)，它不知道有多少⾏需要被评估。在连表查询时，因为从表可能存在很多或很少的查，不去评估⾏就像是⼀种 “涓滴效应”，不照顾可能较慢的评估，带来更好的整体评估性能。 MySQL ⽀持通过performance_schema在每次执⾏查询时对时间花费初步分析，这可以替代已过时的 SHOW PROFILES 指令。
例⼦34：通过系统性能表，分析查询CC数据
able_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 |
led天花灯说明书
| 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 |
电视机高频头
筋膜放进B里面| 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_: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 预执⾏信息的⼀种补全。
豫公网安备41160202000603
站长QQ:729038198
关于我们
投诉建议