数据库开发规范

目录

基础规范

命名规范

表设计规范

字段设计规范

索引设计规范

SQL使用规范

基础规范

必须使用InnoDB存储引擎

解读:支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高。

必须使用UTF8字符集

解读:万国码,无需转码,无乱码风险,节省空间

数据表、数据字段必须加入中文注释

解读:N年后谁tm知道这个r1,r2,r3字段是干嘛的

禁止使用存储过程、视图、触发器、Event

解读:高并发大数据的互联网业务,架构设计思路是“解放数据库CPU,将计算转移到服务层”,并发量大的情况下,这些功能很可能将数据库拖死,业务逻辑放到服务层具备更好的扩展性,能够轻易实现“增机器就加性能”。数据库擅长存储与索引,CPU计算还是上移吧

禁止存储大文件或者大照片

解读:为何要让数据库做它不擅长的事情?大文件和照片存储在文件系统,数据库里存URI多好

不在数据库做计算,cpu计算务必移至业务层

控制单表数据量,单表记录控制在千万级

控制列数量,字段数控制在20以内

平衡范式与冗余,为提高效率可以牺牲范式设计,冗余数据

拒绝3B(big),大sql,大事务,大批量

命名规范

只允许使用内网域名,而不是ip连接数据库

线上环境、开发环境、测试环境数据库内网域名遵循命名规范

业务名称:xxx

从库在名称后加-s标识,备库在名称后加-ss标识

线上从库:dj.xxx-s.db

线上备库:dj.xxx-sss.db

库名、表名、字段名:小写,下划线风格,不超过32个字符,必须见名知意,禁止拼音英文混用

表名t_xxx,非唯一索引名idx_xxx,唯一索引名uniq_xxx

表设计规范

表必须有主键,例如自增主键

表必须有主键,推荐使用UNSIGNED整数为主键。

禁止使用外键,如果要保证完整性,应由应用程式实现

解读:外键使得表之间相互耦合,影响update/delete等SQL性能,有可能造成死锁,高并发情况下容易成为数据库瓶颈。

建议将大字段,访问频度低的字段拆分到单独的表中存储,分离冷热数据。

表必须有主键,推荐使用UNSIGNED整数为主键。

解读:

主键递增,数据行写入可以提高插入性能,可以避免page分裂,减少表碎片提升空间和内存的使用

主键要选择较短的数据类型, Innodb引擎普通索引都会保存主键的值,较短的数据类型可以有效的减少索引的磁盘空间,提高索引的缓存效率

无主键的表删除,在row模式的主从架构,会导致备库夯住

禁止使用外键,如果有外键完整性约束,需要应用程序控制

解读:外键会导致表与表之间耦合,update与delete操作都会涉及相关联的表,十分影响sql 的性能,甚至会造成死锁。高并发情况下容易造成数据库性能,大数据高并发业务场景数据库使用以性能优先

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

varchar 是可变长字符串,不预先分配存储空间,长度不要超过 5000,如果存储长度大于此值,定义字段类型为 text,独立出来一张表,用主键来对应,避免影响其它字段索引效率

字段设计规范

必须把字段定义为NOT NULL并且提供默认值

解读:

null的列使索引/索引统计/值比较都更加复杂,对MySQL来说更难优化

null 这种类型MySQL内部需要进行特殊处理,增加数据库处理记录的复杂性;同等条件下,表中有较多空字段的时候,数据库的处理性能会降低很多

null值需要更多的存储空,无论是表还是索引中每行中的null的列都需要额外的空间来标识

对null 的处理时候,只能采用is null或is not null,而不能采用=、in、、!=、not in这些操作符号。如:where name!=’shenjian’,如果存在name为null值的记录,查询结果就不会包含name为null值的记录

禁止使用TEXT、BLOB类型

解读:会浪费更多的磁盘和内存空间,非必要的大量的大字段查询会淘汰掉热数据,导致内存命中率急剧降低,影响数据库性能

禁止使用小数存储货币

解读:使用整数吧,小数容易导致钱对不上

必须使用varchar(20)存储手机号

解读:

涉及到区号或者国家代号,可能出现+-()

手机号会去做数学运算么?

varchar可以支持模糊查询,例如:like“138%”

禁止使用ENUM,可使用TINYINT代替

解读:

增加新的ENUM值要做DDL操作

ENUM的内部实际存储就是整数,你以为自己定义的是字符串?

根据业务区分使用tinyint/int/bigint,分别会占用1/4/8字节。

根据业务区分使用char/varchar

解读:

字段长度固定,或者长度近似的业务场景,适合使用char,能够减少碎片,查询性能高。

字段长度相差较大,或者更新较少的业务场景,适合使用varchar,能够减少空间。

根据业务区分使用datetime/timestamp。

解读:前者占用5个字节,后者占用4个字节,存储年使用YEAR,存储日期使用DATE,存储时间使用datetime。

索引设计规范

单表索引建议控制在5个以内

单索引字段数不允许超过5个

解读:字段超过5个时,实际已经起不到有效过滤数据的作用了

禁止在更新十分频繁、区分度不高的属性上建立索引

解读:

更新会变更B+树,更新频繁的字段建立索引会大大降低数据库性能

“性别”这种区分度不大的属性,建立索引是没有什么意义的,不能有效过滤数据,性能与全表扫描类似。

建立组合索引,必须把区分度高的字段放在前面

解读:能够更加有效的过滤数据

不在索引做列运算

务必请使用“同类型”进行比较,否则可能全表扫面。

字符字段必须建前缀索引。

在 varchar 字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据实际文本区分度决定索引长度即可。

解读:索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为 20 的索引,区分度会高达 90%以上,可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

超过三个表禁止join,需要 join的字段,数据类型必须绝对一致;多表关联查询时,保证被关联的字段需要有索引。

解读:即使双表 join 也要注意表索引、SQL性能。

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

解读:索引文件具有 B-Tree 的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引。

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

解读:order by 最后的字段是组合索引的一部分,并且放在索引组合顺序的最后,避免出现 file_sort 的情况,影响查询性能。

利用覆盖索引来进行查询操作,避免回表。

解读:如果一本书需要知道第 11 章是什么标题,会翻开第 11 章对应的那一页吗?目录浏览一下就好,这个目录就是起到覆盖索引的作用。

正例: 能够建立索引的种类:主键索引、唯一索引、普通索引,而覆盖索引是一种查询的一种 效果,用 explain 的结果,extra 列会出现:using index。

利用延迟关联或者子查询优化超多分页场景。

解读:MySQL 并不是跳过 offset 行,而是取 offset+N 行,然后返回放弃前 offset 行,返回 N 行,那当 offset 特别大的时候,效率就非常的低下,要么控制返回的总页数,要么对超过特定阈值的页数进行 SQL 改写。

SQL 性能优化的目标:至少要达到 range 级别,要求是 ref 级别,如果可以是 consts 最好。

解读:

consts 单表中最多只有一个匹配行(主键或者唯一索引),在优化阶段即可读取到数据。

ref 指的是使用普通的索引(normal index)。

range 对索引进行范围检索。

建组合索引的时候,区分度最高的在最左边。

解读:存在非等号和等号混合判断条件时,在建索引时,请把等号条件的列前置。如:where a>? and b=? 那么即使 a 的区分度更高,也必须把 b 放在索引的最前列。

SQL使用规范

禁止使用SELECT *,只获取必要的字段,需要显示说明列属性

解读:

读取不需要的列会增加CPU、IO、NET消耗

不能有效的利用覆盖索引

使用SELECT *容易在增加或者删除字段后出现程序BUG

禁止使用INSERT INTO t_xxx VALUES(xxx),必须显示指定插入的列属性

解读:容易在增加或者删除字段后出现程序BUG

禁止使用属性隐式转换

phone是varchar类型,SQL语句带入的是整形,故不会命中索引,加个引号就好了

SELECT uid FROM t_user WHERE phone=’13812345678’

禁止在WHERE条件的属性上使用函数或者表达式

解读:SELECT uid FROM t_user WHERE from_unixtime(day)>='2017-02-15' 会导致全表扫描

正确的写法:SELECT uid FROM t_user WHERE day>= unix_timestamp('2017-02-15 00:00:00')

禁止负向查询,以及%开头的模糊查询

解读:

负向查询条件:NOT、!=、、!、NOT IN、NOT LIKE等,会导致全表扫描

%开头的模糊查询,会导致全表扫描

禁止大表使用JOIN查询,禁止大表使用子查询

解读:会产生临时表,消耗较多内存与CPU,极大影响数据库性能

禁止使用OR条件,必须改为IN查询

解读:旧版本Mysql的OR查询是不能命中索引的,即使能命中索引,为何要让数据库耗费更多的CPU帮助实施查询优化呢?

应用程序必须捕获SQL异常,并有相应处理

使用新能分析工

使用union all替代union

解读:union有去重开销。

sql语句尽可能简单

解读:大语句拆小语句,减少锁时间;一条大sql可以堵死整个库。

OR改写为IN()

OR改写为UNION

limit高效分页

解读:limit越大,效率越低。

`

尽量不用连接join。

不要使用 count(列名)或 count(常量)来替代 count(),count()是 SQL92 定义的标准统计行数的语法,跟数据库无关,跟 NULL 和非 NULL 无关。

解读:count(*)会统计值为 NULL 的行,而 count(列名)不会统计此列为 NULL 值的行。

count(distinct col) 计算该列除 NULL 之外的不重复行数,注意 count(distinct col1, col2) 如果其中一列全为 NULL,那么即使另一列有不同的值,也返回为 0。

当某一列的值全是 NULL 时,count(col)的返回结果为 0,但 sum(col)的返回结果为 NULL,因此使用 sum()时需注意 NPE 问题。

使用 ISNULL()来判断是否为 NULL值。注意:NULL 与任何值的直接比较都为 NULL。

解读:

NULLNULL 的返回结果是 NULL,而不是 false。

NULL=NULL 的返回结果是 NULL,而不是 true。

NULL1 的返回结果是 NULL,而不是 true。

不得使用外键与级联,一切外键概念必须在应用层解决。

解读:外键与级联更新适用于单机低并发,不适合分布式、高并发集群;级联更新是强阻塞,存在数据库更新风暴的风险;外键影响数据库的插入速度。

in 操作能避免则避免,若实在避免不了,需要仔细评估 in 后边的集合元素数量,控制在 1000 个之内

如果有全球化需要,所有的字符存储与表示,均以 utf-8 编码,注意字符统计函数的区别。

TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少,但 TRUNCATE 无事务且不触发 trigger,有可能造成事故,故不建议在开发代码中使用此语句。

解读:TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

  • 发表于:
  • 原文链接:http://kuaibao.qq.com/s/20180420G02L0O00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。

扫码关注云+社区

领取腾讯云代金券