目次
とある日
複合インデックスを使うにはwhere句に書くカラムの順番を合わせないとダメとか無いですよねという話 - ser1zw's blog
↑これ読んで、「へぇー、そうなんだ」って初めて知った情報がありました。
ただ、自分も順番合わせないとダメだと思ってたので、なんでだろうなって考えたときに一つの答えが出てきたのでそれを書きます。
本記事を読む上で、上記記事を一読していただけるとわかりやすいかと思います。
概要
「複合インデックスを使うにはwhere句に書くカラムの順番をインデックスの定義順と合わせなければならんのじゃよ」って、個人的にも同じこと思ってたので、その理由で思いたるものを書く。
(colum_a, colum_b) みたいなインデックスの順番で colum_a を使わず where column_b = 'hoge'; だけだったりすると多くのDBでは当然インデックスが使われないけど、そういうことではなく。 ちなみにこういうケースでもOracleではINDEX SKIP SCANってやつでインデックスが使えるらしい。すげえな。
一応、本記事で書こうと思っていることをご本人はご存知だと思われます。
どういうこと
複合インデックスは、定義した順番でINDEXが使われる場合と使われない場合が存在します。
CREATE TABLE test ( id INT NOT NULL auto_increment, last_name CHAR(30) NOT NULL, first_name CHAR(30) NOT NULL, PRIMARY KEY (id), INDEX name (last_name,first_name) );
上記のように定義したINDEXの場合で以下SQLはINDEXが利用されます。
(定義したINDEXの列すべてを使った場合、定義したINDEXの左端のみを使った場合、)
SELECT * FROM test WHERE last_name='Jones' AND first_name='John'; (root@localhost) [test] 8.0.33 > explain SELECT * FROM test WHERE last_name='Jones' AND first_name='John'; +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+ | 1 | SIMPLE | test | NULL | ref | name | name | 240 | const,const | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) SELECT * FROM test WHERE last_name='Jones'; (root@localhost) [test] 8.0.33 > explain SELECT * FROM test WHERE last_name='Jones'; +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | test | NULL | ref | name | name | 120 | const | 4 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
ただ、以下のSQLの場合は実行されません。
(定義したINDEXの2番目の列だけを使った場合)
SELECT * FROM test WHERE first_name='John'; (root@localhost) [test] 8.0.33 > explain -> -> SELECT * FROM test WHERE first_name='John'; +----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+ | 1 | SIMPLE | test | NULL | index | name | name | 240 | NULL | 989337 | 10.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
もっと詳しく書くと、複合インデックスは左端のプレフィックスごとでINDEXを分割して使えます。
例えば、以下のようにINDEXを定義した場合。
(col1, col2, col3)
- INDEXが使われる場合
col1col1, col2col1, col2, col3
- INDEXが使われない場合
col2col2, col3col3
テーブルに複数列のインデックスがある場合、オプティマイザはインデックスの左端のプレフィックスを使用して行を検索できます。たとえば、 に 3 列のインデックスがある場合
(col1, col2, col3)、 、 、および に対するインデックス付き検索機能が(col1)あり(col1, col2)ます(col1, col2, col3)。 MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.3.5 複数列インデックス
上述した内容を踏まえて「INDEXが左端のプレフィックスから使用される」 ＝「SQLのWHERE句で順番が必要」と誤認したのではないでしょうか。
(念のため書きますが、上記は偽りでまったく関係ないです)
少なくとも自分がこういった誤認してました。
なので元記事の内容はとても勉強になりました。
というわけで試してみる
MySQL :: MySQL 8.0 リファレンスマニュアル :: 8.3.6 複数列インデックス
環境
(元記事はPostgreSQLもやってますがMySQLだけで試しました)
- MySQL 8.0.33
手順
- Dockerコンテナ起動
- テーブル作成＆データ挿入
- SQLを実行する
Dockerコンテナ起動
代々MySQLのマイナーバージョンを検証するためのdocker composeです。
db9: image: mysql:8.0.33-debian container_name: mysql_ver_8.0.33-debian environment: MYSQL_ROOT_PASSWORD: root MYSQL_DATABASE: test MYSQL_USER: docker MYSQL_PASSWORD: docker TZ: "Asia/Tokyo" command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci volumes: - ../../MySQL/settings/my.cnf:/etc/mysql/conf.d/my.cnf - ../../MySQL/settings/mylogin.sh:/root/mylogin.sh
テーブル作成＆データ挿入
以下テーブルに約1,000,000件のランダムデータを挿入して試します。
CREATE TABLE test ( id INT NOT NULL auto_increment, last_name CHAR(30) NOT NULL, first_name CHAR(30) NOT NULL, PRIMARY KEY (id), INDEX name (last_name,first_name) ); insert into test (last_name,first_name) select SUBSTR(MD5(RAND()),1,30),SUBSTR(MD5(RAND()),1,30) from test_limit; analyze table test;
(test_limitは適当に行数がいっぱいあったテーブルです)
あと、実行するSQLにヒットするデータも入れます。
(root@localhost) [test] 8.0.33 > insert into test (last_name,first_name) values('Jones','John'); Query OK, 1 row affected (0.02 sec) (root@localhost) [test] 8.0.33 > insert into test (last_name,first_name) values('Jones','Jon'); Query OK, 1 row affected (0.01 sec) (root@localhost) [test] 8.0.33 > insert into test (last_name,first_name) values('Jones','M'); Query OK, 1 row affected (0.01 sec) (root@localhost) [test] 8.0.33 > insert into test (last_name,first_name) values('Jones','N'); Query OK, 1 row affected (0.01 sec)
諸々の情報。
(きれいにデータ入れるとOrder byつけなくても適当にid順でソートされますが、大量に適当データをいれるとOrder byの重要性を感じました)
(root@localhost) [test] 8.0.33 > select count(*) from test; +----------+ | count(*) | +----------+ | 994688 | +----------+ 1 row in set (1.78 sec) (root@localhost) [test] 8.0.33 > show index from test; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | test | 0 | PRIMARY | 1 | id | A | 989333 | NULL | NULL | | BTREE | | | YES | NULL | | test | 1 | name | 1 | last_name | A | 961320 | NULL | NULL | | BTREE | | | YES | NULL | | test | 1 | name | 2 | first_name | A | 989333 | NULL | NULL | | BTREE | | | YES | NULL | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 3 rows in set (0.01 sec) (root@localhost) [test] 8.0.33 > TABLE test limit 10; +--------+--------------------------------+--------------------------------+ | id | last_name | first_name | +--------+--------------------------------+--------------------------------+ | 902452 | 00001aeb7a80c048ef6ac72cfe6bf5 | ce3694223bdabd98ef40b96bd593ae | | 145185 | 00001b22c84e58268a77ddadda8a84 | 3a8abd34a6aac2a2e0c8c7aa1ba62b | | 188429 | 00001dcc927b3443d44154a98f02f0 | 543def531f570dc03ab85192217f20 | | 833751 | 00002ef6441b217d0087408d244134 | 60c94706044cd5c3917ccef02f3b08 | | 894367 | 00004feaa8b104c3fab107952484a6 | 7f989183c22b4d494877cbbb3b799a | | 386703 | 00007222c2ae7c7a6fc6e36242a6d2 | c1181faffa5308b22fa6d78a361764 | | 899086 | 000089d81aa601a935af33a254371e | fef0aa8155af6a9d24f8259e751014 | | 484834 | 0000a19d3b3bb76cef2079b1fbcdfe | 6a799f98f51bb18ddb1acbe4d80ddc | | 73415 | 0000a4b3bce2b8eb7aec56d3699613 | 2c38b462de2ec64cd5cc5d9079747a | | 302075 | 0000b13d8820d0506c275cd8e29b5c | 5212c16f39f3d56a776b0522ab82d7 | +--------+--------------------------------+--------------------------------+ 10 rows in set (0.00 sec)
SQLを実行する
INDEXが使用されるSQL
(root@localhost) [test] 8.0.33 > explain SELECT * FROM test WHERE last_name='Jones'; +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | test | NULL | ref | name | name | 120 | const | 4 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) (root@localhost) [test] 8.0.33 > explain SELECT * FROM test WHERE last_name='Jones' AND first_name='John'; +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+ | 1 | SIMPLE | test | NULL | ref | name | name | 240 | const,const | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) (root@localhost) [test] 8.0.33 > explain SELECT * FROM test WHERE last_name='Jones' AND (first_name='John' OR first_name='Jon'); +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | test | NULL | range | name | name | 240 | NULL | 2 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) (root@localhost) [test] 8.0.33 > explain SELECT * FROM test WHERE last_name='Jones' AND first_name >='M' AND first_name < 'N'; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | test | NULL | range | name | name | 240 | NULL | 1 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
INDEXが使用されないSQL
(root@localhost) [test] 8.0.33 > explain -> -> SELECT * FROM test WHERE first_name='John'; +----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+ | 1 | SIMPLE | test | NULL | index | name | name | 240 | NULL | 989337 | 10.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) (root@localhost) [test] 8.0.33 > explain SELECT * FROM test WHERE first_name='John'; +----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+ | 1 | SIMPLE | test | NULL | index | name | name | 240 | NULL | 989337 | 10.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) (root@localhost) [test] 8.0.33 > explain SELECT * FROM test WHERE last_name='Jones' OR first_name='John'; +----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+ | 1 | SIMPLE | test | NULL | index | name | name | 240 | NULL | 989337 | 19.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
オプティマイザトレースの結果
今回の検証結果のついでに、複数INDEXの列を入れ替えたオプティマイザトレースを見てみたので結果貼り付けます。
元記事の検証結果と同じですが、順番変わっても関係ないみたいですね。
オプティマイザトレースの結果もほぼ同じでした。
比較対象は以下のとおりです。
explain SELECT * FROM test WHERE last_name='Jones' AND first_name='John'; explain SELECT * FROM test WHERE first_name='John'AND last_name='Jones'; (root@localhost) [test] 8.0.33 > explain SELECT * FROM test -> WHERE last_name='Jones' AND first_name='John'; +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+ | 1 | SIMPLE | test | NULL | ref | name | name | 240 | const,const | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) (root@localhost) [test] 8.0.33 > (root@localhost) [test] 8.0.33 > explain SELECT * FROM test -> WHERE first_name='John'AND last_name='Jones'; +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+ | 1 | SIMPLE | test | NULL | ref | name | name | 240 | const,const | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
INDEXの順番通り
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G; (root@localhost) [test] 8.0.33 > SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G; *************************** 1. row *************************** QUERY: explain SELECT * FROM test WHERE last_name='Jones' AND first_name='John' TRACE: { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `test`.`id` AS `id`,`test`.`last_name` AS `last_name`,`test`.`first_name` AS `first_name` from `test` where ((`test`.`last_name` = 'Jones') and (`test`.`first_name` = 'John'))" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`test`.`last_name` = 'Jones') and (`test`.`first_name` = 'John'))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`test`.`last_name` = 'Jones') and (`test`.`first_name` = 'John'))" }, { "transformation": "constant_propagation", "resulting_condition": "((`test`.`last_name` = 'Jones') and (`test`.`first_name` = 'John'))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`test`.`last_name` = 'Jones') and (`test`.`first_name` = 'John'))" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`test`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ { "table": "`test`", "field": "last_name", "equals": "'Jones'", "null_rejecting": true }, { "table": "`test`", "field": "first_name", "equals": "'John'", "null_rejecting": true } ] }, { "rows_estimation": [ { "table": "`test`", "range_analysis": { "table_scan": { "rows": 989337, "cost": 103621 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "name", "usable": true, "key_parts": [ "last_name", "first_name", "id" ] } ], "best_covering_index_scan": { "index": "name", "cost": 106208, "chosen": false, "cause": "cost" }, "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "name", "usable": false, "cause": "no_range_predicate" } ] }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "name", "ranges": [ "last_name = 'Jones' AND first_name = 'John'" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": true, "in_memory": 1, "rows": 1, "cost": 0.36, "chosen": true } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "name", "rows": 1, "ranges": [ "last_name = 'Jones' AND first_name = 'John'" ] }, "rows_for_plan": 1, "cost_for_plan": 0.36, "chosen": true } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`test`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "name", "rows": 1, "cost": 0.35, "chosen": true }, { "access_type": "range", "range_details": { "used_index": "name" }, "chosen": false, "cause": "heuristic_index_cheaper" } ] }, "condition_filtering_pct": 100, "rows_for_plan": 1, "cost_for_plan": 0.35, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "((`test`.`last_name` = 'Jones') and (`test`.`first_name` = 'John'))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`test`", "attached": "((`test`.`last_name` = 'Jones') and (`test`.`first_name` = 'John'))" } ] } }, { "finalizing_table_conditions": [ { "table": "`test`", "original_table_condition": "((`test`.`last_name` = 'Jones') and (`test`.`first_name` = 'John'))", "final_table_condition ": null } ] }, { "refine_plan": [ { "table": "`test`" } ] } ] } }, { "join_explain": { "select#": 1, "steps": [ ] } } ] } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.01 sec)
INDEXの順番とは逆
(root@localhost) [test] 8.0.33 > SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G; *************************** 1. row *************************** QUERY: explain SELECT * FROM test WHERE first_name='John'AND last_name='Jones' TRACE: { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `test`.`id` AS `id`,`test`.`last_name` AS `last_name`,`test`.`first_name` AS `first_name` from `test` where ((`test`.`first_name` = 'John') and (`test`.`last_name` = 'Jones'))" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`test`.`first_name` = 'John') and (`test`.`last_name` = 'Jones'))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`test`.`first_name` = 'John') and (`test`.`last_name` = 'Jones'))" }, { "transformation": "constant_propagation", "resulting_condition": "((`test`.`first_name` = 'John') and (`test`.`last_name` = 'Jones'))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`test`.`first_name` = 'John') and (`test`.`last_name` = 'Jones'))" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`test`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ { "table": "`test`", "field": "last_name", "equals": "'Jones'", "null_rejecting": true }, { "table": "`test`", "field": "first_name", "equals": "'John'", "null_rejecting": true } ] }, { "rows_estimation": [ { "table": "`test`", "range_analysis": { "table_scan": { "rows": 989337, "cost": 104389 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "name", "usable": true, "key_parts": [ "last_name", "first_name", "id" ] } ], "best_covering_index_scan": { "index": "name", "cost": 106208, "chosen": false, "cause": "cost" }, "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "name", "usable": false, "cause": "no_range_predicate" } ] }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "name", "ranges": [ "last_name = 'Jones' AND first_name = 'John'" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": true, "in_memory": 1, "rows": 1, "cost": 0.36, "chosen": true } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "name", "rows": 1, "ranges": [ "last_name = 'Jones' AND first_name = 'John'" ] }, "rows_for_plan": 1, "cost_for_plan": 0.36, "chosen": true } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`test`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "name", "rows": 1, "cost": 0.35, "chosen": true }, { "access_type": "range", "range_details": { "used_index": "name" }, "chosen": false, "cause": "heuristic_index_cheaper" } ] }, "condition_filtering_pct": 100, "rows_for_plan": 1, "cost_for_plan": 0.35, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "((`test`.`first_name` = 'John') and (`test`.`last_name` = 'Jones'))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`test`", "attached": "((`test`.`first_name` = 'John') and (`test`.`last_name` = 'Jones'))" } ] } }, { "finalizing_table_conditions": [ { "table": "`test`", "original_table_condition": "((`test`.`first_name` = 'John') and (`test`.`last_name` = 'Jones'))", "final_table_condition ": null } ] }, { "refine_plan": [ { "table": "`test`" } ] } ] } }, { "join_explain": { "select#": 1, "steps": [ ] } } ] } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.00 sec)
おまけ
複数列インデックスは並べ替えられた配列とみなすことができ、その行にはインデックス付き列の値を連結することによって作成された値が含まれます。
おそらくこの一行を読み解くと、元記事の疑問の回答になるのかなと思いました。
〆
検証してて、列数とINDEXの列数は一致しなくていいという発見もしました。
↓これもINDEX効くんだって初めて知りました。
(root@localhost) [test] 8.0.33 > explain SELECT * FROM test WHERE last_name='Jones' AND (first_name='John' OR first_name='Jon'); +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | test | NULL | range | name | name | 240 | NULL | 2 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) (root@localhost) [test] 8.0.33 > explain SELECT * FROM test WHERE last_name='Jones' AND first_name >='M' AND first_name < 'N'; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | test | NULL | range | name | name | 240 | NULL | 1 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
