インデックスに関する話をしている時によく出てくる質問と言えば…マルチカラムインデックスを使うべきか、カラムそれぞれにインデックスを張るべきか、ということだ。Peter Zaitevがこれについて2008年に書いていて、その時の結論としては、マルチカラムインデックスが多くの場合においてベストな解決策だ、というこだった。しかし、最近のオプティマイザの進化によって、MySQL 5.6では事情は違ってきてはいないだろうか？
準備
テストのため、以下のような2つのテーブルを使う。
CREATE TABLE t1000merge (
id int not null auto_increment primary key,
i int(11) NOT NULL,
j int(11) NOT NULL,
val char(10) NOT NULL,
KEY i (i),
KEY j (j)
) ENGINE=InnoDB;
CREATE TABLE t1000idx2 (
id int not null auto_increment primary key,
i int(11) NOT NULL,
j int(11) NOT NULL,
val char(10) NOT NULL,
KEY ij (i,j)
) ENGINE=InnoDB;
これらのテーブルには、100万行のデータを入れてあり、iとjは1000の一意で互いに独立な値を含んでいる。バッファプールは、全てのデータとインデックスが入るのに十分な大きさだ。
以下のクエリについて、MySQL 5.5.35とMySQL 5.6.15でそれぞれ実行したものを見ていこう。
SELECT sum(length(val)) FROM T WHERE j=2 AND i BETWEEN 100 and 200
どうしてここでこのクエリなのか？
MySQL 5.5では、テーブルt1000idx2に関して、オプティマイザは(i,j)のインデックスは選択するに値しないと判断してしまい、フルテーブルスキャンが発生してしまう。テーブルt1000mergeに関しては、(j)のインデックスがフィルタするために明らかによいと判断する。
従って、t1000idx2では実行に0.45秒かかるが、t1000mergeでは0.01秒とよいレスポンスになる。
MySQL 5.6では、このクエリはindex condition pushdown(ICP)の対象とされるため、t1000idx2のレスポンスが改善することが期待できる。
ICP: FORCE INDEXを使う
ところが残念ながら、オプティマイザはフルテーブルスキャンを選んでしまい、相変わらずレスポンスが悪くなってしまう。
mysql5.6> EXPLAIN SELECT sum(length(val)) FROM t1000idx2 WHERE j=2 AND i BETWEEN 100 and 200;
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | t1000idx2 | ALL | ij | NULL | NULL | NULL | 1000545 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
FORCE INDEXを使った場合はどうだろうか？
mysql5.6 > EXPLAIN SELECT sum(length(val)) FROM t1000idx2 FORCE INDEX(ij) WHERE j=2 AND i BETWEEN 100 and 200;
+----+-------------+-----------+-------+---------------+------+---------+------+--------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+------+---------+------+--------+-----------------------+
| 1 | SIMPLE | t1000idx2 | range | ij | ij | 8 | NULL | 188460 | Using index condition |
+----+-------------+-----------+-------+---------------+------+---------+------+--------+-----------------------+
今度はICPが使われた(Extra列に「Using index condition」がある)！
レスポンスタイムの違いははっきりしている。
- FORCE INDEXなし(フルテーブルスキャン) : 0.45秒
- FORCE INDEXあり(マルチカラムインデックス+index condition pushdown) : 0.04秒 10倍高速化！
更なる考察
このシンプルなクエリに対して、オプティマイザがベストな実行計画を立てるのに失敗しているのは興味深いことだ。オプティマイザトレースを実行することで、理由が明らかになる。
mysql> SET optimizer_trace="enabled=on";
mysql> SELECT sum(length(val)) FROM T WHERE j=2 AND i BETWEEN 100 and 200;
mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
[...]
"range_analysis": {
"table_scan": {
"rows": 1000545,
"cost": 202835
},
上の結果が、フルテーブルスキャンのコストだ。
一方で、(i,j)のインデックスを使ったスキャンのコストについてオプティマイザがどう予測しているかが以下の部分だ。
[...]
"range_scan_alternatives": [
{
"index": "ij",
"ranges": [
"100 <= i <= 200"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 188460,
"cost": 226153,
"chosen": false,
"cause": "cost"
}
]
[...]
この段階では、オプティマイザはまだICPが使えるかどうかが分かっていない。レンジスキャンのコストが多く見積もられているのはこの製だと思われる。
FORCE INDEXヒントを使ったクエリのオプティマイザトレースを見てみると、レンジスキャンが選択された直後にICPの使用が判断されている。
[...]
"refine_plan": [
{
"table": "`t1000idx2` FORCE INDEX (`ij`)",
"pushed_index_condition": "((`t1000idx2`.`j` = 2) and (`t1000idx2`.`i` between 100 and 200))",
"table_condition_attached": null,
"access_type": "range"
}
[...]
結論
マルチカラムインデックスか、カラムごとのインデックスか？カラムごとにインデックスを張ると、オプティマイザがindex_mergeタイプのアクセスを行うことにつながり、これは一般的にはマルチカラムインデックスにアクセスするよりよくないものだ。MySQL 5.6では、index condition pushdownによって、マルチカラムインデックスがより効率的になっている。
ただし、オプティマイザが完璧でないことは忘れないように。この機能を有効に使うには、インデックスに関するヒントを使う必要があるかもしれない。
