MySQLのチューニング

Debug Barなどで遅いクエリを見つけたら、phpmyadminでそのクエリを実行し、時間を計測します。

その際、SQL_NO_CACHE を使って、キャッシュにかからないように、phpmyadminから実行します。(そうしないと、実行時間が2回目以降に短くなります。)

[使い方]
select hoge1 from hogetable;

select SQL_NO_CACHE hoge from hogetable;

selectの後にSQL_NO_CACHEを入れてsql文を実行します。

スポンサーリンク

クエリキャッシュは有効化?されているか調べる

現在、キャッシュが有効かを調べるクエリは下記です。
show variables like ‘query_cache_%';

★結果
| query_cache_limit | 1048 |
| query_cache_min_res_unit | 2096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |

クエリキャッシュがきいていると、quiery_cache_sizeのサイズが0以上になります。

Explainでクエリの実行速度を把握

  • 概要
  • table
    データを操作する対象となっているテーブル名。
  • type
    データを操作する際の結合の型。
    ALLの場合、フルスキャンを実行(遅い)。
  • possible_keys
    利用できる可能性のあるインデックスのリスト。
    NULLの場合、インデックスが作成されていない。
  • MySQLは1つのクエリーで1つのテーブルに対し、1つのインデックスしか機能しないので、Posiible keysのなかから、key(インデックス)が一つ選ばれて実行されます。
  • key
    possible_keysの中からMySQLが選択した利用するインデックスの名前。
    NULLの場合、インデックスが利用されていない。
  • key_len
    keyの値のサイズ(byte)。
    複合インデックスの場合、利用される範囲のサイズ。
  • ref
    テーブルからレコードを選択する際に利用するカラム名または定数。
  • rows
    SQLの条件を満たすかどうか調査する必要があると推定される行数。
    この数値が大きい場合、SQL遅延の原因の可能性がある。
  • Extra
    対象のSQL実行の際の追加情報。

EXPLAINをSQLの前につけて実行。

★出力結果の意味(下記を参考)

http://www.tatsuya-koyama.com/4.0/html/tknotes/knowledge/programming/mysql_explain_cheat_sheet.html

possible_keys

  • オプティマイザがテーブルのアクセスに利用可能なインデックスの候補として挙げたキー

type

  • どのようなアクセスか。レコードアクセスタイプとも呼ばれる
  • 以下、効率の良い順であり、indexとallという値は改善必須。
system テーブルに1行しかデータが無い場合のアクセス
const PRIMARY KEY, UNIQUE インデックスのルックアップによるアクセス。最速
eq_ref JOIN において PRIMARY KEY, UNIQUE KEY が利用されるタイプのアクセス
ref PRIMARY KEY や UNIQUE KEY 以外の、ユニークでないインデックスを使用したアクセス
range インデックスを用いた範囲検索。検索句で範囲が指定されている
index フルインデックススキャン。 インデックスファイル(.MYI)を使って全件検索。とても遅い
ALL フルテーブルスキャン。 データファイルを使って全件検索が行われる。改善必須

Extra

Using filesortが出た場合、改善したほうがよい。

Distinct 1つのデータが白鍵できればよい状態
Not exists LEFT/RIGHT JOIN で、反対側のテーブルが NOT NULL として定義されたフィールドで JOIN されているから、 マッチしない行を探せばよい、という状態
Using where WHERE 句に条件が指定されているけど、インデックスが使用できない状態
Using index クエリがインデックスだけで解決できる。Covering Index を利用している場合など
Using filesort メモリとファイルを利用したクイックソートが行われた
Using index for group-by MIN() / MAX() が GROUP BY 句と併用されているとき、クエリがインデックスだけを用いて解決できる
Range checked for each record (index map: N) JOIN において range または index_merge が利用される場合

select_type

  • どのようなコンテキストで(どのような種類のクエリで)テーブルが参照されるか
  • クエリの種類とは、JOIN, UNION, サブクエリとその組み合わせのこと
SIMPLE UNION やサブクエリが無い
PRIMARY UNION で最も最初の SELET. またはサブクエリでもっとも外側にある SELECT
SUBQUERY 親クエリと相関関係の無いサブクエリ
DEPENDENT SUBQUERY 相関関係のあるサブクエリ
UNCACHEABLE SUBQUERY 実行するまで結果が分からないようなサブクエリ。例えば RAND を含む

注意

インデックスが増えれば検索速度も向上しますが、同時にINSERT/UPDATEが遅くなります。
SQLのWHERE句の順序などを変えることで、インデックスの効率が変わりそうですが、MySQLのクエリオプティマイザは自動的に実行前にクエリの構文解析を行い最も効率的なクエリの実行方法を見つけ出しますので、順序はあまり関係ないのかもしれません。

・適切なインデックス候補が存在するか

・どのインデックスが最適であるか

・どのテーブルが関係するのか

・エイリアスを使用するのか

・複数テーブルの場合どの結合順序が適切か

・どの検索順序が適切か

などが、オプティマイザでsqlを解析してから、実行されますので、順序はあまり関係ないようです。

インデックスは昇順・降順 (ASC/DESC) を指定することができますが、実際にはこの指定は無視され、常に昇順 (ASC) になります。

インデックスの追加

– 表示
SHOW INDEX FROM table_name;

– 追加
ALTER TABLE table_name ADD INDEX index_name(column_name);

– 追加 複合インデックス
ALTER TABLE table_name ADD INDEX index_name(column1_name, column2_name);

– 削除
ALTER TABLE table_name DROP INDEX index_name;

スポンサーリンク

Leave a Reply