Mysql与Redis数据库排名实现
- 2021-10-30 12:07:58
- 幻音い
- 6129
温馨提示: 这篇文章于1141天前编写,现在可能不再适用或落后.
前段时间遇到一个单独用mysql查询排名的问题,主要是如果单纯用Mysql来实现排名的话,没有找到更好高效的办法。
单独Mysql实践如下(不建议参考,仅作极端实现)
表名称
player
数据表结构
id | name | score |
---|---|---|
1 | 张三 | 80 |
2 | 李四 | 40 |
3 | 王二 | 55 |
4 | 麻子 | 20 |
tip:数据不止4条甚至成千上万,暂时不加索引
问题
- 1:如何获取表中某条数据在于表内得排名
- 2: 如何通过分页查询数据后获取所在页数数据得所有记录排名
性能极低解决方案
比如我想要获取id为3的用户,在表中的排名数据,根据上面得数据一眼看过去是排名3,常规情况下来计算排名的话,需要将所排数据存入临时表然后计算rank排名,再通过id = 3的条件获取对应数据的rank值
单条数据排名实现
-- 临时表获取rank排名
SELECT @rank := @rank + 1 AS rank,player.* FROM player,( SELECT @rank := 0 ) rank
-- 通过用户id获取对应的排名
SELECT
rank,name,score
FROM
(
SELECT @rank := @rank + 1 AS rank,player.* FROM player,( SELECT @rank := 0 ) rank
)
player
WHERE
player.id = 3;
这样是可以实现问题的,但是会全表扫描,但是如果参与排名的数据量达到1000条的情况下,就会全表扫描1000次
使用php添加剩下的996条用户数据
<?php
$conn = new mysqli("127.0.0.1","root","rootroot","test");
if($conn->connect_error) exit('connect err');
$conn->query("begin");
$sql = "INSERT INTO player(`name`,`score`)VALUES('%s',%d)";
//插入剩余的996条数据
for($i=1;$i<=996;$i++){
$runSql = sprintf($sql,"游客{$i}",$i + 100);
if($conn->query($runSql)){
echo "添加成功{$i}".PHP_EOL;
}else{
echo "添加失败:{$i} -> {$conn->error}".PHP_EOL;
$conn->query("rollback");
exit;
}
}
$conn->query('commit');
echo "添加完成".PHP_EOL;
然后去执行执行计划
EXPLAIN SELECT
rank,name,score
FROM
(
SELECT @rank := @rank + 1 AS rank,player.* FROM player,( SELECT @rank := 0 ) rank
)
player
WHERE
player.id = 3;
毫无疑问的是因为通过临时表来全表统计rank值,这里rows影响行数是1000,并且也是全表扫描。
只用Mysql,不借助其他工具的遗留问题
- 1.在数据表中增加rank字段,当有用户积分数据发生变化的数据重新计算所有用户的排名【因为涉及到更新所有用户的排名rank字段,肯定性能也会更低,如果积分变动频繁更不适合】
- 2.也就是上面的方案,将当前全表所有数据存储到临时表中用于查找对应rank【但是就按当前1000记录来说,查询速度还是挺快的,但是并发一高,记录数量一多,并且如果是通过分页筛选条件的情况下的话,就会造成更多的临时表查询,会产生很多的全表扫描】
最终使用Redis解决排名问题
已知redis种有一个zset有序集合,提供了一个排名方法rank,实践如下
<?php
require './vendor/autoload.php'; //导入composer, 安装了predis
$conn = new mysqli("127.0.0.1","root","rootroot","leti");
if($conn->connect_error) exit('connect err');
logger("mysql连接成功"); //logger只是一个输出打印方法
//
$redis = new Predis\Client("tcp://127.0.0.1:6379");
logger('redis连接成功');
$redis->select(0);
//将当前排名写入到redis里面去
$result = $conn->query("SELECT * FROM player");
while($item = $result->fetch_row()){
//通过zadd方法把对应用户id添加到zset集合去
$redis->zadd("rank_list",[
"用户{$item[1]}"=>$item[4] //这里方便查看加了个用户文字前缀
]);
logger('add'.$item['1']);
}
//然后根据倒叙查询所有数据
$result = $conn->query("SELECT * FROM player ORDER BY score desc");
while($item = $result->fetch_row()){
$rank = 0;
$key = "用户{$item[1]}";
$rank = $redis->zrevrank("rank_list",$key) + 1; //通过zrevrank将redis种积分倒序获取所在索引位置,然后 + 1
logger("用户id: {$item[1]},当前分数: {$item[4]},当前排行: {$rank}");
}
运行结果
后续处理
当后续用户积分变动的时候,同时zset集合种也要zincrby增加或扣除对应的积分,同时也要保证Redis存放的用户数量与mysql中的用户数量一致性,否则排名可能出现异常。
阁下需要登录后才可以查看评论哦~