MariaDB 10.3中书籍排名查询counter值错误的优化咨询

你的核心问题是变量累加时机错误：原查询里@var是在分组后、排序前就开始计数的，导致counter对应的是未排序的原始数据顺序，而非按calculated_points降序后的排名。

优化方案1：子查询排序后再计数（兼容传统变量写法） #

先在子查询中完成分组、得分计算和排序，再在外层基于排好序的结果生成正确的排名：

SET @var = 0;
SELECT 
    (@var:=@var+1) AS counter,
    calculated_points
FROM (
    SELECT 
        (row1 * row2 / 4) AS calculated_points
    FROM bookz
    WHERE kat = '6'
      AND pubdate > DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
      AND pub = '1'
      AND ratings > '1'
      AND productType = 'pbook'
    GROUP BY title
    ORDER BY calculated_points DESC
) AS sorted_books
LIMIT 256;

优化方案2：使用窗口函数（MariaDB 10.2+支持，更简洁） #

MariaDB 10.3已经支持窗口函数，用ROW_NUMBER()可以直接生成正确排名，无需手动维护变量：

SELECT 
    ROW_NUMBER() OVER (ORDER BY calculated_points DESC) AS counter,
    calculated_points
FROM (
    SELECT 
        (row1 * row2 / 4) AS calculated_points
    FROM bookz
    WHERE kat = '6'
      AND pubdate > DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
      AND pub = '1'
      AND ratings > '1'
      AND productType = 'pbook'
    GROUP BY title
) AS book_points
ORDER BY counter
LIMIT 256;

性能优化建议 #

为了提升查询速度，建议给bookz表创建联合索引，覆盖过滤、分组所需字段，减少数据库扫描的数据量：

CREATE INDEX idx_bookz_ranking ON bookz (kat, pub, productType, pubdate, ratings, title);

索引字段顺序可根据实际数据的筛选优先级调整，优先放筛选性强的字段。

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