写在前面

本篇是笔者在实际工作中总结的10种常用的SQL调优方式，这里进行总结和分享。

常用方式

避免使用select *

很多时候为了方便写sql语句，都喜欢直接使用select *，一次性查出表中所有列的数据，如下所示：

1
select * from t_user where id=1;

而在实际业务场景中，可能只需要两三列，多查了很多数据，又不用，白白浪费了数据库资源。此外多查出来的数据，通过网络IO传输的过程中，也会增加数据传输的时间。

最重要的是select *不会走覆盖索引，会出现大量的回表操作，而从导致查询sql的性能很低。此时我们应当根据实际情况，只查询要使用的列，多余的列不需要查询：

1
select name,age from user where id=1;

小表驱动大表

所谓的小表驱动大表，即使用小表的数据集来驱动大表的数据集。

举个例子，现在工单表有1000万数据，而用户表只有10万条，要查询所有有效用户最近一次处理的工单明显。可以使用in关键字，对应语句如下：

1
2
3
4
5
6
7
select * 
from t_order
where user_id in (
    select id 
    from t_user 
    where status=1
)

或者使用exists关键字，对应语句如下：

1
2
3
4
5
6
7
select * from t_order
where exists (
    select 1 
    from t_user 
    where t_order.user_id = t_user.id 
    and t_user.status=1
)

不过笔者更建议使用in关键字去实现，因为如果SQL语句中包含了in关键字，则它会优先执行in里面的子查询语句，然后再执行in外面的语句。如果in里面的数据量很少，作为条件查询的话，速度会更快。

当然，如果SQL语句中包含了exists关键字，那么它优先执行exists左边的语句（即主查询语句），然后把它作为条件，去跟右边的语句匹配。如果匹配得上，则可以查询出数据。如果匹配不上，数据就被过滤掉了。

在上面的例子中，工单表有1000万数据，而用户表只有10万条，因此工单表是大表，而用户表则是小表。如果工单表在左边，那使用in关键字性能更好。总结一下原则：

（1）in适用于左边大表，右边小表;

（2）exists 适用于左边小表，右边大表。

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

多用limit

有时候我们只是想查询第一条数据，如id为1的用户他处理的第一个工单的时间，有些人可能会这样书写SQL：

1
2
3
4
select order_id, deal_time 
from t_order 
where user_id=1
order by deal_time asc;

先根据用户id查询工单，之后按照处理时间顺序排序，接着在得到的工单集合中，获取第一个元素的数据，即首单的数据，就能获取首个工单的处理时间：

1
2
List<TOrder> list = orderMapper.getOrderList();
TOrder order = list.get(0);

这种做法在功能实现上没有任何问题，但是它的效率不高，需要先查询出所有数据，太浪费资源了。实际上我们只需使用limit 1语句，直接返回一条数据即可：

1
2
3
4
5
select order_id, deal_time 
from t_order 
where user_id=1
order by deal_time asc
limit 1;

批量操作

当我们有一批数据现在经过处理后，需要插入到数据库，此时该怎样操作呢？有些人会遍历列表，然后单独插入每一行：

1
2
3
4
5
6
7
8
for(Order order: list){
   orderMapper.insert(order):
}

<insert>
  insert into order(id,code,user_id) 
  values(10001,'12312',1);
</insert>

毫无疑问，这种操作需要多次请求数据库，才能完成这批数据的插入操作。实际上我们在代码中，每次远程请求数据库，都会消耗一定的性能。因此非常不建议使用这种方式，应该采用批量插入方式：

1
2
3
orderMapper.insertBatch(orderListinsert into order(id,code,user_id) ):

values(10001,'12312',1),(10002,'12313',2),(10003,'12314',3);

这样只需远程请求一次数据库，即可批量插入数据。但是一次批量操作的数据不能太多，如果数据太多也会导致数据库响应缓慢，建议数据量控制在500条左右，多于500条，建议采用多线程分批次处理。

增量查询

有时候，我们需要通过远程接口来查询数据，然后将其同步到另一个数据库，此时最简单的SQL就是全量查询：

1
2
select *
from t_user;

这种做法虽然简单粗暴，但是如果数据过多，那么势必会影响性能。此时，可以考虑使用id和时间升序来进行控制，每次只同步一批数据，如500条，之后记下同步数据的最大id和时间：

1
2
3
4
5
select *
from t_user 
where id > #{lastId} 
      and create_time >= #{lastCreateTime} 
limit 500;

每次同步完成之后，保存该500条数据中最大的id和时间，给同步下一批数据的时候用。通过这种增量查询的方式，能够极大提升单次查询的效率。

高效分页

一般来说，在进行列表查询时，为避免一次性返回太多数据，我们都会对查询接口进行分页处理。通常是配合limit关键字来使用：

1
2
3
4
select
id,order_name
from t_order
limit 1,10

如果表中数据量不大，用limit关键字做分页，这是允许的。但如果表中数据量很大，再使用它就会出现较为严重的性能问题。举个例子，现在分页变成如下：

1
2
3
4
select
id,order_name
from t_order
limit 1000000,10

此时数据库会查询1000010条数据，然后丢弃前面的1000000条，只查后面的10条数据，毫无疑问这种方式是很浪费资源的。如果id是自增且连续的，那么我们可以先找到上次分页的最大id，然后利用id来进行过滤：

1
2
3
4
5
select
id,order_name
from t_order
where id > 1000000
limit 10

当然了，也可以使用between关键字来优化查询：

1
2
3
4
select
id,order_name
from t_order
where id between 1000000 and 1000010

请注意，between关键字需要在唯一索引上进行分页，不然会出现每页大小不一致的问题。

连接查询替代子查询

当我们需要从多张表中查询数据时，会有两种查询方式：子查询和连接查询。以前面的例子为例，进行说明。下面是一个子查询的例子，子查询通过in关键字实现，一个查询语句的条件落在另一个查询语句的查询结果中。程序会先运行嵌套在最内层的语句，然后运行外层的语句。

1
2
3
4
5
6
7
select * 
from t_order
where user_id in (
    select id 
    from t_user 
    where status=1
)

如果涉及的表数量不多，那可以使用。我们知道MySQL在使用子查询时，先创建临时表，等查询完成后，再删除这些临时表，所以存在一定的性能消耗。此时推荐使用连接查询：

1
2
3
4
select o.* 
from t_order o
inner join t_user u on o.user_id = u.id
where u.status=1

选择合适的字段类型

char表示固定字符串类型，该类型字段存储空间是固定的，因此会浪费存储空间，而varchar表示变长字符串类型，该类型的字段存储空间会根据实际数据的长度进行调整，不会浪费存储空间。

对于长度固定的字段，如用户手机号，一般都是11位，因此可定义成char类型，长度是11字节。对于不固定的字段，如公司名，定义成char类型就不行了。

在定义字段类型时，可以考虑遵循如下原则：

（1）优先使用数字类型，能不用字符串就不用，因为字符的处理往往比数字要慢；

（2）尽量使用小的类型，如使用bit存储布尔值，tinyint存储枚举值；

（3）长度固定的字符串字段，使用char类型；

（4）长度可变的字符串字段，使用varchar类型；

（5）金额字段用decimal，这样可以避免精度丢失问题。

提升group by效率

group by字段使用非常频繁，通常用在去重和分组的时候，一般会配合having字段使用，用于实现分组后再根据条件过滤数据。

举个例子，我们需要统计用户id不大于100的用户，他们各自完成的工单数量，可以使用如下SQL语句：

1
2
3
4
5
6
select 
  user_id,
  count(1) 
from t_order
group by user_id
having user_id <= 100;

不推荐使用这种方式，它是先将所有的用户id和工单数进行了统计，之后再过滤用户id大于100的用户。分组其实是一个非常耗时的操作，我们完全可以先过滤之后再分组的：

1
2
3
4
5
6
select 
  user_id,
  count(1) 
from t_order
where user_id <= 100
group by user_id;

这样就极大的减少了需要分组的数据，当然这里只是介绍一种思路，还有其他的方式。

join表不要太多

笔者建议，join表的数量不要超过3个，一旦超过会出现严重的性能问题：

1
2
3
4
5
6
7
8
9
10
11
12
13
select 
  a.name,
  b.name,
  c.name,
  d.name,
  e.name,
  f.name
from a 
left join b on a.id = b.a_id
left join c on c.b_id = b.id
left join d on d.c_id = c.id
left join e on e.d_id = d.id
left join f on f.e_id = e.id

join太多的话，MySQL在选择索引时，很容易选错索引，而且如果没有命中，那么会分别从两个表中读取一行数据两两进行对比，这样复杂度就是N^2了。

一般三个表合适，

1
2
3
4
5
6
7
8
9
10
select 
  a.name,
  b.name,
  c.name,
  a_d_name,
  b_e_name,
  c_f_name
from a 
left join b on a.id = b.a_id
left join c on c.b_id = b.id

当然在实际开发过程中，某些数据就得通过查询另外几张表中才能获取到，此时可以在a、b、c表中通过添加冗余的字段来实现，如在表a中添加a_d_name，表b中添加b_e_name，表c中添加c_f_name字段，来保存需要查询的数据。所以说，适当的冗余实际上会加快系统的访问速度。

总结

以上10种SQL优化方式是笔者的工作总结，在实际开发过程中得到了很好的验证，提升了开发效率和查询速度。