我的书签
添加书签
移除书签CREATE INDEX
CREATE INDEX 语句用于在已有表中添加新索引，功能等同于 ALTER TABLE .. ADD INDEX。包含该语句提供了 MySQL 兼容性。
语法图
1. CreateIndexStmt ::=
2. 'CREATE' IndexKeyTypeOpt 'INDEX' IfNotExists Identifier IndexTypeOpt 'ON' TableName '(' IndexPartSpecificationList ')' IndexOptionList IndexLockAndAlgorithmOpt
4. IndexKeyTypeOpt ::=
5. ( 'UNIQUE' | 'SPATIAL' | 'FULLTEXT' )?
7. IfNotExists ::=
8. ( 'IF' 'NOT' 'EXISTS' )?
10. IndexTypeOpt ::=
11. IndexType?
13. IndexPartSpecificationList ::=
14. IndexPartSpecification ( ',' IndexPartSpecification )*
16. IndexOptionList ::=
17. IndexOption*
19. IndexLockAndAlgorithmOpt ::=
20. ( LockClause AlgorithmClause? | AlgorithmClause LockClause? )?
22. IndexType ::=
23. ( 'USING' | 'TYPE' ) IndexTypeName
25. IndexPartSpecification ::=
26. ( ColumnName OptFieldLen | '(' Expression ')' ) Order
28. IndexOption ::=
29. 'KEY_BLOCK_SIZE' '='? LengthNum
30. | IndexType
31. | 'WITH' 'PARSER' Identifier
32. | 'COMMENT' stringLit
33. | IndexInvisible
35. IndexTypeName ::=
36. 'BTREE'
37. | 'HASH'
38. | 'RTREE'
40. ColumnName ::=
41. Identifier ( '.' Identifier ( '.' Identifier )? )?
43. OptFieldLen ::=
44. FieldLen?
46. IndexNameList ::=
47. ( Identifier | 'PRIMARY' )? ( ',' ( Identifier | 'PRIMARY' ) )*
49. KeyOrIndex ::=
50. 'Key' | 'Index'
示例
1. CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, c1 INT NOT NULL);
1. Query OK, 0 rows affected (0.10 sec)
1. INSERT INTO t1 (c1) VALUES (1),(2),(3),(4),(5);
1. Query OK, 5 rows affected (0.02 sec)
2. Records: 5 Duplicates: 0 Warnings: 0
1. EXPLAIN SELECT * FROM t1 WHERE c1 = 3;
1. +-------------------------+----------+-----------+---------------+--------------------------------+
2. | id | estRows | task | access object | operator info |
3. +-------------------------+----------+-----------+---------------+--------------------------------+
4. | TableReader_7 | 10.00 | root | | data:Selection_6 |
5. | └─Selection_6 | 10.00 | cop[tikv] | | eq(test.t1.c1, 3) |
6. | └─TableFullScan_5 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
7. +-------------------------+----------+-----------+---------------+--------------------------------+
8. 3 rows in set (0.00 sec)
1. CREATE INDEX c1 ON t1 (c1);
1. Query OK, 0 rows affected (0.30 sec)
1. EXPLAIN SELECT * FROM t1 WHERE c1 = 3;
1. +------------------------+---------+-----------+------------------------+---------------------------------------------+
2. | id | estRows | task | access object | operator info |
3. +------------------------+---------+-----------+------------------------+---------------------------------------------+
4. | IndexReader_6 | 10.00 | root | | index:IndexRangeScan_5 |
5. | └─IndexRangeScan_5 | 10.00 | cop[tikv] | table:t1, index:c1(c1) | range:[3,3], keep order:false, stats:pseudo |
6. +------------------------+---------+-----------+------------------------+---------------------------------------------+
7. 2 rows in set (0.00 sec)
1. ALTER TABLE t1 DROP INDEX c1;
1. Query OK, 0 rows affected (0.30 sec)
1. CREATE UNIQUE INDEX c1 ON t1 (c1);
1. Query OK, 0 rows affected (0.31 sec)
表达式索引
在一些场景中，查询的条件往往是基于某个表达式进行过滤。在这些场景中，一般的索引不能生效，执行查询只能遍历整个表，导致查询性能较差。表达式索引是一种特殊的索引，能将索引建立于表达式上。在创建了表达式索引后，基于表达式的查询便可以使用上索引，极大提升查询的性能。
假设要基于 lower(col1) 这个表达式建立索引，示例的 SQL 语句如下：
1. CREATE INDEX idx1 ON t1 ((lower(col1)));
或者等价的语句：
1. ALTER TABLE t1 ADD INDEX idx1((lower(col1)));
还可以在建表的同时指定表达式索引：
1. CREATE TABLE t1(col1 char(10), col2 char(10), index((lower(col1))));
注意：
表达式索引中的表达式需要用
(和)包围起来，否则会报语法错误。
删除表达式索引与删除普通索引的方法一致：
1. DROP INDEX idx1 ON t1;
注意：
表达式索引涉及众多表达式。为了确保正确性，当前仅允许经充分测试的一部分函数用于创建表达式索引，即生产环境中仅允许表达式中包含这些函数。这些函数可以通过查询变量
tidb_allow_function_for_expression_index得到。在后续版本中，这些函数会持续增加。
1. mysql> select @@tidb_allow_function_for_expression_index; 2. +--------------------------------------------+ 3. | @@tidb_allow_function_for_expression_index | 4. +--------------------------------------------+ 5. | lower, md5, reverse, upper, vitess_hash | 6. +--------------------------------------------+ 7. 1 row in set (0.00 sec)对于以上变量返回结果之外的函数，由于未完成充分测试，当前仍为实验特性，不建议在生产环境中使用。其他的表达式例如运算符、
cast和case when也同样为实验特性，不建议在生产环境中使用。如果仍然希望使用，可以在 TiDB 配置文件中进行以下设置：
1. allow-expression-index = true表达式索引不能为主键。
表达式索引中的表达式不能包含以下内容：
- 易变函数，例如
rand()和now()等。- 系统变量以及用户变量。
- 子查询。
- AUTO_INCREMENT 属性的列。一个例外是设置系统变量
tidb_enable_auto_increment_in_generated为true后，可以去掉该限制。- 窗口函数。
- row 函数。例如
create table t (j json, key k (((j,j))));。- 聚合函数。
表达式索引将隐式占用名字，
_V$_{index_name}_{index_offset}，如果已有相同名字的列存在，创建表达式索引将报错。如果后续新增相同名字的列，也会报错。在表达式索引中，表达式的函数参数个数必须正确。
当索引的表达式使用了字符串相关的函数时，受返回类型以及其长度的影响，创建表达式索引可能会失败。这时可以使用
cast()函数显式指定返回的类型以及长度。例如表达式repeat(a, 3)，为了能根据该表达式建立表达式索引，需要将表达式改写为cast(repeat(a, 3) as char(20))这样的形式。
当查询语句中的表达式与表达式索引中的表达式一致时，优化器可以为该查询选择使用表达式索引。依赖于统计信息，某些情况下优化器不一定选择表达式索引。这时可以通过 hint 指定强制使用表达式索引。
在以下示例中，假设建立在 lower(col1) 表达式上的索引为 idx。
当读取的结果为相同的表达式时，可以使用表达式索引。例如：
1. SELECT lower(col1) FROM t;
当过滤的条件中有相同的表达式时，可以使用表达式索引。例如：
1. SELECT * FROM t WHERE lower(col1) = "a";
2. SELECT * FROM t WHERE lower(col1) > "a";
3. SELECT * FROM t WHERE lower(col1) BETWEEN "a" AND "b";
4. SELECT * FROM t WHERE lower(col1) in ("a", "b");
5. SELECT * FROM t WHERE lower(col1) > "a" AND lower(col1) < "b";
6. SELECT * FROM t WHERE lower(col1) > "b" OR lower(col1) < "a";
当查询按照相同的表达式进行排序时，可以使用表达式索引。例如：
1. SELECT * FROM t ORDER BY lower(col1);
当聚合函数或者 GROUP BY 中包含相同的表达式时，可以使用表达式索引。例如：
1. SELECT max(lower(col1)) FROM t；
2. SELECT min(col1) FROM t GROUP BY lower(col1);
要查看表达式索引对应的表达式，可执行 show index 或查看系统表 information_schema.tidb_indexes 以及 information_schema.STATISTICS 表，输出中 Expression 这一列显示对应的表达式。对于非表达式索引，该列的值为 NULL。
维护表达式索引的代价比一般的索引更高，因为在插入或者更新每一行时都需要计算出表达式的值。因为表达式的值已经存储在索引中，所以当优化器选择表达式索引时，表达式的值就不需要再计算。因此，当查询速度比插入速度和更新速度更重要时，可以考虑建立表达式索引。
表达式索引的语法和限制与 MySQL 相同，是通过将索引建立在隐藏的虚拟生成列 (generated virtual column) 上来实现的。因此所支持的表达式继承了虚拟生成列的所有限制。
不可见索引
不可见索引 (Invisible Indexes) 不会被查询优化器使用：
1. CREATE TABLE t1 (c1 INT, c2 INT, UNIQUE(c2));
2. CREATE UNIQUE INDEX c1 ON t1 (c1) INVISIBLE;
具体可以参考 ALTER INDEX。
相关系统变量
和 CREATE INDEX 语句相关的系统变量有 tidb_ddl_reorg_worker_cnt 、tidb_ddl_reorg_batch_size 、tidb_ddl_reorg_priority 和 tidb_enable_auto_increment_in_generated，具体可以参考系统变量。
MySQL 兼容性
- 不支持
FULLTEXT，HASH和SPATIAL索引。 - 不支持降序索引 （类似于 MySQL 5.7）。
- 无法向表中添加
CLUSTERED类型的PRIMARY KEY。要了解关于CLUSTERED主键的详细信息，请参考聚簇索引。 - 表达式索引与视图存在兼容性问题。通过视图进行查询时，无法使用上表达式索引。
- 表达式索引与 Binding 存在兼容性问题。当表达式索引中的表达式存在常量时，对应查询所建的 Binding 会扩大范围。假设表达式索引中的表达式为
a+1，对应的查询条件为a+1 > 2。则建立的 Binding 为a+? > ?，这会导致像a+2 > 2这样的查询也会强制使用表达式索引，得到一个较差的执行计划。这同样影响 SQL Plan Management (SPM) 中的捕获和演进功能。
