位置：文档库 > 数据库 > Oracle找出需要建立索引的表

Oracle找出需要建立索引的表

老子上传于 2021-01-07 23:51

《Oracle找出需要建立索引的表》

在Oracle数据库性能优化中，索引是提升查询效率的核心工具之一。然而，盲目创建索引可能导致存储空间浪费、写入性能下降以及维护成本增加。因此，如何科学地识别需要建立索引的表，成为DBA和开发人员的重要课题。本文将从索引原理、性能分析方法、工具使用及实践案例四个维度，系统阐述如何精准定位需要优化索引的表。

一、索引的核心作用与适用场景

索引是数据库中一种特殊的数据结构，通过建立B树或位图索引，将表中特定列的值与物理存储位置关联，从而加速数据检索。其核心价值在于减少全表扫描（Full Table Scan）的次数，尤其适用于以下场景：

WHERE子句中的等值查询（如WHERE user_id = 1001）
ORDER BY、GROUP BY等排序操作
表连接中的关联字段（如JOIN ON a.id = b.a_id）
高频查询的组合条件（如WHERE status = 'ACTIVE' AND create_time > SYSDATE-30）

但索引并非万能药，以下情况需谨慎使用：

表数据量极小（如少于1000行）
列值重复率过高（如性别字段，选择性低于5%）
频繁更新的表（索引维护成本可能超过查询收益）

二、识别需要索引的表的五大方法

方法1：通过AWR报告分析高负载SQL

Oracle AWR（Automatic Workload Repository）报告是性能分析的黄金标准。通过以下步骤定位问题SQL：

生成AWR报告：

SQL> @?/rdbms/admin/awrrpt.sql
-- 选择报告类型（HTML/TEXT）和时间范围

在"SQL Statistics"部分查看"Top SQL by Elapsed Time"或"Top SQL by CPU Time"
关注执行计划中存在"FULL TABLE SCAN"的SQL，尤其是涉及大表的操作

示例：某订单表（ORDERS）的查询执行计划显示全表扫描，但WHERE条件包含ORDER_DATE > SYSDATE-7，此时在ORDER_DATE列建立索引可显著提升性能。

方法2：使用SQL Trace和TKPROF

对于临时诊断，可通过SQL Trace捕获单个会话的详细执行信息：

-- 开启跟踪
SQL> ALTER SESSION SET tracefile_identifier = 'index_analysis';
SQL> ALTER SESSION SET events '10046 trace name context forever, level 12';

-- 执行待分析SQL
SQL> SELECT * FROM CUSTOMERS WHERE REGION = 'APAC';

-- 关闭跟踪
SQL> ALTER SESSION SET events '10046 trace name context off';

-- 使用TKPROF格式化跟踪文件
$ tkprof [trace_file_path] [output_file] sys=no sort=prsela,exeela,fchela

在生成的报告中，查找"FULL TABLE SCAN"行数多且执行时间长的SQL，其涉及的表即为候选索引表。

方法3：监控V$SQL和V$SQL_PLAN

实时监控高频执行的SQL及其执行计划：

SELECT sql_id, executions, elapsed_time/1000000 as elapsed_sec,
       sql_text
FROM v$sqlarea
WHERE executions > 100
ORDER BY elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;

-- 查看特定SQL的执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id', null, 'ALLSTATS LAST'));

重点关注"Cost"值高且存在全表扫描的SQL。

方法4：分析数据分布与选择性

通过统计信息评估列的选择性（Distinct Values / Total Rows）：

-- 计算列的选择性
SELECT 
  COUNT(DISTINCT column_name) / COUNT(*) * 100 AS selectivity_pct
FROM table_name;

-- 查看直方图信息（需先收集统计信息）
SELECT 
  endpoint_number, endpoint_value
FROM table_name
WHERE column_name IS NOT NULL
ORDER BY endpoint_value;

选择性高于20%的列通常适合建索引，而低于5%的列（如状态标志）可能收益有限。

方法5：利用DBMS_STATS收集统计信息

准确的统计信息是优化器生成高效执行计划的基础：

-- 收集表统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'SCHEMA_NAME',
  tabname => 'TABLE_NAME',
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO',
  degree => 4,
  cascade => TRUE
);

设置cascade=>TRUE可同时收集索引统计信息，避免因统计信息过时导致的错误执行计划。

三、索引创建的最佳实践

1. 选择合适的索引类型

B树索引：默认选择，适用于等值查询和高选择性列
位图索引：适合低基数列（如性别、状态）和统计查询
函数索引：对计算列或函数结果建索引（如UPPER(name)）
复合索引：遵循最左前缀原则，将高频查询条件放在左侧

2. 避免过度索引

每个索引会增加：

10%-20%的存储空间占用
INSERT/UPDATE/DELETE操作的维护开销
重建索引的时间成本

建议单表索引数不超过5个，关键业务表可放宽至8个。

3. 定期维护索引

-- 重建碎片化严重的索引
ALTER INDEX index_name REBUILD ONLINE;

-- 收集索引统计信息
EXEC DBMS_STATS.GATHER_INDEX_STATS('SCHEMA_NAME', 'INDEX_NAME');

四、实践案例：电商系统索引优化

案例背景

某电商平台的订单查询接口响应时间从200ms飙升至2s，经AWR报告分析发现，以下SQL存在全表扫描：

SELECT o.order_id, o.amount, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'SHIPPED'
AND o.order_date BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD') 
                      AND TO_DATE('2023-01-31', 'YYYY-MM-DD')
ORDER BY o.order_date DESC;

优化步骤

分析选择性：

-- status列选择性（5种状态）
SELECT COUNT(DISTINCT status)/COUNT(*) FROM orders; -- 返回0.003（0.3%）

-- order_date列选择性（每日约10万订单）
SELECT COUNT(DISTINCT TRUNC(order_date))/COUNT(*) FROM orders; -- 返回0.3（30%）

创建复合索引：

CREATE INDEX idx_orders_status_date ON orders(status, order_date DESC);

验证效果：

-- 执行计划显示改用INDEX RANGE SCAN
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id', null, 'ALLSTATS LAST'));

优化后接口响应时间降至80ms，CPU使用率下降40%。

五、常见误区与解决方案

误区1：索引越多越好

过度索引会导致：

DML操作变慢（每个索引需同步更新）
优化器选择困难（可能选择次优索引）

解决方案：定期审查无用索引（通过V$OBJECT_USAGE监控索引使用情况）。

误区2：忽视索引维护成本

某金融系统每月批量导入数据时因索引过多导致导入时间从2小时延长至8小时。解决方案：

-- 批量操作前禁用索引
ALTER INDEX index_name UNUSABLE;

-- 导入完成后重建
ALTER INDEX index_name REBUILD;

误区3：复合索引顺序错误

错误示例：对WHERE city = 'Beijing' AND name LIKE '张%'创建索引(name, city)会导致无法使用city列过滤。正确顺序应为(city, name)。

六、自动化监控方案

通过以下脚本定期生成索引优化建议：

-- 识别全表扫描的高负载表
SELECT 
  o.owner, o.object_name, o.object_type,
  s.executions, s.elapsed_time/1000000 as elapsed_sec,
  s.sql_text
FROM 
  v$sqlarea s
JOIN 
  dba_objects o ON s.address = o.data_object_id
WHERE 
  s.executions > 50
  AND s.elapsed_time > 10000000 -- 超过10秒
  AND s.sql_text LIKE '%FULL%'
ORDER BY 
  s.elapsed_time DESC;

-- 生成索引建议报告
SELECT 
  t.table_name, c.column_name,
  ROUND(c.num_distinct/t.num_rows*100,2) as selectivity,
  CASE WHEN c.num_distinct/t.num_rows > 0.2 THEN '建议建索引'
       ELSE '谨慎建索引' END as recommendation
FROM 
  dba_tables t
JOIN 
  dba_tab_columns c ON t.owner = c.owner AND t.table_name = c.table_name
WHERE 
  t.num_rows > 10000
  AND c.data_type IN ('NUMBER','VARCHAR2','DATE')
ORDER BY 
  selectivity DESC;

关键词：Oracle索引优化、AWR报告分析、SQL Trace、执行计划、索引选择性、复合索引、索引维护、性能监控

简介：本文系统阐述了Oracle数据库中识别需要建立索引的表的方法，涵盖AWR报告分析、SQL Trace工具使用、数据选择性评估等五大技术手段，结合电商系统优化案例，提供了从问题定位到索引创建的全流程解决方案，并总结了常见误区与自动化监控方案。

立即下载

数据库相关