MySQL 编码规范:保障代码质量的关键

1. 流程

数据库表结构的修改需要相关人员和Leader一起评审,保证符合涉及规范。

不允许使用root账号,所有开发和测试应当分配指定账号,并授予最小数据库权限

2. 数据库与表规范

表命名规范

  1. 常规表表名以 t_开头,t 代表 table 的意思,命名规则即 t + 模块(包含模块含义的简写)+ 表(包含表含义的简写) ,比如用户模块的教育信息表:t_user_eduinfo。

  2. 临时表(RD、QA 或 DBA 同学用于数据临时处理的表),命名规则:temp 前缀+模块+表+日期后缀:temp_user_eduinfo_20210719

  3. 备份表(用于保存和归档历史数据或者作为灾备恢复的数据)命名规则,bak 前缀+模块+表+日期后缀:bak_user_eduinfo_20210719

  4. 区分位: iz_* [String(1)] 1表示是 0表示否,(禁用 is_,代码生成实体有问题 )

  5. 状态位: *_status [String(1-2)] 状态字段必须加注释说明每个值代表含义

  6. 同一个模块的表尽可能使用相同的前缀,表名称尽可能表达含义

  7. 多个单词以下划线 _ 分隔

  8. 常规表表名尽量不超过 30 个字符,temp 表和 bak 表视情况而定,也尽量简短为宜,命名应使用小写

数据库对象设计规范

  1. 字符集统一使用utf8mb4,排序utf8mb4_general_ci

  2. 不允许使用视图、存储过程、触发器

  3. 数据库引擎统一innodb

  4. 模块划分和代码中业务模块一致

表设计规范

  1. 不要使用外键

  2. 表必须有主键,设置为varchar(64),唯一

  3. 表必须有create_time(timestamp)字段

  4. 表必须有update_time(timestamp)字段

  5. 禁止使用复杂类型,json使用要看情况

  6. 需要json连接的字段,类型必须一致,防止隐式转换

  7. 严禁使用分区表

  8. 单表字段数不要太多,建议最多不要大于50个。过度的宽表对性能也是很大的影响。

  9. MySQL在处理大表时,性能就开始明显降低,所以建议单表物理大小限制在16GB,表中数据行数控制在2000W内。^[ 业内的规则是超过2000W性能开始明显降低。但是这个值是灵活的,你可以根据实际情况进行测试来判断,比如阿里的标准就是500W,百度的确是2000W。实际上是否宽表,单行数据所占用的空间都有起到作用的。

  10. 表字段注释,每个字段必须设置注释说明;

  11. 表字段注释,状态类型的字段必须说明取值规则(比如性别sex取值规则)

3. 字段规范

字段命名规范

  1. 字段命名需要表示其实际含义的英文单词或简写,单词之间用下划线 _ 进行连接,如 service_ip、service_port。

  2. 各表之间相同意义的字段必须同名,比如 a 表和 b 表都有创建时间,应该统一为 create_time,不一致会很混乱。

  3. 多个单词以下划线 _ 分隔

  4. 字段名尽量不超过 30 个字符,命名应该使用小写

字段设计规范

  1. 对于精确浮点型数据存储,需要使用DECIMAL,严禁使用FLOAT和DOUBLE

  2. 禁止使用TEXT、BLOG字段,特殊情况除外

  3. 字段使用NOT NULL属性,可用默认值代替NULL

  4. 区分、状态、类型字段,尽量用String类型,避免数字类型的一些问题

  5. 字段默认值(字段尽量不设置默认值,采用编码方式加默认值)

  6. 逻辑删除字段,del_flag [int(1)],1表示删除 0表示未删除 ,可选择加

  7. 乐观锁字段, update_count[Integer],可选择加

4. 索引规范

索引命名规范

  1. 唯一索引使用uni + 字段名 来命名:create unique index uni_uid on t_user_basic(uid) 。

  2. 非唯一索引使用idx + 字段名 来命名:create index idx_uname_mobile on t_user_basic(uname,mobile) 。

  3. 多个单词以下划线 _ 分隔

  4. 索引名尽量不超过50个字符,命名应该使用小写,组合索引的字段不宜太多,不然也不利于查询效率的提升。

  5. 多单词组成的列名,取尽可能代表意义的缩写,如 test_contact表member_id和friend_id上的组合索引:idx_mid_fid。

  6. 理解组合索引最左前缀原则,避免重复建设索引,如果建立了(a,b,c),相当于建立了(a), (a,b), (a,b,c)。

索引设计规范

  1. 索引必须创建在索引选择性(区分度)较高的列上,选择性的计算方式为: selecttivity = count(distinct c_name)/count(*) ; 如果区分度结果小于0.2,则不建议在此列上创建索引,否则大概率会拖慢SQL执行

  2. 单张表的索引数量理论上应控制在5个以内。经常有大批量插入、更新操作表,应尽量少建索引,索引建立的原则理论上是**多读少写的场景 **。

  3. ORDER BY,GROUP BY,DISTINCT的字段需要添加在索引的后面,形成覆盖索引

  4. 联合索引注意最左匹配原则:必须按照从左到右的顺序匹配,MySQL会一直向右匹配索引直到遇到范围查询(>、<、between、like) 然后停止匹配。如:depno=1 and empname>'' and job=1 如果建立(depno,empname,job)顺序的索引,job是用不到索引的。

  5. 应需而取策略,查询记录的时候,不要一上来就使用*,只取需要的数据,可能的话尽量只利用索引覆盖,可以减少回表操作,提升效率。

  6. 避免索引失效的原则:禁止对索引字段使用函数、运算符操作,会使索引失效

  7. 避免非必要的类型转换,字符串字段使用数值进行比较的时候会导致索引无效。

  8. 模糊查询'%value%'会使索引无效,变为全表扫描,因为无法判断扫描的区间,但是'value%'是可以有效利用索引

  9. 索引覆盖排序字段,这样可以减少排序步骤,提升查询效率

  10. 尽量的扩展索引,非必要不新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。

扩展下最左前缀

# 这里创建一个索引
CREATE INDEX idx_name ON users (first_name, last_name);

# 在这个索引中,first_name 是最左前缀。现在,当您执行类似以下的查询时,索引可以被有效地利用:
SELECT * FROM users WHERE first_name = 'John' AND last_name = 'Doe';

5. SQL 查询编写规范

  1. 不允许使用select *

  2. 查询必须加where条件,避免全表扫描

  3. 如果必须有TEXT对象,必须单独加表并关联

  4. where条件中过滤字段严禁使用任何函数,包括数据类型转换函数

  5. 分页查询必须带排序条件

  6. 用in/union替换or,并注意in个数小于300

  7. 如无必要不要使用%前缀进行模糊查询,避免全表查询

  8. 避免使用子查询,转为join连接

子查询性能差原因

**「·」**子查询的结果集无法使用索引,通常子查询的结果集会被存储到临时表中,不论是内存临时表还是磁盘临时表都不会存在索引,所以查询性能 会受到一定的影响;

**「·」**特别是对于返回结果集比较大的子查询,其对查询性能的影响也就越大;

**「·」**由于子查询会产生大量的临时表也没有索引,所以会消耗过多的CPU和IO资源,产生大量的慢查询。

6. 事务管理和性能优化

  1. 所有方法涉及到更新、删除、添加操作需要使用@Transactional注解

  2. 数据量大的情况下, 需要分批次操作

7. 安全性考虑

  • 数据库配置应当使用druid等框架对明文进行加密

  • 避免拼接SQL

  • 防止SQL注入��接SQL

  • 防止SQL注入

最后更新于