【ShardingSphere】分库分表之Sharing-jdbc
·
随着一个项目的数据量越来越大，我们经常会遇到一种情况。那就是数据越来越查不动了。我们常见的操作就是进行sql优化。但是sql优化也会有个极限。
在考虑分库分表前，先会去考虑sql优化能不能解决问题，作为一个开发老油条，能尽量不做大动作的牵动代码就不去做，哈哈。如果问题能通过以下方式解决，就不需要拆分：
- SQL 本身优化：慢查询（如全表扫描、无效 JOIN）可通过加索引、重构 SQL（拆分子查询、避免 SELECT *）、优化事务粒度解决；
- 配置与硬件优化：MySQL 参数（如
innodb_buffer_pool_size调大、log_buffer_size优化）、升级 SSD（解决 IO 瓶颈）、加 CPU / 内存（解决计算瓶颈）可提升性能（不过一般这个操作反而在公司是最难的操作，公司经费有限，很难让你畅快的使用，申请难度极大）； - 缓存与读写分离：高频读请求（如商品详情）用 Redis 缓存拦截，写压力用 “主从复制” 分流（主库写、从库读），能缓解 80% 以上的常规压力；（主从复制与读写分离我会单开一片文章去讲）
- 表结构优化：大表拆 “冷热数据”（如订单表保留近 1 年数据在主表，历史数据迁移到归档表）、冗余字段减少 JOIN，比分库分表更轻量。
只有当上述优化全部落地后，仍出现以下 4 类 “底层瓶颈”，说明单库单表架构已达上限，必须通过分库分表突破：
1. 单表数据量过大，索引 / 查询彻底 “失效”
这是最核心的触发场景，数据量超过阈值后，即使索引优化到极致，性能也会断崖式下跌。
- 具体阈值：行业通用经验是「InnoDB 单表数据量超过 2000 万行」（或表文件大小超过 100GB），但需结合业务：
- 若表字段少（如仅 10 个字段，无大文本），可容忍到 5000 万行；
- 若表字段多（含大文本、多索引），1000 万行就可能出现查询慢。
- 无法解决的问题：
- 索引失效：数据量过大时，索引树层级变深（如 B + 树从 3 层变成 4 层），查询时磁盘 IO 次数激增，即使走索引，耗时也从 10ms 涨到 100ms+；
- 写入卡顿：插入 / 更新时，不仅要写数据，还要维护索引（如 B + 树分裂），数据量越大，索引维护耗时越长，甚至出现 “行锁等待”；
- 备份 / 运维困难：单表数据量过亿，全量备份需数小时，且恢复时无法快速定位问题，运维风险极高。
2. 高并发写入压力，单库 “扛不住”
当业务存在高频写入场景（如秒杀订单、实时日志、支付记录），单库的写入能力会先于读能力达到上限，优化配置也无法突破。
- 具体阈值：单库写入并发（QPS）超过「3000-5000」（取决于 SQL 复杂度和硬件），且持续处于高负载（CPU 利用率 80%+、磁盘 IO util 90%+）。
- 无法解决的问题：
- 写入排队：单库的事务日志（redo log）、binlog 刷盘是串行或有限并行的，大量写入请求会排队等待刷盘，导致 “插入超时”；
- 锁冲突加剧：高并发写入时，行锁、表锁冲突概率上升（如秒杀时多个请求更新同一商品库存），即使优化锁粒度（如用行锁替代表锁），仍会因请求量过大导致锁等待；
- 读写互斥：部分场景（如 MyISAM 引擎、或 InnoDB 的高写入 + 高频读）会出现 “写阻塞读”，优化读写分离后，主库的写入压力仍无法缓解（从库只能分流读，不能分流写）。
3. 业务强隔离需求，单库 “拆不开”
当多个核心业务模块（如 “订单”“用户”“商品”“支付”）共用一个数据库时，某一模块的高负载会拖累其他模块，这种 “业务耦合” 问题无法通过 SQL 优化解决。
- 典型场景：
- 订单模块做促销活动，QPS 暴涨导致 CPU 100%，同时用户模块的 “登录查询” 也变慢（即使用户查询本身已优化索引）；
- 不同业务对数据库的要求不同（如订单库需高可靠、商品库需高并发读），单库配置无法同时满足（如订单库要高频刷盘保证数据不丢，商品库要减少刷盘提升读速度）。
- 核心矛盾：SQL 优化只能解决 “单条查询 / 单张表” 的性能问题，无法解决 “多业务模块资源争抢” 的架构问题，必须通过分库（按业务模块拆库）实现隔离。
4. 硬件资源已到顶，无法再升级
当服务器的 CPU、内存、磁盘 IO、网络带宽已达物理上限（如已用顶配 CPU、SSD 磁盘 IO util 长期 100%、网络带宽占满），且无法通过 “加服务器做读写分离” 缓解时，只能通过分库分表跨服务器拆分资源。
- 无法解决的问题：
- 单服务器 CPU 瓶颈：即使优化 SQL 减少计算量，所有查询的总 CPU 消耗仍超过服务器核心能力（如 8 核 CPU 持续 100%）；
- 磁盘 IO 饱和：大量读写请求导致磁盘 IOPS（每秒输入输出次数）达上限（如 SSD 的 IOPS 约 1 万，持续跑满），优化索引、减少读写次数后仍无法下降；
- 内存不足：
innodb_buffer_pool_size已设为物理内存的 70%（如 128G 内存设 90G 缓存），但仍有大量请求需要磁盘读（缓存命中率低于 95%），且无法再升级内存。
于是引入我们今天的主角-——分库分表利器Sharding-JDBC。至于另一个工具Sharding-Proxy，我目前尚未使用过，等实际体验后再与大家分享。
分库分表大致分为4种：
- 垂直分库
- 垂直分表
- 水平分库
- 水平分表
说到分库，除了上述说的垂直分库、水平分库之外呢，我们平常为了缓解压力，会按照业务的划分将不同的业务建立不同的数据库，但都是在同一台mysql分库里面，这样或者在配置的时候方便，但其实能够缓解的压力有限。
哪些场景下有轻微优化作用？
虽然无法缓解整体流量压力，但这种拆分在特定场景下能带来一些管理或性能上的微小优化。
- 减少表级锁冲突：如果原数据库中存在大量高并发的表级锁操作（如 MyISAM 引擎），将这些表拆分到不同数据库后，不同库的表锁不会相互阻塞，能减少锁等待时间。
- 简化权限管理：可以针对不同业务数据库设置独立的访问权限，避免单一账号拥有所有表的权限，提升安全性和可维护性。
- 优化备份策略：可针对不同重要性的数据库制定差异化备份计划，例如核心业务库实时备份，非核心库每日备份，降低备份资源消耗。
于是便想着同一台服务器多台mysql，把不同的业务库分配到这几台mysql中去。但是仍然受限于物理资源的上限：当所有实例的总 CPU 使用率达到 100%、磁盘 IO 达到饱和（如 iostat 显示 util 接近 100%），或网络带宽占满时，所有实例都会出现响应变慢、超时等问题，本质和 “单实例多库” 面临的 “总资源不够” 问题一致。
于是分库还有一种方式，那就是多服务器下的多mysql实例。这样是最好的解决方案，个人认为。
但是这个方案有个最大的问题就是，在公司服务器的资源没那么容易审批通过，如果说多配几套服务器是为了某个项目把服务拆分，把数据库业务库拆分。那申请的难度可想而知。
回归到我们分库分表的概念中来，什么是垂直分库、水平分库、垂直分表、水平分表呢？
- 垂直分库
垂直分库的概念就是我们上面提到的，按照业务的不同拆分成不同的库，即使不同其他的工具，我们也已经这么做了。
- 水平分库
针对垂直拆分后的 “订单库”（数据量增长最快、并发最高），按 “用户 ID 哈希” 拆为 3 个水平库（order_db_0/1/2），突破单库数据量和并发上限。也就是说一个订单库变成三个订单库。
分库不是今天的重点，等有机会再展开叙述。
- 垂直分表
在一张表内按照字段进行拆分，按照字段的查询频率和字段的大小划分。这个目前按照我们所做的业务来看，这种拆法，及其的考验对业务的理解程度，以及前期和后续字段的建立是否合理。要是B端的产品业务会比较复杂，说实话，没法真的对于数据库的设计做到非常的合理，这就比较头疼，所以项目就没有采用这种做法来做。（可能做着把自己给绕进去了）
简单点来讲就是拆 “列”，表结构不同，数据关联（比如 1 张用户表拆成 “用户基础表”（存 username、phone）和 “用户详情表”（存 avatar、intro），两张表用 user_id 关联）
平常我们在做数据库设计的时候，也会这么考虑。如果是一开始就这么设计，那自然是没什么影响。但是如果是一张业务量很大且字段很多的表，此时如果进行垂直分表，那就有点危险了。
- 水平分表
对于水平分表，就是同一张表按照业务id，进行拆分多个表（比如 1 张订单表拆成 3 张按时间的订单表，每张表都有 order_id、user_id 等字段）
今天就水平分表展开详细与大家分享一下。
我现在有一个需求就是我有一张过车记录表大概现在是1000万条，表中有字段in_time和out_time，分别是车辆进场时间和车辆离场时间。我想根据进场时间、离场时间分别来查停车场的过车记录数据。这里就不谈sql优化的事情了。在水平分表下怎么操作？
在设计的时候，我有个需求就是我设计不用管是那年的月份，只需要按月去拆分，所以在配置分片规则的时候就要注意
方案核心：主表 + 索引表双表结构（按月分表）
1. 主表设计（按in_time月分表）
- 分表规则：按
in_time的月份分表，表名pass_record_yyyyMM（如pass_record_202410存储 2024 年 10 月进场的记录）。 - 核心字段：
字段名 类型 说明 id bigint 主键（自增） record_id bigint 全局唯一 ID（雪花算法，跨表唯一） car_no varchar 车牌号 in_time bigint 进场时间戳（秒级，分表键） out_time bigint 出场时间戳（秒级，可为空） fee decimal 费用
- 索引：
idx_in_time（in_time，分表键）、idx_record_id（record_id，唯一索引，关联索引表）。
2. 二级索引表设计（按out_time月分表）
专门为out_time查询构建索引，记录 “出场时间” 与 “主表分表” 的映射，避免out_time查询跨全量主表。
- 分表规则：按
out_time的月份分表，表名pass_record_out_index_yyyyMM（如pass_record_out_index_202410存储 2024 年 10 月出场的记录）。 - 核心字段：
字段名 类型 说明 id bigint 主键（自增） record_id bigint 关联主表的 record_id（唯一）in_time_table varchar 主表分表名（如 pass_record_202410）out_time bigint 出场时间戳（秒级，分表键） - 索引：
idx_out_time（out_time，分表键）、idx_record_id（record_id，唯一索引）。
关键流程（写入 + 查询）
1. 写入流程（保证数据一致性）
车辆进场和出场时分别操作：
- 进场时：
- 此时
out_time为空，不写入索引表。 - 根据
in_time确定主表分表（如pass_record_202410），写入主表（out_time为空）。 - 生成
record_id（全局唯一）。
- 此时
-
出场时：
- 根据
record_id定位主表（通过record_id的生成时间反推主表，或直接查最近主表），更新out_time和fee。 - 根据
out_time确定索引表分表（如pass_record_out_index_202410），写入索引表（存储record_id和in_time_table）。 - 事务保证：更新主表和写入索引表在同一事务中（本地事务即可，单库场景），避免数据不一致。
- 根据
2. 查询流程（两种场景均高效）
场景 1：按in_time查询（如 “查 202410 进场的车辆”）
- 直接路由到主表
pass_record_202410，执行查询：sql
SELECT * FROM pass_record_202410 WHERE in_time BETWEEN 1730256000（2024-10-01 00:00） AND 1733020799（2024-10-31 23:59）;
- 性能：单表 100 万数据，索引查询耗时 < 50ms。
场景 2：按out_time查询（如 “查 202410 出场的车辆”）
- 步骤 1：路由到索引表
pass_record_out_index_202410，获取所有record_id和in_time_table：sql
SELECT record_id, in_time_table FROM pass_record_out_index_202410 WHERE out_time BETWEEN 1730256000 AND 1733020799; - 步骤 2：根据
in_time_table（如pass_record_202409、pass_record_202410）遍历对应主表，查询具体记录：sql
-- 假设in_time_table为pass_record_202410 SELECT * FROM pass_record_202410 WHERE record_id IN (1001, 1002, ...); -- 索引表返回的record_id列表 - 性能：索引表仅存储关联关系（100 万条记录约占 10MB），查询耗时 < 30ms；主表查询按
record_id（唯一索引），单表 100 万数据耗时 < 50ms，总耗时 < 100ms。
Sharding-JDBC 配置（适配按月分表）
spring:
shardingsphere:
datasource:
names: pass-db # 单库（月均100万，无需分库）
pass-db:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/pass_db?useSSL=false&serverTimezone=UTC
username: root
password: root
rules:
sharding:
tables:
# 主表：pass_record（逻辑表）
pass_record:
actual-data-nodes: pass-db.pass_record_${*} # 动态匹配所有pass_record_yyyyMM表
table-strategy:
standard:
sharding-column: in_time # 分表键：in_time
sharding-algorithm-name: in-time-month-algorithm
# 索引表：pass_record_out_index（逻辑表）
pass_record_out_index:
actual-data-nodes: pass-db.pass_record_out_index_${*} # 动态匹配所有索引表
table-strategy:
standard:
sharding-column: out_time # 分表键：out_time
sharding-algorithm-name: out-time-month-algorithm
sharding-algorithms:
# 主表分表算法（in_time转yyyyMM）
in-time-month-algorithm:
type: CLASS_BASED
props:
strategy: STANDARD
algorithm-class-name: com.example.algorithm.InTimeMonthShardingAlgorithm
# 索引表分表算法（out_time转yyyyMM）
out-time-month-algorithm:
type: CLASS_BASED
props:
strategy: STANDARD
algorithm-class-name: com.example.algorithm.OutTimeMonthShardingAlgorithm
props:
sql-show: true # 打印SQL，验证路由
2. 自定义分表算法（按月转换）
主表和索引表的算法逻辑一致，仅分表键不同：
// 主表分表算法（in_time转yyyyMM）
public class InTimeMonthShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
private static final DateTimeFormatter FORMATTER = DateTimeFormatter.ofPattern("yyyyMM");
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
Long inTime = shardingValue.getValue(); // in_time为秒级时间戳
LocalDateTime dateTime = LocalDateTime.ofInstant(
Instant.ofEpochSecond(inTime), ZoneOffset.ofHours(8) // 东八区
);
String suffix = dateTime.format(FORMATTER); // 如202410
return shardingValue.getLogicTableName() + "_" + suffix; // pass_record_202410
}
}
// 索引表分表算法（out_time转yyyyMM，代码同上）
public class OutTimeMonthShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
// 与InTimeMonthShardingAlgorithm逻辑一致，分表键为out_time
}
- 索引表自动创建：每月 25 日通过定时任务创建下月的主表和索引表（如 10 月创建 11 月的
pass_record_202411和pass_record_out_index_202411），避免写入时表不存在。 - 未出场车辆查询：
out_time为空的车辆（未出场）无法通过索引表查询，需直接查最近 3 个月的主表（如查 “未出场车辆” 时，遍历pass_record_202408、202409、202410），因数据量小（300 万），性能可接受。 - 数据归档：超过 1 年的主表和索引表可迁移到冷存储（如 HDFS），查询时通过接口路由，不影响热表性能。
一、分片主表创建 SQL（按 in_time 年月分表）
表名格式：car_park_use_log_yyyyMM（如 car_park_use_log_202410），与原表结构完全一致：
配置分表前的按照时间从202301-202512开始建表，隐藏了真实业务表的字段，仅做展示Demo。
-- 创建2024年10月分片主表（其他月份仅需修改后缀）
CREATE TABLE IF NOT EXISTS `car_park_use_log_202301` (
`id` varchar(64) NOT NULL DEFAULT '',
`number_plate` varchar(16) NOT NULL COMMENT '车牌号',
`in_time` bigint(19) unsigned NOT NULL DEFAULT '0' COMMENT '进场时间（分表键）',
`out_time` bigint(19) unsigned NOT NULL DEFAULT '0' COMMENT '出场时间',
KEY `in_time` (`in_time`), -- 分表键索引，必须保留
KEY `out_time` (`out_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='车辆出入记录（2023年01月）';
二、索引表创建 SQL（按 out_time 年月分表）
索引表关联主表的核心字段，表名格式：car_park_out_index_yyyyMM（如 car_park_out_index_202410）：
-- 创建2024年10月索引表（其他月份仅需修改后缀）
CREATE TABLE IF NOT EXISTS `cf_car_park_out_index_202410` (
`id` varchar(64) NOT NULL DEFAULT '' COMMENT '自增ID（同主表id）',
`number_plate` varchar(16) NOT NULL COMMENT '车牌号（冗余，加速查询）',
`record_id` varchar(64) NOT NULL COMMENT '关联主表的id',
`in_time_table` varchar(64) NOT NULL COMMENT '主表分片表名（如cf_car_park_use_log_202410）',
`out_time` bigint(19) unsigned NOT NULL DEFAULT '0' COMMENT '出场时间（分表键）',
`car_park_id` varchar(64) NOT NULL COMMENT '停车场id（冗余，加速筛选）',
`pay_time` bigint(19) unsigned NOT NULL DEFAULT '0' COMMENT '支付时间（冗余，加速查询）',
PRIMARY KEY (`id`) USING BTREE,
KEY `out_time` (`out_time`), -- 分表键索引
KEY `record_id` (`record_id`), -- 关联主表的唯一索引
KEY `number_plate` (`number_plate`), -- 车牌号索引，支持按车牌查出场记录
KEY `car_park_id` (`car_park_id`) -- 停车场ID索引，支持按停车场筛选
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='车辆出场索引表（2024年10月）';
索引表作用：通过 out_time 快速定位出场记录，避免扫描所有主表分表，同时冗余 number_plate、car_park_id 等常用查询字段，提升查询效率。
三、历史数据拆分 SQL（迁移旧表数据到分表）
假设历史表为 car_park_use_log（已经创建好分片表），按 in_time 拆分到对应年月分表：
-- 1. 先创建目标分表（以2023年1月为例，其他年月需提前创建）
CREATE TABLE IF NOT EXISTS cf_car_park_use_log_202301 LIKE cf_car_park_use_log_202410;
-- 2. 迁移2023年1月的主表数据（时间戳范围：2023-01-01 00:00:00 至 2023-01-31 23:59:59）
INSERT INTO cf_car_park_use_log_202301
SELECT * FROM cf_car_park_use_log
WHERE in_time BETWEEN 1672502400 AND 1675180799 -- 时间戳替换为目标月份的起止秒级时间戳
AND deleted = 0; -- 过滤已删除数据
-- 3. 创建对应索引表并迁移数据（仅迁移已出场的记录）
CREATE TABLE IF NOT EXISTS cf_car_park_out_index_202301 LIKE cf_car_park_out_index_202410;
INSERT INTO cf_car_park_out_index_202301 (
id, number_plate, record_id, in_time_table, out_time, car_park_id, pay_time
) SELECT
id,
number_plate,
id AS record_id, -- 主表id即record_id
CONCAT('cf_car_park_use_log_', DATE_FORMAT(FROM_UNIXTIME(in_time), '%Y%m')), -- 主表分表名
out_time,
car_park_id,
pay_time
FROM cf_car_park_use_log_202301
WHERE out_time > 0 -- 仅迁移已出场的记录（out_time不为0）
AND deleted = 0;
四、动态创建分表和索引表的代码（按年月递增）
每年 12 月自动创建下一年所有月份的分表和索引表，适配你的表结构：
package com.parking.shardingdemo.task;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import java.time.LocalDate;
@Slf4j
@Component
public class AnnualShardingTableTask {
@Autowired
private JdbcTemplate jdbcTemplate;
// 每年12月25日0点执行，创建下一年所有月份的分表
@Scheduled(cron = "0 0 0 25 12 ?")
public void createNextYearTables() {
int nextYear = LocalDate.now().plusYears(1).getYear();
log.info("开始创建{}年所有分表和索引表...", nextYear);
// 循环1-12月
for (int month = 1; month <= 12; month++) {
String yearMonth = String.format("%d%02d", nextYear, month);
log.info("开始创建{}年{}月分表...", nextYear, month);
// 1. 创建主表（car_park_use_log_yyyyMM）
String mainTableSql = String.format("""
CREATE TABLE IF NOT EXISTS `car_park_use_log_%s` (
`id` varchar(64) NOT NULL DEFAULT '',
`number_plate` varchar(16) NOT NULL COMMENT '车牌号',
`in_time` bigint(19) unsigned NOT NULL DEFAULT '0' COMMENT '进场时间（分表键）',
`out_time` bigint(19) unsigned NOT NULL DEFAULT '0' COMMENT '出场时间'
PRIMARY KEY (`id`) USING BTREE,
KEY `number_plate` (`number_plate`),
KEY `in_time` (`in_time`),
KEY `out_time` (`out_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='车辆出入记录（%s年%s月）';
""", yearMonth, nextYear, month);
jdbcTemplate.execute(mainTableSql);
// 2. 创建索引表（car_park_out_index_yyyyMM）
String indexTableSql = String.format("""
CREATE TABLE IF NOT EXISTS `cf_car_park_out_index_%s` (
`id` varchar(64) NOT NULL DEFAULT '' COMMENT '自增ID（同主表id）',
`number_plate` varchar(16) NOT NULL COMMENT '车牌号（冗余，加速查询）',
`record_id` varchar(64) NOT NULL COMMENT '关联主表的id',
`in_time_table` varchar(64) NOT NULL COMMENT '主表分片表名',
`out_time` bigint(19) unsigned NOT NULL DEFAULT '0' COMMENT '出场时间（分表键）',
`car_park_id` varchar(64) NOT NULL COMMENT '停车场id（冗余，加速筛选）',
`pay_time` bigint(19) unsigned NOT NULL DEFAULT '0' COMMENT '支付时间（冗余，加速查询）',
PRIMARY KEY (`id`) USING BTREE,
KEY `out_time` (`out_time`),
KEY `number_plate` (`number_plate`),
KEY `car_park_id` (`car_park_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='车辆出场索引表（%s年%s月）';
""", yearMonth, nextYear, month);
jdbcTemplate.execute(indexTableSql);
log.info("{}年{}月分表创建完成：主表car_park_use_log_{}，索引表car_park_out_index_{}",
nextYear, month, yearMonth, yearMonth);
}
log.info("{}年所有分表和索引表创建完成！", nextYear);
}
}
启动配置：
在 Spring Boot 启动类添加 @EnableScheduling 开启定时任务：
@SpringBootApplication
@EnableScheduling
public class ParkingApplication {
public static void main(String[] args) {
SpringApplication.run(ParkingApplication.class, args);
}
}
北京人形旗下天工造物具身智能开源社区，聚焦具身天工与慧思开物两大平台
更多推荐
27
0- 0
已为社区贡献3条内容
所有评论(0)