sql语句优化总结

1.大SQL尽量拆分(根据实际业务情况)

2. like语句优化(根据实际业务情况)

SELECT id FROM A WHERE name like '%abc%'

由于abc前面用了“%”，因此该查询必然走全表查询，除非必要，否则不要在关键词前加%，优化成如下

SELECT id FROM A WHERE name like 'abc%'

3.分页

Select a from A limit 10000,10; 这种大偏移量下效率非常低。

可以考虑如下几个方案：

 

select a from A WHERE id>=xxxx limit 11;(将上一页的最大值通过where id> 进行预处理，然后分页)

 

select a from A WHERE id >= ( select a from A limit 10000,1 ) limit 10;

select a from A inner join (select a from A limit 10000,10) using (id) ;

4. where子句使用 ！= 或 <> 操作符优化

在where子句中使用 ！= 或 <>操作符，索引将被放弃使用，会进行全表查询。

 

    如SQL:SELECT id FROM A WHERE ID != 5

    

优化成：SELECT id FROM A WHERE ID>5 OR ID<5

5.where子句中使用 IS  NULL 或 IS  NOT  NULL 的优化(根据实际业务情况)

在where子句中使用 IS NULL 或 IS NOT NULL 判断，索引将被放弃使用，会进行全表查询。

 

    如SQL:SELECT id FROM A WHERE num IS NULL

    

    优化成num上设置默认值0，确保表中num没有null值，然后SQL为：SELECT id FROM A WHERE num=0

 

6.where子句使用or的优化

很多时候使用union all 或 nuin(必要的时候)的方式替换“or”会得到更好的效果。where子句中使用了or,索引将被放弃使用。

 

    如SQL: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=20

 

7.where子句使用IN 或 NOT  IN的优化(根据实际业务情况)

in和not in 也要慎用，否则也会导致全表扫描。

 

方案一：between替换in

 

    如SQL:SELECT id FROM A WHERE num in(1,2,3)

    

    优化成：SELECT id FROM A WHERE num between 1 and 3

 

方案二：exists替换in

 

    如SQL: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

 

    如SQL:SELECT id FROM A WHERE num in(select num from B)

    

    优化成：SELECT id FROM A LEFT  JOIN B ON A.num = B.num

8.where子句中对字段进行表达式操作的优化

不要在where子句中的“=”左边进行函数、算数运算或其他表达式运算，否则系统将可能无法正确使用索引。

 

    如SQL:SELECT id FROM A WHERE num/2 = 100

    优化成：SELECT id FROM A WHERE num = 100*2

    

    如SQL:SELECT id FROM A WHERE datediff(day,createdate,'2016-11-30')=0

    优化成：SELECT id FROM A WHERE createdate>='2016-11-30' and createdate<'2016-12-1'

    

    如SQL:SELECT id FROM A WHERE year(addate) <2016

    优化成：SELECT id FROM A where addate<'2016-01-01'

9. select * from table 优化　

任何地方都不要用 select * from table ，用具体的字段列表替换"*"，不要返回用不到的字段　

10.利用limit 1 、top 1 取得一行

有时要查询一张表时，你知道只需要看一条记录，你可能去查询一条特殊的记录。可以使用limit 1 或者 top 1 来终止数据库索引继续扫描整个表或索引。

 

    如SQL：SELECT id FROM A LIKE 'abc%'

    

    优化为：SELECT id FROM A LIKE 'abc%' limit 1

11.尽量不要使用 BY RAND()命令(不做要求)

BY RAND()是随机显示结果，这个函数可能会为表中每一个独立的行执行BY RAND()命令，这个会消耗处理器的处理能力。

 

如SQL：SELECT * FROM A order by rand() limit 10

 

优化为：SELECT * FROM A WHERE id >= ((SELECT MAX(id) FROM A)-(SELECT MIN(id) FROM A)) * RAND() + (SELECT MIN(id) FROM A) LIMIT 10

12.Inner join 和 left join、right join、子查询

第一：inner join内连接也叫等值连接，left/rightjoin是外连接。

    SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id =B.id;

    

    SELECT A.id,A.name,B.id,B.name FROM A RIGHT JOIN ON B A.id= B.id;

    

    SELECT A.id,A.name,B.id,B.name FROM A INNER JOIN ON A.id =B.id;

 

经过多方面的证实inner join性能比较快，因为inner join是等值连接，或许返回的行数比较少。但是我们要记得有些语句隐形的用到了等值连接，如：

 

　SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id;

 

推荐：能用inner join连接尽量使用inner join连接

 

第二：子查询的性能又比外连接性能慢，尽量用外连接来替换子查询。

 

Select* from A where exists (select * from B where id>=3000 and A.uuid=B.uuid)

 

我们可以通过explain可以查看到子查询是一个相关子查询(DEPENDENCE SUBQUERY);Mysql是先对外表A执行全表查询，然后根据uuid逐次执行子查询，如果外层表是一个很大的表，我们可以想象查询性能会比较糟糕。

 

一种简单的优化就是用inner join的方法来代替子查询，查询语句改为：

 

Select* from A inner join B ON A.uuid=B.uuid using(uuid) where b.uuid>=3000; 

 

第三：使用JOIN时候，应该用小的结果驱动大的结果

（left join 左边表结果尽量小，如果有条件应该放到左边先处理，right join同理反向），同时尽量把牵涉到多表联合的查询拆分多个query (多个表查询效率低，容易锁表和阻塞)。如：

Select * from A left join B A.id=B.ref_id  where  A.id>10;

可以优化为：select * from (select * from A wehre id >10) T1 left join B on T1.id=B.ref_id;

13.exists 代替 in

    SELECT * from A WHERE id in (SELECT id from B)

    

    SELECT * from A WHERE id  EXISTS(SELECT 1 from A.id= B.id)

 

in 是在内存中遍历比较

 

exist 需要查询数据库，所以当B的数据量比较大时，exists效率优于in.

 

in()只执行一次，把B表中的所有id字段缓存起来，之后检查A表的id是否与B表中的id相等，如果id相等则将A表的记录加入到结果集中，直到遍历完A表的所有记录。

 

in()适合B表比A表数据小的情况，exists()适合B表比A表数据大的情况。

 

 

 

                            ---------------长按二维码关注程序媛小姐姐公众号有更多彩蛋哦---------------