聚簇索引

聚簇索引 (clustered index) 是 TiDB 从 v5.0 开始支持的特性，用于控制含有主键的表数据的存储方式。通过使用聚簇索引，TiDB 可以更好地组织数据表，从而提高某些查询的性能。有些数据库管理系统也将聚簇索引称为“索引组织表” (index-organized tables)。

目前 TiDB 中含有主键的表分为以下两类：

  • NONCLUSTERED，表示该表的主键为非聚簇索引。在非聚簇索引表中，行数据的键由 TiDB 内部隐式分配的 _tidb_rowid 构成，而主键本质上是唯一索引，因此非聚簇索引表存储一行至少需要两个键值对，分别为
    • _tidb_rowid（键）- 行数据（值）
    • 主键列数据（键） - _tidb_rowid（值）
  • CLUSTERED，表示该表的主键为聚簇索引。在聚簇索引表中，行数据的键由用户给定的主键列数据构成，因此聚簇索引表存储一行至少只要一个键值对，即
    • 主键列数据（键） - 行数据（值）

注意：

TiDB 仅支持根据表的主键来进行聚簇操作。聚簇索引启用时，“主键”和“聚簇索引”两个术语在一些情况下可互换使用。主键指的是约束（一种逻辑属性），而聚簇索引描述的是数据存储的物理实现。

使用场景

相较于非聚簇索引表，聚簇索引表在以下几个场景中，性能和吞吐量都有较大优势：

  • 插入数据时会减少一次从网络写入索引数据。
  • 等值条件查询仅涉及主键时会减少一次从网络读取数据。
  • 范围条件查询仅涉及主键时会减少多次从网络读取数据。
  • 等值或范围条件查询仅涉及主键的前缀时会减少多次从网络读取数据。

另一方面，聚簇索引表也存在一定的劣势：

  • 批量插入大量取值相邻的主键时，可能会产生较大的写热点问题。
  • 当使用大于 64 位的数据类型作为主键时，可能导致表数据需要占用更多的存储空间。该现象在存在多个二级索引时尤为明显。

使用方法

创建聚簇索引表

从 TiDB 版本 5.0 开始，要指定一个表的主键是否使用聚簇索引，可以在 CREATE TABLE 语句中将 CLUSTERED 或者 NONCLUSTERED 非保留关键字标注在 PRIMARY KEY 后面，例如：

  1. CREATE TABLE t (a BIGINT PRIMARY KEY CLUSTERED, b VARCHAR(255));
  2. CREATE TABLE t (a BIGINT PRIMARY KEY NONCLUSTERED, b VARCHAR(255));
  3. CREATE TABLE t (a BIGINT KEY CLUSTERED, b VARCHAR(255));
  4. CREATE TABLE t (a BIGINT KEY NONCLUSTERED, b VARCHAR(255));
  5. CREATE TABLE t (a BIGINT, b VARCHAR(255), PRIMARY KEY(a, b) CLUSTERED);
  6. CREATE TABLE t (a BIGINT, b VARCHAR(255), PRIMARY KEY(a, b) NONCLUSTERED);

注意，列定义中的 KEY 和 PRIMARY KEY 含义相同。

此外，TiDB 支持使用可执行的注释语法指定聚簇索引属性：

  1. CREATE TABLE t (a BIGINT PRIMARY KEY /*T![clustered_index] CLUSTERED */, b VARCHAR(255));
  2. CREATE TABLE t (a BIGINT PRIMARY KEY /*T![clustered_index] NONCLUSTERED */, b VARCHAR(255));
  3. CREATE TABLE t (a BIGINT, b VARCHAR(255), PRIMARY KEY(a, b) /*T![clustered_index] CLUSTERED */);
  4. CREATE TABLE t (a BIGINT, b VARCHAR(255), PRIMARY KEY(a, b) /*T![clustered_index] NONCLUSTERED */);

对于未显式指定该关键字的语句，默认行为受系统变量 @@global.tidb_enable_clustered_index 影响。该变量有三个取值：

  • OFF 表示所有主键默认使用非聚簇索引。
  • ON 表示所有主键默认使用聚簇索引。
  • INT_ONLY 此时的行为受配置项 alter-primary-key 控制。如果该配置项取值为 true，则所有主键默认使用非聚簇索引；如果该配置项取值为 false，则由单个整数类型的列构成的主键默认使用聚簇索引，其他类型的主键默认使用非聚簇索引。

系统变量 @@global.tidb_enable_clustered_index 本身的默认值为 INT_ONLY。

添加、删除聚簇索引

目前 TiDB 不支持在建表之后添加或删除聚簇索引，也不支持聚簇索引和非聚簇索引的互相转换。例如：

  1. ALTER TABLE t ADD PRIMARY KEY(b, a) CLUSTERED; -- 暂不支持
  2. ALTER TABLE t DROP PRIMARY KEY;     -- 如果主键为聚簇索引，则不支持
  3. ALTER TABLE t DROP INDEX `PRIMARY`; -- 如果主键为聚簇索引，则不支持

添加、删除非聚簇索引

TiDB 支持在建表之后添加或删除非聚簇索引。此时可以选择显式指定 NONCLUSTERED 关键字或省略关键字：

  1. ALTER TABLE t ADD PRIMARY KEY(b, a) NONCLUSTERED;
  2. ALTER TABLE t ADD PRIMARY KEY(b, a); -- 不指定关键字，则为非聚簇索引
  3. ALTER TABLE t DROP PRIMARY KEY;
  4. ALTER TABLE t DROP INDEX `PRIMARY`;

查询主键是否为聚簇索引

可通过以下方式来确定一张表的主键是否使用了聚簇索引：

  • 执行语句 SHOW CREATE TABLE。
  • 执行语句 SHOW INDEX FROM。
  • 查询系统表 information_schema.tables 中的 TIDB_PK_TYPE 列。

通过 SHOW CREATE TABLE 查看，PRIMARY KEY 的属性可能为 CLUSTERED 或 NONCLUSTERED：

  1. mysql> SHOW CREATE TABLE t;
  2. +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | Table | Create Table                                                                                                                                                                                      |
  4. +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | t     | CREATE TABLE `t` (
  6.   `a` bigint(20) NOT NULL,
  7.   `b` varchar(255) DEFAULT NULL,
  8.   PRIMARY KEY (`a`) /*T![clustered_index] CLUSTERED */
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
  10. +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  11. 1 row in set (0.01 sec)

通过 SHOW INDEX FROM 查看，Clustered 一列可能的结果为 Yes 或 No：

  1. mysql> SHOW INDEX FROM t;
  2. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+-----------+
  3. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | Clustered |
  4. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+-----------+
  5. | t     |          0 | PRIMARY  |            1 | a           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | YES     | NULL       | YES       |
  6. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+-----------+
  7. 1 row in set (0.01 sec)

查询 information_schema.tables 系统表中的 TIDB_PK_TYPE 列，可能的结果为 CLUSTERED 或 NONCLUSTERED：

  1. mysql> SELECT TIDB_PK_TYPE FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 't';
  2. +--------------+
  3. | TIDB_PK_TYPE |
  4. +--------------+
  5. | CLUSTERED    |
  6. +--------------+
  7. 1 row in set (0.03 sec)

限制

目前 TiDB 的聚簇索引具有以下几类限制：

  • 明确不支持且没有支持计划的使用限制：
    • 不支持与 SHARD_ROW_ID_BITS 一起使用；PRE_SPLIT_REGIONS 在聚簇索引表上不生效。
    • 不支持对聚簇索引表进行降级。如需降级，请使用逻辑备份工具迁移数据。
  • 尚未支持，但未来有计划支持的使用限制：
    • 尚未支持通过 ALTER TABLE 语句增加、删除、修改聚簇索引。
  • 特定版本的限制：
    • 在 v5.0 版本中，聚簇索引不支持与 TiDB Binlog 一起使用。开启 TiDB Binlog 后，TiDB 只允许创建单个整数列作为主键的聚簇索引；已创建的聚簇索引表的数据插入、删除和更新动作不会通过 TiDB Binlog 同步到下游。如需同步聚簇索引表，请升级至 v5.1 版本或使用 TiCDC。

开启 TiDB Binlog 之后，要创建的聚簇索引如果不是由单个整数列构成，会报以下错误：

  1. mysql> CREATE TABLE t (a VARCHAR(255) PRIMARY KEY CLUSTERED);
  2. ERROR 8200 (HY000): Cannot create clustered index table when the binlog is ON

与 SHARD_ROW_ID_BITS 一起使用时会报以下错误：

  1. mysql> CREATE TABLE t (a VARCHAR(255) PRIMARY KEY CLUSTERED) SHARD_ROW_ID_BITS = 3;
  2. ERROR 8200 (HY000): Unsupported shard_row_id_bits for table with primary key as row id

兼容性

升降级兼容性

TiDB 支持对聚簇索引表的升级兼容，但不支持降级兼容，即高版本 TiDB 聚簇索引表的数据在低版本 TiDB 上不可用。

聚簇索引在 TiDB v3.0 和 v4.0 中已完成部分支持，当表中存在单个整数列作为主键时默认启用，即：

  • 表设置了主键
  • 主键只有一列
  • 主键的数据类型为整数类型

TiDB v5.0 完成了所有类型主键的支持，但默认行为与 TiDB v3.0 和 v4.0 保持一致。要修改默认行为，请设置系统变量 @@tidb_enable_clustered_index 为 ON 或 OFF。

MySQL 兼容性

TiDB 支持使用可执行注释的语法来包裹 CLUSTERED 或 NONCLUSTERED 关键字，且 SHOW CREATE TABLE 的结果均包含 TiDB 特有的可执行注释，这些注释在 MySQL 或低版本的 TiDB 中会被忽略。

TiDB 生态工具兼容性

聚簇索引仅与 v5.0 及以后版本的以下生态工具兼容：

  • 备份与恢复工具 BR、Dumpling、TiDB Lightning。
  • 数据迁移和同步工具 DM、TiCDC。

v5.0 的 BR 不能通过备份恢复将非聚簇索引表转换成聚簇索引表，反之亦然。

与 TiDB 其他特性的兼容性

在非单整数列作为主键的表中，从非聚簇索引变为聚簇索引之后，在 v5.0 之前版本的 TiDB 能够执行的 SPLIT TABLE BY/BETWEEN 语句在 v5.0 及以后版本的 TiDB 上不再可用，原因是行数据键的构成发生了变化。在聚簇索引表上执行 SPLIT TABLE BY/BETWEEN 时需要依据主键列指定值，而不是指定一个整数值。例如：

  1. mysql> create table t (a int, b varchar(255), primary key(a, b) clustered);
  2. Query OK, 0 rows affected (0.01 sec)
  4. mysql> split table t between (0) and (1000000) regions 5;
  5. ERROR 1105 (HY000): Split table region lower value count should be 2
  7. mysql> split table t by (0), (50000), (100000);
  8. ERROR 1136 (21S01): Column count doesn't match value count at row 0
  10. mysql> split table t between (0, 'aaa') and (1000000, 'zzz') regions 5;
  11. +--------------------+----------------------+
  12. | TOTAL_SPLIT_REGION | SCATTER_FINISH_RATIO |
  13. +--------------------+----------------------+
  14. |                  4 |                    1 |
  15. +--------------------+----------------------+
  16. 1 row in set (0.00 sec)
  18. mysql> split table t by (0, ''), (50000, ''), (100000, '');
  19. +--------------------+----------------------+
  20. | TOTAL_SPLIT_REGION | SCATTER_FINISH_RATIO |
  21. +--------------------+----------------------+
  22. |                  3 |                    1 |
  23. +--------------------+----------------------+
  24. 1 row in set (0.01 sec)

AUTO_RANDOM 属性只能在聚簇索引表上使用。在非聚簇索引上使用 AUTO_RANDOM 会报以下错误：

  1. mysql> create table t (a bigint primary key nonclustered auto_random);
  2. ERROR 8216 (HY000): Invalid auto random: column a is not the integer primary key, or the primary key is nonclustered