如何在MySQL亿级流水表中通过联合索引优化时间范围统计？-mysql教程-PHP中文网

联合索引中时间字段不必强制包含且放右端，其位置应依查询模式而定：高选择性等值字段（如biz_channel、user_id）须置最左，时间范围字段（oper_time）宜靠右以保障最左前缀连续性；若仅按时间查询，应单独建索引。

如何在mysql亿级流水表中通过联合索引优化时间范围统计？

联合索引必须包含时间字段且放右端？

不是。时间字段在联合索引中的位置取决于查询条件的过滤强度和组合方式。如果 WHERE 中同时有高选择性字段（如 user_id、BIZ_CHANNEL）和时间范围（OPER_TIME），应把过滤性强的字段放左，时间字段放右——例如 (BIZ_CHANNEL, OPER_TIME)，而非反过来。

原因在于：MySQL 的最左前缀匹配要求查询必须从索引最左列开始连续使用。若写成 (OPER_TIME, BIZ_CHANNEL)，而查询只带 BIZ_CHANNEL IN (...) 但没带时间条件，该索引就完全失效；反之，(BIZ_CHANNEL, OPER_TIME) 支持单用 BIZ_CHANNEL，也支持两者共用，兼容性更好。

高频等值查询字段（如渠道码、用户ID）优先靠左
时间范围（BETWEEN / >=）只能作为联合索引的“后缀”，不能中断最左连续性
若查询中时间是唯一过滤条件（无其他等值字段），那单独建 OPER_TIME 索引更合理，联合索引反而浪费

覆盖索引能绕过回表，但哪些字段必须进索引？

对统计类查询（如 COUNT(DISTINCT ...), SUM(...), GROUP BY），覆盖索引的目标是让 MySQL 仅扫描索引树就能返回全部所需数据，不查聚簇索引（即主键组织的数据页）。

以交易流水表 BANK_OPER 的典型统计为例：

SELECT BIZ_CHANNEL,
       COUNT(DISTINCT SERIAL_ID),
       COUNT(DISTINCT PAY_ACCT),
       SUM(OPER_AMOUNT)
FROM BANK_OPER
WHERE OPER_TIME BETWEEN '2024-01-01' AND '2024-12-31'
  AND BIZ_CHANNEL IN ('C01','C02')
GROUP BY BIZ_CHANNEL;

要让它走覆盖索引，联合索引至少得包含：

BIZ_CHANNEL（WHERE 等值 + GROUP BY）
OPER_TIME（WHERE 范围）
SERIAL_ID（COUNT(DISTINCT) 需访问该列值）
PAY_ACCT（同上）
OPER_AMOUNT（SUM() 需求）

即：CREATE INDEX idx_covering ON BANK_OPER (BIZ_CHANNEL, OPER_TIME, SERIAL_ID, PAY_ACCT, OPER_AMOUNT);。注意顺序不可乱：等值字段在前，范围字段居中，被聚合/去重字段在后。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

为什么加了联合索引，EXPLAIN 还显示 Using temporary？

即使走了索引，EXPLAIN 中出现 Using temporary 说明 MySQL 仍需创建临时表来完成 GROUP BY 或 DISTINCT —— 这通常是因为索引未覆盖 GROUP BY 字段，或字段顺序不满足排序需求。

关键点：

GROUP BY 字段必须是联合索引的最左前缀，且顺序一致。例如 GROUP BY BIZ_CHANNEL 可用 (BIZ_CHANNEL, OPER_TIME)，但 GROUP BY OPER_TIME 就不行
如果 ORDER BY BIZ_CHANNEL DESC 存在，索引需声明为 BIZ_CHANNEL DESC（MySQL 8.0+ 支持混合方向），否则仍会触发 Using filesort
COUNT(DISTINCT x) 即使 x 在索引里，只要 x 不是索引最左列且无前置等值条件，优化器也可能放弃索引去重，改走临时表