先看现象：为什么你的SQL跑得慢？

-- 案例表结构CREATE TABLE orders (    order_id NUMBER PRIMARY KEY,    customer_id NUMBER,    order_date DATE,    amount NUMBER(10,2),    status VARCHAr2(20));-- 创建索引CREATE INDEX idx_customer ON orders(customer_id);CREATE INDEX idx_date ON orders(order_date);

当执行SELECt * FROM orders WHERe customer_id = 100时，数据库可能有3种完全不同的执行方式。选错了，性能差百倍！

一、全表扫描：朴实无华的"大力出奇迹"

工作原理

-- 执行计划显示全表扫描EXPLAIN PLAN FORSELECt * FROM orders WHERe amount > 1000;SELECt * FROM TABLE(DBMS_XPLAN.DISPLAY);-- 结果示例：-- | Id | Operation         | Name   | Rows  |-- |----|------------------|--------|-------|-- |  0 | SELECt STATEMENT |        |      |-- |* 1 | TABLE ACCESS FULL| ORDERS | 50000 |

核心机制：

• 顺序读取表中所有数据块
• 逐行检查WHERe条件
• 不使用任何索引

什么时候该用？

-- 适合全表扫描的场景：-- 1. 查询大部分数据（通常>10-20%）SELECT * FROM orders WHERe status != 'DELETeD'; -- status='DELETED'只有5%-- 2. 小表（数据量小）SELECT * FROM config_table; -- 只有100行-- 3. 没有合适的索引SELECt * FROM orders WHERe UPPER(customer_name) = 'JOHN';-- 4. 强制全表扫描（特殊场景）SELECt  * FROM orders WHERe customer_id = 100;

性能真相

-- 性能测试对比SET TIMING ON;-- 场景1：查询大量数据（适合全表扫描）SELECt COUNT(*) FROM orders WHERe order_date < SYSDATE - 365; -- 80%的数据-- 全表扫描：0.5秒-- 索引扫描：2.3秒（更慢！）-- 场景2：查询少量数据（不适合全表扫描）SELECt * FROM orders WHERe order_id = 12345; -- 主键查询-- 全表扫描：0.8秒-- 索引+ROWID：0.01秒

二、索引扫描：数据库的"导航系统"

B树索引工作原理

索引结构示例：Root├── 1-100│   ├── 1-50│   └── 51-100└── 101-200    ├── 101-150    └── 151-200

索引范围扫描

-- 范围查询使用索引EXPLAIN PLAN FORSELECt * FROM orders WHERe order_date BETWEEN DATE '2023-01-01' AND DATE '2023-01-31';-- | Id | Operation                   | Name    | Rows  |-- |----|----------------------------|---------|-------|-- |  0 | SELECt STATEMENT           |         |       |-- |  1 | TABLE ACCESS BY INDEX ROWID| ORDERS  |   1200|-- |* 2 | INDEX RANGE SCAN           | IDX_DATE|   1200|

索引快速全扫描

-- 只需要索引列，不回表EXPLAIN PLAN FORSELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id;-- | Id | Operation            | Name        | Rows  |-- |----|---------------------|-------------|-------|-- |  0 | SELECt STATEMENT    |             |       |-- |  1 | SORT GROUP BY NOSORT|             |   500 |-- |  2 | INDEX FAST FULL SCAN| IDX_CUSTOMER| 50000 |

索引跳跃扫描

-- 复合索引跳过前导列CREATE INDEX idx_comp ON orders(status, customer_id);-- 查询只用到了第二列EXPLAIN PLAN FORSELECT * FROM orders WHERe customer_id = 100;-- 数据库会"跳跃"扫描不同status值的索引块

三、ROWID访问：直达数据的"快递员"

什么是ROWID？

-- 查看ROWID结构SELECt rowid, order_id,        DBMS_ROWID.ROWID_OBJECT(rowid) as object_id,       DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) as file_id,       DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) as block_id,       DBMS_ROWID.ROWID_ROW_NUMBER(rowid) as row_numFROM orders WHERe rownum <= 3;-- 结果：-- ROWID              OBJECT_ID FILE_ID BLOCK_ID ROW_NUM-- AAAAEAAAAAAGAAAAA  12345     1       123      0

ROWID直接访问

-- 1. 通过索引获取ROWID-- 2. 使用ROWID直接定位数据块EXPLAIN PLAN FORSELECt  *FROM orders WHERe customer_id = 100;-- 执行计划：-- TABLE ACCESS BY INDEX ROWID--   INDEX RANGE SCAN (IDX_CUSTOMER)

性能对比实验

-- 创建测试环境CREATE TABLE test_table AS SELECt level as id,       'Customer_' || level as name,       SYSDATE - dbms_random.value(0, 1000) as create_dateFROM dual ConNECT BY level <= 100000;CREATE INDEX idx_test_id ON test_table(id);CREATE INDEX idx_test_date ON test_table(create_date);-- 测试1：主键查询SELECt * FROM test_table WHERe id = 50000;-- 执行路径：索引扫描 -> ROWID访问 -> 返回数据-- 耗时：0.003秒-- 测试2：全表扫描相同查询SELECt  * FROM test_table WHERe id = 50000;-- 耗时：0.152秒（慢50倍！）

四、实战选择策略：什么时候选什么？

决策流程图

开始查询    ↓检查WHERe条件    ↓是否有合适索引？ → 无 → 全表扫描    ↓有估算返回行数    ↓< 5% 的数据？ → 否 → 全表扫描    ↓是需要所有列？ → 否 → 只扫描索引    ↓是索引扫描 + ROWID访问    ↓考虑索引合并/跳跃扫描

具体场景决策

-- 场景1：点查询（选择索引+ROWID）SELECt * FROM orders WHERe order_id = 123;-- 建议：主键索引 → ROWID访问-- 场景2：小范围查询（选择索引范围扫描）SELECt * FROM orders WHERe order_date >= SYSDATE - 7   AND order_date < SYSDATE;-- 建议：索引范围扫描 → ROWID访问-- 场景3：聚合查询（选择索引快速全扫描）SELECt customer_id, SUM(amount)FROM ordersGROUP BY customer_id;-- 建议：索引快速全扫描（不需要回表）-- 场景4：大范围查询（选择全表扫描）SELECt * FROM orders WHERe status = 'SHIPPED'; -- 70%的数据都是SHIPPED-- 建议：全表扫描-- 场景5：索引列覆盖（选择索引扫描）SELECt order_id, customer_id FROM ordersWHERe customer_id BETWEEN 1000 AND 2000;-- 建议：索引扫描（不需要回表）

高级优化技巧

-- 1. 使用复合索引减少回表CREATE INDEX idx_covering ON orders(customer_id, order_date, amount);-- 查询可以被索引完全覆盖SELECt customer_id, order_date, amountFROM ordersWHERe customer_id = 100;-- 不需要回表！-- 2. 监控索引使用情况SELECt index_name, blevel, leaf_blocks, distinct_keysFROM user_indexesWHERe table_name = 'ORDERS';-- 3. 识别需要重建的索引-- 如果blevel > 4，考虑重建索引ALTER INDEX idx_customer REBUILD;-- 4. 使用索引提示强制选择SELECt  *FROM ordersWHERe customer_id = 100 AND amount > 1000;

五、常见误区与真相

误区1：索引一定比全表扫描快

真相：当查询返回超过表数据的10-20%时，全表扫描通常更快。

误区2：ROWID访问总是最快

真相：ROWID访问需要"索引扫描+回表"两步，如果回表次数太多，反而慢。

误区3：应该为所有列创建索引

真相：索引维护有成本，写操作会变慢。需要平衡读写比例。

误区4：索引扫描不需要排序

真相：只有索引本身有序，如果ORDER BY与索引顺序不一致，仍需排序。

六、黄金法则

1. 数据量决定一切：小数据量（<5%）用索引，大数据量用全表扫描
2. 查询列决定策略：只需要索引列时，使用索引扫描不回表
3. 避免回表爆炸：回表超过5%的数据，考虑全表扫描
4. 利用复合索引：精心设计的复合索引是性能神器
5. 监控调整：定期分析执行计划，适时调整索引策略

最后一张表总结

记住：没有绝对的优劣，只有适合的场景。看懂执行计划，理解数据分布，才是优化之王道。