INSERT · k8s原理與實踐

[TOC] # INSERT INSERT 算子用于將指定的數據插入數據表，數據來源包括直接指定的值和子查詢的結果。 OceanBase 數據庫支持的 INSERT 算子包括 INSERT 和 MULTI PARTITION INSERT。 ## INSERT INSERT 算子用于向數據表的單個分區中插入數據。如下例所示，Q1 查詢將值 (1, '100') 插入到非分區表 t1 中。其中 1 號算子 EXPRESSION 用來生成常量表達式的值。 ~~~ obclient>CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 VARCHAR2(10)); Query OK, 0 rows affected (0.12 sec) obclient>CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 VARCHAR2(10)) PARTITION BY HASH(c1) PARTITIONS 10; Query OK, 0 rows affected (0.12 sec) obclient>CREATE TABLE t3 (c1 INT PRIMARY KEY, c2 VARCHAR2(10)); Query OK, 0 rows affected (0.12 sec) obclient>CREATE INDEX IDX_t3_c2 ON t3 (c2) PARTITION BY HASH(c2) PARTITIONS 3; Query OK, 0 rows affected (0.12 sec) Q1: obclient>EXPLAIN INSERT INTO t1 VALUES (1, '100')\G; *************************** 1. row *************************** Query Plan: ==================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ------------------------------------ |0 |INSERT | |1 |1 | |1 | EXPRESSION| |1 |1 | ==================================== Outputs & filters: ------------------------------------- 0 - output([__values.C1], [__values.C2]), filter(nil), columns([{T1: ({T1: (T1.C1, T1.C2)})}]), partitions(p0) 1 - output([__values.C1], [__values.C2]), filter(nil) values({1, '100'}) ~~~ 上述示例中，執行計劃展示中的 outputs & filters 詳細列出了 INSERT 算子的輸出信息如下： <table data-tag="table" id="table-zed-f98-qm7" class="table"><colgroup span="1" width="139" data-tag="col" id="col-rqr-e6b-ew2" colwidth="1*" colnum="1" colname="col1" style="width:50%" class="col"></colgroup><colgroup span="1" width="609" data-tag="col" id="col-o3i-e5n-gk9" colwidth="1*" colnum="2" colname="col2" style="width:50%" class="col"></colgroup><thead id="thead-wwx-ffi-19a" class="thead"><tr id="tr-793-oah-z4c"><th id="td-zz2-tjw-5f1"><p id="p-x54-ljf-wbw"><b>信息名稱</b></p></th><th id="td-m9t-ckc-mxk"><p id="p-ibj-13u-gif"><b>含義</b></p></th></tr></thead><tbody data-tag="tbody" id="tbody-3s1-4zh-20q" class="tbody"><tr data-tag="tr" id="tr-m45-5vf-ra6" class="tr"><td data-tag="td" id="td-qzt-70v-5uk" class="td"><p id="p-elk-bnq-xal">output</p></td><td data-tag="td" id="td-0eh-z7d-mx5" class="td"><p id="p-jkj-rqi-t0c">該算子輸出的表達式。</p></td></tr><tr data-tag="tr" id="tr-pzy-flc-ptc" class="tr"><td data-tag="td" id="td-tvg-dsl-1ul" class="td"><p id="p-byo-04w-m82">filter</p></td><td data-tag="td" id="td-8vw-qm7-c5u" class="td"><p id="p-lo0-si6-sjy">該算子上的過濾條件。</p><p id="p-0kw-a6k-xq6">由于示例中 INSERT 算子沒有設置 filter，所以為 nil。</p></td></tr><tr data-tag="tr" id="tr-ue7-qyo-zl2" class="tr"><td data-tag="td" id="td-mhb-y19-xz5" class="td"><p data-tag="p" id="p-ff3-3b8-l5e" class="p">columns</p></td><td data-tag="td" id="td-xy3-m7b-9hn" class="td"><p data-tag="p" id="p-ov1-45n-43l" class="p">插入操作涉及的數據表的列。</p></td></tr><tr data-tag="tr" id="tr-941-4qd-u9t" class="tr"><td data-tag="td" id="td-j3m-lvs-ops" class="td"><p id="p-mio-17g-g3d">partitions</p></td><td data-tag="td" id="td-ezk-ztr-72b" class="td"><p id="p-idj-gxh-ex7">插入操作涉及到的數據表的分區（非分區表可以認為是一個只有一個分區的分區表）。</p></td></tr></tbody></table> 更多 INSERT 算子的示例如下： * Q2 查詢將值（2, '200'）、（3, '300'）插入到表 t1 中。 ~~~ Q2: obclient>EXPLAIN INSERT INTO t1 VALUES (2, '200'),(3, '300')\G; *************************** 1. row *************************** Query Plan: ==================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ------------------------------------ |0 |INSERT | |2 |1 | |1 | EXPRESSION| |2 |1 | ==================================== Outputs & filters: ------------------------------------- 0 - output([__values.C1], [__values.C2]), filter(nil), columns([{T1: ({T1: (T1.C1, T1.C2)})}]), partitions(p0) 1 - output([__values.C1], [__values.C2]), filter(nil) values({2, '200'}, {3, '300'}) ~~~ * Q3 查詢將子查詢`SELECT * FROM t3`的結果插入到表 t1 中。 ~~~ Q3: obclient>EXPLAIN INSERT INTO t1 SELECT * FROM t3\G; *************************** 1. row *************************** Query Plan: ==================================== |0 |INSERT | |100000 |117862| |1 | EXCHANGE IN DISTR | |100000 |104060| |2 | EXCHANGE OUT DISTR| |100000 |75662 | |3 | SUBPLAN SCAN |VIEW1|100000 |75662 | |4 | TABLE SCAN |T3 |100000 |61860 | ================================================ Outputs & filters: ------------------------------------- 0 - output([VIEW1.C1], [VIEW1.C2]), filter(nil), columns([{T1: ({T1: (T1.C1, T1.C2)})}]), partitions(p0) 1 - output([VIEW1.C1], [VIEW1.C2]), filter(nil) 2 - output([VIEW1.C1], [VIEW1.C2]), filter(nil) 3 - output([VIEW1.C1], [VIEW1.C2]), filter(nil), access([VIEW1.C1], [VIEW1.C2]) 4 - output([T3.C1], [T3.C2]), filter(nil), access([T3.C2], [T3.C1]), partitions(p0) ~~~ * Q4 查詢將值（1, '100'）插入到分區表 t2 中，通過`partitions`參數可以看出，該值會被插入到 t2 的 p5 分區。 ~~~ Q4: obclient>EXPLAIN INSERT INTO t2 VALUES (1, '100')\G; *************************** 1. row *************************** Query Plan: ==================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ------------------------------------ |0 |INSERT | |1 |1 | |1 | EXPRESSION| |1 |1 | ==================================== Outputs & filters: ------------------------------------- 0 - output([__values.C1], [__values.C2]), filter(nil), columns([{T2: ({T2: (T2.C1, T2.C2)})}]), partitions(p5) 1 - output([__values.C1], [__values.C2]), filter(nil) values({1, '100'}) ~~~ ## MULTI PARTITION INSERT MULTI PARTITION INSERT 算子用于向數據表的多個分區中插入數據。如下例所示，Q5 查詢將值（2, '200'）、（3, '300'）插入到分區表 t2 中，通過`partitions`可以看出，這些值會被插入到 t2 的 p0 和 p6 分區。 ~~~ Q5: obclient>EXPLAIN INSERT INTO t2 VALUES (2, '200'),(3, '300')\G; *************************** 1. row *************************** Query Plan: =============================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ----------------------------------------------- |0 |MULTI PARTITION INSERT| |2 |1 | |1 | EXPRESSION | |2 |1 | =============================================== Outputs & filters: ------------------------------------- 0 - output([__values.C1], [__values.C2]), filter(nil), columns([{T2: ({T2: (T2.C1, T2.C2)})}]), partitions(p0, p6) 1 - output([__values.C1], [__values.C2]), filter(nil) values({2, '200'}, {3, '300'}) ~~~ 上述示例的執行計劃展示中的 outputs & filters 詳細列出了 MULTI PARTITION INSERT 算子的信息，字段的含義與 INSERT 算子相同。更多 MULTI PARTITION INSERT 算子的示例如下： * Q6 查詢將子查詢`SELECT * FROM t3`的結果插入到分區表 t2 中，因為無法確定子查詢的結果集，因此數據可能插入到 t2 的 p0 到 p9 的任何一個分區中。從1 號算子可以看到，這里的`SELECT * FROM t3`會被放在一個子查詢中，并將子查詢命名為 VIEW1。當 OceanBase 數據庫內部改寫 SQL 產生了子查詢時，會自動為子查詢命名，并按照子查詢生成的順序命名為 VIEW1、VIEW2、VIEW3... ~~~ Q6: obclient>EXPLAIN INSERT INTO t2 SELECT * FROM t3\G; *************************** 1. row *************************** Query Plan: ============================================== |ID|OPERATOR |NAME|EST. ROWS|COST| -------------------------------------------------- |0 |MULTI PARTITION INSERT| |100000 |117862| |1 | EXCHANGE IN DISTR | |100000 |104060| |2 | EXCHANGE OUT DISTR | |100000 |75662 | |3 | SUBPLAN SCAN |VIEW1|100000 |75662 | |4 | TABLE SCAN |T3 |100000 |61860 | ================================================== Outputs & filters: ------------------------------------- 0 - output([VIEW1.C1], [VIEW1.C2]), filter(nil), columns([{T2: ({T2: (T2.C1, T2.C2)})}]), partitions(p[0-9]) 1 - output([VIEW1.C1], [VIEW1.C2]), filter(nil) 2 - output([VIEW1.C1], [VIEW1.C2]), filter(nil) 3 - output([VIEW1.C1], [VIEW1.C2]), filter(nil), access([VIEW1.C1], [VIEW1.C2]) 4 - output([T3.C1], [T3.C2]), filter(nil), access([T3.C2], [T3.C1]), partitions(p0) ~~~ * Q7 查詢將值（1, '100'）插入到非分區表 t3 中。雖然 t3 本身是一個非分區表，但因為 t3 上存在全局索引 idx\_t3\_c2，因此本次插入也涉及到了多個分區。 ~~~ Q7: obclient>EXPLAIN INSERT INTO t3 VALUES (1, '100')\G; *************************** 1. row *************************** Query Plan: ============================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ----------------------------------------------- |0 |MULTI PARTITION INSERT| |1 |1 | |1 | EXPRESSION | |1 |1 | =============================================== Outputs & filters: ------------------------------------- 0 - output([__values.C1], [__values.C2]), filter(nil), columns([{T3: ({T3: (T3.C1, T3.C2)}, {IDX_T3_C2: (T3.C2, T3.C1)})}]), partitions(p0) 1 - output([__values.C1], [__values.C2]), filter(nil) values({1, '100'}) ~~~