◼︎ 概要
ある程度大きな規模のサービスで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を作成する場合にデータ追加時に処理が重くなるので問題ないか確認する</p>
カーディなりティを意識するといいかも
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に変更すると行ロックになるのでつまることがなくなる |
| キャッシュを使う | 同じクエリを使う場合はキャッシュを使ったほうがいいかも |
コメントを書く
コメント一覧