平时做技术实践时，很多问题不是概念不会，而是细节没串起来。拿“sql优化之减少数据库堆栈性能的12个小技巧整理总结”来说，它看着像小点，放到项目里常会牵出环境、配置、兼容性和维护成本。下面按实际采用顺序，把思路、关键写法和容易踩坑的地方讲清楚，便于大家直接对照操作。
前言
显而易见，SQL运用广，但数据库堆栈性能问题也事故百出。
从实现思路看，前段时间我们一个同事犯了一个大错，一个关联查询的sql没加分区过滤数据尽可能缩小数据范围，中间结果集很大，导致整个集群空间撑爆，间接导致集群内部的其他应用也崩溃，这都属于是严重的生产事故。
sql生产大致事件如下所示：
sql优化是一个大家都比较关注的热门话题。
结合项目来看，若某天你负责的某个线上接口，出现了性能问题，需做优化。那么你首先想到的很有可能是优化sql语句，因为它的改造成本相对于代码来说也要小得多。
此篇文章从12个方面，分享了sql优化的一些小技巧，希望对你有所帮助
那么，如何优化sql语句呢？
1.列裁剪
即避免采用select * ,能够只读取查询中所需用到的列，而忽略其它列。
实际处理时，在实际业务场景里，可能我们真正需采用的只有其中一两列。查了很多数据，但是不用，傻傻增加数据库堆栈性能，如：内存或者cpu。
在这个场景下，此外，多查出来的数据，借助网络IO传输的过程中，也会增加数据传输的时间。
举个栗子：
select * from word_text; 【x错】
select word,100,num from word_text; 【√正确】
2.分区裁剪 提升group by的效率
栗子：
select userId ,userAccount from f_user group by userId where userId < 500; 【x错】
select userId ,userAccount from f_user where userId < 500 group by userId ; 【√正确】
我们有很多业务场景需采用group by关键字，它主要的功能是去重和分组。
通常它会跟having一起配合采用，表示分组后再根据一定的条件过滤数据。
从实现思路看，这种写法性能不好，它先把所有的订单根据用户id分组之后，再去过滤用户id大于等于200的用户。
分组是一个相对耗时的操作，为什么我们不先缩小数据的范围之后，再分组呢？
结合项目来看，采用where条件在分组前，就把多余的数据过滤掉了，这样分组时效率就会更高一些。
实际处理时，其实这是一种思路，不仅限于group by的优化。我们的sql语句在做一些耗时的操作之前，应尽可能缩小数据范围，这样能提升sql整体的性能。
3.谓词下推
predicate
从实现思路看，所谓的谓词下推就是在join之前的阶段提前对表进行过滤优化，使得最后参与join的表的数据量更小
谓词下推 --不扫描全表的表才能实现谓词下推，下面谓词是指name字段
select
*
from
(
select * from table1
) t1
join
(
select * from table2
) t2
on
t1.id=t2.id
where
t1.name='user1' and t2.name='user2';
-- 谓词下推
-- 针对内连接和全连接无论筛选条件写在哪里都不能实现谓词下推
select
*
from
(
select * from table1 where name='user1'
) t1
join
(
select * from table2 where name='user2'
) t2
on
t1.id=t2.id
-- 左连接谓词下推
-- 如下能够实现谓词下推的例子，如下t1表是不能够实现谓词下推，会扫描全表（左表是全表），但是t2表是可以实现谓词下推，
-- join的时候只扫描满足where条件的那部分数据
select
*
from
(
select * from table1 where name='user1'
) t1
left join
(
select * from table2 where name='user2'
) t2
on
t1.id=t2.id;
-- 右连接时谓词下推
select
*
from
(
select * from table1 where name='user1'
) t1
right join
(
select * from table2 where name='user2'
) t2
on
4.用union all代替union
sql语句采用union关键字后，能够拿到排重后的数据。
而如果采用union all关键字，能够拿到所有数据，包含重复的数据。
反栗子：
(select userId ,userAccount from f_user where userId=1 )
union
((select userId ,userAccount from f_user where userId=2);
排重的过程需遍历、排序和比较，它更耗时，更消耗cpu资源。
所以如果能用union all的时候，尽量不用union。
正栗子：
(select userId ,userAccount from f_user where userId=1 )
union all
((select userId ,userAccount from f_user where userId=2);
结合项目来看，除非是有些特殊的场景，比如union all之后，结果集中出现了重复数据，而业务场景中是不允许产生重复数据的，这时能够采用union。
5.小表驱动大表
in和exists用法
小表驱动大表，也就是说用小表的数据集驱动大表的数据集。
理解这一步时，假如有f_post（帖子表）和f_user_relationship_thumb(用户关系表)两张表，其中f_post表有10000条数据，而f_user_relationship_thumb表有100条数据。
这时如果想查一下，仅查我的好友帖子列表。
能够采用in关键字实现：**(备注：**以下是从用户关系表中获得我的好友，再借助好友userAccount关联帖子表中的userAccount获得好友帖子数据)
select * FROM f_post where userAccount
in (
SELECT userAccount FROM f_user_relationship_thumb
WHERE friendsId = #{userId} AND reviewStatus = 1
UNION
SELECT friendsUserAccount as userAccount FROM f_user_relationship_thumb
WHERE userId = #{userId} AND reviewStatus = 1
LIMIT 500
)
也能够采用exists关键字实现：
select * FROM f_post where userAccount
exists (
SELECT userAccount FROM f_user_relationship_thumb
WHERE friendsId = #{userId} AND reviewStatus = 1
UNION
SELECT friendsUserAccount as userAccount FROM f_user_relationship_thumb
WHERE userId = #{userId} AND reviewStatus = 1
LIMIT 500
)
前面提到的这种业务场景，采用in关键字去实现业务需求，更加合适。
为什么呢？
结合项目来看，因为如果sql语句中包含了in关键字，则它会优先执行in里面的子查询语句，随后再执行in外面的语句。如果in里面的数据量很少，作为条件查询速度更快。
实际处理时，而如果sql语句中包含了exists关键字，它优先执行exists左边的语句（即主查询语句）。随后把它作为条件，去跟右边的语句匹配。如果匹配上，则能够查询出数据。如果匹配不上，数据就被过滤掉了。
落到代码里，这个需求中，其中f_post表有10000条数据，而f_user_relationship_thumb表有100条数据。f_post表是大表，f_user_relationship_thumb表是小表。如果f_post表在左边，则用in关键字性能更好。
结论：
- 其实in、exists在算法复杂度Log N层面来讲，大差不差
in适用来左边大表，右边小表。exists适用来左边小表，右边大表。
不管是用in，还是exists关键字，其核心思想都是用小表驱动大表。
真实栗子如图：
6.批量操作 减少对数据库的操作次数
若你有一批数据经过业务处理之后，需插入数据，该怎么办？
栗子：批量上传图片
反栗子：
for(photoNames photoName: list){
postMapper.insert(photoName):
}在循环中逐条插入数据。
insert into f_post(id,photoName)
values(001,'/assets/userPost/photoName1');
...
该操作需多次请求数据库，才能完成这批数据的插入。
落到代码里，但众所周知，我们在代码中，每次远程请求数据库，是会消耗一定性能的。而如果我们的代码需请求多次数据库，才能完成本次业务功能，势必会消耗更多的性能。
那么如何优化呢？
正例√：
postMapper.insertBatch(list):sql
提供一个批量插入数据的方法。
insert into f_post(id,photoName)
values(001,'/assets/userPost/photoName1'),(001,'/assets/userPost/photoName2'),(001,'/assets/userPost/photoName3');
落到代码里，这时只需远程请求一次数据库，sql性能会得到提升，数据量越多，提升越大。
在这个场景下，但这里需留意，不建议一次批量操作太多的数据，如果数据太多数据库响应也会很慢。批量操作需把握一个度，建议每批数据尽量控制在500以内。如果数据多于500，则分多批次处理。
7.巧用limit
实际处理时，举个栗子，有时候，我们需查询某些数据中的第一条，比如：查询某个用户下的第一个帖子，想看看他第一次的发帖时间。
反栗子：
select id, createTime
from f_post
where userAccount='500佰'
order by createTime asc;
实际处理时，根据用户userAccount查询帖子，按帖子新建时间排序，先查出该用户所有的帖子数据，得到一个帖子集合。随后在代码中，拿到第一个元素的数据，即第一个帖子的数据，就能拿到第一次发帖时间。
List<Post> list = postMapper.getPostList();
Post post = list.get(0);
落到代码里，虽说这种做法在功能上没有问题，但它的效率很不高，需先查询出所有的数据，有点浪费资源。
那么，如何优化呢？
正栗子：
select id, createTime
from f_post
where userAccount='500佰'
order by createTime asc
limit 1;
采用limit 1，只得到该用户第一次发帖的那一条数据即可。
8. in中值太多
理解这一步时，轻松说，对于批量查询接口，我们通常会采用in关键字过滤出数据。比如：想借助指定的一些id，批量查询出用户信息。
sql语句如下所示：
select * FROM f_post where userId in ( 1,2,3...9999999);
从实现思路看，若我们不做任何限制，该查询语句一次性可能会查询出很多的数据，很容易导致接口超时。
这时该怎么办呢？
select * FROM f_post where userId in (1,2,3...100) limit 500;
能够在sql中对数据用limit做限制。
不过我们更多的是要在业务代码中加限制，伪代码如下所示：
public List<Post> getPost(List<String> uids) {
if(CollectionUtils.isEmpty(uids)) {
return null;
}
if(uids.size() > 500) {
throw new BusinessException("每次最多允许查询500条记录")
}
return mapper.getPostList(uids);
}还有一个方案就是：如果uids超过500条记录，能够分批用多线程去查询数据。每批只查500条记录，最后把查询到的数据汇总到一起得到。不过这只是一个临时方案，不适合于uids实在太多的场景。因为uids太多，即使能更快查出数据，但如果得到的数据量太大了，网络传输也是很消耗性能的，需留意这一点。
9.高效的分页查询
结合项目来看，轻松说，有时候，列表页在查询数据时，为了避免一次性得到过多的数据影响接口性能，我们一般会对查询接口去做分页处理。
分页一般用的limit关键字：
select userId,userName,userAvatar
from f_user limit 10,20;
理解这一步时，若表中数据量少，用limit关键字做分页，没啥问题。但如果表中数据量很多，用它就会出现性能问题。
比如现在分页参数变成了：
select userId,userName,userAvatar
from f_user limit 1000000,20;
理解这一步时，这样会查到1000020条数据，随后丢弃前面的1000000条，只查后面的20条数据，这个是消浪费据库资源和增加堆栈性能。
那么，这种海量数据该怎么分页呢？
所谓高效分页,优化sql：
select userId,userName,userAvatar
from f_user where userId > 1000000 limit 20;
结合项目来看，先找到上次分页最大的id，然后借助id上的索引查询。不过该方案，要求id是连续的，同时且有序的。
还能采用between优化分页。
select userId,userName,userAvatar
from f_user where userId between 1000000 and 1000020;
从实现思路看，这里需留意between要在唯一索引上分页，不然会出现每页大小不一致的问题。
10.用连接查询代替子查询
为啥？原因在哪？
结合项目来看，原因是执行子查询时，需新建临时表，查询完毕后，需再删除这些临时表，有一些额外的性能消耗。
子查询的栗子如下所示：
实际处理时，若需从两张以上的表中查询出数据的话，一般有两种实现方法：子查询 和 连接查询。
select * FROM f_post where userAccount
in (
SELECT userAccount FROM f_user_relationship_thumb
WHERE friendsId = #{userId} AND reviewStatus = 1
UNION
SELECT friendsUserAccount as userAccount FROM f_user_relationship_thumb
WHERE userId = #{userId} AND reviewStatus = 1
LIMIT 500
)
**执行逻辑：**子查询语句能够借助in关键字实现，一个查询语句的条件落在另一个select语句的查询结果中。程序先运行在嵌套在最内层的语句，再运行外层的语句。
若涉及的表数量不多的话，子查询语句的优点是轻松，结构化。
这时能够改成连接查询减小临时额外的性能消耗。具体栗子如下所示：
select * FROM f_post where userAccount
JOIN (
SELECT userAccount FROM f_user_relationship_thumb
WHERE friendsId = #{userId} AND reviewStatus = 1
UNION
SELECT friendsUserAccount as userAccount as remarks FROM f_user_relationship_thumb
WHERE userId = #{userId} AND reviewStatus = 1
LIMIT 500
) fr ON pm.userAccount = fr.userAccount
11.连续join的表不宜超过3个
从实现思路看，根据阿里巴巴开发者手册的规定，join表的数量不应该超过3个。
连续join多表
select a.name,b.name.c.name,d.name,e.name,f.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太多，在选择索引的时候会很复杂，很容易选错索引。
解决办法：如果实现业务场景中需查询出另外几张表中的数据，能够在a、b、c表中冗余专门的字段
12. join时需留意
实际处理时，涉及到多张表联合查询的时候，一般会采用join关键字。
而join采用最多的是left join和inner join。
left join：求两个表的交集外加左表剩下的数据。inner join：求两个表交集的数据。- 理解这一步时，另外不管是left join、inner join还是其他jion操作，切记一定不要采用多个连续的jion 这样会极大的增加查询数据库堆栈。
落到代码里，若两张表采用inner join关联，会自动选择两张表中的小表，去驱动大表，所以性能上不会有太大的问题。
在这个场景下，若两张表采用left join关联，会默认用left join关键字左边的表，去驱动它右边的表。如果左边的表数据很多时，就会出现性能问题。
特别提醒：在用left join关联查询时，左边要用小表，右边能够用大表。如果能用inner join的地方，尽量少用left join。
阅读到此，您已经超越99%的技术boy
最后：
在这个场景下，若这篇sql优化文章对您有所帮助，或者有所启发的话，给作者一个小关，您的兼容是的精神粮食。
到此这篇关于sql优化之减少数据库堆栈性能的12个小技巧总结的文章就介绍到这了,更多相关sql减少数据库堆栈性能内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多兼容脚本之家！
- 从MySQL得到最大的优化性能
- 分析SQL语句性能3种方法分享
- 基于MySQL分区性能的详细说明
- 大幅优化MySQL查询性能的奇技淫巧
- 10个MySQL性能调优的方法