標籤：

為什麼 MySQL 的優化器不能做智能的類型轉換?

02-21

假設有這麼一張表:

DROP TABLE IF EXISTS `order`;
CREATE TABLE `order` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`user_id` VARCHAR(20) NOT NULL
COMMENT 用戶 id,
`ctime` DATETIME NOT NULL
COMMENT 創建時間,
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_ctime` (`ctime`)
)
ENGINE = InnoDB
AUTO_INCREMENT = 0
DEFAULT CHARSET = utf8mb4
COMMENT = 訂單;

其中 user_id 類型為 VARCHAR, 且有索引.

以下執行計劃顯示查詢會掃描全表:

EXPLAIN SELECT *
FROM `order`
WHERE user_id = 10000;

而將 where 子句中查詢條件的類型改為字元串後, 查詢就會走索引:

EXPLAIN SELECT *
FROM `order`
WHERE user_id = 10000;

上述第一條 SQL 中, 因為查詢條件類型為整數, 而 user_id 類型為 VARCHAR, 會進行類型轉換, user_id 列數據需要轉成整數類型再進行 where 條件過濾, 所以查詢不會走索引.

問題是, 理論上, 在第一條 SQL 中, 優化器可以更智能一點, 對查詢條件進行類型轉換, 從而避免索引失效嗎?

謝邀

這並不是優化器不智能，而是確實沒有辦法。

在 TiDB 中也是一樣的行為，這個問題我們被用戶問過很多次。TiDB 的行為是以 MySQL 為準，所以這裡我以 TiDB 為例作為回答。

  • 對於 userid = 10000 這個表達式如何求值？

先上代碼：點擊這裡。簡單說，就是把 equal 表達式左右兩邊的值統一為某一種類型後再比較，這個例子中，會都當做（或轉換為整數類型），由於 userid 是varchar，那麼運算時會做一次 string -&> int 的轉換，然後再比較兩個整數的值是否相等。

  • 什麼情況可以用索引？

能夠使用索引的前提是能夠構造出索引查詢條件，也就是構造出有限個確定性的值，在用這些值去 索引數據結構中查找到對應的 entry（對於 MySQL 是 B-Tree，對於 TiDB 來說，是去 TiKV 中查找）。由於 userid 是 varchar 類型，那麼索引數據中存儲的 entry 也是字元類型，大家可以想一下哪些字元類型的值和 10000 這個整數相等？實際上有非常多的字元型的值轉換為整數後，可以和 10000 這個整數相等，比如 "10000.0"、"10000.00", "0100000".....，也就是無法構造出一個有限的（或者說不大的）查找候選集，去索引中查詢，所以並不能用上索引。

假設我們換一個場景，userid 是 int 類型，where 條件是 userid = "10000"，那麼是否可以用索引？大家可以想一下。

MySQL做得最靈活的就是數據類型，幾乎每種類型都可以做兼容計算，兼容計算帶來的壞處就是很多SQL寫得不嚴格的話，執行不會報錯，但是可能執行效率不會太好。SQL裡面的表達式計算和編程語言里的類型計算是同一個道理，要做計算前需要做兼容類型提升，只有提升到相同類型才能做表達式運算，我們將提升類型稱為關係運算f那麼兩個A B值得等值關係就有f(A)=f(B)

對於你這裡的等值其實就是f(user_id)=f(10000)，user_id和int的兼容比較類型是bigint，也就說當左邊的列類型是varchar，而右邊值類型是int類型族裡的任何一個類型，最終都需要轉換到bigint去做比較，所以f(user_id)其實就是cast(user_id as signed)，這是執行器內部的隱式轉換，所以這裡我們可以看出，當varchar列的值為10000,010000,0010000，以此類推上面這個等式都成立，所以和10000相等的varchar值很多，而是否能走索引，取決於能否從等值中提取出有限個常量值去btree索引上查找，我們把這個求解常量的過程稱為extract range，在OB中這個模塊叫query range，從上面的數學關係可以看出，從右往左的映射關係存在一對多，從這個表達式可以看出，左邊可以提取出無窮個可能滿足這個等式的值來，所以無法得到有限個可能的值去查索引，自然的，user_id=10000這個條件無法走索引，而user_id=10000由於兩邊類型相同，不需要做兼容類型提升，所以左邊能反推出來滿足這個關係的值只有10000，拿這個值去查索引是很容易的，這是extract range的關係，跟類型轉換無關。

推薦閱讀：

TAG:SQL | MySQL | SQL優化 |