From Getting Started with Indexes: Primary Key it says:

In InnoDB tables, all indexes contain the primary key as a suffix. Thus, when using this storage engine, keeping the primary key as small as possible is particularly important. If a primary key does not exist and there are no UNIQUE indexes, InnoDB creates a 6-bytes clustered index which is invisible to the user.

So without a primary key, we'll create a table with a hidden clustered index. Then later on the same section a few paragraphs later says:

You cannot create a primary key with the CREATE INDEX command. If you do want to add one after the table has already been created, use ALTER TABLE, for example:

but doesn't say if the new primary key will become a clustered index.

Also ALTER TABLE: ADD PRIMARY KEY only says:

For PRIMARY KEY indexes, you can specify a name for the index, but it is silently ignored, and the name of the index is always PRIMARY.

but does not comment on the clustered index.

My confusion results in these questions:

1. If I add a primary key later on, will the primary key be clustered?
2. If so, what happens to the storage layout? Does it have to copy all the data to the new clustered index and then delete the previous hidden clustered index?
3. Is the primary key always clustered?

Minimum reproducing example:

create database test
use test
create table test_table ( id bigint(20) NULL );

describe test_table;
 ------- ------------ ------ ----- --------- ------- 
| Field | Type       | Null | Key | Default | Extra |
 ------- ------------ ------ ----- --------- ------- 
| id    | bigint(20) | YES  |     | NULL    |       |
 ------- ------------ ------ ----- --------- ------- 
1 row in set (0.003 sec)

show index from test_table;
Empty set (0.000 sec)


alter table test_table add primary key(id);
Query OK, 0 rows affected (0.067 sec)
Records: 0  Duplicates: 0  Warnings: 0

describe test_table;
 ------- ------------ ------ ----- --------- ------- 
| Field | Type       | Null | Key | Default | Extra |
 ------- ------------ ------ ----- --------- ------- 
| id    | bigint(20) | NO   | PRI | NULL    |       |
 ------- ------------ ------ ----- --------- ------- 
1 row in set (0.007 sec)

show index from test_table;
 ------------ ------------ ---------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 ------------ ------------ ---------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 
| test_table |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
 ------------ ------------ ---------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 
1 row in set (0.001 sec)

CodePudding user response：

If the table has a primary key, then that is always the clustered index (assuming it's in the InnoDB engine).

If you alter a table to add a primary key, then that will become the clustered index.

Adding (or changing) the primary key does need to restructure the table, because the layout will be completely changed.