【SQL优化案例】索引创建不合理导致SQL消耗大量CPU资源
#隐式转换
第一章 适用环境
- oracle 11g+
- linux 6.9+
第二章 Top SQL概况
下面列出我们发现的特定模块中Top SQL的相关情况：
| SQL_ID | 模块 | SQL类型 | 主要问题 |
| fnc58puaqkd1n | 无 | select | 索引创建不合理，导致全索引扫描，产生了大量逻辑读 |
第三章 SQL优化方案
3.1 SQL_ID:fnc58puaqkd1n
3.1.1 SQL文本
SELECT MAX(TEST_ID) TEST_ID FROM TEST1 WHERE TEST_NAME = 28 3.1.2 SQL执行计划
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fnc58puaqkd1n, child number 0
-------------------------------------
SELECT MAX(TEST_ID) TEST_ID FROM TEST1 WHERE TEST_NAME = 28 Plan hash value: 2527920730 ---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 23200 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX FAST FULL SCAN| TEST1_TEST_ID_IDX | 1 | 13 | 23200 (3)| 00:04:39 |
--------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter(TO_NUMBER("TEST_NAME")=28) 3.1.3 SQL资源消耗
CPU(MS) ELA(MS) DISK GET ROWS ROWS APPLI(MS) CONCUR(MS) CLUSTER(MS) USER_IO(MS) PLSQL JAVA
EXEC PRE EXEC PRE EXEC PRE EXEC PRE EXEC PRE EXEC PRE FETCH PER EXEC PER EXEC PER EXEC PER EXEC PER EXEC PER EXEC SQL_PROFILE
---------- -------- -------- -------- ------------ ----------- --------- --------- ---------- ----------- ----------- -------- -------- ---------------
2887 2,116 2,146 0 89,108 1 1 0 0 0 0 0 0 该SQL执行了2887次，每次执行的平均逻辑读为89,108，物理读为0，每次返回1条数据，耗时2.146秒，其中CPU消耗2.116秒。
四、 问题分析及优化思路
通过分析SQL文本，发现该SQL为单表扫描类型，select涉及max函数。
通过分析执行计划，SQL主要性能消耗在TEST1_TEST_ID_IDX 的全索引扫描，该索引的体积达到705MB，由于该SQL执行频率高达2887次，等待主要在CPU上，这就是该SQL执行耗时只有2秒原因。
那为什么会导致该SQL走的是快速索引全扫描，而不是索引范围扫描？观察到执行计划中有一个隐式转换，是它的原因吗？通过测试发现并不是。那问题在哪？
再看下TEST1_TEST_ID_IDX组成列的顺序是TEST_ID、TEST_NAME，而SQL的过滤条件是TEST_NAME，针对TEST_ID算MAX值，到这里问题就清楚了，索引创建的顺序有问题，前导列应该是TEST_NAME，正确的索引创建顺序应该是TEST_NAME、TEST_ID，让执行计划走MAX/MIN类型。
另外还有一个问题，该SQL存在隐式转换，观察到TEST_NAME字段类型是varchar2,而SQL传值类型是数值，如果不消除这个问题，有正确的索引仍然无法走最优的执行计划。
结合以上分析，该SQL需要消除隐式转换，再创建合适的索引即可优化。
五、 优化方案
5.1 创建组合索引
CREATE INDEX "TESTUSER"."TEST1_N1" ON "TESTUSER"."TEST1" (TEST_NAME,TEST_ID) tablespace TESTUSER_IDX online;5.2 查看索引效果
SQL> SELECT MAX(TEST_ID) TEST_ID FROM TESTUSER.TEST1 WHERE TEST_NAME = 28;Execution Plan
----------------------------------------------------------
Plan hash value: 3698544155------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 13251 (3)| 00:02:40 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX FAST FULL SCAN| TEST1_N1 | 1075K| 13M| 13251 (3)| 00:02:40 |
------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - filter(TO_NUMBER("TEST_NAME")=28)观察仍然走的TEST1_N1索引INDEX FAST FULL SCAN。有两种方案可以解决，一是创建函数索引，二是修改传参类型，这里我们选择改传参类型。
5.3 优化后的效果
SQL> SELECT MAX(TEST_ID) TEST_ID FROM TESTUSER.TEST1 WHERE TEST_NAME = '28';Execution Plan
----------------------------------------------------------
Plan hash value: 1923666499-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | FIRST ROW | | 1 | 13 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| TEST1_N1 | 1 | 13 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------3 - access("TEST_NAME"='28')六、 优化效果对比
通过确认关键表数据量，制定执行计划，可以减少每次查询的逻辑读和物理读，提高SQL执行性能。
| 逻辑读 | 物理读 | 执行时间(s) | |
| 优化前 | 89,108 | 0 | 2.3 |
| 优化后 | 3 | 0 | 0.01 |
🚀 更多数据库干货，欢迎关注【安呀智数据坊】
如果你觉得这篇文章对你有帮助，欢迎点赞 👍、收藏 ⭐ 和留言 💬 交流，让我知道你还想了解哪些数据库知识！
📬 想系统学习更多数据库实战案例与技术指南？
📊 实战项目分享
📚 技术原理讲解
🧠 数据库架构思维
🛠 工具推荐与实用技巧
立即关注，持续更新中 👇
