Yakstは、海外の役立つブログ記事などを人力で翻訳して公開するプロジェクトです。
4年弱前投稿 修正あり

MySQL 5.6での、マルチカラムインデックスとカラムごとのインデックスの比較

MySQL Performance Blogの翻訳。複数のカラムを指定したマルチカラムインデックスを使うべきか、カラムごとに別々にインデックスを作るべきかは悩ましい問題だ。しかし、MySQL 5.6で導入されたIndex condition pushdownの仕組みを理解すれば、マルチカラムインデックスを効率的に使うことができるようになる。

原文
Multiple column index vs multiple indexes with MySQL 5.6 (English)
原文ライセンス
CC BY-NC-SA
翻訳依頼者
D98ee74ffe0fafbdc83b23907dda3665
翻訳者
D98ee74ffe0fafbdc83b23907dda3665 doublemarket
原著者への翻訳報告
未報告


インデックスに関する話をしている時によく出てくる質問と言えば…マルチカラムインデックスを使うべきか、カラムそれぞれにインデックスを張るべきか、ということだ。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によって、マルチカラムインデックスがより効率的になっている。

ただし、オプティマイザが完璧でないことは忘れないように。この機能を有効に使うには、インデックスに関するヒントを使う必要があるかもしれない。

次の記事
Linuxカーネルハッカーになる4つの方法
前の記事
MySQLを使ったアプリケーションを作るエンジニアが知るべきMySQLの内部構造とは?

Feed small 記事フィード