Beginner Redis (2) using Redis as the cache for Mysql Databases

Two problems must be solved when Redis is used as the Mysql database cache. First, determine the data structure used to store data from Mysql. After determining the data structure, consider the identifier used as the key of the data structure. Intuitively, data in Mysql is stored by table. In a more microscopic view, these tables are stored by row. Each execution of selec

Two problems must be solved when Redis is used as the Mysql database cache. First, determine the data structure used to store data from Mysql. After determining the data structure, consider the identifier used as the key of the data structure. Intuitively, data in Mysql is stored by table. In a more microscopic view, these tables are stored by row. Each execution of selec

Two problems must be solved when Redis is used as the Mysql database cache. First, determine the data structure used to store data from Mysql. After determining the data structure, consider the identifier used as the key of the data structure.

Intuitively, data in Mysql is stored by table. In a more microscopic view, these tables are stored by row. Each time a select query is executed, Mysql returns a result set consisting of several rows. Therefore, a natural idea is to find a data structure corresponding to the Mysql row in Redis. Redis provides five basic data structures: string, list, hash, set, and sorted set ). After investigation, we found that there are two data structures suitable for storing rows: string and hash.

To store Mysql row data into a string, you must first format the row data. In fact, each row of the result set can be considered as a set of key-value pairs consisting of field names and their corresponding values. This type of key-Value Pair structure reminds us of the Json format. Therefore, the Json format is used as the format template for each row in the result set. Based on this idea, we can format the result set into several Json objects and convert the Json object into a string into the Redis code:

// This function converts each row in the result set into a Json STRING and stores it in the Redis STRING Structure. // The STRING key should contain the result set identifier and STRING number, the format is "cache. string: 123456: 1 "string Cache2String (SQL: Connection * mysql_connection, redisContext * redis_connection, SQL: ResultSet * resultset, const string & resultset_id, int ttl) {if (resultset-> rowsCount () = 0) {throw runtime_error ("FAILURE-no rows");} // STRING key prefix, string prefix ("cache. string: "+ resultset_id +": "); unsigned int num_row = 1; // STRING number, appended to the end of the STRING key, starting from 1 :: resultSetMetaData * meta = resultset-> getMetaData (); unsigned int num_col = meta-> getColumnCount (); // store all the STRING keys corresponding to all rows in the result SET to this SET, the SET key contains the result SET identifier string redis_row_set_key ("resultset. string: "+ resultset_id); redisReply * reply; string ttlstr; stringstream ttlstream; ttlstream <ttl; ttlstr = ttlstream. str (); resultset-> beforeFirst (); // convert each row in the result set into a Json STRING, and store the Json STRING into a STRING, // each STRING corresponds to a row in the result set while (resultset-> next () {string redis_row_key; // STRING key name, which consists of the stringstream keystream prefix and STRING number; keystream <prefix <num_row; redis_row_key = keystream. str (); Json: Value row; for (int I = 1; I <= num_col; ++ I) {string col_label = meta-> getColumnLabel (I ); string col_value = resultset-> getString (col_label); row [col_label] = col_value;} Json: FastWriter writer; string redis_row_value = writer. write (row); // Save the STRING key and Json value pairs to Redis reply = static_cast
 
  
(RedisCommand (redis_connection, "SET % s", redis_row_key.c_str (), redis_row_value.c_str (); freeReplyObject (reply); // Add the STRING key to SET reply = static_cast
  
   
(RedisCommand (redis_connection, "SADD % s", second (), redis_row_key.c_str (); freeReplyObject (reply); // set the STRING expiration time reply = static_cast
   
    
(RedisCommand (redis_connection, "EXPIRE % s", redis_row_key.c_str (), ttlstr. c_str (); freeReplyObject (reply); ++ num_row;} // SET the expiration time of SET reply = static_cast
    
     
(RedisCommand (redis_connection, "EXPIRE % s", redis_row_set_key.c_str (), ttlstr. c_str (); freeReplyObject (reply); return redis_row_set_key; // return the SET key for other functions to obtain the content in the SET}
    
   
  
 

It is more intuitive to store Mysql row data in hash than to store data in string. This is determined by the structure of hash. hash itself is a set of key-value pairs. A "parent key" contains many "subkeys ", each "subkey" corresponds to a value. According to the previous analysis, each row in the result set is actually a set of key-value pairs. Using a Redis key-value pair set to represent a Mysql key-value pair set should be no longer appropriate: for a row in the result set, the field corresponds to the hash "sub-key ", the value corresponding to the field is the value corresponding to the hash "subkey", that is, a row in the result set exactly corresponds to a hash. The implementation code of this idea is as follows:

// 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, // such as "cache. string: 123456: 1 "string Cache2Hash (SQL: Connection * mysql_connection, redisContext * redis_connection, SQL: ResultSet * resultset, const string & resultset_id, int ttl) {if (resultset-> rowsCount () = 0) {throw runtime_error ("FAILURE-no rows");} // HASH key prefix, string prefix ("cache. hash: "+ resultset_id +": "); unsigned int num_row = 1; // HASH number, appended to the end of the HASH key, starting from 1 :: resultSetMetaData * meta = resultset-> getMetaData (); unsigned int num_col = meta-> getColumnCount (); // store all HASH keys corresponding to all rows in the result SET to this SET, the SET key contains the result SET identifier string redis_row_set_key ("resultset. hash: "+ resultset_id); redisReply * reply; string ttlstr; stringstream ttlstream; ttlstream <ttl; ttlstr = ttlstream. str (); // each row in the result set corresponds to a HASH. All rows in the result set are saved to the corresponding HASH. resultset-> beforeFirst (); while (resultset-> next () {string redis_row_key; // HASH key name, which consists of a stringstream keystream composed of prefix and HASH number; keystream <prefix <num_row; redis_row_key = keystream. str (); for (int I = 1; I <= num_col; ++ I) {string col_label = meta-> getColumnLabel (I ); string col_value = resultset-> getString (col_label); // store the field names and corresponding values of a row in the result set to HASH reply = static_cast
 
  
(RedisCommand (redis_connection, "HSET % s", redis_row_key.c_str (), col_label.c_str (), col_value.c_str (); freeReplyObject (reply );} // Add the HASH key to SET reply = static_cast
  
   
(RedisCommand (redis_connection, "SADD % s", reset (), redis_row_key.c_str (); freeReplyObject (reply); // set the expiration time of HASH reply = static_cast
   
    
(RedisCommand (redis_connection, "EXPIRE % s", redis_row_key.c_str (), ttlstr. c_str (); freeReplyObject (reply); ++ num_row;} // SET the expiration time of SET reply = static_cast
    
     
(RedisCommand (redis_connection, "EXPIRE % s", redis_row_set_key.c_str (), ttlstr. c_str (); freeReplyObject (reply); return redis_row_set_key; // return the SET key for other functions to obtain the content in the SET}
    
   
  
 

So far, we have provided two solutions to store Mysql result sets. This is the first question we asked at the beginning, that is, the answer to the data structure we choose to store Mysql result sets. The next article will study the second problem, that is, the problem of data structure Key Identifier selection.