MySQL同表查询优化：先筛选processed为NULL再检索data如何提速

问题原因 #

你改写的子查询不会产生优化效果，绝大多数关系型数据库的查询优化器会自动将这类简单子查询扁平化处理，最终生成的执行计划和原始SQL完全一致，不会实现你预期的「先过滤processed为NULL的行再检索data」的执行逻辑。

优化方案 #

• 方案1：加基础索引快速过滤processed字段
  首先给processed字段建立单列索引，优先避免全表扫描，快速筛选出所有processed IS NULL的记录，再对这部分小数据集做文本匹配：

  -- 建索引
  CREATE INDEX idx_events_processed ON events(processed);
  

  建完后可以用EXPLAIN查看执行计划，确认筛选processed的步骤走了索引即可。

• 方案2：增加全文索引优化文本检索
  如果你的数据库支持全文索引（MySQL、PostgreSQL等主流数据库都支持），给data字段建立全文索引，替换性能极低的instr逐行字符串匹配：

  -- MySQL 示例，建全文索引
  CREATE FULLTEXT INDEX idx_events_data ON events(data);
  -- 查询语句改写
  SELECT id, data FROM events 
  WHERE processed IS NULL 
  AND MATCH(data) AGAINST('-Captain' IN BOOLEAN MODE);
  

  全文索引的检索性能比instr高几个数量级，适合大文本字段的检索场景。

• 方案3：用生成列+组合索引实现索引全覆盖
  如果你的检索关键词Captain是固定的业务检索规则，可以新增存储生成列，再建组合索引实现查询全走索引，性能最优：

  -- 新增生成列，自动标记data中是否包含Captain
  ALTER TABLE events ADD COLUMN has_captain TINYINT 
  GENERATED ALWAYS AS (INSTR(data, 'Captain') > 0) STORED;
  -- 建组合索引，查询可以直接走索引不需要回表
  CREATE INDEX idx_events_processed_captain ON events(processed, has_captain);
  -- 改写查询语句
  SELECT id, data FROM events 
  WHERE processed IS NULL AND has_captain = 0;
  

  该方案仅适用于检索关键词固定的场景

• 方案4：表分区优化
  如果表数据量达到千万级以上，可以按processed字段做分区，将processed IS NULL的记录单独存放在一个分区，查询时直接扫描该分区，跳过其他已处理的分区数据，大幅减少扫描数据量。

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