MySQL分区、分表、分库从原理到生产最佳实践记录_Mysql

前言

单机 mysql 的瓶颈如下：

关于分库、分表和读写分离的架构图如下所示；

1、分库分表分区

1.1、联系

技术	物理位置	逻辑视图	是否跨实例
分区（partitioning）	同一表、同一库、同一 mysql 实例	仍是一个表	否
分表（table sharding）	多个表（如 `user_0`, `user_1`），同一库	应用需知道多个表	否
分库（database sharding）	多个数据库（如 `db_0`, `db_1`），可能跨 mysql 实例	应用需路由到不同库	是

核心思想：

分区：数据库内部优化
分库分表：应用层或中间件实现的分布式架构

1.2、对比

能力	分区	分表	分库
是否跨实例	❌	❌	✅
应用是否感知	❌（透明）	✅（需拼表名）	✅（需路由）
突破单机瓶颈	❌	❌（仍在单库）	✅
支持跨分片查询	✅（自动裁剪）	❌	❌（需中间件模拟）
扩容难度	低	高	极高
典型工具	mysql 原生	应用层	shardingsphere, vitess

2、分区（partitioning）

2.1、介绍

mysql 原生支持将一个大表的数据物理拆分到多个“分区”中，但逻辑上仍是一个表，数据库内置能力。

开发人员在数据操作时仍然是对这个整体大表进行操作，之后由数据库底层内部去寻找对应的分区进行操作，这样在数据操作时可以只对特定分区操作以提高效率，存储时也可以将不同分区的物理文件分开存放。

2.2、核心原理

物理存储分离：每个分区是一个独立 .ibd 文件
逻辑视图统一：应用仍操作一个表名
查询自动裁剪：优化器只扫描相关分区

2.3、常见分区类型

1.range 分区（按范围）

如下所示：

-- 按年份分区
create table sales (
  id bigint,
  sale_date date,
  amount decimal(10,2)
) partition by range (year(sale_date)) (
  partition p2022 values less than (2023),
  partition p2023 values less than (2024),
  partition p2024 values less than (2025),
  partition p_future values less than maxvalue
);

-- 查询自动裁剪
explain select * from sales where sale_date = '2023-06-01';
-- 只扫描 p2023 分区

(2) list 分区（按枚举值）

如下所示：

-- 按地区分区
create table users (
  id bigint,
  region varchar(10)
) partition by list columns(region) (
  partition p_north values in ('bj', 'tj'),
  partition p_south values in ('gz', 'sz'),
  partition p_west values in ('cd', 'xa')
);

(3) hash 分区（均匀分布）

如下所示：

-- 按 user_id 均匀分布到 4 个分区
create table orders (
  id bigint,
  user_id bigint
) partition by hash(user_id) partitions 4;

(4) key 分区（类似 hash，支持多列）

如下所示：

partition by key(user_id, order_type) partitions 8;

2.4、分区管理命令

-- 添加新分区
alter table sales add partition (
  partition p2025 values less than (2026)
);

-- 删除旧分区（秒级！）
alter table sales drop partition p2022;

-- 重建分区（整理碎片）
alter table sales rebuild partition p2023;

✅ 优点

对应用透明：sql 不用改，select * from logs where create_time > '2024-01-01' 自动只查 p2024
提升查询性能：分区裁剪（partition pruning）
方便数据管理：alter table logs drop partition p2023; 快速删除旧数据

❌ 缺点

仍在单机：无法突破单 mysql 实例的 cpu/内存/磁盘瓶颈
分区数有限：通常建议 < 100 个分区
不支持所有引擎：仅 innodb、myisam 支持

📌 适用场景

单表过大（> 1000万行），但总数据量未超单机容量
按时间冷热分离（如日志、订单）
需要快速删除历史数据

3、分表（table sharding）

3.1、介绍

将一张大表拆成多张结构相同的表，应用层拆分。

如下所示：

3.2、使用原因

为什么需要分表？

innodb 单表建议 < 5000万行（b+树深度增加）
表锁/元数据锁竞争（ddl 操作阻塞）

如：

user_0, user_1, user_2, user_3

如何路由？

通常用 分片键（shard key） + 取模/哈希 决定数据存哪张表。

如下所示：

示例：按 user_id 分 4 张表

// java 伪代码
public string gettablename(long userid) {
    int tableindex = (int) (userid % 4);
    return "user_" + tableindex;
}

// 查询用户
string sql = "select * from " + gettablename(userid) + " where id = ?";

3.3、分片策略设计

1.分片键选择

字段	优点	缺点
user_id	用户数据聚集	热点用户问题
order_id	均匀分布	无法按用户查订单
tenant_id	多租户隔离	租户数据不均

最佳实践：选高频查询字段作为分片键

2.分片算法

// 取模（简单但扩容难）
int tableindex = userid % tablecount;

// 一致性哈希（扩容友好）
hashfunction hash = hashing.murmur3_32();
int bucket = hash.hashlong(userid).asint() & integer.max_value;
int tableindex = bucket % tablecount;

3.4、mybatis + 分表

步骤 1：定义分表路由

@component
public class tableshardingrouter {
    private static final int table_count = 4;
    
    public string gettablename(string basename, long shardkey) {
        int index = (int) (shardkey % table_count);
        return basename + "_" + index;
    }
}

步骤 2：mybatis 动态表名

<!-- usermapper.xml -->
<select id="selectbyid" resulttype="user">
  select * from ${tablename} where id = #{id}
</select>

步骤 3：service 层调用

@service
public class userservice {
    @autowired
    private tableshardingrouter router;
    
    @autowired
    private usermapper usermapper;
    
    public user getuser(long userid) {
        string tablename = router.gettablename("user", userid);
        return usermapper.selectbyid(tablename, userid);
    }
}

优点

突破单表性能瓶颈（innodb 单表建议 < 5000万行）
实现简单，无需中间件

缺点

应用强耦合：每个 sql 都要拼表名；
无法跨表 join / 聚合：select count(*) from user_* 需查 4 次再 sum；
扩容困难：从 4 表扩到 8 表，需迁移一半数据；

适用场景

单库能扛住，但单表太大
查询基本都带分片键（如 where user_id = ?）

4、分库（database sharding）

4.1、分库原因

为什么需要分库？

单机资源耗尽（cpu/内存/io）
连接数瓶颈（max_connections）
多租户数据隔离需求

4.2、分库分表组合模式

模式 1：库内分表（推荐）

4 个库 × 每库 16 张表 = 64 分片
优点：减少数据库连接数

模式 2：仅分库

4 个库 × 每库 1 张表
优点：简化表结构

📊 计算公式：

总分片数 = 库数量 × 表数量；

分片id = hash(shard_key) % 总分片数；
库名 = 分片id / 表数量；

表名 = 分片id % 表数量

4.3、架构

如下所示：

是什么？

将数据分散到多个数据库实例（可能在不同机器），真正的分布式。

如：

db_0（ip: 10.0.0.1）
db_1（ip: 10.0.0.2）

通常分库 + 分表一起用，如：

db_0.user_0, db_0.user_1
db_1.user_0, db_1.user_1

架构图

application
│
├── router (shardingsphere / mycat / 自研)
│ │
│ ├── db_0 (10.0.0.1)
│ │ ├── user_0
│ │ └── user_1
│ │
│ └── db_1 (10.0.0.2)
│ ├── user_0
│ └── user_1

优点