Comparison of implementation labels between REDIS and MYSQL

Comparison of implementation labels between REDIS and MYSQL
Here we will illustrate the data conversion problem between REDIS and MYSQL. REDIS is a typical KEY-value nosql database and provides an additional rich data type. Here we briefly list the application issues of the label type.

For example, in MySQL, the label of the content has the following simple tables. Here I will only list the split table structure.

First, MySQL,

Content TABLE: create table 'content' ('id' int (10) unsigned not null, -- content id, unique. 'Name' varchar (60) default null, -- content name 'created _ timestamp' timestamp null default null, -- content addition time primary key ('id ')) ENGINE = InnoDB default charset = latin1 tag TABLE: create table 'tag' ('tag _ name' varchar (60) not null, -- tag name, unique 'visit _ count' int (10) unsigned not null default '0', -- tag access times primary key ('tag _ name ')) ENGINE = InnoDB default charset = latin1 relationship between content and tags, many to many. Create table 'content _ tag_relation '('content _ id' int (10) unsigned not null, -- content id' tag _ name' varchar (60) not null -- tag name) ENGINE = InnoDB default charset = latin1

Suppose we have the following requirements:
1. Get the document name corresponding to the tag,

SELECT a.name FROM content AS a,content_tag_relation AS bWHERE a.id = b.content_id AND b.tag_name = 'mysql'

2. display the first three tags Based on the traffic volume,

SELECT tag_name FROM tag WHERE 1 ORDER BY visit_count DESC LIMIT 3;

 

Next we will store this part of data in REDIS.
Second, redis,
1. a. content, which is stored in JSON format and STRING type,

t_girl:6379> set string:content_id:4  '{"name":"test48601","created_timestamp":"2012-01-01 05:41:01"}'OKt_girl:6379> get string:content_id:4"{\"name\":\"test48601\",\"created_timestamp\":\"2012-01-01 05:41:01\"}"

However, if you want to get the corresponding name and Creation Time of the content, you have to hand it over to the program for REDIS.


B, or you can use the HASH type for storage,

t_girl:6379> hset 'hset:content_id:4' name 'test48601'(integer) 1t_girl:6379> hset 'hset:content_id:4' created_timestamp '2012-01-01 05:41:01'(integer) 1

It is very easy to get the corresponding name and time.

t_girl:6379> hget hset:content_id:4 name"test48601"t_girl:6379> hget hset:content_id:4 created_timestamp"2012-01-01 05:41:01"t_girl:6379> 

2. Tags: We use Ordered Sets to sort access traffic.

t_girl:6379> zadd zset:tag 680 database 469 db2(integer) 2

For example, if we want to get the first three tags for access?

t_girl:6379> zrevrangebyscore zset:tag +inf 0 limit 0 31) "mongodb"2) "sql"3) "postgresql"

3. Relationship between tags and content. We use a set to do it,

t_girl:6379> sadd set:content_id:4 role mongodb role database (integer) 3

It is also easy to get the tag corresponding to the specified content

t_girl:6379> smembers set:content_id:41) "database"2) "role"3) "mongodb"

4. a. If we use the above design to implement a slightly more complex requirement: for example, get the document name corresponding to the tag. There seems to be no way to use such a requirement directly. For example, I wrote a piece of PYTHON code to obtain it:

    import redis    content_id_keys = r.keys('set*')    content_id_keys_len = len(content_id_keys)    i = 0    j = 0    content_name_list = []    while i < content_id_keys_len:        if r.sismember(content_id_keys[i],'mysql') == 1:            content_name_list.append(eval(r.get(content_id_keys[i].replace('set','string')))['name'])            print('Content name is :' + content_name_list[j])            j += 1        i += 1  

B. In fact, we can store redundant sets in REDIS so that we can take out the information directly.

 

t_girl:6379> sadd tag:mysql test123 test133 test144 test155(integer) 4t_girl:6379> smembers tag:mysql1) "test133"2) "test155"3) "test123"4) "test144"