MySQL日期查询优化：千万级数据带索引仍耗时8秒的方案咨询

嘿，针对你这个1000万行数据的count查询慢的问题，我来分享几个实际项目里验证过的优化思路，毕竟我之前也碰到过类似的场景：

1. 先确认索引的实际执行情况 #

首先再仔细拆解EXPLAIN的输出结果：

• 检查type列是不是range（范围扫描，这是匹配你查询条件的最优执行类型）；
• 检查key列是不是显示你为last_updated创建的索引，确保优化器真的用上了索引；
• 看Extra列有没有Using index——如果有，说明是覆盖索引扫描，不用回表取数据，这是最理想的状态；如果没有，可能是索引被隐式忽略（比如字段类型和查询值类型不匹配？不过你这里字符串格式是标准datetime格式，大概率没问题）。

要是能把EXPLAIN的完整结果贴出来，能更精准判断问题，但先按这个方向排查准没错。

2. 尝试替换COUNT(1)为COUNT(*) #

虽然很多资料说InnoDB里COUNT(1)和COUNT(*)性能差异不大，但实际测试中，优化器对COUNT(*)的支持更友好——它明确知道是统计行数，会优先选择体积最小的索引来扫描（二级索引比主键索引小很多）。试一下这条语句：

SELECT Count(*) AS loopCount FROM main_iteminstance mi WHERE mi.last_updated >= '2018-04-12 07:25:23.000';

3. 清理索引碎片 #

如果你的表经常有更新、删除操作，last_updated的索引可能产生大量碎片，导致扫描时需要额外的磁盘IO。可以做这些操作：

• 先查看索引基数是否准确：SHOW INDEX FROM main_iteminstance;，看Cardinality列的值是否接近实际的唯一值数量；
• 在业务低峰期重建表和索引：

  -- MySQL 5.7及以下版本用这个
  OPTIMIZE TABLE main_iteminstance;
  -- MySQL 8.0+推荐用这个，锁表时间更短
  ALTER TABLE main_iteminstance ENGINE=InnoDB;
  

4. 考虑按日期分区 #

如果你的业务查询经常按last_updated的时间范围过滤，把表按日期分区是个长期有效的优化方案。比如按月份分区，查询时MySQL只会扫描符合条件的分区，不用遍历整个表的索引。举个简单的分区示例：

ALTER TABLE main_iteminstance 
PARTITION BY RANGE (TO_DAYS(last_updated)) (
    PARTITION p201804 VALUES LESS THAN (TO_DAYS('2018-05-01')),
    PARTITION p201805 VALUES LESS THAN (TO_DAYS('2018-06-01')),
    -- 后续分区按需添加
    PARTITION p_current VALUES LESS THAN MAXVALUE
);

分区后，你的查询只会扫描p201804及之后的分区，速度会有明显提升。

5. 预计算统计值（非实时场景） #

如果这个查询不需要严格的实时数据，建议用定时任务预计算统计结果：

1. 创建一个统计表：

   CREATE TABLE iteminstance_stats (
       stat_datetime DATETIME PRIMARY KEY,
       loop_count BIGINT NOT NULL
   );
   

2. 用crontab或者数据库事件，每天凌晨计算截至各个时间点的count值，写入这个表；
3. 查询时直接从统计表取数，速度能达到毫秒级。

6. 检查服务器硬件和配置 #

最后，排查一下基础环境：

• 确认innodb_buffer_pool_size设置是否合理，专用数据库服务器建议设为内存的50%-70%，确保索引数据能加载到内存，减少磁盘IO；
• 如果用的是机械硬盘，换成SSD能大幅提升随机读性能，这对索引扫描的帮助非常大。

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