このページは機械翻訳したものです。

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

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) として処理します。

オプティマイザは、拡張セカンダリインデックスの主キーカラムを考慮して、そのインデックスを使用する方法と使用するかどうかを決定します。 これにより、クエリー実行計画の効率が向上し、パフォーマンスが向上する可能性があります。

オプティマイザは、ref、range および 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'

実行計画は、拡張インデックスが使用されているかどうかによって異なります。

オプティマイザがインデックス拡張を考慮しない場合、それはインデックス 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 だけでなく、d と i1 も使用されていることを示しています。

• キールックアップで 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 TABLES および FLUSH 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 バイト) への通常の制限によります。