主要包括: 数据库命名,设计,字段设计,索引设计与选取,sql开发规范
命名规范
- 所有数据库对象名称必须使用小写字母并用下划线分割
- 临时库表必须以
tmp_
为前缀并以日期为后缀,备份表必须以bak_
为前缀并以日期 (时间戳) 为后缀
基本规范
- 所有表必须使用InnoDB存储引擎,innodb支持事物,行锁,MVCC机制,有重做日志可以快速进行数据库异常崩溃后的灾难恢复。
- 不要强制实现第三范式,大多场景第二范式使用更加方便
- 数据库可表统一使用UTF-8编码
- 所有表和字段都要添加注释
- 经常一起使用的列放到一个表中,避免过度联表查询
- 所有存储相同数据的列名和列类型必须一致(一般作为关联列,如果查询时关联列类型不一致会自动进行数据类型隐式转换,会造成列上的索引失效,导致查询效率降低)
- 禁止在数据库中存储文件,很消耗存储空间,可以将文件存入minio类似的第三方文件服务中。
字段设计规范
- 字段优先选择符合需求的最小的数据类型
- 避免使用TEXT 或 BLOB 类型 ,不能直接创建索引需要创建前缀索引,并且不能设置默认值。
- 尽可能把所有字段设置为不为NULL,因为NULL会占用存储空间,并且NULL具有不确定性,即使两个NULL值比较也不一定相当,还会影响聚合函数avg的运算结果,NULL加任何数都为NULL,影响排序结果。可以选择使用空字符串来代替NULL.
- 不要使用字符串来创建日期字符串占用的空间更大,字符串存储的日期效率比较低(逐个字符进行比对),无法用日期相关的 API 进行计算和比较。
- 金额类型的数据选择decimal类型,防止精度丢失
索引的设计
- 一张表中的索引数量不能超过5个,如果有多个字段需要创建索引可以考虑使用联合索引
- 避免创建全文索引,全文索引创建速度慢,而且对有全文索引的各种数据修改操作也慢
- InnoDB表尽量创建主键,数据的存储的逻辑顺序是按照主键索引的顺序来组织的。
- 不要使用更新频繁的列作为主键,不使用多列主键(相当于联合索引)
- 不要使用 UUID,MD5,HASH,字符串列作为主键( B+树是多路平衡树,如果索引的数据不是有序的,那么就需要在插入时排序,如果数据是整型还好,否则类似于字符串或 UUID 这种又长又难比较的数据,插入或查找的速度肯定比较慢)
- 主键建议使用自增 ID 值
索引的选取
- 经常出现在where条件中的列,频繁被排序,分组,去重的列,在关联查询中经常使用的列,如果符合索引条件的列比较多可以考虑使用联合索引。
- 联合索引的顺序 通常把区分度高,字段长度小,使用频繁的列往前方。
- 避免创建重复索引和冗余索引
- 对于频繁查询的索引优先选择索引覆盖索引(包含所有查询列的索引) 可以不用回表
SQL开发规范
- 尽量不要在数据库做运算,复杂运算需要在业务应用中完成
- 可通过explain 命令分析sql执行过程查看索索引执行情况对sql进行优化调整
- 可以查看mysql的慢查询日志对查询速度慢的sql进行优化
- 在使用索引的时候应避免索引失效 ,例如模糊查询,对索引项进行运算,函数操作,不遵守最左匹配原则等。
- 禁止使用select* 会消耗CPU和网络带宽,并且表结构变动后需要修改实体,无法使用mysql优化器。
- 避免使用子查询,使用连接查询代替,子查询结果集会存储到临时表中,临时表无法使用索引,若子查询数据量大还可能出现慢查询现象。
- 避免join关联太多表,因为mysql纯在缓存机制,会分配关联缓存,如果缓存过多会导致内层不足,服务器内存溢出的现象。
- 在合并结果重复量不大时避免使用union(去重),选择使用iunion all(不去重) ,前者会先将合并结果放到临时表然后再去重
- 拆分大sql
- 大 SQL 逻辑上比较复杂,需要占用大量 CPU 进行计算的 SQL
- MySQL 中,一个 SQL 只能使用一个 CPU 进行计算
- SQL 拆分后可以通过并行执行来提高处理效率