查询性能分析 - 非官方 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(<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(<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=<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 预执行信息的一种补全。

译自：
Profiling Queries - The Unofficial MySQL 8.0 Optimizer Guide