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

◼︎ 参考

Share

最近の投稿

NetlifyのSplit TestingとFunctionsについて使い方をまとめる Split TestingはGitHubのブランチをベースにしたA/Bテストを行うための機能のことで、FunctionsはNetlifyでAWS Lambdaを使うことができる

NetlifyとはHTMLなどの静的コンテンツのみで構成されたWebサイトを閲覧できる形で配信するWebサービス GitHubやBitbucket、GitLabなどと連携して使うことができて、リポジトリにプッシュすることで自動でCI/CDを行うことができる、無料枠が豊富で独自ドメインを設定可能

WordPressからJekyll(GitHub Pages)に移行した手順をまとめる。 お金的な事情や使いやすさなどの理由で無料のJekyll+GitHub Pagesに移行した。JekyllとはMarkdown等から静的ページを生成する静的サイトジェネレータ

Scala開発のためにScalaらしさをまとめる 言語設計者の設計思想を元にScalaらしさについてまとめる オブジェクト指向と関数型の融合について

StorybookとはUI開発環境を提供するツール React、React Native、Angular、Vueなどをサポートしている ユーザーは独立した開発環境でコンポーネントを個別に作成して挙動の確認をテストできたり、コンポーネントを一覧にしてカタログ化できるので他の人に紹介する時に使えたりする

カテゴリ一覧

タグ一覧