在 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%，说明查询条件筛选出的数据量相对较大，优化器可能会选择全表扫描。
这是由于通过索引扫描获取数据后，还需要进行回表操作来读取完整的数据行，而当数据量较大时，回表操作的成本可能会高于直接进行全表扫描的成本。
3）5% - 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_COLUMN"='FILTER_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_COLUMN"='FILTER_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秒，逻辑读158380，SQL性能大幅提升。
写在最后
此案例中，查询从2亿数据量的`TBL_PARTITIONED_ENTITY`表中返回近百万行数据（占比约5%），执行计划采用全表扫描，导致高物理读（336万次）和长耗时（4分16秒）。
经分析，该查询存在索引优化空间，可通过在`FILTER_COLUMN`列创建适配索引（如B树索引或位图索引）、更新统计信息确保优化器准确评估成本，并利用SQL提示测试索引效果。
优化后，预期将降低物理读次数，减少执行时间，从全表扫描转为更高效的索引扫描模式，实现性能提升。
所以，并非看到返回结果集非常大的场景，就一定不适合创建索引，还需要考虑另一个关键因素，就是过滤条件的选择性！
作者介绍
大家好，我是刘峰，安丫科技创始人 & 数据库技术高级讲师，专注于 PostgreSQL、国产数据库运维与迁移、数据库性能优化 等方向。
作为 PG中国分会官方授权讲师、PostgreSQL ACE 讲师认证专家，我长期活跃在一线项目实战中，拥有 10年以上大型数据库管理与优化经验，曾深度参与电信、金融、政务等多个行业的数据库性能调优与迁移项目。
欢迎关注我，一起深入探索数据库的无限可能，技术交流不设限！
📌 觉得有收获的话，记得点赞、收藏、转发支持一下哦，别忘了关注我获取更多数据库干货~
原文链接：https://mp.weixin.qq.com/s/YeN6cJxkY9gnZusmRkWHUg
