インデックスを有効にする / where, 複合インデックス(MySQL)

あくまで参考としつつ、お手元の実行計画で解析をお願いします。

環境 - MySQL 5.7

TL;DR

• (1) WHERE を使う != インデックスが有効になる
• (2) 複合インデックスを使う時は、順序と範囲に留意する

この記事で使うサンプルテーブル

店舗(shops) テーブルを使う。

CREATE TABLE shops (
  id          INTEGER      AUTO_INCREMENT,
  name        VARCHAR(255) NOT NULL DEFAULT '',
  asset       INTEGER      NOT NULL DEFAULT 0,
  debt        INTEGER      NOT NULL DEFAULT 0,
  trust_score INTEGER      NOT NULL DEFAULT 0,

  PRIMARY KEY(id),
  INDEX finance_index (asset, debt, trust_score)
);

データは次の通りとする(クリック・タップすると開く)

SELECT * FROM shops;

+----+------+-------+------+-------------+
| id | name | asset | debt | trust_score |
+----+------+-------+------+-------------+
|  1 | A    |  9000 | 4000 |          50 |
|  2 | B    |   300 |   50 |          50 |
|  3 | C    |    50 |  100 |          10 |
|  4 | D    |   700 | 4000 |           0 |
|  5 | E    |  5000 |  300 |          80 |
|  6 | F    |  2000 |  300 |          90 |
|  7 | G    |  4000 | 3000 |          40 |
|  8 | H    |  3000 | 2000 |          20 |
|  9 | I    |   100 |  200 |           0 |
| 10 | J    |    50 |  200 |           0 |
| 11 | K    |  2000 |  300 |          80 |
| 12 | L    |    50 |   50 |          20 |
| 13 | M    |  1000 |  100 |          90 |
| 14 | N    |   200 |  100 |          30 |
+----+------+-------+------+-------------+

-- 参考用に INSERT 文

INSERT INTO shops (name, asset, debt, trust_score) VALUES
  ('A', 9000, 4000, 50),
  ('B', 300,  50,   50),
  ('C', 50,   100,  10),
  ('D', 700,  4000, 0),
  ('E', 5000, 300,  80),
  ('F', 2000, 300,  90),
  ('G', 4000, 3000, 40),
  ('H', 3000, 2000, 20),
  ('I', 100,  200,  0),
  ('J', 50,   200,  0),
  ('K', 2000, 300,  80),
  ('L', 50,   50,   20),
  ('M', 1000, 100,  90),
  ('N', 200,  100,  30);

(1) WHERE を使う != インデックスが有効になる

• 一意生のある検索はインデックスが効く
• 一意生のない検索はインデックスが効かない

(1-1) 一意生のある検索

一意的に絞りこまれている為、インデックスが有効である

SELECT * FROM shops WHERE asset = 4000;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: shops
   partitions: NULL
         type: ref
possible_keys: finance_index
          key: finance_index
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

SELECT * FROM shops WHERE 1000 <= asset AND asset <= 2000;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: shops
   partitions: NULL
         type: range
possible_keys: finance_index
          key: finance_index
      key_len: 4
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index condition

(1-2) 一意生のない検索

SELECT * FROM shops WHERE asset < 2000 OR 3000 < asset;

• テーブル内を全スキャンする
• 全行を調べてから絞り込むので、インデックスを有効利用できない

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: shops
   partitions: NULL
         type: ALL
possible_keys: finance_index
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 14
     filtered: 78.57
        Extra: Using where

(2) 複合インデックスを使う時は、順序と範囲を念頭に置く

複合インデックスが (x, y, z) の順番ならば

インデックスが有効になるのは、以下のいづれか( leftmost prefix )

• WHERE x = X
• WHERE x = X AND y = Y
• WHERE x = X AND y = Y AND z = Z

以下の場合、インデックスは効かない

• WHERE y = Y AND z = Z ( 左端キー飛ばし )
• WHERE y < Y AND x = X ( 一意生のない検索 )
• WHERE x = X OR y = Y ( OR 検索 )

(2-1) インデックスが効く場合 ( leftmost prefix )

複合インデックスにおいて以下条件を満たすならば、インデックスが有効になる

• 作成した複合キー「左から順に」
• AND で絞り込む

サンプル用テーブルの複合キーは (asset, debt, trust_score) の順である。

SELECT * FROM shops WHERE asset = 2000;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: shops
   partitions: NULL
         type: ref
possible_keys: finance_index
          key: finance_index
      key_len: 4
          ref: const
         rows: 2
     filtered: 100.00
        Extra: NULL

SELECT * FROM shops WHERE asset = 2000 AND debt = 300;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: shops
   partitions: NULL
         type: ref
possible_keys: asset_index,finance_index
          key: finance_index
      key_len: 8
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL

SELECT * FROM shops WHERE asset = 2000 AND debt = 300 AND trust_score = 80;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: shops
   partitions: NULL
         type: ref
possible_keys: finance_index
          key: finance_index
      key_len: 12
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: NULL

(2-2) インデックスが効かない場合

左端キーを飛ばす

サンプル用テーブルの複合キー (asset, debt, trust_score) の asset を飛ばす。

SELECT * FROM shops WHERE trust_score = 300 AND trust_score = 90;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Impossible WHERE

一意生のない検索

複合キーの順に沿っているが、以下の検索時はインデックスが効かない

• 範囲指定がある場合
• OR 検索している場合

SELECT * FROM shops WHERE asset <= 2000 AND debt = 3000;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: shops
   partitions: NULL
         type: ALL
possible_keys: finance_index
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 14
     filtered: 7.14
        Extra: Using where

SELECT * FROM shops WHERE asset = 2000 AND trust_score = 300 OR debt = 3000;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: shops
   partitions: NULL
         type: ALL
possible_keys: finance_index
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 14
     filtered: 10.64
        Extra: Using where

参考書籍

• Efficient MySQL Performance