MySQL(InnoDB)でカーディナリティの低いカラムにINDEXを張るの「おまけ」の続きです。
同じWHERE句を指定したのに、UPDATE文とSELECT文の間で結果が分かれました。
オプティマイザトレースを使って、もう少し詳しく調べてみます。
オプティマイザトレースとは
MySQL 5.6から実装された、「オプティマイザが実行計画を選択する過程を見ることができる機能」です。
EXPLAINでは、最終的に選択される実行計画のみが表示されますが、オプティマイザトレースでは、比較検討された実行計画の情報なども含めて表示されます。
オプティマイザトレースを使うには
以下の変数を設定するだけで、接続中のセッションでオプティマイザトレースが有効になります。
mysql> SET optimizer_trace='enabled=on';
Query OK, 0 rows affected (0.00 sec)
有効な状態でSQLを実行し、それに続いて
mysql> SELECT * FROM information_schema.optimizer_trace\G
とすることで、実行計画の情報が表示されます。
終わったら、
mysql> SET optimizer_trace='enabled=off';
で無効にします。
使ってみましょう
MySQL(InnoDB)でカーディナリティの低いカラムにINDEXを張るのときのテーブルで、「WHERE flag = 0」を付けたSELECT文と、UPDATE文を比較してみます（10,000件の中から10件を抽出）。
まずはEXPLAINのおさらいです。
mysql> USE cardi_test;
Database changed
mysql> EXPLAIN SELECT * FROM test_data WHERE flag = 0;
+----+-------------+-----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test_data | NULL | ref | idx_flag | idx_flag | 4 | const | 10 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN UPDATE test_data SET flag = 1 WHERE flag = 0;
+----+-------------+-----------+------------+-------+---------------+----------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+----------+---------+-------+------+----------+------------------------------+
| 1 | UPDATE | test_data | NULL | range | idx_flag | idx_flag | 4 | const | 10 | 100.00 | Using where; Using temporary |
+----+-------------+-----------+------------+-------+---------------+----------+---------+-------+------+----------+------------------------------+
1 row in set (0.00 sec)
…あらためて見比べてみると、「type」がSELECT文のとき「ref」（主に非ユニークINDEXで等価検索）、UPDATE文のとき「range」（主に範囲検索）、となっており、「rows」が同じ「10」でも違う判断になっていることがわかります。
SELECT文のオプティマイザトレースの情報を見てみます。
mysql> SELECT * FROM test_data WHERE flag = 0;
+-------+--------------+------+
| id | name | flag |
+-------+--------------+------+
| 9991 | shouhin09991 | 0 |
| 9992 | shouhin09992 | 0 |
| 9993 | shouhin09993 | 0 |
| 9994 | shouhin09994 | 0 |
| 9995 | shouhin09995 | 0 |
| 9996 | shouhin09996 | 0 |
| 9997 | shouhin09997 | 0 |
| 9998 | shouhin09998 | 0 |
| 9999 | shouhin09999 | 0 |
| 10000 | shouhin10000 | 0 |
+-------+--------------+------+
10 rows in set (0.00 sec)
mysql> SELECT * FROM information_schema.optimizer_trace\G
*************************** 1. row ***************************
QUERY: SELECT * FROM test_data WHERE flag = 0
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `test_data`.`id` AS `id`,`test_data`.`name` AS `name`,`test_data`.`flag` AS `flag` from `test_data` where (`test_data`.`flag` = 0)"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`test_data`.`flag` = 0)",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "multiple equal(0, `test_data`.`flag`)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "multiple equal(0, `test_data`.`flag`)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "multiple equal(0, `test_data`.`flag`)"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`test_data`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`test_data`",
"field": "flag",
"equals": "0",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "`test_data`",
"range_analysis": {
"table_scan": {
"rows": 10207,
"cost": 2074.5
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_flag",
"usable": true,
"key_parts": [
"flag",
"id"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_flag",
"ranges": [
"0 <= flag <= 0"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 10,
"cost": 13.01,
"chosen": true
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "idx_flag",
"rows": 10,
"ranges": [
"0 <= flag <= 0"
]
},
"rows_for_plan": 10,
"cost_for_plan": 13.01,
"chosen": true
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`test_data`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idx_flag",
"rows": 10,
"cost": 12,
"chosen": true
},
{
"access_type": "range",
"range_details": {
"used_index": "idx_flag"
},
"chosen": false,
"cause": "heuristic_index_cheaper"
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 10,
"cost_for_plan": 12,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`test_data`.`flag` = 0)",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`test_data`",
"attached": null
}
]
}
},
{
"refine_plan": [
{
"table": "`test_data`"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
…長いです（笑）。
"rows_estimation": [
{
"table": "`test_data`",
"range_analysis": {
"table_scan": {
"rows": 10207,
"cost": 2074.5
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_flag",
"usable": true,
"key_parts": [
"flag",
"id"
]
}
],
のところで、
・テーブルスキャンの場合の行数が「10207」、コストが「2074.5」と推定されていること
・「range」検索において、主キーのINDEXの使用が「not_applicable」（これを使うのは不適当？）と判定されていること
・一方、セカンダリINDEX「idx_flag」については「使えるのでは？」と推定されていること
がわかります。
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_flag",
"ranges": [
"0 <= flag <= 0"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 10,
"cost": 13.01,
"chosen": true
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "idx_flag",
"rows": 10,
"ranges": [
"0 <= flag <= 0"
]
},
"rows_for_plan": 10,
"cost_for_plan": 13.01,
"chosen": true
}
のところでは、「WHERE 0 <= flag AND flag <= 0」と読み替えた形のrange検索の行数が「10」、コストが「13.01」と推定されていることがわかります。
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`test_data`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idx_flag",
"rows": 10,
"cost": 12,
"chosen": true
},
{
"access_type": "range",
"range_details": {
"used_index": "idx_flag"
},
"chosen": false,
"cause": "heuristic_index_cheaper"
}
]
},
のところで、最適なアクセス経路が「idx_flag」を「ref」（等価）検索した場合の、行数「10」、コスト「12」であり、「"chosen": true」から、これが選択されていることがわかります（range絡みの別オブジェクトにも「"chosen": true」があるのでちょっと見分けがつきにくいのですが）。
では、続いてUPDATE文です。
mysql> UPDATE test_data SET flag = 1 WHERE flag = 0;
Query OK, 10 rows affected (0.00 sec)
Rows matched: 10 Changed: 10 Warnings: 0
mysql> SELECT * FROM information_schema.optimizer_trace\G
*************************** 1. row ***************************
QUERY: UPDATE test_data SET flag = 1 WHERE flag = 0
TRACE: {
"steps": [
{
"substitute_generated_columns": {
}
},
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`test_data`.`flag` = 0)",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "multiple equal(0, `test_data`.`flag`)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "multiple equal(0, `test_data`.`flag`)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "multiple equal(0, `test_data`.`flag`)"
}
]
}
},
{
"table": "`test_data`",
"range_analysis": {
"table_scan": {
"rows": 10207,
"cost": 2074.5
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": true,
"key_parts": [
"id"
]
},
{
"index": "idx_flag",
"usable": true,
"key_parts": [
"flag",
"id"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "no_join"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_flag",
"ranges": [
"0 <= flag <= 0"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 10,
"cost": 13.01,
"chosen": true
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "idx_flag",
"rows": 10,
"ranges": [
"0 <= flag <= 0"
]
},
"rows_for_plan": 10,
"cost_for_plan": 13.01,
"chosen": true
}
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
相変わらず長いのですが、SELECT文のときより、ちょっと短いですね。
・「range」検索に主キーのINDEXも（「idx_flag」と同様に）「使える？」と推定されている
・「ref」についての情報が出てきていない
ということで、最終的に「range」（範囲）検索扱いになっていることがわかります。
さて、そろそろ辛くなってきたと思いますが（笑）、「10,000件から9,990件を抽出する」ほう（「WHERE flag = 1」）です。
mysql> EXPLAIN SELECT * FROM test_data WHERE flag = 1;
+----+-------------+-----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test_data | NULL | ref | idx_flag | idx_flag | 4 | const | 5103 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN UPDATE test_data SET flag = 0 WHERE flag = 1;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+-------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+-------+----------+------------------------------+
| 1 | UPDATE | test_data | NULL | index | idx_flag | PRIMARY | 8 | NULL | 10207 | 100.00 | Using where; Using temporary |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+-------+----------+------------------------------+
1 row in set (0.00 sec)
「ref」（10件抽出のときと同じ）と「index」に結果が分かれています。
SELECT文では、
mysql> SELECT * FROM test_data WHERE flag = 1;
+------+--------------+------+
| id | name | flag |
+------+--------------+------+
| 1 | shouhin00001 | 1 |
| 2 | shouhin00002 | 1 |
| 3 | shouhin00003 | 1 |
| 4 | shouhin00004 | 1 |
| 5 | shouhin00005 | 1 |
（中略）
| 9986 | shouhin09986 | 1 |
| 9987 | shouhin09987 | 1 |
| 9988 | shouhin09988 | 1 |
| 9989 | shouhin09989 | 1 |
| 9990 | shouhin09990 | 1 |
+------+--------------+------+
9990 rows in set (0.01 sec)
mysql> SELECT * FROM information_schema.optimizer_trace\G
*************************** 1. row ***************************
QUERY: SELECT * FROM test_data WHERE flag = 1
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `test_data`.`id` AS `id`,`test_data`.`name` AS `name`,`test_data`.`flag` AS `flag` from `test_data` where (`test_data`.`flag` = 1)"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`test_data`.`flag` = 1)",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "multiple equal(1, `test_data`.`flag`)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "multiple equal(1, `test_data`.`flag`)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "multiple equal(1, `test_data`.`flag`)"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`test_data`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`test_data`",
"field": "flag",
"equals": "1",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "`test_data`",
"range_analysis": {
"table_scan": {
"rows": 10207,
"cost": 2074.5
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_flag",
"usable": true,
"key_parts": [
"flag",
"id"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_flag",
"ranges": [
"1 <= flag <= 1"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 5103,
"cost": 6124.6,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`test_data`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idx_flag",
"rows": 5103,
"cost": 1113.6,
"chosen": true
},
{
"rows_to_scan": 10207,
"access_type": "scan",
"resulting_rows": 10207,
"cost": 2072.4,
"chosen": false
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 5103,
"cost_for_plan": 1113.6,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`test_data`.`flag` = 1)",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`test_data`",
"attached": null
}
]
}
},
{
"refine_plan": [
{
"table": "`test_data`"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
「ref」と「range」で推定されている行数は同じ「5103」（おそらくINDEXの走査をあきらめて、統計情報から拾ってきた数字でしょう）ですが、推定コストが「1113.6」と「6124.6」で、かなり差があります。
「ref」の「1113.6」という数字は、テーブルスキャン時の「2072.4」より小さいため、最終的に「ref」が選ばれたようです（推定された「5103」という行数がかなり実態とかけ離れているために、結果として選択ミスのようになってしまっていますが）。
次はUPDATE文、ようやく最後です。
mysql> UPDATE test_data SET flag = 0 WHERE flag = 1;
Query OK, 9990 rows affected (0.23 sec)
Rows matched: 9990 Changed: 9990 Warnings: 0
mysql> UPDATE test_data SET flag = 0 WHERE flag = 1;
Query OK, 9990 rows affected (0.23 sec)
Rows matched: 9990 Changed: 9990 Warnings: 0
mysql> SELECT * FROM information_schema.optimizer_trace\G
*************************** 1. row ***************************
QUERY: UPDATE test_data SET flag = 0 WHERE flag = 1
TRACE: {
"steps": [
{
"substitute_generated_columns": {
}
},
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`test_data`.`flag` = 1)",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "multiple equal(1, `test_data`.`flag`)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "multiple equal(1, `test_data`.`flag`)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "multiple equal(1, `test_data`.`flag`)"
}
]
}
},
{
"table": "`test_data`",
"range_analysis": {
"table_scan": {
"rows": 10207,
"cost": 2074.5
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": true,
"key_parts": [
"id"
]
},
{
"index": "idx_flag",
"usable": true,
"key_parts": [
"flag",
"id"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "no_join"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_flag",
"ranges": [
"1 <= flag <= 1"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 5103,
"cost": 6124.6,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
}
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
UPDATE文で10件抽出のときと同様、「ref」について検討していないようです。
結果、「range」よりも「index」（INDEXフルスキャン）のほうがコストが低いと推定され、選択されたのではないでしょうか？
UPDATE文の場合はSELECTと比べて細かい情報が出ていないようなので、もしかしたら間違っているかもしれませんが。
最後に
漢のコンピュータ道でおなじみ、奥野幹也さんの著書『詳解MySQL5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド』が発売されています。
オプティマイザトレースについても、JOINの場合を例に、もっと詳しい解説が載っていますので、興味がある方は是非読んでみてください。
といっても、私は翔泳社・Oracle・奥野さんとは何の関係もありませんが。
あ、そういえば、奥野さんには、以前オープンソースカンファレンスのブースでお会いしたときに、サービス精神旺盛な感じで、色々お話をしていただいたことはありました。
【おまけ】
MySQL 5.7関連投稿記事へのリンクを集めました。