Summary of actual combat based on Redis leaderboard

Objective:
    The design and implementation of the leaderboard has been written before, and the architecture and design model behind the requirements are different.
    Platform differences, some based on the game platform, for a number of applications to provide services, and some are limited to a single game. Ranking range of differences, some of the global ranking, and some only do friends circle ranking. Real-time differences, offline statistics, real-time rankings are more common.
    In any case, this article will be combined with the previous written page of the game, to specifically elaborate on the Redis leaderboard based on the actual process.

Related article series:
I've written two articles about the leaderboard before, but that's for the game platform (like, hand Q, etc.). Each user has its own leaderboard, not global.
• Social game leaderboard Design and implementation (1)

   Articles for the game global leaderboard
   

Requirements Description:
    Take the game as an example, the leaderboard is based on the number of players to rank, which is reasonable. But if two players scored the same, how can this scenario be measured?
    in other words, score (score) is the first sort factor, and time (the crack duration) is the second sort factor .
    However, if you use Redis's sorted set to implement, you can only set a single sorting score score. In this case, the two-rank sort would like to use Redis, and it seems that this path is unworkable.
    Do not lose heart, dream is there, in case the realization of it? ^_^.
    Yes, the solution is there, first sell a xiaoguanzi, and see below decomposition. Also, what are the advantages of using redis compared to MySQL?

MySQL Program:
    Every time a player rushes through a level, they need to record their score record in that off. On the other hand, the player is a repetition of the action, so in the design score model, the score record also helps to go heavy.
    Record Data Model

    CREATE TABLE IF not EXISTS ' Tb_game_record ' (      ' id ' int (one) not null auto_increment,      ' userid ' varchar (+) NOT NULL COMMENT ' User ID ',      ' gateid ' int (one) not null COMMENT ' level number ',      ' slove_time ' bigint ' NOT null COMMENT ' resolve point in time ', 
   
    primary key (' id '),      UNIQUE key ' userid ' (' userid ', ' Gateid ')    ) Engine=innodb  DEFAULT Charset=utf8;
   

    Note: userid represents the user, Gateid is the level number, Slove_time is the point of time to resolve. Userid+gateid is a federated unique index for de-weight.
    Based on the simple design of the data table, how the Top-n query evolves.
    The leaderboard query is similar to top-n, whose SQL representation is somewhat complex, and is a nested subquery.
    1). Count the number of pass and the latest breaking time point

    SELECT userid, COUNT (Gateid) as score, MAX (Slove_time) as Last_slove_time from    Tb_game_record    GROUP by UserID

    2). Sort (by score descending, time ascending)

    SELECT Useid, score, last_slove_time from    (...)    ORDER by Score DESC, Last_slove_time ASC

    3). Integrated sql+ interval Segment

Select UserID, Score, last_slove_time from    (        SELECT userid, COUNT (Gateid) as score, MAX (slove_time) as Last_slo Ve_time from        Tb_game_record        GROUP by userid    ) T    ORDER by score DESC, Last_slove_time ASC    LIMIT ?, ?

    On the whole, it's still quite smooth, but how about the performance? Let's do a explain evaluation.

　　

    The use of sub-SQL to Filesort, this is very performance, but there is no alternative.
    Is there a plan to improve it? Of course, why not introduce a scoring table alone?
    Total score Record Data model

CREATE TABLE IF not EXISTS ' Tb_game_score ' (      ' id ' int (one) not NULL auto_increment,      ' userid ' varchar (+) not null,< c7/> ' score ' int (one) not null,      ' Last_slove_time ' bigint (no null),      unique key ' ID ' (' id '),      unique key ' use Rid ' (' userid ')    ) Engine=innodb DEFAULT Charset=utf8;

    Note: Score is the total score for the recorded userid.
    Whenever the player cracked a level, it automatically added a, although some write consumption, but for query top-n, then helped a lot of busy.
    Top-n's query SQL evolved to:

    SELECT userid, score, last_slove_time from    Tb_game_score    ORDER by score DESC, Last_slove_time ASC

    If you are using explain for SQL analysis:

　　

    Although it is also used in Filesort, its data size is one order less than that of Tb_game_record.
    Of course it also introduces the risk of data consistency, so it is necessary to do transactional protection when updating.

Redis+mysql Solution:
    The total Score record table is introduced, and there is a certain performance loss on the query. Redis is known as the memory data structure server, can replace the function of Mysql+cache?
    At least in the function of the leaderboard, its data structure sorted set is fully able to meet the requirements. It can replace the score record table, ^_^.
    Of course, the difficulty lies in the two-level ordering of the model abstraction, sorted set only supports the first level of ordering (sorted set of the score field is a double type), so the problem evolved to be able to build a mapping function, the two-level sorting map to a one-level sort (double field) .
    Fortunately, on the demand for leaderboards, level two (score, time) is a field that can be mapped to a first order (sorted set score).
    Can be simply set:

Score (score) +time (9999999999-unix of the epoch seconds, and fixed length)

    The Unix epoch seconds, in the foreseeable future, the length of time is fixed length, and take negative . Score in front, time in the back.
    For example, player a scored 10, and the last level was 2016/3/30 17:35:47, the timestamp was: 1459330547. The final is: 8540669452=9999999999-1459330547.
    108540669452.

    This will be perfect to get to the initial level of the two ranked ranking needs.
    Mapping function Design Note points
    This is actually very important, because the sorted set of the score is a double domain, the accuracy of its expression is actually limited. If this accuracy limit is exceeded, then no matter the rank of the order is meaningless.
    The representation of a Double field

    1bit (sign bit)    11bits (digit digit)    52bits (digits) value of    floating-point = significand x base ^ exponent, with sign
   
     (floating point) value =      mantissa    x base    ^ exponent, (plus sign)
   

    the absolute precision of a double is 15 bits .
    In the mapping function, remember the upper limit of 15 bits . Pre-set the ranking of the leaderboard for a total of 12 bits (2-bit game score value, 10-bit Unix era seconds), which is satisfied with the requirements.

Summarize:
    There are many articles on the online redis sorted set used for leaderboards, but the real case is not much. This multilevel sort may be more common in applications.

Public Number & Games sites:
Personal public Number: Wooden purpose H5 game world

　　

Personal Game Folio site (still under construction ...): www.mmxfgame.com, also direct IP access : http://120.26.221.54/. 

Summary of actual combat based on Redis leaderboard