聚集索引 vs. 非聚集索引 Clustered vs. Non clustered Index

• Illustration

An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in B+ tree that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.

聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。「聚簇」表示数据行和相邻键值紧凑地存储在一起。

对于 InnoDB 存储引擎来说，表采用的存储方式称为索引组织表 index organizedtable 即表都是根据主键的顺序来进行组织存放的（逻辑顺序

聚集索引就是按照每张表的主键构造一棵 B+ 树，同时叶子节点中存放的即为表中一行一行的数据。聚集索引能够在 B+ 树索引的叶子节点上直接找到数据，在聚集索引中，索引即数据，数据即索引

Cluster index is a type of index which sorts the data rows in the table on their key values. With a clustered index the rows are stored physically on the disk in the same order as the index. Therefore, there can be only one clustered index.

A Non-clustered index stores the data at one location and indices at another location. The index contains pointers to the location of that data. A single table can have many non-clustered indexes as an index in the non-clustered index is stored in different places. For example, a book can have more than one index, one at the beginning which displays the contents of a book unit wise while the second index shows the index of terms in alphabetical order.

B+ Tree

主键目录

MySQL 在存储数据的时候是以数据页为最小单位，数据页中的数据是按照主键排序的（未指定主键则由 MySQL自己维护的 ROW_ID）每个数据页中会有一个最小主键，数据页页号和最小主键组成主键目录。假设现在要查找主键为 2 的数据，通过二分查找法最后确定下主键为 2 的记录在数据页 1 中，此时就会定位到数据页 1 接着再去定位主键为 2 的记录

索引页

如果主键目录的记录非常多，MySQL 会拆分到不同的索引页中

二级（辅助）索引

Compared to MyISAM, the secondary index is very different from the clustered index. The leaf of the secondary index of InnoDB contains the value of the primary key instead of the row pointers, which reduces the overhead of maintaining the secondary index when moving data or data pages are split, because InnoDB does not need to update the row pointer of the index.

聚集索引只能有一个，所有同一个表的其他字段只能是非主键索引，即非聚集索引

CREATE TABLE `book` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`name` varchar(255) DEFAULT NULL,
	`author` varchar(20) DEFAULT NULL,
	`published` date DEFAULT NULL,
	PRIMARYKEY (`id`),
	KEY`idx_name` (`name`),
	KEY`idx_author_name` (`author`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

INSERT bookVALUES (1,'book1','author1','2018-01-01'),(2,'book2','author1','2018-01-01'),(3,'book3','author2','2018-01-01'),(4,'book4','author2','2019-01-01');