インフラ的なチューニング項目もあるけど、今回は上物のチューニング。
上物のチューニングといえば、「Wordpressでページ送りが表示されない」の記事で出てきたEXPLAIN文。
こいつは、SQL文の前にEXPLAINとつけて実行すると、そのSQL文が実行されるときの実行計画が表示される。
また、チューニングするには情報収集が大事で、クエリのログを取得するのが第一歩。
長時間を要するログを取得するのに、MySQLアップデート － epel/remiリポジトリでやったスロークエリログを取得するほかに、
my.cnf
log = /var/log/mysql-query.log
の様に記述することで、クエリ等のログを取得できるが、スロークエリの方が解析しやすいと思う。
で、取得したスローログは、mysqldumpslowコマンドで処理できる。
mysqldumpslow -s t all-sql.log >sorted.txt
の様に実行することで、総消費時間（実行時間ｘ実行回数）順でソートしたSQL文一覧が出力されるので、これの上からチューニングを施していく。
SQLのチューニングと言えばインデックスを張ることで、ユニークなキー、クエリの要素を出来るだけ多く含んだキーを設定することで速度的に大きな恩恵が得られる事が多い。
そして、インデックスがどのように働くかというのを知る方法が、冒頭に述べたEXPLAIN文である。
sorted.txt
Count: 11108 Time=0.04s (451s) Lock=0.00s (0s) Rows=0.0 (425), est_bk[est]@localhost
SELECT price FROM est WHERE r_id = ‘S’ AND s_id =N LIMIT N
の様に出てきた場合、平均実行時間0.04秒のクエリが11108回実行されていると言うことになる。このSQL文を
EXPLAIN SELECT price FROM est WHERE r_id = ‘S’ AND s_id =0 LIMIT 10
等の様に実行する（S/Nは文字列・数値の代替文字なので、適宜数値などを設定する）
実行結果は
| id | select_type | table | type | possible_keys |
|---|---|---|---|---|
| 1 | SIMPLE | est | ALL | NULL |
| key | key_len | ref | rows | Extra |
| NULL | NULL | NULL | 5935 | Using where |
のように出力される。
この場合、SIMPLE(UNION等を含まない)セレクトがestテーブルに実行されて、有効キーはなく5935行をALL（全域スキャン）を行う計画になっている。 全域スキャンはコストが高いので、インデックスを設定することで高速化できそう。
確認のため、インデックスを張る前に、EXPLAINを外して普通に実行すると、クエリの実行時間 0.0580 秒であった。
ALTER TABLEestADD INDEXINDEX_R_S_PRICE(r_id,s_id)
のようにしてインデックスを張って、再度、EXPLAIN文を実行すると
| id | select_type | table | type | possible_keys |
|---|---|---|---|---|
| 1 | SIMPLE | est | ref | INDEX_R_S_PRICE |
| key | key_len | ref | rows | Extra |
| INDEX_R_S_PRICE | 44 | const,const | 1 | Using where |
のように、INDEX_R_S_PRICEが有効化された。
この状態で、再度、EXPLAINを外した文を実行すると、クエリの実行時間 0.0009 秒となった。
これで、インデックスを張る前よりも１．５５％まで処理時間を短縮できた事になる。
同様の手法で、ログを上から片付けていけばDB動作の高速化が望める（但し、インデックスを張りすぎると、INSERT等の更新系処理で、インデックスを構築する負荷が増加するのに注意が必要）
(336)
