Remove the constraint that auto column must be defined as a key

[benmaoer]

Feature Request

Is your feature request related to a problem? Please describe:

Before TiDB v6.5.0, when you create a table with an AUTO_INCREMENT, in order to be compatible with MySQL, an AUTO_INCREMENT column must be defined as the first or only column of some index. Otherwise, you may get an error:

mysql> create table test (
    `id` int(11) NOT NULL AUTO_INCREMENT,  
    `k` int(11) NOT NULL DEFAULT '0', 
    `c` char(120) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',  
     PRIMARY KEY(`k`, `id`) 
);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

You could add an index for AUTO_INCREMENT column to avoid it:

mysql> create table test2 (
    `id` int(11) NOT NULL AUTO_INCREMENT,  
    `k` int(11) NOT NULL DEFAULT '0', 
    `c` char(120) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',  
    PRIMARY KEY(`k`, `id`), 
    key idx_1 (`id)`
);
Query OK, 0 rows affected (0.24 sec)

TIDB designed this mechanism only for compatibility with InnoDB. While in MySQL 5.7 and earlier, InnoDB uses the in-memory auto-increment counter as long as the server runs. When the server is stopped and restarted, InnoDB re initializes the counter for each table by MAX( id ) + 1 based on the data contained for the first INSERT to the table, as described earlier. The indexes of the AUTO_INCREMENT column which is not required to be a PRIMARY KEY or UNIQUE, make it faster. That's an old problem: https://bugs.mysql.com/bug.php?id=199, and at last it's fixed at MySQL 8.0. The AUTO_INCREMENT is persistent now. Refer to MySQL 8.0 doc: https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html

InnoDB provides a configurable locking mechanism that can significantly improve scalability and performance of SQL statements that add rows to tables with AUTO_INCREMENT columns. To use the AUTO_INCREMENT mechanism with an InnoDB table, an AUTO_INCREMENT column must be defined as the first or only column of some index such that it is possible to perform the equivalent of an indexed SELECT MAX(ai_col) lookup on the table to obtain the maximum column value. The index is not required to be a PRIMARY KEY or UNIQUE, but to avoid duplicate values in the AUTO_INCREMENT column, those index types are recommended.

Describe the feature you'd like:

Remove the constraint that auto column must be defined as a key. Then users could create table like:

mysql> create table test (
    `id` int(11) NOT NULL AUTO_INCREMENT,  
    `k` int(11) NOT NULL DEFAULT '0', 
    `c` char(120) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',  
     PRIMARY KEY(`k`, `id`) 
);
Query OK, 0 rows affected (0.24 sec)

Describe alternatives you've considered:

Auto-increment counter of TiDB is persistent from birth. Furthermore, by removing the constraint that auto column must be defined as a key, we have a chance to solve 3 problems at one time:

• hot written spot: the composite primary key of business fields and auto_increment could avoid most of the hot spots
• an auto_increment field could be used for order by clause
• a clustered-index table could improve the performance of certain queries.

Teachability, Documentation, Adoption, Migration Strategy:

No migration