yyblog2.0 数据库开发规范

一、基础规范

(1)必须使用InnoDB存储引擎

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

(2)表字符集默认使用utf8,必要时候使用utf8mb4

解读:1、通用,无乱码风险,汉字3字节,英文1字节。2、utf8mb4是utf8的超集,有存储4字节例如表情符号时,使用它

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

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

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

解读:1、对数据库性能影响较大,互联网业务,能让站点层和服务层干的事情,不要交到数据库层。2、调试,排错,迁移都比较困难,扩展性较差。

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

(5)禁止存储大文件或者大照片

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

二、命名规范

(1)库名,表名,列名必须用小写,采用下划线分隔

解读:abc,Abc,ABC都是给自己埋坑

 (2)库名,表名,列名必须见名知义,长度不要超过32字符,禁止拼音英文混用

解读:tmp,wushan谁TM知道这些库是干嘛的

(3)表名t_xxx,非唯一索引名idx_xxx,唯一索引名uniq_xxx

(4)SQL语句中关键字大写,并放在单独的行开始

三、表设计规范

(1)单实例表数目必须小于2000个

(2)单表列数目必须小于80个

(3)表必须有主键,推荐使用UNSIGNED整数,禁止使用字符串作为主键

解读:1、主键要选择较短的数据类型, Innodb引擎普通索引都会保存主键的值,较短的数据类型可以有效的减少索引的磁盘空间,提高索引的缓存效率。2、 无主键的表删除,在row模式的主从架构,会导致备库夯住

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

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

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

四、字段设计规范

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

(2)根据业务区分使用char/varchar

解读:1、字段长度固定,或者长度近似的业务场景,适合使用char,能够减少碎片,查询性能高。2、字段长度相差较大,或者更新较少的业务场景,适合使用varchar,能够减少空间

(3)必须把字段定义为NOT NULL并设默认值

解读:1、NULL的列使用索引,索引统计,值都更加复杂,MySQL更难优化 2、NULL需要更多的存储空间 3、NULL只能采用IS NULL或者IS NOT NULL,而在=/!=/in/not in时有大坑,参见我之前写的:mysql中不等于过滤null的问题

(4)禁止使用double存储货币,推荐decimal

解读:你就不担心钱对不上吗

(5)使用varchar(20)存储手机号,不要使用整数

解读:1、牵扯到国家代号,可能出现+/-/()等字符,例如+86。  2、手机号不会用来做数学运算。3、varchar可以模糊查询,例如like ‘138%’

五、索引设计规范

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

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

解读:1、更新会变更B+树,更新频繁的字段建立索引会大大降低数据库性能。2、“性别”这种区分度不大的属性,建立索引是没有什么意义的,不能有效过滤数据,性能与全表扫描类似。一般来说,同值的数据超过表的百分之十五,那就没必要建索引了

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

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

六、SQL使用规范

(1)禁止使用SELECT *,只获取必要的字段

解读:1、读取不需要的列会增加cpu/io/内存/带宽的消耗。2、指定字段能有效利用索引覆盖。3、指定字段查询,在表结构变更时,能保证对应用程序无影响

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

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

(3)禁止使用属性隐式转换

解读:SELECT uid FROM t_user WHERE phone=13812345678 会导致全表扫描,而不能命中phone索引。这里应当对13812345678 加上单引号'13812345678 '。实际工作中类型字段的隐式转换是最多的,需要特别注意。

(4)禁止在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')

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

解读:1、负向查询条件:NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等,会导致全表扫描。2、%开头的模糊查询,会导致全表扫描,注意像'138%'也是会使用索引的。

(6)禁止大表使用JOIN查询,禁止大表使用子查询

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

(7)禁止使用OR条件,必须改为IN查询,IN的值必须少于50个

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

(8)多表连接必须使用JOIN,LEFT JOIN,禁止使用FROM tab1,tab2

解读:都能实现关联查询,但是使用join更加灵活,效率更高。同时使用join会突出主表的存在,方便在mybaits等框架中快速定位sql位置。sql必须放在主表的xml中,便于重用。

结语:本规范将作为yyblog2.0数据库的开发要求,后续会不断更新。

yyblog1.0项目地址:https://github.com/allanzhuo/yyblog 欢迎star关注yyblog项目。

参考:架构师之路

❤本博客只适用于研究学习为目的,大多为学习笔记,如有错误欢迎指正,如有误导敬请谅解(本人尽力保证90%的验证和10%的猜想)。

❤如果这篇文章对你有一点点的帮助请给一份推荐! 谢谢!你们的鼓励是我继续前进的动力。

posted @ 2018-11-07 20:37  小卖铺的老爷爷  阅读(435)  评论(0编辑  收藏  举报


^
TOP