数据库,主键为何不宜太长长长长长长长长?

标签: 数据库 主键 | 发表时间:2020-12-10 03:54 | 作者:58沈剑_架构师之路
出处:https://juejin.im/backend?sort=monthly_hottest

继续回答星球水友提问:

_沈老师,我听网上说,MySQL 数据表,在数据量比较大的情况下,主键不宜过长,是不是这样呢?_ 这又是为什么呢?

这个问题嘛,不能一概而论:

(1)如果是 InnoDB 存储引擎,主键不宜过长;

(2)如果是 MyISAM 存储引擎,影响不大;

先举个简单的栗子说明一下前序知识。

假设有数据表:

t(id PK, name KEY, sex, flag);

其中:

(1)id 是主键;

(2)name 建了普通索引;

假设表中有四条记录:

1, shenjian, m, A

3, zhangsan, m, A

5, lisi, m, A

9, wangwu, f, B

如果存储引擎是 MyISAM,其索引与记录的结构是这样的:

(1)有单独的区域存储记录 (record);

(2)主键索引与普通索引结构相同,都存储记录的指针(暂且理解为指针);

画外音:

(1)主键索引与记录不存储在一起,因此它是非聚集索引 (Unclustered Index);

(2)MyISAM 可以没有 PK;

MyISAM 使用索引进行检索时,会先从索引树定位到记录指针,再通过记录指针定位到具体的记录。

画外音:不管主键索引,还普通索引,过程相同。

InnoDB 则不同,其索引与记录的结构是这样的:

(1)主键索引与记录存储在一起;

(2)普通索引存储主键(这下不是指针了);

画外音:

(1)主键索引与记录存储在一起,所以才叫聚集索引 (Clustered Index);

(2)InnoDB 一定会有聚集索引;

InnoDB 通过主键索引查询时,能够直接定位到行记录。

但如果通过普通索引查询时,会先查询出主键,再从主键索引上二次遍历索引树。

回归正题, 为什么 InnoDB 的主键不宜过长呢?

假设有一个 用户中心场景,包含身份证号,身份证 MD5,姓名,出生年月等业务属性,这些属性上均有查询需求。
最容易想到的设计方式是:

  • 身份证作为主键

  • 其他属性上建立索引

user( id_code PK,
id_md5(index),
name(index),
birthday(index));

此时的索引树与行记录结构如上:

  • id_code 聚集索引,关联行记录

  • 其他索引,存储 id_code 属性值

身份证号 id_code 是一个比较长的字符串,每个索引都存储这个值,在数据量大,内存珍贵的情况下,MySQL 有限的缓冲区,存储的索引与数据会减少,磁盘 IO 的概率会增加。

画外音:同时,索引占用的磁盘空间也会增加。

此时,应该新增一个无业务含义的 id 自增列:

  • 以 id 自增列为聚集索引,关联行记录

  • 其他索引,存储 id 值

user( id PK auto inc,
id_code(index),
id_md5(index),
name(index),
birthday(index));

如此一来,有限的缓冲区,能够缓冲更多的索引与行数据,磁盘 IO 的频率会降低,整体性能会增加。

总结

(1)MyISAM 的索引与数据分开存储,索引叶子存储指针,主键索引与普通索引无太大区别;

(2)InnoDB 的聚集索引和数据行统一存储,聚集索引存储数据行本身,普通索引存储主键;

(3)InnoDB 不建议使用太长字段作为 PK(此时可以加入一个自增键 PK),MyISAM 则无所谓;
希望解答了这位水友的疑问。

欢迎大家继续提问,有问必答。

相关文章:
缓冲池 (buffer pool),这次彻底懂了!
写缓冲 (change buffer),这次彻底懂了!》

相关 [数据库 主键] 推荐:

数据库,主键为何不宜太长长长长长长长长?

- - 掘金后端本月最热
_沈老师,我听网上说,MySQL 数据表,在数据量比较大的情况下,主键不宜过长,是不是这样呢. 这个问题嘛,不能一概而论:. InnoDB 存储引擎,主键不宜过长;. MyISAM 存储引擎,影响不大;. 先举个简单的栗子说明一下前序知识. (2)name 建了普通索引;. MyISAM,其索引与记录的结构是这样的:.

数据库sharding

- - 数据库 - ITeye博客
当团队决定自行实现sharding的时候,DAO层可能是嵌入sharding逻辑的首选位置,因为在这个层面上,每一个DAO的方法都明确地知道需要访问的数据表以及查询参数,借助这些信息可以直接定位到目标shard上,而不必像框架那样需要对SQL进行解析然后再依据配置的规则进行路由. 另一个优势是不会受ORM框架的制约.

数据库索引

- - CSDN博客推荐文章
索引是由用户创建的、能够被修改和删除的、实际存储于数据库中的物理存在;创建索引的目的是使用户能够从整体内容直接查找到某个特定部分的内容. 一般来说,索引能够提高查询,但是会增加额外的空间消耗,并且降低删除、插入和修改速度. 1.聚集索引:表数据按照索引的顺序来存储的. 2.非聚集索引:表数据存储顺序与索引顺序无关.

数据库事务

- - 数据库 - ITeye博客
事务传播发生在类似以下情形:. 假设methodB的配置是:. 如果methodA在事务里,那么methodB也在这个事务中运行. 如果methodA不在事务里,那么methodB重新建立一个事务运行. 如果methodA在事务里,那么methodB也在这个事务中运行. 如果methodA不在是事务里,那么methodB在非事务中运行.

数据库优化

- - 数据库 - ITeye博客
程序运行效率,优化应用程序,在SP编写过程中应该注意以下几点: . a) SQL的使用规范: .   i.尽量避免大事务操作,慎用holdlock子句,提高系统并发能力.   ii.尽量避免反复访问同一张或几张表,尤其是数据量较大的表,可以考虑先根据条件提取数据到临时表中,然后再做连接.   iii.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该改写;如果使用了游标,就要尽量避免在游标循环中再进行表连接的操作.

数据库调优

- - 数据库 - ITeye博客
1、1、调整数据结构的设计. 这一部分在开发信息系统之前完成,程序员需要考虑是否使用ORACLE数据库的分区功能,对于经常访问的数据库表是否需要建立索引等. 这一部分也是在开发信息系统之前完成,程序员在这一步需要考虑应用程序使用什么样的体系结构,是使用传统的Client/Server两层体系结构,还是使用Browser/Web/Database的三层体系结构.

MySQL数据库的修复

- Xin - 博客园-首页原创精华区
找到mysql的安装目录的bin/myisamchk工具,在命令行中输入:. 然后myisamchk 工具会帮助你恢复数据表的索引. 好象也不用重新启动mysql,问题就解决了. 当你试图修复一个被破坏的表的问题时,有三种修复类型. 如果你得到一个错误信息指出一个临时文件不能建立,删除信息所指出的文件并再试一次--这通常是上一次修复操作遗留下来的.

Oracle 发布 NoSQL 数据库

- 冷月 - 博客园新闻频道
  Oracle 作为全球最大的关系型数据库提供商,在其产品链条中,也加入了 NoSQL 数据库这一环,而且这个新的数据库名字很霸气,就叫 NoSQL Database,想起了当年新浪微博更换 weibo.com 域名之时的一个笑话:. 原来有三家人做面包,张三家的面包叫三张牌面包,李四家的牌子叫李四牌面包,王五家出品的是王五牌面包,而突然有一天,张三家的面包改名了,叫面包牌面包.

WineHQ 数据库泄漏

- gnawux - LinuxTOY
运行于 *Nix 之上的开源跨平台 Win32 API 兼容层 WineHQ 的 AppDB 和 Bugzilla 数据库被黑客攻击. CodeWeavers CEO Jeremy 在信中提到黑客利用某种方式获取了 WineHQ 的 AppDB 和 Bugzilla 的访问,并且下载了完整数据库文件.