MySQLでチューニングをしよう

◼︎ 目次

  1. 概要
  2. 重いクエリの洗い出し
  3. チューニング
  4. その他確認項目
  5. 参考

◼︎ 概要

ある程度大きな規模のサービスでMySQLを使っているなら避けては通れないMySQLのチューニングについてまとめます

重いクエリの出し方からexplainを使ったチューニング例まで簡単に紹介します

◼︎ 重いクエリの洗い出し

クエリに何秒かかっているか出力させるため設定を入れる

$ sudo rm mysqld-slow.log
$ mysql -u user_name -p

// 指定した秒数以上かかったクエリを記録
mysql > SET GLOBAL long_query_time = 0;
// ログのローテート
mysql> FLUSH LOGS;

集計してランキングを出す

$ sudo mysqldumpslow -s t mysqld-slow.log

これでどのクエリが重いのかがわかる

◼︎ チューニング

explainを使ってSQLの改善点を模索する

explainとは
MySQLがステートメントをどのように実行するのかに関する情報を提供する
どんな順番で結合されているのかに関する情報を含む、ステートメントを処理する方法を説明する
もっと簡単に言うと「MySQLが作成したクエリをどのように判断して実行したのか」が見れる
使い方はSQLのSELECT文の頭にexplainをつける

> explain select * from test;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    1 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.01 sec)
各項目について
id select毎に振られるID,処理順ではないので注意
select_type selectの種類 (SIMPLE,SUBQUERY,UNIONなど)
table 参照するテーブル
passible_keys 使用可能なインデックス
key MySQLが実際に使用を決定したインデックス
key_ken 読み取ったインデックスのバイト数
ref 検索条件でkeyと比較されているカラムまたは定数
rows 検索される推定レコード数、同じ結果となるSQLならここが少ないほど良い
type 結合型
Extra どのようにクエリが解決されるのかに関する追加情報
typeについて
const 一致するレコードが最大1つ
UNIQUEまたはPRIMARY KEYのインデックスの等価検索
eq_ref 1: 1のJOIN
UNIQUEまたはPRIMARY KEYのインデックスを使用
ref 1: nのJOIN
UNIQUEまたはPRIMARY KEY以外のインデックスを使用
index_merge インデックスマージが使用される
unique_shbquery サブクエリでUNIQUEまたはPRIMARY KEYのインデックスを使用
index_subquery サブクエリでUNIQUEまたはPRIMARY KEY以外のインデックスを使用
range インデックスを使用した範囲検索
index フルインデックススキャン
インデックスに対するALLのため遅い
ALL フルテーブルスキャン
インデックスの追加などで回避可能
Extraについて
Using filesort ORDER BYにインデックスを使用できない
Using temporary クエリの解決にテンポラリテーブルの作成が必要
DISTINCTの使用時やORDER BYのインデックスを使用できない場合、ORDER BYとGROUP BY式が異なる場合など

確認項目

項目 解決例 補足
typeがALLになっている indexを貼る 関数を適用した場合はインデックスは効かない
typeがindexだがrowsが大きいもの 違うindexを考える 既存のindexは削除しないで追加でindexを作成する場合にデータ追加時に処理が重くなるので問題ないか確認する

カーディなりティを意識するといいかも
http://qiita.com/soyanchu/items/034be19a2e3cb87b2efb

Cardinality:インデックス内のユニークな値の多さを表した指数
Cardinalityが低いと効果が少ない

ExtraにUsing temporary;Using filesortでrowsが大きい ORDER BY句時のINDEXについて見直す ソートにインデックスは有効か?
→ LIMIT句がある場合に効果が高いインデックスはソート済みのためソートが不要

◼︎ その他確認項目

クエリの改善以外での対策例

項目 説明
analyze tableを行う show index from tablename時にCardinalityがNULLになっていると
発行したSQLにおいてインデックスが利用されないパターンがある
analyze tableをすると統計情報を再取得するので解消する場合がある
WriteBackにする キャッシュ書き込み方式をWriteThroughからWriteBackにするとレプリケーションの遅延などが解消された事例がある
エンジンをInnoDBに変更する MyISAMはSELECT時でもテーブルロックがかかりトランザクションが多いと詰まってしまう
InnoDBに変更すると行ロックになるのでつまることがなくなる
キャッシュを使う 同じクエリを使う場合はキャッシュを使ったほうがいいかも

◼︎ 参考


Be First to Comment

コメントを残す

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