本文由
AI辅助撰写，可能存在不准确之处，请读者注意甄别！
在日志系统、报表统计等场景中，我们经常需要对 table_authlog（登录日志）或 table_scanrecords（扫码记录）这类大表进行按日维度的统计。
最常见的直觉式 SQL 写法往往是使用 TO_DAYS() 或 DATE() 函数来匹配时间。在数据量较小时，这种写法不会有感知。但当数据量突破百万甚至千万级时，这种写法会导致严重的 CPU 飙升和查询延迟，甚至拖垮整个数据库实例。
本文以实际生产环境中的两个日志表为例，剖析导致性能瓶颈的底层原理，并给出从 SQL 层面到架构层面的优化路径。
一、 性能瓶颈的底层原理
1. 典型的问题 SQL
在代码审查中，我们经常看到如下统计“今日数据”的写法：
SELECT COUNT(*)
FROM table_authlog
WHERE TO_DAYS(auth_time) = TO_DAYS(NOW());或者：
SELECT COUNT(*)
FROM table_scanrecords
WHERE DATE(scan_time) = CURRENT_DATE();2. 为什么这是“性能杀手”？
即使你在 auth_time 或 scan_time 字段上建立了索引，上述查询依然会触发 全表扫描 (Full Table Scan)。
原理分析：
MySQL 的 B+ 树索引是按照字段的原始值进行排序存储的。
当你对索引列使用函数（如 TO_DAYS(auth_time)）时，相当于要求数据库引擎执行以下步骤：
- 取出索引树或数据页中的每一行记录。
- 对每一行的
auth_time值计算TO_DAYS结果。 - 将计算结果与
TO_DAYS(NOW())进行比对。
由于索引中存储的是时间，而不是“时间经过函数转换后的值”，引擎无法利用 B+ 树的有序性快速定位数据，必须遍历所有行。这种现象在数据库领域称为 索引失效，该查询谓词被称为 Non-SARGable (Non-Search ARGument ABLE)。
对于千万级数据表，全表扫描意味着巨大的磁盘 I/O 和 CPU 计算开销。
二、 优化核心：改写为范围查询
优化的核心原则非常简单：“转换条件，而不是转换数据”。
我们要将“计算每一行数据是否符合条件”转换为“计算出一个时间范围，让数据库直接去索引中截取”。
1. 优化方案：左闭右开区间
将时间匹配改为标准的范围查询（Range Query）。
推荐写法：
WHERE time_column >= '今日起始时间' AND time_column < '明日起始时间'这种写法有以下优势：
- 命中索引：符合最左前缀原则，MySQL 可以直接在 B+ 树上定位到起始位置，然后向后扫描直到不满足条件为止，复杂度从 $O(N)$ 降低为 $O(\log N + M)$（M为符合条件的行数）。
- 避免边界问题：使用
>=和<的组合（即 $[start, end)$ 区间），可以完美避开BETWEEN可能导致的毫秒级精度丢失或跨天边界重复计算问题。
2. 索引准备
确保时间字段已有索引，这是优化的前提：
ALTER TABLE table_authlog ADD INDEX idx_auth_time(auth_time);
ALTER TABLE table_scanrecords ADD INDEX idx_scan_time(scan_time);三、 实战代码：优化后的 SQL 逻辑
针对业务中涉及的 table_authlog 和 table_scanrecords 两张表，具体的生产级 SQL 如下。
1. table_authlog (登录日志)
统计今日记录：
利用 CURDATE() 获取当天零点，利用 INTERVAL 计算明天零点。
SELECT COUNT(*) AS total_records_today
FROM table_authlog
WHERE auth_time >= CURDATE()
AND auth_time < CURDATE() + INTERVAL 1 DAY;统计昨日记录：
SELECT COUNT(*) AS total_records_yesterday
FROM table_authlog
WHERE auth_time >= CURDATE() - INTERVAL 1 DAY
AND auth_time < CURDATE();2. table_scanrecords (扫码记录)
统计今日记录：
SELECT COUNT(*) AS total_records_today
FROM table_scanrecords
WHERE scan_time >= CURDATE()
AND scan_time < CURDATE() + INTERVAL 1 DAY;统计昨日记录：
SELECT COUNT(*) AS total_records_yesterday
FROM table_scanrecords
WHERE scan_time >= CURDATE() - INTERVAL 1 DAY
AND scan_time < CURDATE();四、 效果验证 (EXPLAIN)
优化完成后，必须使用 EXPLAIN 命令验证执行计划。
EXPLAIN SELECT COUNT(*)
FROM table_authlog
WHERE auth_time >= CURDATE() AND auth_time < CURDATE() + INTERVAL 1 DAY;观察重点：
- type: 应当显示为 range。如果显示为
ALL或index（全索引扫描），说明优化未生效。 - key: 应当显示为 idx_auth_time。
- rows: 该数值应大幅小于表总行数，代表扫描行数显著减少。
五、 进阶：面向亿级数据的架构设计
如果单表数据量持续增长（如达到亿级），单纯的 SQL 优化虽然能走索引，但在高并发下 COUNT 操作的大量页面扫描依然会消耗 I/O。此时需要从架构层面解决。
1. 引入统计汇总表 (Summary Table)
这是解决报表查询性能最彻底的方法。不要在用户刷新页面时实时去数千万行日志里“数数”。
设计思路：
建立一张小表 stats_daily_log：
| date | type | count |
|---|---|---|
| 2025-01-01 | auth | 150230 |
| 2025-01-01 | scan | 320110 |
写入策略：
- T+1 模式：每天凌晨通过定时任务（如 XXL-JOB）跑一次昨天的聚合 SQL，写入汇总表。
- 实时模式：如果需要实时数据，可以将“截止到昨天的数据”从汇总表读，加上“今天的数据”用 Redis 计数器维护或 SQL 范围查询（因为当天数据量相对较小）。
2. 数据库分区 (Partitioning)
如果业务必须保留明细且查询频繁，可以考虑按时间范围分区（Range Partition）。
PARTITION BY RANGE (TO_DAYS(auth_time)) (
PARTITION p202501 VALUES LESS THAN (TO_DAYS('2025-02-01')),
...
);优势：查询特定日期时，MySQL 会自动发生 Partition Pruning（分区剪枝），只扫描对应的物理文件，效率极大提升。
总结
数据库性能优化的本质是减少 I/O 和 CPU 消耗。
- 代码规范：严禁在
WHERE条件的左侧索引列上使用函数（如TO_DAYS,SUBSTR），这会导致索引失效。 - 改写习惯：涉及时间段查询，一律使用
column >= start AND column < end的范围查询写法。 - 架构演进：对于日志类大表，SQL 优化是第一步；随着数据量级增长，引入汇总表或分区表是必然选择。
通过以上优化，我们成功将原本耗时数百毫秒的全表扫描统计，降低到了毫秒级的范围索引查询，保证了数据库在高负载下的稳定性。
版权属于：soarli
本文链接：https://blog.soarli.top/archives/771.html
转载时须注明出处及本声明。
