Designing Scalable Like/Comment Systems: MySQL vs Redis Strategies

Scenario Requirements

Display the total number of likes for an article.

Determine whether a specific user has already liked an item (de‑duplication).

Show a personal list of liked items in the user centre.

Show the list of users who liked a particular article.

MySQL Design (suitable for <10 million rows)

-- article table
CREATE TABLE post (
    post_id   INT(11) NOT NULL AUTO_INCREMENT,
    /* other columns */
    star_num  INT(11) COMMENT '点赞数量',
    PRIMARY KEY (post_id)
);

-- user table
CREATE TABLE user (
    user_id   INT(11) NOT NULL AUTO_INCREMENT,
    /* other columns */
    star_num  INT(11) COMMENT '点赞数量',
    PRIMARY KEY (user_id)
);

-- like (star) table
CREATE TABLE star (
    id        INT(11) NOT NULL AUTO_INCREMENT,
    post_id   INT(11) NOT NULL,
    user_id   INT(11) NOT NULL,
    /* other columns */
    PRIMARY KEY (id),
    INDEX idx_user (user_id),
    INDEX idx_post (post_id)
);

Common queries:

SELECT post_id FROM star WHERE user_id = ?;

SELECT user_id FROM star WHERE post_id = ?;

The like count can be aggregated periodically (e.g., every few minutes) and written back to post.star_num and user.star_num. This approach works well while the data volume is modest.

Drawbacks : As the table grows, a single table becomes a bottleneck. Horizontal sharding is required, but sharding by post_id or user_id alone cannot satisfy both query patterns, leading to redundant tables and potential consistency problems.

Redis Design (for hundreds of millions of likes)

Scenario A – Like count

# Set initial count for article 888
SET star:tid:888 898
# Increment atomically
INCR star:tid:888   # => 899

Scenario B – De‑duplication (prevent duplicate likes)

Store a SET of user IDs for each article. Adding a user ID to the set automatically prevents duplicates.

SADD star:set:tid:888 123 456 789   # add users
SISMEMBER star:set:tid:888 456      # => 1 if already liked

Scenario C – User’s liked list

Maintain a SET per user (e.g., star:user:uid:123) that contains the IDs of articles the user has liked. The same data used in Scenario B can be queried in reverse to build the personal like list.

Scenario D – Article’s liked list

SADD star:list:tid:888 123 456 789   # add user IDs to article set
SISMEMBER star:list:tid:888 456      # check if user 456 liked the article
SMEMBERS star:list:tid:888           # retrieve all user IDs for the article

Alternative storage structures for the counter:

String : simple SET/INCR for a single counter.

Hash : group multiple articles (e.g., 100 per hash) to reduce global key count. Example key star:hash:0 with field 888 =898.

Sorted Set : store hot articles with the like count as the score, enabling ranking queries.

Data Consistency

When Redis is used as primary storage, enable both RDB snapshots and AOF persistence to avoid data loss. This consumes a significant portion of memory, so monitor capacity and scale nodes proactively.

Keep MySQL as the authoritative source for periodic aggregation. Implement a write‑through or background synchronization process to keep MySQL and Redis in sync, handling failover and migration scenarios to mitigate consistency gaps.