教程直播第7期|如何對 OceanBase 進行 SQL 診斷和調優

OceanBase資料庫發表於2022-01-24

目前,資料庫是絕大多數應用系統儲存資料的主要工具。當使用者系統需要訪問資料庫時,需要使用 SQL 把應用的指令告訴資料庫。因此 SQL 是應用與資料庫系統“溝通”的重要手段,SQL 效能的好壞將直接影響“溝通”的效率,進一步地會影響到系統的使用者響應時間、系統吞吐量、IT 設定成本等。

那麼什麼是 SQL 診斷與調優?今天我們來告訴你。SQL 診斷就是透過一些技術手段來找出“溝通”效率不高的原因或潛在影響“溝通”效率的因素,例如發現執行效能不佳的 SQL、可能存在效能瓶頸的 SQL 等等。而 SQL 調優則是透過一系列的技術手段,來提高 SQL 的執行效率,解決 SQL 的效能瓶頸,從而達到提高應用與資料庫“溝通”效率的目的。

OceanBase 社群版教程直播第七期,將為你帶來 “SQL 診斷與調優”更多幹貨資訊。

驚喜直播預告

《如何對 OceanBase 效能診斷和調優》


1月25日(週二)19:30,OceanBase 社群版教程直播將迎來 第七期:如何對 OceanBase 效能診斷和調優本次直播,OceanBase 高階開發工程師義博將一次性為你講透關於“效能診斷和調優”的幾大知識點。

教程直播第7期|如何對 OceanBase 進行 SQL 診斷和調優

● 一條 SQL 從應用到 OceanBase 中執行經過了哪些流程?同一類 SQL 反覆執行 OceanBase 又會怎樣重用執行計劃?

● 如何使用 OceanBase 中的各種檢視來做 SQL 診斷?

● 對於一條慢 SQL,我們可以從哪些方面來最佳化它的執行效能?

以上內容將幫助你解決以下痛點:

● 想要對執行在 OceanBase 上的 SQL 進行診斷,但 OceanBase 提供的各種檢視讓人眼花繚亂,無從下手。

● 發現一條 SQL 在 OceanBase 上執行很慢,想要最佳化 SQL 的執行效能,卻無處下手

學完本期教程直播,你將能輕鬆應對以下問題:

● 如何利用 OceanBase 提供的各種檢視診斷 SQL。

● 如何使用一些常見的 SQL 調優手段來最佳化 SQL 在 OceanBase 中的執行效能。

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 診斷和調優。

教程直播第7期|如何對 OceanBase 進行 SQL 診斷和調優

往期推薦:

開源實踐 | OceanBase 在紅象雲騰大資料場景下的實踐與思考

2021 OceanBase 年度報告 | 用技術讓海量資料的管理和使用更簡單!

2021 OceanBase 開源半年度報告 | 不忘初心,感恩同行

全面提升體系化資料管理能力 | OceanBase 釋出全新3.X工具家族

參與更多技術交流,請至 OceanBase 社群版 。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/69909943/viewspace-2853860/,如需轉載,請註明出處,否則將追究法律責任。

相關文章