タイトルが何を言っているのか意味不明ですが、MySQL 8.0では（正確にはおそらくMySQL 8.0.1 DMRから）、オプティマイザのコスト計算で、
- ストレージからデータを読み取る場合
- バッファプールにあるデータを読み取る場合
のそれぞれの実行コストを区別し、デフォルトで後者（バッファプール読み取り）を低コストと見積もるようになりました。
ので、ちょっとだけ試してみます。
ネタ元はこちらです。
- MySQL 8.0: Query Optimizer Takes Data Buffering into Account（MySQL Server Blog）
- 8.9.5 The Optimizer Cost Model（MySQL 8.0 Reference Manual）
※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の自分の記事の公開日と被った…。