前段时间遇到一个单独用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中的用户数量一致性，否则排名可能出现异常。