MariaDB/MySQL自定义函数排序及关联商品查询性能疑问

要实现用自定义函数排序，核心是在ORDER BY子句中直接调用自定义函数，步骤如下：

• 先创建符合需求的自定义函数，比如一个根据商品价格和销量计算排序权重的函数：

  DELIMITER //
  CREATE FUNCTION calc_sort_weight(price DECIMAL(10,2), sales INT) 
  RETURNS DECIMAL(12,2)
  DETERMINISTIC
  BEGIN
      -- 示例：价格权重占30%，销量权重占70%
      RETURN (price * 0.3) + (sales * 0.7);
  END //
  DELIMITER ;
  

• 在查询中通过ORDER BY调用该函数，实现自定义排序逻辑：

  SELECT id, name, price, sales
  FROM products
  ORDER BY calc_sort_weight(price, sales) DESC;
  

注意事项：

• 自定义函数性能通常不如内置函数，若排序逻辑能用内置表达式实现，优先用表达式替代；
• 确保函数标记为DETERMINISTIC（确定性），避免重复计算拖慢性能；
• 若函数涉及复杂逻辑或大量数据计算，可能会显著增加查询耗时，需结合实际场景优化。

假设表结构 #

先明确三张核心表的结构：

• products：商品表，字段id（主键）、name等；
• categories：分类表，字段id（主键）、name等；
• product_categories：商品-分类关联表，字段product_id、category_id（联合主键）。

查询语句 #

以指定商品ID为123为例，查询逻辑是先获取该商品的所有分类，再统计其他商品与这些分类的交集数量，按交集数排序取前10：

SELECT 
    p.id,
    p.name,
    COUNT(pc.category_id) AS common_category_count
FROM 
    products p
JOIN 
    product_categories pc ON p.id = pc.product_id
WHERE 
    pc.category_id IN (
        SELECT category_id FROM product_categories WHERE product_id = 123
    )
    AND p.id != 123 -- 排除当前商品自身
GROUP BY 
    p.id, p.name
ORDER BY 
    common_category_count DESC
LIMIT 10;

性能开销分析 #

在10万商品、100万条关联记录的场景下，性能取决于索引优化程度：

• 最优情况：给product_categories表创建两个联合索引：(product_id, category_id)和(category_id, product_id)。此时子查询能快速定位指定商品的10个分类，JOIN操作也能通过索引快速匹配关联商品，GROUP BY和ORDER BY的开销极低，查询可在毫秒级完成；
• 无索引情况：会触发全表扫描关联表，100万条数据的扫描会导致查询耗时大幅增加，甚至达到秒级；
• 长期优化：若数据持续增长，可考虑预计算：定时离线统计所有商品间的共同分类数，存入专门的product_relations表，查询时直接读取预计算结果，性能会更稳定。

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