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

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

添加執(zhí)行計劃

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í)行計劃管理添加baseline以及優(yōu)化器得出的plan

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

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實現(xiàn)

如果我們想使用MERGE_JOIN實現(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)存中存在兩個執(zhí)行計劃

此時我們再次對不帶hint的SQL進(jìn)行explain查看執(zhí)行計劃

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`)

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

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

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

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

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

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

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

BASELINE PERSIST 572448969439842322

這樣就可以保存整個baseline,mycat2在啟動的時候會自動加載該baseline以及它的執(zhí)行計劃.