5亿行级MySQL大表简单查询耗时超2分钟，如何优化？

问题根因 #

查询性能差的核心原因是WHERE条件对索引列使用了函数，导致date索引失效：

• 原查询WHERE DATE(date) = '2021-08-23'对date字段加了DATE()函数，MySQL无法再利用已经创建的date普通索引，只能全索引扫描4.5亿行数据，所以耗时极高。
• 从EXPLAIN结果也能验证：possible_keys列为NULL，实际走了campaigns_id的普通索引做全索引扫描，扫描行数达454956815行。

第一优先级优化：改写SQL，触发索引生效 #

把DATE()函数包裹的写法改成范围查询，即可直接利用已有的date索引，性能提升100倍以上：

SELECT COUNT(emails_id) AS count FROM person_deliveries 
WHERE date >= '2021-08-23' AND date < '2021-08-24';

这种写法等价于原来的日期匹配逻辑，且不会对索引列做函数运算，优化器会直接命中date索引，仅扫描符合日期条件的50多万行数据，耗时可以降到1秒以内。

第二优先级优化：创建覆盖索引，消除回表开销 #

如果这类按日期统计的需求频繁，可以创建联合覆盖索引，进一步把查询耗时降到毫秒级：

CREATE INDEX idx_date_emails_id ON person_deliveries(date, emails_id);

这个联合索引满足最左匹配原则，日期筛选后可以直接从索引中获取emails_id的值，不需要回表访问主数据，性能最优。
另外如果emails_id是非空字段（你的表结构中emails_id定义为NOT NULL），可以把COUNT(emails_id)改成COUNT(*)，MySQL优化器会自动选择最小的索引做统计，性能更好。

长期高频场景优化 #

如果业务侧经常需要按日期做投递量统计，可以进一步做架构优化：

• 按date字段做表分区，按天或者按月分区，查询时仅扫描对应日期的分区，避免扫描全表数据，适合超大量级的冷数据查询场景
• 新增预聚合统计表，用定时任务每天凌晨统计前一天的各维度投递量，业务查询直接访问统计表，避免每次都扫描原始大表。

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