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 和业务方一起做好全链路的架构设计。