MySQL查询优化求助：耗时7-10秒的考勤查询语句优化

原始查询及问题 #

原始查询执行耗时7-10秒，语句如下：

select * 
from `attendance_marks` 
where exists (select * 
              from `attendables` 
              where `attendance_marks`.`attendable_id` = `attendables`.`id` 
              and `attendable_type` = 'student' 
              and `attendable_id` = 258672 
              and `attendables`.`deleted_at` is null
              ) 
and (`marked_at` between '2022-09-01 00:00:00' and '2022-09-30 23:59:59')

相关表数据量：

• attendables表：80966行
• attendance_marks表：1853696行

优化步骤 #

1. 简化子查询逻辑，减少关联开销

原查询的子查询同时关联字段和固定ID，可拆分逻辑：直接过滤attendance_marks的attendable_id=258672，再用子查询验证该ID在attendables中符合要求。子查询无需返回所有列，用SELECT 1更高效：

SELECT am.*
FROM `attendance_marks` am
WHERE am.attendable_id = 258672
  AND am.marked_at BETWEEN '2022-09-01 00:00:00' AND '2022-09-30 23:59:59'
  AND EXISTS (
    SELECT 1
    FROM `attendables` a
    WHERE a.id = 258672
      AND a.attendable_type = 'student'
      AND a.deleted_at IS NULL
  )

2. 创建针对性联合索引

索引是大表查询性能提升的核心，根据过滤条件创建以下索引：

• 为attendance_marks表创建联合索引：

  CREATE INDEX idx_attendable_marked ON `attendance_marks`(attendable_id, marked_at);
  

  该索引直接覆盖两个过滤条件，可快速定位目标数据，避免全表扫描。
• 为attendables表创建联合索引：

  CREATE INDEX idx_id_type_deleted ON `attendables`(id, attendable_type, deleted_at);
  

  子查询通过id查找并验证其他字段，该索引可让数据库直接通过索引完成验证，无需回表查询原数据。

3. 可选：用JOIN改写查询（需注意去重）

部分场景下JOIN性能更优，可改写为以下语句（添加DISTINCT避免重复结果）：

SELECT DISTINCT am.*
FROM `attendance_marks` am
JOIN `attendables` a ON am.attendable_id = a.id
WHERE a.id = 258672
  AND a.attendable_type = 'student'
  AND a.deleted_at IS NULL
  AND am.marked_at BETWEEN '2022-09-01 00:00:00' AND '2022-09-30 23:59:59'

4. 验证数据类型一致性

确保attendance_marks.attendable_id与attendables.id的数据类型完全一致，避免隐式类型转换导致索引失效。

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