记录一次生产环境SQL优化
针对多张表的关联统计的一次SQL优化,主表(M表,500万左右数据),关联表1(C表,客户表,千万级数据),关联表2(CER表,客户员工关系表,千万级数据),关联表3(E表,员工表,数据量忽略不计),做了一次统计的SQL开发,主要就是一个优化的方法问题,以后写SQL需要注意一下,使用的是Oracle的数据库
一、第一版SQL编写
1 SELECT COUNT(CASE WHEN M.INDI_ID = '0001' TEHN 1 END) COUNT1 FROM ( 2 SELECT M.*, E.* FROM M 3 LEFT JOIN C 4 ON M.CUST_NO = C.CUST_NO 5 LEFT JOIN CER 6 ON C.CUST_NO = CER.CUST_NO 7 LEFT JOIN E 8 ON CER.EMP_NO = E.EMP_NO 9 )
这种sql 是第一次写的,本来是这样看起来清晰一些,放到数据量大的情况下,直接查询不出来了,好几分钟也出不来,只能进一步优化了,以后写SQL避免这种从查询出来的数据中在去嵌套查询的方法,切记,万不得已不要加这种嵌套查询
二、第二版SQL优化
Oracle parallel() 并行优化
SELECT /*+parallel(M, 4)*/ COUNT(CASE WHEN M.INDI_ID = '0001' TEHN 1 END) COUNT1 FROM M LEFT JOIN C ON M.CUST_NO = C.CUST_NO LEFT JOIN CER ON C.CUST_NO = CER.CUST_NO LEFT JOIN E ON CER.EMP_NO = E.EMP_NO
去掉外面的嵌套from,直接加上并行优化,但是还是执行时间有些长,继续优化,加上日期查询条件字段的索引,但是效果不是很明显,跟搞数据开发的人讨论,于是有了最终的优化方案
三、最终版SQL
Oracle use_hash(M, C, CER, E) 优化
SELECT /*+use_hash(M, C, CER, E)*/ COUNT(CASE WHEN M.INDI_ID = '0001' TEHN 1 END) COUNT1 FROM M LEFT JOIN C ON M.CUST_NO = C.CUST_NO LEFT JOIN CER ON C.CUST_NO = CER.CUST_NO LEFT JOIN E ON CER.EMP_NO = E.EMP_NO
use_hash 官方说明
The USE_HASH hint causes Oracle to join each specified table with another row source with a hash join. The syntax of the USE_HASH hint is USE_HASH(table table) where table is a table to be joined to the row source resulting from joining the previous tables in the join order using a hash join.
翻译过来就是:
USE_HASH 提示使 Oracle 通过散列连接将每个指定的表与另一个行源连接起来。 USE_HASH 提示的语法是 USE_HASH(table table),其中 table 是要连接到行源的表,该表是通过使用哈希连接以连接顺序连接先前的表而产生的。