聊聊sql优化的15个⼩技巧
sql优化是⼀个⼤家都⽐较关注的热门话题，⽆论你在⾯试，还是⼯作中，都很有可能会遇到。
如果某天你负责的某个线上接⼝，出现了性能问题，需要做优化。那么你⾸先想到的很有可能是优化sql语句，因为它的改造成本相对于代码来说也要⼩得多。
那么，如何优化sql语句呢？
这篇⽂章从15个⽅⾯，分享了sql优化的⼀些⼩技巧，希望对你有所帮助。
1 避免使⽤select *
很多时候，我们写sql语句时，为了⽅便，喜欢直接使⽤select *，⼀次性查出表中所有列的数据。
反例：
select * from user where id=1;
在实际业务场景中，可能我们真正需要使⽤的只有其中⼀两列。查了很多数据，但是不⽤，⽩⽩浪费了数据库资源，⽐如：内存或者cpu。此外，多查出来的数据，通过⽹络IO传输的过程中，也会增加数据传输的时间。
还有⼀个最重要的问题是：select *不会⾛覆盖索引，会出现⼤量的回表操作，⽽从导致查询sql的性能很低。
那么，如何优化呢？
正例：
select name,age from user where id=1;
sql语句查询时，只查需要⽤到的列，多余的列根本⽆需查出来。
2 ⽤union all代替union
我们都知道sql语句使⽤union关键字后，可以获取排重后的数据。
⽽如果使⽤union all关键字，可以获取所有数据，包含重复的数据。
反例：
(select * from user where id=1)
union
(select * from user where id=2);
排重的过程需要遍历、排序和⽐较，它更耗时，更消耗cpu资源。
所以如果能⽤union all的时候，尽量不⽤union。
正例：
(select * from user where id=1)
union all
(select * from user where id=2);
除⾮是有些特殊的场景，⽐如union all之后，结果集中出现了重复数据，⽽业务场景中是不允许产⽣重复数据的，这时可以使⽤union。
3 ⼩表驱动⼤表
⼩表驱动⼤表，也就是说⽤⼩表的数据集驱动⼤表的数据集。
假如有order和user两张表，其中order表有10000条数据，⽽user表有100条数据。
这时如果想查⼀下，所有有效的⽤户下过的订单列表。
可以使⽤in关键字实现：
select * from order
where user_id in (select id from user where status=1)
也可以使⽤exists关键字实现：
select * from order
where exists (select 1 from user where order.user_id = user.id and status=1)
前⾯提到的这种业务场景，使⽤in关键字去实现业务需求，更加合适。
为什么呢？
因为如果sql语句中包含了in关键字，则它会优先执⾏in⾥⾯的⼦查询语句，然后再执⾏in外⾯的语句。如果in⾥⾯的数据量很少，作为条件查询速度更快。
⽽如果sql语句中包含了exists关键字，它优先执⾏exists左边的语句（即主查询语句）。然后把它作为条件，去跟右边的语句匹配。如果匹配上，则可以查询出数据。如果匹配不上，数据就被过滤掉了。
这个需求中，order表有10000条数据，⽽user表有100条数据。order表是⼤表，user表是⼩表。如果order表在左边，则⽤in关键字性能更好。
总结⼀下：
in 适⽤于左边⼤表，右边⼩表。
exists 适⽤于左边⼩表，右边⼤表。
不管是⽤in，还是exists关键字，其核⼼思想都是⽤⼩表驱动⼤表。
4 批量操作
如果你有⼀批数据经过业务处理之后，需要插⼊数据，该怎么办？
反例：
for(Order order: list){
orderMapper.insert(order):
}
在循环中逐条插⼊数据。
insert into order(id,code,user_id)
values(123,'001',100);
该操作需要多次请求数据库，才能完成这批数据的插⼊。
但众所周知，我们在代码中，每次远程请求数据库，是会消耗⼀定性能的。⽽如果我们的代码需要请求多次数据库，才能完成本次业务功能，势必会消耗更多的性能。
那么如何优化呢？
正例：
orderMapper.insertBatch(list):
提供⼀个批量插⼊数据的⽅法。
insert into order(id,code,user_id)
values(123,'001',100),(124,'002',100),(125,'003',101);
这样只需要远程请求⼀次数据库，sql性能会得到提升，数据量越多，提升越⼤。
但需要注意的是，不建议⼀次批量操作太多的数据，如果数据太多数据库响应也会很慢。批量操作需要把握⼀个度，建议每批数据尽量控制在500以内。如果数据多于500，则分多批次处理。
5 多⽤limit
有时候，我们需要查询某些数据中的第⼀条，⽐如：查询某个⽤户下的第⼀个订单，想看看他第⼀次的⾸单时间。
反例：
select id, create_date
from order
where user_id=123
order by create_date asc;
根据⽤户id查询订单，按下单时间排序，先查出该⽤户所有的订单数据，得到⼀个订单集合。然后在代码中，获取第⼀个元素的数据，即⾸单的数据，就能获取⾸单时间。
List<Order> list = OrderList();
Order order = (0);
虽说这种做法在功能上没有问题，但它的效率⾮常不⾼，需要先查询出所有的数据，有点浪费资源。
那么，如何优化呢？
正例：
select id, create_date
from order
where user_id=123
order by create_date asc
limit 1;
使⽤limit 1，只返回该⽤户下单时间最⼩的那⼀条数据即可。
此外，在删除或者修改数据时，为了防⽌误操作，导致删除或修改了不相⼲的数据，也可以在sql语句最后加上limit。
例如：
update order set status=0,edit_time=now(3)
where id>=100 and id<200 limit 100;
sql语句优化方式
这样即使误操作，⽐如把id搞错了，也不会对太多的数据造成影响。
6 in中值太多
对于批量查询接⼝，我们通常会使⽤in关键字过滤出数据。⽐如：想通过指定的⼀些id，批量查询出⽤户信息。
sql语句如下：
select id,name from category
where id in (1,100000000);
如果我们不做任何限制，该查询语句⼀次性可能会查询出⾮常多的数据，很容易导致接⼝超时。
这时该怎么办呢？
select id,name from category
where id in (1,100)
limit 500;
可以在sql中对数据⽤limit做限制。
不过我们更多的是要在业务代码中加限制，伪代码如下：
public List<Category> getCategory(List<Long> ids) {
if(CollectionUtils.isEmpty(ids)) {
return null;
}
if(ids.size() > 500) {
throw new BusinessException("⼀次最多允许查询500条记录")
}
CategoryList(ids);
}
还有⼀个⽅案就是：如果ids超过500条记录，可以分批⽤多线程去查询数据。每批只查500条记录，最后把查询到的数据汇总到⼀起返回。
不过这只是⼀个临时⽅案，不适合于ids实在太多的场景。因为ids太多，即使能快速查出数据，但如果返回的数据量太⼤了，⽹络传输也是⾮常消耗性能的，接⼝性能始终好不到哪⾥去。
7 增量查询
有时候，我们需要通过远程接⼝查询数据，然后同步到另外⼀个数据库。
反例：
select * from user;
如果直接获取所有的数据，然后同步过去。这样虽说⾮常⽅便，但是带来了⼀个⾮常⼤的问题，就是如果数据很多的话，查询性能会⾮常差。
这时该怎么办呢？
正例：
select * from user
where id>#{lastId} and create_time >= #{lastCreateTime}
limit 100;
按id和时间升序，每次只同步⼀批数据，这⼀批数据只有100条记录。每次同步完成之后，保存这100条数据中最⼤的id和时间，给同步下⼀批数据的时候⽤。
通过这种增量查询的⽅式，能够提升单次查询的效率。
8 ⾼效的分页
有时候，列表页在查询数据时，为了避免⼀次性返回过多的数据影响接⼝性能，我们⼀般会对查询接⼝做分页处理。
在mysql中分页⼀般⽤的limit关键字：
select id,name,age
from user limit 10,20;
如果表中数据量少，⽤limit关键字做分页，没啥问题。但如果表中数据量很多，⽤它就会出现性能问题。
⽐如现在分页参数变成了：
select id,name,age
from user limit 1000000,20;
mysql会查到1000020条数据，然后丢弃前⾯的1000000条，只查后⾯的20条数据，这个是⾮常浪费资源的。
那么，这种海量数据该怎么分页呢？
优化sql：
select id,name,age
from user where id > 1000000 limit 20;
先到上次分页最⼤的id，然后利⽤id上的索引查询。不过该⽅案，要求id是连续的，并且有序的。
还能使⽤between优化分页。
select id,name,age
from user where id between 1000000 and 1000020;
需要注意的是between要在唯⼀索引上分页，不然会出现每页⼤⼩不⼀致的问题。
9 ⽤连接查询代替⼦查询
mysql中如果需要从两张以上的表中查询出数据的话，⼀般有两种实现⽅式：⼦查询 和 连接查询。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。