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

Published: 2016年2月27日 by tomsato

◼︎ 概要

ある程度大きな規模のサービスで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式が異なる場合など

確認項目

</tbody> </table>

◼︎ その他確認項目

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

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

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

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

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に変更すると行ロックになるのでつまることがなくなる
キャッシュを使う 同じクエリを使う場合はキャッシュを使ったほうがいいかも

◼︎ 参考

コメントを書く

※ Emailは公開されません

※ コメントは承認されると下記に表示されます

コメント一覧

最近の投稿

vue-pdfを使ってNuxt.jsで作成しているアプリケーションに pdfスライドを表示させるサンプルを作成しました README.md通りに実装してもうまくいかないところがあったのでそのあたり含めてまとめます

Vue.js / Nuxt.jsにおけるログインの実装方法をまとめる Auth0やNuxt.jsのAuth Moduleとmiddlewareについて調べつつサンプルを作成することで理解を深める

コンポーネント設計について考える Atomic DesignやPresentational Component, Container Componentについてまとめつつ 自分だったらVue.js / Nuxt.jsでどういうコンポーネント設計にするかについてまとめます

Netlify Formsを使ってブログサイトにコメント機能を追加する方法を調べたので紹介 Netlify FormsはNetlifyに標準機能として用意されているフォーム機能 サーバレスなので別途コメント用にサーバを用意する必要がなくHTMLを埋め込むだけで準備できる

TypeScriptの入門の入門チートシート 型の種類や使い方についてまとめていくだけでなく、 TypeScriptについてや必要性などについてもまとめることでこのページでTypeScriptをざっくり理解できる程度の内容を目指す

カテゴリ一覧

タグ一覧