如何在MySQL中快速查询600万行的大量数据

一、索引优化 #

• 优先给operation_log表的create_time字段创建单值索引，适配时间范围查询和分组排序：

  CREATE INDEX idx_operationlog_createtime ON operation_log(create_time);
  

• 进阶使用覆盖索引，包含查询所需的type字段，避免回表查询原数据，进一步提升效率：

  CREATE INDEX idx_operationlog_createtime_type ON operation_log(create_time, type);
  

二、查询语句优化 #

优化后语句（与原查询结果完全一致） #

SELECT
    DATE(create_time) AS date,
    COUNT(*) AS count,
    COUNT(IF(type = 1, 1, NULL)) AS type1_count,
    COUNT(IF(type = 2, 1, NULL)) AS type2_count,
    COUNT(IF(type = 3, 1, NULL)) AS type3_count
FROM
    operation_log
WHERE
    create_time BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59'
GROUP BY
    DATE(create_time)
ORDER BY
    date ASC;

核心优化点 #

• 用DATE(create_time)替代DATE_FORMAT(create_time, '%Y-%m-%d')，MySQL对内置日期转换函数的处理效率更高，输出结果完全一致。
• 用COUNT(IF(...))替代SUM(CASE...)，逻辑等价但写法更简洁，性能表现持平。
• 用BETWEEN替代双范围条件，语法更简洁，不影响优化器执行逻辑。

三、额外优化建议 #

• 禁止在WHERE条件中对create_time使用函数（比如DATE(create_time) >= '2023-01-01'），会直接导致索引失效，必须保持字段直接参与比较。
• 若服务器资源充足，可调整MySQL参数sort_buffer_size、tmp_table_size，提升分组排序的内存使用优先级，减少磁盘临时表的生成。
• 对实时性要求不高的场景，可建立统计汇总表，通过定时任务（如crontab+SQL脚本）每日预计算前一天的统计数据，查询时直接读取汇总表，耗时可降至毫秒级。

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