いつものように気になったので意訳
これら書く問題というのはMySQLの最初の方にある問題なんだが、気をつけてクエリーを解析しないと知らないまま終わってしまうことだ。phpDay.it でこの落とし穴についてデモしてきたが、ブログの記事にするのもいいとおもったんだ。
問題をデモするための典型的な例として、売上げクエリーを考えよう。データは次の3つのテーブルに入っている。
CREATE TABLE `products` (
`prd_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`prd_name` varchar(32) NOT NULL,
PRIMARY KEY (`prd_id`),
KEY `name` (`prd_name`)
)
CREATE TABLE `tags` (
`tag_prd_id` int(10) UNSIGNED NOT NULL,
`tag_name` varchar(32) NOT NULL,
PRIMARY KEY (`tag_name`, `tag_prd_id`)
)
CREATE TABLE `items_ordered` (
`itm_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`itm_prd_id` int(10) UNSIGNED NOT NULL,
`itm_order_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`itm_id`),
KEY `itm_prd_id__and__itm_order_timestamp` (`itm_prd_id`,`itm_order_timestamp`)
)
そして十分な量のデータを入れた。
そして次のクエリーを考える。昨日にどれだけ液晶テレビが売れたのかを調べるクエリーだ。
SELECT COUNT(1)
FROM tags t
JOIN products p
ON p.prd_id = t.tag_prd_id
JOIN items_ordered i
ON i.itm_prd_id = p.prd_id
WHERE t.tag_name = 'lcd'
AND i.itm_order_timestamp>= '2010-05-16 00:00:00'
AND i.itm_order_timestamp <'2010-05-17 00:00:00'
+----------+
| COUNT(1) |
+----------+
| 4103 |
+----------+
ちゃんとできたかな！
データ構造を見る限り、うまくいったように見える。tagsテーブルにはインデックスtag_nameがあり、items_orderedには(itm_prd_id, itm_order_timestamp)などが結合時に使われている。ではさらに詳細に見てみるとどうだろう。
SHOW STATUS LIKE 'Handler_read%';
+-----------------------+--------+
| Variable_name | Value |
+-----------------------+--------+
| Handler_read_first | 0 |
| Handler_read_key | 3 |
| Handler_read_next | 118181 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+--------+
ちょっとよくないように見える。結果が4103行なのに、120000行もスキャンしている。必要なカラムにはちゃんとインデックスつけているのに！EXPLAINはどうなっているのか。
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 98
ref: const
rows: 1
Extra: Using where; Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: p
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: example_db.t.tag_prd_id
rows: 1
Extra: Using index
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: i
type: ref
possible_keys: itm_prd_id__and__itm_order_timestamp
key: itm_prd_id__and__itm_order_timestamp
key_len: 4
ref: example_db.p.prd_id
rows: 10325
Extra: Using where; Using index
ここでちょっと思い出してみる。
`itm_prd_id` int(10) UNSIGNED NOT NULL
`itm_order_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
KEY `itm_prd_id__and__itm_order_timestamp` (`itm_prd_id`,`itm_order_timestamp`)
EXPLAINの3行目のkey_lenは4バイトなのだが、(itm_prd_id, itm_order_timestamp)は4+4で8バイトあるはずだ。と言うことは、1つのカラムしか見ていないことになる。
これをどう理解すべきか。データベースはlcdとタグの付いたソートされたデータを全て読み込む。それはやく120000行で、そのあと日付で絞り込まれる。なんと非効率な方法だ！MySQLは製品IDと日付範囲をインデックスでマッチして関連する行を読み込むだけな単純な最適化もできない。
これは結合するときだけ影響する。範囲条件を最初のテーブル(そしてそれのみ)に限定すれば、期待通りに動く。
EXPLAIN
SELECT COUNT(1)
FROM items_ordered i
WHERE i.itm_prd_id = 5
AND i.itm_order_timestamp>= '2010-05-16 00:00:00'
AND i.itm_order_timestamp <'2010-05-17 00:00:00'
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
TABLE: i
type: range
possible_keys: itm_prd_id__and__itm_order_timestamp
KEY: itm_prd_id__and__itm_order_timestamp
key_len: 8
ref: NULL
rows: 1306
Extra: USING WHERE; USING INDEX
この場合、結合しないのでMySQLはrefとは表示せず、key_lenも8バイトとなっている。つまりちゃんとインデックスが利用されていると言うことだ。
この問題に対しては多くの回避策があるが、基本的に状況に依存する。本質的には、結合から範囲条件を外すことになる。今回の場合は、日付(DATA)のカラムを追加して、それで絞り込むことにすればよい。
ALTER TABLE items_ordered ADD itm_order_date DATE NOT NULL, ADD INDEX itm_prd_id__and__itm_order_date (itm_prd_id, itm_order_date);
UPDATE items_ordered SET itm_order_date = DATE(itm_order_timestamp);
EXPLAIN
SELECT COUNT(1)
FROM tags t
JOIN products p
ON p.prd_id = t.tag_prd_id
JOIN items_ordered i
ON i.itm_prd_id = p.prd_id
WHERE t.tag_name = 'lcd'
AND i.itm_order_date = '2010-05-16'
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
TABLE: t
type: ref
possible_keys: PRIMARY
KEY: PRIMARY
key_len: 98
ref: const
rows: 1
Extra: USING WHERE; USING INDEX
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
TABLE: p
type: eq_ref
possible_keys: PRIMARY
KEY: PRIMARY
key_len: 4
ref: example_db.t.tag_prd_id
rows: 1
Extra: USING INDEX
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
TABLE: i
type: ref
possible_keys: itm_prd_id__and__itm_order_timestamp,itm_prd_id__and__itm_order_date
KEY: itm_prd_id__and__itm_order_date
key_len: 7
ref: example_db.p.prd_id,const
rows: 206494
Extra: USING WHERE; USING INDEX
このクエリーはitm_prd_id__and__itm_order_dateで7バイトとなっているので、(itm_prd_id, itm_order_date)をちゃんと利用していることが分かる。またrefでも2つのカラムを結合で利用していることが分かる。
SHOW STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 3 |
| Handler_read_next | 4104 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
非常によくなったことが分かるだろう。
しかし、クエリーが異なれば、解決方法も異なることは覚えておいて欲しい。
この問題に対するバグレポートがいくつかある(例えば #8569, #19548)が、MySQLからの回答では6.0かそれ以降のバージョンで解決されると言うことだ。または「それは解説されている振る舞いである」と言うことらしい。でも実際には仕様ではなく致命的なバグであり、修正が必要だ。
