摘要：智能把控大數(shù)據(jù)量查詢，防患系統(tǒng)奔潰于未然

什么是最大讀取行

一直以來，大數(shù)據(jù)量查詢是數(shù)據(jù)庫(kù) DBA 們調(diào)優(yōu)的重點(diǎn)，DBA 們通常十八般武藝輪番上陣以期提升大數(shù)據(jù)查詢的性能：例如分庫(kù)分表、給表增加索引、設(shè)定合理的 WHERE 查詢條件、限定單次查詢的條數(shù)……

然而，DBA 再厲害，應(yīng)用程序千千萬，寫代碼的程序員萬碼奔騰，大數(shù)據(jù)量的查詢像地雷，不定什么時(shí)候就爆了。比如隱藏在某段代碼里的查詢，因?yàn)橐粋€(gè)新手程序員的經(jīng)驗(yàn)不足，查詢代碼寫得欠佳，沒有 WHERE 子句或缺少索引引發(fā)了不必要的多行讀取，甚至全表掃描，給服務(wù)器帶來了過度的壓力，導(dǎo)致業(yè)務(wù)執(zhí)行緩慢，甚至最后服務(wù)器 OOM 崩潰。

為了避免這種“爆雷”，GaussDB(for MySQL)近期上線了最大讀取行特性。優(yōu)化器產(chǎn)生執(zhí)行計(jì)劃后，如果優(yōu)化器預(yù)估的讀取行數(shù)超過了所設(shè)置的最大讀取行閾值，則自動(dòng)中止查詢，將雷的導(dǎo)火索切斷。

這種機(jī)制的優(yōu)點(diǎn)在于：執(zhí)行計(jì)劃階段就對(duì)查詢進(jìn)行了干預(yù)，而不是語句開始執(zhí)行后在執(zhí)行過程中進(jìn)行中斷。既杜絕了劣質(zhì)查詢對(duì)服務(wù)器和業(yè)務(wù)運(yùn)行造成的風(fēng)險(xiǎn)，又大大節(jié)省了時(shí)間和資源。

如何設(shè)置最大讀取行

在 GaussDB(for MySQL)中，設(shè)置 rds_max_row_read，指定查詢?cè)试S讀取的最大行數(shù)。GaussDB(for MySQL)收到查詢指令，執(zhí)行查詢之前，會(huì)對(duì)查詢要讀取的行數(shù)進(jìn)行估計(jì)。當(dāng)估值超過所設(shè)置的最大讀取行時(shí)，將中止查詢，即查詢沒有機(jī)會(huì)運(yùn)行，提前規(guī)避不必要的資源消耗。

下面是一份測(cè)試數(shù)據(jù)，說明了開啟最大讀取行前后的差異。

假設(shè)表 t1 有 4M 大小的行，當(dāng)開發(fā)人員或應(yīng)用程序嘗試運(yùn)行以下查詢時(shí)，運(yùn)行需要 7 分鐘。

mysql> SELECT * FROM t1;

WHERE 子句的缺失致使需要全表掃描，查詢耗時(shí)長(zhǎng)。對(duì)于更大的表，這類查詢將需要更多的耗時(shí)，使服務(wù)器消耗更多資源，查詢耗時(shí)甚至可能高達(dá)數(shù)小時(shí)。

最大讀取行特性的使用，可以節(jié)省寶貴的時(shí)間和資源。比如假設(shè)將最大讀取行數(shù)指定為 1000000：

mysql> set rds_max_row_read =1000000;

Query OK, 0 rows affected (0.00 sec)

修改后，重新運(yùn)行不含 WHERE 子句的查詢，收到了讀取行超限的提示，查詢被停止。

mysql> SELECT * FROM t1;

ERROR HY000: Expected number of read rows exceeds the maximum allowed (see @@rds_max_row_read)

通過最大讀取行，相當(dāng)于擁有了一個(gè)工具，DBA 或者軟件工程師根據(jù)業(yè)務(wù)情況可以自如設(shè)置和調(diào)整限制規(guī)則，保證業(yè)務(wù)正常運(yùn)行的同時(shí)，限制次優(yōu)查詢，避免性能異常。

適用范圍

適用于 SELECT、CREATE SELECT 和 INSERT SELECT。

功能開啟

默認(rèn)情況下，該功能是禁用的，只有當(dāng) rds_max_row_read 設(shè)置了值時(shí)，該功能才會(huì)被激活。

為了功能的穩(wěn)定，避免無心的錯(cuò)誤設(shè)置對(duì)業(yè)務(wù)造成不必要的影響，rds_max_row_read 做了最低值限制，不允許用戶設(shè)置比最低值更低的值。

實(shí)現(xiàn)原理

GaussDB(for MySQL)通過遍歷每個(gè)查詢塊并聚合各查詢塊的貢獻(xiàn)來整體評(píng)估查詢的讀取行數(shù)：也就是對(duì)各 join 對(duì)象的讀取行數(shù)評(píng)估后累加。

如果在累加評(píng)估過程中的某一刻，估計(jì)值超過了所設(shè)置的限制，查詢將被終止。

對(duì)于關(guān)聯(lián)子查詢，評(píng)估辦法為：評(píng)估子查詢的讀取行數(shù)，然后乘以查詢被執(zhí)行的次數(shù)。

需要特別說明的是，對(duì)每個(gè) JOIN 對(duì)象的估計(jì)是執(zhí)行計(jì)劃預(yù)估返回的行數(shù)，可能與真實(shí)執(zhí)行返回的行數(shù)有偏差。這雖然是一個(gè)相對(duì)簡(jiǎn)單的評(píng)估模型，但是我們堅(jiān)信其具有足夠的魯棒性。

對(duì)于復(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í)行計(jì)劃中的這個(gè)評(píng)估有多準(zhǔn)確呢？

執(zhí)行如下查詢查看語句實(shí)際被執(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 顯示實(shí)際上的讀取是 17 行，而不是 54 行。

這個(gè) 17 是怎么來的呢？

這是一個(gè)哈希連接：

-遍歷整張表時(shí)，左表有 9 行數(shù)據(jù)+1 行額外行。

-右表有 6 行+1 行額外行。

優(yōu)化器中會(huì)預(yù)估返回讀取行，例如，54。在這個(gè)示例中，它并沒有很好地猜測(cè)到返回的行數(shù)，它高估了行讀取的數(shù)量。在大多數(shù)情況下，讀取行數(shù)的估計(jì)不夠精確，但可以肯定的是，它是足夠穩(wěn)健的，能達(dá)到相應(yīng)的目的。

示例 2

創(chuàng)建例表 t1：

mysql> CREATE TABLE t1(a INT);

在表中填充 1536 行數(shù)據(jù)后。將 rds_max_row_read 設(shè)置為 500，進(jìn)行以下測(cè)試查詢：

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)化器估計(jì)的讀取行數(shù)是 512 行，因此查詢被終止。如果在 a 字段上添加索引（這是一件明智的事情），同一查詢的估計(jì)讀取行數(shù)是 1，查詢檢測(cè)順利通過。

這個(gè)簡(jiǎn)單的示例說明：最大讀取行能幫助您編寫更加優(yōu)質(zhì)的查詢語句。

結(jié)論

最大讀取行特性針對(duì)讀取過多行的查詢，識(shí)別和過濾出效率低下的查詢。用戶可以為讀取行數(shù)設(shè)置閾值，超過該閾值則終止查詢。為了識(shí)別此類查詢，GaussDB(for MySQL)在優(yōu)化器中進(jìn)行了讀取總行數(shù)的粗略估計(jì)。當(dāng)查詢終止時(shí)，可以檢查 optimizer trace，從中收集線索，以幫助重寫更高效的查詢。

簡(jiǎn)而言之，最大讀取行為用戶提供了一個(gè)工具，使他們可以更充分地利用手上的資源。

審核編輯 黃宇