函数索引 - 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",可直接查找12345678910111213141516为什么需要函数索引? 问题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()函数使索引失效123456789101112131415161718192021222324252627性能影响:
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 (函数索引)
→ 恢复索引性能!12345678910111213141516问题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开销大1234567891011121314151617Oracle函数索引实现 创建语法 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';12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849内部结构 c/* 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);
}123456789101112131415161718192021222324252627282930313233343536373839404142434445464748MySQL函数索引实现 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;123456789101112131415161718192021222324252627282930313233343536373839404142434445464748实现原理 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)123456789101112131415PostgreSQL表达式索引 创建语法 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';123456789101112131415161718192021222324252627282930313233实际应用案例 案例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';
-- ✓ 使用函数索引,快速定位
-- ✓ 大小写不敏感,用户体验好12345678910111213141516171819202122案例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调用123456789101112131415161718192021222324案例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, ...123456789101112131415161718192021222324252627282930313233343536案例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有索引)
-- 范围查询通常比函数索引更高效123456789101112131415161718192021222324252627性能考虑 优势 ✓ 加速函数查询
- 避免全表扫描
- 查询性能从O(n)提升到O(log n)
✓ 灵活性强
- 支持任意确定性函数
- 可组合多个函数
✓ 透明使用
- 查询无需修改
- 优化器自动选择1234567891011劣势 ✗ 维护成本高
- INSERT/UPDATE/DELETE时需重新计算函数
- 写入性能下降10-30%
✗ 存储空间
- 需要额外存储函数结果
- 可能接近原表大小
✗ 函数限制
- 必须是确定性函数(DETERMINISTIC)
- 不能包含随机数、当前时间等
✗ 优化器限制
- 不是所有函数都能被识别
- 可能需要Hint强制使用123456789101112131415性能测试 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%12345678910111213141516171819最佳实践 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()));
-- ✗ 非确定性函数,不允许!12345678910111213141516172. 优先考虑替代方案 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 = ?;
-- ✓ 最简单,无需特殊索引12345678910111213141516171819203. 监控和维护 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;123456789101112131415161718192021局限性 函数限制 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)));
-- 判断标准:
-- 相同输入 → 始终相同输出 = 确定性函数12345678910数据库支持 Oracle:
✓ 最完善的支持
✓ 任意确定性函数
✓ 内置函数 + 自定义函数
MySQL:
✓ 8.0.13+ 支持
✓ 通过隐藏生成列实现
✓ 功能较Oracle弱
PostgreSQL:
✓ 表达式索引
✓ 功能强大
✓ 支持复杂表达式
SQL Server:
✗ 不直接支持
→ 使用计算列 + 索引替代123456789101112131415161718参考资料 官方文档 Oracle Function-Based IndexesMySQL Generated ColumnsPostgreSQL Expression Indexes相关术语 覆盖索引哈希索引位图索引版本历史:
2026-04-12: 初始版本,全面讲解函数索引原理与实践