mysql - 获取mariadb/mysql中按分数表排序的N条前后记录
问题描述
我有一个包含分数的表(最多 20000 条记录)。我想显示一个带有延迟加载功能的记分牌:只显示球员得分的 20 条记录,如果他向上滚动,则再显示 20 条记录,或者如果他向下滚动,则获得 20 条记录。这个板会经常被大量玩家同时调用,所以我必须以最轻松的方式来做。
CREATE TABLE cities (
cityId SMALLINT UNSIGNED NOT NULL,
points SMALLINT UNSIGNED NOT NULL, -- not unique at all
PRIMARY KEY (cityId)
)
ENGINE = INNODB;
ALTER TABLE cities
ADD INDEX points (points);
如何有效地获取points
指定行的前 10 行和后 10 行(按降序排序WHERE cityId=<myCityId>
)
我怎样才能找到下一个 20 ?因为使用似乎不是最好的
OFFSET
方式https://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow/LIMIT
谢谢
编辑:
我尝试了两种@Schwern 解决方案,但都没有按预期工作,因为我可以有相同分数的线条。
select points, cityName from (
(
select *
from cities
where points < (select points from cities where cityName = :cityName)
order by points desc
limit 5
)
union
select * from cities where cityName = :cityName
union
(
select *
from cities
where points >= (select points from cities where cityName = :cityName)
and cityName != :cityName
order by points
limit 5
)
) t
order by points;
结果limit=5
和cityName=Viry
:
points cityName
0 Nantes
0 Amiens
2223 Roye
3705 Caps City
4446 Toulouse
5187 Viry
5187 Rampillon
5187 Vdr
5187 Chicago
5187 Le Village
5187 Titoucity
缺少很多相同分数的行(例如:32行,分数=4446,这里只有一个)
MariaDB/MySQL 版本翻译自Oracle 解决方案
WITH RECURSIVE boundaries (prevr, nextr, lvl) as (
select
COALESCE(
(
select max(c.points)
from cities AS c
where c.points < c2.points
),
c2.points
) AS prevr,
COALESCE(
(
select min(c.points)
from cities AS c
where c.points > c2.points
),
c2.points
) AS nextr,
1 lvl
from cities AS c2
where cityName = :cityName
union all
select
COALESCE(
(
select max(points)
from cities AS c
where c.points < prevr
),
prevr
) AS prevr,
COALESCE(
(
select min(points)
from cities AS c
where c.points > nextr
),
nextr
) AS nextr,
lvl+1 lvl
from boundaries
where lvl+1 <= :lvl
)
select c.points, c.cityName
from cities AS c
join boundaries AS b
on c.points between b.prevr and b.nextr
and b.lvl = :lvl
order by c.points;
结果lvl=1
与cityName=Viry
points cityName
4446 Toulouse
4446 Jotown
4446 Guignes
4446 Douns
4446 Colombes
4446 Chambly
4446 Cassandra Gn
4446 Bussyland
4446 Magny Les Hameaux
4446 Palamos
4446 Ville
4446 Loujul
4446 Osny
4446 Sqy
4446 Senlis
4446 Vendres
4446 Amiens
4446 Saint Jean De Luz
4446 Senlis
4446 Abbeville
4446 Ca City
4446 Tolkien
4446 Paiementland
4446 Cash City
4446 Amiens
4446 Beauvais
4446 Kona
4446 St Petaouchnoc'
4446 Amiens
4446 Pick City
4446 Conflans
4446 Versailles ^ +1
5187 Le Village
5187 Compiegne
5187 Titoucity
5187 Vdr
5187 Rampillon
5187 Chicago
5187 Moustache Ville
5187 Viry ^ 0
5928 Trot Ville v -1
5928 Amiens
5928 Cityc
5928 Bakel City
5928 Rouen
5928 Noailles
5928 Caps Town
5928 Atlantis
5928 Camon
5928 Smart City
5928 Maville
5928 Azzana
5928 Strasbourg
5928 Sqy Park
它有效,但我需要决定我得到多少行,有时我可以有 50 个相同的分数,有时只有一两个。
重新:编辑
使用第二个字段重试第一个解决方案以获取订单
SET @mypoints := (select points from cities where cityId = :cityId);
select t.points, t.cityId, t.cityName from (
(
select *
from cities AS c1
where c1.points <= @mypoints
AND c1.cityId > :cityId
order by c1.points DESC, c1.cityId ASC
limit 5
)
union
select * from cities AS c2 where c2.cityId = :cityId
union
(
select *
from cities AS c3
where c3.points >= @mypoints
AND c3.cityId < :cityId
order by c3.points ASC, c3.cityId DESC
limit 5
)
) t
order by t.points;
结果limit=5
与cityId=36
points cityId cityName
0 49 Nantes
1482 53 Paris
1482 51 Mattown
2223 56 Haudiville
3705 37 Caps City
5187 36 Viry < ==
6669 29 Prospercity
6669 31 Amiens
8892 22 Meteor
20007 34 Ouagadougou
20007 35 Meaux
和第一个问题一样
解决方案
因为城市可以有相同的点,所以我们需要注意不要在前一个和下一个之间重复行。
首先,我们通过按点排序并找到具有相同或更多点的那些获得下一行,不包括所选城市。很简单。
select *
from ranking
where points >= (select points from ranking where cityId = :cityId)
and cityId != :cityId
order by points
limit 10
然后我们得到有问题的行。
select * from ranking where cityId = :cityId
然后我们通过寻找点数较少的行来获得前几行,但我们必须按点数降序排列。这给出了相反的结果,我们稍后会解决这个问题。
select *
from ranking
where points < (select points from ranking where cityId = :cityId)
order by points desc
limit 10
我们可以用 s 将这些全部放在一个查询中union
。对组合查询进行排序可以解决先前行被反转的问题。
select * from (
(
select *
from ranking
where points < (select points from ranking where cityId = :cityId)
order by points desc
limit 10
)
union
select * from ranking where cityId = :cityId
union
(
select *
from ranking
where points >= (select points from ranking where cityId = :cityId)
and cityId != :cityId
order by points
limit 10
)
) t
order by points;
我通过生成 200,000 个随机日期来对此与限制/偏移进行基准测试。有显着的性能改进。不像您在互联网上看到的那么可怕,但这可能是硬件差异。
使用union
时间 < 10 毫秒。limit 10 offset X
使用 X 进行扩展,在 50,000 时需要 20 到 120 毫秒,具体取决于它是否需要文件排序。
在单个 SQL 语句中获取一行加 N 行任一侧中概述了另一个选项。因为它是为 Oracle 编写的,所以替换nvl
为coalesce
.
推荐阅读
- android - 在 android 中获取联系人列表需要时间和崩溃的应用程序
- git - Google Cloud Build - 触发器仅调用分支构建
- mockito - Mockito 为 Future 返回空值
- xml - EditText 在选择时失去背景
- python - 如何检测pyqt中的密钥释放
- powershell - 如何获取dll文件中的类等信息?
- powershell - Powershell:删除arraylist中的重复条目
- python - 在 Python 3 中生成随机数学
- php - 使用参数重定向到另一个 PHP 的 PHP
- ruby-on-rails - 转发域但保留 URL