干货！SQL性能优化，书写高质量SQL语句 _SQL

文章插图

写SQL语句的时候我们往往关注的是SQL的执行结果，但是是否真的关注了SQL的执行效率，是否注意了SQL的写法规范？
以下的干货分享是在实际开发过程中总结的，希望对大家有所帮助！
1. limit分页优化当偏移量特别大时，limit效率会非常低。
SELECT id FROM A LIMIT 1000,10 很快
SELECT id FROM A LIMIT 90000,10 很慢
方案一：
select id from A order by id limit 90000,10;如果我们结合order by使用。很快，0.04秒就OK 。因为使用了id主键做索引！当然，是否能够使用索引还需要根据业务逻辑来定，这里只是为了提醒大家，在分页的时候还需谨慎使用！
方案二
select id from A order by idbetween 90000 and 90010;2.利用limit 1 、top 1 取得一行有些业务逻辑进行查询操作时(特别是在根据某一字段DESC,取最大一笔).可以使用limit 1 或者 top 1 来终止[数据库索引]继续扫描整个表或索引。
反例
SELECT id FROM A LIKE 'abc%' 正例
SELECT id FROM A LIKE 'abc%' limit 13. 任何情况都不要用 select * from table ，用具体的字段列表替换"*"，不要返回用不到的字段,避免全盘扫描！4. 批量插入优化反例

INSERT into person(name,age) values('A',24)INSERT into person(name,age) values('B',24)INSERT into person(name,age) values('C',24)

正例
INSERT into person(name,age) values('A',24),('B',24),('C',24),

sql语句的优化主要在于对索引的正确使用，而我们在开发中经常犯的错误便是对表进行全盘扫描，一来影响性能，而来耗费时间!

5.like语句的优化反例
SELECT id FROM A WHERE name like '%abc%'由于abc前面用了“%”，因此该查询必然走全表查询,除非必要(模糊查询需要包含abc)，否则不要在关键词前加%
正例
SELECT id FROM A WHERE name like 'abc%'6.where子句使用 in 或 not in 的优化sql语句中 in 和 not in 的使用请慎用！使用in 或者 not in 会丢弃索引，从而进行全盘扫描！
方案一：between 替换 in
反例
SELECT id FROM A WHERE num in (1,2,3) 正例
SELECT id FROM A WHERE num between 1 and 3方案二：exist 替换 in
注：关于exist和in的用法，片尾有彩蛋～
反例
SELECT id FROM A WHERE num in (select num from B)正例
SELECT num FROM A WHERE num exists (select 1 from B where B.num = A.num)方案三：left join 替换 in
反例
SELECT id FROM A WHERE num in (select num from B) 正例
SELECT id FROM A LEFT JOIN B ON A.num = B.num7.where子句使用or的优化通常使用 union all 或 union 的方式替换“or”会得到更好的效果。where子句中使用了or关键字,索引将被放弃使用。
反例
SELECT id FROM A WHERE num = 10 or num = 20正例
SELECT id FROM A WHERE num = 10 union all SELECT id FROM A WHERE num=208.where子句中使用 IS NULL 或 IS NOT NULL 的优化反例
SELECT id FROM A WHERE num IS NULL在where子句中使用 IS NULL 或 IS NOT NULL 判断，索引将被放弃使用，会进行全表查询。
正例
优化成num上设置默认值0，确保表中num没有null值, IS NULL 的用法在实际业务场景下SQL使用率极高，我们应注意避免全表扫描
SELECT id FROM A WHERE num=09.where子句中对字段进行表达式操作的优化不要在where子句中的“=”左边进行函数、算数运算或其他表达式运算，否则系统将可能无法正确使用索引。

SELECT id FROM A WHERE datediff(day,createdate,'2019-11-30')=0 优化为
SELECT id FROM A WHERE createdate>='2019-11-30' and createdate<'2019-12-1'

SELECT id FROM A WHERE year(addate) <2020优化为
SELECT id FROM A where addate<'2020-01-01'10.排序的索引问题 MySQL查询只是用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引。因此数据库默认排序可以符合要求情况下不要使用排序操作；
尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
11. 尽量用 union all 替换 unionunion和union all的差异主要是前者需要将两个（或者多个）结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的cpu运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用union all而不是union