201.3 Clustered Index vs. Non-Clustered Index: A Minor Issue

This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 201.3 聚簇索引与非聚簇有一点小问题

| username: Timothy

Mr. Bao’s examples are comprehensive, but throughout the course, not all SQL statements were fully executed. So, I executed them myself and found some minor issues.

Mr. Bao mentioned that only primary keys of type int are automatically considered clustered indexes. However, when I executed the third SQL statement, which is as follows:

DROP TABLE IF EXISTS test.t3_nonclustered;
CREATE TABLE test.t3_nonclustered (
    id2 BIGINT,
    name CHAR(255),
    varname CHAR(200));

In fact, when I verified it using the following statement:

show index from test.t3_nonclustered;

The result was as follows:

At the same time, I checked for _tidb_rowid and it did not exist. I wondered if it was an issue with my experimental version. The current TiDB experimental version is 6.6.0. I checked the official documentation and found the following attribute, which might explain the issue:

It roughly states that the current default value of this attribute is ON. Therefore, if neither clustered nor non-clustered is specified, the default behavior is that all primary keys use clustered indexes. What Mr. Bao intended to convey is more similar to the situation when this attribute is set to INT_ONLY. I wonder if the default value was INT_ONLY in older versions.

On the clustered index page, it mentions version 5.0 and compatibility with older versions. This line might be an official bug, as the descriptions on both sides are inconsistent at least for now.


Upgrade and Downgrade Compatibility

TiDB supports upgrade compatibility for clustered index tables but does not support downgrade compatibility. That is, data from higher version TiDB clustered index tables is not usable on lower version TiDB.

Clustered indexes were partially supported in TiDB v3.0 and v4.0, enabled by default when a table has a single integer column as the primary key, i.e.:

  • The table has a primary key
  • The primary key has only one column
  • The primary key’s data type is an integer type

TiDB v5.0 completed support for all types of primary keys, but the default behavior remains consistent with TiDB v3.0 and v4.0. To change the default behavior, set the system variable @@tidb_enable_clustered_index to ON or OFF.

Marking this here, more experiments are needed.

| username: redgame | Original post link

Mark it.

| username: Defined2014 | Original post link

In the old version, the default value was int_only.