前言
如果没有统一的约定,那么必然会带来许多额外的工作。例如:JDBC规范。如果没有这个规范,每个数据库厂商都各自为政,开发自己的一套与数据库交互的驱动,那可想而知,对于开发者而言无疑会增加负担和工作。这里,我们来聊一聊,编码规范中的MySQL规范。
MySQL规约
建表规约
表或字段命名
- 必须使用小写字母或数字。
- 禁止以数字开头。
- 禁止两个下划线之间只有数字。
- 不使用重复名词。
即使以逗号分隔的,有集合意义的字段也不要这样取名。这类字段不应太多。同时如果存储的值太大,应当作为大字段对待。 - 禁止使用保留关键字。
避免在某些情况下发生冲突。 - 是否概念字段,应使用is_xxx命名。
这个需要注意:在做ORM映射时,字段不要使用isXxx,而应当把is干掉,避免某些框架做Bean解析造成错误。而java类型,可以用boolean.
数据类型
类型含义 | 类型 | 备注 |
---|---|---|
小数 | decimal | 避免计算上的错误(小数和整数是单独存储的) |
货币 | bigint | 用最小单位-分,避免小数运算 |
字符型 | char | 值域长度几乎相同(不够长度,自动填空格) |
字符型 | varchar | 需要用1-2个字节来记录长度,最长不要超过5000.与char的区别 |
表中必要字段
id主键
create_time创建时间
udpate_time更新时间
推荐规约
- 表名:业务名称_表的作用
- 库名应当与应用名字保持一致
- 修改字段含义时,应及时修改该字段的注释
- 字段允许冗余,以提高查询性能,但数据类型必须一致。
注意,如果单表数据量太大了,因为此时查询效率更多的受数据量的影响。数据量大的表,可以考虑只做单表查询,再把其关联字段通过索引来查询其他表,如果频繁查询的字段可以考虑缓存。 - 单表超过500万行,或容量超过2GB,可以考虑分库分表。
索引规约
索引的特点:持久性(占用存储空间)、有序性。
索引(Index)是帮助数据库高效获取数据的数据结构
索引分类
按是否存储数据分:
- 聚簇索引:叶子节点存储的是数据。
- 非聚簇索引:叶子节点存储的是主键。
按是否带有唯一约束:
- 唯一索引
- 普通索引
主键是唯一标识一个实体的一个属性或属性集。
数据库会默认给主键子弹建立一个比较特殊的索引,既在叶子节点存储数据,也会带有一个唯一约束。所以主键索引既是聚簇索引,也是唯一索引。通常一个表只有一个聚簇索引,因为数据只存储一份。
索引命名规约
主键索引名:pk_表名_字段名
唯一索引名:uk_表名_字段名
普通索引名:idx表名_字段名
创建规约
- 有唯一特性的字段应当设置唯一约束,基于此,如果该字段还没有唯一约束,应当建立唯一索引。
- 在varchar字段上建立索引时,必须指定索引长度。
说明:索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为20的索引,区分度会高达90%以上,可以使用count(distinct left(列名, 索引长度))/count(*)的区分度来确定。 - 创建组合索引时,区分度最高的在最左边。
小结
索引会占用存储空间
索引可以提高查询效率
索引会影响记录更新和插入效率
在创建索引时,应当综合权衡对查询效率和更新插入的效率,合理创建索引。因为创建索引需要先排序,所以优先使用天然有序的数据类型。推荐使用数字型数据类型,这样一来减少空间占用,而来效率高。
SQL规约
- 查询时,应当避免字段隐式类型转换。想象一下把字段值转换之后再来做比较,原索引还能用吗?
- 利用索引覆盖,从而避免回表。
- 需要排序的情况,应尽量利用索引字段的有序性。
- 禁左模糊、全模糊查询。真的需要,则使用搜索引擎。
- 超过三个表禁止 join
知乎上有大佬说因为MySQL的优化器和执行器都比较弱。 - 推荐使用count(*),让优化器来优化。
- 当某一列全部都是NULL,count(col)返回0,sum(col)返回NULL
- 分页查询时,count=0,应提前返回。
- 调页查询优化,通过延迟关联或子查询优化
回表,是指对于像MySQL-innodb引擎这样的通过聚簇索引来存储数据的,通过普通索引查询数据时,需要再更加其叶子节点的主键再到主键索引中查找数据的过程。
而像PostgreSQL这样使用堆表存储数据的,所有的索引的叶子节点存储的都是数据在堆表中的坐标,不管是主键索引还是普通索引,都是要回表才能取出数据。
避坑指南
- 不使用外键与级联,关联在应用层解决。
- 禁止使用存储过程。其拓展性差。
- 更新/删除前应先查询,避免误删除。
- 涉及多张表时,应使用别名。
- 表的别名前面要有as,并且以t1、t2、t3命名。
- in的元素个数不宜太多,应当控制好选择率,避免索引失效。经验值不超过1000个。
优化目标
结合Explain,将SQL优化到range级别以上。
ORM映射规约
- 在表查询中,一律不要使用 * 作为查询的字段列表
- POJO类的布尔类型,不要使用is前缀。
- 查询返回结果都要使用ResultMap映射,避免增加字段时出现遗漏。
- 不要使用{},避免SQL注入。
- 不要使用Mybatis的queryForList。据说有坑,对内存不友好。
- 不使用HashMap接受数据集。
- 更新表时,必须同时更新update_time。
- 不要写一个大而全的接口
避免更新不需要更新的字段。其一容易出错,其二效率低,其三增加binlog存储负担。