SQL优化案例｜ 返回近百万数据的 SQL，一定不适合走索引吗？

在 Oracle 数据库的 SQL 优化工作中过滤选择性是决定查询性能的核心要素之一。它直接影响优化器对执行计划的选择尤其是在处理返回大量数据的查询时过滤选择性的分析更是至关重要。理解并掌握过滤选择性能够帮助开发者和数据库管理员精准优化 SQL 语句提升数据库整体性能。今天我就碰到了这样一个案例一个简单的单表过滤查询SQL返回近百万条数据耗时4多分钟逻辑读和物理读更是上百万不用看执行计划就知道肯定走的全表扫描那这个SQL到底有没有优化空间先不着急下结论在开始今天的案例分析之前我们先了解选择性这个概念。01过滤选择性的定义与计算过滤选择性Selectivity是指在 SQL 查询中满足特定过滤条件的行数与表总记录数的比值它反映了查询条件对数据的筛选程度。计算公式为过滤选择性 满足条件的行数 ÷ 表总记录数 × 100%例如在一个包含 100 万条记录的员工信息表中若执行查询语句SELECT * FROM employees WHERE department_id 10且满足department_id 10条件的员工记录有 5000 条那么该查询条件的过滤选择性为(5000 ÷ 1000000) × 100% 0.5%02过滤选择性的判断方法通常情况下Oracle 优化器会依据过滤选择性的高低来决定是使用索引扫描还是全表扫描1高选择性 5%当过滤选择性低于 5% 时意味着查询条件能够显著缩小数据范围此时索引扫描如 INDEX RANGE SCAN、INDEX UNIQUE SCAN 等通常是更优的选择。因为索引可以快速定位到少量符合条件的数据减少磁盘 I/O 操作提升查询效率。2中低选择性 10%若过滤选择性超过 10%说明查询条件筛选出的数据量相对较大优化器可能会选择全表扫描。这是由于通过索引扫描获取数据后还需要进行回表操作来读取完整的数据行而当数据量较大时回表操作的成本可能会高于直接进行全表扫描的成本。35% - 10% 之间在这个区间内优化器的决策会更加复杂需要综合考虑索引的结构、表的存储特性、统计信息的准确性等因素来确定最优的执行计划。03案例展示1. 案例SQL及执行计划SELECT * FROM TBL_PARTITIONED_ENTITY t WHERE t.FILTER_COLUMN FILTER_VALUE; Plan hash value: 2627847197 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 981K| 11M| 913K (2)| 03:02:46 | |* 1 | TABLE ACCESS FULL| TBL_PARTITIONED_ENTITY| 981K| 11M| 913K (2)| 03:02:46 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(T.FILTER_COLUMNFILTER_VALUE) Statistics ---------------------------------------------------------- 2 recursive calls 2 db block gets 3503859 consistent gets 3363792 physical reads 58676 redo size 51435226 bytes sent via SQL*Net to client 708502 bytes received via SQL*Net from client 64364 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 965444 rows processed其中表TBL_PARTITIONED_ENTITY有近2亿数据。2. 案例分析分析执行计划和统计信息该 SQL 是否存在索引优化空间一方面表数据量达 2 亿查询返回近百万行占比约 5%属于中等选择性理论上索引扫描 回表的成本可能低于全表扫描另一方面当前执行计划为全表扫描导致 336 万次物理读和 350 万次逻辑读执行耗时 4 分 16 秒通过对比估算的ROWS及真实返回结果统计信息是准的那就是因FILTER_COLUMN无索引所致。3. 案例优化措施对此可采取以下优化措施首先在FILTER_COLUMN列上创建 B 树索引其次通过 SQL 提示/* INDEX(t idx_filter_column) */强制执行索引扫描并对比性能。优化后执行计划有望转变为索引扫描 回表大幅减少物理读次数显著提升查询效率 。4. 优化效果Plan hash value: 4129332837 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 981K| 110M| 31816 (1)| 00:06:22 | | 1 | TABLE ACCESS BY INDEX ROWID| TBL_PARTITIONED_ENTITY | 981K| 110M| 31816 (1)| 00:06:22 | |* 2 | INDEX RANGE SCAN | IDX_PARTITIONED_COLUMN | 981K| | 3929 (1)| 00:00:48 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(FILTER_COLUMNFILTER_VALUE) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 158380 consistent gets 0 physical reads 148525145 bytes sent via SQL*Net to client 708502 bytes received via SQL*Net from client 64364 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 965444 rows processed通过创建合适的索引之后耗时15秒逻辑读158380SQL性能大幅提升。写在最后此案例中查询从2亿数据量的TBL_PARTITIONED_ENTITY表中返回近百万行数据占比约5%执行计划采用全表扫描导致高物理读336万次和长耗时4分16秒。经分析该查询存在索引优化空间可通过在FILTER_COLUMN列创建适配索引如B树索引或位图索引、更新统计信息确保优化器准确评估成本并利用SQL提示测试索引效果。优化后预期将降低物理读次数减少执行时间从全表扫描转为更高效的索引扫描模式实现性能提升。所以并非看到返回结果集非常大的场景就一定不适合创建索引还需要考虑另一个关键因素就是过滤条件的选择性作者介绍大家好我是刘峰安丫科技创始人 数据库技术高级讲师专注于 PostgreSQL、国产数据库运维与迁移、数据库性能优化 等方向。作为 PG中国分会官方授权讲师、PostgreSQL ACE 讲师认证专家我长期活跃在一线项目实战中拥有 10年以上大型数据库管理与优化经验曾深度参与电信、金融、政务等多个行业的数据库性能调优与迁移项目。欢迎关注我一起深入探索数据库的无限可能技术交流不设限 觉得有收获的话记得点赞、收藏、转发支持一下哦别忘了关注我获取更多数据库干货~原文链接https://mp.weixin.qq.com/s/YeN6cJxkY9gnZusmRkWHUg