如何优化含INNER JOIN与Like查询的MySQL语句以提升执行效率

以下是针对你的查询场景的具体优化手段，确保结果正确的同时缩短执行时间：

• 优化LIKE查询的索引策略

  • 如果你的模糊搜索是前缀匹配（比如content LIKE 'keyword%'），可以给目标字段创建前缀索引，比如：

    CREATE INDEX idx_pages_content_prefix ON pages(content(50));
    

    前缀长度根据字段实际内容调整，平衡索引大小和匹配精度。
  • 如果是中间/后缀匹配（比如%keyword%或%keyword），普通索引无法生效，建议改用MySQL的全文索引：

    -- 先创建全文索引
    CREATE FULLTEXT INDEX idx_pages_content_full ON pages(content);
    -- 用MATCH AGAINST替代LIKE
    SELECT p.*, e.* FROM pages p
    INNER JOIN events e ON p.id = e.page_id
    WHERE MATCH(p.content) AGAINST('keyword' IN BOOLEAN MODE);
    

    注意：全文索引默认对英文分词友好，中文需要额外配置（比如使用ngram分词插件）。

• 先过滤再关联，减少JOIN的数据量
  调整查询顺序，先筛选出pages表中满足LIKE条件的记录，再和events表关联，避免先关联大表再过滤：

  SELECT p.*, e.*
  FROM (SELECT * FROM pages WHERE content LIKE 'keyword%' AND user_id = ?) p
  INNER JOIN events e ON p.id = e.page_id;
  

  这种写法能让数据库先处理小范围的pages数据，再进行JOIN操作。

• 验证索引的实际使用情况
  用EXPLAIN分析查询执行计划，确认索引是否被正确命中：

  EXPLAIN SELECT p.*, e.* FROM pages p
  INNER JOIN events e ON p.id = e.page_id
  WHERE p.content LIKE 'keyword%' AND p.user_id = ?;
  

  查看type列是否为range或ref（表示用到索引），如果是ALL说明发生了全表扫描，需要排查索引是否失效（比如字段类型不匹配、函数包裹字段等）。

• 优化JOIN的关联条件
  确保pages表的主键（比如id）是主键索引，events表的page_id索引已经正确创建，且关联字段的类型完全一致（比如都是INT），避免隐式类型转换导致索引失效。

• 分页场景下的优化
  如果查询包含分页，避免使用OFFSET来跳过大量数据，改用主键或唯一键进行范围查询：

  -- 低效写法
  SELECT p.*, e.* FROM pages p
  INNER JOIN events e ON p.id = e.page_id
  WHERE p.content LIKE 'keyword%'
  LIMIT 10 OFFSET 10000;
  
  -- 优化写法
  SELECT p.*, e.* FROM pages p
  INNER JOIN events e ON p.id = e.page_id
  WHERE p.id > 10000 AND p.content LIKE 'keyword%'
  LIMIT 10;
  

• 数据量过大时的架构优化
  如果pages或events表数据量超过百万级，可以考虑：

  • 按user_id对pages表进行分表，减少单表数据量；
  • 按时间维度对events表进行分区，缩小查询扫描范围。

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