关于sql:聊聊sql优化的15个小技巧

前言

面试材料、经验总结，开发技巧，更多干货内容在：https://github.com/dvsusan/susanSayJava

sql 优化是一个大家都比拟关注的热门话题，无论你在面试，还是工作中，都很有可能会遇到。

如果某天你负责的某个线上接口，呈现了性能问题，须要做优化。那么你首先想到的很有可能是优化 sql 语句，因为它的革新老本绝对于代码来说也要小得多。

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

这篇文章从 15 个方面，分享了 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 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 右边的语句（即主查问语句）。而后把它作为条件，去跟左边的语句匹配。如果匹配上，则能够查问出数据。如果匹配不上，数据就被过滤掉了。

这个需要中，order 表有 10000 条数据，而 user 表有 100 条数据。order 表是大表，user 表是小表。如果 order 表在右边，则用 in 关键字性能更好。

总结一下：

• in 实用于右边大表，左边小表。
• exists 实用于右边小表，左边大表。

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

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;

应用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，批量查问出用户信息。

sql 语句如下：

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

还有一个计划就是：如果 ids 超过 500 条记录，能够分批用多线程去查问数据。每批只查 500 条记录，最初把查问到的数据汇总到一起返回。

不过这只是一个长期计划，不适宜于 ids 切实太多的场景。因为 ids 太多，即便能疾速查出数据，但如果返回的数据量太大了，网络传输也是十分耗费性能的，接口性能始终好不到哪里去。

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 会查到 1000020 条数据，而后抛弃后面的 1000000 条，只查前面的 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;

须要留神的是 between 要在惟一索引上分页，不然会呈现每页大小不统一的问题。

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 的表不宜过多

依据阿里巴巴开发者手册的规定，join 表的数量不应该超过 3 个。

反例：

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 在抉择索引的时候会非常复杂，很容易选错索引。

并且如果没有命中中，nested loop join 就是别离从两个表读一行数据进行两两比照，复杂度是 n^2。

所以咱们应该尽量管制 join 表的数量。

正例：

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

如果实现业务场景中须要查问出另外几张表中的数据，能够在 a、b、c 表中 冗余专门的字段，比方：在表 a 中冗余 d_name 字段，保留须要查问出的数据。

不过我之前也见过有些 ERP 零碎，并发量不大，但业务比较复杂，须要 join 十几张表能力查问出数据。

所以 join 表的数量要依据零碎的理论状况决定，不能一概而论，尽量越少越好。

11 join 时要留神

咱们在波及到多张表联结查问的时候，个别会应用 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.id
where 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.id
where u.status=1;

如果两张表应用 left join 关联，mysql 会默认用 left join 关键字右边的表，去驱动它左边的表。如果右边的表数据很多时，就会呈现性能问题。

要特地留神的是在用 left join 关联查问时，右边要用小表，左边能够用大表。如果能用 inner join 的中央，尽量少用 left join。

12 管制索引的数量

家喻户晓，索引可能显著的晋升查问 sql 的性能，但索引数量并非越多越好。

因为表中新增数据时，须要同时为它创立索引，而索引是须要额定的存储空间的，而且还会有肯定的性能耗费。

阿里巴巴的开发者手册中规定，单表的索引数量应该尽量管制在 5 个以内，并且单个索引中的字段数不超过 5 个。

mysql 应用的 B + 树的构造来保留索引的，在 insert、update 和 delete 操作时，须要更新 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 位的，能够定义成 char 类型，长度是 11 字节。

但如果是企业名称字段，如果定义成 char 类型，就有问题了。

如果长度定义得太长，比方定义成了 200 字节，而理论企业长度只有 50 字节，则会节约 150 字节的存储空间。

如果长度定义得太短，比方定义成了 50 字节，但理论企业名称有 100 字节，就会存储不下，而抛出异样。

所以倡议将企业名称改成 varchar 类型，变长字段存储空间小，能够节俭存储空间，而且对于查问来说，在一个绝对较小的字段内搜寻效率显然要高些。

咱们在抉择字段类型时，应该遵循这样的准则：

1. 能用数字类型，就不必字符串，因为字符的解决往往比数字要慢。
2. 尽可能应用小的类型，比方：用 bit 存布尔值，用 tinyint 存枚举值等。
3. 长度固定的字符串字段，用 char 类型。
4. 长度可变的字符串字段，用 varchar 类型。
5. 金额字段用 decimal，防止精度失落问题。

还有很多准则，这里就不一一列举了。

14 晋升 group by 的效率

咱们有很多业务场景须要应用 group by 关键字，它次要的性能是去重和分组。

通常它会跟 having 一起配合应用，示意分组后再依据肯定的条件过滤数据。

反例：

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';

后果：

通过这几列能够判断索引应用状况，执行打算蕴含列的含意如下图所示：

如果你想进一步理解 explain 的具体用法，能够看看我的另一篇文章《explain | 索引优化的这把绝世好剑，你真的会用吗？》

说实话，sql 语句没有走索引，排除没有建索引之外，最大的可能性是索引生效了。

上面说说索引生效的常见起因：

如果不是下面的这些起因，则须要再进一步排查一下其余起因。

此外，你有没有遇到过这样一种状况：明明是同一条 sql，只有入参不同而已。有的时候走的索引 a，有的时候却走的索引 b？

没错，有时候 mysql 会选错索引。

必要时能够应用 force index 来强制查问 sql 走某个索引。

至于为什么 mysql 会选错索引，前面有专门的文章介绍的，这里先留点悬念。

最初说一句(求关注，别白嫖我)

如果这篇文章对您有所帮忙，或者有所启发的话，帮忙扫描下发二维码关注一下，您的反对是我保持写作最大的能源。

求一键三连：点赞、转发、在看。

关注公众号：【苏三说技术】，在公众号中回复：面试、代码神器、开发手册、工夫治理有超赞的粉丝福利，另外回复：加群，能够跟很多 BAT 大厂的前辈交换和学习。