一. 命名规范

库名、表名、字段名必须使用小写字母,并采用下划线分割
为了统一规范, 库名、表名、字段名使用小写字母,禁用关键字(index,order等)。

前缀命令规范
视图以view_开头,事件以event_开头,触发器以trig_开头,存储过程以proc_开头,函数以func_开头,应用上面禁用

普通索引以idx_各个列名简称,唯一索引以uk_各个列名简称命名,中间用_隔开。如 idx_col1_col2_col3(col1,col2,col3),如果列过长,用简写

临时表以tmp_实体表名,线上禁用,备份表以bak_日期_实体表名,尽可能备份至HDFS

库名、表名、字段名禁止超过32个字符,需见名知意
库名、表名、字段名支持最多32个字符,但为了统一规范、易于辨识以及减少传输量,禁止超过32个字符,例:业务名称/实体_表作用

按日期时间分表须符合_YYYYMMDD格式
按月或日生成的表,以_YYYYMM[DD]方式命名。

二. 库表基础规范

  • 使用Innodb存储引擎

所有表必须使用默认存储引擎InnoDB。

  • 表编码方式统一使用UTF8或UTF8MB4的

创建索引时utf8比utf8mb4少一个字节,所以明确没有emoj时,尽量使用utf8。

  • 所有表都要添加注释

所有字段必需要有注释,包括表注释,并标注简单意义

  • 控制单表字段数量

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

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

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

双活的表必须禁用自增主键【snowflake】,InnoDB表实际是一棵索引组织表,顺序存储可以提高存取效率,充分利用磁盘空间。还有对一些复杂查询可能需要自连接来优化时需要用到。

需要全局唯一主键时,gmt_create这类的能使用主键排序的尽量使用主键做分页排序,不浪费索引

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

  • 不使用外键

外键严重影响数据库性能,增加表维护复杂度,所以线上禁用外键

  • 字段名称统一

每张表原则上必须含有gmt_create,gmt_modify,gmt_create为创建时间。gmt_modify属性为NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,gmt_create属性为NOT NULL DEFAULT CURRENT_TIMESTAMP

所有相同意义的表采用统一字段名称,并且字段类型一致,这样应用与维护人员见字段知其含义,方便理解

简单单表数据量控制在5000w以内
尽量保证单表存储空间小于10GB

三. 字段规范

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

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

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

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

把text/blob拆到另一个表中,如果只存不读的禁用,建议直接存HDFS

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

字符集为utf8,varchar类型最大只能创建索引为前255字节;字符集为utf8mb4,varchar类型最大只能创建索引为前191字节。所以字段造型时首选int,如果varchar时字段做查询的越小越好,长度不要超过191。

  • int、tinyint、decimal等数字类型定义

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

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

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

使用int存储IPv4地址,可节省 9%+的数据存储空间

使用Decimal 代替float/double存储精确浮点数对于货币、金额这样的类型,可以使用(货币使用bigint+币种+最小单位),如果精度不高,可以使用decimal(9,2)。float默认只能能精确到6位有效数字

  • timestamp与datetime选择

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 要小。

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

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

优先使用timestamp,datetime也没问题,timestamp精度可以到秒后6位,即timestamp(6)

  • where条件里不要对时间列上使用时间函数

  • 建议字段都定义为NOT NULL加上default值

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

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

上面两条如果需要双机房同步,那同步的表新增或更改必须为NULL,再行初始化数据

类型选择原则

Int->char->varchar->text,从左到右性能越差

四. 索引规范

索引个数限制

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

单张表的索引数量控制在5个以内,索引的大小尽量比表小。若单张表多个字段在查询需求上都要单独用到索引,需要经过DBA评估。

避免冗余索引

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

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

索引创建原则

尽可能选择过滤效果好的列上创建索引

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

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

如果某列为sex,数据倾斜比较大,male占比99%,female占比1%,而每次查询都为female,则需要创建该列的索引

最左前缀原则

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

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

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

mysql的optimizer_switch,可以配置icp,mrr等特性。mrr为多个字段做合并,如已存在c1,c2,c3索引,查询时c1=’a’ and/or c2=’b’,则会使用到索引合并,如果大部分查询没有c1和c2同时存在,则建议创建单列索引

前缀索引

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

前缀索引尽量第一列区分度高,这样如果优化器判断时会比较准确 ,A列1000个不同值,B列有100000个不同值,这样建议索引创建时idx_B_A(B,A)

合理使用覆盖索引减少IO

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

不要在频繁更新的列上创建索引

五. SQL设计

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

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

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

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

  • 建议不要使用隐式类型转换

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

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

返回小结果集不是很大的情况下,可以对返回列使用函数,简化程序开发,但尽量不要使用函数索引

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

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

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

如 select t1.a,t2.b * from t1,t2 where 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,那么可以用到完整的索引

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

  • 正确选取inner join和left join

  • 少用或不用子查询,改用join

mysql 5.6版本只支持nest loop,让mysql自已判断需要使用的索引

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

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

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

  • IN的内容尽量不超过200个

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

  • 拒绝大事务

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

  • 避免使用is null, is not null这样的比较
  • 杜绝危险SQL

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

  • SQL中不允许出现DDL语句。一般也不给予create/alter这类权限

六. 行为规范

  • 不允许在DBA不知情的情况下导现网数据
  • 大批量更新,如修复数据,避开高峰期,并通知DBA
  • 及时处理已下线业务的SQL
  • 复杂sql上线审核
  • 因为目前还没有SQL审查机制,复杂sql如多表join,count,group by,主动上报DBA评估。
  • 重要项目的数据库方案选型和设计必须提前通知DBA参与
  • 严格按照现有的SQL规范提交SQL审核,如果有不确认单独和DBA沟通,如SQL较多,建议以脚本文件方式提交
  • 每周定某段时间窗口内提交SQL,其他时间段需要走紧急审批流程,原则上至少提前一天提交脚本审核

我们约定每周周一、周二、周三、周四下午3点到5点间执行SQL,其他时间段需要走紧急流程(流程后续优化),周五提交原则上周一才能执行

  • 严格执行【在线业务,客户直接使用,保证实时性】,【客户olap应用】,【离线、运营相关】,【内部系统】 存放,核心业务与其他业务隔离,业务耦合度高的应用尽可能放在一个实例中
  • 提交SQL时,不允许出现drop table if exists
  • 重要项目涉及数据库DBA必须参加,不然影响项目进度由项目自己负责
  • 表字段名称修改如果有otter同步时,则必须要保证两边数据没写入
  • count(*)无条件,或者扫描行数较大,大于10000行,理论上都是不被允许的,计数功能尽可能交由redis处理
  • 批量操作必须由DBA审核执行
  • 杭州、上海机房双活、双向,自增长主键的使用必须禁用
  • 在线数据库的人工变更(已有表结构变更、数据导入、更新、删除等)须要提前申请,主管审批,并知会运营可能的影响面

七.使用建议

order by .. limit

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

c1 < a order by c2

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

分页优化

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

count计数

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

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

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

delete,update语句改成select再explain

涉及到复杂sql时,务必先参考已有索引设计,先explain
简单SQL拆分,不以代码处理复杂为由。

比如 OR 条件: f_phone=’10000’ or f_mobile=’10000’,两个字段各自有索引,但只能用到其中一个。可以拆分成2个sql,或者union all,如果非要用mysql索引实现,则需要创建两列单列索引,打开index_merge_union后会转义成union。

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

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

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

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

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

隐式类型使用注意事项

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

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

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

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

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

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

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

例:A列为int类型时,A=’123’隐式转换后可以使用索引;A为char类型时,A=123隐式转换后无法使用索引

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

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

explain解读

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

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中无法利用索引完成的排序操作称为“文件排序”