Mycat2 執(zhí)行計(jì)劃管理與注釋

Mycat2的執(zhí)行計(jì)劃管理可以實(shí)現(xiàn)以下功能(v1.18以后)

1. 通過緩存多個(gè)執(zhí)行計(jì)劃,使用SQL參數(shù)計(jì)算它們的執(zhí)行代價(jià),從中挑選執(zhí)行代價(jià)最小的執(zhí)行計(jì)劃
2. 帶有注釋的SQL與不帶注釋的SQL進(jìn)行綁定,使得處理不帶注釋的SQL的時(shí)候使用帶注釋SQL的執(zhí)行計(jì)劃

添加執(zhí)行計(jì)劃

BASELINE ADD select * from db1.travelrecord n join db1.company s on n.id = s.id and n.id = 1

BASELINE_ID STATUS
572448969439842322  OK

作用:執(zhí)行計(jì)劃管理添加baseline以及優(yōu)化器得出的plan

此時(shí)我們使用explain語句查看執(zhí)行計(jì)劃

EXPLAIN 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.travelrecord]], conditions=[=($0, CAST(?0):BIGINT NOT NULL)])
  MycatView(distribution=[[db1.company]], conditions=[=($0, CAST(?0):BIGINT NOT NULL)])
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` = ?))

它得出使用HashJoin實(shí)現(xiàn)

如果我們想使用MERGE_JOIN實(shí)現(xiàn)JOIN則怎么辦?

BASELINE ADD /*+MYCAT:use_merge_join(n,s) */select * from db1.travelrecord n join db1.company s on n.id = s.id and n.id = 1

BASELINE_ID STATUS
572448969439842322  OK

可以留意到此處的BASELINE_ID與上面的BASELINE_ID是相同的,說明它們的baseline sql以及SQL約束是相同的.

查看內(nèi)存中的PLAN信息

BASELINE LIST;

BASELINE_ID PARAMETERIZED_SQL   PLAN_ID EXTERNALIZED_PLAN   FIXED   ACCEPTED
572448969439842322  select * from db1.travelrecord n join db1.company s on n.id = s.id and n.id = ? 572809558016135252  MycatHashJoin(condition=[=($0, $6)], joinType=[inner])
  MycatView(distribution=[[db1.travelrecord]], conditions=[=($0, CAST(?0):BIGINT NOT NULL)])
  MycatView(distribution=[[db1.company]], conditions=[=($0, CAST(?0):BIGINT NOT NULL)])
    false   true
572448969439842322  /*+MYCAT:use_merge_join(n,s) */ select * from db1.travelrecord n join db1.company s on n.id = s.id and n.id = ? 572810594013417563  MycatSortMergeJoin(condition=[=($0, $6)], joinType=[inner])
  MycatMergeSort(sort0=[$0], dir0=[ASC])
    MycatView(distribution=[[db1.travelrecord]], conditions=[=($0, CAST(?0):BIGINT NOT NULL)])
  MycatMergeSort(sort0=[$0], dir0=[ASC])
    MycatView(distribution=[[db1.company]], conditions=[=($0, CAST(?0):BIGINT NOT NULL)])
    true    true

可以看到內(nèi)存中存在兩個(gè)執(zhí)行計(jì)劃

此時(shí)我們?cè)俅螌?duì)不帶hint的SQL進(jìn)行explain查看執(zhí)行計(jì)劃

EXPLAIN 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]], conditions=[=($0, CAST(?0):BIGINT NOT NULL)])
  MycatMergeSort(sort0=[$0], dir0=[ASC])
    MycatView(distribution=[[db1.company]], conditions=[=($0, CAST(?0):BIGINT NOT NULL)])
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`)

此時(shí)我們看到它已經(jīng)選擇了MERGE_JOIN實(shí)現(xiàn).但是現(xiàn)在執(zhí)行計(jì)劃是有可能根據(jù)參數(shù)id變化的(使用代價(jià)分析挑選).

如果我們想把SQL永遠(yuǎn)與MERGE_JOIN實(shí)現(xiàn)的執(zhí)行計(jì)劃進(jìn)行一對(duì)一綁定,怎么辦?

BASELINE FIX /*+MYCAT:use_merge_join(n,s) */SELECT * FROM db1.travelrecord n JOIN db1.company s ON n.id = s.id AND n.id = 1

BASELINE_ID STATUS
572448969439842322  OK

此時(shí)已經(jīng)在內(nèi)存中進(jìn)行綁定,對(duì)于SQL

SELECT * FROM db1.travelrecord n JOIN db1.company s ON n.id = s.id AND n.id = ?

永遠(yuǎn)都會(huì)選擇MERGE_JOIN實(shí)現(xiàn)

此時(shí)還不足夠,我們需要持久化該執(zhí)行計(jì)劃的綁定關(guān)系,不這樣做,Mycat重啟后將會(huì)丟失這個(gè)執(zhí)行計(jì)劃

BASELINE PERSIST 572448969439842322

這樣就可以保存整個(gè)baseline,mycat2在啟動(dòng)的時(shí)候會(huì)自動(dòng)加載該baseline以及它的執(zhí)行計(jì)劃.