开发中 MySQL 规范

  • Last updated on 2020-05-27
  • 788
Mysql  

一、建表规范

 
1、数据库名、表名、字段名必须使用小写字母或数字,并且禁止以数字开头

示例:goods_category、agent_operate_201812_log

 

2、数据库名、表名、字段名要做到见名知意

示例:goods_category,不能 gc

 

3、配置表建议以 xx_config 形式命名

示例:shop_payment_config

 

4、日志表建议以 xx_log 形式命名

示例:system_log

 

5、临时表建议以 temp_xx 形式命名

示例:temp_order_info_export

 

6、创建时间使用 create_time,更新时间使用 update_time

类型使用 int(11) unsigned

 

7、字段类型为字符串时需要注意的

如果存储的字符串长度几乎相等,则应该使用 char 定长字符串类型。

如果长度超过5000个字符,则应该将字段类型定义为 text,并独立出来一张表,用主键对应,避免影响其他字段的索引效率。

 

8、字段允许适当冗余,以提高查询性能,但必须考虑数据一致

 

9、单表行数超过 500 万行 或者 单表容量超过 2GB 时,才推荐进行分库分表

 

10、当存储的字段为小数时,数据类型设置为 decimal,禁止使用 float 和 double

在存储的时候,float 和 double 存在精度损失的问题,很可能在比较值的时候,得到不正确的结果。

如果存储的数据范围超过 decimal 的范围,建议将数据拆成整数和小数分开存储。

存储商品价格时,统一转为分,存储类型应为整型 int。

 

11、没有特殊要求的情况下,所有的数据表必须使用 Innodb 存储引擎

Innodb 支持事务,支持行级锁,拥有更好的并发性能和恢复性。

 

12、数据库和数据表的字符集统一使用 utf8,需要存储 emoji 表情的使用 utf8mb4

 

13、所有数据表和字段必须写 comment 注释说明

有条件尽量建立数据字典。

 

14、尽量做到冷热数据分离,减小表的宽度

表越宽,把表装进内存缓冲池时所占用的内存也就越大,也会消耗更多的 IO。

 

15、禁止在数据表中建立预留字段

预留字段的命名很难做到见名识意,并且无法选择合适的类型,而且对预留字段修改时,会对整张表进行锁定。

 

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

文件很大时,IO 将会很耗时,也会占用很多带宽,影响响应速度。

建议图片、视频、大文件统一存储在文件存储空间,比如阿里云、腾讯云的对象存储空间和文件存储空间,数据库中只记录文件地址。

 

17、设置合适的字符存储长度

对象 年龄区间 类型 字节 表示范围
150岁以内 unsigned tinyint 1 无符号值 0~255
乌龟 数百岁 unsigned smallint 2 无符号值 0~65535
恐龙化石 数千万年 unsigned int 4 无符号值 0~约42.9亿
太阳 约50亿年 unsigned bigint 8 无符号值 0~约10^19

 

18、条件允许,就将字符串转换成数字类型存储

比如存储ip时,使用 ip2long 和 long2ip

 

19、避免使用 enum 类型存储字段

enum 类型的 orderby 操作效率低。

 

20、建议把所有列定义为 not null

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

 

21、禁止在开发环境、测试环境直接连接生产环境数据库

 

 

二、索引规范

 
1、业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引

唯一索引影响 insert 的速度可以忽略不计,但会明显提高查询速度。

另外,即使在应用层做了非常完善的校验控制,只要没有唯一索引,根据墨菲定律,必然会有脏数据产生。

 

2、禁止3个表以上join。需要join的字段,数据类型必须一致,当多表关联时,保证被关联的字段有索引

 

3、限制每张表上的索引数量,尽量不超过5个

索引增加查询效率的同时,也会降低插入和更新的效率,甚至有时会降低查询效率。

mysql优化器在选择如何优化查询时,会根据统一信息,对每一个可以用到的索引进行评估,以生成一个最佳的执行计划。

如果同时有很多个索引都可以用于查询,就会增加mysql优化器生成执行计划的时间,进而降低查询性能。

 

4、在 varchar 字段上建立索引时,必须指定索引长度

没必要对全字段建立索引,根据实际文本区分度决定索引长度即可。一般对字符串数据,长度为20的索引,区分度就会高达 90%。

可以使用 count(distinct left (列名,索引长度)) / count(*) 来确定区分度。

 

5、页面搜索严禁左模糊或全模糊,如果需要,请使用搜索引擎解决

索引文件具有最左匹配特性,如果左边的值未确定,则无法使用此索引。

 

6、如果有order by 的场景,请注意利用索引的有序性

正例:where a=5 and b=10 order by c; 索引 a_b_c 生效

反例:where a>10 order by b 索引中有范围查找,索引 a_b 不生效

 

7、使用延迟关联或者子查询优化超多分页场景

MySQL 并不是跳过 offset 行,而是取 offset + n 行。

当 offset 特别大时,效率将会非常低,要么控制返回的总页数,要么对超过特定阀值的页数进行 SQL 改写。

正例:先快速定位需要获取的 id 段,然后再关联。

  1. SELECT a.* FROM 1 a, (select id from 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

实例对比:

  1. select a.* from agent_admin a, (select agent_admin_id from agent_admin where admin_id = 11400 limit 1000,5) b where a.agent_admin_id=b.agent_admin_id
  2. 0.017s
  3. SELECT * from agent_admin where admin_id = 22240 limit 1000,5
  4. 0.023S

 

8、建立组合索引时,区分度最高的放在最左边

 

9、哪些字段最好建索引

(1)经常出现在 where 从句的字段

(2)包含在 order by,group by、distinct 中的字段

 

10、避免建立重复索引和冗余索引

建立冗余索引,ui增加查询优化器生成执行计划的时间

  1. // 重复索引示例
  2. primary key(id)
  3. index(id)
  4. unique key(id)
  1. // 冗余索引示例
  2. index(a,b,c)
  3. index(a,b)
  4. index(a)

 

11、创建索引时尽量避免如下误解

(1)宁滥勿缺:认为一个查询就需要建立一个索引

(2)宁缺毋滥:任务索引会消耗空间、严重拖慢更新和新增速度

(3)抵制唯一索引:认为业务的唯一性一律需要在应用层通过“先查后插”的方式解决

 

 

三、SQL 开发规范

 

1、不要使用 count(列名) 或 count(常量) 来替代 count(*)

count(*) 是 SQL92 定义的标准统计行数的语法,count(*) 会统计值为NULL的行,而count(列名) 不会统计此列值为 null 的行。

 
2、在代码中写分页查询逻辑时,如果 count 为 0 ,应直接返回结果,避免继续执行后面的程序再返回结果

 

3、禁止使用存储过程

存储过程难以调试和扩展,新人接手麻烦,可移植性差。

 

4、禁止使用外键与级联,一切外键概念必须在应用层解决

以学生和成绩的关系为例,学生表中的student_id是主键,那么成绩表中的student_id则为外键。如果更新学生表中的 student_id,同时触发成绩表中的student_id更新,即为级联更新。

外键与级联更新适用于单机低并发,不适合分布式、高并发集群;

级联更新是强阻塞,存在数据库更新风暴的风险;外键影响数据库的插入速度。

 

5、建议使用预编译语句进行数据库操作

尽量使用框架自带的查询构造器,其底层均封装了预编译处理。如果特殊情况使用不了框架的查询构造器,也要手动预编译查询。

预编译语句可以重复使用优化查询器生成的执行计划,减少 SQL编译 所需要的时间,还可以解决动态 SQL 所带来的的 SQL 注入问题。

 

6、避免数据类型的隐式转换

  1. select user_name,age from admin where admin_id = '11140';

 

7、禁止使用 select *,必须指定要查询的具体字段

(1)无法使用覆盖索引

注:覆盖索引的含义是 select 的数据列只从索引中就能够取得,不必读取数据行,换句话说查询列已经被所建的索引覆盖。

(2)消耗更多的 cpu 和 IO 以及网络带宽资源。

 

8、避免使用子查询,可以把子查询优化成join查询

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

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

 

9、避免使用JOIN关联太多的表

对于Mysql来说,是存在关联缓存的,缓存的大小可以由join_buffer_size参数进行设置。

在 Mysql 中,对于同一个 SQL 多关联一个表,就会多分配一个关联缓存,在一个SQL 中,关联的表越多,所占用的内存就越大。

如果程序中大量的使用了多表关联的操作,同时 join_buffer_size 设置的也不合理的情况下,就容易造成服务器内存溢出的情况,进而影响到服务器数据库性能的稳定性。

MySQL 最多允许关联61个表,建议不超过5个

 

10、减少和数据库的交互操作

合并多个相同的操作到一起,可以提高处理效率。比如批量更新时,将语句处理合并后,在提交到 MySQL 中进行处理,这样效率会更高。

一定要避免在循环中执行 SQL。

 

11、对同一列进行 or 判断时,使用 in 代替 or

举例:

  1. select user_name,age from admin where city in (1024, 1028);

in 操作可以更有效的利用索引,or 大多数情况下很少能利用到索引。但需要注意的是,in 的值不要超过500个。

 

12、禁止在 SQL 语句中进行函数转换和计算

将数据取出来再在程序中进行处理,比如格式化时间和转换ip时。

 

13、在明显不会有重复值时使用 UNINON ALL,而不是 UNION

UNION 会把两个结果集的所有数据放到临时表,再进行去重操作

UNINON ALL不会再对结果集进行去重操作

 

14、拆分复杂的大 SQL 为多个小 SQL

SQL 拆分后可以通过并行执行来提高处理效率。

 

15、大批量操作分批执行

大批量修改数据,会造成表中大量数据行被锁定,从而造成大量的阻塞。

长时间的阻塞会占满数据库所有的可用连接,使生产环境中的其他应用无法连接到数据库。

因此一定要注意大批量写操作一定要分批执行。

 

 



Top