DML语句
1、【强制】SELECT语句必须指定需要的具体字段名称，禁止写成“*”。
说明：select *获取多余的字段会额外消耗不必要的CPU和IO；消耗网络带宽；表结构发生变化时会造成程序异常；另外select * 往往无法使用覆盖索引来优化执行计划。
2、【强制】insert语句指定具体字段名称，不要写成insert into t1 values(...)。
说明：同上
3、【建议】insert into t1 (col...) values(XX),(XX),(XX)...批量插入insert增加吞吐，需要考虑系统负载程度，建议不超过500。
说明：
1）批量插入可以增加io吞吐，提高效率。
2）需要考虑insert批量值，控制结果集大小，可以适当压测做到最优性能。一瞬间的大批量insert值会导致写io等待造成堵塞，同时消耗带宽造成从库延迟。
4、【建议】如无去重需要，不要使用UNION，推荐使用UNION ALL，并且UNION子句个数限制在5个以内。
说明：union将会按照字段的顺序进行排序；union all只是简单的将两个结果合并后就返回。从效率上说，union all 要比union快很多。
5、【建议】in值列表限制在200以内。需要考虑in值的区分度，防止过多值造成全表扫描。
6、【强制】大表禁止使用order by rand()来获取随机数据；可以使用rand()函数取出主键值进行提取。
说明：order by rand()在大表中相当于全表扫描然后随机获取，性能会非常低下。通过随机函数获得主键值，再次进行查找相对而言性能会非常好。
7、【强制】生产环境禁止未经DBA审核使用hint，如sql_no_cache，force index，ignore key，straight join等。
说明：hint会按照语法强行绑定执行计划，当过滤条件或者数据量发生变化时该执行计划并非最优。需要同DBA评审后决定是否在线上使用。
8、【强制】where条件里等号左右字段类型、排序规则必须一致，否则无法利用索引。
9、【建议】SELECT|UPDATE|DELETE|REPLACE要有WHERE子句，且WHERE子句的条件使用索引查找。
说明：mysql中innodb存储引擎是根据索引进行加锁，如果无法使用索引会升级到表级锁，性能低下。
10、【建议】WHERE 子句中不建议使用全模糊(%condition%)或者前缀模糊(%condition)的LIKE条件进行大范围过滤查找，必要时将查询拆分出去采用其他全文检索引擎。
说明：
1）前模糊匹配、全模糊匹配都无法使用innodb中的索引导致全表扫描从而造成性能降低。
2）update/delete全表扫描会升级为表级锁导致并发性能急剧下降。
11、【强制】检索索引列不要使用函数或表达式，否则无法利用索引。
示例：
SELECT * FROM t WHERE YEAR(d) >= 2016;
由于MySQL不支持函数索引，即使d字段有索引，也会直接全表扫描，应改为
SELECT * FROM t WHERE d >= ‘2016-01-01’;
12、【建议】避免使用or语句，可将or语句优化为union，然后在各个where条件上建立索引。
示例：
SELECT * FROM t WHERE a=1 or b=2;
改为
SELECT * FROM t WHERE a=1
union
SELECT * FROM t WHERE b=2;
13、【建议】分页查询，当limit起点较高时，可提前查询出id，然后进行join匹配查询。不建议大数据量排序分页。
示例：
SELECT * FROM t order by id limit 10000000,100;
改为
SELECT a.* FROM t a,
(SELECT id FROM t order by id limit 10000000,100) b
where a.id=b.id;
14、【建议】避免使用!=;<>;is not null;not in等非等值条件检索。
说明：
1）非确定条件检索都无法使用innodb中的索引导致全表扫描从而造成性能降低。
2）update/delete全表扫描会升级为表级锁导致并发性能急剧下降。
15、【强制】程序尽量使用参数化，在批量参数传入时可以减少解析时间提高性能并且避免SQL注入 。
16、【强制】对同一张表的多个alter操作合并成一条语句;alter添加更改字段不要指定字段位置(before/after column)。
说明：
1）alter table test add col1 int comment ‘test1’;alter table test add col2 int comment ‘test2’;改为
alter table test add col1 int comment ‘test1’,add col2 int comment ‘test2’;
2）Mysql5.6/5.7虽然添加了online ddl，但是不管inplace还是copy方式进行表结构更改，仍然需要添加mdl锁并且对表数据进行复制。
3）合并同一张表的alter操作可以将多次表数据复制合并成一次，可以降低io和锁冲突。
17、【建议】避免更改表名、字段名；避免删除表、列。需要和上下游充分沟通。
18、【建议】使用not exists替换not in。
多表连接
1、【强制】禁止跨db的join语句。
2、【强制】禁止在业务的更新类SQL语句中使用跨表join关联更新，比如update t1 join t2。
3、【建议】不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用join来代替子查询。
说明：
1）5.6之前没有对子查询进行优化，mysql对待子查询是从外向内进行查询的：即先查出外部结果集然后传入子查询中进行一行行的匹配。
2）5.6后mysql针对select语句中的子查询进行了一系列的优化，但是如果子查询结果集比较大并且结果集匹配列没有索引时，由于外表结果集驱动子查询结果集的设定会导致效率低下。另外update,delete中的子查询mysql往往会生成效率低下的执行计划。
3）将子查询替换成表连接后，mysql可以灵活确定驱动表生成合理的执行计划。
4、【建议】线上环境，多表join不要超过3个表。
5、【建议】在多表join中，尽量选取结果集较小的表作为驱动表，来join其他表。
6、【建议】表连接尽量使用内连接，避免外连接的使用。
说明：
1）外连接会指定驱动表顺序
2）当使用left join时，左表是驱动表，右表是被驱动表；
3）当使用right join时，右表时驱动表，左表是被驱动表；
事务
1、【建议】事务中INSERT|UPDATE|DELETE|REPLACE语句操作的行数控制在500以内。
2、【建议】批量操作大数量时，将大事务拆分成小事务。小事务之间进行必要的sleep。
大事务造成的影响：
1）级联表处理造成的批量锁，锁力度不够细。并发大事务会产生死锁，锁超时的问题；
2）同步等待第三方调用异常容易造成事务等待，造成事务堵塞，锁等待严重造成连接异常；
3）结果集过大造成io、网络流量激增。造成日志写入堵塞；从库延迟等。
3、【建议】控制事务中SQL数目，尽量不超过5个(支付业务除外)。
4、【建议】事务里的语句尽量基于主键或unique key。
5、【建议】尽量把一些典型外部调用移出事务，如调用webservice，访问文件存储等，从而避免事务过长。
6、【建议】如有外部事务嵌套调用，需要设置合理的超时时间回滚机制，防止由于外部事务相应过长导致空事务造成数据库锁争用。
7、【建议】设置合理的重试机制，防止由于重试并发过高导致数据库雪崩。
8、【建议】对于MySQL主从延迟严格敏感的select语句，请开启事务强制访问主库。
排序和分组
1、【建议】减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。order by、group by、distinct这些语句较为耗费CPU。
2、【建议】order by、group by、distinct这些SQL尽量利用索引直接检索出排序好的数据。
3、【建议】如无去重需要，不要使用UNION，推荐使用UNION ALL，并且UNION子句个数限制在5个以内。
评论区