本文正在参加「技术专题19期漫谈数据库技术」活动

1、避免使用select

很多时候我们写SQL语句时，为了方便，喜欢直接使用select *，一次性查出表中所有列的数据。
在实际业务场景中，可能我们需要使用的只有一两列。查了很多的数据，会造成资源浪费。
此外，多查出来的数据，通过网络IO传输的过程中，也会增加数据传输的时间。

例如:

select * from user where id=1;

优化:

select name,age from user where id=1;

# 只查询需要的列,用不到的列不查询

2、用union all 代替 union

在SQL语句中使用union关键字后，可以获得排重后的数据。

而如果使用union all关键字，可以获取所有数据，包含重复的数据。

例如：

(select * from user where id =1)
union
(select * from user where id =2);

优化:

(select * from user where id =1)
union all
(select * from user where id =2);

排重后的过程需要遍历、排序和比较，它更耗时间，更消耗资源。

所以能用union all的时候，就不要用union。

除非是有些特殊的场景，比如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 exists=1)

前面提到的业务场景,使用in关键字也适合去实现业务需求。

in适合用于左边大表，右边小表；

exists适用于左边小表，右边大表；

不管是用in，还是exists关键字，其核心思想都是用小表驱动大表。

4、批量操作

如果有一批数据经过业务处理之后，需要插入数据，该怎么办？

例如:

for(order:lsit){
	orderMapper.insert(order):
}

在循环中逐条插入数据:

insert into order(id,code,user_id)
valuse(123,'001',100);

该操作需要多次请求数据库，才能完成这批数据的插入。

优化:

但我们在实际情况中，每次远程请求数据库，都会消耗一定性能。而如果我们的代码需要请求多次数据库，才能完成本次业务功能，势必会消耗更多的性能。

orderMapper.insertBatch(list):

提供一个批量插入数据的方法:

insert into order(id,code,user_id)
values(123,'001',100),(123,'002',100),(124,'030',100);

这样只需要远程请求一次数据库，SQL性能会得到提升，数据量越多，提升越大。

5、多用limit

有时候，我们需要查询某些数据的第一条，比如：查询某个用户下的第一个订单，想看他第一次下单的时间

例如:

select id, create_date
from order
where user_id=123
order by create_date asc;

根据用户id查询订单，按下单时间排序，先查出该用户所有的订单数据，得到一个订单合集。然后在代码中，获取第一个元素的数据，即首单的数据，就能获取到第一次下单的时间。

优化：

select id,create_date
from order
where user_id=123
order by create_date asc
limit 1;

使用limit 1，只返回该用户下单时间最小的那一条数据即可。

6、增量查询

有时候，我们需要通过远程接口查询数据，然后同步到另外一个数据库。

例子:

select * from user;

如果直接获取所有的数据，再同步过去。这样说虽然省事，但是难免会存在查询性能差的问题。

优化：

select * from user
where id>#{lastId} and create_time >= #{lastCreateTime}
limit 100;

按id和时间升序，每次只同步一批数据，这一批数据只有100条记录。每次同步完成之后，保存这100条数据中最大的id和时间，给同步下一批数据的时候用。

数据库SQL6个优化技巧