MySQL 实战案例：电商系统商品库分表设计（按类目时间分片）

本文链接：https://blog.csdn.net/linnaa6/article/details/146279481

在电商系统中，商品数据量大、访问频率高，如果不进行优化，单表的查询和写入性能会成为瓶颈。为了提升 MySQL 的性能，我们可以对商品库进行分库分表，常见的分片方式有 按类目分片 和 按时间分片。本文将结合实际业务场景，探讨如何设计商品表的分片方案，并提供 SQL 及代码示例。

1. 为什么要进行分表？

在单库单表模式下，商品表通常设计如下：

CREATE TABLE product (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,  -- 自增主键
    name VARCHAR(255) NOT NULL,             -- 商品名称
    category_id INT NOT NULL,               -- 商品所属类目
    price DECIMAL(10,2) NOT NULL,           -- 商品价格
    stock INT NOT NULL,                     -- 商品库存
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 创建时间
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

问题分析：

数据量过大：电商平台有海量商品，表数据可能达到 千万级别甚至上亿，影响查询性能。
索引性能下降：MySQL 索引树变大后，B+ 树的查询性能下降。
单库并发瓶颈：高并发时，数据库负载过重，影响订单处理速度。

如何优化？

按类目（category_id）分片：适用于类目相对稳定的场景，提高查询效率。
按时间（created_at）分片：适用于新增商品量大、查询按时间范围为主的场景，如秒杀商品、活动商品。

2. 方案一：按类目分片

2.1 方案设计

核心思路：根据 category_id（商品分类 ID）进行水平拆分，将商品存储到多个表中。例如：

商品分类	存储表
数码产品（category_id=1）	product_1
服饰（category_id=2）	product_2
家电（category_id=3）	product_3
…	…

2.2 表结构设计

CREATE TABLE product_1 (  -- category_id=1
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    category_id INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE product_2 (  -- category_id=2
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    category_id INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2.3 分片规则

存储逻辑：

category_id % N（N 为表的数量）
例如，假设有 4 张表（product_0, product_1, product_2, product_3），则 category_id % 4 计算存储表。

SQL 查询示例（应用层路由）：

public String getTableName(int categoryId) {
    int tableIndex = categoryId % 4; // 4 张表
    return "product_" + tableIndex;
}

// 查询商品
public Product queryProductById(int categoryId, long productId) {
    String tableName = getTableName(categoryId);
    String sql = "SELECT * FROM " + tableName + " WHERE id = ?";
    return jdbcTemplate.queryForObject(sql, new Object[]{productId}, new ProductRowMapper());
}

2.4 适用场景

✅ 适用于：

类目较稳定，查询通常基于 category_id 过滤的场景。
商品分类较多，每个类目数据量较均衡。

❌ 不适用于：

类目分布不均衡（部分分类数据量过大，造成单表热点）。
查询跨多个分类（如搜索功能），需要跨表查询，可能影响性能。

3. 方案二：按时间分片

3.1 方案设计

核心思路：按照 created_at（商品发布时间）进行分片，如每月或每季度存储到不同的表，适用于新增商品量大、查询多按时间范围进行的场景。

例如：

时间范围	存储表
2024 年 1 月	product_202401
2024 年 2 月	product_202402
2024 年 3 月	product_202403
…	…

3.2 表结构设计

CREATE TABLE product_202401 (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    category_id INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE product_202402 (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    category_id INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

3.3 分片规则

存储逻辑：

created_at 按 YYYYMM 生成表名，例如 product_202401 表示 2024 年 1 月的商品表。

SQL 查询示例（应用层路由）：

public String getTableNameByTime(LocalDateTime createdAt) {
    DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMM");
    String tableSuffix = createdAt.format(formatter);
    return "product_" + tableSuffix;
}

// 查询最近 3 个月商品
public List<Product> queryRecentProducts() {
    String tableName1 = getTableNameByTime(LocalDateTime.now().minusMonths(1));
    String tableName2 = getTableNameByTime(LocalDateTime.now().minusMonths(2));
    String sql = "SELECT * FROM " + tableName1 + " UNION ALL SELECT * FROM " + tableName2;
    return jdbcTemplate.query(sql, new ProductRowMapper());
}

3.4 适用场景

✅ 适用于：

时间范围查询多，如“查询最近 3 个月商品”或“查询 2023 年的商品”。
新商品数据量大，但历史数据查询少，可归档历史表优化性能。

❌ 不适用于：

查询跨多个时间段时，需要跨表查询，SQL 复杂度增加。
历史数据查询频繁的场景，可能影响查询效率。

4. 方案对比与选型建议

方案	适用场景	优势	劣势
按类目分片	类目查询多	查询高效，索引小	类目分布不均会导致数据倾斜
按时间分片	新增商品量大，按时间查询多	便于归档，减少索引压力	跨时间查询需要 UNION ALL