函数索引 - Function-Based Index详解

定义

函数索引 (Function-Based Index, FBI) 是一种建立在表达式或函数计算结果上的索引,而非直接建立在列值上。当查询条件中包含对列的函数调用时,普通索引无法使用,而函数索引可以显著加速这类查询。函数索引预先计算并存储表达式的结果,查询时直接使用预计算值进行索引查找。

核心特征

特征	说明
索引键	表达式/函数结果,非原始列值
适用场景	WHERE中包含函数调用
维护成本	高于普通索引(需维护表达式)
存储空间	取决于表达式结果大小
典型应用	大小写不敏感搜索、计算列
支持数据库	Oracle(原生), MySQL 8.0.13+, PostgreSQL

与普通索引对比

sql

-- 场景: 大小写不敏感的用户名搜索

-- 普通索引(无效!)
CREATE INDEX idx_username ON users(username);

SELECT * FROM users WHERE UPPER(username) = 'ALICE';
-- ✗ 索引失效! 全表扫描
-- 原因: 索引的是原始值"alice",查询用的是UPPER("alice")="ALICE"


-- 函数索引(有效!)
CREATE INDEX idx_upper_username ON users(UPPER(username));

SELECT * FROM users WHERE UPPER(username) = 'ALICE';
-- ✓ 使用函数索引
-- 原理: 索引中存储的是"ALICE",可直接查找

为什么需要函数索引?

问题1: 函数导致索引失效

sql

-- 用户表
CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  email VARCHAR(255),
  username VARCHAR(100),
  created_at DATETIME
);

CREATE INDEX idx_email ON users(email);


-- 查询1: 正常查询(使用索引)
SELECT * FROM users WHERE email = 'alice@example.com';
-- ✓ 使用idx_email索引


-- 查询2: 包含函数(索引失效)
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
-- ✗ 全表扫描!
-- 原因: WHERE条件对列应用了函数


-- 查询3: 包含计算
SELECT * FROM orders 
WHERE YEAR(order_date) = 2024;
-- ✗ 全表扫描!
-- YEAR()函数使索引失效

性能影响:

100万行users表:

普通索引查询:
SELECT * FROM users WHERE email = 'alice@example.com';
→ 耗时: 0.5ms (索引查找)

函数查询(无函数索引):
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
→ 耗时: 500ms (全表扫描)
→ 慢1000倍!

函数查询(有函数索引):
CREATE INDEX idx_lower_email ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
→ 耗时: 0.5ms (函数索引)
→ 恢复索引性能!

问题2: 计算列重复计算

sql

-- 订单表
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  unit_price DECIMAL(10,2),
  quantity INT,
  discount DECIMAL(5,2),
  order_date DATE
);

-- 查询: 找出总金额大于1000的订单
SELECT * FROM orders
WHERE (unit_price * quantity * (1 - discount)) > 1000;

-- 问题:
-- 1. 每行都要计算表达式
-- 2. 无法使用索引
-- 3. CPU开销大

Oracle函数索引实现

创建语法

sql

-- Oracle是最早支持函数索引的数据库

-- 示例1: 大小写不敏感搜索
CREATE INDEX idx_upper_name ON employees(UPPER(first_name));

SELECT * FROM employees 
WHERE UPPER(first_name) = 'JOHN';
-- ✓ 使用函数索引


-- 示例2: 复合函数索引
CREATE INDEX idx_emp_dept ON employees(
  UPPER(department_id),
  LOWER(job_title)
);

SELECT * FROM employees
WHERE UPPER(department_id) = 'SALES'
  AND LOWER(job_title) = 'manager';


-- 示例3: 自定义函数
CREATE OR REPLACE FUNCTION get_age(birth_date DATE) 
RETURN NUMBER DETERMINISTIC AS
BEGIN
  RETURN MONTHS_BETWEEN(SYSDATE, birth_date) / 12;
END;

CREATE INDEX idx_emp_age ON employees(get_age(birth_date));

SELECT * FROM employees
WHERE get_age(birth_date) > 30;


-- 示例4: CASE表达式
CREATE INDEX idx_salary_range ON employees(
  CASE 
    WHEN salary < 5000 THEN 'LOW'
    WHEN salary < 10000 THEN 'MEDIUM'
    ELSE 'HIGH'
  END
);

SELECT * FROM employees
WHERE CASE 
  WHEN salary < 5000 THEN 'LOW'
  WHEN salary < 10000 THEN 'MEDIUM'
  ELSE 'HIGH'
END = 'HIGH';

内部结构

/* Oracle函数索引内部结构(简化) */

typedef struct fbi_index {
  /* 标准索引头 */
  index_header_t header;
  
  /* 函数信息 */
  expression_t* expression;   /* 编译后的表达式 */
  char* function_name;    /* 函数名 */
  
  /* 依赖关系 */
  uint32_t num_dependencies;
  dependency_t dependencies[];  /* 依赖的列和函数 */
  
  /* 统计信息 */
  histogram_t* histogram;   /* 函数结果的直方图 */
  
} fbi_index_t;

/**
 * 函数索引插入
 */
void fbi_insert(
  fbi_index_t* index,
  row_t* new_row)
{
  /* 1. 计算表达式结果 */
  datum_t result = evaluate_expression(
    index->expression, 
    new_row);
  
  /* 2. 插入到B+Tree(像普通索引一样) */
  btree_insert(index->btree, result, new_row->rowid);
  
  /* 3. 更新统计信息 */
  update_histogram(index->histogram, result);
}

/**
 * 函数索引查询
 */
rowid_list_t* fbi_search(
  fbi_index_t* index,
  datum_t search_value)
{
  /* 直接使用预计算的函数值搜索 */
  return btree_search(index->btree, search_value);
}

MySQL函数索引实现

MySQL 8.0.13+支持

sql

-- MySQL 8.0.13开始支持隐藏列实现的函数索引

-- 示例1: 大小写不敏感搜索
CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  email VARCHAR(255),
  username VARCHAR(100)
);

-- 创建函数索引(MySQL 8.0.13+)
CREATE INDEX idx_lower_email ON users((LOWER(email)));

-- 查询自动使用函数索引
EXPLAIN
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
-- type: ref
-- key: idx_lower_email  ✓


-- 示例2: 计算列索引
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  unit_price DECIMAL(10,2),
  quantity INT,
  total_amount DECIMAL(12,2) GENERATED ALWAYS AS 
    (unit_price * quantity) STORED,  -- 生成列
  
  INDEX idx_total (total_amount)
);

-- 或者使用虚拟列
ALTER TABLE orders 
ADD COLUMN total_virtual DECIMAL(12,2) 
GENERATED ALWAYS AS (unit_price * quantity) VIRTUAL,
ADD INDEX idx_total_virtual (total_virtual);


-- 示例3: JSON字段索引
CREATE TABLE events (
  id BIGINT PRIMARY KEY,
  event_data JSON,
  
  -- 提取JSON字段建立索引
  INDEX idx_user_id ((CAST(event_data->>'$.user_id' AS UNSIGNED)))
);

SELECT * FROM events
WHERE CAST(event_data->>'$.user_id' AS UNSIGNED) = 12345;

实现原理

MySQL通过"隐藏生成列"实现函数索引:

CREATE INDEX idx_lower_email ON users((LOWER(email)));

实际执行:
1. 自动添加隐藏列: ALTER TABLE users ADD COLUMN `FUNC_1` VARCHAR(255) 
 GENERATED ALWAYS AS (LOWER(email)) VIRTUAL;

2. 在隐藏列上创建普通索引: CREATE INDEX idx_lower_email ON users(`FUNC_1`);

3. 查询重写: WHERE LOWER(email) = 'xxx' → WHERE `FUNC_1` = 'xxx'

查看隐藏列:
SHOW FULL COLUMNS FROM users;
-- 会看到Field列中有 FUNC_1 (hidden)

PostgreSQL表达式索引

创建语法

sql

-- PostgreSQL天然支持表达式索引

-- 示例1: 大小写不敏感
CREATE INDEX idx_lower_email ON users(LOWER(email));

-- 示例2: 复杂表达式
CREATE INDEX idx_name_length ON users(LENGTH(username));

SELECT * FROM users WHERE LENGTH(username) > 10;


-- 示例3: 数组操作
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  tags TEXT[]
);

CREATE INDEX idx_tag_count ON articles(CARDINALITY(tags));

SELECT * FROM articles WHERE CARDINALITY(tags) > 5;


-- 示例4: 时间提取
CREATE INDEX idx_order_year ON orders(EXTRACT(YEAR FROM order_date));

SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2024;


-- 示例5: 多列表达式
CREATE INDEX idx_full_name ON users((first_name || ' ' || last_name));

SELECT * FROM users 
WHERE first_name || ' ' || last_name = 'John Doe';

实际应用案例

案例1: 邮箱验证登录

sql

-- 用户表
CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  status ENUM('active', 'inactive', 'banned')
);

-- 问题: 用户输入邮箱可能大小写混用
-- Alice@Example.com vs alice@example.com

-- 解决方案: 函数索引
CREATE INDEX idx_lower_email ON users((LOWER(email)));

-- 登录查询
SELECT id, email, password_hash
FROM users
WHERE LOWER(email) = LOWER('Alice@Example.COM')
  AND status = 'active';

-- ✓ 使用函数索引,快速定位
-- ✓ 大小写不敏感,用户体验好

案例2: URL路径搜索

sql

-- Web访问日志
CREATE TABLE access_logs (
  id BIGINT PRIMARY KEY,
  request_url VARCHAR(500),
  user_agent VARCHAR(500),
  access_time DATETIME,
  response_code INT
);

-- 需求: 统计某个API端点的访问量
-- URL可能有查询参数: /api/users?page=1&size=20

-- 函数索引: 提取路径部分
CREATE INDEX idx_url_path ON access_logs(
  SUBSTRING_INDEX(request_url, '?', 1)
);

-- 查询
SELECT COUNT(*), AVG(response_time)
FROM access_logs
WHERE SUBSTRING_INDEX(request_url, '?', 1) = '/api/users'
  AND access_time >= DATE_SUB(NOW(), INTERVAL 1 DAY);

-- ✓ 忽略查询参数,准确统计API调用

案例3: 电话号码标准化

sql

-- 联系人表
CREATE TABLE contacts (
  id BIGINT PRIMARY KEY,
  name VARCHAR(100),
  phone VARCHAR(20)  -- 格式不统一: +86-138-0000-0000, 13800000000, ...
);

-- 函数: 标准化电话号码(去除所有非数字字符)
DELIMITER //
CREATE FUNCTION normalize_phone(raw_phone VARCHAR(20))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
  DECLARE result VARCHAR(20) DEFAULT '';
  DECLARE i INT DEFAULT 1;
  DECLARE ch CHAR(1);
  
  WHILE i <= LENGTH(raw_phone) DO
    SET ch = SUBSTRING(raw_phone, i, 1);
    IF ch BETWEEN '0' AND '9' THEN
    SET result = CONCAT(result, ch);
    END IF;
    SET i = i + 1;
  END WHILE;
  
  RETURN result;
END//
DELIMITER ;

-- 创建函数索引
CREATE INDEX idx_normalized_phone ON contacts(normalize_phone(phone));

-- 查询: 无论用户输入什么格式,都能找到
SELECT * FROM contacts
WHERE normalize_phone(phone) = normalize_phone('+86-138-0000-0000');
-- 匹配: 13800000000, +8613800000000, 138-0000-0000, ...

案例4: 日期范围优化

sql

-- 订单表
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  order_date DATETIME,
  amount DECIMAL(12,2),
  status VARCHAR(20)
);

-- 常见查询: 按年份统计
SELECT YEAR(order_date) as year, SUM(amount) as total
FROM orders
GROUP BY YEAR(order_date);

-- 优化: 创建年份索引
CREATE INDEX idx_order_year ON orders((YEAR(order_date)));

-- 查询某年订单
SELECT * FROM orders
WHERE YEAR(order_date) = 2024;
-- ✓ 使用函数索引

-- 更好的方案: 使用范围查询
SELECT * FROM orders
WHERE order_date >= '2024-01-01' 
  AND order_date < '2025-01-01';
-- ✓ 使用普通索引(如果order_date有索引)
-- 范围查询通常比函数索引更高效

性能考虑

优势

✓ 加速函数查询
  - 避免全表扫描
  - 查询性能从O(n)提升到O(log n)

✓ 灵活性强
  - 支持任意确定性函数
  - 可组合多个函数

✓ 透明使用
  - 查询无需修改
  - 优化器自动选择

劣势

✗ 维护成本高
  - INSERT/UPDATE/DELETE时需重新计算函数
  - 写入性能下降10-30%

✗ 存储空间
  - 需要额外存储函数结果
  - 可能接近原表大小

✗ 函数限制
  - 必须是确定性函数(DETERMINISTIC)
  - 不能包含随机数、当前时间等

✗ 优化器限制
  - 不是所有函数都能被识别
  - 可能需要Hint强制使用

性能测试

sql

-- 测试环境: 100万行users表

-- 测试1: 无函数索引
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
-- 全表扫描: 800ms

-- 测试2: 创建函数索引
CREATE INDEX idx_lower_email ON users((LOWER(email)));

-- 测试3: 有函数索引
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
-- 函数索引: 0.5ms
-- 性能提升: 1600倍!

-- 测试4: 写入性能影响
INSERT INTO users (email, username) VALUES ('new@test.com', 'newuser');
-- 无函数索引: 0.1ms
-- 有函数索引: 0.13ms
-- 性能下降: 30%

最佳实践

1. 选择合适的场景

sql

-- ✓ 适合:
-- 高频查询,低频更新
CREATE INDEX idx_lower_email ON users((LOWER(email)));

-- 大小写不敏感搜索
-- 数据标准化
-- 计算列过滤


-- ✗ 不适合:
-- 频繁更新的列
CREATE INDEX idx_updated_at_func ON orders((DATE_FORMAT(updated_at, '%Y-%m')));
-- 每次UPDATE都要重建索引!

-- 随机函数
CREATE INDEX idx_rand ON users((RAND()));
-- ✗ 非确定性函数,不允许!

2. 优先考虑替代方案

sql

-- 方案A: 函数索引
CREATE INDEX idx_lower_email ON users((LOWER(email)));
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';


-- 方案B: 生成列(推荐!)
ALTER TABLE users 
ADD COLUMN email_lower VARCHAR(255) 
GENERATED ALWAYS AS (LOWER(email)) STORED,
ADD INDEX idx_email_lower (email_lower);

SELECT * FROM users WHERE email_lower = 'test@example.com';
-- ✓ 更清晰,更易维护


-- 方案C: 应用层处理
-- 在代码中统一转小写再查询
String normalizedEmail = email.toLowerCase();
SELECT * FROM users WHERE email = ?;
-- ✓ 最简单,无需特殊索引

3. 监控和维护

sql

-- Oracle: 查看函数索引
SELECT 
  index_name,
  table_name,
  funcidx_status,
  expression
FROM user_indexes
WHERE index_type = 'FUNCTION-BASED NORMAL';

-- MySQL: 查看隐藏列
SHOW FULL COLUMNS FROM users;

-- PostgreSQL: 查看表达式索引
SELECT 
  indexname,
  indexdef
FROM pg_indexes
WHERE indexdef LIKE '%LOWER%';

-- 重建函数索引
ALTER INDEX idx_lower_email REBUILD;

局限性

函数限制

sql

-- ✗ 非确定性函数(不允许)
CREATE INDEX idx_now ON logs((NOW()));
CREATE INDEX idx_rand ON table((RAND()));

-- ✓ 确定性函数(允许)
CREATE INDEX idx_lower ON users((LOWER(email)));
CREATE INDEX idx_length ON users((LENGTH(username)));

-- 判断标准:
-- 相同输入 → 始终相同输出 = 确定性函数

数据库支持

Oracle:
✓ 最完善的支持
✓ 任意确定性函数
✓ 内置函数 + 自定义函数

MySQL:
✓ 8.0.13+ 支持
✓ 通过隐藏生成列实现
✓ 功能较Oracle弱

PostgreSQL:
✓ 表达式索引
✓ 功能强大
✓ 支持复杂表达式

SQL Server:
✗ 不直接支持
→ 使用计算列 + 索引替代

函数索引 - Function-Based Index详解

定义

核心特征

与普通索引对比

为什么需要函数索引?

问题1: 函数导致索引失效

问题2: 计算列重复计算

Oracle函数索引实现

创建语法

内部结构

MySQL函数索引实现

MySQL 8.0.13+支持

实现原理

PostgreSQL表达式索引

创建语法

实际应用案例

案例1: 邮箱验证登录

案例2: URL路径搜索

案例3: 电话号码标准化

案例4: 日期范围优化

性能考虑

优势

劣势

性能测试

最佳实践

1. 选择合适的场景

2. 优先考虑替代方案

3. 监控和维护

局限性

函数限制

数据库支持

参考资料

官方文档

相关术语

函数索引 - Function-Based Index详解 ​

定义 ​

核心特征 ​

与普通索引对比 ​

为什么需要函数索引? ​

问题1: 函数导致索引失效 ​

问题2: 计算列重复计算 ​

Oracle函数索引实现 ​

创建语法 ​

内部结构 ​

MySQL函数索引实现 ​

MySQL 8.0.13+支持 ​

实现原理 ​

PostgreSQL表达式索引 ​

创建语法 ​

实际应用案例 ​

案例1: 邮箱验证登录 ​

案例2: URL路径搜索 ​

案例3: 电话号码标准化 ​

案例4: 日期范围优化 ​

性能考虑 ​

优势 ​

劣势 ​

性能测试 ​

最佳实践 ​

1. 选择合适的场景 ​

2. 优先考虑替代方案 ​

3. 监控和维护 ​

局限性 ​

函数限制 ​

数据库支持 ​

参考资料 ​

官方文档 ​

相关术语 ​

函数索引 - Function-Based Index详解

定义

核心特征

与普通索引对比

为什么需要函数索引?

问题1: 函数导致索引失效

问题2: 计算列重复计算

Oracle函数索引实现

创建语法

内部结构

MySQL函数索引实现

MySQL 8.0.13+支持

实现原理

PostgreSQL表达式索引

创建语法

实际应用案例

案例1: 邮箱验证登录

案例2: URL路径搜索

案例3: 电话号码标准化

案例4: 日期范围优化

性能考虑

优势

劣势

性能测试

最佳实践

1. 选择合适的场景

2. 优先考虑替代方案

3. 监控和维护

局限性

函数限制

数据库支持

参考资料

官方文档

相关术语