TiDB與MySQL優化器對照

今天這個我在公眾號不是首發。首發連結是：https://tidb.io/blog/00e83347   

    今天這個文章牽涉到我多年前遇到的一個故障，當時資料庫CPU100%，從而引發我對這個場景的介入。分析問題以後發現是SQL寫法的不同導致優化器執行的天差地別。而每種資料庫的優化器又都不一樣。今天我們要做的是MySQL和TiDB的優化器差異對比。首先申明這沒有誰好誰壞，客觀描述。這裡我引用我自己公眾號的一篇中的部分（MySQL部分來說明）。下面是我原文連結。 資料庫對比系列之一 (qq.com)其中部分圖和資料用了原文，屬於自己引用自己。當時沒有學習TiDB，現在學習了TiDB以後想對比一下。因為TiDB是相容MySQL的，那麼看看在這方面是借鑑還是自立門戶？今天的實驗就帶大家看看。

全表資料不用多5條就夠演示說明。

接下來同樣在TiDB中建立一個類似的表a，也是id是主鍵。從這個介面還是可以看出是TiDB的，儘管前面還是MySQL的提示符。但是就看書寫還是有點區別的。我真想建議官方把命令提示符改成TiDB>

環境準備完畢。我們先看看在MySQL中查詢主鍵的執行計劃，可以看到下圖紅框，他用到了主鍵。由於a列有索引，所以用到了w1索引。

做過優化的同學應該知道這就是用到了索引，不去查全部的資料，通過索引估算查到資料有一行。

     下面看TiDB的執行計劃。由於直接查主鍵索引，執行計劃也是估算一行。可以看出兩個執行計劃的風格，排版也不一樣。

 執行計劃的表現形式可以看出來和MySQL不一樣，但是效果一樣。

    這是第一步，我們往下看在極值函式下的表現形式。

由於是極值函式，所以優化器判斷最優的了。

     那麼看看TiDB的極值函式，他在TiKV節點上用了索引全掃描。可以看出了除了TiDB本身的TiKV架構不一樣，他的實現邏輯是不一樣的。

好了，前面都鋪墊好了。接下來就是重頭戲了。

    第三步，請看這兩句SQL，在MySQL中。就是括號之前一個是in一個是=的區別。

Select * from w where a  in  （select min(a) from w）;

Select * from w where a  =   （select min(a) from w）;

這兩句的執行計劃天差地別。使用in的時候子查詢全表查詢。而=的時候子查詢極值，父查詢用到了索引。這裡說一下當年就是因為這個導致了當時的故障。因為在Oracle中無論哪種寫法執行計劃都是一模一樣的。而開發同學一般不知道這些細節。認為在Oracle中可以這樣用，那麼在MySQL中也一樣可以這樣用。實際上不是這樣的。

     下面我們看看TIDB的表現。圖有點小。

TiDB在使用in的時候沒有像MySQL那樣子查詢使用了全表，而是執行了類似我們做的極值函式的執行計劃，然後父查詢用了範圍查詢。（可能是因為是in的關係）

在使用=的時候，這個子查詢的就當錯一個類似的常量了。我們把常量代入看看效果。

    結論TiDB雖然是相容MySQL（就連引數都一模一樣），但是優化器可以看出來是自己寫的，沒有說照抄。他的實現方式是自己獨立寫的。比起有些包一層的國產來說，TiDB應該是重寫了所有的優化器。

      做過資料庫的都知道資料庫的執行快慢取決於優化器的強大與統計資訊的準確。優化器是資料庫的大腦，自己寫優化器是很難的。因為全是數學知識，很多年前聽說MySQL的優化器有25種演算法，PG的優化器有50多種演算法。而Oracle的優化器有250多種演算法，之所以優化器強大，是有很多白髮蒼蒼的數學教授將數學的精髓注入了優化器中。這部分是資料庫與資料庫拉開差距的一個主要指標。有些東西可以抄但是如果自己寫那麼是需要很大的勇氣，更加要的是實力。就以我們這個案例來說，資料庫要針對SQL，識別出怎麼執行。

      由於精力有限，目前我主要投身在Oracle、MySQL、PostgreSQL和TiDB上。我認為沒有優劣之分，都是好的產品，有各自的適用場景和範圍。當然也和整體的開發水平和運維能力要結合看待。今天就到這裡。