服务器之家

服务器之家 > 正文

mysql数据库开发规范【推荐】

时间:2020-08-16 17:11     来源/作者:erisen

最近一段时间一边在线上抓取SQL来优化,一边在整理这个开发规范,尽量减少新的问题SQL进入生产库。今天也是对公司的开发做了一次培训,PPT就不放上来了,里面有十来个生产SQL的案例。因为规范大部分还是具有通用性,所以也借鉴了像去哪儿和赶集的规范,但实际在撰写本文的过程中,每一条规范的背后无不是在工作中有参照的反面例子的。如果时间可以的话,会抽出一部分或分析其原理,或用案例证明。

一. 命名规范

1.库名、表名、字段名必须使用小写字母,并采用下划线分割

(1)MySQL有配置参数lower_case_table_names=1,即库表名以小写存储,大小写不敏感。如果是0,则库表名以实际情况存储,大小写敏感;如果是2,以实际情况存储,但以小写比较。

(2)如果大小写混合使用,可能存在abc,Abc,ABC等多个表共存,容易导致混乱。

(3)字段名显示区分大小写,但实际使⽤时不区分,即不可以建立两个名字一样但大小写不一样的字段。

(4)为了统一规范, 库名、表名、字段名使用小写字母。

2.库名以 d 开头,表名以 t 开头,字段名以 f_ 开头

(1)比如表 t_crm_relation,中间的 crm 代表业务模块名

(2)视图以view_开头,事件以event_开头,触发器以trig_开头,存储过程以proc_开头,函数以func_开头

(3)普通索引以idx_col1_col2命名,唯一索引以uk_col1_col2命名(可去掉f_公共部分)。如 idx_companyid_corpid_contacttime(f_company_id,f_corp_id,f_contact_time)

3.库名、表名、字段名禁止超过32个字符,需见名知意

库名、表名、字段名支持最多64个字符,但为了统一规范、易于辨识以及减少传输量,禁止超过32个字符

4.临时库、表名须以tmp加日期为后缀

如 t_crm_relation_tmp0425。备份表也类似,形如 _bak20160425 。

5.按日期时间分表须符合_YYYY[MM][DD]格式

这也是为将来有可能分表做准备的,比如t_crm_ec_record_201403,但像 t_crm_contact_at201506就打破了这种规范。
不具有时间特性的,直接以 t_tbname_001 这样的方式命名。

二. 库表基础规范

1.使用Innodb存储引擎

5.5版本开始mysql默认存储引擎就是InnoDB,5.7版本开始,系统表都放弃MyISAM了。

2.表字符集统一使用UTF8

(1)UTF8字符集存储汉字占用3个字节,存储英文字符占用一个字节

(2)校对字符集使用默认的 utf8_general_ci

(3)连接的客户端也使用utf8,建立连接时指定charset或SET NAMES UTF8;。(对于已经在项目中长期使用latin1的,救不了了)

(4)如果遇到EMOJ等表情符号的存储需求,可申请使用UTF8MB4字符集

3.所有表都要添加注释

(1)尽量给字段也添加注释

(2)类status型需指明主要值的含义,如”0-离线,1-在线”

4.控制单表字段数量

(1)单表字段数上限30左右,再多的话考虑垂直分表,一是冷热数据分离,二是大字段分离,三是常在一起做条件和返回列的不分离。

(2)表字段控制少而精,可以提高IO效率,内存缓存更多有效数据,从而提高响应速度和并发能力,后续 alter table 也更快。

5.所有表都必须要显式指定主键

(1)主键尽量采用自增方式,InnoDB表实际是一棵索引组织表,顺序存储可以提高存取效率,充分利用磁盘空间。还有对一些复杂查询可能需要自连接来优化时需要用到。

(2)需要全局唯一主键时,使用外部发号器ticket server(建设中)

(3)如果没有主键或唯一索引,update/delete是通过所有字段来定位操作的行,相当于每行就是一次全表扫描

(4)少数情况可以使用联合唯一主键,需与DBA协商

6.不强制使用外键参考

即使2个表的字段有明确的外键参考关系,也不使用 FOREIGN KEY ,因为新纪录会去主键表做校验,影响性能。

7.适度使用存储过程、视图,禁止使用触发器、事件

(1)存储过程(procedure)虽然可以简化业务端代码,在传统企业写复杂逻辑时可能会用到,而在互联网企业变更是很频繁的,在分库分表的情况下要升级一个存储过程相当麻烦。又因为它是不记录log的,所以也不方便debug性能问题。如果使用过程,一定考虑如果执行失败的情况。

(2)使用视图一定程度上也是为了降低代码里SQL的复杂度,但有时候为了视图的通用性会损失性能(比如返回不必要的字段)。

(3)触发器(trigger)也是同样,但也不应该通过它去约束数据的强一致性,mysql只支持“基于行的触发”,也就是说,触发器始终是针对一条记录的,而不是针对整个sql语句的,如果变更的数据集非常大的话,效率会很低。掩盖一条sql背后的工作,一旦出现问题将是灾难性的,但又很难快速分析和定位。再者需要ddl时无法使用pt-osc工具。放在transaction执行。

(4)事件(event)也是一种偷懒的表现,目前已经遇到数次由于定时任务执行失败影响业务的情况,而且mysql无法对它做失败预警。建立专门的 job scheduler 平台。

a.单表数据量控制在5000w以内

b.数据库中不允许存储明文密码

三. 字段规范

1.char、varchar、text等字符串类型定义

(1)对于长度基本固定的列,如果该列恰好更新又特别频繁,适合char

(2)varchar虽然存储变长字符串,但不可太小也不可太大。UTF8最多能存21844个汉字,或65532个英文

(3)varbinary(M)保存的是二进制字符串,它保存的是字节而不是字符,所以没有字符集的概念,M长度0-255(字节)。只用于排序或比较时大小写敏感的类型,不包括密码存储

(4)TEXT类型与VARCHAR都类似,存储可变长度,最大限制也是2^16,但是它20bytes以后的内容是在数据页以外的空间存储(row_format=dynamic),对它的使用需要多一次寻址,没有默认值。

一般用于存放容量平均都很大、操作没有其它字段那样频繁的值。

网上部分文章说要避免使用text和blob,要知道如果纯用varchar可能会导致行溢出,效果差不多,但因为每行占用字节数过多,会导致buffer_pool能缓存的数据行、页下降。另外text和blob上面一般不会去建索引,而是利用sphinx之类的第三方全文搜索引擎,如果确实要创建(前缀)索引,那就会影响性能。凡事看具体场景。

另外尽可能把text/blob拆到另一个表中

(5)BLOB可以看出varbinary的扩展版本,内容以二进制字符串存储,无字符集,区分大小写,有一种经常提但不用的场景:不要在数据库里存储图片

2.int、tinyint、decimal等数字类型定义

(1)使用tinyint来代替 enum和boolean
ENUM类型在需要修改或增加枚举值时,需要在线DDL,成本较高;ENUM列值如果含有数字类型,可能会引起默认值混淆
tinyint使用1个字节,一般用于status,type,flag的列

(2)建议使用 UNSIGNED 存储非负数值
相比不使用 unsigned,可以扩大一倍使用数值范围

(3)int使用固定4个字节存储,int(11)与int(4)只是显示宽度的区别

(4)使用Decimal 代替float/double存储精确浮点数
对于货币、金额这样的类型,使用decimal,如 decimal(9,2)。float默认只能能精确到6位有效数字

3.timestamp与datetime选择

(1)datetime 和 timestamp类型所占的存储空间不同,前者8个字节,后者4个字节,这样造成的后果是两者能表示的时间范围不同。前者范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59,后者范围为 1970-01-01 08:00:01 到 2038-01-19 11:14:07 。所以 TIMESTAMP 支持的范围比 DATATIME 要小。

(2)timestamp可以在insert/update行时,自动更新时间字段(如 f_set_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP),但一个表只能有一个这样的定义。

(3)timestamp显示与时区有关,内部总是以 UTC 毫秒 来存的。还受到严格模式的限制

(4)优先使用timestamp,datetime也没问题

(5)where条件里不要对时间列上使用时间函数

4.建议字段都定义为NOT NULL

(1)如果是索引字段,一定要定义为not null 。因为null值会影响cordinate统计,影响优化器对索引的选择

(2)如果不能保证insert时一定有值过来,定义时使用default ‘' ,或 0

5.同一意义的字段定义必须相同

比如不同表中都有 f_user_id 字段,那么它的类型、字段长度要设计成一样

四. 索引规范

1.任何新的select,update,delete上线,都要先explain,看索引使用情况

尽量避免extra列出现:Using File Sort,Using Temporary,rows超过1000的要谨慎上线。
explain解读

(1)type:ALL, index, range, ref, eq_ref, const, system, NULL(从左到右,性能从差到好)

(2)possible_keys:指出MySQL能使用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用

(3)key:表示MySQL实际决定使用的键(索引)
如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX

(4)ref:表示选择 key 列上的索引,哪些列或常量被用于查找索引列上的值

(5)rows:根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数

(6)Extra

a.Using temporary:表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询
b.Using filesort:MySQL中无法利用索引完成的排序操作称为“文件排序”

1.索引个数限制

(1)索引是双刃剑,会增加维护负担,增大IO压力,索引占用空间是成倍增加的

(2)单张表的索引数量控制在5个以内,或不超过表字段个数的20%。若单张表多个字段在查询需求上都要单独用到索引,需要经过DBA评估。

2.避免冗余索引

(1.)InnoDB表是一棵索引组织表,主键是和数据放在一起的聚集索引,普通索引最终指向的是主键地址,所以把主键做最后一列是多余的。如f_crm_id作为主键,联合索引(f_user_id,f_crm_id)上的f_crm_id就完全多余

(2)(a,b,c)、(a,b),后者为冗余索引。可以利用前缀索引来达到加速目的,减轻维护负担

3.没有特殊要求,使用自增id作为主键

(1.)主键是一种聚集索引,顺序写入。组合唯一索引作为主键的话,是随机写入,适合写少读多的表

(2)主键不允许更新

4.索引尽量建在选择性高的列上

(1)不在低基数列上建立索引,例如性别、类型。但有一种情况,idx_feedbackid_type (f_feedback_id,f_type),如果经常用 f_type=1 比较,而且能过滤掉90%行,那这个组合索引就值得创建。有时候同样的查询语句,由于条件取值不同导致使用不同的索引,也是这个道理。

(2)索引选择性计算方法(基数 ÷ 数据行数)

Selectivity = Cardinality / Total Rows = select count(distinct col1)/count(*) from tbname,越接近1说明col1上使用索引的过滤效果越好

(3)走索引扫描行数超过30%时,改全表扫描

5.最左前缀原则

(1)mysql使用联合索引时,从左向右匹配,遇到断开或者范围查询时,无法用到后续的索引列
比如索引idx_c1_c2_c3 (c1,c2,c3),相当于创建了(c1)、(c1,c2)、(c1,c2,c3)三个索引,where条件包含上面三种情况的字段比较则可以用到索引,但像 where c1=a and c3=c 只能用到c1列的索引,像 c2=b and c3=c等情况就完全用不到这个索引

(2)遇到范围查询(>、<、between、like)也会停止索引匹配,比如 c1=a and c2 > 2 and c3=c,只有c1,c2列上的比较能用到索引,(c1,c2,c3)排列的索引才可能会都用上

(3)where条件里面字段的顺序与索引顺序无关,mysql优化器会自动调整顺序

6.前缀索引

(1)对超过30个字符长度的列创建索引时,考虑使用前缀索引,如 idx_cs_guid2 (f_cs_guid(26))表示截取前26个字符做索引,既可以提高查找效率,也可以节省空间

(2)前缀索引也有它的缺点是,如果在该列上 ORDER BY 或 GROUP BY 时无法使用索引,也不能把它们用作覆盖索引(Covering Index)

(3)如果在varbinary或blob这种以二进制存储的列上建立前缀索引,要考虑字符集,括号里表示的是字节数

7.合理使用覆盖索引减少IO

INNODB存储引擎中,secondary index(非主键索引,又称为辅助索引、二级索引)没有直接存储行地址,而是存储主键值。
如果用户需要查询secondary index中所不包含的数据列,则需要先通过secondary index查找到主键值,然后再通过主键查询到其他数据列,因此需要查询两次。覆盖索引则可以在一个索引中获取所有需要的数据列,从而避免回表进行二次查找,节省IO因此效率较高。

例如SELECT email,uid FROM user_email WHERE uid=xx,如果uid不是主键,适当时候可以将索引添加为index(uid,email),以获得性能提升。

8.尽量不要在频繁更新的列上创建索引

如不在定义了 ON UPDATE CURRENT_STAMP 的列上创建索引,维护成本太高(好在mysql有insert buffer,会合并索引的插入)

五. SQL设计

1.杜绝直接 SELECT * 读取全部字段

即使需要所有字段,减少网络带宽消耗,能有效利用覆盖索引,表结构变更对程序基本无影响

2.能确定返回结果只有一条时,使用 limit 1

在保证数据不会有误的前提下,能确定结果集数量时,多使用limit,尽快的返回结果。

3.小心隐式类型转换

(1)转换规则

a. 两个参数至少有一个是 NULL 时,比较的结果也是 NULL,例外是使用 <=> 对两个 NULL 做比较时会返回 1,这两种情况都不需要做类型转换

b. 两个参数都是字符串,会按照字符串来比较,不做类型转换

c. 两个参数都是整数,按照整数来比较,不做类型转换

d. 十六进制的值和非数字做比较时,会被当做二进制串

e. 有一个参数是 TIMESTAMP 或 DATETIME,并且另外一个参数是常量,常量会被转换为 timestamp

f. 有一个参数是 decimal 类型,如果另外一个参数是 decimal 或者整数,会将整数转换为 decimal 后进行比较,如果另外一个参数是浮点数,则会把 decimal 转换为浮点数进行比较

g. 所有其他情况下,两个参数都会被转换为浮点数再进行比较。

(2)如果一个索引建立在string类型上,如果这个字段和一个int类型的值比较,符合第 g 条。如f_phone定义的类型是varchar,但where使用f_phone in (098890),两个参数都会被当成成浮点型。发生这个隐式转换并不是最糟的,最糟的是string转换后的float,mysql无法使用索引,这才导致了性能问题。如果是 f_user_id = ‘1234567' 的情况,符合第 b 条,直接把数字当字符串比较。

4.禁止在where条件列上使用函数

(1)会导致索引失效,如lower(email),f_qq % 4。可放到右边的常量上计算

(2)返回小结果集不是很大的情况下,可以对返回列使用函数,简化程序开发

5.使用like模糊匹配,%不要放首位

会导致索引失效,有这种搜索需求是,考虑其它方案,如sphinx全文搜索

6.涉及到复杂sql时,务必先参考已有索引设计,先explain

(1)简单SQL拆分,不以代码处理复杂为由。

(2)比如 OR 条件: f_phone='10000' or f_mobile='10000',两个字段各自有索引,但只能用到其中一个。可以拆分成2个sql,或者union all。

(3)先explain的好处是可以为了利用索引,增加更多查询限制条件

7.使用join时,where条件尽量使用充分利用同一表上的索引

(1)如 select t1.a,t2.b * from t1,t2 and t1.a=t2.a and t1.b=123 and t2.c= 4 ,如果t1.c与t2.c字段相同,那么t1上的索引(b,c)就只用到b了。此时如果把where条件中的t2.c=4改成t1.c=4,那么可以用到完整的索引

(2)这种情况可能会在字段冗余设计(反范式)时出现

(3)正确选取inner join和left join

8.少用子查询,改用join

小于5.6版本时,子查询效率很低,不像Oracle那样先计算子查询后外层查询。5.6版本开始得到优化

9.考虑使用union all,少使用union,注意考虑去重

(1)union all不去重,而少了排序操作,速度相对比union要快,如果没有去重的需求,优先使用union all

(2)如果UNION结果中有使用limit,在2个子SQL可能有许多返回值的情况下,各自加上limit。如果还有order by,请找DBA。

10.IN的内容尽量不超过200个

超过500个值使用批量的方式,否则一次执行会影响数据库的并发能力,因为单SQL只能且一直占用单CPU,而且可能导致主从复制延迟

11.拒绝大事务

比如在一个事务里进行多个select,多个update,如果是高频事务,会严重影响MySQL并发能力,因为事务持有的锁等资源只在事务rollback/commit时才能释放。但同时也要权衡数据写入的一致性。

12.避免使用is null, is not null这样的比较

13.order by .. limit

这种查询更多的是通过索引去优化,但order by的字段有讲究,比如主键id与f_time都是顺序递增,那就可以考虑order by id而非 f_time 。

14.c1 < a order by c2

与上面不同的是,order by之前有个范围查询,由前面的内容可知,用不到类似(c1,c2)的索引,但是可以利用(c2,c1)索引。另外还可以改写成join的方式实现。

15.分页优化

建议使用合理的分页方式以提高分页效率,大页情况下不使用跳跃式分页

假如有类似下面分页语句:

SELECT FROM table1 ORDER BY ftime DESC LIMIT 10000,10;

这种分页方式会导致大量的io,因为MySQL使用的是提前读取策略。

推荐分页方式:

SELECT FROM table1 WHERE ftime < last_time ORDER BY ftime DESC LIMIT 10

即传入上一次分页的界值

SELECT * FROM table as t1 inner JOIN (SELECT id FROM table ORDER BY time LIMIT 10000,10) as t2 ON t1.id=t2.id

16.count计数

(1)首先count()、count(1)、count(col1)是有区别的,count()表示整个结果集有多少条记录,count(1)表示结果集里以primary key统计数量,绝大多数情况下count()与count(1)效果一样的,但count(col1)表示的是结果集里 col1 列 NOT null 的记录数。优先采用count()

(2)大数据量count是消耗资源的操作,甚至会拖慢整个库,查询性能问题无法解决的,应从产品设计上进行重构。例如当频繁需要count的查询,考虑使用汇总表

(3)遇到distinct的情况,group by方式可能效率更高。

17.delete,update语句改成select再explain

select最多导致数据库慢,写操作才是锁表的罪魁祸首

18.减少与数据库交互的次数,尽量采用批量SQL语句

(1)INSERT ... ON DUPLICATE KEY UPDATE ...,插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE,如果不重复则直接插入,影响1行。

(2)REPLACE INTO类似,但它是冲突时删除旧行。INSERT IGNORE相反,保留旧行,丢弃要插入的新行。

(3)INSERT INTO VALUES(),(),(),合并插入。

19.杜绝危险SQL

(1)去掉where 1=1 这样无意义或恒真的条件,如果遇到update/delete或遭到sql注入就恐怖了

(2)SQL中不允许出现DDL语句。一般也不给予create/alter这类权限,但阿里云RDS只区分读写用户

六. 行为规范

(1)不允许在DBA不知情的情况下导现网数据

(2)大批量更新,如修复数据,避开高峰期,并通知DBA。直接执行sql的由运维或DBA同事操作

(3)及时处理已下线业务的SQL

(4)复杂sql上线审核

因为目前还没有SQL审查机制,复杂sql如多表join,count,group by,主动上报DBA评估。

(5)重要项目的数据库方案选型和设计必须提前通知DBA参与

总结

以上就是本文的全部内容,希望对大家有所帮助。

感谢阅读,希望朋友们对本站多多支持!

原文链接:http://www.cnblogs.com/erisen/p/5970190.html

标签:

相关文章

热门资讯

2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全 2019-12-26
歪歪漫画vip账号共享2020_yy漫画免费账号密码共享
歪歪漫画vip账号共享2020_yy漫画免费账号密码共享 2020-04-07
Intellij idea2020永久破解,亲测可用!!!
Intellij idea2020永久破解,亲测可用!!! 2020-07-29
男生常说24816是什么意思?女生说13579是什么意思?
男生常说24816是什么意思?女生说13579是什么意思? 2019-09-17
沙雕群名称大全2019精选 今年最火的微信群名沙雕有创意
沙雕群名称大全2019精选 今年最火的微信群名沙雕有创意 2019-07-07
返回顶部