优化排序依据

Question

我正在尝试优化这个查询，该查询通过信誉字段（第1个）和ID字段（第2个）对帖子进行排序。没有第一个字段查询需要大约0.250秒，但有了它需要大约2.500秒（意味着慢了10倍，可怕）。有什么建议吗？

SELECT -- everything is ok here
FROM posts AS p
ORDER BY 
    -- 1st: sort by reputation if exists (1 reputation = 1 day)
    (CASE WHEN p.created_at >= unix_timestamp(now() - INTERVAL p.reputation DAY) 
        THEN +p.reputation ELSE NULL END) DESC, -- also used 0 instead of NULL
    -- 2nd: sort by id dec
    p.id DESC
WHERE p.status = 'published' -- the only thing for filter
LIMIT 0,10 -- limit provided as well

注意:
-使用InnoDB（MySQL 5.7.19)
-主要是posts表上的ID
-字段同时被索引created_at和信誉

解释结果：

# id,  select_type, table, partitions, type,  possible_keys, key,  key_len, ref,  rows,    filtered, Extra
# '1', 'SIMPLE',    'p',   NULL,       'ALL', NULL,          NULL, NULL,    NULL, '31968', '100.00', 'Using filesort'

更新^^

信誉规定：一个帖子，多少(n=信誉）天可以显示在列表的顶部。

实际上，我试图给一些帖子的声誉，可以在列表的顶部，并找到解决办法：订单的帖子由“代表”，但只有“一”天的限制。但是过了一段时间（大约2年），由于表数据量的增加，这个解决方案现在成了一个问题。如果我不能解决这个问题，那么我应该从服务中删除该功能。

更新^^

-- all date's are unix timestamp (bigint)
SELECT p.*
    , u.name user_name, u.status user_status
    , c.name city_name, t.name town_name, d.name dist_name
    , pm.meta_name, pm.meta_email, pm.meta_phone
    -- gets last comment as json
    , (SELECT concat("{", 
        '"id":"', pc.id, '",', 
        '"content":"', replace(pc.content, '"', '\\"'), '",', 
        '"date":"', pc.date, '",', 
        '"user_id":"', pcu.id, '",', 
        '"user_name":"', pcu.name, '"}"') last_comment_json 
        FROM post_comments pc 
        LEFT JOIN users pcu ON (pcu.id = pc.user_id) 
        WHERE pc.post_id = p.id
        ORDER BY pc.id DESC LIMIT 1) AS last_comment
FROM posts p
    -- no issues with these
    LEFT JOIN users u ON (u.id = p.user_id)
    LEFT JOIN citys c ON (c.id = p.city_id)
    LEFT JOIN towns t ON (t.id = p.town_id)
    LEFT JOIN dists d ON (d.id = p.dist_id)
    LEFT JOIN post_metas pm ON (pm.post_id = p.id)
WHERE p.status = 'published'
GROUP BY p.id
ORDER BY 
    -- everything okay until here
    -- any other indexed fields makes query slow, not just "case" part
    (CASE WHEN p.created_at >= unix_timestamp(now() - INTERVAL p.reputation DAY) 
        THEN +p.reputation ELSE NULL END) DESC, 
    -- only id field (primary) is effective, no other indexes 
    p.id DESC
LIMIT 0,10;

解释；

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, PRIMARY, p, , ref, PRIMARY,user_id,status,reputation,created_at,city_id-town_id-dist_id,title-content, status, 1, const, 15283, 100.00, Using index condition; Using temporary; Using filesort
# dunno, these join's are not using, but if i remove returning fields from select part show "Using index condition"
1, PRIMARY, u, , eq_ref, PRIMARY, PRIMARY, 2, p.user_id, 1, 100.00, 
1, PRIMARY, c, , eq_ref, PRIMARY, PRIMARY, 1, p.city_id, 1, 100.00, 
1, PRIMARY, t, , eq_ref, PRIMARY, PRIMARY, 2, p.town_id, 1, 100.00, 
1, PRIMARY, d, , eq_ref, PRIMARY, PRIMARY, 2, p.dist_id, 1, 100.00, 
1, PRIMARY, pp, , eq_ref, PRIMARY, PRIMARY, 2, p.id, 1, 100.00, 
2, DEPENDENT SUBQUERY, pc, , ref, post_id,visibility,status, post_id, 2, func, 2, 67.11, Using index condition; Using where; Using filesort
2, DEPENDENT SUBQUERY, pcu, , eq_ref, PRIMARY, PRIMARY, 2, pc.user_id, 1, 100.00, 

Answer 1

你的问题是：

• “Order BY expression”：必须为表中的每一行计算表达式，然后对整个表进行排序，然后结果通过限制。
• 没有索引use：当“col”是索引的一部分时，“order BY col”可以通过按顺序遍历索引来消除排序。这在使用LIMIT时非常高效。但是，它在这里行不通。

有办法摆脱这种混乱，但你需要告诉你有多少不同级别的“声誉”（比如3个，或者说“很多”），以及它们在统计上是如何分布的（比如，1个用户的声誉为100，其余的用户都为零，或者平均分布）。

编辑

嗯，没有关于“声誉”的统计分布或其可能的数值范围的信息。在这种情况下,让我们使用直截了当的方法：

让我们添加一个列“repdate”，它包含：

repdate = p.created_at + INTERVAL p.reputation DAY

这就相当于在未来的某一天为他们拥有的每一个声誉点转移帖子。然后它们将进行相应的排序。如果p.created_at不是datetime,请根据需要进行调整。

现在，我们可以简单地“Order BY repdate desc”，并且有了索引，它将会很快。

Answer 2

这是一个非常有趣的查询。在优化过程中，您可能会发现并理解许多关于MySQL工作方式的新信息。我不确定我会有时间一下子把所有的细节都写出来，但我可以逐渐更新。

基本上有两种情况：一种是快速的，一种是缓慢的。

在一个快速场景中，您将按照预定义的顺序在一个表上遍历，并可能同时从其他表中按每行的id快速获取一些数据。在这种情况下，只要LIMIT子句指定了足够多的行，就会立即停止遍历。订单从何而来？根据表上的B树索引或子查询中结果集的顺序。

在一个缓慢的场景中，您没有预定义的顺序，MySQL必须隐式地将所有数据放入一个临时表中，在某个字段上对表进行排序，并从LIMIT子句返回n行。如果您放入临时表中的任何字段是TEXT类型的（而不是VARCHAR)，则MySQL甚至不会尝试将该表保存在RAM中，而是在磁盘上对其进行刷新和排序（因此需要额外的IO处理）。

在许多情况下，您无法构建一个允许您遵循其顺序的索引（例如，当您按不同表中的列排序时），因此在这种情况下，经验法则是最小化MySQL将放入临时表中的数据。你怎么能做到？您只选择子查询中行的标识符，获得ID后，将ID连接到表本身和其他表以获取内容。也就是说，您用订单制作一个小桌子，然后使用quick场景。（这与一般的SQL略有矛盾，但SQL的每种风格都有自己的方法以这种方式优化查询）。

巧合的是，您的选择--everything is ok here看起来很有趣，因为这是它不ok的第一个地方。

SELECT p.*
    , u.name user_name, u.status user_status
    , c.name city_name, t.name town_name, d.name dist_name
    , pm.meta_name, pm.meta_email, pm.meta_phone
    , (SELECT concat("{", 
        '"id":"', pc.id, '",', 
        '"content":"', replace(pc.content, '"', '\\"'), '",', 
        '"date":"', pc.date, '",', 
        '"user_id":"', pcu.id, '",', 
        '"user_name":"', pcu.name, '"}"') last_comment_json 
        FROM post_comments pc 
        LEFT JOIN users pcu ON (pcu.id = pc.user_id) 
        WHERE pc.post_id = p.id
        ORDER BY pc.id DESC LIMIT 1) AS last_comment
FROM (
    SELECT id
    FROM posts p
    WHERE p.status = 'published'
    ORDER BY 
        (CASE WHEN p.created_at >= unix_timestamp(now() - INTERVAL p.reputation DAY) 
            THEN +p.reputation ELSE NULL END) DESC, 
        p.id DESC
    LIMIT 0,10
) ids
JOIN posts p ON ids.id = p.id  -- mind the join for the p data
LEFT JOIN users u ON (u.id = p.user_id)
LEFT JOIN citys c ON (c.id = p.city_id)
LEFT JOIN towns t ON (t.id = p.town_id)
LEFT JOIN dists d ON (d.id = p.dist_id)
LEFT JOIN post_metas pm ON (pm.post_id = p.id)
;

这是第一步，但即使现在您也可以看到，您不需要对不需要的行进行这些无用的左联接和json序列化。（我跳过了group BY p.id，因为我看不出哪个左联接可能会产生几行，所以您没有进行任何聚合）。

尚未写到：

• 索引
• 重新制定CASE子句（使用UNION ALL)
• 可能强制索引