关于SQL语句优化的问题

lsekfe

关于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秒之下。

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

    在对数据库的访问中，排序是一种开销较搞的操作，数据库为了完成排序操作，需要扫描表中的所有记录，之后采取相应的算法对记录进行排序。如果表中的记录随系统的运行累积增加，那么排序操作的执行开销会逐渐变大，执行时间会越来越长。

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

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

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

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

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

1. <font face="微软雅黑" size="3">select *  
   
2. from user join ure on user.id=ure.id join on org on ure.oid=org.oid
   
3. where org.pcode=’12012’
   
4. order by ure.update;
   
5. </font>

复制代码

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

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

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

分析表连接方式，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语句执行效率低下，应考虑修改业务逻辑。