cockroach 索引分片

cockroach 基于索引的分片

• 基于二级索引查看如下例子

drop table t123;
create table t123(a varchar(10) primary key,b varchar(20));
create index t123_1 on table t123(a,b);
alter index t123_1 partition range by(a,b)(
	partition beijing1 values from (MINVALUE,MINVALUE) TO ('100a','100b'),
	partition beijing2 values from ('100a','100b') TO ('100c','200d'),
	partition beijing3 values from ('100c','200d') TO ('200e','200f'),
	partition beijing4 values from ('200e','200f') TO (MAXVALUE,MAXVALUE);
);
show ranges from index t123@t123_1; 

drop table t123;
create table t123(a varchar(20),b varchar(30),primary key(a,b)) partition by range(a,b)
(
 partition p1 values from (MINVALUE,MINVALUE) TO ('100a','100b'),
 partition p2 values from ('100a','100b') TO ('100c','200d'),
 partition p3 values from ('100c','200d') TO ('200e','200f')
);

• 基于主键的分片

drop table  test1;
create table test1(loan_no varchar(30) primary key,b int default 1)
PARTITION BY RANGE (loan_no) (
PARTITION beijing1 VALUES FROM('80009102013614524-001') TO('80009242012616013-001'),
PARTITION beijing2 VALUES FROM('80009242012616013-001') TO('80009402012616640-001'),
PARTITION beijing3 VALUES FROM('80009402012616640-001') TO('80009412012615162-001'),
PARTITION beijing4 VALUES FROM('80009412012615162-001') TO('80009592016610678-001'),
PARTITION beijing5 VALUES FROM('80009592016610678-001') TO('80009812017616462-001'),
PARTITION beijing6 VALUES FROM('80009812017616462-001') TO('JNRCB1002886749-001')
);
ALTER PARTITION beijing1 OF INDEX test1@primary CONFIGURE ZONE USING num_replicas = 1,constraints = '{''+rack=1''}',lease_preferences = '[[+rack=1]]';
ALTER PARTITION beijing2 OF INDEX test1@primary CONFIGURE ZONE USING num_replicas = 1,constraints = '{''+rack=2''}',lease_preferences = '[[+rack=2]]';
ALTER PARTITION beijing3 OF INDEX test1@primary CONFIGURE ZONE USING num_replicas = 1,constraints = '{''+rack=3''}',lease_preferences = '[[+rack=3]]';
ALTER PARTITION beijing4 OF INDEX test1@primary CONFIGURE ZONE USING num_replicas = 1,constraints = '{''+rack=4''}',lease_preferences = '[[+rack=4]]';
ALTER PARTITION beijing5 OF INDEX test1@primary CONFIGURE ZONE USING num_replicas = 1,constraints = '{''+rack=5''}',lease_preferences = '[[+rack=5]]';
ALTER PARTITION beijing6 OF INDEX test1@primary CONFIGURE ZONE USING num_replicas = 1,constraints = '{''+rack=6''}',lease_preferences = '[[+rack=6]]';

insert into test1 values('JNRCB1002886748-001');
insert into test1 values('80009412012615162-001');
insert into test1 values('80009242012616013-001');
insert into test1 values('80009402012616640-001');
insert into test1 values('80009102013614524-001');
insert into test1 values('JJ20210112700319527');
insert into test1 values('80009892015613743-001');
insert into test1 values('80009812017616462-001');
insert into test1 values('80009592016610678-001');
insert into test1 values('80009142017617257-001');

show ranges from table test1;