GA

2022/06/22

MySQLだけでWindow関数を使って@rowとか使わずに95%ileを計算したい

やり方があってるかどうかわからないので違ってたら教えてほしい。

サンプルデータこんな感じ。


mysql80 209534> SELECT * FROM t1 LIMIT 3;

+---------------------+-----------+
| dt                  | rows_read |
+---------------------+-----------+
| 2022-05-31 17:16:00 |         0 |
| 2022-05-31 17:16:01 |         6 |
| 2022-05-31 17:16:03 |         0 |
+---------------------+-----------+

3 rows in set (0.00 sec)

まずは全期間でrows_readの95%ileを計算してみたい。

パーセンタイルを一発で求めるなにかは無さそうなので、まずはおとなしくRANK()で並べ替える。

mysql80 209534> SELECT dt, rows_read, RANK() OVER (ORDER BY rows_read) AS _rank FROM t1;
+---------------------+-----------+-------+
| dt                  | rows_read | _rank |
+---------------------+-----------+-------+
| 2022-05-31 17:16:00 |         0 |     1 |
| 2022-05-31 17:16:03 |         0 |     1 |
| 2022-05-31 17:16:04 |         0 |     1 |

..
| 2022-05-31 18:26:54 |   1652200 |  5297 |
| 2022-05-31 18:20:12 |   1656600 |  5298 |
| 2022-05-31 18:40:09 |   1668900 |  5299 |
+---------------------+-----------+-------+
5299 rows in set (0.00 sec)

要はこの _rank / 5299 がパーセンタイルになるので割ってやればいいんだけど、5299の部分はもちろん動的に作りたい……が、COUNT取らないといけないので一旦WITH句に逃がす。

mysql80 209534> WITH
    -> _count AS (
    ->   SELECT COUNT(*) AS c FROM t1
    -> ),
    -> _ranked AS (
    ->   SELECT dt, rows_read, RANK() OVER (ORDER BY rows_read) AS _rank FROM t1
    -> )
    -> SELECT dt, rows_read, _rank, c, (_rank / c) * 100 AS percentile
    -> FROM _ranked JOIN _count;

+---------------------+-----------+-------+------+------------+
| dt                  | rows_read | _rank | c    | percentile |
+---------------------+-----------+-------+------+------------+
| 2022-05-31 17:16:00 |         0 |     1 | 5299 |     0.0189 |
| 2022-05-31 17:16:03 |         0 |     1 | 5299 |     0.0189 |
| 2022-05-31 17:16:04 |         0 |     1 | 5299 |     0.0189 |

..
| 2022-05-31 18:26:54 |   1652200 |  5297 | 5299 |    99.9623 |
| 2022-05-31 18:20:12 |   1656600 |  5298 | 5299 |    99.9811 |
| 2022-05-31 18:40:09 |   1668900 |  5299 | 5299 |   100.0000 |
+---------------------+-----------+-------+------+------------+
5299 rows in set (0.01 sec)

この結果セットの WHERE percentile >= 95 ORDER BY percentile ASC LIMIT 1 が95%ile値ってことで合ってるかしらん。

mysql80 209534> WITH
    -> _count AS (
    ->   SELECT COUNT(*) AS c FROM t1
    -> ),
    -> _ranked AS (
    ->   SELECT dt, rows_read, RANK() OVER (ORDER BY rows_read) AS _rank FROM t1
    -> ),
    -> _percentiled AS (
    ->   SELECT dt, rows_read, (_rank / c) * 100 AS percentile
    ->   FROM _ranked JOIN _count
    -> )
    -> SELECT rows_read, percentile
    -> FROM _percentiled
    -> WHERE percentile >= 95
    -> ORDER BY percentile ASC
    -> LIMIT 1;
+-----------+------------+
| rows_read | percentile |
+-----------+------------+
|   1562614 |    95.0179 |
+-----------+------------+
1 row in set (0.01 sec)

とりあえず出てきた。

mysql80 209534> WITH
    -> _count AS (
    ->   SELECT COUNT(*) AS c FROM t1
    -> ),
    -> _ranked AS (
    ->   SELECT dt, rows_read, RANK() OVER (ORDER BY rows_read) AS _rank FROM t1
    -> )
    -> SELECT dt, rows_read, _rank, c, (_rank / c) * 100 AS percentile
    -> FROM _ranked JOIN _count;

..
| 2022-05-31 18:06:07 |   1562400 |  5031 | 5299 |    94.9424 |
| 2022-05-31 18:22:27 |   1562435 |  5032 | 5299 |    94.9613 |
| 2022-05-31 18:25:30 |   1562506 |  5033 | 5299 |    94.9802 |
| 2022-05-31 18:23:04 |   1562587 |  5034 | 5299 |    94.9991 |
| 2022-05-31 18:44:19 |   1562614 |  5035 | 5299 |    95.0179 |   <--- ちゃんとここの行
| 2022-05-31 18:09:39 |   1562900 |  5036 | 5299 |    95.0368 |
| 2022-05-31 18:34:26 |   1562900 |  5036 | 5299 |    95.0368 |
| 2022-05-31 18:44:26 |   1563000 |  5038 | 5299 |    95.0745 |

..

合ってるかなこれで。

さて、更にこれを「dtを1分単位で丸めて、その枠の中の95%ile値」にしたい。

RANK()を取るところまではいける。

mysql80 209534> SELECT DATE_FORMAT(dt, '%Y-%m-%d %H:%i:00') AS _dt, rows_read, RANK() OVER (PARTITION BY DATE_FORMAT(dt, '%Y-%m-%d  %H:%i:00') ORDER BY rows_read) AS _rank FROM t1;
+---------------------+-----------+-------+
| _dt                 | rows_read | _rank |
+---------------------+-----------+-------+
| 2022-05-31 17:16:00 |         0 |     1 |
| 2022-05-31 17:16:00 |         0 |     1 |
| 2022-05-31 17:16:00 |         0 |     1 |

..
| 2022-05-31 18:45:00 |   1563137 |    57 |
| 2022-05-31 18:45:00 |   1573000 |    58 |
| 2022-05-31 18:45:00 |   1598500 |    59 |
| 2022-05-31 18:46:00 |         0 |     1 |
+---------------------+-----------+-------+
5299 rows in set (0.02 sec)

さっきはCOUNTで割る数の5299が得られたけど、今度はパーティションごとにCOUNTの値が違うので MAX(_rank) で代用するのをWITHに備え付ける。

mysql80 209534> WITH
    -> _ranked AS (
    ->   SELECT DATE_FORMAT(dt, '%Y-%m-%d %H:%i:00') AS _dt, rows_read, RANK() OVER (PARTITION BY DATE_FORMAT(dt, '%Y-%m-%d %H:%i:00') ORDER BY rows_read) AS _rank FROM t1
    -> ),
    -> _count_max AS (
    ->   SELECT _dt, MAX(_rank) AS c FROM _ranked GROUP BY _dt
    -> )
    -> SELECT _dt, rows_read, _rank, c, (_rank / c) * 100 AS percentile
    -> FROM _ranked JOIN _count_max USING(_dt);
+---------------------+-----------+-------+------+------------+
| _dt                 | rows_read | _rank | c    | percentile |
+---------------------+-----------+-------+------+------------+
| 2022-05-31 17:16:00 |         0 |     1 |   59 |     1.6949 |
| 2022-05-31 17:16:00 |         0 |     1 |   59 |     1.6949 |
| 2022-05-31 17:16:00 |         0 |     1 |   59 |     1.6949 |

..
| 2022-05-31 18:45:00 |   1563137 |    57 |   59 |    96.6102 |
| 2022-05-31 18:45:00 |   1573000 |    58 |   59 |    98.3051 |
| 2022-05-31 18:45:00 |   1598500 |    59 |   59 |   100.0000 |
| 2022-05-31 18:46:00 |         0 |     1 |    1 |   100.0000 |
+---------------------+-----------+-------+------+------------+
5299 rows in set (0.05 sec)

ここからWHEREと…フレームごとにORDER BY percentile ASC LIMIT 1は難しいので、フレームごとのMIN(rows_read)でいけるかしら。

mysql80 209534> WITH
    -> _ranked AS (
    ->   SELECT DATE_FORMAT(dt, '%Y-%m-%d %H:%i:00') AS _dt, rows_read, RANK() OVER (PARTITION BY DATE_FORMAT(dt, '%Y-%m-%d %H:%i:00') ORDER BY rows_re
ad) AS _rank FROM t1
    -> ),
    -> _count_max AS (
    ->   SELECT _dt, MAX(_rank) AS c FROM _ranked GROUP BY _dt
    -> ),
    -> _percentiled AS (
    ->   SELECT _dt, rows_read, _rank, c, (_rank / c) * 100 AS percentile
    ->   FROM _ranked JOIN _count_max USING(_dt)
    -> )
    -> SELECT _dt, MIN(rows_read)
    -> FROM _percentiled
    -> WHERE percentile >= 95
    -> GROUP BY _dt;
+---------------------+----------------+
| _dt                 | MIN(rows_read) |
+---------------------+----------------+
| 2022-05-31 17:16:00 |           1600 |
| 2022-05-31 17:17:00 |            822 |
| 2022-05-31 17:18:00 |           8022 |
| 2022-05-31 17:19:00 |          79000 |

..
| 2022-05-31 18:43:00 |        1594800 |
| 2022-05-31 18:44:00 |        1578900 |
| 2022-05-31 18:45:00 |        1563137 |
| 2022-05-31 18:46:00 |              0 |
+---------------------+----------------+
91 rows in set (0.05 sec)

18:43近辺の前のクエリの結果で調べてみると

| 2022-05-31 18:43:00 |   1586000 |    52 |   59 |    88.1356 |
| 2022-05-31 18:43:00 |   1588800 |    53 |   59 |    89.8305 |
| 2022-05-31 18:43:00 |   1589332 |    54 |   59 |    91.5254 |
| 2022-05-31 18:43:00 |   1594400 |    55 |   59 |    93.2203 |
| 2022-05-31 18:43:00 |   1594600 |    56 |   59 |    94.9153 |
| 2022-05-31 18:43:00 |   1594800 |    57 |   59 |    96.6102 |
| 2022-05-31 18:43:00 |   1610900 |    58 |   59 |    98.3051 |
| 2022-05-31 18:43:00 |   1615500 |    59 |   59 |   100.0000 |
| 2022-05-31 18:44:00 |         0 |     1 |   58 |     1.7241 |
| 2022-05-31 18:44:00 |         0 |     1 |   58 |     1.7241 |
| 2022-05-31 18:44:00 |         0 |     1 |   58 |     1.7241 |

うん、合ってそう。
合ってますかね?

2022/04/07

MySQL標準のEXPLAINと、実際にテーブルにアクセスする順番と

TL;DR

t3を含んだサブクエリ (SELECT SUBSTRING(val, 1, 1) AS f, SUM(num) FROM t3 GROUP BY f) AS tmp_t3 が最初に処理


まず、テーブルを開く順番。これはメタデータロックの順番でもある。

(全部InnoDBなので) ha_innobase::open にブレークポイントを仕掛けて

$ gdb -p $(pidof mysqld)
(gdb) b ha_innobase::open
+b ha_innobase::open
Breakpoint 1 at 0xc2de7e: ha_innobase::open. (2 locations)
(gdb) c
+c
Continuing.

FLUSH TABLES してテーブルキャッシュを吹っ飛ばしてから実行。
クエリは 前回 のと一緒。

mysql80 8> FLUSH TABLES;
Query OK, 0 rows affected (0.01 sec)

mysql80 8> SELECT * FROM t1, t2, (SELECT SUBSTRING(val, 1, 1) AS f, SUM(num) FROM t3 GROUP BY f) AS tmp_t3 WHERE t1.num IN (SELECT num FROM t5 WHERE num = (SELECT MAX(num) FROM t4) AND val LIKE '%');

起動直後だとデータディクショナリにアクセスするタイミングで1回目のブレークが来るけど取り敢えず次へ。

(gdb) bt
+bt
#0  ha_innobase::open(char const*, int, unsigned int, dd::Table const*) () at /home/yoku0825/mysql-8.0.28/storage/innobase/handler/ha_innodb.cc:6995
#1  0x0000000000ff7775 in handler::ha_open (this=0x7efa9c045a40, table_arg=table_arg@entry=0x7efa9c0450c0, name=0x7efa9c03faf0 "./mysql/check_constraints", mode=mode@entry=2, test_if_locked=2, table_def=0x7f0b4716f7d0)
    at /home/yoku0825/mysql-8.0.28/sql/handler.cc:2812
#2  0x0000000000eac9f2 in open_table_from_share(THD*, TABLE_SHARE*, char const*, unsigned int, unsigned int, unsigned int, TABLE*, bool, dd::Table const*) () at /home/yoku0825/mysql-8.0.28/sql/table.cc:3175
#3  0x0000000000d08c5f in open_table(THD*, TABLE_LIST*, Open_table_context*) () at /home/yoku0825/mysql-8.0.28/sql/sql_base.cc:3367
#4  0x0000000000d0a298 in open_and_process_table (ot_ctx=0x7f0b40128be0, has_prelocking_list=false, prelocking_strategy=0x7f0b40128c70, counter=0x7f0b40128c64, tables=0x7efa9c03e118, lex=<optimized out>, thd=0x7efa9c000d20)
    at /home/yoku0825/mysql-8.0.28/sql/sql_base.cc:5034
#5  open_tables (thd=0x7efa9c000d20, start=start@entry=0x7f0b40128c68, counter=counter@entry=0x7f0b40128c64, flags=flags@entry=18434, prelocking_strategy=prelocking_strategy@entry=0x7f0b40128c70)
    at /home/yoku0825/mysql-8.0.28/sql/sql_base.cc:5842
#6  0x0000000001fb2af0 in open_tables (flags=18434, counter=0x7f0b40128c64, tables=0x7f0b40128c68, thd=<optimized out>) at /home/yoku0825/mysql-8.0.28/sql/sql_base.h:455
#7  dd::Open_dictionary_tables_ctx::open_tables (this=this@entry=0x7f0b40128ce0) at /home/yoku0825/mysql-8.0.28/sql/dd/impl/transaction_impl.cc:107
#8  0x0000000001e2638c in dd::cache::Storage_adapter::get<dd::Item_name_key, dd::Abstract_table> (thd=thd@entry=0x7efa9c000d20, key=
      @0x7f0b40128ee0: {<dd::Object_key> = {_vptr.Object_key = 0x39499c0 <vtable for dd::Item_name_key+16>}, m_container_id_column_no = 1, m_name_column_no = 2, m_container_id = 6, m_object_name = "t1", m_cs = 0x3b80160 <my_charset_utf8_tolower_ci>}, isolation=isolation@entry=ISO_READ_COMMITTED, bypass_core_registry=bypass_core_registry@entry=false, object=object@entry=0x7f0b40128da0) at /home/yoku0825/mysql-8.0.28/sql/dd/impl/transaction_impl.h:90
#9  0x0000000001e1581f in dd::cache::Shared_dictionary_cache::get_uncached<dd::Item_name_key, dd::Abstract_table> (this=this@entry=0x3db4700 <dd::cache::Shared_dictionary_cache::instance()::s_cache>, thd=thd@entry=0x7efa9c000d20,
    key=
      @0x7f0b40128ee0: {<dd::Object_key> = {_vptr.Object_key = 0x39499c0 <vtable for dd::Item_name_key+16>}, m_container_id_column_no = 1, m_name_column_no = 2, m_container_id = 6, m_object_name = "t1", m_cs = 0x3b80160 <my_charset_utf8_tolower_ci>}, isolation=isolation@entry=ISO_READ_COMMITTED, object=object@entry=0x7f0b40128da0) at /home/yoku0825/mysql-8.0.28/sql/dd/impl/cache/shared_dictionary_cache.cc:109
#10 0x0000000001e1589f in dd::cache::Shared_dictionary_cache::get<dd::Item_name_key, dd::Abstract_table> (this=0x3db4700 <dd::cache::Shared_dictionary_cache::instance()::s_cache>, thd=0x7efa9c000d20, key=
      @0x7f0b40128ee0: {<dd::Object_key> = {_vptr.Object_key = 0x39499c0 <vtable for dd::Item_name_key+16>}, m_container_id_column_no = 1, m_name_column_no = 2, m_container_id = 6, m_object_name = "t1", m_cs = 0x3b80160 <my_charset_utf8_tolower_ci>}, element=element@entry=0x7f0b40128e38) at /home/yoku0825/mysql-8.0.28/sql/dd/impl/cache/shared_dictionary_cache.h:174
#11 0x0000000001dbdaa5 in dd::cache::Dictionary_client::acquire<dd::Item_name_key, dd::Abstract_table> (this=this@entry=0x7efa9c0044c0, key=
      @0x7f0b40128ee0: {<dd::Object_key> = {_vptr.Object_key = 0x39499c0 <vtable for dd::Item_name_key+16>}, m_container_id_column_no = 1, m_name_column_no = 2, m_container_id = 6, m_object_name = "t1", m_cs = 0x3b80160 <my_charset_utf8_tolower_ci>}, object=object@entry=0x7f0b40128ec8, local_committed=local_committed@entry=0x7f0b40128ebe, local_uncommitted=local_uncommitted@entry=0x7f0b40128ebf)
    at /opt/rh/devtoolset-10/root/usr/include/c++/10/bits/stl_tree.h:1014
#12 0x0000000001dc21de in dd::cache::Dictionary_client::acquire<dd::Abstract_table> (this=this@entry=0x7efa9c0044c0, schema_name="d2", object_name="t1", object=object@entry=0x7f0b40129040)
    at /home/yoku0825/mysql-8.0.28/sql/dd/impl/cache/dictionary_client.cc:846
#13 0x0000000000d01547 in get_table_share(THD*, char const*, char const*, char const*, unsigned long, bool, bool) () at /opt/rh/devtoolset-10/root/usr/include/c++/10/bits/basic_string.h:3342
#14 0x0000000000d082a7 in get_table_share_with_discover (error=<synthetic pointer>, open_secondary=false, key_length=6, key=<optimized out>, table_list=0x7efa9c01a6f0, thd=0x7efa9c000d20)
    at /home/yoku0825/mysql-8.0.28/sql/sql_base.cc:3202
#15 open_table(THD*, TABLE_LIST*, Open_table_context*) () at /home/yoku0825/mysql-8.0.28/sql/sql_base.cc:3202
#16 0x0000000000d0a298 in open_and_process_table (ot_ctx=0x7f0b401298b0, has_prelocking_list=false, prelocking_strategy=0x7f0b40129958, counter=0x7efa9c003c58, tables=0x7efa9c01a6f0, lex=<optimized out>, thd=0x7efa9c000d20)
    at /home/yoku0825/mysql-8.0.28/sql/sql_base.cc:5034
#17 open_tables (thd=thd@entry=0x7efa9c000d20, start=start@entry=0x7f0b40129948, counter=0x7efa9c003c58, flags=flags@entry=0, prelocking_strategy=prelocking_strategy@entry=0x7f0b40129958)
    at /home/yoku0825/mysql-8.0.28/sql/sql_base.cc:5842
#18 0x0000000000d0b6bd in open_tables_for_query (thd=thd@entry=0x7efa9c000d20, tables=<optimized out>, flags=0) at /home/yoku0825/mysql-8.0.28/sql/sql_base.cc:6722
#19 0x0000000000df387c in Sql_cmd_dml::prepare(THD*) () at /home/yoku0825/mysql-8.0.28/sql/sql_cmd.h:83
#20 0x0000000000df40ee in Sql_cmd_dml::execute(THD*) () at /home/yoku0825/mysql-8.0.28/sql/sql_select.cc:528
#21 0x0000000000d9bd08 in mysql_execute_command(THD*, bool) () at /home/yoku0825/mysql-8.0.28/sql/sql_parse.cc:4544
#22 0x0000000000d9f5d2 in dispatch_sql_command (thd=thd@entry=0x7efa9c000d20, parser_state=parser_state@entry=0x7f0b4012ac20) at /home/yoku0825/mysql-8.0.28/sql/sql_parse.cc:5174
#23 0x0000000000da08de in dispatch_command(THD*, COM_DATA const*, enum_server_command) () at /home/yoku0825/mysql-8.0.28/sql/sql_parse.cc:1938
#24 0x0000000000da29b2 in do_command (thd=thd@entry=0x7efa9c000d20) at /home/yoku0825/mysql-8.0.28/sql/sql_parse.cc:1352
#25 0x0000000000edcb70 in handle_connection (arg=arg@entry=0x7eeb0c0) at /home/yoku0825/mysql-8.0.28/sql/conn_handler/connection_handler_per_thread.cc:302
#26 0x00000000024b0770 in pfs_spawn_thread (arg=0x7e441d0) at /home/yoku0825/mysql-8.0.28/storage/perfschema/pfs.cc:2947
#27 0x00007f0b594d6ea5 in start_thread () from /lib64/libpthread.so.0
#28 0x00007f0b57af0b0d in clone () from /lib64/libc.so.6

(gdb) c
+c
Continuing.

↓のような感じでテーブルオープンのタイミングが見える。

(gdb) bt
+bt
#0  ha_innobase::open(char const*, int, unsigned int, dd::Table const*) () at /home/yoku0825/mysql-8.0.28/storage/innobase/handler/ha_innodb.cc:6995
#1  0x0000000000ff7775 in handler::ha_open (this=0x7efa9ca814e0, table_arg=table_arg@entry=0x7efa9ca8b900, name=0x7efa9c165620 "./d2/t1", mode=mode@entry=2, test_if_locked=2, table_def=0x7efa9c1275d0)
    at /home/yoku0825/mysql-8.0.28/sql/handler.cc:2812
#2  0x0000000000eac9f2 in open_table_from_share(THD*, TABLE_SHARE*, char const*, unsigned int, unsigned int, unsigned int, TABLE*, bool, dd::Table const*) () at /home/yoku0825/mysql-8.0.28/sql/table.cc:3175
#3  0x0000000000d08c5f in open_table(THD*, TABLE_LIST*, Open_table_context*) () at /home/yoku0825/mysql-8.0.28/sql/sql_base.cc:3367
#4  0x0000000000d0a298 in open_and_process_table (ot_ctx=0x7f0b401298b0, has_prelocking_list=false, prelocking_strategy=0x7f0b40129958, counter=0x7efa9c003c58, tables=0x7efa9c01a600, lex=<optimized out>, thd=0x7efa9c000d20)
    at /home/yoku0825/mysql-8.0.28/sql/sql_base.cc:5034
#5  open_tables (thd=thd@entry=0x7efa9c000d20, start=start@entry=0x7f0b40129948, counter=0x7efa9c003c58, flags=flags@entry=0, prelocking_strategy=prelocking_strategy@entry=0x7f0b40129958)
    at /home/yoku0825/mysql-8.0.28/sql/sql_base.cc:5842
#6  0x0000000000d0b6bd in open_tables_for_query (thd=thd@entry=0x7efa9c000d20, tables=<optimized out>, flags=0) at /home/yoku0825/mysql-8.0.28/sql/sql_base.cc:6722
#7  0x0000000000df387c in Sql_cmd_dml::prepare(THD*) () at /home/yoku0825/mysql-8.0.28/sql/sql_cmd.h:83
#8  0x0000000000df40ee in Sql_cmd_dml::execute(THD*) () at /home/yoku0825/mysql-8.0.28/sql/sql_select.cc:528
#9  0x0000000000d9bd08 in mysql_execute_command(THD*, bool) () at /home/yoku0825/mysql-8.0.28/sql/sql_parse.cc:4544
#10 0x0000000000d9f5d2 in dispatch_sql_command (thd=thd@entry=0x7efa9c000d20, parser_state=parser_state@entry=0x7f0b4012ac20) at /home/yoku0825/mysql-8.0.28/sql/sql_parse.cc:5174
#11 0x0000000000da08de in dispatch_command(THD*, COM_DATA const*, enum_server_command) () at /home/yoku0825/mysql-8.0.28/sql/sql_parse.cc:1938
#12 0x0000000000da29b2 in do_command (thd=thd@entry=0x7efa9c000d20) at /home/yoku0825/mysql-8.0.28/sql/sql_parse.cc:1352
#13 0x0000000000edcb70 in handle_connection (arg=arg@entry=0x7eeb0c0) at /home/yoku0825/mysql-8.0.28/sql/conn_handler/connection_handler_per_thread.cc:302
#14 0x00000000024b0770 in pfs_spawn_thread (arg=0x7e441d0) at /home/yoku0825/mysql-8.0.28/storage/perfschema/pfs.cc:2947
#15 0x00007f0b594d6ea5 in start_thread () from /lib64/libpthread.so.0
#16 0x00007f0b57af0b0d in clone () from /lib64/libc.so.6
(gdb) p name
+p name
$8 = 0x7efa9c165620 "./d2/t1"

これを繰り返すと、 t1, t2, t3, t5, t4の順番が得られた。

SELECT
  * 
FROM 
  t1,                      /* 1st */
  t2,                     /* 2nd */
  (
    SELECT 
      SUBSTRING(val, 1, 1) AS f, 
      SUM(num) 
    FROM 
      t3                 /* 3rd */
    GROUP BY f
  ) AS tmp_t3 
WHERE 
  t1.num IN 
    (
      SELECT 
         num 
       FROM 
         t5                     /* 4th */
        WHERE 
          num = 
            (
              SELECT 
                MAX(num) 
              FROM 
                t4               /* 5th */
            ) AND 
          val LIKE '%'
    )
;

これはクエリに出てきた順番に合致している。なるほど。

とはいえテーブルオープンの順番が実際に行にアクセスする順番ではないはずなので、色々探した挙句に件数を減らした(各テーブルレコード1件)の状態で ha_innobase::index_readにブレークポイントを置いてみる。

(gdb) b ha_innobase::index_read
+b ha_innobase::index_read
Breakpoint 1 at 0x20d8425: file /home/yoku0825/mysql-8.0.28/include/my_compiler.h, line 55.

(gdb) c
+c
Continuing.
mysql80 8> SELECT * FROM t1, t2, (SELECT SUBSTRING(val, 1, 1) AS f, SUM(num) FROM t3 GROUP BY f) AS tmp_t3 WHERE t1.num IN (SELECT num FROM t5 WHERE num = (SELECT MAX(num) FROM t4) AND val LIKE '%');
Breakpoint 1, ha_innobase::index_read (this=0x7efa9c0b87b0, buf=0x7efa9c133f50 "", key_ptr=0x0, key_len=0, find_flag=HA_READ_BEFORE_KEY) at /home/yoku0825/mysql-8.0.28/include/my_compiler.h:55
55      constexpr bool unlikely(bool expr) { return __builtin_expect(expr, false); }
(gdb) p this->m_share->table_name
+p this->m_share->table_name
$1 = 0x7efa9ca93f60 "./d2/t4"
(gdb) c
+c
Continuing.

Breakpoint 1, ha_innobase::index_read (this=0x7efa9ca814e0, buf=0x7efa9c00b120 "", key_ptr=0x7efa9c063c90 "\001", key_len=8, find_flag=HA_READ_KEY_EXACT) at /home/yoku0825/mysql-8.0.28/include/my_compiler.h:55
55      constexpr bool unlikely(bool expr) { return __builtin_expect(expr, false); }
(gdb) p this->m_share->table_name
+p this->m_share->table_name
$2 = 0x7efa9ca85be0 "./d2/t1"
(gdb) c
+c
Continuing.

Breakpoint 1, ha_innobase::index_read (this=0x7efa9caa44a0, buf=0x7efa9ca984e0 "", key_ptr=0x7efa9c064498 "\001", key_len=8, find_flag=HA_READ_KEY_EXACT) at /home/yoku0825/mysql-8.0.28/include/my_compiler.h:55
55      constexpr bool unlikely(bool expr) { return __builtin_expect(expr, false); }
(gdb) p this->m_share->table_name
+p this->m_share->table_name
$3 = 0x7efa9ca85850 "./d2/t5"
(gdb) c
+c
Continuing.

Breakpoint 1, ha_innobase::index_read (this=0x7efa9c136c90, buf=0x7efa9c03f740 "\377", key_ptr=0x0, key_len=0, find_flag=HA_READ_AFTER_KEY) at /home/yoku0825/mysql-8.0.28/include/my_compiler.h:55
55      constexpr bool unlikely(bool expr) { return __builtin_expect(expr, false); }
(gdb) p this->m_share->table_name
+p this->m_share->table_name
$4 = 0x7efa9c082ab0 "./d2/t2"
(gdb) c
+c
Continuing.

Breakpoint 1, ha_innobase::index_read (this=0x7efa9ca96e10, buf=0x7efa9ca834f0 "\377", key_ptr=0x0, key_len=0, find_flag=HA_READ_AFTER_KEY) at /home/yoku0825/mysql-8.0.28/include/my_compiler.h:55
55      constexpr bool unlikely(bool expr) { return __builtin_expect(expr, false); }
(gdb) p this->m_share->table_name
+p this->m_share->table_name
$5 = 0x7efa9c05d720 "./d2/t3"
(gdb) c
+c
Continuing.

この手順でやると、 t4, t1, t5, t2, t3 ………えっ、あれ、 t3 最後に読むの?

+----+-------------+------------+------------+-------+---------------+------+---------+-------+------+----------+-------------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key  | key_len | ref   | rows | filtered | Extra                         |
+----+-------------+------------+------------+-------+---------------+------+---------+-------+------+----------+-------------------------------+
|  1 | PRIMARY     | t1         | NULL       | const | num           | num  | 8       | const |    1 |   100.00 | NULL                          |
|  1 | PRIMARY     | t5         | NULL       | const | num           | num  | 8       | const |    1 |   100.00 | NULL                          |
|  1 | PRIMARY     | t2         | NULL       | ALL   | NULL          | NULL | NULL    | NULL  |    1 |   100.00 | NULL                          |
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL | NULL    | NULL  |    2 |   100.00 | Using join buffer (hash join) |
|  4 | SUBQUERY    | NULL       | NULL       | NULL  | NULL          | NULL | NULL    | NULL  | NULL |     NULL | Select tables optimized away  |
|  2 | DERIVED     | t3         | NULL       | ALL   | NULL          | NULL | NULL    | NULL  |    1 |   100.00 | Using temporary               |
+----+-------------+------------+------------+-------+---------------+------+---------+-------+------+----------+-------------------------------+

バージョンを5.5まで遡ったら、ステップ実行しても t3, t4, t1, t5, t2 の順番で読むようになったので、5.6でそういえばそんな最適化が…と思ったらこれだった。

Materialization of subqueries in the FROM clause is postponed until their contents are needed during query execution, which improves performance.

というわけで、 前回 言っていた「t3が一番最初」は誤りでしたorz

2022/03/30

MySQL標準のEXPLAIN(format=TRADITIONAL)とEXPLAIN format=TREEを並べて見つめてみる

いつまで経ってもformat=TREEに慣れないので、メモしつつ勉強してみる。

  • MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.8.2 EXPLAIN ステートメント

    mysql80 8> EXPLAIN SELECT * FROM t1, t2, (SELECT SUBSTRING(val, 1, 1) AS f, SUM(num) FROM t3 GROUP BY f) AS tmp_t3 WHERE t1.num IN (SELECT num FROM t5 WHERE num = (SELECT MAX(num) FROM t4) AND val LIKE '%');
    +----+-------------+------------+------------+-------+---------------+------+---------+-------+--------+----------+-------------------------------+
    | id | select_type | table      | partitions | type  | possible_keys | key  | key_len | ref   | rows   | filtered | Extra                         |
    +----+-------------+------------+------------+-------+---------------+------+---------+-------+--------+----------+-------------------------------+
    |  1 | PRIMARY     | t1         | NULL       | const | num           | num  | 8       | const |      1 |   100.00 | NULL                          |
    |  1 | PRIMARY     | t5         | NULL       | const | num           | num  | 8       | const |      1 |   100.00 | NULL                          |
    |  1 | PRIMARY     | t2         | NULL       | index | NULL          | val  | 131     | NULL  | 982049 |   100.00 | Using index                   |
    |  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL | NULL    | NULL  | 996250 |   100.00 | Using join buffer (hash join) |
    |  4 | SUBQUERY    | NULL       | NULL       | NULL  | NULL          | NULL | NULL    | NULL  |   NULL |     NULL | Select tables optimized away  |
    |  2 | DERIVED     | t3         | NULL       | index | val           | val  | 131     | NULL  | 996250 |   100.00 | Using index; Using temporary  |
    +----+-------------+------------+------------+-------+---------------+------+---------+-------+--------+----------+-------------------------------+
    6 rows in set, 1 warning (0.00 sec)
    

いわゆるフツーのEXPLAIN。

  • t3を含んだサブクエリ (SELECT SUBSTRING(val, 1, 1) AS f, SUM(num) FROM t3 GROUP BY f) AS tmp_t3 が最初に処理
  • SELECT MAX(num) FROM t4 のサブクエリがテーブルアクセス抜き ( Select tables optimized away )で処理(optimized awayが発生するのはMIXまたはMAXなのでテーブル名まで決まり)
  • SUBQUERYが1か所しかないので、 (SELECT num FROM t5 WHERE num = (ここは処理済み) AND val LIKE '%') はJOINに書き換えられている
  • t1から1行フェッチして SELECT num FROM t5 WHERE num = (ここは処理済み) AND val LIKE '%' が1行フェッチされる -> t2が1行フェッチされる -> (SELECT SUBSTRING(val, 1, 1) AS f, SUM(num) FROM t3 GROUP BY f) AS tmp_t3 (これがid:2なので と表示される) が1行フェッチされる
  • ↑のステップがt1の全レコードが終わるまでループ

「idの数字の大小に関係なく、最後の行から解釈する(ただしDEPENDENT SUBQUERYが例外)」
「idが同じものはNested Loop Joinで、idが同じ行のうち先頭の行から解釈する」

あたりで一般化できるか。
OK、これは読める、日本語で説明できた。じゃあ format=TREE。

mysql80 8> EXPLAIN format=TREE SELECT * FROM t1, t2, (SELECT SUBSTRING(val, 1, 1) AS f, SUM(num) FROM t3 GROUP BY f) AS tmp_t3 WHERE t1.num IN (SELECT num FROM t5 WHERE num = (SELECT MAX(num) FROM t4) AND val LIKE '%')\G
*************************** 1. row ***************************
EXPLAIN: 
-> Inner hash join (no condition)  (cost=6556388.62 rows=0)
    -> Table scan on tmp_t3  (cost=2.50..2.50 rows=0)
        -> Materialize  (cost=2.50..2.50 rows=0)
            -> Table scan on <temporary>
                -> Aggregate using temporary table
                    -> Index scan on t3 using val  (cost=103631.25 rows=996250)
    -> Hash
        -> Index scan on t2 using val  (cost=104645.03 rows=982049)

1 row in set (0.00 sec)

全然読めない…が、t3を含んだサブクエリが最初なので、最初だったけどOracle流の読み方としてはここから読むので正しいらしい…(がそうするとますますMySQLのformat=TREEって間違ってるんじゃ…?) Index scan on t3 using val (cost=103631.25 rows=996250) が最初、これはderivedになるので Aggregate using temporary table と Table scan on <temporary> と Materialize が Using temporary の意味か。

Table scan on tmp_t3 はTRADITIONALの table: <derived2>, type: ALL に相当しそう。

あとはもう Inner hash join (no condition) でt1, t5に関する言及は無くてひとまとまり、 Index scan on t2 using val でt2に言及はあるけど、ここから t1, t5, t2, tmp_t3の順だと字面だけで判断するのは俺には無理な気がする…。

(t1, t5を自明なInnser hash joinだと割り切れば、tmp_t3とHashが同じインデント位置にいるから後ろから読めば良いってことなのかな…)

mysql80 18> EXPLAIN ANALYZE SELECT * FROM t1, t2, (SELECT SUBSTRING(val, 1, 1) AS f, SUM(num) FROM t3 GROUP BY f) AS tmp_t3 WHERE t1.num IN (SELECT num FROM t5 WHERE num = (SELECT MAX(num) FROM t4) AND val LIKE '%')\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (no condition)  (cost=6556388.62 rows=0) (actual time=4058.502..5291.463 rows=16000000 loops=1)
    -> Table scan on tmp_t3  (cost=2.50..2.50 rows=0) (actual time=0.001..0.004 rows=16 loops=1)
        -> Materialize  (cost=2.50..2.50 rows=0) (actual time=3334.956..3334.961 rows=16 loops=1)
            -> Table scan on <temporary>  (actual time=0.004..0.008 rows=16 loops=1)
                -> Aggregate using temporary table  (actual time=3334.335..3334.340 rows=16 loops=1)
                    -> Covering index scan on t3 using val  (cost=103631.25 rows=996250) (actual time=1.751..893.790 rows=1000000 loops=1)
    -> Hash
        -> Covering index scan on t2 using val  (cost=104645.03 rows=982049) (actual time=2.126..543.544 rows=1000000 loops=1)

1 row in set (8.78 sec)

EXPLAIN ANALYZEならテーブル名も出るかなと思ったけどそんなことは無かった。