优化含OR条件的MariaDB LEFT JOIN SQL查询性能

问题根源 #

原查询的性能瓶颈在于OR条件跨越了billing和payments两个关联表：

SELECT billing.id, payments.id 
FROM billing
LEFT JOIN payments ON payments.billing_id = billing.id
WHERE billing.email = 'foo' OR payments.txn = 'bar';

当使用跨表OR时，优化器无法同时高效利用billing.email和payments.txn的索引，只能先通过billing.email索引扫描大量记录（EXPLAIN显示扫描99464行），再逐一关联payments表，导致耗时飙升。而移除任一条件或改为AND时，优化器可以精准匹配单表索引，因此查询速度极快。

优化方案 #

1. 新增复合索引 #

给payments表创建**(txn, billing_id)复合索引**，直接支持txn='bar'条件的快速筛选与关联：

CREATE INDEX idx_payments_txn_billing_id ON payments (txn, billing_id);

该索引可以让数据库直接定位到txn='bar'的所有记录，同时直接获取关联的billing_id，避免后续低效的全表关联扫描。

2. 强制指定关联顺序 #

使用STRAIGHT_JOIN强制优化器调整关联顺序，优先处理txn='bar'的条件，再合并billing.email='foo'的结果：

SELECT billing.id, payments.id 
FROM payments
STRAIGHT_JOIN billing ON billing.id = payments.billing_id
WHERE billing.email = 'foo' OR payments.txn = 'bar';

执行后通过EXPLAIN验证扫描行数是否减少，确认执行计划是否优化。

3. 升级MariaDB版本到10.6+ #

MariaDB 10.6及以上版本对跨表OR条件的优化逻辑做了显著增强，优化器能更好地识别多表索引组合，无需修改查询语句即可自动提升执行效率。

4. 启用索引合并优化 #

确保index_merge优化器开关处于开启状态（默认通常已开启，可手动确认）：

SET optimizer_switch='index_merge=on';

该开关允许优化器将多个索引的查询结果合并，有助于优化跨表OR条件的执行路径。

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