官网文档

https://www.oceanbase.com/

分区

以下按按月分区为例,有创建时间的尽量按创建时间分区,没有时间的比如关联表用一对多的一方id分区
原sql的comment语句后面添加分区语句
comment '运单订单关联表' partition by hash (waybill_id) partitions 20;

对于时间戳类型

先定义联合主键PRIMARY KEY (id, create_time) 

1
2
3
4
partition by range columns (create_time)(
        PARTITION p0 VALUES LESS THAN(1685548800),
        PARTITION p0 VALUES LESS THAN(1688140800)
    );

对于datetime类型

先定义联合主键PRIMARY KEY (id, create_time) 

1
2
3
4
5
partition by RANGE (TO_DAYS(create_time))(
    PARTITION p0 VALUES LESS THAN (TO_DAYS('2023-07-01')),
    PARTITION p1 VALUES LESS THAN (TO_DAYS('2023-08-01')),
    PARTITION p2 VALUES LESS THAN (TO_DAYS('2023-09-01'))
);

对于id类型

1
partition by hash (waybill_id) partitions 20;

修改分区规则

https://www.oceanbase.com/docs/common-oceanbase-database-10000000001701212

添加分区

对于时间戳类型:

1
2
ALTER TABLE sys_log_ob ADD PARTITION (partition p7 values less than (1701446399000));
ALTER TABLE sys_log_ob ADD PARTITION (partition p8 values less than (1701446399000));

索引

https://www.oceanbase.com/docs/common-oceanbase-database-10000000001702796

索引名要求

主键索引名为pk_字段名；唯一索引名为uk_字段名；普通索引名则为idx_字段名。

局部索引

1
CREATE INDEX idx_waybill_id_order_id ON danger_waybill_re_order(waybill_id,order_id) LOCAL;

错误

A PRIMARY KEY must include all columns in the table’s partitioning function

MySQL主键的限制，每一个分区表中的公式中的列，必须在主键/unique key 中包括。
使用联合主键即可 PRIMARY KEY (id, create_time)