分享 MySQL 为什么需要一个主键

ibugs · 2015年07月07日 · 最后由 ibugs 回复于 2015年08月28日 · 18560 次阅读
本帖已被设为精华帖!

主键

表中每一行都应该有可以唯一标识自己的一列(或一组列)。

一个顾客可以使用顾客编号列,而订单可以使用订单 ID,雇员可以使用雇员 ID 或 雇员社会保险号。

主键(primary key)一列(或一组列),其值能够唯一区分表中的每个行。 唯一标识表中每行的这个列(或这组列)称为主键。没有主键,更新或删除表中特定行很困难,因为没有安全的方法保证只设计相关的行。

虽然并不总是都需要主键,但大多数数据库设计人员都应保证他们创建的每个表有一个主键,以便于以后数据操纵和管理

表中的任何列都可以作为主键,只要它满足一下条件:

1、任何两行都不具有相同的主键值 2、每个行都必须具有一个主键值(主键列不允许 NULL 值)

主键值规范:这里列出的规则是 MySQL 本身强制实施的。

主键的最好习惯: 除 MySQL 强制实施的规则外,应该坚持的几个普遍认为的最好习惯为:

1、不更新主键列的值 2、不重用主键列的值 3、不在主键列中使用可能会更改的值(例如,如果使用一个名字作为主键以标识某个供应商,应该供应商合并和更改其名字时,必须更改这个主键)

总之:不应该使用一个具有意义的 column(id 本身并不保存表 有意义信息)作为主键,并且一个表必须要有一个主键,为方便扩展、松耦合,高可用的系统做铺垫。


非常感谢 @pathbox@est@hooooopo 同学的提醒。主键的作用,在于索引。

无特殊需求下 Innodb 建议使用与业务无关的自增 ID 作为主键

InnoDB 引擎使用聚集索引,数据记录本身被存于主索引(一颗 B+Tree)的叶子节点上。这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放,因此每当有一条新的记录插入时,MySQL 会根据其主键将其插入适当的节点和位置,如果页面达到装载因子(InnoDB 默认为 15/16),则开辟一个新的页(节点)

1、如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。如下图所示:

这样就会形成一个紧凑的索引结构,近似顺序填满。由于每次插入时也不需要移动已有数据,因此效率很高,也不会增加很多开销在维护索引上。

2、 如果使用非自增主键(如果身份证号或学号等),由于每次插入主键的值近似于随机,因此每次新纪录都要被插到现有索引页得中间某个位置:

此时 MySQL 不得不为了将新记录插到合适位置而移动数据,甚至目标页面可能已经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多开销,同时频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结构,后续不得不通过 OPTIMIZE TABLE 来重建表并优化填充页面。

在使用 InnoDB 存储引擎时,如果没有特别的需要,请永远使用一个与业务无关的自增字段作为主键。

InnoDB 存储引擎采用了聚集(clustered)的方式,因此每张表的存储都是按主键的顺序进行存放。如果没有显式地在表定义时指定主键,InnoDB 存储引擎会为每一行生成一个 6 字节的 ROWID,并一次作为主键。

mysql 在频繁的更新、删除操作,会产生碎片。而含碎片比较大的表,查询效率会降低。此时需对表进行优化,这样才会使查询变得更有效率。


非常感谢 @xiaoronglv 同学

  • If you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index.

  • If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index.

具体参考 14.2.10.1 Clustered and Secondary Indexes

我的简书

使用自增 id 为主键, 加 自定义索引配合, 基本就没什么问题了

@pathbox 恩。因为有些同事,或小伙伴问,为什么要给表设置主键。其实 主键是一个表的最基本设置。

主键的更大作用是磁盘存储。。。。得靠这玩意。

概括成一句话:应该用代理键做主键。

if there is no primary key,innodb will create a hidden column as primary key,and cluster rows on it

应该问,为什么 mysql 不把主键作为必选项/实现一个隐藏主键?

@xiaoronglv you are right!

自增 id 不会带来效率提升, 经常参与排序的列可以,比如顾客编号,时间

一人一个身份证号

自增 id 会带来额外的开销,建立合适的索引有利于数据的插入和查找。

我觉得:ID, create_time, update_time 这三个应该数据库本身默认内置,这点 Rails 做得好。

貌似会有一个自增的 ROW_COUNT 但是没法引用。

主键名字应该是设成 id 还是 #tablename_id 比较好?

如果使用自增长列作为主键,在查询上是不能利用到索引的优势的。因为你基本上不会用到自增列作为查询的条件。更多时候你可以考虑不会经常改变的列来作为索引,楼主说的应该说明限制条件。

#15 楼 @kakaxi2 一般情况来说呢,在 InnoDB 中,将 id 作为索引的最后一个列,并且默认情况下,设置主键索引的。因为自增主键,不会重复,那么就代表的高的索引基数(Cardinality),查询,如

SELECT * FROM table ORDER BY id desc limit 10;

就会很有效果。

有些查询,按照创建时间倒叙排列


SELECT * FROM table ORDER BY created_at desc ;
其实可以改写成
SELECT * FROM table ORDER BY id desc;

这种情况下,还是蛮实用的。因为在 created_at 上创建索引的代价,会远远大于在 id 上创建索引的代价。 所以创建一个 主键 id,并且设置为自增,还是有必要的。

不太明白 这里的 ** 不会经常改变的列 ** 是什么意思呢?

#11 楼 @winnie Rails 做的确实很好,经常会有这种,按照创建时间,或更新时间倒叙排列的需求。

#10 楼 @echooo 根据不同的业务需求选择合适的主键 id。

#8 楼 @sevk 你可以试一试哦,在各种不同情况下,会有不同的反应。InnoDB,创建索引的时候,默认会把 id 当做索引中的最后一列。自增不一定最好,但是还是希望主键不要重复。

#16 楼 @ibugs 你说的这个场景确实是使用自增主键的一个好的例子。但是大部分时候,如果你用 InnoDB,而且不需要特殊的聚簇索引,一个好的做法就是使用代理主键 (surrogate key)——独立于你的应用中的数据,就是使用一个 AUTO_INCREMENT 的列,这会保证记录按照顺序插入,这是因为 InnoDB 使用的是聚簇索引

@kakaxi2 实际上由于 id 是聚簇索引,并没有一个单独的索引树存 id,因此在磁盘上,id 索引树的叶节点上就是数据。 InnoDB 以 page 为单位读取,在取 id 的过程中,必须将所有的数据读入。仁兄,你说的是这个意思么?

#23 楼 @ibugs 兄弟,好像不是啊

#24 楼 @kakaxi2 仁兄,能详细说明下这种方式的具体应用场景和这种方式的优势么?

在使用 InnoDB 存储引擎时,如果没有特别的需要,请永远使用一个与业务无关的自增字段作为主键。
----- 这句话有出处吗? 是 mysql 手册上写的? 请发个链接.

@sleepless 以及《高性能 MySQL》第三版,121 页

这是一个通用的设计实践,在 “查找表” 时采用整数主键避免采用基于字符串的值进行关联

及后面的 4.1.6 选择标识符 (identifier) 中的描述

所以为标识列选择数据类型时,应该选择跟表关联表中的对应列一样的类型 整数通常是标识列最好的选择,因为他们很快并且可以使用 AOTO_INCREMENT

所以综上所述,使用与业务无关的主键 ID 是一个非常不错的选择。

我们的缓存就是通过主键查询的。

恩,一直是这样实现,现在知道为什么了.

@sleepless

《高性能 MySQL》终于找到原话了。

当然这个也不是绝对的了,要根据业务来选择合适的主键

ibugs Schema 与数据类型优化小技巧 中提及了此贴 08月30日 11:44
需要 登录 后方可回复, 如果你还没有账号请点击这里 注册