星空

人生不仅有眼前的苟且,还有诗和远方.

0%

MYSQL线上审核规范

命名规范

Ø库名、表名、字段名,使用小写字母

Ø库名、表名、字段名,不要超过30个字符

Ø库名、表名、字段名,尽量见名知意,使用下划线分割

Ø库名、表名、字段名,禁止使用MySQL保留字

Ø临时库、表名,必须以tmp为前缀,以日期为后缀,例如tmp_product_20151229

Ø备份表、表名,必须以日期为后缀,例如produce_bak_20151229

库表设计

Ø表必须有主键,使用bigint unsigned类型auto_increment属性

Ø除主键外的其他字段都必须有注释,所有表都必须有说明

Ø最多更改和查询的字段放在基础表内,方便完整载入内存

Ø访问频率低的或大字段放到扩展表里,分离冷热数据

Ø多张关联表之间,适当的冗余字段,可以减少JOIN查询

Ø如果预计数据量较大,预先制定分表策略,如按日期拆分或按某键值取模分表

Ø尽量不用分区表,如需使用请与DBA沟通

字段设计

Ø只有主键使用UNSIGNED属性,其他数值列不要使用,因为计算出现负数时会报错

Ø当字符串较短,或数据频繁更新时,可以使用CHAR(N),N表示字符数而非字节数

Ø当字符串长度可预见时,可以使用VARCHAR(N),N表示字符数而非字节数

Ø使用DECIMAL代替FLOAT和DOUBLE,以存储精确浮点数,例如支付相关数据

Ø使用INT系类型代替ENUM类型,前者只要自己定义,后者却要修改表

Ø使用INT UNSIGNED存储IPV4,inet_aton()和inet_ntoa()用于IPV4与INT互转

Ø尽可能不使用TEXT类型,禁止使用BLOB类型

Ø所有字段必须定义为NOT NULL,定义为default 0或default ‘’,NULL可能在计数(count)和匹配(is null)时引起歧义

索引设计

Ø如多个字段组合有唯一性需要,可以创建唯一索引

Ø不在低基数(低筛选度)的列上建立索引,例如“性别”

Ø一条SQL只会用到一个索引,无用的索引越多,写入性能越差

Ø禁止冗余索引,如已有(a,b)索引,可以删除(a)索引

Ø合理创建组合索引,(a,b,c)相当于(a)、(a,b)、(a,b,c)

Ø组合索引的组成字段数尽量不超过3个

Ø组合索引中,区分度大(高筛选度)的字段放在最前

Ø尽可能利用索引完成排序,即排序的字段在索引里,且不使用降序排序

Ø适度将组合索引提升为覆盖索引,避免回表,减少IO

Ø对较长字符串可使用前缀索引,前缀索引长度由数据区分度确定

Ø禁止使用外键,防止死锁,避免隐藏的数据逻辑

Ø数据扫描过多,如所有索引的过虑性不佳,会放弃使用索引

Ø仅使用最有效的过滤条件,索引不是越长越好,where条件并不是越多越好

Ø普通索引按照“idx_表名_字段名称”进行命名,例如idx_table1_name

Ø唯一索引按照“uniq_表名_字段名称”进行命名,例如uniq_table1_name

Ø索引名必须全部使用小写,过长的字段名可以采⽤缩写形式,例如idx_t1_name_age
SQL编写

Ø尽量使用主键,且不要修改主键的值

Ø只select需要的字段,禁止使用select *

Ø分批获取大量数据时,禁止大偏移量的limit M,N语句,使用主键游标 where PK>… limit N

Ø同字段OR条件,用IN代替,包含的值个数应少于300个

Ø禁止隐式转换,数值类型禁止加引号,字符和日期类型必须加引号

Ø减少与数据库交互次数,尽量采用批量递交、块插入和缓存(memcache)

Ø使用prepared statement批量递交语句,可以提升性能,且避免SQL注⼊

Ø注意UNION ALL和UNION的区别,UNION默认有去重效果

Ø统计行数时,使用COUNT(*)或COUNT(1),不要使用count(字段名),会忽略值为NULL的行

Ø写入语句中禁止出现结果不确定的函数,如sysdate()、rand()、current_user()等

ØINSERT语句必须指定字段列表,禁止使用 INSERT INTO xxx values()

Ø执行频率高的SQL和重要功能的SQL,都必须能有索引可用

Ø禁止使用左%模糊匹配,例如like ‘%abc’,无法用到索引

Ø禁止使用反向匹配,例如 not in、!=、not like,无法用到索引

Ø禁止在SQL中进行算术和函数计算,应放置到应用服务器端

Ø保证每张表的JOIN列的数据类型相同,并且都建立了索引

Ø禁止使用order by rand()实现乱序效果,会导致CPU过高

Ø禁止JOIN和子查询,如无法避免,应尽可能进行优化

Ø适当增加冗余字段,避免关联JOIN查询

Ø可以拆分复杂的JOIN为多个小SQL,避免大语句

Ø尽可能减少Join语句中的循环总次数,就是让驱动表的结果集尽可能的小,永远用小结果集驱动大的结果集

Ø优先优化内层循环,内层循环是循环中执行次数最多的,每次循环节约很小的资源,在整个循环中就能节约很大的
资源

Ø子查询只允许返回主键和必须的字段,不允许select *

Ø禁止单条SQL语句同时更新多个表,拆分成多条SQL,放在一个事务里

Ø程序应有捕获SQL异常的处理机制,必要时通过rollback显式回滚

Ø严禁大事务,会锁住更多的资源,引发更多的等待和竞争

Ø不同事务对同一批表的操作,要前后顺序一致