If a table has a clustered index and a secondary index, the leaf nodes in secondary index contain the attribute value of the clustered index. But what if a table only has non-clustered index? How can a non-clustered index retrieve data without a clustered index?
create table table_without_primary_key(
name varchar(30) not null ,
date datetime not null
);
insert into table_without_primary_key
values ('jack',now());
insert into table_without_primary_key
values ('alice',now());
insert into table_without_primary_key
values ('ribbon',now());
create index time_index
on table_without_primary_key (date);
show index from table_without_primary_key;
Result:
+---------------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| table_without_primary_key | 1 | time_index | 1 | date | A | 2 | NULL | NULL | | BTREE | | | YES | NULL |
+---------------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.02 sec)
Besides, I also heard from somewhere that if a table does not have primary key or unique index, it will use row id to create a clustered index.
But I doubt if it is true, since I found no automatic clustered index on row id from the table I described above.
2
Answers
All InnoDB tables are stored as a clustered index.
https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html says:
In addition to what Bill says, and tested on 8.0.31-0ubuntu0.22.04.1:
Note that it does have a
PRIMARY KEY
calledGEN_CLUST_INDEX
.A ‘row’ in a secondary index will have
PRIMARY KEY
. As already noted, there will always be a PK, but you might see it as aUNIQUE
or the hidden 6-byte thingie.There will also be B+Tree overhead stuff, such as links to other nodes, both at the same and other levels.