分享MySQL操作规范

背景

主要介绍，日常线上mysql操作，需要注意的情况。包括字段类型，索引，数据表，上线alter table等规范和注意事项。
等级划分：建议，不推荐，禁止
所有规则总有例外，要灵活，辩证看待问题

创建sql规范
DATABASE
1，是否需要使用数据库?
不是所有的东西都需要存在DB中，临时数据，日志数据等不需要，合理使用HBase,memcached,Redis等 不仅仅只有RMDB

2，数据库划分？
目前一般是根据业务逻辑划分数据库。
如果是新业务需求，要合理考虑DB存放mysqld。 目前线上主要3台mysqld，按照业务重要程度来划分：
主库相关：DB33
S级业务数据：DB22  pay, ivr, verify等库
不很重要的数据：DB19 mail,sms,newsletter等库


TABLE

1，存储引擎: InnoDB
没有任何理由不使用InnoDB， 也不建议使用MyISAM，性能上并没有好多少。
其他的存储引擎，更不推荐。
禁止主从库的数据表使用不同的存储引擎

2，CHARSET: utf8
线上大家统一标准，没有特殊理由，禁止使用其他编码标准

3，遵循大段数据和小段数据分开：
如果某个表，有一个大内容字段，和一堆小字段。尤其是小字段的读取频繁、更新频繁跟大内容字段不一致时，建议放置这个大内容字段和小字段段。当然这样也增加了一些复杂度，要全面考虑。
例子： 线上的一张deal表，有频繁更新的curnumber字段，也有非常巨大的字段detail(blob)，导致deal的各种查询都有可能慢查询。
主要原因：1，频繁写block读；2，字段太大，不利于缓存，读写性能都差。

4，遵循冷热数据分开：
建表时，遵循更新频率不同的数据分开。频繁更新的字段应该和不怎么频繁更新的字段分开。这样有利于在流量大时，进行分开控制。更新不频繁的字段可以最限度地使用缓存。

5，预估表数据量：
线上一个表究竟可以存放多少行？取决于该表每一行的大小。通常来说，不要超过千万（如果是类似的日志表，扫表需求小，不要超过亿行）。如果业务逻辑估计会突破亿，就要考虑分表。
当然分表带来的缺点：可能会存放一些容易的索引表信息，有些group 等需求无法实现。要全面考虑。
目前线上库是支持一些分表场景，具体参考相关文档。
例子： creditlog用到了非主键+取模分表； smstask用到了主键+区间分表； mailtask用到了时间分表，它们都是针对特定业务逻辑选择的分表方法

Schema Data Type
1，够用原则
tiny int , small int, medium int, int, big int 表示范围不同，请根据业务需求使用，必要浪费字段
注意：不要太极端，线上推荐3种， tiny, int, big int。 一般status等都是tiny int，，，可能超过42亿的ID(或者手机号码)，用big int, 其他id都是 int。
例子： 历史上，有多次使用smallint，结果超过范围，导致出错的情况。

2，简单原则
int 比 string 更块的比较。 手机号码，IP地址等存储。当然，如果IP存储只是为了人看的统计，记录string更直观一些

3，禁止NULL数据
避免NULL，默认值也不要是NULL。　增加排序复杂

4，char Vs varchar
长度明确 && 短小，建议用char, 其他情况用varchar

5，varchar(xxx) 不建议太慷慨
临时表计算（order, group）会有麻烦
例子：mailtask utm_参数过大，导致自己group by 统计，超过/tmp下空间，导致失败。

6，如果可能，不要用实数。如果要用，请使用DECIAML， 而不是FLOAT, DOUBLE (精确度问题)
decimal 的计算，是mysql实现，而不是CPU直接实现。效率较低，并且存储空间多。线上价格 存储都用decimal(11,2) ，其实也可以直接存int, 数值是分。

Index

1，禁止重复索引
PRIMARY KEY , UNIQUE 不要同时出现

2，禁止滥用索引
索引仅仅是为了线上查询提供服务，不要为没有的线上访问需求滥用索引。

3，不推荐对数据区分小的字段，建立索引
比如性别字段，经验值，重合度低于20%(视表的横向大小浮动)。
主要是因为 硬盘的顺序读 > 随机读  mysql 预读机制

4，不推荐索引varchar：
如果真要索引，可以使用前缀索引（字符串很长），例如 deal表的predealid  KEY `predealid` (`predealid`(8)),
或者自建hash值，索引（字符串长度比较固定）。需要注意hash值，用crc,而不是md5 散列问题.

5，尽可能多级索引,而不是多余索引
多余索引 index1 (xid, addtime), (xid, type)
多级索引 index1 (xid, type, addtime)
反例：为了防止旧索引过大，可以考虑多余索引

线上执行sql规范
总体流程：

1，充分论证
比如加字段、加索引
A、得确定一定需要加这个索引。加这个字段。
没有别的手段，或者别的手段实现很不好。比如带来很多程序上的改动、导致整个系统结构复杂。
B、当前表数据量允许加这个字段加这个索引
C、是否其他组的同学会受到影响？

2，小心谨慎  不能随意操作
原则如下：
A、上百M的数据量得停止该相关的服务。
B、数据量不到百M，记录数在百万条以内，可以在低峰期进行（比如凌晨）。
C、数据量在万条以内、数据量在10M以内，可以在白天的非高峰点操作（可以分成多步操作）。

3，多步合一
在操作时，为了尽可能减少影响和操作时间，对同一个表进行的多步操作进行合并。比如对同一个表既加字段、又加索引，那么就应该写成一条语句。减少复制临时表的时间。

4，事前准备
A、事前确认操作的数据、操作命令及其所带来的影响。可以事先联系sa,搭建虚拟环境，测试.
B、写下整个操作的流程，中间用到的命令、事后检查所需要的命令。

5，事后检验
A、操作完成之后，检查所有同步是否正常。
B、操作完成之后，用命令检查所有的数据库是否均已生效。（不仅仅是主库，还包括所有辅库，检查脚本提前准备好）。
C、对于多步大的操作，应该至少有两人参加（sa和rd在一起），每一步操作后都要确认。比如，操作是否达到到预期的效果。（这些操作检查，都应该配备脚本，在检查时执行）。

6，发邮件通知
通知给相关同学，包括mis,data组

一些规范
1，所有的SQL都要存放在git sql目录下。
2，建立ticket给sa，必须明确一下内容：
服务器名字
sql文件的位置
是否需要重新启动apache服务器
该脚本预期执行时间，会影响的数据行数，是否对线上服务有影响
一些特殊要求，比如关闭主从报警等
3，禁止单表多个更改字段，用多次alter table命令
4，线上删除数据，需要严格控制速度，不要超过100/s;
原因分析:
见 innodb_max_purge_lag研究分享http://hot66hot.iteye.com/blog/1771034

5，明确alter table的危害性
alter table就是拷贝了一份全量数据入新表，即使你认为应该不需要拷贝的情况，他都会拷贝，比如
rename 一个column name （mv .frm）
修改column default value (alter table alter/change/modify column)
都会全量拷贝数据一次。
解决方案&分析见：http://www.taobaodba.com/html/534_mysql_online_ddl_and_nosql_schemaless_design.html