Mycat2 注釋

2021-09-22 20:17 更新

注釋的解析語法

query:  [ '/*+' hint [, hint]* '*/' ] select
select:
      SELECT   [ '/*+' hint [, hint]* '*/' ]  [ ALL | DISTINCT ]
          { * | projectItem [, projectItem ]* }
      FROM tableExpression
      [ WHERE booleanExpression ]
      [ GROUP BY { groupItem [, groupItem ]* } ]
      [ HAVING booleanExpression ]
selectWithoutFrom:
      SELECT [ ALL | DISTINCT ]
          { * | projectItem [, projectItem ]* }
projectItem:
      expression [ [ AS ] columnAlias ]
  |   tableAlias . *
tableExpression:
      tableReference [, tableReference ]*
  |   tableExpression [ NATURAL ] [ ( LEFT | RIGHT | FULL ) [ OUTER ] ] JOIN tableExpression [ joinCondition ]
  |   tableExpression CROSS JOIN tableExpression
  |   tableExpression [ CROSS | OUTER ] APPLY tableExpression
joinCondition:
      ON booleanExpression
  |   USING '(' column [, column ]* ')'
|tableReference:
      tablePrimary
      [ [ AS ] alias [ '(' columnAlias [, columnAlias ]* ')' ] ]
|tablePrimary:
     | [ [ catalogName . ] schemaName . ] tableName
     | '(' TABLE [ [ catalogName . ] schemaName . ] tableName ')'
     tablePrimary [ '/*+' hint [, hint]* '*/' ]
|values:
      VALUES expression [, expression ]*
groupItem:
      expression
  |   '(' ')'
  |   '(' expression [, expression ]* ')'

  
SELECT UNION [ALL | DISTINCT] SELECT ...

特殊地,SQL頭部可以編寫注釋

可以留意到[ '/*+' hint [, hint]* '*/' ]在select關(guān)鍵字之后,tablePrimary(即表名)之后

在mycat2里,建議在SQL頭部寫注釋,其他語法單元寫注釋更多是為了輔助分析3

hint: 'MYCAT' ':'  HINT_TEXT

HINT_TEXT是一段文本,沒有固定格式

這里的HINT語法并非指注釋只能在上述的語法單元中編寫,而是說,Mycat2在這些語法單元捕獲這些注釋,進(jìn)行處理,不在這些語法單元的注釋會(huì)被忽略

HINT的處理組件

Mycat2支持注釋控制Mycat2對(duì)SQL的處理過程,在分庫分表型數(shù)據(jù)庫中,注釋的生效的地方有三處地方

  1. SQL優(yōu)化器
  2. 執(zhí)行器
  3. 存儲(chǔ)節(jié)點(diǎn)即MySQL

本文描述第一種注釋,它控制SQL編譯器生成執(zhí)行器

注釋應(yīng)用對(duì)象的定位

注釋在作用范圍上習(xí)慣上分為兩大類

  1. 全局的,
  2. 針對(duì)某個(gè)節(jié)點(diǎn)的(可能結(jié)合父子節(jié)點(diǎn)判斷).

定位的方法

  1. 路徑法,根據(jù)遍歷順序或者名稱路徑找到該節(jié)點(diǎn)
  2. 唯一名稱法,在SQL中標(biāo)記唯一的名稱,在遍歷時(shí)候找到帶有該名稱的節(jié)點(diǎn).

于是有下面的具體的定位方法.

QB_NAME

參考MySQL的優(yōu)化器注釋Optimizer Hints,Mycat2也使用QB_NAME給表與查詢塊(Select語法元素)建立名稱.注釋命令通過該名稱定位生效的作用域.

格式

QB_NAME(name)

name為符合MySQL詞法的標(biāo)識(shí)符

表注釋

SELECT * FROM db1.travelrecord /*+MYCAT:QB_NAME(SEL$1)*/


SELECT * FROM db1.travelrecord /*+MYCAT:QB_NAME(SEL$2)*/ 
union all
SELECT * FROM db1.travelrecord /*+MYCAT:QB_NAME(SEL$4)*/ 


SELECT * FROM db1.travelrecord t where t.id in (SELECT id FROM db1.user /*+MYCAT:QB_NAME(SEL$4)*/  where user_id = 1)

查詢塊注釋

SELECT /*+MYCAT:QB_NAME(SEL$1)*/ * FROM db1.travelrecord 


SELECT /*+MYCAT:QB_NAME(SEL$1)*/  * FROM db1.travelrecord
union all
SELECT /*+MYCAT:QB_NAME(SEL$3)*/  * FROM db1.travelrecord /*+MYCAT:QB_NAME(SEL$4)*/ 


SELECT /*+MYCAT:QB_NAME(SEL$1)*/ * FROM db1.travelrecord t where t.id in (SELECT id FROM db1.user /*+MYCAT:QB_NAME(SEL$4)*/  where user_id = 1)

自動(dòng)生成的QB_NAME注釋 Mycat2的SQL編譯器在處理語法樹的時(shí)候會(huì)在Select語法單元或表語法單元自動(dòng)添加QB_NAME注釋,它們生成的參數(shù)是以SEL$為前綴,以1為開始值,Select語法單元或表語法單元出現(xiàn)的順序,當(dāng)表有別名的時(shí)候,則忽略該值,使用別名作為QB_NAME的參數(shù).

//以該無注釋的SQL為例
SELECT * FROM db1.travelrecord
union all
SELECT * FROM db1.travelrecord


//自動(dòng)生成QB_NAME
SELECT /*+MYCAT:QB_NAME(SEL$1)*/  * FROM db1.travelrecord /*+MYCAT:QB_NAME(SEL$2)*/
union all
SELECT /*+MYCAT:QB_NAME(SEL$3)*/  * FROM db1.travelrecord /*+MYCAT:QB_NAME(SEL$4)*/ 


//當(dāng)有別名的時(shí)候
SELECT /*+MYCAT:QB_NAME(SEL$1)*/ * FROM db1.travelrecord t /*+MYCAT:QB_NAME(t)*/ where t.id in (SELECT /*+MYCAT:QB_NAME(SEL$3)*/ id FROM db1.user /*+MYCAT:QB_NAME(SEL$4)*/  where user_id = 1)

無需QB_NAME 上一段描述了QB_NAME的作用,實(shí)際上是為了定位注釋的參數(shù)是關(guān)系表達(dá)式中哪個(gè)對(duì)象.實(shí)際上QB_NAME不是一定需要的,比如

SELECT /*+MYCAT:USE_HASH_JOIN(travelrecord,user)* FROM db1.travelrecord t where t.id in (SELECT id FROM db1.user where user_id = 1)

這個(gè)情況下,我們只需探測join節(jié)點(diǎn)下的兩個(gè)表是否為travelrecord和user即可定位該JOIN節(jié)點(diǎn)就是應(yīng)用USE_HASH_JOIN注釋的對(duì)象.

SQL編譯器對(duì)執(zhí)行優(yōu)化器注釋(物理表達(dá)式注釋)的處理

SQL通過參數(shù)化處理得到帶有HINT的參數(shù)化SQL與不帶HINT的參數(shù)化SQL,前者用于生成執(zhí)行計(jì)劃,后者用在執(zhí)行計(jì)劃管理,起SQL模板的作用,滿足該SQL模板的SQL集合都可以使用該執(zhí)行計(jì)劃,

首先,帶有HINT的參數(shù)化SQL經(jīng)過SQL編譯器編譯得到邏輯關(guān)系表達(dá)式,并把SQL語法節(jié)點(diǎn)上的HINT綁定到邏輯關(guān)系表達(dá)式上.

其次,根據(jù)SQL的詞法作用域?qū)int進(jìn)行傳播,從父節(jié)點(diǎn)往子節(jié)點(diǎn)開始傳播,下節(jié)點(diǎn)會(huì)得到上節(jié)點(diǎn)的hint,并執(zhí)行hint的校驗(yàn)函數(shù),判斷節(jié)點(diǎn)(關(guān)系表達(dá)式)是否適用該節(jié)點(diǎn).這樣,我們就得到了帶有Hint的邏輯關(guān)系表達(dá)式.

然后,SQL優(yōu)化器會(huì)對(duì)邏輯關(guān)系表達(dá)式進(jìn)行優(yōu)化,最終轉(zhuǎn)換成物理關(guān)系表達(dá)式.其中過程非常復(fù)雜,最常見的就是關(guān)系表達(dá)式之間的上拉下推.所以Hint也有可能跟隨這些邏輯關(guān)系表達(dá)式進(jìn)行在關(guān)系表達(dá)式樹的位置中發(fā)生變化或者因?yàn)轭~外添加或者刪除關(guān)系表達(dá)式導(dǎo)致hint無法生效.所以一般來說,hint作用于關(guān)鍵節(jié)點(diǎn),它不能影響語義,無法生效也不足為奇.

在分庫分表數(shù)據(jù)庫中,因?yàn)橐肓薞iew關(guān)系算子,它內(nèi)部仍然是邏輯關(guān)系表達(dá)式,它的父節(jié)點(diǎn)是可以是邏輯關(guān)系表達(dá)式也可以是物理關(guān)系表達(dá)式.已經(jīng)內(nèi)部的關(guān)系表達(dá)式不視為代價(jià)優(yōu)化器可以直接優(yōu)化的部分,它們是根據(jù)Mycat2的RBO規(guī)則生成的,雖然它們?nèi)匀粎⑴c代價(jià)計(jì)算.

但是考慮到Hint的作用域,考慮以下這一種情況.已經(jīng)成為View內(nèi)部的表節(jié)點(diǎn)帶有的QB_NAME,它們?nèi)匀灰鼙煌獠康腍ashJoin的Hint處理器檢查到,即Hint的處理過程是跨越View的外部和內(nèi)部的.

物理表達(dá)式注釋

Join物理算子注釋

該注釋支持QB_NAME(別名)定位參數(shù)也支持表名直接指定,注釋不生效不會(huì)報(bào)錯(cuò)

格式

use_xxx_join(QB_NAME,QB_NAME)

第一個(gè)QB_NAME是代表左表的關(guān)系算子,第二個(gè)QB_NAME是代表右表的關(guān)系算子

use_nl_join(QB_NAME,QB_NAME)(1.18-2021-4-29后)

/*+MYCAT:use_nl_join(n,s) */select * from db1.travelrecord n join db1.company s on n.id = s.id and n.id = 1
plan
MycatNestedLoopJoin(condition=[=($0, $6)], joinType=[inner])
  MycatView(distribution=[[db1.travelrecord]])
  MycatMatierial
    MycatView(distribution=[[db1.company]])
Each(targetName=c0, sql=SELECT * FROM db1_0.travelrecord_0 WHERE (`id` = ?) union all SELECT * FROM db1_0.travelrecord_1 WHERE (`id` = ?))
Each(targetName=c1, sql=SELECT * FROM db1_1.travelrecord_0 WHERE (`id` = ?) union all SELECT * FROM db1_1.travelrecord_1 WHERE (`id` = ?))
Each(targetName=prototype, sql=SELECT * FROM db1.company WHERE (`id` = ?))
...

use_hash_join(QB_NAME,QB_NAME)(1.18-2021-4-29后)

/*+MYCAT:use_hash_join(n,s) */select * from db1.travelrecord n join db1.company s on n.id = s.id and n.id = 1
plan
MycatHashJoin(condition=[=($0, $6)], joinType=[inner])
  MycatView(distribution=[[db1.company]])
Each(targetName=c0, sql=SELECT * FROM db1_0.travelrecord_0 WHERE (`id` = ?) union all SELECT * FROM db1_0.travelrecord_1 WHERE (`id` = ?))
Each(targetName=c1, sql=SELECT * FROM db1_1.travelrecord_0 WHERE (`id` = ?) union all SELECT * FROM db1_1.travelrecord_1 WHERE (`id` = ?))
Each(targetName=prototype, sql=SELECT * FROM db1.company WHERE (`id` = ?))
...

use_merge_join(QB_NAME,QB_NAME)(1.18-2021-4-29后)

/*+MYCAT:use_merge_join(n,s) */select * from db1.travelrecord n join db1.company s on n.id = s.id and n.id = 1
plan
MycatSortMergeJoin(condition=[=($0, $6)], joinType=[inner])
  MycatMergeSort(sort0=[$0], dir0=[ASC])
    MycatView(distribution=[[db1.travelrecord]])
  MycatMergeSort(sort0=[$0], dir0=[ASC])
    MycatView(distribution=[[db1.company]])
Each(targetName=c0, sql=SELECT * FROM db1_0.travelrecord_0 WHERE (`id` = ?) ORDER BY (`id` IS NULL), `id`)
Each(targetName=c0, sql=SELECT * FROM db1_0.travelrecord_1 WHERE (`id` = ?) ORDER BY (`id` IS NULL), `id`)
Each(targetName=c1, sql=SELECT * FROM db1_1.travelrecord_0 WHERE (`id` = ?) ORDER BY (`id` IS NULL), `id`)
Each(targetName=c1, sql=SELECT * FROM db1_1.travelrecord_1 WHERE (`id` = ?) ORDER BY (`id` IS NULL), `id`)
Each(targetName=prototype, sql=SELECT * FROM db1.company WHERE (`id` = ?) ORDER BY (`id` IS NULL), `id`)
...

no_hash_join(QB_NAME,QB_NAME)(1.18-2021-4-29后)

禁用生成HashJoin

以上內(nèi)容是否對(duì)您有幫助：
在線筆記
App下載
App下載

掃描二維碼

下載編程獅App

公眾號(hào)
微信公眾號(hào)

編程獅公眾號(hào)