MySQL分区与分表深度解析：从原理到实战

一、分区类型详解

MySQL分区是将一个大表物理拆分为多个更小、更易管理的部分，同时逻辑上仍表现为单一表的技术。

1.1 基础分区类型

RANGE分区

按照连续的范围值进行分区，常用于日期或数值范围。

CREATE TABLE sales (
    id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

实践建议：

• 适合有明显时间序列特征的数据
• 定期增加新分区（如每月/每年）
• 可结合ALTER TABLE ... DROP PARTITION快速删除历史数据

LIST分区

基于离散的值列表进行分区，适用于有明确分类的数据。

CREATE TABLE employees (
    id INT NOT NULL,
    name VARCHAR(50),
    store_id INT
) PARTITION BY LIST (store_id) (
    PARTITION pNorth VALUES IN (1, 3, 5),
    PARTITION pSouth VALUES IN (2, 4, 6),
    PARTITION pOther VALUES IN (DEFAULT)
);

HASH分区

通过哈希函数将数据均匀分布到不同分区。

CREATE TABLE user_logs (
    id INT NOT NULL,
    user_id INT,
    log_time DATETIME
) PARTITION BY HASH(user_id)
PARTITIONS 4;

KEY分区

类似于HASH分区，但使用MySQL服务器内置的哈希函数。

CREATE TABLE devices (
    id INT NOT NULL,
    device_name VARCHAR(100),
    location_id INT
) PARTITION BY KEY(location_id)
PARTITIONS 6;

1.2 复合分区（Subpartitioning）

在分区基础上再进行二级分区，适合超大规模数据。

CREATE TABLE financial_records (
    id INT NOT NULL,
    record_date DATE,
    branch_id INT,
    amount DECIMAL(15,2)
) PARTITION BY RANGE (YEAR(record_date))
SUBPARTITION BY HASH(branch_id)
SUBPARTITIONS 4 (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022)
);

分区选择决策树：

二、分表策略实战

2.1 水平分表（按行拆分）

将同一表的不同行存储到不同的物理表中，通常基于某个字段的哈希值或范围。

实现方式：

1. 应用层路由：在代码中根据分片键决定操作哪个表
2. 中间件路由：通过ShardingSphere等中间件透明化分表逻辑

示例场景：用户表按用户ID哈希分表

-- 原始表
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

-- 分表后
CREATE TABLE users_0 ( LIKE users );
CREATE TABLE users_1 ( LIKE users );
-- ...共16个分表

2.2 垂直分表（按列拆分）

将宽表的列拆分到不同的表中，通常按照访问频率或业务属性分组。

示例场景：电商商品表拆分

-- 原始表
CREATE TABLE products (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    description TEXT,
    supplier_info JSON,
    created_at TIMESTAMP
);

-- 垂直拆分后
CREATE TABLE product_basic (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    created_at TIMESTAMP
);

CREATE TABLE product_detail (
    product_id BIGINT PRIMARY KEY,
    description TEXT,
    supplier_info JSON
);

分表策略对比：

2.3 分库分表中间件选型

ShardingSphere生态

• 特点：Apache顶级项目，支持多种分片策略
• 优势：功能丰富，支持读写分离、分布式事务

• 示例配置（YAML）：

  spring:
  shardingsphere:
    datasource:
      names: ds0,ds1
    sharding:
      tables:
        t_order:
          actual-data-nodes: ds$->{0..1}.t_order_$->{0..15}
          table-strategy:
            inline:
              sharding-column: order_id
              algorithm-expression: t_order_$->{order_id % 16}

MyCat

• 特点：基于Proxy的中间件
• 优势：对应用透明，支持多种数据库

• 配置示例（server.xml）：

  <schema name="testdb" checkSQLschema="false">
    <table name="users" primaryKey="id" dataNode="dn1,dn2" 
           rule="mod-long" />
  </schema>

中间件选型建议：

1. 需要精细控制选ShardingSphere-JDBC
2. 多语言环境选Proxy方案
3. 简单场景可使用客户端分片

三、分区与分表的实践陷阱

3.1 常见问题解决方案

1. 全局唯一ID生成：

   • 雪花算法（Snowflake）
   • 数据库序列（如Twitter的Leaf）

2. 跨分片查询：

   // 伪代码：并行查询合并结果
   List<CompletableFuture<List<User>>> futures = shards.stream()
       .map(shard -> CompletableFuture.supplyAsync(
           () -> shard.queryUsers(condition)))
       .collect(Collectors.toList());
   
   List<User> results = futures.stream()
       .flatMap(f -> f.join().stream())
       .collect(Collectors.toList());

3. 分布式事务：

   • 柔性事务（SAGA、TCC）
   • 使用Seata等框架

3.2 监控与维护

关键监控指标：

• 分区/分表数据分布均衡性
• 跨分片查询比例
• 热点分片检测

维护脚本示例（自动增加分区）：

DELIMITER //
CREATE PROCEDURE auto_add_partition(IN schema_name VARCHAR(64), IN table_name VARCHAR(64))
BEGIN
    DECLARE next_year INT;
    DECLARE next_partition_name VARCHAR(10);
    DECLARE next_boundary VARCHAR(20);
    
    SET next_year = YEAR(CURDATE()) + 1;
    SET next_partition_name = CONCAT('p', next_year);
    SET next_boundary = CONCAT(next_year);
    
    SET @sql = CONCAT('ALTER TABLE ', schema_name, '.', table_name, 
                     ' ADD PARTITION (PARTITION ', next_partition_name, 
                     ' VALUES LESS THAN (', next_boundary, '))');
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

四、架构决策指南

何时选择分区：

• 单机大表，主要解决性能问题
• 需要利用分区裁剪优化查询
• 数据有自然生命周期（如按时间过期）

何时选择分表：

• 数据量超过单机容量
• 需要真正的水平扩展能力
• 不同分片可部署在不同硬件上

混合架构示例：

通过合理组合分区和分表技术，可以构建出既能处理海量数据，又保持良好性能的MySQL数据库架构。关键是根据具体业务特点选择最适合的拆分策略，并建立相应的运维体系。