利用 Mysql 高效实现排行榜

需求: 根据分数查出排行榜(若分数相同，根据得分时间先来后到)，并查询出自己的排名，和自己上一名的用户分数信息。

思路: 根据 积分、更新时间、userId 做联合索引。从而根据积分排序直接走索引。

此处注意mysql索引默认是正序排列(mysql 8 退出倒序索引，)，所以需求根据时间先来后到则是 score DESC,upd_score_dt ASC.

如果排序两字段方向不一致则会产生 filesort。

建表:

CREATE TABLE `rank` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户id',
  `score` int(11) NOT NULL DEFAULT '0' COMMENT '积分',
  `upd_score_dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后一次更新分数时间',
  `upddt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `insdt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_user_id` (`user_id`) USING BTREE,
  KEY `index_score_upds` (`score`,`upd_score_dt`,`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='排行榜';

查询SQL:

• 查询前几名 :

select * from rank limit 0,10 

• 查询自己与自己上一名:

    SELECT
		* 
	FROM
		(
		SELECT
			@r := @r + 1 AS ran,
			user_id,
			score 
		FROM
			rank,
			( SELECT @r := 0 ) r 
		WHERE
			score >= ( SELECT score FROM rank WHERE user_id = #{userId} ) 
		ORDER BY
			score DESC,
			upd_score_dt 
		) r 
	ORDER BY
	ran DESC 
	LIMIT 0,2

• 查一下性能:

30w的表，查询第30w名，消耗时间0.219s(名次越靠后越久,Mysql实现无可避免),

查询前几十名，消耗时间0.07s。

这条SQL避免全表扫描， 打个比方说，表里有10w条数据，你查询的用户是第40名，那么只会扫描40条数据。