MySQLが勝手に作るファイルのパーミッションを指定する

 

TL;DR

• UMASK 環境変数で制御する
  • MySQL :: MySQL 8.0 リファレンスマニュアル :: B.3.3.1 ファイル権限の問題
• ファイルであれば最小パーミッションは 0600, ディレクトリであれば 0710 の値になる
  • MySQL :: MySQL 8.0 リファレンスマニュアル :: 4.9 環境変数
  • https://github.com/mysql/mysql-server/blob/mysql-8.0.29/mysys/my_init.cc#L141-L149

---

• UMASK を設定しないと、ファイルは 0640, ディレクトリは 0750
  • ただし auto_generate_certs で作られる証明書は 0600 (公開用のは 0644 っぽい

$ env | grep UMASK  ### からっぽ
$ /usr/mysql/8.0.29/bin/mysqld --no-defaults --initialize-insecure --datadir=/tmp/noumask
2022-06-28T03:49:08.901551Z 0 [System] [MY-013169] [Server] /usr/mysql/8.0.29/bin/mysqld (mysqld 8.0.29) initializing of server in progress as process 116145
2022-06-28T03:49:08.921448Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-06-28T03:49:09.938657Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-06-28T03:49:11.335845Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
$ ll /tmp/noumask/
total 176568
-rw-r----- 1 yoku0825 yoku0825       56 Jun 28 12:49 auto.cnf
-rw------- 1 yoku0825 yoku0825     1676 Jun 28 12:49 ca-key.pem
-rw-r--r-- 1 yoku0825 yoku0825     1112 Jun 28 12:49 ca.pem
-rw-r--r-- 1 yoku0825 yoku0825     1112 Jun 28 12:49 client-cert.pem
-rw------- 1 yoku0825 yoku0825     1676 Jun 28 12:49 client-key.pem
-rw-r----- 1 yoku0825 yoku0825   196608 Jun 28 12:49 #ib_16384_0.dblwr
-rw-r----- 1 yoku0825 yoku0825  8585216 Jun 28 12:49 #ib_16384_1.dblwr
-rw-r----- 1 yoku0825 yoku0825     5944 Jun 28 12:49 ib_buffer_pool
-rw-r----- 1 yoku0825 yoku0825 12582912 Jun 28 12:49 ibdata1
-rw-r----- 1 yoku0825 yoku0825 50331648 Jun 28 12:49 ib_logfile0
-rw-r----- 1 yoku0825 yoku0825 50331648 Jun 28 12:49 ib_logfile1
drwxr-x--- 2 yoku0825 yoku0825        6 Jun 28 12:49 #innodb_temp
drwxr-x--- 2 yoku0825 yoku0825      143 Jun 28 12:49 mysql
-rw-r----- 1 yoku0825 yoku0825 25165824 Jun 28 12:49 mysql.ibd
drwxr-x--- 2 yoku0825 yoku0825     8192 Jun 28 12:49 performance_schema
-rw------- 1 yoku0825 yoku0825     1676 Jun 28 12:49 private_key.pem
-rw-r--r-- 1 yoku0825 yoku0825      452 Jun 28 12:49 public_key.pem
-rw-r--r-- 1 yoku0825 yoku0825     1112 Jun 28 12:49 server-cert.pem
-rw------- 1 yoku0825 yoku0825     1680 Jun 28 12:49 server-key.pem
drwxr-x--- 2 yoku0825 yoku0825       28 Jun 28 12:49 sys
-rw-r----- 1 yoku0825 yoku0825 16777216 Jun 28 12:49 undo_001
-rw-r----- 1 yoku0825 yoku0825 16777216 Jun 28 12:49 undo_002

• UMASK=0600 を押し込むと、ファイルは 0600, ディレクトリは…… 0710 ?
  • 証明書類はUMASKの影響を受けてなさそう

$ UMASK=0600 /usr/mysql/8.0.29/bin/mysqld --no-defaults --initialize-insecure --datadir=/tmp/umask0600
2022-06-28T03:52:21.587890Z 0 [System] [MY-013169] [Server] /usr/mysql/8.0.29/bin/mysqld (mysqld 8.0.29) initializing of server in progress as p
rocess 116751
2022-06-28T03:52:21.595297Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-06-28T03:52:22.194497Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-06-28T03:52:23.877843Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off th
e --initialize-insecure option.

$ ll /tmp/umask0600/
total 176568
-rw------- 1 yoku0825 yoku0825       56 Jun 28 12:52 auto.cnf
-rw------- 1 yoku0825 yoku0825     1680 Jun 28 12:52 ca-key.pem
-rw-r--r-- 1 yoku0825 yoku0825     1112 Jun 28 12:52 ca.pem
-rw-r--r-- 1 yoku0825 yoku0825     1112 Jun 28 12:52 client-cert.pem
-rw------- 1 yoku0825 yoku0825     1676 Jun 28 12:52 client-key.pem
-rw------- 1 yoku0825 yoku0825   196608 Jun 28 12:52 #ib_16384_0.dblwr
-rw------- 1 yoku0825 yoku0825  8585216 Jun 28 12:52 #ib_16384_1.dblwr
-rw------- 1 yoku0825 yoku0825     5600 Jun 28 12:52 ib_buffer_pool
-rw------- 1 yoku0825 yoku0825 12582912 Jun 28 12:52 ibdata1
-rw------- 1 yoku0825 yoku0825 50331648 Jun 28 12:52 ib_logfile0
-rw------- 1 yoku0825 yoku0825 50331648 Jun 28 12:52 ib_logfile1
drwx--x--- 2 yoku0825 yoku0825        6 Jun 28 12:52 #innodb_temp
drwx--x--- 2 yoku0825 yoku0825      143 Jun 28 12:52 mysql
-rw------- 1 yoku0825 yoku0825 25165824 Jun 28 12:52 mysql.ibd
drwx--x--- 2 yoku0825 yoku0825     8192 Jun 28 12:52 performance_schema
-rw------- 1 yoku0825 yoku0825     1676 Jun 28 12:52 private_key.pem
-rw-r--r-- 1 yoku0825 yoku0825      452 Jun 28 12:52 public_key.pem
-rw-r--r-- 1 yoku0825 yoku0825     1112 Jun 28 12:52 server-cert.pem
-rw------- 1 yoku0825 yoku0825     1680 Jun 28 12:52 server-key.pem
drwx--x--- 2 yoku0825 yoku0825       28 Jun 28 12:52 sys
-rw------- 1 yoku0825 yoku0825 16777216 Jun 28 12:52 undo_001
-rw------- 1 yoku0825 yoku0825 16777216 Jun 28 12:52 undo_002

0710 がもんにょりしたけど、ドキュメントに書いてあった。

UMASK 変数および UMASK_DIR 変数は、その名前にもかかわらず、マスクではなくモードとして使用されます。

UMASK が設定されている場合、mysqld は ($UMASK | 0600) をファイル作成のモードとして使用し、新しく作成されるファイルのモードは 0600 から 0666 の範囲になります (すべて 8 進数の値)。

UMASK_DIR が設定されている場合、mysqld は ($UMASK_DIR | 0700) をディレクトリ作成のベースモードとして使用し、次に ~(~$UMASK & 0666) との AND が取られます。そのため新しく作成されるファイルのモードは 0700 から 0777 の範囲になります (すべて 8 進数の値)。 AND 演算によってディレクトリモードから読み取り/書き込み権が削除されることがありますが、実行権が削除されることはありません。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 4.9 環境変数

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 |

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

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

TL;DR

• 日々の覚書: MySQL標準のEXPLAIN(format=TRADITIONAL)とEXPLAIN format=TREEを並べて見つめてみる で言ってたやつは、過去の記憶(?)だった

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

• 5.6とそれ以降はDelayed Materializationによりt3を含んだサブクエリが最後に実行されていた
  • これはEXPLAINだけでは読み切れなさそうな予感
  • MySQL :: MySQL 5.6 Release Notes :: Changes in MySQL 5.6.3 (2011-10-03, Milestone 6)

---

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

(全部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.

• MySQL :: MySQL 5.6 Release Notes :: Changes in MySQL 5.6.3 (2011-10-03, Milestone 6)

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