3种SQL语句优化方法，测试人必知必会！

关于SQL语句的优化，本质上就是尽量降低SQL语句的执行时间，对于如何降低SQL语句的执行时间，可以从以下几个方面入手。

一、降低SQL语句执行时的资源消耗

这是我们在数据库性能调优中常用的方法，该方法以分析SQL语句的执行计划为切入点，核心思路是找到执行计划中开销较高的操作，通过改写SQL语句或改变表访问方式调整执行计划，从而达到降低SQL语句执行消耗，缩短执行时间的目的。

对于改变表访问方式，常见的手段是使用索引替代开销较高的全表扫描，但这种方式不是万能的，是有一定的使用前提的，有时候，滥用索引反而会带来较高的性能开销。

以下列举一些适合采用访问索引的方式替代原有操作的案例。

1. 使用索引替代全表扫描

如果查询结果集只占表中的一小部分数据，这时，可以采用索引访问的方式替代全表扫描，即使不能达到索引覆盖而产生回表操作，其开销也小于采用全表扫描操作。

例如，使用Oracle数据库存储，一个用户信息表

user(userid,username,sex,tel,code)，code列表示用户的社保号信息，系统运行之初允许社保号信息为非必填项，这就导致少部分code列的值为null，为了提高对code列检索的效率，我们为code列创建了普通B树索引inx_code(code)，要查询所有未提供社保号的那部分用户信息，之后系统为这部分用户发送信息，提示补全社保号信息，语句如下：

select * from user where code is null;

语句执行后，用时20秒左右，user表中存在50万条左右的用户记录，返回的未提供社保号的用户记录有5000条左右。

通过分析执行计划，在查询执行时采用了全表扫描方式，这是造成查询执行时间较长的主要原因。

对于B树单列索引，null值将使得索引失效，所以优化器采用了全表扫描方式。

该查询实际返回记录5000条左右，表中共有记录50万条左右，实际返回的记录只占表总记录的1%，这时，可以采取使用索引扫描替代全表扫描。

如何使得包含null值的列在检索时可以使用索引呢，这就需要将基于B树的单列索引改为复合索引，将原有索引修改为inx_code(code,0)，再次执行查询，对user表的访问方式由全表扫描改为索引范围扫描，执行时间降至1秒之下。

现在我也找了很多测试的朋友，做了一个分享技术的交流群，共享了很多我们收集的技术文档和视频教程。
如果你不想再体验自学时找不到资源，没人解答问题，坚持几天便放弃的感受
可以加入我们一起交流。而且还有很多在自动化，性能，安全，测试开发等等方面有一定建树的技术大牛
分享他们的经验，还会分享很多直播讲座和技术沙龙
可以免费学习！划重点！开源的！！！
qq群号：1150305204【暗号：csdn999】

2. 利用索引的有序性消除排序操作

在对数据库的访问中，排序是一种开销较搞的操作，数据库为了完成排序操作，需要扫描表中的所有记录，之后采取相应的算法对记录进行排序。

如果表中的记录随系统的运行累积增加，那么排序操作的执行开销会逐渐变大，执行时间会越来越长。

索引是有序的，因为相应的索引键值已经事先按一定规则完成排序，如果SQL语句中需要按表中的某列进行排序，此时，可以为该列创建索引，从而达到通过索引扫描代替完成排序需要的全表扫描，达到降低访问开销，缩短执行时间的目的。

例如，在MySQL8.0数据库中，如果需要对多个列进行排序，且排序顺序有升有降，即：order by col1,col2 desc，此时，可以为两个排序列创建一个复合降序索引idx_col1_col2(c1,c2 desc)。

3. 利用索引改变表关联方式

在执行表关联查询操作时，数据库的优化器可能选择了不合理的表关联方式，使得表关联查询开销较高，耗时较长。

例如，PostgreSQL数据库中有user、ure、org三个表，分别存储系统用户信息、已完成认证的用户信息和相应的组织信息。有如下的查询语句：select *  

from user join ure on user.id=ure.id join on org on ure.oid=org.oid

where org.pcode=’12012’

order by ure.update;

该查询的执行计划如下图所示：

查询执行用时32毫秒，开销为2206。

通过执行计划我们发现，user、ure和org三表关联均采用了Hash Join的关联方式，这种关联方式是最优的吗？

分析表连接方式，org表是第一个关联的驱动表，该表过滤后的结果集只有24条记录，结果集很小，所以，可以将org表与user表的连接方式调整为嵌套循环连接方式。

此外，对org表的访问采用了全表扫描，可将其调整为索引扫描。

为查询org表的where条件列pcode列创建索引，同时为作为嵌套循环连接的被驱动表user表的关联条件列oid列创建索引，本次调优后的执行计划如下图所示。

改变org表与user表的连接方式后，执行时间降低为4.85ms，执行开销降低到273。

进一步分析调优后的执行计划，org表与user表连接后的结果集只有47条记录，该结果集与ure表的连接方式仍可以调整为嵌套循环连接，以该结果集作为驱动表，ure表作为被驱动表。为达到该目的，为ure表的连接条件列id设置索引即可。

最终的执行计划如下图所示。

由此可见，将表连接方式全部由Hash连接调整为嵌套循环连接后，执行时间最终降至1ms，执行开销降至30。

二、并行执行SQL语句

这种方式是通过增加额外的资源消耗来换取SQL执行时间的缩短，其意义类似于代码优化中的“以空间换时间”的策略。

增加的额外资源主要是指数据库服务器的处理器（CPU）、内存、I/O等硬件资源。

例如：在Oracle数据库中，对于一个查询操作，如果其所作的工作可以分割成多个互不相关的部分，则该查询可以由多个进程并发执行。可以并行执行的查询操作主要有全表扫描、快速索引全扫描、分区索引范围扫描、以及需要执行全表扫描完成的表连接。

三、避免不必要的资源争用导致SQL执行效率下降

有些SQL语句，其执行时间不定，时快时慢，这些语句的执行计划自身未存在问题。导致语句执行效率差的原因是语句执行时，数据库服务器在执行其他消耗资源的操作，出现资源争用的情况。

例如：某系统每日凌晨定时执行统计信息收集工作，如果这时对系统执行性能测试，涉及对该数据的查询操作将受到影响，导致性能测试结果不准确。因此，性能测试需要避开数据库执行统计信息收集的时间。

以上对SQL语句的优化方法做了简要的介绍，下面对SQL语句的优化步骤和方法做一个说明和总结。

• 1. 找到执行时间较长、消耗资源较多的SQL语句。例如：MySQL数据库可从慢查询日志中获取，Oracle数据库可查看AWR报告。

• 2. 分析以上获取的性能较差的SQL语句的执行计划，找到执行计划中开销较高的部分，评估执行计划是否合理，是否需要调整。

• 3. 对于执行计划中开销较高的部分，采取相应的措施降低执行开销，缩短执行时间，例如如下方式：

• （1）如果是统计信息不准确导致生成了错误的执行计划，需要首先重新收集统计信息；

• （2）如果是SQL语句编写存在问题，可以在不改变业务逻辑的前提下对SQL语句进行适当的改写；

• （3）对于不必要的全表扫描或排序，可以通过创建合适索引消除全表扫描和排序；

• （4）如果因为某些原因导致SQL语句的执行计划不稳定，在条件允许的情况下，使用提示器（Hint）固定SQL语句的执行计划；

• （5）如果因为表或索引本身设置不合理，导致执行开销较高，用时较长，则应该对表或索引重新设计，例如：表中记录过大，超过亿级，此时应考虑分表分库；

• （6）对于具备并行执行的部分，考虑采用并行执行的方式；

• （7）如果是业务设计不合理导致SQL语句执行效率低下，应考虑修改业务逻辑。

 

作为一个软件测试的过来人，我想尽自己最大的努力，帮助每一个伙伴都能顺利找到工作。所以我整理了下面这份资源，现在免费分享给大家，有需要的小伙伴可以关注【公众号：开心螺蛳粉】自提！

软件测试面试文档

我们学习必然是为了找到高薪的工作，下面这些面试题是来自阿里、腾讯、字节等一线互联网大厂最新的面试资料，并且有字节大佬给出了权威的解答，刷完这一套面试资料相信大家都能找到满意的工作。

行动吧，在路上总比一直观望的要好，未来的你肯定会感谢现在拼搏的自己！如果想学习提升找不到资料，没人答疑解惑时，请及时加入群：1150305204，里面有各种测试开发资料和技术可以一起交流哦。