拒絕“爆雷”！GaussDB(for MySQL)新上線了這個功能
摘要：智能把控大數(shù)據(jù)量查詢，防患系統(tǒng)奔潰于未然
什么是最大讀取行
一直以來，大數(shù)據(jù)量查詢是數(shù)據(jù)庫DBA們調(diào)優(yōu)的重點，DBA們通常十八般武藝輪番上陣以期提升大數(shù)據(jù)查詢的性能：例如分庫分表、給表增加索引、設(shè)定合理的WHERE查詢條件、限定單次查詢的條數(shù)……
然而，DBA再厲害，應(yīng)用程序千千萬，寫代碼的程序員萬碼奔騰，大數(shù)據(jù)量的查詢像地雷，不定什么時候就爆了。比如隱藏在某段代碼里的查詢，因為一個新手程序員的經(jīng)驗不足，查詢代碼寫得欠佳，沒有WHERE子句或缺少索引引發(fā)了不必要的多行讀取，甚至全表掃描，給服務(wù)器帶來了過度的壓力，導(dǎo)致業(yè)務(wù)執(zhí)行緩慢，甚至最后服務(wù)器OOM崩潰。
為了避免這種“爆雷”，GaussDB(for MySQL)近期上線了最大讀取行特性。優(yōu)化器產(chǎn)生執(zhí)行計劃后，如果優(yōu)化器預(yù)估的讀取行數(shù)超過了所設(shè)置的最大讀取行閾值，則自動中止查詢，將雷的導(dǎo)火索切斷。
這種機制的優(yōu)點在于：執(zhí)行計劃階段就對查詢進行了干預(yù)，而不是語句開始執(zhí)行后在執(zhí)行過程中進行中斷。既杜絕了劣質(zhì)查詢對服務(wù)器和業(yè)務(wù)運行造成的風(fēng)險，又大大節(jié)省了時間和資源。
如何設(shè)置最大讀取行
在GaussDB(forMySQL)中，設(shè)置rds_max_row_read，指定查詢允許讀取的最大行數(shù)。GaussDB(for MySQL)收到查詢指令，執(zhí)行查詢之前，會對查詢要讀取的行數(shù)進行估計。當(dāng)估值超過所設(shè)置的最大讀取行時，將中止查詢，即查詢沒有機會運行，提前規(guī)避不必要的資源消耗。
下面是一份測試數(shù)據(jù)，說明了開啟最大讀取行前后的差異。
假設(shè)表t1有4M大小的行，當(dāng)開發(fā)人員或應(yīng)用程序嘗試運行以下查詢時，運行需要7分鐘。
mysql> SELECT * FROM t1;
WHERE子句的缺失致使需要全表掃描，查詢耗時長。對于更大的表，這類查詢將需要更多的耗時，使服務(wù)器消耗更多資源，查詢耗時甚至可能高達數(shù)小時。
最大讀取行特性的使用，可以節(jié)省寶貴的時間和資源。比如假設(shè)將最大讀取行數(shù)指定為1000000：
mysql> set rds_max_row_read =1000000;
Query OK, 0 rows affected (0.00 sec)
修改后，重新運行不含WHERE子句的查詢，收到了讀取行超限的提示，查詢被停止。
mysql> SELECT * FROM t1;
ERROR HY000: Expected number of read rows exceeds the maximum allowed (see @@rds_max_row_read)
通過最大讀取行，相當(dāng)于擁有了一個工具，DBA或者軟件工程師根據(jù)業(yè)務(wù)情況可以自如設(shè)置和調(diào)整限制規(guī)則，保證業(yè)務(wù)正常運行的同時，限制次優(yōu)查詢，避免性能異常。
適用范圍
適用于SELECT、CREATE SELECT和INSERT SELECT。
功能開啟
默認情況下，該功能是禁用的，只有當(dāng)rds_max_row_read設(shè)置了值時，該功能才會被激活。
為了功能的穩(wěn)定，避免無心的錯誤設(shè)置對業(yè)務(wù)造成不必要的影響，我們對rds_max_row_read做了最低值限制，不允許用戶設(shè)置比最低值更低的值。
實現(xiàn)原理
GaussDB(for MySQL)通過遍歷每個查詢塊并聚合各查詢塊的貢獻情況來整體評估查詢的讀取行數(shù)：也就是對各本質(zhì)上是通過估計每個join對象的讀取行數(shù)評估后累加后求和來實現(xiàn)的。
如果在累加評估過程中的某一刻，估計值超過了所設(shè)置的限制，查詢將被終止。
對于關(guān)聯(lián)子查詢，評估辦法為：評估子查詢的讀取行數(shù)，然后乘以查詢被執(zhí)行的次數(shù)。
需要特別說明的是，對每個JOIN對象的估計是執(zhí)行計劃預(yù)估返回的行數(shù)，可能與真實執(zhí)行返回的行數(shù)有偏差。這雖然是一個相對簡單的評預(yù)估模型，但是經(jīng)過測試我們堅相信其這個預(yù)估會被證明是具有足夠的魯棒性的。
對于復(fù)雜查詢，GaussDB(for MySQL)還通過optimizer trace提供了更多信息以幫助您確定優(yōu)化器做決策的原因及如何優(yōu)化查詢。
示例
示例1
mysql> EXPLAIN format=tree SELECT * FROM table_1, table_2;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (no condition) (cost=6.50 rows=54)
-> Table scan on table_1 (cost=0.19 rows=9)
-> Hash
-> Table scan on table_2 (cost=0.85 rows=6)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SET rds_max_row_read =20;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM table_1, table_2;
ERROR 1888 (HY000): The expected number of read rows exceeds the allowed maximum (see @@rds_max_row_read)
查詢讀取的行太多，。我們來嘗試在optimizer trace的幫助下尋找下原因：
SET optimizer_trace="enabled=on";
SELECT * from table_1, table_2;
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
在optimizer trace中，可以找到：
{
"Max_row_read": {
"select#": 1,
"current_estimate_of_rows": 54,
"rows_contributed_by_this_query_block": 54
}
}
這表示此查詢中的唯一查詢塊，行讀取數(shù)為54。
執(zhí)行計劃中的這個評估有多準確呢？
執(zhí)行如下查詢查看語句實際被執(zhí)行的次數(shù)：
mysql> show status like "handler_read_rnd_next";
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_read_rnd_next | 17 |
+----------------------------+-------+
1 rows in set (0.00 sec)
handler_read_rnd_next顯示實際上的讀取是17行，而不是54行。
這個17是怎么來的呢？
這是一個哈希連接：
-遍歷整張表時，左表有9行數(shù)據(jù)+1行額外行。
-右表有6行+1行額外行。
優(yōu)化器中用于聚合估計值的統(tǒng)計信息會預(yù)估返回預(yù)估讀取行的行數(shù)，例如，54。在這個示例中，它并沒有很好地猜測到返回的行數(shù)，它高估了行讀取的數(shù)量。可以肯定的是，在大多數(shù)情況下，讀取行數(shù)的估計不夠精確準確，但可以肯定的是，它是足夠穩(wěn)健的，能達到相應(yīng)的目的。
示例2
創(chuàng)建例表t1：
mysql> CREATE TABLE t1(a INT);
在表中填充1536行數(shù)據(jù)后。將rds_max_row_read設(shè)置為500，。進行以下測試查詢：
mysql> SELECT * FROM t1 WHERE a>6;
ERROR HY000: Expected number of read rows exceeds the maximum allowed (see @@rds_max_row_read)
在optimizer trac優(yōu)化器日志的幫助下，可以看到優(yōu)化器估計的查到估計的讀取行數(shù)是512行，因此查詢被終止。如果在a字段上添加索引（這是一件明智的事情）——從我們的測試看，這是一件明智的事情，同一查詢的估計讀取行數(shù)是1行，查詢檢測順利通過。
這個簡單的示例說明：此示例是一個最大讀取行能幫助您確保所編寫更加優(yōu)質(zhì)的查詢語句。寫的查詢更智能的非常簡單的示例。
結(jié)論
最大讀取行特性針對讀取過多行的查詢，識別和過濾出效率低下的查詢。用戶可以為讀取行數(shù)設(shè)置閾值，超過該閾值則終止查詢。為了識別此類查詢，GaussDB(for MySQL)在優(yōu)化器中進行了讀取總行數(shù)的粗略估計。當(dāng)查詢終止時，可以檢查optimizer trace，從中收集線索，以幫助重寫更高效的查詢。
簡而言之，最大讀取行為用戶提供了一個工具，使他們可以更充分地利用手上的資源。
