PingKai Logo下载
搜索

CREATE INDEX

该语句用于为已有表添加一个新索引。它是 ALTER TABLE .. ADD INDEX 的另一种语法形式，并为 MySQL 兼容性而提供。

语法

CreateIndexStmt
CREATE IndexKeyTypeOpt INDEX IfNotExists Identifier IndexTypeOpt ON TableName ( IndexPartSpecificationList ) IndexOptionList IndexLockAndAlgorithmOpt
IndexKeyTypeOpt
UNIQUE SPATIAL FULLTEXT
IfNotExists
IF NOT EXISTS
IndexTypeOpt
IndexType
IndexPartSpecificationList
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 WHERE Expression
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 变量获取这些函数。目前允许的函数如下：

对于未包含在上述列表中的函数，这些函数尚未经过充分测试，不建议在生产环境中使用，可视为实验性特性。其他表达式如运算符、CAST、CASE WHEN 也属于实验性特性，不建议在生产环境中使用。

注意

表达式索引不能创建在主键上。

表达式索引中的表达式不能包含以下内容：

表达式索引会隐式占用一个名称（例如 _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）。而在多值索引中，多条索引记录对应一条数据记录（N:1）。多值索引用于为 JSON 数组建立索引。例如，在 zipcode 字段上定义多值索引时，zipcode 数组中的每个元素都会生成一条索引记录。

{
    "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'

同一条记录可以有重复值，但不同记录有重复值时会报错。

-- 插入成功
mysql> INSERT INTO t1 VALUES('[1,1,2]');
mysql> INSERT INTO t1 VALUES('[3,3,3,4,4,4]');

-- 插入失败
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)))
);

当多值索引被定义为组合索引时，多值部分可以出现在任意位置，但只能出现一次。

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'.

写入的数据必须与多值索引定义的类型完全一致，否则写入数据会失败：

-- zipcode 字段中的所有元素必须为 UNSIGNED 类型。
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"]}'); -- 与 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 数组上创建多值索引。
  • 多值索引不能作为主键或外键。
  • 多值索引额外占用的存储空间 = 每行数组元素的平均个数 × 普通二级索引占用的空间。
  • 与普通索引相比，DML 操作会修改更多的多值索引记录，因此多值索引对性能的影响大于普通索引。
  • 由于多值索引属于特殊类型的表达式索引，因此多值索引具有与表达式索引相同的限制。
  • 如果表使用了多值索引，不能通过 BR、TiCDC 或 TiDB Lightning 将该表备份、同步或导入到 v6.6.0 之前的 TiDB 集群。
  • 对于包含复杂条件的查询，TiDB 可能无法选择多值索引。关于多值索引支持的条件模式，参见 使用多值索引。

部分索引 从 v8.5.7 版本开始引入

部分索引是建立在表中部分行子集上的索引。创建部分索引时，你可以指定一个条件表达式，也称为谓词，用于定义这个行子集。索引中仅包含满足该谓词的行的条目。

使用场景

在以下场景中，使用部分索引有助于提升查询性能或减少索引维护开销：

  • 选择性过滤：当你经常基于特定条件查询一小部分行时，可以使用部分索引。对于满足部分索引谓词的查询，TiDB 可以使用部分索引来避免扫描无关行，并减少索引占用的存储空间。
  • 条件唯一性：当你只需要对满足特定条件的行强制执行唯一性约束时，可以使用唯一部分索引，以避免将唯一性约束应用到整张表。
  • 减少 DML 开销：当许多 INSERT、UPDATE 或 DELETE 操作影响到不需要建立索引的行时，可以使用部分索引。与维护完整索引相比，维护部分索引可以减少索引维护开销。

创建部分索引

你可以通过在索引定义中添加 WHERE 子句来创建部分索引。例如：

CREATE TABLE t1 (c1 INT, c2 INT, c3 TEXT);
CREATE INDEX idx1 ON t1 (c1) WHERE c2 > 10;

你也可以使用 ALTER TABLE 创建部分索引：

ALTER TABLE t1 ADD INDEX idx2 (c1, c2) WHERE c3 = 'abc';

你还可以在创建表时指定部分索引：

CREATE TABLE t2 (
    id INT PRIMARY KEY,
    status VARCHAR(20),
    created_at DATETIME,
    INDEX idx_active_status (status) WHERE status = 'active'
);

使用示例

以下示例演示了如何有效使用部分索引：

创建一个包含用户数据的表：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    status VARCHAR(20),
    created_at DATETIME,
    score INT
);

为常见查询模式创建部分索引：

CREATE INDEX idx_active_users ON users (name) WHERE status = 'active';
CREATE INDEX idx_high_score_users ON users (created_at) WHERE score > 1000;
CREATE INDEX idx_pending_status ON users (status) WHERE status = 'pending';

然后，以下查询可以使用部分索引：

mysql> EXPLAIN SELECT * FROM users WHERE status = 'active' AND name = 'John';
+-------------------------------+---------+-----------+-------------------------------------------+-------------------------------------------------------+
| id                            | estRows | task      | access object                             | operator info                                         |
+-------------------------------+---------+-----------+-------------------------------------------+-------------------------------------------------------+
| IndexLookUp_9                 | 1.00    | root      |                                           |                                                       |
| ├─IndexRangeScan_6(Build)     | 10.00   | cop[tikv] | table:users, index:idx_active_users(name) | range:["John","John"], keep order:false, stats:pseudo |
| └─Selection_8(Probe)          | 1.00    | cop[tikv] |                                           | eq(test.users.status, "active")                       |
|   └─TableRowIDScan_7          | 10.00   | cop[tikv] | table:users                               | keep order:false, stats:pseudo                        |
+-------------------------------+---------+-----------+-------------------------------------------+-------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM users WHERE status = 'active' ORDER BY name;
+-------------------------------+----------+-----------+-------------------------------------------+---------------------------------+
| id                            | estRows  | task      | access object                             | operator info                   |
+-------------------------------+----------+-----------+-------------------------------------------+---------------------------------+
| IndexLookUp_18                | 10.00    | root      |                                           |                                 |
| ├─IndexFullScan_15(Build)     | 10000.00 | cop[tikv] | table:users, index:idx_active_users(name) | keep order:true, stats:pseudo   |
| └─Selection_17(Probe)         | 10.00    | cop[tikv] |                                           | eq(test.users.status, "active") |
|   └─TableRowIDScan_16         | 10000.00 | cop[tikv] | table:users                               | keep order:false, stats:pseudo  |
+-------------------------------+----------+-----------+-------------------------------------------+---------------------------------+
4 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM users WHERE score > 10000 ORDER BY created_at;
+-------------------------------+----------+-----------+-----------------------------------------------------+--------------------------------+
| id                            | estRows  | task      | access object                                       | operator info                  |
+-------------------------------+----------+-----------+-----------------------------------------------------+--------------------------------+
| IndexLookUp_18                | 3333.33  | root      |                                                     |                                |
| ├─IndexFullScan_15(Build)     | 10000.00 | cop[tikv] | table:users, index:idx_high_score_users(created_at) | keep order:true, stats:pseudo  |
| └─Selection_17(Probe)         | 3333.33  | cop[tikv] |                                                     | gt(test.users.score, 10000)     |
|   └─TableRowIDScan_16         | 10000.00 | cop[tikv] | table:users                                         | keep order:false, stats:pseudo |
+-------------------------------+----------+-----------+-----------------------------------------------------+--------------------------------+
4 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM users WHERE status = 'pending';
+-------------------------------+---------+-----------+-----------------------------------------------+-------------------------------------------------------------+
| id                            | estRows | task      | access object                                 | operator info                                               |
+-------------------------------+---------+-----------+-----------------------------------------------+-------------------------------------------------------------+
| IndexLookUp_7                 | 10.00   | root      |                                               |                                                             |
| ├─IndexRangeScan_5(Build)     | 10.00   | cop[tikv] | table:users, index:idx_pending_status(status) | range:["pending","pending"], keep order:false, stats:pseudo |
| └─TableRowIDScan_6(Probe)     | 10.00   | cop[tikv] | table:users                                   | keep order:false, stats:pseudo                              |
+-------------------------------+---------+-----------+-----------------------------------------------+-------------------------------------------------------------+
3 rows in set (0.00 sec)

如果查询的谓词不满足部分索引定义的条件，TiDB 即使在使用 hint 的情况下也不会选择部分索引。例如，以下语句无法使用部分索引 idx_high_score_users，因为查询谓词 score > 100 不满足部分索引定义 score > 1000：

mysql> EXPLAIN SELECT * FROM users USE INDEX(idx_high_score_users) WHERE score > 100 ORDER BY created_at;
+---------------------------+----------+-----------+---------------+--------------------------------+
| id                        | estRows  | task      | access object | operator info                  |
+---------------------------+----------+-----------+---------------+--------------------------------+
| Sort_5                    | 3333.33  | root      |               | test.users.created_at          |
| └─TableReader_10          | 3333.33  | root      |               | data:Selection_9               |
|   └─Selection_9           | 3333.33  | cop[tikv] |               | gt(test.users.score, 100)      |
|     └─TableFullScan_8     | 10000.00 | cop[tikv] | table:users   | keep order:false, stats:pseudo |
+---------------------------+----------+-----------+---------------+--------------------------------+

限制

  • 部分索引中的 WHERE 子句支持基本比较运算符（=、!=、<、<=、>、>=）、IS NULL、IS NOT NULL 以及带常量值的 IN 谓词。
  • 谓词中的列和常量值必须是相同的数据类型。
  • 谓词只能引用同一张表中的列。
  • 不能在表达式索引上创建部分索引。

隐式索引

默认情况下，隐式索引是被查询优化器忽略的索引：

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 索引。

    注意

    目前，仅部分 AWS 区域的平凯数据库云服务 Starter 实例支持 FULLTEXT 语法和索引。

  • TiDB 为兼容 MySQL，语法上接受 HASH、BTREE 和 RTREE 等索引类型，但会忽略它们。

  • 不支持降序索引（与 MySQL 5.7 类似）。

  • 不支持为表添加 CLUSTERED 类型的主键。关于 CLUSTERED 类型主键的更多信息，参见 聚簇索引。

  • 表达式索引与视图不兼容。通过视图执行查询时，不能同时使用表达式索引。

  • 表达式索引与绑定存在兼容性问题。当表达式索引的表达式中包含常数时，为对应查询创建的绑定会扩展其作用域。例如，假设表达式索引的表达式为 a+1，对应的查询条件为 a+1 > 2，此时创建的绑定为 a+? > ?，这意味着带有条件如 a+2 > 2 的查询也会被强制使用表达式索引，导致执行计划不佳。此外，这也会影响 SQL Plan Management (SPM) 中的基线捕获和基线演进。

  • 使用多值索引写入的数据必须与定义的数据类型完全一致，否则写入数据会失败。详情参见 创建多值索引。

  • 将 UNIQUE KEY 作为 全局索引 并通过 GLOBAL 索引选项设置，是 TiDB 针对 分区表 的扩展，不兼容 MySQL。

另请参阅