本文档风哥主要介绍MySQL分区表优化的实战技巧，包括分区设计原则、分区类型选择、分区维护等内容，风哥教程参考MySQL官方文档，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。更多视频教程www.fgedu.net.cn
Part01-基础概念与理论知识
1.1 分区表概述
分区表是将大表拆分为多个物理小表的技术：
1. 分区表定义
分区表特点：
– 将大表拆分为多个物理小表
– 逻辑上仍是一个表
– 对应用透明
– 提高查询和维护效率
分区优势：
– 提高查询性能（分区裁剪）
– 简化数据维护
– 提高可用性
– 优化存储管理
分区限制：
– 主键/唯一键必须包含分区键
– 外键不支持
– 全文索引有限制
– 分区数量有限制
2. 查看分区支持
检查分区支持：
mysql> SHOW PLUGINS WHERE Name = ‘partition’;
输出示例：
+———–+———-+—————-+———+———+
| Name | Status | Type | Library | License |
+———–+———-+—————-+———+———+
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+———–+———-+—————-+———+———+
查看分区变量：
mysql> SHOW VARIABLES LIKE ‘%partition%’;
输出示例：
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| have_partitioning | YES |
+——————-+——-+
3. 创建分区表
创建简单分区表：
mysql> CREATE TABLE orders_partitioned (
id INT NOT NULL AUTO_INCREMENT,
customer_id INT,
order_date DATE,
amount DECIMAL(10,2),
status VARCHAR(20),
PRIMARY KEY (id, order_date)
)
PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
输出示例：
Query OK, 0 rows affected (0.05 sec)
4. 查看分区信息
查看分区详情：
mysql> SELECT
PARTITION_NAME,
PARTITION_METHOD,
TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = ‘orders_partitioned’;
输出示例：
+—————-+——————+————+
| PARTITION_NAME | PARTITION_METHOD | TABLE_ROWS |
+—————-+——————+————+
| p2023 | RANGE | 0 |
| p2024 | RANGE | 0 |
| p2025 | RANGE | 0 |
| pmax | RANGE | 0 |
+—————-+——————+————+
5. 分区裁剪验证
查看分区裁剪：
mysql> EXPLAIN PARTITIONS
SELECT * FROM orders_partitioned
WHERE order_date = ‘2024-01-15’;
输出示例：
+—-+————-+———————+————+——+—————+——+———+——+——+———-+————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+———————+————+——+—————+——+———+——+——+———-+————-+
| 1 | SIMPLE | orders_partitioned | p2024 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+—-+————-+———————+————+——+—————+——+———+——+——+———-+————-+
1.2 分区类型
MySQL支持多种分区类型：
1. RANGE分区
特点：按范围分区，适合时间序列数据
创建RANGE分区：
mysql> CREATE TABLE sales_range (
id INT NOT NULL AUTO_INCREMENT,
sale_date DATE NOT NULL,
amount DECIMAL(10,2),
region VARCHAR(50),
PRIMARY KEY (id, sale_date)
)
PARTITION BY RANGE (TO_DAYS(sale_date)) (
PARTITION p202301 VALUES LESS THAN (TO_DAYS(‘2023-02-01’)),
PARTITION p202302 VALUES LESS THAN (TO_DAYS(‘2023-03-01’)),
PARTITION p202303 VALUES LESS THAN (TO_DAYS(‘2023-04-01’)),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
输出示例：
Query OK, 0 rows affected (0.03 sec)
2. LIST分区
特点：按离散值列表分区，适合分类数据
创建LIST分区：
mysql> CREATE TABLE customers_list (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(100),
region VARCHAR(50),
PRIMARY KEY (id, region)
)
PARTITION BY LIST COLUMNS (region) (
PARTITION p_north VALUES IN (‘Beijing’, ‘Tianjin’, ‘Hebei’),
PARTITION p_south VALUES IN (‘Guangdong’, ‘Fujian’, ‘Hainan’),
PARTITION p_east VALUES IN (‘Shanghai’, ‘Jiangsu’, ‘Zhejiang’),
PARTITION p_west VALUES IN (‘Sichuan’, ‘Chongqing’, ‘Shaanxi’)
);
输出示例：
Query OK, 0 rows affected (0.03 sec)
3. HASH分区
特点：按哈希值分区，数据均匀分布
创建HASH分区：
mysql> CREATE TABLE logs_hash (
id INT NOT NULL AUTO_INCREMENT,
user_id INT,
log_time TIMESTAMP,
message TEXT,
PRIMARY KEY (id)
)
PARTITION BY HASH (id)
PARTITIONS 4;
输出示例：
Query OK, 0 rows affected (0.03 sec)
4. KEY分区
特点：类似HASH分区，使用MySQL内部哈希函数
创建KEY分区：
mysql> CREATE TABLE users_key (
id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(50),
email VARCHAR(100),
PRIMARY KEY (id)
)
PARTITION BY KEY (id)
PARTITIONS 4;
输出示例：
Query OK, 0 rows affected (0.03 sec)
5. 复合分区
RANGE + HASH复合分区：
mysql> CREATE TABLE sales_composite (
id INT NOT NULL AUTO_INCREMENT,
sale_date DATE NOT NULL,
customer_id INT,
amount DECIMAL(10,2),
PRIMARY KEY (id, sale_date)
)
PARTITION BY RANGE (TO_DAYS(sale_date))
SUBPARTITION BY HASH (customer_id)
SUBPARTITIONS 2 (
PARTITION p2023 VALUES LESS THAN (TO_DAYS(‘2024-01-01’)) (
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION p2024 VALUES LESS THAN (TO_DAYS(‘2025-01-01’)) (
SUBPARTITION s2,
SUBPARTITION s3
)
);
输出示例：
Query OK, 0 rows affected (0.05 sec)
1.3 分区语法
分区表支持丰富的管理语法：
1. 添加分区
RANGE分区添加：
mysql> ALTER TABLE orders_partitioned
ADD PARTITION (
PARTITION p2026 VALUES LESS THAN (2027)
);
输出示例：
Query OK, 0 rows affected (0.02 sec)
2. 删除分区
删除分区（会删除分区中的所有数据）：
mysql> ALTER TABLE orders_partitioned DROP PARTITION p2023;
输出示例：
Query OK, 0 rows affected (0.01 sec)
3. 合并分区
合并相邻分区：
mysql> ALTER TABLE orders_partitioned
REORGANIZE PARTITION p2024, p2025 INTO (
PARTITION p2024_2025 VALUES LESS THAN (2026)
);
输出示例：
Query OK, 0 rows affected (0.03 sec)
4. 拆分分区
拆分分区：
mysql> ALTER TABLE orders_partitioned
REORGANIZE PARTITION p2024_2025 INTO (
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026)
);
输出示例：
Query OK, 0 rows affected (0.03 sec)
5. 分区维护
分析分区：
mysql> ALTER TABLE orders_partitioned ANALYZE PARTITION p2024;
输出示例：
+—————————————–+———+———-+———-+
| Table | Op | Msg_type | Msg_text |
+—————————————–+———+———-+———-+
| production_db.orders_partitioned | analyze | status | OK |
+—————————————–+———+———-+———-+
优化分区：
mysql> ALTER TABLE orders_partitioned OPTIMIZE PARTITION p2024;
输出示例：
Query OK, 0 rows affected (0.02 sec)
6. 分区截断
截断分区：
mysql> ALTER TABLE orders_partitioned TRUNCATE PARTITION p2024;
输出示例：
Query OK, 0 rows affected (0.01 sec)
7. 交换分区
交换分区数据：
mysql> CREATE TABLE orders_2024 LIKE orders_partitioned;
mysql> ALTER TABLE orders_2024 REMOVE PARTITIONING;
输出示例：
Query OK, 0 rows affected (0.02 sec)
mysql> ALTER TABLE orders_partitioned
EXCHANGE PARTITION p2024 WITH TABLE orders_2024;
输出示例：
Query OK, 0 rows affected (0.02 sec)
Part02-生产环境规划与建议
2.1 分区设计原则
合理的分区设计是性能优化的基础：
1. 分区键选择原则
选择合适的分区键：
– 经常用于查询条件的列
– 数据分布均匀的列
– 范围查询的列
问题设计（主键不包含分区键）：
mysql> CREATE TABLE bad_partition (
id INT PRIMARY KEY,
created_at TIMESTAMP
)
PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
优化设计（主键包含分区键）：
mysql> CREATE TABLE good_partition (
id INT,
created_at TIMESTAMP,
PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
输出示例：
Query OK, 0 rows affected (0.03 sec)
2. 分区粒度原则
分区粒度选择：
+——————-+————————+
| 数据量 | 推荐分区粒度 |
+——————-+————————+
| 百万级 | 按年分区 |
| 千万级 | 按月分区 |
| 亿级 | 按日或周分区 |
+——————-+————————+
3. 分区数量原则
查看当前分区数：
mysql> SELECT COUNT(DISTINCT PARTITION_NAME) AS partition_count
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = ‘production_db’;
输出示例：
+—————–+
| partition_count |
+—————–+
| 12 |
+—————–+
4. 数据分布监控
查看分区数据分布：
mysql> SELECT
PARTITION_NAME,
TABLE_ROWS,
TABLE_ROWS * 100.0 / SUM(TABLE_ROWS) OVER() AS percentage
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = ‘orders_partitioned’;
输出示例：
+—————-+————+————+
| PARTITION_NAME | TABLE_ROWS | percentage |
+—————-+————+————+
| p2024 | 50000 | 52.08 |
| p2025 | 45000 | 46.88 |
| pmax | 1000 | 1.04 |
+—————-+————+————+
2.2 分区优化策略
制定合理的分区优化策略：
1. 分区裁剪优化
验证分区裁剪（有效）：
mysql> EXPLAIN PARTITIONS
SELECT * FROM orders_partitioned
WHERE created_at BETWEEN ‘2024-01-01’ AND ‘2024-12-31’;
输出示例：
+—-+————-+———————+————+——+—————+——+———+——+——+———-+————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+———————+————+——+—————+——+———+——+——+———-+————-+
| 1 | SIMPLE | orders_partitioned | p2024 | ALL | NULL | NULL | NULL | NULL | 50000| 11.11 | Using where |
+—-+————-+———————+————+——+—————+——+———+——+——+———-+————-+
优化建议：
– 查询条件包含分区键
– 使用范围查询
– 避免函数转换分区键
2. 自动添加分区
创建自动添加分区存储过程：
mysql> DELIMITER //
mysql> CREATE PROCEDURE add_monthly_partition(IN p_table VARCHAR(100))
BEGIN
DECLARE v_next_month DATE;
DECLARE v_partition_name VARCHAR(20);
SET v_next_month = DATE_ADD(LAST_DAY(CURDATE()), INTERVAL 1 DAY);
SET v_partition_name = CONCAT(‘p’, DATE_FORMAT(v_next_month, ‘%Y%m’));
SET @sql = CONCAT(‘ALTER TABLE ‘, p_table,
‘ ADD PARTITION (PARTITION ‘, v_partition_name,
‘ VALUES LESS THAN (TO_DAYS(”’ ,
DATE_FORMAT(DATE_ADD(v_next_month, INTERVAL 1 MONTH), ‘%Y-%m-%d’),
”’)))’);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
mysql> DELIMITER ;
输出示例：
Query OK, 0 rows affected (0.01 sec)
3. 分区索引优化
创建本地索引：
mysql> ALTER TABLE orders_partitioned
ADD INDEX idx_customer (customer_id);
输出示例：
Query OK, 0 rows affected (0.10 sec)
4. 分区查询优化
使用分区键查询：
mysql> SELECT * FROM orders_partitioned
WHERE created_at >= ‘2024-01-01’
AND created_at < '2024-02-01';
输出示例：
+----+-------------+------------+--------+-----------+
| id | customer_id | created_at | amount | status |
+----+-------------+------------+--------+-----------+
| 1 | 1 | 2024-01-15 | 100.00 | completed |
+----+-------------+------------+--------+-----------+
2.3 分区监控
建立完善的分区监控体系：
1. 监控分区大小
查看分区大小：
mysql> SELECT
PARTITION_NAME,
TABLE_ROWS,
DATA_LENGTH / 1024 / 1024 AS data_mb,
INDEX_LENGTH / 1024 / 1024 AS index_mb
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = ‘orders_partitioned’;
输出示例：
+—————-+————+———-+———-+
| PARTITION_NAME | TABLE_ROWS | data_mb | index_mb |
+—————-+————+———-+———-+
| p2024 | 50000 | 7.81 | 1.00 |
| p2025 | 45000 | 7.03 | 0.94 |
+—————-+————+———-+———-+
2. 监控分区性能
创建性能监控视图：
mysql> CREATE VIEW v_partition_stats AS
SELECT
TABLE_NAME,
PARTITION_NAME,
TABLE_ROWS,
DATA_LENGTH,
INDEX_LENGTH
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = ‘production_db’;
3. 监控分区裁剪
检查分区裁剪效果：
mysql> EXPLAIN PARTITIONS
SELECT * FROM orders_partitioned
WHERE created_at = ‘2024-01-15’;
输出示例：
+—-+————-+———————+————+——+—————+——+———+——+——+———-+————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+———————+————+——+—————+——+———+——+——+———-+————-+
| 1 | SIMPLE | orders_partitioned | p2024 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+—-+————-+———————+————+——+—————+——+———+——+——+———-+————-+
Part03-生产环境项目实施方案
3.1 分区实施规范
制定分区实施规范确保一致性：
1. 分区表创建规范
标准分区表模板：
mysql> CREATE TABLE orders_standard (
id BIGINT NOT NULL AUTO_INCREMENT,
order_no VARCHAR(50) NOT NULL,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
amount DECIMAL(12,2),
status TINYINT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id, order_date),
UNIQUE KEY uk_order_no (order_no, order_date),
KEY idx_customer (customer_id, order_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (TO_DAYS(order_date)) (
PARTITION p202401 VALUES LESS THAN (TO_DAYS(‘2024-02-01’)),
PARTITION p202402 VALUES LESS THAN (TO_DAYS(‘2024-03-01’)),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
输出示例：
Query OK, 0 rows affected (0.05 sec)
2. 分区命名规范
命名约定：
+——————-+————————+
| 分区类型 | 命名规则 |
+——————-+————————+
| RANGE按年 | pYYYY |
| RANGE按月 | pYYYYMM |
| RANGE按日 | pYYYYMMDD |
| LIST | p_region_name |
| HASH | p0, p1, p2, … |
| MAXVALUE | pmax |
+——————-+————————+
3. 分区规划文档
创建分区规划表：
mysql> CREATE TABLE partition_planning (
id INT AUTO_INCREMENT PRIMARY KEY,
table_name VARCHAR(100),
partition_type VARCHAR(20),
partition_column VARCHAR(50),
partition_granularity VARCHAR(20),
retention_period INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
输出示例：
Query OK, 0 rows affected (0.02 sec)
3.2 分区维护
分区维护是长期运营的重要工作：
1. 定期添加分区
创建自动添加分区过程：
mysql> DELIMITER //
mysql> CREATE PROCEDURE auto_add_partitions()
BEGIN
DECLARE v_table VARCHAR(100);
DECLARE v_partition_name VARCHAR(20);
DECLARE v_next_month DATE;
SET v_table = ‘orders_partitioned’;
SET v_next_month = DATE_ADD(LAST_DAY(CURDATE()), INTERVAL 1 DAY);
SET v_partition_name = CONCAT(‘p’, DATE_FORMAT(v_next_month, ‘%Y%m’));
SET @sql = CONCAT(‘ALTER TABLE ‘, v_table,
‘ ADD PARTITION (PARTITION ‘, v_partition_name,
‘ VALUES LESS THAN (TO_DAYS(”’ ,
DATE_FORMAT(DATE_ADD(v_next_month, INTERVAL 1 MONTH), ‘%Y-%m-%d’),
”’)))’);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
mysql> DELIMITER ;
输出示例：
Query OK, 0 rows affected (0.01 sec)
2. 定期删除旧分区
创建删除旧分区过程：
mysql> DELIMITER //
mysql> CREATE PROCEDURE auto_drop_old_partitions(
IN p_months_to_keep INT
)
BEGIN
DECLARE v_partition VARCHAR(64);
DECLARE v_cutoff_days INT;
DECLARE done INT DEFAULT FALSE;
SET v_cutoff_days = TO_DAYS(DATE_SUB(CURDATE(), INTERVAL p_months_to_keep MONTH));
DECLARE cur CURSOR FOR
SELECT PARTITION_NAME
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = ‘orders_partitioned’
AND TABLE_SCHEMA = ‘production_db’
AND PARTITION_NAME != ‘pmax’
AND PARTITION_DESCRIPTION < v_cutoff_days;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO v_partition;
IF done THEN LEAVE read_loop; END IF;
SET @sql = CONCAT('ALTER TABLE orders_partitioned DROP PARTITION ', v_partition);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
CLOSE cur;
END //
mysql> DELIMITER ;
输出示例：
Query OK, 0 rows affected (0.01 sec)
3. 分区重建
重建分区：
mysql> ALTER TABLE orders_partitioned REBUILD PARTITION p2024;
输出示例：
Query OK, 0 rows affected (0.02 sec)
4. 分区优化
分析分区：
mysql> ALTER TABLE orders_partitioned ANALYZE PARTITION p2024;
输出示例：
+—————————————–+———+———-+———-+
| Table | Op | Msg_type | Msg_text |
+—————————————–+———+———-+———-+
| production_db.orders_partitioned | analyze | status | OK |
+—————————————–+———+———-+———-+
优化分区：
mysql> ALTER TABLE orders_partitioned OPTIMIZE PARTITION p2024;
输出示例：
Query OK, 0 rows affected (0.02 sec)
3.3 分区迁移
分区迁移是将普通表转换为分区表的过程：
1. 普通表转分区表
步骤1：检查表结构
mysql> SHOW CREATE TABLE orders\G
输出示例：
*************************** 1. row ***************************
Table: orders
Create Table: CREATE TABLE `orders` (
`id` int NOT NULL AUTO_INCREMENT,
`order_date` date DEFAULT NULL,
`amount` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
步骤2：修改主键包含分区键
mysql> ALTER TABLE orders MODIFY COLUMN order_date DATE NOT NULL;
mysql> ALTER TABLE orders DROP PRIMARY KEY, ADD PRIMARY KEY (id, order_date);
输出示例：
Query OK, 0 rows affected (0.05 sec)
步骤3：添加分区
mysql> ALTER TABLE orders
PARTITION BY RANGE (TO_DAYS(order_date)) (
PARTITION p202301 VALUES LESS THAN (TO_DAYS(‘2023-02-01’)),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
输出示例：
Query OK, 10000 rows affected (5.00 sec)
步骤4：验证分区
mysql> SELECT PARTITION_NAME, TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = ‘orders’;
输出示例：
+—————-+————+
| PARTITION_NAME | TABLE_ROWS |
+—————-+————+
| p202301 | 3000 |
| pmax | 7000 |
+—————-+————+
2. 分区表转普通表
移除分区：
mysql> ALTER TABLE orders REMOVE PARTITIONING;
输出示例：
Query OK, 10000 rows affected (3.00 sec)
3. 在线迁移
使用pt-online-schema-change：
pt-online-schema-change \
–alter “PARTITION BY RANGE (TO_DAYS(order_date)) (
PARTITION p202401 VALUES LESS THAN (TO_DAYS(‘2024-02-01’)),
PARTITION pmax VALUES LESS THAN MAXVALUE
)” \
D=production_db,t=orders \
–execute
输出示例：
No slaves found.
Altering `production_db`.`orders`…
Successfully altered `production_db`.`orders`.
Part04-生产案例与实战讲解
4.1 分区性能案例
以下是分区表性能优化的实战案例：
# 案例：大表查询性能优化
# 问题描述：
# 订单表数据量达到1亿，查询性能下降
# 步骤1：分析当前表
mysql> SELECT COUNT(*) FROM orders;
# 输出示例：
# +———-+
# | COUNT(*) |
# +———-+
# | 100000000|
# +———-+
# 步骤2：设计分区方案
# – 按月分区
# – 保留24个月数据
# 步骤3：创建分区表
mysql> CREATE TABLE orders_partitioned (
id BIGINT NOT NULL AUTO_INCREMENT,
customer_id INT,
order_date DATE NOT NULL,
amount DECIMAL(12,2),
status VARCHAR(20),
PRIMARY KEY (id, order_date),
KEY idx_customer (customer_id, order_date)
)
PARTITION BY RANGE (TO_DAYS(order_date)) (
PARTITION p202401 VALUES LESS THAN (TO_DAYS(‘2024-02-01’)),
PARTITION p202402 VALUES LESS THAN (TO_DAYS(‘2024-03-01’)),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
# 输出示例：
# Query OK, 0 rows affected (0.10 sec)
# 步骤4：迁移数据
mysql> INSERT INTO orders_partitioned SELECT * FROM orders;
# 输出示例：
# Query OK, 100000000 rows affected (300.00 sec)
# 步骤5：性能对比
# 查询一个月的数据
# 原表查询：
# 执行时间：30秒
# 分区表查询：
mysql> SELECT * FROM orders_partitioned
WHERE order_date BETWEEN ‘2024-01-01’ AND ‘2024-01-31’;
# 执行时间：2秒
# 性能提升：15倍
4.2 分区裁剪案例
以下是分区裁剪优化的实战案例：
# 案例：分区裁剪失效问题
# 问题描述：
# 分区表查询没有使用分区裁剪
# 步骤1：检查查询
mysql> EXPLAIN PARTITIONS
SELECT * FROM orders_partitioned
WHERE YEAR(order_date) = 2024;
# 输出示例：
# +—-+————-+———————+———————+——+—————+——+———+——+——+———-+————-+
# | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
# +—-+————-+———————+———————+——+—————+——+———+——+——+———-+————-+
# | 1 | SIMPLE | orders_partitioned | p202201,p202202,… | ALL | NULL | NULL | NULL | NULL | 100M | 100.00 | Using where |
# +—-+————-+———————+———————+——+—————+——+———+——+——+———-+————-+
# 问题分析：
# – 使用了YEAR()函数
# – 函数导致分区裁剪失效
# – 扫描了所有分区
# 步骤2：优化查询
mysql> EXPLAIN PARTITIONS
SELECT * FROM orders_partitioned
WHERE order_date BETWEEN ‘2024-01-01’ AND ‘2024-12-31’;
# 输出示例：
# +—-+————-+———————+———————+——+—————+——+———+——+——+———-+————-+
# | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
# +—-+————-+———————+———————+——+—————+——+———+——+——+———-+————-+
# | 1 | SIMPLE | orders_partitioned | p202401,p202402,… | ALL | NULL | NULL | NULL | NULL | 8M | 100.00 | Using where |
# +—-+————-+———————+———————+——+—————+——+———+——+——+———-+————-+
# 优化效果：
# – 只扫描2024年的分区
# – 减少了扫描数据量
4.3 分区归档案例
以下是分区归档的实战案例：
# 案例：历史数据归档
# 问题描述：
# 需要保留最近12个月数据，旧数据归档
# 步骤1：创建归档表
mysql> CREATE TABLE orders_archive LIKE orders_partitioned;
mysql> ALTER TABLE orders_archive REMOVE PARTITIONING;
# 输出示例：
# Query OK, 0 rows affected (0.05 sec)
# 步骤2：交换分区到归档表
mysql> ALTER TABLE orders_partitioned
EXCHANGE PARTITION p202301 WITH TABLE orders_archive;
# 输出示例：
# Query OK, 0 rows affected (2.00 sec)
# 步骤3：删除分区
mysql> ALTER TABLE orders_partitioned DROP PARTITION p202301;
# 输出示例：
# Query OK, 0 rows affected (0.01 sec)
# 步骤4：验证归档结果
mysql> SELECT COUNT(*) FROM orders_archive;
# 输出示例：
# +———-+
# | COUNT(*) |
# +———-+
# | 3000000 |
# +———-+
# 步骤5：创建定时归档任务
mysql> CREATE EVENT monthly_archive_event
ON SCHEDULE EVERY 1 MONTH
STARTS ‘2026-05-01 02:00:00’
DO CALL archive_old_partitions();
# 输出示例：
# Query OK, 0 rows affected (0.01 sec)
Part05-风哥经验总结与分享
5.1 分区最佳实践
以下是MySQL分区表的最佳实践：
1. 设计原则
– 选择合适的分区键
– 确保分区裁剪有效
– 控制分区数量
– 保持数据均匀分布
2. 性能原则
– 利用分区裁剪
– 避免全分区扫描
– 合理设置索引
– 定期维护分区
3. 维护原则
– 自动添加新分区
– 定期归档旧分区
– 监控分区大小
– 设置告警机制
5.2 分区反模式
以下是MySQL分区表中需要避免的反模式：
1. 避免选择错误的分区键
反模式：
– 选择不常用的列作为分区键
– 选择数据分布不均的列
正确做法：
– 选择常用查询条件列
– 确保数据均匀分布
2. 避免分区裁剪失效
反模式：
– 在分区键上使用函数
– 使用OR条件连接分区键
正确做法：
– 使用范围查询
– 使用IN条件
3. 避免过多分区
反模式：
– 创建过多小分区
– 分区数量超过限制
正确做法：
– 合理设置分区粒度
– 控制分区数量
4. 避免忽略主键约束
反模式：
– 主键不包含分区键
正确做法：
– 主键必须包含分区键
– 调整主键定义
5.3 分区检查清单
以下是MySQL分区表的检查清单：
1. 设计阶段检查
[ ] 是否选择了合适的分区键
[ ] 是否确定分区粒度
[ ] 是否符合主键约束
[ ] 是否规划分区数量
2. 开发阶段检查
[ ] 是否创建了必要的索引
[ ] 是否测试了分区裁剪
[ ] 是否测试了查询性能
[ ] 是否验证了数据分布
3. 测试阶段检查
[ ] 是否测试了分区维护操作
[ ] 是否测试了数据迁移
[ ] 是否测试了归档功能
[ ] 是否验证了数据完整性
4. 运维阶段检查
[ ] 是否设置了自动添加分区
[ ] 是否设置了旧分区归档
[ ] 是否监控了分区大小
[ ] 是否设置了告警阈值
风哥提示：分区表是MySQL处理大表的重要技术，通过合理设计分区可以显著提高查询性能和维护效率。分区裁剪是分区表最大的性能优势，务必确保查询条件能够触发分区裁剪。分区键的选择至关重要，应该选择经常用于范围查询的列。分区维护是长期工作，建议设置自动化的分区管理任务。对于生产环境的分区表优化，务必在测试环境验证后再执行。更多视频教程请访问www.fgedu.net.cn
注意：本文档内容基于MySQL 8.4官方文档编写，适合DBA人员在学习和测试中使用。在生产环境中应用时，请务必进行充分的测试和验证。分区表操作可能影响数据完整性，需要在测试环境验证后再应用到生产环境。文档中的命令和配置可能因MySQL版本不同而有所差异，请根据实际情况进行调整。
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
