在MySQL中缓存行排名？

Question

我有一个需要排序的列，每天会定期更新排名。目前我在代码中使用以下内容：

get all rows from table order by column

rank = 1
foreach row in table
   update row's rank to rank
   rank++

这需要在MySQL中对每一行进行更新。有更高效的方法吗？

- Timmy

Answer 1

使用带有联接的更新:

set @rank := 0;

update tbl a join
  (select id, @rank := @rank + 1 as new_rank from tbl order by col) b
  on a.id = b.id set a.rank = b.new_rank;

如果预期有大量行，最好使用已建立索引的表进行连接以获得最佳性能，例如：

set @rank := 0;

create temporary table tmp (id int primary key, rank int)
  select id, @rank := @rank + 1 as rank from tbl order by col;

update tbl join tmp on tbl.id = tmp.id set tbl.rank = tmp.rank;

最后，你可以通过完全跳过更新步骤并更换新表格（不总是可行）来使其更快：

set @rank := 0;

create table new_tbl (id int primary key, rank int, col char(10),
  col2 char(20)) select id, @rank := @rank + 1 as rank, col, col2
  from tbl order by col;

drop table tbl;
rename table new_tbl to tbl;

- jspcal

---

谢谢，我已经为你点赞了。你有什么想法这个复杂度是多少？假设所有明显的优化（键等）都已经采取了？ - Timmy

是的，最佳性能是在有索引的情况下，理论上应该是相当线性的，因为主键是一个哈希值，您将更新表中的所有项目。 - jspcal

可能 n log n 就可以了，只要不陷入二次方领域。我已经进行了一些测试，即使对于小的 N 值，它也明显更快。唯一的问题可能是可能的表锁定，我应该研究一下（或者将其去规范化到自己的表中）。 - Timmy