经常看到有帖子或博客讨论主键选择问题,有人建议使用业务无关的自增主键,有人觉得没有必要,完全可以使用如学号或身份证号这种唯一字段作为主键。不论支持哪种论点,大多数论据都是业务层面的。如果从数据库索引优化角度看,使用InnoDB引擎而不使用自增主键绝对是一个糟糕的主意。下面从各个方面来讨论一下。 在使用InnoDB存储引擎时,如果没有特别的需要,请永远使用一个与业务无关的自增字段作为主键,除非高并发写入操作可能需要衡量自增主键或有业务安全性要求,后面会讲。 一、首先不管主键策略是什么,这两点都是必须遵守的。 主键不可修改 对于数据库来说,主键其实是可以修改的,只要不和其他主键冲突就可以。但是,对于应用来说,如果一条记录要修改主键,那就会出大问题。 因为主键的第二个作用是让其他表的外键引用自己,从而实现关系结构。一旦某个表的主键发生了变化,就会导致所有引用了该表的数据必须全部修改外键。很多Web应用的数据库并不是强约束(仅仅引用主键但并没有设置外键约束),修改主键会导致数据完整性直接被破坏。 业务字段不可用于主键 所有涉及到业务的字段,无论它看上去是否唯一,都决不能用作主键。例如,用户表的Email字段是唯一的,但是,如果用它作主键,就会导致其他表到处引用Email字段,从而泄露用户信息。 类似的,看上去唯一的用户名、身份证号等,也不能用作主键。对这些唯一字段,应该加上unique索引约束。 此外,修改Email实际上是一个业务操作,这个操作就直接违反了上一条原则。 那么,主键应该使用哪个字段呢? 主键必须使用单独的,完全没有业务含义的字段,也就是主键本身除了唯一标识和不可修改这两个责任外,主键没有任何业务含义。 二、主键应该用什么类型? 上面说了,不考虑业务,从数据库索引优化角度看,使用InnoDB引擎而不使用自增主键绝对是一个糟糕的主意。 下面先简单说说MySQL索引实现。在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。 MyISAM存储引擎 MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。 下图是MyISAM索引的原理图: 这里设表一共有三列,假设我们以Col1为主键,则上图是一个MyISAM表的主索引(Primary key)示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主