教程直播第7期｜如何对 OceanBase 进行 SQL 诊断和调优

教程直播第7期｜如何对 OceanBase 进行 SQL 诊断和调优

目前，数据库是绝大多数应用系统储存数据的主要工具。当用户系统需要访问数据库时，需要使用 SQL 把应用的指令告诉数据库。因此 SQL 是应用与数据库系统“沟通”的重要手段，SQL 性能的好坏将直接影响“沟通”的效率，进一步地会影响到系统的用户响应时间、系统吞吐量、IT 设置成本等。

那么什么是 SQL 诊断与调优？今天我们来告诉你。SQL 诊断就是通过一些技术手段来找出“沟通”效率不高的原因或潜在影响“沟通”效率的因素，例如发现执行性能不佳的 SQL、可能存在性能瓶颈的 SQL 等等。而 SQL 调优则是通过一系列的技术手段，来提高 SQL 的执行效率，解决 SQL 的性能瓶颈，从而达到提高应用与数据库“沟通”效率的目的。

Oceanbase 社区版教程直播第七期，将为你带来“SQL 诊断与调优”更多干货信息。

 

01  为什么要进行 SQL 诊断与调优

很多人可能会说，我只要把每一条 SQL 都写好，保证 SQL 跑得快不就行了么，这样还省去了后面做 SQL 诊断和调优了。对于这个说法，我们只能说，对但不全对。

首先，写出一条“好”的 SQL 并不容易，它要求写 SQL 的人对数据库的原理和业务需求都有很深入的理解。而一般情况下，开发人员往往都是聚焦于业务需求，对数据库的理解相对不深，DBA 则难以保证对每一个业务都如数家珍。

其次，大型系统中 SQL 的数量往往非常庞大，业务的需求也在不断变化，很难有足够多的专业 DBA 与业务研发一起优化好每一条 SQL。

再次，即使是一条已经优化好的 SQL，随着数据量的变化或者数据库版本的升级也存在性能变差的可能，进而存在潜在的风险。

因此，无论是正在开发建设的应用系统，还是已经在线上稳定运行的应用系统，都需通过 SQL 诊断来识别风险，通过调优来解决性能瓶颈、保障系统稳定。

02  Oceanbase SQL 诊断的常用手段

在使用分布式数据库的时候，你是否遇到过诸如此类的烦恼：

痛点1：我的集群中有很多 server，怎么查看集群中的 SQL 请求流量是否均匀？

痛点2：数据库中跑着这么多 SQL，我要如何从中找到这几类 SQL：消耗 CPU 最多的 SQL，某段时间内请求次数排在TOP-N的SQL，或某段时间内平均 RT 排在 TOP-N 的SQL？

痛点3：集群正在稳定运行，但是 SQL 的 RT 突然抖动了，怎么找到引起抖动的 SQL？

针对以上三项痛点，其实，Oceanbase 的 [G]V$SQL_AUDIT 视图就可以解决你的烦恼。[G]V$SQL_AUDIT 是 SQL 诊断时最常用的一个视图，记录了每一次 SQL 请求的来源、执行状态及各种统计信息。使用这个视图我们可以查看任意一条 SQL 的计划类型、是否命中计划缓存、执行耗时、等待事件、是否存在重试等等。通过一些简单的 SQL ，我们便可以从这个视图中查出各种需要的信息。

下面我们举几个例子。

例一：想要查看集群中 SQL 请求流量是否均匀。

思路：我们首先可以查出某个时间段内数据库中所有 SQL 并按照 server 级别进行聚合，再统计该时间段内每台机器上的 QPS。

如下的 SQL 查出了前一秒各个 server 中的 SQL 请求数量。不难发现，SQL 请求集中在了192.168.35.138和192.168.14.0两台机器上，而192.168.35.111和 192.168.14.60两台机器上 SQL 请求的数量相对较少。

select t2.zone, t1.svr_ip,  count(*) as QPS
from oceanbase.gv$sql_audit t1, oceanbase.__all_server t2
where t1.svr_ip = t2.svr_ip and t1.tenant_id = 1001
and IS_EXECUTOR_RPC = 0 and request_time > (time_to_usec(now()) - 1000000)
and request_time < time_to_usec(now())
group by t1.svr_ip   order by QPS;
+---------------+----------------+------+
| zone          | svr_ip         | QPS  |
+---------------+----------------+------+
| cn-hangzhou-h | 192.168.14.60  |  705 |
| cn-hangzhou-i | 192.168.35.111 | 1485 |
| cn-hangzhou-h | 192.168.14.0   | 3119 |
| cn-hangzhou-i | 192.168.35.138 | 4959 |
+---------------+----------------+------+

例二：找到某个时间段请求次数排在 TOP-N 的 SQL。

我们可以查出某个时间段内数据库中的所有 SQL，按照 SQL_ID（每条 SQL 的唯一标识符）聚合，并统计每个 SQL_ID 的数量，其中数量排名前 N 的 SQL_ID 就是我们要找的 TOP-N SQL。如下的 SQL 查出了前一秒执行次数排前10 SQL的 SQL_ID、执行次数和执行时间。基于这个结果我们可以进一步分析这些 SQL 中是否存在需要进一步优化的 SQL。

select SQL_ID, count(*) as QPS, avg(t1.elapsed_time) RT
from oceanbase.gv$sql_audit t1
where   tenant_id = 1001       and IS_EXECUTOR_RPC = 0
and request_time > (time_to_usec(now()) - 10000000)
and request_time < time_to_usec(now())
group by t1.sql_id order by QPS desc limit 10;
+----------------------------------+------+------------+
| SQL_ID                           | QPS  | RT         |
+----------------------------------+------+------------+
| BF7AA13A28DF50BA5C33FF19F1DBD8A9 | 2523 |  4233.2085 |
| CE7208ADDE365D0AB5E68EE24E5FD730 | 1268 |  5935.8683 |
| E5C7494018989226E69AE7D08B3D0F15 | 1028 |  7275.7490 |
| D0E8D8C937E44BC3BB9A5379AE1064C5 | 1000 | 12999.1640 |
| 2D45D7BE4E459CFBEAE4803971F0C6F9 | 1000 |  8050.6360 |
| C81CE9AA555BE59B088B379CC7AE5B40 | 1000 |  6865.4940 |
| BDC4FE903B414203A04E41C7DDA6627D | 1000 | 12751.8960 |
| B1B136047D7C3B6B9125F095363A9D23 |  885 | 13293.2237 |
| 47993DD69888868E92A7CAB2FDE65380 |  880 |  7282.0557 |
| 05C6279D767C7F212619BF4B659D3BAB |  844 | 11474.5438 |
+----------------------------------+------+------------+

当然除了[G]V$SQL_AUDIT，Oceanbase 还提供了[G]V$PLAN_CACHE_PLAN_STAT、[G]V$PLAN_CACHE_PLAN_EXPLAIN 等视图用于 SQL 执行计划的诊断。合理地使用这些视图可以让 SQL 诊断事半功倍。

03  Oceanbase SQL 调优的常用手段

当我们发现某一条 SQL 存在性能问题时，我们可以通过很多方式对这条 SQL 进行优化，其中最常见的是索引调优。索引调优通过为数据表创建合适的索引来达到减少数据扫描量，消除排序等目的。索引调优是一种比较简单的调优方式，也是 SQL 出现性能问题时通常在第一时间考虑的优化方式。在单表扫描场景下创建一个合适的索引往往可以极大地提高 SQL 的执行性能。

在建索引前，我们需要考虑是否有必要建索引、应该在哪些列上建索引、索引列的顺序应该怎样安排。

在建索引时，一个最基础的策略是将存在等值条件的列放在索引的前面，将存在范围条件的列放在索引的后面，有多个列上存在范围条件时将过滤性强的列放在前面。例如一条 SQL 中存在三个过滤条件，分别是 a = 1、b > 0、c between 1 and 12。其中 b > 0 可以过滤掉30%的数据，c between 1 and 12 可以过滤掉90%的数据，那么按照我们的基础策略，对于这条 SQL 可以在 (a, c, b) 上建一个索引进行优化。当然这个基础策略也不是万能的，在实际优化时往往需要结合实际场景，具体问题具体分析。

除了索引调优外，还有连接调优、SQL 语句调优等多种调优手段，受于篇幅限制没法详细讲解，更多详细内容欢迎大家来收看1月25日 19:30 Oceanbase 社区版教程直播第七期：如何对 Oceanbase 进行 SQL 诊断和调优。