数据库优化:计算排名
问题描述:
This question问如何通过他的ID选择用户的排名。数据库优化:计算排名
id name points
1 john 4635
3 tom 7364
4 bob 234
6 harry 9857
接受的答案是
SELECT uo.*,
(
SELECT COUNT(*)
FROM users ui
WHERE (ui.points, ui.id) >= (uo.points, uo.id)
) AS rank
FROM users uo
WHERE id = @id
这是有道理的。我想了解这种方法之间的性能折衷是什么,或者通过修改数据库结构来存储计算出来的排名(我想这会在每次排名发生变化时都需要进行大规模的更改)或者其他任何方法我觉得太新奇了。我是一个db noob。
答
的性能折衷基本上会是你所描述的:
如果您修改存储等级结构,查询将是非常,非常简单和快速。然而,这将需要一些开销随时“点”改变,因为你必须验证排名没有改变。如果排名发生了变化,您必须进行多次更新。
这会在每次更新/插入时导致更多的工作(可能存在错误)。权衡是非常快的读取。如果您的典型用法与数百万次读取相比只有极少的修改,并且您发现此查询是一个瓶颈,那么可能需要考虑重新进行此操作。但是,除非您真的发现这是一个问题,否则我会避免增加的复杂性和可维护性问题,因为当前的解决方案需要较少的存储空间并且非常灵活。
答
该查询的'where'部分在内部不需要读取整个表吗?我了解过早优化。在学术上,似乎这不会比几千行进一步扩大。
答
您引用的链接是MySQL问题。如果原始数据库是Oracle,则接受的答案是使用分析函数,该函数确实可以缩放:
SQL> select id, name, points from users order by id
2/
ID NAME POINTS
---------- ---------- ----------
1 john 4635
3 tom 7364
4 bob 234
6 harry 9857
8 algernon 1
9 sebastian 234
10 charles 888
7 rows selected.
SQL> select name, id, points, rank() over (order by points)
2 from users
3/
NAME ID POINTS RANK()OVER(ORDERBYPOINTS)
---------- ---------- ---------- -------------------------
algernon 8 1 1
bob 4 234 2
sebastian 9 234 2
charles 10 888 4
john 1 4635 5
tom 3 7364 6
harry 6 9857 7
7 rows selected.
SQL> select name, id, points, dense_rank() over (order by points desc)
2 from users
3/
NAME ID POINTS DENSE_RANK()OVER(ORDERBYPOINTSDESC)
---------- ---------- ---------- -----------------------------------
harry 6 9857 1
tom 3 7364 2
john 1 4635 3
charles 10 888 4
bob 4 234 5
sebastian 9 234 5
algernon 8 1 6
7 rows selected.
SQL>