探究索引可大幅提升查询效率的查询特征

嘿，我完全懂你这种挫败感——折腾大表、建索引，结果执行计划显示用了索引，但性能提升却微乎其微，换谁都会挠头。问题出在你当前的查询和数据分布没触碰到索引能发挥最大价值的场景，咱们一步步拆解：

先搞懂你当前的瓶颈：数据分布太均匀 #

你生成的many_students表有100万条数据，city有10000个不同值，平均每个城市对应100条记录。这种情况下：

• 全表扫描时，Oracle用多块读（multiblock read）能快速遍历数据块，100万条数据的扫描成本其实很低；
• 用索引的话，需要先查索引找到对应的ROWID，再回表读取实际数据——这两步的开销加起来，和全表扫描的差距被抹平了，自然看不到大幅提升。

能让索引产生数量级提升的核心查询特征 #

要看到索引的威力，你需要触发以下几种场景之一：

1. 高选择性的过滤条件 #

当查询的过滤条件只会返回极少数数据（比如1条、几条）时，索引能直接定位到目标数据，避免全表扫描百万条记录的开销。

举个例子：
先修改一条数据，让某个城市只有唯一一条记录：

UPDATE many_students SET city = 'One-of-a-Kind City' WHERE student_id = 1;
COMMIT;

然后执行查询：

SELECT * FROM many_students WHERE city = 'One-of-a-Kind City';

这时候对比索引和全表扫描的执行时间，你会发现差距瞬间拉开——全表要扫100万条，索引直接定位到1条数据，速度差几个数量级。

2. 利用索引的有序性：范围查询/排序 #

索引本身是有序的，当你需要做范围查询或者排序时，能避免全表扫描后的排序操作，性能提升会非常明显。

比如：

-- 范围查询：找城市名大于'9500 City'的所有记录
SELECT * FROM many_students WHERE city > '9500 City';

-- 排序取前N条：按城市名排序取前100条
SELECT * FROM many_students ORDER BY city FETCH FIRST 100 ROWS ONLY;

没有索引的话，Oracle需要先全表扫描，再做排序（开销极大）；有索引的话，直接按索引顺序读取数据，不用额外排序，速度会快很多。

3. 覆盖索引：避免回表开销 #

如果你的查询所需的所有字段都包含在索引里，Oracle可以直接从索引返回结果，不用回表读取原数据块，这也能带来显著提升。

比如创建包含city和student_id的覆盖索引：

CREATE INDEX idx_city_id ON many_students(city, student_id);

然后执行查询：

SELECT student_id FROM many_students WHERE city = '5467 City';

这时候索引已经包含了查询需要的所有字段，Oracle不用回表，比全表扫描或者普通索引的回表操作快得多。

总结一下 #

索引的数量级提升效果，本质是避免了全表扫描的巨大开销，或者省去了昂贵的排序/回表操作。你之前的查询因为数据分布均匀、返回行数较多，刚好没触发这些场景，所以提升不明显。试试上面的例子，应该就能看到索引的威力了！

内容的提问来源于stack exchange，提问作者AlwaysLearning