MySQL 8.0 功能性索引（Functional Index）与生成列可验证实践

MySQL 8.0.13+ 引入功能性索引（表达式索引），同时长期支持基于生成列的索引。本文用可复现实验比较两者的写法与行为，明确适用场景与注意事项。

## 版本与前提

• 功能性索引需 MySQL 8.0.13+。
• `EXPLAIN ANALYZE` 需 8.0.18+；`FORMAT=TREE` 需 8.0.16+。

## 示例表与数据

CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL
) ENGINE=InnoDB;

INSERT INTO users VALUES
  (1, 'Alice', '[email protected]', '2024-01-01 10:00:00'),
  (2, 'Bob',   '[email protected]',  '2024-01-01 11:00:00');

## 功能性索引（表达式索引）

-- 直接对表达式创建索引（8.0.13+）
CREATE INDEX idx_lower_email ON users ((lower(email)));

EXPLAIN ANALYZE
SELECT id FROM users WHERE lower(email) = '[email protected]';

验证：执行计划应显示使用 `idx_lower_email` 做 `ref`/`range`，避免全表扫描。

## 生成列 + 索引

ALTER TABLE users
  ADD COLUMN email_lc VARCHAR(255) GENERATED ALWAYS AS (lower(email)) STORED,
  ADD INDEX idx_email_lc (email_lc);

EXPLAIN ANALYZE
SELECT id FROM users WHERE email_lc = '[email protected]';

对比：生成列索引在更早版本可用，并且可被其他查询复用；功能性索引更简洁，但表达式变更需重建索引。

## 写法与注意事项

• 表达式需确定性：非确定性函数不可用于功能性索引。
• 长度与字符集：对文本表达式索引时关注前缀长度与字符集排序规则（collation）。
• 迁移策略：从旧版本迁移时优先用生成列索引，以兼容性优先；升级到 8.0.13+ 可改为表达式索引简化。

## 观测与验证

EXPLAIN FORMAT=TREE SELECT id FROM users WHERE lower(email) = '[email protected]';
SHOW INDEX FROM users; -- 确认索引选择与可见性

## 结语

MySQL 8.0 的功能性索引与生成列索引都能解决“表达式可索引化”的问题。结合版本特性与维护成本选择其一，并用 EXPLAIN/EXPLAIN ANALYZE 实证执行路径，确保优化效果可验证与可复现。