如何优化MySQL WHERE子句中datetime类型的比较查询性能

• 你的查询条件是last_indexed_at is null OR updated_at > last_indexed_at，属于两个列的动态比较，不是列和常量的等值/范围匹配，现有仅针对last_indexed_at的单列索引无法匹配这种查询规则，导致触发全表扫描，2亿+行的全表扫描自然耗时极长。
• 即使把COALESCE改写成OR逻辑，本质还是两列比较，没有匹配索引的情况下依然要扫全表，所以性能没有提升。

方案1：新增虚拟生成列+索引（最高效，推荐） #

MySQL 5.7支持虚拟生成列，无需修改业务代码即可自动计算标识字段：

1. 执行DDL新增虚拟列并建索引，2亿行表建议低峰期执行，添加参数避免锁表：

ALTER TABLE documents 
ADD COLUMN need_reindex TINYINT(1) GENERATED ALWAYS AS (CASE WHEN last_indexed_at IS NULL OR updated_at > last_indexed_at THEN 1 ELSE 0 END) VIRTUAL,
ADD INDEX idx_need_reindex (need_reindex),
ALGORITHM=INPLACE, LOCK=NONE;

2. 之后查询直接走索引即可，无论是计数还是取待处理数据都可以做到毫秒/秒级返回：

-- 计数
SELECT COUNT(*) FROM documents WHERE need_reindex = 1;
-- 取待处理数据
SELECT * FROM documents WHERE need_reindex = 1 LIMIT 1000;

3. Rails侧配置忽略该生成列，避免schema导出和迁移冲突：

# app/models/document.rb
class Document < ApplicationRecord
  self.ignored_columns = ["need_reindex"]
end

该方案的优势是索引基数极低（只有0和1两个值，其中1占比约25%），查询时直接命中索引无需额外计算，写入时虚拟列的计算开销几乎可以忽略。

方案2：新增联合覆盖索引（无需加列） #

如果不想新增字段，可以创建last_indexed_at和updated_at的联合覆盖索引，让数据库直接扫描索引文件即可完成条件判断，无需回表读取整行数据：

CREATE INDEX idx_last_updated_at ON documents (last_indexed_at, updated_at), ALGORITHM=INPLACE, LOCK=NONE;

索引创建完成后，你的原查询SELECT * FROM documents WHERE last_indexed_at IS NULL OR updated_at > last_indexed_at会直接走该覆盖索引，扫描速度比全表扫提升至少10倍以上，limit 1的查询几乎可以瞬间返回。

方案3：拆分OR查询为UNION ALL（无需修改表结构） #

如果暂时不能加索引，可以把原OR逻辑拆为两个可命中现有索引的子查询合并：

SELECT COUNT(*) FROM (
  SELECT id FROM documents WHERE last_indexed_at IS NULL
  UNION ALL
  SELECT id FROM documents WHERE last_indexed_at IS NOT NULL AND updated_at > last_indexed_at
) AS t;

第一个子查询last_indexed_at IS NULL可以直接命中你现有的index_documents_on_last_indexed_at单列索引，无需全表扫描，整体耗时可以压缩到1分钟以内。

• 如果你不需要完全精确的总计数，可以直接查询系统表获取估算值，瞬间返回：

SELECT TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = '你的库名' AND TABLE_NAME = 'documents';

• 批量处理待索引文档时，不要一次性拉取全部5000多万条，采用主键分段分页的方式处理，避免深分页和内存溢出：

SELECT * FROM documents WHERE need_reindex = 1 AND id > '上次处理的最大ID' ORDER BY id ASC LIMIT 1000;

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