SQL优化小结

对此前学习的一些SQL调优实践小结一下，如有总结的不当之处，敬请指正。

1、选择谓词尽可能简单

选择谓词要尽可能采用较为简单的形式，避免对谓词列使用函数或者数学表达式，如果对谓词列使用函数或者数学符号，会对谓词列索引的使用造成影响，从而使得SQL运行速度变慢。

例：

SELECT * FROM T WHERE CAST( COLUM_CHAR5 AS INT) = 100

 改成

SELECT * FROM T WHERE COLUM_CHAR5 =  CAST( 100 AS CHAR(5))

 

SELECT * FROM T WHERE COLUM_INT*1.3>1000.00

改成

SELECT * FROM T WHERE COLUM_INT>1000.00/1.3

 

2、避免在索引列上直接使用<>

在索引列上尽可能不使用<>，用 > and <代替，例如，WHERE A.SALARY>100 AND A.SALARY<100

 

3、用 UNION ALL 代替 UNION

条件允许的情况下，如明确知道UNION的两个结果集不会有重复元素，则使用UNION ALL，因为UNION会把结果去重，涉及到排序操作，而UNION ALL不考虑结果集是否有重复元素。

 

4、考虑各个表的连接顺序，尽量减小中间结果集的数据量

一般情况下，DB2 会根据各表的 JOIN 顺序自顶向下顺序处理，连接的顺序直接影响执行查询时的 I/O 次数和 CPU 代价，因此合理排列各表的连接顺序会提高查询性能。

例：

SELECT * FROM T1

JOIN T2

ON T1.c1 = T2.c1

JOIN T3

ON T1.c1 = T3.c1

如果采取下面的顺序将三表连接，假如三表都只有 1000 个记录，

T1 => T2 => T3

那么有可能 T1，T2 连接后的结果的记录数是 1000000，再和 T3 连接效率就很低。如果按下面的顺序连接，

T1 => T3 =>T2

T1, T3 连接后只有 1000 个记录，再和 T2 连接效率就提高很多。

 

5、合理使用Fetch First N Rows Only

在分页显示结果集时，第一页只要求呈现给用户 10 行数据，可使用以下语句，

SELECT * FROM T ORDER BY ID FETCH FIRST 10 ROWS ONLY

下一页的数据可以在后台并行处理查出，这样对用户来讲系统查询效率很高。

如果列 c1 上有索引，上面的 SQL 可以利用索引扫描直接得到 c1 的最大值，避免了对 T 的全表扫描。

SELECT MAX(C1) FROM T

可以写成，

SELECT C1 FROM T ORDER BY C1 FETCH FIRST ROW ONLY

 

6、合理使用left join

有些情况下会查询T1表的某些字段的数据是否在T2表存在，会用到not in或者not exists

例：

SELECT C1 FROM T1 WHERE T1.C1 NOT IN (SELECT C1 FROM T2)

SELECT C1 FROM T1 WHERE NOT EXISTS (SELECT 1 FROM T2 WHERE T1.C1= T2.C1)

当T2表特别大时会导致查询效率很慢，可改写成如下形式：

SELECT DISTINCT C1 FROM T1 LEFT JOIN T2 ON T1.C1 = T2.C1 WHERE T2.C1 IS NOT NULL

用join可能导致数据出现重复，具体分析后可加上distinct

 

7、两大表进行关联时先进行筛选

有先情况下两个大表需要关联，而关联之后需要做一些筛选，可以选择先做筛选，再关联。

例：

SELECT T1.C1 FROM T1 JOIN T2 ON T1.C1 = T2.C1 WHERE T2.C2= 100

可改为：

SELECT T1.C1 FROM T1 JOIN (SELECT C1 FROM T2 WHERE T2.C2=100) AS T3 WHERE T1.C1=T3.C1

当T2表为超大表，且T2.c2筛选了大量数据时可能有效。具体是否有优化还得关注执行计划。

 

8、exists,in,not exists,not in的选择

关于in和exists：

in是把外表和内表作hash连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。

当查询的两个表大小相当，那么用in和exists差别不大；如果两个表中一个较小一个较大，则子查询表大的用exists，子查询表小的用in。

 

9、参数固定时，用In代替OR

例：

SELECT C1 FROM T1 WHERE C1='A' OR C1='B'

改为

SELECT C1 FROM T1 WHERE C1 IN ('A','B')

 

10、利用执行计划

很多情况下，还是无法确定所写的语句是否合理，或者难以比较两种写法到底孰优孰劣时，需要进一步使用执行计划去判定。对于执行计划，还没细致学习过，可在DB2 命令窗口里输入如下语句进行查看：

db2expln -d db_name -u user_name password  -q "select * from ods_fat.cus_evlgz where year(dta_dte)= 2017 AND prd_nbr ='XXX' " -g -t (db_name,user_name,password 分别表示数据库名，用户名，密码)

在执行计划的输出结果中主要关注其执行消耗指标：Estimate costs，表的扫描是否是全表扫描还是运用了索引，表的处理顺序是怎么样的，是否有排序，连接方式是什么（嵌套连接，合并连接或散列连接），谓词的筛选率等等。

在输出结果中也可以看到执行计划树：

执行计划树由下往上看，一般来说，层次越少，效率越高，原始表也尽量位于底层较好。

 

11、索引的设计

创建索引时，列名的序列要考虑谓词的使用。除了选择谓词，连接谓词也要一并考虑。如对于下面的查询，

select * from T1, T2 where T1.c1 = T2.c1 and T1.c2 = 1 and T1.c3 IN ('a','b','c')

我们可以在 T1 上创建索引。有三个 T1 的列出现在谓词里面，c1, c2, c3，应当在这三个列上建索引。考虑列的序列时，出现在等式谓词中的列应放在前面，因为它们可以用来在索引中直接定位对应的记录。其中，选择谓词应放在最前面，连接谓词放在其后，因为选择谓词所限定的列值在索引扫描的过程中是不变的，连接谓词是不断变化的，把选择谓词放在前面有助于减少访问磁盘的随机性。最后将非等式谓词的列放在后面，也就是按（c2, c1, c3) 的顺序创建索引。

 

 

12、临时表声明

必须集中在程序逻辑开始之前进行。

必须符合数据对象设计规范。

必须显式包含的选项：

not logged

with replace

按需决定是否包含的选项：

on commit preserve rows

尽量避免使用like方式声明临时表，仅特殊用途除外。

 

13、临时表使用

临时表声明后需显式提交事务。

临时表上如需创建索引，必须在数据写操作完成后创建。

如果临时表中存放的数据量较大，且根据程序逻辑已能确认后续程序段中不再使用该数据的，必须显式将其中的数据清空。

如果临时表中存放的数据量较大，推荐在数据写操作完成后显式对其统计信息进行收集。

 

参考文献：

1.SQL优化

2.编写高效 SQL 语句的最佳实践