数据库分库分表海量商品存储优化实战指南

知识分享

2026-07-15 21:00

18 阅读

# 数据库分库分表海量商品存储优化实战指南 ## 什么时候该考虑分库分表当商品表单表数据超过 500 万行，或者磁盘 IO、连接数持续饱和时，**单一 MySQL 实例的读写能力就会成为瓶颈**。常见的现象是商品列表查询变慢、库存更新超时、后台导出卡死。此时就需要通过**分库分表（Sharding）**来将数据分散到多个数据库或表中，提升并发吞吐和存储上限。 > 核心思想：把一个大表“拆”成多个小表，分别存放在不同 MySQL 实例或同一个实例的不同库中，通过路由规则（如按商品 ID 哈希）决定数据落在哪里。 ## 分库分表的核心策略与工具选型 ### 两种拆分方式 - **垂直分库**：按业务模块拆分，例如将商品基本信息、库存、价格分别放到独立的数据库，降低单库压力。 - **水平分表**：将同一张商品表按某个字段（如 shop_id 或 goods_id）取模，分布到多张结构相同的表中。实际生产常将两者结合。 ### 常用中间件对比 | 工具 | 特点 | 适用场景 | |------|------|----------| | MyCat | 基于代理层，对应用透明，配置相对简单 | 中小规模，团队偏运维 | | ShardingSphere-JDBC | 应用内嵌，性能高，功能更细 | 微服务架构，Java 技术栈 | | Vitess | 基于 Kubernetes，大规模云原生 | 日均亿级访问 | 对于新手站长，我推荐从 **MyCat** 入手，因为它不需要修改代码，只需在数据库层加一个代理即可体验分片效果。 ## 以 MyCat 为例搭建分片环境（关键步骤） ### 1. 准备环境 - 两台以上 MySQL 实例（可用同一台机器不同端口模拟），分别创建数据库 `goods_db1` 和 `goods_db2`。 - 下载 MyCat 1.6 版本（`mycat-server-1.6.x`），解压到 `/usr/local/mycat`。 ### 2. 配置分片规则编辑 `conf/schema.xml`，定义逻辑库和分片表： ```xml ``` ### 3. 配置路由规则编辑 `conf/rule.xml`，添加按 goods_id 取模的分片算法： ```xml id mod-long 2 ``` ### 4. 启动 MyCat ```bash cd /usr/local/mycat ./bin/mycat start ``` 连接 MyCat（默认端口 8066）：`mysql -h127.0.0.1 -P8066 -uroot -p`。登录后执行建表语句，MyCat 会在两个 dataNode 上自动创建 `t_goods` 表。 ## 避坑：分库分表常见问题 1. **全局主键冲突**：禁止使用数据库自增 ID，应改用雪花算法生成唯一 ID。MyCat 内置了全局序列功能，配置 `sequence_conf.properties` 即可。 2. **跨节点 JOIN**：尽量避免关联查询，或使用 MyCat 的全局表（全局表在每个分片都存一份）来绕过。 3. **数据迁移丢失**：老表数据导入分片表前，务必用 `count(*)` 比对总行数，最好在业务低峰期操作。 4. **分片字段选择失误**：如果按 `create_time` 分片会导致热点集中在最后一张表，通常按用户 ID 或商品 ID 哈希更均匀。 > 高频问题：为什么分片后某些查询反而更慢？—— 因为跨分片的聚合查询（如 `ORDER BY` + `LIMIT`）需要 MyCat 在内存中归并，大偏移量时性能下降。建议在应用层做二次分页。 ## 验证优化效果 1. **确认数据分布**：登录每个 dataNode 的 MySQL，执行 `SELECT COUNT(*) FROM t_goods`，对比总数是否一致。 2. **压测负载**：用 `sysbench` 模拟并发写入，观察 MyCat 代理的 QPS 是否比单库提升至少一倍。 3. **查询延迟**：随机查询某个商品 ID，执行 `EXPLAIN` 确保只命中一个分片（`dataNodes` 显示为单一节点）。如果发现路由异常（如数据全部落在一个节点），检查 `rule.xml` 中的分片字段是否与表定义匹配。 ## 写在最后分库分表能显著缓解海量商品存储的压力，但引入中间件也增加了维护复杂度。建议从 **2~4 个分片** 起步，先跑通业务再逐步扩容。本文中 MyCat 的配置步骤可以帮你快速搭建实验环境，实际生产请做好备份和灰度切换。如果你在使用过程中遇到“ERROR 3009”等报错，请优先排查 `conf/logs/mycat.log` 中的路由日志。延伸阅读：后续可以继续了解 ShardingSphere-JDBC 的配置方式，以及如何用 ClickHouse 做商品数据的聚合分析。