ORDER BY子句导致MariaDB查询性能骤降的问题求助

问题原因 #

从执行计划能看出，添加ORDER BY后，优化器选择了先遍历productPrices的idx_currentPrice索引，再逐行关联products表验证全文搜索条件。这种路径存在两个核心问题：

• 为了避免排序操作，优化器优先走价格索引，但需要扫描大量符合storeID='store-4513'的行，才能筛选出同时满足全文搜索条件的结果，低性能服务器上这种遍历+关联的开销会被放大。
• 无ORDER BY时，优化器会选择更高效的路径：先通过products的全文索引快速筛选出含'beef'的productID，再关联productPrices过滤门店，数据量小所以速度快，但排序需求打乱了这个最优选择。

优化方案 #

1. 先筛选全文结果再关联 #

用子查询先缩小数据范围，让优化器优先处理全文搜索，再关联价格表：

SELECT pp.productID
FROM (
    SELECT productID FROM products 
    WHERE MATCH(productName) AGAINST('beef' IN NATURAL LANGUAGE MODE)
) p
JOIN productPrices pp ON p.productID = pp.productID
WHERE pp.storeID IN ('store-4513')
ORDER BY pp.currentPrice LIMIT 10 OFFSET 0;

这里把原LEFT JOIN改为INNER JOIN（因为WHERE条件已经要求p表有匹配，LEFT JOIN无意义），先拿到符合全文条件的productID集合，再去关联价格表，大幅减少关联的行数。

2. 创建针对性联合索引 #

在productPrices表上创建(storeID, currentPrice, productID)联合索引：

CREATE INDEX idx_storeID_currentPrice_productID ON productPrices(storeID, currentPrice, productID);

这个索引可以让数据库一步完成：筛选指定门店的行→按价格排序→直接获取productID，不需要回表查询原始数据，再关联products表时效率会显著提升。

3. 强制优化器走指定索引 #

如果优化器依然不选择最优路径，用FORCE INDEX强制优先筛选门店数据：

SELECT pp.productID
FROM productPrices pp FORCE INDEX(idx_storeID)
JOIN products p ON pp.productID = p.productID
WHERE MATCH(p.productName) AGAINST('beef' IN NATURAL LANGUAGE MODE)
    AND pp.storeID IN ('store-4513')
ORDER BY pp.currentPrice LIMIT 10 OFFSET 0;

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