DELETE · k8s原理與實踐

[TOC] # DELETE DELETE 算子用于刪除數據表中滿足指定條件的數據行。 OceanBase 數據庫支持的 DELETE 算子包括 DELETE 和 MULTI PARTITION DELETE。 ## DELETE DELETE 算子用于刪除數據表單個分區中的數據。如下例所示，Q1 查詢刪除了表 t1 中所有滿足`c2>'100'`的行。 ~~~ 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 DELETE FROM t1 WHERE c2 > '100'\G; *************************** 1. row *************************** Query Plan: ====================================== |ID|OPERATOR |NAME|EST. ROWS|COST | -------------------------------------- |0 |DELETE | |10000 |118697| |1 | TABLE SCAN|T1 |10000 |108697| ====================================== Outputs & filters: ------------------------------------- 0 - output(nil), filter(nil), table_columns([{T1: ({T1: (T1.C1, T1.C2)})}]) 1 - output([T1.C1], [T1.C2]), filter([T1.C2 > '100']), access([T1.C1], [T1.C2]), partitions(p0) ~~~ 上述示例中，執行計劃展示中的 outputs & filters 詳細列出了 DELETE 算子的輸出信息如下： <table data-tag="table" id="table-n2f-3ev-gzw" class="table"><colgroup span="1" width="139" data-tag="col" id="col-k7q-nmx-s4w" colwidth="1*" colnum="1" colname="col1" style="width:50%" class="col"></colgroup><colgroup span="1" width="590" data-tag="col" id="col-uv9-634-9in" colwidth="1*" colnum="2" colname="col2" style="width:50%" class="col"></colgroup><thead id="thead-2bn-65v-8ke" class="thead"><tr id="tr-glz-6wl-hl0"><th id="td-3xf-80q-1qn">信息名稱</th><th id="td-a4w-ob3-mgd">含義</th></tr></thead><tbody data-tag="tbody" id="tbody-h4u-ig9-4fe" class="tbody"><tr data-tag="tr" id="tr-72b-oha-66b" class="tr"><td data-tag="td" id="td-o15-3yq-oq4" class="td">output</td><td data-tag="td" id="td-9ba-oct-roy" class="td">該算子輸出的表達式。</td></tr><tr data-tag="tr" id="tr-s5g-3b3-foy" class="tr"><td data-tag="td" id="td-cis-lfy-pq4" class="td">filter</td><td data-tag="td" id="td-twa-el9-o0h" class="td">該算子上的過濾條件。由于示例中 DELETE 算子沒有設置 filter，所以為 nil。對于刪除語句，WHERE 中的謂詞會下推到基表上，比如 Q1 查詢中的 <code data-tag="code" class="code">c2>'100'</code> 被下推到了 1 號算子上。</td></tr><tr data-tag="tr" id="tr-ib1-khb-4vl" class="tr"><td data-tag="td" id="td-zmy-ix7-87o" class="td">table_columns</td><td data-tag="td" id="td-gso-2ix-tnh" class="td">刪除操作涉及的數據表的列。</td></tr></tbody></table> 更多 DELETE 算子的示例如下： * Q2 查詢刪除 t1 中的所有數據行。 * Q3 查詢刪除分區表 t2 中滿足`c1 = 1`的數據行。 * Q4 查詢刪除分區表 t2 中滿足`c2 > '100'`的數據行。從執行計劃中可以看到，DELETE 算子分配在 EXCHANGE 算子下面，因此 2 號和 3 號算子會作為一個 task 以分區的粒度進行調度。在計劃執行時， 3 號算子掃描出 t2 一個分區中滿足`c2 > '100'`的數據，2 號算子 DELETE 則只會刪除相應分區下掃描出的數據。 ~~~ Q2: obclient>EXPLAIN DELETE FROM t1\G; *************************** 1. row *************************** Query Plan: ====================================== |ID|OPERATOR |NAME|EST. ROWS|COST | -------------------------------------- |0 |DELETE | |100000 |161860| |1 | TABLE SCAN|T1 |100000 |61860 | ====================================== Outputs & filters: ------------------------------------- 0 - output(nil), filter(nil), table_columns([{T1: ({T1: (T1.C1, T1.C2)})}]) 1 - output([T1.C1], [T1.C2]), filter(nil), access([T1.C1], [T1.C2]), partitions(p0) Q3: obclient>EXPLAIN DELETE FROM t2 WHERE c1 = 1\G; *************************** 1. row *************************** Query Plan: =================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ----------------------------------- |0 |DELETE | |1 |53 | |1 | TABLE GET|T2 |1 |52 | =================================== Outputs & filters: ------------------------------------- 0 - output(nil), filter(nil), table_columns([{T2: ({T2: (T2.C1, T2.C2)})}]) 1 - output([T2.C1], [T2.C2]), filter(nil), access([T2.C1], [T2.C2]), partitions(p5) Q4: obclient>EXPLAIN DELETE FROM t2 WHERE c2 > '100'\G; *************************** 1. row *************************** Query Plan: =============================================== |ID|OPERATOR |NAME |EST. ROWS|COST | ------------------------------------------------------- |0 |PX COORDINATOR | |100000 |1186893| |1 | EXCHANGE OUT DISTR |:EX10000|100000 |1186893| |2 | PX PARTITION ITERATOR| |100000 |1186893| |3 | DELETE | |100000 |1186893| |4 | TABLE SCAN |T2 |100000 |1086893| ================================================== Outputs & filters: ------------------------------------- 0 - output(nil), filter(nil) 1 - output(nil), filter(nil), dop=1 2 - output(nil), filter(nil) 3 - output(nil), filter(nil), table_columns([{T2: ({T2: (T2.C1, T2.C2)})}]) 4 - output([T2.C1], [T2.C2]), filter([T2.C2 > '100']), access([T2.C1], [T2.C2]), partitions(p[0-9]) ~~~ ## MULTI PARTITION DELETE MULTI PARTITION DELETE 算子用于刪除數據表多個分區中的數據。如下例所示，Q5 查詢刪除了表 t3 中所有滿足`c2 > '100'`的數據行。雖然 t3 本身是一個非分區表，但因為 t3 上存在全局索引 idx\_t3\_c2，因此每一條數據行會存在于多個分區中。 ~~~ Q5: obclient>EXPLAIN DELETE FROM t3 WHERE c2 > '100'\G; *************************** 1. row *************************** Query Plan: ======================================================== |ID|OPERATOR |NAME |EST. ROWS|COST | ----------------------------------------------------------- |0 |MULTI PARTITION DELETE | |10001 |27780| |1 | PX COORDINATOR | |10001 |17780| |2 | EXCHANGE OUT DISTR |:EX10000 |10001 |14941| |3 | PX PARTITION ITERATOR| |10001 |14941| |4 | TABLE SCAN |T3(IDX_T3_C2)|10001 |14941| =========================================================== Outputs & filters: ------------------------------------- 0 - output(nil), filter(nil), table_columns([{T3: ({T3: (T3.C1, T3.C2)}, {IDX_T3_C2: (T3.C2, T3.C1)})}]) 1 - output([T3.C1], [T3.C2]), filter(nil) 2 - output([T3.C2], [T3.C1]), filter(nil), dop=1 3 - output([T3.C2], [T3.C1]), filter(nil) 4 - output([T3.C2], [T3.C1]), filter(nil), access([T3.C2], [T3.C1]), partitions(p[0-2]) ~~~ 上述示例的執行計劃展示中的 outputs & filters 詳細列出了 MULTI PARTITION DELETE 算子的信息，字段的含義與 DELETE 算子相同。