背景
想象一下自己是一名伐木工人，手里有林场里最好的斧子，因此你是工作效率最高的。突然有一天场里来了个推销的，他把一种新的砍树工具——链锯——给夸到了天上去。你也买了一把，不过你不懂得怎么用。你估摸着按照自己原来擅长的砍树方法，把链锯大力地挥向树干……
MySQL 这个工具也是一样，设计规范就是的一个很好的工具说明。即统一了命名风格，又可以让新人快速上手。
本文的主要内容可以在网上找到类似的版本，但是在一些细节点又略微不同。基于多年 MySQL 使用经验，基于应用与 MySQL 的通盘考虑（视 MySQL 为低配版本的：Bigtable + KV），才有了这些细节上的调整。
命名
避免使用 MySQL 关键词 作为 db / table / field / index 名称
- DB
- 使用项目名作为前缀，“_db” 作为后缀；分库添加后缀8位宽度的数字，数字从0开始
- 风格：由下划线分割的小写英文字母组成
- DB 名称总长度小于 42 个字符
- Table
- “_db” 作为后缀；分表添加后缀8位宽度的数字，数字从0开始
- 风格：由下划线分割的小写英文字母组成
- 表名称总长度小于 48 个字符
- Field
- 主键统一定义为：
idBIGINT UNSIGNED NOT NULL - 指向其他表主键的字段以 “_id” 后缀结尾
- 风格：由下划线分割的小写英文字母组成
- Index
- 使用 “idx_” 作为前缀；索引字段名字、顺序组合为名称
- 风格：由下划线分割的小写英文字母组成
- Comment
- 纯英文单词注释所有字段
DB
- 使用 Innodb 存储引擎
Innodb 支持事务，支持行级锁，更好的恢复性，高并发下性能更好
- 使用 utf8mb4_unicode_ci 编码
兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效
Table
- 使用 utf8mb4_unicode_ci 编码
- 每张表必须显式定义主键
- 数据的存储顺序和主键的顺序是相同的
- 不要使用更新频繁的列作为主键，不要使用 UUID、MD5、HASH、字符串等无法保证数据的顺序增长的字段作为主键
- 尽量控制单表数据量的大小，建议控制在 1000万 以内
- 该量级数据量查询性能较好
- 可以用历史数据归档，分库分表等手段来控制单表数据量
- 宽表尽量拆分为索引表和内容表以提高查询性能
- MySQL 限制每个表最多存储 4096 列，并且每一行数据的大小不能超过 65535 字节 减少磁盘 IO，保证热数据的内存缓存命中率
- 表越宽，装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的 IO，更有效的利用缓存，避免读入无用的冷数据
- 谨慎使用 JOIN
- 应用层缓存效率更高，可以在多种查询场景复用缓存
- 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展
- 查询效率提升。使用 ID 查询，可以让 MySQL 按照主键索引顺序查询，相比关联要更稳定高效
- 谨慎使用 MySQL 分区表
分区表在物理上表现为多个文件，在逻辑上表现为一个表 谨慎选择分区键，跨分区查询效率可能更低 建议采用物理分表的方式管理大数据
- 不要使用外键
- MySQL 外键实现比较简单粗糙，性能不好
- MySQL 作为后端存储，不在 MySQL 上放置任何计算逻辑
- 如果依赖于在 MySQL 服务器上运行的计算逻辑，进行数据库/表分片将非常困难
Field
- 优先选择符合存储需要的最小的数据类型
列的字段越大，索引时所需要的空间越大，磁盘单页存储的索引节点数越少，遍历时 IO 次数就越多， 索引性能也就越差
方法：
1）将字符串转换成数字类型存储，如：将IP地址转换成整形数据（inet_aton / inet_ntoa）
2）对于非负型的数据（如自增ID、整型IP）来说，要优先使用无符号整型来存储
- 存储相同数据的列名和列类型必须一致
如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低
- 尽可能把所有列定义为 NOT NULL
- NULL 占用额外的空间来保存
- NULL 需要特殊处理，可能会导致应用程序异常
- NULL MySQL 索引统计和值比较更复杂
- 避免使用 ENUM 类型
- 修改 ENUM 值需要使用 ALTER 语句
- ENUM 类型的 ORDER BY 操作效率低，需要额外操作
- 禁止使用数值作为 ENUM 的枚举值
- 禁止在数据库中存储长文本、图片，文件等大数据
MySQL 内存临时表不支持 TEXT、BLOB 大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行
而且对于这种数据，MySQL 还是要进行二次查询，会使 SQL 性能变得很差，但是不是说一定不能使用这样的数据类型
- 禁止建立预留字段
- 预留字段的命名很难做到见名识义
- 预留字段无法确认存储的数据类型，所以无法选择合适的类型
- 对预留字段类型的修改，会对表进行锁定
Index
- 限制每张表上的索引数量，建议单张表索引不超过5个
MySQL 优化器优化查询时，会根据统计信息，对候选索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能
Stored Programs
- 禁止使用 mysql 视图，存储过程，触发器，自定义函数
Queries
- 禁止直连生产环境，手工删除和修改生产数据
- 禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询
可减少表结构变更对应用程序的影响
- 禁止使用不含字段列表的INSERT语句
正确：INSERT INTO tbl(c1,c2,c3) VALUES (a,b,c);
错误：INSERT INTO VALUES (a,b,c);
- WHERE从句中禁止对列进行函数转换和计算
对列进行函数转换或计算时会导致无法使用索引。
正确：WHERE create_time >= 20190101 AND create_time < 20190102
错误：WHERE DATE(create_time)=20190101
- 不会有重复值时使用 UNION ALL 而不是 UNION
UNION 将结果集的所有数据放到临时表后再去重
UNION ALL 不会再对结果集进行去重
参考链接：
https://www.cnblogs.com/huchong/p/10219318.html
本文作者 ： cyningsun
本文地址 ： https://www.cyningsun.com/06-06-2021/mysql-design-guide.html
版权声明 ：本博客所有文章除特别声明外，均采用 CC BY-NC-ND 3.0 CN 许可协议。转载请注明出处！
