MySQL查询添加DATE()至GROUP BY后性能骤降，求优化方案

问题核心原因 #

在字段al.activated_date上使用DATE()函数会导致MySQL无法利用该字段上的索引，触发全表扫描，这就是查询时间从1.2秒暴涨到15秒的关键原因。

优化后的查询语句 #

SELECT 
    a.id, a.description, DATE(al.activated_date) as activated_date, a.category, COUNT(a.id) as total
FROM 
    application_logs al
JOIN 
    applications a ON al.behavior_id = a.id
JOIN 
    users u ON al.manager_id = u.id 
WHERE
    u.year_id = 3
    AND u.shift_id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,16)
    AND u.is_active = 1
    AND a.system_id IN ("43","70","68","69","19","20","45","44","77","46","47","78","11","53","62","63","7","3","50","65","64","73","66","4","12","82","75","26","76","1","2","13","51","42","67","85","14","5","52","8","48","17","71","58","60","79","80","81","18","6","21","22","55","83","23","84","24","25","56","9","27","28","31","29","32","33","34","10","35","36","15","37","38","39","59","40","41","72","16")
    AND a.id IN (4500+ ids)
    -- 替换DATE函数，让索引生效
    AND al.activated_date >= '2024-07-31 00:00:00'
    AND al.activated_date < '2025-05-31 00:00:00'
    AND a.category in ('aa','bb')
GROUP BY 
    a.id, DATE(al.activated_date);

具体优化措施 #

• 避免在字段上使用函数过滤：把DATE(al.activated_date) >= "2024-07-31"改成al.activated_date >= '2024-07-31 00:00:00'，DATE(al.activated_date) <= "2025-05-30"改成al.activated_date < '2025-05-31 00:00:00'。这样MySQL可以直接使用activated_date字段上的索引进行范围查询，无需全表扫描。
• 添加针对性复合索引：
  • 给application_logs表创建复合索引：CREATE INDEX idx_al_manager_behavior_activated ON application_logs(manager_id, behavior_id, activated_date);，覆盖JOIN条件（manager_id、behavior_id）和过滤条件（activated_date），减少回表查询。
  • 给applications表创建覆盖索引：CREATE INDEX idx_a_id_system_category_desc ON applications(id, system_id, category, description);，覆盖WHERE条件和SELECT需要返回的字段，避免回表读取数据。
  • 给users表创建复合索引：CREATE INDEX idx_u_year_shift_active_id ON users(year_id, shift_id, is_active, id);，快速过滤符合条件的用户，同时覆盖JOIN需要的id字段。
• 优化超长IN子句：如果a.id IN (4500+ ids)里的ID数量极多，可以把这些ID存入临时表，再通过JOIN替代IN子句，比如：

  CREATE TEMPORARY TABLE temp_app_ids (id INT PRIMARY KEY);
  INSERT INTO temp_app_ids VALUES (id1), (id2), ...; -- 批量插入4500+个ID
  -- 然后修改查询中的AND a.id IN (...)为AND a.id IN (SELECT id FROM temp_app_ids) 或者直接JOIN temp_app_ids
  

• 可选：使用虚拟列优化GROUP BY：如果GROUP BY的DATE(al.activated_date)仍然影响性能，可以给application_logs表添加虚拟列并建索引：

  ALTER TABLE application_logs ADD COLUMN activated_date_date DATE AS (DATE(activated_date)) STORED;
  CREATE INDEX idx_al_activated_date ON application_logs(activated_date_date);
  

  之后查询中的DATE(al.activated_date)可以替换为activated_date_date，GROUP BY和SELECT都用这个虚拟列，进一步提升效率。

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