本文系统介绍了数据库中的拉链表、索引和分区技术。
拉链表通过时间区间记录维度变化历史，包含开链闭链操作机制；
索引部分详解了B-Tree、位图、函数等索引类型及其适用场景，特别分析了索引失效条件和组合索引的最左原则；
分区技术重点讲解了范围分区的实现方式和查询优化。
三者共同构成了数据库性能优化的重要技术手段，各有其适用场景和实现要点，需要根据具体业务需求和数据特征进行合理选择和应用。
拉链表：记录数据历史变化，追踪每条记录的完整生命周期（START_DATE、END_DATE、FLAG）
课堂笔记：拉链表、索引与分区
一、 拉链表
1. 核心功能
定义：用于记录维度的缓慢变化过程（如员工职位、薪资的历史变更）。
解决问题：普通表同步数据会丢失历史变更记录，拉链表通过时间区间记录每一个历史状态。
2. 核心原理
结构：在源表字段基础上，新增三个关键字段：
START_DATE：该条记录生效的开始时间。END_DATE：该条记录的结束时间。FLAG：标志位（1=当前最新数据，0=历史数据）。
操作机制：
闭链：数据发生变化时，将旧记录的
END_DATE改为变化当天，FLAG改为0（关闭历史区间）。开链：插入一条新记录，
START_DATE为变化当天，END_DATE为9999-12-31，FLAG为1（开启最新区间）。
3. 典型操作 (SQL 示例)
建表 & 初始化 (2020-01-01)
sql
-- 1. 创建拉链表结构 CREATE TABLE L_EMP AS SELECT E.*, SYSDATE AS START_DATE, SYSDATE AS END_DATE, 1 AS FLAG FROM EMP E WHERE 1=2; -- 2. 初始化历史数据 INSERT INTO L_EMP SELECT E.*, TO_DATE('2020-01-01','YYYY-MM-DD') AS START_DATE, TO_DATE('9999-12-31','YYYY-MM-DD') AS END_DATE, 1 AS FLAG FROM EMP E;新增员工 (2022-03-12)
sql
-- 插入不存在于拉链表的新员工 INSERT INTO L_EMP SELECT E.*, TO_DATE('2022-03-12','YYYY-MM-DD'), TO_DATE('9999-12-31','YYYY-MM-DD'), 1 FROM EMP E WHERE NOT EXISTS (SELECT 1 FROM L_EMP F WHERE E.EMPNO = F.EMPNO);批量修改 (2023-01-01, 20号部门涨薪)
sql
-- 第一步：闭链 (关闭旧状态) UPDATE L_EMP SET END_DATE = TO_DATE('2023-01-01','YYYY-MM-DD'), FLAG = 0 WHERE DEPTNO = 20; -- 第二步：开链 (插入新状态) INSERT INTO L_EMP SELECT E.*, TO_DATE('2023-01-01','YYYY-MM-DD'), TO_DATE('9999-12-31','YYYY-MM-DD'), 1 FROM EMP E WHERE DEPTNO = 20;
4. 查询技巧
查最新数据：
WHERE FLAG = 1或END_DATE = '9999-12-31'查历史快照：
WHERE '2021-10-12' BETWEEN START_DATE AND END_DATE
5. 优缺点
优点：极大降低存储成本（相比全量快照表），完整记录变化过程。
缺点：查询相对复杂（需带时间范围），ETL开发逻辑较普通表复杂。
二、 索引
核心概念
类比：书的目录。
原理：通过构建树状结构（如B-Tree），大幅减少数据扫描次数（从全表遍历 O(n) 降为树深度 O(log n)）。
索引的原理（树状结构示例）
假设表中有数据 ID：1 ~ 100，内部构造一个索引树： 1~100 / \ 1~50 51~100 / \ / \ 1~25 26~50 51~75 76~100 / \ ... / \ / \ 1~12 13~25 ... 51~62 63~75 ... 87~100 ... ... ... ... / \ 1 2 3... 87 88 89...100 ROWID1... ROWID88...索引分类
| 类型 | 关键字 | 适用场景 | 示例 |
|---|---|---|---|
| 唯一索引 | UNIQUE INDEX | 列值唯一（允许NULL），如身份证号 | CREATE UNIQUE INDEX ... ON EMP(EMPNO); |
| 位图索引 | BITMAP INDEX | 重复值高、基数小的列，如性别、学历 | CREATE BITMAP INDEX ... ON EMP(DEPTNO); |
| 函数索引 | INDEX(普通) | 查询中经常对列使用函数（如UPPER） | CREATE INDEX ... ON EMP(UPPER(ENAME)); |
| 组合索引 | INDEX(普通) | 多字段联合查询，注意最左原则 | CREATE INDEX ... ON EMP(EMPNO, ENAME); |
详细说明
唯一索引
sql
CREATE UNIQUE INDEX ABCD ON EMP(EMPNO);
特性：限定该列值唯一，允许为 NULL（Oracle 允许多个 NULL）
与主键区别：主键 = 唯一 + 非空
位图索引
sql
CREATE BITMAP INDEX ABC ON EMP(DEPTNO);
特性：针对重复性高、基数小的列
常用于：性别、学历、婚姻状态、订单状态、地区等
⚠️ 适合 OLAP（数据仓库），不适合频繁更新的 OLTP 系统
函数索引
sql
CREATE INDEX AB ON EMP(UPPER(ENAME));
特性：针对函数调用时走索引扫描
sql
SELECT * FROM EMP WHERE UPPER(ENAME) = 'SMITH'; -- 走索引 SELECT * FROM EMP WHERE ENAME = 'SMITH'; -- 不走该索引
组合索引
sql
CREATE INDEX XXX ON EMP(EMPNO, ENAME, JOB);
特性：遵循最左前缀原则，只有第一列被引用时才走索引
sql
SELECT * FROM EMP WHERE EMPNO = 10 AND ENAME = 'S'; -- ✅ 走索引（第一列在条件中） SELECT * FROM EMP WHERE ENAME = 'S' AND JOB = 'K'; -- ❌ 不走索引（第一列不在条件中） SELECT * FROM EMP WHERE EMPNO = 10; -- ✅ 走索引 SELECT * FROM EMP WHERE EMPNO = 10 AND JOB = 'K'; -- ✅ 走索引（第一列在条件中）
索引失效场景 (重点)
列运算：
WHERE salary + 1000 = 5000(应改为salary = 4000)函数使用：
WHERE UPPER(name) = 'SMITH'(除非建立了函数索引)隐式转换：
WHERE empno = '123'(字段是数字类型)不等判断：
!=,<>,IS NULL,IS NOT NULL模糊查询：
LIKE '%SMITH'(通配符在前)组合索引：未使用索引的第一个字段作为过滤条件。
优缺点
优点：提升查询效率。
缺点：占用磁盘空间；降低
INSERT,UPDATE,DELETE的执行效率（需要维护索引树）。
常见面试题汇总
Q1：索引的类型有哪些？
唯一索引、位图索引、组合索引、函数索引、B-Tree 索引（默认）、反向键索引等。
Q2：索引的优缺点？
优点：提升查询效率（SELECT）
缺点：降低 DML 效率（INSERT/UPDATE/DELETE）、占用存储空间、增加维护成本
Q3：唯一索引与主键的区别？
| 唯一索引 | 主键 | |
|---|---|---|
| 唯一性 | ✅ | ✅ |
| 允许 NULL | ✅（Oracle 允许多个） | ❌ |
| 每表数量 | 多个 | 一个 |
| 是否自动创建索引 | 需手动 | 自动创建唯一索引 |
Q4：组合索引的注意事项？
遵循最左前缀原则：查询条件必须包含组合索引的第一列，否则不走索引扫描。
Q5：索引失效的场景？
索引列使用NULL 判断：
WHERE col IS NULL或IS NOT NULL索引列使用不等值判断：
!=、<>、NOT IN、NOT EXISTS索引列进行逻辑运算、函数转换：
WHERE UPPER(col) = 'A'组合索引第一列未被引用
模糊查询通配符在前：
WHERE col LIKE '%abc'隐式类型转换：
WHERE varchar_col = 123（varchar_col 是字符串）
Q6：一张表没有走索引扫描的原因？
索引失效（上述场景）
数据量太少：全表扫描更快，优化器不走索引
选择性差：过滤后数据量占比高（如 10 亿条过滤剩 9.9 亿），全表扫描更优
统计信息过时：优化器判断失误
使用了提示（Hint）强制不走索引
补充：索引使用原则
适合建索引的场景
WHERE、ORDER BY、GROUP BY、JOIN 的列
选择性高的列（distinct 值多）
数据量大的表
不适合建索引的场景
小表（几百行）
频繁 DML 的表
选择性极差的列（如性别，只有 2 种值）
很少出现在 WHERE 中的列
在 Oracle 中，唯一索引允许有多个 NULL 值。
原因
Oracle 将 NULL 视为“未知值”，判断两个 NULL 是否相等时，结果既不是 TRUE 也不是 FALSE，而是UNKNOWN。因此，Oracle 不会将多个 NULL 视为违反唯一约束。
与其他数据库的对比
数据库 唯一约束/索引中允许的 NULL 数量 Oracle 多个 PostgreSQL 多个 SQL Server 单个（2008 之前），多个（2008+ 默认行为） MySQL 多个（InnoDB 引擎） DB2 不允许 NULL
注意事项
如果需要强制只允许一个 NULL，可以通过函数索引实现：
sql
-- 只允许一个 NULL 的实现方式 CREATE UNIQUE INDEX idx_one_null ON test_unique (CASE WHEN name IS NULL THEN 1 ELSE NULL END);这样会确保最多只有一条记录的 name 为 NULL。
位图索引的核心特点
位图索引适合低基数（distinct值少）的列，不适合高基数列。
主要使用场景
1.数据仓库/OLAP 系统
大量历史数据分析、报表查询
DML 操作少（主要是批量插入，很少单条更新/删除）
2.低基数列
列类型 示例 基数 性别 男、女 2 婚姻状况 已婚、未婚、离异、丧偶 4 学历 小学、初中、高中、本科、硕士、博士 6 是否活跃 是、否 2 地区 华东、华南、华北、华西、东北 5 订单状态 待支付、已支付、已发货、已完成、已取消 5-10 3.多条件组合查询（AND/OR 条件）
位图索引最擅长处理这种查询，因为两个位图可以快速进行位运算（AND/OR/NOT）：
sql
-- 典型 OLAP 查询 SELECT COUNT(*) FROM sales WHERE region = '华东' -- 低基数列 AND product_category = '家电' -- 低基数列 AND is_active = '是'; -- 低基数列4.统计分析查询
sql
-- 分组聚合查询 SELECT status, COUNT(*), SUM(amount) FROM orders GROUP BY status;5.星型模型中的维度表
数据仓库的维度表通常包含大量低基数列，非常适合位图索引。
不适合的场景
场景 原因 OLTP 系统 频繁的 INSERT/UPDATE/DELETE 会导致位图索引锁开销大，可能锁住多行 高基数列（如 ID、姓名、时间戳） 位图会非常稀疏，存储效率低，查询效果差 频繁单行更新 更新一行可能影响多个位图块，导致严重的锁竞争 性能对比示例
sql
-- 假设有 1000 万行，status 有 5 种取值 -- B-Tree 索引：需要扫描更多索引条目 SELECT * FROM orders WHERE status = '已完成'; -- 位图索引：只需读取一个位图块，效率极高 -- 位图存储：'已完成' -> 1010010101...（0/1 序列）Oracle 创建位图索引语法
sql
-- 单列位图索引 CREATE BITMAP INDEX idx_sales_region ON sales(region); -- 复合位图索引 CREATE BITMAP INDEX idx_sales_region_status ON sales(region, status);总结
判断维度 适合位图索引 系统类型 数据仓库、报表系统、BI 分析 列基数 很低（< 100，通常 < 20） DML 频率 低（批量插入为主） 查询类型 多条件 AND/OR、聚合统计 并发更新 无或少
三、 分区
1. 核心功能
定义：将一张大表物理拆分为多个小表（分区），逻辑上仍视为一张表。
目的：查询时只扫描特定分区，减少数据扫描范围，提升性能。
2. 分区类型
范围分区：按时间或数值范围划分（最常用）。
列表分区：按离散值划分（如按省份）。
哈希分区：按哈希算法均匀分布数据。
组合分区：多种方式嵌套（如先范围后列表）。
3. 实操案例 (范围分区)
sql
-- 1. 创建按入职年份的范围分区表 CREATE TABLE F_EMP( EMPNO NUMBER, ENAME VARCHAR2(100), HIREDATE DATE, DEPTNO NUMBER ) PARTITION BY RANGE(HIREDATE) ( PARTITION PAR_1980 VALUES LESS THAN (TO_DATE('1981-01-01','YYYY-MM-DD')), PARTITION PAR_1981 VALUES LESS THAN (TO_DATE('1982-01-01','YYYY-MM-DD')) -- 存放1981年数据 ); -- 2. 查询特定分区 (直接跳过1980、1982年的数据) SELECT * FROM F_EMP PARTITION (PAR_1981) M INNER JOIN DEPT D ON M.DEPTNO = D.DEPTNO;范围分区的顺序要求 ✅必须严格
范围分区必须按照 VALUES LESS THAN 的数值/日期大小严格递增顺序定义
sql
-- ✅ 正确写法：按时间递增 PARTITION BY RANGE(HIREDATE) ( PARTITION PAR_1980 VALUES LESS THAN (TO_DATE('1981-01-01','YYYY-MM-DD')), PARTITION PAR_1981 VALUES LESS THAN (TO_DATE('1982-01-01','YYYY-MM-DD')), PARTITION PAR_1982 VALUES LESS THAN (TO_DATE('1983-01-01','YYYY-MM-DD')) ); -- ❌ 错误写法：顺序混乱 PARTITION BY RANGE(HIREDATE) ( PARTITION PAR_1981 VALUES LESS THAN (TO_DATE('1982-01-01','YYYY-MM-DD')), PARTITION PAR_1980 VALUES LESS THAN (TO_DATE('1981-01-01','YYYY-MM-DD')), -- 报错！ PARTITION PAR_1982 VALUES LESS THAN (TO_DATE('1983-01-01','YYYY-MM-DD')) );
"范围分区定义顺序" → 必须：从小到大递增
4. 常见题型
Q：为什么用了索引还是慢？
A：可能数据量太小（全表扫描更快）；或者过滤后数据量占比太高（如过滤后还剩80%数据）。
Q：主键和唯一索引的区别？
A：主键 = 唯一 + 非空；唯一索引 = 唯一 + 允许NULL（但只能有一个NULL）。
Q：组合索引 (A, B, C) 在
WHERE B=1 AND C=2时会生效吗？A：不会。因为缺少了第一列
A。
