转载自  InnoDB 的辅助索引叶子节点为什么不直接保存的记录地址而要存主键键值

RT，最近看书关于innodb的

InnoDB是索引组织表，所以完整的数据记录都存在聚集索引的叶子节点上

辅助索引的叶子节点保存的是对应的主键键值，而另外一种存储引擎myisam叶子节点保存的是记录的地址


我的问题是InnoDB 的辅助索引叶子节点为什么不直接保存的记录地址而要存主键键值？（导致在使用辅助索引的查询中要两次访问索引）


ps：想到的一个可能原因是 update 非索引列时，不需要更新辅助索引页，不知道还有没有其它原因？

（1）

本身innodb是以主键为主排序的表。。所以对于innodb来讲，其在磁盘的排序已经由主键的顺序定了，不能再按其它健排序。

其实辅助索引只要主键也有它的优点：一个磁盘块能存储更多的键值，使其在进行索引查找是，只需要读取相对少量的磁盘块就能扫描完全表，加之读到主键，再查询记录是比较快的。

根本原因是INNODB是主键的索引组织表，其物理存储已经由主键决定，不能再改变在磁盘中的存储顺序。

（2）

“一个磁盘块能存储更多的键值”

这个未必吧，还得取决于主键和地址的大小，地址一般由 文件号+块号+块内偏移组成，大概10个字节

不过也可以作为一个考虑因素，谢谢你的回复

（3）

个人也觉得是跟DML有关，当对一个innodb表进行大量DML后，B+树会分裂，同一行记录的页地址会发生变化。

由于辅助索引记录的是主键值，因此完全不用理会这个变化，如果辅助索引记录的是实际页地址，那DML一旦引起B+树分裂，还得与辅助索引进行同步。

总结：
1、mysql的innodb表，就是索引组织表，表中的所有数据行都放在索引上，这就约定了数据是严格按照顺序存放的，所以不管插入的先后顺序，它在那个物理上的那个位置与插入的先后顺序无关。
2、聚集索引，叶子节点存的是整行数据，直接通过这个聚集索引的键值找到某行
3、聚集索引，数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。
4、聚集索引，数据行和相邻的键值紧凑地存储在一起，因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚集索引。
5、非聚集索引，叶子节点存的是字段的值，通过这个非聚集索引的键值找到对应的聚集索引字段的值，再通过聚集索引键值找到表的某行，类似oracle通过键值找到rowid，再通过rowid找到行
6、mysql的innodb表，其聚集索引相当于整张表，而整张表也是聚集索引。默认通过主键聚集数据，如果没有定义主键，则选择第一个非空的唯一索引，如果没有非空唯一索引，则选择rowid来作为聚集索引
7、mysql的innodb表，因为整张表也是聚集索引，select出来的结果是顺序排序的，比如主键字段的数据插入顺序可以是5、3、4、2、1,查询时不带order by得出的结果也是按1、2、3、4、5排序
8、通俗理解
聚集索引：类似新华字典正文内容本身就是一种按照一定规则排列的目录
非聚集索引：这种目录纯粹是目录，正文纯粹是正文的排序方式
每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序 。
9、oracle一般使用堆表，mysql的innodb是索引组织表
9.1、堆表以一种显然随机的方式管理，数据插入时时存储位置是随机的，主要是数据库内部块的空闲情况决定，数据会放在最合适的地方，而不是以某种特定顺序来放置。
9.2、堆表的存储速度因为不用考虑排序, 所以存储速度会比较快. 但是要查找符合某个条件的记录, 就必须得读取全部的记录以便筛选。
9.3、堆表其索引中记录了记录所在位置的rowid，查找的时候先找索引，然后再根据索引rowid找到块中的行数据。
9.4、堆表的索引和表数据是分离的
9.5、索引组织表，其行数据以索引形式存放，因此找到索引，就等于找到了行数据。
9.6、索引组织表索引和数据是在一起的
基于主键索引和普通索引的查询有什么区别？
mysql> create table T( id int primary key,k int not null,name varchar(16),index (k))engine=InnoDB;
(ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)
主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚集索引(clustered index)。
非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引(secondary index)。
如果语句是 select * from T where ID=500，即 主键查询方式，则只需要搜索 ID 这棵 B+树 ；
如果语句是 select * from T where k=5，即 普通索引查询方式，则需要先搜索 k 索引树，得到 ID的值为 500，再到 ID 索引树搜索一次。这个过程称为回表
B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。以上面为例，如果插入新的行 ID 值为 700，则只只需要在 R5 的记录后面插入一个新记录。如果新插入的 ID值为 400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。
test1表 innodb引擎，索引和数据放在一个文件里面
-rw-r----- 1 mysql mysql  8678 Nov 20 14:05 test1.frm
-rw-r----- 1 mysql mysql 98304 Nov 20 16:51 test1.ibd
test2表 myisam引擎，索引和数据放在不同文件
-rw-r----- 1 mysql mysql  8558 Nov 22 10:22 test2.frm
-rw-r----- 1 mysql mysql     0 Nov 22 10:22 test2.MYD
-rw-r----- 1 mysql mysql  1024 Nov 22 10:22 test2.MYI
https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_table
table
The rows of an InnoDB table are organized into an index structure known as the clustered index , with entries sorted based on the primary key columns of the table. Data access is optimized for queries that filter and sort on the primary key columns, and each index contains a copy of the associated primary key columns for each entry. Modifying values for any of the primary key columns is an expensive operation. Thus an important aspect of InnoDB table design is choosing a primary key with columns that are used in the most important queries, and keeping the primary key short, with rarely changing values.
InnoDB表的行被组织成称为聚集索引的索引结构 ，条目根据表的主键列进行排序。 数据访问针对对主键列进行筛选和排序的查询进行了优化，每个索引都包含每个条目的关联主键列的副本。 修改任何主键列的值是一项昂贵的操作。 因此，InnoDB表设计的一个重要方面是选择一个主键，该主键具有在最重要的查询中使用的列，并保持主键很短，很少更改值。
https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_clustered_index
clustered index
The InnoDB term for a primary key index. InnoDB table storage is organized based on the values of the primary key columns, to speed up queries and sorts involving the primary key columns. For best performance, choose the primary key columns carefully based on the most performance-critical queries. Because modifying the columns of the clustered index is an expensive operation, choose primary columns that are rarely or never updated.In the Oracle Database product, this type of table is known as an index-organized table
InnoDB术语表示主键索引。 InnoDB表存储基于主键列的值进行组织，以加速涉及主键列的查询和排序。 为获得最佳性能，请根据性能最关键的查询仔细选择主键列。 因为修改聚集索引的列是一项昂贵的操作，所以选择很少或从不更新的主列。在Oracle数据库产品中，此类表称为索引组织表。
https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html
Every InnoDB table has a special index called the clustered index where the data for the rows is stored. Typically, the clustered index is synonymous with the primary key.
When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. Define a primary key for each table that you create. If there is no logical unique and non-null column or set of columns, add a new auto-increment column, whose values are filled in automatically.
If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index .
If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index named GEN_CLUST_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.
Accessing a row through the clustered index is fast because the index search leads directly to the page with all the row data. 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.
All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.
每个InnoDB表都有一个称为聚集索引的特殊索引，其中存储了行的数据。通常，聚集索引与主键同义。
在表上定义PRIMARY KEY时，InnoDB将其用作聚集索引 。为您创建的每个表定义主键。如果没有逻辑唯一且非空列或一组列，请添加一个新的自动增量列，其值将自动填充。
如果没有为表定义PRIMARY KEY，MySQL将找到第一个UNIQUE索引，其中所有键列都是NOT NULL，而InnoDB将它用作聚集索引。
如果表没有PRIMARY KEY或合适的UNIQUE索引，InnoDB会在包含行ID值的合成列内部生成名为GEN_CLUST_INDEX的隐藏聚集索引 。这些行按InnoDB分配给此类表中的行的ID排序。行ID是一个6字节的字段，在插入新行时会单调增加。因此，由行ID排序的行在物理上处于插入顺序。
通过聚集索引访问行很快，因为索引搜索直接指向包含所有行数据的页面。 如果表很大，则与使用与索引记录不同的页面存储行数据的存储组织相比，聚集索引体系结构通常会保存磁盘I / O操作。
除聚集索引之外的所有索引都称为辅助索引。 在InnoDB中，辅助索引中的每个记录都包含该行的主键列以及为辅助索引指定的列。 InnoDB使用此主键值来搜索聚集索引中的行。

聚集索引是索引叶子节点上存的是数据，非聚集索引的叶子节点存的是数据的指针。

聚集索引是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储不连续。

聚集索引每张表只能有一个，非聚集索引可以有多个。

mysql的innodb引擎必须要有主键，因为数据存放在聚集索引上，即使不设置主键，mysql也会设置一个默认主键，需要去存放数据。其他索引也就是非聚集索引或者叫二级索引（辅助索引）存放的是主键的数据，从而根据主键的查找到数据。

myisam引擎可以不需要主键，因为引擎会单独存储数据，索引上存放的是指向数据的指针。

 

总结：
1、mysql的innodb表，就是索引组织表，表中的所有数据行都放在索引上，这就约定了数据是严格按照顺序存放的，所以不管插入的先后顺序，它在那个物理上的那个位置与插入的先后顺序无关。
2、聚集索引，叶子节点存的是整行数据，直接通过这个聚集索引的键值找到某行
3、聚集索引，数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。
4、聚集索引，数据行和相邻的键值紧凑地存储在一起，因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚集索引。
5、非聚集索引，叶子节点存的是字段的值，通过这个非聚集索引的键值找到对应的聚集索引字段的值，再通过聚集索引键值找到表的某行，类似oracle通过键值找到rowid，再通过rowid找到行
6、mysql的innodb表，其聚集索引相当于整张表，而整张表也是聚集索引。默认通过主键聚集数据，如果没有定义主键，则选择第一个非空的唯一索引，如果没有非空唯一索引，则选择rowid来作为聚集索引
7、mysql的innodb表，因为整张表也是聚集索引，select出来的结果是顺序排序的，比如主键字段的数据插入顺序可以是5、3、4、2、1,查询时不带order by得出的结果也是按1、2、3、4、5排序
8、通俗理解
聚集索引：类似新华字典正文内容本身就是一种按照一定规则排列的目录
非聚集索引：这种目录纯粹是目录，正文纯粹是正文的排序方式
每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序
。
9、oracle一般使用堆表，mysql的innodb是索引组织表
9.1、堆表以一种显然随机的方式管理，数据插入时时存储位置是随机的，主要是数据库内部块的空闲情况决定，数据会放在最合适的地方，而不是以某种特定顺序来放置。
9.2、堆表的存储速度因为不用考虑排序, 所以存储速度会比较快. 但是要查找符合某个条件的记录, 就必须得读取全部的记录以便筛选。
9.3、堆表其索引中记录了记录所在位置的rowid，查找的时候先找索引，然后再根据索引rowid找到块中的行数据。
9.4、堆表的索引和表数据是分离的
9.5、索引组织表，其行数据以索引形式存放，因此找到索引，就等于找到了行数据。
9.6、索引组织表索引和数据是在一起的
基于主键索引和普通索引的查询有什么区别？
mysql> create table T( id int primary key,k int not null,name varchar(16),index (k))engine=InnoDB;
(ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)
主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚集索引(clustered index)。
非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引(secondary index)。
如果语句是 select * from T where ID=500，即
主键查询方式，则只需要搜索 ID 这棵 B+树
；
如果语句是 select * from T where k=5，即
普通索引查询方式，则需要先搜索 k 索引树，得到 ID的值为 500，再到 ID 索引树搜索一次。这个过程称为回表
B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。以上面为例，如果插入新的行 ID 值为 700，则只只需要在 R5 的记录后面插入一个新记录。如果新插入的 ID值为 400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。
test1表
innodb引擎，索引和数据放在一个文件里面
-rw-r----- 1 mysql mysql  8678 Nov 20 14:05 test1.frm
-rw-r----- 1 mysql mysql 98304 Nov 20 16:51 test1.ibd
test2表
myisam引擎，索引和数据放在不同文件
-rw-r----- 1 mysql mysql  8558 Nov 22 10:22 test2.frm
-rw-r----- 1 mysql mysql     0 Nov 22 10:22 test2.MYD
-rw-r----- 1 mysql mysql  1024 Nov 22 10:22 test2.MYI
https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_table
table
The rows of an InnoDB table are organized into an index structure known as the clustered index
, with entries sorted based on the primary key columns of the table. Data access is optimized for queries that filter and sort on the primary key columns, and each index contains a copy of the associated primary key columns for each entry. Modifying values for any of the primary key columns is an expensive operation. Thus an important aspect of InnoDB table design is choosing a primary key with columns that are used in the most important queries, and keeping the primary key short, with rarely changing values.
InnoDB表的行被组织成称为聚集索引的索引结构
，条目根据表的主键列进行排序。 数据访问针对对主键列进行筛选和排序的查询进行了优化，每个索引都包含每个条目的关联主键列的副本。 修改任何主键列的值是一项昂贵的操作。 因此，InnoDB表设计的一个重要方面是选择一个主键，该主键具有在最重要的查询中使用的列，并保持主键很短，很少更改值。
https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_clustered_index
clustered index
The InnoDB term for a primary key index. InnoDB table storage is organized based on the values of the primary key columns, to speed up queries and sorts involving the primary key columns. For best performance, choose the primary key columns carefully based on the most performance-critical queries. Because modifying the columns of the clustered index is an expensive operation, choose primary columns that are rarely or never updated.In the Oracle Database product, this type of table is known as an index-organized table
InnoDB术语表示主键索引。 InnoDB表存储基于主键列的值进行组织，以加速涉及主键列的查询和排序。 为获得最佳性能，请根据性能最关键的查询仔细选择主键列。 因为修改聚集索引的列是一项昂贵的操作，所以选择很少或从不更新的主列。在Oracle数据库产品中，此类表称为索引组织表。
https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html
Every InnoDB table has a special index called the clustered index where the data for the rows is stored. Typically, the clustered index is synonymous with the primary key.
When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index.
Define a primary key for each table that you create. If there is no logical unique and non-null column or set of columns, add a new auto-increment column, whose values are filled in automatically.
If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index
.
If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index named GEN_CLUST_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.
Accessing a row through the clustered index is fast because the index search leads directly to the page with all the row data. 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.
All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.
每个InnoDB表都有一个称为聚集索引的特殊索引，其中存储了行的数据。通常，聚集索引与主键同义。
在表上定义PRIMARY KEY时，InnoDB将其用作聚集索引
。为您创建的每个表定义主键。如果没有逻辑唯一且非空列或一组列，请添加一个新的自动增量列，其值将自动填充。
如果没有为表定义PRIMARY KEY，MySQL将找到第一个UNIQUE索引，其中所有键列都是NOT NULL，而InnoDB将它用作聚集索引。
如果表没有PRIMARY KEY或合适的UNIQUE索引，InnoDB会在包含行ID值的合成列内部生成名为GEN_CLUST_INDEX的隐藏聚集索引
。这些行按InnoDB分配给此类表中的行的ID排序。行ID是一个6字节的字段，在插入新行时会单调增加。因此，由行ID排序的行在物理上处于插入顺序。
通过聚集索引访问行很快，因为索引搜索直接指向包含所有行数据的页面。 如果表很大，则与使用与索引记录不同的页面存储行数据的存储组织相比，聚集索引体系结构通常会保存磁盘I / O操作。
除聚集索引之外的所有索引都称为辅助索引。 在InnoDB中，辅助索引中的每个记录都包含该行的主键列以及为辅助索引指定的列。 InnoDB使用此主键值来搜索聚集索引中的行。