MySQL InnoDB表必须有主键，并且推荐整型自增

【面试题】为什么建议 MySQL InnoDB 表建一个整型自增主键？
  你如果不了解MySQL索引中数据的存储机制、查询过程和插入过程，请参考《浅析二叉树、B树、B+树、B*树和MySQL索引底层原理》，它介绍了二叉树、AVL树、红黑树、B树、B+树和B*树等六种树的数据结构后，浅析MySQL InnoDB索引选择B+树的原因，并给出一些 MySQL 索引高频面试题。这些知识点对回答上述问题非常非常重要，我简明扼要的归纳与本文密切相关的以下几点索引机制：

• B+树是一种多路平衡搜索树
  
• MySQL InnoDB存储引擎的索引在B+树上维护表数据
  
• B+树上的每个节点在计算机中叫做数据页，默认大小为16KB
  
• 主键索引的叶子节点存储所有数据行，内部节点只存储用于路由的主键值
  
• 辅助索引的叶子存储的是主键值而非数据行地址，查询时需通过主键回表
  
• 主键索引的叶子通过双向链表链接，其数据行按主键值升序排列，提升范围查询性能
  

  我们接下来进入正题，回答为什么主键建议是整型且自增这个面试题。MySQL 建议使用‌自增主键‌主要出于‌性能、存储效率和系统稳定性‌的综合考量。
主键为什么是整型

  主键为什么需要设置为整型？查找操作就是不停的进行数据比较，数据比较是消耗CPU资源的，而不同的数据类型耗时不同，字符串之间的比较涉及字符编码和排序规则，因此比整型直接比较大小更复杂更耗时。
‌  减少索引存储空间。从存储空间来说，长整型bigint主键占用8个字节，字符串索引的大小取决于字符集类型和字符串的长度。长度超过8个字节的字符串（如字符串类型的手机号、UUID或者身份证号码）会占用更多的存储空间，从而影响索引的性能。另一方面，由于辅助索引的叶子节点存储主键值，所以主键越小，辅助索引占用空间就越少。
主键为什么需要自增

  为什么需要自增主键呢？原因也可想而知，避免页分裂和页分裂带来的额外I/O操作，提升插入性能。InnoDB的主键索引是按主键ID大小排好序的，如果插入的新数据是自增的，那么只需要按顺序追加到索引末尾，当一页写满，就会自动开辟一个新的页。写入过程如下图所示：

  这样就会形成一个紧凑的索引结构，近似顺序填满，提升了数据页的最大填充率，不浪费数据页。由于新记录不需要为计算插入位置而做出额外的消耗，不需要移动已有数据或触发页分裂，也不需要增加很多开销在维护索引上，因此大幅提高写入效率。如果使用非自增主键，由于每次插入主键的位置近似于随机，使得MySQL不得不移动已有数据，甚至目标可能已经被回写到磁盘上而从缓存中清掉了，此时又要从磁盘上读回来，这增加了很多开销；同时频繁的移动记录、页分裂造成了大量的碎片，使得索引结构不够紧凑，后续不得不通过 OPTIMIZE TABLE来重建表，从而消除碎片、回收空间，并让数据页的填充更高效。
  保持数据有序，优化查询性能‌。自增主键的有序性使得B+ 树支持高效的区间扫描（如 WHERE id > 1000 and id < 1100），即从根节点找到满足条件的第一个叶子节点后，无需再遍历内部节点，直接使用叶子节点之间的链接遍历相邻的叶子节点，并提取出在查询范围内的键值对，直到完成区间扫描。
‌  简化系统设计，增强可维护性‌。自增主键由数据库自动生成，与业务逻辑解耦，避免因业务字段变更（如邮箱、手机号）导致主键更新的高成本操作。‌确保唯一性与非空性‌。主键天然保证每行数据唯一且非空，自增机制进一步自动化该过程，降低应用层复杂度。
  引申一下，主键一定需要自增吗？答案是不。我们都知道在分布式系统或者分库分表时，自增主键就显得捉襟见肘不适用了。比如把订单表分成16个表，如果都使用自增ID的话，肯定会造成订单id重复，所以此时的解决方案就是分布式id（如雪花算法、美团Leaf、Redis生成ID等），保证趋势递增即可。
  总之，不应该使用一个具有业务含义的列作为主键，并且一个表必须有一个整型自增主键，为方便扩展、松耦合、高可用的系统做铺垫。
自增主键的缺点

自增ID有那么多好处，有缺点吗？肯定有。下面简单枚举几个：

• 高并发性能瓶颈：innodb在高并发写入场景下，主键的上界会成为争抢的热点，造成剧烈的锁竞争，导致性能下降。
  
• 分布式系统难题：在分布式架构中，自增ID无法实现跨数据库或跨系统的唯一标识，因而难以分布式存储，而且数据迁移和分库分表时会出现ID冲突问题。
  
• 安全隐患：自增ID具有可预测性，比如可根据自增来判断订单量。
  
• 业务灵活性不足：当业务需要根据特定规则生成ID时，自增ID无法满足需求，缺乏灵活性。
  

小结

  今天我通过讲解MySQL数据的存储方式以及数据查找与插入过程，从MySQL的底层机制了解了MySQL主键为什么建议使用整型并且自增，最后我们引申了一个分布式id的问题，此时并不强制严格自增，保证趋势递增即可。
  好了，今天的文章就到这里了。快去翻阅一下你写的SQL语句，看看在哪些地方重构可以提升性能吧！
Reference

• https://cloud.tencent.com/developer/article/2512204
  
• MySQL 整数索引与字符串索引的区别
  

  读后有收获，小礼物走一走，请作者喝咖啡。  

Buy me a coffee. ☕Get red packets.        

        

作者：楼兰胡杨链接：https://www.cnblogs.com/east7/p/19719822本文版权归作者和博客园共有，欢迎转载，但请注明原文链接，并保留此段声明，否则保留追究法律责任的权利。
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！