More than 5 years have passed since last update.

MySQL 8.0（8.0.3 RC以降）でpt-query-digestっぽいSQL統計情報を大雑把に見てみる

Last updated at 2018-12-07Posted at 2018-04-15

久々に MySQL 8.0 ネタです。
MySQL最新情報セミナー 2017年10月 in 東京 で「MySQL 8.0 では 2つの意味の『Histogram』があって区別が…」という話を聞いたのをすっかり忘れていて、先日 lefred さんの↓の記事が TL に流れてきたときに思い出したので、ちょっと触ってみました。

MySQL 8.0: Statements Latency Histograms

…といっても↑の記事のところまでは到達できずに、1か月前のこちらの記事の範囲で終わってしまいましたが。

MySQL 8.0 : Digest Query Samples in Performance_Schema

2018/4/19・4/30追記：
MySQL 8.0.11 GA がついにリリースされました！
その関係で、リンク先のリファレンスマニュアルの項番が変更になっ~~ていますが、文中ではあえて元のままにしてあります（気が向いたら直すかも…）~~たので直しました。

試してみる前に

MySQL 5.6～5.7 の頃の情報ですが、おなじみ @yoku0825 さんが書かれている記事を先に読んでおいたほうが理解しやすいです。

MySQLのperformance_schemaでどれくらいの情報が見られるのか（日々の覚書）
MySQLのsysスキーマまとめ（日々の覚書）
スローログの集計に便利な「pt-query-digest」を使ってみよう（Think IT）

※私は、一通り触った後に「そういえば『日々の覚書』の記事に情報あったかも」と気付きました…気付くの遅すぎ。

要は、

SQL を正規化（WHERE hoge = 100 AND fuga = 'XYZ'の100・'XYZ'の部分を?で置き換え、空白などをそろえる）
↑をハッシュ化（SHA-256）してDIGESTを生成
同じDIGESTを持つ SQL 毎に処理時間の最短／平均／最長などを集計

というようなものです。

MySQL 8.0 でどう変わったか？

performance_schema.events_statements_summary_by_digestテーブルに、以下の行が増えました。

QUANTILE_95 : SQL 実行レイテンシ 95 パーセンタイル値（単位：ピコ秒）
QUANTILE_99 : SQL 実行レイテンシ 99 パーセンタイル値（単位：ピコ秒）
QUANTILE_999 : SQL 実行レイテンシ 99.9 パーセンタイル値（単位：ピコ秒）
QUERY_SAMPLE_TEXT : SQL サンプル（EXPLAIN実行用）
QUERY_SAMPLE_SEEN : ↑が実行されたタイミング
QUERY_SAMPLE_TIMER_WAIT : ↑の実行レイテンシ（単位：ピコ秒）

あと、FIRST_SEENとLAST_SEENの値が小数点以下6桁（マイクロ秒）まで拡張されています（MySQL 5.7 と比べて）。

SQL のサンプルに関する情報については、実行毎にコロコロ変わってしまうとEXPLAINしづらいかもしれませんので、以下の設定項目があります。

performance_schema_max_digest_sample_age（MySQL 8.0 Reference Manual） ※デフォルト60秒

その他、MySQL 8.0 Reference Manual の以下のページを読んでおくと良いでしょう。

※サーバサイドのプリペアードステートメントについては、別のテーブルに集計されますが、今のところそちらには残念ながら 95 バーセンタイルなどの情報は追加されていないようです。

試してみる

とりあえず、全てデフォルトの設定のままで試してみます。

以下のような、

INSERT10,000 行
主キーでのUPDATE5,000 行
非キーでのUPDATE2,000 行（後でセカンダリキーでのUPDATEに変更）

SQL を実行して、正規化された SQL 毎の統計情報を確認してみます。

digest_test.sql（部分）

TRUNCATE TABLE digest_test.test_db;
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
TRUNCATE TABLE performance_schema.events_statements_history;
SET AUTOCOMMIT=0;
INSERT INTO digest_test.test_db SET val = 95;
INSERT INTO digest_test.test_db SET val = 1375;
INSERT INTO digest_test.test_db SET val = 6076;
（中略）
INSERT INTO digest_test.test_db SET val = 1671;
COMMIT;
UPDATE digest_test.test_db SET val = 391 WHERE id = 3822;
UPDATE digest_test.test_db SET val = 5770 WHERE id = 1576;
UPDATE digest_test.test_db SET val = 9523 WHERE id = 7828;
（中略）
UPDATE digest_test.test_db SET val = 8262 WHERE id = 7446;
COMMIT;
UPDATE digest_test.test_db SET val = 2905 WHERE val = 3446;
UPDATE digest_test.test_db SET val = 6602 WHERE val = 8143;
UPDATE digest_test.test_db SET val = 9159 WHERE val = 4193;
（中略）
UPDATE digest_test.test_db SET val = 3256 WHERE val = 9796;
COMMIT;

※最初にTRUNCATE TABLEすることで、performance_schema内の統計情報をクリアしています。

1. テーブル作成（セカンダリキーなし）

テーブル作成

mysql> CREATE DATABASE digest_test;
Query OK, 1 row affected (0.00 sec)

mysql> USE digest_test;
Database changed
mysql> CREATE TABLE test_db (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, val INT UNSIGNED);
Query OK, 0 rows affected (0.01 sec)

2. SQL 実行→結果を見る

SQL実行

$ mysql -u root -p digest_test < digest_test.sql

関係する SQL（INSERTとUPDATE）だけに絞って結果を見てみます。

結果を見る

mysql> SELECT * FROM performance_schema.events_statements_summary_by_digest WHERE DIGEST_TEXT LIKE 'INSERT %' OR DIGEST_TEXT LIKE 'UPDATE %'\G
*************************** 1. row ***************************
                SCHEMA_NAME: digest_test
                     DIGEST: 810c6c33e68ebfc59eb88dcf70559678d333651f0180f6bd50db18c1c36f69ab
                DIGEST_TEXT: INSERT INTO `digest_test` . `test_db` SET `val` = ?
                 COUNT_STAR: 10000
             SUM_TIMER_WAIT: 673609442000
             MIN_TIMER_WAIT: 55357000
             AVG_TIMER_WAIT: 67360000
             MAX_TIMER_WAIT: 1679037000
              SUM_LOCK_TIME: 210607000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 10000
              SUM_ROWS_SENT: 0
          SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2018-04-15 11:31:00.886171
                  LAST_SEEN: 2018-04-15 11:31:01.705296
                QUANTILE_95: 75857757
                QUANTILE_99: 87096358
               QUANTILE_999: 1380384264
          QUERY_SAMPLE_TEXT: INSERT INTO digest_test.test_db SET val = 4870
          QUERY_SAMPLE_SEEN: 2018-04-15 11:31:01.181595
    QUERY_SAMPLE_TIMER_WAIT: 1679037000
*************************** 2. row ***************************
                SCHEMA_NAME: digest_test
                     DIGEST: f19b14bedfea94d9947ee0c83c75ab4ded31b8417bc75410c5079395fe4398fe
                DIGEST_TEXT: UPDATE `digest_test` . `test_db` SET `val` = ? WHERE `id` = ?
                 COUNT_STAR: 5000
             SUM_TIMER_WAIT: 463418319000
             MIN_TIMER_WAIT: 69071000
             AVG_TIMER_WAIT: 92683000
             MAX_TIMER_WAIT: 288838000
              SUM_LOCK_TIME: 136217000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 4999
              SUM_ROWS_SENT: 0
          SUM_ROWS_EXAMINED: 5000
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2018-04-15 11:31:01.715294
                  LAST_SEEN: 2018-04-15 11:31:02.246220
                QUANTILE_95: 104712854
                QUANTILE_99: 125892541
               QUANTILE_999: 158489319
          QUERY_SAMPLE_TEXT: UPDATE digest_test.test_db SET val = 391 WHERE id = 3822
          QUERY_SAMPLE_SEEN: 2018-04-15 11:31:01.715294
    QUERY_SAMPLE_TIMER_WAIT: 288838000
*************************** 3. row ***************************
                SCHEMA_NAME: digest_test
                     DIGEST: 39ee5b41b81eb0177ddd2cab3c87ff0744d9d7c69d4243feecc00adb112a883c
                DIGEST_TEXT: UPDATE `digest_test` . `test_db` SET `val` = ? WHERE `val` = ?
                 COUNT_STAR: 2000
             SUM_TIMER_WAIT: 9873219195000
             MIN_TIMER_WAIT: 4755461000
             AVG_TIMER_WAIT: 4936609000
             MAX_TIMER_WAIT: 5326378000
              SUM_LOCK_TIME: 60237000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 1961
              SUM_ROWS_SENT: 0
          SUM_ROWS_EXAMINED: 20000000
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2018-04-15 11:31:02.261459
                  LAST_SEEN: 2018-04-15 11:31:12.221192
                QUANTILE_95: 5248074602
                QUANTILE_99: 5248074602
               QUANTILE_999: 5495408738
          QUERY_SAMPLE_TEXT: UPDATE digest_test.test_db SET val = 2367 WHERE val = 4554
          QUERY_SAMPLE_SEEN: 2018-04-15 11:31:10.777409
    QUERY_SAMPLE_TIMER_WAIT: 5326378000
3 rows in set (0.00 sec)

MySQL 8.0 で追加・変更されたカラムもちゃんと表示されています。
MySQL 5.7 時代からあるsys.statement_analysisビューで見るとこうなります。

sys.statement_analysis

mysql> SELECT * FROM sys.statement_analysis WHERE query LIKE 'INSERT %' OR query LIKE 'UPDATE %';
+-----------------------------------------------------------------+-------------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+---------------+---------------+-------------------+---------------+-------------------+------------+-----------------+-------------+-------------------+------------------------------------------------------------------+----------------------------+----------------------------+
| query                                                           | db          | full_scan | exec_count | err_count | warn_count | total_latency | max_latency | avg_latency | lock_latency | rows_sent | rows_sent_avg | rows_examined | rows_examined_avg | rows_affected | rows_affected_avg | tmp_tables | tmp_disk_tables | rows_sorted | sort_merge_passes | digest                                                           | first_seen                 | last_seen                  |
+-----------------------------------------------------------------+-------------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+---------------+---------------+-------------------+---------------+-------------------+------------+-----------------+-------------+-------------------+------------------------------------------------------------------+----------------------------+----------------------------+
| UPDATE `digest_test` . `test_db` SET `val` = ? WHERE `val` = ?  | digest_test |           |       2000 |         0 |          0 | 9.87 s        | 5.33 ms     | 4.94 ms     | 60.24 ms     |         0 |             0 |      20000000 |             10000 |          1961 |                 1 |          0 |               0 |           0 |                 0 | 39ee5b41b81eb0177ddd2cab3c87ff0744d9d7c69d4243feecc00adb112a883c | 2018-04-15 11:31:02.261459 | 2018-04-15 11:31:12.221192 |
| INSERT INTO `digest_test` . `test_db` SET `val` = ?             | digest_test |           |      10000 |         0 |          0 | 673.61 ms     | 1.68 ms     | 67.36 us    | 210.61 ms    |         0 |             0 |             0 |                 0 |         10000 |                 1 |          0 |               0 |           0 |                 0 | 810c6c33e68ebfc59eb88dcf70559678d333651f0180f6bd50db18c1c36f69ab | 2018-04-15 11:31:00.886171 | 2018-04-15 11:31:01.705296 |
| UPDATE `digest_test` . `test_db` SET `val` = ? WHERE `id` = ?   | digest_test |           |       5000 |         0 |          0 | 463.42 ms     | 288.84 us   | 92.68 us    | 136.22 ms    |         0 |             0 |          5000 |                 1 |          4999 |                 1 |          0 |               0 |           0 |                 0 | f19b14bedfea94d9947ee0c83c75ab4ded31b8417bc75410c5079395fe4398fe | 2018-04-15 11:31:01.715294 | 2018-04-15 11:31:02.246220 |
+-----------------------------------------------------------------+-------------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+---------------+---------------+-------------------+---------------+-------------------+------------+-----------------+-------------+-------------------+------------------------------------------------------------------+----------------------------+----------------------------+
3 rows in set (0.00 sec)

※トータルで時間が掛かった順（降順）に表示されているので、表示は実行順ではありません。

3. セカンダリキーを付けてテーブル再作成

カラムvalにセカンダリキー（INDEX）を付けます。

テーブル再作成

mysql> DROP TABLE test_db;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE test_db (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, val INT UNSIGNED, INDEX(val));
Query OK, 0 rows affected (0.02 sec)

4. SQL 再実行→結果を見る

SQL再実行

$ mysql -u root -p digest_test < digest_test.sql

結果を見る

mysql> SELECT * FROM performance_schema.events_statements_summary_by_digest WHERE DIGEST_TEXT LIKE 'INSERT %' OR DIGEST_TEXT LIKE 'UPDATE %'\G
*************************** 1. row ***************************
                SCHEMA_NAME: digest_test
                     DIGEST: 810c6c33e68ebfc59eb88dcf70559678d333651f0180f6bd50db18c1c36f69ab
                DIGEST_TEXT: INSERT INTO `digest_test` . `test_db` SET `val` = ?
                 COUNT_STAR: 10000
             SUM_TIMER_WAIT: 746230558000
             MIN_TIMER_WAIT: 59827000
             AVG_TIMER_WAIT: 74623000
             MAX_TIMER_WAIT: 1972931000
              SUM_LOCK_TIME: 209744000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 10000
              SUM_ROWS_SENT: 0
          SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2018-04-15 11:32:25.095310
                  LAST_SEEN: 2018-04-15 11:32:25.981473
                QUANTILE_95: 83176377
                QUANTILE_99: 100000000
               QUANTILE_999: 1513561248
          QUERY_SAMPLE_TEXT: INSERT INTO digest_test.test_db SET val = 6217
          QUERY_SAMPLE_SEEN: 2018-04-15 11:32:25.192608
    QUERY_SAMPLE_TIMER_WAIT: 1972931000
*************************** 2. row ***************************
                SCHEMA_NAME: digest_test
                     DIGEST: f19b14bedfea94d9947ee0c83c75ab4ded31b8417bc75410c5079395fe4398fe
                DIGEST_TEXT: UPDATE `digest_test` . `test_db` SET `val` = ? WHERE `id` = ?
                 COUNT_STAR: 5000
             SUM_TIMER_WAIT: 535014810000
             MIN_TIMER_WAIT: 68229000
             AVG_TIMER_WAIT: 107002000
             MAX_TIMER_WAIT: 1534471000
              SUM_LOCK_TIME: 139503000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 4999
              SUM_ROWS_SENT: 0
          SUM_ROWS_EXAMINED: 5000
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2018-04-15 11:32:25.993579
                  LAST_SEEN: 2018-04-15 11:32:26.599944
                QUANTILE_95: 125892541
                QUANTILE_99: 144543977
               QUANTILE_999: 173780082
          QUERY_SAMPLE_TEXT: UPDATE digest_test.test_db SET val = 5575 WHERE id = 3001
          QUERY_SAMPLE_SEEN: 2018-04-15 11:32:26.122096
    QUERY_SAMPLE_TIMER_WAIT: 1534471000
*************************** 3. row ***************************
                SCHEMA_NAME: digest_test
                     DIGEST: 39ee5b41b81eb0177ddd2cab3c87ff0744d9d7c69d4243feecc00adb112a883c
                DIGEST_TEXT: UPDATE `digest_test` . `test_db` SET `val` = ? WHERE `val` = ?
                 COUNT_STAR: 2000
             SUM_TIMER_WAIT: 232915377000
             MIN_TIMER_WAIT: 71584000
             AVG_TIMER_WAIT: 116457000
             MAX_TIMER_WAIT: 1674336000
              SUM_LOCK_TIME: 56663000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 1961
              SUM_ROWS_SENT: 0
          SUM_ROWS_EXAMINED: 3922
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2018-04-15 11:32:26.607049
                  LAST_SEEN: 2018-04-15 11:32:26.870795
                QUANTILE_95: 181970085
                QUANTILE_99: 208929613
               QUANTILE_999: 275422870
          QUERY_SAMPLE_TEXT: UPDATE digest_test.test_db SET val = 4225 WHERE val = 4057
          QUERY_SAMPLE_SEEN: 2018-04-15 11:32:26.609660
    QUERY_SAMPLE_TIMER_WAIT: 1674336000
3 rows in set (0.00 sec)

mysql> SELECT * FROM sys.statement_analysis WHERE query LIKE 'INSERT %' OR query LIKE 'UPDATE %';
+-----------------------------------------------------------------+-------------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+---------------+---------------+-------------------+---------------+-------------------+------------+-----------------+-------------+-------------------+------------------------------------------------------------------+----------------------------+----------------------------+
| query                                                           | db          | full_scan | exec_count | err_count | warn_count | total_latency | max_latency | avg_latency | lock_latency | rows_sent | rows_sent_avg | rows_examined | rows_examined_avg | rows_affected | rows_affected_avg | tmp_tables | tmp_disk_tables | rows_sorted | sort_merge_passes | digest                                                           | first_seen                 | last_seen                  |
+-----------------------------------------------------------------+-------------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+---------------+---------------+-------------------+---------------+-------------------+------------+-----------------+-------------+-------------------+------------------------------------------------------------------+----------------------------+----------------------------+
| INSERT INTO `digest_test` . `test_db` SET `val` = ?             | digest_test |           |      10000 |         0 |          0 | 746.23 ms     | 1.97 ms     | 74.62 us    | 209.74 ms    |         0 |             0 |             0 |                 0 |         10000 |                 1 |          0 |               0 |           0 |                 0 | 810c6c33e68ebfc59eb88dcf70559678d333651f0180f6bd50db18c1c36f69ab | 2018-04-15 11:32:25.095310 | 2018-04-15 11:32:25.981473 |
| UPDATE `digest_test` . `test_db` SET `val` = ? WHERE `id` = ?   | digest_test |           |       5000 |         0 |          0 | 535.01 ms     | 1.53 ms     | 107.00 us   | 139.50 ms    |         0 |             0 |          5000 |                 1 |          4999 |                 1 |          0 |               0 |           0 |                 0 | f19b14bedfea94d9947ee0c83c75ab4ded31b8417bc75410c5079395fe4398fe | 2018-04-15 11:32:25.993579 | 2018-04-15 11:32:26.599944 |
| UPDATE `digest_test` . `test_db` SET `val` = ? WHERE `val` = ?  | digest_test |           |       2000 |         0 |          0 | 232.92 ms     | 1.67 ms     | 116.46 us   | 56.66 ms     |         0 |             0 |          3922 |                 2 |          1961 |                 1 |          0 |               0 |           0 |                 0 | 39ee5b41b81eb0177ddd2cab3c87ff0744d9d7c69d4243feecc00adb112a883c | 2018-04-15 11:32:26.607049 | 2018-04-15 11:32:26.870795 |
+-----------------------------------------------------------------+-------------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+---------------+---------------+-------------------+---------------+-------------------+------------+-----------------+-------------+-------------------+------------------------------------------------------------------+----------------------------+----------------------------+
3 rows in set (0.00 sec)

最後に実行した 2,000 件の非主キーUPDATEが大幅に高速化していることが分かります。
なお、通常は、sys.statement_analysisビューで確認すべき SQL を見つけてから、performance_schema.events_statements_summary_by_digestテーブルを確認→EXPLAINする流れのほうが良さそうです。

DIGESTで非主キーUPDATEを確認→EXPLAIN

mysql> SELECT * FROM performance_schema.events_statements_summary_by_digest WHERE DIGEST = '39ee5b41b81eb0177ddd2cab3c87ff0744d9d7c69d4243feecc00adb112a883c'\G
*************************** 1. row ***************************
                SCHEMA_NAME: digest_test
                     DIGEST: 39ee5b41b81eb0177ddd2cab3c87ff0744d9d7c69d4243feecc00adb112a883c
                DIGEST_TEXT: UPDATE `digest_test` . `test_db` SET `val` = ? WHERE `val` = ?
                 COUNT_STAR: 2000
             SUM_TIMER_WAIT: 232915377000
             MIN_TIMER_WAIT: 71584000
             AVG_TIMER_WAIT: 116457000
             MAX_TIMER_WAIT: 1674336000
              SUM_LOCK_TIME: 56663000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 1961
              SUM_ROWS_SENT: 0
          SUM_ROWS_EXAMINED: 3922
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2018-04-15 11:32:26.607049
                  LAST_SEEN: 2018-04-15 11:32:26.870795
                QUANTILE_95: 181970085
                QUANTILE_99: 208929613
               QUANTILE_999: 275422870
          QUERY_SAMPLE_TEXT: UPDATE digest_test.test_db SET val = 4225 WHERE val = 4057
          QUERY_SAMPLE_SEEN: 2018-04-15 11:32:26.609660
    QUERY_SAMPLE_TIMER_WAIT: 1674336000
1 row in set (0.00 sec)

mysql> EXPLAIN UPDATE digest_test.test_db SET val = 4225 WHERE val = 4057;
+----+-------------+---------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref   | rows | filtered | Extra                        |
+----+-------------+---------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+
|  1 | UPDATE      | test_db | NULL       | range | val           | val  | 5       | const |    1 |   100.00 | Using where; Using temporary |
+----+-------------+---------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+
1 row in set (0.00 sec)

※Histogram の確認に続~~く…かも？~~きます。

MySQL 8.0でSQL実行時間の分布（ヒストグラム）を確認する

Qiitaに投稿したMySQL 8.0関連記事

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up