MySQLのindex初心者から脱したい
※私の解釈なので間違っていれば優しく教えてください
※個人的メモ程度
indexとは
一言で表すとSELECT文に合わせてレコードを並び替えたもの
Btreeの形になっているため二分探索ができる。
計算量は
O(N) >>> O(log2 N)
雲泥の差
複合index
2つ以上のカラムを基準に並び替える
例
ALTER TABLE comment ADD INDEX index1(thread_id, status, post_time);
この場合以下の順番でレコードが並ぶindexが作られる
thread_idで並び替える->複数あれば同じグループの中でstatusを並び替える-> さらにpostTimeで並び替える
MySQLの評価順位
indexで重要になるのがMySQLの評価順序 以下のような順番で評価する。
1. FROM 2. ON 3. JOIN 4. WHERE 5. GROUP BY 6. HAVING 7. SELECT 8. DISTINCT 9. ORDER BY 10. TOP(LIMIT)
有効なSELECT文
indexの並び順とMySQL評価順が一致！😄
SELECT
*
FROM
thread
LEFT JOIN
comment
ON
thread.id = comment.thread_id
WHERE
comment.status = 0
ORDER BY
comment.post_time;
有効でないSELECT文
indexの並び順とMySQL評価順が一致しない！😓
SELECT
*
FROM
thread
LEFT JOIN
comment
ON
thread.id = comment.thread_id
WHERE
comment.post_time > 20181231
GROUP BY
comment.status;
ちなみにindexで絞った結果が全体レコードの30%以上になるとオプティマイザがindexを使用するのを放棄する。
Explainよみかた
+----+-------------+---------------+--------+---------------+-----------+---------+---------------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+--------+---------------+-----------+---------+---------------+-------+-------------+ | 1 | SIMPLE | hoge | ALL | NULL | NULL | NULL | NULL | 16913 | Using where | | 1 | SIMPLE | huge | ref | i1 | i1 | 4 | banana.t.id | 1 | NULL | | 1 | SIMPLE | poge | eq_ref | coupon_id | coupon_id | 4 | melon.t.id | 1 | Using where | | 1 | SIMPLE | page | ref | i1 | i1 | 4 | tomato.t.id | 1 | NULL | | 1 | SIMPLE | hige | ref | i1 | i1 | 4 | cherry.t.id | 1 | NULL | | 1 | SIMPLE | pige | ref | i1 | i1 | 4 | apple.t.id | 1 | NULL | +----+-------------+---------------+--------+---------------+-----------+---------+---------------+-------+-------------+
id
テーブルがどのグループに属するかをみることができる、基本id=1 サブクエリがある場合id=2,3出てくる
select_type
クエリの種類を表すもの、joinやjoinすらしないものはsimpleなので、基本simpleしか出てこない
以下はsimple以外のパターン。
- PRIMARY・・・外部クエリを示す。
- SUBQUERY・・・相関関係のないサブクエリ。
- DEPENDENT SUBQUERY・・・相関関係のあるサブクエリ。
- UNCACHEABLE SUBQUERY・・・実行する度に結果が変わる可能性のあるサブクエリ。
DERIVED・・・FROM句で用いられているサブクエリ。
table
アクセスされるテーブルtype
たぶん一番大事なフィールド、クリティカルなヤバい奴を探したいときはここを見る
const・・・PRIMARY KEYまたはUNIQUEインデックスのルックアップによるアクセス。最速。
- eq_ref・・・JOINにおいてPRIARY KEYまたはUNIQUE KEYが利用される時のアクセスタイプ。constと似ているがJOINで用いられるところが違う。
- ref・・・ユニーク（PRIMARY or UNIQUE）でないインデックスを使って等価検索（WHERE key = value）を行った時に使われるアクセスタイプ。
- range・・・インデックスを用いた範囲検索。
- index・・・フルインデックススキャン。インデックス全体をスキャンする必要があるのでとても遅い。
- ALL・・・フルテーブルスキャン。インデックスがまったく利用されていないことを示す。やばい
possible_keys
オプティマイザがテーブルのアクセスに利用可能なインデックスの候補として挙げたキーの一覧。key
オプティマイザによって選択されたキー。key_len
選択されたキーの長さ。インデックスの走査は、キー長が短い方が高速。ref
検索条件で、keyと比較されている値やカラムの種類。定数が指定されている場合はconstと表示される。JOINが実行されている時には、結合する相手側のテーブルで検索条件として利用されているカラムが表示される。例えば次の例では、CountryテーブルはCityテーブルとCity.CountryCodeカラムでJOINされるということを示している。rows
そのテーブルからフェッチされる行数の見積もり。あくまでもテーブル全体の行数やインデックスの分散具合から導き出された大まかな見積もりなので、実際にフェッチされる正確な行数ではないので注意。また、フェッチされた全ての行がそのまま結果として返されるわけではないという点にも注意。後述するUsing whereがExtraフィールドに表示されている場合は、フェッチした行に対してさらにWHERE句の検索条件が適用されて行の絞り込みが行われるので、クライアントへ返される結果行は少なくなる可能性がある。Extra
オプティマイザさんの独り言が表示される、でもとても大事。
- Using where・・・WHERE句に検索条件が指定されており、なおかつインデックスを見ただけではWHERE句の条件を全て適用することが出来ない場合に表示される。
- Using index・・・クエリがインデックスだけを用いて解決できることを示す。Covering Indexを利用している場合などに表示される。
- Using filesort・・・filesort（クイックソート）でソートを行っていることを示す。
- Using temporary・・・クエリの実行にテンポラリテーブルが必要なことを示す。
- Using index for group-by・・・MIN()/MAX()がGROUP BY句と併用されているとき、クエリがインデックスだけを用いて解決できることを示す。
