主键是否自增问题
- 主键id不自增行不行? 当然可以
- 为什么要用自增id做主键?
- 没有主键可以吗?
- 什么情况下不应该自增?
解答 :为什么要用自增id做主键?
我们在数据库里保存的数据就跟excel表一样,一行行似的。
user表
而在底层,这一行行数据,就是保存在一个个16k大小的页里。
每次都去遍历所有的行性能会不好,于是为了加速搜索,我们可以根据主键id,从小到大排列这些行数据,将这些数据页用双向链表的形式组织起来,再将这些页里的部分信息提取出来放到一个新的16kb的数据页里,再加入层级的概念。于是,一个个数据页就被组织起来了,成为了一棵B+树索引。
B+树结构
而当我们在建表sql里声明了PRIMARY KEY (id)
时,mysql的innodb引擎,就会为主键id生成一个主键索引,里面就是通过B+树的形式来维护这套索引。
到这里,我们有两个点是需要关注的:
- 数据页大小是固定16k
- 数据页内,以及数据页之间,数据主键id都是从小到大排序的
由于数据页大小固定了是16k,当我们需要插入一条新的数据,数据页会被慢慢放满,当超过16k时,这个数据页就有可能会进行分裂。
针对B+树叶子节点,如果主键是自增的,那它产生的id每次都比前一次要大,所以每次都会将数据加在B+树尾部,B+树的叶子节点本质上是双向链表,查找它的首部和尾部,时间复杂度O(1)。而如果此时最末尾的数据页满了,那创建个新的页就好。
主键id自增的情况
主键id不自增的情况
如果主键不是自增的,比方说上次分配了id=7,这次分配了id=3,为了让新加入数据后B+树的叶子节点还能保持有序,它就需要往叶子结点的中间找,查找过程的时间复杂度是O(lgn),如果这个页正好也满了,这时候就需要进行页分裂了。并且页分裂操作本身是需要加悲观锁的。总体看下来,自增的主键遇到页分裂的可能性更少,因此性能也会更高。
解答 :没有主键可以吗?
mysql表如果没有主键索引,查个数据都得全表扫描,那既然它这么重要,我今天就不当人了,不声明主键,可以吗?
嗯,你完全可以不声明主键。
你确实可以在建表sql里写成这样。
CREATE TABLE `user` (
`name` char(10) NOT NULL DEFAULT '' COMMENT '名字'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
看起来确实是没有主键的样子。然而实际上,mysql的innodb引擎内部会帮你生成一个名为ROW_ID
列,它是个6字节的隐藏列,你平时也看不到它,但实际上,它也是自增的。有了这层兜底机制保证,数据表肯定会有主键和主键索引。
跟ROW_ID被隐藏的列还有trx_id
字段,用于记录当前这一行数据行是被哪个事务修改的,和一个roll_pointer
字段,这个字段是用来指向当前这个数据行的上一个版本,通过这个字段,可以为这行数据形成一条版本链,从而实现多版本并发控制(MVCC)。有没有很眼熟,这个在之前写的文章里出现过。
隐藏的row_id列
解答 :什么时候不适合让主键自增?
-
分库分表的情况,如果让主键自增,那么很可能会导致多个重复的user_id出现
-
用户id不建议用自增id
前面提到的不建议使用自增id的场景,都是技术原因导致的,而下面介绍的这个,单纯是因为业务。
举个例子吧。
如果你能知道一个产品每个月,新增的用户数有多少,这个对你来说会是有用的信息吗?
对程序员来说,可能这个信息价值不大。
但如果你是做投资的呢,或者是分析竞争对手呢?
那反过来。
如果你发现你的竞争对手,总能非常清晰的知道你的产品每个月新进的注册用户是多少人,你会不会心里毛毛的?
如果真出现了这问题,先不要想是不是有内鬼,先检查下你的用户表主键是不是自增的。
如果用户id是自增的,那别人只要每个月都注册一个新用户,然后抓包得到这个用户的user_id,然后跟上个月的值减一下,就知道这个月新进多少用户了。
同样的场景有很多,有时候你去小店吃饭,发票上就写了你是今天的第几单,那大概就能估计今天店家做了多少单。你是店家,你心里也不舒服吧。
再比如说一些小app的商品订单id,如果也做成自增的,那就很容易可以知道这个月成了多少单。