Oracle数据库设计规范篇{六}——索引规范【下】（联合索引、索引数量、没有索引、失效索引）

梁敬彬

915人浏览 · 2025-04-30 07:05:29

梁敬彬 · 2025-04-30 07:05:29 发布

梁敬彬梁敬弘兄弟出品

往期回顾
Oracle数据库设计规范篇＜一＞——表规范
 Oracle数据库设计规范篇＜二＞——物理设计规范【上】（基本规范、表设计规范）
Oracle数据库设计规范篇＜三＞——物理设计规范【中】（分区设计规范）
Oracle数据库设计规范篇＜四＞——物理设计规范【下】（列的设计、命名的规范）
Oracle数据库设计规范篇＜五＞ ——索引规范【上】（分区索引、函数索引、位图索引、外键索引）

3.5 建立联合索引需谨慎

3.5.1 要结合单列查询考虑前缀

如：即可以建立 col1,col2 的联合索引，又可以建col2,col1的联合索引，此时如果存在col1 列单独查询较多的情况下，一般倾向于建col1,col2的联合索引。

3.5.2 超过4个字段的联合索引需注意

select table_name, index_name, count(*)
 from user_ind_columns
 group by table_name, index_name
having count(*) >= 4
order by count(*) desc

3.5.3 范围查询影响组合索引

组合查询中，如果有等值条件和范围条件组合的情况，等值条件在前，性能更高。
如：where col1=2 and col2>=100 and col2<=120 ,此时是col1,col2的组合索引性能高过col2,col1的组合索引，可以在系统执行SQL进行简单分析，如下：

select sql_text,
        sql_id,
        service,
        module,
        t.first_load_time,
  from v$sql t
 where (sql_text like '%>%' or sql_text like '%<%' or sql_text like '%<>%')
   and sql_text not like '%=>%'
   and service not like 'SYS$%'

3.5.4 需考虑回表因素

如果建索引可以避免回表（在索引中即可完成检测），有时也可考虑对多列建组合索引，不过需要谨慎判断必要性，同时组合索引列不宜超过4个。

-- 创建一个员工表
CREATE TABLE EMPLOYEES (
    EMPLOYEE_ID NUMBER(6) PRIMARY KEY,
    FIRST_NAME VARCHAR2(20),
    LAST_NAME VARCHAR2(25),
    EMAIL VARCHAR2(25),
    PHONE_NUMBER VARCHAR2(20),
    HIRE_DATE DATE,
    JOB_ID VARCHAR2(10),
    SALARY NUMBER(8,2),
    DEPARTMENT_ID NUMBER(4)
);

-- 分析查询模式：如果常见查询只需要几个特定列的数据，并且都是这几个列组合在一起查询的，同时更新也不频繁。
-- 查询示例：频繁查询员工姓名和部门
SELECT FIRST_NAME, LAST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 50;

-- 避免回表的索引设计：创建覆盖索引，包含所有查询所需的列
CREATE INDEX IDX_EMP_DEPT_NAME ON EMPLOYEES(DEPARTMENT_ID, FIRST_NAME, LAST_NAME);

-- 这样查询时所有数据都可以从索引中获取，无需回表访问原表

-- 在实际应用中监控索引的使用情况
SELECT index_name, table_name, used, start_monitoring 
FROM v$object_usage
WHERE index_name = 'IDX_EMP_DEPT_NAME';

3.6 单表索引个数需控制

3.6.1 索引个数超过5个以上的

超过5个以上的索引，在表的记录很大时，将会极大的影响该表的更新，因此在表中建索引时需要谨慎考虑，以下是查询索引个数超过5个表的脚本，如下：

select table_name, count(*)
 from user_indexes
 group by table_name
having count(*) >= 5
 order by count(*) desc

3.6.2 建后2个月内从未使用过的索引

一般来说，在2个月内从未被用到的索引是多余的索引，可以考虑删除，具体跟踪和定位的方法如下：

select 'alter index '||owner||'.'||index_name||' monitoring usage;'
from user_indexes;

然后观察：

set linesize 166
col INDEX_NAME for a10
col TABLE_NAME for a10
col START_MONITORING for a25
col END_MONITORING for a25
select * from v$object_usage;

–停止对索引的监控，观察v$object_usage状态变化

alter index IDX_OBJECT_ID nomonitoring usage;

3.7单表无任何无索引需重视

单表无任何索引的情况一般比较少见，可以查询出来，再结合SQL应用进行分析，观察该表的大小以及是否有时间字段及编码字段这样的适宜建索引的列，分析可以从以下脚本开始：

select table_name
 from user_tables
 where table_name not in (select table_name from user_indexes);

3.8需注意索引的失效情况

3.8.1 导致索引失效的一般因素

对表进行move操作，会导致索引失效，操作需考虑索引的重建。
对分区表进行系列操作，如split、drop 、truncate分区时，容易导致分区表的全局索引失效，需要考虑增加update global indexes的关键字进行操作，或者重建索引。
3.分区表SPLIT的时候，如果MAX区中已经有记录了，这个时候SPLIT就会导致有记录的新增分区的局部索引失效
3.8.2 不同类型索引失效的查询
普通表及分区表的全局索引失效：

select index_name, table_name, tablespace_name, index_type
  from user_indexes
 where status = 'UNUSABLE';

分区表局部索引失效:

select t1.index_name,                         
       t1.partition_name,                     
       t1.global_stats,                       
       t2.table_name,                         
       t2.table_type                          
  from user_ind_partitions t1, user_indexes t2
 where t2.index_name = t1.index_name          
   and t1.status = 'UNUSABLE'