Oceanbase SQL调优

加入收藏设为首页

SQL Life: Oceanbase SQL调优
时间：2021/6/11 17:27:41 作者：枫叶来源：solgle.com 查看：540 评论：0
内容摘要：允许每个分支使用不同的连接算法来加速查询，避免使用笛卡尔连接示例：Q1 会被改写成 Q2 的形式。对于 Q1 来说，它的连接方式只能是 nested loop join (笛卡尔乘积)，但是被改写之后，每个子查询都可以选择 nested loop join，hash join ...
允许每个分支使用不同的连接算法来加速查询，避免使用笛卡尔连接

示例：Q1 会被改写成 Q2 的形式。

对于 Q1 来说，它的连接方式只能是 nested loop join (笛卡尔乘积)，但是被改写之后，每个子查询都可以选择 nested loop join，hash join 或者 merge join，这样会有更多的优化空间。

Q1: select * from t1, t2 where t1.a = t2.a or t1.b = t2.b;

Q2: select * from t1, t2 where t1.a = t2.a

union all

select * from t1, t2 where t1.b = t2.b and lnnvl(t1.a = t2.a

允许每个分支分别消除排序，更加快速的获取top-k结果

示例：Q1 会被改写成 Q2。

对于 Q1 来说，执行方式是只能把满足条件的行数找出来，然后进行排序，最终取top-10 结果。对于

Q2 来说，如果存在索引(a,b), 那么 Q2 中的两个子查询都可以使用索引把排序消除，每个子查询取

top-10 结果，然后最终对这20行数据排序一下取出最终的 top-10 行。

Q1: select * from t1 where t1.a = 1 or t1.a = 2 order by b limit 10;

Q2: select * from

(select * from t1 where t1.a = 1 order by b limit 10

union all

select * from t1 where t1.a = 2 order by b limit 10)

as temp

order by temp.b limit 10;

执行计划

数据库在接收到 SQL 查询时，必须为其生成一个“执行计划”。OceanBase 的执行计划与本质上是由物理操作符构成的一棵执行树。

执行树从形状上可以分为“左深树”、“右深树”和“多枝树”三种（参见下图）。 OceanBase 的优化器在生成连接顺序时主要考虑左深树的连接形式。

通过Explain命令查看优化器针对给定SQL生成的逻辑执行计划。Explain不会真正执行给定的SQL，可以放心使用该功能而不用担心在性能调试中可能给系统性能带来影响。

一次完整的语法解析、语义分析、查询改写、查询优化、代码生成的SQL编译流程称为一次“硬解析”，硬

解析生成执行计划的过程比较耗时（一般为毫秒级），这对于OLTP语句来说是很难接受的。

OceanBase通过计划缓存（Plan Cache）来避免SQL硬解析

执行计划缓存的淘汰

自动淘汰优先淘汰最久没被使用的执行计划，

手动淘汰

alter system flush plan cache; 命令

有些计划缓存暂不支持的场景：

(1)内存大小超过 20 M 执行计划不加入计划缓存。

(2)如果该计划为分布式计划且涉及多个表，则不加入计划缓存。

计划缓存中的执行计划因各种原因失效时，会将计划缓存中失效的计划依据流量驱动地刷新（可能会导致新的计划）：

1. SQL中涉及的表的SCHEMA进行变更时（比如添加索引，删除或增加列等），该SQL在计划缓存中对应的执行计划将被刷新；

2. SQL中涉及的表的统计信息被更新时，该SQL对应的执行计划会被刷新，由于OceanBase在合并时统一进行统计信息的收集，因此每次合并之后，计划缓存中所有的计划将被刷新；

3. SQL进行outline计划绑定变更时，该SQL对应的执行计划会被刷新，更新为按绑定的outline生成的执行计划。

执行计划缓存的设置使用控制

系统变量控制

ob_enable_plan_cache设置为ture时表示SQL请求可以使用计划缓存，设置为false时表示SQL请求不使用计划缓存。可进行session级和global级设置，默认设置为true。

Hint控制

/*+use_plan_cache(none)*/ , 该hint表示请求不使用计划缓存

/*+use_plan_cache(default)*/, 该hint表示使用计划缓存

SQL性能问题来源

1. 用户SQL写法 - 遵循开发规约

2. 代价模型缺陷 - 绑定执行计划

3. 统计信息不准确 - 仅支持本地存储，合并时更新

4. 数据库物理设计 - 决定查询性能

5. 系统负载 - 影响整体吞吐率，影响单sql rt

6. 客户端路由 - 远程执行

SQL调优方法

针对单条 SQL 执行的性能调优

单表访问场景

索引、排序或聚合、分区、分布式并行

多表访问场景

连接顺序、连接算法、分布式并行、查询改写

针对吞吐量的性能优化

优化慢 SQL

均衡 SQL 的流量资源

局部索引与全局索引的取舍

1. 如果查询条件里“包含完整的分区键”，使用本地索引是最高效的。

2. 如果需要“不包含完整分区键”的唯一约束，

用全局索引

或者本地索引，且需要索引列上必须带上表的分区键

3. 其它情况，case by case：

通常来说，全局索引能为高频且精准命中的查询（比如单记录查询）提速并减少IO；对范围查询则不一定哪种索引效果更好。

不能忽视全局索引在DML语句中引入的额外开销：数据更新时带来的跨机分布式事务，事务的数据量越大则分布式事务越复杂。

4. 如果数据量较大，或者容易出现索引热点，可考虑创建全局分区索引

SQL执行性能监控

(g)v$sql_audit 是全局 SQL 审计表，可以用来查看每次请求客户端来源，执行 server 信息，执行状态信息，等待事件及执行各阶段耗时等。

设置 sql_audit 使用开关。

alter system set enable_sql_audit = true/false;

设置 sql_audit 内存上限。默认内存上限为3G，可设置范围为 [64M,+∞]。

alter system set sql_audit_memory_limit = '3G';

(g)v$sql_audit看什么

1. retry 次数是否很多(RETRY_CNT字段）, 如果次数很多，则可能有锁冲突或切主等情况

2. queue time 的值是否过大(QUEUE_TIME 字段)，很高表明CPU资源不够用

3. 获取执行计划时间(GET_PLAN_TIME), 如果时间很长，一般会伴随 IS_HIT_PLAN ＝ 0, 表示没有命中plan cache

4. 查看 EXECUTE_TIME 值，如果值过大，则：

1) 查看是否有很长等待事件耗时

2) 分析逻辑读次数是否异常多(突然有大账户时可能会出现）

SQL Trace 能够交互式的提供上一次执行的 SQL 请求执行过程信息及各阶段的耗时。

SQL Trace 功能默认时关闭的，可通过 session 变量来控制其关闭和打开。

SQL Trace 开关：

set ob_enable_trace_log = 0/1;

Show Trace

当 SQL Trace 功能打开后，执行需要诊断的 SQL，然后通过 show trace 能够查看该 SQL 执行的信息。

标签：Oceanbase SQL调优

上一篇：Oceanbase分布式事务技术
下一篇：Oceanbase的转储与合并

相关文章

相关评论

本类更新

本类推荐

本类排行

: 国家数据 | 中证指数 | Oracle官方文档 | 沪深行情 | 流量统计 | 深交所 | 上交所 | 中国证券登记 | 同业拆放利率 | 巨潮资讯 | 指数估值 | 看财报 |

联系我们 - 关于我们 - 网站同盟