老大问我：“为什么建表的时候要设置自增id？用序号做主键不是很好吗？”不，先从进程和表结构说起。我：blahblahblahblahblahblah...老板：为什么给这张表设置自增id？只需使用序列号（用户号/产品号）作为主键即可。?我：这是DBA规定的。创建表id、create_time、update_time必须有三个字段。《Java 开发规范》也是这样规定的。小朋友：（附和）对，这是规定！老板：自来水号，你就只有这个指标吗？设置为主键，这样就不用id了，存一个查询回表？我：……（好像很有道理，所以不敢说话。）现在，回过头来看看为什么要设计一个自增id？我：拿出一个小本子（回去查资料~）。”1.建表协议Java开发手册-松山版工作中，DBA在建表的时候，也会复习建表SQL来检查是否符合规范，常用字段是否有索引。CREATETABLE`xxxx`(`id`bigint(20)NOTNULLAUTO_INCREMENTCOMMENT'自增主键',`create_time`datetime(3)NOTNULLDEFAULTcurrent_timestamp(3)COMMENT'创建时间',`update_time`datetime(3)NOTNULLDEFAULTcurrent_timestamp(3)ONUPDATEcurrent_timestamp(3)COMMENT'更新时间',PRIMARYKEY(`id`)USINGBTREE,KEY`idx_create_time`(`create_time`)USINGBTREE,KEY`idx_update_time`(`update_time`)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=5DEFAULTCHARSET=utf8mb4COMMENT='table评论';所以在我的使用过程中，序列号被设置为一个单独的字段，比如trans_no，但是这次遇到了一个问题：既然trans_no是唯一的，为什么不直接用trans_no作为id呢？让我们从查找相关资料开始，逐步了解为什么？2.主键什么是主键？在MySQL主键的这个定义中，我们主要关注最后一句话：“在选择主键值时，考虑使用任意值（合成键）而不是依赖于从其他来源派生的值（自然键）。“意思是在创建主键时，尽量使用MySQL自增主键，而不是使用业务产生的值作为主键。主键的特点简而言之：非空、唯一、很少或没有变化。如何添加主键可以在建表的时候指定，也可以使用alter语句来添加主键，不过??官方建议是在建表的时候指定。为什么要加主键主键可以唯一标识这一行数据，这样在进行删除更新操作的时候，只操作这一行数据。索引要求每个InnoDB表都有一个特殊的索引，即聚簇索引，用于存储行数据。通常，聚集索引和主键是同义词。声明主键，InnoDB将使用主键作为聚集索引。未声明时，将在所有UNIQUE键列的位置找到第一个索引，NOTNULL并将其用作聚集索引。如果没有声明，找不到合适的UNIQUE索引，内部会生成一个隐藏的聚簇索引GEN_CLUST_INDEX。此隐藏行ID为6个字节且单调递增。3、索引这里只介绍InnoDB引擎。具体可以参考官方文档，介绍比较简单。IndexedClassifiedClusteredIndex：表存储按照主键列的值进行组织，以加快涉及主键列的查询和排序。引入主键的同时也引入了聚簇索引。二级索引：也可以称为辅助索引，辅助索引中会记录对应的主键列和辅助索引列。基于辅助索引查找时，会根据辅助索引得到对应的主键列，然后根据主键在聚簇索引中查找。一般不建议主键太长，因为主键太长辅助索引会占用更多的空间。》补充：回表：先在二级索引中查询对应的主键值，然后根据主键去聚簇索引中获取查询。索引覆盖：二级索引记录主键列和二级索引列。如果我只查询主键列的值和二级索引列的值，那么就不需要回表了。”索引的物理结构是InnoDB使用的B+数数据结构，构建聚簇索引值（主键/UNQIUE/或自生）。一颗B+树，行记录数据存放在叶子节点中，所以每个叶子节点也可以称为一个数据页。每个数据页默认大小为16k，支持自定义。图：《MySQL 技术内幕 InnoDB 存储引擎》数据插入当插入数据时，InnoDB会将1/16页空出来，用于以后插入和更新索引记录。顺序插入（升序或降序）：会填满索引页剩余的15/16随机插入：只使用容量的1/2到15/16随机插入会频繁移动和分页，导致大碎片数量，使索引树不够紧凑。但是使用顺序插入的方式，数据比较紧凑，空间利用率更高。4.总结Q&AQ：什么是返回表和索引覆盖率？A：回表：先在二级索引中查询对应的主键值，然后根据主键去聚簇索引中查询。索引覆盖：二级索引记录了主键列和二级索引列。如果我只查询主键列的值和二级索引列的值，那么就不需要回表了。Q：为什么要设置自增主键id？A：可以唯一标识一行数据，InnoDB建立索引树时会用到主键。自增id是顺序的，可以保证索引树上的数据比较紧凑，空间利用率更高，减少数据页的拆分合并等操作，提高效率。一般使用手机号和身份证号作为主键并不能保证顺序。序列号一般比较长，比如28位，32位等，如果太长，二级索引会占用更多的空间。同时，出于业务需要，序列号具有一定的随机性。结束语本文主要是查资料了解为什么要设置一个与业务无关的自增id作为主键。很多内容都比较简单，比如InnoDB的B+树，分页合并，插入过程。有兴趣的朋友可以深入研究。同时，除了建表时设置一个自增id作为主键外，小伙伴们在业务开发过程中会不会也遇到一种情况：用户注销、数据删除等都是逻辑删除，而不是物理删除。这篇文章的介绍比较简单，也有不足之处。希望大家指正。相关资料[1]MySQL官方文档：https://dev.mysql.com/doc/refman/8.0/en/[2]《MySQL 技术内幕 InnoDB 存储引擎》第二版转载自微信公众号“刘志航”，大家可以使用以下二维码关注。转载本文请联系刘志航公众号。
