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

To use Redis as a MySQL database cache, 2 issues must be resolved. First, you should determine what data structure is used to store your MySQL-based information , and after determining the data structure, consider what identity is used as the key to the data structure.

Visually, the data in MySQL is stored on a table, and more microscopically, the tables are stored on a row. Each time a select query is executed, MySQL returns a result set that consists of several rows. So, a natural idea is to find a data structure in Redis that corresponds to a MySQL row. There are five basic data structures available in Redis, namely, string, list, hash (hash), collection (set), and ordered set (sorted set). After investigation, it is found that there are two kinds of data structures suitable for storing rows, namely, string and hash.

To put the MySQL row data into a string, you first need to format the row data. In fact, each row of the result set can be seen as a collection of key-value pairs consisting of field names and their corresponding values. This kind of key-value pair structure makes it easy to think of JSON format. Therefore, the JSON format is chosen as the format template for each row of the result set. Based on this idea, we can implement the code that formats the result set as several JSON objects and converts the JSON object into a string of Redis:

// This function converts each row in the result set into a Json format string and stores it into the STRING structure of Redis.
// The STRING key should contain the result set identifier and STRING number in the form "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");
  }
  // prefix of the STRING key, containing the identifier of the result set
  string prefix ("cache.string:" + resultset_id + ":");
  unsigned int num_row = 1; // STRING number, appended to the end of the STRING key, starting from
  sql :: ResultSetMetaData * meta = resultset-> getMetaData ();
  unsigned int num_col = meta-> getColumnCount ();
  // 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
  string redis_row_set_key ("resultset.string:" + resultset_id);
  redisReply * reply;
  string ttlstr;
  stringstream ttlstream;
  ttlstream << ttl;
  ttlstr = ttlstream.str ();
  resultset-> beforeFirst ();
  // Convert each line in the result set to a Json format string, store these Json strings in STRING,
  // Each STRING corresponds to a row in the result set
  while (resultset-> next ()) {
    string redis_row_key; // STRING key name, consisting of prefix and STRING number
    stringstream keystream;
    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);
// Store the STRING key and the corresponding value pair in Json format into Redis
    reply = static_cast <redisReply *> (redisCommand (redis_connection,
                                                 "SET% s% s",
                                                 redis_row_key.c_str (),
                                                 redis_row_value.c_str ()));
    freeReplyObject (reply);
    // Add STRING key to SET
    reply = static_cast <redisReply *> (redisCommand (redis_connection,
                                                 "SADD% s% s",
                                                 redis_row_set_key.c_str (),
                                                 redis_row_key.c_str ()));
    freeReplyObject (reply);
    // Set the expiration time of STRING
    reply = static_cast <redisReply *> (redisCommand (redis_connection,
                                                 "EXPIRE% s% s",
                                                 redis_row_key.c_str (),
                                                 ttlstr.c_str ()));
    freeReplyObject (reply);
    ++ num_row;
  }
  // Set the expiration time of SET
  reply = static_cast <redisReply *> (redisCommand (redis_connection,
                                               "EXPIRE% s% s",
                                               redis_row_set_key.c_str (),
                                               ttlstr.c_str ()));
  freeReplyObject (reply);
  return redis_row_set_key; // Return the SET key, so that other functions can get the contents of the SET
}

The process of storing MySQL data in hash is much more intuitive than storing the data in string. This is determined by the structural nature of the hash--hash itself is a set of key-value pairs: A "Parent key" contains a lot of "sub-keys", 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. It should be more appropriate to use the Redis key value pairs to represent the set of MySQL key values to the collection: for a row in the result set, the field corresponds to the "subkey" of the hash, and the value of the field corresponds to the value of the hash "subkey", that is, one row of the result set corresponds to a hash. The implementation code for 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,
// looks like "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");
  }
  // The prefix of the HASH key, which contains the identifier of the result set
  string prefix ("cache.hash:" + resultset_id + ":");
  unsigned int num_row = 1; // HASH number, appended to the end of the HASH key, starting from 1
  sql :: ResultSetMetaData * meta = resultset-> getMetaData ();
  unsigned int num_col = meta-> getColumnCount ();
  // 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
  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, and all rows in the result set are stored in the corresponding HASH
  resultset-> beforeFirst ();
  while (resultset-> next ()) {
    string redis_row_key; // HASH key name, consisting of prefix and HASH number
    stringstream keystream;
    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 into HASH
      reply = static_cast <redisReply *> (redisCommand (redis_connection,
                                                   "HSET% s% s% s",
                                                   redis_row_key.c_str (),
                                                   col_label.c_str (),
                                                   col_value.c_str ()));
      freeReplyObject (reply);
    }
// Add HASH key to SET
    reply = static_cast <redisReply *> (redisCommand (redis_connection,
                                                 "SADD% s% s",
                                                 redis_row_set_key.c_str (),
                                                 redis_row_key.c_str ()));
    freeReplyObject (reply);
// Set the expiration time of HASH
    reply = static_cast <redisReply *> (redisCommand (redis_connection,
                                                 "EXPIRE% s% s",
                                                 redis_row_key.c_str (),
                                                 ttlstr.c_str ()));
    freeReplyObject (reply);
    ++ num_row;
  }
  // Set the expiration time of SET
  reply = static_cast <redisReply *> (redisCommand (redis_connection,
                                               "EXPIRE% s% s",
                                               redis_row_set_key.c_str (),
                                               ttlstr.c_str ()));
  freeReplyObject (reply);
  return redis_row_set_key; // Return the SET key, so that other functions can get the contents of the SET
} 

At this point, we have given two scenarios for storing MySQL result sets, which is the first question we put forward at the end of this article, namely, what data structures are chosen to store the answers to the MySQL result set. The next article will look at the second problem, that is, the identifier selection of the data structure key.

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