クエリパフォーマンス分析-非公式MySQL 8.0最適化ガイド-学習ノート

5904 ワード

EXPLAINは、クエリの実行に関するより多くの統計情報を提供することなく、クエリのコストの予見のみを示し、これらの情報はより完全なシナリオを示すことができる.オプティマイザはインデックスに基づいてすべてのローを評価することはできません(EXPLAINの結果にattached_conditionが追加されて接触するローが示されています).どのローが評価される必要があるか分かりません.テーブル・クエリーを連結する場合、テーブルから多くの検索が存在するか少ないため、ローを評価しないことは「微細効果」のようなものであり、遅い評価に配慮せず、全体的な評価パフォーマンスを向上させます.
MySQLは、古いSHOW PROFILESコマンドの代わりに、クエリを実行するたびに時間の初期分析をperformance_schemaでサポートします.
例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の事前実行情報の補完である.
原文:Profiling Queries-The Unofficial MySQL 8.0 Optimizer Guide

c++実装優先キュー

チェーンテーブルベースの両端キューのクラステンプレートのC++実装