笔记 | OceanBase DBA 实战营

基础运维OceanBase 运维实战

1、分区表的使用技巧

partition(分区)设计规范
分布式数据库的优势在于对于空间问题和请求访问问题分而治之。针对每个分区的访问，由该分区所在的节点响应即可。 即使该 SQL 并发很高，由于访问的是不同的分区，分别由不同的节点提供服务。每个节点自身也有一定能力满足一定的 QPS，所有节点集中在一起就能提供更大的 QPS。这个时候如果扩容节点数量，该 SQL 总的 QPS 也能获得相应的提升，这是分布式数据库里最好的情形。
用户必须在业务查询条件明确的情况下，根据真实业务场景进行分区规划，不要在场景不明确的情况下随意进行分区规则。在规划分区时，建议尽量保证各个分区的数据量相对均衡。最常用的三种分区方式如下:
·HASH 分区:一般适用于分区列 NDV(不同值的种类)较大，且难以划分出明确范围的情况。优点是容易让没有特定规则的数据也能够在不同的分区内均匀分布，缺点是在范围查询时难以进行分区裁剪。
·RANGE 分区:一般适用于分区键容易划分出明确的范围的情况，例如可以把记录流水信息的大表，根据表示信息时间的列做 RANGE 分区。
LIST 分区:一般适用于需要显式控制各行数据如何映射到具体的某一个分区时，"优点是可以对无序或无关的数据集进行精准分区，缺点是在范围查询时难以进行分区裁剪。
为了更好地支持并行计算和分区裁剪，OceanBase 还支持二级分区。OceanBase 数据库MySQL模式目前支持 HASH、RANGE、LIST、KEY、RANGE COLUMNS 和 LIST COLUMNS 六种分区类型，二级分区为任意两种分区类型的组合。
操作步骤
步骤 1：连接数据库
使用 root 用户登录集群的 sys 租户。

obclient -h127.0.0.1 -uroot@sys -P2881 -Dtestdb -A

步骤 2：插入 List 分区
在进行 List 分区时，数据库使用离散值列表作为每个分区的分区键。分区键由一个或多个列组成。 可以使用 List 分区来控制单个行如何映射到指定分区。当不方便根据分区键进行排序时，可以使用 List 分区对数据进行分组和管理。List 分区仅支持单分区键，分区键可以是一列，也可以是一个表达式。分区键的数据类型仅支持 INT 类型。

CREATE TABLE `performance` (
                       `id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
                       `course_id` int NOT NULL COMMENT '课程 ID，关联 course.id',
                       `student_id` int NOT NULL COMMENT '学号，关联 student.id',
                       `score` int COMMENT '课程分数， 值范围 0~100， NULL 表示无成绩',
                       PRIMARY KEY (`id`, `course_id`),
                       UNIQUE KEY ux_performance_course_student (`course_id`, `student_id`)
                   ) COMMENT '成绩' PARTITION BY LIST (course_id) (
                       PARTITION p0 VALUES IN (1, 2),
                       PARTITION p1 VALUES IN (3, 4)
                   );

步骤 3：查询表分区信息
SHOW CREATE TABLE 命令查询分区
使用 SHOW CREATE TABLE 命令查询所创建表的语句，从而获取表分区信息。 查询 performance 表中的分区信息。

SHOW CREATE TABLE `performance`;

NFORMATION_SCHEMA.partitions 命令查询分区
除了 SHOW CREATE TABLE 命令外，同时可以通过访问 INFORMATION_SCHEMA 中的 partitions 表，获取分区信息的元数据。 查询 performance 表中的分区信息。

SELECT * FROM INFORMATION_SCHEMA.partitions WHERE TABLE_NAME = 'performance';

步骤 4：访问指定分区
在 performance 表中插入几条示例数据，并查询指定分区中的数据。

INSERT INTO
                     `performance` (`course_id`, `student_id`, `score`)
                   VALUES
                     (1, 1, 81),
                     (1, 2, 90),
                     (2, 1, 88),
                     (2, 2, 75),
                     (3, 1, 99),
                     (3, 2, 85);
SELECT * FROM performance partition(p0);
SELECT * FROM performance partition(p1);
--通过 EXPLAIN 查询此时扫描的分区数
EXPLAIN SELECT * FROM performance partition(p1);

步骤 5：添加分区
通过 ALTER TABLE … ADD PARTITION 命令可以添加分区。 在 performance 表中添加一个分区。

ALTER TABLE `performance`
                   ADD PARTITION (
                       PARTITION p3 VALUES IN (7, 8)
                   );
SHOW CREATE TABLE `performance`;
-- 当存在 DEFAULT 分区时，无法添加分区。

2、理解、查询、修改 —— OceanBase 中的系统变量、配置项、用户变量

OceanBase 数据库提供了“系统变量”和“配置项”两种不同的参数来对数据库的行为进行配置，使之能够符合业务的要求。
有很多用户可能还不了解系统变量和配置项的影响范围，例如某个参数的设置，是会影响整个集群，还是影响当前租户，亦或是只影响当前 session。所以先抛开用户变量，先说说这两个更常见和常用的东西。
2.1租户系统变量
OceanBase 的系统变量都是租户级的，也就是只对当前的租户生效。所以系统变量又可以被称作:租户
系统变量。
设置系统变量时，又可以通过增加 Global 关键字或者 Session 关键字，来设置对应系统变量为全局级别或者会话级别，以调整系统变量在租户内的生效范围:
全局变量:表示 Global 级别的修改，数据库同一租户内的不同用户共享一个全局变量。全局变量的修改不会随会话的退出而失效。此外，全局变量修改后，对当前已打开的 Session 不生效，需要重新建立Session 才能生效。
会话变量:表示 Session 级别的修改，Session 变量的修改仅对当前 Session 生效。当新的客户端连接到数据库后，数据库会复制全局变量来自动生成对应连接上的 Session 变量。
执行 show variables;可以看到全量系统变量
2.2配置项
OceanBase 数据库的配置项分为集群级配置项和租户级配置项。
集群级配置项:指适用于整个 OceanBase 数据库集群的配置选项，它们具有全局性质，用于配置整个集群的基本信息、性能参数、安全选项等等。这些配置项通常包括数据备份和恢复、负载均衡等方面的配置选项。集群级配置项通常是在集群启动时进行配置，配置后不轻易修改。
租户级配置项:指适用于租户级别的配置选项，它们是针对单个租户或多个租户的配置选项。用于对单个租
户或多个租户进行特定的配置和优化。这些配置项通常包括存储引擎参数、SQL 执行策略、访问控制等方面的配置选项。租户级配置项通常可以在租户创建和管理时进行配置，可以随时根据需要进行修改。
SHOW PARAMETERS;展示所有配置项
scope 列对应的值为 CLUSTER 表示该配置项的生效范围为整个集群;如果 scope 列对应的值为TENANT，则表示该配置项的生效范围为当前租户。edit level 列对应的值为 DYNAMIC EFFECTIVE 时表示修改后立即生效，为 STATIC EFFECTIVE 时则表示需要在重启 OBServer 节点后生效。
2.3用户自定义变量
“用户变量”其实就是“用户自定义变量”，一般会出现在 PL 的存储过程和匿名块之类的地方。

--查询当前租户
SHOW tenant;
--查看租户列表，非 sys 租户下只能查看当前租
SELECT * FROM oceanbase.DBA_OB_TENANTS;
--查询系统参数
SHOW parameters;
-- 查询系统变量 
SHOW variables;
--查询 Unit 分布
--通过 oceanbase.gv$ob_units 视图查看 Unit 分布信息。
SELECT
                     unit_id,
                     zone,
                     max_cpu,
                     min_cpu,
                     ROUND(memory_size/1024/1024/1024, 1) AS momory_GB
                   FROM
                   oceanbase.gv$ob_units;

3、关于通过内部表进行元数据查询的一些常见问题

查询监控指标
通过 oceanbase.gv$sysstat 视图可以查询租户的各类监控指标。

-- 查询全部监控指标
SELECT * FROM oceanbase.`gv$sysstat`;
-- 查询特定监控指标
SELECT * FROM oceanbase.`gv$sysstat` WHERE name LIKE '%cpu%';
--内存相关 
SELECT * FROM oceanbase.`gv$sysstat` WHERE name LIKE '%memory%';
-- MemStore 相关
SELECT * FROM oceanbase.`gv$sysstat` WHERE name LIKE '%memstore%';
-- SQL 语句相关
SELECT * FROM oceanbase.`gv$sysstat` WHERE name LIKE 'sql%';
-- IO 相关
SELECT * FROM oceanbase.`gv$sysstat` WHERE name LIKE 'io%';

查询计算指标
gv$sysstat 视图维护的均为原始指标，有时我们需要观察 qps、rt 等速率指标。 此时需要通过计算不同时间采样的值，例如通过 SQl 查询次数可以计算每秒的查询次数

-- 先执行第一次采样
SELECT CURRENT_TIMESTAMP, SUM(`VALUE`) INTO @t1, @v1 FROM oceanbase.`gv$sysstat` WHERE `NAME` = @metric_name ;
SELECT sleep(2) FROM dual;
-- 再进行下一次采样
SELECT CURRENT_TIMESTAMP, SUM(`VALUE`) INTO @t2, @v2 FROM oceanbase.`gv$sysstat` WHERE `NAME` = @metric_name ;
-- 进行计算
SELECT ROUND((@v2-@v1) * 1000000 / (TIMESTAMPDIFF(MICROSECOND,@t1,@t2)), 0) AS value_per_second FROM dual;

查询内存状态
每个租户内部的内存分模块划分，需要关注的模块主要包含：

不可动态伸缩的内存：MemStore
可动态伸缩的内存：KVCache
执行计划缓存：PLAN CACHE
执行 SQL 内存：SQL AREA，主要供 parser 和优化器使用
工作线程内存：WORKER AREA

-- 查询内存使用大于 200MB 的模块
SELECT * FROM oceanbase.gv$ob_memory WHERE used > 1024*1024*200;
--查询内存占用最大的 10 个模块
SELECT
                     `MOD_NAME`,
                     ROUND(USED / 1024 / 1024 , 2) AS USED_MB
                   FROM oceanbase.gv$ob_memory
                   GROUP BY `MOD_NAME`
                   ORDER BY USED DESC 
                   limit 10;

查询转储状态
所谓 MemStore，是指租户申请的内存资源中可供存放更新的数据量， 例如一个租户可使用的内存为 8GB (建立租户时，Resource Unit 的 min_memory)， 系统参数 memstore_limit_percentage 控制可用于存写入数据的内存空间（其余的内存会被用作其它用途，例如缓存）。

当租户的 MemStore 内存使用率达到 freeze_trigger_percentage 参数的值， 并且转储的次数已经达到了 major_compact_trigger 参数的值，会自动触发合并。

SELECT `TENANT_ID`, `SVR_IP`, `SVR_PORT`,
                   round(ACTIVE_SPAN/1024/1024/1024, 2) AS ACTIVE_SPAN_GB,
                   round(MEMSTORE_USED/1024/1024/1024, 2) AS MEMSTORE_USED_GB,
                   round(FREEZE_TRIGGER/1024/1024/1024, 2) AS FREEZE_TRIGGER_GB,
                   round(MEMSTORE_LIMIT/1024/1024/1024, 2) AS MEMSTORE_LIMIT_GB,
                   FREEZE_CNT AS FREEZE_COUNT
                   FROM oceanbase.gv$ob_memstore;

字段含义：

ACTIVE_SPAN：当前租户活跃的 MemStore。
MEMSTORE_USED：当前租户 MemStore 总和。
FREEZE_TRIGGER：触发 Major Freeze 的值大小。
MEMSTORE_LIMIT：租户总的内存上限大小。
FREEZE_CNT：上一次合并后到现在为止租户转储次数。

OceanBase 运维实战性能调优

4、阅读和管理 OceanBase SQL 执行计划

查看执行计划大抵有两种方式，一种是通过 explain 来看优化器预估下次执行时会用到的计划(nexttime)，另一种是通过 dbms_xplan.display_cursor 系统包函数分析真实出现过的执行计划。我们先来看大家更不了解，功能也更强大的这个 dbms xplan.display cursor 系统包函数。
4.l: 通过 dbms_xplan.display_cursor 查看执行计划
在 SQL 性能调优的过程中，大家可能会遇到优化器因为种种原因，导致估行出学出较入的偏差，进而在计划空间里评估计划代价时出现问题，最终导致选择不优的计划，让SQL性能变差。
优化器出现这个问题的根因，可能是用户没及时收集统计信息，或者收集统计信息的方式不优，再或者数据库本身的问题等等，这里不细说。
DBMS XPLAN 这个系统包里一个叫做 display_cursor 的 Function。这个函数可以看到真实的执行计划，还能看到每个算子真实处理的行数和实际耗时，还还能和优化器生成计划时的估行(estimaterows/time)进行对比，是一个分析 SQL 性能问题的好方法。
4.2:通过 EXPLAIN 查看执行计划
创建 student 和 performance 表并写入数据
以教学管理系统的学生对象为例，创建表名为 student 的学生表和 performance 表，并添加数据。

-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
                     `id` int NOT NULL AUTO_INCREMENT COMMENT 'ID, 学号',
                     `name` varchar(20) NOT NULL COMMENT '姓名',
                     `sex` varchar(1) NOT NULL COMMENT '性别，可选值 m 男性，f 女性',
                     `birth` date NULL COMMENT '出生日期',
                     `province` varchar(20) NOT NULL COMMENT '省份',
                     `city` varchar(20) NULL COMMENT '城市',
                     `mobile_phone` varchar(20)  NULL COMMENT '手机号',
                     `email` varchar(128)  NULL COMMENT '邮箱地址',
                     `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '审计字段，记录创建时间',
                     `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '审计字段，记录修改时间',
                     PRIMARY KEY (`id`),
                     KEY idx_student_province_city (`province`, `city`),
                     UNIQUE KEY ux_student_mobile_phone (`mobile_phone`),
                     UNIQUE KEY ux_student_email (`email`)
                   ) COMMENT '学生';
-- 学生表内插入数据
INSERT INTO
                     `student` (`name`, `sex`, `birth`, `province`, `city`, `mobile_phone`, `email`)
                   VALUES
                     ('李四', 'm', '2001-12-12', '浙江', '杭州', '12000002222', 'lisi@xxx.cn'),
                     ('王五', 'm', '2002-02-28', '四川', '成都', '12000003333', 'wangwu@xxx.cn'),
                     ('赵六', 'm', '2002-11-23', '浙江', '宁波', '12000004444', 'zhaoliu@xxx.cn');
--创建课程表
DROP TABLE IF EXISTS `performance`;
CREATE TABLE `performance` (
                     `id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
                     `course_id` int NOT NULL COMMENT '课程 ID，关联 course.id',
                     `student_id` int NOT NULL COMMENT '学号，关联 student.id',
                     `score` int COMMENT '课程分数， 值范围 0~100， NULL 表示没有成绩',
                     `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '审计字段，记录创建时间',
                     `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '审计字段，记录修改时间',
                     PRIMARY KEY (`id`),
                     UNIQUE KEY ux_performance_course_student (`course_id`,`student_id`)
                   ) COMMENT '成绩';
-- 课程表中插入数据
INSERT INTO `performance`
                     (`course_id`, `student_id`, `score`)
                   VALUES
                     (1, 1, 81),
                     (1, 2, 90),
                     (1, 3, 99),
                     (2, 1, 88),
                     (2, 2, 75),
                     (2, 3, 85);

使用 EXPLAIN 命令查询 SELECT、DELETE、INSERT、REPLACE 或 UPDATE 语句的执行计划。

--使用 explain 命令查询语句的执行计划。
EXPLAIN SELECT * FROM  `student`;
--使用 explain basic 命令查询语句的执行计划
EXPLAIN BASIC SELECT * FROM  `student`;
-- 使用 explain extended 命令查询语句的执行计划
EXPLAIN EXTENDED SELECT * FROM  `student`;

TABLE SCAN
TABLE SCAN 表明当前查询对表中的每个行组合进行扫描，是存储层和 SQL 层的接口，用于展示优化器选择哪个索引来访问数据。该算子属于范围扫描，会返回 0 行或者多行数据
TABLE SCAN 算子的 operator 同时包含 TABLE GET 形式，表明使用主键进行定位，返回 0 行或 1 行数据
JOIN 算子用于将两张表的数据，按照特定的条件进行联接。OceanBase 数据库支持的 JOIN 算子主要包含 NESTED LOOP JOIN (NLJ)、MERGE JOIN (MJ) 和 HASH JOIN (HJ)。
其中 0 号算子是一个 MERGE JOIN 算子，包含两个子节点，分别是 1 和 2 号算子，2 号算子是对 3 号算子的数据进行排序。该计划的执行逻辑步骤如下：
读取 performance 的数据，并进行排序。
根据联接列，尝试与 student 表的数据进行联接

5、通过 Hint 控制计划生成的形态

Hint 机制可以使优化器生成指定的执行计划。一般情况下，优化器会为用户查询选择最佳的执行计划，不需要用户使用 Hint 指定。但在某些场景下，优化器生成的执行计划可能不满足用户的要求，这时就需要用户使用 Hint 来指定生成某种执行计划。
5.1 Hint 语法
{ DELETE I INSERT | SELECT I UPDATE | REPLACE } /*+ [hint text][,hint text]… /
例如下面这条查询语句，通过 /+ PARALLEL(3) /指定 SQL 的执行并行度为 3，通过 explain 就可以看到计划的 dop =3，通过 explain EXTENDED_NOADDR 还可以看到计划中会展示一个 Note:Degree of Parallelism is 3 because of hint.
Hint 从语法上看是一种特殊的 SQL 注释，所不同的是在注释的左标记后("/“符号)增加了一个”+"因为 Hint 是注释，所以如果因为语法出错的原因，导致 observer 服务端无法识别 SQL 语句中的Hint，优化器会选择忽略用户指定的 Hint，去生成默认的执行计划。另外，Hint 只影响优化器生成的执行计划，不会影响 SQL 语句的语义。
使用常用 Hint 前，需要创建表并写入数据。 以教学管理系统教师表和课程表为例，创建表名为 teacher 的教师表和 course 课程表，并写入教师和课程数据。

--创建教师表
CREATE TABLE `teacher` (
                    `id` int NOT NULL AUTO_INCREMENT COMMENT 'ID，教师编号',
                    `name` varchar(20) NOT NULL COMMENT '姓名',
                    `sex` varchar(1) NOT NULL COMMENT '性别，可选值 m 男性，f 女性',
                    `mobile_phone` varchar(20) NULL COMMENT '手机号',
                    `email` varchar(128) NULL COMMENT '邮箱地址',
                    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '审计字段，记录创建时间',
                    `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '审计字段，记录修改时间',
                    PRIMARY KEY (`id`),
                    UNIQUE KEY ux_teacher_mobile_phone (`mobile_phone`),
                    UNIQUE KEY ux_teacher_email (`email`)
                   ) COMMENT '教师';
--创建课程表，以 teacher_id 键进行 HASH 分区，分区数量 3
CREATE TABLE `course` (
                    `name` varchar(20) NOT NULL COMMENT '课程名称',
                    `teacher_id` int NOT NULL COMMENT '授课教师，关联 teacher.id',
                    `start_date` date NOT NULL COMMENT '开课日期',
                    `credit` int NOT NULL COMMENT '学分',
                    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '审计字段，记录创建时间',
                    `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '审计字段，记录修改时间',
                    PRIMARY KEY (`name`,`teacher_id`, `start_date`)
                   ) COMMENT '课程'
                   PARTITION BY HASH(`teacher_id`)
                   PARTITIONS 3;
-- 写入教师数据
INSERT INTO `teacher` (`id`,`name`, `sex`,`mobile_phone`,`email`) VALUES
                   (1, '张三', 'm', '12000001111','zhangsan@xxx.cn'),
                   (2, '李四', 'f', '12000002222','lisi@xxx.cn'),
                   (3, '王五', 'f', '12000003333','wangwu@xxx.cn'),
                   (4, '赵六', 'm', '12000004444','zhaoliu@xxx.cn');
--写入课程数据
INSERT INTO `course`(`name`,`teacher_id`,`start_date`,`credit`) VALUES
                   ('古代文学', 1, '2021-09-05', 4),
                   ('现代汉语', 1, '2021-11-02', 2),
                   ('线性代数', 2, '2021-10-09', 6),
                   ('概率与统计', 2, '2021-11-02', 4),
                   ('有机化学', 3, '2021-11-02', 4),
                   ('无机化学', 3, '2021-09-08', 4),
                   ('C++程序设计', 1, '2021-09-02', 2),
                   ('操作系统', 1, '2021-11-02', 4),
                   ('编译原理', 1, '2021-11-08', 6);

--QUERY_TIMEOUT Hint 指示服务器设定某条 SQL 执行时的超时时间，单位为微秒
/*+ QUERY_TIMEOUT (intnum) */
SET @previous_ob_query_timeout = @@ob_query_timeout;
SET @@ob_query_timeout = 3000000;
-- 模拟 5 秒查询，查询结果报 4012 时间超时
SELECT sleep(5) from dual;
-- 使用 Hint 将当前查询的临时查询超时更改为 10 秒，查询成功
SELECT /*+ QUERY_TIMEOUT (10000000) */ sleep(5) FROM dual;
-- 把 ob_query_timeout 修改为原来的值
SET @@ob_query_timeout = @previous_ob_query_timeout;

并行执行并行度
PARALLEL Hint 是语句级的 Hint，用于指示优化器指定并行操作可使用的并行线程的数量。此 Hint 将覆盖初始化参数 PARALLEL_DEGREE_ POLICY 的值。该 Hint 可以作为 SELECT、INSERT、UPDATE 和 DELETE 语句的一部分，以及表扫描的一部分。如果违反了任何的并行限制，则 PARALLEL Hint 被忽略。

USE_PX Hint 强制指示服务器在执行 SQL 语句时使用 PX 模式，PX 模式允许在执行语句时采用多线程方式。一般 USE_PX Hint 和 PARALLEL Hint 配合使用。

/*+ PARALLEL(integer) */
/*+ USE_PX */
SELECT /*+ PARALLEL(3) */ *  FROM `course`;
SELECT /*+ USE_PX PARALLEL(3)*/ c.teacher_id as teacher_id, sum(c.credit) as total_credit FROM course c GROUP BY c.teacher_id;

弱一致性读
READ_CONSISTENCY Hint 指示服务器去指定某条 SQL 所读取的表模式为弱一致（指定参数 WEAK）或强一致性（指定参数 STRONG）

/*+ READ_CONSISTENCY(WEAK) */
SELECT /*+ READ_CONSISTENCY(WEAK) */ t.`name` as teacher_name, c.`name` as course_name,  c.`credit` as credit FROM `teacher` t JOIN `course` c ON t.id=c.teacher_id WHERE t.id = 2;

访问路径指定索引
INDEX Hint 指示优化器对指定的表使用索引扫描。您可以将 INDEX Hint 用于基于函数、域、B-Tree、位图和位图联接的索引。

/*+ INDEX ( [ @queryblock ] tablespec [ indexspec [ indexspec ]... ] ) */
EXPLAIN SELECT * FROM `teacher` WHERE `email` like 'zhang%';
-- 指定访问路径
EXPLAIN SELECT /*+ INDEX (teacher ux_teacher_mobile_phone)*/ * FROM `teacher` WHERE `email` like 'zhang%';

联接顺序
LEADING Hint 指示优化器在执行计划中使用指定的表作为驱动表来指定表的联接顺序。LEADING Hint 比 ORDERED Hint 更通用。

-- 指定联接顺序为 teacher 到 course
EXPLAIN SELECT /*+ LEADING(t c) */ t.`name` as teacher_name, c.`name` as course_name, c.`credit` as credit FROM `teacher` t JOIN `course` c ON t.id=c.teacher_id;
-- 指定联接顺序为 course 到 teacher
EXPLAIN SELECT /*+ LEADING(c t) */ t.`name` as teacher_name, c.`name` as course_name, c.`credit` as credit FROM `teacher` t JOIN `course` c ON t.id=c.teacher_id; 

通过观察执行计划可以确定实际的联接顺序。例如查看 teacher 到 course 的联接顺序对应的执行计划
SQL 改写
OceanBase 数据库支持基于规则和代价的查询改写。

基于代价的改写举例（OR-EXPANSION）
OR-EXPANSION 将一个查询改写成若干个用 UNION 组成的子查询，此改写可能会给每个子查询提供更优的优化空间，但是也会导致多个子查询的执行，因此此改写需要基于代价去判断。例如 OR-EXPANSION 的改写可以允许每个分支使用不同的索引来加速查询。如下例所示，Q1 会被改写成 Q2 的形式，其中 Q2 中的谓词 lnnvl(t2.mobile_phone='12000001111) 保证了这两个子查询不会生成重复的结果。如果不进行改写，Q1 通常会选择主表作为访问路径；对于 Q2，如果 t1 上存在索引（a）和索引（b），则此改写可能会使 Q2 中的每一个子查询选择索引作为访问路径。

SELECT * FROM `teacher` t where t.`mobile_phone`='12000001111' or t.`email`='wangwu@xxx.cn';
SELECT * FROM `teacher` t1 where t1.`mobile_phone`='12000001111' 
                   union all
                   SELECT * FROM `teacher` t2 where t2.`email`='wangwu@xxx.cn' and lnnvl(t2.`mobile_phone`='12000001111');

使用 NO_REWRITE 禁用查询改写
查询改写有一定的可能性导致性能下降，此时可通过 NO_REWRITE Hint 指示优化器禁用查询块的查询改写

SELECT /*+ NO_REWRITE */  * FROM `teacher` t where t.`mobile_phone`='12000001111' OR t.`email`='wangwu@xxx.cn';

OceanBase 运维实战-- 问题诊断

6、分析 SQL 监控视图

查询 SQL 审计表信息
SQL 审计表 gvob_sql_audit 记录了最近执行的 SQL 详细信息，可以用于分析 SQL 执行情况。在使用 gvob_sql_audit 时主要关注耗时信息和特征信息。

SELECT svr_ip, trace_id, user_name, db_name, sql_id, query_sql, plan_id FROM  oceanbase.gv$ob_sql_audit LIMIT 1;
SELECT svr_ip, query_sql, elapsed_time, queue_time, execute_time, total_wait_time_micro, event FROM  oceanbase.gv$ob_sql_audit LIMIT 1;
SELECT sql_id, query_sql, is_executor_rpc, is_inner_sql, ret_code, plan_type, rpc_count, is_hit_plan, request_type FROM  oceanbase.gv$ob_sql_audit LIMIT 1;
-- 模拟 SQL 执行出错
SELECT wrong syntax FROM table_not_exists;
--  查询最近 5 分钟出错的语句
SELECT substr(usec_to_time(s.request_time),1,19) request_time, 
                   s.svr_ip, s.user_name, s.db_name, s.sql_id, s.query_sql, s.ret_code, s.elapsed_time, s.queue_time, s.execute_time, 
                   round(s.request_memory_used/1024/1024/1024,2) req_mem_mb, plan_type, is_executor_rpc, is_inner_sql, trace_id 
                   FROM oceanbase.gv$ob_sql_audit s
                   WHERE ret_code < 0 
                   AND db_name=DATABASE()
                   AND request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 5 MINUTE) )
                   ORDER BY request_time DESC
                   LIMIT 100;
--模拟慢 SQL 通过 sleep() 函数模拟慢 SQL
SELECT sleep(5) as slow_test_query_5_seconds FROM dual;
-- 通过 query_sql like '%slow_test%' 查询条件匹配步骤 a 的 SQL 语句
SELECT svr_ip, query_sql,
                      elapsed_time, queue_time, execute_time, total_wait_time_micro, event
                   FROM  oceanbase.gv$ob_sql_audit WHERE query_sql LIKE '%slow_test%' LIMIT 10;
--elapsed_time 统计了 SQL 执行的总耗时，按照 elapsed_time 逆序排序查询耗时最长的 10 个 SQL 执行记录
SELECT svr_ip, query_sql,
                      elapsed_time, queue_time, execute_time, total_wait_time_micro, event
                   FROM oceanbase.gv$ob_sql_audit 
                   WHERE is_executor_rpc=0 AND is_inner_sql=0 
                   AND request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 5 MINUTE) )
                   ORDER BY elapsed_time DESC LIMIT 10;

查询占用 CPU 最多的 SQL
CPU 时间的计算公式为 execute_time+get_plan_time-total_wait_time_micro ；分布式计划和远程计划的时间是分别计算的，因此在查询时需要使用 trace_id 进行聚合计算

-- 查询本地计划 CPU 时间
SELECT svr_ip, query_sql,plan_type,
                       (execute_time+get_plan_time-total_wait_time_micro) AS cpu_time,
                       elapsed_time, queue_time, execute_time, total_wait_time_micro
                   FROM  oceanbase.gv$ob_sql_audit 
                   WHERE is_executor_rpc=0 AND is_inner_sql=0 AND plan_type=1
                   AND request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 5 MINUTE) )
                   ORDER BY cpu_time DESC LIMIT 10;

-- 查询分布式计划和远程计划 CPU 时间
SELECT trace_id, sql_id, substr(usec_to_time(request_time),1,19) AS request_time_, query_sql, plan_type,
                      sum(execute_time+get_plan_time-total_wait_time_micro) AS cpu_time,
                      sum(execute_time) AS execute_time, 
                      sum(get_plan_time) AS get_plan_time, 
                      sum(total_wait_time_micro) AS total_wait_time_micro
                   FROM oceanbase.gv$ob_sql_audit 
                   WHERE is_executor_rpc=0 AND is_inner_sql=0 AND plan_type<>1
                   AND request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 5 MINUTE) )
                   GROUP BY trace_id
                   ORDER BY cpu_time DESC LIMIT 10;

7、全链路诊断

全链路诊断可以记录全链路各阶段的耗时及各组件诊断相关的 Trace 信息，帮助用户快速定位组件内部的问题所在。

用户可以在 OceanBase 数据库的交互场景使用 Show Trace 能力，通过 Show Trace 命令快速定位问题。

当用户在命令行中手动执行某个语句后，如果需要对其执行过程进行性能分析或调优，可以使用 Show Trace 功能来查看该语句的执行调用链路情况以及链路中各阶段的耗时情况。通过该功能，用户可以便捷地找到性能瓶颈点，进而进行分析和优化。

本教程介绍如何使用 Show Trace 来分析慢查询的原因以及进行优化。

--创建测试表
create table tp1(c1 bigint,c2 bigint);
-- 插入数据
insert into tp1 select uniform(1,1000000,RANDOM()),uniform(1,1000000,RANDOM()) from table(generator(1000000));
-- 查看插入了哪些数据
select * from tp1 limit 10;
-- 开启 Show Trace 功能
set ob_enable_show_trace = 'on';
--执行以下查询语句
select * from tp1 where c1 = 802052;
-- 通过 Show Trace 语句查看该语句的执行调用链路情况以及链路中各阶段的耗时情况
Show Trace;
-- 查询表 tp1 的结构并分析
desc tp1;
-- 为测试表创建索引
create index z on tp1(c1) global;
-- 重新执行上述查询语句
select * from tp1 where c1 = 802052;
-- 再次通过 Show Trace 语句查看该语句的执行调用链路情况以及链路中各阶段的耗时情况，此时发现查询效率明显提升
show trace;

新特性体验OceanBase 运维实战

8、旁路导入

目前，OceanBase主流使用三种导入方式:load data、obloader 和 oms，这些方式都使用 insert 语句将数据写入 OceanBase。
然而，使用 insert 语句写入数据需要经过 SQL、事务和存储。OceanBase 使用 LSM-Tree 结构进行存储。在这种存储结构中，insert 语句会先将数据写入内存表中，然后经过多轮转储和合并，才能最终存入最底层的 SSTable 中。这些过程消耗了大量的系统资源，特别是 CPU 资源，因此导入数据的速度不够理想。
为了加快导入速度，我们采用了一种绕过中间步骤的技术，将需要导入的数据直接写入最底层的SSTable 中，这种技术称为旁路导入。此外，旁路导入还可以用于加速一些需要大量写入 SQL 的操作,例如背景中提到的 insert into select 语句。旁路导入的语法详见官网中几个相关的语法，例如 loaddat2

-- 创建测试表 t1、t2、t3
create table t1(c1 bigint,c2 bigint);
 create table t2(c1 bigint,c2 bigint);
 create table t3(c1 bigint,c2 bigint);
-- 设置超时时间
set ob_query_timeout = 1000000000;
-- 在 t1 表内生成 1000W 条随机数据
insert into t1 select uniform(1,10000000,RANDOM()),uniform(1,10000000,RANDOM()) from table(generator(10000000));
-- 使用普通 insert into 语句把 t1 表内数据插入到 t2 表内，查看执行所需时间
insert /*+ enable_parallel_dml parallel(8)*/ into t2 select * from t1;
-- 使用旁路导入 insert into 语句把 t1 表内数据插入到 t3 表内，查看执行所需时间
insert /*+ append enable_parallel_dml parallel(8)*/ into t3 select * from t1;

9、快速生成随机测试数据

OceanBase 功能测试、压力测试、PoC 等等场景下都会使用随机数据填充测试表，但是，在 OceanBase V4.2 之前的版本存在两类问题：

随机函数种类少，不支持数据分布控制，需要手写 UDF 或 PL 包。
多行数据生成时，需要用 CONNECT BY 或 CTE，它们不仅语法复杂，而且数据行数较多时存在性能问题。
OceanBase V4.2 版本起提供了全新的多行数据导入功能，彻底解决了上述痛点。它包含如下特性：

简洁易记的导数语法。
支持任意长度的随机字符串生成函数。
支持分布函数，轻松构造倾斜数据。

行数据生成
Table function 是一种在 SQL 语言中使用的函数，它能够返回一张数据表作为结果。与传统的 SQL 函数只能返回标量值不同，table function 可以返回多行、多列的数据集。新增 generator 函数，并允许在 table function 中调用它，最终返回 N 行数据。

select [随机函数] from table(generator(N));
-- 创建测试表 t1
create table t1 (c1 bigint primary key, c2 bigint);
--创建 sequence 对象
create sequence s1 cache 1000000 noorder;
-- 插入随机数
insert into t1 select s1.nextval, random() from table(generator(1000));
-- 查看所生成的随机数
select * from t1;

千万行级别的随机数据生成
推荐配合使用 OceanBase V4.1 推出旁路导入功能，以获得最高的性能。只需要添加append enable_parallel_dml parallel(8) hint 即可，此处使用了并行度 8

-- 设置 ob_query_timeout 的值
set ob_query_timeout = 1000000000;
-- 创建表 t1
drop table if exists t1;
create table t1 (c1 bigint, c2 varchar(10));
-- 生成千万行级别的随机数据
Insert /*+ append enable_parallel_dml parallel(8) */ into t1 select random(), randstr(10, random()) from table(generator(10000000));
-- 查看所生成的随机数
select count(*) from t1;

生成包含多个宏块的数据
为了测试包含多个宏块的场景，需要插入大量的数据。但是偶尔我们会发现，即使插入了大量行，OceanBase 凭借其强大的压缩能力，把这些数据都给压缩没了。即使插入了数十万行，还装不满一个宏块。 MySQL 模式下可以使用 randstr() 来生成足够长的随机串避免压缩后数据量过少。

-- 创建表 t1
create table t1 (c1 bigint, c2 varchar(10000));
-- 生成宏块数据
Insert into t1 select random(), randstr(1000, random()) from table(generator(1));
-- 查看所生成的随机数
select * from t1;

倾斜数据生成
可以让数据符合正态分布或 zipf 分布，这样就能构造出数据倾斜。

例如下面随机生成 20 行数据，zipf 分布可以让小数字出现的频率更高

-- 创建表 t1 
drop table if exists t1;
create table t1 (c1 bigint);
-- 生成倾斜数据的随机数据
Insert into t1 select zipf(1, 20, random()) from table(generator(20));
-- 查看所生成的随机数
select * from t1;

长短不一的字符串生成

-- 创建表 t1 
drop table if exists t1;
create table t1 (c1 bigint);
-- 生成长短不一的随机数据
Insert into t1 select randstr(1+zipf(1, 20, random()), random()) from table(generator(20));
-- 查看所生成的随机数
select * from t1;

批量插入单词
一些场景下，希望插入的字符串有一定规律，不要长得像乱码。

比如，插入的内容是字典里的单词。可以通过预先构造一个单词表解决这个问题：

-- 创建表 t1 ，t2
drop table if exists t1;
create table t1 (c1 int, c2 varchar(10));
 create table t2 (c1 varchar(10));
-- 构建单词表 t1
insert into t1 values (0, 'hello'), (1, 'world'), (2, 'movie');
-- 插入单词
insert /*+ parallel(3) enable_parallel_dml */ into t2 select b.c2 from table(generator(1000)) a, t1 b where b.c1 = random() % 3;
-- 查看所生成的随机数
select * from t2;

插入部分 NULL 值
在数据集中掺入 NULL 值，常能有效暴露一些潜在 bug。MySQL 模式中可以用 if 来实现在随机数中掺 NULL，Oracle 模式下，可以用 decode 来实现。下面的例子里，都以 10% 的概率生成 NULL 值：

 -- 创建表 t1
drop table if exists t1;
create table t1 (c1 varchar(100));
-- 生成随机数据
insert into t1 select  if(random(4) % 10 = 0, null, random(4)) from table(generator(10));
-- 查看所生成的随机数
select * from t1;

Mysqltest 中如何生成稳定的随机数据
Mysqltest 要求数据必须稳定，否则每次回归的结果都不一样。我们只需要传入一个常数种子（seed）到随机函数中就可以保证每次插入到表中的数据是一样的。所谓 seed 就是给 random() 函数传入一个任意的常量值，seed 相同，每次执行输出的结果都相同。例如下面的例子中，3 就是 seed。

-- 创建表 t1
drop table if exists t1;
create table t1 (c1 bigint);
-- 创建表 t2
drop table if exists t2;
create table t2 (c1 bigint);
-- 往 t1，t2 表内生成随机数据
Insert into t1 select random(3) from table(generator(10));
 Insert into t2 select random(3) from table(generator(10));
-- 查看 t1 表数据
select * from t1;
-- 查看 t2 表数据
select * from t2;