MySQL可视化表自增_设计 MySQL 数据表的时候一般都有一列为自增 ID，这样设计原因是什么，有什么好处？...

注：本文截图摘自《高性能 MySQL》

聚簇索引

在 InnoDB 中，每个表都会有一个聚簇索引，在定义了主键( primary key )的情况下，主键所在的列会被作为聚簇索引存储。所谓聚簇索引，意思是数据实际上是存储在索引的叶子节点上，「聚簇」的含义就是数据行和相邻的数据紧凑地存储在一起。因为不能(或者不值得)同时把数据行存储在两个不同的位置，所以一个表只能有一个聚簇索引。

关于 InnoDB 选择哪个列作为聚簇索引存储，大概的优先级为：如果定义了主键( primary key )，则使用主键

如果没有定义主键，则选择第一个不包含 NULL( NOT NULL )的 UNIQUE KEY

如果也没有，则会隐式定义一个主键作为聚簇索引。

下图展示了聚簇索引中记录(数据)是如何存放的：

如上图所示，聚簇索引中，不但存储了索引，还存储了整张表的数据到叶子节点上。可以认为 InnoDB 中，聚簇索引「就是」表。对应的，InnoDB 的其它索引中，叶子节点所存储的，其实是主键的值。存储主键的值而不是数据行的位置，这样的存储方式可以减少当出现数据行移动或者数据页分裂时二级索引的维护工作。

聚簇与非聚簇表的数据存储方式

我们假设有如下数据表：

我们假设列 col1 是 primary key，那么，对应的聚簇索引存储结构就会如下：

(暂时不必关心 TID 和 RP，它们是事务 ID 和回滚指针)如上所示，聚簇索引除了存储 col1 的值之外，还会存储其它列的值(本例的 col2)。

如果 col2 设置了普通索引，对应地，col2 的索引存储结构如下：

可以看到，对应 B+ 树叶子节点上存储了对应行的主键的值。

抽象来看，InnoDB 通过如下结构存储主键索引(聚簇索引)：

InnoDB 通过如下结构存储二级索引：

作为参考，MyISAM(另一个 MySQL 存储引擎)是这样存储主键索引和二级索引的：

InnoDB 表中按主键顺序插入

一般来讲，使用一个业务无关的自增( AUTO_INCREMENT )ID，可以保证数据在插入时会被按顺序写入。假设我们使用 UUID 作为聚簇索引，在插入数据的时候，聚簇索引所被插入的位置将变得完全随机。大量的随机插入会导致页分裂和碎片非常多。

下图展示了数据插入有序递增时，聚簇索引会如何存储插入的数据行：

可以看到，因为主键是有序的，InnoDB 把每一条记录都存储在上一条记录的后面。当当前页即将写满时(之所以是即将而不是已经，是因为 InnoDB 会预留一点空间用于以后修改数据，默认预留页的 1/16 大小)，下一条记录被插入时，将会写入到新的页中去。所有被插入的数据，都将有序地放到聚簇索引最后的位置上去。

对应地，如果使用 UUID 作为主键索引，InnoDB 将完全随机地将数据插入到聚簇索引对应的位置上去：

如上，因为新插入的行的主键不一定比之前插入的大(由于是 UUID，将会非常随机)，所以 InnoDB 将无法简单地总是把新行插入到索引的最后，而是需要根据主键 ID 的值为它寻找合适的索引位置，并为其分配空间。使用 UUID 作为聚簇索引，有以下缺点：写入的目标页可能已经写入到磁盘而不只是存在于内存中，又或者目标页还没有被加载到内存中，InnoDB 在插入前需要先找到并从磁盘中读取目标页到内存中去，这会产生大量的磁盘随机 IO。

因为写入是乱序的，InnoDB 需要频繁地做页分裂操作，一遍为新的行分配空间。页分裂需要移动大量数据。

有序频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。

所以，在使用 InnoDB 时应该尽可能使用单调递增的主键 ID 顺序插入数据。单调递增的主键 ID 并不只有 AUTO INCREMENT 一种方式，比如一些分布式发号器算法，也能产生递增的 ID 序列。