產品替代查詢性能緩慢 - 需要多對多表和 ROW_NUM(OVER PARTITION…) 優化？

當使用者在我的 Web 應用程序中選擇產品時，我會執行一個查詢，以基於通過與類別參考表的多對多關係連結到該產品的產品類別來搜尋相關替代品。

只有 6000 種產品，替代查詢需要 500 多毫秒，我預計產品數量在短期內會達到 x10。有沒有更好的方法來建構我的查詢？

關係非常簡單：我有一個類別參考表（例如短褲、褲子、襯衫、S、M、L、XL、棉等），每個產品都有一個或多個與之相關的類別。有適當的 FK 索引。

我的“替代品”查詢試圖最大化匹配類別的數量 - 產品的匹配類別越多，其匹配分數就越高。它還排除了所選產品並將產品變體合併為單獨的產品記錄（ ROW_NUM() OVER(PARTITION… )。合併避免建議非常相似的產品作為替代品（例如，不同顏色的相同產品）

為了進一步對具有相同類別匹配分數的產品進行排名，計算文本相關性分數（MATCH AGAINST fulltext），結果按類別匹配計數和文本相關性排序。全文匹配對查詢性能的影響很小，但是變體合併大約需要200ms。

此時，我無法更改應用程序/數據模型來規範產品變體。

這是 SQL：

SELECT * FROM (
   SELECT P.*, 
   (SUM(CASE WHEN C.category_id = 3 OR C.category_id = 11 OR C.category_id = 18 THEN 1 ELSE 0 END)) AS cat_score,
   MATCH(P.name, P.description) AGAINST('blue short sleeve shirt' IN NATURAL LANGUAGE MODE) AS rel_score,
   ROW_NUMBER() OVER(PARTITION BY variant_group ORDER BY price ASC) variantIndex 
   FROM RPDB.Product P 
   JOIN Product_has_Category PC ON PC.product_id = P.product_id 
   JOIN Category C ON C.category_id = PC.category_id 
   WHERE P.product_id <> 123 AND variant_group <> 65
   GROUP BY P.product_id
) result
WHERE variantIndex = 1
ORDER BY cat_score DESC
LIMIT 0, 5

解釋輸出（注意我在上面的 SQL 中簡化了一些欄位名）：

我不在乎性能改進來自哪裡，所以查詢的任何可以改進的方面都將受到歡迎！

我再次考慮了這個問題，從 Product_has_Category 開始，給我一組相關的產品（一個或多個類別），然後在合併變體和訂購分數之前將其加入 Products 表。

這在〜150ms而不是500ms內執行。

SELECT * FROM (
   SELECT 
       *,
       MATCH(P.name, P.desc_short, P.desc_long) AGAINST('blue short sleeve shirt' IN NATURAL LANGUAGE MODE) AS rel_score,
       ROW_NUMBER() OVER(PARTITION BY Organization_idOrganization, variant_group ORDER BY price ASC) variantIndex
   FROM Product P 
       JOIN (
           SELECT Product_idProduct, COUNT(Product_idProduct) AS cat_score
           FROM Product_has_Category PC 
           JOIN Category C ON PC.Category_idCategory = C.idCategory
           WHERE PC.Category_idCategory IN (3, 11, 18)
           GROUP BY Product_idProduct
           ) AS R 
           ON P.idProduct = R.Product_idProduct
   WHERE idProduct <> 123 AND variant_group <> 65 AND (P.price BETWEEN 0.0 AND 999999.0)
) AS PR
WHERE variantIndex = 1
ORDER BY cat_score DESC, rel_score DESC
LIMIT 0, 5

多對多表的索引不足是很常見的。請參閱http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table了解它應該有的兩個索引和它不應該有的索引。

引用自：https://dba.stackexchange.com/questions/314154