SQL优化是大家都关心的热门话题。无论你是在面试还是在工作中，你都可能会遇到它。

如果有一天你负责的线上界面出现了性能问题，你就需要对其进行优化。那么你的第一个想法可能是优化 SQL 语句，因为它的转换成本比代码小得多。#sql优化#

那么，如何优化SQL语句呢？

今天我将分享一些关于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"替换为为”union all"。

我们都知道，在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较小，作为条件查询速度会比较快。

如果 SQL 语句包含exists关键字，则首先执行的是exists左侧的语句(主查询语句)。然后用它作为条件来匹配右边的语句。如果匹配，就可以查询数据。如果不匹配，则过滤掉数据。

在这个需求中，order表有10000条数据，user表有100条数据。

order是大表，user是小表。

如果order位于左侧，则最好使用关键字in。

综上所述：

in适用于大表在左边和小表在右边。
exists适用于小表在左边和大表在右边。

4.批量操作

如果您有一批数据需要在业务处理后插入怎么办？

# 不正确的示例
(Order order: list) { 
   orderMapper.insert(order); 
}
循环中一项一项地插入数据。

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

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

但众所周知，在我们的代码中，每次远程请求数据库时，都会消耗一定的性能。

而如果我们的代码需要多次请求数据库来完成这个业务功能，那么必然会消耗更多的性能。

正确的做法是提供批量插入数据的方法。

# 正确示例
orderMapper.insertBatch(list);
# insert order(id,code,user_id) 
# value (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;

使用 limit 1 只返回用户下单时间最早的数据。

另外，在删除或修改数据时，为了防止误操作，导致删除或修改无关数据，limit也可以添加在SQL语句末尾。

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("too many")
   }
   return mapper.getCategoryList(ids);
}

另一种解决方案是：如果ids超过500条记录，可以使用多线程批量查询数据。每批次只查询500条记录，最后将查询到的数据进行聚合返回

不过这只是一个临时方案，不适合id太多的场景。因为id太多，即使能够快速查询到数据，如果返回的数据量太大，网络传输也非常消耗性能，接口性能也好不到哪里去。

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会找到1,000,020条数据，然后丢弃前1,000,000条数据，只检查最后20条数据，这会造成极大的资源浪费。

那么，如何对如此海量的数据进行分页呢？

优化Sql:

select id,name,age from user where id > 1000000 limit 20;

首先找到最后一次分页的最大的id，然后利用id上的索引进行查询。但此方案中要求id是连续且有序的。

还可以使用 Between（因为between会走索引）来优化分页。

select id,name,age from user where id between 1000000 and 1000020;

查询id、name、age , id 介于 1000000 和 1000020 之间的用户；

9. 用连接查询替换子查询

MySQL中如果需要查询两个以上表的数据，一般有两种实现方式：子查询和连接查询。

子查询的示例如下：

select * from order
where user_id in (select id from user where status=1)

子查询语句可以通过in关键字来实现，一条查询语句的条件落入另一条select语句的查询结果之内。程序首先运行嵌套的最内层语句，然后运行外层语句。

子查询语句的优点是语句简单，如果涉及的表数据量较少。

但缺点是MySQL执行子查询时需要创建临时表。查询完成后，需要删除这些临时表，这有一些额外的性能消耗。

这时可以改为连接查询。

select o.* from order o
inner join user u on o.user_id = u.id
where u.status=1

10、Join表不宜过多

# 错误示例
select a.name,b.name.c.name,d.name
from a 
inner join b on a.id = b.a_id
inner join c on c.b_id = b.id
inner join d on d.c_id = c.id
inner join e on e.d_id = d.id
inner join f on f.e_id = e.id
inner join g on g.f_id = f.id

如果Join太多，MySQL在选择索引时会非常复杂，而且很容易选择错误的索引。

而如果没有命中索引，嵌套循环连接就是从两个表中读取一行数据进行两两比较，复杂度为n²。

所以我们应该尽量控制连接表的数量。

# 正确示例
select a.name,b.name.c.name,a.d_name 
from a 
inner join b on a.id = b.a_id
inner join c on c.b_id = b.id

不过，我之前也见过一些ERP系统。并发量不大，但业务比较复杂。需要连接十几张表来查询数据。

因此，连接表的数量要根据系统的实际情况来确定。不能一概而论。越少越好。

11.inner join

当涉及到多个表的连接查询时，我们通常使用 join 关键字。

最常用的连接是左连接和内连接。

左连接：求两个表的交集加上左表中剩余的数据。
内连接：查找两个表的交集数据。

使用内连接的示例如下：

select o.id,o.code,u.name from order o 
inner join user u on o.user_id = u.id
where u.status=1;

如果两个表使用inner join关联起来，MySQL会自动选择两个表中的小表来驱动大表，所以在性能上不会有太多的问题。

使用左连接的示例如下：

select o.id,o.code,u.name from order o 
left join user u on o.user_id = u.id
where u.status=1;

如果两个表使用left join关联，MySQL默认会使用left join左边的表来驱动右边的表。如果左表数据很多，就会出现性能问题。

需要注意的是，使用left join查询时，左侧使用小表，右侧使用大表。如果可以使用 inner join，就尽量少用left join。

12.限制索引的数量

众所周知，索引可以显著提高查询SQL的性能，但索引并不是越多越好。

因为当新的数据添加到表中时，需要同时为其构建索引，而构建索引需要额外的存储空间和一定的性能消耗。

单表索引数量尽量控制在5个以内，单个索引字段数量不要超过5个

MySQL用来保存索引使用的是B+树的结构，B+树索引在插入、更新和删除操作时需要更新索引树的结构。如果索引过多，会消耗大量额外的性能。

那么，问题就是，如果表中索引太多，超过5个怎么办？

这个问题需要辩证地看待。如果你的系统并发量较低，表的数据量不是太多，其实5个以上也可以，只要不超过太多即可。

但对于一些高并发的系统，一定要遵守单表不超过5个索引的限制。

那么，高并发系统如何优化索引数量呢？

如果能建联合索引，就不要建单一索引，可以删除无用的单一索引。

将一些查询功能迁移到其他类型的数据库，如Elastic Seach、HBase等，只需要在业务表中构建几个关键索引即可。

13. 选择适当的字段类型。

char代表固定的字符串类型，该类型的字段的存储空间是固定的，会浪费存储空间。

alter table order add column code char(20) NOT NULL;

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

alter table order add column code varchar(20) NOT NULL;

如果是固定长度的字段，比如用户的手机号码，一般为11位，可以定义为长度为11字节的char类型。

但如果是姓名字段，如果定义为char类型，就有问题了。

如果长度定义过长，比如定义为200字节，而name实际长度只有50字节，就会浪费150字节的存储空间。如果长度定义太短，比如定义为50字节，但实际name有100字节，则不会存储，并抛出异常。因此，建议将name改为varchar类型。变长字段的存储空间较小，可以节省存储空间，而且对于查询来说，在相对较小的字段中搜索效率明显更高。

我们在选择字段类型时，应该遵循以下原则：

如果可以使用数字类型，则不需要字符串，因为字符的处理速度往往比数字慢。
尽可能使用小类型，比如用bit存储布尔值，用tinyint存储枚举值等。
固定长度字符串字段，类型为 char。可变长度字符串字段，类型为 varchar。
decimal字段使用十进制以避免精度损失的问题。

14、提高group by的效率

我们有很多业务场景需要使用group by关键字。它的主要功能是去重和分组。通常与have结合使用，表示对数据进行分组，然后按照一定的条件进行过滤。

# 错误示例
select user_id,user_name from order group by user_id
having user_id <= 200;

这种写法性能较差。它首先根据用户id对所有订单进行分组，然后过滤用户id大于或等于200的用户。

分组是一个比较耗时的操作，为什么我们不在分组之前缩小数据范围呢？

# 正确示例
select user_id,user_name from order where user_id <= 200
group by user_id

分组前使用where条件过滤掉冗余数据，这样分组时效率会更高。

其实这是一个套路，并不局限于group by的优化。在我们的SQL语句做一些耗时的操作之前，我们应该尽可能的缩小数据范围，这样可以提高SQL的整体性能。

15、索引优化

在SQL优化中，有一个非常重要的内容：索引优化。

很多情况下，使用索引和不使用索引时SQL语句的执行效率有很大差异。因此，索引优化是SQL优化的首选。

索引优化的第一步是检查SQL语句是否命中索引。那么，如何检查SQL是否到了索引呢？

可以使用explain命令查看MySQL的执行计划。

explain select * from `order` where code='002';

具体详细参数我会单独写一篇文章来解释。

以下是索引失败的一些常见原因：

不满足最左前缀原则。
范围索引列没有放在最后。
使用选择 * 。
对索引列进行数据计算。
索引列上有函数。
不带引号的字符串类型。
错误使用%。
错误使用 null 。
错误使用or .

如果不是上述原因，则需要进一步排查其他原因。

如果需要，可以使用force index来强制查询SQL走到某个索引。

如果喜欢这篇文章，点赞支持一下，关注我第一时间查看更多内容！

SQL 优化的 15 个最佳实践