从全表扫描到按天毫秒出数：MySQL 8.0 函数索引替代虚拟生成列实测

问题背景：按天统计为什么容易扫全表

业务中最常见的报表需求之一，是把 created_at 这类 DATETIME 或 TIMESTAMP 字段按天聚合：

SELECT DATE(created_at) AS dt, COUNT(*) AS cnt
FROM orders
WHERE created_at >= '2026-01-01'
  AND created_at <  '2026-02-01'
GROUP BY DATE(created_at)
ORDER BY dt;

很多团队已经给 created_at 建了普通索引，但慢查询仍然存在。原因是这条 SQL 同时包含范围过滤、函数计算和分组：数据量较大时，优化器可能认为回表成本不高而选择索引范围扫描，也可能因为选择度不足直接全表扫描；即使走了时间索引，仍要对大量记录执行 DATE() 计算并临时聚合。

过去 MySQL 5.7 常见做法是增加一个虚拟生成列，再在生成列上建索引。MySQL 8.0.13 开始支持函数索引，也就是函数键部分，即 functional key parts，可以直接对表达式建索引，不必把表达式暴露成业务字段。

本文用一个可重复执行的订单表示例，对比三种落地方式：普通时间索引、虚拟生成列索引、MySQL 8.0 函数索引，并重点说明按天统计场景下如何稳定命中索引。

三种方案对比：普通索引、虚拟生成列与函数索引

先给结论：如果数据库已经是 MySQL 8.0.13 或更高版本，且目标是加速固定表达式，如 DATE(created_at)，优先考虑函数索引；如果表达式需要被业务查询、ORM、视图反复引用，或者数据库版本仍停留在 MySQL 5.7，则使用虚拟生成列更合适。

三种方案的差异主要体现在四个方面：

方案	表结构侵入	索引表达式	适用版本	主要问题
普通索引 (created_at)	低	不保存 DATE() 结果	所有版本	聚合仍需计算，低选择度下可能全表扫描
虚拟生成列 + 索引	中	created_date	MySQL 5.7+	多出字段，ORM 和权限模型需要适配
函数索引	低	(DATE(created_at))	MySQL 8.0.13+	查询表达式必须与索引表达式匹配

需要强调的是，函数索引不是“给函数加了索引就一切加速”。本质上它仍然是一棵 B+Tree，键值是表达式计算结果。如果查询返回表中大部分数据，或者过滤条件不能有效缩小范围，优化器照样可能放弃索引。

数据准备：构造可复现的按天统计表

下面的示例使用 MySQL 8.0.13 及以上版本。先建表并写入 10 万级测试数据，时间跨度约为 180 天：

DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_no VARCHAR(32) NOT NULL,
  status TINYINT NOT NULL DEFAULT 1,
  amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 递归 CTE 生成约 18 万行测试数据，MySQL 8.0 可直接执行
INSERT INTO orders (order_no, status, amount, created_at)
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 180000
)
SELECT
  CONCAT('O', LPAD(n, 9, '0')) AS order_no,
  CASE WHEN n % 20 = 0 THEN 0 ELSE 1 END AS status,
  ROUND(50 + RAND() * 450, 2) AS amount,
  TIMESTAMPADD(
    SECOND,
    FLOOR(RAND(n) * 180 * 24 * 3600),
    '2026-01-01 00:00:00'
  ) AS created_at
FROM seq;

先执行一次典型报表 SQL，并查看执行计划：

EXPLAIN ANALYZE
SELECT DATE(created_at) AS dt, COUNT(*) AS cnt, SUM(amount) AS total_amount
FROM orders
WHERE created_at >= '2026-03-01'
  AND created_at <  '2026-03-08'
  AND status = 1
GROUP BY DATE(created_at)
ORDER BY dt;

在测试库中，普通 idx_created_at 是否被使用取决于一周数据占比和统计信息。你可能看到 type=range，也可能看到 type=ALL。即便使用范围扫描，Extra 中也常出现 Using where; Using temporary。

方案一：虚拟生成列如何支持按天聚合

MySQL 5.7 可以新增一个 VIRTUAL 生成列。虚拟列本身不占普通数据页空间，但在二级索引中会实际存储键值：

ALTER TABLE orders
  ADD COLUMN created_date DATE
  GENERATED ALWAYS AS (DATE(created_at)) VIRTUAL,
  ADD KEY idx_created_date (created_date);

随后把查询中的 DATE(created_at) 改成生成列：

EXPLAIN
SELECT created_date AS dt, COUNT(*) AS cnt, SUM(amount) AS total_amount
FROM orders
WHERE created_date >= '2026-03-01'
  AND created_date <  '2026-03-08'
  AND status = 1
GROUP BY created_date
ORDER BY created_date;

这个方案的优点是直观，索引键就是一个 DATE 字段；缺点也明显：表结构多了一列，使用 SELECT * 会返回该字段，某些数据同步工具、ORM 映射、归档脚本可能需要改造。此外，生成列表达式修改并不轻松，通常需要删除列和索引后重新添加。

方案二：MySQL 8.0 函数索引直接落地

函数索引不需要新增业务字段，可以直接对表达式建索引：

ALTER TABLE orders
  ADD KEY idx_func_date ((DATE(created_at)));

注意语法中有两层括号：外层属于索引定义，内层表示表达式。少写一层会得到语法错误。

查询时仍然写原始表达式，优化器才能识别并匹配函数索引：

EXPLAIN
SELECT DATE(created_at) AS dt, COUNT(*) AS cnt, SUM(amount) AS total_amount
FROM orders
WHERE DATE(created_at) >= '2026-03-01'
  AND DATE(created_at) <  '2026-03-08'
  AND status = 1
GROUP BY DATE(created_at)
ORDER BY dt;

重点观察执行计划：

SHOW INDEX FROM orders WHERE Key_name = 'idx_func_date';

EXPLAIN FORMAT=TREE
SELECT DATE(created_at) AS dt, COUNT(*), SUM(amount)
FROM orders
WHERE DATE(created_at) >= '2026-03-01'
  AND DATE(created_at) <  '2026-03-08'
  AND status = 1
GROUP BY DATE(created_at);

命中时，key 应显示为 idx_func_date，访问类型通常是 range。如果查询一周数据，函数索引可以把扫描范围限制在 7 个日期键附近，而不是从 18 万行开始过滤。

但真实报表一般不止一个日期条件。例如还要过滤 status，只对 amount 聚合，可以考虑联合函数索引：

ALTER TABLE orders
  ADD KEY idx_func_date_status_amount (
    (DATE(created_at)),
    status,
    amount
  );

这个索引的顺序含义是：先按日期表达式定位，再按状态过滤，最后利用索引中的金额减少回表。它更适合下面这种窄条件、聚合列固定的查询：

EXPLAIN ANALYZE
SELECT DATE(created_at) AS dt, COUNT(*) AS cnt, SUM(amount) AS total_amount
FROM orders
WHERE DATE(created_at) >= '2026-03-01'
  AND DATE(created_at) <  '2026-03-08'
  AND status = 1
GROUP BY DATE(created_at)
ORDER BY dt;

不要盲目把所有过滤字段都塞进联合索引。索引越宽，写入放大越明显，而且 SUM(amount) 是否能完全覆盖，还取决于查询列是否都包含在索引中。对于 SELECT * 或查询列很多的场景，覆盖索引通常不划算。

函数索引与普通时间索引如何取舍

看到函数索引有效后，有人会直接删除 idx_created_at，这是危险的。DATE(created_at) 索引适合按天等值或日期范围查询，但原始时间点查询仍然依赖 created_at：

-- 更适合 idx_created_at
SELECT id, amount
FROM orders
WHERE created_at >= '2026-03-01 08:00:00'
  AND created_at <  '2026-03-01 09:00:00';

-- 更适合函数索引
SELECT DATE(created_at), COUNT(*)
FROM orders
WHERE DATE(created_at) = '2026-03-01'
GROUP BY DATE(created_at);

如果业务既有小时级精确查询，又有按天报表，通常需要保留两个索引，再通过慢查询和写入压力评估是否合并。不要只看一次 EXPLAIN：应使用 EXPLAIN ANALYZE 观察实际行数、实际时间和估算偏差，并在数据分布变化后执行：

ANALYZE TABLE orders;

避坑总结：函数索引失效的常见原因

1. 查询表达式和索引表达式不一致

常见错误是索引建在 DATE(created_at) 上，查询却写成：

-- 可能无法匹配 DATE(created_at) 函数索引
WHERE created_at >= '2026-03-01'
  AND created_at <  '2026-03-08'

或者使用了 CAST()、CONVERT_TZ()、DATE_FORMAT() 等不同表达式。MySQL 不会总是自动证明两个表达式完全等价。修正方法是保持谓词和分组表达式与索引定义完全一致；需要时区转换时，就把同一个时区转换表达式放进函数索引和 SQL 中。

2. 在 UTC 时间字段上按本地时区统计

表字段如果存 UTC，报表却要求北京时间，直接写 DATE(created_at) 会把每天 00:00 到 08:00 的数据算到前一天。错误现象通常是“每日总数和业务后台差 8 小时边界的数据”。修正方式是统一表达式：

ALTER TABLE orders
  ADD KEY idx_func_bj_date (
    (DATE(CONVERT_TZ(created_at, '+00:00', '+08:00')))
  );

SELECT DATE(CONVERT_TZ(created_at, '+00:00', '+08:00')) AS bj_date,
       COUNT(*)
FROM orders
WHERE DATE(CONVERT_TZ(created_at, '+00:00', '+08:00')) = '2026-03-01'
GROUP BY bj_date;

同时要确认 MySQL 时区表已正确加载，否则 CONVERT_TZ() 可能返回 NULL。

3. 低选择度条件导致优化器主动放弃索引

如果查询一整年或全表数据，函数索引命中了也需要大量回表，优化器选择全表扫描并不一定是错。很多人看到 type=ALL 就强行加 FORCE INDEX，结果报表更慢。正确做法是缩小时间范围、增加高选择度条件、设计合适的联合索引，或对历史报表使用汇总表。

4. 只建索引不处理排序和分页

按天统计通常还要 ORDER BY dt DESC、LIMIT。如果表达式索引顺序是升序，而查询只取最近几天，可以显式建立降序索引：

ALTER TABLE orders
  ADD KEY idx_func_date_desc ((DATE(created_at)) DESC);

否则可能出现 Using filesort。当然，只有几天分组结果时，文件排序成本很小，不应为了执行计划“好看”而过度建索引。

5. 忽略函数索引对写入和 DDL 的成本

函数索引在写入时需要计算表达式并维护额外 B+Tree，批量导入、更新时间字段时成本会上升。大表加索引应使用在线 DDL 评估锁与复制延迟，MySQL 8.0 可优先考虑 ALGORITHM=INPLACE, LOCK=NONE，但仍要在低峰期执行：

ALTER TABLE orders
  ADD KEY idx_func_date ((DATE(created_at))),
  ALGORITHM=INPLACE,
  LOCK=NONE;

最终建议

按天统计场景下，MySQL 8.0 函数索引是虚拟生成列的轻量替代方案：它不增加业务字段，表达式清晰，适合固定报表口径。落地时不要只验证“索引是否存在”，而要验证“SQL 表达式是否完全匹配、条件范围是否足够窄、联合索引顺序是否符合过滤和聚合路径”。

我的实践决策是：MySQL 5.7 继续使用虚拟生成列；MySQL 8.0 新系统优先函数索引；高频宽口径报表再引入日汇总表。这样既能让单日、单周统计从大范围扫描变成毫秒级范围定位，也能避免为了一条慢查询无节制地堆叠索引。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。