我要评论一、索引基础概念
索引类型与适用场景
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 |
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论