DBA操作规范

本文主要是自己工作过程中总结的与DBA规范有关的知识点，内容大多来源各类DBA论坛，本文将不定期更新。

库表设计规范

1、表必须有主键，例如自增主键。
这样可以保证数据行是按照顺序写入，对于SAS传统机械式硬盘写入性能更好，根据主键做关联查询的性能也会更好，并且还方便了数据仓库抽取数据。从性能的角度来说，使用UUID作为主键是个最不好的方法，它会使插入变得随机。（PG建议使用序列类型bigserial做主键）

2、禁止使用分区表。
分区表的好处是对于开发来说，不用修改代码，通过后端DB的设置，比如对于时间字段做拆分，就可以轻松实现表的拆分。但这里面涉及一个问题，查询的字段必须是分区键，否则会遍历所有的分区表，并不会带来性能上的提升。此外，分区表在物理结构上仍旧是一张表，此时我们更改表结构，一样不会带来性能上的提升。所以应采用切表的形式做拆分，如程序上需要对历史数据做查询，可通过union all的方式关联查询。另外随着时间的推移，历史数据表不再需要，只需在从库上dump出来，即便捷地迁移至备份机上。

索引规范

1、索引不是越多越好，按实际需要进行创建。
索引是一把双刃剑，它可以提高查询效率但也会降低插入和更新的速度并占用磁盘空间。适当的索引对应用的性能至关重要，而且在MySQL中使用索引它的速度是极快的。遗憾的是，索引也有相关的开销。每次向表中写入时（如INSERT、UPDATEH或DELETE），如果带有一个或多个索引，那么MySQL也要更新各个索引，这样索引就增加了对各个表的写入操作的开销。只有当某列被用于WHERE子句时，才能享受到索引的性能提升的好处。如果不使用索引，它就没有价值，而且会带来维护上的开销。
2、查询的字段必须创建索引。
如：1、SELECT、UPDATE、DELETE语句的WHERE条件列；2、多表JOIN的字段。
3、不在索引列进行数学运算和函数运算。
无法使用索引，导致全表扫描。
例：SELECT * FROM t WHERE YEAR(d) >= 2016;
由于MySQL不像Oracle那样支持函数索引，即使d字段有索引，也会直接全表扫描。
应改为----->
SELECT * FROM t WHERE d >= ‘2016-01-01’;
4、不在低基数列上建立索引，例如‘性别’。
有时候，进行全表浏览要比必须读取索引和数据表更快，尤其是当索引包含的是平均分布的数据集是更是如此。对此典型的例子是性别，它有两个均匀分布的值（男和女）。通过性别需要读取大概一半的行。在种情况下进行全表扫描浏览要更快。
5、不使用%前导的查询，如like ‘%xxx’。
无法使用索引，导致全表扫描。
低效查询
SELECT * FROM t WHERE name LIKE ‘%de%’;
----->
高效查询
SELECT * FROM t WHERE name LIKE ‘de%’;
6、不使用反向查询，如 not in / not like。
无法使用索引，导致全表扫描。
7、避免冗余或重复索引。
联合索引IX_a_b_c(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)，那么索引 (a) 、(a,b) 就是多余的。

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;

SQL自审核规范

SELECT审核

检查项：
1、select * 是否有必要查询所有的字段？
2、警告！没有where条件，注意where后面的字段要加上索引
3、没有limit会查询更多的数据
4、警告！子查询性能低下，请转为join表关联
5、提示：in里面的数值不要超过1000个
6、提示：采用join关联，注意关联字段要都加上索引，如on a.id=b.id
7、提示：MySQL对多表join关联性能低下，建议不要超过3个表以上的关联
8、警告！like '%%'双百分号无法用到索引，like ‘mysql%‘这样是可以利用到索引的
9、提示：默认情况下，MySQL对所有GROUP BY col1，col2…的字段进行排序。如果查询包括GROUP BY，想要避免排序结果的消耗，则可以指定ORDER BY NULL禁止排序。
10、警告！MySQL里用到order by rand()在数据量比较多的时候是很慢的，因为会导致MySQL全表扫描，故也不会用到索引
11、提示：是否要加一个having过滤下？
12、警告！禁止不必要的order by排序，因为前面已经count统计了
13、警告！MySQL里不支持函数索引，例DATE_FORMAT(‘create_time’,’%Y-%m-%d’)='2016-01-01’是无法用到索引的，需要改写为
create_time>=‘2016-01-01 00:00:00’ and create_time<=‘2016-01-01 23:59:59’
14、之后会调用美团网SQLAdvisor进行索引检查

INSERT审核

检查项：
警告：insert 表1 select 表2，会造成锁表。

UPDATE审核规则

检查项：
1、警告！没有where条件，update会全表更新，禁止执行！！！
2、更新的行数小于1000行，可以由开发自助执行。否则请联系DBA执行！！！
3、防止where 1=1 绕过审核规则
4、检查更新字段有无索引

CREATE审核规则

检查项：
1、警告！表没有主键
2、警告！表主键应该是自增的，缺少AUTO_INCREMENT
3、提示：id自增字段默认值为1，auto_increment=1
4、警告！表没有索引
5、警告！表中的索引数已经超过5个，索引是一把双刃剑，它可以提高查询效率但也会降低插入和更新的速度并占用磁盘空间
6、警告！表字段没有中文注释，COMMENT应该有默认值，如COMMENT ‘姓名’
7、警告！表没有中文注释
8、警告！表缺少utf8字符集，否则会出现乱码
9、警告！表存储引擎应设置为InnoDB
10、警告！表应该为timestamp类型加默认系统当前时间

ALTER审核规则

检查项：
1、警告！不支持create index语法，请更改为alter table add index语法。
2、警告！更改表结构要减少与数据库的交互次数，应改为，例alter table t1 add index IX_uid(uid),add index IX_name(name)
3、表记录小于100万行，可以由开发自助执行。否则表太大请联系DBA执行!
4、支持删除索引，但不支持删除字段

阿里嵩山版-MYSQL使用规范

建表规约

【强制】表必备三字段：id, create_time, update_time。
说明：其中 id 必为主键，类型为 bigint unsigned、单表时自增、步长为 1。create_time, update_time 的类型均为 datetime 类型，前者现在时表示主动式创建，后者过去分词表示被动式更新。
【推荐】表的命名最好是遵循“业务名称_表的作用”。
正例：alipay_task / force_project / trade_config
【推荐】单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。
说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

索引规约

【强制】业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。
说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。
【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。
说明：即使双表 join 也要注意表索引、SQL 性能。
【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。（%xx 这种无法使用到索引）
【推荐】如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。
正例：where a=? and b=? order by c; 索引：a_b_c
反例：索引如果存在范围查询，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引 a_b 无法排序。
【推荐】利用延迟关联或者子查询优化超多分页场景。
说明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL改写。
正例：先快速定位需要获取的 id 段，然后再关联：
SELECT t1.* FROM 表 1 as t1, (select id from 表 1 where 条件 LIMIT 100000,20 ) as t2 where t1.id=t2.id
【推荐】SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts
最好。说明：
1） consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
2） ref 指的是使用普通的索引（normal index）。
3） range 对索引进行范围检索。
反例：explain 表的结果，type=index，索引物理文件全扫描，速度非常慢，这个 index 级别比较 range还低，与全表扫描是小巫见大巫。
【参考】创建索引时避免有如下极端误解：
1） 索引宁滥勿缺。认为一个查询就需要建一个索引。
2） 吝啬索引的创建。认为索引会消耗空间、严重拖慢记录的更新以及行的新增速度。
3） 抵制惟一索引。认为惟一索引一律需要在应用层通过“先查后插”方式解决。

SQL语句

【强制】不要使用 count(列名)或 count(常量)来替代 count()，count()是 SQL92 定义的标
准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。
说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。
【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。
【推荐】SQL 语句中表的别名前加 as，并且以 t1、t2、t3、…的顺序依次命名。
说明：1）别名可以是表的简称，或者是依照表在 SQL 语句中出现的顺序，以 t1、t2、t3 的方式命名。2）别名前加 as 使别名更容易识别。
正例：select t1.name from table_first as t1, table_second as t2 where t1.id=t2.id;
【推荐】in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控制在 1000 个之内。

ORM映射

【强制】在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。
说明：1）增加查询分析器解析成本。2）增减字段容易与 resultMap 配置不一致。3）无用字段增加网络消耗，尤其是 text 类型的字段。
【强制】更新数据表记录时，必须同时更新记录对应的 update_time 字段值为当前时间。