1.避免使用select *
因为select * 查出来的数据是全部的数据,需要的数据包含其中,但是也有不需要的数据,效率低
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左边的语句（即主查询语句）。然后把它作为条件，去跟右边的语句匹配。如果匹配上，则可以查询出数据。如果匹配不上，数据就被过滤掉了。
所以 in 为左边大表,右边小表, exsits 为左边小表,右边大表
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 = orderMapper.getOrderList();
Order order = list.get(0);
这个需要查出全部的数据,然后取出第一条
优化:
select id, create_date
from order
where user_id=123
order by create_date asc
limit 1;
此外，在删除或者修改数据时，为了防止误操作，导致删除或修改了不相干的数据，也可以在sql语句最后加上limit。
update order set status=0,edit_time=now(3)
where id>=100 and id<200 limit 100;
这样即使误操作，比如把id搞错了，也不会对太多的数据造成影响。
6.in内东西过多
对于批量查询接口，我们通常会使用in关键字过滤出数据。比如：想通过指定的一些id，批量查询出用户信息
select id,name from category
where id in (1,2,3...100000000);
这条语句会查出很多数据,很容易导致接口超时
select id,name from category
where id in (1,2,3...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条记录")
}
return mapper.getCategoryList(ids);
}
7.增量查询
有时候，我们需要通过远程接口查询数据，然后同步到另外一个数据库。
select * from user;
如果直接获取所有的数据，然后同步过去。这样虽说非常方便，但是带来了一个非常大的问题，就是如果数据很多的话，查询性能会非常差。
select * from user
where id>#{lastId} and create_time >= #{lastCreateTime}
limit 100;
按id和时间升序，每次只同步一批数据，这一批数据只有100条记录。每次同步完成之后，保存这100条数据中最大的id和时间，给同步下一批数据的时候用。
通过这种增量查询的方式，能够提升单次查询的效率。
8.高效的分页
有时候，列表页在查询数据时，为了避免一次性返回过多的数据影响接口性能，我们一般会对查询接口做分页处理。
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;
还能使用between优化分页。
select id,name,age
from user where id between 1000000 and 1000020;