CREATE INDEX

CREATE INDEX 语句用于在已有表中添加新索引,功能等同于 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

示例

CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, c1 INT NOT NULL);
Query OK, 0 rows affected (0.10 sec)
INSERT INTO t1 (c1) VALUES (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.02 sec) Records: 5 Duplicates: 0 Warnings: 0
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)
CREATE INDEX c1 ON t1 (c1);
Query OK, 0 rows affected (0.30 sec)
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)
ALTER TABLE t1 DROP INDEX c1;
Query OK, 0 rows affected (0.30 sec)
CREATE UNIQUE INDEX c1 ON t1 (c1);
Query OK, 0 rows affected (0.31 sec)

表达式索引

在一些场景中,查询的条件往往是基于某个表达式进行过滤。在这些场景中,一般的索引不能生效,执行查询只能遍历整个表,导致查询性能较差。表达式索引是一种特殊的索引,能将索引建立于表达式上。在创建了表达式索引后,基于表达式的查询便可以使用上索引,极大提升查询的性能。

假设要基于 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;

当查询语句中的表达式与表达式索引中的表达式一致时,优化器可以为该查询选择使用表达式索引。依赖于统计信息,某些情况下优化器不一定选择表达式索引。这时可以通过 hint 指定强制使用表达式索引。

在以下示例中,假设建立在 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 相同,是通过将索引建立在隐藏的虚拟生成列 (generated virtual column) 上来实现的。因此所支持的表达式继承了虚拟生成列的所有限制

不可见索引

不可见索引 (Invisible Indexes) 不会被查询优化器使用:

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_cnttidb_ddl_reorg_batch_sizetidb_ddl_reorg_prioritytidb_enable_auto_increment_in_generated,具体可以参考系统变量

MySQL 兼容性

  • TiDB 支持解析 FULLTEXTSPATIAL 语法,但尚不支持使用 FULLTEXTHASHSPATIAL 索引。
  • 不支持降序索引 (类似于 MySQL 5.7)。
  • 无法向表中添加 CLUSTERED 类型的 PRIMARY KEY。要了解关于 CLUSTERED 主键的详细信息,请参考聚簇索引
  • 表达式索引与视图存在兼容性问题。通过视图进行查询时,无法使用上表达式索引。
  • 表达式索引与 Binding 存在兼容性问题。当表达式索引中的表达式存在常量时,对应查询所建的 Binding 会扩大范围。假设表达式索引中的表达式为 a+1,对应的查询条件为 a+1 > 2。则建立的 Binding 为 a+? > ?,这会导致像 a+2 > 2 这样的查询也会强制使用表达式索引,得到一个较差的执行计划。这同样影响 SQL Plan Management (SPM) 中的捕获和演进功能。

另请参阅

下载 PDF
产品
TiDB
TiDB Cloud
© 2024 PingCAP. All Rights Reserved.
Privacy Policy.