TiDB-explain詳解

1. 使用 EXPLAIN 來優(yōu)化 SQL 語句

EXPLAIN 語句的返回結(jié)果提供了 TiDB 執(zhí)行 SQL 查詢的詳細(xì)信息：

• EXPLAIN 可以和 SELECT, DELETE, INSERT, REPLACE, 以及 UPDATE 語句一起使用；

• 執(zhí)行 EXPLAIN，TiDB 會返回被 EXPLAIN 的 SQL 語句經(jīng)過優(yōu)化器后的最終物理執(zhí)行計劃。也就是說，EXPLAIN 展示了 TiDB 執(zhí)行該 SQL 語句的完整信息，比如以什么樣的順序，什么方式 JOIN 兩個表，表達(dá)式樹長什么樣等等。詳細(xì)請看 EXPLAIN 輸出格式；

• TiDB 目前還不支持 EXPLAIN [options] FOR CONNECTION connection_id，我們將在未來支持它，詳細(xì)請看：#4351；

通過觀察 EXPLAIN 的結(jié)果，你可以知道如何給數(shù)據(jù)表添加索引使得執(zhí)行計劃使用索引從而加速 SQL 語句的執(zhí)行速度；你也可以使用 EXPLAIN 來檢查優(yōu)化器是否選擇了最優(yōu)的順序來 JOIN 數(shù)據(jù)表。

2. EXPLAIN 輸出格式

目前 TiDB 的 EXPLAIN 會輸出 6 列，分別是：id，parents，children，task，operator info 和 count，執(zhí)行計劃中每個 operator 都由這 6 列屬性來描述，EXPLAIN 結(jié)果中每一行描述一個 operator。下面詳細(xì)解釋每個屬性的含義：

3.概述

3.1Task 簡介

    目前 TiDB 的計算任務(wù)隸屬于兩種不同的 task: cop task 和 root task。cop task 是指被下推到 KV 端分布式執(zhí)行的計算任務(wù)，root task 是指在 TiDB 端單點執(zhí)行的計算任務(wù)。SQL 優(yōu)化的目標(biāo)之一是將計算盡可能的下推到 KV 端執(zhí)行。

3.2表數(shù)據(jù)和索引數(shù)據(jù)

    TiDB 的表數(shù)據(jù)是指一張表的原始數(shù)據(jù)，存放在 TiKV 中。對于每行表數(shù)據(jù)，它的 key 是一個 64 位整數(shù)，稱為 Handle ID。如果一張表存在 int 類型的主鍵，我們會把主鍵的值當(dāng)作表數(shù)據(jù)的 Handle ID，否則由系統(tǒng)自動生成 Handle ID。表數(shù)據(jù)的 value 由這一行的所有數(shù)據(jù)編碼而成。在讀取表數(shù)據(jù)的時候，我們可以按照 Handle ID 遞增的順序返回。

    TiDB 的索引數(shù)據(jù)和表數(shù)據(jù)一樣，也存放在 TiKV 中。它的 key 是由索引列編碼的有序 bytes，value 是這一行索引數(shù)據(jù)對應(yīng)的 Handle ID，通過 Handle ID 我們可以讀取這一行的非索引列。在讀取索引數(shù)據(jù)的時候，我們按照索引列遞增的順序返回，如果有多個索引列，我們首先保證第 1 列遞增，并且在第 i 列相等的情況下，保證第 i + 1 列遞增。

3.3范圍查詢

    在 WHERE/HAVING/ON 條件中，我們會分析主鍵或索引鍵的查詢返回。如數(shù)字、日期類型的比較符，如大于、小于、等于以及大于等于、小于等于，字符類型的 LIKE 符號等。值得注意的是，我們只支持比較符一端是列，另一端是常量，或可以計算成某一常量的情況，類似 year(birth_day) < 1992 的查詢條件是不能利用索引的。還要注意應(yīng)盡可能使用同一類型進(jìn)行比較，以避免引入額外的 cast 操作而導(dǎo)致不能利用索引，如 user_id = 123456，如果 user_id 是字符串，需要將 123456 也寫成字符串常量的形式。針對同一列的范圍查詢條件使用 AND 和 OR 組合后，等于對范圍求交集或者并集。對于多維組合索引，我們可以寫多個列的條件。例如對組合索引(a, b, c)，當(dāng) a 為等值查詢時，可以繼續(xù)求 b 的查詢范圍，當(dāng) b 也為等值查詢時，可以繼續(xù)求 c 的查詢范圍，反之如果 a 為非等值查詢，則只能求 a 的范圍。

4.Operator Info

4.1TableReader 和 TableScan

    TableScan 表示在 KV 端對表數(shù)據(jù)進(jìn)行掃描，TableReader 表示在 TiDB 端從 TiKV 端讀取，屬于同一功能的兩個算子。table 表示 SQL 語句中的表名，如果表名被重命名，則顯示重命名。range 表示掃描的數(shù)據(jù)范圍，如果在查詢中不指定 WHERE/HAVING/ON 條件，則會選擇全表掃描，如果在 int 類型的主鍵上有范圍查詢條件，會選擇范圍查詢。keep order 表示 table scan 是否按順序返回。

4.2IndexReader 和 IndexLookUp

    Index 在 TiDB 端的讀取方式有兩種：IndexReader 表示直接從索引中讀取索引列，適用于 SQL 語句中僅引用了該索引相關(guān)的列或主鍵；IndexLookUp 表示從索引中過濾部分?jǐn)?shù)據(jù)，僅返回這些數(shù)據(jù)的 Handle ID，通過 Handle ID 再次查找表數(shù)據(jù)，這種方式需要兩次從 TiKV 獲取數(shù)據(jù)。Index 的讀取方式是由優(yōu)化器自動選擇的。

    IndexScan 是 KV 端讀取索引數(shù)據(jù)的算子，和 TableScan 功能類似。table 表示 SQL 語句中的表名，如果表名被重命名，則顯示重命名。index 表示索引名。range 表示掃描的數(shù)據(jù)范圍。out of order 表示 index scan 是否按照順序返回。注意在 TiDB 中，多列或者非 int 列構(gòu)成的主鍵是當(dāng)作唯一索引處理的。

4.3Selection

    Selection 表示 SQL 語句中的選擇條件，通常出現(xiàn)在 WHERE/HAVING/ON 子句中。

4.4Projection

    Projection 對應(yīng) SQL 語句中的 SELECT 列表，功能是將每一條輸入數(shù)據(jù)映射成新的輸出數(shù)據(jù)。

4.5Aggregation

    Aggregation 對應(yīng) SQL 語句中的 Group By 語句或者沒有 Group By 語句但是存在聚合函數(shù)，例如 count 或 sum 函數(shù)等。TiDB 支持兩種聚合算法：Hash Aggregation 以及 Stream Aggregation（待補(bǔ)充）。Hash Aggregation 是基于哈希的聚合算法，如果 Hash Aggregation 緊鄰 Table 或者 Index 的讀取算子，則聚合算子會在 TiKV 端進(jìn)行預(yù)聚合，以提高計算的并行度和減少網(wǎng)絡(luò)開銷。

4.6Join

    TiDB 支持 Inner Join 以及 Left/Right Outer Join，并會自動將可以化簡的外連接轉(zhuǎn)換為 Inner Join。

    TiDB 支持三種 Join 算法：Hash Join，Sort Merge Join 和 Index Look up Join。Hash Join 的原理是將參與連接的小表預(yù)先裝載到內(nèi)存中，讀取大表的所有數(shù)據(jù)進(jìn)行連接。Sort Merge Join 會利用輸入數(shù)據(jù)的有序信息，同時讀取兩張表的數(shù)據(jù)并依次進(jìn)行比較。Index Look Up Join 會讀取外表的數(shù)據(jù)，并對內(nèi)表進(jìn)行主鍵或索引鍵查詢。

4.7Apply

    Apply 是 TiDB 用來描述子查詢的一種算子，行為類似于 Nested Loop，即每次從外表中取一條數(shù)據(jù)，帶入到內(nèi)表的關(guān)聯(lián)列中，并執(zhí)行，最后根據(jù) Apply 內(nèi)聯(lián)的 Join 算法進(jìn)行連接計算。

    值得注意的是，Apply 一般會被查詢優(yōu)化器自動轉(zhuǎn)換為 Join 操作。用戶在編寫 SQL 的過程中應(yīng)盡量避免 Apply 算子的出現(xiàn)。