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, INDEX 不要同时出现

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

Tagged on:

One thought on “MySQL操作规范

发表评论

电子邮件地址不会被公开。 必填项已用*标注


*