mysql表中是否需要自增主键？

发表于 2020-11-22 更新于 2022-08-18 分类于 Linux 评论：阅读次数：

最近遇到个数据库表设计的问题，一张账号表，业务逻辑存在一列唯一accountId，是否还要在数据库表中加一列自增int作为主键？这应该是实践中普遍存在的问题，于是研究了下推荐做法。

简单回答：这个问题没有明确答案，看情况决定。stackoverflow的相关问题已经被标记为带有观点的回答。没有答案的原因在于是否需要自增主键主要取决于应用场景。不过我倾向于在绝大部分情况下，使用自增int主键，如有必要，增加额外的唯一键（如这里的accountId）进行索引和查询。

如下讨论都基于InnoDB引擎。

自增主键 vs UUID

主键(Primary Key)定义

官方文档中主键的定义：

A set of columns—and by implication, the index based on this set of columns—that can uniquely identify every row in a table. As such, it must be a unique index that does not contain any NULL values.

InnoDB requires that every table has such an index (also called the clustered index or cluster index), and organizes the table storage based on the column values of the primary key.

与聚集索引(Clustered Index)的关系

官方文档

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.

一般来说，聚集索引和主键是同一码事。一张表只能有一个聚集索引，原因也很简单，数据按聚集索引对应的列顺序进行存储。

使用自增主键(auto-incrementing primary key)

优点：

自增主键执行insert效率高，数据按顺序存储
占空间小，所有二级索引都含有主键并使用主键进行记录查找
物理存储要求表必须有主键，自增int主键开销小，使用便捷

缺点：

高并发场景自增Id的生成影响系统性能
该值与业务无关，除了唯一标识一条记录并无太多意义（未必是缺点，正因为没有确定意义，业务变化时不会影响自增主键，从而不影响底层存储顺序）

自增主键影响并发性能？

# auto-increment locking

The convenience of an auto-increment primary key involves some tradeoff with concurrency. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.

确实如此，在高并发的场景下，自增主键会影响数据库性能，多个并发的事务必须串行执行。

使用UUID为主键

优点：

UUID便于分布式数据库并发插入
业务逻辑不依赖于Id生成，如业务需要通过Id关联多条记录，在自增Id条件下记录必须先行插入之后才能获取Id再行关联。UUID可以线下生成Id并直接关联，不依赖于数据库

缺点：

相对自增主键，使得所有二级索引占据更多空间
数据插入效率较低，新插数据可能在索引的中间位置，为将数据插入合适的位置可能需要额外的IO操作，同时造成索引不连续，影响查询效率

针对第一个缺点进行说明，# How Secondary Indexes Relate to the Clustered Index:

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.

If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key.

由于所有二级索引都包含了主键，从而在使用二级索引的查询中先用二级索引找到对应的主键，再用主键在聚集索引中找到对应的行。如果主键本身很长，那么二级索引占据Buffer Pool的空间就会变大，不利于数据库的扩展。

所以这事有点反直觉，对自然键如accountId本身占空间较大的情况，使用自增int和二级索引自然键，反比直接使用自然键效率高。主要差别在于索引所占空间的不同。