MySQL 8.0 生成列与函数索引：让计算逻辑下沉到存储层

国医中兴

157人浏览 · 2026-06-12 10:38:10

国医中兴 · 2026-06-12 10:38:10 发布

MySQL 8.0 生成列与函数索引：让计算逻辑下沉到存储层

一、查询优化的隐藏瓶颈：计算列的索引盲区

在 MySQL 的实际业务中，经常遇到这样的场景：需要按某个计算表达式进行查询和排序。例如，用户表中有 first_name 和 last_name 两个字段，但查询条件是全名匹配 WHERE CONCAT(first_name, ' ', last_name) = '张三'。再比如，订单表中有 amount 和 tax_rate，需要按含税金额排序 ORDER BY amount * (1 + tax_rate)。

这类查询的共同特点是：WHERE 或 ORDER BY 子句中使用了表达式，而 MySQL 无法在表达式上建立普通索引。查询优化器只能选择全表扫描，即使表中有数百万行数据。在存储部的实际业务中，一张 5000 万行的订单表，按含税金额排序的查询耗时从 0.5ms（有索引）退化到 12s（全表扫描 + filesort）。

MySQL 5.7 引入的生成列（Generated Column）和 MySQL 8.0 增强的函数索引（Functional Index），正是解决这个问题的利器。它们将计算逻辑下沉到存储层，在写入时预计算表达式的值并存储，使得查询时可以直接使用索引。

二、生成列与函数索引的底层机制

2.1 生成列的两种类型

生成列分为 VIRTUAL 和 STORED 两种：

VIRTUAL（虚拟生成列）：列值不存储在磁盘上，查询时动态计算。不占用存储空间，但每次读取都需要重新计算。可以在虚拟列上创建二级索引，索引中存储计算后的值。
STORED（存储生成列）：列值在 INSERT/UPDATE 时计算并持久化到磁盘。占用存储空间，但读取时无需计算。

flowchart TD
    A[INSERT/UPDATE 语句] --> B{生成列类型}
    B -->|VIRTUAL| C[仅更新二级索引中的计算值]
    B -->|STORED| D[计算表达式值并写入数据行]
    D --> E[同时更新二级索引]
    C --> F[查询时通过索引直接定位]
    E --> F
    F --> G[避免全表扫描和表达式计算]

    subgraph 存储结构对比
        H[VIRTUAL: 聚簇索引中不存储] 
        I[STORED: 聚簇索引中存储]
    end

2.2 函数索引的底层实现

MySQL 8.0.13 引入的函数索引，本质上是在虚拟生成列上创建索引的语法糖。以下两种写法等价：

-- 方式 1：显式创建虚拟生成列 + 索引
ALTER TABLE orders ADD COLUMN total_amount DECIMAL(10,2)
    GENERATED ALWAYS AS (amount * (1 + tax_rate)) VIRTUAL;
CREATE INDEX idx_total ON orders(total_amount);

-- 方式 2：函数索引（MySQL 8.0.13+）
CREATE INDEX idx_total ON orders((amount * (1 + tax_rate)));

函数索引在 InnoDB 内部的实现方式是：自动创建一个隐藏的虚拟生成列，然后在该列上创建二级索引。二级索引的叶子节点存储的是计算后的值和主键，查询时通过索引定位到主键，再回表获取完整行。

2.3 优化器如何选择生成列索引

MySQL 8.0 的优化器增强了对生成列索引的匹配能力。当 WHERE 子句中的表达式与生成列的定义完全一致时，优化器会自动匹配到生成列上的索引。关键在于"完全一致"——表达式的文本形式必须匹配，包括空格和括号。

三、生产级实战：从建表到查询优化

3.1 生成列的建表与索引设计

-- 订单表：含税金额和全名是高频查询条件
CREATE TABLE orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL,
    amount DECIMAL(10,2) NOT NULL COMMENT '不含税金额',
    tax_rate DECIMAL(4,4) NOT NULL COMMENT '税率',
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,

    -- 存储生成列：含税金额（高频排序和范围查询，选择 STORED）
    total_amount DECIMAL(10,2)
        GENERATED ALWAYS AS (amount * (1 + tax_rate)) STORED,

    -- 虚拟生成列：全名（高频等值查询，选择 VIRTUAL 节省空间）
    full_name VARCHAR(101)
        GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_order_no (order_no),
    KEY idx_total_amount (total_amount),
    KEY idx_full_name (full_name),
    KEY idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3.2 查询优化对比

-- 优化前：无法使用索引，全表扫描 + filesort
EXPLAIN SELECT * FROM orders
ORDER BY amount * (1 + tax_rate) DESC
LIMIT 20;
-- type: ALL, Extra: Using filesort

-- 优化后：直接使用生成列索引
EXPLAIN SELECT * FROM orders
ORDER BY total_amount DESC
LIMIT 20;
-- type: index, key: idx_total_amount, Extra: Backward index scan

-- 等值查询优化
EXPLAIN SELECT * FROM orders
WHERE full_name = '张 三';
-- type: ref, key: idx_full_name

3.3 JSON 字段的生成列索引

MySQL 8.0 中 JSON 字段的使用越来越广泛，但 JSON 内部字段无法直接建索引。生成列是解决这个问题的标准方案：

CREATE TABLE user_profiles (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    profile JSON NOT NULL COMMENT '用户画像 JSON',

    -- 从 JSON 中提取字段建立生成列索引
    city VARCHAR(50)
        GENERATED ALWAYS AS (JSON_UNQUOTE(profile->'$.address.city')) STORED,
    age INT
        GENERATED ALWAYS AS (CAST(profile->'$.age' AS UNSIGNED)) STORED,
    vip_level INT
        GENERATED ALWAYS AS (CAST(profile->'$.vip_level' AS UNSIGNED)) STORED,

    PRIMARY KEY (id),
    KEY idx_city (city),
    KEY idx_age (age),
    KEY idx_vip_level (vip_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 按 JSON 内部字段查询，走生成列索引
SELECT * FROM user_profiles WHERE city = '北京' AND age > 25;

3.4 生成列的维护与数据一致性

-- 生成列的值由数据库引擎自动维护，不能手动 INSERT 或 UPDATE
INSERT INTO orders (order_no, amount, tax_rate, first_name, last_name)
VALUES ('ORD-20260612-001', 1000.00, 0.1300, '张', '三');
-- total_amount 自动计算为 1130.00，full_name 自动计算为 '张 三'

-- 尝试手动指定生成列的值会报错
INSERT INTO orders (order_no, amount, tax_rate, first_name, last_name, total_amount)
VALUES ('ORD-20260612-002', 1000.00, 0.1300, '李', '四', 999.00);
-- ERROR 3105 (HY000): The value specified for generated column 'total_amount' in table 'orders' is not allowed.