インデックスの作成

この文は既存のテーブルに新しいインデックスを追加します。これはALTER TABLE .. ADD INDEXの代替構文であり、MySQLとの互換性のために用意されています。

注記：

4つのvCPUを搭載したクラスタTiDB Cloud専用の場合、インデックス作成時にリソース制限がクラスタの安定性に影響を与えないように、 tidb_ddl_enable_fast_reorg手動で無効にすることをお勧めします。この設定を無効にすると、トランザクションを使用してインデックスを作成できるようになり、クラスタ全体への影響が軽減されます。

概要

CreateIndexStmt ::=
    'CREATE' IndexKeyTypeOpt 'INDEX' IfNotExists Identifier IndexTypeOpt 'ON' TableName '(' IndexPartSpecificationList ')' IndexOptionList IndexLockAndAlgorithmOpt

IndexKeyTypeOpt ::=
    ( 'UNIQUE' | 'SPATIAL' | 'FULLTEXT' )?

IfNotExists ::=
    ( 'IF' 'NOT' 'EXISTS' )?

IndexTypeOpt ::=
    IndexType?

IndexPartSpecificationList ::=
    IndexPartSpecification ( ',' IndexPartSpecification )*

IndexOptionList ::=
    IndexOption*

IndexLockAndAlgorithmOpt ::=
    ( LockClause AlgorithmClause? | AlgorithmClause LockClause? )?

IndexType ::=
    ( 'USING' | 'TYPE' ) IndexTypeName

IndexPartSpecification ::=
    ( ColumnName OptFieldLen | '(' Expression ')' ) Order

IndexOption ::=
    'KEY_BLOCK_SIZE' '='? LengthNum
|   IndexType
|   'WITH' 'PARSER' Identifier
|   'COMMENT' stringLit
|   ("VISIBLE" | "INVISIBLE")
|   ("GLOBAL" | "LOCAL")

IndexTypeName ::=
    'BTREE'
|   'HASH'
|   'RTREE'

ColumnName ::=
    Identifier ( '.' Identifier ( '.' Identifier )? )?

OptFieldLen ::=
    FieldLen?

IndexNameList ::=
    ( Identifier | 'PRIMARY' )? ( ',' ( Identifier | 'PRIMARY' ) )*

KeyOrIndex ::=
    'Key' | 'Index'

例

mysql> CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, c1 INT NOT NULL);
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO t1 (c1) VALUES (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM t1 WHERE c1 = 3;
+-------------------------+----------+-----------+---------------+--------------------------------+
| id                      | estRows  | task      | access object | operator info                  |
+-------------------------+----------+-----------+---------------+--------------------------------+
| TableReader_7           | 10.00    | root      |               | data:Selection_6               |
| └─Selection_6           | 10.00    | cop[tikv] |               | eq(test.t1.c1, 3)              |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------+
3 rows in set (0.00 sec)

mysql> CREATE INDEX c1 ON t1 (c1);
Query OK, 0 rows affected (0.30 sec)

mysql> EXPLAIN SELECT * FROM t1 WHERE c1 = 3;
+------------------------+---------+-----------+------------------------+---------------------------------------------+
| id                     | estRows | task      | access object          | operator info                               |
+------------------------+---------+-----------+------------------------+---------------------------------------------+
| IndexReader_6          | 10.00   | root      |                        | index:IndexRangeScan_5                      |
| └─IndexRangeScan_5     | 10.00   | cop[tikv] | table:t1, index:c1(c1) | range:[3,3], keep order:false, stats:pseudo |
+------------------------+---------+-----------+------------------------+---------------------------------------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE t1 DROP INDEX c1;
Query OK, 0 rows affected (0.30 sec)

mysql> CREATE UNIQUE INDEX c1 ON t1 (c1);
Query OK, 0 rows affected (0.31 sec)

表現インデックス

クエリのフィルタリング条件が特定の式に基づいているシナリオもあります。このようなシナリオでは、通常のインデックスが機能せず、テーブル全体をスキャンしてクエリを実行する必要があるため、クエリのパフォーマンスが比較的低下します。式インデックスは、式に基づいて作成できる特殊なインデックスの一種です。式インデックスを作成すると、TiDBは式ベースのクエリにそのインデックスを使用できるため、クエリのパフォーマンスが大幅に向上します。

たとえば、 LOWER(col1)に基づいてインデックスを作成する場合は、次の SQL ステートメントを実行します。

CREATE INDEX idx1 ON t1 ((LOWER(col1)));

または、次の同等のステートメントを実行することもできます。

ALTER TABLE t1 ADD INDEX idx1((LOWER(col1)));

テーブルを作成するときに式インデックスを指定することもできます。

CREATE TABLE t1 (
    col1 CHAR(10), 
    col2 CHAR(10),
    INDEX ((LOWER(col1)))
);

注記：

式インデックス内の式は(と)で囲む必要があります。囲まない場合は構文エラーが発生します。

通常のインデックスを削除するのと同じ方法で、式インデックスを削除できます。

DROP INDEX idx1 ON t1;

式インデックスには様々な種類の式が含まれます。正確性を保証するため、式インデックスの作成には、完全にテストされた一部の関数のみを使用できます。つまり、本番環境では、これらの関数のみが式で使用できます。これらの関数は、変数tidb_allow_function_for_expression_indexクエリすることで取得できます。現在、使用できる関数は次のとおりです。

• JSON_ARRAY()
• JSON_ARRAY_APPEND()
• JSON_ARRAY_INSERT()
• JSON_CONTAINS()
• JSON_CONTAINS_PATH()
• JSON_DEPTH()
• JSON_EXTRACT()
• JSON_INSERT()
• JSON_KEYS()
• JSON_LENGTH()
• JSON_MERGE_PATCH()
• JSON_MERGE_PRESERVE()
• JSON_OBJECT()
• JSON_PRETTY()
• JSON_QUOTE()
• JSON_REMOVE()
• JSON_REPLACE()
• JSON_SCHEMA_VALID()
• JSON_SEARCH()
• JSON_SET()
• JSON_STORAGE_SIZE()
• JSON_TYPE()
• JSON_UNQUOTE()
• JSON_VALID()
• LOWER()
• MD5()
• REVERSE()
• TIDB_SHARD()
• UPPER()
• VITESS_HASH()

上記のリストに含まれていない関数は、十分にテストされていないため、本番環境での使用は推奨されません。これは実験的とみなされます。演算子、 CASTなどの他CASE WHEN式も実験的とみなされ、本番環境での本番は推奨されません。

注記：

主キーに式インデックスを作成することはできません。

式インデックス内の式には、次の内容を含めることはできません。

• RAND()やNOW()などの揮発性関数。
• システム変数とユーザー変数 。
• サブクエリ。
• AUTO_INCREMENT列。この制限は、システム変数tidb_enable_auto_increment_in_generatedの値をtrueに設定することで解除できます。
• ウィンドウ関数 。
• ROW関数(例: CREATE TABLE t (j JSON, INDEX k (((j,j)))); )。
• 集計関数 。

式インデックスは暗黙的に名前（例： _V$_{index_name}_{index_offset} ）を取得します。列に既に設定されている名前で新しい式インデックスを作成しようとすると、エラーが発生します。また、同じ名前の新しい列を追加した場合もエラーが発生します。

式インデックスの式内の関数パラメータの数が正しいことを確認してください。

インデックスの式に文字列関連の関数が含まれており、戻り値の型と長さの影響を受ける場合、式インデックスの作成に失敗する可能性があります。このような状況では、関数CAST()使用して、戻り値の型と長さを明示的に指定できます。例えば、式REPEAT(a, 3)に基づいて式インデックスを作成するには、この式をCAST(REPEAT(a, 3) AS CHAR(20))に変更する必要があります。

クエリ文内の式が式インデックス内の式と一致する場合、オプティマイザはクエリに対して式インデックスを選択できます。ただし、統計情報によっては、オプティマイザが式インデックスを選択しない場合もあります。このような場合は、オプティマイザヒントを使用することで、オプティマイザに式インデックスを選択させることができます。

次の例では、式LOWER(col1)に式インデックスidx作成するとします。

クエリ文の結果が同じ式である場合、式インデックスが適用されます。次の文を例に挙げます。

SELECT LOWER(col1) FROM t;

フィルタリング条件に同じ式が含まれている場合、式インデックスが適用されます。以下の文を例に挙げます。

SELECT * FROM t WHERE LOWER(col1) = "a";
SELECT * FROM t WHERE LOWER(col1) > "a";
SELECT * FROM t WHERE LOWER(col1) BETWEEN "a" AND "b";
SELECT * FROM t WHERE LOWER(col1) IN ("a", "b");
SELECT * FROM t WHERE LOWER(col1) > "a" AND LOWER(col1) < "b";
SELECT * FROM t WHERE LOWER(col1) > "b" OR LOWER(col1) < "a";

クエリが同じ式でソートされている場合、式インデックスが適用されます。次の文を例に挙げましょう。

SELECT * FROM t ORDER BY LOWER(col1);

同じ式が集約関数（ GROUP BY ）に含まれている場合、式のインデックスが適用されます。次の文を例に挙げましょう。

SELECT MAX(LOWER(col1)) FROM t;
SELECT MIN(col1) FROM t GROUP BY LOWER(col1);

式インデックスに対応する式を確認するには、 SHOW INDEX実行するか、システムテーブルinformation_schema.tidb_indexesとテーブルinformation_schema.STATISTICSを確認してください。出力のExpression列が対応する式を示します。式以外のインデックスの場合、この列にはNULL表示されます。

式インデックスの維持コストは、行が挿入または更新されるたびに式の値を計算する必要があるため、他のインデックスの維持コストよりも高くなります。式の値は既にインデックスに格納されているため、オプティマイザが式インデックスを選択する際に再計算する必要はありません。

したがって、クエリのパフォーマンスが挿入および更新のパフォーマンスを上回る場合は、式のインデックス作成を検討できます。

式インデックスはMySQLと同じ構文と制限事項を持ちます。式インデックスは、生成された非表示の仮想列にインデックスを作成することで実装されるため、サポートされる式はすべて仮想生成列の制限継承します。

多値インデックス

多値インデックスは、配列の列に定義されるセカンダリインデックスの一種です。通常のインデックスでは、1つのインデックスレコードが1つのデータレコードに対応します（1:1）。多値インデックスでは、複数のインデックスレコードが1つのデータレコードに対応します（N:1）。多値インデックスはJSON配列のインデックス作成に使用されます。例えば、 zipcodeフィールドに多値インデックスを定義すると、配列zipcode各要素に対して1つのインデックスレコードが生成されます。

{
    "user":"Bob",
    "user_id":31,
    "zipcode":[94477,94536]
}

複数値インデックスを作成する

式インデックスを作成する場合と同様に、インデックス定義でCAST(... AS ... ARRAY)関数を使用して、複数値インデックスを作成できます。

mysql> CREATE TABLE customers (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name CHAR(10),
    custinfo JSON,
    INDEX zips((CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)))
);

複数値インデックスを一意のインデックスとして定義できます。

mysql> CREATE TABLE customers (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name CHAR(10),
    custinfo JSON,
    UNIQUE INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)))
);

複数値インデックスが一意のインデックスとして定義されている場合、重複するデータを挿入しようとするとエラーが報告されます。

mysql> INSERT INTO customers VALUES (1, 'pingcap', '{"zipcode": [1,2]}');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO customers VALUES (1, 'pingcap', '{"zipcode": [2,3]}');
ERROR 1062 (23000): Duplicate entry '2' for key 'customers.zips'

同じレコードに重複した値が存在する可能性がありますが、異なるレコードに重複した値が存在する場合はエラーが報告されます。

-- Insert succeeded
mysql> INSERT INTO t1 VALUES('[1,1,2]');
mysql> INSERT INTO t1 VALUES('[3,3,3,4,4,4]');

-- Insert failed
mysql> INSERT INTO t1 VALUES('[1,2]');
mysql> INSERT INTO t1 VALUES('[2,3]');

複数値インデックスを複合インデックスとして定義することもできます。

mysql> CREATE TABLE customers (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name CHAR(10),
    custinfo JSON,
    INDEX zips(name, (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)))
);

複数値インデックスが複合インデックスとして定義されている場合、複数値部分は任意の位置に出現できますが、出現できるのは 1 回だけです。

mysql> CREATE TABLE customers (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name CHAR(10),
    custinfo JSON,
    INDEX zips(name, (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)), (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)))
);
ERROR 1235 (42000): This version of TiDB doesn't yet support 'more than one multi-valued key part per index'.

書き込まれるデータは、複数値インデックスで定義された型と完全に一致する必要があります。一致しない場合、データの書き込みは失敗します。

-- All elements in the zipcode field must be the UNSIGNED type.
mysql> INSERT INTO customers VALUES (1, 'pingcap', '{"zipcode": [-1]}');
ERROR 3752 (HY000): Value is out of range for expression index 'zips' at row 1

mysql> INSERT INTO customers VALUES (1, 'pingcap', '{"zipcode": ["1"]}'); -- Incompatible with MySQL
ERROR 3903 (HY000): Invalid JSON value for CAST for expression index 'zips'

mysql> INSERT INTO customers VALUES (1, 'pingcap', '{"zipcode": [1]}');
Query OK, 1 row affected (0.00 sec)

複数値インデックスを使用する

詳細はインデックスの選択 - 複数値インデックスを使用するご覧ください。

制限事項

• 空の JSON 配列の場合、対応するインデックス レコードは生成されません。
• CAST(... AS ... ARRAY)のターゲットタイプはBINARY 、 JSON 、 YEAR 、 FLOAT 、 DECIMALのいずれにもできません。ソースタイプは JSON である必要があります。
• 並べ替えに複数値インデックスを使用することはできません。
• 複数値インデックスを作成できるのは JSON 配列のみです。
• 複数値インデックスは主キーまたは外部キーにすることはできません。
• 複数値インデックスによって使用される追加のstorageスペース = 行あたりの配列要素の平均数 * 通常のセカンダリ インデックスによって使用されるスペース。
• 通常のインデックスと比較すると、DML 操作では複数値インデックスのインデックス レコードがより多く変更されるため、複数値インデックスは通常のインデックスよりもパフォーマンスに大きな影響を与えます。
• 複数値インデックスは特殊なタイプの式インデックスであるため、複数値インデックスには式インデックスと同じ制限があります。
• テーブルで複数値インデックスが使用されている場合、 BR、TiCDC、またはTiDB Lightningを使用して、v6.6.0 より前の TiDB クラスターにテーブルをバックアップ、複製、またはインポートすることはできません。
• 複雑な条件を持つクエリの場合、TiDBは複数値インデックスを選択できない可能性があります。複数値インデックスでサポートされる条件パターンについては、 複数値インデックスを使用するを参照してください。

目に見えないインデックス

デフォルトでは、非表示のインデックスはクエリ オプティマイザーによって無視されるインデックスです。

CREATE TABLE t1 (c1 INT, c2 INT, UNIQUE(c2));
CREATE UNIQUE INDEX c1 ON t1 (c1) INVISIBLE;

TiDB v8.0.0 以降では、システム変数tidb_opt_use_invisible_indexes変更することで、オプティマイザーが非表示のインデックスを選択するようにすることができます。

詳細はALTER INDEX参照。

関連するシステム変数

CREATE INDEX文に関連付けられているシステム変数はtidb_ddl_enable_fast_reorg 、 tidb_ddl_reorg_worker_cnt 、 tidb_ddl_reorg_batch_size 、 tidb_enable_auto_increment_in_generated 、 tidb_ddl_reorg_priorityです。詳細はシステム変数を参照してください。

MySQLの互換性

• TiDB はFULLTEXT構文の解析をサポートしていますが、 FULLTEXT 、 HASH 、およびSPATIALインデックスの使用はサポートしていません。
• TiDB RTREE BTREE HASHインデックス タイプを受け入れますが、それらを無視します。
• 降順インデックスはサポートされていません ( MySQL 5.7と同様)。
• CLUSTERED型の主キーをテーブルに追加することはサポートされていません。3 型CLUSTERED主キーの詳細については、 クラスター化インデックスを参照してください。
• 式インデックスはビューと互換性がありません。ビューを使用してクエリを実行する場合、式インデックスを同時に使用することはできません。
• 式インデックスには、バインディングとの互換性に関する問題があります。式インデックスの式に定数が含まれる場合、対応するクエリに対して作成されるバインディングのスコープが拡張されます。例えば、式インデックス内の式がa+1で、対応するクエリ条件がa+1 > 2あるとします。この場合、作成されるバインディングはa+? > ?です。つまり、 a+2 > 2などの条件を持つクエリも式インデックスの使用を強制され、実行プランの品質が低下します。さらに、これはSQLプラン管理（SPM）におけるベースラインキャプチャとベースライン進化にも影響を及ぼします。
• 多値インデックスで書き込まれるデータは、定義されたデータ型と完全に一致する必要があります。一致しない場合、データの書き込みは失敗します。詳細については、 複数値インデックスを作成する参照してください。
• GLOBALインデックス オプションを使用してUNIQUE KEY グローバルインデックスとして設定することは、 パーティションテーブルの TiDB 拡張であり、MySQL とは互換性がありません。

参照

• インデックスの選択
• インデックス問題の解決方法
• インデックスを追加
• インデックスの削除
• インデックス名の変更
• インデックスの変更
• 列を追加
• テーブルの作成
• EXPLAIN