4. (C++) Use redis or MySQL to implement a shooting game ranking system

1. MySQL implementation method

Suppose we want to design a ranking system, which must involve two major types of data: weapon data and non-weapon general data. They usually have a common attribute: that is, the primary key is unique, such as the player’s numerical number, usually In MySQL, it is an auto-incrementing unsigned integer field.

Non-weapon general data: It can be understood as data that has no connection with weapons, such as player ID, nickname, signature, registration time, login time, etc. In MySQL, it is similar to the following:

Weapon data: The type corresponding to each weapon here is equivalent to a field in MYSQL, roughly similar to the following:

This form data usually only increases and does not decrease. If MYSQL is used as a ranking system, when the number of players increases exponentially, it will expose two problems:

1. The efficiency is too low: a large number of select and update statements may appear very bloated;

2. Not easy to expand: If you need to add new weapon data types (fields) in real time, it may be inconvenient

At this time, it will be particularly suitable to use redis for data storage, as if it was born to do this!

2. Use Redis to implement the method

1. String encoding conversion before storage

It is generally not recommended to store plaintext strings directly in REDIS. It is recommended to use web page encoding to store them. The source code of the conversion function is as follows:

//Hexadecimal character table
const UCHAR g_szHexTable[] = { '0', '1', '2', '3', '4', '5', '6', \ '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F ' };

//
// Get the web page encoding
//
int getUrlCode(const UCHAR *pszBuff, const int nSize, char *pszOutput, const int nMaxSize)
{
int i = 0;
char *pszTemp = NULL;

if (pszBuff == NULL || nSize == 0)
return 0;

if (pszOutput == NULL || nMaxSize == 0)
return 0;

memset(pszOutput, 0x00, nMaxSize);
pszTemp = pszOutput;

for (int i = 0; i < nSize; i + + )
{
pszTemp[0] = '%';
pszTemp[1] = '%';
pszTemp[2] = g_szHexTable[(pszBuff[i] >> 4) & amp; 0x0F];
pszTemp[3] = g_szHexTable[pszBuff[i] & amp; 0x0F];
pszTemp + = 4;
}

return nSize * 4;
}

// test function
void test_url_code()
{
UCHAR szInput[] = "I want to hit 10!";
char szOutput[128] = { 0x00 };

getUrlCode(szInput, strlen((char *)szInput), szOutput, 128);
printf("%s\\
", szOutput);
}

2. User table operations

We can use the Redis Hset command to assign values to fields in the hash table; if the hash table does not exist, a new hash table is created and the HSET operation is performed; if the field already exists in the hash table, the old value will covered; covered

HSET KEY_NAME FIELD VALUE

KEY_NAME: key value
FIELD: field
VALUE: value (character transfer, number, floating point)

When we use a combination method to distinguish the key value of KEY_NAME:

// KEY_NAME = type : id
#define STATS_ALL "a" // Overall list a:1
#define STATS_YEAR "y" // Year list y2022:1
#define STATS_MONTH "m" // Monthly list m202205:1
#define STATS_DAY "d" // Daily list d20220501:1
#define STATS_SESSION "s" // Season standings s1_2022:1
#define STATS_USER "user" // User information user:1

Then when the modified statement in SQL modifies the user name, it looks like this:

UPDATE dbuser SET name = 'aa' WHERE index = 1;
UPDATE dbuser SET signature = 'I want to type 10! ' WHERE index = 1;
UPDATE dbuser SET regdate = '2023/10/1' WHERE index = 1;

And in Redis it should look like this:

// Note that the KEY here is combined

//Set the game nickname of player ID=1 to "aa"
HSET user:1 name %a%a

//Set the game nickname for player ID=1 as "I want to fight 10!"
HSET user:1 signature %?%?%?%?%?%?%1%0%?%?%?%? 

// Set the registration time of player ID=1 to "2023/10/1"
HSET user:1 regdate %2%0%2%3%/%1%0%/%1

//Set the experience value time of player ID=1 to 100
HSET user:1 exp 100


//The above redis command can also be optimized into the following sentence and executed
HMSET user:1 name %a%a signature %?%?%?%?%?%?%1%0%?%?%?%? regdate %2%0%2%3%/%1%0% /%1 exp 100

The C++ code snippet is as follows:

 wsprintf(szCommand, /*HSET %s:%u %s %s*/XorStr<0xAB, 17, 0x72ACBFF2>("\xE3\xFF\xE8\xFA\x8F\x95 \xC2\x88\x96\xC1\x95\x93\xC4\x98\x9C\xC9" + 0x72ACBFF2).s, STATS_USER, pInfo->m_nDbUid, g_szUserInfo[nPos], szValue);

reply = (redisReply *)redisCommand(pRedis, szCommand);
if (NULL == reply) goto STEP_END;
if (REDIS_REPLY_ERROR == reply->type)goto STEP_END;
bRet = TRUE;

STEP_END:
if (NULL != reply)
{
freeReplyObject(reply);
reply = NULL;
}

3. Weapon data operation

In this regard, data operations are relatively easy to implement, and the number of fields can be expanded at any time, unlike the fixed number like SQL; in addition, the numerical operations on the fields of weapons are self-added and cumulative, without adding or deleting operations. , for example, when the gateway server submits a command that player ID=1, weapon=2, and the cumulative number of enemies killed increases by 3, the corresponding SQL statement is usually as follows:

//Modify the overall ranking data
UPDATE dbweapon_all SET kill = kill + 3 where index = 1 AND wid = 2;

//Modify annual ranking data
UPDATE dbweapon_2023 SET kill = kill + 3 where index = 1 AND wid = 2;

//Modify season ranking data
UPDATE dbweapon_s1_2023 SET kill = kill + 3 where index = 1 AND wid = 2;

And in Redis it should look like this:

// HINCRBY command prototype
// Reference address: https://www.runoob.com/redis/hashes-hincrby.html
HINCRBY KEY_NAME FIELD_NAME INCR_BY_NUMBER

// redis executes 3 commands
HINCRBY a:1 1:kill 3
HINCRBY y2023:1 1:kill 3
HINCRBY s1_2023:1 1:kill 3

Key value KEY_NAME annotation:
a:1 represents the overall list
y2023:1 Appearance Annual List
s1_2023:1 represents the 2023 first season rankings

Field FIELD_NAME comments:
1:kill The weapon number is 1 and the type is kill.

The field INCR_BY_NUMBER represents the self-increasing or self-decrementing value

4. MySQL modify points query ranking

Generally speaking, you need to write a complex MySQL statement to calculate the player’s points, which is roughly as follows:

//Modify points
UPDATE SET db_user exp = exp + 3.0 WHERE index = 1;

// Query points
SELECT exp FROM db_user WHERE index = 1;

// Query ranking
SELECT COUNT(*) + 1 AS rank
FROM db_user
WHERE exp >= (SELECT exp FROM db_user WHERE index = 1);

There is a ready-made one in redis itself. Usually when the weapon data is processed in the gateway, the points value of the list will be modified at the same time.

5. Redis Zincrby Modify Points

The Redis Zincrby command adds increment to the score of the specified member in the ordered set. You can pass a negative value increment to subtract the corresponding value from the score. For example, ZINCRBY key -5 member means subtracting 5 from the score value of the member. ; When the key does not exist, or the score is not a member of the key, the ZINCRBY key increment member is equivalent to the ZADD key increment member; when the key is not an ordered set type, an error is returned; the score value can be an integer value or a double-precision floating point number.

The command prototype is as follows:

ZINCRBY key increment member

key is the same as the weapon list above
increment Points that increase or decrease automatically
member player id


// Assuming that weapon No. 1 kills an enemy and accumulates 3 points, the corresponding command is as follows:
ZINCRBY a:1 3.0 1
ZINCRBYy2023:1 3.0 1
ZINCRBY s1_2023:1 3.0 1

The C++ code snippet is as follows:

 // Overall ranking record
wsprintf(szCommand, /*ZINCRBY %s %s %u*/XorStr<0x09, 17, 0x5A9256F5>("\x53\x43\x45\x4F\x5F\x4C\x56\x30 \x34\x61\x33\x31\x66\x36\x32\x6D" + 0x5A9256F5).s, STATS_ALL, szScore, nDbUid);

bRet = redisGetReply(m_pRedis, (void **) & amp;reply);
if (bRet != REDIS_OK)
goto STEP_END;

if (reply)
{
freeReplyObject(reply);
reply = NULL;
}

6. Redis Zscore query points

Redis ordered set (sorted set), the Redis Zscore command returns the score value of the member in the sorted set. If the member element is not a member of the sorted set key, or key does not exist, nil is returned.

The command prototype is as follows:

ZSCORE key member

key is the same as the weapon list above
member player id


//Query points command for different rankings:
ZSCORE a:1 1
ZSCORE y2023:1 1
ZSCORE s1_2023:1 1

The C++ operation code snippet is as follows:

 // Get player points (only the overall ranking record is obtained in the plug-in)
wsprintf(szCommand, /*ZSCORE %s %u*/XorStr<0x40, 13, 0xF87B9E21>("\x1A\x12\x01\x0C\x16\x00\x66\x62\ x3B\x69\x6F\x3E" + 0xF87B9E21).s, STATS_ALL, nDbUid);
reply = (redisReply *)redisCommand(pRedis, szCommand);
if (NULL == reply) goto STEP_END;
if (REDIS_REPLY_ERROR == reply->type)goto STEP_END;
if (REDIS_REPLY_STRING == reply->type)
{
fScore = strtod(reply->str, NULL);
}
if (NULL != reply)
{
freeReplyObject(reply);
reply = NULL;
}

7. Redis Zrevrank query ranking

Redis ordered set (sorted set), the Redis Zrevrank command returns the ranking of members in the sorted set. The members of the ordered set are sorted by decreasing score (from large to small); the ranking is based on 0, that is, the member with the largest score is ranked 0; use the ZRANK command to get the members sorted by increasing score (from small to large) ranking.

The command prototype is as follows:

Zrevrank key member

key is the same as the weapon list above
member player id


// Query the ranking command of different lists:
ZREVRANK a:1 1
ZREVRANK y2023:1 1
ZREVRANK s1_2023:1 1

The C++ operation code snippet is as follows:

 // Get the player rankings (only the overall ranking records are obtained in the plug-in)
wsprintf(szCommand, /*ZREVRANK %s %u*/XorStr<0x01, 15, 0x9CCD219A>("\x5B\x50\x46\x52\x57\x47\x49\x43\ x29\x2F\x78\x2C\x28\x7B" + 0x9CCD219A).s, STATS_ALL, nDbUid);
reply = (redisReply *)redisCommand(pRedis, szCommand);
if (NULL == reply) goto STEP_END;
if (REDIS_REPLY_ERROR == reply->type)goto STEP_END;
if (REDIS_REPLY_NIL == reply->type)
{
nRank = pServer->m_nMaxPlayers;
bRet = TRUE;
}
if (REDIS_REPLY_INTEGER == reply->type)
{
nRank = (reply->integer & amp; 0xFFFFFFFF) + 1;
bRet = TRUE;
}

if (NULL != reply)
{
freeReplyObject(reply);
reply = NULL;
}

3. Summarize and avoid pitfalls

1. Try not to use fixed strings as KEY

When writing rankings, generally the character type “kill” is not used in the KEY of weapon data. Usually a number is used instead of aspect expansion. When expanding, you only need to modify the enum macro, weapon + data Typeto splice the KEY_NAME of %u:%u , similar to the following:

// push data category // command character points
enum EnumPushType{
EPT_UNKNOW = '@', // Unknown 64 @ + 0
EPT_KILL, // Kill 65 A + 2
EPT_SHOT, // Shoot 66 B + 0
EPT_HEADSHOT, // Headshot 67 C + 1
EPT_HIT, // hit 68 D + 0

EPT_DAMAGE, // Damage 69 E + 0
EPT_DEATH, // Death 70 F -2
EPT_FIRSTKILL, // First kill 71 G + 1
EPT_FIRSTDEATH, // first death 72 H + 0
EPT_BOMB_DEFUSION, // Demolish C4 73 I + 2

EPT_BOMB_PLANTING, // Install C4 74 J + 2
EPT_TIME_ONLINE, // Online 75 K + 0 + 0.002 minutes per second (7.2 minutes per hour)

EPT_KILL_WORLD, // Killed 76 L + 0
EPT_KILL_SELF, // Number of suicides 77 M + 0

EPT_MAX_PLAYER, //Maximum player 78 N + 0
EPT_RANK, // Current ranking 79 O + 0
EPT_SCORE, // Current score 80 P + 0

// Bodily harm
EPT_DMAGE_NONE, // Hit empty gun 81 Q + 0
EPT_DMAGE_HEAD, // Hit the head 82 R + 0
EPT_DMAGE_CHEST, // hit chest 83 S + 0
EPT_DMAGE_STOMACH, // Hit in the stomach 84 T + 0
EPT_DMAGE_LEFTARM, // Hit left arm 85 U + 0
EPT_DMAGE_RIGHTARM, // Hit the right arm 86 V + 0
EPT_DMAGE_LEFTEG, // Hit the left foot 87 W + 0
EPT_DMAGE_RIGHTEG, // Hit the right foot 88 X + 0
EPT_DMAGE_SHIELD, // hit shield 89 Y + 0

// Weapon + BKILL
EPT_BKILL, // Killed 90 Z + 0
EPT_BHEAD, // Headshot 91 [ + 0
\t
//Number of hits
EPT_HIT_NONE, // Hit empty gun 92 \ + 0
EPT_HIT_HEAD, // Hit the head 93 ] + 0
EPT_HIT_CHEST, // hit chest 94 ^ + 0
EPT_HIT_STOMACH, // Hit in the stomach 95 _ + 0
EPT_HIT_LEFTARM, // Hit left arm 96 ` + 0
EPT_HIT_RIGHTARM, // Hit the right arm 97 a + 0
EPT_HIT_LEFTEG, // Hit the left foot 98 b + 0
EPT_HIT_RIGHTEG, // Hit the right foot 99 c + 0
EPT_HIT_SHIELD, // hit shield 100 d + 0

//Melee parameters add by MT 2023-09-30
EPT_ROUND, // total round
EPT_RWIN_T, // Round: T wins after killing
EPT_RWIN_BOOM, // Round: T explodes to win
EPT_RWIN_CT, // Round: CT kill wins
EPT_RWIN_DEFUSE, // Round: CT explosion victory
EPT_RWIN_SAVED, // Round: Victory at the end of CT time
EPT_RWIN_RESCUE, // Round: CT rescues hostages and wins
EPT_RWIN_NOT_RESCUE, // Round: CT wins without rescuing the hostages
EPT_RWIN_TYPE1, // Round: Keep Victory 1
EPT_RWIN_TYPE2, // Round: Keep Victory 2

EPT_RLOSE, // failed round
EPT_REVEN, // draw round

//Contest parameters
EPT_SESSION, // total number of sessions
EPT_SWIN, // number of wins
EPT_SLOSE, // failed sessions
EPT_SEVEN, // draw games

EPT_MVP, // best times
EPT_RWS, //Contribution score per round (damage ratio)

//Kill statistics per round
EPT_KILL_0, // 0K Soy Sauce Bureau
EPT_KILL_1, // 1K
EPT_KILL_2, // 2K
EPT_KILL_3, // 3K
EPT_KILL_4, // 3K
EPT_KILL_5, // 5K
EPT_KILL_6, // 6K
EPT_KILL_7, // 7K
EPT_KILL_8, // 8K
EPT_KILL_9, // 9K
EPT_KILL_10, // 10K
EPT_KILL_11, // 11K
EPT_KILL_12, // 12K
EPT_KILL_13, // 13K
EPT_KILL_14, // 14K
EPT_KILL_15, // 15K
EPT_KILL_16, // 16K

// Endgame statistics
EPT_1V1, // 1v1
EPT_1V2, // 1v2
EPT_1V3, // 1v3
EPT_1V4, // 1v4
EPT_1V5, // 1v5
EPT_1V6, // 1v6
EPT_1V7, // 1v7
EPT_1V8, // 1v8
EPT_1V9, // 1v9
EPT_1V10, // 1v10
EPT_1V11, // 1v11
EPT_1V12, // 1v12
EPT_1V13, // 1v13
EPT_1V14, // 1v14
EPT_1V15, // 1v15
EPT_1V16, // 1v16

// Endgame information
EPT_1ROUND, // endgame games
EPT_1RWIN, // Endgame victory

EPT_ASSIST, // Number of assists
EPT_ADR, // Accumulated average actual damage proportion per game

//Wall penetration information
EPT_WALL_HIT, // Cumulative number of hits through walls
EPT_WALL_DAMAGE, // Accumulated shooting damage through walls
EPT_WALL_HEAD, // Cumulative number of headshots through walls
EPT_WALL_KILL, // Cumulative number of kills through walls

EPT_BWALL_HIT, // Cumulative number of hits through walls
EPT_BWALL_DAMAGE, // Accumulated damage from shooting through the wall
EPT_BWALL_HEAD, // Cumulative number of headshots shot through the wall
EPT_BWALL_KILL, // Cumulative number of kills by passing through the wall
};

This macro corresponds to a double array to calculate the score, so only one such function is needed as the gateway’s total interface:

//Submit ranking data
UINT redis_PushCommand(REDIS_SERVER *pServer, const UINT nDataType, const UINT nWeapon, UINT nDbUid, UINT nValue);

2. Try to use transactional reading and writing

Finally, please note that using transaction processing can speed up reading and writing efficiency by more than 10 times. The sample code is as follows:

 // The new version uses transactional reading to speed up
for (i = 0; i < MAX_WEAPONS; i + + )
{
nWeapon = i + EPT_UNKNOW;
for (j = 0; j < MAX_OPT_COUNT; j + + )
{
wsprintf(szCommand, /*HGET %s:%u %u:%u*/XorStr<0x18, 17, 0xF6DE906E>("\x50\x5E\x5F\x4F\x3C\x38\ x6D\x25\x05\x54\x02\x06\x51\x1F\x03\x52" + 0xF6DE906E).s, STATS_ALL, nDbUid, , EPT_UNKNOW + j, nWeapon);
redisAppendCommand(pRedis, szCommand); // transaction reading
}
}

    //Loop through all weapon data
for (i = 0; i < MAX_WEAPONS; i + + )
{
// Iterate through all parameters
for (j = 0; j < MAX_OPT_COUNT; j + + )
{
nRet = redisGetReply(pRedis, (void **) & amp;reply);
if (nRet != REDIS_OK)
continue;

if (reply->type == REDIS_REPLY_STRING)
{
sData.m_nValue[i][j] = atoi(reply->str);
}
if (reply->type == REDIS_REPLY_INTEGER)
{
sData.m_nValue[i][j] = reply->integer & amp; 0xFFFFFFFF;
}
}
}