🗂️ MySQL分区表：大象装进冰箱的艺术

考察点： Range/Hash/List分区、分区裁剪、分区限制

🎬 开场：一个关于档案室的故事

想象你是一个图书管理员，面对一个装满了1000万本书的巨大书架 📚。如果有人问你："我要找2023年出版的所有关于MySQL的书"，你该怎么办？

方式一（没有分区）： 😰
从第一本书开始，一本一本翻看...可能要找几天！

方式二（有分区）： 😎

• 先按年份分区：2020年的在1号柜，2021年的在2号柜...
• 再按主题分类：MySQL在A区，Java在B区...
• 直接去"2023年柜子→MySQL区"，几分钟搞定！

这就是MySQL分区表的核心思想：把大表拆分成多个小表，让查询更快！ 🚀

---

第一部分：什么是分区表？ 📖

1.1 分区表的定义

分区（Partitioning） 是指将一个大表的数据，按照特定规则，物理上分割成多个更小的、更易管理的部分，但从逻辑上看，它仍然是一个表。

+-------------------+          +--------+  分区0（2021年数据）
|                   |   ====>  +--------+  分区1（2022年数据）
|   orders表        |          +--------+  分区2（2023年数据）
| （1000万条数据）   |          +--------+  分区3（2024年数据）
+-------------------+

1.2 为什么需要分区？

问题场景：

-- 有一个订单表，10亿条数据
SELECT * FROM orders 
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

没有分区时：

• MySQL要扫描整个表（10亿条）
• 即使有索引，也要扫描2024年的所有索引数据
• 查询慢 🐌，备份难 💾，维护累 😫

有分区后：

• MySQL只扫描2024年的分区（1亿条）
• 直接跳过其他分区
• 速度提升10倍！ ⚡

1.3 分区的优势 ✅

1. 提高查询性能

   • 分区裁剪：只扫描相关分区
   • 并行查询：多个分区并行执行

2. 便于数据管理

   -- 删除历史数据超快！
   ALTER TABLE orders DROP PARTITION p2020;  -- 秒删
   -- 不分区的话：DELETE FROM orders WHERE year = 2020;  -- 可能要几小时
   

3. 提高可用性

   • 某个分区损坏，其他分区仍可用
   • 可以单独备份某个分区

4. 支持大表

   • 突破单表大小限制
   • 单个分区更小，维护更容易

1.4 分区的劣势 ❌

1. 查询不带分区键时无效

   -- 如果按日期分区，但查询不用日期
   SELECT * FROM orders WHERE customer_id = 123;  
   -- MySQL还是要扫描所有分区！
   

2. 某些功能受限

   • 不支持外键
   • 不支持全文索引（5.7之前）
   • 表锁可能更频繁

3. 维护成本

   • 需要定期创建新分区
   • 分区过多会影响性能

---

第二部分：分区类型详解 🎯

MySQL支持多种分区类型，让我们一一击破！

2.1 RANGE分区（范围分区）⭐⭐⭐⭐⭐

最常用！ 按照某个字段的范围来分区。

🌰 场景：按日期分区的订单表

CREATE TABLE orders (
    order_id BIGINT NOT NULL AUTO_INCREMENT,
    customer_id INT,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (order_id, order_date)  -- ⚠️ 主键必须包含分区键
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2021 VALUES LESS THAN (2022),  -- 2021年及以前
    PARTITION p2022 VALUES LESS THAN (2023),  -- 2022年
    PARTITION p2023 VALUES LESS THAN (2024),  -- 2023年
    PARTITION p2024 VALUES LESS THAN (2025),  -- 2024年
    PARTITION p_future VALUES LESS THAN MAXVALUE  -- 2025年及以后
);

📊 数据分布：

分区p2021: order_date < '2022-01-01' 的数据
分区p2022: '2022-01-01' <= order_date < '2023-01-01' 的数据
分区p2023: '2023-01-01' <= order_date < '2024-01-01' 的数据
分区p2024: '2024-01-01' <= order_date < '2025-01-01' 的数据
分区p_future: order_date >= '2025-01-01' 的数据

查询示例：

-- ✅ 好查询：利用分区裁剪
SELECT * FROM orders 
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';
-- 只扫描 p2024 分区

-- ❌ 差查询：无法利用分区
SELECT * FROM orders WHERE customer_id = 123;
-- 扫描所有分区

查看分区裁剪效果：

EXPLAIN PARTITIONS 
SELECT * FROM orders 
WHERE order_date = '2024-06-15';

-- 输出示例：
-- partitions: p2024  ← 只访问了一个分区！

生活例子 🏢：

想象一个公司的人事档案室：

• 2021年入职的员工档案在1号柜
• 2022年入职的在2号柜
• 2023年入职的在3号柜

要查"2024年入职的小王"，直接去4号柜翻！

---

2.2 RANGE COLUMNS分区（增强版RANGE）

RANGE只能用整数或返回整数的表达式，RANGE COLUMNS更灵活！

-- RANGE COLUMNS支持日期、字符串等类型
CREATE TABLE orders (
    order_id BIGINT NOT NULL,
    order_date DATE NOT NULL,
    region VARCHAR(50),
    amount DECIMAL(10,2),
    PRIMARY KEY (order_id, order_date)
)
PARTITION BY RANGE COLUMNS(order_date) (  -- 直接用DATE类型
    PARTITION p_q1 VALUES LESS THAN ('2024-04-01'),
    PARTITION p_q2 VALUES LESS THAN ('2024-07-01'),
    PARTITION p_q3 VALUES LESS THAN ('2024-10-01'),
    PARTITION p_q4 VALUES LESS THAN ('2025-01-01')
);

优点： 不需要用函数包装，分区裁剪更高效！

---

2.3 LIST分区（列表分区）⭐⭐⭐

按照离散值分区，适合有固定分类的场景。

🌰 场景：按地区分区

CREATE TABLE users (
    user_id INT NOT NULL,
    name VARCHAR(50),
    region VARCHAR(20),
    created_at DATE,
    PRIMARY KEY (user_id, region)
)
PARTITION BY LIST COLUMNS(region) (
    PARTITION p_north VALUES IN ('北京', '天津', '河北'),
    PARTITION p_east VALUES IN ('上海', '江苏', '浙江'),
    PARTITION p_south VALUES IN ('广东', '深圳', '海南'),
    PARTITION p_west VALUES IN ('四川', '重庆', '云南')
);

查询示例：

-- ✅ 只扫描 p_north 分区
SELECT * FROM users WHERE region = '北京';

-- ✅ 只扫描 p_east 和 p_south 分区
SELECT * FROM users WHERE region IN ('上海', '广东');

生活例子 🗺️：

超市的商品分区：

• 生鲜区：蔬菜、水果、肉类
• 日用区：洗衣粉、牙膏、纸巾
• 零食区：薯片、饮料、糖果

按商品类别直接去对应区域找！

---

2.4 HASH分区（哈希分区）⭐⭐⭐

按照哈希值分区，数据分布均匀。

🌰 场景：按用户ID分区

CREATE TABLE user_sessions (
    session_id BIGINT NOT NULL,
    user_id INT NOT NULL,
    login_time DATETIME,
    ip_address VARCHAR(50),
    PRIMARY KEY (session_id, user_id)
)
PARTITION BY HASH(user_id)
PARTITIONS 8;  -- 分成8个分区

数据分布：

user_id % 8 = 0  →  分区p0
user_id % 8 = 1  →  分区p1
user_id % 8 = 2  →  分区p2
...
user_id % 8 = 7  →  分区p7

优缺点：

✅ 优点：

• 数据分布均匀
• 适合不好做范围分区的场景

❌ 缺点：

• 无法按范围删除数据
• 增加分区要重新分布所有数据（成本高）

生活例子 🎰：

银行柜台排号：

• 尾号1、2的去1号窗口
• 尾号3、4的去2号窗口
• 尾号5、6的去3号窗口
• ...

保证每个窗口的人数大致相同！

---

2.5 KEY分区

类似HASH分区，但使用MySQL自己的哈希函数。

CREATE TABLE sessions (
    session_id VARCHAR(100) NOT NULL,
    user_id INT,
    data TEXT,
    PRIMARY KEY (session_id)
)
PARTITION BY KEY(session_id)
PARTITIONS 4;

区别：

• HASH需要整数列
• KEY可以用任何类型（字符串、日期等）

---

2.6 子分区（二级分区）⭐⭐

先按一种方式分区，再在每个分区内按另一种方式分区。

CREATE TABLE sales (
    sale_id BIGINT NOT NULL,
    sale_date DATE NOT NULL,
    region VARCHAR(20),
    amount DECIMAL(10,2),
    PRIMARY KEY (sale_id, sale_date, region)
)
PARTITION BY RANGE (YEAR(sale_date))
SUBPARTITION BY HASH(region)
SUBPARTITIONS 4 (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

结构：

p2023
├── p2023_sp0  (2023年 + region哈希值0)
├── p2023_sp1  (2023年 + region哈希值1)
├── p2023_sp2  (2023年 + region哈希值2)
└── p2023_sp3  (2023年 + region哈希值3)
p2024
├── p2024_sp0
├── p2024_sp1
├── p2024_sp2
└── p2024_sp4

---

第三部分：分区的使用限制 ⚠️

3.1 主键/唯一键限制（最坑的一个！）

规则： 主键和唯一键必须包含分区键！

❌ 错误示例：

-- 错误：主键不包含分区键order_date
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,  -- ❌ 缺少order_date
    order_date DATE NOT NULL,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);
-- 报错：A PRIMARY KEY must include all columns in the table's partitioning function

✅ 正确示例：

-- 方案1：联合主键
CREATE TABLE orders (
    order_id BIGINT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (order_id, order_date)  -- ✅ 包含分区键
)
PARTITION BY RANGE (YEAR(order_date)) (...);

-- 方案2：去掉主键（用唯一索引代替）
CREATE TABLE orders (
    order_id BIGINT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2),
    UNIQUE KEY uk_order_id (order_id, order_date)  -- ✅ 包含分区键
)
PARTITION BY RANGE (YEAR(order_date)) (...);

为什么有这个限制？ 🤔

因为MySQL需要通过分区键快速定位数据在哪个分区。如果主键不包含分区键：

查询：SELECT * FROM orders WHERE order_id = 123;
MySQL：这个order_id在哪个分区？我不知道！只能扫描所有分区...

3.2 外键限制

分区表不支持外键！

-- ❌ 这样做不行
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id)  -- ❌ 不支持
)
PARTITION BY RANGE (YEAR(order_date)) (...);

解决方案：

• 在应用层保证数据完整性
• 或者不用分区表

3.3 全文索引限制

-- MySQL 5.7之前不支持
CREATE TABLE articles (
    id INT PRIMARY KEY,
    content TEXT,
    created_at DATE,
    FULLTEXT INDEX ft_content (content)  -- ⚠️ 5.7之前不行
)
PARTITION BY RANGE (YEAR(created_at)) (...);

MySQL 5.7+ 已经支持！

3.4 其他限制

1. 最大分区数： 8192个（但实际建议不超过1000个）

2. 不支持的查询：

   -- ❌ 不支持：LOAD INDEX INTO CACHE
   -- ❌ 不支持：CHECK/OPTIMIZE/ANALYZE TABLE的某些操作
   

3. 临时表不支持分区

4. 分区键必须是整数或返回整数的表达式（RANGE/HASH分区）

---

第四部分：分区管理操作 🛠️

4.1 添加分区

-- 添加新年份的分区
ALTER TABLE orders ADD PARTITION (
    PARTITION p2026 VALUES LESS THAN (2027)
);

4.2 删除分区

-- 删除旧数据（超快！）
ALTER TABLE orders DROP PARTITION p2020;
-- 数据直接删除，不走DELETE逻辑，秒删！

4.3 查看分区信息

-- 查看分区定义
SHOW CREATE TABLE orders;

-- 查看分区详细信息
SELECT 
    PARTITION_NAME,
    PARTITION_METHOD,
    PARTITION_EXPRESSION,
    TABLE_ROWS,
    DATA_LENGTH
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'orders';

4.4 重组分区

-- 合并分区
ALTER TABLE orders 
REORGANIZE PARTITION p2020, p2021 INTO (
    PARTITION p_old VALUES LESS THAN (2022)
);

-- 拆分分区
ALTER TABLE orders 
REORGANIZE PARTITION p_future INTO (
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

4.5 分区裁剪（Partition Pruning）

-- 查看是否使用了分区裁剪
EXPLAIN PARTITIONS
SELECT * FROM orders 
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- 输出：
-- partitions: p2024  ← 只扫描了一个分区！

---

第五部分：实战案例 💼

案例1：日志表分区（最典型）

需求：

• 日志表数据量大（每天1000万条）
• 查询都是按日期范围
• 需要定期删除历史数据

设计：

CREATE TABLE app_logs (
    log_id BIGINT NOT NULL AUTO_INCREMENT,
    user_id INT,
    action VARCHAR(50),
    log_time DATETIME NOT NULL,
    details TEXT,
    PRIMARY KEY (log_id, log_time)
)
PARTITION BY RANGE (TO_DAYS(log_time)) (
    PARTITION p20240101 VALUES LESS THAN (TO_DAYS('2024-01-02')),
    PARTITION p20240102 VALUES LESS THAN (TO_DAYS('2024-01-03')),
    PARTITION p20240103 VALUES LESS THAN (TO_DAYS('2024-01-04')),
    -- ... 每天一个分区
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

自动化脚本（定期创建新分区）：

#!/usr/bin/env python3
import pymysql
from datetime import datetime, timedelta

def add_tomorrow_partition():
    tomorrow = datetime.now() + timedelta(days=1)
    partition_name = f"p{tomorrow.strftime('%Y%m%d')}"
    next_day = tomorrow + timedelta(days=1)
    
    sql = f"""
    ALTER TABLE app_logs ADD PARTITION (
        PARTITION {partition_name} 
        VALUES LESS THAN (TO_DAYS('{next_day.strftime('%Y-%m-%d')}'))
    )
    """
    
    conn = pymysql.connect(host='localhost', user='root', password='xxx', db='mydb')
    try:
        with conn.cursor() as cursor:
            cursor.execute(sql)
        conn.commit()
        print(f"✅ 成功创建分区：{partition_name}")
    except Exception as e:
        print(f"❌ 创建失败：{e}")
    finally:
        conn.close()

# 每天0点执行
if __name__ == '__main__':
    add_tomorrow_partition()

删除历史数据（超快）：

-- 删除30天前的数据
ALTER TABLE app_logs DROP PARTITION p20231201;
-- 秒删！不会产生大量binlog

---

案例2：订单表分区

CREATE TABLE orders (
    order_id BIGINT NOT NULL,
    user_id INT NOT NULL,
    order_date DATE NOT NULL,
    status TINYINT,
    amount DECIMAL(10,2),
    PRIMARY KEY (order_id, order_date),
    KEY idx_user (user_id, order_date)
)
PARTITION BY RANGE COLUMNS(order_date) (
    PARTITION p_2023_q1 VALUES LESS THAN ('2023-04-01'),
    PARTITION p_2023_q2 VALUES LESS THAN ('2023-07-01'),
    PARTITION p_2023_q3 VALUES LESS THAN ('2023-10-01'),
    PARTITION p_2023_q4 VALUES LESS THAN ('2024-01-01'),
    PARTITION p_2024_q1 VALUES LESS THAN ('2024-04-01'),
    PARTITION p_2024_q2 VALUES LESS THAN ('2024-07-01'),
    PARTITION p_2024_q3 VALUES LESS THAN ('2024-10-01'),
    PARTITION p_2024_q4 VALUES LESS THAN ('2025-01-01'),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 查询当前季度订单（只扫描一个分区）
SELECT * FROM orders 
WHERE order_date >= '2024-10-01' AND order_date < '2025-01-01';

---

案例3：按用户HASH分区（负载均衡）

-- 用户活跃度表
CREATE TABLE user_activity (
    id BIGINT NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    action_type VARCHAR(50),
    action_time DATETIME,
    PRIMARY KEY (id, user_id)
)
PARTITION BY HASH(user_id)
PARTITIONS 16;  -- 16个分区，负载均衡

-- 查询某个用户（只扫描一个分区）
SELECT * FROM user_activity WHERE user_id = 12345;

---

第六部分：分区表 vs 分表 🥊

6.1 对比表格

维度	分区表	分表（应用层分表）
实现方式	MySQL内置	应用层路由
SQL复杂度	透明，无需改SQL	需要改SQL
跨分区查询	支持	需要应用层聚合
分区数量	最多8192个	理论无限
主键限制	必须包含分区键	无限制
事务支持	完整支持	跨分区事务困难
扩展性	单机	可分布式
适用数据量	单表亿级	十亿级以上

6.2 选择建议

数据量 < 1亿  →  不分区，用索引优化
1亿 < 数据量 < 5亿  →  分区表
数据量 > 5亿  →  分库分表（ShardingSphere等）

---

第七部分：性能优化建议 ⚡

7.1 选择合适的分区键

✅ 好的分区键：

• 查询经常用到
• 数据分布均匀
• 不经常更新

❌ 不好的分区键：

• 查询很少用到 → 无法利用分区裁剪
• 数据分布不均 → 某些分区特别大
• 经常更新 → 可能触发跨分区移动

7.2 控制分区数量

分区数量建议：
- 日志表：按天分区，保留90天 = 90个分区 ✅
- 订单表：按季度分区，保留5年 = 20个分区 ✅
- 不要搞几千个分区 ❌

7.3 定期维护

-- 定期分析分区
ANALYZE TABLE orders PARTITION (p2024_q4);

-- 优化分区
OPTIMIZE TABLE orders PARTITION (p2024_q4);

-- 检查分区
CHECK TABLE orders PARTITION (p2024_q4);

---

🎓 总结：分区表决策树

              [表数据量大吗？]
                  /        \
               < 1亿        > 1亿
                 |            |
             [不分区]      [查询有规律吗？]
                          /           \
                    有规律            无规律
                     |                  |
              [按规律分区]      [考虑分库分表]
                  |
           [日期？地区？用户？]
              /      |      \
           日期     地区    用户
            |       |       |
         RANGE    LIST    HASH

---

📚 面试要点

1. 分区类型：RANGE（最常用）、LIST、HASH、KEY
2. 主键限制：必须包含分区键
3. 分区裁剪：只扫描相关分区，提高性能
4. 使用场景：日志表、历史数据、大表优化
5. 删除数据：DROP PARTITION比DELETE快得多
6. 分区 vs 分表：数据量、跨分区查询、分布式需求

---

最后总结：
分区表是把大象装进冰箱的艺术 🐘 ➡️ 🧊
第一步：打开冰箱（创建分区）
第二步：把大象分块（按规则分区）
第三步：关上冰箱（优雅查询）

用得好，性能起飞 🚀
用不好，徒增烦恼 😵

加油！💪