河北互联网公司,关键词怎么优化,腾讯云服务器如何使用,建设局网站查询个人信息聊一个实际问题#xff1a;淘宝的数据库#xff0c;主键是如何设计的#xff1f; 某些错的离谱的答案还在网上年复一年的流传着#xff0c;甚至还成为了所谓的 MySQL 军规。其中#xff0c;一个最明显的错误就是关于MySQL 的主键设计。 大部分人的回答如此自信#xff… 聊一个实际问题淘宝的数据库主键是如何设计的 某些错的离谱的答案还在网上年复一年的流传着甚至还成为了所谓的 MySQL 军规。其中一个最明显的错误就是关于MySQL 的主键设计。 大部分人的回答如此自信用 8 字节的 BIGINT 做主键而不要用 INT 。 错 这样的回答只站在了数据库这一层而没有 从业务的角度 思考主键。主键就是一个自增 ID 吗 13.1 自增ID的问题 自增 ID 做主键简单易懂几乎所有数据库都支持自增类型只是实现上各自有所不同而已。自增 ID 除了简单其他都是缺点总体来看存在以下几方面的问题 1. 可靠性不高 存在自增 ID 回溯的问题这个问题直到最新版本的 MySQL 8.0 才修复。 2. 安全性不高 对外暴露的接口可以非常容易猜测对应的信息。比如 /User/1/ 这样的接口可以非常容易猜测用户 ID 的值为多少总用户数量有多少也可以非常容易地通过接口进行数据的爬取。 3. 性能差 自增 ID 的性能较差需要在数据库服务器端生成。 4. 交互多 业务还需要额外执行一次类似 last_insert_id() 的函数才能知道刚才插入的自增值这需要多一次的 网络交互。在海量并发的系统中多 1 条 SQL 就多一次性能上的开销。 5. 局部唯一性 最重要的一点自增 ID 是局部唯一只在当前数据库实例中唯一而不是全局唯一在任意服务器间都是唯一的。对于目前分布式系统来说这简直就是噩梦。 13.2 业务字段做主键 为了能够唯一地标识一个会员的信息需要为 会员信息表 设置一个主键。那么怎么为这个表设置主键才能达到我们理想的目标呢 这里我们考虑业务字段做主键。 表数据如下 在这个表里哪个字段比较合适呢 选择卡号cardno 会员卡号 cardno 看起来比较合适因为会员卡号不能为空而且有唯一性可以用来 标识一条会员记录。 mysql CREATE TABLE demo .membermaster - ( - cardno CHAR ( 8 ) PRIMARY KEY , -- 会员卡号为主键 - membername TEXT , - memberphone TEXT , - memberpid TEXT , - memberaddress TEXT , - sex TEXT , - birthday DATETIME - ); Query OK, 0 rows affected ( 0.06 sec) 不同的会员卡号对应不同的会员字段“cardno”唯一地标识某一个会员。如果都是这样会员卡号与会员一一对应系统是可以正常运行的。 但实际情况是 会员卡号可能存在重复使用 的情况。比如张三因为工作变动搬离了原来的地址不再到商家的门店消费了 退还了会员卡于是张三就不再是这个商家门店的会员了。但是商家不想让这个会 员卡空着就把卡号是“10000001” 的会员卡发给了王五。 从系统设计的角度看这个变化只是修改了会员信息表中的卡号是 “10000001” 这个会员 信息并不会影响到数据一致性。也就是说修改会员卡号是“10000001” 的会员信息 系统的各个模块都会获取到修改后的会员信息不会出现“ 有的模块获取到修改之前的会员信息有的模块获取到修改后的会员信息而导致系统内部数据不一致” 的情况。因此从 信息系统层面 上看是没问题的。 但是从使用 系统的业务层面 来看就有很大的问题 了会对商家造成影响。 比如我们有一个销售流水表 trans 记录了所有的销售流水明细。 2020 年 12 月 01 日张三在门店购买了一本书消费了 89 元。那么系统中就有了张三买书的流水记录如下所示 接着我们查询一下 2020 年 12 月 01 日的会员销售记录 如果会员卡“10000001”又发给了王五我们会更改会员信息表。导致查询时 这次得到的结果是王五在 2020 年 12 月 01 日买了一本书消费 89 元。显然是错误的结论 千万不能把会员卡号当做主键。 选择会员电话 或 身份证号 会员电话可以做主键吗不行的。在实际操作中手机号也存在 被运营商收回 重新发给别人用的情况。 那身份证号行不行呢好像可以。因为身份证决不会重复身份证号与一个人存在一一对 应的关系。可 问题是身份证号属于 个人隐私 顾客不一定愿意给你。要是强制要求会员必须登记身份证号会把很 多客人赶跑的。其实客户电话也有这个问题这也是我们在设计会员信息表的时候允许身份证号和 电话都为空的原因。 所以建议尽量不要用跟业务有关的字段做主键。毕竟作为项目设计的技术人员我们谁也无法预测 在项目的整个生命周期中哪个业务字段会因为项目的业务需求而有重复或者重用之类的情况出现。 经验 刚开始使用 MySQL 时很多人都很容易犯的错误是喜欢用业务字段做主键想当然地认为了解业务需求但实际情况往往出乎意料而更改主键设置的成本非常高。 13.3 淘宝的主键设计 在淘宝的电商业务中订单服务是一个核心业务。请问 订单表的主键 淘宝是如何设计的呢是自增 ID吗 打开淘宝看一下订单信息 从上图可以发现订单号不是自增 ID 我们详细看下上述 4 个订单号 1550672064762308113 1481195847180308113 1431156171142308113 1431146631521308113 订单号是 19 位的长度且订单的最后 5 位都是一样的都是 08113 。且订单号的前面 14 位部分是单调递增的。 大胆猜测淘宝的订单 ID 设计应该是 订单 ID 时间 去重字段 用户 ID 后 6 位尾号 这样的设计能做到全局唯一且对分布式系统查询及其友好。 13.4 推荐的主键设计 非核心业务 对应表的主键自增 ID 如告警、日志、监控等信息。 核心业务 主键设计至少应该是全局唯一且是单调递增 。全局唯一保证在各系统之间都是唯一的单调 递增是希望插入时不影响数据库性能。 这里推荐最简单的一种主键设计改造UUID。 UUID 的特点 全局唯一占用 36 字节数据无序插入性能差。 缺点: 满足全局唯一 但无法保证单调递增 认识 UUID 为什么 UUID 是全局唯一的 为什么 UUID 占用 36 个字节 为什么 UUID 是无序的 MySQL 数据库的 UUID 组成如下所示 UUID 时间 UUID 版本 16 字节 - 时钟序列 4 字节 - MAC 地址 12 字节 我们以 UUID 值e0ea12d4-6473-11eb-943c-00155dbaa39d举例 为什么 UUID 是全局唯一的 在 UUID 中时间部分占用 60 位存储的类似 TIMESTAMP 的时间戳但表示的是从 1582-10-15 00 00 00.00 到现在的100ns 的计数。可以看到 UUID 存储的时间精度比 TIMESTAMPE 更高时间维度发生重复的概率降低到1/100ns 。 时钟序列是为了避免时钟被回拨导致产生时间重复的可能性。 MAC 地址用于全局唯一。 为什么 UUID 占用 36 个字节 UUID 根据字符串进行存储设计时还带有无用 - 字符串因此总共需要 36 个字节。 为什么 UUID 是随机无序的呢 因为 UUID 的设计中将时间低位放在最前面而这部分的数据是一直在变化的并且是无序。 改造 UUID 若将时间高低位互换则时间就是单调递增的了也就变得单调递增了。 MySQL 8.0 可以更换时间低位和时间高位的存储方式这样UUID 就是有序的 UUID 了。 MySQL 8.0 还解决了 UUID 存在的空间占用的问题除去了 UUID 字符串中无意义的 - 字符串并且将字符串用二进制类型保存这样存储空间降低为了16 字节。 36个符号去掉4个 - 剩下32个符号, 用二进制存储16进制符号需要4比特位, 32 × 4 128 位 128 / 8 16字节 可以通过 MySQL8.0 提供的 uuid_to_bin 函数实现上述功能同样的 MySQL 也提供了 bin_to_uuid 函数进行 转化 (TRUE ) SET uuid UUID(); SELECT uuid ,uuid_to_bin( uuid ),uuid_to_bin( uuid , TRUE ); 通过函数uuid_to_bin(uuid,true)将UUID转化为有序UUID了。全局唯一 单调递增这就是我们想要的主键 4 、有序 UUID 性能测试 16 字节的有序 UUID 相比之前 8 字节的自增 ID 性能和存储空间对比究竟如何呢 我们来做一个测试插入 1 亿条数据每条数据占用 500 字节含有3个二级索引最终的结果如下所示 从上图可以看到插入 1 亿条数据有序 UUID 是最快的而且在实际业务使用中有序 UUID 在 业务端就可以生成 。还可以进一步减少 SQL 的交互次数。 另外虽然有序 UUID 相比自增 ID 多了 8 个字节但实际只增大了 3G 的存储空间还可以接受。 在当今的互联网环境中非常不推荐自增ID作为主键的数据库设计。更推荐类似有序UUID的全局 唯一的实现。 另外在真实的业务系统中主键还可以加入业务和系统属性如用户的尾号机房的信息等。这样的主键设计就更为考验架构师的水平了。 如果不是 MySQL8.0 肿么办 手动赋值字段做主键 比如设计各个分店的会员表的主键因为如果每台机器各自产生的数据需要合并就可能会出现主键 重复的问题。 可以在总部 MySQL 数据库中有一个管理信息表在这个表中添加一个字段专门用来记录当前会员编 号的最大值。 门店在添加会员的时候先到总部 MySQL 数据库中获取这个最大值在这个基础上加 1 然后用这个值 作为新会员的“id” 同时更新总部 MySQL 数据库管理信息表中的当 前会员编号的最大值。 这样一来各个门店添加会员的时候都对同一个总部 MySQL 数据库中的数据表字段进 行操作就解决了各门店添加会员时会员编号冲突的问题。
