mysql チューニング – 上物 explain mysqldumpslow

インフラ的なチューニング項目もあるけど、今回は上物のチューニング。

上物のチューニングといえば、「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

の様に実行することで、総消費時間(実行時間x実行回数)順でソートした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 TABLE est ADD INDEX INDEX_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 秒となった。
これで、インデックスを張る前よりも1.55%まで処理時間を短縮できた事になる。

同様の手法で、ログを上から片付けていけばDB動作の高速化が望める(但し、インデックスを張りすぎると、INSERT等の更新系処理で、インデックスを構築する負荷が増加するのに注意が必要)

(57)


カテゴリー: LAMP[Linux, Apache, MySQL, PHP], MySQL   パーマリンク

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です