MySQL 数据库铁律(小结)

数据库  /  管理员 发布于 8年前   266

好的数据库规范有助于减少软件实现的复杂度，降低沟通成本，本铁律主要涵盖了建库建表、建索引、写 SQL、ORM 映射等方面的处理约定。

1.建库铁律

-	铁律	Level	备注
字符集	使用 utf-8。如果存储的是表情则选用 utf8mb4 进行存储。	强制
排序规则	使用 utf8_general_ci	强制

2.建表铁律

-	铁律	Level	备注
注释	一定要有字段注释。	强制
编码	使用 utf-8。如果存储的是表情则选用 utf8mb4 进行存储。	强制
是否概念的字段	必须用 is_xx 命名，数据类型是 unsigned tinyint（1是0否）例如 is_deleted（1删除0未删除）。	强制	任何字段如果非负数必须unsigned
表名、字段名	只能使用小写字母、下划线或者数字；禁止以下划线或者数字开头；禁止两个下划线之间只出现数字；禁用保留字；表名禁止使用复数名词。	强制
库名、表名的命名	库名尽量与应用名称一致，表名最好用 业务名称_表的作用 命名。	强制
索引命名	主键索引用 pk_字段名；唯一索引用 uk_字段名；普通索引用 idx_字段名。	强制	pk_ 即 primary key；uk_即 unique key；idx_即 index
小数类型	数据类型是 decimal，禁止使用 float 和 double，float 和 double 存在精度损失，如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数并分开存储。	强制
varchar类型	varchar是可变长字符串，不预先分配存储空间，长度不要超过5000个字符，如果长度大于5000应用text（独立出一张表来，用主键来对应，避免影响其他字段的索引效率）。	强制
表名必备三字段	id（数据类型是 unsigned bigint，单表递增，步长为1），gmt_create、gmt_modified（主动创建时间、被动更新时间，数据类型都是 datetime）。	强制
字段冗余	字段允许适当冗余，但必须考虑数据一致，冗余字段应具备1）不频繁修改；2）不是varchar超长字段，更不能是text字段。	推荐
分库分表	单表行数超过500万行或者单表容量超过2GB时，才推荐分库分表。	推荐

设置合适的字符存储长度，不但可以节约数据库表空间和索引存储，更重要的是能够提升检索速度。

3.建索引铁律

-	铁律	Level	备注
唯一索引	业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。虽然唯一索引影响了 insert 速度，这个损耗可以忽略，但是明显提高了查询速度；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。	强制
join	超过三个表禁止 join，需要 join 的字段，数据类型必须一致；当多表关联查询时，保证被关联的字段需要有索引；即使双表 join 也要注意表索引、SQL 性能。	强制
varchar字段上建立索引	必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。索引长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90% 以上，可以使用 count(distinct left(列名, 索引长度))/count(*) 的区分度来确定。	强制
页面搜索禁止模糊	页面搜索禁止左模糊或者全模糊，如果有需要请走搜索引擎来解决。禁止原因：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。	强制
order by	如果有 order by 的场景，请注意索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。正例：where a=? and b=? order by c; 索引应建为 a_b_c；反例：索引中有范围查找，那么索引有序性无法利用，如 where a>10 order by b; 索引 a_b 无法排序。	推荐

4.写SQL铁律

-	铁律	Level	备注
count(*)	不要使用 count(列名) 或 count(常量) 来替代 count(*)，count(*) 是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。count(*) 会统计值为 NULL 的行，而 count(列名) 不会统计此列为 NULL 的行。	强制
count(distinct col)	计算该列除 NULL 外的不重复行数。注意，count(distinct col1, col2)，如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。	强制
sum(col)	当一列的值全为 NULL 时，count(col) 的返回结果为 0，但 sum(col) 的返回结果为 NULL，因此使用 sum() 时需要注意 NPE 问题。可用如下方式避免 NPE 问题：select if(isnull(sum(g)), 0, sum(g)) from table;	强制
isnull	使用 isnull() 来判断是否为 NULL 值。NULL 与任何值的比较都为 NULL。	强制
分页查询逻辑	若 count 为 0 应直接返回，避免执行后面的分页语句。	强制
外键与级联	禁止使用外键与级联，一切外键概念必须在应用层解决。原因：外键与级联不适合分布式、高并发集群，级联更新是强阻塞，存在数据库更新风暴的风险，外键影响数据库的插入速度。	强制
存储过程	禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。	强制
数据订正	数据订正（特别是删除、修改记录操作）时要先 select，避免出现误删除，确认无误后才能执行更新语句。	强制
in	in 操作能避免就避免，如果实在避免不了，in 后面的集合元素数量要控制在 1000 个以内。	推荐
truncate table	禁止使用 truncate table，truncate table 比 delete 速度快，且使用的系统和日志资源少，但是 truncate 无事务且不触发 trigger，有可能造成事故，故不要在开发代码中使用此语句。	参考

5.ORM映射铁律

-	铁律	Level	备注
表查询	禁止使用 * 作为查询的字段列表，需要哪些字段必须明确。	强制
POJO	POJO 类的布尔属性不能加 is，而数据库字段必须加 is，要求在 resultMap 中进行字段与属性之间的映射。	强制
返回参数	禁止用 resultClass 作为返回参数，即使所有类属性名与数据库字段一一对应，也需要定义；反过来，每一个表也必然有一个属性与之对应。原因：配置映射关系，使字段与 DO 类结耦，方便维护。	强制
返回参数	禁止直接使用 HashMap、HashTable 作为查询结果集的输出。原因：属性值的类型不可控。	强制
sql.xml 配置参数	sql.xml 配置参数使用 #{}, #param#，不要使用 ${}，${} 容易出现SQL注入。	强制
queryForList	禁止使用 Mybatis 自带的 queryForList(String statementName, int start, int size)。原因：其实现方式是在数据库取到 statementName 对应的 SQL 语句的所有记录，再通过 subList 取 start, size 的子集合。	强制
更新时间	更新数据库表记录时，必须同时更新记录对应的修改时间。	强制
更新数据库表记录	不要写一个大而全的数据更新接口（传入为 POJO 类）。执行 SQL 时，不要更新无改动的字段，原因：容易出错、效率低、增加 binlog 存储。	推荐
@Transactional	@Transactional 事务不要滥用。事务会影响数据库的 QPS。另外，使用事务的地方需要考虑各方面的回滚方案，包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等。	参考
Mybatis 动态sql标签	< isEqual> 中的 compareValue 是与属性值对比的常量，一般是数字，表示相等时执行相应的 SQL 语句；< isNotEmpty> 表示不为空且不为 null 时执行；< isNotNull> 表示不为 null 时执行。	参考

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。