MySQLのインデックスによるチューニング(備忘録)

SQLの高速化にあたって、インデックスをはることで高速化できるケースがある。

インデックスとは

辞書の索引のようなもので、特定のレコードへのアクセスを高速で行える。
インデックスを使わない検索は、一旦データを全て持ってきて、その中からwhere句に合う物を選ぶため時間がかかる。
インデックススキャンは、データの一部しか読み込まないため処理時間を短くできる。

インデックス作成の目安

1. JOINがある場合
   主キー、外部キーにインデックス追加
   （mysqlでは外部キー制約で自動的にインデックス付与される）

2. WHEREがある場合
   大量データを返すなら絞り込み対象のカラムにインデックス追加

3. GROUP BYなどの集計処理がある場合
   集計対象のカラムにインデックス追加

4. ORDER BYがある場合
   ORDER BY対象カラムにインデックス追加

ただし必ず効果検証することで有効かを判断すること。

その他インデックスを使うべきケース

• データ量が多い（約1万件以上）場合
• 絞り込めるデータの割合が全データと比較して少ない場合
  • 絞り込めるデータ量の割合が15%未満の場合は、インデックススキャンを用いるというのが一つの目安。
  • DB全体を読み込む方が早いとSQLが判断すると、フルスキャンが行われる。
  • ケースバイケースなので、処理速度はよく検証すること。
• MAX()、MIN()を用いる場合
• インデックスユニークスキャンを使える場合
  • テーブル内のユニークな一件をインデックスを用いて検索し、一件見つかれば処理が終了するため高速

　　　　  /* 主キー、ユニークキーによる絞り込み */
 　　  SELECT * FROM users
 　  　WHERE id = 1;

※きちんとインデックスが使われているかは、SQL文の頭にEXPLAINをつけて確認する。

 　　EXPLAIN SELECT * FROM tableA
 　　WHERE col_a = 1;

インデックスが活用できていないケース

• 索引列を加工している
  • インデックスのあるカラムに何らかの計算がされている場合インデックス利用できない。左辺はカラムだけにする。

　　　　/* GOOD　*/
 　　SELECT * FROM tableA
 　　WHERE col_a * 1.1 > 100;

 　　/* BAD　*/
　　　　SELECT * FROM tableA
 　　WHERE col_a > 100 / 1.1;

• 索引列に関数を使っている

• 否定形を使っている

• 複合インデックスの場合に、ORを使っている

　　　　/* nameとageにインデックスを作成*/
  
　　　　SELECT * FROM customer
 　　WHERE name = "佐藤" AND age = 25; # インデックス利用できる

  SELECT * FROM customer
 　　WHERE name = "佐藤" OR age = 25; # インデックス利用できない

• 複合インデックスの場合に、列の順番を間違えている

　　　　/* nameとageにインデックスを作成*/
  
　　　　SELECT * FROM customer
 　　WHERE name = "佐藤"; # インデックス利用できる

  SELECT * FROM customer
 　　WHERE name = age =25; # インデックス利用できない

　　　　SELECT * FROM customer
 　　WHERE age = 25 OR name = "佐藤"; # インデックス利用できない

• LIKEの中間一致・後方一致を使用している
  • 後方一致、中間一致はインデックス利用できない。（辞書の索引と同じ。）

　　　　/* インデックス利用できる　*/
 　　SELECT * FROM tableA
 　　WHERE name LIKE "佐藤%";

 　　/* インデックス利用できない　*/
　　　　SELECT * FROM tableA
 　　WHERE name LIKE "%佐藤";

  SELECT * FROM tableA
 　　WHERE name LIKE "%藤%";

• カラムのデータ型を間違えている
  • 文字列のカラムなのに',"をつけなかったり、逆に数値のカラムにつけたりすると暗黙の型変換が行われてインデックスが利用できない。

　　　　　　　/* BAD(int型のカラムにクォートをつけてる）　*/
    SELECT * FROM customer
    WHERE customer_id = '1';

　　　　　　　　/* GOOD　*/    
　　　　　　　　SELECT * FROM customer
    WHERE customer_id = 1;

注意

不要なインデックスは領域を無駄に使用して、SQLが使用するインデックスを判断するための時間を要するので、適切・適度に使用すること。

参考

MySQLリファレンスマニュアル