PolarDB 中的分区键选择：确保分区剪枝生效与数据均匀分布的方法

"你们组的新项目数据库已经卡了三回查询了！"当我接到CTO电话时，手里刚泡好的咖啡瞬间不香了。看着慢查询日志里那些超过10秒的请求，我知道是时候解决困扰我们已久的分区策略问题了...

一、什么是分区键的关键抉择？

在PolarDB的场景中，分区键就像快递分拣站的自动分拣机。假设每天要处理百万件快递，如果我们按照省份分区（河北省件放A区，广东省件放B区），但当遇到"查询北京市所有顺丰快递"的需求时，分拣机却能直接锁定北京分区快速处理——这就是分区剪枝的魔力。

但现实往往更复杂，比如当选择用户ID作为分区键时：

-- 示例使用PolarDB MySQL版 8.0.2
CREATE TABLE user_orders (
    order_id BIGINT AUTO_INCREMENT,
    user_id INT,
    order_date DATE,
    amount DECIMAL(10,2),
    PRIMARY KEY (order_id, user_id)  -- 必须包含分区键
) PARTITION BY HASH(user_id)
PARTITIONS 10;

此时执行用户订单查询时：

-- 好的情况
EXPLAIN SELECT * FROM user_orders 
WHERE user_id = 10086; -- 精确命中分区5

-- 坏的情况
EXPLAIN SELECT * FROM user_orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'; -- 扫描全部分区

这就像把所有水果按颜色分类后，突然要按品种检索，就会导致遍历所有筐子。所以选择分区键的本质，是在查询模式的命中率与数据分布的合理性之间找平衡点。

二、典型业务场景的决策过程

2.1 时序数据场景：日志分析系统

每天产生10GB的日志文件，常见查询是时间范围检索：

CREATE TABLE app_logs (
    log_time DATETIME(6),
    app_name VARCHAR(50),
    log_content TEXT,
    INDEX idx_time (log_time)
) PARTITION BY RANGE COLUMNS(log_time) (
    PARTITION p202301 VALUES LESS THAN ('2023-02-01'),
    PARTITION p202302 VALUES LESS THAN ('2023-03-01'),
    PARTITION p202303 VALUES LESS THAN ('2023-04-01')
);

-- 每月自动增加分区
ALTER TABLE app_logs ADD PARTITION (
    PARTITION p202310 VALUES LESS THAN ('2023-11-01')
);

当查询最近三天日志时，优化器会智能排除三个月前的分区。但隐患在于，如果按app_name分区：

SELECT * FROM app_logs 
WHERE log_time >= NOW() - INTERVAL 3 DAY 
  AND app_name = 'payment_service'; -- 全分区扫描

这个取舍告诉我们：在时间序列场景下，优先保证高频查询的剪枝能力，其他条件通过二级索引弥补。

2.2 电商订单系统

当面对订单状态查询的高并发时：

CREATE TABLE orders (
    order_id VARCHAR(32),
    user_id BIGINT,
    status ENUM('pending','paid','shipped'),
    create_time TIMESTAMP,
    INDEX idx_status (status)
) PARTITION BY LIST COLUMNS(status) (
    PARTITION p_pending VALUES IN ('pending'),
    PARTITION p_paid VALUES IN ('paid'),
    PARTITION p_shipped VALUES IN ('shipped')
);

-- 但状态分布严重不均：
SELECT status, COUNT(*) 
FROM orders 
GROUP BY status;
/*
+---------+----------+
| status  | COUNT(*) |
+---------+----------+
| pending |   12,345 |
| paid    |  987,654 |
| shipped |    3,456 |
+---------+----------+

此时订单状态分区就会导致分区数据严重倾斜，paid分区的压力是其他分区的百倍。正确的做法应该是组合分区：

CREATE TABLE orders (
    ...
) PARTITION BY HASH(user_id)
SUBPARTITION BY LIST COLUMNS(status) (
    PARTITION p0 VALUES IN ('pending'),
    PARTITION p1 VALUES IN ('paid'),
    PARTITION p2 VALUES IN ('shipped')
) PARTITIONS 4;

2.3 社交平台用户画像

处理包含数十个标签字段的用户数据表时：

CREATE TABLE user_tags (
    user_id BIGINT,
    gender ENUM('M','F'),
    age_group TINYINT,
    city_code INT,
    ...
) PARTITION BY KEY()
PARTITIONS 16;  -- 全字段hash分区

看似中立的哈希分区却存在隐患。当执行基于城市的查询时：

SELECT * FROM user_tags
WHERE city_code = 1001;  -- 每个分区都有该城市的数据

解决这个矛盾需要使用双重策略：

CREATE TABLE user_tags (
    ...
) PARTITION BY LINEAR HASH(city_code)
PARTITIONS 64;

通过放大分区数，让每个城市对应足够多的分区，同时用HASH算法保证均匀分布。

三、技术实施的黄金法则

3.1 选择分区键的3个维度

数据离散度：测试字段基数是否适合分区数量

SELECT COUNT(DISTINCT user_id) AS uid_count,
       COUNT(DISTINCT order_date) AS date_count
FROM orders;

查询模式匹配度：抓取Slow Query Log分析WHERE条件
业务扩展性：未来三年数据增长预估

3.2 检验数据分布的4种手段

查看分区数据量：

SELECT PARTITION_NAME, TABLE_ROWS 
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_NAME = 'user_orders';

检查查询计划：

EXPLAIN PARTITIONS 
SELECT * FROM sales 
WHERE sale_date > '2023-09-01';

统计热点扫描：

SELECT partition_id, 
       SUM(rows_read) AS total_read
FROM polar_stat_activity 
GROUP BY partition_id
ORDER BY total_read DESC;

使用散列分布检测工具：

SELECT ABS(MAX(cnt) - MIN(cnt)) * 100.0 / AVG(cnt) AS imbalance_percent
FROM (
    SELECT PARTITION_NAME, TABLE_ROWS AS cnt
    FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_NAME = 'user_logs'
) t;

3.3 组合分区的实战案例

针对订单系统的复杂查询需求：

CREATE TABLE multi_part_orders (
    order_id VARCHAR(32),
    user_id BIGINT,
    order_date DATE,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date))  -- 第一级按年分区
SUBPARTITION BY HASH(user_id % 100)    -- 第二级按用户尾号
SUBPARTITIONS 10 (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023)
);

该设计的优势在于：

按年份快速归档历史数据
用户维度查询可锁定特定子分区
数值尾号哈希避免用户聚集

四、避坑指南：血的教训总结

4.1 动态分区的陷阱

某个广告系统使用自增ID作为分区键：

CREATE TABLE click_logs (
    id BIGINT AUTO_INCREMENT,
    ad_id INT,
    click_time DATETIME,
    INDEX (ad_id)
) PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (1000000),
    PARTITION p1 VALUES LESS THAN (2000000),
    ... 
);

三个月后的查询性能急剧下降。原因是广告主常查询某个ad_id的点击数据：

SELECT * FROM click_logs 
WHERE ad_id = 123 
  AND click_time BETWEEN ...;

解决方案是重新设计为组合分区：

ALTER TABLE click_logs 
PARTITION BY HASH(ad_id)
PARTITIONS 32;

4.2 数值溢出的幽灵事件

某金融系统按交易额区间分区：

CREATE TABLE fund_trans (
    trans_id BIGINT,
    amount DECIMAL(16,2),
    ...
) PARTITION BY RANGE (amount) (
    PARTITION p0 VALUES LESS THAN (1000),
    PARTITION p1 VALUES LESS THAN (10000),
    PARTITION p_max VALUES LESS THAN MAXVALUE
);

当出现单笔超过1亿的转账时，所有超大金额交易都集中在p_max分区，导致该分区爆炸式增长。改进方案是定期动态调整分区阈值。

五、工具链的巧妙使用

5.1 自动化监控脚本示例

#!/bin/bash
# 检查分区均衡度
mysql -uadmin -p$PASSWORD -N <<< "SELECT 
  (MAX(TABLE_ROWS)-MIN(TABLE_ROWS))*100/AVG(TABLE_ROWS) AS imbalance 
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_NAME='$1'" | awk '{if($1 > 30) exit 1}'

5.2 自动重建倾斜分区

-- 使用存储过程动态调整
DELIMITER $$
CREATE PROCEDURE rebalance_partitions()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE p_name VARCHAR(50);
  DECLARE cur CURSOR FOR 
    SELECT PARTITION_NAME 
    FROM INFORMATION_SCHEMA.PARTITIONS 
    WHERE TABLE_NAME = 'hot_table' 
    ORDER BY TABLE_ROWS DESC
    LIMIT 3;
  
  OPEN cur;
  part_loop: LOOP
    FETCH cur INTO p_name;
    IF done THEN LEAVE part_loop; END IF;
    
    -- 拆分最大分区
    SET @split_value = (SELECT MAX(user_id) FROM hot_table PARTITION (p_name));
    SET @sql = CONCAT('ALTER TABLE hot_table REORGANIZE PARTITION ', p_name,
                      ' INTO (PARTITION ', p_name,'_1 VALUES LESS THAN (', @split_value/2, '),',
                      ' PARTITION ', p_name,'_2 VALUES LESS THAN MAXVALUE)');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
  END LOOP;
END$$
DELIMITER ;

六、总结与最佳实践

经过多个项目的实战打磨，我们总结出黄金法则：

双80原则：80%的查询能够命中分区键，分区间数据量差异不超过20%
动态调整机制：通过定时任务监控并自动优化分区策略
组合拳策略：采用HASH+RANGE等组合分区应对复杂场景
预防性设计：预留足够的分区容量和重组方案

在未来的数据库设计中，随着机器学习的发展，我们有理由相信会出现更智能的自动分区调优系统。但在此之前，理解业务特征、掌握数据规律，仍然是我们驾驭PolarDB这匹性能怪兽的关键所在。

敲码拾光专注于编程技术，涵盖编程语言、代码实战案例、软件开发技巧、IT前沿技术、编程开发工具，是您提升技术能力的优质网络平台。