一、索引基础概念
索引类型与适用场景
-
B树索引：最常用，适合高基数列（唯一值多）的等值或范围查询。
-
位图索引：适用于低基数列（如性别、状态），常用于数据仓库。
-
函数索引：基于列的函数表达式创建（如UPPER(name)），优化带函数的查询。
-
复合索引：多列组合，列顺序至关重要（高选择性列在前）。
-
反向索引：优化模糊查询（如LIKE ‘%abc’）。
索引的优缺点
- 优点：加速数据检索，减少磁盘I/O。
- 缺点：占用存储空间；降低DML操作（增删改）效率；需定期维护。
二、索引查询方法
1. 查看索引元信息
表的所有索引
SELECT index_name, index_type, uniqueness FROM dba_indexes WHERE table_name = 'EMPLOYEES';
索引的列信息
SELECT column_name, column_position FROM dba_ind_columns WHERE index_name = 'IDX_DEPT_FIRSTNAME';
索引所在的表信息分析
SELECT i.index_name, i.table_name, ic.column_name, ic.column_position FROM dba_indexes i JOIN dba_ind_columns ic ON i.index_name = ic.index_name WHERE i.index_name = 'IDX_NAME'; --按索引名称条件查询
2. 分析索引使用情况
监控索引使用频率
SELECT * FROM v$index_usage; -- 跟踪索引是否被有效利用,需 12c 以上版本管理员权限
检查未使用索引
SELECT index_name FROM dba_indexes WHERE index_name NOT IN (SELECT name FROM v$index_usage);
索引碎片与空间效率
-- 1.查询当前用户创建的索引碎片率
SELECT index_name,
blevel,
leaf_blocks,
clustering_factor,
ROUND((leaf_blocks * 100) / NULLIF(clustering_factor, 0), 2) AS fragmentation_ratio
FROM (
SELECT di.index_name,
di.blevel,
di.leaf_blocks,
di.clustering_factor
FROM dba_indexes di
JOIN dba_tables dt ON di.table_name = dt.table_name
WHERE dt.owner = USER -- 只查询当前用户创建的表
AND di.clustering_factor > 1
) t
WHERE (leaf_blocks * 100) / clustering_factor > 30; -- >30%表示需重建
-- 2.查询 索引所在的表信息分析
SELECT i.index_name, i.table_name, ic.column_name, ic.column_position
FROM dba_indexes i
JOIN dba_ind_columns ic ON i.index_name = ic.index_name
WHERE i.index_name = 'IDX_NAME'; --按索引名称条件查询
--3.重建碎片化索引
ALTER INDEX IDX_NAME REBUILD ONLINE; -- IDX_NAME 为索引名称
3. 执行计划分析
EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 10; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
关键指标：
- INDEX RANGE SCAN：索引有效使用。
- FULL TABLE SCAN：可能缺失或未使用索引。
三、优化索引空间的策略
1. 创建索引
CREATE UNIQUE INDEX idx_user_name ON user_info(user_name) TABLESPACE idx_tbs COMPRESS NOLOGGING;
2. 重建碎片化索引
ALTER INDEX IDX_OLD REBUILD ONLINE; -- 减少空间碎片，提升查询效率
3. 调整存储参数
ALTER INDEX IDX_LARGE PCTFREE 10; -- 降低空闲空间预留，压缩索引体积
4. 删除冗余索引
DROP INDEX IDX_REDUNDANT; -- 通过监控确认使用率低的索引
5. 启用高级压缩（仅限企业版）
ALTER INDEX IDX_BIG COMPRESS ADVANCED LOW; -- 节省30-50%空间
四、关键监控指标
| | 指标 | 查看方式 | 优化阈值 |
|---|---|---|---|
| | 索引大小 | dba_segments.bytes | >表空间的20%需优化 |
| | 碎片率 | (leaf_blocks / clustering_factor) * 100 | >30%需重建 |
| | 使用频率 | v$index_usage.user_reads | 近30天无读操作可删 |
| | 分区均匀性 | dba_index_partitions.bytes 的方差值 | 方差>50%需调整分区 |
五、 查询实践案例
1. 查询 Oracle 表空间大小
-- 表空间使用率监控（含自动扩展状态）
SELECT
df.tablespace_name "Tablespace",
df.total_mb,
df.total_mb - fs.free_mb "Used_MB",
fs.free_mb "Free_MB",
ROUND((df.total_mb - fs.free_mb) / df.total_mb * 100, 2) Pct_Used, -- 使用率
autoext "AutoExt"
FROM
(SELECT tablespace_name,
SUM(bytes)/1024/1024 total_mb,
MAX(DECODE(autoextensible,'YES','Y','N')) autoext
FROM dba_data_files
GROUP BY tablespace_name) df
JOIN
(SELECT tablespace_name,
SUM(bytes)/1024/1024 free_mb
FROM dba_free_space
GROUP BY tablespace_name) fs
ON df.tablespace_name = fs.tablespace_name
WHERE ROUND((df.total_mb - fs.free_mb) / df.total_mb * 100, 2) > 80 -- 仅显示>80%使用率的表空间
ORDER BY Pct_Used DESC;
结果示例：
| Tablespace | total_mb | Used_MB | Free_MB | Pct_Used | AutoExt |
|---|---|---|---|---|---|
| TBS_PICP | 3548 | 3309 | 239 | 93 | .26 |
| TBS_PICP_NEW | 4048 | 3523 | 525 | 87 | .03 |
2. 查询 Oracle 索引使用情况
替换 PICP_FORMAL（表用户） 和 T_USER_INFO（表名称 需大写）
-- 替换 PICP_FORMAL 和 T_USER_INFO（需大写）
WITH table_info AS (
SELECT
t.owner,
t.table_name,
t.tablespace_name,
t.num_rows,
t.avg_row_len,
ROUND((t.num_rows * t.avg_row_len) / 1024 / 1024, 2) AS estimated_data_size_mb,
ROUND(SUM(s.bytes) / 1024 / 1024, 2) AS actual_table_size_mb
FROM dba_tables t
JOIN dba_segments s ON t.owner = s.owner AND t.table_name = s.segment_name
WHERE t.owner = 'PICP_FORMAL'
AND t.table_name = 'T_USER_INFO'
AND s.segment_type = 'TABLE'
GROUP BY t.owner, t.table_name, t.tablespace_name, t.num_rows, t.avg_row_len
),
index_info AS (
SELECT
i.index_name,
ROUND(s.bytes / 1024 / 1024, 2) AS index_size_mb,
i.uniqueness
FROM dba_indexes i
JOIN dba_segments s ON i.owner = s.owner AND i.index_name = s.segment_name
WHERE i.table_owner = 'PICP_FORMAL'
AND i.table_name = 'T_USER_INFO'
AND s.segment_type = 'INDEX'
)
SELECT
-- 表基本信息
ti.table_name,
ti.tablespace_name,
ti.num_rows,
ti.avg_row_len,
ti.estimated_data_size_mb,
ti.actual_table_size_mb,
-- 索引详细信息
ii.index_name,
ii.index_size_mb,
ii.uniqueness,
-- 索引汇总信息
ROUND(SUM(ii.index_size_mb) OVER (), 2) AS total_index_size_mb,
ROUND((SUM(ii.index_size_mb) OVER () / ti.actual_table_size_mb) * 100, 2) AS index_to_table_ratio_percent
FROM table_info ti
LEFT JOIN index_info ii ON 1=1
ORDER BY ii.index_size_mb DESC NULLS LAST;
示例结果如下：
| TABLE_NAME | TABLESPACE_NAME | NUM_ROWS | AVG_ROW_LEN | ESTIMATED_DATA_SIZE_MB | ACTUAL_TABLE_SIZE_MB | INDEX_NAME | INDEX_SIZE_MB | UNIQUENESS | TOTAL_INDEX_SIZE_MB | INDEX_TO_TABLE_RATIO_PERCENT |
|---|---|---|---|---|---|---|---|---|---|---|
| T_USER_INFO | TBS_PICP_NEW | 636046 | 37 | 262.55 | 271 | PK_T_USER_ID | 45 | UNIQUE | 371 | 37.09 |
| T_USER_INFO | TBS_PICP_NEW | 636046 | 37 | 262.55 | 271 | IDX_USER_NAME | 28 | NONUNIQUE | 71 | 37.09 |
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持IT俱乐部。
