Use Redis as the cache "go" for MySQL database

To use Redis as a Mysql database cache, two problems must be solved. First, you should determine what data structure to use to store data from Mysql; after determining the data structure, you must also consider what identifier is used as the key of the data structure.
Intuitively, the data in Mysql is stored in tables; more microscopically, these tables are stored in rows. Each time you execute a select query, Mysql will return a result set, this result set consists of several rows. Therefore, a natural idea is to find a data structure corresponding to Mysql rows in Redis. Five basic data structures are provided in Redis, namely string, list, hash, set and sorted set. After investigation, it was found that there are two types of data structures suitable for storing rows, namely string and hash.
To store Mysql's row data into a string, you first need to format the row data. In fact, each row of the result set can be regarded as a set of key-value pairs consisting of field names and their corresponding values. This key-value pair structure is easy to remind us of the Json format. Therefore, the Json format is selected here as the formatting template for each row of the result set. According to this idea, we can format the result set into several Json objects, and convert the Json object into a string and store it in Redis code:
view sourceprint?
01.
// This function converts each line in the result set into a Json format string and stores it in the STRING structure of Redis,
02.
// The STRING key should contain the result set identifier and STRING number in the form of "cache.string: 123456: 1"
03.
string Cache2String (sql :: Connection * mysql_connection,
04.
redisContext * redis_connection,
05.
sql :: ResultSet * resultset,
06.
const string & resultset_id, int ttl) {
07.
if (resultset-> rowsCount () == 0) {
08.
throw runtime_error ("FAILURE-no rows");
09.
}
10.
// The prefix of the STRING key contains the identifier of the result set
11.
string prefix ("cache.string:" + resultset_id + ":");
12.
unsigned int num_row = 1; // STRING number, attached to the end of STRING key, starting from
13.
sql :: ResultSetMetaData * meta = resultset-> getMetaData ();
14.
unsigned int num_col = meta-> getColumnCount ();
15.
// Store all STRING keys corresponding to all rows in the result set into the SET, the SET key contains the identifier of the result set
16.
string redis_row_set_key ("resultset.string:" + resultset_id);
17.
redisReply * reply;
18.
string ttlstr;
19.
stringstream ttlstream;
20.
ttlstream << ttl;
twenty one.
ttlstr = ttlstream.str ();
twenty two.
resultset-> beforeFirst ();
twenty three.
// Convert each line in the result set to a Json format string, and store these Json strings in STRING,
twenty four.
// Each STRING corresponds to a row in the result set
25.
while (resultset-> next ()) {
26.
string redis_row_key; // STRING key name, consisting of prefix and STRING number
27.
stringstream keystream;
28.
keystream << prefix << num_row;
29.
redis_row_key = keystream.str ();
30.
Json :: Value row;
31.
for (int i = 1; i <= num_col; ++ i) {
32.
string col_label = meta-> getColumnLabel (i);
33.
string col_value = resultset-> getString (col_label);
34.
row [col_label] = col_value;
35.
}
36.
Json :: FastWriter writer;
37.
string redis_row_value = writer.write (row);
38.
// Store the pair of STRING key and corresponding value in Json format into Redis
39.
reply = static_cast <redisReply *> (redisCommand (redis_connection,
40.
"SET% s% s",
41.
redis_row_key.c_str (),
42.
redis_row_value.c_str ()));
43.
freeReplyObject (reply);
44.
// Add STRING key to SET
45.
reply = static_cast <redisReply *> (redisCommand (redis_connection,
46.
"SADD% s% s",
47.
redis_row_set_key.c_str (),
48.
redis_row_key.c_str ()));
49.
freeReplyObject (reply);
50.
// Set the expiration time of STRING
51.
reply = static_cast <redisReply *> (redisCommand (redis_connection,
52.
"EXPIRE% s% s",
53.
redis_row_key.c_str (),
54.
ttlstr.c_str ()));
55.
freeReplyObject (reply);
56.
++ num_row;
57.
}
58.
// Set the expiration time of SET
59.
reply = static_cast <redisReply *> (redisCommand (redis_connection,
60.
"EXPIRE% s% s",
61.
redis_row_set_key.c_str (),
62.
ttlstr.c_str ()));
63.
freeReplyObject (reply);
64.
return redis_row_set_key; // Return SET key, so that other functions can get the content of the SET
65.
}


To store Mysql row data in the hash, the process is much more intuitive than storing the data in the string. This is determined by the structural nature of the hash—the hash itself is a collection of key-value pairs: a “parent key” contains many “child keys”, and each “child key” corresponds to a value. According to the previous analysis, each row in the result set is actually a set of key-value pairs. The Redis key-value pair collection means that the Mysql key-value pair collection should be more appropriate: for a row in the result set, the field corresponds to the hash "subkey", and the value corresponding to the field is the value corresponding to the hash "subkey" One row of the result set corresponds to exactly one hash. The implementation code of this idea is as follows:
view sourceprint?
01.
// This function stores each row in the result set into a HASH structure. The HASH key should include the result set identifier and HASH number,
02.
// Shaped like "cache.string: 123456: 1"
03.
string Cache2Hash (sql :: Connection * mysql_connection,
04.
redisContext * redis_connection,
05.
sql :: ResultSet * resultset,
06.
const string & resultset_id, int ttl) {
07.
if (resultset-> rowsCount () == 0) {
08.
throw runtime_error ("FAILURE-no rows");
09.
}
10.
// The prefix of the HASH key contains the identifier of the result set
11.
string prefix ("cache.hash:" + resultset_id + ":");
12.
unsigned int num_row = 1; // HASH number, attached to the end of HASH key, starting from
13.
sql :: ResultSetMetaData * meta = resultset-> getMetaData ();
14.
unsigned int num_col = meta-> getColumnCount ();
15.
// Store all HASH keys corresponding to all rows in the result set into the SET, the SET key contains the identifier of the result set
16.
string redis_row_set_key ("resultset.hash:" + resultset_id);
17.
redisReply * reply;
18.
string ttlstr;
19.
stringstream ttlstream;
20.
ttlstream << ttl;
twenty one.
ttlstr = ttlstream.str ();
twenty two.
// Each row in the result set corresponds to a HASH, all rows in the result set are stored in the corresponding HASH
twenty three.
resultset-> beforeFirst ();
twenty four.
while (resultset-> next ()) {
25.
string redis_row_key; // HASH key name, consisting of prefix and HASH number
26.
stringstream keystream;
27.
keystream << prefix << num_row;
28.
redis_row_key = keystream.str ();
29.
for (int i = 1; i <= num_col; ++ i) {
30.
string col_label = meta-> getColumnLabel (i);
31.
string col_value = resultset-> getString (col_label);
32.
// Save the field name and corresponding value of a row in the result set to HASH
33.
reply = static_cast <redisReply *> (redisCommand (redis_connection,
34.
"HSET% s% s% s",
35.
redis_row_key.c_str (),
36.
col_label.c_str (),
37.
col_value.c_str ()));
38.
freeReplyObject (reply);
39.
}
40.
// Add HASH key to SET
41.
reply = static_cast <redisReply *> (redisCommand (redis_connection,
42.
"SADD% s% s",
43.
redis_row_set_key.c_str (),
44.
redis_row_key.c_str ()));
45.
freeReplyObject (reply);
46.
// Set the expiration time of HASH
47.
reply = static_cast <redisReply *> (redisCommand (redis_connection,
48.
"EXPIRE% s% s",
49.
redis_row_key.c_str (),
50.
ttlstr.c_str ()));
51.
freeReplyObject (reply);
52.
++ num_row;
53.
}
54.
// Set the expiration time of SET
55.
reply = static_cast <redisReply *> (redisCommand (redis_connection,
56.
"EXPIRE% s% s",
57.
redis_row_set_key.c_str (),
58.
ttlstr.c_str ()));
59.
freeReplyObject (reply);
60.
return redis_row_set_key; // Return SET key, so that other functions can get the content of the SET
61.
}

So far, we have given two solutions for storing Mysql result sets. This is the first question we asked at the beginning of the article, namely, which data structure to choose to store Mysql result sets. The next article will study the second problem, which is the identifier selection of data structure keys.
 

 

After caching Mysql result set into Redis string or hash structure, we are faced with a new problem, namely how to name these strings or hashes, that is how to determine their keys. Because the rows corresponding to these data structures belong to a certain result set, if you can find a method to uniquely identify the result set, then you only need to assign a unique sequence number to these data structures, and then combine the result set identifier with the sequence number When you get up, you can uniquely identify a data structure. Therefore, the problem of naming character strings and hashes becomes the problem of determining the identifier of the result set.
After investigation, we found a more general method for determining the identifier of the result set. As we know, the result set data cached in Redis is obtained from Mysql using sql statements such as select. The same query statement will generate the same result set (the order of each record in the result set is not discussed here for now). This property can be used to determine the unique identifier of the result set. Of course, it is not advisable to simply use the entire sql statement as the result set identifier. One obvious reason is that unprocessed sql query statements contain several spaces, and Redis keys do not allow spaces. At this time, we need a function that can convert the SQL statement into a unique identifier. Usually, this function is completed by the hash function, and many algorithms including MD5, SHA series and other encrypted hash functions can achieve this purpose.
After determining the result set identifier, the idea of reading data from Redis or writing data to Redis is clear. For a data request in the form of a SQL statement, first calculate the MD5 of the statement and get the result set identifier based on it, and then use the identifier to find the result set in Redis. Note that each row in the result set has a corresponding key, and these keys are stored in a Redis collection structure. This set happens to correspond to the required result set, so the key of the set must contain the result set identifier. If there is no such a set in Redis, it means that the result set you are looking for is not in Redis, so you need to execute the corresponding SQL statement, query the corresponding result set in Mysql, and then follow the above method to each row in the result set Store in Redis as a string or hash. The code to find the corresponding result set in Redis is as follows:
view sourceprint?
01.
// This function queries the corresponding result set in Redis according to the sql statement, and returns the key of the data structure corresponding to each row in the result set
02.
vector <string> GetCache (sql :: Connection * mysql_connection,
03.
redisContext * redis_connection,
04.
const string & sql, int ttl, int type) {
05.
vector <string> redis_row_key_vector;
06.
string resultset_id = md5 (sql); // Calculate the md5 of the SQL statement, which is the key to uniquely identify the result set
07.
// When type == 1, the function will query the corresponding STRING set or write the result set to several STRING
08.
string cache_type = (type == 1)? "string": "hash";
09.
// Synthesize SET key based on type information and result set identifier
10.
string redis_row_set_key = "resultset." + cache_type + ":" + resultset_id;
11.
redisReply * reply;
12.
// Try to get all the keys saved in SET from reply
13.
reply = static_cast <redisReply *> (redisCommand (redis_connection,
14.
"SMEMBERS% s",
15.
redis_row_set_key.c_str ()));
16.
if (reply-> type == REDIS_REPLY_ARRAY) {
17.
// If the SET you are looking for does not exist, it means there is no corresponding result set in Redis, you need to call Cache2String or
18.
// The Cache2Hash function pulls data from Mysql to Redis
19.
if (reply-> elements == 0) {
20.
freeReplyObject (reply);
twenty one.
sql :: Statement * stmt = mysql_connection-> createStatement ();
twenty two.
sql :: ResultSet * resultset = stmt-> executeQuery (sql);
twenty three.
if (type == 1) {
twenty four.
redis_row_set_key = Cache2String (mysql_connection, redis_connection,
25.
resultset, resultset_id, ttl);
26.
} else {
27.
redis_row_set_key = Cache2Hash (mysql_connection, redis_connection,
28.
resultset, resultset_id, ttl);
29.
}
30.
// Try again to get all the keys saved in SET from reply
31.
reply = static_cast <redisReply *> (redisCommand (redis_connection,
32.
"SMEMBERS% s",
33.
redis_row_set_key.c_str ()));
34.
delete resultset;
35.
delete stmt;
36.
}
37.
// Store each STRING or HASH key in SET into redis_row_key_vector
38.
string redis_row_key;
39.
for (int i = 0; i <reply-> elements; ++ i) {
40.
redis_row_key = reply-> element [i]-> str;
41.
redis_row_key_vector.push_back (redis_row_key);
42.
}
43.
freeReplyObject (reply);
44.
} else {
45.
freeReplyObject (reply);
46.
throw runtime_error ("FAILURE-SMEMBERS error");
47.
}
48.
return redis_row_key_vector;
49.
}

Now we have mastered the method of determining the result set identifier in Redis and the key of each data structure. The next article will study the sorting and paging of result sets in Redis.
Before implementing the cache sorting function, you must first understand the rationality of this function. May wish to think about it, since you can sort in the database, why do you need to put the sorting function in the cache? Here are a brief summary of two reasons: first, sorting will increase the load on the database and it is difficult to support highly concurrent applications; second, sorting in the cache will not encounter the problem of table locking. Redis happens to provide a sorting function, so that we can easily implement cache sorting.
The SORT command is used to implement the sorting function in Redis. The command provides various parameters to sort lists, sets and ordered sets. The SORT command format is as follows:
view sourceprint?
1.
SORT key [BY pattern] [LIMIT offset count] [GET pattern [GET pattern ...]] [ASC | DESC] [ALPHA] [STORE destination]

The BY parameter is used to specify the sort field, and its function is similar to the order by in SQL. For lists and collections, it is often meaningless to sort only by their values. Take the collection returned by the function Cache2Hash as an example (actually, the collection keys are returned). The collection stores a series of complete hash keys, which are only sorted according to these keys. Obviously not very useful. This is because the actual storage of row data is the hash structure itself, not the hash key. Assuming that the collection key is "resultset.hash: 123456", the hash structure corresponding to each hash key in the collection has a field named "timestamp", and now all the hash keys in the collection should be processed according to the timestamp field Sort, at this time, just execute the following command:
view sourceprint?
1.
SORT resultset.hash: 123456 BY *-> timestamp

As you can see from the above example, the true power of BY is that it allows SORT commands to be sorted according to the external field of a specified external key. SORT replaces the first "*" after the BY parameter with each value in the collection resultset.hash: 123456 (that is, each hash key), and obtains its value according to the field given after "->", and finally according to These field values sort the hash keys.
The LIMIT parameter is used to limit the number of elements returned after sorting, and the function is similar to the limit in SQL. This parameter accepts two other parameters, namely offset and count. LIMIT offset count means to skip the first offset elements and return successive count elements afterwards. It can be seen that the LIMIT parameter can be used to implement the paging function.
The GET parameter is used to return the specified field value. Taking the collection resultset.hash: 123456 as an example, after using the BY parameter to sort all hash keys in the collection according to the timestamp field in the hash structure, the SORT command returns all sorted hash keys. If a request needs not a key but some field values, then the GET parameter is used to make the SORT command return the specified field value. Assuming that in addition to the timestamp field, there is also a field named "id" in the hash structure corresponding to each hash key in the collection, the following command can be used to make SORT return the hash corresponding to each hash key sorted by timestamp The timestamp and id values in the Greek structure:
view sourceprint?
1.
SORT resultset.hash: 123456 BY *-> timestamp GET *-> timestamp GET *-> id

SORT replaces the first "*" after the GET parameter with each value in the collection resultset.hash: 123456 (that is, each hash key), and uses it as the return value. It is worth noting that using GET # can get the hash key itself in the collection.
The ASC and DESC parameters are used to specify the sort order (the default is ASC, that is, from low to high), and the ALPHA parameter is used to arrange non-numeric elements in lexicographic order.
The STORE parameter is used to store the return value of the SORT command, that is, the sorting result, into a specified list. After adding the STORE parameter, the return value of the SORT command becomes the number of sorting results.
The following code implements the process of sorting the hash keys in the collection according to a certain field of the hash, and storing the results in the list:
view sourceprint?
01.
// This function sorts all the HASH keys in the collection according to a field in the HASH corresponding to the HASH key,
02.
// The sorting result is stored in a LIST structure, the LIST key should contain the result set identifier and sorting field identifier,
03.
// shaped like "sorted: 123456: 1234"
04.
string SortHash (sql :: Connection * mysql_connection,
05.
redisContext * redis_connection,
06.
const string & resultset_id,
07.
const string & sort_field,
08.
int offset, int count, int order, int ttl) {
09.
// Only consider SET to store HASH key
10.
string redis_row_set_key = "resultset.hash:" + resultset_id;
11.
redisReply * reply;
12.
// Check if SET exists
13.
reply = static_cast <redisReply *> (redisCommand (redis_connection,
14.
"EXISTS% s",
15.
redis_row_set_key.c_str ()));
16.
if (reply-> integer == 0) {
17.
freeReplyObject (reply);
18.
throw runtime_error ("FAILURE-no resultsets");
19.
} else {
20.
freeReplyObject (reply);
twenty one.
}
twenty two.
string field_md5 = md5 (sort_field); // Use MD5 to exclude the effects of spaces in sort fields
twenty three.
// Store the sorting result in the LIST
twenty four.
string redis_sorted_list_key = "sorted:" + resultset_id + ":" + field_md5;
25.
string by ("*->" + sort_field); // Determine the sort field
26.
string ord = (order == 1)? "ASC": "DESC"; // Order == 1 in ascending order; otherwise descend
27.
stringstream ofsstream, cntstream;
28.
ofsstream << offset;
29.
cntstream << count;
30.
// Execute the sorting command and store the sorting result in LIST
31.
reply = static_cast <redisReply *> (redisCommand (
32.
redis_connection,
33.
"SORT% s BY% s LIMIT% s% s GET% s ALPHA STORE% s",
34.
redis_row_set_key.c_str (),
35.
by.c_str (),
36.
ofsstream.str (). c_str (),
37.
cntstream.str (). c_str (),
38.
"#",
39.
redis_sorted_list_key.c_str ()));
40.
freeReplyObject (reply);
41.
stringstream ttlstream;
42.
ttlstream << ttl;
43.
// Set the expiration time of LIST
44.
reply = static_cast <redisReply *> (redisCommand (redis_connection,
45.
"EXPIRE% s% s",
46.
redis_sorted_list_key.c_str (),
47.
ttlstream.str (). c_str ()));
48.
freeReplyObject (reply);
49.
return redis_sorted_list_key; // return LIST key, so that other functions can get the content of the LIST

Obviously, sorting the hash keys in the result set is more intuitive and convenient than sorting the string keys. With the help of the sorting function, it is easy to implement querying the sorted result set in Redis. The code is as follows:
view sourceprint?
01.
// This function queries and sorts the corresponding result set in Redis according to the SQL statement and sorting parameters, and finally returns
02.
// HASH key after sorting
03.
vector <string> GetSortedCache (sql :: Connection * mysql_connection,
04.
redisContext * redis_connection,
05.
const string & sql, const string & sort_field,
06.
int offset, int count, int order, int ttl) {
07.
vector <string> redis_row_key_vector;
08.
redisReply * reply;
09.
string resultset_id = md5 (sql); // result set identifier
10.
string field_md5 = md5 (sort_field); // Sort field identifier
11.
// Try to get all the HASH keys in LIST
12.
string redis_sorted_list_key = "sorted:" + resultset_id + ":" + field_md5;
13.
// Try to get all the HASH keys in LIST
14.
reply = static_cast <redisReply *> (redisCommand (redis_connection,
15.
"LRANGE% s% s% s",
16.
redis_sorted_list_key.c_str (),
17.
"0",
18.
"-1"));
19.
if (reply-> type == REDIS_REPLY_ARRAY) {
20.
// If LIST does not exist, call Cache2Hash function to pull data from Mysql to Redis, and then call SortHash function
twenty one.
// Sort the result set and store the sorted HASH key in LIST
twenty two.
if (reply-> elements == 0) {
twenty three.
freeReplyObject (reply);
twenty four.
sql :: Statement * stmt = mysql_connection-> createStatement ();
25.
sql :: ResultSet * resultset = stmt-> executeQuery (sql);
26.
Cache2Hash (mysql_connection, redis_connection, resultset,
27.
resultset_id, ttl);
28.
redis_sorted_list_key = SortHash (mysql_connection, redis_connection,
29.
resultset_id, sort_field, offset,
30.
count, order, ttl);
31.
// Try again to get all the HASH keys in LIST
32.
reply = static_cast <redisReply *> (redisCommand (
33.
redis_connection,
34.
"LRANGE% s% s% s",
35.
redis_sorted_list_key.c_str (),
36.
"0",
37.
"-1"));
38.
delete resultset;
39.
delete stmt;
40.
}
41.
// Store all HASH keys in LIST into redis_row_key_vector
42.
string redis_row_key;
43.
for (int i = 0; i <reply-> elements; ++ i) {
44.
redis_row_key = reply-> element [i]-> str;
45.
redis_row_key_vector.push_back (redis_row_key);
46.
}
47.
freeReplyObject (reply);
48.
} else {
49.
freeReplyObject (reply);
50.
throw runtime_error ("FAILURE-LRANGE error");
51.
}
52.
return redis_row_key_vector;
53.
}

In this way, a simple sorting operation on the result set in Redis is realized.
 

Use Redis as a cache for Mysql database [transfer]