数据库设计-MySQL设计小册

前言

最近回顾了下MySQL相关的知识，比如索引、几大日志、事务、MVCC、SQL执行流程、Buffer Pool等等。理论知识看了一大堆，自然还是需要实践的，第一个反应就是数据库设计规范。项目开发中，数据库设计自然是重要的一环，但是目前大多都忽略了成了一个不那么重要的环节，但往往是设计阶段留下的坑，开发阶段来填。没有统一的规范，大多数开发人员都是按照之前的数据库设计进行惯性开发，不犯错就是最大的正确。我搜了下相关文章，发现还是有不少好文章，说的很清楚，让我一瞬间放下再写一篇文章的念头，但是我发现这些文章普遍阅读量不高，那就让我的这篇文章作为引子，给大家学习数据库设计规范提供一个好的平台。

表设计

重点规范

【强制】表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint （1 表示是，0 表示否）。
:::info
注意：POJO 类中的任何布尔类型的变量，都不要加 is 前缀，所以，需要在设置从 is_xxx 到 Xxx 的映射关系。数据库表示是与否的值，使用 tinyint 类型，坚持 is_xxx 的命名方式是为了明确其取值含 义与取值范围。
正例：表达逻辑删除的字段名 is_deleted，1 表示删除，0 表示未删除。
补充：切记建表字段长度不要选1，不然mybatis-plus会自动转换成布尔值
:::
【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只 出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。
:::info
说明：MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、表名、 字段名，都不允许出现任何大写字母，避免节外生枝。
正例：aliyun_admin，rdc_config，level3_name
反例：AliyunAdmin，rdcConfig，level_3_name
:::
【强制】表必备三字段：id, create_time, update_time。
:::info
说明：其中 id 必为主键，类型为 bigint unsigned、单表时自增、步长为 1。create_time, update_time 的类型均为 datetime 类型，前者现在时表示主动式创建，后者过去分词表示被动式更新。
:::
【推荐】字段允许适当冗余，以提高查询性能，但必须考虑数据一致。冗余字段应遵循：
:::info
不是频繁修改的字段。
不是唯一索引的字段。
不是 varchar 超长字段，更不能是 text 字段。
正例：各业务线经常冗余存储商品名称，避免查询时需要调用 IC 服务获取。
:::

规范汇总

1. 表字段、索引名、表名等都要见名知意，选择合适的字段类型—最基本的一步了，不要省事让别人看不懂。字段类型根据业务来定，允许范围内越精简越好
2. 主键推荐自增，选择bigint即可，自增主键性能很好，非分库分表设计首选
3. 除了上面的固定三字段，一般会有逻辑删除字段is_deleted，创建人修改人。
4. 业务允许的情况下尽量别用null做默认值–坏处一大把，问题是大家都懒得设置，哈哈
5. 单表字段不宜过多，做好冷热字段分离，比如列表就属于热数据，具体数据需要点进去详情页看，这些字段就属于冷数据，这两部分就可以分开建表，用一个base_id关联
6. 时间字段推荐使用datetime，范围大。timestamp就到2038年…

简要总结

我个人在设计表的时候是推荐尽量将表设计成独立的，可以单表查询，为此可以增加冗余字段，表与表之间通过字段关联。凡是写了很冗长的SQL，说明要么是业务实在是复杂，要么就是当初表设计考虑不足，或者压根是叠shi山，来一坨垒一坨。上面的重要规范是摘抄的阿里开发手册的一些我觉得非常重要的部分，全文我放在了好文推荐一栏，值得大家一看。
MySQL本身不易扩展，并且相对娇弱，所以设计思路一定要把复杂的动作放在应用程序去处理，避免MySQL做过重的逻辑操作，做好存储和简单的查询即可，尽量不要让MySQL成为性能瓶颈。应用程序可以从以下三个方面给MySQL减负：

1. 减少函数的使用，例如数据转换之类的交由应用程序处理，同时也避免了索引可能不生效的情况。
2. 外键肯定不能用了，要增加冗余字段，在应用程序侧去做主动关联，例如主表和扩展表通过一个base_id字段去关联
3. 拆分大SQL，应用程序来做数据关联，应用侧缓存、多线程用上一般不会比MySQL慢

索引设计

重点规范

1. 索引列建议
   1. 出现在 SELECT 、 UPDATE 、 DELETE 语句的 WHERE 从句中的列
   2. 包含在 ORDER BY 、 GROUP BY 、 DISTINCT 中的字段
   3. 多表 JOIN 的关联列

注意： 并不要将符合 1 和 2 中的字段的列都建立一个索引，通常将 1 、 2 中的字段建立联合索引效果更好

2. 如何选择联合索引列的顺序
   1. 区分度最高的放在联合索引的最左侧（区分度 = 列中不同值的数量 / 列的总行数）；
   2. 尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大， IO 性能也就越好）；
   3. 使用最频繁的列放到联合索引的左侧（这样可较少的建立一些索引）。

规范汇总

1. 索引不要过多，5个左右即可–索引也是数据，并且增删改还会重建索引，影响数据库处理速度
2. 优先建联合索引—覆盖索引、最左匹配多舒服，注意区分度高的放在左边

简要总结

索引这个我之前写从零开始的SQL修炼手册那块已经说的很清楚了。对于查询的提升是非常大的，特别是覆盖索引，优化救星。
建索引相当于必修课吧，对我们理解MySQL也是很有用的，相关的索引结构、优化特性、设计思路都很有参考价值。上一篇大数据查询接口我在构想解法的时候，第一时间就想到了类似索引这种目录检索的方法，包括ES也是相似的解法。所以了解索引，也是开阔我们的技术视野。

好文推荐

重点来了，以下几篇文章写的都太好了，理论这块还得多看看大佬们。还是老样子，文章提供链接，会配上我的读后感。
Java开发手册-嵩山版
阿里及社区一众大佬们合力写的规范，包含很多维度的开发建议，绝对是Java的必读手册。数据库是其中一部分，写的很好，建议都很中肯，但是限于篇幅限制吧，还是有很多地方没有涉及，适合有一点数据库基础的读者们观看。所有建议均有正例反例，绝了，朋友们，上车了，本次列车是宝宝巴士。
MySQL数据库设计开发规范（汇总篇）
这一篇属实太全了，本来我想写个汇总，结果一看这篇直接给我干碎，面面俱到，给我整不会了。初学者一定要看，绝对是养成好习惯的基石，配合上面阿里小册，观感更佳。
archer-MySQL数据库设计规范
推荐这个主要是连带着这个GitHub项目比较有意思，规范写的还不错。项目几年不维护了，不过是开源项目，我没用过，不过看着挺有意思。