如何让过滤非bot用户订单的SQL查询使用指定复合索引？

我有一张orders表，包含索引：
"orders_market_id_owner_id_status_idx" btree (owner_id, market_id, status)
另有users表，含role_alias字段，可选值为bot、member。

需求是获取非bot用户创建的orders记录，但尝试以下三种查询均未使用上述索引：

-- 2 & 3 是需要排除的bot用户ID
EXPLAIN 
SELECT o.* 
FROM orders AS o 
LEFT JOIN users as u ON o.owner_id = u.id 
WHERE owner_id NOT IN (2,3) AND o.market_id = 'btcusdt' LIMIT 10;

EXPLAIN
SELECT o.* 
FROM orders AS o 
WHERE o.owner_id IN (
  SELECT id FROM users AS u WHERE u.role_alias != 'bot') 
  AND o.market_id = 'btcusdt';

EXPLAIN
SELECT o.* 
FROM users AS u 
INNER JOIN orders AS o ON u.id = o.owner_id 
WHERE u.role_alias != 'bot' AND o.market_id = 'btcusdt';

我推测查询未使用索引的原因：

• 使用了非等于运算符u.role_alias != 'bot'
• 使用了IN子查询IN (SELECT id FROM users AS u WHERE u.role_alias != 'bot')

优化方案 #

1. 调整orders表的索引顺序

当前索引以owner_id开头，但查询中先指定market_id的等值条件，后过滤owner_id。BTREE索引的前缀匹配特性决定，将索引调整为(market_id, owner_id, status)更贴合查询逻辑，优化器可优先匹配market_id的等值筛选，再基于此过滤owner_id，能有效触发索引使用：

CREATE INDEX orders_market_id_owner_id_status_new_idx ON orders(market_id, owner_id, status);
-- 原索引可根据实际业务需求保留或删除

2. 给users表添加role_alias索引

针对role_alias的过滤条件，给users表的该字段添加索引，能加速非bot用户ID的查询，避免优化器因子查询/关联操作的开销放弃使用orders表的索引：

CREATE INDEX idx_users_role_alias ON users(role_alias);

3. 消除冗余关联，简化查询

第一个查询中的LEFT JOIN users属于冗余操作（未用到users表字段），直接改写为查询orders表，减少优化器的判断成本：

EXPLAIN 
SELECT o.* 
FROM orders AS o 
WHERE o.owner_id NOT IN (2,3) AND o.market_id = 'btcusdt' LIMIT 10;

4. 用等值筛选替代不等值，结合JOIN优化

因为role_alias只有bot和member两个可选值，用u.role_alias = 'member'替代!= 'bot'，配合INNER JOIN和已优化的索引，能让优化器更清晰地选择索引路径：

EXPLAIN
SELECT o.* 
FROM users AS u 
INNER JOIN orders AS o ON u.id = o.owner_id 
WHERE u.role_alias = 'member'
  AND o.market_id = 'btcusdt';

内容的提问来源于stack exchange，提问作者Najeeb Ullah Shah