SQL优化常用实践

良好的sql可以提升sql的执行效率，从而提高系统的性能。以下是一些常用实践，希望对你有所帮助：

• SELECT语句指定具体字段名称，禁止写成*，这样一方面可以去掉无用的字段，也可以尽可能的使用覆盖索引

• insert语句指定具体字段名称，不要写成insert into t2 values(…)

• insert into…values(XX),(XX),(XX)…;这里XX的值不要超过5000个。值过多虽然执行很快，但会引起主从同步延迟

• SELECT语句不要使用UNION，推荐使用UNION ALL，并且UNION子句个数限制在5个以内。因为union all不需要去重，节省数据库资源，提高性能

• 事务里批量更新数据需要控制数量，进行必要的sleep，做到少量多次

• 除静态表或小表（100行以内），DML语句必须有where条件，且使用索引查找。

• 生产环境禁止使用hint，如sql_no_cache，force index，ignore key，straight join等。因为hint是用来强制SQL按照某个执行计划来执行，但随着数据量变化我们无法保证自己当初的预判是正确的，因此我们要相信MySQL优化器！

• where条件里等号左右字段类型必须一致，否则无法利用索引(隐式类型转换会让索引失效)

• 生产数据库中强烈不推荐大表上发生全表扫描，但对于100行以下的静态表可以全表扫描。查询数据量不要超过表行数的25%，否则不会利用索引

• WHERE 子句中禁止只使用全模糊的LIKE条件进行查找，必须有其他等值或范围查询条件，否则无法利用索引

• 索引列不要使用函数或表达式，否则无法利用索引。如where length(name)='Admin’或where user_id+2=10023

• 减少使用or语句，可将or语句优化为union，然后在各个where条件上建立索引。如where a=1 or b=2优化为where a=1… union …where b=2, key(a),key(b)

• 分页查询，当limit起点较高时，可先用过滤条件进行过滤。如select a,b from t1 limit 20000,20;优化为: select a,b from t1 where id>20000 limit 20;

• where条件中不允许索引字段包含数学运算或函数运算

• where条件中不建议使用前通配符查找

• 修改表将可空字段改为非空时要明确指定默认值，修改表新增非空字段时要明确指定默认值

• 插入语句不允许使用sysdate()函数

• 禁跨db的join语句。因为这样可以减少模块间耦合，为数据库拆分奠定基础

• 禁在业务的更新类SQL语句中使用join，比如update t1 join t2…

• 不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用join来代替子查询(子查询不会走索引)

• 多表join不要超过3个表

• 在多表join中，尽量选取结果集较小的表作为驱动表，来join其他表(小表驱动)

• 事务中INSERT|UPDATE|DELETE|REPLACE语句操作的行数控制在20000以内，以及WHERE子句中IN列表的传参个数控制在20000以内

• 批量操作数据时，需要控制事务处理间隔时间，进行必要的sleep，一般建议值5-10秒(一般在定时或者跑批系统中)

• 对于有auto_increment属性字段的表的插入操作，并发需要控制在200以内

• 程序设计必须考虑“数据库事务隔离级别”带来的影响，包括脏读、不可重复读和幻读。线上事务隔离级别为READ-COMMITTED

• 避免长事务。因为过长的事务会导致锁数据较久，MySQL内部缓存、连接消耗过多等雪崩问题

• 事务里更新语句尽量基于主键或unique key，如update … where id=XX; 否则会产生间隙锁，内部扩大锁定范围，导致系统性能下降，可能产生死锁

• 尽量把一些典型外部调用移出事务，如调用webservice，访问文件存储等，从而避免事务过长

• 对于MySQL主从延迟严格敏感的select语句，请开启事务强制访问主库

• 减少使用order by，能不排序就不排序，或将排序放到程序端去做。order by、group by、distinct这些语句较为耗费CPU

• order by、group by、distinct这些SQL尽量利用索引直接检索出排序好的数据。如where a=1 order by可以利用key(a,b)

• 不用关联子查询，如update t1 set … where name in(select name from user where…);效率极其低下

• 禁用insert into …on duplicate key update…在高并发环境下，会造成主从不一致