clustered index和secondary indexes

Every InnoDB table has a special index called the clustered index where the data for the rows is stored.

If you define a PRIMARY KEY on your table, the index of the primary key is the clustered index.

每个InnoDB 表有一个特定的索引叫做clustered index,存储行的数据。

If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index. If there is no

such index in the table, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such

a table.

The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

如果你的表没有定义主键,MySQL 选择第一个UNIQUE index 有NOT NULL 列作为primary key ，InnoDB 使用它作为 clustered index.

如果表中没有这样的索引,InnoDB 内部生成一个隐藏的 clustered index 在虚拟的列包含ROW ID值。

记录按ID 排序,InnoDB 指定这行的行在一个表里。

row ID 是一个6个字节的列 当行记录插入后自动增加

Accessing a row through the clustered index is fast because the row data is on the same page where the index search leads. If a table is large, the clustered index architecture often saves a disk

I/O operation when compared to storage organizations that store row data using a different page from the index record. (For example, MyISAM uses one file for data rows and another for index

records.)

通过 clustered index 访问记录是快速的,因为记录数据在相同的页,当索引搜索时。如果表很大, clustered index结构经常节约大量的磁盘I/O操作 相比存储引擎组织存储的记录

使用不同的页。

比如,MyISAM 使用一个文件存储数据另一个文件存储索引记录

从物理意义上来讲，InnoDB表由共享表空间、日志文件组（redo文件组）、表结构定义文件组成。若将innodb_file_per_table设置为on，则系统将为每一个表单独的生成一个table_name.ibd的文件，在此文件中，存储与该表

相关的数据、索引、表的内部数据字典信息。表结构文件则以.frm结尾，这与存储引擎无关。

In InnoDB, the records in non-clustered indexes (also called secondary indexes) contain the primary key value for the row.

InnoDB uses this primary key value to search for the row in the clustered index. If the primary key is long, the secondary indexes use more space,

so it is advantageous to have a short primary key.

在InnoDB, 记录在 non-clustered indexes (也被称为 secondary indexes) 包含主键值的记录,

InnoDB 使用primary key value 来搜索在clustered index中的记录, 如果 primary key太长,

secondary indexes 使用更多的空间,
这里的 secondary index

第二索引就是非主键索引