1. 分区表基础与生命周期管理第一次接触OceanBase分区表时我被它既能处理海量数据又能保持查询效率的特性惊艳到了。这就像把一个大仓库划分成多个小隔间找东西时不用翻遍整个仓库直奔目标区域就行。在实际项目中我经常用分区表解决两个头疼问题历史数据归档和查询性能优化。先说说最常用的RANGE分区特别适合按时间增长的数据。假设我们要管理用户订单表用日期做分区键CREATE TABLE orders ( order_id BIGINT PRIMARY KEY, user_id INT, order_date DATE, amount DECIMAL(10,2) ) PARTITION BY RANGE(TO_DAYS(order_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)) );当新月份到来时用ADD PARTITION动态扩展就像给仓库加盖新房间ALTER TABLE orders ADD PARTITION ( PARTITION p202304 VALUES LESS THAN (TO_DAYS(2023-05-01)) );清理过期数据更是优雅DROP PARTITION操作比DELETE语句高效十倍不止。去年我做数据迁移时就深有体会删除3个月前的1亿条数据DELETE花了6小时而DROP PARTITION只用了3秒ALTER TABLE orders DROP PARTITION p202301;不过要注意两个坑一是RANGE分区只能往后追加不能在中间插入二是删除分区会同时清除数据重要数据记得先备份。有次我误删分区幸好有备份才没酿成事故。2. 分区裁剪性能优化的秘密武器分区裁剪是分区表最厉害的特性它能让查询只扫描必要的分区。就像查字典时如果知道字母范围就不用翻完整本书。OceanBase支持四种裁剪方式各有妙用。HASH分区适合均匀分布的数据。我们做过测试5亿用户表按user_id哈希分区后点查询速度提升8倍CREATE TABLE users ( user_id INT PRIMARY KEY, user_name VARCHAR(50) ) PARTITION BY HASH(user_id) PARTITIONS 16; -- 只会扫描特定分区 EXPLAIN SELECT * FROM users WHERE user_id 10086;LIST分区适合枚举值比如按地区分类。我在电商项目中用它存储多国订单查询特定国家的订单时效率极高CREATE TABLE intl_orders ( order_id BIGINT PRIMARY KEY, country_code CHAR(2) ) PARTITION BY LIST(country_code) ( PARTITION p_us VALUES IN (US), PARTITION p_eu VALUES IN (DE,FR,IT), PARTITION p_asia VALUES IN (CN,JP,KR) ); -- 只扫描亚洲分区 EXPLAIN SELECT * FROM intl_orders WHERE country_code IN (CN,JP);RANGE分区的裁剪最需要注意边界条件。有次性能调优我们发现这样的查询无法裁剪-- 无法裁剪因为c11不是单调函数 EXPLAIN SELECT * FROM t1 WHERE c1 150 AND c1 110;改成等值查询后立即见效-- 成功裁剪到p1分区 EXPLAIN SELECT * FROM t1 WHERE c1 150;3. 二级分区双维度管理大师当单维度分区不够用时二级分区就像给仓库加装立体货架。我们有个物联网项目设备数据按设备类型哈希分区再按时间范围子分区CREATE TABLE device_data ( device_id BIGINT, collect_time DATETIME, metrics JSON, PRIMARY KEY(device_id, collect_time) ) PARTITION BY HASH(device_id) PARTITIONS 8 SUBPARTITION BY RANGE(TO_DAYS(collect_time)) ( SUBPARTITION sp0 VALUES LESS THAN (TO_DAYS(2023-01-01)), SUBPARTITION sp1 VALUES LESS THAN (TO_DAYS(2023-04-01)), SUBPARTITION sp2 VALUES LESS THAN (TO_DAYS(2023-07-01)) );查询特定设备最近三个月的数据时OceanBase会先定位设备哈希分区再锁定时间子分区-- 只扫描device_id哈希对应的分区时间子分区 EXPLAIN SELECT * FROM device_data WHERE device_id 1001 AND collect_time BETWEEN 2023-04-01 AND 2023-06-30;实测下来二级分区使查询速度比单分区快12倍写入速度也提升5倍。但要注意分区数过多会导致元数据管理开销增大我们曾因设置1000分区导致DDL变慢。4. 实战中的避坑指南五年用下来我总结出这些血泪经验分区键选择必须谨慎。有次用UUID做主键导致严重写入放大后来改用自增ID时间组合分区键才解决。理想的分区键应该出现在常用查询条件中数据分布均匀避免频繁更新分区数量需要平衡。一般建议单个表不超过100个分区每个分区数据量控制在1GB-10GB考虑节点数确保分区均匀分布监控分区表特别重要。我们部署了定期检查脚本-- 检查分区数据分布 SELECT partition_name, table_rows FROM information_schema.partitions WHERE table_name orders; -- 检查分区裁剪效果 EXPLAIN PARTITIONS SELECT * FROM orders WHERE order_date 2023-06-01;还有个容易忽略的点跨分区查询可能比单分区慢。有次分页查询性能骤降发现是OFFSET导致全分区扫描。改用上一页最后ID方式才解决-- 低效写法 SELECT * FROM orders ORDER BY order_date LIMIT 10000, 100; -- 优化写法 SELECT * FROM orders WHERE order_date 2023-01-01 AND id 10000 ORDER BY order_date LIMIT 100;最近我们在金融项目中采用RANGEHASH复合分区交易表按日期分区后再按账户哈希既方便按日归档又均匀分布查询负载。一个有趣的发现是合理设置分区能让95%的查询只访问10%的分区
【OceanBase】分区表实战：从生命周期管理到性能调优
发布时间：2026/5/19 11:41:10
1. 分区表基础与生命周期管理第一次接触OceanBase分区表时我被它既能处理海量数据又能保持查询效率的特性惊艳到了。这就像把一个大仓库划分成多个小隔间找东西时不用翻遍整个仓库直奔目标区域就行。在实际项目中我经常用分区表解决两个头疼问题历史数据归档和查询性能优化。先说说最常用的RANGE分区特别适合按时间增长的数据。假设我们要管理用户订单表用日期做分区键CREATE TABLE orders ( order_id BIGINT PRIMARY KEY, user_id INT, order_date DATE, amount DECIMAL(10,2) ) PARTITION BY RANGE(TO_DAYS(order_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)) );当新月份到来时用ADD PARTITION动态扩展就像给仓库加盖新房间ALTER TABLE orders ADD PARTITION ( PARTITION p202304 VALUES LESS THAN (TO_DAYS(2023-05-01)) );清理过期数据更是优雅DROP PARTITION操作比DELETE语句高效十倍不止。去年我做数据迁移时就深有体会删除3个月前的1亿条数据DELETE花了6小时而DROP PARTITION只用了3秒ALTER TABLE orders DROP PARTITION p202301;不过要注意两个坑一是RANGE分区只能往后追加不能在中间插入二是删除分区会同时清除数据重要数据记得先备份。有次我误删分区幸好有备份才没酿成事故。2. 分区裁剪性能优化的秘密武器分区裁剪是分区表最厉害的特性它能让查询只扫描必要的分区。就像查字典时如果知道字母范围就不用翻完整本书。OceanBase支持四种裁剪方式各有妙用。HASH分区适合均匀分布的数据。我们做过测试5亿用户表按user_id哈希分区后点查询速度提升8倍CREATE TABLE users ( user_id INT PRIMARY KEY, user_name VARCHAR(50) ) PARTITION BY HASH(user_id) PARTITIONS 16; -- 只会扫描特定分区 EXPLAIN SELECT * FROM users WHERE user_id 10086;LIST分区适合枚举值比如按地区分类。我在电商项目中用它存储多国订单查询特定国家的订单时效率极高CREATE TABLE intl_orders ( order_id BIGINT PRIMARY KEY, country_code CHAR(2) ) PARTITION BY LIST(country_code) ( PARTITION p_us VALUES IN (US), PARTITION p_eu VALUES IN (DE,FR,IT), PARTITION p_asia VALUES IN (CN,JP,KR) ); -- 只扫描亚洲分区 EXPLAIN SELECT * FROM intl_orders WHERE country_code IN (CN,JP);RANGE分区的裁剪最需要注意边界条件。有次性能调优我们发现这样的查询无法裁剪-- 无法裁剪因为c11不是单调函数 EXPLAIN SELECT * FROM t1 WHERE c1 150 AND c1 110;改成等值查询后立即见效-- 成功裁剪到p1分区 EXPLAIN SELECT * FROM t1 WHERE c1 150;3. 二级分区双维度管理大师当单维度分区不够用时二级分区就像给仓库加装立体货架。我们有个物联网项目设备数据按设备类型哈希分区再按时间范围子分区CREATE TABLE device_data ( device_id BIGINT, collect_time DATETIME, metrics JSON, PRIMARY KEY(device_id, collect_time) ) PARTITION BY HASH(device_id) PARTITIONS 8 SUBPARTITION BY RANGE(TO_DAYS(collect_time)) ( SUBPARTITION sp0 VALUES LESS THAN (TO_DAYS(2023-01-01)), SUBPARTITION sp1 VALUES LESS THAN (TO_DAYS(2023-04-01)), SUBPARTITION sp2 VALUES LESS THAN (TO_DAYS(2023-07-01)) );查询特定设备最近三个月的数据时OceanBase会先定位设备哈希分区再锁定时间子分区-- 只扫描device_id哈希对应的分区时间子分区 EXPLAIN SELECT * FROM device_data WHERE device_id 1001 AND collect_time BETWEEN 2023-04-01 AND 2023-06-30;实测下来二级分区使查询速度比单分区快12倍写入速度也提升5倍。但要注意分区数过多会导致元数据管理开销增大我们曾因设置1000分区导致DDL变慢。4. 实战中的避坑指南五年用下来我总结出这些血泪经验分区键选择必须谨慎。有次用UUID做主键导致严重写入放大后来改用自增ID时间组合分区键才解决。理想的分区键应该出现在常用查询条件中数据分布均匀避免频繁更新分区数量需要平衡。一般建议单个表不超过100个分区每个分区数据量控制在1GB-10GB考虑节点数确保分区均匀分布监控分区表特别重要。我们部署了定期检查脚本-- 检查分区数据分布 SELECT partition_name, table_rows FROM information_schema.partitions WHERE table_name orders; -- 检查分区裁剪效果 EXPLAIN PARTITIONS SELECT * FROM orders WHERE order_date 2023-06-01;还有个容易忽略的点跨分区查询可能比单分区慢。有次分页查询性能骤降发现是OFFSET导致全分区扫描。改用上一页最后ID方式才解决-- 低效写法 SELECT * FROM orders ORDER BY order_date LIMIT 10000, 100; -- 优化写法 SELECT * FROM orders WHERE order_date 2023-01-01 AND id 10000 ORDER BY order_date LIMIT 100;最近我们在金融项目中采用RANGEHASH复合分区交易表按日期分区后再按账户哈希既方便按日归档又均匀分布查询负载。一个有趣的发现是合理设置分区能让95%的查询只访问10%的分区
