在 MySQL 與各類關(guān)系型數(shù)據(jù)庫(kù)調(diào)優(yōu)中有一個(gè)讓無(wú)數(shù)開(kāi)發(fā)和 DBA 既熟悉又抓狂的經(jīng)典翻車案例開(kāi)發(fā)同學(xué)信誓旦旦地說(shuō)“大喜姐我明明在phone手機(jī)號(hào)字段上建了二級(jí)索引為什么一跑生產(chǎn)查詢它竟然還是走了全表掃描這數(shù)據(jù)庫(kù)是不是出 Bug 了”當(dāng)你讓他把實(shí)際執(zhí)行的 SQL 貼出來(lái)時(shí)往往會(huì)看到這樣一行極其具有迷惑性的代碼SELECT * FROM users WHERE phone 13800000000;乍一看毫無(wú)破綻字段名對(duì)、索引建了、數(shù)值也是對(duì)的。但仔細(xì)一查表結(jié)構(gòu)phone字段在 DDL 中定義的類型是varchar(20)。正是因?yàn)檫@一個(gè)看似微不足道的細(xì)節(jié)觸碰了數(shù)據(jù)庫(kù)底層最冷酷的規(guī)則——隱式類型轉(zhuǎn)換Implicit Type Conversion。一條原本應(yīng)該在 1 毫秒內(nèi)搞定的索引點(diǎn)查瞬間淪為了掃描數(shù)千萬(wàn)行的慢查詢CPU 直接打滿更詭異的是如果把場(chǎng)景反過(guò)來(lái)一個(gè)字段在 DDL 中是整型user_id int而查詢寫(xiě)成WHERE user_id 1001它居然又能神奇地走索引這究竟是為什么數(shù)據(jù)庫(kù)內(nèi)部到底執(zhí)行了怎樣的類型轉(zhuǎn)換優(yōu)先級(jí)今天我們就來(lái)從數(shù)據(jù)庫(kù)內(nèi)核的底層邏輯切入徹底終結(jié)這個(gè)頑疾。一、底層裁判規(guī)則MySQL 的類型轉(zhuǎn)換優(yōu)先級(jí)矩陣當(dāng) SQL 的等號(hào)兩端出現(xiàn)**“字符串類型VARCHAR”與“數(shù)值類型INT/BIGINT/DOUBLE”不一致時(shí)數(shù)據(jù)庫(kù)不會(huì)拒絕執(zhí)行而是會(huì)根據(jù)內(nèi)部?jī)?nèi)置的類型強(qiáng)制轉(zhuǎn)換優(yōu)先級(jí)Type Conversion Precedence**暗中將其中一方轉(zhuǎn)換為另一方。在 MySQL 與絕大多數(shù) ANSI SQL 標(biāo)準(zhǔn)引擎中轉(zhuǎn)換裁決原則如下當(dāng)一個(gè)字符串與一個(gè)數(shù)字進(jìn)行比較時(shí)MySQL 會(huì)無(wú)條件將“字符串”隱式轉(zhuǎn)換為“浮點(diǎn)數(shù) / 數(shù)值型DOUBLE”再做比較【隱式類型轉(zhuǎn)換的兩種截然相反的命運(yùn)】 場(chǎng)景 A: 字段是 VARCHAR傳入數(shù)字 SQL: WHERE phone 13800000000 底層被等價(jià)重寫(xiě)為: WHERE CAST(phone AS DOUBLE) 13800000000.0 災(zāi)難發(fā)生函數(shù)作用在【索引列】本身B 樹(shù)的有序性被徹底破壞索引失效全表掃描 ───────────────────────────────────────────────────────────── 場(chǎng)景 B: 字段是 INT傳入字符串 SQL: WHERE user_id 1001 底層被等價(jià)重寫(xiě)為: WHERE user_id CAST(1001 AS SIGNED) WHERE user_id 1001 ? 奇跡生還函數(shù)作用在【常量值】端索引列依然保持純凈B 樹(shù)范圍與點(diǎn)查完美生效看懂了嗎核心死穴在于隱式類型轉(zhuǎn)換到底是作用在“常量端”還是“字段端”一旦轉(zhuǎn)換發(fā)生在等號(hào)左側(cè)的索引列上就等同于在索引字段上包裹了一層內(nèi)置函數(shù)CAST(col AS DOUBLE)。根據(jù) B 樹(shù)的物理檢索原理沒(méi)有哪個(gè)索引樹(shù)能預(yù)先知道任意函數(shù)轉(zhuǎn)換后的排序位置因此優(yōu)化器除了全表掃描別無(wú)選擇二、真實(shí)事故復(fù)盤(pán)8000 萬(wàn)行用戶大表全表掃描我們來(lái)看一個(gè)在電商用戶中心極其慘痛的生產(chǎn)事故用戶表dim_users包含約 8000 萬(wàn)行活躍用戶核心索引為idx_user_phone (phone)。下游某個(gè)調(diào)用方服務(wù)在編寫(xiě) MyBatis 代碼時(shí)Java 實(shí)體類中的phone屬性被錯(cuò)誤地聲明為了Long類型// Java 實(shí)體類中的低級(jí)錯(cuò)誤 public class UserQueryParam { private Long phone; // 錯(cuò)誤手機(jī)號(hào)帶有業(yè)務(wù)標(biāo)識(shí)屬性必須是 String }MyBatis 框架在拼接動(dòng)態(tài) SQL 時(shí)將該參數(shù)以數(shù)字形式不帶單引號(hào)直接綁定到了 SQL 預(yù)編譯語(yǔ)句中SELECT user_id, user_name, status FROM dim_users WHERE phone 13912345678;1. EXPLAIN 深入透視DBA 查看執(zhí)行計(jì)劃赫然發(fā)現(xiàn)--------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | --------------------------------------------------------------------------------------------- | 1 | SIMPLE | dim_users | ALL | idx_user_phone| NULL | NULL | NULL | 79820110 | Using where | ---------------------------------------------------------------------------------------------key: NULL優(yōu)化器完全拋棄了idx_user_phone索引type: ALL整整 8000 萬(wàn)行數(shù)據(jù)被無(wú)情全表掃描查詢耗時(shí)從原本的0.8 毫秒暴漲至 14.5 秒并發(fā)請(qǐng)求瞬間將數(shù)據(jù)庫(kù)連接池耗盡服務(wù)大面積報(bào) 504 Gateway Timeout。2. 精準(zhǔn)糾偏代碼修復(fù)方案極其簡(jiǎn)單在參數(shù)兩端加上單引號(hào)或者將 Java 實(shí)體類修正為String phone-- 加上單引號(hào)消除隱式轉(zhuǎn)換 SELECT user_id, user_name, status FROM dim_users WHERE phone 13912345678;再次執(zhí)行EXPLAIN類型瞬間變?yōu)閠ype: refkey: idx_user_phone掃描行數(shù)降為 1 行耗時(shí)重回0.5 毫秒三、字符集與排序規(guī)則引發(fā)的隱蔽隱式轉(zhuǎn)換Collation Trap除了“字符串與數(shù)字”這種顯而易見(jiàn)的類型轉(zhuǎn)換外還有一種更加陰險(xiǎn)、隱蔽數(shù)倍的隱式轉(zhuǎn)換字符集與排序規(guī)則Charset Collation不一致當(dāng)兩個(gè)表進(jìn)行JOIN時(shí)即使字段在兩邊都是VARCHAR(64)依然可能發(fā)生全表掃描SELECT o.order_sn, u.user_name FROM orders o JOIN users u ON o.user_id_str u.user_id_str;如果orders表的字符集是utf8mb4而老歷史表users的字符集是utf8或兩者雖然都是utf8mb4但一個(gè)是utf8mb4_general_ci另一個(gè)是utf8mb4_unicode_ci由于utf8mb4包含的字符集范圍大于utf8根據(jù)字符集向下兼容原則MySQL 會(huì)暗中對(duì)utf8表的連接字段調(diào)用CONVERT(u.user_id_str USING utf8mb4)這同樣是在連接列上包裹了轉(zhuǎn)換函數(shù)導(dǎo)致被驅(qū)動(dòng)表上的索引徹底癱瘓四、工程化根治與防御體系建設(shè)在大型企業(yè)研發(fā)中絕不能依賴開(kāi)發(fā)人員在 Code Review 時(shí)的肉眼排查。必須建立自動(dòng)化的攔截中樞統(tǒng)一數(shù)據(jù)模型建模規(guī)范具有業(yè)務(wù)編號(hào)性質(zhì)的純數(shù)字字段如手機(jī)號(hào)、身份證號(hào)、銀行卡號(hào)、訂單號(hào)、SKU ID在 DDL 中一律強(qiáng)制定義為VARCHAR嚴(yán)禁定義為BIGINT業(yè)務(wù)代碼中的實(shí)體類屬性類型必須與 DDL 保持 1:1 嚴(yán)格對(duì)齊CI/CD 流水線 SQL 靜態(tài)語(yǔ)法檢查SonarQube / SQL-Lint在代碼提交階段通過(guò) AST 解析器掃描所有的 MyBatis XML 與 SQL 文件凡是針對(duì)VARCHAR類型字段的比較右值如果未包裹單引號(hào)CI 檢查直接飄紅中斷構(gòu)建架構(gòu)救急神器函數(shù)索引Function-based Index如果上游調(diào)用方是一個(gè)極其龐大、短期內(nèi)無(wú)法停機(jī)發(fā)布修改的第三方黑盒系統(tǒng)DBA 可以利用 MySQL 8.0 的函數(shù)索引進(jìn)行臨時(shí)緊急打底救命-- 創(chuàng)建基于隱式轉(zhuǎn)換的表達(dá)式函數(shù)索引 (臨時(shí)救命解法) ALTER TABLE dim_users ADD INDEX idx_phone_numeric ((CAST(phone AS DOUBLE)));五、總結(jié)在數(shù)據(jù)庫(kù)優(yōu)化的微觀世界里“差之毫厘謬以千里”絕不是一句夸張的比喻。隱式類型轉(zhuǎn)換看似是數(shù)據(jù)庫(kù)為了提供“容錯(cuò)便利”而設(shè)計(jì)的一種寬容機(jī)制但在海量數(shù)據(jù)的真實(shí)生產(chǎn)中這種所謂的“寬容”往往演變成了吞噬系統(tǒng)性能的毒藥。深諳底層類型優(yōu)先級(jí)轉(zhuǎn)換法則嚴(yán)守字段類型對(duì)齊契約才能讓每一條索引都精準(zhǔn)發(fā)揮出它的最大威力。