MySQL IN演算子が遅い時の対策

質問

topコマンドで見た時にいつもmysqldのCPU使用率が300前後になっています

改善方法など全くといっていいほどわからないのですが、まずslow-logを覗いてみたところ

SELECT `a_id`, `b_id`, `hoge`
FROM `table1`
WHERE `a_id` IN (10,27,37,38,46,47,51,69,73,82,86,90,103,110,125,129,134,135,136,137,139,142,148,150,161,168,181,184,187,191,192,198,200,206,215,222,230,231,249,264,266,270,271,302,315,338,342,349,360,361,366,376,383,385,399,405,406,410,412,424,427,432,438,444,454,456,457,462,480,483,484,487,488,492,493,494,506,509,517,549,559,573,574,578,579,586,587,594,597,599,609,612,617,619,625,629,632,633,637,642,646,679,681,684,687,688,689,691,692,696,698,699,700,708,716,738,739,740,742
,755,758,764,767,768,772,774,778,780,782,783,786,787,792,793,795,796,799,802,807,808,810,811,815,816,817,819,821,823,826,827,828,829,831,833,834,839,840,841,846,849,854,855,856,857,858,859,860,861,864,872,879,881,882,884,888,890,892,893,894,896,899,907)
ORDER BY `updatetime` DESC
LIMIT 0, 100;

このようなクエリが1秒以上かかっていました。
`table1`と`table2`は`a_id`で紐付いていて、事前に`table2`に入っているa_idの一覧を取得してから上記のクエリを作成しています。

`table1`のスキーマを確認したところ
INDEX a_id(`a_id`)
INDEX updatetime(`updatetime`)
となっていました。
（素人ながらに
INDEX idx1(`a_id`, `updatetime`)
とするべきだったのでは…と思ったのですがALTER TABLEで追加してみたところ、結果は変わりませんでした。）

EXPLAINしてみたところ
1 | SIMPLE | feed | range | a_id,i1 | a_id | 5 | NULL | 237900 | Using where; Using filesort
と返ってきました。

このような状態では、どのように対策するのが良いのでしょうか。
決まった正解はないのかもしれないですが、考えられる可能性で試すべき項目を教えて頂ければ幸いです。

・table2のidに紐付いたtable1の情報を取り出したい場合の適切な設計
・適切なSQL文
・適切なインデックスの貼り方

などをご教授いただけると嬉しいです。

21時～0時くらいの時間帯になるとアクセスが増えload averageが10前後になってしまい、いつサーバーが落ちるかとヒヤヒヤしています…

是非ご回答宜しくお願い致します。

回答No.5

yambejp

苦労されているようでもうひと踏ん張りしてほしいところですが・・・

＞INDEX a_id(`a_id`)
されているのであれば、別解で上げているように

select a_id,b_id,hoge from table1 force index(a_id) where a_id IN (・・・) order by updatetime desc limit 0,100 ;

の処理で高速化が確認できませんか？

回答No.4

yambejp

>複合インデックスではなくそれぞれに設定するということでしょうか。

いえ複合です
create table table1(a_id int ,b_id int,hoge varchar(20),updatetime datetime);
insert into table1 values(1,1,'1','2013-08-27 00:00:01')
,(2,2,'2','2013-08-27 00:00:02')
,(3,3,'3','2013-08-27 00:00:03')
,(4,4,'4','2013-08-27 00:00:04')
,(5,5,'5','2013-08-27 00:00:05')
,(6,6,'6','2013-08-27 00:00:06')
,(7,7,'7','2013-08-27 00:00:07')
,(8,8,'8','2013-08-27 00:00:08')
,(9,9,'9','2013-08-27 00:00:09');

として、
explain select a_id,b_id,hoge from table1 where a_id IN (1,3,10,20) order by updatetime desc limit 0,100;

すると、type=ALL,possible_keys=NULLのグダグダなSQLになります

alter table table1 add index index1(a_id,b_id,hoge,updatetime);
でインデックスを追加して、

explain select a_id,b_id,hoge from table1 where a_id IN (1,3,10,20) order by updatetime desc limit 0,100;
すると、type=range,possible_keys=index1の結果が得られます

ただし、今回のように絞り込みがa_idでのみ処理される場合はFORCE INDEXでの
絞り込みも有効です

//元のindexをはずしておく
alter table table1 drop index index1;
alter table table1 add index index2(a_id);

explain select a_id,b_id,hoge from table1 force index(index2) where a_id IN (1,3,10,20) order by updatetime desc limit 0,100 ;

この場合a_idをインデックスに利用しているのがわかります。

お礼

回答ありがとうございます。

ALTER TABLEを試してみたのですが、なぜか処理が終わらずしかもtopコマンドで見た時にmysqldのCPU使用率が2%くらいとなってしまい、サイト自体も開けなくなってしまいました。

テーブルには現在962362行のデータしか入っていません。
ALTER TABLEをした瞬間にmysqldのCPU使用率が2%前後になり何も仕事をしなくなってしまうのはなぜなのでしょうか。
ALTER TABLEを停止しようにも、mysqlを再起動しないとどうにもならない状態となり、stopするのにも非常に時間がかかります。

一度nginxをstopしてから試してみたのですが結果は同じでした。

今回の質問とはずれてきてしまうので、新しく質問を作り直したいと思います。

回答No.3

mitoneko

No.1さんのＳＱＬ文のwhere句は、もっともっと長くしても全然かまわないんですよ。元のINの中のリストを作るのに必要な全部の条件をandで繋げてあげればそれで事足ります。

　別解を作るなら、

　SELECT `a_id`, `b_id`, `hoge`
　FROM `table1`
　WHERE `a_id` EXISTS (SELECT a_id FROM　table2 WHERE　お好きなだけ条件をつける)
　ORDER BY `updatetime` DESC
　LIMIT 0, 100;

　でも良いです。

　まぁ、どちらにせよ、今よりは、劇的に早くなるはずです。
　インデックスはそっと置いておいてもね。
　実は、両方のテーブルに、a_idのインデックスがあり、かつ、どちらかのテーブルにa_idの外部参照制約がついていれば、No1.さんのSQLの方が早いと思います。

　このSQLの為だけのインデックス考慮なら、table2に条件に絡むインデックスと、table1にa_idとupdatetimeに単独カラムのインデックスでしょう。

　ただでさえ、INの使用は気をつけないと、速度低下を招きますが・・・・今回の事例は、それに輪を掛けています。どれだけＣＰＵ資源を使えるかへのチャレンジと言われても仕方ないくらいにです。（これINの中身が増殖してきたらどうなるんでしょう？いったい、select文は何文字まで書いていいんでしょう？という問いへの実証SQLかもしれません。多分、どこかの時点で、いずれ、エラーで止まります。）

お礼

そうだったのですね。
勉強不足でお恥ずかしい限りです…

教えていただいたようにEXISTSを使ってSQL文を作ってみたのですが、

WHERE `a_id` EXISTS (…
ではなくて
WHERE EXISTS (…　`table1`.`a_id` = `table2`.`a_id`　…
で大丈夫でしょうか？

EXISTSの前に`a_id`を書くとエラーとなってしまいうまくいきませんでした。

何はともあれidを羅列するSQL文じゃなくなってスッキリできました。
ありがとうございます。

回答No.2

yambejp

インデックスは a_id, b_id, hoge,updatetime

補足

複合インデックスではなくそれぞれに設定するということでしょうか。

回答No.1

Picosoft

> `table1`と`table2`は`a_id`で紐付いていて、事前に`table2`に入っているa_idの一覧を取得してから上記のクエリを作成しています。
事前にtable2から一覧を取得しなくても、以下のSQLですべて事足ります。
SELECT T1.a_id, T1.b_id, T1.hoge
FROM table1 T1, table2 T2
WHERE T1.a_id = T2.a_id
ORDER BY T1.updatetime DESC
LIMIT 0, 100

補足

回答ありがとうございます。

SQL文ですが、table2のa_idを全て取り出しているわけではなく、WHEREで条件を指定しています。
説明不足で申し訳ございません。
引き続き宜しくお願い致します。