项目场景
项目现场的某个kettle模型执行非常缓慢,原因在于某个SQL执行效率非常的低。甲方得知此事要求公司赶紧优化,负责该模块的同事对SQL优化并不熟悉。所以作为一个立志成为优秀DBA的ETL工程师,我自告奋勇:不是DBA,就不能搞SQL优化了吗?
查询效率慢的SQL格式如下,因为涉及到项目隐私,所以对表名、字段名做了匿名化处理。
SELECT
INFO.ID,
INFO.NAME,
INFO.AGE,
INFO.SEX,
MP.ADDR,
MP.PHONE
FROM INFORMATION INFO
LEFT JOIN MP ON MP.ID = INFO.ID
WHERE INFO.ID = 1234567
很简单的一个SQL。每次查询都是按照 INFO.ID = xxx 这个条件进行查询,每次只会返回1行数据。且关联字段 MP.ID 与 INFO.ID 都有索引,没有大字段。但每次执行查询,大概都要耗时2s左右;不要觉得这个耗时很短,因为有大量的数据需要查询出来进行同步,这个速度就不可被接受了。
两张表的数据量如下
SELECT COUNT(*) FROM INFORMATION --3667874
SELECT COUNT(*) FROM MP --2125263
问题分析
以下是上述SQL格式的执行计划,是项目上真实SQL的执行计划。我已经将对此次优化的无关执行计划信息删除,只保留了执行计划和谓词信息;且将涉及项目隐私部分的表名字段名改写。
Plan hash value: 3295416379
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 29513 (100)| | 1 |00:00:01.20 | 108K|
| 1 | NESTED LOOPS OUTER | | 1 | 1 | 115 | 29513 (1)| 00:00:02 | 1 |00:00:01.20 | 108K|
| 2 | TABLE ACCESS BY INDEX ROWID| INFORMATION | 1 | 1 | 98 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 3 | INDEX UNIQUE SCAN | UK_20230901211918_5341 | 1 | 1 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 4 | TABLE ACCESS FULL | MP | 1 | 1 | 17 | 29510 (1)| 00:00:02 | 1 |00:00:01.20 | 108K|
-------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."ID"=1234567)
4 - filter((TO_NUMBER("MP"."ID")=1234567 AND "A"."ID"=TO_NUMBER("MP"."ID")))
通过执行计划我们可以分析出:
- 连接方式 (执行计划:Id=1)
MP表和INFO表的连接方式是 NESTED LOOPS OUTER;因为此查询返回数据量少,每次执行只会返回1行数据,所以走嵌套循环连接是正确的。
- 访问路径 (执行计划:Id=2)
INFO.ID=1234567 这个谓词过滤是走了索引ROWID扫描的;返回数据量少,走索引,也是正确的。
- 访问路径(执行计划:Id=4)
对MP表的访问路径是TABLE ACCESS FULL(全表扫描)。这个就有问题了,MP表有上百万条数据,走全表扫描肯定是低效的、错误的。MP表上的ID字段是有索引的,为什么没有走索引呢?我们继续往下看。
- 谓词信息
从谓词信息我们可以看到MP表在进行谓词过滤时,将MP表的ID字段从varchar类型的值通过to_number()转换成了number类型;因为使用了to_number()函数,索引索引失效。此转换属于是Oracle在比对不同数据类型的字段或者表达式时,自动发生的隐式转换;隐式转换的目的肯定是好的,但是在此处对SQL查询效率影响可太大了。
那么对于这个SQL优化而言,需要做的其实就是消除隐式转换带来的影响。那怎么消除嘞?大家继续看下面的SQL改写。
我不晓得会不会有朋友疑问,为什么发生隐式转换的是MP.ID 而不是 INFOMATION.ID。
我在这里多解释一句:
可以从执行计划中看出来,
距离NESTED LOOPS OUTER这个连接方式关键字下方最近的一张表就是驱动表。
这也就意味着,驱动表INFOMATION传一个number类型的数据给MP表,那么MP表必须对自己的字段进行转换才可以进行等值匹配。
SQL改写
这是改写后的SQL,现在查询只需要0.05左右,速度提高了几十倍。完全可以满足ETL要求了。
SELECT
INFO2.ID,
INFO2.NAME,
INFO2.AGE,
INFO2.SEX,
MP.ADDR,
MP.PHONE
FROM (SELECT CAST(INFO.ID AS VARCHAR2(10) AS ID,INFO.NAME,INFO.AGE,INFO.SEX FROM INFORMATION INFO WHERE INFO.ID = 1234567) INFO2
LEFT JOIN MP ON MP.ID = INFO2.ID
该SQL的执行计划如下,同样的,为了隐私表名和字段名我做了匿名化处理。
Plan hash value: 3589640507
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 9 (100)| | 1 |00:00:00.01 | 8 |
| 1 | NESTED LOOPS OUTER | | 1 | 1 | 119 | 5 (0)| 00:00:01 | 1 |00:00:00.01 | 8 |
| 2 | TABLE ACCESS BY INDEX ROWID| INFORMATION | 1 | 1 | 98 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 3 | INDEX UNIQUE SCAN | UK_20230901211918_5341 | 1 | 1 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
| 4 | TABLE ACCESS BY INDEX ROWID| MP | 1 | 1 | 21 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 5 | INDEX UNIQUE SCAN | UK_20230901210612_192177 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
---------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("INFO"."ID"=1234567)
5 - access("MP"."PID"=CAST("INFO"."ID" AS VARCHAR2(20)))