化中容易被忽視的關(guān)鍵優(yōu)化手段)
一條慢SQL卡了整整一個下午數(shù)據(jù)量明明不大索引也建了可看一眼執(zhí)行計劃差點沒把我氣笑——兩張百萬級的表被毫無過濾條件地做了全表關(guān)聯(lián)關(guān)鍵關(guān)聯(lián)字段上的過濾條件明明寫在WHERE里優(yōu)化器卻像沒看見一樣先把一大管子中間結(jié)果倒騰出來再在內(nèi)存里慢慢篩。這種問題十有八九就是連接條件下推沒有生效造成的。連接條件下推簡單說就是讓SQL執(zhí)行引擎在掃描每一張表的時候就把能提前過濾的條件按下去先把兩張大表變成兩堆“壓縮餅干”再去做關(guān)聯(lián)動作。這個優(yōu)化看似不起眼但決定了你的SQL是從秒級跌到毫秒級還是反過來。這篇文章我從原理講到實操從單機數(shù)據(jù)庫講到分布式場景把我這幾年排查慢SQL時積累的關(guān)于連接條件下推的經(jīng)驗一次性說清楚。1. 連接條件下推的核心原理優(yōu)化器到底在做什么1.1 一次執(zhí)行計劃的拆解下推發(fā)生前與發(fā)生后先看一條非常典型的OLTP查詢SELECT o.order_no, c.customer_name, p.pay_amount FROM orders o JOIN customers c ON o.customer_id c.id JOIN payments p ON o.id p.order_id WHERE o.created_at 2024-01-01 AND c.customer_level VIP;這條SQL要查2024年之后的VIP客戶的訂單及支付信息。從語義上看過濾條件有三個維度訂單時間、客戶等級、訂單與支付的關(guān)系。如果連接條件下推生效執(zhí)行計劃的大致形狀是這樣掃描orders表時直接從索引或數(shù)據(jù)頁上過濾出created_at 2024-01-01的行假設(shè)100萬訂單里只有10萬合格那引擎只帶著10萬行進入下一環(huán)節(jié)。掃描customers表時直接過濾customer_level VIP也許50萬客戶只剩5萬。帶著兩張都瘦過身的表去做JOIN中間結(jié)果就不會膨脹。如果下推沒有生效執(zhí)行計劃會變成另一個形狀orders表100萬行全量讀取customers表全量讀取兩張全量表先在內(nèi)存中做關(guān)聯(lián)生成一個可能遠超實際需求的中間結(jié)果集然后再往這個巨大的中間結(jié)果上應(yīng)用WHERE過濾條件。你可以想象一下同樣是10萬行和5萬行的關(guān)聯(lián)是拿著10萬和5萬去連還是拿著100萬和50萬去連性能差距完全是數(shù)量級的。1.2 連接條件下推與謂詞下推兩個字面上的區(qū)別很多人在看執(zhí)行計劃時會把“連接條件下推”和“謂詞下推”混著叫這沒問題但兩者的范圍其實有區(qū)分。謂詞下推Predicate Pushdown是個更寬泛的概念它指的是把WHERE、HAVING、JOIN … ON后面的過濾條件盡可能推到執(zhí)行計劃的更底層——推到存儲引擎掃描數(shù)據(jù)的那一層甚至直接推給存儲引擎的索引條件。這就像你讓倉庫管理員在揀貨時就順手把過期商品扔出去而不是把所有貨都搬到分揀臺上再慢慢挑。連接條件下推則是謂詞下推里專門針對JOIN場景的那一部分。優(yōu)化的關(guān)鍵點在“連接條件”四個字上即ON子句里的等值或范圍條件。比如前面SQL里的o.customer_id c.id這個條件能不能在掃描customers表時就被當成過濾條件這里的邏輯是如果兩張表關(guān)聯(lián)字段上有索引引擎可以在掃描大表時直接做“半連接”或“索引連接”把不符合關(guān)聯(lián)條件的小表數(shù)據(jù)擋在門外。兩者在實際執(zhí)行中的關(guān)系可以用下面這張表理清下推類型作用對象典型場景最大收益點謂詞下推WHERE中的獨立過濾條件單表查詢、子查詢減少單表掃描的數(shù)據(jù)量連接條件下推JOIN的ON關(guān)聯(lián)條件及其引申過濾多表JOIN減少JOIN中間結(jié)果集改變關(guān)聯(lián)順序分區(qū)裁剪分區(qū)表上的過濾條件按時間分區(qū)的大表跳過無關(guān)分區(qū)文件這三者經(jīng)常同時出現(xiàn)在一個執(zhí)行計劃里但連接條件下推最容易被人忽視因為它的收益不像“這條SQL放棄了全表掃描、改成范圍索引掃描”那么容易被看到它體現(xiàn)在中間結(jié)果集大小的變化上。2. 為什么連接條件下推能帶來可感知的性能躍升2.1 中間結(jié)果集膨脹是慢SQL的第一殺手很多初級開發(fā)者以為慢SQL只是“表太大”或“沒走索引”其實更常見的元兇是JOIN生成的中間結(jié)果集爆炸。假設(shè)A表100萬行B表50萬行A和B做等值JOIN關(guān)聯(lián)字段選擇性一般平均每個關(guān)聯(lián)鍵在B表命中10行。如果沒有提前過濾最壞情況下中間結(jié)果可能有幾百上千萬行再套一層WHERE過濾時每一條都要做一次無謂的判斷。而連接條件下推相當于改變了計算的“乘法順序”。我們來算一筆賬不加下推時引擎需要把100萬行和50萬行全部讀出來并加入哈希表或嵌套循環(huán)如果先應(yīng)用過濾把100萬行變成10萬行把50萬行變成5萬行掃描數(shù)據(jù)量縮減到十分之一甚至二十分之一內(nèi)存中建立的哈希表也會大幅縮小。哈希表小了構(gòu)建時間就短探測時沖突也少整個環(huán)節(jié)的CPU、內(nèi)存、IO全部受益。這里有個對比案例我前陣子處理過一張訂單表和一張訂單明細表。明細表有800萬行訂單表有120萬行一條按商戶維度匯總的SQL跑了11秒。表面看是沒走索引實際加了索引也沒用真正的問題在于優(yōu)化器把merchant_id M10086這個商戶過濾條件放在了JOIN之后才執(zhí)行導(dǎo)致明細表800萬行全量參與關(guān)聯(lián)。后來我把條件從WHERE挪進子查詢讓明細表先按商戶過濾再關(guān)聯(lián)SQL直接從11秒降到了0.3秒。這前后36倍的差距就是中間結(jié)果集從幾億行縮減到幾十萬行的結(jié)果。2.2 從磁盤IO和網(wǎng)絡(luò)傳輸角度看下推的價值如果你只是在內(nèi)存里做計算下推的收益相對有限但一旦涉及磁盤IO和網(wǎng)絡(luò)傳輸那就完全是另一個量級。數(shù)據(jù)庫的數(shù)據(jù)是以數(shù)據(jù)頁為單位讀入緩沖池的8KB或16KB一頁。同樣是掃描100萬行和掃描10萬行后者需要讀取的數(shù)據(jù)頁數(shù)量少一個數(shù)量級磁盤IO次數(shù)自然也隨之大降。如果這10萬行數(shù)據(jù)很多都集中在少數(shù)幾個數(shù)據(jù)頁上順序IO的優(yōu)勢也能體現(xiàn)出來。這套邏輯在分布式數(shù)據(jù)庫中體現(xiàn)得更徹底。節(jié)點之間的數(shù)據(jù)傳遞是要走網(wǎng)絡(luò)的網(wǎng)絡(luò)帶寬和延遲比本地內(nèi)存高很多個數(shù)量級。如果連接條件能被下推到各個存儲節(jié)點上讓每個節(jié)點先把本地的數(shù)據(jù)過濾一遍再傳回計算節(jié)點網(wǎng)絡(luò)傳輸量就能被壓到最小。我在使用ClickHouse處理寬表JOIN時發(fā)現(xiàn)同樣的關(guān)聯(lián)查詢能否在子查詢里先做過濾直接決定了查詢是秒出還是等半分鐘。因為ClickHouse的分布式子查詢下推如果生效每個分片只處理過濾后的一小塊數(shù)據(jù)匯總壓力小得多。2.3 下推如何反向影響JOIN順序與執(zhí)行策略連接條件下推的價值不止體現(xiàn)在數(shù)據(jù)量上它還會改變優(yōu)化器選擇連接順序的決策空間。最經(jīng)典的優(yōu)化原則是“小表驅(qū)動大表”但這里的“小表”指的不是表本身的物理容量而是經(jīng)過過濾條件壓縮后的“邏輯大小”。假設(shè)驅(qū)動表是大表但過濾后只剩1萬行被驅(qū)動表是小表但過濾條件很少所以仍有20萬行那顯然應(yīng)該讓前者做驅(qū)動表。連接條件下推正是把這種基于真實數(shù)據(jù)量的選擇權(quán)交還給優(yōu)化器。如果過濾條件沒有被推下去優(yōu)化器看到的全是表的原始尺寸它只能憑借基礎(chǔ)統(tǒng)計信息去猜做出的順序選擇自然容易跑偏。更關(guān)鍵的是過濾條件下推還會影響優(yōu)化器選擇哈希連接還是嵌套循環(huán)。數(shù)據(jù)量大到內(nèi)存放不下時優(yōu)化器大概率選哈希連接但當下推把數(shù)據(jù)壓到足夠小嵌套循環(huán)配合索引可能會更快。這種執(zhí)行策略的連鎖反應(yīng)就是為什么同一個SQL寫法下推不生效時性能差異會放大到幾十倍。3. 實操如何讓連接條件真正推下去3.1 復(fù)現(xiàn)一個慢SQL從執(zhí)行計劃里找證據(jù)我拿一個標準的三表場景來做演示。為了說明問題我在測試庫里建了三張表數(shù)據(jù)量分別是100萬、50萬、30萬。表和索引結(jié)構(gòu)如下CREATE TABLE orders ( id INT PRIMARY KEY, customer_id INT NOT NULL, order_no VARCHAR(64), created_at DATETIME, KEY idx_customer_id (customer_id), KEY idx_created_at (created_at) ); CREATE TABLE customers ( id INT PRIMARY KEY, customer_name VARCHAR(64), customer_level VARCHAR(16), KEY idx_level (customer_level) ); CREATE TABLE payments ( id INT PRIMARY KEY, order_id INT NOT NULL, pay_amount DECIMAL(12,2), KEY idx_order_id (order_id) );查詢目標不變查2024年以來的VIP客戶訂單及金額。我先看一眼EXPLAIN結(jié)果EXPLAIN SELECT o.order_no, c.customer_name, p.pay_amount FROM orders o JOIN customers c ON o.customer_id c.id JOIN payments p ON o.id p.order_id WHERE o.created_at 2024-01-01 AND c.customer_level VIP;MySQL 8.0的執(zhí)行計劃如果顯示orders表的rows估算為100000過濾掉了九成customers表rows估算為50000payments表的ref連接方式和filtered比例都合理那說明下推是生效的。但我看過的真實生產(chǎn)中這條SQL的執(zhí)行計劃經(jīng)常是orders表rows顯示為1000000customers表rows顯示為500000payments表顯示“全表掃描”訪問類型為ALL。這時候你就能確認連接條件下推沒有生效優(yōu)化器選擇了全量關(guān)聯(lián)再過濾的路徑。判斷執(zhí)行計劃是否下推成功重點看三處第一單表掃描階段是否出現(xiàn)Using where或Using index condition第二rows估算值是否接近過濾后的行數(shù)還是等于表總行數(shù)第三連接順序是否從最小結(jié)果集開始。如果沒有說明SQL寫法可能限制了優(yōu)化器的發(fā)揮空間。3.2 改寫SQL的三種姿勢讓優(yōu)化器聽你的話遇到下推失效時我的經(jīng)驗是可以從SQL結(jié)構(gòu)上做三種調(diào)整不需要動表結(jié)構(gòu)。第一種把過濾條件寫進子查詢提前壓縮表數(shù)據(jù)。這是最常用也最有效的方式SELECT o.order_no, c.customer_name, p.pay_amount FROM (SELECT * FROM orders WHERE created_at 2024-01-01) o JOIN (SELECT * FROM customers WHERE customer_level VIP) c ON o.customer_id c.id JOIN payments p ON o.id p.order_id;這種寫法直接告訴優(yōu)化器我在子查詢里已經(jīng)把orders和customers壓到最小你只需對壓縮后的結(jié)果做關(guān)聯(lián)。但需要注意MySQL 8.0里默認會把派生表合并到外層查詢?nèi)绻愕淖硬樵兝镉芯酆稀IMIT、DISTINCT合并可能會被阻止這時反而可能讓優(yōu)化器更糾結(jié)。我通常建議先用EXPLAIN驗證再看是否能再包一層。第二種把過濾條件寫在ON子句里而不是WHERE里。對INNER JOIN來說ON和WHERE的語義沒區(qū)別優(yōu)化器也會自行轉(zhuǎn)換但對LEFT JOIN或RIGHT JOIN條件寫在ON和WHERE里語義差別很大ON里的條件在關(guān)聯(lián)之前生效WHERE里的條件則在外連接完成后才過濾一個不留神就把外連接變成了內(nèi)連接。如果想讓左表的過濾條件提前壓縮左表數(shù)據(jù)寫在WHERE里沒問題如果想讓右表的數(shù)據(jù)參與關(guān)聯(lián)但只在關(guān)聯(lián)時過濾出符合條件的數(shù)據(jù)一定要寫在ON里。第三種用CTE表達“先壓縮后連接”的邏輯這在PostgreSQL里尤其好用WITH filtered_orders AS ( SELECT * FROM orders WHERE created_at 2024-01-01 ), filtered_customers AS ( SELECT * FROM customers WHERE customer_level VIP ) SELECT o.order_no, c.customer_name, p.pay_amount FROM filtered_orders o JOIN filtered_customers c ON o.customer_id c.id JOIN payments p ON o.id p.order_id;3.3 優(yōu)化器參數(shù)手動干預(yù)下推決策的開關(guān)SQL改寫解決的是“優(yōu)化器能不能看到過濾后的小表”的問題但有些時候優(yōu)化器看到了卻因為自己的成本估算模型不準確仍然做出了錯誤選擇。這時候就需要通過優(yōu)化器參數(shù)手動干預(yù)。在PostgreSQL里控制JOIN重寫和子查詢提升行為的參數(shù)主要是join_collapse_limit和from_collapse_limit。默認值是8意思是如果JOIN數(shù)量少于8優(yōu)化器可以自由地按成本重排連接順序。如果把這個值設(shè)成1優(yōu)化器就會嚴格按FROM子句從左到右的連接順序執(zhí)行不再做重排。在某些極端情況下比如連接條件寫在子查詢里但優(yōu)化器死活不肯下推我會把這個參數(shù)臨時調(diào)低讓優(yōu)化器老老實實按“先過濾、再連接”的順序來。注意這種調(diào)整在單條SQL級別就能做SET LOCAL join_collapse_limit 1;在MySQL 8.0里對應(yīng)的控制開關(guān)是optimizer_switch里的condition_pushdown和相關(guān)子查詢優(yōu)化項。derived_merge控制派生表合并derived_condition_pushdown控制條件要不要推入派生表。有時候我發(fā)現(xiàn)一個查詢外層條件明明能推入子查詢?nèi)ミ^濾數(shù)據(jù)但優(yōu)化器選擇了合并派生表之后再過濾搞得效果很差。這時可以關(guān)閉derived_mergeSET optimizer_switch derived_mergeoff;當然這屬于進階手段不建議全局修改最好在會話級別配合EXPLAIN一起調(diào)。全局亂改這些參數(shù)很容易讓其他正常SQL的執(zhí)行計劃也跑偏。4. 連接條件下推失效的常見坑與排查實錄4.1 隱式類型轉(zhuǎn)換下推的第一殺手我排查慢SQL這么多年最頭疼的就是隱式類型轉(zhuǎn)換導(dǎo)致的下推失效。執(zhí)行引擎在比較兩個不同類型的值時如果字段類型是VARCHAR傳入的是數(shù)字MySQL會把字段值全部轉(zhuǎn)成數(shù)字再做比較索引直接失效過濾條件自然也下推不下去。經(jīng)典表現(xiàn)是這種WHERE c.phone 13800138000如果phone字段是VARCHAR這條SQL就不會走索引執(zhí)行計劃里會看到Using where伴隨全表掃描。改成字符串寫法就能復(fù)用索引并讓條件下推WHERE c.phone 13800138000對于JOIN場景也是如此。兩表的關(guān)聯(lián)字段類型不一致比如A表customer_id是INTB表customer_id是VARCHAR等值JOIN必然產(chǎn)生隱式轉(zhuǎn)換連接條件無法下推到索引掃描執(zhí)行計劃就會異常難看。我處理過一個訂單系統(tǒng)主表的用戶ID是BIGINT用戶表的用戶ID被建成CHAR(11)兩表JOIN跑出十幾秒把用戶表ID字段的類型改成BIGINT之后SQL瞬間掉到幾十毫秒。遇到這種情況別急著加索引先查兩表字段類型是否一致。4.2 函數(shù)包裹列讓優(yōu)化器無從下手在WHERE條件里對列做函數(shù)運算也是下推失效的高頻原因。比如WHERE DATE(created_at) 2024-01-01這會讓created_at上的索引失去意義優(yōu)化器沒法把條件直接下推到索引掃描層。即使你不需要用索引這種寫法也會導(dǎo)致過濾動作必須在行讀取之后才能執(zhí)行存儲引擎層無法預(yù)先過濾。正確的做法是改寫為范圍條件WHERE created_at 2024-01-01 AND created_at 2024-01-02另一種常見情況是在關(guān)聯(lián)字段上套函數(shù)比如JOIN b ON DATE(a.created_at) DATE(b.created_at)。這種JOIN條件下推幾乎不可能因為優(yōu)化器無法利用任何索引做等值匹配。碰到這類需求我的建議是考慮在表里增加冗余的日期列并建索引而不是指望優(yōu)化器幫你把函數(shù)倒騰清楚。4.3 外連接中條件位置的陷阱LEFT JOIN場景下條件的放置位置非常講究。以如下SQL為例SELECT o.*, p.pay_amount FROM orders o LEFT JOIN payments p ON o.id p.order_id WHERE p.pay_amount 100;這個SQL有一個隱蔽問題WHERE條件p.pay_amount 100實際上會過濾掉那些沒有支付記錄的訂單行從而把LEFT JOIN悄悄變成了INNER JOIN的語義。從執(zhí)行計劃看優(yōu)化器確實會把p.pay_amount 100下推成對payments表的過濾但這不是你想要的語義。正確寫法是SELECT o.*, p.pay_amount FROM orders o LEFT JOIN payments p ON o.id p.order_id AND p.pay_amount 100;把條件放在ON里payments表先過濾LEFT JOIN語義才真實同時過濾條件也能被下推到payments表掃描層。這里我特別想提醒不要以為下推失效只是性能問題它還可能靜悄悄地改變業(yè)務(wù)邏輯結(jié)果。排查時如果發(fā)現(xiàn)同一條SQL在某個版本前后返回行數(shù)不一致要先看看是不是條件被優(yōu)化器挪了位置。4.4 優(yōu)化器估算偏差讓下推決策翻車即使SQL寫得很干凈、字段類型一致、沒有函數(shù)包裹優(yōu)化器仍有可能因為統(tǒng)計信息陳舊而下推失敗。最典型的情況是表的統(tǒng)計信息長時間沒有更新優(yōu)化器以為某個過濾條件能篩掉90%的行實際只能篩掉1%或者反過來于是它選了一個災(zāi)難性的執(zhí)行計劃。解決這類問題的第一步是刷新統(tǒng)計信息。在MySQL里是ANALYZE TABLE orders;在PostgreSQL里是ANALYZE orders;更新統(tǒng)計信息之后再看EXPLAIN的rows估算是否接近真實掃描行數(shù)。如果仍不準就該考慮連接順序固定或者改寫SQL。有時我也會直接使用HintMySQL 8.0支持SELECT /* JOIN_FIXED_ORDER() */ ...PostgreSQL需要安裝pg_hint_plan擴展然后可以寫SELECT /* Leading(c o p) */ ...Hints這招屬于最后的強行干預(yù)手段能用改寫SQL解決的問題我一般不會先上Hints因為Hints會把SQL限定死后續(xù)數(shù)據(jù)分布變化了原本的手動優(yōu)化可能變成新的瓶頸。5. 連接條件下推在分布式數(shù)據(jù)庫和OLAP場景中的延伸5.1 分布式場景為什么推下去比什么都重要單機數(shù)據(jù)庫里連接條件下推的收益主要體現(xiàn)在磁盤IO和CPU消耗上到了分布式數(shù)據(jù)庫收益就被放大了無數(shù)倍因為數(shù)據(jù)要跨節(jié)點傳輸。像ClickHouse、TiDB、Trino這類系統(tǒng)查詢計劃往往會把部分計算下推到存儲節(jié)點執(zhí)行。以TiDB為例它的優(yōu)化器會把能下推的算子封裝成Cop Task發(fā)送給存儲節(jié)點TiKV并行執(zhí)行其中最經(jīng)典的就是把過濾條件下推到TiKV的掃表階段這個動作在TiDB里被稱為“算子下推”。如果你在TiDB里做一個大表JOIN而關(guān)聯(lián)條件沒法下推那所有數(shù)據(jù)都要匯集到TiDB Server節(jié)點單點內(nèi)存和網(wǎng)絡(luò)帶寬瞬間爆炸查詢直接就沒了命。ClickHouse對JOIN的支持相對單薄但它的PREWHERE優(yōu)化和子查詢下推同樣值得關(guān)注。我個人經(jīng)驗是在ClickHouse里做JOIN時絕對要把過濾條件寫在子查詢里而不是直接寫在JOIN之后因為ClickHouse的優(yōu)化器不會像MySQL那樣大膽地做條件推導(dǎo)。另外ClickHouse的join_use_nulls設(shè)置和外連接關(guān)系密切一個條件位置不對結(jié)果集就可能出現(xiàn)意料之外的空值排查起來非常痛苦。5.2 分區(qū)裁剪另一種意義上的“下推”分布式數(shù)據(jù)倉庫還有一個和連接條件下推原理相似的優(yōu)化分區(qū)裁剪Partition Pruning。如果一張大表按天做分區(qū)你在查詢條件里寫了event_date 2024-01-01優(yōu)秀的優(yōu)化器會把條件下推到元數(shù)據(jù)層直接跳過其他999個分區(qū)文件只讀取1月1日那一個分區(qū)的數(shù)據(jù)。這個動作不是發(fā)生在存儲引擎過濾數(shù)據(jù)時而是發(fā)生在計劃生成階段但效果和連接條件下推一樣最大限度減少參與計算的數(shù)據(jù)量。在基于Trino或Spark SQL寫數(shù)據(jù)湖查詢時分區(qū)裁剪的實現(xiàn)依賴于分區(qū)的元數(shù)據(jù)和過濾條件的可識別性。如果條件里套了函數(shù)比如DATE_FORMAT(event_date, %Y-%m-%d) 2024-01-01裁剪直接就失效了分區(qū)目錄會被全部掃一遍。所以在寫OLAP查詢時我一直強調(diào)一個原則不要在分區(qū)列上套函數(shù)不要對分區(qū)列做類型轉(zhuǎn)換這是讓分區(qū)條件下推生效的最基本前提。5.3 主流數(shù)據(jù)庫的連接條件下推現(xiàn)狀對比很多人都以為連接條件下推是數(shù)據(jù)庫引擎默認就做得很好的事實際差別很大。目前主流數(shù)據(jù)庫對它的支持程度各有不同數(shù)據(jù)庫支持程度常見觸發(fā)方式典型失效場景MySQL 8.0較好索引連接、派生表合并隱式轉(zhuǎn)換、函數(shù)包裹列PostgreSQL很好子查詢提升、參數(shù)化路徑統(tǒng)計信息陳舊、JOIN數(shù)過多ClickHouse一般PREWHERE、子查詢下推JOIN后置過濾無法自動下推TiDB很好Cop Task算子下推關(guān)聯(lián)字段類型不一致Trino較好下推連接條件到分片分區(qū)列上套函數(shù)這張表可以當成一個排查方向的參考。如果你手里的引擎屬于“下推支持一般”的類型那SQL寫法的規(guī)范性就顯得格外重要不能指望系統(tǒng)幫你兜底。5.4 單機轉(zhuǎn)分布式時的自查建議從單機數(shù)據(jù)庫遷到分布式數(shù)據(jù)庫時最容易踩的坑就是沿用原來的SQL習(xí)慣以為“貴的查詢引擎會自動幫我優(yōu)化”。我的建議是遷移之前把每條核心SQL都拿出來重新審查一遍重點看三件事關(guān)聯(lián)字段類型是否完全一致過濾條件有沒有寫在分區(qū)列上JOIN順序是否明顯不合理。這些問題在單機時代可能只是慢那么幾百毫秒到了分布式架構(gòu)下一個小問題就可能放大成集群級別的事故。我在幫一個業(yè)務(wù)從MySQL遷到TiDB時就遇到過一個典型案例。原系統(tǒng)里一條訂單匯總SQL用MySQL跑大概是1.2秒遷移到TiDB后直接變成25秒。排查了很久發(fā)現(xiàn)問題是開發(fā)在兩張表的關(guān)聯(lián)字段上一邊用了BIGINT另一邊用了VARCHARMySQL優(yōu)化器會在內(nèi)部做隱式轉(zhuǎn)換后繼續(xù)嘗試用索引而TiDB的優(yōu)化器對這種情況的處理方式不同連接條件無法下推成Cop Task大量的關(guān)聯(lián)數(shù)據(jù)就只能在TiDB Server節(jié)點上處理。像這種問題表面上是SQL慢實際是類型不一致導(dǎo)致的條件下推失效。遷庫之前花了半天統(tǒng)一字段類型SQL恢復(fù)到1秒以內(nèi)。結(jié)尾一點個人體會如果你問我連接條件下推最核心的一句話是什么我會說數(shù)據(jù)庫優(yōu)化器在窮舉執(zhí)行策略時最需要你幫它的就是把過濾條件放在它一眼能看到的地方。我在實際排查中反復(fù)發(fā)現(xiàn)很多慢SQL的根源不是索引缺失、不是服務(wù)器配置低而是SQL寫法把優(yōu)化器的路堵死了——類型不匹配、函數(shù)包裹、條件寫在語義錯誤的位置每一條都在告訴優(yōu)化器“別想下推了”。所以我現(xiàn)在的習(xí)慣是任何核心查詢上線前先看執(zhí)行計劃再檢查關(guān)聯(lián)字段類型然后確認過濾條件是否落在表的掃描階段。這個習(xí)慣幫我避開了大量生產(chǎn)事故也讓我在處理別人的慢SQL時能第一時間抓住命門。如果你現(xiàn)在正有一條慢SQL查不出原因別急著加索引先把執(zhí)行計劃打開看看你的連接條件到底有沒有推下去。另外分享一個小技巧在排查這類問題時我習(xí)慣把EXPLAIN輸出的rows估算值和真實命中的行數(shù)做對比如果兩者差距超過10倍優(yōu)化器的成本模型大概率已經(jīng)被誤導(dǎo)下推效果也不會好這個時候優(yōu)先修復(fù)統(tǒng)計信息而不是繼續(xù)調(diào)SQL。這個細節(jié)很多DBA都不一定會告訴你。 連接條件下推：慢SQL優(yōu)化中容易被忽視的關(guān)鍵優(yōu)化手段
一條慢SQL卡了整整一個下午數(shù)據(jù)量明明不大索引也建了可看一眼執(zhí)行計劃差點沒把我氣笑——兩張百萬級的表被毫無過濾條件地做了全表關(guān)聯(lián)關(guān)鍵關(guān)聯(lián)字段上的過濾條件明明寫在WHERE里優(yōu)化器卻像沒看見一樣先把一大管子中間結(jié)果倒騰出來再在內(nèi)存里慢慢篩。這種問題十有八九就是連接條件下推沒有生效造成的。連接條件下推簡單說就是讓SQL執(zhí)行引擎在掃描每一張表的時候就把能提前過濾的條件按下去先把兩張大表變成兩堆“壓縮餅干”再去做關(guān)聯(lián)動作。這個優(yōu)化看似不起眼但決定了你的SQL是從秒級跌到毫秒級還是反過來。這篇文章我從原理講到實操從單機數(shù)據(jù)庫講到分布式場景把我這幾年排查慢SQL時積累的關(guān)于連接條件下推的經(jīng)驗一次性說清楚。1. 連接條件下推的核心原理優(yōu)化器到底在做什么1.1 一次執(zhí)行計劃的拆解下推發(fā)生前與發(fā)生后先看一條非常典型的OLTP查詢SELECT o.order_no, c.customer_name, p.pay_amount FROM orders o JOIN customers c ON o.customer_id c.id JOIN payments p ON o.id p.order_id WHERE o.created_at 2024-01-01 AND c.customer_level VIP;這條SQL要查2024年之后的VIP客戶的訂單及支付信息。從語義上看過濾條件有三個維度訂單時間、客戶等級、訂單與支付的關(guān)系。如果連接條件下推生效執(zhí)行計劃的大致形狀是這樣掃描orders表時直接從索引或數(shù)據(jù)頁上過濾出created_at 2024-01-01的行假設(shè)100萬訂單里只有10萬合格那引擎只帶著10萬行進入下一環(huán)節(jié)。掃描customers表時直接過濾customer_level VIP也許50萬客戶只剩5萬。帶著兩張都瘦過身的表去做JOIN中間結(jié)果就不會膨脹。如果下推沒有生效執(zhí)行計劃會變成另一個形狀orders表100萬行全量讀取customers表全量讀取兩張全量表先在內(nèi)存中做關(guān)聯(lián)生成一個可能遠超實際需求的中間結(jié)果集然后再往這個巨大的中間結(jié)果上應(yīng)用WHERE過濾條件。你可以想象一下同樣是10萬行和5萬行的關(guān)聯(lián)是拿著10萬和5萬去連還是拿著100萬和50萬去連性能差距完全是數(shù)量級的。1.2 連接條件下推與謂詞下推兩個字面上的區(qū)別很多人在看執(zhí)行計劃時會把“連接條件下推”和“謂詞下推”混著叫這沒問題但兩者的范圍其實有區(qū)分。謂詞下推Predicate Pushdown是個更寬泛的概念它指的是把WHERE、HAVING、JOIN … ON后面的過濾條件盡可能推到執(zhí)行計劃的更底層——推到存儲引擎掃描數(shù)據(jù)的那一層甚至直接推給存儲引擎的索引條件。這就像你讓倉庫管理員在揀貨時就順手把過期商品扔出去而不是把所有貨都搬到分揀臺上再慢慢挑。連接條件下推則是謂詞下推里專門針對JOIN場景的那一部分。優(yōu)化的關(guān)鍵點在“連接條件”四個字上即ON子句里的等值或范圍條件。比如前面SQL里的o.customer_id c.id這個條件能不能在掃描customers表時就被當成過濾條件這里的邏輯是如果兩張表關(guān)聯(lián)字段上有索引引擎可以在掃描大表時直接做“半連接”或“索引連接”把不符合關(guān)聯(lián)條件的小表數(shù)據(jù)擋在門外。兩者在實際執(zhí)行中的關(guān)系可以用下面這張表理清下推類型作用對象典型場景最大收益點謂詞下推WHERE中的獨立過濾條件單表查詢、子查詢減少單表掃描的數(shù)據(jù)量連接條件下推JOIN的ON關(guān)聯(lián)條件及其引申過濾多表JOIN減少JOIN中間結(jié)果集改變關(guān)聯(lián)順序分區(qū)裁剪分區(qū)表上的過濾條件按時間分區(qū)的大表跳過無關(guān)分區(qū)文件這三者經(jīng)常同時出現(xiàn)在一個執(zhí)行計劃里但連接條件下推最容易被人忽視因為它的收益不像“這條SQL放棄了全表掃描、改成范圍索引掃描”那么容易被看到它體現(xiàn)在中間結(jié)果集大小的變化上。2. 為什么連接條件下推能帶來可感知的性能躍升2.1 中間結(jié)果集膨脹是慢SQL的第一殺手很多初級開發(fā)者以為慢SQL只是“表太大”或“沒走索引”其實更常見的元兇是JOIN生成的中間結(jié)果集爆炸。假設(shè)A表100萬行B表50萬行A和B做等值JOIN關(guān)聯(lián)字段選擇性一般平均每個關(guān)聯(lián)鍵在B表命中10行。如果沒有提前過濾最壞情況下中間結(jié)果可能有幾百上千萬行再套一層WHERE過濾時每一條都要做一次無謂的判斷。而連接條件下推相當于改變了計算的“乘法順序”。我們來算一筆賬不加下推時引擎需要把100萬行和50萬行全部讀出來并加入哈希表或嵌套循環(huán)如果先應(yīng)用過濾把100萬行變成10萬行把50萬行變成5萬行掃描數(shù)據(jù)量縮減到十分之一甚至二十分之一內(nèi)存中建立的哈希表也會大幅縮小。哈希表小了構(gòu)建時間就短探測時沖突也少整個環(huán)節(jié)的CPU、內(nèi)存、IO全部受益。這里有個對比案例我前陣子處理過一張訂單表和一張訂單明細表。明細表有800萬行訂單表有120萬行一條按商戶維度匯總的SQL跑了11秒。表面看是沒走索引實際加了索引也沒用真正的問題在于優(yōu)化器把merchant_id M10086這個商戶過濾條件放在了JOIN之后才執(zhí)行導(dǎo)致明細表800萬行全量參與關(guān)聯(lián)。后來我把條件從WHERE挪進子查詢讓明細表先按商戶過濾再關(guān)聯(lián)SQL直接從11秒降到了0.3秒。這前后36倍的差距就是中間結(jié)果集從幾億行縮減到幾十萬行的結(jié)果。2.2 從磁盤IO和網(wǎng)絡(luò)傳輸角度看下推的價值如果你只是在內(nèi)存里做計算下推的收益相對有限但一旦涉及磁盤IO和網(wǎng)絡(luò)傳輸那就完全是另一個量級。數(shù)據(jù)庫的數(shù)據(jù)是以數(shù)據(jù)頁為單位讀入緩沖池的8KB或16KB一頁。同樣是掃描100萬行和掃描10萬行后者需要讀取的數(shù)據(jù)頁數(shù)量少一個數(shù)量級磁盤IO次數(shù)自然也隨之大降。如果這10萬行數(shù)據(jù)很多都集中在少數(shù)幾個數(shù)據(jù)頁上順序IO的優(yōu)勢也能體現(xiàn)出來。這套邏輯在分布式數(shù)據(jù)庫中體現(xiàn)得更徹底。節(jié)點之間的數(shù)據(jù)傳遞是要走網(wǎng)絡(luò)的網(wǎng)絡(luò)帶寬和延遲比本地內(nèi)存高很多個數(shù)量級。如果連接條件能被下推到各個存儲節(jié)點上讓每個節(jié)點先把本地的數(shù)據(jù)過濾一遍再傳回計算節(jié)點網(wǎng)絡(luò)傳輸量就能被壓到最小。我在使用ClickHouse處理寬表JOIN時發(fā)現(xiàn)同樣的關(guān)聯(lián)查詢能否在子查詢里先做過濾直接決定了查詢是秒出還是等半分鐘。因為ClickHouse的分布式子查詢下推如果生效每個分片只處理過濾后的一小塊數(shù)據(jù)匯總壓力小得多。2.3 下推如何反向影響JOIN順序與執(zhí)行策略連接條件下推的價值不止體現(xiàn)在數(shù)據(jù)量上它還會改變優(yōu)化器選擇連接順序的決策空間。最經(jīng)典的優(yōu)化原則是“小表驅(qū)動大表”但這里的“小表”指的不是表本身的物理容量而是經(jīng)過過濾條件壓縮后的“邏輯大小”。假設(shè)驅(qū)動表是大表但過濾后只剩1萬行被驅(qū)動表是小表但過濾條件很少所以仍有20萬行那顯然應(yīng)該讓前者做驅(qū)動表。連接條件下推正是把這種基于真實數(shù)據(jù)量的選擇權(quán)交還給優(yōu)化器。如果過濾條件沒有被推下去優(yōu)化器看到的全是表的原始尺寸它只能憑借基礎(chǔ)統(tǒng)計信息去猜做出的順序選擇自然容易跑偏。更關(guān)鍵的是過濾條件下推還會影響優(yōu)化器選擇哈希連接還是嵌套循環(huán)。數(shù)據(jù)量大到內(nèi)存放不下時優(yōu)化器大概率選哈希連接但當下推把數(shù)據(jù)壓到足夠小嵌套循環(huán)配合索引可能會更快。這種執(zhí)行策略的連鎖反應(yīng)就是為什么同一個SQL寫法下推不生效時性能差異會放大到幾十倍。3. 實操如何讓連接條件真正推下去3.1 復(fù)現(xiàn)一個慢SQL從執(zhí)行計劃里找證據(jù)我拿一個標準的三表場景來做演示。為了說明問題我在測試庫里建了三張表數(shù)據(jù)量分別是100萬、50萬、30萬。表和索引結(jié)構(gòu)如下CREATE TABLE orders ( id INT PRIMARY KEY, customer_id INT NOT NULL, order_no VARCHAR(64), created_at DATETIME, KEY idx_customer_id (customer_id), KEY idx_created_at (created_at) ); CREATE TABLE customers ( id INT PRIMARY KEY, customer_name VARCHAR(64), customer_level VARCHAR(16), KEY idx_level (customer_level) ); CREATE TABLE payments ( id INT PRIMARY KEY, order_id INT NOT NULL, pay_amount DECIMAL(12,2), KEY idx_order_id (order_id) );查詢目標不變查2024年以來的VIP客戶訂單及金額。我先看一眼EXPLAIN結(jié)果EXPLAIN SELECT o.order_no, c.customer_name, p.pay_amount FROM orders o JOIN customers c ON o.customer_id c.id JOIN payments p ON o.id p.order_id WHERE o.created_at 2024-01-01 AND c.customer_level VIP;MySQL 8.0的執(zhí)行計劃如果顯示orders表的rows估算為100000過濾掉了九成customers表rows估算為50000payments表的ref連接方式和filtered比例都合理那說明下推是生效的。但我看過的真實生產(chǎn)中這條SQL的執(zhí)行計劃經(jīng)常是orders表rows顯示為1000000customers表rows顯示為500000payments表顯示“全表掃描”訪問類型為ALL。這時候你就能確認連接條件下推沒有生效優(yōu)化器選擇了全量關(guān)聯(lián)再過濾的路徑。判斷執(zhí)行計劃是否下推成功重點看三處第一單表掃描階段是否出現(xiàn)Using where或Using index condition第二rows估算值是否接近過濾后的行數(shù)還是等于表總行數(shù)第三連接順序是否從最小結(jié)果集開始。如果沒有說明SQL寫法可能限制了優(yōu)化器的發(fā)揮空間。3.2 改寫SQL的三種姿勢讓優(yōu)化器聽你的話遇到下推失效時我的經(jīng)驗是可以從SQL結(jié)構(gòu)上做三種調(diào)整不需要動表結(jié)構(gòu)。第一種把過濾條件寫進子查詢提前壓縮表數(shù)據(jù)。這是最常用也最有效的方式SELECT o.order_no, c.customer_name, p.pay_amount FROM (SELECT * FROM orders WHERE created_at 2024-01-01) o JOIN (SELECT * FROM customers WHERE customer_level VIP) c ON o.customer_id c.id JOIN payments p ON o.id p.order_id;這種寫法直接告訴優(yōu)化器我在子查詢里已經(jīng)把orders和customers壓到最小你只需對壓縮后的結(jié)果做關(guān)聯(lián)。但需要注意MySQL 8.0里默認會把派生表合并到外層查詢?nèi)绻愕淖硬樵兝镉芯酆稀IMIT、DISTINCT合并可能會被阻止這時反而可能讓優(yōu)化器更糾結(jié)。我通常建議先用EXPLAIN驗證再看是否能再包一層。第二種把過濾條件寫在ON子句里而不是WHERE里。對INNER JOIN來說ON和WHERE的語義沒區(qū)別優(yōu)化器也會自行轉(zhuǎn)換但對LEFT JOIN或RIGHT JOIN條件寫在ON和WHERE里語義差別很大ON里的條件在關(guān)聯(lián)之前生效WHERE里的條件則在外連接完成后才過濾一個不留神就把外連接變成了內(nèi)連接。如果想讓左表的過濾條件提前壓縮左表數(shù)據(jù)寫在WHERE里沒問題如果想讓右表的數(shù)據(jù)參與關(guān)聯(lián)但只在關(guān)聯(lián)時過濾出符合條件的數(shù)據(jù)一定要寫在ON里。第三種用CTE表達“先壓縮后連接”的邏輯這在PostgreSQL里尤其好用WITH filtered_orders AS ( SELECT * FROM orders WHERE created_at 2024-01-01 ), filtered_customers AS ( SELECT * FROM customers WHERE customer_level VIP ) SELECT o.order_no, c.customer_name, p.pay_amount FROM filtered_orders o JOIN filtered_customers c ON o.customer_id c.id JOIN payments p ON o.id p.order_id;3.3 優(yōu)化器參數(shù)手動干預(yù)下推決策的開關(guān)SQL改寫解決的是“優(yōu)化器能不能看到過濾后的小表”的問題但有些時候優(yōu)化器看到了卻因為自己的成本估算模型不準確仍然做出了錯誤選擇。這時候就需要通過優(yōu)化器參數(shù)手動干預(yù)。在PostgreSQL里控制JOIN重寫和子查詢提升行為的參數(shù)主要是join_collapse_limit和from_collapse_limit。默認值是8意思是如果JOIN數(shù)量少于8優(yōu)化器可以自由地按成本重排連接順序。如果把這個值設(shè)成1優(yōu)化器就會嚴格按FROM子句從左到右的連接順序執(zhí)行不再做重排。在某些極端情況下比如連接條件寫在子查詢里但優(yōu)化器死活不肯下推我會把這個參數(shù)臨時調(diào)低讓優(yōu)化器老老實實按“先過濾、再連接”的順序來。注意這種調(diào)整在單條SQL級別就能做SET LOCAL join_collapse_limit 1;在MySQL 8.0里對應(yīng)的控制開關(guān)是optimizer_switch里的condition_pushdown和相關(guān)子查詢優(yōu)化項。derived_merge控制派生表合并derived_condition_pushdown控制條件要不要推入派生表。有時候我發(fā)現(xiàn)一個查詢外層條件明明能推入子查詢?nèi)ミ^濾數(shù)據(jù)但優(yōu)化器選擇了合并派生表之后再過濾搞得效果很差。這時可以關(guān)閉derived_mergeSET optimizer_switch derived_mergeoff;當然這屬于進階手段不建議全局修改最好在會話級別配合EXPLAIN一起調(diào)。全局亂改這些參數(shù)很容易讓其他正常SQL的執(zhí)行計劃也跑偏。4. 連接條件下推失效的常見坑與排查實錄4.1 隱式類型轉(zhuǎn)換下推的第一殺手我排查慢SQL這么多年最頭疼的就是隱式類型轉(zhuǎn)換導(dǎo)致的下推失效。執(zhí)行引擎在比較兩個不同類型的值時如果字段類型是VARCHAR傳入的是數(shù)字MySQL會把字段值全部轉(zhuǎn)成數(shù)字再做比較索引直接失效過濾條件自然也下推不下去。經(jīng)典表現(xiàn)是這種WHERE c.phone 13800138000如果phone字段是VARCHAR這條SQL就不會走索引執(zhí)行計劃里會看到Using where伴隨全表掃描。改成字符串寫法就能復(fù)用索引并讓條件下推WHERE c.phone 13800138000對于JOIN場景也是如此。兩表的關(guān)聯(lián)字段類型不一致比如A表customer_id是INTB表customer_id是VARCHAR等值JOIN必然產(chǎn)生隱式轉(zhuǎn)換連接條件無法下推到索引掃描執(zhí)行計劃就會異常難看。我處理過一個訂單系統(tǒng)主表的用戶ID是BIGINT用戶表的用戶ID被建成CHAR(11)兩表JOIN跑出十幾秒把用戶表ID字段的類型改成BIGINT之后SQL瞬間掉到幾十毫秒。遇到這種情況別急著加索引先查兩表字段類型是否一致。4.2 函數(shù)包裹列讓優(yōu)化器無從下手在WHERE條件里對列做函數(shù)運算也是下推失效的高頻原因。比如WHERE DATE(created_at) 2024-01-01這會讓created_at上的索引失去意義優(yōu)化器沒法把條件直接下推到索引掃描層。即使你不需要用索引這種寫法也會導(dǎo)致過濾動作必須在行讀取之后才能執(zhí)行存儲引擎層無法預(yù)先過濾。正確的做法是改寫為范圍條件WHERE created_at 2024-01-01 AND created_at 2024-01-02另一種常見情況是在關(guān)聯(lián)字段上套函數(shù)比如JOIN b ON DATE(a.created_at) DATE(b.created_at)。這種JOIN條件下推幾乎不可能因為優(yōu)化器無法利用任何索引做等值匹配。碰到這類需求我的建議是考慮在表里增加冗余的日期列并建索引而不是指望優(yōu)化器幫你把函數(shù)倒騰清楚。4.3 外連接中條件位置的陷阱LEFT JOIN場景下條件的放置位置非常講究。以如下SQL為例SELECT o.*, p.pay_amount FROM orders o LEFT JOIN payments p ON o.id p.order_id WHERE p.pay_amount 100;這個SQL有一個隱蔽問題WHERE條件p.pay_amount 100實際上會過濾掉那些沒有支付記錄的訂單行從而把LEFT JOIN悄悄變成了INNER JOIN的語義。從執(zhí)行計劃看優(yōu)化器確實會把p.pay_amount 100下推成對payments表的過濾但這不是你想要的語義。正確寫法是SELECT o.*, p.pay_amount FROM orders o LEFT JOIN payments p ON o.id p.order_id AND p.pay_amount 100;把條件放在ON里payments表先過濾LEFT JOIN語義才真實同時過濾條件也能被下推到payments表掃描層。這里我特別想提醒不要以為下推失效只是性能問題它還可能靜悄悄地改變業(yè)務(wù)邏輯結(jié)果。排查時如果發(fā)現(xiàn)同一條SQL在某個版本前后返回行數(shù)不一致要先看看是不是條件被優(yōu)化器挪了位置。4.4 優(yōu)化器估算偏差讓下推決策翻車即使SQL寫得很干凈、字段類型一致、沒有函數(shù)包裹優(yōu)化器仍有可能因為統(tǒng)計信息陳舊而下推失敗。最典型的情況是表的統(tǒng)計信息長時間沒有更新優(yōu)化器以為某個過濾條件能篩掉90%的行實際只能篩掉1%或者反過來于是它選了一個災(zāi)難性的執(zhí)行計劃。解決這類問題的第一步是刷新統(tǒng)計信息。在MySQL里是ANALYZE TABLE orders;在PostgreSQL里是ANALYZE orders;更新統(tǒng)計信息之后再看EXPLAIN的rows估算是否接近真實掃描行數(shù)。如果仍不準就該考慮連接順序固定或者改寫SQL。有時我也會直接使用HintMySQL 8.0支持SELECT /* JOIN_FIXED_ORDER() */ ...PostgreSQL需要安裝pg_hint_plan擴展然后可以寫SELECT /* Leading(c o p) */ ...Hints這招屬于最后的強行干預(yù)手段能用改寫SQL解決的問題我一般不會先上Hints因為Hints會把SQL限定死后續(xù)數(shù)據(jù)分布變化了原本的手動優(yōu)化可能變成新的瓶頸。5. 連接條件下推在分布式數(shù)據(jù)庫和OLAP場景中的延伸5.1 分布式場景為什么推下去比什么都重要單機數(shù)據(jù)庫里連接條件下推的收益主要體現(xiàn)在磁盤IO和CPU消耗上到了分布式數(shù)據(jù)庫收益就被放大了無數(shù)倍因為數(shù)據(jù)要跨節(jié)點傳輸。像ClickHouse、TiDB、Trino這類系統(tǒng)查詢計劃往往會把部分計算下推到存儲節(jié)點執(zhí)行。以TiDB為例它的優(yōu)化器會把能下推的算子封裝成Cop Task發(fā)送給存儲節(jié)點TiKV并行執(zhí)行其中最經(jīng)典的就是把過濾條件下推到TiKV的掃表階段這個動作在TiDB里被稱為“算子下推”。如果你在TiDB里做一個大表JOIN而關(guān)聯(lián)條件沒法下推那所有數(shù)據(jù)都要匯集到TiDB Server節(jié)點單點內(nèi)存和網(wǎng)絡(luò)帶寬瞬間爆炸查詢直接就沒了命。ClickHouse對JOIN的支持相對單薄但它的PREWHERE優(yōu)化和子查詢下推同樣值得關(guān)注。我個人經(jīng)驗是在ClickHouse里做JOIN時絕對要把過濾條件寫在子查詢里而不是直接寫在JOIN之后因為ClickHouse的優(yōu)化器不會像MySQL那樣大膽地做條件推導(dǎo)。另外ClickHouse的join_use_nulls設(shè)置和外連接關(guān)系密切一個條件位置不對結(jié)果集就可能出現(xiàn)意料之外的空值排查起來非常痛苦。5.2 分區(qū)裁剪另一種意義上的“下推”分布式數(shù)據(jù)倉庫還有一個和連接條件下推原理相似的優(yōu)化分區(qū)裁剪Partition Pruning。如果一張大表按天做分區(qū)你在查詢條件里寫了event_date 2024-01-01優(yōu)秀的優(yōu)化器會把條件下推到元數(shù)據(jù)層直接跳過其他999個分區(qū)文件只讀取1月1日那一個分區(qū)的數(shù)據(jù)。這個動作不是發(fā)生在存儲引擎過濾數(shù)據(jù)時而是發(fā)生在計劃生成階段但效果和連接條件下推一樣最大限度減少參與計算的數(shù)據(jù)量。在基于Trino或Spark SQL寫數(shù)據(jù)湖查詢時分區(qū)裁剪的實現(xiàn)依賴于分區(qū)的元數(shù)據(jù)和過濾條件的可識別性。如果條件里套了函數(shù)比如DATE_FORMAT(event_date, %Y-%m-%d) 2024-01-01裁剪直接就失效了分區(qū)目錄會被全部掃一遍。所以在寫OLAP查詢時我一直強調(diào)一個原則不要在分區(qū)列上套函數(shù)不要對分區(qū)列做類型轉(zhuǎn)換這是讓分區(qū)條件下推生效的最基本前提。5.3 主流數(shù)據(jù)庫的連接條件下推現(xiàn)狀對比很多人都以為連接條件下推是數(shù)據(jù)庫引擎默認就做得很好的事實際差別很大。目前主流數(shù)據(jù)庫對它的支持程度各有不同數(shù)據(jù)庫支持程度常見觸發(fā)方式典型失效場景MySQL 8.0較好索引連接、派生表合并隱式轉(zhuǎn)換、函數(shù)包裹列PostgreSQL很好子查詢提升、參數(shù)化路徑統(tǒng)計信息陳舊、JOIN數(shù)過多ClickHouse一般PREWHERE、子查詢下推JOIN后置過濾無法自動下推TiDB很好Cop Task算子下推關(guān)聯(lián)字段類型不一致Trino較好下推連接條件到分片分區(qū)列上套函數(shù)這張表可以當成一個排查方向的參考。如果你手里的引擎屬于“下推支持一般”的類型那SQL寫法的規(guī)范性就顯得格外重要不能指望系統(tǒng)幫你兜底。5.4 單機轉(zhuǎn)分布式時的自查建議從單機數(shù)據(jù)庫遷到分布式數(shù)據(jù)庫時最容易踩的坑就是沿用原來的SQL習(xí)慣以為“貴的查詢引擎會自動幫我優(yōu)化”。我的建議是遷移之前把每條核心SQL都拿出來重新審查一遍重點看三件事關(guān)聯(lián)字段類型是否完全一致過濾條件有沒有寫在分區(qū)列上JOIN順序是否明顯不合理。這些問題在單機時代可能只是慢那么幾百毫秒到了分布式架構(gòu)下一個小問題就可能放大成集群級別的事故。我在幫一個業(yè)務(wù)從MySQL遷到TiDB時就遇到過一個典型案例。原系統(tǒng)里一條訂單匯總SQL用MySQL跑大概是1.2秒遷移到TiDB后直接變成25秒。排查了很久發(fā)現(xiàn)問題是開發(fā)在兩張表的關(guān)聯(lián)字段上一邊用了BIGINT另一邊用了VARCHARMySQL優(yōu)化器會在內(nèi)部做隱式轉(zhuǎn)換后繼續(xù)嘗試用索引而TiDB的優(yōu)化器對這種情況的處理方式不同連接條件無法下推成Cop Task大量的關(guān)聯(lián)數(shù)據(jù)就只能在TiDB Server節(jié)點上處理。像這種問題表面上是SQL慢實際是類型不一致導(dǎo)致的條件下推失效。遷庫之前花了半天統(tǒng)一字段類型SQL恢復(fù)到1秒以內(nèi)。結(jié)尾一點個人體會如果你問我連接條件下推最核心的一句話是什么我會說數(shù)據(jù)庫優(yōu)化器在窮舉執(zhí)行策略時最需要你幫它的就是把過濾條件放在它一眼能看到的地方。我在實際排查中反復(fù)發(fā)現(xiàn)很多慢SQL的根源不是索引缺失、不是服務(wù)器配置低而是SQL寫法把優(yōu)化器的路堵死了——類型不匹配、函數(shù)包裹、條件寫在語義錯誤的位置每一條都在告訴優(yōu)化器“別想下推了”。所以我現(xiàn)在的習(xí)慣是任何核心查詢上線前先看執(zhí)行計劃再檢查關(guān)聯(lián)字段類型然后確認過濾條件是否落在表的掃描階段。這個習(xí)慣幫我避開了大量生產(chǎn)事故也讓我在處理別人的慢SQL時能第一時間抓住命門。如果你現(xiàn)在正有一條慢SQL查不出原因別急著加索引先把執(zhí)行計劃打開看看你的連接條件到底有沒有推下去。另外分享一個小技巧在排查這類問題時我習(xí)慣把EXPLAIN輸出的rows估算值和真實命中的行數(shù)做對比如果兩者差距超過10倍優(yōu)化器的成本模型大概率已經(jīng)被誤導(dǎo)下推效果也不會好這個時候優(yōu)先修復(fù)統(tǒng)計信息而不是繼續(xù)調(diào)SQL。這個細節(jié)很多DBA都不一定會告訴你。