MySQL多表关联查询含OR与文本列LIKE操作的性能优化问题

原查询慢的核心原因 #

• 逻辑冗余：先全量关联两张表再分组去重，中间产生大量重复的Request记录，占用内存和计算资源
• 模糊查询无优化：delegatedUserFor like '%(a@xyz.com)%'使用前导通配符，无法走普通索引，触发全表扫描
• 缺失有效联合索引：关联、过滤、排序字段都没有匹配的索引，产生大量随机IO和文件排序
• 不必要的子查询嵌套：增加了优化器的解析成本，无法利用索引下推等优化特性

具体优化措施 #

1. SQL逻辑改写 #

将「先关联再去重」改为「先筛选符合条件的请求ID再关联」，推荐使用EXISTS半连接，匹配到符合条件的日志即可终止判断，无需返回全量日志数据：

SELECT r.id, r.title, r.actionDateTime -- 按需指定字段，禁止用SELECT *
FROM Request r
WHERE r.type = 'custom'
AND EXISTS (
    SELECT 1 
    FROM RequestHistoryLog rh
    WHERE rh.reqId = r.id
      AND rh.status IN ('Approved', 'Done', 'Completed', 'Queried', 'Rejected')
      AND (
          rh.byUser = 'a@xyz.com' 
          -- 若byUser实际存储格式为"姓名(邮箱)"，需修正为 rh.byUser LIKE '%(a@xyz.com)'
          OR rh.delegatedUserFor LIKE '%(a@xyz.com)%'
      )
)
ORDER BY r.actionDateTime DESC
LIMIT 10;

如果已经对delegatedUserFor建立全文索引，将模糊查询替换为全文匹配，性能提升10倍以上：

-- 替换OR后的条件
OR MATCH(rh.delegatedUserFor) AGAINST('"(a@xyz.com)"' IN BOOLEAN MODE)

2. 索引优化（核心提速手段） #

针对查询的过滤、关联、排序逻辑建立覆盖索引，避免回表查询：

• Request表联合索引：ALTER TABLE Request ADD INDEX idx_type_dt_id (type, actionDateTime DESC, id);
  该索引可以直接筛选type='custom'的记录，且按actionDateTime倒序存储，查询时无需额外排序，找到10条符合条件的记录即可终止扫描
• RequestHistoryLog表联合索引：ALTER TABLE RequestHistoryLog ADD INDEX idx_reqid_status_byuser (reqId, status, byUser);
  关联时直接按reqId定位对应日志，过滤status和byUser都可以走索引，无需回表

3. 长期存储结构优化（根治模糊查询性能问题） #

delegatedUserFor多值存储在单个TEXT字段属于不符合范式的设计，既存在查询性能问题，也容易出现匹配错误（比如邮箱为aa@xyz.com的记录会被%(a@xyz.com)%误匹配）。
建议拆分出单独的代办人关联表RequestHistoryDelegatedUser，字段为rhId(bigint)、userEmail(VARCHAR)，每条记录对应一个代办人，查询时直接用等值匹配，性能提升数十倍。

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