一、如何选择OceanBase分区提高性能

1.1 什么是分区表？

表类型

• 非分区表
• 分区表

如下图所示，一张表被划分成了 5 个分区，分布在 2 台机器上：

1.2 分区表的优势与特点

#特点：
- 提高可用性 
- 更轻松地管理对象
- 减少 OLsqTP 系统中共享资源的争用
- 增强数据仓库中的查询性能
- 提供更好的负载均衡效果

1.3 分区类型

1.3.1 一级分区

（1）Range 分区

# 定义：
Range 分区是最常见的分区类型，通常与日期一起使用。在进行 Range 分区时，数据库根据分区键的值范围将行映射到分区。

# 特点：
- Range 分区的分区键只支持一列，并且只支持 INT 类型 
- 如果要支持多列的分区键，或者其他数据类型，可以使用 Range Columns 分区。
- RANGE分区可以新增、删除分区。如果最后一个 RANGE 分区指定了 MAXVALUE ，则不能新增分区。

# Range分区管理
##创建range分区表
-- 分区字段是start_time，类型datetime
CREATE TABLE dba_test_range_1 (
 id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
 `name` varchar(50) NOT NULL COMMENT 'name',
 start_time datetime NOT NULL COMMENT '开始时间',
PRIMARY KEY (id,start_time)
)AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COMMENT = 'test range' 
PARTITION BY RANGE(to_days(start_time))
 (
 PARTITION M202301 VALUES LESS THAN(to_days('2023-02-01')),
 PARTITION M202302 VALUES LESS THAN(to_days('2023-03-01')),
 PARTITION M202303 VALUES LESS THAN(to_days('2023-04-01'))
 );
 
-- 分区字段是start_time，类型TIMESTAMP
CREATE TABLE dba_test_range_2 (
 id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
 `name` varchar(50) NOT NULL COMMENT 'name',
 start_time TIMESTAMP NOT NULL COMMENT '开始时间',
PRIMARY KEY (id,start_time)
)AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COMMENT = 'test range' 
PARTITION BY RANGE(UNIX_TIMESTAMP(start_time))
 (
 PARTITION M202301 VALUES LESS THAN(UNIX_TIMESTAMP('2023-02-01')),
 PARTITION M202302 VALUES LESS THAN(UNIX_TIMESTAMP('2023-03-01')),
 PARTITION M202303 VALUES LESS THAN(UNIX_TIMESTAMP('2023-04-01'))
 );
 
##新增分区
-- 向 dba_test_range_2 中添加一级分区 M202304
ALTER TABLE dba_test_range_2 ADD PARTITION (PARTITION M202304 VALUES LESS THAN(UNIX_TIMESTAMP('2023-05-01')));

##删除分区
-- 删除 dba_test_range_2 中的一级分区 M202303，M202304
ALTER TABLE dba_test_range_2 DROP PARTITION M202303, M202304;
-- Truncate 一级分区
ALTER TABLE dba_test_range_2 TRUNCATE PARTITION M202303;

（2）RANGE COLUMNS 分区

# Range Columns 分区作用跟 Range 分区基本类似，不同点如下：
 - Range Columns 拆分列结果不要求是整型，可以是任意类型。
 - Range Columns 拆分列不能使用表达式。
 - Range Columns 拆分列可以写多个列（即列向量）。

# 示例
##创建 RANGE COLUMNS 分区表
CREATE TABLE dba_test_range_cloumns (
 id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
 name varchar(50) NOT NULL COMMENT 'name',
 start_time datetime NOT NULL COMMENT '开始时间',
PRIMARY KEY (id,start_time)
) AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COMMENT = 'test range columns' 
PARTITION BY RANGE COLUMNS(start_time)
 (
 PARTITION M202301 VALUES LESS THAN('2023-02-01'),
 PARTITION M202302 VALUES LESS THAN('2023-03-01'),
 PARTITION M202303 VALUES LESS THAN('2023-04-01')
);

##新增分区
-- 向 dba_test_range_cloumns 中添加一级分区 M202304
ALTER TABLE dba_test_range_cloumns ADD PARTITION (PARTITION M202304 VALUES LESS THAN('2023-05-01'));

##删除分区
-- 删除 dba_test_range_cloumns 中的一级分区 M202303，M202304
ALTER TABLE dba_test_range_cloumns DROP PARTITION M202303, M202304;
-- Truncate 一级分区
ALTER TABLE dba_test_range_cloumns TRUNCATE PARTITION M202303;

（3）Hash 分区

#定义
- 行的目标分区是由内部 Hash 函数计算出一个 Hash 值，再根据 Hash 分区个数来确定的。当分区数量为 2 的幂次方时，哈希算法会创建所有分区中大致均匀的行分布。
- 哈希算法在分区之间均匀分布行，使分区的大小大致相同。

# 使用场景&特点
- 分区的数据不是历史数据或没有明显的分区键
- 数据不能指定数据的分区键的列表特征。
- 不同范围内的数据大小相差非常大，并且很难手动调整均衡。
- 使用 RANGE 分区后数据聚集严重
- Hash 分区键的表达式必须返回 INT 类型
- HASH 分区不支持做删除操作

#示例
##创建分区表
CREATE TABLE dba_test_hash (
 id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
 `name` varchar(50) NOT NULL COMMENT 'name',
 hid int NOT NULL COMMENT 'aa',
PRIMARY KEY (id,hid)
)AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COMMENT = 'test range hash' 
PARTITION BY hash(hid)  partitions 4;

##新增分区 --> 不支持
##删除分区 --> 不支持
mysql> ALTER TABLE dba_test_hash TRUNCATE PARTITION p0;
ERROR 1235 (0A000): drop hash partition not supported

（4）Key分区

# Key 分区和 Hash 分区类似。主要区别如下：
- Hash 分区的分区键可以是用户自定义的表达式，而 Key 分区的分区键只能是列，或者不指定。
- key 分区的分区键不限于 INT 类型, 支持除 TEXT 和 BLOB 之外的所有数据类型的分区
- key 分区可以指定或不指定列，也可以指定多个列作为分区键
- 如果 Key 分区不指定分区键，那么分区键就是主键列。如果没有主键，有UNIQUE键，那么分区键就是UNIQUE键。

#示例
##创建分区表
CREATE TABLE dba_test_key (
 id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
 nn varchar(50) NOT NULL COMMENT 'nn',
 hid int NOT NULL COMMENT 'aa',
PRIMARY KEY (id,nn)
)AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COMMENT = 'test range key' 
PARTITION BY key(nn)  partitions 4;

##新增分区 --> 不支持
##删除分区 --> 不支持
mysql> ALTER TABLE dba_test_key drop PARTITION p0;
ERROR 1235 (0A000): drop hash partition not supported

（5）List 分区

#定义
数据库某个字段是离散值，为了显式的控制记录行如何映射到分区，此时可以采用LIST分区。优点是可以方便的对无序或无关的数据集进行分区。

#特点
- list分区键可以由一个或多个列组成
- 分区键可以是一列，也可以是一个表达式
- 分区键的数据类型仅支持 INT 类型。
- LIST分区可以新增分区，指定新的不重复的列表，也可以删除分区。

#示例
##创建分区表
CREATE TABLE dba_test_list (
 id bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
 nn varchar(50) NOT NULL COMMENT 'nn',
 hid int NOT NULL COMMENT 'hid',
PRIMARY KEY (id,hid)
) AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COMMENT = 'test range' 
PARTITION BY list(hid) (
 PARTITION p0 VALUES IN (1, 2, 3),
 PARTITION p1 VALUES IN (5, 6)
);

##新增分区
-- 向 dba_test_list 中添加一级分区 p2 和 p3
ALTER TABLE dba_test_list ADD PARTITION 
     (PARTITION p2  VALUES IN (7,8),
      PARTITION p3  VALUES IN (DEFAULT)
     );
     
##删除分区
ALTER TABLE dba_test_list drop PARTITION p3;
ALTER TABLE dba_test_list truncate PARTITION p2;

（6）List Columns 分区

# LIST COLUMNS 分区作用跟 LIST 分区基本相同，不同之处在于：
 - LIST COLUMNS 的拆分列不能是表达式。
 - LIST COLUMNS 的拆分列可以是多列（即列向量）

#示例
##创建分区表
CREATE TABLE dba_test_list_columns (
 id bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
 nn varchar(50) NOT NULL COMMENT 'nn',
 hid int NOT NULL COMMENT 'hid',
PRIMARY KEY (id,nn)
) AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COMMENT = 'test list column' 
PARTITION BY list COLUMNS(nn) (
 PARTITION p0 VALUES IN ('00', '01'),
 PARTITION p1 VALUES IN ('02', '03')
);

##新增分区
-- 向 dba_test_list_columns 中添加一级分区 p2 和 p3
ALTER TABLE dba_test_list_columns ADD PARTITION 
     (PARTITION p2  VALUES IN ('07','08'),
      PARTITION p3  VALUES IN (DEFAULT)
     );
##删除分区
ALTER TABLE dba_test_list_columns drop PARTITION p3;
ALTER TABLE dba_test_list_columns truncate PARTITION p2;

（7）生成列分区

◼ 一级分区
◼ 生成列是指这一列是由其他列计算而得
◼ 生成列分区是指将生成列作为分区键进行分区，该功能能够更好的满足期望将某些字段进行一定处理后作为分区键
的需求（比如提取一个字段的一部分，作为分区键）

# 示例
##创建分区表
CREATE TABLE gc_part_t(
 t_key varchar(10) PRIMARY KEY, 
 gc_user_id VARCHAR(4) GENERATED ALWAYS AS (SUBSTRING(t_key, 1, 4)) VIRTUAL, 
 c3 INT NOT NULL COMMENT 'hid'
)PARTITION BY KEY(gc_user_id) PARTITIONS 2;

1.3.2 二级分区（组合分区）

二级分区相当于在一级分区的基础上，又从第二个维度进行了拆分

示例：

如下图：

1.4 分区表管理

1.4.1 新增分区

#注意事项
（1）对于 Range/Range Columns 分区，只能在最大的分区之后添加一个分区，不可以在中间或者开始的地方添加。如果当前的分区中有 MAXVALUE 的分区，则不能继续添加分区。
（2）List/List Columns 分区添加一级分区时，要求添加的分区不与之前的分区冲突即可。
如果一个 List/List Columns 分区有默认分区即 Default Partition，则不能添加任何分区。
（3）在 Range/Range Columns/List/List Columns 分区中添加一级分区不会影响全局索引和局部索引的使用。

#查看已创建的分区
show create table tablename\G
# 查看数据落入的分区
SELECT * FROM tablename partition(M202208);

#一级分区表添加一级分区
（1）向Range中添加一级分区M1202501
ALTER TABLE tablename ADD PARTITION (PARTITION M1202501 VALUES LESS THAN(UNIX_TIMESTAMP('2025/02/01')));
（2）向List中添加一级分区 p2 和 p3
ALTER TABLE tablename ADD PARTITION 
     (PARTITION p2 VALUES IN (7,8),
      PARTITION p3  VALUES IN (DEFAULT));

#二级分区表添加一级分区
-- 向 Range Columns + Range Columns 模板化分区表 t_m_rcrc 中添加一级分区 p3 和 p4
ALTER TABLE t_m_rcrc ADD PARTITION
     (PARTITION p3 VALUES LESS THAN(400),
      PARTITION p4 VALUES LESS THAN(500));

# 添加二级分区：当前 MySQL 模式暂不支持向表中添加二级分区。

1.4.2 删除分区

# 删除一级分区
（1）删除一级分区表 table1 中的 M202207 和 M202208。
ALTER TABLE table1 DROP PARTITION M202207,M202208;

# Truncate 一级分区
（1）清除一级分区表 table1 中 M202209 和 M202208 分区的数据。
ALTER TABLE table1 TRUNCATE PARTITION M202208,M202209;

#Truncate/drop 二级分区
ALTER TABLE t2_f_rr TRUNCATE SUBPARTITION sp1,sp2;
ALTER TABLE t2_f_rr drop SUBPARTITION sp1,sp2;

1.5 分区表设计最佳实践

重点说明：
● HASH/LIST/RANGE 分区分区表达式的结果必须是 int 类型，不⽀持向量 
● HASH 分区通常能消除热点查询 
● KEY/LIST COLUMNS/RANGE COLUMNS 分区不要求是int类型，可以是任意类型，不⽀持表达式，⽀持向量 
● KEY 分区⽤户通常没有办法⾃⼰通过简单的计算来得知某⼀⾏属于哪个分区 
● KEY 分区不写分区键表示使⽤主键列 
● RANGE 分区是按⽤户指定的表达式范围将每⼀条记录划分到不同分区，⽀持 ADD DROP 分区
● ⽣成列分区是指将⽣成列作为分区键进⾏分区，该功能能够更好的满⾜期望将某些字段进⾏⼀定处理后作为分区键的需求（⽐如提取⼀个字段的⼀部分，作为分区键）

# 创建分区表注意事项
 - 如果数据量很大并且访问比较集中时，可以在创建表时使用分区表。
 - 分区表在表创建的时候需要指定，后续不支持将非分区表在线改造成分区表，也不支持分区数量、分区类型、分区键值的在线调整。
 - 建分区表时，表上的每一个主键、唯一键所对应的字段里都必须至少有一个字段包含在表的分区键字段中。
 - 分区表中的全局唯一性建议能通过主键实现的都通过主键实现。
 - 分区表的唯一索引必须包含表分区的拆分键。

# 分区键选择
 - 推荐从表的实际用途和应用场景方面进行设计/*实际用途：历史表，流水表。应用场景：存在明显访问热点的表。*/
 - hash 分区：选择区分度较大、在查询条件中出现频率最高的字段作为 hash 分区的分区键，如：用户的订单流水表，适合按照用户id进行。/**/
 - range 和 list 分区：根据业务规则选择合适的字段作为分区键，但分区数量不宜过少。/*示例：如果是日志类型的大表，根据时间类型的列做 range 分区。*/
 - key 分区：定义分区数时，需要选择质数个分区数，来协助数据分布均匀。
 - Range分区：最后一列不能是 maxvalue。
 - 如果是日志类型的大表，根据时间类型的列做 RANGE 分区是最合适的
 - 如果是并发访问非常高的表，结合业务特点选择能满足绝大部分核心业务查询的列作为拆分键是最合适的

二、利用表组提升性能

- 逻辑概念，表示一组表或者表的集合  /*会影响多个表的分区在 OceanBase 机器上的分布特征*/
- 分区方式相同的表聚集到一起形成表组
- 一个分区组的分区到一个机器上
- 可将分布式事务优化为单机事务

作用：减少join时的跨机器的通信，提高数据库性能
建议：对业务上关系密切的表，设置相同的表组; 租户的 unit_num=1 且 Primary Zone 只有一个 Zone，不需要 Table Group

• 租户的分区分布在一个observer上

• 租户的分区分布在多个observer上，t3和t4在一个表组

2.2 表组的使用限制

- 分区类型需相同
- 如果是 Key 分区，要求引用的列数相同，且分区个数相同 /*（不要求列名相同）*/
- 如果是 Hash 分区，要求分区个数相同
- 如果是 Range 分区，要求分区数相同，且 Range 分割点相同（各 value 的规则相同）。
- 分区增减只支持 Range 分区的表组。
- 表组内的表不能单独进行分区管理操作  
- 表组名称，最长 64 个字符，字符只能有大小写英文字母，数字和下划线，而且必须以字母或下划线开头，并且不能使用 OceanBase 数据库的关键字。

2.3 如何使用表组

#类型
 - 非分区表表组
 - 分区表表组

#创建表组
CREATE TABLEGROUP tgn;  #创建非分区表表组

#创建Range 分区的表组
CREATE TABLEGROUP tg1_r22 PARTITION BY RANGE 
(
 PARTITION M202301 VALUES LESS THAN(to_days('2023-02-01')),
 PARTITION M202302 VALUES LESS THAN(to_days('2023-03-01')),
 PARTITION M202303 VALUES LESS THAN(to_days('2023-04-01'))
);

#创建hash分区的表组
CREATE TABLEGROUP tg1_h PARTITION BY HASH PARTITIONS 10;

# 查看表组信息
SHOW TABLEGROUPS;
SHOW TABLEGROUPS WHERE Tablegroup_name='tgn';
SHOW CREATE TABLEGROUP tgn\G

#创建表时指定表组
##创建 myt1 非分区表，并加入 tgn 表组。
mysql> CREATE TABLE myt1 (c1 int, c2 int ) TABLEGROUP = tgn;
mysql> show tablegroups where tablegroup_name='tgn';
+-----------------+------------+---------------+
| Tablegroup_name | Table_name | Database_name |
+-----------------+------------+---------------+
| tgn             | myt1       | db_dba_lcl003 |
+-----------------+------------+---------------+
1 row in set (0.03 sec)
##创建一级分区表 ttgh 并指定表组 tg1_h
mysql> CREATE TABLE ttgh(c1 INT, c2 INT) TABLEGROUP=tg1_h PARTITION BY HASH(c1) PARTITIONS 10;
mysql> show tablegroups where tablegroup_name='tg1_h';
+-----------------+------------+---------------+
| Tablegroup_name | Table_name | Database_name |
+-----------------+------------+---------------+
| tg1_h           | ttgh       | db_dba_lcl003 |
+-----------------+------------+---------------+
1 row in set (0.02 sec)
##创建一级分区表 dba_test_range_222 并指定表组 tg1_r22
CREATE TABLE dba_test_range_222 (
 id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
 `name` varchar(50) NOT NULL COMMENT 'name',
 start_time datetime NOT NULL COMMENT '开始时间',
PRIMARY KEY (id,start_time)
)AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COMMENT = 'test range' 
TABLEGROUP=tg1_r22 PARTITION BY RANGE(to_days(start_time))
 (
 PARTITION M202301 VALUES LESS THAN(to_days('2023-02-01')),
 PARTITION M202302 VALUES LESS THAN(to_days('2023-03-01')),
 PARTITION M202303 VALUES LESS THAN(to_days('2023-04-01'))
 );
##针对表组添加分区          
ALTER TABLEGROUP tg1_r22 ADD PARTITION (PARTITION M202304 VALUES LESS THAN(to_days('2023/05/01')));
mysql> ALTER table dba_test_range_222 ADD PARTITION (PARTITION M202305 VALUES LESS THAN(to_days('2023/06/01')));
ERROR 4179 (HY000): add/drop table partition in 2.0 tablegroup not allowed

#向表组中增加表
ALTER TABLE myt1 TABLEGROUP=tgn; #修改表的表组属性 /*修改表的表组属性时名字不用单引号，否则会区分大小写。*/
ALTER TABLE myt1 set TABLEGROUP='tgn';
ALTER TABLEGROUP tgn ADD myt1;   #向表组中加入表 /*向表组中加入表的前提是表的分区策略跟表组的分区策略保持一致。*/

#删除表组
mysql> DROP TABLEGROUP tgn;  #如果该表组中存在表，删除表组时会报错，需要先移除表组中的表
ERROR 4615 (HY000): tablegroup is not empty
ALTER TABLE myt1 SET TABLEGROUP '';   #移除 tgn 表组中的 myt1 表

三、利用OceanBase的全局索引与局部索引优化SQL

3.1 传统“非分区表”中主表和索引的关系

主表的所有数据都保存在一个完整的数据结构中

 /* 传统的“非”分区表中，主表和索引的对应关系：   - 主表的所有数据都保存在一个完整的数据结构中，主表上的每一个索引也对应一个完整的数据结构（比如最常见的B+Tree），主表的数据结构和索引的数据结构之间是一对一的关系，  如下图所展示，在 employee表中，以 emp_id创建的索引：   传统的 索引保存数据的方式一般有两种： 数据区保存id 对应行数据的所有数据具体内容。 数据区保存的是真正保存数据的磁盘地址。 */ ​

3.2 分区表的索引

◼ 局部索引
局部索引又名分区索引，创建索引的分区关键字是LOCAL，分区键等同于表的分区键，分区数等同于表的分区数，总之，局部索引的分区机制和表的分区机制一样
◼ 全局索引
全局索引的创建规则是在索引属性中指定GLOBAL关键字，与局部索引相比，全局索引最大的特点是全局索引的分区规则跟表分区是相互独立的，全局索引允许指定自己的分区规则和分区个数，不一定需要跟表分区规则保持一致

3.2.1 局部索引

• 分区表的局部索引和非分区表的索引类似，索引的数据结构与主表的数据结构保持一对一的关系
• 分区表与索引的分区保持一致的分区策略，每个索引分区的索引数据覆盖相应的分区表的分区
• 对每一个索引数据结构来说，里面的键（Key）只映射到自己分区中的主表数据，不会映射到其它分区中的主表，因此这种索引被称为局部索引,也叫本地索引（LOCAL）

3.2.2 全局索引

• 分区表的全局索引不再和主表的分区保持一对一的关系，是将所有主表分区的数据合成一个整体
• 索引中的一个键可能会映射到多个主表分区中的数据（当索引键有重复值时）
• 全局索引可以定义自己独立的数据分布模式，既可以选择非分区模式也可以选择分区模式
• 在分区模式中，分区的方式既可以和主表相同也可以和主表不同。

# 分类
- 按照全局索引的分区模式来分：全局非分区索引、全局分区索引
- 按照分区键是否是全局索引的左前缀来分：全局前缀索引、全局非前缀索引

全局非分区索引（Global Non-Partitioned Index）

• 索引数据不做分区，保持单一的数据结构
• 索引中的某一个键映射到不同主表分区的情况，即一对多的对应关系

全局分区索引（Global Partitioned Index）

• 索引数据按照指定的方式做分区处理，例如做Hash分区或者Range分区，将索引数据分散到不同的分区中
• 索引的分区模式是完全独立的，和主表的分区没有任何关系
• 索引分区和主表分区之间是多对多的对应关系

全局分区索引的结构如下图所示:

3.2.3 如何对全局索引和局部索引取舍

• 业务上除了主键外，还有其他列的组合需要满足全局唯一性的强需求，这个业务需求仅能通过全局性的唯一索引来实现。
• 业务的查询无法得到分区键的条件谓词，且业务表没有高并发的同时写入，为避免进行全分区的扫描，可以根据查询条件构建全局索引，必要时可以将全局索引按照新的分区键来分区。
• 如果主表和全局索引的分区方式完全一样的话，除去具有唯一性的非前缀索引，其他索引建议定义成局部索引，全局索引在分区管理和维护上代价要远远大于局部索引
• 建分区表时，表上的每一个主键、唯一键所对应的字段里都必须至少有一个字段包含在表的分区键字段中；建议分区表的主键设置为（分区键, id），再加一个global的唯一索引（id）[OB的实现机制决定的]

#局部索引与全局索引的取舍
1. 如果需要“不包含完整分区键”的唯一约束：
   • 用全局索引
   • 或者本地索引，且需要索引列上必须带上表的分区键
2. 其它情况，case by case： 
   • 通常来说，全局索引能为高频且精准命中的查询（比如单记录查询）提速并减少IO；对范围查询则不一定哪种索
引效果更好
   • 全局索引可能会导致数据更新时带来的跨机分布式事务 /*不能忽视全局索引在DML语句中引入的额外开销：数据更新时带来的跨机分布式事务，事务的数据量越大则分布
式事务越复杂*/
3. 如果数据量较大，或者容易出现索引热点，可考虑创建全局分区索引

3.3 分区表索引创建语法

#索引使用建议
1. 建议尽可能的使用本地索引，只有在有必要的时候才使用全局索引。其原因是全局索引会降低 DML 的性能，DML 可能会因此产生分布式事务。
2. 通常创建索引时默认都是LOCAL索引，全局索引需要在后面增加关键字 global。

#示例    
#为test表创建分区表的本地索引 idx_log_name
CREATE INDEX idx_log_name ON test(log_name) LOCAL;
alter table test add index idx_log_name(log_name) LOCAL;

#为test表创建全局非分区索引
CREATE INDEX idx_log_name ON test(log_name) global;
alter table test add index idx_log_name(log_name) global;

#为test表创建全局唯一的分区索引
create unique index idx_t_p_key_c3_g on t_p_key (c3) global partition by key (c3) partitions 3;

#查看已创建的分区索引
SHOW INDEX FROM idx_log_name;
SELECT * FROM information_schema.`TABLE_CONSTRAINTS` WHERE table_schema='data_ny' AND table_name='idx_log_name';

四、如何写SQL才能使用分区裁剪 SQL提速

4.1 读懂OceanBase的执行计划

#EXPLAIN 命令格式
 - EXPLAIN BASIC 命令用于最基本的计划展示。
 - EXPLAIN EXTENDED 命令用于最详细的计划展示（通常在排查问题时使用这种展示模式）。
 - EXPLAIN 命令所展示的信息可以帮助普通用户了解整个计划的执行方式。

#执行计划示例
mysql> explain select name from dba_test_hash where hid=1 and name='aa' order by id desc limit 10\G
*************************** 1. row ***************************
Query Plan: ==============================================================
|ID|OPERATOR  |NAME                           |EST. ROWS|COST|
--------------------------------------------------------------
|0 |TABLE SCAN|dba_test_hash(idx_name,Reverse)|10       |404 |
==============================================================

Outputs & filters:
-------------------------------------
  0 - output([dba_test_hash.name]), filter([dba_test_hash.hid = 1]),
      access([dba_test_hash.hid], [dba_test_hash.name]), partitions(p1),
      limit(10), offset(nil)

1 row in set (0.25 sec)


Explain输出的：
1-第一部分是执行计划的树形结构展示。其中每一个操作在树中的层次通过其在OPERATOR中的缩进予以展示
2-第二部分是各操作算子的详细信息，包括输出表达式、过滤条件、分区信息以及各算子的独有信息（包括排序键、联接键、下压条件等）
更多执行计划算子信息，见官网：https://www.oceanbase.com/docs/enterprise-oceanbase-database-cn-10000000000357886

示例

operator：TABLE SCAN 算子是存储层和 SQL 层的接口，用于展示优化器选择哪个索引来访问数据。
   TABLE SCAN 算子的 operator 有两种形式：TABLE SCAN 和 TABLE GET。
      - TABLE SCAN 属于范围扫描，会返回 0 行或者多行数据。
      - TABLE GET 直接用主键定位，返回 0 行或者 1 行数据。
name：dba_test_hash(idx_name)。选择用哪个索引来访问数据
output：该算子的输出列
filter：该算子的过滤谓词
partitions：查询需要扫描的分区
is_index_back：该算子是否需要回表
filter_before_indexback：与每个 filter 对应，表明该 filter 是可以直接在索引上进行计算，还是需要索引回表之后才能计算

4.2 分区裁剪（Partition Pruning）

当用户访问分区表时，往往只需要访问其中的部分分区，通过优化器避免访问无关分区的优化过程我们称之为分区裁剪（Partition Pruning）。
使用分区裁剪的前提条件是查询条件中能够指定分区键，可以减少在查询过程中读取的分区个数，从而能够提高查询检索的效率。
#示例：
create table t2(c1 int primary key, c2 int, c3 int) partition by hash(c1) partitions 5;

4.2.1 一级分区裁剪-Hash/List 分区

- 根据 where 子句里面的条件计算得到分区列的值，然后通过结果判断需要访问哪些分区；
- 如果分区条件为表达式，且该表达式作为一个整体出现在等值条件里，也可以做分区裁剪

#示例
mysql> CREATE TABLE t1(c1 INT,c2 INT) PARTITION BY HASH(c1 + c2) PARTITIONS 5;
mysql> EXPLAIN SELECT * FROM t1 WHERE c1 + c2 = 1 \G
*************************** 1. row ***************************
Query Plan: ===================================
|ID|OPERATOR  |NAME|EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|t1  |5        |1303|
===================================

Outputs & filters:
-------------------------------------
  0 - output([t1.c1], [t1.c2]), filter([t1.c1 + t1.c2 = 1]),
      access([t1.c1], [t1.c2]), partitions(p1)

4.2.2 一级分区裁剪-Range 分区

- 通过 where 子句的分区键的范围跟表定义的分区范围的交集来确定需要访问的分区;
- 如果分区条件是一个函数并且查询条件是一个范围，则不支持分区裁剪

#示例
mysql> CREATE TABLE t1(c1 INT,c2 INT) PARTITION BY RANGE(c1 + 1)
       (PARTITION p0 VALUES LESS THAN(100),
        PARTITION p1 VALUES LESS THAN(200)
        );

4.2.3 二级分区裁剪

先按照一级分区键确定一级分区需要访问的分区，再通过二级分区键确定二级分区需要访问的分区。最后做一个乘积确定二级分区需要访问的所有物理分区

#示例
CREATE TABLE t1(c1 INT ,c2 INT) 
PARTITION BY hash(c1) 
SUBPARTITION BY RANGE(c2) SUBPARTITION template (
  SUBPARTITION sp0 VALUES less than(100),
  SUBPARTITION sp1 VALUES less than(200)
) partitions 5

4.3 hint指定索引查询

#hint特点
- 一种 SQL 语句注释，基于代价的优化器，与Oracle的Hint类似
- 如果使用MySQL的客户端执行带Hint的SQL语句，需要使用-c选项登陆， 否则MySQL客户端会将Hint作为注释从用户SQL中去除，导致系统无法收到用户Hint
- 如果server端不认识SQL语句中的Hint，直接忽略而不报错
- Hint只影响数据库优化器生成计划的逻辑，而不影响SQL语句本身的语义
- 该注释必须跟随SELECT、UPDATE、INSERT、REPLACE 或 DELETE 关键字。

#示例
(1) 强制走索引
select /*+ index(dba_test_hash primary)*/ * from dba_test_hash where hid=1 and name='aa' order by id desc;
(2)设置sql查询超时时间为 20 秒
select /*+ query_timeout(20000000) */ * from dba_test_hash;
(3)指定 SQL 所读取的表模式为弱一致性
SELECT /*+ READ_CONSISTENCY(WEAK) */ * from dba_test_hash where hid=1;
(4)指定并行查询
SELECT /*+ PARALLEL(8) */ * FROM dba_test_hash;

五、总结

• 根据业务情况，从表的实际用途和应用场景方面进行设计，选择合适的分区类型建立分区表
• 分区表在表创建的时候需要指定，后续不支持将非分区表在线改造成分区表，也不支持分区数量、分区类型、分区键值的在线调整。（3.x版本）
• 对于有关联性的表，建议使用关联键作为分区键，并采用相同分区方式，使用 Table Group 将相同的分区配置在同样的节点上，以减少跨节点的数据交互。
• 推荐利⽤覆盖索引来进⾏查询操作，来避免回表操作。
• 对于查询条件过滤性差的SQL，可以SQL绑定执⾏计划
• in操作能避免则避免，若实在⽆法避免，需要仔细评估in后边的集合元素数量，控制在100个之内，注意in最⼤个数8192，否则报错size overflow。