EXPLAIN · k8s原理與實踐

[TOC] # EXPLAIN ## 描述該語句用于解釋 SQL 語句的執行計劃，可以是SELECT、DELETE、INSERT、REPLACE或UPDATE語句。提供正則過濾功能，通過session變量explain\_regex對輸出行/JSON進行過濾。 ## 格式 ~~~ 獲取表或列的信息： {EXPLAIN | DESCRIBE | DESC} tbl_name [col_name | wild] 獲取SQL計劃信息： {EXPLAIN | DESCRIBE | DESC} [BASIC | OUTLINE | EXTENDED | EXTENDED_NOADDR | PARTITIONS | FORMAT = {TRADITIONAL| JSON}] {SELECT statement | DELETE statement | INSERT statement | REPLACE statement| UPDATE statement} ~~~ ## 參數解釋 <table data-tag="table" id="table-7kh-3hs-cjf" class="table"><colgroup width="329" span="1" data-tag="col" id="col-9v3-s4j-o9f" colwidth="1*" colnum="1" colname="col1" style="width:50%" class="col"></colgroup><colgroup width="376" span="1" data-tag="col" id="col-tob-5v2-jil" colwidth="1*" colnum="2" colname="col2" style="width:50%" class="col"></colgroup><thead id="thead-up1-viz-1fi" class="thead"><tr id="tr-sqw-kup-1n9"><th id="td-0z0-0t4-1i6">參數</th><th id="td-4rt-3jp-y9m">描述</th></tr></thead><tbody data-tag="tbody" id="tbody-1t0-nr2-57w" class="tbody"><tr data-tag="tr" id="tr-2y3-0hs-4bl" class="tr"><td data-tag="td" id="td-fh2-ak0-fbs" class="td">tbl_name</td><td data-tag="td" id="td-zbe-2l8-zs5" class="td">指定表名。</td></tr><tr data-tag="tr" id="tr-lc1-x26-y2n" class="tr"><td data-tag="td" id="td-rdi-b8d-b6h" class="td">col_name</td><td data-tag="td" id="td-9ie-l92-2b5" class="td">指定表的列名。</td></tr><tr data-tag="tr" id="tr-ha9-vdb-gz4" class="tr"><td data-tag="td" id="td-zs7-ulv-4x5" class="td">BASIC</td><td data-tag="td" id="td-wpf-eh4-kp2" class="td">指定輸出計劃的基礎信息，如算子ID、算子名稱、所引用的表名。</td></tr><tr data-tag="tr" id="tr-gs3-9et-e97" class="tr"><td data-tag="td" id="td-eik-kn0-oqe" class="td">OUTLINE</td><td data-tag="td" id="td-e7s-zgo-8h5" class="td">指定輸出的計劃信息包含outline信息。</td></tr><tr data-tag="tr" id="tr-x12-jv9-d93" class="tr"><td data-tag="td" id="td-zrj-ykt-8lw" class="td">EXTENDED</td><td data-tag="td" id="td-u6v-sfu-29g" class="td">EXPLAIN產生附加信息，包括：每個算子的輸入列和輸出列，訪問表的分區信息，當前使用的filter信息，如果當前算子使用了索引，顯示所使用的索引列及抽取的query range。</td></tr><tr data-tag="tr" id="tr-k9a-1l3-4wr" class="tr"><td data-tag="td" id="td-j0i-sj0-z4b" class="td">EXTENDED_NOADDR</td><td data-tag="td" id="td-54y-pbx-qto" class="td">以簡約的方式展示附加信息。</td></tr><tr data-tag="tr" id="tr-urd-z72-v5i" class="tr"><td data-tag="td" id="td-v8u-9ma-c0n" class="td">PARTITIONS</td><td data-tag="td" id="td-ch4-1w9-vrl" class="td">顯示分區相關信息。</td></tr><tr data-tag="tr" id="tr-5rw-wwl-oih" class="tr"><td data-tag="td" id="td-bvr-u8n-dod" class="td">FORMAT = {TRADITIONAL| JSON}</td><td data-tag="td" id="td-bqg-rx0-gby" class="td">指定EXPALIN的輸出格式：<ul lake-indent="0" data-tag="ul" id="ul-6pv-pxt-oou" class="ul"><li data-tag="li" id="li-kzw-vz2-uz0" class="li">TRADITIONAL：表格輸出格式</li><li data-tag="li" id="li-scq-vna-cz9" class="li">JSON：KEY:VALUE輸出格式, JSON顯示為JSON字符串，包括EXTENDED和PARTITIONS信息。</li></ul></td></tr></tbody></table> ## 示例 * **省略explain\_type** ~~~ OceanBase(admin@test)>explain select * from t1,t2 where t1.c2=t2.c2 and t2.c1 > 4\G *************************** 1. row *************************** Query Plan: ======================================= |ID|OPERATOR |NAME|EST. ROWS|COST | --------------------------------------- |0 |HASH JOIN | |9801000 |5933109| |1 | TABLE SCAN|t2 |10000 |6219 | |2 | TABLE SCAN|t1 |100000 |68478 | ======================================= Outputs & filters: ------------------------------------- 0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil), equal_conds([t1.c2 = t2.c2]), other_conds(nil) 1 - output([t2.c2], [t2.c1]), filter(nil), access([t2.c2], [t2.c1]), partitions(p0) 2 - output([t1.c2], [t1.c1]), filter(nil), access([t1.c2], [t1.c1]), partitions(p0) ~~~ * **EXTENDED** ~~~ OceanBase(admin@test)>explain extended_noaddr select * from t1,t2 where t1.c2=t2.c2 and t2.c1 > 4\G *************************** 1. row *************************** Query Plan: ======================================= |ID|OPERATOR |NAME|EST. ROWS|COST | --------------------------------------- |0 |HASH JOIN | |9801000 |5933109| |1 | TABLE SCAN|t2 |10000 |6219 | |2 | TABLE SCAN|t1 |100000 |68478 | ======================================= Outputs & filters: ------------------------------------- 0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil), equal_conds([t1.c2 = t2.c2]), other_conds(nil) 1 - output([t2.c2], [t2.c1]), filter(nil), access([t2.c2], [t2.c1]), partitions(p0), is_index_back=false, range_key([t2.c1]), range(4 ; MAX), range_cond([t2.c1 > 4]) 2 - output([t1.c2], [t1.c1]), filter(nil), access([t1.c2], [t1.c1]), partitions(p0), is_index_back=false, range_key([t1.__pk_increment], [t1.__pk_cluster_id], [t1.__pk_partition_id]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true ~~~ * **TRADITIONAL****格式** ~~~ OceanBase(admin@test)>explain format=TRADITIONAL select * from t1,t2 where t1.c2=t2.c2 and t2.c1 > 4\G *************************** 1. row *************************** Query Plan: ======================================= |ID|OPERATOR |NAME|EST. ROWS|COST | --------------------------------------- |0 |HASH JOIN | |9801000 |5933109| |1 | TABLE SCAN|t2 |10000 |6219 | |2 | TABLE SCAN|t1 |100000 |68478 | ======================================= Outputs & filters: ------------------------------------- 0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil), equal_conds([t1.c2 = t2.c2]), other_conds(nil) 1 - output([t2.c2], [t2.c1]), filter(nil), access([t2.c2], [t2.c1]), partitions(p0) 2 - output([t1.c2], [t1.c1]), filter(nil), access([t1.c2], [t1.c1]), partitions(p0) ~~~ * **JSON格式** ~~~ OceanBase(admin@test)>explain format=JSON select * from t1,t2 where t1.c2=t2.c2 and t2.c1 > 4\G *************************** 1. row *************************** Query Plan: { "ID":2, "OPERATOR":"JOIN", "NAME":"JOIN", "EST.ROWS":9800999, "COST":5933108, "output": [ "t1.c1", "t1.c2", "t2.c1", "t2.c2" ], "TABLE SCAN": { "ID":0, "OPERATOR":"TABLE SCAN", "NAME":"TABLE SCAN", "EST.ROWS":10000, "COST":6218, "output": [ "t2.c2", "t2.c1" ] }, "TABLE SCAN": { "ID":1, "OPERATOR":"TABLE SCAN", "NAME":"TABLE SCAN", "EST.ROWS":100000, "COST":68477, "output": [ "t1.c2", "t1.c1" ] } } ~~~ EXPLAIN 的每個輸出行提供一個表的相關信息，并且每個行包括下面的列： <table data-tag="table" id="table-lb8-z13-lky" class="table"><colgroup width="226" span="1" data-tag="col" id="col-veq-2xe-qx6" colwidth="1*" colnum="1" colname="col1" style="width:50%" class="col"></colgroup><colgroup width="495" span="1" data-tag="col" id="col-dpo-0hk-lj2" colwidth="1*" colnum="2" colname="col2" style="width:50%" class="col"></colgroup><thead id="thead-fva-9mc-afe" class="thead"><tr id="tr-ac4-jor-38n"><th id="td-96a-vm1-7vu">列名</th><th id="td-2j9-ld1-fvm">說明</th></tr></thead><tbody data-tag="tbody" id="tbody-8rv-cb9-80k" class="tbody"><tr data-tag="tr" id="tr-dfg-if1-c4t" class="tr"><td data-tag="td" id="td-6b3-k7q-zj9" class="td">ID</td><td data-tag="td" id="td-utv-ywd-obd" class="td">計劃執行序列號。</td></tr><tr data-tag="tr" id="tr-tuj-3z2-s6m" class="tr"><td data-tag="td" id="td-jp5-v78-3ni" class="td">OPERATOR</td><td data-tag="td" id="td-s1p-9kd-lg7" class="td">執行算子。</td></tr><tr data-tag="tr" id="tr-2h5-lfg-67q" class="tr"><td data-tag="td" id="td-afu-shh-c2r" class="td">NAME</td><td data-tag="td" id="td-9uc-qil-fz9" class="td">算子所引用的表。</td></tr><tr data-tag="tr" id="tr-ghw-x1g-vza" class="tr"><td data-tag="td" id="td-118-n21-bpw" class="td">EST.ROWS</td><td data-tag="td" id="td-l7n-6w6-sxh" class="td">估計執行到當前算子輸出的行數。</td></tr><tr data-tag="tr" id="tr-rsy-thp-hif" class="tr"><td data-tag="td" id="td-47d-3fr-zem" class="td">COST</td><td data-tag="td" id="td-rue-vm5-219" class="td">執行到當前算子的CPU時間。</td></tr></tbody></table>