Carry の Blog
Carry の Blog
2024-07-27
目录

分区表详解

# 分区表详解

分区表是MySQL中一种重要的数据管理技术，它将大表分割成多个较小、更易管理的部分，每个部分称为一个分区。本文将深入探讨MySQL分区表的原理、使用方法和优化策略。

# 1. 分区表基础概念

# 1.1 分区表定义

分区表是将一个大表的数据按照某种规则分散存储在多个物理文件中的技术。每个分区在逻辑上仍然是一个完整的表，但在物理存储上是独立的。

-- 创建分区表的基本语法
CREATE TABLE partition_table (
    id INT,
    name VARCHAR(50),
    created_date DATE,
    amount DECIMAL(10,2)
) 
PARTITION BY RANGE (YEAR(created_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);
1
2
3
4
5
6
7
8
9
10
11
12
13

# 1.2 分区表的优势

-- 分区表的主要优势：
-- 1. 提高查询性能：只扫描相关分区
-- 2. 简化数据管理：可以单独管理分区
-- 3. 提高维护效率：可以单独备份/恢复分区
-- 4. 改善数据可用性：部分分区故障不影响整体
-- 5. 支持大数据量：将大表分割成小块

-- 性能对比示例
-- 无分区表：全表扫描
SELECT * FROM large_table WHERE created_date = '2024-01-01';

-- 分区表：只扫描相关分区
SELECT * FROM partition_table WHERE created_date = '2024-01-01';
1
2
3
4
5
6
7
8
9
10
11
12
13

# 1.3 分区表的类型

-- MySQL支持的分区类型：
-- 1. RANGE分区：基于范围值
-- 2. LIST分区：基于枚举值
-- 3. HASH分区：基于哈希函数
-- 4. KEY分区：基于KEY的哈希值
-- 5. COLUMNS分区：基于列值（MySQL 8.0+）

-- RANGE分区示例
CREATE TABLE sales (
    id INT,
    sale_date DATE,
    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 p_future VALUES LESS THAN MAXVALUE
);

-- LIST分区示例
CREATE TABLE regions (
    id INT,
    region_name VARCHAR(50),
    sales_amount DECIMAL(10,2)
) 
PARTITION BY LIST COLUMNS(region_name) (
    PARTITION p_north VALUES IN ('North', 'Northeast'),
    PARTITION p_south VALUES IN ('South', 'Southeast'),
    PARTITION p_west VALUES IN ('West', 'Northwest'),
    PARTITION p_east VALUES IN ('East', 'Midwest')
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

# 2. 分区表创建与管理

# 2.1 RANGE分区详解

-- RANGE分区详解
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10,2),
    status VARCHAR(20)
) 
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 查看分区信息
SELECT 
    PARTITION_NAME,
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH
FROM information_schema.PARTITIONS 
WHERE TABLE_NAME = 'orders' 
AND TABLE_SCHEMA = 'your_database';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

# 2.2 LIST分区详解

-- LIST分区详解
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    department VARCHAR(30),
    salary DECIMAL(10,2)
) 
PARTITION BY LIST COLUMNS(department) (
    PARTITION p_it VALUES IN ('IT', 'Development', 'DevOps'),
    PARTITION p_sales VALUES IN ('Sales', 'Marketing'),
    PARTITION p_hr VALUES IN ('HR', 'Recruitment'),
    PARTITION p_finance VALUES IN ('Finance', 'Accounting')
);

-- 插入数据
INSERT INTO employees VALUES 
(1, 'John Doe', 'IT', 75000),
(2, 'Jane Smith', 'Sales', 65000),
(3, 'Bob Johnson', 'HR', 60000);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 2.3 HASH分区详解

-- HASH分区详解
CREATE TABLE logs (
    log_id INT PRIMARY KEY,
    log_date DATETIME,
    log_level VARCHAR(10),
    message TEXT
) 
PARTITION BY HASH(log_id) 
PARTITIONS 4;

-- KEY分区示例
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2)
) 
PARTITION BY KEY(category) 
PARTITIONS 3;

-- 查看分区状态
SHOW CREATE TABLE logs;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

# 3. 分区表查询优化

# 3.1 分区裁剪

-- 分区裁剪是分区表最重要的优化特性
-- 查询时MySQL会自动识别需要扫描的分区

-- 优化前：全表扫描
SELECT * FROM orders WHERE order_date = '2023-06-15';

-- 优化后：分区裁剪
-- 如果order_date在2023年，只会扫描p2023分区

-- 查看查询执行计划
EXPLAIN SELECT * FROM orders WHERE order_date = '2023-06-15';
1
2
3
4
5
6
7
8
9
10
11

# 3.2 分区选择性分析

-- 分析分区选择性
-- 1. 选择合适的分区键
-- 2. 确保分区均匀分布

-- 查看分区数据分布
SELECT 
    PARTITION_NAME,
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH
FROM information_schema.PARTITIONS 
WHERE TABLE_NAME = 'orders' 
AND TABLE_SCHEMA = 'your_database'
ORDER BY TABLE_ROWS DESC;

-- 分区键选择示例
-- 好的分区键：按时间分区，数据分布均匀
-- 不好的分区键：按地区分区，某些地区数据特别多
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

# 3.3 复合分区策略

-- 复合分区策略
-- 1. 范围+HASH组合
-- 2. 范围+LIST组合

-- 范围+HASH复合分区
CREATE TABLE sales_data (
    sale_id INT,
    sale_date DATE,
    region VARCHAR(20),
    amount DECIMAL(10,2)
) 
PARTITION BY RANGE (YEAR(sale_date)) 
SUBPARTITION BY HASH(region) 
SUBPARTITIONS 4 (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 范围+LIST复合分区
CREATE TABLE user_activity (
    user_id INT,
    activity_date DATE,
    activity_type VARCHAR(20),
    data TEXT
) 
PARTITION BY RANGE (YEAR(activity_date)) 
SUBPARTITION BY LIST COLUMNS(activity_type) (
    PARTITION p2020 VALUES LESS THAN (2021) (
        SUBPARTITION sp_login VALUES IN ('login'),
        SUBPARTITION sp_view VALUES IN ('view'),
        SUBPARTITION sp_download VALUES IN ('download')
    ),
    PARTITION p2021 VALUES LESS THAN (2022) (
        SUBPARTITION sp_login VALUES IN ('login'),
        SUBPARTITION sp_view VALUES IN ('view'),
        SUBPARTITION sp_download VALUES IN ('download')
    )
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

# 4. 分区表维护

# 4.1 分区添加与删除

-- 添加新分区
ALTER TABLE orders 
ADD PARTITION (
    PARTITION p2024 VALUES LESS THAN (2025)
);

-- 删除分区
ALTER TABLE orders 
DROP PARTITION p2020;

-- 合并分区
ALTER TABLE orders 
COALESCE PARTITION 2;

-- 重新分区
ALTER TABLE orders 
REORGANIZE PARTITION p2022 INTO (
    PARTITION p2022_q1 VALUES LESS THAN (20220401),
    PARTITION p2022_q2 VALUES LESS THAN (20220701),
    PARTITION p2022_q3 VALUES LESS THAN (20221001),
    PARTITION p2022_q4 VALUES LESS THAN (20230101)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

# 4.2 分区数据管理

-- 分区数据管理
-- 1. 分区数据导入导出
-- 2. 分区数据备份恢复
-- 3. 分区数据清理

-- 导出特定分区数据
SELECT * FROM orders PARTITION (p2023);

-- 分区数据备份
-- 可以单独备份特定分区
mysqldump -u user -p database_name orders --where="PARTITION(p2023)";

-- 分区数据清理
-- 清理特定分区数据
DELETE FROM orders PARTITION (p2020) WHERE amount < 100;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 4.3 分区统计信息更新

-- 更新分区统计信息
-- 1. ANALYZE TABLE更新统计信息
-- 2. 优化查询计划

-- 更新分区统计信息
ANALYZE TABLE orders PARTITION (p2023);

-- 更新所有分区统计信息
ANALYZE TABLE orders;

-- 查看分区统计信息
SHOW INDEX FROM orders PARTITION (p2023);
1
2
3
4
5
6
7
8
9
10
11
12

# 5. 分区表性能优化

# 5.1 分区键选择优化

-- 分区键选择优化原则：
-- 1. 选择高选择性的列
-- 2. 考虑查询模式
-- 3. 确保数据分布均匀

-- 好的分区键选择示例
-- 按日期分区 - 常见且有效
CREATE TABLE order_events (
    event_id INT,
    event_date DATETIME,
    event_type VARCHAR(20),
    user_id INT
) 
PARTITION BY RANGE (YEAR(event_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 不好的分区键选择示例
-- 按用户ID分区 - 可能导致数据分布不均
CREATE TABLE bad_partition (
    user_id INT,
    data VARCHAR(100)
) 
PARTITION BY HASH(user_id) 
PARTITIONS 10;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

# 5.2 分区数量优化

-- 分区数量优化
-- 1. 分区数不宜过多或过少
-- 2. 一般建议10-100个分区
-- 3. 考虑存储引擎限制

-- 查看分区数量
SELECT 
    COUNT(*) as partition_count
FROM information_schema.PARTITIONS 
WHERE TABLE_NAME = 'orders' 
AND TABLE_SCHEMA = 'your_database';

-- 优化分区数量
-- 优化前：分区过多
CREATE TABLE large_table (
    id INT,
    date_col DATE
) 
PARTITION BY RANGE (YEAR(date_col)) (
    -- 100个分区，可能过多
    PARTITION p2000 VALUES LESS THAN (2001),
    -- ... 99个分区
    PARTITION p2099 VALUES LESS THAN (2100)
);

-- 优化后：分区适中
CREATE TABLE optimized_table (
    id INT,
    date_col DATE
) 
PARTITION BY RANGE (YEAR(date_col)) (
    PARTITION p2000 VALUES LESS THAN (2005),
    PARTITION p2005 VALUES LESS THAN (2010),
    PARTITION p2010 VALUES LESS THAN (2015),
    PARTITION p2015 VALUES LESS THAN (2020),
    PARTITION p2020 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

# 5.3 分区压缩优化

-- 分区压缩优化
-- 1. 启用表压缩
-- 2. 合理设置压缩参数

-- 创建压缩分区表
CREATE TABLE compressed_table (
    id INT,
    data TEXT,
    created_date DATE
) 
PARTITION BY RANGE (YEAR(created_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p_future VALUES LESS THAN MAXVALUE
) 
ROW_FORMAT=COMPRESSED;

-- 查看压缩状态
SELECT 
    TABLE_NAME,
    TABLE_TYPE,
    ROW_FORMAT
FROM information_schema.TABLES 
WHERE TABLE_NAME = 'compressed_table';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

# 6. 分区表监控与诊断

# 6.1 分区使用监控

-- 分区使用情况监控
SELECT 
    PARTITION_NAME,
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH,
    (DATA_LENGTH + INDEX_LENGTH) as TOTAL_SIZE
FROM information_schema.PARTITIONS 
WHERE TABLE_NAME = 'orders' 
AND TABLE_SCHEMA = 'your_database'
ORDER BY TABLE_ROWS DESC;

-- 分区使用率分析
SELECT 
    PARTITION_NAME,
    ROUND((TABLE_ROWS * 100.0 / 
           (SELECT SUM(TABLE_ROWS) FROM information_schema.PARTITIONS 
            WHERE TABLE_NAME = 'orders' AND TABLE_SCHEMA = 'your_database')), 2) as usage_percent
FROM information_schema.PARTITIONS 
WHERE TABLE_NAME = 'orders' 
AND TABLE_SCHEMA = 'your_database'
ORDER BY usage_percent DESC;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

# 6.2 分区性能分析

-- 分区性能分析
-- 1. 分区扫描统计
-- 2. 查询性能对比

-- 分析分区查询
EXPLAIN SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

-- 查看查询执行计划中的分区信息
-- 在EXPLAIN输出中查看partitions字段

-- 性能对比示例
-- 无分区表性能
SELECT COUNT(*) FROM large_table WHERE created_date = '2023-01-01';

-- 分区表性能
SELECT COUNT(*) FROM partition_table WHERE created_date = '2023-01-01';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 6.3 分区维护脚本

-- 创建分区维护存储过程
DELIMITER //
CREATE PROCEDURE maintain_partitions()
BEGIN
    -- 1. 分析分区统计信息
    ANALYZE TABLE orders;
    
    -- 2. 检查分区状态
    SELECT 
        PARTITION_NAME,
        TABLE_ROWS,
        DATA_LENGTH
    FROM information_schema.PARTITIONS 
    WHERE TABLE_NAME = 'orders' 
    AND TABLE_SCHEMA = 'your_database';
    
    -- 3. 建议添加新分区
    -- 根据数据增长情况建议添加分区
    
    -- 4. 记录维护日志
    INSERT INTO partition_maintenance_log (timestamp, operation, notes) 
    VALUES (NOW(), 'ANALYZE', 'Partition analysis completed');
END //
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

# 7. 分区表最佳实践

# 7.1 设计阶段考虑

-- 分区表设计最佳实践：
-- 1. 明确分区目的
-- 2. 选择合适的分区键
-- 3. 考虑查询模式
-- 4. 预估数据增长

-- 设计示例：按时间分区的订单表
CREATE TABLE order_history (
    order_id BIGINT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    order_amount DECIMAL(10,2),
    status VARCHAR(20),
    INDEX idx_customer_date (customer_id, order_date),
    INDEX idx_date_status (order_date, status)
) 
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 为分区表创建合适的索引
CREATE INDEX idx_order_date ON order_history(order_date);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

# 7.2 查询优化建议

-- 查询优化建议：
-- 1. 在分区键上使用WHERE条件
-- 2. 避免跨分区查询
-- 3. 合理使用LIMIT

-- 优化查询示例
-- 优化前：无分区键条件
SELECT * FROM orders WHERE customer_id = 123;

-- 优化后：使用分区键
SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';

-- 分区裁剪示例
-- 优化前：可能扫描所有分区
SELECT * FROM orders WHERE customer_id = 123;

-- 优化后：明确指定分区
SELECT * FROM orders PARTITION (p2023) WHERE customer_id = 123;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

# 7.3 维护策略

-- 分区表维护策略：
-- 1. 定期分析分区
-- 2. 监控分区状态
-- 3. 及时添加新分区
-- 4. 清理过期分区

-- 创建分区维护事件
CREATE EVENT partition_maintenance
ON SCHEDULE EVERY 1 WEEK
DO
BEGIN
    -- 分析所有分区
    ANALYZE TABLE orders;
    
    -- 记录维护日志
    INSERT INTO maintenance_log (timestamp, operation, table_name) 
    VALUES (NOW(), 'PARTITION_MAINTENANCE', 'orders');
END;

-- 自动添加新分区
DELIMITER //
CREATE PROCEDURE auto_add_partition()
BEGIN
    DECLARE current_year INT;
    DECLARE next_year INT;
    DECLARE partition_name VARCHAR(50);
    
    SELECT YEAR(CURDATE()) INTO current_year;
    SET next_year = current_year + 1;
    SET partition_name = CONCAT('p', next_year);
    
    -- 添加新分区（需要根据实际表结构调整）
    -- ALTER TABLE orders ADD PARTITION (PARTITION p2025 VALUES LESS THAN (2026));
END //
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

# 8. 分区表限制与注意事项

# 8.1 分区表限制

-- 分区表的限制：
-- 1. 主键必须包含分区键
-- 2. 外键约束限制
-- 3. 事务限制
-- 4. 存储引擎限制

-- 主键约束示例
-- 正确：主键包含分区键
CREATE TABLE correct_table (
    id INT,
    date_col DATE,
    PRIMARY KEY (id, date_col)
) 
PARTITION BY RANGE (YEAR(date_col)) (
    PARTITION p2020 VALUES LESS THAN (2021)
);

-- 错误：主键不包含分区键
-- CREATE TABLE wrong_table (
--     id INT,
--     date_col DATE,
--     PRIMARY KEY (id)  -- 缺少date_col
-- ) 
-- PARTITION BY RANGE (YEAR(date_col)) (
--     PARTITION p2020 VALUES LESS THAN (2021)
-- );
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

# 8.2 常见问题处理

-- 常见分区问题及处理：
-- 1. 分区数据分布不均
-- 2. 分区键选择不当
-- 3. 分区维护复杂

-- 分析数据分布不均
SELECT 
    PARTITION_NAME,
    TABLE_ROWS,
    ROUND(TABLE_ROWS * 100.0 / 
          (SELECT SUM(TABLE_ROWS) FROM information_schema.PARTITIONS 
           WHERE TABLE_NAME = 'orders'), 2) as percentage
FROM information_schema.PARTITIONS 
WHERE TABLE_NAME = 'orders' 
AND TABLE_SCHEMA = 'your_database';

-- 调整分区策略
-- 如果发现某些分区数据量特别大，考虑重新分区
ALTER TABLE orders 
REORGANIZE PARTITION p2023 INTO (
    PARTITION p2023_q1 VALUES LESS THAN (20230401),
    PARTITION p2023_q2 VALUES LESS THAN (20230701),
    PARTITION p2023_q3 VALUES LESS THAN (20231001),
    PARTITION p2023_q4 VALUES LESS THAN (20240101)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

# 9. 总结

分区表是MySQL中一种强大的数据管理技术，它能够显著提升大型表的查询性能和管理效率。通过合理设计分区策略、优化分区键选择、定期维护分区状态，可以充分发挥分区表的优势。

关键要点包括：

  1. 选择合适的分区类型：根据数据特点和查询模式选择RANGE、LIST、HASH等分区类型
  2. 合理设计分区键：确保分区键具有良好的选择性和均匀的数据分布
  3. 优化分区数量：避免分区过多或过少，一般控制在10-100个分区
  4. 监控分区状态：定期分析分区使用情况，及时调整分区策略
  5. 维护分区表：定期执行分析、优化和清理操作

通过系统性的分区表使用和管理，可以有效提升大型数据库系统的性能和可维护性，为业务提供更好的支撑。

#MySQL#分区表#表分区#性能优化
上次更新: 3/4/2026

← SQL语句中的Join问题→

最近更新
01
表空间管理与回收
03-04
02
MySQL抖动刷脏页
03-04
03
count函数详解
03-04
更多文章>
Theme by Vdoing