Oracle数据库设计规范篇{五} ——索引规范【上】（分区索引、函数索引、位图索引、外键索引）

梁敬彬梁敬弘兄弟出品

往期回顾
Oracle数据库设计规范篇{一}——表规范
Oracle数据库设计规范篇{二}——物理设计规范【上】（基本规范、表设计规范）
Oracle数据库设计规范篇{三}——物理设计规范【中】（分区设计规范）
Oracle数据库设计规范篇{四}——物理设计规范【下】（列的设计、命名的规范）

3.索引规范

3.1 用不上分区条件的局部索引不宜建

分区表建立分区索引后，如果在查询应用中无法用到这个分区索引列的条件，索引读将可能遍历所有的分区，如果有100个分区，相当于遍历了100个小索引，将会严重影响性能，此时需要慎重考虑，判断是否需要修改为全局索引。

-- 创建分区表
CREATE TABLE SALES_HISTORY (
    SALE_ID NUMBER(10),
    CUSTOMER_ID NUMBER(10),
    SALE_DATE DATE,
    AMOUNT NUMBER(10,2)
)
PARTITION BY RANGE (SALE_DATE) (
    PARTITION SALES_Q1_2023 VALUES LESS THAN (TO_DATE('01-APR-2023', 'DD-MON-YYYY')),
    PARTITION SALES_Q2_2023 VALUES LESS THAN (TO_DATE('01-JUL-2023', 'DD-MON-YYYY')),
    PARTITION SALES_Q3_2023 VALUES LESS THAN (TO_DATE('01-OCT-2023', 'DD-MON-YYYY')),
    PARTITION SALES_Q4_2023 VALUES LESS THAN (TO_DATE('01-JAN-2024', 'DD-MON-YYYY'))
);

-- 如果创建局部索引但查询中不使用分区键条件，则不建议
CREATE INDEX SALES_CUST_IDX ON SALES_HISTORY (CUSTOMER_ID) LOCAL;

-- 当执行以下查询时，会扫描所有分区索引
SELECT * FROM SALES_HISTORY WHERE CUSTOMER_ID = 1001;

-- 建议：如果CUSTOMER_ID经常用于查询而不带SALE_DATE条件，应该使用全局索引
CREATE INDEX SALES_CUST_IDX_GLOBAL ON SALES_HISTORY (CUSTOMER_ID) GLOBAL;

3.2 函数索引大多用于列运算，一般需要避免

从实际应用情况来分析，应用函数索引大多是因为设计阶段考虑步骤，比如trunc(时间列)的写法，往往可以轻易转换成去掉trunc的写法，所以需要取出验证，如下：

select       t.index_name,
       t.index_type,
       t.status,
       t.blevel,
       t.leaf_blocks
  from user_indexes t
 where index_type in ('FUNCTION-BASED NORMAL');

3.3 位图索引遇到更新将是噩梦，需谨慎设计

1. 位图索引不适合用在表频繁更新的场合。
2. 位图索引不适合在所在列重复度很低的场合。
   因为位图索引的应用比较特殊，使用场合比较少，因此有必要取出系统中的位图索引，进行核对检测，如下：

select 
       t.index_name,
       t.index_type,
       t.status,
       t.blevel,
       t.leaf_blocks
  from user_indexes t
 where index_type in ('BITMAP');

3.4 外键未建索引将引发死锁及影响表连接性能

外键未建索引，将有可能导致两个严重的问题：1.更新相关的表产生死锁；2两表关联查询时性能低下，因此设计中需要谨慎考虑。以下为外键未建索引的表的查找语句，以方便我们分析和确认：

select table_name,
       constraint_name,
       cname1 || nvl2(cname2, ',' || cname2, null) ||
       nvl2(cname3, ',' || cname3, null) ||
       nvl2(cname4, ',' || cname4, null) ||
       nvl2(cname5, ',' || cname5, null) ||
       nvl2(cname6, ',' || cname6, null) ||
       nvl2(cname7, ',' || cname7, null) ||
       nvl2(cname8, ',' || cname8, null) columns
  from (select b.table_name,
               b.constraint_name,
               max(decode(position, 1, column_name, null)) cname1,
               max(decode(position, 2, column_name, null)) cname2,
               max(decode(position, 3, column_name, null)) cname3,
               max(decode(position, 4, column_name, null)) cname4,
               max(decode(position, 5, column_name, null)) cname5,
               max(decode(position, 6, column_name, null)) cname6,
               max(decode(position, 7, column_name, null)) cname7,
               max(decode(position, 8, column_name, null)) cname8,
               count(*) col_cnt
          from (select substr(table_name, 1, 30) table_name,
                       substr(constraint_name, 1, 30) constraint_name,
                       substr(column_name, 1, 30) column_name,
                       position
                  from user_cons_columns) a,
               user_constraints b
         where a.constraint_name = b.constraint_name
           and b.constraint_type = 'R'
         group by b.table_name, b.constraint_name) cons
 where col_cnt> ALL
 (select count(*)
          from user_ind_columns i
         where i.table_name = cons.table_name
           and i.column_name in (cname1, cname2, cname3, cname4, cname5,
                cname6, cname7, cname8)
           and i.column_position<= cons.col_cnt
         group by i.index_name)

3.5 索引选择性评估

选择性高的列（唯一值比例高的列）更适合建立索引。可以通过计算列唯一值与总行数比例来评估。

示例：

-- 评估索引列的选择性
SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity
FROM table_name;

-- 高选择性(接近1)表示该列适合建立索引
-- 低选择性(接近0)表示该列不适合单独建立索引

-- 示例：评估CUSTOMER表中EMAIL列的选择性
SELECT COUNT(DISTINCT EMAIL) / COUNT(*) AS email_selectivity
FROM CUSTOMER;

未完待续…
Oracle数据库设计规范篇＜六＞——索引规范【下】（联合索引、索引数量、没有索引、失效索引）

系列回顾

“大白话人工智能” 系列
“数据库拍案惊奇” 系列
“世事洞明皆学问” 系列