Designing Scalable Like/Comment Systems: MySQL vs Redis Strategies

1. Requirements Overview

Display like count

Check if a user has liked (deduplication)

Show personal like list (user center)

Show article like list

2.1 MySQL Solution

MySQL remains viable for most small‑to‑medium projects with data below ten million rows; using table partitioning and cache can handle the load.

-- Article table
create table post {
    post_id int(11) NOT NULL AUTO_INCREMENT,
    ...,
    star_num int(11) COMMENT '点赞数量'
}

-- User table
create table user {
    user_id int(11) NOT NULL AUTO_INCREMENT,
    ...,
    star_num int(11) COMMENT '点赞数量'
}

-- Like table
create table star {
    id int(11) NOT NULL AUTO_INCREMENT,
    post_id,
    user_id,
    ...
}

Common queries:

select post_id from star where user_id=?   -- articles liked by a user
select user_id from star where post_id=?   -- users who liked an article

When data volume is modest, this design satisfies requirements, with like counts periodically aggregated into post and user tables.

Drawbacks: With large data sets a single table becomes a bottleneck; sharding by post_id or user_id conflicts with requirements, leading to redundant tables and increased storage/maintenance.

2.2 Redis Solution

When data reaches hundreds of millions, read/write pressure on MySQL necessitates using Redis as storage or cache. Redis offers rich data types suitable for various scenarios.

Two usage patterns: as pure cache (write‑through from MySQL) or as storage combined with MySQL.

Example for storing like count (string):

# set like count for article 888
SET star:tid:888 898
# increment
INCR star:tid:888

Deduplication (set of user IDs):

# add user IDs who liked article 888
SADD star:list:tid:888 123 456 789
# check if user 456 liked it
SISMEMBER star:list:tid:888 456

Other scenarios use hash or sorted set to reduce memory and support pagination.

Pros and Cons Comparison

Hash reduces global keys and memory usage but adds routing complexity.

String is simple but may not scale for massive user lists.

3. Data Consistency

When Redis is used as storage, enable both RDB and AOF persistence; monitor memory usage and plan capacity expansion. Replication introduces consistency challenges that must be addressed.