Clustered Indexes

TiDB supports the clustered index feature since v5.0. This feature controls how data is stored in tables containing primary keys. It provides TiDB the ability to organize tables in a way that can improve the performance of certain queries.

The term clustered in this context refers to the organization of how data is stored and not a group of database servers working together. Some database management systems refer to clustered indexes as index-organized tables (IOT).

Currently, tables containing primary keys in TiDB are divided into the following two categories:

• NONCLUSTERED: The primary key of the table is non-clustered index. In tables with non-clustered indexes, the keys for row data consist of internal _tidb_rowid implicitly assigned by TiDB. Because primary keys are essentially unique indexes, tables with non-clustered indexes need at least two key-value pairs to store a row, which are:
  • _tidb_rowid (key) - row data (value)
  • Primary key data (key) - _tidb_rowid (value)
• CLUSTERED: The primary key of the table is clustered index. In tables with clustered indexes, the keys for row data consist of primary key data given by the user. Therefore, tables with clustered indexes need only one key-value pair to store a row, which is:
  • Primary key data (key) - row data (value)

User scenarios

Compared to tables with non-clustered indexes, tables with clustered indexes offer greater performance and throughput advantages in the following scenarios:

• When data is inserted, the clustered index reduces one write of the index data from the network.
• When a query with an equivalent condition only involves the primary key, the clustered index reduces one read of index data from the network.
• When a query with a range condition only involves the primary key, the clustered index reduces multiple reads of index data from the network.
• When a query with an equivalent or range condition only involves the primary key prefix, the clustered index reduces multiple reads of index data from the network.

On the other hand, tables with clustered indexes have certain disadvantages. See the following:

• There might be write hotspot issues when inserting a large number of primary keys with close values.
• The table data takes up more storage space if the data type of the primary key is larger than 64 bits, especially when there are multiple secondary indexes.

Usages

Create a table with clustered indexes

Since TiDB v5.0, you can add non-reserved keywords CLUSTERED or NONCLUSTERED after PRIMARY KEY in a CREATE TABLE statement to specify whether the table's primary key is a clustered index. For example:

CREATE TABLE t (a BIGINT PRIMARY KEY CLUSTERED, b VARCHAR(255));
CREATE TABLE t (a BIGINT PRIMARY KEY NONCLUSTERED, b VARCHAR(255));
CREATE TABLE t (a BIGINT KEY CLUSTERED, b VARCHAR(255));
CREATE TABLE t (a BIGINT KEY NONCLUSTERED, b VARCHAR(255));
CREATE TABLE t (a BIGINT, b VARCHAR(255), PRIMARY KEY(a, b) CLUSTERED);
CREATE TABLE t (a BIGINT, b VARCHAR(255), PRIMARY KEY(a, b) NONCLUSTERED);

Note that keywords KEY and PRIMARY KEY have the same meaning in the column definition.

You can also use the comment syntax in TiDB to specify the type of the primary key. For example:

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

For statements that do not explicitly specify the keyword CLUSTERED/NONCLUSTERED, the default behavior is controlled by the system variable @@global.tidb_enable_clustered_index. Supported values for this variable are as follows:

• OFF indicates that primary keys are created as non-clustered indexes by default.
• ON indicates that primary keys are created as clustered indexes by default.
• INT_ONLY indicates that the behavior is controlled by the configuration item alter-primary-key. If alter-primary-key is set to true, primary keys are created as non-clustered indexes by default. If it is set to false, only the primary keys which consist of an integer column are created as clustered indexes.

The default value of @@global.tidb_enable_clustered_index is INT_ONLY.

Add or drop clustered indexes

TiDB does not support adding or dropping clustered indexes after tables are created. Nor does it support the mutual conversion between clustered indexes and non-clustered indexes. For example:

ALTER TABLE t ADD PRIMARY KEY(b, a) CLUSTERED; -- Currently not supported.
ALTER TABLE t DROP PRIMARY KEY;     -- If the primary key is a clustered index, then not supported.
ALTER TABLE t DROP INDEX `PRIMARY`; -- If the primary key is a clustered index, then not supported.

Add or drop non-clustered indexes

TiDB supports adding or dropping non-clustered indexes after tables are created. You can explicitly specify the keyword NONCLUSTERED or omit it. For example:

ALTER TABLE t ADD PRIMARY KEY(b, a) NONCLUSTERED;
ALTER TABLE t ADD PRIMARY KEY(b, a); -- If you omit the keyword, the primary key is a non-clustered index by default.
ALTER TABLE t DROP PRIMARY KEY;
ALTER TABLE t DROP INDEX `PRIMARY`;

Check whether the primary key is a clustered index

You can check whether the primary key of a table is a clustered index using one of the following methods:

• Execute the command SHOW CREATE TABLE.
• Execute the command SHOW INDEX FROM.
• Query the TIDB_PK_TYPE column in the system table information_schema.tables.

By running the command SHOW CREATE TABLE, you can see whether the attribute of PRIMARY KEY is CLUSTERED or NONCLUSTERED. For example:

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

By running the command SHOW INDEX FROM, you can check whether the result in the column Clustered shows YES or NO. For example:

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

You can also query the column TIDB_PK_TYPE in the system table information_schema.tables to see whether the result is CLUSTERED or NONCLUSTERED. For example:

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

Limitations

Currently, there are several different types of limitations for the clustered index feature. See the following:

• Situations that are not supported and not in the support plan:
  • Using clustered indexes together with the attribute SHARD_ROW_ID_BITS is not supported. Also, the attribute PRE_SPLIT_REGIONS does not take effect on tables with clustered indexes.
  • Downgrading tables with clustered indexes is not supported. If you need to downgrade such tables, use logical backup tools to migrate data instead.
• Situations that are not supported yet but in the support plan:
  • Adding, dropping, and altering clustered indexes using ALTER TABLE statements are not supported.
• Limitations for specific versions:
  • In v5.0, using the clustered index feature together with TiDB Binlog is not supported. After TiDB Binlog is enabled, TiDB only allows creating a single integer column as the clustered index of a primary key. TiDB Binlog does not replicate data changes (such as insertion, deletion, and update) on existing tables with clustered indexes to the downstream. If you need to replicate tables with clustered indexes to the downstream, upgrade your cluster to v5.1 or use TiCDC for replication instead.

After TiDB Binlog is enabled, if the clustered index you create is not a single integer primary key, TiDB returns the following error:

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

If you use clustered indexes together with the attribute SHARD_ROW_ID_BITS, TiDB reports the following error:

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

Compatibility

Compatibility with earlier and later TiDB versions

TiDB supports upgrading tables with clustered indexes but not downgrading such tables, which means that data in tables with clustered indexes on a later TiDB version is not available on an earlier one.

The clustered index feature is partially supported in TiDB v3.0 and v4.0. It is enabled by default when the following requirements are fully met:

• The table contains a PRIMARY KEY.
• The PRIMARY KEY consists of only one column.
• The PRIMARY KEY is an INTEGER.

Since TiDB v5.0, the clustered index feature is fully supported for all types of primary keys, but the default behavior is consistent with TiDB v3.0 and v4.0. To change the default behavior, you can configure the system variable @@tidb_enable_clustered_index to ON or OFF. For more details, see Create a table with clustered indexes.

Compatibility with MySQL

TiDB specific comment syntax supports wrapping the keywords CLUSTERED and NONCLUSTERED in a comment. The result of SHOW CREATE TABLE also contains TiDB specific SQL comments. MySQL databases and TiDB databases of an earlier version will ignore these comments.

Compatibility with TiDB migration tools

The clustered index feature is only compatible with the following migration tools in v5.0 and later versions:

• Backup and restore tools: BR, Dumpling, and TiDB Lightning.
• Data migration and replication tools: DM and TiCDC.

However, you cannot convert a table with non-clustered indexes to a table with clustered indexes by backing up and restoring the table using the v5.0 BR tool, and vice versa.

Compatibility with other TiDB features

For a table with a combined primary key or a single non-integer primary key, if you change the primary key from a non-clustered index to a clustered index, the keys of its row data change as well. Therefore, SPLIT TABLE BY/BETWEEN statements that are executable in TiDB versions earlier than v5.0 are no longer workable in v5.0 and later versions of TiDB. If you want to split a table with clustered indexes using SPLIT TABLE BY/BETWEEN, you need to provide the value of the primary key column, instead of specifying an integer value. See the following example:

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

The attribute AUTO_RANDOM can only be used on clustered indexes. Otherwise, TiDB returns the following error:

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