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

13.1.20.9 セカンダリインデックスと生成されたカラム

InnoDB では、仮想生成カラムのセカンダリインデックスがサポートされます。 その他のインデックスタイプはサポートされていません。 仮想カラムに定義されたセカンダリインデックスは、「「仮想インデックス」」と呼ばれることもあります。

セカンダリインデックスは、1 つ以上の仮想カラム、または仮想カラムと通常のカラムまたは格納された生成カラムの組合せに対して作成できます。 仮想カラムを含むセカンダリインデックスは、UNIQUE として定義できます。

セカンダリインデックスが仮想生成カラムに作成されると、生成されたカラム値はインデックスのレコードで実体化されます。 インデックスが covering index(クエリーによって取得されたすべてのカラムを含む) の場合、生成されたカラム値は、計算された「「その場で」」ではなく、インデックス構造の実体化された値から取得されます。

INSERT および UPDATE の操作中にセカンダリインデックスレコードの仮想カラム値を実体化するときに計算が実行されるため、仮想カラムでセカンダリインデックスを使用する際に考慮する追加の書込みコストがあります。 追加の書込みコストがあっても、生成される stored カラム (クラスタインデックスで実体化される) よりも仮想カラムのセカンダリインデックスの方が望ましい場合があり、その結果、より多くのディスク領域およびメモリーが必要なテーブルが大きくなります。 セカンダリインデックスが仮想カラムに定義されていない場合、カラムの行が調査されるたびに仮想カラムの値を計算する必要があるため、読取りに追加のコストがかかります。

インデックス付けされた仮想カラムの値は MVCC ログに記録され、ロールバック中またはパージ操作中に生成されたカラム値の不要な再計算を回避します。 ログに記録される値のデータ長は、COMPACT および REDUNDANT の行形式では 767 バイト、DYNAMIC および COMPRESSED の行形式では 3072 バイトのインデックスキー制限によって制限されます。

仮想カラムに対するセカンダリインデックスの追加または削除はインプレース操作です。

JSON カラムインデックスを提供するための生成されたカラムのインデックス付け

他の場所で説明したように、JSON カラムは直接インデックス付けできません。 このようなカラムを間接的に参照するインデックスを作成するには、次の例に示すように、インデックス付けする必要がある情報を抽出する生成カラムを定義し、生成されたカラムにインデックスを作成します:

Press CTRL+C to copy
 
mysql> CREATE TABLE jemp (
    ->     c JSON,
    ->     g INT GENERATED ALWAYS AS (c->"$.id"),
    ->     INDEX i (g)
    -> );
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO jemp (c) VALUES
     >   ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'),
     >   ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT c->>"$.name" AS name
     >     FROM jemp WHERE g > 2;
+--------+
| name   |
+--------+
| Barney |
| Betty  |
+--------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT c->>"$.name" AS name
     >    FROM jemp WHERE g > 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: jemp
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select json_unquote(json_extract(`test`.`jemp`.`c`,'$.name'))
AS `name` from `test`.`jemp` where (`test`.`jemp`.`g` > 2)
1 row in set (0.00 sec)

(この例では、表示領域に合うように最後のステートメントの出力をラップしています。)

EXPLAIN を SELECT または -> または ->> 演算子を使用する 1 つ以上の式を含む他の SQL ステートメントで EXPLAIN を使用する場合、JSON_EXTRACT() および (必要に応じて) JSON_UNQUOTE() を使用して、この EXPLAIN ステートメントに続いてすぐに SHOW WARNINGS から出力の通り、これらの式はそれらの相当に変換されます:

Press CTRL+C to copy
 
mysql> EXPLAIN SELECT c->>"$.name"
     > FROM jemp WHERE g > 2 ORDER BY c->"$.name"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: jemp
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select json_unquote(json_extract(`test`.`jemp`.`c`,'$.name')) AS
`c->>"$.name"` from `test`.`jemp` where (`test`.`jemp`.`g` > 2) order by
json_extract(`test`.`jemp`.`c`,'$.name')
1 row in set (0.00 sec)

追加情報および例については、-> および ->> の演算子と、JSON_EXTRACT() および JSON_UNQUOTE() の関数の説明を参照してください。

この手法を使用して、GEOMETRY カラムなど、直接インデックス付けできない他のタイプのカラムを間接的に参照するインデックスを提供することもできます。

MySQL 8.0.21 以降では、式を使用するクエリーの最適化に使用できる式を指定した JSON_VALUE() 関数を使用して、JSON カラムにインデックスを作成することもできます。 詳細および例については、その関数の説明を参照してください。

NDB Cluster での JSON カラムと間接インデックス

MySQL NDB Cluster では、次の条件に従って JSON カラムの間接インデックスを使用することもできます:

1. NDB は、JSON カラムの値を BLOB として内部的に処理します。 つまり、JSON カラムが 1 つ以上ある NDB テーブルには主キーが必要であり、それ以外の場合はバイナリログに記録できません。

2. NDB ストレージエンジンは、仮想カラムのインデックス作成をサポートしていません。 生成されるカラムのデフォルトは VIRTUAL であるため、間接インデックスを STORED として適用する生成されるカラムを明示的に指定する必要があります。

次に示す jempn テーブルの作成に使用される CREATE TABLE ステートメントは、NDB と互換性があるように変更された、前述の jemp テーブルのバージョンです:

Press CTRL+C to copy
 
CREATE TABLE jempn (
  a BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  c JSON DEFAULT NULL,
  g INT GENERATED ALWAYS AS (c->"$.name") STORED,
  INDEX i (g)
) ENGINE=NDB;

次の INSERT ステートメントを使用して、このテーブルに移入できます:

Press CTRL+C to copy
 
INSERT INTO jempn (a, c) VALUES
  (NULL, '{"id": "1", "name": "Fred"}'),
  (NULL, '{"id": "2", "name": "Wilma"}'),
  (NULL, '{"id": "3", "name": "Barney"}'),
  (NULL, '{"id": "4", "name": "Betty"}');

次に示すように、NDB でインデックス i を使用できるようになりました:

Press CTRL+C to copy
 
mysql> EXPLAIN SELECT c->>"$.name" AS name
          FROM jempn WHERE g > 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: jempn
   partitions: p0,p1
         type: range
possible_keys: i
          key: i
      key_len: 5
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where with pushed condition (`test`.`jempn`.`g` > 2)
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select
json_unquote(json_extract(`test`.`jempn`.`c`,'$.name')) AS `name` from
`test`.`jempn` where (`test`.`jempn`.`g` > 2)
1 row in set (0.00 sec)

格納された生成カラムは DataMemory を使用し、このようなカラムのインデックスは IndexMemory を使用することに注意してください。