聊聊SQL优化的几个小技巧

当前位置：点晴教程→知识管理交流 →『技术文档交流』

admin

2024年1月15日 14:16 本文热度 451

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

那么，如何优化Sql呢？本章节分享了12个小技巧，我们一起来学习下。

1、避免使用select * 查询

很多时候，为了使用起来方便简单，我们喜欢直接使用select * 来查询数据。

反例：

select * from TP_SYS_USER_0118;

但实际开发过程中，我们可能并不需要返回所有的字段列，而是其中的某几列，这个时候建议大家直接列出查询字段。

正例：

select OBJECT_ID,LOGIN_NAME,ENAME from TP_SYS_USER_0118;

使用select * 的弊端：

1.性能问题：SELECT * 会检索表中所有列的数据，包括可能不需要的列，如果表中有大量列或存在大字段的列，将会严重影响性能。
2.安全性问题：SELECT * 查询可能返回敏感数据，通过明确定义所需的列，可以减少意外暴露敏感信息的风险。

2、使用union all替换union

union：union操作符会合并两个查询结果集，并去除重复的行，只保留一个副本。

select OBJECT_ID, LOGIN_NAME, ENAMEfrom TP_SYS_USER_0118unionselect OBJECT_ID, LOGIN_NAME, ENAMEfrom TP_SYS_USER;

union all：union all 不去除重复行，直接将两个查询结果集合并在一起。

select OBJECT_ID, LOGIN_NAME, ENAMEfrom TP_SYS_USER_0118union allselect OBJECT_ID, LOGIN_NAME, ENAMEfrom TP_SYS_USER;

从性能层面分析：

1、由于 union 需要执行去重操作，因此在处理大量数据时，性能可能较低。而union all 则是直接合并结果，不需要去重。

在实际使用过程中，我们还是要视情况而定，比如说我们就是需要去重复的行数据，则需要使用union。

3、避免使用子查询

如果我们想要从两张或者多张表中查询到数据，通常的实现方式：连接查询或者子查询。

子查询的例子如下：

select s1.OBJECT_ID, s1.LOGIN_NAME, s1.ENAMEfrom TP_SYS_USER s1where OBJECT_ID in (select s2.OBJECT_ID from TP_DIM_OBJECT s2);

网上查询有关不建议使用子查询的缘由是：需要创建临时表，查询完毕后，需要再删除这些临时表，有一些额外的性能消耗。

建议调整为连接查询：

select s1.OBJECT_ID, s1.LOGIN_NAME, s1.ENAME from TP_SYS_USER s1inner join TP_DIM_OBJECT s2 on s1.OBJECT_ID=s2.OBJECT_ID;

4、多表查询时一定要以小驱大

例如我们使用left join 连表查询：

场景一：以大表驱动小表

# TP_SYS_USER大表驱动TP_SYS_USER_0118小表select s1.OBJECT_ID, s1.LOGIN_NAME, s1.ENAME, s2.OBJECT_ID, s2.LOGIN_NAME, s2.ENAMEfrom TP_SYS_USER s1 left join TP_SYS_USER_0118 s2 on s1.OBJECT_ID = s2.OBJECT_ID;

通过explain分析SQL的执行计划：

我们发现两个表的type都是ALL：表示全表分析，然后才找到匹配的行，我们可以通过字段rows看到查询的行数据。

场景二:以小表驱动大表

# TP_SYS_USER_0118小表驱动TP_SYS_USER大表select s1.OBJECT_ID, s1.LOGIN_NAME, s1.ENAME, s2.OBJECT_ID, s2.LOGIN_NAME, s2.ENAMEfrom TP_SYS_USER_0118 s1 left join TP_SYS_USER s2 on s1.OBJECT_ID = s2.OBJECT_ID;

通过explain分析SQL的执行计划：

我们可以看到s2表的type是eq_ref：表示查询时命中主键 primary key 或者 unique key 索引，这里使用的是primary key。并且查询rows的行数是1

通过explain分析我们可以很明显的对比出来，用小表驱动大表的时候，查询效率更高些。

或者我们也可以使用inner join 来替换left join的以小表驱动大表的方案。

5、不要使用like左模糊和全模糊查询

场景一：使用like右模糊查询

# USER_CODE存在索引select * from TP_SYS_USER where USER_CODE like 'yg02%';

通过explain分析SQL的执行计划：

我们可以看到key（实际使用的索引）是有值的：

执行结果中key有值：表示查询中实际使用的索引为IP_SYS_USER_USER_CODE。

场景二：使用like左模糊查询

# USER_CODE存在索引select * from TP_SYS_USER where USER_CODE like '%yg02';

通过explain分析SQL的执行计划：

执行结果中key为null表示没有使用索引查询。

场景三：使用like全模糊查询

# USER_CODE存在索引 select * from TP_SYS_USER where USER_CODE like '%yg02%';

通过explain分析SQL的执行计划：

执行结果中key为null表示没有使用索引查询。

通过以上几种场景对比，我们可以看到like左模糊查询和全模糊查询都是没有走索引的，所以查询效率较低，我们不建议这么使用。

6、exists&in的合理利用

exists&in一般用于子查询。

# 使用in子查询select s1.* from TP_DIM_OBJECT s1where OBJECT_ID in (select s2.OBJECT_ID from TP_SYS_USER s2);
# 使用exists子查询select s1.* from TP_DIM_OBJECT s1where exists(select 1 from TP_SYS_USER s2 where s1.OBJECT_ID=s2.OBJECT_ID); s1.OBJECT_ID=s2.OBJECT_ID);

exists：一般情况下，如果子查询的数据量大，可能exists会更高效，因为它只关系是否存在给外表匹配的行，而不需要返回具体的数据。
in：使用in一般会先进行内表查询，然后对外表查询，匹配结果集；当子查询寻得结果集较小，外表较大时，使用in效率更高。

7、在表中增加索引，优先考虑where和order by使用到的字段

通过为字段添加索引，来提升查询效率，例如：

# USER_CODE 添加索引select s1.OBJECT_ID, s1.LOGIN_NAME, s1.ENAME from TP_SYS_USER s1 where USER_CODE='yg02';

8、避免在索引上使用内置函数

反例:

SELECT * FROM user WHERE DATE_ADD(birthday,INTERVAL 7 DAY) >=NOW();

正例：

SELECT * FROM user WHERE birthday >= DATE_ADD(NOW(),INTERVAL 7 DAY);

使用内置函数会导致索引失效。

9、!=、<>、not in、not like、or...要慎用

例如如下几种场景：已知USER_CODE存在索引

通过explain执行计划分析，共通之处是key为null，表示没有走索引，也就意味着存在的索引USER_CODE并没有发挥作用，索引失效。

10、提升group by的效率

在实际业务，我们经常性的会用到group by 来分组获取数据，不知道小伙伴是否有这样的习惯：先group by 然后在通过having过滤条件。

反例：

select TYPE_ID, count(*)from TP_DIM_OBJECTgroup by TYPE_IDhaving TYPE_ID != '11ecfdf11bef0e5d968093c522e0eb78';

分组是一个消耗性能的动作，我们为什么不先加过滤条件，缩小范围数据范围在分组呢？

正例：

select TYPE_ID, count(*)from TP_DIM_OBJECTwhere TYPE_ID != '11ecfdf11bef0e5d968093c522e0eb78'group by TYPE_ID;

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

11、明确仅返回一条数据的语句可以使用limit 1

在业务开发过程中，我们有没有遇到过这样的场景，按照时间排序，我们只需要获取最新的数据。

例如：

select s1.OBJECT_ID, s1.LOGIN_NAME, s1.ENAMEfrom TP_SYS_USER s1order by CREATE_DATE desc;

在业务逻辑中，我们可能通过代码逻辑底层使用如上SQL获取到的数据集合，然后在通过collection.get(0)，获取到第一条数据。

虽然这种做法没啥问题，但是它的效率很低，怎么优化呢？

select s1.OBJECT_ID, s1.LOGIN_NAME, s1.ENAMEfrom TP_SYS_USER s1order by CREATE_DATE desc limit 1;

如果我们很明确我们的业务需求，就是获取最新的数据，我们可以直接在排序后加上limit 1，表示只获取结果的最新1条。

12、业务逻辑尽量批量化完成

如果存在业务需求，我们需要插一批数据入库。

场景一：执行单次插入操作：

for(Order order: list){orderMapper.insert(order):}

执行SQL:

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

这个过程是在for循环中执行的，我们需要多次的请求数据执行插入操作。

场景二：执行批量插入操作：

orderMapper.insertBatch(list):

执行SQL:

insert into order(id,code,user_id) values(123,'001',100),(124,'002',100),(125,'003',101);

这个过程，我们可以一次完成，不需要多次请求数据库。相比较场景一的多次请求，相对而言我们批量插入的操作会大大提升客户端的请求性能。

如果批量插入的数据量过大，我们也建议分开执行，比如200条一次。

该文章在 2024/1/22 8:58:35 编辑过

关键字查询

优化

sql

技巧

正在查询...

点晴ERP是一款针对中小制造业的专业生产管理软件系统,系统成熟度和易用性得到了国内大量中小企业的青睐。

点晴PMS码头管理系统主要针对港口码头集装箱与散货日常运作、调度、堆场、车队、财务费用、相关报表等业务管理，结合码头的业务特点，围绕调度、堆场作业而开发的。集技术的先进性、管理的有效性于一体，是物流码头及其他港口类企业的高效ERP管理信息系统。

点晴WMS仓储管理系统提供了货物产品管理,销售管理,采购管理,仓储管理,仓库管理,保质期管理,货位管理,库位管理,生产管理,WMS管理系统,标签打印,条形码,二维码管理,批号管理软件。

点晴免费OA是一款软件和通用服务都免费，不限功能、不限时间、不限用户的免费OA协同办公管理系统。