MySQL 如何设计主键-木庄网络博客

本文摘自PHP中文网，作者Guanhui，侵删。

本篇讲解 Mysql 的主键问题，从为什么的角度来了解 Mysql 主键相关的知识，并拓展到主键的生成方案问题。再也不怕被问到 Mysql 时只知道 CRUD 了。

一、为什么需要主键

数据记录需具有唯一性(第一范式)

数据需要关联 join

数据库底层索引用于检索数据所需

以下废话连篇，可以直接跳过到下一节。

“信息是用来消除随机不定性的东西”（香农）。人通过获得、识别自然界和社会的不同信息来区别不同事物，得以认识和改造世界。数据是反映客观事物属性的记录，是信息的具体表现形式。数据经过加工处理之后，就成为信息；而信息需要经过数字化转变成数据才能存储和传输。数据库就是用于存储数据记录的。既已如此，记录便是具有确定性(相对)的信息，其确定性即唯一性。我们得出第一条原因：

1.数据记录需具有唯一性

世界是由客观存在及其关系组成的。数据是数字化和模型化的存在关系。数据除了本身的描述价值外，其价值还在于其相互关联性。为实现关联的准确性，数据需要有对外相互关联的标识。所以体现在数据存储上，主键的第二作用，也是存在的第二因素即：

2.数据需要关联

数据用于描述客观实在的，本身没有意义。只有在根据主观需求组织之后，通过一定方式满足人认识事物的过程才具有了意义。所以数据需要被检索，被组织。则主键第三个作用：

3.数据库底层索引用于检索数据所需

二、为什么主键不宜过长

这个问题的点在长上。那短比长有什么优势？（嘿嘿嘿，内涵）―― 短不占空间。但这么点磁盘空间相对整个数据量来说微不足道，而且我们一般不怎么用到主键列。那么原因应该在快上，而且和原始数据关系不大。以此自然得出和索引相关，而且和索引读取相关。那么为什么长主键在索引中会影响性能？

上面是 Innodb 的索引数据结构。左边是聚簇索引，通过主键定位数据记录。右边是二级索引，对列数据做索引，通过列数据查找数据主键。如果通过二级索引查询数据，流程如图上所示，先从二级索引树上搜索到主键，然后在聚簇索引上通过主键搜索到数据行。其中二级索引的叶子节点是直接存储的主键值，而不是主键指针。所以如果主键太长，一个二级索引树所能存储的索引记录就会变少，这样在有限的索引缓冲中，需要读取磁盘的次数就会变多，所以性能就会下降。

三、为什么建议使用自增 ID

InnoDB 使用聚簇索引，如上图所示，数据记录本身被存于主索引（一颗 B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL 会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB 默认为 15/16），则开辟一个新的页（节点）。

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上，如下图左侧所示。否则由于每次插入主键的值近似于随机，因此每次新记录都要被插到现有索引页的中间某个位置，MySQL 不得不为了将新记录插到合适位置而移动数据，如下图右侧所示，这样就造成了一定的开销。由于此，Mysql 为维护索引可能需要频繁的刷新缓冲，增加了方法磁盘 IO 的次数，而且时常需要对索引结构进行重组织。

四、业务 Key VS 逻辑 Key

业务 Key，即使用具有业务意义的 id 作为 Key，比如使用订单流水号作为订单表的主键 Key。逻辑 Key，即无关业务的 Key，按某种规则生成 Key，如自增 Key。

业务 Key 的优点

Key 具有业务意义，在查询时可以直接作为搜索关键字使用

不需要额外的列和索引空间

可以减少一些 join 操作。

业务 Key 的缺点

当业务发生变化时，有时需要变更主键

涉及多列 Key 时比较难操作

阅读剩余部分