建站
咨詢
售后
建站咨詢
新聞中心
基于規(guī)則的查詢改寫方式主要包括子查詢相關(guān)改寫、外聯(lián)接消除、 簡化條件改寫和 非 SPJ（SELECT PROJECT JOIN）的改寫等。
成都創(chuàng)新互聯(lián)于2013年成立，是專業(yè)互聯(lián)網(wǎng)技術(shù)服務(wù)公司，擁有項目成都網(wǎng)站建設(shè)、網(wǎng)站設(shè)計網(wǎng)站策劃，項目實施與項目整合能力。我們以讓每一個夢想脫穎而出為使命，1280元印臺做網(wǎng)站,已為上家服務(wù),為印臺各地企業(yè)和個人服務(wù),聯(lián)系電話:028-86922220
子查詢相關(guān)改寫
優(yōu)化器對于子查詢一般使用嵌套執(zhí)行的方式，也就是父查詢每生成一行數(shù)據(jù)后，都需要執(zhí)行一次子查詢。使用這種方式需要多次執(zhí)行子查詢，執(zhí)行效率很低。對于子查詢的優(yōu)化方式，一般會將其改寫為聯(lián)接操作，可大大提高執(zhí)行效率，主要優(yōu)點如下：
-
可避免子查詢多次執(zhí)行。
-
優(yōu)化器可根據(jù)統(tǒng)計信息選擇更優(yōu)的聯(lián)接順序和聯(lián)接方法。
-
子查詢的聯(lián)接條件、過濾條件改寫為父查詢的條件后，優(yōu)化器可以進(jìn)行進(jìn)一步優(yōu)化，比如條件下壓等。
子查詢改寫的方式主要包括視圖合并、子查詢展開和將 ANY/ALL 使用 MAX/MIN 改寫等。
視圖合并
視圖合并是指將代表一個視圖的子查詢合并到包含該視圖的查詢中，視圖合并后，有助于優(yōu)化器增加聯(lián)接順序的選擇、訪問路徑的選擇以及進(jìn)一步做其他改寫操作，從而選擇更優(yōu)的執(zhí)行計劃。
OceanBase 數(shù)據(jù)庫支持對 SPJ 視圖進(jìn)行合并。如下示例為 Q1 改寫為 Q2：
obclient>CREATE TABLE t1 (c1 INT, c2 INT);
Query OK, 0 rows affected (0.00 sec)
obclient>CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT);
Query OK, 0 rows affected (0.00 sec)
obclient>CREATE TABLE t3 (c1 INT PRIMARY KEY, c2 INT);
Query OK, 0 rows affected (0.00 sec)
Q1:
obclient>SELECT t1.c1, v.c1
FROM t1, (SELECT t2.c1, t3.c2
FROM t2, t3
WHERE t2.c1 = t3.c1) v
WHERE t1.c2 = v.c2;
<==>
Q2:
obclient>SELECT t1.c1, t2.c1
FROM t1, t2, t3
WHERE t2.c1 = t3.c1 AND t1.c2 = t3.c2;如果 Q1 不進(jìn)行改寫，則其聯(lián)接順序有以下幾種：
-
t1, v(t2,t3)
-
t1, v(t3,t2)
-
v(t2,t3), t1
-
v(t3,t2), t1
進(jìn)行視圖合并改寫后，可選擇的聯(lián)接順序有：
-
t1, t2, t3
-
t1, t3, t2
-
t2, t1, t3
-
t2, t3, t1
-
t3, t1, t2
-
t3, t2, t1
可以看出，進(jìn)行視圖合并后，聯(lián)接順序可選擇空間增加。對于復(fù)雜查詢，視圖合并后，對路徑的選擇和可改寫的空間均會增大，從而使得優(yōu)化器可生成更優(yōu)的計劃。
子查詢展開
子查詢展開是指將 WHERE 條件中子查詢提升到父查詢中，并作為聯(lián)接條件與父查詢并列進(jìn)行展開。轉(zhuǎn)換后子查詢將不存在，外層父查詢中會變成多表聯(lián)接。
這樣改寫的好處是優(yōu)化器在進(jìn)行路徑選擇、聯(lián)接方法和聯(lián)接排序時都會考慮到子查詢中的表，從而可以獲得更優(yōu)的執(zhí)行計劃。涉及的子查詢表達(dá)式一般有 NOT IN、IN、NOT EXIST、EXIST、ANY、ALL。
子查詢展開的方式如下：
-
改寫條件使生成的聯(lián)接語句能夠返回與原始語句相同的行。
-
展開為半聯(lián)接（SEMI JOIN / ANTI JOIN）
如下例所示，t2.c2 不具有唯一性，改為 SEMI JOIN，該語句改寫后執(zhí)行計劃為：
obclient>CREATE TABLE t1 (c1 INT, c2 INT); Query OK, 0 rows affected (0.17 sec) obclient>CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT); Query OK, 0 rows affected (0.01 sec) obclient>EXPLAIN SELECT * FROM t1 WHERE t1.c1 IN (SELECT t2.c2 FROM t2)\G; *************************** 1. row *************************** Query Plan: ======================================= |ID|OPERATOR |NAME|EST. ROWS|COST| --------------------------------------- |0 |HASH SEMI JOIN| |495 |3931| |1 | TABLE SCAN |t1 |1000 |499 | |2 | TABLE SCAN |t2 |1000 |433 | ======================================= Outputs & filters: ------------------------------------- 0 - output([t1.c1], [t1.c2]), filter(nil), equal_conds([t1.c1 = t2.c2]), other_conds(nil) 1 - output([t1.c1], [t1.c2]), filter(nil), access([t1.c1], [t1.c2]), partitions(p0) 2 - output([t2.c2]), filter(nil), access([t2.c2]), partitions(p0)將查詢前面操作符改為 NOT IN 后，可改寫為 ANTI JOIN，具體計劃如下例所示：
obclient>EXPLAIN SELECT * FROM t1 WHERE t1.c1 NOT IN (SELECT t2.c2 FROM t2)\G; *************************** 1. row *************************** Query Plan: ================================================ |ID|OPERATOR |NAME|EST. ROWS|COST | ------------------------------------------------ |0 |NESTED-LOOP ANTI JOIN| |0 |520245| |1 | TABLE SCAN |t1 |1000 |499 | |2 | TABLE SCAN |t2 |22 |517 | ================================================ Outputs & filters: ------------------------------------- 0 - output([t1.c1], [t1.c2]), filter(nil), conds(nil), nl_params_([t1.c1], [(T_OP_IS, t1.c1, NULL, 0)]) 1 - output([t1.c1], [t1.c2], [(T_OP_IS, t1.c1, NULL, 0)]), filter(nil), access([t1.c1], [t1.c2]), partitions(p0) 2 - output([t2.c2]), filter([(T_OP_OR, ? = t2.c2, ?, (T_OP_IS, t2.c2, NULL, 0))]), access([t2.c2]), partitions(p0)
-
子查詢展開為內(nèi)聯(lián)接
上面示例的 Q1 中如果將 t2.c2 改為 t2.c1，由于 t2.c1 為主鍵，子查詢輸出具有唯一性，此時可以直接轉(zhuǎn)換為內(nèi)聯(lián)接，如下例所示：
Q1: obclient>SELECT * FROM t1 WHERE t1.c1 IN (SELECT t2.c1 FROM t2)\G; <==> Q2: obclient>SELECT t1.* FROM t1, t2 WHERE t1.c1 = t2.c1;Q1 改寫后的計劃如下例所示：
obclient>EXPLAIN SELECT * FROM t1 WHERE t1.c1 IN (SELECT t2.c1 FROM t2)\G; *************************** 1. row *************************** Query Plan: ==================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ------------------------------------ |0 |HASH JOIN | |1980 |3725| |1 | TABLE SCAN|t2 |1000 |411 | |2 | TABLE SCAN|t1 |1000 |499 | ==================================== Outputs & filters: ------------------------------------- 0 - output([t1.c1], [t1.c2]), filter(nil), equal_conds([t1.c1 = t2.c1]), other_conds(nil) 1 - output([t2.c1]), filter(nil), access([t2.c1]), partitions(p0) 2 - output([t1.c1], [t1.c2]), filter(nil), access([t1.c1], [t1.c2]), partitions(p0)對于 NOT IN、IN、NOT EXIST、EXIST、ANY、ALL 子查詢表達(dá)式都可以對應(yīng)做類似的改寫操作。
ANY/ALL 使用 MAX/MIN 改寫
對于 ANY/ALL 的子查詢，如果子查詢中沒有 GROUP BY 子句、聚集函數(shù)以及 HAVING 時，以下表達(dá)式可以使用聚集函數(shù) MIN/MAX 進(jìn)行等價轉(zhuǎn)換，其中 col_item 為單獨列且有非 NULL 屬性：
val > ALL(SELECT col_item ...) <==> val > ALL(SELECT MAX(col_item) ...);
val >= ALL(SELECT col_item ...) <==> val >= ALL(SELECT MAX(col_item) ...);
val < ALL(SELECT col_item ...) <==> val < ALL(SELECT MIN(col_item) ...);
val <= ALL(SELECT col_item ...) <==> val <= ALL(SELECT MIN(col_item) ...);
val > ANY(SELECT col_item ...) <==> val > ANY(SELECT MIN(col_item) ...);
val >= ANY(SELECT col_item ...) <==> val >= ANY(SELECT MIN(col_item) ...);
val < ANY(SELECT col_item ...) <==> val < ANY(SELECT MAX(col_item) ...);
val <= ANY(SELECT col_item ...) <==> val <= ANY(SELECT MAX(col_item) ...);將子查詢更改為含有 MAX/MIN 的子查詢后，再結(jié)合使用 MAX/MIN 的改寫，可減少改寫前對內(nèi)表的多次掃描，如下例所示：
obclient>SELECT c1 FROM t1 WHERE c1 > ANY(SELECT c1 FROM t2);
<==>
obclient>SELECT c1 FROM t1 WHERE c1 > ANY(SELECT MIN(c1) FROM t2);結(jié)合 MAX/MIN 的改寫后，可利用 t2.c1 的主鍵序?qū)?LIMIT 1 直接下壓到 TABLE SCAN，將 MIN 值輸出，執(zhí)行計劃如下：
obclient>EXPLAIN SELECT c1 FROM t1 WHERE c1 > ANY(SELECT c1 FROM t2)\G;
*************************** 1. row ***************************
Query Plan:
===================================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
---------------------------------------------------
|0 |SUBPLAN FILTER | |1 |73 |
|1 | TABLE SCAN |t1 |1 |37 |
|2 | SCALAR GROUP BY| |1 |37 |
|3 | SUBPLAN SCAN |subquery_table|1 |37 |
|4 | TABLE SCAN |t2 |1 |36 |
===================================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1]), filter([t1.c1 > ANY(subquery(1))]),
exec_params_(nil), onetime_exprs_(nil), init_plan_idxs_([1])
1 - output([t1.c1]), filter(nil),
access([t1.c1]), partitions(p0)
2 - output([T_FUN_MIN(subquery_table.c1)]), filter(nil),
group(nil), agg_func([T_FUN_MIN(subquery_table.c1)])
3 - output([subquery_table.c1]), filter(nil),
access([subquery_table.c1])
4 - output([t2.c1]), filter(nil),
access([t2.c1]), partitions(p0),
limit(1), offset(nil)外聯(lián)接消除
外聯(lián)接操作可分為左外聯(lián)接、右外聯(lián)接和全外聯(lián)接。在聯(lián)接過程中，由于外聯(lián)接左右順序不能變換，優(yōu)化器對聯(lián)接順序的選擇會受到限制。外聯(lián)接消除是指將外聯(lián)接轉(zhuǎn)換成內(nèi)聯(lián)接，從而可以提供更多可選擇的聯(lián)接路徑，供優(yōu)化器考慮。
如果進(jìn)行外聯(lián)接消除，需要存在“空值拒絕條件”，即在 WHERE 條件中存在，當(dāng)內(nèi)表生成的值為 NULL 時，輸出為 FALSE 的條件。
如下例所示：
obclient>SELECT t1.c1, t2.c2 FROM t1 LEFT JOIN t2 ON t1.c2 = t2.c2;這是一個外聯(lián)接，在其輸出行中 t2.c2 可能為 NULL。如果加上一個條件 t2.c2 > 5，則通過該條件過濾后，t2.c1 輸出不可能為 NULL， 從而可以將外聯(lián)接轉(zhuǎn)換為內(nèi)聯(lián)接。
obclient>SELECT t1.c1, t2.c2 FROM t1 LEFT JOIN t2 ON t1.c2 = t2.c2 WHERE t2.c2 > 5;
<==>
obclient>SELECT t1.c1, t2.c2 FROM t1 LEFT INNER JOIN t2 ON t1.c2 = t2.c2
WHERE t2.c2 > 5;簡化條件改寫
HAVING 條件消除
如果查詢中沒有聚集操作及 GROUP BY，則 HAVING 可以合并到 WHERE 條件中，并將 HAVING 條件刪除， 從而可以將 HAVING 條件在 WHERE 條件中統(tǒng)一管理，并進(jìn)行進(jìn)一步相關(guān)優(yōu)化。
obclient>SELECT * FROM t1, t2 WHERE t1.c1 = t2.c1 HAVING t1.c2 > 1;
<==>
obclient>SELECT * FROM t1, t2 WHERE t1.c1 = t2.c1 AND t1.c2 > 1;改寫后計劃如下例所示, t1.c2 > 1 條件被下壓到了 TABLE SCAN 層。
obclient>EXPLAIN SELECT * FROM t1, t2 WHERE t1.c1 = t2.c1 HAVING t1.c2 > 1\G;
*************************** 1. row ***************************
Query Plan:
=========================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
-----------------------------------------
|0 |NESTED-LOOP JOIN| |1 |59 |
|1 | TABLE SCAN |t1 |1 |37 |
|2 | TABLE GET |t2 |1 |36 |
=========================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil),
conds(nil), nl_params_([t1.c1])
1 - output([t1.c1], [t1.c2]), filter([t1.c2 > 1]),
access([t1.c1], [t1.c2]), partitions(p0)
2 - output([t2.c1], [t2.c2]), filter(nil),
access([t2.c1], [t2.c2]), partitions(p0)等價關(guān)系推導(dǎo)
等價關(guān)系推導(dǎo)是指利用比較操作符的傳遞性，推倒出新的條件表達(dá)式，從而減少需要處理的行數(shù)或者選擇到更有效的索引。
OceanBase 數(shù)據(jù)庫可對等值聯(lián)接進(jìn)行推導(dǎo)，比如 a ＝ b AND a > 1 可以推導(dǎo)出 a ＝ b AND a > 1 AND b > 1， 如果 b 上有索引，且 b > 1 在該索引選擇率很低，則可以大大提升訪問 b 列所在表的性能。
如下例所示，條件 t1.c1 = t2.c2 AND t1.c1 > 2，等價推導(dǎo)后為 t1.c1 = t2.c2 AND t1.c1 > 2 AND t2.c2 > 2，從計劃中可以看到 t2.c2 已下壓到 TABLE SCAN，并且使用 t2.c2 對應(yīng)的索引。
obclient>CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT);
Query OK, 0 rows affected (0.15 sec)
obclient>CREATE TABLE t2(c1 INT PRIMARY KEY, c2 INT, c3 INT, KEY IDX_c2(c2));
Query OK, 0 rows affected (0.10 sec)
/*此命令需運行于 MySQL 模式下*/
obclient>EXPLAIN EXTENDED_NOADDR SELECT t1.c1, t2.c2 FROM t1, t2
WHERE t1.c1 = t2.c2 AND t1.c1 > 2\G;
*************************** 1. row ***************************
Query Plan:
==========================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
------------------------------------------
|0 |MERGE JOIN | |5 |78 |
|1 | TABLE SCAN|t2(IDX_c2)|5 |37 |
|2 | TABLE SCAN|t1 |3 |37 |
==========================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1], [t2.c2]), filter(nil),
equal_conds([t1.c1 = t2.c2]), other_conds(nil)
1 - output([t2.c2]), filter(nil),
access([t2.c2]), partitions(p0),
is_index_back=false,
range_key([t2.c2], [t2.c1]), range(2,MAX ; MAX,MAX),
range_cond([t2.c2 > 2])
2 - output([t1.c1]), filter(nil),
access([t1.c1]), partitions(p0),
is_index_back=false,
range_key([t1.c1]), range(2 ; MAX),
range_cond([t1.c1 > 2])恒真/假消除
對于如下恒真恒假條件可以進(jìn)行消除：
-
false and expr ＝ 恒 false
-
true or expr = 恒 true
如下例所示，對于 WHERE 0 > 1 AND c1 ＝ 3，由于 0 > 1 使得 AND 恒假， 所以該 SQL 不用執(zhí)行，可直接返回，從而加快查詢的執(zhí)行。
obclient>EXPLAIN EXTENDED_NOADDR SELECT * FROM t1 WHERE 0 > 1 AND c1 = 3\G;
*************************** 1. row ***************************
Query Plan:
===================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|t1 |0 |38 |
===================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1], [t1.c2]), filter([0], [t1.c1 = 3]), startup_filter([0]),
access([t1.c1], [t1.c2]), partitions(p0),
is_index_back=false, filter_before_indexback[false,false],
range_key([t1.__pk_increment], [t1.__pk_cluster_id], [t1.__pk_partition_id]),
range(MAX,MAX,MAX ; MIN,MIN,MIN)always false非 SPJ 的改寫
冗余排序消除
冗余排序消除是指刪除 order item 中不需要的項，減少排序開銷。以下三種情況可進(jìn)行排序消除：
-
ORDER BY 表達(dá)式列表中有重復(fù)列，可進(jìn)行去重后排序。
obclient>SELECT * FROM t1 WHERE c2 = 5 ORDER BY c1, c1, c2, c3 ; <==> obclient>SELECT * FROM t1 WHERE c2 = 5 ORDER BY c1, c2, c3;
-
ORDER BY 列中存在 where 中有單值條件的列，該列排序可刪除。
obclient>SELECT * FROM t1 WHERE c2 = 5 ORDER BY c1, c2, c3; <==> obclient>SELECT * FROM t1 WHERE c2 = 5 ORDER BY c1, c3;
-
如果本層查詢有 ORDER BY 但是沒有 LIMIT，且本層查詢位于父查詢的集合操作中，則 ORDER BY 可消除。因為對兩個有序的集合做 UNION 操作，其結(jié)果是亂序的。但是如果 ORDER BY 中有 LIMIT，則語義是取最大/最小的 N 個，此時不能消除 ORDER BY，否則有語義錯誤。
obclient>(SELECT c1,c2 FROM t1 ORDER BY c1) UNION (SELECT c3,c4 FROM t2 ORDER BY c3)； <==> obclient>(SELECT c1,c2 FROM t1) UNION (SELECT c3,c4 FROM t2)；
LIMIT 下壓
LIMIT 下壓改寫是指將 LIMIT 下降到子查詢中，OceanBase 數(shù)據(jù)庫現(xiàn)在支持在不改變語義的情況下，將 LIMIT 下壓到視圖（示例 1）及 UNION 對應(yīng)子查詢（示例 2）中。
示例 1：
obclient>SELECT * FROM (SELECT * FROM t1 ORDER BY c1) a LIMIT 1;
<==>
obclient>SELECT * FROM (SELECT * FROM t1 ORDER BY c1 LIMIT 1) a LIMIT 1;示例 2：
obclient>(SELECT c1,c2 FROM t1) UNION ALL (SELECT c3,c4 FROM t2) LIMIT 5;
<==>
obclient>(SELECT c1,c2 FROM t1 LIMIT 5) UNION ALL (SELECT c3,c4 FROM t2 limit 5) LIMIT 5;DISTINCT 消除
-
如果 select item 中只包含常量，則可以消除 DISTINCT，并加上 LIMIT 1。
obclient>SELECT DISTINCT 1,2 FROM t1 ; <==> obclient>SELECT DISTINCT 1,2 FROM t1 LIMIT 1; obclient>CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT); Query OK, 0 rows affected (0.17 sec) obclient>EXPLAIN EXTENDED_NOADDR SELECT DISTINCT 1,2 FROM t1\G; *************************** 1. row *************************** Query Plan: =================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ----------------------------------- |0 |TABLE SCAN|t1 |1 |36 | =================================== Outputs & filters: ------------------------------------- 0 - output([1], [2]), filter(nil), access([t1.c1]), partitions(p0), limit(1), offset(nil), is_index_back=false, range_key([t1.c1]), range(MIN ; MAX)always true
-
如果 select item 中包含確保唯一性約束的列，則 DISTINCT 能夠消除，如下示例中 (c1, c2)為主鍵，可確保 c1、c2 和 c3 唯一性， 從而 DISTINCT 可消除。
obclient>CREATE TABLE t2(c1 INT, c2 INT, c3 INT, PRIMARY KEY(c1, c2)); Query OK, 0 rows affected (0.17 sec) obclient>SELECT DISTINCT c1, c2, c3 FROM t2; <==> obclient>SELECT c1, c2 c3 FROM t2; obclient>EXPLAIN SELECT DISTINCT c1, c2, c3 FROM t2\G; *************************** 1. row *************************** Query Plan: =================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ----------------------------------- |0 |TABLE SCAN|t2 |1000 |455 | =================================== Outputs & filters: ------------------------------------- 0 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), access([t2.c1], [t2.c2], [t2.c3]), partitions(p0)
MIN/MAX 改寫
-
當(dāng) MIN/MAX 函數(shù)中參數(shù)為索引前綴列，且不含 GROUP BY 時，可將該 scalar aggregate 轉(zhuǎn)換為走索引掃描 1 行的情況，如下例所示：
obclient>CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, c3 INT, KEY IDX_c2_c3(c2,c3)); Query OK, 0 rows affected (0.17 sec) obclient>SELECT MIN(c2) FROM t1; <==> obclient>SELECT MIN(c2) FROM (SELECT c2 FROM t2 ORDER BY c2 LIMIT 1) AS t; obclient>EXPLAIN SELECT MIN(c2) FROM t1\G; *************************** 1. row *************************** Query Plan: ================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| -------------------------------------------------- |0 |SCALAR GROUP BY| |1 |37 | |1 | SUBPLAN SCAN |subquery_table|1 |37 | |2 | TABLE SCAN |t1(idx_c2_c3) |1 |36 | ================================================== Outputs & filters: ------------------------------------- 0 - output([T_FUN_MIN(subquery_table.c2)]), filter(nil), group(nil), agg_func([T_FUN_MIN(subquery_table.c2)]) 1 - output([subquery_table.c2]), filter(nil), access([subquery_table.c2]) 2 - output([t1.c2]), filter([(T_OP_IS_NOT, t1.c2, NULL, 0)]), access([t1.c2]), partitions(p0), limit(1), offset(nil)
-
如果
SELECT MIN/MAX的參數(shù)為常量，而且包含 GROUP BY，可以將 MIN/MAX 改為常量，從而減少 MIN/MAX 的計算開銷。obclient>SELECT MAX(1) FROM t1 GROUP BY c1; <==> obclient>SELECT 1 FROM t1 GROUP BY c1; obclient>EXPLAIN EXTENDED_NOADDR SELECT MAX(1) FROM t1 GROUP BY c1\G; *************************** 1. row *************************** Query Plan: =================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ----------------------------------- |0 |TABLE SCAN|t1 |1000 |411 | =================================== Outputs & filters: ------------------------------------- 0 - output([1]), filter(nil), access([t1.c1]), partitions(p0), is_index_back=false, range_key([t1.c1]), range(MIN ; MAX)always true
-
如果
SELECT MIN/MAX的參數(shù)為常量，而且不含 GROUP BY，可以按照如下示例進(jìn)行改寫，從而走索引只需掃描 1 行。obclient>SELECT MAX(1) FROM t1; <==> obclient>SELECT MAX(t.a) FROM (SELECT 1 AS a FROM t1 LIMIT 1) t; obclient>EXPLAIN EXTENDED_NOADDR SELECT MAX(1) FROM t1\G; *************************** 1. row *************************** Query Plan: ================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| -------------------------------------------------- |0 |SCALAR GROUP BY| |1 |37 | |1 | SUBPLAN SCAN |subquery_table|1 |37 | |2 | TABLE SCAN |t1 |1 |36 | ================================================== Outputs & filters: ------------------------------------- 0 - output([T_FUN_MAX(subquery_table.subquery_col_alias)]), filter(nil), group(nil), agg_func([T_FUN_MAX(subquery_table.subquery_col_alias)]) 1 - output([subquery_table.subquery_col_alias]), filter(nil), access([subquery_table.subquery_col_alias]) 2 - output([1]), filter(nil), access([t1.c1]), partitions(p0), limit(1), offset(nil), is_index_back=false, range_key([t1.c1]), range(MIN ; MAX)always true
分享標(biāo)題：創(chuàng)新互聯(lián)OceanBase教程：OceanBase基于規(guī)則的查詢改寫
分享地址：http://m.fisionsoft.com.cn/article/dpdihdh.html
