Beginner Redis (3)--using Redis as a cache for MySQL databases

After caching the MySQL result set to a Redis string or hash structure, we face a new question about how to name these strings or hashes, that is, how to determine their keys. Because the rows for these data structures belong to a result set, if you can find a method that uniquely identifies the result set, you only need to assign a unique ordinal number to the data structure, and then combine the result set identifier with the ordinal to uniquely identify a data structure. As a result, the problem of naming strings and hashes translates to the problem of determining the outcome set identifier.

A more general method to determine the result set identifier is found through investigation. As we know, the result set data in Redis is obtained from MySQL using SQL statements such as SELECT. The same query statement produces the same result set (the order of each record in the result set is not discussed here for the moment), which is exactly what you can use to determine the unique identifier of the result set. Of course, it is not advisable to simply take the entire SQL statement as the result set identifier, and one obvious reason is that the unprocessed SQL query statements contain several spaces, while Redis keys do not allow spaces. At this point, we need a function that can convert the SQL statement to a unique identifier. Typically, this function is done by a hash function, and many algorithms, including cryptographic hash functions such as the Md5,sha series, can achieve this goal.

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 a SQL statement format, the MD5 of the statement is computed first and the result set identifier is obtained, and the identifier is then used to find the result set in Redis. Notice that each row in the result set has a corresponding key, which is stored in a REDIS collection structure. This collection corresponds exactly to the desired result set, so the key for the collection must contain the result set identifier. If such a collection does not exist in Redis, it means that the result set you are looking for is not in Redis, so you need to execute the appropriate SQL statement, query the corresponding result set in MySQL, and then follow the above method to store each row in the result set as a string or hash into Redis. The code for finding the corresponding result set in Redis is as follows:

// 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
vector <string> GetCache (sql :: Connection * mysql_connection,
                      redisContext * redis_connection,
                      const string & sql, int ttl, int type) {
  vector <string> redis_row_key_vector;
  string resultset_id = md5 (sql); // Calculate the md5 of the SQL statement, which is the key to uniquely identify the result set
  // When type == 1, the function will query the corresponding STRING set or write the result set to several STRING
  string cache_type = (type == 1)? "string": "hash";
  // Synthesize SET key based on type information and result set identifier
  string redis_row_set_key = "resultset." + cache_type + ":" + resultset_id;
  redisReply * reply;
  // Try to get all the keys saved in SET from reply
  reply = static_cast <redisReply *> (redisCommand (redis_connection,
                                               "SMEMBERS% s",
                                               redis_row_set_key.c_str ()));
  if (reply-> type == REDIS_REPLY_ARRAY) {
// 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
// The Cache2Hash function pulls data from Mysql to Redis
    if (reply-> elements == 0) {
      freeReplyObject (reply);
      sql :: Statement * stmt = mysql_connection-> createStatement ();
      sql :: ResultSet * resultset = stmt-> executeQuery (sql);
      if (type == 1) {
        redis_row_set_key = Cache2String (mysql_connection, redis_connection,
                                         resultset, resultset_id, ttl);
      } else {
        redis_row_set_key = Cache2Hash (mysql_connection, redis_connection,
                                       resultset, resultset_id, ttl);
      }
// Try again to get all the keys saved in SET from reply
      reply = static_cast <redisReply *> (redisCommand (redis_connection,
                                                   "SMEMBERS% s",
                                                   redis_row_set_key.c_str ()));
      delete resultset;
      delete stmt;
    }
// Store each STRING or HASH key in SET into redis_row_key_vector
    string redis_row_key;
    for (int i = 0; i <reply-> elements; ++ i) {
      redis_row_key = reply-> element [i]-> str;
      redis_row_key_vector.push_back (redis_row_key);
    }
    freeReplyObject (reply);
  } else {
    freeReplyObject (reply);
    throw runtime_error ("FAILURE-SMEMBERS error");
  }
  return redis_row_key_vector;
}

Now we have mastered the method of determining the result set identifiers in Redis and the keys for each data structure. The next article will examine the sorting and paging problems of the result set in Redis.

Beginner Redis (3)--using Redis as a cache for MySQL databases