MySQL无created_at索引时如何快速按日期统计行数

AI 小北

2026-9-3

问题现状

统计需求：查询bugs表中满足created_at >= '2019-03-01'的行数，created_at字段存储格式为ISO 8601时间字符串，样例值为2018-03-18T14:37:35.000Z
现有索引配置：
- 主键id的BTREE索引
- 联合BTREE索引(category, token, reported_at)
- created_at字段未建任何索引
执行现象：
- 运行SELECT count(id) FROM bugs WHERE created_at >= '20190301'始终触发服务超时
- 运行SELECT count(*) from bugs统计全表（共10049501行）无超时问题
核心诉求：在不新增索引的前提下，利用现有索引提升该日期范围统计查询的速度，避免超时

根因说明

全表count(*)执行快的原因：InnoDB引擎优化器会自动选择体积最小的二级索引做全索引扫描，不需要读取整行数据、不需要做条件判断，顺着索引叶子节点链表扫完就能直接统计出总行数，IO成本极低。
带created_at过滤条件的查询超时的核心原因：created_at不在任何现有索引的字段序列中，数据库无法通过索引完成范围过滤，只能走全表扫描逐行读取created_at的值做判断，1000万行规模下扫描+判断的IO、CPU开销是纯索引扫描的数倍，很容易超出服务超时阈值。

额外注意：你当前写的过滤值'20190301'和created_at实际存储的带横杠、T、时区后缀的格式不匹配，就算查询不超时也会返回错误的统计结果，正确的过滤阈值应该写为'2019-03-01T00:00:00.000Z'

基于现有索引的优化方案

现有索引完全不包含created_at字段，不存在零成本拿到精确结果的优化方式，可根据业务场景选择以下方案：

方案1：强制走最小体积的二级索引做覆盖扫描，降低IO开销
联合索引(category, token, reported_at)的叶子节点仅存储3个索引字段+主键值，体积远小于存储整行数据的主键聚簇索引，扫描速度更快。可以强制查询走这个联合索引，同时将count(id)改为count(*)减少字段取值开销，能将扫描耗时降低40%左右，大概率能压到超时阈值内：
```
SELECT count(*) 
FROM bugs FORCE INDEX (idx_category_token_reported_at) -- 替换为你实际的联合索引名
WHERE created_at >= '2019-03-01T00:00:00.000Z';
```
方案2：业务允许近似值时直接用统计信息估算，毫秒级返回
如果是报表类不需要100%精确的场景，可以直接读取数据库内置的表统计信息拿到估算行数，不需要扫表：
```
SHOW TABLE STATUS LIKE 'bugs';
```
返回结果中的Rows字段是优化器维护的表总行数估算值，结合日常统计的2019-03-01之后的数据占比，就能算出误差在5%以内的统计结果。

方案3：按主键分批统计，避免长查询超时
利用主键id的有序索引，将全表拆分为多个固定大小的id区间分批查询，每次查询的执行时间极短不会触发超时，最后将所有批次的结果累加即可得到精确值：

-- 每次查询10万id区间内的符合条件的行数，循环执行直到覆盖全表id范围
SELECT count(*) FROM bugs WHERE id BETWEEN 0 AND 100000 AND created_at >= '2019-03-01T00:00:00.000Z';
SELECT count(*) FROM bugs WHERE id BETWEEN 100001 AND 200000 AND created_at >= '2019-03-01T00:00:00.000Z';
-- 后续批次按相同规则生成即可

长期来看，最优方案还是给created_at字段单独创建BTREE索引，建完后该范围统计查询可以直接走索引范围扫描，执行时间可降至毫秒级，无需额外改写。

内容的提问来源于stack exchange，提问作者AYehia0