分享MySQL操作规范

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

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

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

[size=x-large]TABLE[/size]

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

[size=large]2，CHARSET: utf8[/size]
[size=medium]线上大家统一标准，没有特殊理由，禁止使用其他编码标准[/size]

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

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

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

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

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

[size=large]3，禁止NULL数据[/size]
[size=medium]避免NULL，默认值也不要是NULL。　增加排序复杂[/size]

[size=large]4，char Vs varchar[/size]
[size=medium]长度明确 && 短小，建议用char, 其他情况用varchar[/size]

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

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

[size=x-large]Index[/size]

[size=large]1，禁止重复索引[/size]
[size=medium]PRIMARY KEY , UNIQUE 不要同时出现[/size]

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

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

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

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

[size=xx-large]线上执行sql规范[/size]
[size=x-large]总体流程：[/size]

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

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

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

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

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

[size=large]6，发邮件通知[/size]
[size=medium]通知给相关同学，包括mis,data组[/size]

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

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