MySQL EAV库LEFT JOIN+OR查询未利用索引（WordPress场景）问题咨询

该性能问题由MySQL对跨多关联表的OR条件的优化限制导致，具体逻辑如下：

• 同表多OR条件可触发索引合并优化，同时扫描多个索引合并结果，但该场景下OR条件分别作用于两个不同的关联表m1和m2，跨表OR无法触发索引合并逻辑。
• 优化器无法提前筛选出满足m1.meta_value='1'或m2.meta_value='1'的post_id集合，只能采用嵌套循环方案：逐行扫描wp_posts的全部20万行记录，分别关联m1和m2判断是否满足OR条件，大量无效扫描导致性能骤降。
• 当OR替换为AND时，优化器可先筛选m1符合条件的post_id，再关联m2做二次过滤，扫描行数指数级降低，因此执行速度极快。手动改写的UNION ALL方案本质是将跨表OR拆分为两个独立的单表条件查询后合并结果，天然可以命中所有关联表的索引。

• 调整wp_postmeta联合索引顺序：将默认的(meta_key, post_id, meta_value)索引调整为(meta_key, meta_value, post_id)，该调整可让meta_key = 'xxx' AND meta_value = 'xxx'的过滤条件直接在索引树内获取对应post_id，无需回表查询，大幅降低关联查询的IO开销。
• 确认开启MySQL索引下推优化：MySQL 5.7及以上版本默认开启，可执行show variables like 'optimizer_switch';检查index_condition_pushdown=on，该优化可让过滤条件提前在存储引擎层执行，减少返回给Server层的无效数据量。
• 开启Hash Join优化：如果使用MySQL 8.0.22及以上版本，确认optimizer_switch中hash_join=on，Hash Join可替代原有的嵌套循环关联模式，跨表OR场景下性能提升非常明显，无需全量扫描wp_posts表。
• 新增meta_value前缀索引：如果业务中meta_value多为短字符串，可新增10~20长度的meta_value前缀索引，进一步减小索引体积，提升条件匹配速度。
• 配置MySQL查询重写插件：通过MySQL官方的Query Rewrite插件，可在服务端自动将原OR查询改写为验证过的UNION ALL格式，完全不需要修改插件的SQL代码，对上层业务无感知。

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