av激情亚洲男人的天堂国语,日韩欧美精品一中文字幕,无码av一区二区三区无码,国产又色又爽又刺激的a片,国产又色又爽又刺激的a片

創(chuàng)新互聯(lián)OceanBase教程：OceanBase基于代價(jià)的查詢改寫(xiě)

OceanBase 數(shù)據(jù)庫(kù)目前只支持一種基于代價(jià)的查詢改寫(xiě)——或展開(kāi)（OR-EXPANSION）。

創(chuàng)新互聯(lián)公司主要從事成都做網(wǎng)站、網(wǎng)站制作、網(wǎng)頁(yè)設(shè)計(jì)、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)黃埔,10余年網(wǎng)站建設(shè)經(jīng)驗(yàn),價(jià)格優(yōu)惠、服務(wù)專業(yè),歡迎來(lái)電咨詢建站服務(wù):18982081108

數(shù)據(jù)庫(kù)中很多高級(jí)的改寫(xiě)規(guī)則（例如 complex view merge 和窗口函數(shù)改寫(xiě)）都需要基于代價(jià)進(jìn)行改寫(xiě)，OceanBase 數(shù)據(jù)庫(kù)后續(xù)版本會(huì)支持這些復(fù)雜的改寫(xiě)規(guī)則。

或展開(kāi)（OR-EXPANSION）

OR-EXPANSION 是將一個(gè)查詢改寫(xiě)成若干個(gè)用 UNION 組成的子查詢，可以為每個(gè)子查詢提供更優(yōu)的優(yōu)化空間，但是也會(huì)導(dǎo)致多個(gè)子查詢的執(zhí)行，所以這個(gè)改寫(xiě)需要基于代價(jià)去判斷。

OR-EXPANSION 的改寫(xiě)主要有如下三個(gè)作用:

  • 允許每個(gè)分支使用不同的索引來(lái)加速查詢。

    如下例所示，Q1 會(huì)被改寫(xiě)成 Q2 的形式，其中 Q2 中的謂詞 LNNVL(t1.a = 1) 保證了這兩個(gè)子查詢不會(huì)生成重復(fù)的結(jié)果。如果不進(jìn)行改寫(xiě)，Q1 一般來(lái)說(shuō)會(huì)選擇主表作為訪問(wèn)路徑，對(duì)于 Q2 來(lái)說(shuō)，如果 t1 上存在索引（a）和索引（b），那么該改寫(xiě)可能會(huì)讓 Q2 中的每一個(gè)子查詢選擇索引作為訪問(wèn)路徑。

    Q1: 
obclient>SELECT * FROM t1 WHERE t1.a = 1 OR t1.b = 1;
Q2: 
obclient>SELECT * FROM t1 WHERE t1.a = 1 UNION ALL SELECT * FROM t1.b = 1 
       AND LNNVL(t1.a = 1);

    完整示例如下：

    obclient>CREATE TABLE t1(a INT, b INT, c INT, d INT, e INT, INDEX IDX_a(a), 
         INDEX IDX_b(b));
Query OK, 0 rows affected (0.17 sec)

/*如果不進(jìn)行 OR-EXPANSION 的改寫(xiě)，該查詢只能使用主表訪問(wèn)路徑*/
obclient> EXPLAIN SELECT/*+NO_REWRITE()*/ * FROM t1 WHERE t1.a = 1 OR t1.b = 1;
+--------------------------------------------------------------+
| Query Plan                                                                         |
+--------------------------------------------------------------+
| ===================================
|ID|OPERATOR  |NAME|EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|t1  |4        |649 |
===================================

Outputs & filters:
-------------------------------------
  0 - output([t1.a], [t1.b], [t1.c], [t1.d], [t1.e]), filter([t1.a = 1 OR t1.b = 1]),
      access([t1.a], [t1.b], [t1.c], [t1.d], [t1.e]), partitions(p0)

/*改寫(xiě)之后，每個(gè)子查詢能使用不同的索引訪問(wèn)路徑*/
obclient>EXPLAIN SELECT * FROM t1 WHERE t1.a = 1 OR t1.b = 1;
+------------------------------------------------------------------------+
| Query Plan                                                                                         |
+------------------------------------------------------------------------+
| =========================================
|ID|OPERATOR   |NAME     |EST. ROWS|COST|
-----------------------------------------
|0 |UNION ALL  |         |3        |190 |
|1 | TABLE SCAN|t1(idx_a)|2        |94  |
|2 | TABLE SCAN|t1(idx_b)|1        |95  |
=========================================

Outputs & filters:
-------------------------------------
  0 - output([UNION(t1.a, t1.a)], [UNION(t1.b, t1.b)], [UNION(t1.c, t1.c)], [UNION(t1.d, t1.d)], [UNION(t1.e, t1.e)]), filter(nil)
  1 - output([t1.a], [t1.b], [t1.c], [t1.d], [t1.e]), filter(nil),
      access([t1.a], [t1.b], [t1.c], [t1.d], [t1.e]), partitions(p0)
  2 - output([t1.a], [t1.b], [t1.c], [t1.d], [t1.e]), filter([lnnvl(t1.a = 1)]),
      access([t1.a], [t1.b], [t1.c], [t1.d], [t1.e]), partitions(p02

  • 允許每個(gè)分支使用不同的連接算法來(lái)加速查詢，避免使用笛卡爾聯(lián)接。

    如下例所示，Q1 會(huì)被改寫(xiě)成 Q2 的形式。對(duì)于 Q1 來(lái)說(shuō)，它的聯(lián)接方式只能是 NESTED LOOP JOIN (笛卡爾乘積)， 但是被改寫(xiě)之后，每個(gè)子查詢都可以選擇 NESTED LOOP JOIN、HASH JOIN 或者 MERGE JOIN，這樣會(huì)有更多的優(yōu)化空間。

    Q1: 
obclient>SELECT * FROM t1, t2 WHERE t1.a = t2.a OR t1.b = t2.b;

Q2: 
obclient>SELECT * FROM t1, t2 WHERE t1.a = t2.a UNION ALL
     SELECT * FROM t1, t2 WHERE t1.b = t2.b AND LNNVL(t1.a = t2.a);

    完整示例如下：

    obclient> CREATE TABLE t1(a INT, b INT);
Query OK, 0 rows affected (0.17 sec)

obclient> CREATE TABLE t2(a INT, b INT);
Query OK, 0 rows affected (0.13 sec)

/*如果不進(jìn)行改寫(xiě)，只能使用 NESTED LOOP JOIN*/
obclient> EXPLAIN SELECT/*+NO_REWRITE()*/ * FROM t1, t2 
       WHERE t1.a = t2.a OR t1.b = t2.b;
+--------------------------------------------------------------------------+
| Query Plan                                                                                          |
+--------------------------------------------------------------------------+
| ===========================================
|ID|OPERATOR        |NAME|EST. ROWS|COST  |
-------------------------------------------
|0 |NESTED-LOOP JOIN|    |3957     |585457|
|1 | TABLE SCAN     |t1  |1000     |499   |
|2 | TABLE SCAN     |t2  |4        |583   |
===========================================

Outputs & filters:
-------------------------------------
  0 - output([t1.a], [t1.b], [t2.a], [t2.b]), filter(nil),
      conds(nil), nl_params_([t1.a], [t1.b])
  1 - output([t1.a], [t1.b]), filter(nil),
      access([t1.a], [t1.b]), partitions(p0)
  2 - output([t2.a], [t2.b]), filter([? = t2.a OR ? = t2.b]),
      access([t2.a], [t2.b]), partitions(p0)

/*被改寫(xiě)之后，每個(gè)子查詢都使用了 HASH JOIN*/
obclient>  EXPLAIN SELECT * FROM t1, t2 WHERE t1.a = t2.a OR t1.b = t2.b;
+--------------------------------------------------------------------------+
| Query Plan                                                                                         |
+--------------------------------------------------------------------------+
|ID|OPERATOR    |NAME|EST. ROWS|COST|
-------------------------------------
|0 |UNION ALL   |    |2970     |9105|
|1 | HASH JOIN  |    |1980     |3997|
|2 |  TABLE SCAN|t1  |1000     |499 |
|3 |  TABLE SCAN|t2  |1000     |499 |
|4 | HASH JOIN  |    |990      |3659|
|5 |  TABLE SCAN|t1  |1000     |499 |
|6 |  TABLE SCAN|t2  |1000     |499 |
=====================================

Outputs & filters:
-------------------------------------
  0 - output([UNION(t1.a, t1.a)], [UNION(t1.b, t1.b)], [UNION(t2.a, t2.a)], [UNION(t2.b, t2.b)]), filter(nil)
  1 - output([t1.a], [t1.b], [t2.a], [t2.b]), filter(nil),
      equal_conds([t1.a = t2.a]), other_conds(nil)
  2 - output([t1.a], [t1.b]), filter(nil),
      access([t1.a], [t1.b]), partitions(p0)
  3 - output([t2.a], [t2.b]), filter(nil),
      access([t2.a], [t2.b]), partitions(p0)
  4 - output([t1.a], [t1.b], [t2.a], [t2.b]), filter(nil),
      equal_conds([t1.b = t2.b]), other_conds([lnnvl(t1.a = t2.a)])
  5 - output([t1.a], [t1.b]), filter(nil),
      access([t1.a], [t1.b]), partitions(p0)
  6 - output([t2.a], [t2.b]), filter(nil),
      access([t2.a], [t2.b]), partitions(p0)

  • 允許每個(gè)分支分別消除排序，更加快速的獲取 TOP-K 結(jié)果。

    如下例所示，Q1 會(huì)被改寫(xiě)成 Q2。對(duì)于 Q1 來(lái)說(shuō)，執(zhí)行方式是只能把滿足條件的行數(shù)找出來(lái)，然后進(jìn)行排序，最終取 TOP-10 結(jié)果。對(duì)于 Q2 來(lái)說(shuō)，如果存在索引（a,b）, 那么 Q2 中的兩個(gè)子查詢都可以使用索引把排序消除，每個(gè)子查詢?nèi)?TOP-10 結(jié)果，然后最終對(duì)這 20 行數(shù)據(jù)排序一下取出最終的 TOP-10 行。

    Q1: 
obclient>SELECT * FROM t1 WHERE t1.a = 1 OR t1.a = 2 ORDER BY b LIMIT 10;

Q2: 
obclient>SELECT * FROM  
    (SELECT * FROM  t1 WHERE t1.a = 1 ORDER BY b LIMIT 10 UNION ALL
     SELECT * FROM  t1 WHERE t1.a = 2 ORDER BY b LIMIT 10) AS TEMP
    ORDER BY temp.b LIMIT 10;

    完整示例如下：

    obclient> CREATE TABLE t1(a INT, b INT, INDEX IDX_a(a, b));
Query OK, 0 rows affected (0.20 sec)

/*不改寫(xiě)的話，需要排序最終獲取 TOP-K 結(jié)果*/
obclient> EXPLAIN SELECT/*+NO_REWRITE()*/ * FROM t1 WHERE t1.a = 1 OR t1.a = 2 
        ORDER BY b LIMIT 10;
+-------------------------------------------------------------------------+
| Query Plan                                                                                         |
+-------------------------------------------------------------------------+
| ==========================================
|ID|OPERATOR    |NAME     |EST. ROWS|COST|
------------------------------------------
|0 |LIMIT       |         |4        |77  |
|1 | TOP-N SORT |         |4        |76  |
|2 |  TABLE SCAN|t1(idx_a)|4        |73  |
==========================================

Outputs & filters:
-------------------------------------
  0 - output([t1.a], [t1.b]), filter(nil), limit(10), offset(nil)
  1 - output([t1.a], [t1.b]), filter(nil), sort_keys([t1.b, ASC]), topn(10)
  2 - output([t1.a], [t1.b]), filter(nil),
      access([t1.a], [t1.b]), partitions(p0)

/* 進(jìn)行改寫(xiě)的話，排序算子可以被消除，最終獲取 TOP-K 結(jié)果*/
obclient>EXPLAIN SELECT * FROM t1 WHERE t1.a = 1 OR t1.a = 2 
        ORDER BY b LIMIT 10;
+-------------------------------------------------------------------------+
| Query Plan                                                                                          |
+-------------------------------------------------------------------------+
| ===========================================
|ID|OPERATOR     |NAME     |EST. ROWS|COST|
-------------------------------------------
|0 |LIMIT        |         |3        |76  |
|1 | TOP-N SORT  |         |3        |76  |
|2 |  UNION ALL  |         |3        |74  |
|3 |   TABLE SCAN|t1(idx_a)|2        |37  |
|4 |   TABLE SCAN|t1(idx_a)|1        |37  |
===========================================

Outputs & filters:
-------------------------------------
  0 - output([UNION(t1.a, t1.a)], [UNION(t1.b, t1.b)]), filter(nil), limit(10), offset(nil)
  1 - output([UNION(t1.a, t1.a)], [UNION(t1.b, t1.b)]), filter(nil), sort_keys([UNION(t1.b, t1.b), ASC]), topn(10)
  2 - output([UNION(t1.a, t1.a)], [UNION(t1.b, t1.b)]), filter(nil)
  3 - output([t1.a], [t1.b]), filter(nil),
      access([t1.a], [t1.b]), partitions(p0),
      limit(10), offset(nil)
  4 - output([t1.a], [t1.b]), filter([lnnvl(t1.a = 1)]),
      access([t1.a], [t1.b]), partitions(p0),
      limit(10), offset(nil)

分享題目：創(chuàng)新互聯(lián)OceanBase教程：OceanBase基于代價(jià)的查詢改寫(xiě)
當(dāng)前URL：http://uogjgqi.cn/article/cohieps.html
掃二維碼與項(xiàng)目經(jīng)理溝通

我們?cè)谖⑿派?4小時(shí)期待你的聲音

解答本文疑問(wèn)/技術(shù)咨詢/運(yùn)營(yíng)咨詢/技術(shù)建議/互聯(lián)網(wǎng)交流