教程直播第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 t2
where 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) 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 診斷和調優。