mysql语句实战书写规范

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个以内。

Copyright: 采用 知识共享署名4.0 国际许可协议进行许可

Links: https://www.fengpt.cn/archives/mysql语句实战书写规范