Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

MySQL 8.0ではオプティマイザがバッファプールに載っているデータを意識するようになった

More than 3 years have passed since last update.

タイトルが何を言っているのか意味不明ですが、MySQL 8.0では(正確にはおそらくMySQL 8.0.1 DMRから)、オプティマイザのコスト計算で、

  • ストレージからデータを読み取る場合
  • バッファプールにあるデータを読み取る場合

それぞれの実行コストを区別し、デフォルトで後者(バッファプール読み取り)を低コストと見積もるようになりました。
ので、ちょっとだけ試してみます。

ネタ元はこちらです。

MySQL Casual Advent Calendar 2017開催中ですが、個人的に枠を取りすぎたので、こちらは通常記事です…。

1. 検証内容

  • 140万レコードを持つほぼ同じ構造のテーブルが2つ(「flag_test_h」「flag_test_h2」
  • 「flag_test_h」には「flag=1」の行が1,600行
  • 「flag_test_h2」には「flag=1」の行が3,600行
  • 両テーブルで同じ主キー(id)の行を「flag=1」を条件に内部結合すると280行

という状況で、

  • 両テーブルのデータがバッファプールに載っていないケースと、「flag_test_h」が全行バッファプールに載っているケースで実行計画はどう変わる?(駆動表/内部表)
  • コストモデルのテーブル「mysql.engine_cost」でコスト係数を変えたら?

各テーブルの定義、内容等は以下の通りです(使い回しです。すみません)。

テーブル定義・内容等
mysql> USE bp_opt_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SHOW CREATE TABLE flag_test_h\G
*************************** 1. row ***************************
       Table: flag_test_h
Create Table: CREATE TABLE `flag_test_h` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `str` varchar(100) DEFAULT NULL,
  `flag` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `flag` (`flag`)
) ENGINE=InnoDB AUTO_INCREMENT=1400001 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE flag_test_h2\G
*************************** 1. row ***************************
       Table: flag_test_h2
Create Table: CREATE TABLE `flag_test_h2` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `str` varchar(100) DEFAULT NULL,
  `flag` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `flag` (`flag`)
) ENGINE=InnoDB AUTO_INCREMENT=1400001 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM flag_test_h;
+----------+
| COUNT(*) |
+----------+
|  1400000 |
+----------+
1 row in set (2.48 sec)

mysql> SELECT COUNT(*) FROM flag_test_h WHERE flag = 1;
+----------+
| COUNT(*) |
+----------+
|     1600 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM flag_test_h2;
+----------+
| COUNT(*) |
+----------+
|  1400000 |
+----------+
1 row in set (2.48 sec)

mysql> SELECT COUNT(*) FROM flag_test_h2 WHERE flag = 1;
+----------+
| COUNT(*) |
+----------+
|     3600 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=1 AND h2.flag=1;
+----------+
| COUNT(*) |
+----------+
|      280 |
+----------+
1 row in set (0.00 sec)

コストモデルのテーブルのデフォルト値は以下の通りです(今回は「mysql.engine_cost」テーブルの値を途中で変更します)。

コストモデルのテーブル初期値
mysql> SELECT * FROM mysql.server_cost;
+------------------------------+------------+---------------------+---------+---------------+
| cost_name                    | cost_value | last_update         | comment | default_value |
+------------------------------+------------+---------------------+---------+---------------+
| disk_temptable_create_cost   |       NULL | 2017-09-29 14:27:27 | NULL    |            20 |
| disk_temptable_row_cost      |       NULL | 2017-09-29 14:27:27 | NULL    |           0.5 |
| key_compare_cost             |       NULL | 2017-09-29 14:27:27 | NULL    |          0.05 |
| memory_temptable_create_cost |       NULL | 2017-09-29 14:27:27 | NULL    |             1 |
| memory_temptable_row_cost    |       NULL | 2017-09-29 14:27:27 | NULL    |           0.1 |
| row_evaluate_cost            |       NULL | 2017-09-29 14:27:27 | NULL    |           0.1 |
+------------------------------+------------+---------------------+---------+---------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM mysql.engine_cost;
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| engine_name | device_type | cost_name              | cost_value | last_update         | comment | default_value |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| default     |           0 | io_block_read_cost     |       NULL | 2017-12-08 11:48:31 | NULL    |             1 |
| default     |           0 | memory_block_read_cost |       NULL | 2017-12-08 11:59:10 | NULL    |          0.25 |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
2 rows in set (0.00 sec)

ストレージ(ディスク)からの読み取り係数1.0に対し、バッファプール(メモリ)からの読み取り係数は0.25(1/4)です。ざっくり言ってメモリはストレージの4倍高速とみなしている、と考えると良いでしょう。

なお、テストの際、サーバ再起動でバッファプールがウォームアップしないように設定しています。

2. 結果

2-1. 両テーブルがバッファプールに載っていないケース

先に示した条件で結合すると、駆動表(外部表)の1行に対応する内部表の行のフェッチに主キーが使える(ので行フェッチのコストは小さい)⇒外側の「ぐるぐる」(ループ)回数を減らすほうがコストが小さくなることから、「flag_test_h」が駆動表(外部表)になります。

両テーブルがバッファプールに載っていないケース
mysql> EXPLAIN SELECT h.id FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=1 AND h2.flag=1;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE      | h     | NULL       | ref    | PRIMARY,flag  | flag    | 5       | const            | 1600 |   100.00 | Using index |
|  1 | SIMPLE      | h2    | NULL       | eq_ref | PRIMARY,flag  | PRIMARY | 4       | bp_opt_test.h.id |    1 |     5.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

mysql> SELECT h.id FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=1 AND h2.flag=1;
+---------+
| id      |
+---------+
|       1 |
|       2 |
|       3 |
(中略)
| 1380000 |
| 1390000 |
| 1400000 |
+---------+
280 rows in set (0.10 sec)

※オプティマイザトレースの結果は後ほど。

2-2. 「flag_test_h」テーブルがバッファプールに載っているケース

一旦MySQLサーバを再起動してバッファプールを開放し(前述の通りウォームアップ無効)、今度はテーブルチェックサムを取得して「flag_test_h」テーブルのデータをバッファプールに載せます。
すると、「flag_test_h」テーブルの検索コストが小さくなり、「flag_test_h」テーブルを内部表にしたほうがトータルコストが低くなるとオプティマイザが判断したため、駆動表と内部表が入れ替わりました(⇒内部表検索1回あたりのコストが小さい=駆動表のループ回数が増えてもトータルコストが小さくなる、と判断した)。

「flag_test_h」テーブルがバッファプールに載っているケース
mysql> USE bp_opt_test;
No connection. Trying to reconnect...
Connection id:    7
Current database: *** NONE ***

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CHECKSUM TABLE flag_test_h;
+-------------------------+------------+
| Table                   | Checksum   |
+-------------------------+------------+
| bp_opt_test.flag_test_h | 2122123514 |
+-------------------------+------------+
1 row in set (2.48 sec)

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
(中略)
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 687144960
Dictionary memory allocated 389263
Buffer pool size   40960
Free buffers       28637
Database pages     12318
Old database pages 4567
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 12280, created 38, written 51
280.83 reads/s, 0.02 creates/s, 1.19 writes/s
Buffer pool hit rate 937 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 273.85/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 12318, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
(中略)
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.01 sec)

mysql> EXPLAIN SELECT h.id FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=1 AND h2.flag=1;
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | h2    | NULL       | ref    | PRIMARY,flag  | flag    | 5       | const             | 3600 |   100.00 | Using index |
|  1 | SIMPLE      | h     | NULL       | eq_ref | PRIMARY,flag  | PRIMARY | 4       | bp_opt_test.h2.id |    1 |     5.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> SELECT h.id FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=1 AND h2.flag=1;
+---------+
| id      |
+---------+
|       1 |
|       2 |
|       3 |
(中略)
| 1380000 |
| 1390000 |
| 1400000 |
+---------+
280 rows in set (0.01 sec)

2-3. コストモデルのテーブル「mysql.engine_cost」でコスト係数を変えたら

MySQL 8.0(8.0.1 DMR以降)では、コストモデルのテーブルのうち、「mysql.engine_cost」のレコードを書き換えることで、ストレージ(ディスク)上のデータの読み取りコスト係数とバッファプール(メモリ)上のデータの読み取りコスト係数を変更することができます。

先のケースと同じように、「flag_test_h」テーブルのデータをバッファプールに載せます。

先の例と同じ状態にする
mysql> USE bp_opt_test;
No connection. Trying to reconnect...
Connection id:    7
Current database: *** NONE ***

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CHECKSUM TABLE flag_test_h;
+-------------------------+------------+
| Table                   | Checksum   |
+-------------------------+------------+
| bp_opt_test.flag_test_h | 2122123514 |
+-------------------------+------------+
1 row in set (2.48 sec)

mysql> EXPLAIN SELECT h.id FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=1 AND h2.flag=1;
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | h2    | NULL       | ref    | PRIMARY,flag  | flag    | 5       | const             | 3600 |   100.00 | Using index |
|  1 | SIMPLE      | h     | NULL       | eq_ref | PRIMARY,flag  | PRIMARY | 4       | bp_opt_test.h2.id |    1 |     5.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

続いて、バッファプール(メモリ)上のデータの読み取りコスト係数を4に変更し、(計算上の)コストを高くします。

コスト係数を変える
mysql> UPDATE mysql.engine_cost SET cost_value=4.0 WHERE cost_name = 'memory_block_read_cost';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> FLUSH OPTIMIZER_COSTS;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM mysql.engine_cost;
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| engine_name | device_type | cost_name              | cost_value | last_update         | comment | default_value |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| default     |           0 | io_block_read_cost     |       NULL | 2017-12-08 11:48:31 | NULL    |             1 |
| default     |           0 | memory_block_read_cost |          4 | 2017-12-08 13:36:45 | NULL    |          0.25 |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
2 rows in set (0.00 sec)

一旦接続を切り、再接続してからEXPLAINを見てみると、(計算上の)バッファプールからの読み取りコストが大きくなったので、元の実行計画と同様(⇒「flag_test_h」が駆動表)になりました。

再接続後
mysql> USE bp_opt_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CHECKSUM TABLE flag_test_h;
+-------------------------+------------+
| Table                   | Checksum   |
+-------------------------+------------+
| bp_opt_test.flag_test_h | 2122123514 |
+-------------------------+------------+
1 row in set (2.49 sec)

mysql> EXPLAIN SELECT h.id FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=1 AND h2.flag=1;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE      | h     | NULL       | ref    | PRIMARY,flag  | flag    | 5       | const            | 1600 |   100.00 | Using index |
|  1 | SIMPLE      | h2    | NULL       | eq_ref | PRIMARY,flag  | PRIMARY | 4       | bp_opt_test.h.id |    1 |     5.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> SELECT h.id FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=1 AND h2.flag=1;
+---------+
| id      |
+---------+
|       1 |
|       2 |
|       3 |
(中略)
| 1380000 |
| 1390000 |
| 1400000 |
+---------+
280 rows in set (0.09 sec)

※コストモデルのテーブルの設定値はサーバ再起動後も残りますので、元に戻しておきます。

コスト係数を元に戻す
mysql> UPDATE mysql.engine_cost SET cost_value=NULL WHERE cost_name = 'memory_block_read_cost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> FLUSH OPTIMIZER_COSTS;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM mysql.engine_cost;
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| engine_name | device_type | cost_name              | cost_value | last_update         | comment | default_value |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| default     |           0 | io_block_read_cost     |       NULL | 2017-12-08 11:48:31 | NULL    |             1 |
| default     |           0 | memory_block_read_cost |       NULL | 2017-12-08 13:41:52 | NULL    |          0.25 |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
2 rows in set (0.00 sec)

なお、コストモデルのテーブル自体はMySQL 5.7から存在し、設定値の変更も可能ですが、ストレージ(ディスク)/バッファプール(メモリ)のコスト係数は実際の実行計画には反映されず、同一のものとみなされるようです。

3. まとめ

実際、バッファプールに載っているのとストレージから読み取るのでは実行時間が変わるので、妥当な変更だと思います。

ただ、テーブル結合の場合、内部表の読み取りが1回でも行われると読み取られた部分のデータページがバッファプールに載り、結果として当初のコスト計算とは実行時間が変化するはずなので(バッファプールのOldページを考慮に入れると、何回目の読み取りかによっても変化する、と言える)、「ちょっと賢くなったな」程度に見ておくのがいいと思います。

4. オプティマイザトレースの結果とdiff

4-1. 両テーブルがバッファプールに載っていないケース

両テーブルがバッファプールに載っていないケース
mysql> SELECT * FROM information_schema.optimizer_trace\G
*************************** 1. row ***************************
                            QUERY: SELECT h.id FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=1 AND h2.flag=1
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `h`.`id` AS `id` from `flag_test_h` `h` join `flag_test_h2` `h2` where ((`h`.`id` = `h2`.`id`) and (`h`.`flag` = 1) and (`h2`.`flag` = 1))"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`h`.`id` = `h2`.`id`) and (`h`.`flag` = 1) and (`h2`.`flag` = 1))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(multiple equal(`h`.`id`, `h2`.`id`) and multiple equal(1, `h`.`flag`) and multiple equal(1, `h2`.`flag`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(multiple equal(`h`.`id`, `h2`.`id`) and multiple equal(1, `h`.`flag`) and multiple equal(1, `h2`.`flag`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(multiple equal(`h`.`id`, `h2`.`id`) and multiple equal(1, `h`.`flag`) and multiple equal(1, `h2`.`flag`))"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`flag_test_h` `h`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              },
              {
                "table": "`flag_test_h2` `h2`",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`flag_test_h` `h`",
                "field": "id",
                "equals": "`h2`.`id`",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h` `h`",
                "field": "flag",
                "equals": "1",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h` `h`",
                "field": "id",
                "equals": "`h2`.`id`",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h2` `h2`",
                "field": "id",
                "equals": "`h`.`id`",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h2` `h2`",
                "field": "flag",
                "equals": "1",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h2` `h2`",
                "field": "id",
                "equals": "`h`.`id`",
                "null_rejecting": false
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`flag_test_h` `h`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 1347159,
                    "cost": 146282
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "flag",
                      "usable": true,
                      "key_parts": [
                        "flag",
                        "id"
                      ]
                    }
                  ],
                  "best_covering_index_scan": {
                    "index": "flag",
                    "cost": 136193,
                    "chosen": true
                  },
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_single_table"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "flag",
                        "ranges": [
                          "1 <= flag <= 1"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": true,
                        "rows": 1600,
                        "cost": 162.76,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "flag",
                      "rows": 1600,
                      "ranges": [
                        "1 <= flag <= 1"
                      ]
                    },
                    "rows_for_plan": 1600,
                    "cost_for_plan": 162.76,
                    "chosen": true
                  }
                }
              },
              {
                "table": "`flag_test_h2` `h2`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 1352657,
                    "cost": 146896
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "flag",
                      "usable": true,
                      "key_parts": [
                        "flag",
                        "id"
                      ]
                    }
                  ],
                  "best_covering_index_scan": {
                    "index": "flag",
                    "cost": 136746,
                    "chosen": true
                  },
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_single_table"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "flag",
                        "ranges": [
                          "1 <= flag <= 1"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": true,
                        "rows": 3600,
                        "cost": 364.94,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "flag",
                      "rows": 3600,
                      "ranges": [
                        "1 <= flag <= 1"
                      ]
                    },
                    "rows_for_plan": 3600,
                    "cost_for_plan": 364.94,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`flag_test_h` `h`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "PRIMARY",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "access_type": "ref",
                      "index": "flag",
                      "rows": 1600,
                      "cost": 162.75,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "range_details": {
                        "used_index": "flag"
                      },
                      "chosen": false,
                      "cause": "heuristic_index_cheaper"
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 1600,
                "cost_for_plan": 162.75,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`flag_test_h` `h`"
                    ],
                    "table": "`flag_test_h2` `h2`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "eq_ref",
                          "index": "PRIMARY",
                          "rows": 1,
                          "cost": 1760,
                          "chosen": true,
                          "cause": "clustered_pk_chosen_by_heuristics"
                        },
                        {
                          "rows_to_scan": 3600,
                          "filtering_effect": [
                          ],
                          "final_filtering_effect": 1,
                          "access_type": "range",
                          "range_details": {
                            "used_index": "flag"
                          },
                          "resulting_rows": 3600,
                          "cost": 1.16e6,
                          "chosen": false
                        }
                      ]
                    },
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 1600,
                    "cost_for_plan": 1922.8,
                    "chosen": true
                  }
                ]
              },
              {
                "plan_prefix": [
                ],
                "table": "`flag_test_h2` `h2`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "PRIMARY",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "access_type": "ref",
                      "index": "flag",
                      "rows": 3600,
                      "cost": 364.93,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "range_details": {
                        "used_index": "flag"
                      },
                      "chosen": false,
                      "cause": "heuristic_index_cheaper"
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 3600,
                "cost_for_plan": 364.93,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`flag_test_h2` `h2`"
                    ],
                    "table": "`flag_test_h` `h`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "eq_ref",
                          "index": "PRIMARY",
                          "rows": 1,
                          "cost": 3960,
                          "chosen": true,
                          "cause": "clustered_pk_chosen_by_heuristics"
                        },
                        {
                          "rows_to_scan": 1600,
                          "filtering_effect": [
                          ],
                          "final_filtering_effect": 1,
                          "access_type": "range",
                          "range_details": {
                            "used_index": "flag"
                          },
                          "resulting_rows": 1600,
                          "cost": 1.16e6,
                          "chosen": false
                        }
                      ]
                    },
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 3600,
                    "cost_for_plan": 4324.9,
                    "pruned_by_cost": true
                  }
                ]
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`h2`.`flag` = 1) and (`h`.`flag` = 1) and (`h2`.`id` = `h`.`id`))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`flag_test_h` `h`",
                  "attached": null
                },
                {
                  "table": "`flag_test_h2` `h2`",
                  "attached": "(`h2`.`flag` = 1)"
                }
              ]
            }
          },
          {
            "refine_plan": [
              {
                "table": "`flag_test_h` `h`"
              },
              {
                "table": "`flag_test_h2` `h2`"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

4-2. 「flag_test_h」テーブルがバッファプールに載っているケース

「flag_test_h」テーブルがバッファプールに載っているケース
mysql> SELECT * FROM information_schema.optimizer_trace\G
*************************** 1. row ***************************
                            QUERY: SELECT h.id FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=1 AND h2.flag=1
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `h`.`id` AS `id` from `flag_test_h` `h` join `flag_test_h2` `h2` where ((`h`.`id` = `h2`.`id`) and (`h`.`flag` = 1) and (`h2`.`flag` = 1))"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`h`.`id` = `h2`.`id`) and (`h`.`flag` = 1) and (`h2`.`flag` = 1))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(multiple equal(`h`.`id`, `h2`.`id`) and multiple equal(1, `h`.`flag`) and multiple equal(1, `h2`.`flag`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(multiple equal(`h`.`id`, `h2`.`id`) and multiple equal(1, `h`.`flag`) and multiple equal(1, `h2`.`flag`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(multiple equal(`h`.`id`, `h2`.`id`) and multiple equal(1, `h`.`flag`) and multiple equal(1, `h2`.`flag`))"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`flag_test_h` `h`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              },
              {
                "table": "`flag_test_h2` `h2`",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`flag_test_h` `h`",
                "field": "id",
                "equals": "`h2`.`id`",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h` `h`",
                "field": "flag",
                "equals": "1",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h` `h`",
                "field": "id",
                "equals": "`h2`.`id`",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h2` `h2`",
                "field": "id",
                "equals": "`h`.`id`",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h2` `h2`",
                "field": "flag",
                "equals": "1",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h2` `h2`",
                "field": "id",
                "equals": "`h`.`id`",
                "null_rejecting": false
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`flag_test_h` `h`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 1347159,
                    "cost": 137609
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "flag",
                      "usable": true,
                      "key_parts": [
                        "flag",
                        "id"
                      ]
                    }
                  ],
                  "best_covering_index_scan": {
                    "index": "flag",
                    "cost": 136193,
                    "chosen": true
                  },
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_single_table"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "flag",
                        "ranges": [
                          "1 <= flag <= 1"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": true,
                        "rows": 1600,
                        "cost": 162.76,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "flag",
                      "rows": 1600,
                      "ranges": [
                        "1 <= flag <= 1"
                      ]
                    },
                    "rows_for_plan": 1600,
                    "cost_for_plan": 162.76,
                    "chosen": true
                  }
                }
              },
              {
                "table": "`flag_test_h2` `h2`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 1352657,
                    "cost": 146896
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "flag",
                      "usable": true,
                      "key_parts": [
                        "flag",
                        "id"
                      ]
                    }
                  ],
                  "best_covering_index_scan": {
                    "index": "flag",
                    "cost": 136746,
                    "chosen": true
                  },
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_single_table"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "flag",
                        "ranges": [
                          "1 <= flag <= 1"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": true,
                        "rows": 3600,
                        "cost": 364.94,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "flag",
                      "rows": 3600,
                      "ranges": [
                        "1 <= flag <= 1"
                      ]
                    },
                    "rows_for_plan": 3600,
                    "cost_for_plan": 364.94,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`flag_test_h` `h`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "PRIMARY",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "access_type": "ref",
                      "index": "flag",
                      "rows": 1600,
                      "cost": 162.75,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "range_details": {
                        "used_index": "flag"
                      },
                      "chosen": false,
                      "cause": "heuristic_index_cheaper"
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 1600,
                "cost_for_plan": 162.75,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`flag_test_h` `h`"
                    ],
                    "table": "`flag_test_h2` `h2`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "eq_ref",
                          "index": "PRIMARY",
                          "rows": 1,
                          "cost": 1760,
                          "chosen": true,
                          "cause": "clustered_pk_chosen_by_heuristics"
                        },
                        {
                          "rows_to_scan": 3600,
                          "filtering_effect": [
                          ],
                          "final_filtering_effect": 1,
                          "access_type": "range",
                          "range_details": {
                            "used_index": "flag"
                          },
                          "resulting_rows": 3600,
                          "cost": 1.16e6,
                          "chosen": false
                        }
                      ]
                    },
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 1600,
                    "cost_for_plan": 1922.8,
                    "chosen": true
                  }
                ]
              },
              {
                "plan_prefix": [
                ],
                "table": "`flag_test_h2` `h2`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "PRIMARY",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "access_type": "ref",
                      "index": "flag",
                      "rows": 3600,
                      "cost": 364.93,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "range_details": {
                        "used_index": "flag"
                      },
                      "chosen": false,
                      "cause": "heuristic_index_cheaper"
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 3600,
                "cost_for_plan": 364.93,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`flag_test_h2` `h2`"
                    ],
                    "table": "`flag_test_h` `h`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "eq_ref",
                          "index": "PRIMARY",
                          "rows": 1,
                          "cost": 1260,
                          "chosen": true,
                          "cause": "clustered_pk_chosen_by_heuristics"
                        },
                        {
                          "rows_to_scan": 1600,
                          "filtering_effect": [
                          ],
                          "final_filtering_effect": 1,
                          "access_type": "range",
                          "range_details": {
                            "used_index": "flag"
                          },
                          "resulting_rows": 1600,
                          "cost": 1.16e6,
                          "chosen": false
                        }
                      ]
                    },
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 3600,
                    "cost_for_plan": 1624.9,
                    "chosen": true
                  }
                ]
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`h2`.`flag` = 1) and (`h`.`flag` = 1) and (`h`.`id` = `h2`.`id`))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`flag_test_h2` `h2`",
                  "attached": null
                },
                {
                  "table": "`flag_test_h` `h`",
                  "attached": "(`h`.`flag` = 1)"
                }
              ]
            }
          },
          {
            "refine_plan": [
              {
                "table": "`flag_test_h2` `h2`"
              },
              {
                "table": "`flag_test_h` `h`"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

4-3. コスト係数を変えたケース

コスト係数を変えたケース
mysql> SELECT * FROM information_schema.optimizer_trace\G
*************************** 1. row ***************************
                            QUERY: SELECT h.id FROM flag_test_h h, flag_test_h2 h2 WHERE h.id=h2.id AND h.flag=1 AND h2.flag=1
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `h`.`id` AS `id` from `flag_test_h` `h` join `flag_test_h2` `h2` where ((`h`.`id` = `h2`.`id`) and (`h`.`flag` = 1) and (`h2`.`flag` = 1))"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`h`.`id` = `h2`.`id`) and (`h`.`flag` = 1) and (`h2`.`flag` = 1))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(multiple equal(`h`.`id`, `h2`.`id`) and multiple equal(1, `h`.`flag`) and multiple equal(1, `h2`.`flag`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(multiple equal(`h`.`id`, `h2`.`id`) and multiple equal(1, `h`.`flag`) and multiple equal(1, `h2`.`flag`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(multiple equal(`h`.`id`, `h2`.`id`) and multiple equal(1, `h`.`flag`) and multiple equal(1, `h2`.`flag`))"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`flag_test_h` `h`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              },
              {
                "table": "`flag_test_h2` `h2`",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`flag_test_h` `h`",
                "field": "id",
                "equals": "`h2`.`id`",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h` `h`",
                "field": "flag",
                "equals": "1",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h` `h`",
                "field": "id",
                "equals": "`h2`.`id`",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h2` `h2`",
                "field": "id",
                "equals": "`h`.`id`",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h2` `h2`",
                "field": "flag",
                "equals": "1",
                "null_rejecting": false
              },
              {
                "table": "`flag_test_h2` `h2`",
                "field": "id",
                "equals": "`h`.`id`",
                "null_rejecting": false
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`flag_test_h` `h`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 1347159,
                    "cost": 180974
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "flag",
                      "usable": true,
                      "key_parts": [
                        "flag",
                        "id"
                      ]
                    }
                  ],
                  "best_covering_index_scan": {
                    "index": "flag",
                    "cost": 136207,
                    "chosen": true
                  },
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_single_table"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "flag",
                        "ranges": [
                          "1 <= flag <= 1"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": true,
                        "rows": 1600,
                        "cost": 162.79,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "flag",
                      "rows": 1600,
                      "ranges": [
                        "1 <= flag <= 1"
                      ]
                    },
                    "rows_for_plan": 1600,
                    "cost_for_plan": 162.79,
                    "chosen": true
                  }
                }
              },
              {
                "table": "`flag_test_h2` `h2`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 1352657,
                    "cost": 146896
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "flag",
                      "usable": true,
                      "key_parts": [
                        "flag",
                        "id"
                      ]
                    }
                  ],
                  "best_covering_index_scan": {
                    "index": "flag",
                    "cost": 136773,
                    "chosen": true
                  },
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_single_table"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "flag",
                        "ranges": [
                          "1 <= flag <= 1"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": true,
                        "rows": 3600,
                        "cost": 365.03,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "flag",
                      "rows": 3600,
                      "ranges": [
                        "1 <= flag <= 1"
                      ]
                    },
                    "rows_for_plan": 3600,
                    "cost_for_plan": 365.03,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`flag_test_h` `h`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "PRIMARY",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "access_type": "ref",
                      "index": "flag",
                      "rows": 1600,
                      "cost": 162.78,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "range_details": {
                        "used_index": "flag"
                      },
                      "chosen": false,
                      "cause": "heuristic_index_cheaper"
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 1600,
                "cost_for_plan": 162.78,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`flag_test_h` `h`"
                    ],
                    "table": "`flag_test_h2` `h2`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "eq_ref",
                          "index": "PRIMARY",
                          "rows": 1,
                          "cost": 1760,
                          "chosen": true,
                          "cause": "clustered_pk_chosen_by_heuristics"
                        },
                        {
                          "rows_to_scan": 3600,
                          "filtering_effect": [
                          ],
                          "final_filtering_effect": 1,
                          "access_type": "range",
                          "range_details": {
                            "used_index": "flag"
                          },
                          "resulting_rows": 3600,
                          "cost": 1.16e6,
                          "chosen": false
                        }
                      ]
                    },
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 1600,
                    "cost_for_plan": 1922.8,
                    "chosen": true
                  }
                ]
              },
              {
                "plan_prefix": [
                ],
                "table": "`flag_test_h2` `h2`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "PRIMARY",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "access_type": "ref",
                      "index": "flag",
                      "rows": 3600,
                      "cost": 365.02,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "range_details": {
                        "used_index": "flag"
                      },
                      "chosen": false,
                      "cause": "heuristic_index_cheaper"
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 3600,
                "cost_for_plan": 365.02,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`flag_test_h2` `h2`"
                    ],
                    "table": "`flag_test_h` `h`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "eq_ref",
                          "index": "PRIMARY",
                          "rows": 1,
                          "cost": 14760,
                          "chosen": true,
                          "cause": "clustered_pk_chosen_by_heuristics"
                        },
                        {
                          "rows_to_scan": 1600,
                          "filtering_effect": [
                          ],
                          "final_filtering_effect": 1,
                          "access_type": "range",
                          "range_details": {
                            "used_index": "flag"
                          },
                          "resulting_rows": 1600,
                          "cost": 1.16e6,
                          "chosen": false
                        }
                      ]
                    },
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 3600,
                    "cost_for_plan": 15125,
                    "pruned_by_cost": true
                  }
                ]
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`h2`.`flag` = 1) and (`h`.`flag` = 1) and (`h2`.`id` = `h`.`id`))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`flag_test_h` `h`",
                  "attached": null
                },
                {
                  "table": "`flag_test_h2` `h2`",
                  "attached": "(`h2`.`flag` = 1)"
                }
              ]
            }
          },
          {
            "refine_plan": [
              {
                "table": "`flag_test_h` `h`"
              },
              {
                "table": "`flag_test_h2` `h2`"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.01 sec)

4-4. 4-1. と 4-2. のdiff

計算されたコスト値の変化により、駆動表/内部表が入れ替わっているのが分かります。

1と2のdiff
$ diff opt_tr_1.txt opt_tr_2.txt
109c109
<                     "cost": 146282
---
>                     "cost": 137609
360c360
<                           "cost": 3960,
---
>                           "cost": 1260,
381,382c381,382
<                     "cost_for_plan": 4324.9,
<                     "pruned_by_cost": true
---
>                     "cost_for_plan": 1624.9,
>                     "chosen": true
390c390
<               "original_condition": "((`h2`.`flag` = 1) and (`h`.`flag` = 1) and (`h2`.`id` = `h`.`id`))",
---
>               "original_condition": "((`h2`.`flag` = 1) and (`h`.`flag` = 1) and (`h`.`id` = `h2`.`id`))",
395c395
<                   "table": "`flag_test_h` `h`",
---
>                   "table": "`flag_test_h2` `h2`",
399,400c399,400
<                   "table": "`flag_test_h2` `h2`",
<                   "attached": "(`h2`.`flag` = 1)"
---
>                   "table": "`flag_test_h` `h`",
>                   "attached": "(`h`.`flag` = 1)"
408c408
<                 "table": "`flag_test_h` `h`"
---
>                 "table": "`flag_test_h2` `h2`"
411c411
<                 "table": "`flag_test_h2` `h2`"
---
>                 "table": "`flag_test_h` `h`"

4-5. 4-1. と 4-3. のdiff

計算されたコスト値のみ変化しています。

1と3のdiff
$ diff opt_tr_1.txt opt_tr_3.txt
109c109
<                     "cost": 146282
---
>                     "cost": 180974
128c128
<                     "cost": 136193,
---
>                     "cost": 136207,
149c149
<                         "cost": 162.76,
---
>                         "cost": 162.79,
168c168
<                     "cost_for_plan": 162.76,
---
>                     "cost_for_plan": 162.79,
197c197
<                     "cost": 136746,
---
>                     "cost": 136773,
218c218
<                         "cost": 364.94,
---
>                         "cost": 365.03,
237c237
<                     "cost_for_plan": 364.94,
---
>                     "cost_for_plan": 365.03,
262c262
<                       "cost": 162.75,
---
>                       "cost": 162.78,
277c277
<                 "cost_for_plan": 162.75,
---
>                 "cost_for_plan": 162.78,
332c332
<                       "cost": 364.93,
---
>                       "cost": 365.02,
347c347
<                 "cost_for_plan": 364.93,
---
>                 "cost_for_plan": 365.02,
360c360
<                           "cost": 3960,
---
>                           "cost": 14760,
381c381
<                     "cost_for_plan": 4324.9,
---
>                     "cost_for_plan": 15125,

5. おっと、

書くのに時間が掛かって、Advent Calendarの自分の記事の公開日と被った…。


hmatsu47
名古屋で士業向けWebサービスのインフラ構築管理、たまにアプリケーション開発をやっています。 業務利用しているもの、個人研究など、気長にのんびり投稿していきます。ニッチ狙いが多めです。 IPA RISS(001158)・NW・DB/日商・大商2級コレクター?(簿記・ビジネス法務・ビジネス会計)。
https://hmatsu47.hatenablog.com/
infra-workshop
インフラ技術を勉強したい人たちのためのオンライン勉強会です
https://wp.infra-workshop.tech/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away