Mysql使用规范及建议

MySQL数据库使用规范
一.建表规约
1.【强制】表达是与否概念的字段,必须使用is_xxx的方式命名,数据类型是unsigned tinyint
(1表示是,0表示否)
说明:任何字段如果为非负数,必须是unsigned
正例:表达逻辑删除的字段名is_deleted,,1表示删除,0表示未删除
2.【强制】表名、字段名必须使用小写字母或数字,禁止出现数字开头,禁止两个下划线中间出现数字。数据库字段名的修改代价很大,因为无法进行预发布,所以字段名称需要慎重考虑。
正例:getter_admin,task_config,level3_name
反例:GetterAdmin,taskConfig,level_3_name
3.【强制】表名不使复数名词。
说明:表名应该仅仅表示表里面的实体内容,不应该表示实体数量。
4.【强制】禁用保留字,如desc、range、match、delayed等,请参考MySQL官方保留字。
5.【强制】主键索引名为pk_字段名;唯一索引名为uk_字段名;普通索引名为idx_字段名。
说明:pk_即primary key;uk_即unique key;idx_即index的简称。
6.【强制】小数类型为decimal,禁止使用float和double。
说明:float和double在存储的时候,存在精度损失的问题,很可能在值比较的时候得到不正确的结果。如果存储的数据范围超过decimal的范围,建议将数据拆成整数和小数分开存储。
7.【强制】varchar是可变长字符串,不预先分配存储空间,长度不要超过5000,如果存储长度大于此值,定义字段类型为text,独立出来一张表,用主键来对应,避免影响其他字段索引效率。
8.【强制】如果存储的字符串长度几乎相等,使用char定长字符串类型。
9.【强制】表必备三字段:id,create_time,update_time,且类型均为datetime类型
10.【推荐】表的名称最好是加上“业务名称_表的作用”。
11.【推荐】库名与应用名称尽量保持一致。
12.【推荐】如果修改字段含义或对字段表示的状态追加时,需要及时更新字段注释。
13.【推荐】字段允许适当冗余,以提高查询性能,但必须考虑数据一致。冗余字段应遵循:
1)不是频繁修改的字段。
2)不是varchar超长字段,更不能是text字段。
正例:商品类目名称使用频率高,字段长度短,名称基本一成不变,可在相关联的表中冗余存储类目名称,避免关联查询。
14.【推荐】单表行数超过500万行或者单表容量超过2GB,才推荐进行分库分表。
说明:如果预计三年后的数据量根本达不到这个级别,请不要在创建表时就分库分表。
15.【参考】合适的存储长度,不但节约数据库表空间、节约索引存储,更重要的是提升检索速度。

二.索引规约
1.【强制】业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引。
说明:不要以为唯一索引影响了insert速度,这个速度损耗可以忽略,但提高查询速度是明显的;另外,即使在应用层做了非常完善的校验控制,只要没有唯一索引,根据墨菲定律,必然有脏数据产生。
2.【强制】超过三个表禁止join。需要join的字段,数据类型必须绝对一致;多表关联查询时,保证被关联的字段需要有索引。
说明:即使双表join也要注意表索引、SQL性能。
3.【强制】在varchar字段上建立索引时,必须指定索引长度,没必要对全字段建立索
引,根据实际文本区分度决定索引长度即可。
说明:索引长度与区分度是一对矛盾体,一般对字符串类型数据,长度为20的索引区分度会高达90%以上,可以使用count(distinct left(列名,索引长度))/count(*)的区分度来确定。
4.【强制】页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决。
说明:索引文件具有B-tree的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引。
5.【推荐】如果有order by的场景,请注意利用索引的有序性。order by 最后的字段是组合索引的一部分,并且放在索引组合顺序的最后,避免出现file_sort的情况,影响查询性能。
正例:where a=? and b=? order by c; 索引:a_b_c
反例:索引中有范围查找,那么索引有序性无法利用,如 where a>10 order by b; 索引
a_b无法排序。
6.【推荐】利用覆盖索引来进行查询操作,避免回表。
说明:如果一本书需要知道第11章是什么标题,会翻开第11章对应的那一页吗?目录浏览一下就好,这个目录就是起覆盖索引的作用。
正例:能够建立索引的种类:主键索引、唯一索引、普通索引,而覆盖索引是一种查询的效果,利用explain的结果,extra列会出现:using index。
7.【推荐】利用延迟关联或者子查询优化超多分页场景。
说明:MySQL并不是跳过offset行,而是取offset+N行,然后返回放弃前offset行,返回N行,那当offset特别大的时候,效率就非常的低下,要么控制返回的总页数,要么对超过特定阈值的页数进行SQL改写。
正例:先快速定位需要获取的id段,然后在关联:
select a.* from 表 1 a,(select id from 表 1 where 条件 limit 100000,20)b where a.id=b.id
8.【推荐】SQL性能优化的目标:至少要达到range级别,要求是ref级别,如果可以是consts最好。
说明:
1)consts单表中最多只有一个匹配行(主键或者唯一索引),在优化阶段即可读取到数据。
2)ref指的是使用普通的索引(normal index)。
3)range对索引进行范围检索。
反例:explain表的结果,type=index,索引物理文件全扫描,速度非常慢,这个index级别比较range还低,与全表扫描是小巫见大巫。
9.【推荐】建组合索引的时候,区分度最高的在左边。
正例:如果where a=? and b=?, 通过a=?查询几乎接近唯一值,那么只需要单建idx_a索引即可。
说明:存在非等号和等号混合判断条件时,在建索引时,请把等号条件的列前置。如
where a>? and b=? 那么及时a的区分度更高,也必须把b放在索引的最前列。
10.【推荐】防止因为字段类型不同造成的隐式转换,导致索引失效。
11.【参考】创建索引避免有如下极端误解:
1)宁滥勿缺。误认为一个查询就需要建一个索引。
2)宁缺勿滥。误认为索引会消耗空间、严重拖慢更新和新增速度。
3)抵制唯一索引。误认为业务的唯一性一律需要在应用层通过“先查后插”方式能解决。

三.SQL语句
1.【强制】不要使用count(列名)或count(常量)来替代count(*),count(*)是SQL92定义的
标准统计行数的语法,跟数据库无关,跟NULL和非NULL无关。
说明:count(*)会统计值为NULL的行,而count(列名)不会统计此列值为NULL值的行。
2.【强制】count(distinct col)计算该列除NULL之外的不重复行数,注意count(distict
col1,col2)如果其中一列全为NULL,那么即使另一列有不同的值,也返回为0。
3.【强制】当某一列的值全是NULL时,count(col)的返回结果为0,但sum(col)的返回结
果为NULL,因此使用sum()时需要注意NPE问题。
正例:可以使用如下方式来避免sum的NPE问题:select if (isnull(sum(g)), 0, sum(g)) from table;
4.【强制】使用ISNULL()来判断是否为NULL值。注意:NULL与任何值的直接比较都为NULL。
说明:
1)NULL<>NULL的返回结果是NULL,而不是false。
2)NULL=NULL的返回结果是NULL,而不是true。
3)NULL<>1的返回结果是NULL,而不是true。
5.【强制】在代码中写分页查询逻辑时,若count为0应直接返回,避免执行后面的分页语句。
6.【强制】不得使用外键与级联,一切外键概念必须在应用层解决。
说明:(概念解释)学生表中的student_id是主键,那么成绩表中的student_id则为外键。如果更新学生表中的student_id,同时触发成绩表中的student_id更新,则为级联更新。外键与级联更新适用于单机低并发,不适合分布式、高并发集群;级联更新是强阻塞,存在数据库更新风暴的风险;外键影响数据库的插入速度。
7.【强制】禁止使用存储过程,存储过程难以调试和扩展,更没有移植性。
8.【强制】数据订正时,删除和修改记录时,要先select,避免出现误删除,确认无误才能执行更新语句。
9.【推荐】in操作能避免则避免,若实在避免不了,需要仔细评估in后边的集合元素数量,控制在1000个之内。
10.【参考】如果有全球化需要,所有的字符存储与表示,均以utf-8编码,注意字符统计函数的区别。
说明:
select length(“轻松工作”);返回为12
select character_length(“轻松工作”);返回为4
如果要使用表情,那么使用utfmb4来进行存储,注意它与utf-8编码的区别。
11.【参考】 TRUNCATE TABLE 比 DELETE速度快,且使用的系统和是去日志资源少,但TRUNCATE无事务且不触发trigger,有可能造成事故,故不建议在开发代码中使用此语句。
说明:TRUNCATE TABLE 在功能上与不带WHERE子句的DELETE语句相同。

四.值得推荐的一些建议:
库表规范:
1. 库名、表名、字段名必须使用小写字母,库名须以有业务相关开头,如pt_,ns_等,并采用下划线分割
2. 所有mysql表必须为Innodb引擎,加表语句禁止出现DROP TABLE IF EXISTS 判断字句
3. 表字符集统一使用UTF8或更长支持的的utf8mb4,表和字段需要有注释
4. 表字段必须有默认值+ NOT NULL,int等数字类型默认值推荐给0,varchar等字符类型默认值给''
5. 控制单表字段数量,字段上限30左右
6. 表字段不得使用text blob等大字段,如有特殊需要,需要负责人进行沟通
7. 表单行总宽度不得超过8000字节,单表数据量控制在5000W行以内
8. 表主键需要设置为 无业务相关的自增长的int,如写入频繁则设置成bigint
9. 不使用强制外键约束
10. 不建议使用存储过程,视图,禁止使用触发器,事件
11. 数据库中不允许存储明文密码
12. 表一般不做分区表,如有需求需要提前与DBA沟通
13. 数据库服务器一般只做数据库使用,不允许跑其他应用

索引规范:

1. 任何新的select,update,delete上线,都要先explain,看索引使用情况
2. 尽量避免extra列出现:Using File Sort,Using Temporary,rows超过1000的要谨慎上线。
3. explain解读
type:ALL, index, range, ref, eq_ref, const, system, NULL(从左到右,性能从差到好)
possible_keys:指出MySQL能使用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用
key:表示MySQL实际决定使用的键(索引)如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX
ref:表示选择 key 列上的索引,哪些列或常量被用于查找索引列上的值
rows:根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数
Extra:
Using temporary:表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询
Using filesort:MySQL中无法利用索引完成的排序操作称为“文件排序”
Using index:表示使用索引,如果只有 Using index,说明他没有查询到数据表,只用索引表就完成了这个查询,这个叫覆盖索引。如果同时出现Using where,代表使用索引来查找读取记录, 也是可以用到索引的,但是需要查询到数据表。
Using where:表示条件查询,如果不读取表的所有数据,或不是仅仅通过索引就可以获取所有需要的数据,则会出现 Using where。如果type列是ALL或index,而没有出现该信息,则你有可能在执行错误的查询:返回所有数据。

4. 索引个数限制
索引是双刃剑,会增加维护负担,增大IO压力,索引占用空间是成倍增加的
单张表的索引数量控制在5个以内,或不超过表字段个数的20%。若单张表多个字段在查询需求上都要单独用到索引,需要经过DBA评估。
避免冗余索引
索引尽量建在选择性高的列上
最左前缀原则
合理使用覆盖索引减少IO
尽量不要在频繁更新的列上创建索引
业务查询需要有索引,需要提前规划好索引的覆盖程度,可以找DBA协助相关策略
索引命名需以ix_ 开头,下划线后跟上字段,以_分隔,唯一索引以ux_ 开头

 

数据库权限规范

1. 一个帐号对应一个数据库,帐号有增删改查权限,如有DDL需求,再做权限增加
2. 生产环境 禁止直接从办公网访问,如有查询需求,可以提供有只读权限的phpmyadmin使用

SQL使用规范

1. 杜绝直接 SELECT * 读取全部字段,即使需要所有字段。减少网络带宽消耗,能有效利用覆盖索引,表结构变更对程序基本无影响
2. 能确定返回结果只有一条时,使用 limit 1。在保证数据不会有误的前提下,能确定结果集数量时,多使用limit,尽快的返回结果。
3. 小心隐式类型转换
转换规则
1. 两个参数至少有一个是 NULL 时,比较的结果也是 NULL,例外是使用 <=> 对两个 NULL 做比较时会返回 1,这两种情况都不需要做类型转换
2. 两个参数都是字符串,会按照字符串来比较,不做类型转换
3. 两个参数都是整数,按照整数来比较,不做类型转换
4. 十六进制的值和非数字做比较时,会被当做二进制串
5. 有一个参数是 TIMESTAMP 或 DATETIME,并且另外一个参数是常量,常量会被转换为 timestamp
6. 有一个参数是 decimal 类型,如果另外一个参数是 decimal 或者整数,会将整数转换为 decimal 后进行比较,如果另外一个参数是浮点数,则会把 decimal 转换为浮点数进行比较
7. 所有其他情况下,两个参数都会被转换为浮点数再进行比较。
8. 如果一个索引建立在string类型上,如果这个字段和一个int类型的值比较,符合第 g 条。如f_phone定义的类型是varchar,但where使用f_phone in (098890),两个参数都会被当成成浮点型。发生这个隐式转换并不是最糟的,最糟的是string转换后的float,mysql无法使用索引,这才导致了性能问题。如果是 f_user_id = ‘1234567’ 的情况,符合第 b 条,直接把数字当字符串比较。

4. 禁止在where条件列上使用函数。会导致索引失效,如lower(email),f_qq % 4。可放到右边的常量上计算。返回小结果集不是很大的情况下,可以对返回列使用函数,简化程序开发
5. 使用like模糊匹配,%不要放首位。会导致索引失效,有这种搜索需求是,考虑其它方案,如sphinx全文搜索
6. 涉及到复杂sql时,务必先参考已有索引设计,先explain。先explain的好处是可以为了利用索引,增加更多查询限制条件。
7. 简单SQL拆分,不以代码处理复杂为由。比如 OR 条件: f_phone=’10000’ or f_mobile=’10000’,两个字段各自有索引,但只能用到其中一个。可以拆分成2个sql,或者union all。
8. 使用join时,where条件尽量使用充分利用同一表上的索引
- 如 `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,那么可以用到完整的索引。这种情况可能会在字段冗余设计(反范式)时出现

9. 正确选取inner join和left join。少用子查询,改用join。小于5.6版本时,子查询效率很低,不像Oracle那样先计算子查询后外层查询。5.6版本开始得到优化
10. 考虑使用union all,少使用union,注意考虑去重。union all不去重,而少了排序操作,速度相对比union要快,如果没有去重的需求,优先使用union all。如果UNION结果中有使用limit,在2个子SQL可能有许多返回值的情况下,各自加上limit。如果还有order by,请找DBA。
11. IN的内容尽量不超过200个。超过500个值使用批量的方式,否则一次执行会影响数据库的并发能力,因为单SQL只能且一直占用单CPU,而且可能导致主从复制延迟
12. 拒绝大事务。比如在一个事务里进行多个select,多个update,如果是高频事务,会严重影响MySQL并发能力,因为事务持有的锁等资源只在事务rollback/commit时才能释放。但同时也要权衡数据写入的一致性。
13. 避免使用is null, is not null这样的比较
14. order by .. limit。这种查询更多的是通过索引去优化,但order by的字段有讲究,比如主键id与f_time都是顺序递增,那就可以考虑order by id而非 f_time 。
15. c1 < a order by c2。与上面不同的是,order by之前有个范围查询,由前面的内容可知,用不到类似(c1,c2)的索引,但是可以利用(c2,c1)索引。另外还可以改写成join的方式实现。

16. 分页优化
- 建议使用合理的分页方式以提高分页效率,大页情况下不使用跳跃式分页
- 假如有类似下面分页语句:
`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`

17. count计数。首先count(*)、count(1)、count(col1)是有区别的,count(*)表示整个结果集有多少条记录,count(1)表示结果集里以primary key统计数量,绝大多数情况下count(*)与count(1)效果一样的,但count(col1)表示的是结果集里 col1 列 NOT null 的记录数。优先采用count(*)。大数据量count是消耗资源的操作,甚至会拖慢整个库,查询性能问题无法解决的,应从产品设计上进行重构。例如当频繁需要count的查询,考虑使用汇总表
18. 遇到distinct的情况,group by方式可能效率更高。
19. delete,update语句改成select再explain。select最多导致数据库慢,写操作才是锁表的罪魁祸首
20. 减少与数据库交互的次数,尽量采用批量SQL语句。
- INSERT ... ON DUPLICATE KEY UPDATE ...,插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE,如果不重复则直接插入,影响1行。
- REPLACE INTO类似,但它是冲突时删除旧行。
- INSERT IGNORE相反,保留旧行,丢弃要插入的新行。
- INSERT INTO VALUES(),(),(),合并插入。

21. 杜绝危险SQL
22. 去掉where 1=1 这样无意义或恒真的条件,对SQL进行参数化使用。
23. SQL中不允许出现DDL语句。一般也不给予create/alter这类权限。

 

posted on 2019-07-09 11:16  Ryanyanglibin  阅读(...)  评论(...编辑  收藏

导航