php - 使用百分比选择获取行号的最快方法(LIMIT X，1)？

Question

在我的 MySQL 数据库中，我有一个超过 1000 万行的表 (PERSONS)，两个重要的列是:

• 身份证
• 积分

我想知道 ID = randomid 的人的排名

我想返回给这个人他的“排名”，这取决于他的分数。 但是他的排名不会是确切的行数，而更像是一个百分比层。比如:“你在前 5% 中”或“你在 10% - 15% 中”。

当然，我可以查询表格并将行号除以总行数，将其转换为层百分比。但我的问题是，使用 LIMIT X, 1 获取几行会更快(超过 1000 万行)，其中 X 将是表格百分比 100, 95, 90, 85 .. 的一行。下一步:检查这一行的点数是否低于当前人的点数，如果是，则抓取下一层%行，如果不是，则返回上一层行。

在 persons 表中有 9 列，其中包含 2 个 bigints、4 个 varchars 150、1 个日期和 2 个 bool 值。

当然，我更愿意获得准确的行排名，但根据我的测试，这很慢，至少需要几秒钟，而我的 wat 可以在几百秒内完成。

另外，我建议的方式在有多个层具有相同点的情况下并不精确，但不需要那么精确，所以我们可以忽略这一点。

额外信息，我用 PHP 编程，所以如果在 PHP + MySQL 中有针对此的特定解决方案，那也很好。

最后，值得一提的是，该表每小时增长 20,000 行(每天将近 500,000 行)。

感谢所有帮助。

Answer 1

你可以试试这个。我首先计算具有更多 个点的行数，然后将其加一，以防有多个行具有相同的点数。因此，如果有 10 行具有相同的点数，则它们的排名都与该组中的第一行相同。

SELECT SUM(CASE WHEN points > (SELECT POINTS FROM YOUR_TABLE WHERE ID = randomid) THEN 1 ELSE 0 END) + 1 as Rank,
       (SUM(CASE WHEN points > (SELECT POINTS FROM YOUR_TABLE WHERE ID = randomid) THEN 1 ELSE 0 END) + 1) / COUNT(*) as Pct
FROM   YOUR_TABLE

如果那很慢，我会运行两个查询。首先获取该 ID 的点数，然后将其插入第二个查询以确定排名/pct。

SELECT POINTS 
FROM   YOUR_TABLE
WHERE  ID = randomid

然后计算 rank 和 pct，插入上面的点。

SELECT SUM(CASE WHEN points > POINTS THEN 1 ELSE 0 END) + 1 as Rank,
       (SUM(CASE WHEN points > POINTS THEN 1 ELSE 0 END) + 1) / COUNT(*) as Pct
FROM   YOUR_TABLE