一、DBA操作规范
1、统一使用utf8mb4字符集和utf8mb4_general_ci字符排序规则，utf8mb4字符集是utf8的超集。
2、使用InnoDB存储引擎。支持事务，行级锁，更好的安全恢复性，高并发下性能更好。5.5+版本默认就是。
3、隔离级别使用READ-COMMITTED（二进制日志格式使用ROW）。
4、涉及业务上的数据修改/删除，在得到业务方、相关领导的邮件批准后方可执行，执行前做好备份，方便回滚。
5、所有上线需求必须走工单系统（起码要走邮件审批流程），口头通知视为无效。
6、在对大表做表结构变更时，会造成锁表，并会造成从库延迟，从而影响线上业务，必须在凌晨业务低峰期执行，并统一用pt工具变更。
使用范例：
#pt-online-schema-change --alter="add index IX_id_no(id_no)" \
--no-check-replication-filters --recursion-method=none --user=dba \
--password=123456 D=test,t=t1 --execute
对于MongoDB创建索引要在后台创建，避免锁表。
使用范例：
db.t1.createIndex({idCardNum:1},{background:1})
7、所有线上业务库必须搭建MHA高可用架构，避免单点问题。
8、删除匿名账号。
mysql -e "delete from mysql.user where user='';flush privileges;"
9、汇总库开启audit审计日志功能，出现问题时方可追溯。（参考本人文章：https://www.jianshu.com/p/a0e0aec3cb6f）
10、禁止一个MySQL实例存放多个业务数据库，会造成业务耦合性过高，一旦出现问题会殃及池鱼，增加定位故障问题的难度。通常采用多实例解决，一个实例一个业务库，互不干扰。
二、基本规范
1、禁止在数据库中存储明文密码。
2、InnoDB表避免使用COUNT(*)操作。
因内部没有计数器，需要一行一行累加计算，计数统计实时要求较强可以使用memcache或者Redis。如果非要使用最好使用count(主键)。
3、慎用union或union all，特别是合并后再进行排序操作的SQL，碰到数据量比较大时，进行优化会非常困难。
4、所有表和字段都需要添加中文注释。方便他人，方便自己。
5、不在数据库中存储图片、文件等大数据。
图片、文件更适合于类似GFS分布式文件系统，数据库里存放超链接即可。
6、禁止使用存储过程和函数、视图、触发器、外键约束和事件，如有用到要报备DBA。
应尽量把计算放到业务层，而不是数据库层面；因为这些功能的使用增加了数据库的维护难度，另外并发量大的情况下会拖死数据库，而在业务层面计算则可以横向扩展服务器。外键用来保护参照完整性，可在业务端实现，外键会导致父表和子表之间耦合，十分影响SQL性能，会出现过多的锁等待，甚至会造成死锁。
7、对事务一致性要求不高的业务，如日志表等，优先选择存入MongoDB。其自身支持的sharding分片功能，增强了横向扩展的能力，开发不用过多调整业务代码。
8、禁止使用保留字。
要保证你的字段名没有和保留字、数据库系统或者常用访问方法冲突，比如，写的一个ODBC连接程序里有个表，其中就用了DESC作为说明字段名。后果可想而知！DESC 是DESCENDING缩写后的保留词。表里的一个SELECT *语句倒是能用，但我得到的却是一大堆毫无用处的信息。
9、命名规范。
禁止使用中文命名。命名都不得超过30个字符的系统限制，变量名的长度限制为29(不包括标识字符@)。命名都采用英文字符，每个单词的首个字母尽量要大写。
对象命名，如存储过程以sp_为前缀；触发器以tr_为前缀；函数以fn_为前缀；主键以pk_为前缀；索引以idx_为前缀。
三、行为规范
1、禁止在主库上执行后台管理和统计类的SQL
这种复杂类的SQL会造成CPU的升高，进而会影响业务。
2、批量清洗数据，需要开发和DBA共同进行审查。应避开业务高峰期时段执行，并在执行过程中观察服务状态。
3、促销活动等应提前与DBA当面沟通，进行评估。比如提前一周增加机器内存或扩展架构，防止DB出现性能瓶颈。
4、禁止在线上做数据库压力测试。
四、库表设计规范
1、表名、列名、索引名的长度不大于64个字节。
2、如果建立的是临时表，则必须要以tmp为前缀。
3、单实例表个数控制在3w以内，单表行数1000w~2000w内。
4、大表在创建初期，应预留一些扩展字段。
比如2个varchar，2个int，自行根据业务选择组合。避免后面表变大之后，导致增加字段操作引起问题。
5、表必须有主键，例如自增主键。
这样可以保证数据行是按照顺序写入，对于SAS传统机械式硬盘写入性能更好，根据主键做关联查询的性能也会更好，并且还方便了数据仓库抽取数据。从性能的角度来说，使用UUID作为主键是个最不好的习惯，它会使插入数据变得随机。
6、慎用分区表。
分区表的好处是对于开发同学来说，不用修改代码，通过后端DB的设置，比如对于时间字段做拆分，就可以轻松实现表的拆分。但这里面涉及一个问题，查询的字段必须是分区键，否则会遍历所有的分区表，并不会带来性能上的提升。所以应采用切表的形式做拆分，如程序上需要对历史数据做查询，可通过union all的方式关联查询。另外随着时间的推移，历史数据表不再需要，可方便备份、清理。
五、字段设计规范
1、对货币等对精度敏感的数据，要用DECIMAL代替FLOAT、DOUBLE表示或存储。
2、使用TINYINT来代替ENUM类型。
采用enum枚举类型，会存在扩展的问题，例如用户在线状态，如果此时需增加：5表示请勿打扰、6表示开会中、7表示隐身对好友可见，那么增加新的ENUM值就要做DDL修改表结构操作了。
3、字段长度尽量按实际需要进行分配，不要随意分配一个很大的容量。
选择字段的一般原则是保小不保大，能用占用字节少的字段就不用大字段。比如主键，强烈建议用int整型，不用uuid，为什么？省空间啊。空间是什么？空间就是效率！按4个字节和按32个字节定位一条记录，谁快谁慢太明显了。涉及几个表做join时，效果就更明显了。更小的字段类型占用的内存更少，占用的磁盘空间和磁盘IO也会更少，而且还会占用更少的带宽。
有不少开发人员在设计表字段时，只要是针对数值类型的全部用int，但这不一定合适，比如用户的年龄，一般来说，年龄大都在1~150岁之间，长度只有3，那么用int就不适合了，可以用tinyint代替。又比如用户在线状态，0表示离线、1表示在线、2表示离开、3表示忙碌、4表示隐身等，其实类似这样的情况，采用tinyint完全可以满足需要，int占用的是4字节，而tinyint才占用1个字节。
int整型有符号（signed）最大值是2147483647，而无符号（unsigned）最大值是4294967295，如果你的需求没有存储负数，那么建议改成无符号（unsigned），可以增加int存储范围。
int(10)和int(1)没有什么区别，10和1仅是宽度而已，在设置了zerofill扩展属性的时候有用。
4、字段定义为NOT NULL要提供默认值。
从应用层角度来看，可以减少程序判断代码，比如你要查询一条记录，如果没默认值，你是不是得先判断该字段对应变量是否被设置，如果没有，你得通过java把该变量置为''或者0，如果设了默认值，判断条件可直接略过。
NULL值很难进行查询优化，它会使索引统计更加复杂，还需要MySQL内部进行特殊处理。
5、慎用TEXT、BLOB类型。
增加存储空间的占用，读取速度慢。如果要用，则这个列不能设置为NOT NULL。
6、如果是timestamp类型的，则要必须指定默认值。
7、除了大字段列之外，其余字段都必须设置not null和default值。
字符型定义为default ”，数值型定义为0，浮点型定义为0.00
六、索引规范
1、索引不是越多越好，按实际需要进行创建。
索引是一把双刃剑，它可以提高查询效率但也会降低插入和更新效率并占用磁盘空间。适当的索引对性能至关重要，而且在MySQL中使用索引它的速度是极快的。遗憾的是，索引也有相应的开销。每次更新表时（如INSERT、UPDATEH或DELETE），MySQL也要更新索引，这样索引就增加了对表的更新操作的开销。
2、不在索引列进行数学运算和函数运算。
会无法使用索引，导致全表扫描。
3、不在低基数列上建立索引，例如‘性别’。
4、不使用%前缀的模糊查询，如like ‘%xxx’。无法使用索引，导致全表扫描。
5、不使用反向查询，如!= 、not in 、not like等。无法使用索引，导致全表扫描。
6、避免冗余或无效索引。
组合索引idx_a_b_c(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)，那么索引 (a) 、(a,b) 就是多余的。
7、注意组合索引的顺序，最左原则，根据需要调整索引字段顺序。
七、SQL设计规范
1、不使用SELECT *，只取必要的字段。
因为消耗CPU和IO、消耗网络带宽，无法使用覆盖索引。
2、用IN来替换OR。
低效查询
SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;
----->
高效查询
SELECT * FROM t WHERE LOC_IN IN (10,20,30);
3、避免数据类型不一致。会带来隐式转换问题，并且无法使用索引
SELECT * FROM t WHERE id = '19';
----->
SELECT * FROM t WHERE id = 19;
4、减少与数据库的交互次数。
低效查询
INSERT INTO t (id, name) VALUES(1,'Bea');
INSERT INTO t (id, name) VALUES(2,'Belle');
INSERT INTO t (id, name) VALUES(3,'Bernice');
----->
高效查询
INSERT INTO t (id, name) VALUES(1,'Bea'), (2,'Belle'),(3,'Bernice');
Update … where id in (1,2,3,4);
Alter table tbl_name add column col1, add column col2;
5、拒绝大事务、大SQL，要拆分成小事务、小SQL。
低效查询
SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id = tag.id
JOIN post ON tag_post.post_id = post.id
WHERE tag.tag = 'mysql';
----->
高效查询
SELECT * FROM tag WHERE tag = 'mysql'
SELECT * FROM tag_post WHERE tag_id = 1234
SELECT * FROM post WHERE post_id in (123, 456, 567, 9098, 8904);
6、禁止使用order by rand()
低效查询
SELECT * FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 4;
---->
高效查询
SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4;
一致并建立索引
7、关联查询字段的类型及字符集要一致，并建立索引
8、线上业务禁止大表JOIN查询，或子查询，会产生临时表，消耗较多内存和CPU。
参考：http://mp.weixin.qq.com/s/PtIaqAjs298uH6edEYb2xg
网友评论