MySQL规范大全

模糊查询

页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决;

Ps:索引文件具有B-Tree的最左前缀匹配特性,左边值都不能确定的情况,将无法使用索引;

MySQL中存储引擎

没有特殊要求(即Innodb无法满足列存储,存储空间数据等),所有的表必须使用Innodb存储引擎(MySQL5.5之前默认使用Myisam,5.6以后默认为Innodb);

Ps:Innodb支持事务,支持行级锁,更好的恢复性,高并发下性能更好;

数据库和表的字符集

[强制] 统一使用utf8mb4

MySQL注释

[强制] 所有的表和字段必须添加注释,使用comment添加表和列的备注,保证数据字典的可维护性;

预留字段

禁止在表中建预留字段;

Ps:预留字段命名不能做到见名知意;预留字段无法确定存储的数据类型,所以无法选择合适的类型;对预留字段的修改,会对表进行锁定;

存储数据

禁止在数据库存储图片、文件等大的二进制数据;

Ps:通常文件很大,会短时间内造成数据量快速增长,数据库进行读取时,通常会进行大量的随机IO操作,文件很大时,IO特别耗时;

数据类型

优先选择符合存储需要的最小数据类型;

Ps:列的字段越大,建立索引所需要的空间也就越大,这样一页中所能存储的索引节点的数量也就越少,在遍历时所需要的IO次数也就越多,索引的性能也就越差;

避免使用text、blob数据类型,最常见的text类型k可以存储64k的数据;

避免使用enum类型

Ps:修改enum值需要使用alter语句,enum类型的order by 操作效率低,需要额外操作,禁止数值作为enum的枚举值;

尽可能所有列定义为Not Null

Ps:索引Null列需要额外的空间来保存,所以要占用更多的空间,进行比较和计算时要对null值做特别的处理;

使用timestamp(4个字节)或datetime类型(8个字节)存储时间

timestamp存储的时间范围1970-01-01 00:00:00 ~ 2038-01-19 03:14:07
timestamp占用4字节和int相同,但比int可读性高
超出timestamp取值范围使用datetime类型存储
使用字符串存储日期数据是不正确的; 无法用日期函数进行计算和比较; 用字符串存储日期要占用更多的空间;

同财务相关的金额类型必须使用decimal类型

非精准浮点:float,duble
精准浮点: decimal
Decimal类型为精准浮点数,在计算时不会丢失精度;
占用空间由定义的宽度决定,每四个字节可以存储9位数字,并且小数点要占用一个字节;
可用于存储比bigint更大的整型数据;

限制每张表上的索引数量,建议单表索引不超过五个

PS: 索引并不是越多月好,索引可以提高效率但是过多反而降低了效率; 索引可以增加查询效率,但同样会降低插入和更新的效率,甚至有些情况会降低查询效率; 因为MySQL优化器在选择如何优化查询时,会根据统一信息,对每一个而已用到的索引来进行评估,以生出一个最好的执行计划,如果同时有很多个索引都可以用于查询,就会增加MySQL优化器生成执行计划的时间,同样会降低查询性能;

禁止给表中每一列都建立单独的索引

每个Innodb表必须有个主键

Innodb是一种索引组织表;数据存储的逻辑顺序和索引的顺序是相同的;每个表都可以有多个索引,但是表的存储顺序只能有一种;

Innodb是按照主键索引的顺序来组织表的;

不要使用更新频繁的列作为主键,不适用多列主键(相当于联合索引);

不要使用UUID,MD5,HASH,字符串列作为主键(无法保证数据的顺序增长);

主键建议使用自增ID值;

如何选择索引列的顺序

建立索引的目的是:希望通过索引进行数据查找,减少随机IO,增加查询性能,索引能过滤出越少的数据,则从磁盘中读入的数据也就越少;

区分度最高的放在联合索引的最左侧(区分度=列中不同值的熟练/列的总行数)

尽量把字段长度小的列放在联合索引的最左侧(因为字段长度越小,一页能存储的数据量越大,IO性能就越好)

使用最频繁的列放在联合索引的左侧(这样可以比较少的建立一些索引)

避免建立冗余索引和重复索引(增加了查询优化器生成执行计划的时间)

重复索引示例: primary key(id)、index(id)、unique index(id)
冗余索引示例: index(a,b,c)、index(a,b)、index(a)

索引Set规范

尽量避免使用外键约束

不建议使用外键约束(foreign key),但一定要在表与表的关联键上建立索引;

外键可用于保证数据的参照完整性,但建议在业务端实现;

外键会影响父表和子表的写操作从而降低性能;

Select规则

禁止使用SELECT * 必须使用SELECT<字段列表>查询
Ps:这波不过多介绍,自行百度

禁止使用不含字段的INSERT语句

//错误示例
insert into values('jiu','shi','boy');

//正确示例
insert into table(c1,c2,c3) values('jiu','shi','boy');

避免使用子查询,可以把子查询优化为join操作

通常子查询在in子句中,且子查询中为简单SQL(不包含union、group by、order by 、limit从句)时,才可以把子查询转化为关联查询进行优化;

子查询性能差的原因:

子查询的结果集无法使用索引,通常子查询的结果集会被存储到临时表中,不论是内存临时表还是磁盘临时表都不会存在索引,所以查询性能收到一定影响;特别是返回结果集比较大的子查询,对查询性能影响就越大;

由于子查询会产生大量的临时表也没有索引,所以会消耗过多的cpu和io资源,产生大量的慢查询;