與其它主流商業資料庫一樣，TiDB 的查詢優化器負責使用者及系統查詢的優化，生成有效且高效的執行計劃由執行器來執行。而優化器生成的執行計劃的優劣直接影響查詢的執行效率和效能。「TiDB 查詢優化及調優」系列文章將通過一些具體的案例，向大家介紹 TiDB 查詢及優化相關的原理和應用。本文為系列文章的第一篇，將簡要介紹 TiDB 的查詢優化器的優化流程。
TiDB 中常見的邏輯優化規則
優化器的優化過程可以簡單的看成在一個搜尋問題，即針對一條查詢，在由各種可能的執行計劃構成的巨大搜尋空間內尋找到該查詢的最優執行計劃。不同的資料庫查詢優化器根據架構不同，對應的優化流程也有所不同。TiDB 的查詢優化流程主要分為邏輯優化和物理優化兩部分。
在邏輯優化中，利用關係代數的變換規則進行查詢語句表示式的等價變換，並在這個過程中不斷增加或修剪可能的計劃搜尋空間（例如不同的 join order），最後選擇生成最優的邏輯計劃樹。在之後的物理優化過程中，對邏輯計劃樹中的運算元節點生成實際執行的物理計劃，並評估不同物理計劃的實現演算法（例如不同的 join 方法）或物件（例如使用不同的索引）的代價，從中選取代價最小的物理計劃。
下面分別對邏輯優化和物理優化做簡介。
邏輯優化是針對邏輯計劃中的邏輯運算元進行的優化流程。在介紹邏輯優化規則之前，我們先簡介一下 TiDB 中的幾種主要邏輯運算元：
- DataSource：資料來源，表示一個源表，如
select * from t中的t。 - Selection：代表了相應的過濾條件，
select * from t where a = 5中的where a = 5。 - Projection：投影操作，也用於表示式計算，
select c, a + b from t裡面的c和a+b就是投影和表示式計算操作。 - Join：兩個表的連線操作，
select t1.b, t2.c from t1 join t2 on t1.a = t2.a中的t1 join t2 on t1.a = t2.a就是兩個表t1和t2的連線操作。Join 有內連線，左連線，右連線等多種連線方式。
Selection，Projection，Join（簡稱 SPJ） 是 3 種最基本的運算元。
TiDB 的邏輯優化是基於規則的優化，通過對輸入的邏輯執行計劃按順序應用優化規則，使整個邏輯執行計劃變得更加高效。這些常用邏輯優化規則包括：
部分邏輯優化規則示例如下：
規則 4：Max / Min 優化
Max/ Min 優化，會對Max/ Min 語句進行改寫。如下面的語句：
select min(id) from t;
改成下面的寫法，可以實現類似的效果：
select id from t order by id desc limit 1;
前一個語句生成的執行計劃，是一個 TableScan 上面接一個 Aggregation，這是一個全表掃描的操作。後一個語句，生成執行計劃是 TableScan + Sort + Limit。通常資料表中的 id 列是主鍵或者存在索引，資料本身有序，這樣 Sort 就可以消除，最終變成 TableScan/IndexLookUp + Limit，這樣就避免了全表掃描的操作，只需要讀到第一條資料就能返回結果。
最大最小消除由優化器“自動”地做這個變換。
規則 5：外連線消除
外連線消除指的是將整個連線操作從查詢中移除。外連線消除需要滿足一定條件：
- 條件 1：LogicalJoin 的父親運算元只會用到 LogicalJoin 的 outer plan 所輸出的列
條件 2：
- 條件 2.1：LogicalJoin 中的 join key 在 inner plan 的輸出結果中滿足唯一性
- 條件 2.2：LogicalJoin 的父親運算元會對輸入的記錄去重
條件 1 和條件 2 必須同時滿足，但條件 2.1 和條件 2.2 只需滿足一條即可。
滿足條件 1 和 條件 2.1 的一個例子：
select t1.a from t1 left join t2 on t1.b = t2.b;
可以被改寫成：
select t1.a from t1;
TiDB 中常見的物理優化
物理優化是基於代價的優化，這一階段中，優化器會為邏輯執行計劃中的每個運算元選擇具體的物理實現，以將邏輯優化階段產生的邏輯執行計劃轉換成物理執行計劃。邏輯運算元的不同物理實現有著不同的時間複雜度、資源消耗和物理屬性等。在這個過程中，優化器會根據資料的統計資訊來估算不同物理實現的代價，並選擇整體代價最小的物理執行計劃。
物理優化需要做的決策有很多，例如：
- 讀取資料的方式：使用索引掃描或全表掃描讀取資料。
- 如果存在多個索引，索引之間的選擇。
- 邏輯運算元的物理實現，即實際使用的演算法。
- 是否可以將運算元下推到儲存層執行，以提升執行效率。
TiDB 統計資訊
統計資訊對於查詢優化器來說是至關重要的輸入資訊，優化器將會利用統計資訊來估算查詢謂詞的選擇率，查詢的各類基數，以及不同運算元的代價，並利用這些估算來進行部分邏輯優化以及物理優化。如果統計資訊存因為過時或缺失造成較大失真偏差，往往會對優化器的優化造成非常大的影響，從而影響到生成的查詢計劃。所以在此，我們會用較大篇幅介紹統計資訊，以及相關的收集與維護，因為這是優化器在做查詢優化的基石。
TiDB 收集的統計資訊包括了表級別和列級別的資訊，表的統計資訊包括總行數和修改的行數。列的統計資訊包括不同值的數量、NULL 的數量、直方圖、列上出現次數最多的值 TOPN 等資訊。
TiDB 的統計資訊收集包括了手動收集和自動更新兩種方式：
- 手動收集：
通過執行ANALYZE 語句來收集統計資訊。以資料庫中 person 表為例，使用 analyze 的試行語句如下：
analyze table person;收集統計資訊過程中，可以通過show analyze status 語句查詢執行狀態，該語句也可以通過where 子句對輸出結果進行過濾，顯示輸出結果如下：
mysql> show analyze status where job_info = 'analyze columns';
+--------------+------------+-----------------+---------------------+----------+
| Table_schema | Table_name | Job_info | Start_time | State |
+--------------+------------+-----------------+---------------------+----------+
| test | person | analyze columns | 2020-03-07 06:22:34 | finished |
| test | customer | analyze columns | 2020-03-07 06:32:19 | finished |
| test | person | analyze columns | 2020-03-07 06:35:27 | finished |
+--------------+------------+-----------------+---------------------+----------+
3 rows in set (0.01 sec)- 自動更新：
在執行 DML 語句時，TiDB 會自動更新表的總行數以及修改的行數。這些資訊會定期自動持久化，更新週期預設是 1 分鐘（20 * stats-lease）
注意：stats-lease 的預設值是 3s，如果將其設定為 0，則關閉統計資訊自動更新。
目前根據統計資訊收集和使用的演進，TiDB 目前支援兩個版本的統計資訊，其中 Version 2 在 Version 1 的基礎上做了更多的優化來改善統計資訊的維護方式和精度，以及收集效率。具體的差異可以參考 TiDB 統計資訊簡介文件。
在統計資訊收集之後，可以檢視統計資訊以及表的健康度來確認統計資訊是否有較大失真。
檢視錶的統計資訊 meta 資訊：
mysql> show stats_meta where table_name = 'person';
+---------+------------+----------------+---------------------+--------------+-----------+
| Db_name | Table_name | Partition_name | Update_time | Modify_count | Row_count |
+---------+------------+----------------+---------------------+--------------+-----------+
| test | person | | 2020-03-07 07:20:54 | 0 | 4 |
+---------+------------+----------------+---------------------+--------------+-----------+
1 row in set (0.01 sec)檢視錶的健康度資訊：
mysql> show stats_healthy where table_name = 'person';
+---------+------------+----------------+---------+
| Db_name | Table_name | Partition_name | Healthy |
+---------+------------+----------------+---------+
| test | person | | 100 |
+---------+------------+----------------+---------+
1 row in set (0.00 sec)可通過SHOW STATS_HISTOGRAMS 來檢視列的不同值數量以及 NULL 值數量等資訊：
mysql> show stats_histograms where table_name = 'person';
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+
| Db_name | Table_name | Partition_name | Column_name | Is_index | Update_time | Distinct_count | Null_count | Avg_col_size | Correlation |
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+
| test | person | | name | 0 | 2020-03-07 07:20:54 | 4 | 0 | 6.25 | -0.2 |
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+
1 row in set (0.00 sec)可通過SHOW STATS_BUCKETS 來檢視直方圖每個桶的資訊：
mysql> show stats_buckets;
+---------+------------+----------------+-------------+----------+-----------+-------+---------+-------------+-------------+
| Db_name | Table_name | Partition_name | Column_name | Is_index | Bucket_id | Count | Repeats | Lower_Bound | Upper_Bound |
+---------+------------+----------------+-------------+----------+-----------+-------+---------+-------------+-------------+
| test | person | | name | 0 | 0 | 1 | 1 | jack | jack |
| test | person | | name | 0 | 1 | 2 | 1 | peter | peter |
| test | person | | name | 0 | 2 | 3 | 1 | smith | smith |
| test | person | | name | 0 | 3 | 4 | 1 | tom | tom |
+---------+------------+----------------+-------------+----------+-----------+-------+---------+-------------+-------------+
4 rows in set (0.01 sec)可通過執行DROP STATS 語句來刪除統計資訊。語句如下：
mysql> DROP STATS person;TiDB 的統計資訊可以匯入匯出，方便備份以及值班人員復現定位相關問題。
- 匯出：通過以下介面可以獲取資料庫 ${db_name} 中的表${table_name}的 json 格式的統計資訊：
http://${tidb-server-ip}:${tidb-server-status-port}/stats/dump/${db_name}/${table_name}示例：獲取本機上 test 資料庫中 person 表的統計資訊：
curl -G "http://127.0.0.1:10080/stats/dump/test/person" > person.json- 匯入：將統計資訊匯出介面得到的 json 檔案匯入資料庫中：
mysql> LOAD STATS 'file_name';file_name 為被匯入的統計資訊檔名。
本文為「TiDB 查詢優化及調優」系列文章的第一篇，後續將繼續對 TiDB 查詢計劃、慢查詢診斷監控及排查、調整及優化查詢執行計劃以及其他優化器開發或規劃中的診斷調優功能等進行介紹。如果您對 TiDB 的產品有任何建議，歡迎來到 https://internals.tidb.io/ 與我們交流。