とある日

MySQL 5.7 にて STRAIGHT_JOIN を使って JOIN する順番を指定してチューニングした話 - Qiita

上記の記事を見てMySQL 8.0にあるJOIN_ORDER(JOIN_FIXED_ORDER)を試してみた話です。

前提

MySQL 8.0.29でやってます。

参考記事と同じこと
- テーブル構成は同じ
- テーブル行数は同じ
参考記事とは違うこと
- 参考記事で紹介されていたJOIN_ORDERだと効果がでなかったのでJOIN_FIXED_ORDERで検証
- MySQLのバージョン参考記事5.7→本記事8.0
- 初期状態の実行計画
- t0のカラム名（使い慣れたカラム名に変更）

JOIN_FIXED_ORDERってなに？

オプティマイザヒントの一つで結合順序に FROM 句で指定されたテーブルの順序を使用するやつです。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 8.9.3 オプティマイザヒント

JOIN_FIXED_ORDER: オプティマイザが、FROM 句に出現する順序を使用してテーブルを結合するように強制します。これは、SELECT STRAIGHT_JOIN の指定と同じです。

参考記事では、JOIN_ORDERが紹介されていました。↓

JOIN_ORDER: 指定されたテーブルの順序を使用してテーブルを結合するようオプティマイザに指示します。ヒントは、指定したテーブルに適用されます。オプティマイザは、指定されたテーブルの間を含め、結合順序のどこにも名前が付いていないテーブルを配置できます。

両者の違いは、強制できるかどうからしいです。

STRAIGHT_JOINとの違い

STRAIGHT_JOINは指定した順番を強制させるものでしたが，JOIN_ORDER，JOIN_PREFIX，JOIN_SUFFIXは順番を強制しないので，オプティマイザがコストが高いと判断した場合は選択されない可能性があります。

第97回　JOIN_ORDERを使ってJOINの順番を決める：MySQL道普請便り｜gihyo.jp … 技術評論社

ドキュメントからは上記の記述を見つけられなかったですが、検証した結果おそらく間違っていないと思います。

つまり、JOIN_ORDERだとオプティマイザの順序が変わらなかったのでJOIN_FIXED_ORDERで強制的に使用させて検証しました。

やってみる

テーブル作成

概要

t0
- メインテーブル
- 100万レコード
t1
- 1万レコード
t2
- 10万レコード
t3
- 3レコード

テーブル構成

(root@localhost) [test] 8.0.29 > show create table t0\G;
*************************** 1. row ***************************
       Table: t0
Create Table: CREATE TABLE `t0` (
  `id` int NOT NULL AUTO_INCREMENT,
  `t1_id` int NOT NULL,
  `t2_id` int NOT NULL,
  `t3_id` int NOT NULL,
  `created_at` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_t1_id` (`t1_id`),
  KEY `fk_t2_id` (`t2_id`),
  KEY `fk_t3_id` (`t3_id`),
  CONSTRAINT `fk_t1_id` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`t1_id`),
  CONSTRAINT `fk_t2_id` FOREIGN KEY (`t2_id`) REFERENCES `t2` (`t2_id`),
  CONSTRAINT `fk_t3_id` FOREIGN KEY (`t3_id`) REFERENCES `t3` (`t3_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1048561 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

ERROR:
No query specified

(root@localhost) [test] 8.0.29 > show create table t1\G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `t1_id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`t1_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16384 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

ERROR:
No query specified

(root@localhost) [test] 8.0.29 > show create table t2\G;
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `t2_id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`t2_id`)
) ENGINE=InnoDB AUTO_INCREMENT=131071 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

ERROR:
No query specified

(root@localhost) [test] 8.0.29 > show create table t3\G;
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `t3_id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`t3_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

ERROR:
No query specified

使ったSQL

table rokuは別で使った大量にレコードがあるテーブルです。

ランダムデータを生成する際にきれいな順序のテーブルが欲しかったので使ってます。

performance_schema.events_errors_summary_by_thread_by_errorも同じ理由です。

（余談：auto_incrementの仕様で0だと値を保管してくれること忘れてたので別で記事にします）

CREATE TABLE t0 (
    id int PRIMARY KEY AUTO_INCREMENT,
    t1_id int NOT NULL,
    t2_id int NOT NULL,
    t3_id int NOT NULL,
    created_at date NOT NULL,
    CONSTRAINT fk_t1_id FOREIGN KEY (t1_id) REFERENCES t1 (t1_id),
    CONSTRAINT fk_t2_id FOREIGN KEY (t2_id) REFERENCES t2 (t2_id),
    CONSTRAINT fk_t3_id FOREIGN KEY (t3_id) REFERENCES t3 (t3_id)
);

INSERT INTO
    t0(t1_id, t2_id, t3_id, created_at)
SELECT
    CEIL(RAND() * 10000),
    CEIL(RAND() * 100000),
    CEIL(RAND() * 3),
    ADDDATE('1950-01-01', 50000 * rand())
FROM
    roku
LIMIT
    1000000;

CREATE TABLE t1(t1_id int PRIMARY KEY AUTO_INCREMENT);

INSERT INTO
    t1
SELECT
    0
FROM
    performance_schema.events_errors_summary_by_thread_by_error
LIMIT
    10000;

CREATE TABLE t2(t2_id int PRIMARY KEY AUTO_INCREMENT);

INSERT INTO
    t2
SELECT
    0
FROM
    roku
LIMIT
    100000;

CREATE TABLE t3(t3_id int PRIMARY KEY AUTO_INCREMENT);

INSERT INTO
    t3
SELECT
    0
FROM
    performance_schema.events_errors_summary_by_thread_by_error
LIMIT
    3;

初期状態

検索クエリは参考記事のSQLを使用します。

SELECT
    t0.*
FROM
    t0 -- 件数多め（全部で100万レコードくらい）
    JOIN t2 -- 件数やや多め（全部で10万レコードくらい）
    ON t0.t2_id = t2.t2_id
    JOIN t1 -- 件数やや多め（全部で1万レコードくらい）
    ON t0.t1_id = t1.t1_id
    JOIN t3 -- 件数少なめ（全部で3レコードくらい）
    ON t0.t3_id = t3.t3_id
WHERE
    t0.created_at BETWEEN '1940-04-01'
    AND '2085-03-31'
ORDER BY
    t0.created_at
LIMIT
    30;

検索結果

大体、4秒ぐらいかかります。

(root@localhost) [test] 8.0.29 > SELECT
    ->     t0.*
    -> FROM
    ->     t0 -- 100
    ->     JOIN t2 -- 10
    ->     ON t0.t2_id = t2.t2_id
    ->     JOIN t1 -- 1
    ->     ON t0.t1_id = t1.t1_id
    ->     JOIN t3 -- 3
    ->     ON t0.t3_id = t3.t3_id
    -> WHERE
    ->     t0.created_at BETWEEN '1940-04-01'
    ->     AND '2085-03-31'
    -> ORDER BY
    ->     t0.created_at
    -> LIMIT
    ->     30;
+--------+-------+-------+-------+------------+
| id     | t1_id | t2_id | t3_id | created_at |
+--------+-------+-------+-------+------------+
| 299422 |  4529 | 85423 |     3 | 1950-01-01 |
| 172889 |  9136 | 53197 |     3 | 1950-01-01 |
| 878039 |  5457 | 41748 |     2 | 1950-01-01 |
| 363843 |   586 | 90356 |     2 | 1950-01-01 |
| 607999 |  8442 | 62103 |     2 | 1950-01-01 |
| 941697 |   511 | 89842 |     2 | 1950-01-02 |
| 215688 |  6832 | 32944 |     2 | 1950-01-02 |
| 191040 |  6511 | 26207 |     2 | 1950-01-02 |
| 453944 |  6518 | 71709 |     2 | 1950-01-02 |
|  26897 |  6100 | 46133 |     2 | 1950-01-02 |
|  52269 |  7762 |  7463 |     1 | 1950-01-02 |
|  28830 |  4899 | 69765 |     1 | 1950-01-02 |
| 915859 |  5876 | 35518 |     1 | 1950-01-02 |
| 798994 |  5426 | 36991 |     1 | 1950-01-02 |
| 256949 |  4682 | 91008 |     1 | 1950-01-02 |
| 330159 |  5790 | 21294 |     1 | 1950-01-02 |
| 256693 |  8047 | 63955 |     3 | 1950-01-02 |
| 280763 |  7896 | 85648 |     3 | 1950-01-02 |
| 892184 |  8504 | 67073 |     3 | 1950-01-02 |
| 460097 |  3925 | 40393 |     3 | 1950-01-02 |
| 333186 |   831 | 23848 |     3 | 1950-01-02 |
| 512710 |  2286 | 70126 |     3 | 1950-01-02 |
| 550199 |  2337 | 47748 |     3 | 1950-01-03 |
| 598206 |  3416 | 36923 |     3 | 1950-01-03 |
| 897192 |  5508 | 10281 |     3 | 1950-01-03 |
| 664371 |   527 | 21772 |     3 | 1950-01-03 |
| 245277 |  6340 | 93226 |     3 | 1950-01-03 |
| 315596 |   310 | 20293 |     3 | 1950-01-03 |
| 455382 |  3084 | 75567 |     3 | 1950-01-03 |
| 452283 |  3608 | 79144 |     3 | 1950-01-03 |
+--------+-------+-------+-------+------------+
30 rows in set (3.90 sec)

さらに、EXPLAIN結果

(root@localhost) [test] 8.0.29 > explain SELECT     t0.* FROM     t0      JOIN t2      ON t0.t2_id = t2.t2_id     JOIN t1      ON t0.t1_id = t1.t1_id     JOIN t3      ON t0.t3_id = t3.t3_id WHERE     t0.create
d_at BETWEEN '1940-04-01'     AND '2085-03-31' ORDER BY     t0.created_at LIMIT     30;
+----+-------------+-------+------------+--------+----------------------------+----------+---------+---------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys              | key      | key_len | ref           | rows | filtered | Extra                                        |
+----+-------------+-------+------------+--------+----------------------------+----------+---------+---------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | t3    | NULL       | index  | PRIMARY                    | PRIMARY  | 4       | NULL          |    3 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | t0    | NULL       | ref    | fk_t1_id,fk_t2_id,fk_t3_id | fk_t3_id | 4       | test.t3.t3_id |    1 |    11.11 | Using where                                  |
|  1 | SIMPLE      | t1    | NULL       | eq_ref | PRIMARY                    | PRIMARY  | 4       | test.t0.t1_id |    1 |   100.00 | Using index                                  |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY                    | PRIMARY  | 4       | test.t0.t2_id |    1 |   100.00 | Using index                                  |
+----+-------------+-------+------------+--------+----------------------------+----------+---------+---------------+------+----------+----------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

TREEバージョン

（使い慣れてないからまったくわからなかった）

(root@localhost) [test] 8.0.29 > explain format=tree SELECT     t0.* FROM     t0      JOIN t2      ON t0.t2_id = t2.t2_id     JOIN t1      ON t0.t1_id = t1.t1_id     JOIN t3      ON t0.t3_id = t3.t3_id WHERE
   t0.created_at BETWEEN '1940-04-01'     AND '2085-03-31' ORDER BY     t0.created_at LIMIT     30\G;
*************************** 1. row ***************************
EXPLAIN: -> Limit: 30 row(s)
    -> Sort: t0.created_at, limit input to 30 row(s) per chunk
        -> Stream results  (cost=1.83 rows=0.3)
            -> Nested loop inner join  (cost=1.83 rows=0.3)
                -> Nested loop inner join  (cost=1.72 rows=0.3)
                    -> Nested loop inner join  (cost=1.60 rows=0.3)
                        -> Index scan on t3 using PRIMARY  (cost=0.55 rows=3)
                        -> Filter: (t0.created_at between '1940-04-01' and '2085-03-31')  (cost=0.25 rows=0.1)
                            -> Index lookup on t0 using fk_t3_id (t3_id=t3.t3_id)  (cost=0.25 rows=1)
                    -> Single-row covering index lookup on t1 using PRIMARY (t1_id=t0.t1_id)  (cost=0.55 rows=1)
                -> Single-row covering index lookup on t2 using PRIMARY (t2_id=t0.t2_id)  (cost=0.55 rows=1)

1 row in set (0.00 sec)

ERROR:
No query specified

上記の結果から、参考記事では効いていなかったt0のINDEXはrefになってます。

日付全体の割合でINDEXが効くかどうか決まるのかなと思い範囲を限界まで広げても結果変わらず、秒数が少し長くなるだけでした。

JOIN_FIXED_ORDER指定してみる

サンプルがあまりなかったので試行錯誤しながらやりました。

Amazon Aurora MySQL reference - Amazon Aurora

SELECT
    /*+
     JOIN_FIXED_ORDER()
     */
    t0.*
FROM
    t0 -- 件数多め（全部で100万レコードくらい）
    JOIN t1 -- 件数やや多め（全部で1万レコードくらい）
    ON t0.t1_id = t1.t1_id
    JOIN t2 -- 件数やや多め（全部で10万レコードくらい）
    ON t0.t2_id = t2.t2_id
    JOIN t3 -- 件数少なめ（全部で3レコードくらい）
    ON t0.t3_id = t3.t3_id
WHERE
    t0.created_at BETWEEN '1940-04-01'
    AND '2085-03-31'
ORDER BY
    t0.created_at
LIMIT
    30;

検索結果

大体、1秒切ります。早くなってます。

(root@localhost) [test] 8.0.29 > SELECT
    ->     /*+
    ->      JOIN_FIXED_ORDER()
    ->      */
    ->     t0.*
    -> FROM
    ->     t0 -- 100
    ->     JOIN t1 -- 1
    ->     ON t0.t1_id = t1.t1_id
    ->     JOIN t2 -- 10
    ->     ON t0.t2_id = t2.t2_id
    ->     JOIN t3 -- 3
    ->     ON t0.t3_id = t3.t3_id
    -> WHERE
    ->     t0.created_at BETWEEN '1940-04-01'
    ->     AND '2085-03-31'
    -> ORDER BY
    ->     t0.created_at
    -> LIMIT
    ->     30;
+--------+-------+-------+-------+------------+
| id     | t1_id | t2_id | t3_id | created_at |
+--------+-------+-------+-------+------------+
| 878039 |  5457 | 41748 |     2 | 1950-01-01 |
| 299422 |  4529 | 85423 |     3 | 1950-01-01 |
| 172889 |  9136 | 53197 |     3 | 1950-01-01 |
| 607999 |  8442 | 62103 |     2 | 1950-01-01 |
| 363843 |   586 | 90356 |     2 | 1950-01-01 |
| 460097 |  3925 | 40393 |     3 | 1950-01-02 |
| 453944 |  6518 | 71709 |     2 | 1950-01-02 |
| 512710 |  2286 | 70126 |     3 | 1950-01-02 |
| 915859 |  5876 | 35518 |     1 | 1950-01-02 |
| 892184 |  8504 | 67073 |     3 | 1950-01-02 |
| 941697 |   511 | 89842 |     2 | 1950-01-02 |
| 798994 |  5426 | 36991 |     1 | 1950-01-02 |
| 191040 |  6511 | 26207 |     2 | 1950-01-02 |
| 256693 |  8047 | 63955 |     3 | 1950-01-02 |
| 256949 |  4682 | 91008 |     1 | 1950-01-02 |
| 215688 |  6832 | 32944 |     2 | 1950-01-02 |
|  26897 |  6100 | 46133 |     2 | 1950-01-02 |
|  28830 |  4899 | 69765 |     1 | 1950-01-02 |
|  52269 |  7762 |  7463 |     1 | 1950-01-02 |
| 280763 |  7896 | 85648 |     3 | 1950-01-02 |
| 333186 |   831 | 23848 |     3 | 1950-01-02 |
| 330159 |  5790 | 21294 |     1 | 1950-01-02 |
| 315596 |   310 | 20293 |     3 | 1950-01-03 |
|  14489 |  1219 | 17399 |     2 | 1950-01-03 |
|    625 |  7584 | 92614 |     2 | 1950-01-03 |
| 185399 |  5086 | 61947 |     2 | 1950-01-03 |
| 245277 |  6340 | 93226 |     3 | 1950-01-03 |
| 286928 |  2541 | 44597 |     2 | 1950-01-03 |
| 119121 |  2346 | 84171 |     2 | 1950-01-03 |
| 108777 |   617 |  4207 |     1 | 1950-01-03 |
+--------+-------+-------+-------+------------+
30 rows in set (0.67 sec)

EXPLAINの結果。

tableの順序が結合順番に変わっていることが確認できます。

ただ、typeがALLになっています。

(root@localhost) [test] 8.0.29 > explain SELECT     /*+      JOIN_FIXED_ORDER()      */     t0.* FROM     t0      JOIN t1      ON t0.t1_id = t1.t1_id     JOIN t2      ON t0.t2_id = t2.t2_id     JOIN t3      ON
 t0.t3_id = t3.t3_id WHERE     t0.created_at BETWEEN '1940-04-01'     AND '2085-03-31' ORDER BY     t0.created_at LIMIT     30;
+----+-------------+-------+------------+--------+----------------------------+---------+---------+---------------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type   | possible_keys              | key     | key_len | ref           | rows   | filtered | Extra                       |
+----+-------------+-------+------------+--------+----------------------------+---------+---------+---------------+--------+----------+-----------------------------+
|  1 | SIMPLE      | t0    | NULL       | ALL    | fk_t1_id,fk_t2_id,fk_t3_id | NULL    | NULL    | NULL          | 998123 |    11.11 | Using where; Using filesort |
|  1 | SIMPLE      | t1    | NULL       | eq_ref | PRIMARY                    | PRIMARY | 4       | test.t0.t1_id |      1 |   100.00 | Using index                 |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY                    | PRIMARY | 4       | test.t0.t2_id |      1 |   100.00 | Using index                 |
|  1 | SIMPLE      | t3    | NULL       | eq_ref | PRIMARY                    | PRIMARY | 4       | test.t0.t3_id |      1 |   100.00 | Using index                 |
+----+-------------+-------+------------+--------+----------------------------+---------+---------+---------------+--------+----------+-----------------------------+
4 rows in set, 1 warning (0.00 sec)



(root@localhost) [test] 8.0.29 > explain format=tree SELECT     /*+      JOIN_FIXED_ORDER()      */     t0.* FROM     t0      JOIN t1      ON t0.t1_id = t1.t1_id     JOIN t2      ON t0.t2_id = t2.t2_id     JOIN t3      ON t0.t3_id = t3.t3_id WHERE     t0.created_at BETWEEN '1940-04-01'     AND '2085-03-31' ORDER BY     t0.created_at LIMIT     30\G;
*************************** 1. row ***************************
EXPLAIN: -> Limit: 30 row(s)  (cost=483002.80 rows=30)
    -> Nested loop inner join  (cost=483002.80 rows=998123)
        -> Nested loop inner join  (cost=355467.63 rows=998123)
            -> Nested loop inner join  (cost=227932.47 rows=998123)
                -> Sort: t0.created_at  (cost=100397.30 rows=998123)
                    -> Filter: (t0.created_at between '1940-04-01' and '2085-03-31')  (cost=100397.30 rows=998123)
                        -> Table scan on t0  (cost=100397.30 rows=998123)
                -> Single-row covering index lookup on t1 using PRIMARY (t1_id=t0.t1_id)  (cost=0.25 rows=1)
            -> Single-row covering index lookup on t2 using PRIMARY (t2_id=t0.t2_id)  (cost=0.25 rows=1)
        -> Single-row covering index lookup on t3 using PRIMARY (t3_id=t0.t3_id)  (cost=0.25 rows=1)

1 row in set (0.00 sec)

ERROR:
No query specified

試行錯誤

テーブルの結合順番を変えてみる
1. テーブル行数多い順:t2→t1→t3
2. テーブル行数少ない順:t3→t1→t2

結論を書くと、あまり差分はなかった。

推測として時間がかかっているのは最初のt0部分なのでそれ以降は順番が変わってもINDEXが聞いていい感じになるからだと思われます。

テーブル行数多い順

結果変わらず。

(root@localhost) [test] 8.0.29 > SELECT
    ->     /*+
    ->      JOIN_FIXED_ORDER()
    ->      */
    ->     t0.*
    -> FROM
    ->     t0 -- 100
    ->     JOIN t2 -- 10
    ->     ON t0.t2_id = t2.t2_id
    ->     JOIN t1 -- 1
    ->     ON t0.t1_id = t1.t1_id
    ->     JOIN t3 -- 3
    ->     ON t0.t3_id = t3.t3_id
    -> WHERE
    ->     t0.created_at BETWEEN '1940-04-01'
    ->     AND '2085-03-31'
    -> ORDER BY
    ->     t0.created_at
    -> LIMIT
    ->     30;
+--------+-------+-------+-------+------------+
| id     | t1_id | t2_id | t3_id | created_at |
+--------+-------+-------+-------+------------+
| 878039 |  5457 | 41748 |     2 | 1950-01-01 |
| 299422 |  4529 | 85423 |     3 | 1950-01-01 |
| 172889 |  9136 | 53197 |     3 | 1950-01-01 |
| 607999 |  8442 | 62103 |     2 | 1950-01-01 |
| 363843 |   586 | 90356 |     2 | 1950-01-01 |
| 460097 |  3925 | 40393 |     3 | 1950-01-02 |
| 453944 |  6518 | 71709 |     2 | 1950-01-02 |
| 512710 |  2286 | 70126 |     3 | 1950-01-02 |
| 915859 |  5876 | 35518 |     1 | 1950-01-02 |
| 892184 |  8504 | 67073 |     3 | 1950-01-02 |
| 941697 |   511 | 89842 |     2 | 1950-01-02 |
| 798994 |  5426 | 36991 |     1 | 1950-01-02 |
| 191040 |  6511 | 26207 |     2 | 1950-01-02 |
| 256693 |  8047 | 63955 |     3 | 1950-01-02 |
| 256949 |  4682 | 91008 |     1 | 1950-01-02 |
| 215688 |  6832 | 32944 |     2 | 1950-01-02 |
|  26897 |  6100 | 46133 |     2 | 1950-01-02 |
|  28830 |  4899 | 69765 |     1 | 1950-01-02 |
|  52269 |  7762 |  7463 |     1 | 1950-01-02 |
| 280763 |  7896 | 85648 |     3 | 1950-01-02 |
| 333186 |   831 | 23848 |     3 | 1950-01-02 |
| 330159 |  5790 | 21294 |     1 | 1950-01-02 |
| 315596 |   310 | 20293 |     3 | 1950-01-03 |
|  14489 |  1219 | 17399 |     2 | 1950-01-03 |
|    625 |  7584 | 92614 |     2 | 1950-01-03 |
| 185399 |  5086 | 61947 |     2 | 1950-01-03 |
| 245277 |  6340 | 93226 |     3 | 1950-01-03 |
| 286928 |  2541 | 44597 |     2 | 1950-01-03 |
| 119121 |  2346 | 84171 |     2 | 1950-01-03 |
| 108777 |   617 |  4207 |     1 | 1950-01-03 |
+--------+-------+-------+-------+------------+
30 rows in set (0.68 sec)

EXPLAIN結果

(root@localhost) [test] 8.0.29 > explain SELECT     /*+      JOIN_FIXED_ORDER()      */     t0.* FROM     t0      JOIN t2      ON t0.t2_id = t2.t2_id     JOIN t1      ON t0.t1_id = t1.t1_id     JOIN t3      ON
 t0.t3_id = t3.t3_id WHERE     t0.created_at BETWEEN '1940-04-01'     AND '2085-03-31' ORDER BY     t0.created_at LIMIT     30;
+----+-------------+-------+------------+--------+----------------------------+---------+---------+---------------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type   | possible_keys              | key     | key_len | ref           | rows   | filtered | Extra                       |
+----+-------------+-------+------------+--------+----------------------------+---------+---------+---------------+--------+----------+-----------------------------+
|  1 | SIMPLE      | t0    | NULL       | ALL    | fk_t1_id,fk_t2_id,fk_t3_id | NULL    | NULL    | NULL          | 998123 |    11.11 | Using where; Using filesort |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY                    | PRIMARY | 4       | test.t0.t2_id |      1 |   100.00 | Using index                 |
|  1 | SIMPLE      | t1    | NULL       | eq_ref | PRIMARY                    | PRIMARY | 4       | test.t0.t1_id |      1 |   100.00 | Using index                 |
|  1 | SIMPLE      | t3    | NULL       | eq_ref | PRIMARY                    | PRIMARY | 4       | test.t0.t3_id |      1 |   100.00 | Using index                 |
+----+-------------+-------+------------+--------+----------------------------+---------+---------+---------------+--------+----------+-----------------------------+
4 rows in set, 1 warning (0.00 sec)

(root@localhost) [test] 8.0.29 > explain format=tree SELECT     /*+      JOIN_FIXED_ORDER()      */     t0.* FROM     t0      JOIN t2      ON t0.t2_id = t2.t2_id     JOIN t1      ON t0.t1_id = t1.t1_id     JOIN t3      ON t0.t3_id = t3.t3_id WHERE     t0.created_at BETWEEN '1940-04-01'     AND '2085-03-31' ORDER BY     t0.created_at LIMIT     30\G;
*************************** 1. row ***************************
EXPLAIN: -> Limit: 30 row(s)  (cost=483002.80 rows=30)
    -> Nested loop inner join  (cost=483002.80 rows=998123)
        -> Nested loop inner join  (cost=355467.63 rows=998123)
            -> Nested loop inner join  (cost=227932.47 rows=998123)
                -> Sort: t0.created_at  (cost=100397.30 rows=998123)
                    -> Filter: (t0.created_at between '1940-04-01' and '2085-03-31')  (cost=100397.30 rows=998123)
                        -> Table scan on t0  (cost=100397.30 rows=998123)
                -> Single-row covering index lookup on t2 using PRIMARY (t2_id=t0.t2_id)  (cost=0.25 rows=1)
            -> Single-row covering index lookup on t1 using PRIMARY (t1_id=t0.t1_id)  (cost=0.25 rows=1)
        -> Single-row covering index lookup on t3 using PRIMARY (t3_id=t0.t3_id)  (cost=0.25 rows=1)

1 row in set (0.00 sec)

ERROR:
No query specified

テーブル行数少ない順

こちらも何も変わらず。

(root@localhost) [test] 8.0.29 > SELECT
    ->     /*+
    ->      JOIN_FIXED_ORDER()
    ->      */
    ->     t0.*
    -> FROM
    ->     t0 -- 100
    ->     JOIN t3 -- 3
    ->     ON t0.t3_id = t3.t3_id
    ->     JOIN t1 -- 1
    ->     ON t0.t1_id = t1.t1_id
    ->     JOIN t2 -- 10
    ->     ON t0.t2_id = t2.t2_id
    -> WHERE
    ->     t0.created_at BETWEEN '1940-04-01'
    ->     AND '2085-03-31'
    -> ORDER BY
    ->     t0.created_at
    -> LIMIT
    ->     30;
+--------+-------+-------+-------+------------+
| id     | t1_id | t2_id | t3_id | created_at |
+--------+-------+-------+-------+------------+
| 878039 |  5457 | 41748 |     2 | 1950-01-01 |
| 299422 |  4529 | 85423 |     3 | 1950-01-01 |
| 172889 |  9136 | 53197 |     3 | 1950-01-01 |
| 607999 |  8442 | 62103 |     2 | 1950-01-01 |
| 363843 |   586 | 90356 |     2 | 1950-01-01 |
| 460097 |  3925 | 40393 |     3 | 1950-01-02 |
| 453944 |  6518 | 71709 |     2 | 1950-01-02 |
| 512710 |  2286 | 70126 |     3 | 1950-01-02 |
| 915859 |  5876 | 35518 |     1 | 1950-01-02 |
| 892184 |  8504 | 67073 |     3 | 1950-01-02 |
| 941697 |   511 | 89842 |     2 | 1950-01-02 |
| 798994 |  5426 | 36991 |     1 | 1950-01-02 |
| 191040 |  6511 | 26207 |     2 | 1950-01-02 |
| 256693 |  8047 | 63955 |     3 | 1950-01-02 |
| 256949 |  4682 | 91008 |     1 | 1950-01-02 |
| 215688 |  6832 | 32944 |     2 | 1950-01-02 |
|  26897 |  6100 | 46133 |     2 | 1950-01-02 |
|  28830 |  4899 | 69765 |     1 | 1950-01-02 |
|  52269 |  7762 |  7463 |     1 | 1950-01-02 |
| 280763 |  7896 | 85648 |     3 | 1950-01-02 |
| 333186 |   831 | 23848 |     3 | 1950-01-02 |
| 330159 |  5790 | 21294 |     1 | 1950-01-02 |
| 315596 |   310 | 20293 |     3 | 1950-01-03 |
|  14489 |  1219 | 17399 |     2 | 1950-01-03 |
|    625 |  7584 | 92614 |     2 | 1950-01-03 |
| 185399 |  5086 | 61947 |     2 | 1950-01-03 |
| 245277 |  6340 | 93226 |     3 | 1950-01-03 |
| 286928 |  2541 | 44597 |     2 | 1950-01-03 |
| 119121 |  2346 | 84171 |     2 | 1950-01-03 |
| 108777 |   617 |  4207 |     1 | 1950-01-03 |
+--------+-------+-------+-------+------------+
30 rows in set (0.68 sec)

EXPLAIN結果

(root@localhost) [test] 8.0.29 > explain SELECT     /*+      JOIN_FIXED_ORDER()      */     t0.* FROM     t0      JOIN t3      ON t0.t3_id = t3.t3_id     JOIN t1      ON t0.t1_id = t1.t1_id     JOIN t2      ON
 t0.t2_id = t2.t2_id WHERE     t0.created_at BETWEEN '1940-04-01'     AND '2085-03-31' ORDER BY     t0.created_at LIMIT     30;
+----+-------------+-------+------------+--------+----------------------------+---------+---------+---------------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type   | possible_keys              | key     | key_len | ref           | rows   | filtered | Extra                       |
+----+-------------+-------+------------+--------+----------------------------+---------+---------+---------------+--------+----------+-----------------------------+
|  1 | SIMPLE      | t0    | NULL       | ALL    | fk_t1_id,fk_t2_id,fk_t3_id | NULL    | NULL    | NULL          | 998123 |    11.11 | Using where; Using filesort |
|  1 | SIMPLE      | t3    | NULL       | eq_ref | PRIMARY                    | PRIMARY | 4       | test.t0.t3_id |      1 |   100.00 | Using index                 |
|  1 | SIMPLE      | t1    | NULL       | eq_ref | PRIMARY                    | PRIMARY | 4       | test.t0.t1_id |      1 |   100.00 | Using index                 |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY                    | PRIMARY | 4       | test.t0.t2_id |      1 |   100.00 | Using index                 |
+----+-------------+-------+------------+--------+----------------------------+---------+---------+---------------+--------+----------+-----------------------------+
4 rows in set, 1 warning (0.00 sec)

(root@localhost) [test] 8.0.29 > explain format=tree SELECT     /*+      JOIN_FIXED_ORDER()      */     t0.* FROM     t0      JOIN t3      ON t0.t3_id = t3.t3_id     JOIN t1      ON t0.t1_id = t1.t1_id     JOIN t2      ON t0.t2_id = t2.t2_id WHERE     t0.created_at BETWEEN '1940-04-01'     AND '2085-03-31' ORDER BY     t0.created_at LIMIT     30\G;
*************************** 1. row ***************************
EXPLAIN: -> Limit: 30 row(s)  (cost=483002.80 rows=30)
    -> Nested loop inner join  (cost=483002.80 rows=998123)
        -> Nested loop inner join  (cost=355467.63 rows=998123)
            -> Nested loop inner join  (cost=227932.47 rows=998123)
                -> Sort: t0.created_at  (cost=100397.30 rows=998123)
                    -> Filter: (t0.created_at between '1940-04-01' and '2085-03-31')  (cost=100397.30 rows=998123)
                        -> Table scan on t0  (cost=100397.30 rows=998123)
                -> Single-row covering index lookup on t3 using PRIMARY (t3_id=t0.t3_id)  (cost=0.25 rows=1)
            -> Single-row covering index lookup on t1 using PRIMARY (t1_id=t0.t1_id)  (cost=0.25 rows=1)
        -> Single-row covering index lookup on t2 using PRIMARY (t2_id=t0.t2_id)  (cost=0.25 rows=1)

1 row in set (0.00 sec)

ERROR:
No query specified

おまけ

JOIN_FIXED_ORDER指定後のEXPLAINをみててt0のWHERE句にINDEX効くようにすればもっと早いのではと思ったのでやってみます。

INDEX追加。

(root@localhost) [test] 8.0.29 > create index at_index on t0(created_at);
Query OK, 0 rows affected (3.50 sec)
Records: 0  Duplicates: 0  Warnings: 0

更に早くなった。

(root@localhost) [test] 8.0.29 > SELECT     /*+      JOIN_FIXED_ORDER()      */     t0.* FROM     t0      JOIN t3      ON t0.t3_id = t3.t3_id     JOIN t1      ON t0.t1_id = t1.t1_id     JOIN t2      ON t0.t2_id = t2.t2_id WHERE     t0.created_at BETWEEN '1940-04-01'     AND '2085-03-31' ORDER BY     t0.created_at LIMIT     30;
+--------+-------+-------+-------+------------+
| id     | t1_id | t2_id | t3_id | created_at |
+--------+-------+-------+-------+------------+
| 172889 |  9136 | 53197 |     3 | 1950-01-01 |
| 299422 |  4529 | 85423 |     3 | 1950-01-01 |
| 363843 |   586 | 90356 |     2 | 1950-01-01 |
| 607999 |  8442 | 62103 |     2 | 1950-01-01 |
| 878039 |  5457 | 41748 |     2 | 1950-01-01 |
|  26897 |  6100 | 46133 |     2 | 1950-01-02 |
|  28830 |  4899 | 69765 |     1 | 1950-01-02 |
|  52269 |  7762 |  7463 |     1 | 1950-01-02 |
| 191040 |  6511 | 26207 |     2 | 1950-01-02 |
| 215688 |  6832 | 32944 |     2 | 1950-01-02 |
| 256693 |  8047 | 63955 |     3 | 1950-01-02 |
| 256949 |  4682 | 91008 |     1 | 1950-01-02 |
| 280763 |  7896 | 85648 |     3 | 1950-01-02 |
| 330159 |  5790 | 21294 |     1 | 1950-01-02 |
| 333186 |   831 | 23848 |     3 | 1950-01-02 |
| 453944 |  6518 | 71709 |     2 | 1950-01-02 |
| 460097 |  3925 | 40393 |     3 | 1950-01-02 |
| 512710 |  2286 | 70126 |     3 | 1950-01-02 |
| 798994 |  5426 | 36991 |     1 | 1950-01-02 |
| 892184 |  8504 | 67073 |     3 | 1950-01-02 |
| 915859 |  5876 | 35518 |     1 | 1950-01-02 |
| 941697 |   511 | 89842 |     2 | 1950-01-02 |
|    625 |  7584 | 92614 |     2 | 1950-01-03 |
|  14489 |  1219 | 17399 |     2 | 1950-01-03 |
| 108777 |   617 |  4207 |     1 | 1950-01-03 |
| 119121 |  2346 | 84171 |     2 | 1950-01-03 |
| 185399 |  5086 | 61947 |     2 | 1950-01-03 |
| 245277 |  6340 | 93226 |     3 | 1950-01-03 |
| 286928 |  2541 | 44597 |     2 | 1950-01-03 |
| 315596 |   310 | 20293 |     3 | 1950-01-03 |
+--------+-------+-------+-------+------------+
30 rows in set (0.01 sec)

EXPLAIN結果

狙い通り？

(root@localhost) [test] 8.0.29 > explain SELECT     /*+      JOIN_FIXED_ORDER()      */     t0.* FROM     t0      JOIN t3      ON t0.t3_id = t3.t3_id     JOIN t1      ON t0.t1_id = t1.t1_id     JOIN t2      ON
 t0.t2_id = t2.t2_id WHERE     t0.created_at BETWEEN '1940-04-01'     AND '2085-03-31' ORDER BY     t0.created_at LIMIT     30;
+----+-------------+-------+------------+--------+-------------------------------------+----------+---------+---------------+--------+----------+-----------------------+
| id | select_type | table | partitions | type   | possible_keys                       | key      | key_len | ref           | rows   | filtered | Extra                 |
+----+-------------+-------+------------+--------+-------------------------------------+----------+---------+---------------+--------+----------+-----------------------+
|  1 | SIMPLE      | t0    | NULL       | range  | fk_t1_id,fk_t2_id,fk_t3_id,at_index | at_index | 3       | NULL          | 499061 |   100.00 | Using index condition |
|  1 | SIMPLE      | t3    | NULL       | eq_ref | PRIMARY                             | PRIMARY  | 4       | test.t0.t3_id |      1 |   100.00 | Using index           |
|  1 | SIMPLE      | t1    | NULL       | eq_ref | PRIMARY                             | PRIMARY  | 4       | test.t0.t1_id |      1 |   100.00 | Using index           |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY                             | PRIMARY  | 4       | test.t0.t2_id |      1 |   100.00 | Using index           |
+----+-------------+-------+------------+--------+-------------------------------------+----------+---------+---------------+--------+----------+-----------------------+
4 rows in set, 1 warning (0.00 sec)

まとめ

オプティマイザは奥が深い。

なかなかに面白い検証結果となりました。

JOIN_FIXED_ORDERを使うと順序を結合順序を変えられる
JOIN_ORDERだとオプティマイザが言うことを効かない
変更した順序に対して適切なINDEXをつけると更に早くなる

今回の場合は、4秒から0.01秒まで時間が早くなりました。

参考記事と同じように初期状態でt0のINDEXが使えなかったらもっと遅いので差がより際立つのではないでしょうか。

JOIN_FIXED_ORDER前

id	table	type
1	t3	index
2	t0	ref
3	t1	eq_ref
4	t2	eq_ref

JOIN_FIXED_ORDER後

id	table	type
1	t0	range（at_createdにINDEX追加後）
2	t1	eq_ref
3	t2	eq_ref
4	t3	eq_ref

〆

早くなるクエリをみてSQL面白いなと思った休日を過ごしました。

ぜひ、面白いので参考記事もご覧ください。

参考記事

MySQL 5.7 にて STRAIGHT_JOIN を使って JOIN する順番を指定してチューニングした話 - Qiita

MySQL :: MySQL 8.0 Reference Manual :: 8.9.3 Optimizer Hints

第97回　JOIN_ORDERを使ってJOINの順番を決める：MySQL道普請便り｜gihyo.jp … 技術評論社

日々の覚書: MySQL 8.0.1からJOIN_ORDERヒントが書ける

今日はなにの日。

気になったこと勉強になったことのメモ。

今日は、MySQL 8.0 にて JOIN_FIXED_ORDERを使って JOIN する順番を指定してチューニング検証してみたの日。

目次

とある日

前提

JOIN_FIXED_ORDERってなに？

STRAIGHT_JOINとの違い

やってみる

テーブル作成

概要

テーブル構成

使ったSQL

初期状態

JOIN_FIXED_ORDER指定してみる

試行錯誤

テーブル行数多い順

テーブル行数少ない順

おまけ

まとめ

〆

参考記事