SQL语句优化_select * from a left join b where-CSDN博客

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 试用。很快，因为使用了主键索引，或者根据业务逻辑使用索引。

方案二：

select id from A order by id  between 90000 and 90010;

2 批量插入优化

反例
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),

3 避免全盘扫描

任何情况都不要用 select * from table ，用具体的字段列表替换"*"，不要返回用不到的字段,避免全盘扫描

4 like语句的优化

反例
SELECT id FROM A WHERE name like '%abc%'
正例
select nick_name from member where nick_name like '小明%'

5 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=20

6 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=0

is not null不管字段是否为空都不会走索引,is null在字段允许为空时会使用索引！

7 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'

8 排序索引的问题

mysql查询只是用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引。因此数据库默认排序可以符合要求情况下不要使用排序操作；
尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

9 进来用union all 替换 union

union和union all的差异主要是前者需要将两个（或者多个）结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的cpu运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用union all而不是union

10 Inner join 和 left join、right join、子查询

inner join内连接也叫等值连接是，left/rightjoin是外连接。
子查询的性能又比外连接性能慢，尽量用外连接来替换子查询。
mysql是先对外表A执行全表查询，然后根据uuid逐次执行子查询，如果外层表是一个很大的表，我们可以想象查询性能会表现比这个更加糟糕。

反例
Select* from A where exists (select * from B where id>=3000 and A.uuid=B.uuid);
正例
Select* from A inner join B ON A.uuid=B.uuid where b.uuid>=3000;  这个语句执行测试不到一秒；

使用JOIN时候，应该用小的结果驱动大的结果
left join 左边表结果尽量小，如果有条件应该放到左边先处理，right join同理反向。如：

反例
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;

11 exist & 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()适合B表比A表数据小的情况
exists()适合B表比A表数据大的情况