SQL 執行計劃簡介 · k8s原理與實踐

[TOC] # SQL 執行計劃簡介執行計劃（EXPLAIN）是對一條 SQL 查詢語句在數據庫中執行過程的描述。用戶可以通過`EXPLAIN`命令查看優化器針對給定 SQL 生成的邏輯執行計劃。如果要分析某條 SQL 的性能問題，通常需要先查看 SQL 的執行計劃，排查每一步 SQL 執行是否存在問題。所以讀懂執行計劃是 SQL 優化的先決條件，而了解執行計劃的算子是理解`EXPLAIN`命令的關鍵。 ## EXPLAIN 命令格式 OceanBase 數據庫的執行計劃命令有三種模式：`EXPLAIN BASIC`、`EXPLAIN`和`EXPLAIN EXTENDED`。這三種模式對執行計劃展現不同粒度的細節信息: * `EXPLAIN BASIC`命令用于最基本的計劃展示。 * `EXPLAIN EXTENDED`命令用于最詳細的計劃展示（通常在排查問題時使用這種展示模式）。 * `EXPLAIN`命令所展示的信息可以幫助普通用戶了解整個計劃的執行方式。命令格式如下：?? ~~~ EXPLAIN [BASIC | EXTENDED | PARTITIONS | FORMAT = format_name] explainable_stmt format_name: { TRADITIONAL | JSON } explainable_stmt: { SELECT statement | DELETE statement | INSERT statement | REPLACE statement | UPDATE statement } ~~~ ## 執行計劃形狀與算子信息在數據庫系統中，執行計劃在內部通常是以樹的形式來表示的，但是不同的數據庫會選擇不同的方式展示給用戶。如下示例分別為 PostgreSQL 數據庫、Oracle 數據庫和 OceanBase 數據庫對于 TPCDS Q3 的計劃展示。 ~~~ obclient>SELECT /*TPC-DS Q3*/ * FROM (SELECT dt.d_year, item.i_brand_id brand_id, item.i_brand brand, Sum(ss_net_profit) sum_agg FROM date_dim dt, store_sales, item WHERE dt.d_date_sk = store_sales.ss_sold_date_sk AND store_sales.ss_item_sk = item.i_item_sk AND item.i_manufact_id = 914 AND dt.d_moy = 11 GROUP BY dt.d_year, item.i_brand, item.i_brand_id ORDER BY dt.d_year, sum_agg DESC, brand_id) WHERE rownum <= 100; ~~~ * PostgreSQL 數據庫執行計劃展示如下： ~~~ Limit (cost=13986.86..13987.20 rows=27 width=91) -> Sort (cost=13986.86..13986.93 rows=27 width=65) Sort Key: dt.d_year, (sum(store_sales.ss_net_profit)), item.i_brand_id -> HashAggregate (cost=13985.95..13986.22 rows=27 width=65) -> Merge Join (cost=13884.21..13983.91 rows=204 width=65) Merge Cond: (dt.d_date_sk = store_sales.ss_sold_date_sk) -> Index Scan using date_dim_pkey on date_dim dt (cost=0.00..3494.62 rows=6080 width=8) Filter: (d_moy = 11) -> Sort (cost=12170.87..12177.27 rows=2560 width=65) Sort Key: store_sales.ss_sold_date_sk -> Nested Loop (cost=6.02..12025.94 rows=2560 width=65) -> Seq Scan on item (cost=0.00..1455.00 rows=16 width=59) Filter: (i_manufact_id = 914) -> Bitmap Heap Scan on store_sales (cost=6.02..658.94 rows=174 width=14) Recheck Cond: (ss_item_sk = item.i_item_sk) -> Bitmap Index Scan on store_sales_pkey (cost=0.00..5.97 rows=174 width=0) Index Cond: (ss_item_sk = item.i_item_sk) ~~~ * Oracle 數據庫執行計劃展示如下： ~~~ Plan hash value: 2331821367 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100 | 9100 | 3688 (1)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | 2 | VIEW | | 2736 | 243K| 3688 (1)| 00:00:01 | |* 3 | SORT ORDER BY STOPKEY | | 2736 | 256K| 3688 (1)| 00:00:01 | | 4 | HASH GROUP BY | | 2736 | 256K| 3688 (1)| 00:00:01 | |* 5 | HASH JOIN | | 2736 | 256K| 3686 (1)| 00:00:01 | |* 6 | TABLE ACCESS FULL | DATE_DIM | 6087 | 79131 | 376 (1)| 00:00:01 | | 7 | NESTED LOOPS | | 2865 | 232K| 3310 (1)| 00:00:01 | | 8 | NESTED LOOPS | | 2865 | 232K| 3310 (1)| 00:00:01 | |* 9 | TABLE ACCESS FULL | ITEM | 18 | 1188 | 375 (0)| 00:00:01 | |* 10 | INDEX RANGE SCAN | SYS_C0010069 | 159 | | 2 (0)| 00:00:01 | | 11 | TABLE ACCESS BY INDEX ROWID| STORE_SALES | 159 | 2703 | 163 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- ~~~ * OceanBase 數據庫執行計劃展示如下： ~~~ |ID|OPERATOR |NAME |EST. ROWS|COST | ------------------------------------------------------- |0 |LIMIT | |100 |81141| |1 | TOP-N SORT | |100 |81127| |2 | HASH GROUP BY | |2924 |68551| |3 | HASH JOIN | |2924 |65004| |4 | SUBPLAN SCAN |VIEW1 |2953 |19070| |5 | HASH GROUP BY | |2953 |18662| |6 | NESTED-LOOP JOIN| |2953 |15080| |7 | TABLE SCAN |ITEM |19 |11841| |8 | TABLE SCAN |STORE_SALES|161 |73 | |9 | TABLE SCAN |DT |6088 |29401| ======================================================= ~~~ 由示例可見，OceanBase 數據庫的計劃展示與 Oracle 數據庫類似。OceanBase 數據庫執行計劃中的各列的含義如下： <table cols="2" id="table-unw-nt7-1ga" class="table"><colgroup colname="col1" colnum="1" colwidth="1*" id="colgroup-e83-wu3-acc" style="width:50%"></colgroup><colgroup colname="col2" colnum="2" colwidth="1*" id="colgroup-lqd-6fr-bee" style="width:50%"></colgroup><thead id="thead-jwt-p8w-7b1" class="thead"><tr id="tr-0a7-zol-l88"><th id="td-p21-a5u-mtb"><p id="p-u93-nt1-2o5">列名</p></th><th id="td-jch-zwm-6hx"><p id="p-pmp-ja7-3q8">含義</p></th></tr></thead><tbody id="tbody-zmb-9ys-1of" class="tbody"><tr id="tr-ld9-hy0-dc9"><td id="td-5xl-7a4-25j"><p id="p-vjf-n2o-3x6">ID</p></td><td id="td-70i-e7i-exf"><p id="p-9tx-12y-72x">執行樹按照前序遍歷的方式得到的編號（從 0 開始）。</p></td></tr><tr id="tr-eak-2ke-2oe"><td id="td-lx5-isf-bf8"><p id="p-jux-xx0-qpr">OPERATOR</p></td><td id="td-2bx-jtk-yl2"><p id="p-kn0-034-a1u">操作算子的名稱。</p></td></tr><tr id="tr-wt1-u8o-5gf"><td id="td-bjf-0mf-zcj"><p id="p-kc0-a2l-nq4">NAME</p></td><td id="td-q44-k0h-0ul"><p id="p-z9i-noi-hwt">對應表操作的表名（索引名）。</p></td></tr><tr id="tr-r2t-190-hus"><td id="td-p06-32t-ht4"><p id="p-ov3-gy3-ol6">EST. ROWS</p></td><td id="td-swc-jvn-ge8"><p id="p-mw1-lci-1ka">估算該操作算子的輸出行數。</p></td></tr><tr id="tr-5ch-7c9-a3w"><td id="td-4uk-7ba-67a"><p id="p-tu6-ssn-c4a">COST</p></td><td id="td-9pb-ch2-kee"><p id="p-zw3-xkh-vxb">該操作算子的執行代價（微秒）。</p></td></tr></tbody></table> **說明** 在表操作中，NAME 字段會顯示該操作涉及的表的名稱（別名），如果是使用索引訪問，還會在名稱后的括號中展示該索引的名稱，例如 t1(t1\_c2) 表示使用了 t1\_c2 這個索引。如果掃描的順序是逆序，還會在后面使用 RESERVE 關鍵字標識，例如`t1(t1_c2,RESERVE)`。 OceanBase 數據庫`EXPLAIN`命令輸出的第一部分是執行計劃的樹形結構展示。其中每一個操作在樹中的層次通過其在 operator 中的縮進予以展示。樹的層次關系用縮進來表示，層次最深的優先執行，層次相同的以特定算子的執行順序為標準來執行。上述 TPCDS Q3 示例的計劃展示樹如下: ![](https://img.kancloud.cn/53/79/53793898a55c0f19336171be1cb3e604_456x562.png) OceanBase 數據庫`EXPLAIN`命令輸出的第二部分是各操作算子的詳細信息，包括輸出表達式、過濾條件、分區信息以及各算子的獨有信息（包括排序鍵、連接鍵、下壓條件等）。示例如下： ~~~ Outputs & filters: ------------------------------------- 0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil), sort_keys([t1.c1, ASC], [t1.c2, ASC]), prefix_pos(1) 1 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil), equal_conds([t1.c1 = t2.c2]), other_conds(nil) 2 - output([t2.c1], [t2.c2]), filter(nil), sort_keys([t2.c2, ASC]) 3 - output([t2.c2], [t2.c1]), filter(nil), access([t2.c2], [t2.c1]), partitions(p0) 4 - output([t1.c1], [t1.c2]), filter(nil), access([t1.c1], [t1.c2]), partitions(p0) ~~~