关于二级分区数据查询

OceanBase
#1

我的一级分区和二级分区定义如下

image
image1708×320 47 KB

关于一级分区数据查询，分区裁减，看来是正确的

SELECT COUNT(1) from qt_order PARTITION(p202405);

image

关于二级分区数据查询，分区裁减，为什么查询结果是整个一级分区的？且，如下5个二级分区的查询结果都是相同的。

SELECT COUNT(1) from qt_order PARTITION(p202405,p202405sp0);
SELECT COUNT(1) from qt_order PARTITION(p202405,p202405sp1);
SELECT COUNT(1) from qt_order PARTITION(p202405,p202405sp2);
SELECT COUNT(1) from qt_order PARTITION(p202405,p202405sp3);
SELECT COUNT(1) from qt_order PARTITION(p202405,p202405sp4);

image

请问老师们，我想确认一下，我入库的数据，是不是按照我所定义的规则落库到二级分区中，有什么方法验证吗？

附建表DDL语句：

create table recharge.qt_order
(
id bigint auto_increment comment ‘主订单ID’,
agent_id bigint comment ‘代理商ID’,
phone varchar(30) comment ‘充值号码’,
face int comment ‘充值面额’,
business_id bigint comment ‘业务类型ID’,
product_id bigint comment ‘基础产品ID’,
province_id bigint comment ‘省份ID’,
city_id bigint comment ‘城市ID’,
isp int comment ‘运营商：1电信 3移动 5联通’,
cost_money decimal(20, 4) comment ‘下游扣款价’,
apply_time datetime comment ‘下单时间’,
update_time datetime comment ‘更新时间’,
order_state int comment ‘订单状态：-1待处理 0成功 1失败 2充值中 3 待人工处理’,
down_order_no varchar(100) comment ‘下游请求流水号’,
evidence varchar(200) comment ‘凭证流水’ ,
price Decimal(20, 4) comment ‘标准价’,
in_price Decimal(20, 4) comment ‘进货价’,
primary key(apply_time,id),
index idx_id(id),
index idx_downorderno(down_order_no),
index idx_phone(phone)
) comment = ‘下游订单表’
partition by range columns (apply_time)
subpartition by key(id) subpartitions 5
(
partition p202404 values less than(‘2024/05/01’),
partition p202405 values less than(‘2024/06/01’),
partition p202406 values less than(‘2024/07/01’)
);

1 个赞
#3

1 如果你想查询二级分区数据量应该是 SELECT COUNT(1) from qt_order PARTITION(p202405sp0);
2 二级分区如果是hash分区的话，个人好像是没听ob有啥反推的算法的。
一般都是通过explain 看执行计划中扫描了多少个分区 ，下面测试中第一个sql是查询一个一级分区，你会看到扫描了所有二级分区 partitions(p0sp[0-7]) 查询了7个分区表，第二个sql值子查询了一个二级分区，也就是子查询了一个分区partitions(p0sp)

	 CREATE TABLE `test1` (
	  `rowkey` varchar(64) NOT NULL COMMENT '主键',
	  `tenant_id` varchar(64) NOT NULL COMMENT '租户id',
	  `log_time` datetime(3) NOT NULL COMMENT '日志时间',
	  PRIMARY KEY (`rowkey`, `log_time`, `tenant_id`)
	) 
	 partition by range columns(log_time) subpartition by key(tenant_id) subpartition template (
	subpartition p0,
	subpartition p1,
	subpartition p2,
	subpartition p3,
	subpartition p4,
	subpartition p5,
	subpartition p6,
	subpartition p7)
	(
	partition p202404 values less than ('2024-05-01 00:00:00'),
	partition p202405 values less than ('2024-06-01 00:00:00'),
	partition p202406 values less than ('2024-07-01 00:00:00'),
	partition p202407 values less than ('2024-08-01 00:00:00'),
	partition p202408 values less than ('2024-09-01 00:00:00'),
	partition p202409 values less than ('2024-10-01 00:00:00'),
	partition p202410 values less than ('2024-11-01 00:00:00'),
	partition p202411 values less than ('2024-12-01 00:00:00'),
	partition p202412 values less than ('2025-01-01 00:00:00'));
	
	
	insert into test1 values('aaaaaaaaaaa','aaaaaa','2024-04-10');
	insert into test1 values('aaaaaaaaa1a','aaaa1a','2024-04-11');
	insert into test1 values('bbbbbbbbbbb','bbbbbb','2024-05-10');
	insert into test1 values('ccccccccccc','cccccc','2024-06-10');
	insert into test1 values('ddddddddddd','dddddd','2024-07-10');
	insert into test1 values('eeeeeeeeeee','eeeeee','2024-08-10');
	insert into test1 values('fffffffffff','ffffff','2024-09-10');
	insert into test1 values('ggggggggggg','gggggg','2024-10-10');
	insert into test1 values('hhhhhhhhhhh','hhhhhh','2024-11-10');
	
	
	explain select * from  test1 where log_time='2024-04-10' ;
	====================================================
|ID|OPERATOR               |NAME    |EST. ROWS|COST|
----------------------------------------------------
|0 |PX COORDINATOR         |        |1        |47  |
|1 | EXCHANGE OUT DISTR    |:EX10000|1        |46  |
|2 |  PX PARTITION ITERATOR|        |1        |46  |
|3 |   TABLE SCAN          |test1   |1        |46  |
====================================================

Outputs & filters:
-------------------------------------
  0 - output([INTERNAL_FUNCTION(test1.rowkey, test1.tenant_id, test1.log_time)]), filter(nil)
  1 - output([INTERNAL_FUNCTION(test1.rowkey, test1.tenant_id, test1.log_time)]), filter(nil), dop=1
  2 - output([test1.log_time], [test1.tenant_id], [test1.rowkey]), filter(nil)
  3 - output([test1.log_time], [test1.tenant_id], [test1.rowkey]), filter([test1.log_time = ?]),
	  access([test1.log_time], [test1.tenant_id], [test1.rowkey]), partitions(p0sp[0-7])	



	  
explain select * from  test1 where log_time='2024-04-10' and tenant_id='aaaaaa' ;	  
	  obclient [test]> explain select * from  test1 where log_time='2024-04-10' and tenant_id='aaaaaa' ;;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                                                                                                   |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ====================================
|ID|OPERATOR  |NAME |EST. ROWS|COST|
------------------------------------
|0 |TABLE SCAN|test1|1        |46  |
====================================

Outputs & filters:
-------------------------------------
  0 - output([test1.rowkey], [test1.tenant_id], [test1.log_time]), filter([test1.log_time = ?], [test1.tenant_id = 'aaaaaa']),
	  access([test1.log_time], [test1.tenant_id], [test1.rowkey]), partitions(p0sp)
2 个赞
#4

我看官网上写的，要查询二级分区的数据的例子是这样。可能是官网上例子没有更新？

image
image2304×582 46.8 KB

非常感谢老师的解答，我一会儿再试一下

1 个赞
#5

这个例子应该查的是p0分区和p1smp1，这两个分区的数据，p0是一级分区，p1smp1是二级分区。这两个分区之间没有从属关系。

1 个赞
#6

嗯嗯，感谢老师，我试过了，确实是能查到二级分区的数据了

1 个赞