MySQL 分库分表策略

当单表数据量超过千万级，性能瓶颈显现，分库分表是核心解决方案。

拆分策略概述

垂直拆分

垂直分库：按业务拆分数据库

SQL

拆分前：
┌─────────────────────────┐
│      单库 all_db        │
│  ├─ 用户表 users        │
│  ├─ 订单表 orders       │
│  └─ 商品表 products     │
└─────────────────────────┘

拆分后：
┌──────────┐  ┌──────────┐  ┌──────────┐
│ user_db  │  │order_db  │  │product_db│
│  users   │  │  orders  │  │ products │
└──────────┘  └──────────┘  └──────────┘

垂直分表：按列拆分，冷热分离

SQL

-- 拆分前：大宽表
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    username VARCHAR(50),
    password VARCHAR(100),
    nickname VARCHAR(50),
    avatar VARCHAR(200),
    signature TEXT,
    last_login DATETIME,
    login_count INT
);

-- 拆分后：基础信息表 + 扩展信息表
CREATE TABLE users_base (
    id BIGINT PRIMARY KEY,
    username VARCHAR(50),
    password VARCHAR(100),
    nickname VARCHAR(50)
);

CREATE TABLE users_ext (
    user_id BIGINT PRIMARY KEY,
    avatar VARCHAR(200),
    signature TEXT,
    last_login DATETIME,
    login_count INT
);

水平拆分

水平分库：同一表数据分散到多个库

SQL

orders 表按 user_id 分3个库：

┌─────────────┐  ┌─────────────┐  ┌─────────────┐
│  order_db_0 │  │  order_db_1 │  │  order_db_2 │
│  orders     │  │  orders     │  │  orders     │
│  user_id%3=0│  │  user_id%3=1│  │  user_id%3=2│
└─────────────┘  └─────────────┘  └─────────────┘

水平分表：同一库内拆分多表

SQL

-- 按 order_id 分表
orders_0  -- order_id % 4 = 0
orders_1  -- order_id % 4 = 1
orders_2  -- order_id % 4 = 2
orders_3  -- order_id % 4 = 3

分片键选择

选键原则

原则	说明
高频查询	选择最常用查询条件字段
数据均匀	避免数据倾斜
业务关联	关联查询可命中同库
无修改	分片键不应变更

常见分片键

SQL

-- 用户中心：user_id
SELECT * FROM orders WHERE user_id = 1001;

-- 订单系统：order_id 或 user_id
SELECT * FROM orders WHERE order_id = '202405150001';

-- 日志系统：create_time
SELECT * FROM logs WHERE create_time BETWEEN '2024-05-01' AND '2024-05-15';

分片路由算法

1. Hash 取模

SQL

-- 分库：user_id % 库数量
库序号 = user_id % db_count

-- 分表：order_id % 表数量
表序号 = order_id % table_count

-- 示例：user_id = 1001, 分3库
库 = 1001 % 3 = 2 → order_db_2

优点：数据分布均匀缺点：扩容需数据迁移

2. 范围分片（Range）

SQL

-- 按时间范围
orders_202401  -- 2024年1月
orders_202402  -- 2024年2月
orders_202403  -- 2024年3月

-- 按 ID 范围
orders_0_1m    -- id 1-100万
orders_1m_2m   -- id 100万-200万

优点：扩容简单，按时间查询高效缺点：数据可能不均匀

3. 一致性哈希

YAML

         节点A
           |
节点D ---- o ---- 节点B
           |
         节点C

数据分布：按哈希值顺时针定位到节点

优点：扩容只影响相邻节点缺点：实现复杂

4. 基因法（关联查询优化）

SQL

-- user_id 编码到 order_id
order_id = 时间戳 + user_id后4位 + 序列号

-- 通过 order_id 反推 user_id
user_id = (order_id >> 16) & 0xFFFF

-- 查询时可直接定位分库

主键策略

分布式主键生成

方案	实现	优点	缺点
UUID	`UUID()`	简单	无序、索引效率低
雪花算法	Snowflake	有序、分布式	依赖时钟
号段模式	数据库批量获取	高效	需要额外表
自增步长	`auto_increment_increment`	简单	扩容麻烦

SQL

-- 自增步长方案
-- 库1
SET GLOBAL auto_increment_increment = 3;
SET GLOBAL auto_increment_offset = 1;
-- 生成：1, 4, 7, 10...

-- 库2
SET GLOBAL auto_increment_increment = 3;
SET GLOBAL auto_increment_offset = 2;
-- 生成：2, 5, 8, 11...

-- 库3
SET GLOBAL auto_increment_increment = 3;
SET GLOBAL auto_increment_offset = 3;
-- 生成：3, 6, 9, 12...

中间件选型

中间件	类型	特点
ShardingSphere-JDBC	客户端	轻量、无侵入、主流
MyCat	服务端	功能全、需部署
ShardingSphere-Proxy	服务端	兼容 MySQL 协议
Vitess	服务端	YouTube 出品、大规模

ShardingSphere-JDBC 配置示例

SQL

spring:
  shardingsphere:
    datasource:
      names: ds0,ds1
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/order_db_0
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/order_db_1

    rules:
      sharding:
        tables:
          orders:
            actual-data-nodes: ds$->{0..1}.orders_$->{0..3}
            table-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: orders-inline
            key-generate-strategy:
              column: order_id
              key-generator-name: snowflake
        sharding-algorithms:
          orders-inline:
            type: INLINE
            props:
              algorithm-expression: orders_$->{user_id % 4}

常见问题与解决方案

跨库 Join

问题：分库后无法直接 JOIN

解决方案：

text

-- 方案1：应用层组装
-- 分别查询，内存合并
List<Order> orders = orderMapper.selectByUserId(userId);
List<User> users = userMapper.selectByIds(orderUserIds);

-- 方案2：冗余字段
-- 订单表冗余用户名
CREATE TABLE orders (
    order_id BIGINT,
    user_id BIGINT,
    user_name VARCHAR(50),  -- 冗余
    ...
);

-- 方案3：绑定表（同库）
-- user_id 相同的用户表和订单表路由到同一库

分页查询

text

-- 问题：跨库分页结果不准
-- 方案1：各库查全量，内存聚合
SELECT * FROM orders WHERE user_id = ? LIMIT 100;

-- 方案2：路由到单库
SELECT * FROM orders WHERE user_id = ? LIMIT 10 OFFSET 0;

全局表

text

-- 配置表、字典表在所有库同步
CREATE TABLE config (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    value VARCHAR(500)
);

-- 各库保持数据一致，避免跨库查询

要点总结

垂直拆分：按业务或列拆分，降低单表宽度
水平拆分：按数据行拆分，降低单表数据量
分片键选择：高频查询、数据均匀、业务关联
路由算法：Hash 取模（均匀）、Range（易扩容）、一致性哈希
主键策略：雪花算法为主流分布式 ID 方案
中间件选型：ShardingSphere-JDBC 客户端方案最常用

📝 发现内容有误？点击此处直接编辑