【OceanBase】29.OB-SQL调优

1.OB 架构与传统数据库的差异

(1)LSM-tree存储引擎
⚫ 数 据 分 为 静 态 数 据 （ SSTable ） 和动态数据（MemTable）两部分
➢ 当数据合并后，SQL的执行效率往往都有明显的提升
⚫ buffer表是指那些被用户当做业务过程中的临时存储的数据表
➢ 内存中“标记删除”（比如快速“写入-修改-删除”的数据）
➢ 当有大量删除操作后立即访问被删除的数据范围，有可能遇到由于访问标记删除节点而
导致的执行变慢的问题

(2)分布式架构
⚫ 传统的share-disk架构：执行计划并不区分数据所在的物理节点，所有的数据访问都可以认为是
“本地”的
⚫ 分布式share-nothing架构：不同的数据被存储在不同的节点上
➢ 连接两张表时，如果两个表的数据分布在不同的物理节点上，执行计划也变为分布式执行计划
➢ 数据切主，可能出现之前本地执行计划（所访问的数据在本机）变为远程执行计划或者
分布式执行计划的问题

2.SQL 性能问题来源

◼ 用户SQL写法 - 遵循开发规约
◼ 代价模型缺陷 - 绑定执行计划
◼ 统计信息不准确 - 仅支持本地存储，合并时更新
◼ 数据库物理设计 - 决定查询性能
◼ 系统负载 - 影响整体吞吐率，影响单sql rt
◼ 客户端路由 - 远程执行

3.SQL 调优方法

◼ 针对单条 SQL 执行的性能调优
⚫ 单表访问场景
索引、排序或聚合、分区、分布式并行
⚫ 多表访问场景
连接顺序、连接算法、分布式并行、查询改写
◼ 针对吞吐量的性能优化
⚫ 优化慢 SQL
⚫ 均衡 SQL 的请求流量资源
ob_read_consistency、primary zone、客户端路由策略的设置以及业务热点查询分区的分布是否均衡等
⚫ 均衡子计划的 RPC 请求流量资源
OBServer 内部路由策略相关设置、业务热点查询的分区是否均衡 

4.SQL 调优基本流程

在 SQL 调优中，针对慢 SQL 的分析步骤如下：
1). 通过全局 SQL 审计表 (g)v$sql_audit、 SQL Trace 和计划缓存视图查看 SQL 执行信息，
初步查找 SQL 请求的流程中导致耗时或消耗资源（如内存、磁盘 IO 等）的 SQL
2). 单条 SQL 的执行性能往往与该 SQL 的执行计划相关，因此，执行计划的分析是最重要的手段。
通过执行 EXPALIN命令查看优化器针对给定 SQL 生成的逻辑执行计划，确定可能的调优方向
3). 找到具体的慢 SQL，为了使某些 SQL 的执行时间或资源消耗符合预期，常见的优化方式如下：
⚫ 对 SQL 做等价改写生成最佳执行计划
⚫ 针对多表访问的 SQL，还需要关注多表间的联接问题，通过优化访问路径、联接顺序和联接算法
等实现查询优化

5.分区表概述

◼ OceanBase 数据库把普通的表的数据按照一定的规则划分到
不同的区块内，同一区块的数据物理上存储在一起。这种划分
区块的表叫做分区表，其中的每一个区块称作分区。分区技术
是非常重要的分布式能力之一，它能解决大表的容量问题和高
并发访问时性能问题。普通的表只有一个分区，可以看作分区
表的特例。每个分区只能存在于一个节点内部，分区表的不同
分区可以分散在不同节点上。
◼ 创建分区的目的是为了在特定的SQL操作中减少数据读写的总量以减少响应时间
⚫ 可扩展性
⚫ 可管理性
⚫ 提高性能

6.OceanBase分区表特点

◼ 可多机扩展
◼ 提高可管理性
◼ 提高性能
◼ 自动负载均衡、自动容灾
◼ 对业务透明，可以取代“分库分表”方案
◼ 支持分区间并行
◼ 单表分区个数最大8192
◼ 单机partition支持上限： 8万（推荐不超过3万）

7.OceanBase分区表

◼ 分为一级分区 和 二级分区
◼ OB 现在支持的一级分区类型有：HASH， KEY， LIST，RANGE，RANGE COLUMNS，生成列分区
◼ 二级分区相当于在一级分区的基础上，又从第二个维度进行了拆分
MySQL 模式
⚫ RANGE 分区
⚫ RANGE COLUMNS 分区
⚫ LIST 分区
⚫ LIST COLUMNS 分区
⚫ HASH 分区
⚫ KEY 分区
⚫ 组合分区


Oracle 模式
⚫ RANGE 分区
⚫ LIST 分区
⚫ HASH 分区
⚫ 组合分区

8.HASH 分区

◼ 一级分区
◼ HASH分区需要指定分区键和分区个数，适合于对不能用 Range 分区、List 分区方法的场景。
通过对分区键上的Hash 函数值来散列记录到不同分区中。通常用于给定分区键的点查询，
例如按照用户id来分区。 HASH分区通常能消除热点查询。
create table t1 (c1 int, c2 int) partition by hash(c1 + 1) partitions 5
其中partition by hash(c1+1)指定了分区键c1和分区表达式c1 + 1；partitions 5指定了分区数
◼ OB MySQL模式的Hash分区限制和要求：
⚫ 分区表达式的结果必须是int类型。
⚫ 不能写向量，例如partition by hash(c1, c2)

9.KEY 分区

◼ 一级分区
◼ KEY分区与HASH分区类似，也是通过对分区个数取模的方式来确定数据属于哪个分区，不同在于：
⚫ 系统会对 Key 分区键做一个内部默认的 Hash 函数后再取模
⚫ 用户通常没有办法自己通过简单的计算来得知某一行属于哪个分区
⚫ 测试发现KEY分区所用到的HASH函数不太均匀

create table t1 (c1 varchar(16), c2 int) partition by key(c1) partitions 5

⚫ KEY分区不要求是int类型，可以是任意类型
⚫ KEY分区不能写表达式（与HASH分区区别）
⚫ KEY分区支持向量
⚫ KEY分区有一个特殊的语法
create table t1 (c1 int primary key, c2 int) partition by key() partitions 5
KEY分区分区键不写任何column，表示key分区的列是主键

10.LIST 分区

◼ 一级分区
◼ LIST分区是根据枚举类型的值来划分分区的，主要用于枚举类型
◼ LIST分区的限制和要求
⚫ 分区表达式的结果必须是int类型
⚫ 不能写向量，例如partition by list(c1, c2)

create table t1 (c1 int, c2 int) partition by list(c1) 
(partition p0 values in (1,2,3), 
partition p1 values in (5, 6)，
partition p2 values in (default));
◼ List columns 和 list 的区别是：
⚫ list columns分区不要求是int类型，可以是任意类型
⚫ list columns分区不能写表达式
⚫ list columns分区支持向量

11.RANGE 分区

◼ 一级分区
◼ RANGE分区是按用户指定的表达式范围将每一条记录划分到不同分区
常用场景： 按时间字段进行分区
◼ 目前提供对range分区的分区操作功能，能add/drop分区
存在maxvalue的分区的情况，由于add分区现在只能加在最后，所以会添加分区失败。
不存在maxvalue的分区的情况，当插入的数据超出当前分区的最大值，则会插入失败。
◼ 示例：

CREATE TABLE `info_t`(id INT, gmt_create TIMESTAMP, info VARCHAR(20), 
PRIMARY KEY (gmt_create)) 
PARTITION BY RANGE(UNIX_TIMESTAMP(gmt_create)) 
(
PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01 00:00:00')), 
PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2016-01-01 00:00:00')), 
PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2017-01-01 00:00:00'))
);

12.RANGE COLUMNS 分区

◼ 一级分区
◼ RANGE COLUMNS分区与RANGE分区类似，但不同点在于RANGE COLUMNS分区可以按一个或多个分区键向量
进行分区，并且每个分区键的类型除了INT类型还可以支持其他类型，比如VARCHAR、DATETIME等
◼ RANGE COLUMNS和RANGE的区别是
⚫ RANGE COLUMNS分区不要求是int类型，可以是任意类型
⚫ RANGE COLUMNS分区不能写表达式
⚫ RANGE COLUMNS分区支持向量
◼ 示例：

CREATE TABLE `info_t`(id INT, gmt_create DATETIME, info VARCHAR(20), PRIMARY KEY (gmt_create)) 
PARTITION BY RANGE COLUMNS(gmt_create) 
(
PARTITION p0 VALUES LESS THAN ('2015-01-01 00:00:00'),
PARTITION p1 VALUES LESS THAN ('2016-01-01 00:00:00'), 
PARTITION p2 VALUES LESS THAN ('2017-01-01 00:00:00'),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

13.生成列分区

◼ 一级分区
◼ 生成列是指这一列是由其他列计算而得
◼ 生成列分区是指将生成列作为分区键进行分区，该功能能够更好的满足期望将某些字段进行
一定处理后作为分区键的需求（比如提取一个字段的一部分，作为分区键）
◼ 示例
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) 
PARTITION BY KEY(gc_user_id) 
PARTITIONS 10;

14.二级分区

◼ 二级分区是指按照两个维度来把数据拆分成分区。最常用的地方就是类似用户账单领域，
会按照 user_id 做 HASH 分区，按照账单创建时间做 RANGE 分区
◼ 示例：按两个维度划分数据
CREATE TABLE history_t (user_id INT, 
gmt_create DATETIME, info VARCHAR(20), 
PRIMARY KEY(user_id, gmt_create)) 
PARTITION BY RANGE COLUMNS (gmt_create) 
SUBPARTITION BY HASH(user_id) SUBPARTITIONS 3 
(
PARTITION p0 VALUES LESS THAN ('2014-11-11'), 
PARTITION p1 VALUES LESS THAN ('2015-11-11'), 
PARTITION p2 VALUES LESS THAN ('2016-11-11'),
PARTITION p3 VALUES LESS THAN ('2017-11-11')
);

15.二级分区支持的分区方式

◼ 二级分区支持的分区方式
⚫ HASH/KEY + RANGE/RANGE_COLUMNS 分区
⚫ RANGE/RANGE_COLUMNS + HASH/KEY 分区
⚫ LIST/LIST_COLUMNS+ RANGE/RANGE_COLUMNS 分区
⚫ RANGE/RANGE_COLUMNS + LIST/LIST_COLUMNS分区
⚫ HASH/KEY + LIST/LIST_COLUMNS 分区
⚫ LIST/LIST_COLUMNS+HASH/KEY 分区
◼ 对于 RANGE 分区的分区操作 add/drop，必须是 RANGE 分区做为一级分区的方式。
所以强烈建议用 RANGE + HASH 的分区方式，而不是 HASH + RANGE。

16.总结 

HASH/KEY==RANGE 
LIST==RANGE 
LIST==HASH 

如上组合没有先后顺序可以创建分区表。