TiDB Cloud
TiDB Self-Managed
Learning Center
お問い合わせ

インデックスの作成

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

あらすじ

CreateIndexStmt
CREATEIndexKeyTypeOptINDEXIfNotExistsIdentifierIndexTypeOptONTableName(IndexPartSpecificationList)IndexOptionListIndexLockAndAlgorithmOpt
IndexKeyTypeOpt
UNIQUESPATIALFULLTEXT
IfNotExists
IFNOTEXISTS
IndexTypeOpt
IndexType
IndexPartSpecificationList
IndexPartSpecification,
IndexOptionList
IndexOption
IndexLockAndAlgorithmOpt
LockClauseAlgorithmClauseAlgorithmClauseLockClause
IndexType
USINGTYPEIndexTypeName
IndexPartSpecification
ColumnNameOptFieldLen(Expression)Order
IndexOption
KEY_BLOCK_SIZE=LengthNumIndexTypeWITHPARSERIdentifierCOMMENTstringLitIndexInvisible
IndexTypeName
BTREEHASHRTREE
ColumnName
Identifier.Identifier.Identifier
OptFieldLen
FieldLen
IndexNameList
IdentifierPRIMARY,IdentifierPRIMARY
KeyOrIndex
KeyIndex

例

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の変数をクエリすることで取得できます。将来のバージョンでは、さらに多くの関数がリストに追加される可能性があります。

mysql> select @@tidb_allow_function_for_expression_index;
+--------------------------------------------+
| @@tidb_allow_function_for_expression_index |
+--------------------------------------------+
| lower, md5, reverse, upper, vitess_hash    |
+--------------------------------------------+
1 row in set (0.00 sec)

上記の戻り結果に含まれていない関数の場合、これらの関数は完全にテストされておらず、実験的ものと見なすことができる実稼働環境には推奨されません。演算子、 castなどの他の式も実験的ものと見case when 、本番環境には推奨されません。

これらの実験的式を引き続き使用する場合は、 TiDB構成ファイルで次の構成を行うことができます。

allow-expression-index = true

ノート：

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

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

  • rand()やnow()などの揮発性関数。
  • システム変数とユーザー変数。
  • サブクエリ。
  • AUTO_INCREMENT列。 tidb_enable_auto_increment_in_generated （システム変数）の値をtrueに設定することで、この制限を取り除くことができます。
  • ウィンドウ関数。
  • create table t (j json, key k (((j,j))));などのROW関数。
  • 集計関数。

式インデックスは暗黙的に名前を取ります（たとえば、 _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);

同じ式がaggregate（ 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と同じ構文と制限があります。これらは、生成された非表示の仮想列にインデックスを作成することで実装されるため、サポートされている式はすべて仮想生成列の制限を継承します。

見えないインデックス

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

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

詳細については、 ALTER INDEXを参照してください。

関連するシステム変数

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

MySQLの互換性

  • FULLTEXT 、およびHASHのインデックスはサポートさSPATIALていません。
  • 降順インデックスはサポートされていません（MySQL 5.7と同様）。
  • CLUSTEREDタイプの主キーをテーブルに追加することはサポートされていません。 CLUSTEREDタイプの主キーの詳細については、 クラスター化されたインデックスを参照してください。
  • 式インデックスはビューと互換性がありません。ビューを使用してクエリを実行する場合、式インデックスを同時に使用することはできません。
  • 式インデックスには、バインディングとの互換性の問題があります。式インデックスの式に定数がある場合、対応するクエリに対して作成されたバインディングはそのスコープを拡張します。たとえば、式インデックスの式がa+1であり、対応するクエリ条件がa+1 > 2であるとします。この場合、作成されたバインディングはa+? > ?です。これは、 a+2 > 2などの条件を持つクエリも式インデックスを使用するように強制され、実行プランが不十分になることを意味します。さらに、これはSQL Plan Management（SPM）のベースラインキャプチャとベースライン進化にも影響します。

も参照してください

このページは役に立ちましたか？