教程直播第 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 QPSfrom oceanbase.gv$sql_audit t1, oceanbase.__all_server t2where t1.svr_ip = t2.svr_ip and t1.tenant_id = 1001and 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) RTfrom oceanbase.gv$sql_audit t1where   tenant_id = 1001       and IS_EXECUTOR_RPC = 0and 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 诊断和调优。