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前後になってしまい、いつサーバーが落ちるかとヒヤヒヤしています…
是非ご回答宜しくお願い致します。
A 回答 (5件)
- 最新から表示
- 回答順に表示
No.5
- 回答日時:
苦労されているようでもうひと踏ん張りしてほしいところですが・・・
>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
- 回答日時:
>複合インデックスではなくそれぞれに設定するということでしょうか。
いえ複合です
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
- 回答日時:
No.1さんのSQL文の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の使用は気をつけないと、速度低下を招きますが・・・・今回の事例は、それに輪を掛けています。どれだけCPU資源を使えるかへのチャレンジと言われても仕方ないくらいにです。(これINの中身が増殖してきたらどうなるんでしょう?いったい、select文は何文字まで書いていいんでしょう?という問いへの実証SQLかもしれません。多分、どこかの時点で、いずれ、エラーで止まります。)
そうだったのですね。
勉強不足でお恥ずかしい限りです…
教えていただいたようにEXISTSを使ってSQL文を作ってみたのですが、
WHERE `a_id` EXISTS (…
ではなくて
WHERE EXISTS (… `table1`.`a_id` = `table2`.`a_id` …
で大丈夫でしょうか?
EXISTSの前に`a_id`を書くとエラーとなってしまいうまくいきませんでした。
何はともあれidを羅列するSQL文じゃなくなってスッキリできました。
ありがとうございます。
No.1
- 回答日時:
> `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で条件を指定しています。
説明不足で申し訳ございません。
引き続き宜しくお願い致します。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで最後の文字だけ置き...
-
1テーブル&複数レコードの更新...
-
SQL Left Join で重複を排除す...
-
副問合せの書き方について
-
エクセルの関数について教えて...
-
SQLにて特定の文字を除いた検索...
-
mysqlのdeleteのサブクエリーで...
-
Updateの複数テーブル条件時のL...
-
バインド変数について
-
VIEWの元のテーブルのindexって...
-
sqlで、600行あるテーブルを100...
-
select文のwhere句に配列を入れ...
-
ある条件の最大値+1を初番する...
-
[MySQL] UNIQUE制約の値を更新...
-
SQLサーバから、項目の属性(型...
-
DataTableで重複行を削除したい
-
Access パラメータクエリをcsv...
-
単純なクエリーなのにSELECTし...
-
Yahoo .comの idには年齢制限、...
-
マイクラPC版のコマンドで効率...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで最後の文字だけ置き...
-
エクセルの関数について教えて...
-
VIEWの元のテーブルのindexって...
-
副問合せの書き方について
-
select文のwhere句に配列を入れ...
-
マイクラPC版のコマンドで効率...
-
SQLサーバから、項目の属性(型...
-
エラー 1068 (42000): 複数の主...
-
[MySQL] 3つのテーブルの結合で...
-
Access パラメータクエリをcsv...
-
SQLにて特定の文字を除いた検索...
-
SQL Left Join で重複を排除す...
-
ストアドのエラーについて
-
Unionした最後にGROUP BYを追加...
-
バインド変数について
-
PL/SQLの変数について
-
sqlで、600行あるテーブルを100...
-
WordpressのContact form 7でzi...
-
selectした大量データをinsert...
-
inner joinをすると数がおかし...
おすすめ情報