15个SQL优化的最佳实践

提升SQL的性能

SQL优化是一个人人关注的热门话题。无论你是在面试中还是在工作中，你都有可能遇到它。

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

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

今天我将分享一些关于SQL优化的技巧，希望能对你有所帮助。

1.避免使用 **select ***.

很多时候，我们在写SQL语句时，为了方便，喜欢直接使用select * ，一次性找出表中所有列的数据。

# incorrect exampleselect * from user where id = 1;

在实际业务场景中，也许我们真的只需要使用其中的一两列。我查了很多数据，但是没有，它浪费了数据库资源，比如内存或CPU。

另外，在通过网络IO传输数据的过程中，数据传输时间也会增加。

另一个最重要的问题是：select * ，不会使用覆盖索引，会有大量的回表操作，导致查询SQL的性能降低。

那么，如何优化它呢？

# correct exampleselect name, age from user where id = 1;

在查询SQL语句时，只检查需要使用的列，多余的列根本不需要检查出来。

2.将"**union"**替换为"**union all"**.

我们都知道，在SQL语句中使用union关键字后，我们可以获得重新排序后的数据。

而如果使用union all关键字，可以得到所有的数据，包括重复的数据。

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

重新排序的过程中需要进行遍历、排序和比较，比较耗时，也会消耗更多的CPU资源。

所以，如果你能使用union all，尽量不要使用union。

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

除非有一些特殊情况，比如在union all ，结果集中出现了重复的数据，而业务场景中不允许出现重复的数据，那么可以使用union 。

3.小表带动大表。

小表带动大表，也就是说，小表的数据集带动大表的数据集。

如果有两个表，订单和用户，order 表有10000条数据，user 表有100条数据。

这时，如果你想检查所有有效用户的订单列表。

这可以使用in 关键字来实现。

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

这也可以用exists 关键字来实现。

select * from orderwhere exists (select 1 from user where order.user_id = user.id and status=1)

在上面提到的业务场景中，使用in关键字来实现业务需求是比较合适的。

为什么？

因为在SQL语句中包含in 一个关键字，它会先执行子查询语句in ，然后再执行外面的in语句。如果数据量in ，作为一个条件，查询速度会更快。

而如果SQL语句中含有exists 关键字，它会先执行exists 左边的语句（主查询语句）。

然后用它作为条件来匹配右边的语句。如果匹配，你就可以查询数据。如果没有匹配，数据就被过滤掉了。

在这个要求中，order 表有 10,000 条数据，user 表有 100 条数据。

order 是一个大表，而user 是一个小表。

如果order 在左边，最好使用in 关键字。

综上所述。

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

4.批量操作。

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

# incorrect examplefor (Order order: list) {   orderMapper.insert(order);}

在一个循环中逐一插入数据。

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

这种操作需要多次向数据库请求，以完成这批数据的插入。

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

而如果我们的代码需要多次请求数据库来完成这个业务功能，就不可避免地会消耗更多的性能。

正确的方法是提供一个方法来分批插入数据。

# correct exampleorderMapper.insertBatch(list);

这样，你只需要远程请求数据库一次，SQL的性能就会得到改善。数据越多，改善就越大。

但是，应该注意的是，不建议一次分批操作过多的数据。如果数据太多，数据库的反应会很慢。

批量操作需要把握一个度，建议每批数据尽量控制在500以内。如果数据超过500，就分多批处理。

5.使用限制。

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

# incorrect exampleselect 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);

虽然这种方法在功能上没有问题，但它的效率很低。它需要先查询所有的数据，这就有点浪费资源了。

那么，如何优化它呢？

# correct exampleselect id, create_date  from order where user_id=123 order by create_date asc limit 1;

使用限制1，只返回用户订单时间最小的数据。

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

update order set status=0,edit_time=now(3) where id>=100 and id<200 limit 100;

这样，即使是错误的操作，如id错误，也不会影响到太多的数据。

6.6.不要在关键字中使用过多的值 **in**关键字。

对于批量查询界面，我们通常使用in关键字来过滤掉数据。例如，我想通过一些指定的id来分批查询用户信息。

SQL语句如下。

select id,name from categorywhere id in (1,2,3...100000000);

如果我们不做任何限制，查询语句可能会一次查询大量的数据，这很容易导致接口超时。

那我们应该怎么做呢？

select id,name from categorywhere id in (1,2,3...100)limit 500;

你可以用SQL中的限制来限制数据。

然而，我们更多的是在业务代码中加入限制。伪装代码如下。

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);}

另一个解决方案是：即如果id中的记录超过500条，你可以使用多个线程来分批查询数据。每批中只检查500条记录，最后将查询到的数据汇总并返回。

然而，这只是一个临时的解决方案，不适合id太多的场景。因为id太多，即使能快速检测到数据，如果返回的数据量太大，网络传输非常耗费性能，接口性能也不会有太大的提升。

7.增量查询。

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

# incorrect exampleselect * 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 来优化分页。

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

需要注意的是，之间应该在唯一的索引上进行分页，否则会出现每页大小不一致的情况。

9.用连接查询代替子查询。

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

一个子查询的例子如下。

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

子查询语句可以通过在关键字中实现，一个查询语句的条件落在另一个选择语句的查询结果中。程序先运行嵌套的最内层语句，然后再运行外层语句。

子查询语句的优点是，如果涉及的表的数量不多，它的结构就很简单。

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

这个时候，可以改成连接查询。

select o.* from order oinner join user u on o.user_id = u.idwhere u.status=1

10.连接表不应该太多。

# incorrect exampleselect a.name,b.name.c.name,d.namefrom a inner join b on a.id = b.a_idinner join c on c.b_id = b.idinner join d on d.c_id = c.idinner join e on e.d_id = d.idinner join f on f.e_id = e.idinner join g on g.f_id = f.id

如果有太多的join ，MySQL在选择索引时就会很复杂，很容易选错索引。

而如果没有命中的话，嵌套循环连接是要从两个表中读取一行数据进行配对比较的，复杂度是n²。

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

# correct exampleselect a.name,b.name.c.name,a.d_name from a inner join b on a.id = b.a_idinner join c on c.b_id = b.id

如果在执行业务场景中需要查询其他表中的数据，可以在a、b、c表中设置冗余的特殊字段，比如在a表中设置冗余的d_name字段，以保存要查询的数据。

不过，我以前也见过一些ERP系统。并发量不大，但业务相对复杂。它需要连接十几个表来查询数据。

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

11.内联接注意事项。

当涉及到join 一个多表的查询时，我们一般会使用join关键字。

最常用的连接是left join 和inner join 。

• left join:找到两个表的交集，加上左边表中的剩余数据。
• inner join: 找到两个表的交集的数据。

一个使用inner join 的例子如下。

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

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

使用left join 的例子如下。

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

如果两个表使用left join ，MySQL将默认使用左连接关键字来驱动右边的表。如果左边的表有大量的数据，就会出现性能问题。

应该注意的是，当使用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字节，而实际的企业长度只有50字节，150字节的存储空间将被浪费掉。

如果长度被定义得太短，例如，它被定义为50字节，但实际的企业名称有100字节，它将不会被存储，并且会抛出一个异常。

因此，建议将企业名称改为varchar 类型。变长字段的存储空间小，可以节省存储空间，对于查询来说，在相对较小的字段中搜索效率明显更高。

当我们选择字段类型时，应该遵循这些原则。

如果你能使用数字类型，就不需要字符串，因为字符的处理速度往往比数字慢。

尽量使用小的类型，比如用bit 来存储布尔值，tinyint 来存储枚举值，等等。

一个固定长度的字符串字段，类型为char 。

一个可变长度的字符串字段，类型为varchar 。

decimal 为金额字段使用，以避免精度损失的问题。

14.提高效率 **group by**

我们有很多业务场景需要使用group by 关键字。它的主要功能是重复计算和分组。

通常情况下，它与having ，也就是分组，然后根据某些条件过滤数据。

# incorrect exampleselect user_id,user_name from ordergroup by user_idhaving user_id <= 200;

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

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

# correct exampleselect user_id,user_name from orderwhere user_id <= 200group by user_id

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

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

15.索引优化。

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

在很多情况下，使用索引和不使用索引时，SQL语句的执行效率是非常不同的。因此，索引优化是SQL优化的第一选择。

索引优化的第一步是检查SQL语句是否有索引。

那么，如何检查SQL是否进入了索引呢？

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

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