SQL——数据库使用规范(入门规范)

SQL 专栏收录该内容
11 篇文章 0 订阅

一.基础规范
1.使用InnoDB存储引擎
2.库、表、列字符集使用utf8mb4,utf8mb4兼容utf8且可以存储表情字符。
3.建议所有表、所有列都需要添加注释
4.不在数据库中存储图,文件等大数据,可以将大对象放到磁盘上,数据库中存储它的路径
5.不在线上做数据库压力测试 (可在预发布环境)
6.不在线下开发环境直连线上数据库主库

二.命名规范
1.库名.表名.字段名必须使用小写字母或数字,禁止出现数字开头,禁止连个下划线中间只出现数字,使用下划线“_”分割,分表使用后缀为 “_xx",例如"order_01,order_99"
2.库名.表名.字段名禁止超过32个字符。须见名知意
3.库名.表名.字段名禁止使用MySQL保留字
4.临时库.表名必须以tmp为前缀,并以日期为后缀
5.备份库.表必须以bak为前缀,并以日期为后缀

三. 库.表.字段开发设计规范
1.按日期时间分表需符合YYYY[MM][DD][HH]格式
2.对日志型表选择分区表策略
3.建议不使用TEXT.BLOB类型
4.建议所有字段均定义为NOT NULL,默认值为空的写法为NOT NULL DEFAULT ‘’ 而不是 NOT NULL DEFAULT NULL,禁止使用NULL字段 NULL字段很难查询优化,NULL字段的索引需要额外空间,NULL字段的复合索引无效,详情请查看:https://dev.mysql.com/doc/refman/5.7/en/problems-with-null.html
5.使用UNSIGNED存储非负整数(存储的范围更大了)
6.关于datetime、timestamp类型的异同点。

相同点:
两者的最小精度都为小数点后6位
不同点:
1)存储范围的问题:timestamp类型的存储范围较小,为’1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC;datetime类型的存储范围较大,为’1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’
2)时区的问题:timestamp会随server端时区的变化而变化,datetime不会

  1. 使用INT UNSIGNED存储IPV4,通过MySQL函数inet_ntoa和inet_aton来进行转化。Ipv6地址目前没有转化函数,需要使用DECIMAL或者两个bigINT来存储。
    8.建议使用VARCHAR存储大小写敏感的变长字符串
    9.禁止在数据库中存储明文密码,把密码加密后存储
    10.不允许使用ENUM (插入非法值的时候,默认会插入一个空值)
    11.所有的表(特殊情况除外,如:日志表)必须要有创建时间、更新时间两列,前者表示主动创建,后者表示被动更新【强制】

create_time datetime(0) DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’
update_time datetime(0) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’)
12.可以适当的添加冗余列,减少表关联,提高查询效率,通过注释表明哪张是主表
13.多表关联导致查询语句性能低下,进行join不超过3个表。超过三个表禁止 join。需要 join 的字段,数据类型必须绝对一致;多表关联查询时, 保证被关联的字段需要有索引。【强制】
说明:即使双表join也要注意表索引、SQL 性能。
14.数据库查询语句where条件范围要非常少,不要超过10条记录返回。(建议)
15.业务数据表不允许包含数据物理硬删除操作,若业务存在数据删除操作需求,必须采用逻辑删除,数据表中增加数据逻辑删除标记is_deleted,确实需要进行物理删除的时候需要经过部门leader同意后DBA方可操作。
(备注:其中is_deleted=’0’为否,is_deteled=’1’为是)
16.varchar 是可变长字符串,不预先分配存储空间,长度不要超过5000,如果存储长度大于此值,定义字段类型为text,独立出来一张表,用主键来对应,避免影响其它字段索引效率。【强制】
17.合适的字符存储长度,不但节约数据库表空间、节约索引存储,更重要的是提升检 索速度。【参考】
18.对于内容重复的大字段如text、varchar(500)等列,可以新建冗余表,通过表关联的方式进行查询,提升查询效率,减少磁盘空间使用。

四.索引规范
1.索引的数量要控制:
(1) 单个索引中的字段数不超过5个(建议3个以内)
(2) 单张表中索引数量不超过5个

2.主键准则
(1) 表必须有主键
(2) 不使用更新频繁的列作为主键
(3) 尽量不选择字符串列作为主键
(4) 不允许UUID MD5 HASH这些作为主键
(5) 默认使用非空的唯一键作为主键
(6) 建议选择自增列作为主键,定义为 int/bigint unsigned auto_increment comment ‘主键’

3.重要的SQL必须被索引,比如:
(1) UPDATE.DELETE语句的WHERE条件列
(2) ORDER BY.GROUP BY.DISTINCT的字段

4.多表JOIN的字段注意以下(涉及到多表JOIN的需求,提前提交到DBA处审核):
(1) 区分度最大的字段放在前面
(2) 核心SQL优先考虑覆盖索引
(3) 避免冗余和重复索引
(4) 索引要综合评估数据密度和分布以及考虑查询和更新比例

5.索引禁忌
(1) 不在低基数列上建立索引,例如“性别”
(2) 不在索引列进行数学运算和函数运算
(3) 创建索引前先查看表结构,避免创建冗余索引
(4) 在varchar字段上建立索引时,指定索引长度,没必要对全字段建立索引,根据实际文本区分度决定索引长度即可。【建议】

说明:索引的长度与区分度是一对矛盾体,一般对字符串类型数据,区分度达到 90%及以上即可,可以使用 count(distinct left(列名, 索引长度))/count(distinct 列名)的区分度 来确定。

(5) 页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决。【强制】
说明:索引文件具有 B-Tree 的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引。

(6) 建组合索引的时候,区分度最高的在最左边。【强制】
正例:如果 where a=? and b=? ,如果 a 列的几乎接近于唯一值,那么只需要单建 idx_a索引即可。

说明:存在非等号和等号混合时,在建索引时,请把等号条件的列前置。如:where c>? and d=? 那么即使 c 的区分度更高,也必须把 d 放在索引的最前列,即索引 idx_d_c。

6.尽量不使用外键
(1)不得使用外键与级联,一切外键概念必须在应用层解决 【强制】
说明:以学生和成绩的关系为例,学生表中的 student_id 是主键,那么成绩表中的 student_id则为外键。如果更新学生表中的 student_id,同时触发成绩表中的student_id更新,即为 级联更新。外键与级联更新适用于单机低并发,不适合分布式、高并发集群;级联更新是强阻塞,存在数据库更新风暴的风险;外键影响数据库的插入速度。
(2) 对父表和子表的操作会相互影响,降低可用性
7.索引命名:非唯一索引以 idx_字段1_字段2命名,唯一索引以uk_字段1_字段2命名,索引名称必须全部小写【建议】
8.新建的唯一索引必须不能和主键重复
9.索引字段的默认值不能为NULL,要改为其他的default或者空。NULL非常影响索引的查询效率
10.反复查看与表相关的SQL,符合最左前缀的特点建立索引,例如:一个索引idx_test(n1,n2,n3),where n1= ;where n1= and n2=;where n1= and n3=;where n1= and n2= and n3=,这四种情况会使用到该索引。多条字段重复的语句,要修改语句条件字段的顺序,为其建立一条联合索引,减少索引数量。【强制】
11.能使用唯一索引就要使用唯一索引,提高查询效率
12.使用EXPLAIN判断SQL语句是否合理使用索引,尽量避免extra列出现:Using File Sort,Using Temporary
13.SQL变更需要确认索引是否需要变更并通知DBA

五.SQL规范
1.sql语句尽可能简单,尽力避免使用JOIN
2.事务要简单,整个事务的时间长度不要太长(多事务,小事务原则) ,单事务数据更改粒度为2000行,单事务提交时间应小于1S
3.避免使用触发器.函数.存储过程,如果临时使用存储过程进行批量操作,每个commit之后应该sleep 1s(根据情况设置),避免由于主库大量写入导致的主从延迟问题。
4.避免在数据库中进行数学运算
5.在表查询中,一律不要使用 * 作为查询的字段列表,需要哪些字段必须明确写明。
说明:
1)增加查询分析器解析成本。
2)增减字段容易与resultMap配置不一致。
3)无用字段增加网络消耗,尤其是text类型的字段。

6.limit分页注意效率。Limit越大,效率越低。可以改写limit,比如例子改写:

select id from t limit 10000, 10;
=>
select id from t where id > 10000 limit 10;

SELECT * FROM table ORDER BY TIME DESC LIMIT 10000,10;
=>
SELECT * FROM table WHERE TIME<last_TIME ORDER BY TIME DESC LIMIT 10.
=>
SELECT * FROM table inner JOIN(SELECT id FROM table ORDER BY TIME LIMIT 10000,10) as t USING(id)

7.使用union all替代union
8.避免使用大表的JOIN
9.对数据的更新要打散后批量更新,不要一次更新太多数据,一次每次更新不超过2000条记录
10.减少与数据库的交互次数
11.注意使用性能分析工具 Sql explain / show profile
12.防止因字段类型不同造成的隐式转换,导致索引失效。如id int,使用where id=‘1’;
13.IN条件里面的数据数量要少,尽量不用,IN条件里边的数量应不超过20
14.能不用NOT IN就不用NOT IN,不会把NULL给查出来
15.在SQL语句中,禁止使用前缀是%的like
16.不使用负向查询,如not in/like
17.关于分页查询:程序里建议合理使用分页来提高效率limit,limit较大要配合子查询使用
例如: 先快速定位需要获取的id段,然后再关联:
SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

18.禁止在数据库中跑大查询
19.使用预编译语句,只传参数,比传递SQL语句更高效;一次解析,多次使用;降低SQL注入概率
20.禁止使用order by rand()
21.禁止单条SQL语句同时更新多个表
22.禁止子查询中用group by,order by,DISTINCT。例如 (select xx,xxx from a where a.id in (select id from b group by xx)
23.对分区表查询 条件中必须带上分区字段
24.不要使用count(列名)或count(常量)来替count(),count()是SQL92定义的标准统计行数的语法,跟数据库无关,跟NULL和非NULL无关。
说明:count(*)会统计值为NULL的行,而count(列名)不会统计此列为NULL值的行。

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 扫一扫,分享海报

参与评论 您还未登录,请先 登录 后发表或查看评论
©️2022 CSDN 皮肤主题:黑客帝国 设计师:我叫白小胖 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值