MySQL关联视图的OR LIKE查询极慢甚至阻塞的原因及优化方案

慢查询核心原因 #

• 条件下推失败：MySQL 5.7 优化器无法将 OR 两侧的过滤条件下推到 JOIN 操作之前执行，只能先完成全量 LEFT JOIN 得到4万行中间结果，再逐行对两个最长6万字符的 TEXT 字段做前导通配符的 LIKE 匹配，计算量是拆分后单独查询的数倍。且短字符串匹配长文本时需要遍历的字符位置更多，进一步放大了计算开销。
• IO开销翻倍：InnoDB 中超过768字节的 TEXT 字段会存储在溢出页，单独查询单表时只需读取单表的 TEXT 溢出页，而组合查询需要同时读取两张表的所有 TEXT 溢出页用于匹配，IO 开销直接翻倍，大量随机IO会拖慢执行速度，甚至占用过多IO资源导致当前 schema 的其他请求阻塞。
• 视图逻辑无法提前裁剪：virtual_table_b 是跨 schema 的视图，优化器无法将过滤条件下推到视图的底层计算逻辑，只能先计算出视图的全量结果再参与 JOIN，进一步放大了中间结果的计算和存储开销。

优化方案 #

方案1：改写SQL拆分过滤逻辑（生效最快，无需改结构） #

将原SQL的 OR 条件拆为两个子查询用 UNION 合并，和原查询返回结果完全一致，且过滤条件可以提前下推：

SELECT * FROM table_a a LEFT JOIN virtual_table_b b ON a.id = b.id WHERE a.text LIKE '%somestring%'
UNION
SELECT * FROM table_a a INNER JOIN virtual_table_b b ON a.id = b.id WHERE b.text LIKE '%somestring%'

如果可以接受重复结果（仅当某行同时满足两个匹配条件时才会出现），可以把 UNION 改为 UNION ALL 性能更好。

方案2：使用全文索引替代模糊匹配（长期最优） #

针对两个表的 text 字段建立 InnoDB 全文索引，注意视图的索引需要建在底层实体表上：

CREATE FULLTEXT INDEX idx_ft_a_text ON table_a(text);
CREATE FULLTEXT INDEX idx_ft_b_text ON virtual_table_b_base_table(text);

查询时用 MATCH AGAINST 替代 LIKE 匹配：

SELECT  *
FROM  table_a a
LEFT JOIN  virtual_table_b b  ON a.id = b.id
WHERE  MATCH(a.text) AGAINST('somestring' IN BOOLEAN MODE)
  OR  MATCH(b.text) AGAINST('somestring' IN BOOLEAN MODE);

InnoDB 默认全文索引最小匹配长度为4，如果需要匹配更短的字符串，可以修改配置 ft_min_word_len = 所需长度 后重建索引即可，性能比 LIKE 模糊匹配高10~100倍。

方案3：提前过滤ID减少TEXT匹配 #

先通过子查询拿到符合条件的ID集合，再用ID做匹配判断，避免逐行扫描长TEXT字段：

SELECT * FROM table_a a LEFT JOIN virtual_table_b b ON a.id = b.id
WHERE a.id IN (SELECT id FROM table_a WHERE text LIKE '%somestring%')
OR b.id IN (SELECT id FROM virtual_table_b WHERE text LIKE '%somestring%');

其他优化建议 #

• 避免使用 SELECT *，只查询业务需要的字段，减少不必要的TEXT字段读取IO。
• 简化 virtual_table_b 视图的嵌套逻辑，减少不必要的关联和计算，条件允许的话可以用定时任务将视图数据同步为实体表，加索引后查询性能会进一步提升。

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