查看原文
其他

收藏!最新《MySQL数据库开发设计规范》

破产码农 InsideMySQL 2022-10-13

        

破产码农

IT圈最会讲故事的网红 · 南山彭于晏
想必《MySQL数据库开发设计规范》是每个 DBA 和业务开发同学想要的一个手册,用于指导基于 MySQL 数据库线上业务的落地。
然而,由于业务“千人千面”,不同业务对于 MySQL 数据库的要求是不同的。
同时,MySQL 数据库的版本也在不断演进。
所以,一个真正好的规范应该“普世”的,通用的,而不是应该是强制的,“抢戏”的。
不好意思,放错图了。真正好的规范应该是这样的:
之前业界也有所谓的 MySQL 军规或开发设计规范,但站在姜老师的角度看,体系性还是差了不少。
正好最近在梳理 MySQL 的相关内容,所以趁此机会,给大家梳理下姜老师心目中的《MySQL数据库孤独九剑》。
哦,不,是《MySQL数据库开发设计规范》。
本规范由四部分组成:表结构设计、索引设计、高可用设计、分布式架构设计。
四大规范,每个规范10条准则。
表结构设计规范
  1. INT 类型不使用 unsigned 无符号属性,容易引入额外的计算问题。

  2. 自增用 8 字节 BIG INT,不要使用 4 字节 INT,且自增在 MySQL 8.0 版本前有回溯问题,请考虑是否业务有影响。

  3. 字符集使用 UTF8MB4 字符编码,不推荐 GBK、UTF-8 等其他字符集。

  4. 日期类型用 DATETIME 类型,需要精确到毫秒用 DATETIME(6),不要使用 INT、TIMESTAMP。

  5. 类型 JSON 可用于存储非结构化数据,典型场景为用户标签,不要将 JSON 用于频繁更新的字段场景。

  6. 每张表一定要有一个主键,这样至少满足一范式的要求,核心业务表用全局唯一字段(雪花算法、有序UUID)做主键,不要使用自增做主键。

  7. 对于日志类的流水表、报警表、日志表,可以使用压缩设计,提升存储效率。MySQL 5.7 版本开始推荐使用透明页压缩,不要使用传统的 KEY_BLOCK_SIZE 的页压缩。

  8. 类别设计,用 ENUM+CHECK 约束,不要使用 INT 类型的设计。

  9. 敏感字段需加密,如账户密码、信用卡号等存储使用:动态盐 + 非固定加密算法(MD5/AES256等) + 多轮加密,不要简单使用 MD5 算法加密,容易被暴力破解。

  10. MySQL 可以通过 KV 的方式访问表中的数据,若业务只是简单的 SET、GET 请求,可考虑将其转化为 Memcached 的 KV 访问方式,减少 SQL 解析的开销,性能可以有至少 50% 的提升。

索引设计规范
  1. 不要陷入设置单表行数、列数限制的固有印象,其他关系型数据库没有行数、列数限制,MySQL 也没有,大表的缺点不是性能,而是后续的 DDL 管理问题,随着 MySQL 8.0 快速加列功能的上线,大表 DDL 问题基本已解决。

  2. MySQL 是索引组织表,表中的数据以 B+ 树索引结构,根据主键逻辑排序,由于 B+ 树索引的特点是树的高度为 3~4 层,所以从数十亿的记录中,通过主键查询一条记录只需要 3、4 次 I/O,当前到 SSD 存储设备设置每秒至少能完成 10000 次的 I/O 查询,不要担心通过索引查询一条或几条记录的性能,每秒百万次查询并不难。

  3. MySQL 是索引组织表,二级索引只存储(键值、主键值),因此需要再通过一次主键索引查询得到记录,这种方式成为回表。在核心业务中,使用索引覆盖技术,提升索引查询性能,对于回表记录数比较大的场景,甚至可以有 10 倍的性能提升;

  4. 对类似 WHERE a = ? ORDER BY b 这样的查询,一定要创建(a、b)组合索引,这样可以避免一次额外排序,提升查询性能。

  5. MySQL 优化器是 CBO(Cost-based Optimizer),所有查询基于成本而不是规则,若发现 SQL 执行计划发生变化,不要怀疑 MySQL 出错,请先分析数据特点、索引创建是否合理,是否可以通过直方图校准数据

  6. MySQL JOIN 支持 NLJ(Nested Loop Join)和 NHJ(Nested Hash Join)两种方式。对于 OLTP 业务,放心大胆使用 JOIN,但一定要做好索引的设计和索引覆盖的考虑(不考虑分布式数据库场景);对于 OLAP 业务,MySQL 8.0 版本开始,支持 Hash Join,对于大数据量的关联,性能提升非常多,可以在不超过 10T 的数仓场景中考虑使用,超过 10T数据量,请一定使用大数据产品,如 Hive、Spark、麒麟等产品。

  7. MySQL 5.7 版本开始子查询优化已经做得不错,但是编写的子查询不能是关联子查询,上线前一定需要确认,若发现关联子查询,请改写子查询为 JOIN 或其他方式。

  8. 不要因为数据量大,使用分区表,MySQL 是索引组织表,数据量再大,定位记录也只需要3、4 次 I/O。可以考虑分区表唯一的应用场景是:需要定期清理历史流水类数据,但如果业务可以按月、按天做分表,那么当前 MySQL 8.0 版本,分区表也不推荐使用。

  9. 业务上线或新版本发布前,DBA 一定要进行所有 SQL Review,确保 SQL 走索引,否则不予上线,或由业务以邮件等正式方式,通知 DBA 该 SQL 不会引起线上事故,业务方承担后续责任。

  10. DBA 每天要对数据库进行巡检,及早发现慢查询或潜在数据库风险,将任何潜在问题尽早抛出,否则后续自己承担相关责任。


高可用设计规范



  1. MySQL 高可用的基石是利用二进制日志的复制技术,核心业务一定要使用无损半同步方式,但凡不使用无损半同步的高可用架构,请业务方业务以邮件等正式方式回复,数据丢失等后续问题自己承担相关责任。

  2. MySQL 5.7 版本开始,一定要使用基于 WRITESET 的从机回放,避免主从延迟。

  3. 当前 MySQL 发生主从延迟的可能性主要是存在大事务,比如定期计算收益等操作,这类大事务,一定要通知业务方将大事务拆成小事务,否则不予上线;若要上线,请业务方以邮件等方式回复,自己承担后续主从延迟带来的后续一系列问题。

  4. MySQL 8.0 版本开始推荐金融业务使用 MGR(MySQL Group Replication),通过 Paxos 协议保证数据一致性,并自己完成选主的逻辑,也可以使用多主模式,数据不冲突的情况下,可以大幅提升写入性能。

  5. 对于核心业务,必须遵循互不信任原则,数据一致性不单单依赖 MySQL 复制本身,DBA 这里需要通过逻辑的方式,对主从数据进行核对,业务这里也需要一套业务层的逻辑进行“对账”。

  6. 核心业务,务必使用一地三中心,两地三中心的跨机房复制架构,这样发生机房级故障,可以切换到另一个机房,保证业务可用性。

  7. 同城容灾架构一定要评估切换到另一个机房后业务访问的性能,多次跨机房访问 DB,虽然每次只多了 2~3ms,但也存在业务雪崩问题,推荐 DB 切换机房,上层业务跟着一起联动切换。

  8. 对于有跨城容灾需求的业务,可以考虑使用三地五中心架构,但是由于 30ms 延迟,业务需要进行评估,对于核心业务,务必使用业务层的跨城机制,将数据层的多次网络耗时合并为一次,这样能大大提升业务的性能。

  9. 业界的 MHA、Ochestrator 等高可用套件都是基于 ssh 访问 MySQL,稳定性、安全性不高,不推荐大厂使用;自己开发一个数据库管理平台,通过 agent 的模式管理高可用和 MySQL 数据库的日常操作更为安全、有效。

  10. 一定做好数据备份架构的设计,全备 + 增量备份 + 延迟备机(可选),做到可以基于任何一点恢复和回滚,同时,遵循互不信任原则,备份文件一定要进行检查,确保需要时一定能够进行恢复。


分布式架构设计规范


  1. 分布式数据库的本质就是根据某几个列的规则,将数据水平打散,存在不同的实例中。数据拆分的列称为分区键(Shard Key),分区键一定是业务大部分访问(超过 80%)的表都会使用的列。若选不出合适的分区键,那就一定不要进行分布式数据库架构的设计;互联网业务绝大部分分区键的选择是用户维度。

  2. 分区算法绝大部分场景使用 Hash算法,这样数据的存储和访问可以平均到下面多个实例,真正的做到可扩展性,Range 算法通常无法解决热点问题,会是灾难,但 Range 算法可以在单实例中使用,作为二级拆分数据的规则。

  3. 分布式数据库分片时,一开始就设计为不少于 1000 个分片的规则,不用担心分片过多的问题,管理 1 个分片和 1000 个分片的成本是一样的,但为后续的扩容做好了充足的准备。

  4. 分布式数据库扩缩容就是通过部分过滤的复制技术,按库或按表进行数据同步,分库分表设计推荐库名、表都不同,做到全局唯一,方便后续拆分。

  5. 分布式数据库索引设计中,非分区键的唯一索引一定带入分区键信息,这样业务查询时可以直接定位到数据所在分片,提升查询效率。

  6. 分布式数据库索引设计中,数据库层的唯一约束只在单个实例中保证,若要保证全局唯一,一定要使用全局唯一的索引设计。

  7. 直接使用 JOIN 请确认一定可以单元化在一个分片中完成,如果涉及跨分片的 JOIN,请通知业务修改成多条 SQL 的访问方式,只访问指定分片而不是所有分片。

  8. 分布式数据库可以进行业务层的分库分表访问,和通过数据库中间件的访问,对于业务耗时敏感的业务,推荐业务层直接根据路由规则访问数据,否则使用数据库中间件,简单易用。

  9. 对于耗时敏感的核心业务,推荐使用最终一致的业务层柔性事务,数据库层的 2PC 分布式事务耗时较大,性能较为一般,但是 2PC 使用简单,能满足大部分业务的使用。

  10. 一定要利用好分布式数据库架构的特点,设计多活架构,每个机房都可以有写入流量,提升资源使用率和业务连续性,请 DBA 和业务方一起做好全链路的架构设计。


总结

上述是姜老师结合近20年MySQL数据库一线实战与内核经验的总结。

纵古论今,想必这是最为全面和最好快速上手的《MySQL数据库开发设计规范》。

但其中为什么要执行这样的规范,以及如果基于这些规范进行设计,如跨机房容灾设计、条带化设计、分片设计、多活机房设计,这些内容都在姜老师最新的《姜承尧的MySQL实战宝典》课程中。

不要犹豫,赶紧扫描下方二维码,这是目前业界最好的 MySQL 架构实战课程。

搞懂一线 MySQL 实战干货,年薪百万不是梦。

加油,有梦想的码农们,respect~~~


BTW,本规范已经上传到github,后续会持续更新哦。地址:

https://github.com/jdaaaaaavid/mysql_rule/


直播预告


每周五、六,不定期直播,分享技术干货


IMG群是码农的交流社区,IMG微信群交流内容包括但不限于技术、经济、军事、八卦等话题。欢迎有态度的码农们加入IMG大家庭。
IMG目前有少林群、武当群、峨眉群、华山群、M悦会(高端VIP群)
仅限码农入群,猎头或其他行业勿加,入群请加姜老师个人微信 82946772,并备注:码农入IMG群
-----------------------
公众号:InsideMySQL
视频号:破产码农
抖音号:破产码农
B站号:姜老师带你飞
长按下图二维码关注,将感受到一个有趣的灵魂,每篇文章都会有新惊喜。


          

往期推荐


成为最好的自己,等风来~~~


这么哇塞的 MySQL 功能,你确定不用么?


作为新生代农民工,码农30岁存款应该有多少?


关于 O_DIRECT 的正确答案!


PostgreSQL 不支持的 O_DIRECT,MySQL 和 Oracle 都有


您可能也对以下帖子感兴趣

文章有问题?点此查看未经处理的缓存