Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.1Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


8.2.1.7 インデックス拡張の使用

InnoDB は、自動的に各セカンダリインデックスに主キーカラムを追加して、それを拡張します。このテーブル定義について考えます。

CREATE TABLE t1 (
  i1 INT NOT NULL DEFAULT 0,
  i2 INT NOT NULL DEFAULT 0,
  d DATE DEFAULT NULL,
  PRIMARY KEY (i1, i2),
  INDEX k_d (d)
) ENGINE = InnoDB;

このテーブルでは、カラム (i1, i2) に主キーを定義しています。さらに、カラム (d) にセカンダリインデックス k_d を定義していますが、内部で InnoDB はこのインデックスを拡張し、それをカラム (d, i1, i2) として処理します。

MySQL 5.6.9 より前では、オプティマイザは拡張セカンダリインデックスの使用方法や使用するかどうかを判断する際に、その主キーカラムを考慮しません。5.6.9 以降、オプティマイザは主キーカラムを考慮するようになったため、より効率的なクエリー実行プランやパフォーマンスの向上につながる可能性があります。

オプティマイザは、refrange、および index_merge インデックスアクセス、ルースインデックススキャン、結合とソートの最適化、および MIN()/MAX() 最適化に拡張セカンダリインデックスを使用できます。

次の例に、オプティマイザが拡張セカンダリインデックスを使用するかどうかによって、実行プランにどのような影響を与えるか示します。これらの行に t1 が移入されているとします。

INSERT INTO t1 VALUES
(1, 1, '1998-01-01'), (1, 2, '1999-01-01'),
(1, 3, '2000-01-01'), (1, 4, '2001-01-01'),
(1, 5, '2002-01-01'), (2, 1, '1998-01-01'),
(2, 2, '1999-01-01'), (2, 3, '2000-01-01'),
(2, 4, '2001-01-01'), (2, 5, '2002-01-01'),
(3, 1, '1998-01-01'), (3, 2, '1999-01-01'),
(3, 3, '2000-01-01'), (3, 4, '2001-01-01'),
(3, 5, '2002-01-01'), (4, 1, '1998-01-01'),
(4, 2, '1999-01-01'), (4, 3, '2000-01-01'),
(4, 4, '2001-01-01'), (4, 5, '2002-01-01'),
(5, 1, '1998-01-01'), (5, 2, '1999-01-01'),
(5, 3, '2000-01-01'), (5, 4, '2001-01-01'),
(5, 5, '2002-01-01');

ここで次のクエリーを考慮します。

EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'

この例では、主キーがカラム (i1, i2) で構成され、クエリーで i2 を参照していないため、オプティマイザは主キーを使用できません。代わりに、オプティマイザは (d) に対してセカンダリインデックス k_d を使用でき、実行プランは拡張インデックスを使用するかどうかによって異なります。

オプティマイザがインデックス拡張を考慮しない場合、それはインデックス k_d(d) のみとして扱います。クエリーの EXPLAIN では次の結果が生成されます。

mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ref
possible_keys: PRIMARY,k_d
          key: k_d
      key_len: 4
          ref: const
         rows: 5
        Extra: Using where; Using index

オプティマイザがインデックス拡張を考慮する場合、それはインデックス k_d(d, i1, i2) として扱います。この場合、それは左端のインデックスプリフィクス (d, i1) を使用して、より適切な実行プランを生成できます。

mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ref
possible_keys: PRIMARY,k_d
          key: k_d
      key_len: 8
          ref: const,const
         rows: 1
        Extra: Using index

どちらの場合も key は、オプティマイザがセカンダリインデックス k_d を使用することを示しますが、EXPLAIN 出力には、拡張インデックスの使用による次のような改善が示されます。

  • key_len は 4 バイトから 8 バイトになり、キールックアップでカラム d だけでなく、di1 も使用されていることを示しています。

  • キールックアップで 1 つではなく 2 つのキーパートが使用されるため、ref 値が const から const,const に変更されています。

  • rows 数は 5 から 1 に減少し、InnoDB が結果を生成するために調査する必要がある行数が少なくなることを示しています。

  • Extra 値が Using where; Using index から Using index に変更されています。このことは、データ行のカラムを参照せずに、インデックスのみを使用して、行を読み取れることを意味します。

拡張インデックスの使用のオプティマイザの動作の違いは、SHOW STATUS でも確認できます。

FLUSH TABLE t1;
FLUSH STATUS;
SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';
SHOW STATUS LIKE 'handler_read%'

前のステートメントには FLUSH TABLEFLUSH STATUS が含まれ、テーブルキャッシュをフラッシュし、ステータスカウンタをクリアします。

インデックス拡張を使用しないと、SHOW STATUS は次の結果を生成します。

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 5     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

インデックス拡張を使用すると、SHOW STATUS は次の結果を生成します。Handler_read_next 値が 5 から 1 に減少し、インデックスをより効率的に使用していることを示しています。

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 1     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

optimizer_switch システム変数の use_index_extensions フラグにより、InnoDB テーブルのセカンダリインデックスの使用方法を判断する際に、オプティマイザが主キーカラムを考慮するかどうかを制御できます。デフォルトで、use_index_extensions は有効です。インデックス拡張の使用を無効にするとパフォーマンスが向上するかどうかを確認するには、次のステートメントを使用します。

SET optimizer_switch = 'use_index_extensions=off';

オプティマイザによるインデックス拡張の使用は、インデックス (16) のキーパートの数と最大キー長 (3072 バイト) への通常の制限によります。


User Comments
Sign Up Login You must be logged in to post a comment.