在MySQL中使用自动递增PK的主要原因之一是，它保证了所有插入到聚集PK索引中的操作都是有序的，因此速度很快。我明白但是二级索引呢？假设我的表有一个二级索引。插入相对于PK聚集索引是有序的，但相对于辅助索引B+ Tree是无序的。那么，插入不会仍然很慢，因为MySQL需要不断地重新排列二级索引B+ Tree，因为插入进来了？我只是想知道在这里使用自动增量是否真的能在插入性能方面为我带来任何好处。将非常感谢一些澄清在这里。
luaexgnf1#
主键将被聚类，这意味着它直接指向磁盘上的数据。重新排列数据意味着必须移动完整的记录。对于二级索引，它实际上只是一堆指向磁盘上位置的指针。二级索引与记录的顺序无关，因此必须在二级索引中移动指针只是移动指针。这是一个比必须移动整个记录快得多的操作。
z4iuyo4d2#
您的基本假设只有在您有一个只写（或至少是只更新）表时才是正确的。如果要删除记录，则新记录的PK将以非顺序（物理）方式插入。索引插入的效率几乎总是次要的考虑因素，打乱它是过早的优化反模式。您是否考虑过基数、键字段长度、缓存大小等通常更重要的问题？使用自动增量代理PK通常是次优的-通常有一个更有用的唯一键，其真实的值以更有意义的方式聚集。（而且你只能用innodb表进行集群--你意识到了这一点，对吧？）“聚类”意味着索引本质上就是表。因此，在插入代理键时，它有一个好处，因为所有内容都被添加到表的末尾，因为下一个索引值总是高于任何前一个索引值（正如您已经知道的那样）。除非你是在填补被删除的记录留下的漏洞。这可能间接发生，但可能是一个开销问题，因为整个记录必须重新定位，这显然比移动索引键值和指针要多。对于单个记录的查询，集群记录并不像对于记录范围（例如，订单的项目、客户、用户。例如，如果您可以为同一个用户提取几条（或几百条）记录，那么就值得进行集群。对于单个用户连续插入记录的可能性要小得多（在大多数情况下），因此按时间顺序进行聚类没有多大帮助。但您的要求可能会有所不同。您没有指定innodb，所以我主要回答myisam（默认值），其中只有自动增量或按时间顺序索引才能模拟集群-没有显式选项。
46qrfjad3#
根据我的发现：数据库表中的行按群集索引排序。一个表上只有一个聚集索引，因为您只能以一种方式对行进行排序。定义主键时，将自动在此键上创建聚集索引。因此，表是按主键排序的。假设索引使用B+树实现，则集群索引的叶节点实际上是包含数据库表的实际行的页（或指向包含磁盘上数据的实际页的指针）。因此，当您向下遍历该索引时，您将到达包含要搜索的记录的页面。注意：您必须通过主键进行搜索，因为B+树中的中间节点包含主键中的值。当您创建辅助索引（非聚集索引）时，数据库将创建一个B+树，其中中间B+树节点中的值实际上是已创建辅助索引的列中的值。当您向下遍历二级索引时，您会到达叶节点（这是一个页面），其中包含与您正在搜索的值相对应的主键值。注意：如果二级索引列包含重复的值，那么搜索算法是这样的，你得到那些包含一组主键对应于你要搜索的值的页面。在获得与正在搜索的值对应的主键值之后，使用主索引（聚集索引）访问包含这些行的实际页。注意：上面部分中的昂贵操作是IO，其中您从磁盘获取页面并将其插入内存（确切地说是buffered_pool）。图形表示-https://stackoverflow.com/a/67958216/7547722更多-https://dba.stackexchange.com/a/260337/198502
buffered_pool
3条答案
按热度按时间luaexgnf1#
主键将被聚类，这意味着它直接指向磁盘上的数据。重新排列数据意味着必须移动完整的记录。对于二级索引，它实际上只是一堆指向磁盘上位置的指针。二级索引与记录的顺序无关，因此必须在二级索引中移动指针只是移动指针。这是一个比必须移动整个记录快得多的操作。
z4iuyo4d2#
您的基本假设只有在您有一个只写（或至少是只更新）表时才是正确的。如果要删除记录，则新记录的PK将以非顺序（物理）方式插入。
索引插入的效率几乎总是次要的考虑因素，打乱它是过早的优化反模式。您是否考虑过基数、键字段长度、缓存大小等通常更重要的问题？
使用自动增量代理PK通常是次优的-通常有一个更有用的唯一键，其真实的值以更有意义的方式聚集。（而且你只能用innodb表进行集群--你意识到了这一点，对吧？）
“聚类”意味着索引本质上就是表。因此，在插入代理键时，它有一个好处，因为所有内容都被添加到表的末尾，因为下一个索引值总是高于任何前一个索引值（正如您已经知道的那样）。
除非你是在填补被删除的记录留下的漏洞。这可能间接发生，但可能是一个开销问题，因为整个记录必须重新定位，这显然比移动索引键值和指针要多。
对于单个记录的查询，集群记录并不像对于记录范围（例如，订单的项目、客户、用户。例如，如果您可以为同一个用户提取几条（或几百条）记录，那么就值得进行集群。对于单个用户连续插入记录的可能性要小得多（在大多数情况下），因此按时间顺序进行聚类没有多大帮助。但您的要求可能会有所不同。
您没有指定innodb，所以我主要回答myisam（默认值），其中只有自动增量或按时间顺序索引才能模拟集群-没有显式选项。
46qrfjad3#
根据我的发现：
数据库表中的行按群集索引排序。一个表上只有一个聚集索引，因为您只能以一种方式对行进行排序。定义主键时，将自动在此键上创建聚集索引。因此，表是按主键排序的。假设索引使用B+树实现，则集群索引的叶节点实际上是包含数据库表的实际行的页（或指向包含磁盘上数据的实际页的指针）。因此，当您向下遍历该索引时，您将到达包含要搜索的记录的页面。注意：您必须通过主键进行搜索，因为B+树中的中间节点包含主键中的值。
当您创建辅助索引（非聚集索引）时，数据库将创建一个B+树，其中中间B+树节点中的值实际上是已创建辅助索引的列中的值。当您向下遍历二级索引时，您会到达叶节点（这是一个页面），其中包含与您正在搜索的值相对应的主键值。注意：如果二级索引列包含重复的值，那么搜索算法是这样的，你得到那些包含一组主键对应于你要搜索的值的页面。在获得与正在搜索的值对应的主键值之后，使用主索引（聚集索引）访问包含这些行的实际页。
注意：上面部分中的昂贵操作是IO，其中您从磁盘获取页面并将其插入内存（确切地说是
buffered_pool）。图形表示-https://stackoverflow.com/a/67958216/7547722更多-https://dba.stackexchange.com/a/260337/198502