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、临时表使用
临时表声明后需显式提交事务。
临时表上如需创建索引,必须在数据写操作完成后创建。
如果临时表中存放的数据量较大,且根据程序逻辑已能确认后续程序段中不再使用该数据的,必须显式将其中的数据清空。
如果临时表中存放的数据量较大,推荐在数据写操作完成后显式对其统计信息进行收集。
参考文献: