Redis for beginners (3) -- using Redis as the cache of Mysql database, redismysql

Redis for beginners (3) -- using Redis as the cache of Mysql database, redismysql

After the Mysql result set is cached in the Redis string or hash structure, we are faced with a new problem, such as what these strings or hash names are, that is, how to determine their keys. Because the rows corresponding to 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 sequence number to these data structures, then combine the result set identifier with the sequence number to uniquely identify a data structure. Therefore, the problem of string and hash naming is converted into the problem of determining the result set identifier.

After investigation, we found a common method to determine the result set identifier. As we know, the result set data cached in Redis is obtained from Mysql using SQL statements such as select. The same query statement generates the same result set (the sequence of each record in the result set is not discussed here). 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. An obvious reason is that unprocessed SQL query statements contain spaces, redis keys cannot contain spaces. In this case, we need a function that converts an SQL statement to a unique identifier. Generally, this function is implemented by hash functions, and many algorithms including MD5 and SHA series encryption hash functions 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 SQL statement format, calculate the MD5 value of the statement and obtain the result set identifier accordingly. Then, use the identifier to search for the result set in Redis. Note that each row in the result set has a corresponding key, which is stored in a Redis collection structure. This set exactly corresponds to the required result set. Therefore, the key of this set must contain the result set identifier. If such a set does not exist in Redis, it indicates that the result set to be queried is not in Redis. Therefore, you need to execute the corresponding SQL statement and query the corresponding result set in Mysql, then, store each row in the result set in the form of a string or hash into Redis according to the method described above. The code for finding the corresponding result set in Redis is as follows:

// This function queries the corresponding result set in Redis Based on the SQL statement, and returns the key 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 value of the SQL statement, which is the key to uniquely identify the result set. // when type = 1, this function will query the corresponding STRING set or write the result set to several STRING string cache_type = (type = 1 )? "String": "hash"; // synthesize the SET key string redis_row_set_key = "resultset. "+ cache_type +": "+ resultset_id; redisReply * reply; // get all the keys saved in the 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 to be searched does not exist, redis does not have a corresponding result set. You need to call the Cache2String or // Cache2Hash function to pull 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) {response = Cache2String (mysql_connection, redis_connection, resultset, resultset_id, ttl);} else {response = Cache2Hash (mysql_connection, redis_connection, resultset, resultset_id, ttl);} // obtain all the keys saved in the SET from reply again. 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 the SET into the string 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 how to determine the result set identifier in Redis and the keys of each data structure. In the next article, we will discuss the sorting and paging issues of the result set in Redis.

How to Use redis for mysql Cache

Redis is used to read and write data, and the queue processor is used to regularly write data to mysql.
At the same time, avoid conflicts. When starting redis, read all table key values from mysql and store them in redis. When writing data to redis, The redis primary key is automatically increased and read, if mysql update fails, you need to promptly clear the cache and synchronize the redis primary key.

This process mainly reads and writes redis in real time, while mysql data is processed asynchronously through queues to relieve mysql pressure. However, this method is mainly used in high concurrency scenarios, in addition, redis's high-availability cluster architecture is more complex and is generally not recommended.

Redis cache Problems

Redis does not have this function. It is only a key-value database, and the cache must be stored in redis using programs.