Like function, use MySQL or Redis

The like function is a basic function in current app development.

Today we will talk about the database design issues for scenarios such as likes, comments, and favorites.

1. Let's first look at the requirements of the scenario:

• Show number of likes
• Determine whether the user has liked the post. This is used to remove duplicates. This is a necessary judgment.
• Displays a list of personal likes, usually in the user center
• Show article likes list

Let’s first look at the examples of Toutiao and Weibo.

Both of these have top-level traffic, and the backend must have a complex architecture. Today we will only talk about popular solutions.

2.1 MySQL solution

MySQL solution: With the popularity of NoSQL and the continued popularity of big data, MySQL is still irreplaceable. For most small and medium-sized projects with data volumes below 10 million, MySQL sharding + cache is fully competent, and its stability is unmatched by other solutions:

 Article Table 
 
 create   table post { 
 
 post_id int (11) NOT   NULL AUTO_INCREMENT, 
 
 ...... 
 
 star_num int (11) COMMENT 'Number of likes'   
 
 } 
 
 User Table 
 
 create   table   user { 
 
 user_id int (11) NOT   NULL AUTO_INCREMENT, 
 
 ...... 
 
 star_num int (11) COMMENT 'Number of likes'   
 
 } 
 
 Like Table 
 
 create   table star { 
 
 id int (11) NOT   NULL AUTO_INCREMENT, 
 
 post_id, 
 
 user_id, 
 
 ...... 
 
 }

Commonly used queries:

 Query the articles that the user has liked select post_id from star where user_id=?  
 Query the user who liked the article select user_id from star where post_id=?

The number of likes can be updated to the post and user tables through scheduled asynchronous statistics.

When the amount of data is not large, this design can basically meet the needs.

shortcoming:

• When the amount of data is large, a table will be under great pressure when querying, and it is necessary to split the table. Whether using post_id or user_id to hash the table, it conflicts with our needs. The only way is to make two redundant tables. This increases storage space and maintenance workload, and may also cause consistency issues.

2.2 redis solution

When the amount of data reaches hundreds of millions, caching is a necessary stage. Since the action of clicking "like" is very casual, many people want to click "like" when they see the thumbs-up, so the amount of data grows very fast. When the data scale increases, there is a lot of pressure on MySQL reading and writing. At this time, you need to consider memcache or redis for storage or caching.

Why do people generally choose redis? As a popular NoSQL, redis has rich data types and can adapt to the needs of multiple scenarios.

There are two uses for redis, one is storage, the other is pure cache, which needs to be used with MySQL. Pure cache means writing data from MySQL to redis first, and the user reads the cache first, and then pulls it from MySQL after a miss, while the cache is synchronized.

In most scenarios, the two are used at the same time and do not conflict.

Here's how to use redis as a storage solution:

Scenario a: Display the number of likes

In the like area, only the number of likes is displayed to distinguish whether the user has liked it. Generally, users do not care about this list. In this scenario, only a number is needed. When the number is relatively large, it is generally displayed as "7k", "10W" or the like.

Use article id as key

 //Take article id=888 as an example
 127.0.0.1:6379[2]> set star:tid:888 898 //Set the number of likes
 OK
 127.0.0.1:6379[2]> incr star:tid:888 // Implement automatic quantity increase ( integer )
 899

Scenario b: Remove duplicate likes to avoid repeated likes

To achieve this requirement, there must be a list of uids that liked the article, with uid as the key. Scenario C: Generally, in the user center, you can see the user's own like list

This requirement can be achieved using the data from scenario b.

Scenario d: Like list of an article, similar to scenario b, with article id as key

 //Take article id=888 as an example
 127.0.0.1:6379[2]> sadd star:list:tid:888 123 456 789 // Like uid list ( integer )
 3
 127.0.0.1:6379[2]> sismember star:list:tid:888 456 //Judge whether to like ( integer )
 1

If you have liked a place, it will be displayed in red, if not, it will be displayed in black and white.

There is no place to see the likes list on Toutiao, but when you click on Weibo, you can see the likes list on the details page, but only the most recent few dozen will be displayed, without pagination.

As shown below, I chose a hot spot, "Pig" with many fans.

Some people may think that no one cares about the likes list, and storing it will waste a lot of resources, so it is better not to store it! However, this data is necessary. Two points:

a. Deduplication. The number of likes can be inaccurate, but deduplication must be accurate.

b. For another social product, every bit of user behavior needs to be recorded, which is meaningful for subsequent user behavior analysis and data mining.

The number of user likes stored in string above can also be stored in hash. The article ids are divided into blocks, and every 100 are stored in a hash table. Each article id is a key of the hash, and the value stores the user id of the user who liked the article. If there are many users who like the article, in order to avoid performance problems caused by too many ids, they can be listed separately and saved in a sorted set structure. After all, the hot spots are only a few.

Comparison of the advantages and disadvantages of the solutions

Hash: Uses fewer global keys, saving memory space; but it also brings problems

How to route to the corresponding hash based on the article id?

Is the user ID in the hash or set? There is uncertainty

Although using hash saves space, it increases complexity. The choice depends on personal needs.

Do you have any other methods besides this?

3. Data consistency

When redis is used as storage, data persistence must be ensured, and RDB and AOF must be enabled. This will result in the business only being able to use half of the machine's memory, so capacity monitoring must be done and capacity expansion must be done in a timely manner.

In addition, as long as there is data copy, there will be consistency issues, which is another very important topic. Let's talk about it in detail later!

In conclusion: It is not easy to write the problem clearly. Please pay more attention and leave a message. Thank you!

An article I wrote a few days ago received enthusiastic responses from many colleagues. I feel honored to be able to communicate with so many colleagues! There is no standard solution to engineering problems. A thousand people have a thousand solutions. Only you know which one is best for you! I look forward to your better ideas and methods.