数据库主键设计

数据库主键设计

如果使用 b-tree 索引形式，有序 id 比无需 id 好，如果是 hash 索引，两个差别不大。主要原因是索引在磁盘上存储的形式， 常用的 b-tree 索引如果 id 是连续的，那么数据存储在相邻的磁盘上，如果查询和写入操作的 id 连续，那么减少随机读写硬盘的几率，提升读写效率。

作者：郭麒 链接：https://www.zhihu.com/question/43500172/answer/95876101 来源：知乎 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

以默认的 innodb 存储引擎为例：做为主键时，uuid和自增相比较，自增更适合。原因：1 uuid是无序的， 插入数据时，页的位置会发生变化，页分裂，速度慢。 2 uuid占的空间大，并且innodb中，别的索引还都要包含主键的值，那么每个索引的空间也都会增大，占的空间大，需要读数据时一般会认为需要的io次数多。

作者：河南-老宋 链接：https://www.zhihu.com/question/43500172/answer/113356943 来源：知乎 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

innodb 中的主键是聚簇索引，会把相邻主键的数据安放在相邻的物理存储上。如果主键不是自增，而是随机的，那么频繁的插入会使 innodb 频繁地移动磁盘块，而影响写入性能。

作者：Java编程宇宙 链接：https://www.zhihu.com/question/43500172/answer/2285446787 来源：知乎 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

因为 uuid 相对顺序的自增 id 来说是毫无规律可言的，新行的值不一定要比之前的主键的值要大，所以innodb无法做到总是把新行插入到索引的最后，而是需要为新行寻找新的合适的位置从而来分配新的空间。这个过程需要做很多额外的操作，数据的毫无顺序会导致数据分布散乱，将会导致以下的问题：1）写入的目标页很可能已经刷新到磁盘上并且从缓存上移除，或者还没有被加载到缓存中，innodb在插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机IO2）因为写入是乱序的,innodb不得不频繁的做页分裂操作,以便为新的行分配空间,页分裂导致移动大量的数据，一次插入最少需要修改三个页以上3）由于频繁的页分裂，页会变得稀疏并被不规则的填充，最终会导致数据会有碎片在把随机值 (uuid和雪花id）载入到聚簇索引(innodb默认的索引类型)以后,有时候会需要做一次OPTIMEIZE TABLE来重建表并优化页的填充，这将又需要一定的时间消耗。结论：使用innodb应该尽可能的按主键的自增顺序插入，并且尽可能使用单调的增加的聚簇键的值来插入新行2.3 使用自增 id 的缺点那么使用自增的id就完全没有坏处了吗？并不是，自增id也会存在以下几点问题：1）别人一旦爬取你的数据库,就可以根据数据库的自增id获取到你的业务增长信息，很容易分析出你的经营情况2）对于高并发的负载，innodb在按主键进行插入的时候会造成明显的锁争用，主键的上界会成为争抢的热点，因为所有的插入都发生在这里，并发插入会导致间隙锁竞争3）Auto_Increment锁机制会造成自增锁的抢夺, 有一定的性能损失 附：Auto_increment的锁争抢问题，如果要改善需要调优innodb_autoinc_lock_mode 的配置

https://www.zhihu.com/question/43500172

http://www.cnblogs.com/tintown/archive/2005/03/02/111459.html

数据库主键设计之思考

在我们的数据库设计中，不可逃避的就是数据库表的主键，可能有很多朋友没有深入思考过，主键的设计对整个数据库的设计影响很大，因此我们不得不要重视起来。

主键的必要性:

有些朋友可能不提倡数据库表必须要主键，但在我的思考中，觉得每个表都应该具有主键，不管是单主键还是双主键，主键的存在就代表着表结构的完整性，表的记录必须得有唯一区分的字段，主键主要是用于其他表的外键关联，本记录的修改与删除，当我们没有主键时，这些操作会变的非常麻烦。

主键的无意义性:

我强调主键不应该具有实际的意义，这可能对于一些朋友来说不太认同，比如订单表吧，会有"订单编号"字段，而这个字段呢在业务实际中本身就是应该具有唯一性，具有唯一标识记录的功能，但我是不推荐采用订单编号字段作为主键的，因为具有实际意义的字段，具有"意义更改"的可能性，比如订单编号在刚开始的时候我们一切顺利，后来客户说"订单可以作废，并重新生成订单，而且订单号要保持原订单号一致”，这样原来的主键就面临危险了。因此，具有唯一性的实际字段也代表可以作为主键。因此，我推荐是新设一个字段专门用为主键，此主键本身在业务逻辑上不体现，不具有实际意义。而这种主键在一定程序增加了复杂度，所以要视实际系统的规模大小而定，对于小项目，以后扩展不会很大的话，也查允许用实际唯一的字段作主键的。

主键的选择

我们现在在思考一下，应该采用什么来作表的主键比较合理，申明一下，主键的设计没有一个定论，各人有各人的方法，哪怕同一个，在不同的项目中，也会采用不同的主键设计原则。

第一: 编号作主键

此方法就是采用实际业务中的唯一字段的"编号"作为主键设计，这在小型的项目中是推荐这样做的，因为这可以使项目比较简单化，但在使用中却可能带来一些麻烦，比如要进行"编号修改"时，可能要涉及到很多相关联的其他表，就象黎叔说的"后果很严重”;还有就是上面提到的"业务要求允许编号重复时”，我们再那么先知，都无法知道业务将会修改成什么?

第二: 自动编号主键

这种方法也是很多朋友在使用的，就是新建一个ID字段，自动增长，非常方便也满足主键的原则，优点是: 数据库自动编号，速度快，而且是增量增长，聚集型主键按顺序存放，对于检索非常有利;数字型的，占用空间小，易排序，在程序中传递也方便;如果通过非系统增加记录 (比如手动录入，或是用其他工具直接在表里插入新记录，或老系统数据导入) 时，非常方便，不用担心主键重复问题。

缺点: 其实缺点也就是来自其优点，就是因为自动增长，在手动要插入指定ID的记录时会显得麻烦，尤其是当系统与其他系统集成时，需要数据导入时，很难保证原系统的ID不发生主键冲突 (前提是老系统也是数字型的) ;如果其他系统主键不是数字型那就麻烦更大了，会导致修改主键数据类型了，这也会导致其他相关表的修改，后果同样很严重;就算其他系统也是数字型的，在导入时，为了区分新老数据，可能想在老数据主键前统一加一个"o”(old)来表示这是老数据，那么自动增长的数字型又面临一个挑战。

第三: Max加一

由于自动编号存在那些问题，所以有些朋友就采用自己生成，同样是数字型的，只是把自动增长去掉了，采用在Insert时，读取Max值后加一，这种方法可以避免自动编号的问题，但也存在一个效率问题，如果记录非常大的话，那么Max()也会影响效率的;更严重的是并发性问题，如果同时有两人读到相同的Max后，加一后插入的ID值会重复，这已经是有经验教训的了。

第四: 自制加一

考虑Max加一的效率后，有人采用自制加一，也就是建一个特别的表，字段为: 表名，当前序列值。这样在往表中插入值时，先从此表中找到相应表的最大值后加一，进行插入，有人可能发现，也可能会存在并发处理，这个并发处理，我们可以采用lock线程的方式来避免，在生成此值的时，先Lock，取到值以后，再unLock出来，这样不会有两人同时生成了。这比Max加一的速度要快多了。但同样存在一个问题: 在与其他系统集成时，脱离了系统中的生成方法后，很麻烦保证自制表中的最大值与导入后的保持一致，而且数字型都存在上面讲到的"o"老数据的导入问题。因此在"自制加一"中可以把主键设为字符型的。字符型的自制加一我倒是蛮推荐的，应该字符型主键可以应付很多我们意想不到的情况。

第五: GUID主键

目前一个比较好的主键是采用GUID，当然我是推荐主键还是字符型的，但值由GUID生成，GUID是可以自动生成，也可以程序生成，而且键值不可能重复，可以解决系统集成问题，几个系统的GUID值导到一起时，也不会发生重复，就算有"o"老数据也可以区分，而且效率很高，在.NET里可以直接使用System.Guid.NewGuid()进行生成，在SQL里也可以使用 NewID()生成。优点是:

同 IDENTITY 列相比，uniqueidentifier 列可以通过 NewID() 函数提前得知新增加的行 ID，为应用程序的后续处理提供了很大方便。

便于数据库移植，其它数据库中并不一定具有 IDENTITY 列，而 Guid 列可以作为字符型列转换到其它数据库中，同时将应用程序中产生的 GUID 值存入数据库，它不会对原有数据带来影响。

便于数据库初始化，如果应用程序要加载一些初始数据， IDENTITY 列的处理方式就比较麻烦，而 uniqueidentifier 列则无需任何处理，直接用 T-SQL 加载即可。

便于对某些对象或常量进行永久标识，如类的 ClassID，对象的实例标识，UDDI 中的联系人、服务接口、tModel标识定义等。

缺点是:

GUID 值较长，不容易记忆和输入，而且这个值是随机、无顺序的

GUID 的值有 16 个字节，与其它那些诸如 4 字节的整数相比要相对大一些。这意味着如果在数据库中使用 uniqueidentifier 键，可能会带来两方面的消极影响: 存储空间增大；索引时间较慢。

我也不是推荐GUID最好，其实在不同的情况，我们都可以采用上面的某一种方式，思考了一些利与弊，也方便大家在进行设计时参考。这些也只是我的一点思考而已，而且可能我知识面限制，会有一些误论在里面，希望大家有什么想法欢迎讨论。

听棠

2005-3-2

MySQL主键设计 今日格言: 让一切回归原点，回归最初的为什么。

本篇讲解 MySQL 的主键问题，从为什么的角度来了解 MySQL 主键相关的知识，并拓展到主键的生成方案问题。再也不怕被问到 MySQL 时只知道 CRUD 了。

一、为什么需要主键 数据记录需具有唯一性(第一范式) 数据需要关联 join 数据库底层索引用于检索数据所需 以下废话连篇，可以直接跳过到下一节。

“信息是用来消除随机不定性的东西” (香农) 。人通过获得、识别自然界和社会的不同信息来区别不同事物，得以认识和改造世界。数据是反映客观事物属性的记录，是信息的具体表现形式。数据经过加工处理之后，就成为信息；而信息需要经过数字化转变成数据才能存储和传输。数据库就是用于存储数据记录的。既已如此，记录便是具有确定性(相对)的信息，其确定性即唯一性。我们得出第一条原因:

1.数据记录需具有唯一性

世界是由客观存在及其关系组成的。数据是数字化和模型化的存在关系。数据除了本身的描述价值外，其价值还在于其相互关联性。为实现关联的准确性，数据需要有对外相互关联的标识。所以体现在数据存储上，主键的第二作用，也是存在的第二因素即:

2.数据需要关联

数据用于描述客观实在的，本身没有意义。只有在根据主观需求组织之后，通过一定方式满足人认识事物的过程才具有了意义。所以数据需要被检索，被组织。则主键第三个作用:

3.数据库底层索引用于检索数据所需

二、为什么主键不宜过长 这个问题的点在长上。那短比长有什么优势？ (嘿嘿嘿，内涵) —— 短不占空间。但这么点磁盘空间相对整个数据量来说微不足道，而且我们一般不怎么用到主键列。那么原因应该在快上，而且和原始数据关系不大。以此自然得出和索引相关，而且和索引读取相关。那么为什么长主键在索引中会影响性能？

上面是 Innodb 的索引数据结构。左边是聚簇索引，通过主键定位数据记录。右边是二级索引，对列数据做索引，通过列数据查找数据主键。如果通过二级索引查询数据，流程如图上所示，先从二级索引树上搜索到主键，然后在聚簇索引上通过主键搜索到数据行。其中二级索引的叶子节点是直接存储的主键值，而不是主键指针。所以如果主键太长，一个二级索引树所能存储的索引记录就会变少，这样在有限的索引缓冲中，需要读取磁盘的次数就会变多，所以性能就会下降。

三、为什么建议使用自增 ID

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

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上，如下图左侧所示。否则由于每次插入主键的值近似于随机，因此每次新记录都要被插到现有索引页的中间某个位置，MySQL 不得不为了将新记录插到合适位置而移动数据，如下图右侧所示，这样就造成了一定的开销。由于此，MySQL 为维护索引可能需要频繁的刷新缓冲，增加了方法磁盘 IO 的次数，而且时常需要对索引结构进行重组织。

四、业务 Key VS 逻辑 Key 业务 Key，即使用具有业务意义的 id 作为 Key，比如使用订单流水号作为订单表的主键 Key。逻辑 Key，即无关业务的 Key，按某种规则生成 Key，如自增 Key。

业务 Key 的优点 Key 具有业务意义，在查询时可以直接作为搜索关键字使用 不需要额外的列和索引空间 可以减少一些 join 操作。 业务 Key 的缺点 当业务发生变化时，有时需要变更主键 涉及多列 Key 时比较难操作 业务 Key 往往比较长，所占空间更大，导致更大的磁盘 IO 在 Key 确定前不能持久化数据，有时我们没有在确定数据 Key 时，就想先添加一条记录，之后再更新业务 Key 设计一个兼具易用和性能的 Key 生成方案比较难 逻辑 Key 的优点 不会因为业务的变动而需要修改 Key 逻辑 操作简单，且易于管理 逻辑 Key 往往更小，性能更优 逻辑 Key 更容易保证唯一性 更易于优化 逻辑 Key 缺点 查询主键列和主键索引需要额外的磁盘空间 在插入数据和更新数据时需要额外的 IO 更多的 join 可能 如果没有唯一性策略限制，容易出现重复的 Key 测试环境和正式环境 Key 不一致，不利于排查问题 Key 的值没有和数据关联，不符合三范式 不能用于搜索关键字 依赖不同数据库系统的具体实现，不利于底层数据库的替换 五、主键生成 一般情况下，我们都使用 MySQL 的自增 ID，来作为表的主键，这样简单，而且从上面讲到的来看，性能也是最好的。但是在分库分表的情况情况下，自增 ID 则不能满足需求。我们可以来看看不同数据库生成 ID 的方式，也看一些分布式 ID 生成方案。利于我们思考甚至实现自己的分布式 ID 生成服务。

数据库的实现 MySQL 自增 MySQL 在内存中维护一个自增计数器，每次访问 auto-increment 计数器的时候， InnoDB 都会加上一个名为AUTO-INC 锁直到该语句结束(注意锁只持有到语句结束,不是事务结束)。AUTO-INC 锁是一个特殊的表级别的锁，用来提升包含 auto_increment 列的并发插入性。

在分布式的情况下，其实可以独立一个服务和数据库来做 id 生成，依旧依赖 MySQL 的表 id 自增能力来为第三方服务统一生成 id。为性能考虑可以不同业务使用不同的表。

Mongodb ObjectId Mongodb 为防止主键冲突，设计了一个 ObjectId 作为主键 id。它由一个 12 字节的十六进制数字组成，其中包含以下几部分:

Time: 时间戳。4 字节。秒级。

Machine: 机器标识。3 字节。一般是机器主机名的散列值，这样就确保了不同主机生成不同的机器 hash 值，确保在分布式中不造成冲突，同一台机器的值相同。

PID: 进程 ID。2 字节。上面的 Machine 是为了确保在不同机器产生的 objectId 不冲突，而 pid 就是为了在同一台机器不同的 mongodb 进程产生的 objectId 不冲突。

INC: 自增计数器。3 字节。前面的九个字节保证了一秒内不同机器不同进程生成的 objectId 不冲突，自增计数器，用来确保在同一秒内产生的 objectId 也不会发现冲突，允许 256 的 3 次方等于 16777216 条记录的唯一性。

Cassandra TimeUUID Cassandra 使用下面规则生成一个唯一的 id: time + MAC + sequence

方案 Zookeeper 自增: 通过 zk 的自增机制实现。 Redis 自增: 通过 Redis 的自增机制实现。 UUID: 使用 UUID 字符串作为 Key。 snowflake 算法: 和 Mongodb 的实现类似，1位符号位 + 41位时间戳 (毫秒级) + 10位数据机器位 + 12位毫秒内的序列。 开源实现 百度 UidGenerator: 基于snowflake算法。 美团 Leaf: 同时实现了基于 MySQL 自增 (优化) 和 snowflake 算法的机制。 推荐系列 列式存储 时间序列数据库(TSDB)初识与选择 十分钟了解 Apache Druid Apache Druid 底层存储设计 Apache Druid 的集群设计与工作流程 MySQL 大表问题和解决

https://juejin.cn/post/6844904132915003399

id

user 表的 pk 是 id order 表 pk 是 id fk 是 user_id

https://v2ex.com/t/709455?p=1