记录一次MySQL的分库分表行为

场景：原库因为数据量过大，导致慢查询漫天飞，总数据量已经千万以上，常见优化手段已经无效，初始设计没给足够时间应对这种场景方案和配套分离搭建，现在出时间到了。特殊场景：只有每天最新的数据会经常使用，范围广点也就是今天的数据，之前的数据极少使用，偶尔会出现一些调用。

一、分库分表核心策略

时间范围分片（冷热分离）

热库存储近期数据：将最近3个月（或高频访问周期）的数据单独存放于高性能数据库集群（如SSD存储），采用水平分表策略（如按天/周分表），满足高并发读写需求。
冷库存储历史数据：超过时间阈值的数据迁移至低成本存储（如HDD或列式数据库），仅保留基本查询功能。可通过按月/年分库分表，降低单表数据量。
动态分片规则

分片键选择：以时间字段（如create_time）为主分片键，结合业务ID哈希作为二级分片键，避免单一时间分片导致的数据倾斜。
自动滚动分表：使用中间件（如ShardingSphere）配置时间间隔规则，例如按月自动创建新表，确保新数据写入最新分片，历史表只读。

二、架构优化关键点

冷热数据迁移机制

异步归档工具：通过定时任务将冷数据迁移至历史库，迁移期间采用双写机制保证数据一致性，完成后触发索引重建。
透明化路由：业务层通过中间件自动路由查询请求，近期数据访问热库，历史查询自动转发至冷库，对应用无感知。
查询性能优化

热点数据缓存：对近期数据引入Redis缓存，减少数据库压力。
历史数据索引优化：冷库采用压缩表格式（如InnoDB压缩表），并为常用查询字段建立覆盖索引。复杂查询可同步至Elasticsearch实现聚合分析。
分布式事务处理

最终一致性补偿：跨冷热库操作采用异步消息队列（如RocketMQ）保证最终一致性，例如订单状态变更后异步更新冷库统计信息。
本地事务+柔性事务：热库内事务使用本地ACID，跨库操作通过TCC模式（Try-Confirm-Cancel）回滚。

三、实施步骤与工具

中间件选型

方案：采用ShardingSphere，配置动态分片规则示例：
yaml
spring:
shardingsphere:
rules:
sharding:
tables:
order:
actual-data-nodes: ds_ $KaTeX parse error: Expected group after '_' at position 13: {0..1}.order_̲$ {202401…202412}
table-strategy:
interval:
datetime-pattern: “yyyy-MM-dd”
datetime-lower: “2024-01-01”
sharding-suffix-pattern: “yyyyMM”
datetime-interval-unit: “MONTHS”
此配置实现按月自动分表，新数据写入当月表，历史表只读。
数据迁移与扩容