14.mysql 40条军规DBA操作规范
1、涉及业务上的修改/删除数据，在得到业务⽅、CTO的邮件批准后⽅可执⾏，执⾏前提前做好备份，必要时可
逆。
2、所有上线需求必须⾛⼯单系统，⼝头通知视为⽆效。
3、在对⼤表做表结构变更时，如修改字段属性会造成锁表，并会造成从库延迟，从⽽影响线上业务，必须在凌晨
0:00 后业务低峰期执⾏，另统⼀⽤⼯具 pt-online-schema-change 避免锁表且降低延迟执⾏时间。
使⽤范例
#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})
4、所有线上业务库均必须搭建MHA⾼可⽤架构，避免单点问题。
5、给业务⽅开权限时，密码要⽤MD5加密，⾄少16位。权限如没有特殊要求，均为select查询权限，并做库表级
限制。
6、删除默认空密码账号。
delete from mysql.user where user='' and password='';
flush privileges;
7、汇总库开启Audit审计⽇志功能，出现问题时⽅可追溯。
⾏为规范
8、禁⽌⼀个MySQL实例存放多个业务数据库，会造成业务耦合性过⾼，⼀旦出现问题会殃及池⻥，增加了定位故
障问题的难度。通常采⽤多实例解决，⼀个实例⼀个业务库，互不⼲扰。
9、禁⽌在主库上执⾏后台管理和统计类的功能查询，这种复杂类的SQL会造成CPU的升⾼，进⽽会影响业务。
10、批量清洗数据，需要开发和DBA共同进⾏审查，应避开业务⾼峰期时段执⾏，并在执⾏过程中观察服务状态。
11、促销活动等应提前与DBA当⾯沟通，进⾏流量评估，⽐如提前⼀周增加机器内存或扩展架构，防⽌DB出现性
能瓶颈。
12、禁⽌在线上做数据库压⼒测试。
基本规范
13、禁⽌在数据库中存储明⽂密码。
14、使⽤InnoDB存储引擎。
⽀持事务，⾏级锁，更好的恢复性，⾼并发下性能更好。
InnoDB表避免使⽤COUNT(*)操作，因内部没有计数器，需要⼀⾏⼀⾏累加计算，计数统计实时要求较强可以使⽤
memcache或者Redis。
15、表字符集统⼀使⽤UTF8。
不会产⽣乱码⻛险。
16、所有表和字段都需要添加中⽂注释。
⽅便他⼈、⽅便⾃⼰。
17、不在数据库中存储图⽚、⽂件等⼤数据。
图⽚、⽂件更适合于GFS分布式⽂件系统，数据库⾥存放超链接即可。
18、避免使⽤存储过程、视图、触发器、事件。
MySQL是OLTP应⽤，最擅⻓简单的增、删、改、查操作，但对逻辑计算分析类的应⽤，并不适合，所以这部分的
需求最好通过程序上实现。
19、避免使⽤外键，外键⽤来保护参照完整性，可在业务端实现。
外键会导致⽗表和⼦表之间耦合，⼗分影响SQL性能，出现过多的锁等待，甚⾄会造成死锁。
20、对事务⼀致性要求不⾼的业务，如⽇志表等，优先选择存⼊MongoDB。
其⾃身⽀持的sharding分⽚功能，增强了横向扩展的能⼒，开发不⽤过多调整业务代码。
库表设计规范
21、表必须有主键，例如⾃增主键。
这样可以保证数据⾏是按照顺序写⼊，对于SAS传统机械式硬盘写⼊性能更好，根据主键做关联查询的性能也会更
好，并且还⽅便了数据仓库抽取数据。从性能的⻆度来说，使⽤UUID作为主键是个最不好的⽅法，它会使插⼊变
得随机。
22、禁⽌使⽤分区表。
分区表的好处是对于开发来说，不⽤修改代码，通过后端DB的设置，⽐如对于时间字段做拆分，就可以轻松实现
表的拆分。但这⾥⾯涉及⼀个问题，查询的字段必须是分区键，否则会遍历所有的分区表，并不会带来性能上的提
升。此外，分区表在物理结构上仍旧是⼀张表，此时我们更改表结构，⼀样不会带来性能上的提升。所以应采⽤切
表的形式做拆分，如程序上需要对历史数据做查询，可通过union all的⽅式关联查询。另外随着时间的推移，历史
数据表不再需要，只需在从库上dump出来，即便捷地迁移⾄备份机上。
字段设计规范
23、⽤DECIMAL代替FLOAT和DOUBLE存储精确浮点数。
浮点数的缺点是会引起精度问题，请看下⾯⼀个例⼦：
mysql> CREATE TABLE t3 (c1 float(10,2),c2 decimal(10,2));
Query OK, 0 rows affected (0.05 sec)
>mysql> insert into t3 values (999998.02, 999998.02);
Query OK, 1 row affected (0.01 sec)
>mysql> select * from t3;
+-----------+-----------+
| c1 | c2 |
+-----------+-----------+
| 999998.00 | 999998.02 |
+-----------+-----------+
1 row in set (0.00 sec)
可以看到c1列的值由999998.02变成了999998.00，这就是float浮点数类型的不精确性造成的。因此对货币等对精
度敏感的数据，应该⽤定点数表示或存储。
24、使⽤TINYINT来代替ENUM类型。
采⽤enum枚举类型，会存在扩展的问题，例如⽤户在线状态，如果此时增加了：5表示请勿打扰、6表示开会中、
7表示隐身对好友可⻅，那么增加新的ENUM值要做DDL修改表结构操作了。
25、字段⻓度尽量按实际需要进⾏分配，不要随意分配⼀个很⼤的容量。
选择字段的⼀般原则是保⼩不保⼤，能⽤占⽤字节少的字段就不⽤⼤字段。⽐如主键，强烈建议⽤int整型，不⽤
uuid，为什么？省空间啊。空间是什么？空间就是效率！按4个字节和按32个字节定位⼀条记录，谁快谁慢太明显
了。涉及⼏个表做join时，效果就更明显了。更⼩的字段类型占⽤的内存就更少，占⽤的磁盘空间和磁盘I/O也会更
少，⽽且还会占⽤更少的带宽。
有不少开发⼈员在设计表字段时，只要是针对数值类型的全部⽤int，但这不⼀定合适，就⽐如⽤户的年龄，⼀般来
说，年龄⼤都在1~100岁之间，⻓度只有3，那么⽤int就不适合了，可以⽤tinyint代替。⼜⽐如⽤户在线状态，0表
示离线、1表示在线、2表示离开、3表示忙碌、4表示隐身等，其实类似这样的情况，⽤int都是没有必要的，浪费
空间，采⽤tinyint完全可以满⾜需要，int占⽤的是4字节，⽽tinyint才占⽤1个字节。
int整型有符号（signed）最⼤值是2147483647，⽽⽆符号（unsigned）最⼤值是4294967295，如果你的需求没
有存储负数，那么建议改成有符号（unsigned），可以增加int存储范围。
int(10)和int(1)没有什么区别，10和1仅是宽度⽽已，在设置了zerofill扩展属性的时候有⽤，例：
root@localhost(test)10:39>create table test(id int(10) zerofill,id2 int(1));
Query OK, 0 rows affected (0.13 sec)
root@localhost(test)10:39>insert into test values(1,1);
Query OK, 1 row affected (0.04 sec)
root@localhost(test)10:56>insert into test values(1000000000,1000000000);
Query OK, 1 row affected (0.05 sec)
root@localhost(test)10:56>select * from test;
+------------+------------+
| id | id2 |
+------------+------------+
| 0000000001 | 1 |
| 1000000000 | 1000000000 |
+------------+------------+
2 rows in set (0.01 sec)
26、字段定义为NOT NULL要提供默认值。
从应⽤层⻆度来看，可以减少程序判断代码，⽐如你要查询⼀条记录，如果没默认值，你是不是得先判断该字段对
应变量是否被设置，如果没有，你得通过java把该变量置为’’或者0，如果设了默认值，判断条件可直接略过。
NULL值很难进⾏查询优化，它会使索引统计更加复杂，还需要MySQL内部进⾏特殊处理。
27、尽可能不使⽤TEXT、BLOB类型。
增加存储空间的占⽤，读取速度慢。
索引规范
28、索引不是越多越好，按实际需要进⾏创建。
索引是⼀把双刃剑，它可以提⾼查询效率但也会降低插⼊和更新的速度并占⽤磁盘空间。适当的索引对应⽤的性能
⾄关重要，⽽且在MySQL中使⽤索引它的速度是极快的。遗憾的是，索引也有相关的开销。每次向表中写⼊时（如
INSERT、UPDATEH或DELETE），如果带有⼀个或多个索引，那么MySQL也要更新各个索引，这样索引就增加了
对各个表的写⼊操作的开销。只有当某列被⽤于WHERE⼦句时，才能享受到索引的性能提升的好处。如果不使⽤
索引，它就没有价值，⽽且会带来维护上的开销。
29、查询的字段必须创建索引。
如：1、SELECT、UPDATE、DELETE语句的WHERE条件列；2、多表JOIN的字段。
30、不在索引列进⾏数学运算和函数运算。
⽆法使⽤索引，导致全表扫描。
例：
SELECT * FROM t WHERE YEAR(d) >= 2016;
由于MySQL不像Oracle那样⽀持函数索引，即使d字段有索引，也会直接全表扫描。
应改为----->
SELECT * FROM t WHERE d >= '2016-01-01';
31、不在低基数列上建⽴索引，例如‘性别’。
有时候，进⾏全表浏览要⽐必须读取索引和数据表更快，尤其是当索引包含的是平均分布的数据集是更是如此。对
此典型的例⼦是性别，它有两个均匀分布的值（男和⼥）。通过性别需要读取⼤概⼀半的⾏。在种情况下进⾏全表
扫描浏览要更快。
32、不使⽤%前导的查询，如like ‘%xxx’。
⽆法使⽤索引，导致全表扫描。
低效查询
SELECT * FROM t WHERE name LIKE '%de%';
----->
⾼效查询
SELECT * FROM t WHERE name LIKE 'de%';
33、不使⽤反向查询，如 not in / not like。
⽆法使⽤索引，导致全表扫描。
34、避免冗余或重复索引。
联合索引IX_a_b_c(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)，那么索引 (a) 、(a,b) 就是多余的。
SQL设计规范
*35、不使⽤SELECT ，只获取必要的字段。**
消耗CPU和IO、消耗⽹络带宽；
⽆法使⽤覆盖索引。
36、⽤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);
37、避免数据类型不⼀致。
SELECT * FROM t WHERE id = '19';
----->
SELECT * FROM t WHERE id = 19;
38、减少与数据库的交互次数。
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;
39、拒绝⼤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);
40、禁⽌使⽤order by rand()
SELECT * FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 4;
---->
SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4;