行計(jì)劃：數(shù)據(jù)庫(kù)查詢優(yōu)化與慢SQL排查實(shí)戰(zhàn))
1. 為什么考完試就忘干凈了元組演算和域演算的真實(shí)用處我見(jiàn)過(guò)太多數(shù)據(jù)庫(kù)系統(tǒng)工程師考生把元組演算、域演算背得滾瓜爛熟考試一過(guò)就徹底拋到腦后。這個(gè)現(xiàn)象本身不奇怪——考試要的是公式推導(dǎo)和符號(hào)變換而日常開發(fā)面對(duì)的是SQL語(yǔ)句和慢查詢?nèi)罩緝烧呖雌饋?lái)完全不是一個(gè)世界的東西。但我想說(shuō)的是這些理論概念恰恰是理解數(shù)據(jù)庫(kù)底層邏輯的鑰匙尤其是當(dāng)你開始研究查詢優(yōu)化器、分析執(zhí)行計(jì)劃、排查慢SQL的時(shí)候你當(dāng)年背誦的那些公式會(huì)以另一種方式重新出現(xiàn)在你面前。先把這個(gè)話題攤開來(lái)說(shuō)。元組演算和域演算誕生于上世紀(jì)七十年代是關(guān)系模型創(chuàng)始人E.F.Codd在研究如何用數(shù)學(xué)語(yǔ)言描述數(shù)據(jù)庫(kù)查詢時(shí)提出的。它們和關(guān)系代數(shù)一樣是關(guān)系數(shù)據(jù)庫(kù)查詢語(yǔ)言的理論根基。SQL雖然看起來(lái)像是英文句子但它的語(yǔ)義基礎(chǔ)其實(shí)是元組演算的變體——SELECT ... FROM ... WHERE ...這種結(jié)構(gòu)本質(zhì)上就是在做元組變量的聲明和條件判斷。這塊內(nèi)容的核心價(jià)值在哪里我總結(jié)下來(lái)有三點(diǎn)。第一它是理解查詢優(yōu)化器推理過(guò)程的理論前提。優(yōu)化器要把SQL重寫成執(zhí)行計(jì)劃需要一套嚴(yán)密的等價(jià)變換規(guī)則這些規(guī)則的來(lái)源就是關(guān)系代數(shù)和演算的恒等變換。不懂這套底層邏輯看執(zhí)行計(jì)劃永遠(yuǎn)只能看個(gè)皮毛。第二它是判斷一條SQL是否可優(yōu)化、如何優(yōu)化的思維工具。比如一個(gè)子查詢能不能改寫成JOIN一個(gè)EXISTS能不能換成IN這些問(wèn)題的答案其實(shí)都隱藏在關(guān)系代數(shù)和演算的等價(jià)性里。第三它是你作為數(shù)據(jù)庫(kù)系統(tǒng)工程師區(qū)別于普通CRUD開發(fā)者的知識(shí)壁壘。面試聊到索引、執(zhí)行計(jì)劃、查詢重寫的時(shí)候深度立刻見(jiàn)分曉。這篇文章我就打算從元組演算和域演算的基本概念講起一路聊到它們和SQL的對(duì)應(yīng)關(guān)系再深入到查詢優(yōu)化器的工作機(jī)制最后落到真實(shí)的執(zhí)行計(jì)劃分析場(chǎng)景里。如果你正在備考數(shù)據(jù)庫(kù)系統(tǒng)工程師或者工作中經(jīng)常被慢查詢折磨這篇內(nèi)容應(yīng)該能幫上忙。2. 元組演算和域演算到底在說(shuō)什么從集合論到查詢公式2.1 先搞清楚研究對(duì)象元組、域和關(guān)系要理解元組演算和域演算先得把三個(gè)基礎(chǔ)概念厘清元組、域、關(guān)系。關(guān)系數(shù)據(jù)庫(kù)里的“關(guān)系”在數(shù)學(xué)上就是一組元組的集合。元組可以理解成一張表中的一行它是一組值的有序排列。域則更簡(jiǎn)單——它是元組中某個(gè)分量可能取值的集合。舉個(gè)例子一張學(xué)生表里有學(xué)號(hào)、姓名、年齡三個(gè)列那么每一行就是一個(gè)元組(2024001, 張三, 20)這個(gè)三元組就是元組的具體表現(xiàn)形式。而“學(xué)號(hào)”這一列所有可能的取值的集合就是這個(gè)屬性對(duì)應(yīng)的域。理解了這個(gè)基礎(chǔ)元組演算和域演算的區(qū)別就清晰了元組演算以“行”為基本單位做變量聲明和條件判斷域演算以“列的值”為基本單位。用大白話說(shuō)元組演算是“我一整行一整行地看”域演算是“我按每個(gè)單元格的值來(lái)判斷”。這個(gè)區(qū)別看似簡(jiǎn)單但它決定了兩種演算在表達(dá)能力上的差異也決定了它們?cè)诤髞?lái)數(shù)據(jù)庫(kù)實(shí)現(xiàn)中的命運(yùn)。實(shí)際關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)里SQL的語(yǔ)義實(shí)現(xiàn)更靠近元組演算而域演算則更多地出現(xiàn)在一些形式化驗(yàn)證和邏輯推導(dǎo)的場(chǎng)景中。所以很多教材會(huì)偏重元組演算這是有工程原因的。2.2 元組演算的基本形式存在量詞和全稱量詞元組演算的基本表達(dá)式長(zhǎng)得像這樣{t | P(t)}這個(gè)式子的意思是所有滿足條件P的元組t的集合。理解這個(gè)表達(dá)式是理解整個(gè)元組演算的關(guān)鍵。它其實(shí)用了一階謂詞邏輯的語(yǔ)言來(lái)定義集合——你給我一個(gè)條件我從整個(gè)關(guān)系集合里把所有符合條件的行挑出來(lái)。這里最讓初學(xué)者頭疼的是兩個(gè)量詞存在量詞?和全稱量詞?。存在量詞好理解意思是“存在這樣一行”全稱量詞麻煩一點(diǎn)意思是“對(duì)所有行都成立”。舉一個(gè)特別典型的例子。給定學(xué)生表S學(xué)號(hào), 姓名, 年齡, 系別和選課表SC學(xué)號(hào), 課程號(hào), 成績(jī)要查“選修了全部課程的學(xué)生姓名”。這個(gè)需求用自然語(yǔ)言說(shuō)很簡(jiǎn)單但用元組演算寫就稍微繞一點(diǎn){t[姓名] | S(t) ∧ (?u)(C(u) → (?v)(SC(v) ∧ v[學(xué)號(hào)]t[學(xué)號(hào)] ∧ v[課程號(hào)]u[課程號(hào)]))}這個(gè)式子的邏輯是我先找到學(xué)生元組t然后對(duì)所有課程元組u只要這門課程存在就必須存在一條選課記錄v把t和u關(guān)聯(lián)起來(lái)。外層是對(duì)所有學(xué)生遍歷內(nèi)層是對(duì)所有課程推導(dǎo)。如果某門課沒(méi)有對(duì)應(yīng)的選課記錄這個(gè)學(xué)生的元組就會(huì)被排除。這個(gè)例子考研、軟考、數(shù)據(jù)庫(kù)系統(tǒng)工程師考試都愛(ài)出。很多人考試時(shí)靠死記硬背這個(gè)模板通過(guò)但如果你真的理解了它的推理過(guò)程你會(huì)發(fā)現(xiàn)在“對(duì)所有課程都選了”這種業(yè)務(wù)需求的SQL實(shí)現(xiàn)中你會(huì)有更清晰的改寫思路——用NOT EXISTS還是用COUNT比較背后的邏輯基礎(chǔ)就在這里。2.3 域演算按值域說(shuō)話的另一種視角域演算的表達(dá)式長(zhǎng)這樣{x1, x2, ..., xn | P(x1, x2, ..., xn)}它聲明的是若干個(gè)域變量每個(gè)變量取各自域中的值然后通過(guò)條件P來(lái)約束這些變量的組合。不用聲明整行元組作為變量而是直接針對(duì)列的值做操作。域演算的一個(gè)經(jīng)典例子是“查年齡大于18歲的學(xué)生姓名”{姓名 | (?學(xué)號(hào))(?年齡)(?系別)(學(xué)生(學(xué)號(hào), 姓名, 年齡, 系別) ∧ 年齡 18)}這里的變量是學(xué)號(hào)、年齡、系別這些具體的值而不是一整行。它更像是用邏輯公式描述一張?zhí)摂M的結(jié)果表這一列放姓名那幾列是約束條件涉及的值大家組合起來(lái)。從查詢表達(dá)力的角度看域演算和元組演算是等價(jià)的——能用一個(gè)表達(dá)出來(lái)的查詢另一個(gè)也能表達(dá)。但認(rèn)知方式很不一樣元組演算比較接近“行掃描”的直覺(jué)域演算更接近“列投影”加“篩選”的認(rèn)知。有意思的是SQL的執(zhí)行計(jì)劃中投影操作和過(guò)濾操作確實(shí)是分開的域演算的這種拆分方式反而和物理執(zhí)行過(guò)程有著某種呼應(yīng)。給備考的朋友一個(gè)建議考試中域演算的題目不用摳太深掌握基本轉(zhuǎn)換規(guī)則即可。但元組演算請(qǐng)務(wù)必理解透因?yàn)镾QL的嵌套子查詢語(yǔ)義和它高度同構(gòu)。2.4 安全性問(wèn)題別讓查詢結(jié)果無(wú)窮大學(xué)演算還有一個(gè)繞不開的話題——安全性。由于演算用的是謂詞邏輯如果不加限制你完全可能寫出一個(gè)公式它的結(jié)果集合是無(wú)限的。舉個(gè)例子{t | ?S(t)}的意思是“所有不在學(xué)生表S里的元組的集合”。如果元組的定義域是無(wú)限的自然數(shù)集合這個(gè)查詢的結(jié)果也是無(wú)限的這在工程上毫無(wú)意義也不可能執(zhí)行。所以數(shù)據(jù)庫(kù)提供了“安全表達(dá)式”的概念一個(gè)演算表達(dá)式是安全的當(dāng)且僅當(dāng)它的所有可能結(jié)果都來(lái)自某個(gè)有限的域集合。實(shí)際操作中這個(gè)有限域集合通常來(lái)自關(guān)系實(shí)例中實(shí)際出現(xiàn)的值以及查詢自身用到的常量。SQL之所以是可執(zhí)行的查詢語(yǔ)言根源就在于它隱含了這樣的安全性約束。這事聽(tīng)著抽象但它在SQL中的影子很常見(jiàn)——為什么SQL的SELECT結(jié)果必須是有限的行數(shù)為什么子查詢里的NOT IN在遇到NULL時(shí)會(huì)有詭異的行為這些工程問(wèn)題追溯到底層都和安全語(yǔ)義有關(guān)。理解這些數(shù)學(xué)邊角能幫你更淡定地面對(duì)那些“玄學(xué)”SQL問(wèn)題。3. 演算到SQL的橋梁SEL/SELECT-WHERE結(jié)構(gòu)與查詢樹的推演3.1 SQL的語(yǔ)義核心是“謂詞約束元組投影”現(xiàn)在把視角從數(shù)學(xué)世界拉回工程世界。SQL的SELECT ... FROM ... WHERE ...結(jié)構(gòu)和元組演算的{t | P(t)}存在清晰的對(duì)應(yīng)關(guān)系FROM子句提供了元組遍歷的范圍WHERE子句扮演了謂詞P的角色SELECT子句則決定了最終保留哪些分量。這個(gè)對(duì)應(yīng)關(guān)系不是巧合SQL在設(shè)計(jì)時(shí)就是受了元組演算的啟發(fā)。SQL的創(chuàng)始人Donald Chamberlin和Raymond Boyce在1974年發(fā)表的論文里明確提到了關(guān)系演算對(duì)SEQUEL語(yǔ)言設(shè)計(jì)的影響。所以當(dāng)你寫一條SQL時(shí)其實(shí)你已經(jīng)在用元組演算的思維了只是平時(shí)沒(méi)人提醒你這一點(diǎn)。工程上的一個(gè)特定實(shí)現(xiàn)是SELselect算子和它的參數(shù)化形式。其實(shí)不一定要停留在某個(gè)特定數(shù)據(jù)庫(kù)產(chǎn)品的層面我們可以從通用角度理解一條SQL在執(zhí)行計(jì)劃層面會(huì)被拆成一系列邏輯算子包括掃描算子、過(guò)濾算子、投影算子、連接算子等。這些算子的排列組合形成了查詢樹而查詢樹是優(yōu)化器做邏輯等價(jià)變換的基礎(chǔ)。3.2 查詢樹的等價(jià)變換把公式變成可執(zhí)行的方案我舉個(gè)例子來(lái)說(shuō)明查詢樹推演的過(guò)程。假設(shè)有兩個(gè)表——訂單表O訂單號(hào), 客戶號(hào), 金額, 狀態(tài)和客戶表C客戶號(hào), 姓名, 城市?，F(xiàn)在要查“北京的客戶在2024年下的金額大于1000元的訂單”。SQL寫出來(lái)很直觀SELECT O.訂單號(hào), O.金額, C.姓名 FROM 訂單 O JOIN 客戶 C ON O.客戶號(hào) C.客戶號(hào) WHERE C.城市 北京 AND O.金額 1000 AND O.下單時(shí)間 BETWEEN 2024-01-01 AND 2024-12-31;在關(guān)系代數(shù)層面這條SQL對(duì)應(yīng)的查詢樹是先做訂單表掃描再做客戶表掃描然后做連接操作最后做選擇和投影。但問(wèn)題是這個(gè)執(zhí)行順序是不是最優(yōu)的優(yōu)化器會(huì)做一件關(guān)鍵的事——把可以提前做的過(guò)濾操作盡量下推。比如可以在掃描訂單表的時(shí)候就把金額1000和下單時(shí)間范圍的過(guò)濾條件直接套在表掃描之上只把符合條件的訂單行送入連接操作客戶表也一樣先把城市北京的客戶過(guò)濾出來(lái)。這樣進(jìn)入連接操作的數(shù)據(jù)量會(huì)大幅減少連接代價(jià)也隨之降低。在關(guān)系代數(shù)里這個(gè)操作的理論依據(jù)是選擇和連接的可交換性σ_條件(連接(A, B)) 等價(jià)于 連接(σ_條件A(A), σ_條件B(B))只要過(guò)濾條件只涉及A或只涉及B就可以安全地下推。3.3 一個(gè)反直覺(jué)的改寫案例EXISTS的演算本質(zhì)關(guān)于等價(jià)變換我分享一個(gè)特別有意思的真實(shí)案例。有一次我在優(yōu)化一條SQL業(yè)務(wù)場(chǎng)景是查“有訂單的客戶”。第一版SQL用的是IN子查詢SELECT C.客戶號(hào), C.姓名 FROM 客戶 C WHERE C.客戶號(hào) IN (SELECT O.客戶號(hào) FROM 訂單 O WHERE O.狀態(tài) 已完成);這條SQL在客戶表數(shù)據(jù)量十萬(wàn)、訂單表數(shù)據(jù)量百萬(wàn)的情況下執(zhí)行計(jì)劃出現(xiàn)了很糟糕的選擇——優(yōu)化器選擇了先全量掃描訂單子查詢?cè)賹?duì)客戶表做逐行探測(cè)。雖然訂單上建有客戶號(hào)索引但子查詢需要先過(guò)濾狀態(tài)已完成索引使用效率不高導(dǎo)致執(zhí)行時(shí)間飆到十幾秒。我當(dāng)時(shí)的處理是把IN改寫為EXISTSSELECT C.客戶號(hào), C.姓名 FROM 客戶 C WHERE EXISTS (SELECT 1 FROM 訂單 O WHERE O.客戶號(hào) C.客戶號(hào) AND O.狀態(tài) 已完成);改寫后優(yōu)化器可以更靈活地選擇執(zhí)行策略把客戶表作為驅(qū)動(dòng)表訂單表通過(guò)客戶號(hào)索引進(jìn)行關(guān)聯(lián)探測(cè)執(zhí)行時(shí)間從十幾秒降到了幾百毫秒。那么用演算怎么解釋這個(gè)改寫的正確性IN子查詢的語(yǔ)義是“客戶號(hào)在子查詢結(jié)果集合中”EXISTS的語(yǔ)義是“存在一條訂單記錄滿足條件”。用元組演算的語(yǔ)言來(lái)說(shuō)IN對(duì)應(yīng)的是外層元組的值是否屬于一個(gè)已確定的集合EXISTS對(duì)應(yīng)的是一個(gè)存在量詞推導(dǎo)。這兩個(gè)表達(dá)式的邏輯等價(jià)性正是謂詞邏輯中“元素屬于集合”和“存在量詞斷言”之間的同義變換。這個(gè)案例給我們的啟示是優(yōu)化器雖然能做很多自動(dòng)重寫但它依賴統(tǒng)計(jì)信息、索引結(jié)構(gòu)、代價(jià)模型并不總是智能到能識(shí)別所有等價(jià)的寫法。作為工程師理解底層演算語(yǔ)義能在關(guān)鍵時(shí)刻用人工改寫的方式幫優(yōu)化器一把。4. 優(yōu)化器內(nèi)部視角從邏輯樹到物理計(jì)劃的完整推理鏈路4.1 邏輯優(yōu)化和物理優(yōu)化其實(shí)是兩層不同的決策深入查詢優(yōu)化器內(nèi)部你會(huì)發(fā)現(xiàn)它做的事情遠(yuǎn)不止“等價(jià)重寫”這么簡(jiǎn)單。一個(gè)完整的查詢優(yōu)化流程可以拆成兩個(gè)層面邏輯優(yōu)化和物理優(yōu)化。邏輯優(yōu)化是在不改變查詢語(yǔ)義的前提下對(duì)查詢樹做結(jié)構(gòu)性的等價(jià)變換。常見(jiàn)的手法包括謂詞下推把過(guò)濾盡可能提前、子查詢展開把子查詢改寫成連接、連接重排序決定多表連接的先后順序、視圖合并把視圖的定義并入主查詢。這些變換的理論基礎(chǔ)就是前面說(shuō)的關(guān)系代數(shù)和演算的恒等性。物理優(yōu)化則是在邏輯計(jì)劃確定之后決定每一具體操作的實(shí)現(xiàn)方式表的訪問(wèn)路徑是走全表掃描還是索引掃描連接算法是用嵌套循環(huán)、哈希連接還是排序合并是否需要額外的排序操作并發(fā)執(zhí)行的程度如何設(shè)定這一層依賴的是數(shù)據(jù)庫(kù)系統(tǒng)內(nèi)部的代價(jià)模型、統(tǒng)計(jì)信息和物理存儲(chǔ)結(jié)構(gòu)。打個(gè)比喻邏輯優(yōu)化像是你在規(guī)劃旅行路線時(shí)決定“先去北京再去上海還是先去上海再去北京”物理優(yōu)化則像是決定“這兩座城市之間坐高鐵還是飛機(jī)”。前者關(guān)注的是步驟的先后和內(nèi)容后者關(guān)注的是每一步的執(zhí)行方式。4.2 代價(jià)模型的兩個(gè)核心組件基數(shù)估計(jì)和成本公式理解了這兩層優(yōu)化之后我想帶你看看優(yōu)化器做決策的最關(guān)鍵依據(jù)——代價(jià)模型。絕大多數(shù)現(xiàn)代數(shù)據(jù)庫(kù)的代價(jià)模型都包含兩個(gè)核心組件基數(shù)估計(jì)和成本公式?；鶖?shù)估計(jì)是估算某個(gè)中間結(jié)果集有多少行成本公式則是根據(jù)基數(shù)估算、數(shù)據(jù)分布、物理存儲(chǔ)特性來(lái)算一個(gè)操作的代價(jià)數(shù)值。優(yōu)化器會(huì)枚舉多個(gè)可能的計(jì)劃用代價(jià)模型估算每個(gè)計(jì)劃的總代價(jià)然后選代價(jià)最小的那一個(gè)?；鶖?shù)估計(jì)的難度遠(yuǎn)超想象它需要統(tǒng)計(jì)信息、數(shù)據(jù)分布假設(shè)通常是均勻分布或直方圖、列之間有相關(guān)性假設(shè)。當(dāng)統(tǒng)計(jì)信息過(guò)期、數(shù)據(jù)分布不均勻基數(shù)估計(jì)的結(jié)果會(huì)產(chǎn)生數(shù)量級(jí)偏差優(yōu)化器就會(huì)做出災(zāi)難性的執(zhí)行計(jì)劃選擇。這也是為什么實(shí)際運(yùn)維中要定期做統(tǒng)計(jì)信息更新比如ANALYZE、UPDATE STATISTICS以及為什么一些簡(jiǎn)單的SQL會(huì)莫名奇妙地走錯(cuò)執(zhí)行計(jì)劃。成本公式的細(xì)節(jié)因數(shù)據(jù)庫(kù)產(chǎn)品而異但大框架非常相似。對(duì)全表掃描來(lái)說(shuō)代價(jià)和表的總行數(shù)、塊數(shù)成正比對(duì)索引掃描來(lái)說(shuō)代價(jià)和選擇度、索引層數(shù)、回表次數(shù)相關(guān)對(duì)連接操作來(lái)說(shuō)代價(jià)取決于驅(qū)動(dòng)表行數(shù)、被驅(qū)動(dòng)表探測(cè)代價(jià)、內(nèi)存可用量等。理解這些你才能讀懂執(zhí)行計(jì)劃里那些數(shù)值的含義。4.3 統(tǒng)計(jì)信息對(duì)執(zhí)行計(jì)劃的影響一個(gè)被忽視的殺手我在一線工作中遇到過(guò)太多因?yàn)榻y(tǒng)計(jì)信息問(wèn)題導(dǎo)致的性能事故。有一個(gè)印象很深的例子某業(yè)務(wù)表的數(shù)據(jù)量從十萬(wàn)漲到五千萬(wàn)但因?yàn)樽詣?dòng)統(tǒng)計(jì)信息的閾值設(shè)置不當(dāng)分區(qū)級(jí)的統(tǒng)計(jì)信息沒(méi)有及時(shí)更新優(yōu)化器以為這張表還是十萬(wàn)行于是選擇了一個(gè)對(duì)小表友好的連接順序——大表驅(qū)動(dòng)小表。結(jié)果執(zhí)行計(jì)劃的實(shí)際運(yùn)行時(shí)間從幾十毫秒膨脹到十幾分鐘。當(dāng)時(shí)排查的完整鏈路是這樣的慢查詢?nèi)罩静蹲降揭粭lSQL的執(zhí)行時(shí)間異常拉長(zhǎng)平時(shí)幾毫秒最近穩(wěn)定在十幾分鐘。用EXPLAIN看執(zhí)行計(jì)劃發(fā)現(xiàn)連接順序明顯反直覺(jué)大表作為驅(qū)動(dòng)表小表作為被驅(qū)動(dòng)表。查看統(tǒng)計(jì)信息刷新時(shí)間發(fā)現(xiàn)該表最近一次ANALYZE是在三個(gè)月前當(dāng)時(shí)數(shù)據(jù)量確實(shí)是十萬(wàn)行。手動(dòng)執(zhí)行ANALYZE強(qiáng)制刷新統(tǒng)計(jì)信息。再看執(zhí)行計(jì)劃連接順序已經(jīng)糾正SQL執(zhí)行時(shí)間恢復(fù)到毫秒級(jí)。這個(gè)案例里沒(méi)有任何SQL寫法的問(wèn)題純粹是統(tǒng)計(jì)信息滯后導(dǎo)致的優(yōu)化器判斷失誤。這個(gè)教訓(xùn)我后來(lái)在團(tuán)隊(duì)里反復(fù)強(qiáng)調(diào)遇到SQL性能突變第一件事永遠(yuǎn)先確認(rèn)統(tǒng)計(jì)信息是否新鮮再去懷疑SQL本身的問(wèn)題。4.4 連接順序選擇的天花板當(dāng)優(yōu)化器也無(wú)能為力連接順序的選擇是查詢優(yōu)化中最難的問(wèn)題之一因?yàn)閚個(gè)表的連接順序有n!種可能每一種還需要考慮對(duì)應(yīng)的連接算法組合。對(duì)于7表或者10表以上的連接全枚舉的代價(jià)就已經(jīng)高到不可接受了所以現(xiàn)代優(yōu)化器幾乎都采用動(dòng)態(tài)規(guī)劃和啟發(fā)式搜索相結(jié)合的方式。但這帶來(lái)一個(gè)新的問(wèn)題——啟發(fā)式策略在多數(shù)情況下表現(xiàn)良好但在極少數(shù)邊界場(chǎng)景下會(huì)選出明顯次優(yōu)的計(jì)劃。工程師對(duì)這類查詢的處理方式是分析連接關(guān)系圖譜找出最小、最有選擇性的子集先行連接再逐級(jí)擴(kuò)展或者干脆用查詢提示hint鎖定連接順序。我在給客戶做性能優(yōu)化時(shí)有一條心得對(duì)復(fù)雜查詢與其讓優(yōu)化器做全空間搜索不如人工拆解。把一個(gè)大查詢拆成幾個(gè)中間結(jié)果表每一步都確保執(zhí)行計(jì)劃可控。這種做法的代價(jià)是額外存儲(chǔ)和多一步ETL但換來(lái)的是執(zhí)行計(jì)劃的穩(wěn)定性和可預(yù)測(cè)性。對(duì)生產(chǎn)環(huán)境的穩(wěn)定性要求而言這是值得的。5. 實(shí)戰(zhàn)看執(zhí)行計(jì)劃以EXPLAIN輸出為例的慢查詢定位方法論5.1 執(zhí)行計(jì)劃閱讀的基本順序從嵌套最深到最外層理論知識(shí)說(shuō)了一大堆現(xiàn)在落到最實(shí)務(wù)的部分——怎么通過(guò)執(zhí)行計(jì)劃定位和修復(fù)慢查詢。一個(gè)執(zhí)行計(jì)劃通常以樹狀結(jié)構(gòu)呈現(xiàn)無(wú)論是MySQL的EXPLAIN輸出、PostgreSQL的EXPLAIN還是Oracle的執(zhí)行計(jì)劃輸出核心邏輯是一致的。閱讀執(zhí)行計(jì)劃的正確順序其實(shí)是自內(nèi)向外、自底向上先看每個(gè)表中訪問(wèn)路徑的代價(jià)再看連接操作是不是按合理順序執(zhí)行最后看最外層的結(jié)果集構(gòu)造是否有不必要的開銷。很多新手犯的錯(cuò)誤是一上來(lái)就盯著第一個(gè)節(jié)點(diǎn)看這容易漏掉關(guān)鍵問(wèn)題。我從一次具體的性能排查講起。有一個(gè)場(chǎng)景論壇系統(tǒng)的帖子列表頁(yè)需要展示每個(gè)帖子的標(biāo)題、作者名、最新回復(fù)人和回復(fù)時(shí)間。SQL長(zhǎng)這樣SELECT p.title, u.name AS author_name, r.name AS reply_user, r.reply_time FROM post p JOIN user u ON p.author_id u.id LEFT JOIN LATERAL (SELECT ru.name, r.reply_time FROM reply r JOIN user ru ON r.user_id ru.id WHERE r.post_id p.id ORDER BY r.reply_time DESC LIMIT 1) r ON TRUE WHERE p.status 1 ORDER BY p.update_time DESC LIMIT 20;這條SQL在數(shù)據(jù)量上去之后變得很慢壓測(cè)時(shí)平均響應(yīng)時(shí)間達(dá)到3秒。先看執(zhí)行計(jì)劃的關(guān)鍵部分。5.2 從執(zhí)行計(jì)劃里讀出的三個(gè)問(wèn)題EXPLAIN ANALYZE輸出顯示外層post表掃描走了索引idx_post_status_update沒(méi)問(wèn)題但內(nèi)層LATERAL子查詢對(duì)reply表的探測(cè)居然走了全表掃描。為什么原來(lái)reply表的post_id列上雖然有索引但索引的統(tǒng)計(jì)信息顯示該列的重復(fù)值極多優(yōu)化器估算走索引的回表代價(jià)高于全表掃描。問(wèn)題出在回復(fù)表和帖子表的數(shù)據(jù)傾斜——少量熱帖集中了大量回復(fù)導(dǎo)致優(yōu)化器做了錯(cuò)誤的基數(shù)估計(jì)。第二個(gè)問(wèn)題是ORDER BY p.update_time DESC要求排序而執(zhí)行計(jì)劃的排序節(jié)點(diǎn)使用了臨時(shí)文件排序filesort內(nèi)存排序緩沖區(qū)設(shè)置過(guò)小導(dǎo)致磁盤排序。第三個(gè)問(wèn)題是LEFT JOIN LATERAL在PostgreSQL里是逐行調(diào)用子查詢這在邏輯上是串行的無(wú)法并行化。當(dāng)驅(qū)動(dòng)表需要掃描的行數(shù)很多時(shí)串行代價(jià)會(huì)被線性放大。5.3 修復(fù)方案和實(shí)施步驟針對(duì)這三個(gè)問(wèn)題我當(dāng)時(shí)的處理方案如下第一步修正基數(shù)估計(jì)——重新采集reply表的統(tǒng)計(jì)信息并且給post_id這一列建立覆蓋索引idx_reply_post_user_time (post_id, user_id, reply_time)。覆蓋索引可以讓子查詢中的過(guò)濾和排序都在索引層面完成不需要回表。第二步調(diào)整排序參數(shù)——把排序緩沖區(qū)從默認(rèn)的2MB調(diào)整到32MB同時(shí)檢查是否可以通過(guò)調(diào)整索引讓結(jié)果天然有序。最終是在post表的update_time列和status列上建立了組合索引讓外層查詢的過(guò)濾和排序在同一個(gè)索引掃描中完成直接消除了排序節(jié)點(diǎn)。第三步重寫LATERAL子查詢。這里我能想到的最佳實(shí)踐是如果熱帖的回復(fù)數(shù)量本身就不多可以接受一筆額外的預(yù)聚合如果熱帖集中更適合的方式是把“每個(gè)帖子最近回復(fù)”這個(gè)邏輯做成物化結(jié)果。事實(shí)上在很多高并發(fā)社區(qū)場(chǎng)景里這類“最新回復(fù)”數(shù)據(jù)都是異步寫入緩存或單獨(dú)匯總表的實(shí)時(shí)跑SQL反而是不合理的架構(gòu)。優(yōu)化后的執(zhí)行計(jì)劃里三個(gè)性能瓶頸全部消除SQL平均響應(yīng)時(shí)間從3秒降到80毫秒。這個(gè)案例的典型意義在于它同時(shí)涉及了統(tǒng)計(jì)信息、索引設(shè)計(jì)、參數(shù)配置、SQL結(jié)構(gòu)四個(gè)維度而這些恰恰是查詢優(yōu)化中最常見(jiàn)的四個(gè)切入點(diǎn)。提示看執(zhí)行計(jì)劃時(shí)關(guān)注三種特定的標(biāo)志性字段——filtered比例過(guò)低說(shuō)明索引選擇性差、filesort或sort節(jié)點(diǎn)出現(xiàn)說(shuō)明排序無(wú)法利用索引、臨時(shí)表出現(xiàn)說(shuō)明結(jié)果集產(chǎn)生中間落盤。這三個(gè)信號(hào)基本覆蓋了90%的慢查詢根因。5.4 常見(jiàn)的執(zhí)行計(jì)劃誤讀和我踩過(guò)的坑執(zhí)行計(jì)劃閱讀的坑也值得專門說(shuō)一說(shuō)。第一個(gè)坑把節(jié)點(diǎn)的輸出行數(shù)當(dāng)成實(shí)際行數(shù)。在執(zhí)行計(jì)劃中節(jié)點(diǎn)輸出行數(shù)是優(yōu)化器的基數(shù)估計(jì)值而不是實(shí)際執(zhí)行行數(shù)。如果想看實(shí)際行數(shù)要用EXPLAIN ANALYZE或者EXPLAIN (ANALYZE, BUFFERS)它會(huì)真實(shí)執(zhí)行查詢并回傳實(shí)際行數(shù)和實(shí)際耗時(shí)。只讀估算行數(shù)很容易被誤導(dǎo)。第二個(gè)坑忽略緩沖BUFFERS信息。很多執(zhí)行計(jì)劃的慢不是慢在計(jì)算而是慢在I/O。BUFFERS字段能告訴你這個(gè)節(jié)點(diǎn)訪問(wèn)了多少個(gè)數(shù)據(jù)塊其中多少是命中了共享緩沖區(qū)的。如果某個(gè)節(jié)點(diǎn)讀取的塊數(shù)非常多且命中率低說(shuō)明存在嚴(yán)重的隨機(jī)I/O需要通過(guò)索引調(diào)整來(lái)訪問(wèn)更少的數(shù)據(jù)塊。第三個(gè)坑直接把執(zhí)行計(jì)劃中的總代價(jià)拿來(lái)排序比較。代價(jià)數(shù)值是相對(duì)的不同數(shù)據(jù)庫(kù)、不同版本、不同參數(shù)下的代價(jià)基準(zhǔn)都不一樣。我自己更習(xí)慣的做法是看計(jì)劃結(jié)構(gòu)是否符合直覺(jué)——有沒(méi)有哪個(gè)節(jié)點(diǎn)做了不合理的全表掃描有沒(méi)有多表連接的驅(qū)動(dòng)順序反了有沒(méi)有該走索引卻走了掃描。結(jié)構(gòu)對(duì)加上實(shí)際耗時(shí)驗(yàn)證比糾結(jié)具體數(shù)值更可靠。6. 從演算到現(xiàn)代場(chǎng)景枚舉元組、C#值元組解構(gòu)和畢達(dá)哥拉斯三元組的視角6.1 枚舉元組當(dāng)“元組”遇上現(xiàn)代編程語(yǔ)言聊了很多數(shù)據(jù)庫(kù)系統(tǒng)的內(nèi)容我想再擴(kuò)展一下“元組”這個(gè)詞在現(xiàn)代編程中的含義因?yàn)檫@個(gè)概念對(duì)數(shù)據(jù)庫(kù)工程師來(lái)說(shuō)既熟悉又陌生。在C#、Python、Rust等現(xiàn)代編程語(yǔ)言中元組tuple是一種輕量級(jí)的數(shù)據(jù)結(jié)構(gòu)用于打包一組異構(gòu)的值。C#從7.0開始引入了值元組ValueTuple和解構(gòu)語(yǔ)法這使得開發(fā)者可以這樣寫var (name, age) GetUserInfo(userId); Console.WriteLine(${name} is {age} years old.);這種語(yǔ)法本質(zhì)上是在做元組的解構(gòu)——把一個(gè)元組變量按位置拆成多個(gè)命名變量。這恰好呼應(yīng)了域演算的思維按域變量取值而不是把整個(gè)元組作為一個(gè)整體去操作。對(duì)數(shù)據(jù)庫(kù)開發(fā)者來(lái)說(shuō)理解現(xiàn)代語(yǔ)言中的元組操作有一個(gè)實(shí)際好處當(dāng)你編寫ORM查詢或者做DTO映射時(shí)你實(shí)際上是在關(guān)系的元組和編程語(yǔ)言的元組之間做翻譯。批量枚舉元組、按位置解構(gòu)、按屬性命名這些操作背后的思維模型和關(guān)系數(shù)據(jù)庫(kù)的行列模型是同構(gòu)的。6.2 畢達(dá)哥拉斯三元組一個(gè)經(jīng)典的域演算思維練習(xí)題“noj畢達(dá)哥拉斯3元組”這個(gè)熱搜詞也很有意思。畢達(dá)哥拉斯三元組指的是滿足a2 b2 c2的三個(gè)正整數(shù)比如(3, 4, 5)。用這個(gè)例子來(lái)做域演算的思維練習(xí)特別合適因?yàn)樗枰懵暶魅齻€(gè)域變量然后描述它們之間的約束關(guān)系。用域演算來(lái)表達(dá)“找出所有畢達(dá)哥拉斯三元組”{(a, b, c) | a ∈ N ∧ b ∈ N ∧ c ∈ N ∧ a2 b2 c2 ∧ 1 ≤ a b c ≤ 100}這個(gè)公式本質(zhì)上是一個(gè)約束搜索問(wèn)題的聲明式描述。你聲明三個(gè)整數(shù)變量給出取值范圍和約束條件剩下的交給執(zhí)行器去做。這正好對(duì)應(yīng)了SQL中一個(gè)經(jīng)典問(wèn)題的寫法——生成三個(gè)范圍笛卡爾積后做篩選WITH numbers AS (SELECT generate_series(1, 100) AS n) SELECT a.n AS a, b.n AS b, c.n AS c FROM numbers a, numbers b, numbers c WHERE a.n b.n AND b.n c.n AND a.n * a.n b.n * b.n c.n * c.n;這個(gè)寫法雖然直觀但性能算不上好——范圍小時(shí)還能接受范圍一旦擴(kuò)大笛卡爾積的規(guī)模就是O(n3)優(yōu)化器也無(wú)法把這種約束轉(zhuǎn)換成索引友好的計(jì)劃。工程上的處理方式通常是縮小搜索范圍、用數(shù)學(xué)邊界剪枝、或者事先生成緩存表。這個(gè)例子恰好揭示了聲明式語(yǔ)言的一個(gè)根本矛盾表達(dá)簡(jiǎn)潔不等于執(zhí)行高效優(yōu)化器能做的重寫是有限度的。6.3 數(shù)據(jù)庫(kù)查詢優(yōu)化器在AI和現(xiàn)代數(shù)據(jù)棧中的角色變遷把話題拉回查詢優(yōu)化器本身。在現(xiàn)代數(shù)據(jù)棧里查詢優(yōu)化器早已不局限于傳統(tǒng)關(guān)系型數(shù)據(jù)庫(kù)。Spark SQL、Presto/Trino、ClickHouse這些大數(shù)據(jù)引擎都有自己的一套查詢優(yōu)化策略但底層依然是在做邏輯重寫和物理計(jì)劃選擇。它們面對(duì)的場(chǎng)景更極端——數(shù)據(jù)規(guī)模更大數(shù)據(jù)源更異構(gòu)查詢模式更多樣。值得注意的是近年來(lái)業(yè)界在探索用機(jī)器學(xué)習(xí)技術(shù)改進(jìn)基數(shù)估計(jì)和代價(jià)模型。比如把統(tǒng)計(jì)信息丟給神經(jīng)網(wǎng)絡(luò)去學(xué)習(xí)數(shù)據(jù)分布或者用強(qiáng)化學(xué)習(xí)來(lái)決定連接順序。這些嘗試的方向是對(duì)的但受限于訓(xùn)練數(shù)據(jù)獲取成本、模型解釋性和推理延遲離大規(guī)模落地還有距離。對(duì)一線工程師來(lái)說(shuō)與其等待優(yōu)化器變得更智能不如更勤奮地理解它現(xiàn)有的決策邏輯。在當(dāng)前這個(gè)數(shù)據(jù)環(huán)境下有另一種趨勢(shì)很值得關(guān)注很多團(tuán)隊(duì)開始主動(dòng)繞過(guò)通用優(yōu)化器用物化視圖、預(yù)計(jì)算、增量更新等方式來(lái)回避“查詢時(shí)優(yōu)化”的問(wèn)題。這其實(shí)是一種務(wù)實(shí)的工程折中——既然查詢優(yōu)化器在復(fù)雜場(chǎng)景下難以做到最優(yōu)不如把復(fù)雜計(jì)算挪到寫入端或離線批處理端。能用預(yù)計(jì)算解決的查詢不要在查詢時(shí)挑戰(zhàn)優(yōu)化器。7. 備考復(fù)習(xí)與工作實(shí)踐的結(jié)合路徑我的個(gè)人操作經(jīng)驗(yàn)聊了這么多理論、工程和案例最后分享一些實(shí)際的備考和工作經(jīng)驗(yàn)。如果你是準(zhǔn)備數(shù)據(jù)庫(kù)系統(tǒng)工程師考試的考生我的建議是不要只背符號(hào)公式也不要完全放棄理論。把所有演算表達(dá)式和關(guān)系代數(shù)表達(dá)式翻譯成SQL再翻譯回表達(dá)式來(lái)回做幾遍。這個(gè)過(guò)程的收益遠(yuǎn)超你的想象——它讓你建立的是語(yǔ)義層面的等價(jià)關(guān)系網(wǎng)絡(luò)而考試題考察的恰好就是這種等價(jià)變換能力。我當(dāng)初備考時(shí)的一個(gè)具體做法是把歷年真題里的關(guān)系代數(shù)、元組演算、SQL三者的互轉(zhuǎn)題目自己整理成一張對(duì)應(yīng)表。每種查詢模式選擇、投影、連接、分組、去重、嵌套都寫清楚三種表達(dá)方式的對(duì)應(yīng)規(guī)則。復(fù)習(xí)效果非常好而且這份對(duì)應(yīng)表在后來(lái)工作中分析SQL執(zhí)行計(jì)劃時(shí)依然能派上用場(chǎng)。工作中建議重點(diǎn)掌握幾個(gè)實(shí)操技能EXPLAIN的深度使用、統(tǒng)計(jì)信息的手動(dòng)管理、覆蓋索引的設(shè)計(jì)原則、子查詢和連接的手工改寫。這些技能在慢查詢排查中的實(shí)用性幾乎是日常性的。我給自己團(tuán)隊(duì)定的底線是每個(gè)人都能在十分鐘內(nèi)定位一條慢SQL的根因并給出至少兩種優(yōu)化思路。能做到這一點(diǎn)數(shù)據(jù)庫(kù)系統(tǒng)工程師這個(gè)頭銜才算真正名副其實(shí)。最后再分享一個(gè)小技巧。每次優(yōu)化完一條SQL我會(huì)把優(yōu)化前后的SQL和執(zhí)行計(jì)劃的對(duì)比截圖保存下來(lái)附帶一段文字說(shuō)明根因。半年下來(lái)就是一個(gè)非常有價(jià)值的案例庫(kù)。下次有人問(wèn)“為什么這條SQL變慢了”直接翻案例庫(kù)比重新排查一遍高效太多。這種積累方式對(duì)個(gè)人成長(zhǎng)和團(tuán)隊(duì)沉淀都有好處。 從元組演算到執(zhí)行計(jì)劃：數(shù)據(jù)庫(kù)查詢優(yōu)化與慢SQL排查實(shí)戰(zhàn)
1. 為什么考完試就忘干凈了元組演算和域演算的真實(shí)用處我見(jiàn)過(guò)太多數(shù)據(jù)庫(kù)系統(tǒng)工程師考生把元組演算、域演算背得滾瓜爛熟考試一過(guò)就徹底拋到腦后。這個(gè)現(xiàn)象本身不奇怪——考試要的是公式推導(dǎo)和符號(hào)變換而日常開發(fā)面對(duì)的是SQL語(yǔ)句和慢查詢?nèi)罩緝烧呖雌饋?lái)完全不是一個(gè)世界的東西。但我想說(shuō)的是這些理論概念恰恰是理解數(shù)據(jù)庫(kù)底層邏輯的鑰匙尤其是當(dāng)你開始研究查詢優(yōu)化器、分析執(zhí)行計(jì)劃、排查慢SQL的時(shí)候你當(dāng)年背誦的那些公式會(huì)以另一種方式重新出現(xiàn)在你面前。先把這個(gè)話題攤開來(lái)說(shuō)。元組演算和域演算誕生于上世紀(jì)七十年代是關(guān)系模型創(chuàng)始人E.F.Codd在研究如何用數(shù)學(xué)語(yǔ)言描述數(shù)據(jù)庫(kù)查詢時(shí)提出的。它們和關(guān)系代數(shù)一樣是關(guān)系數(shù)據(jù)庫(kù)查詢語(yǔ)言的理論根基。SQL雖然看起來(lái)像是英文句子但它的語(yǔ)義基礎(chǔ)其實(shí)是元組演算的變體——SELECT ... FROM ... WHERE ...這種結(jié)構(gòu)本質(zhì)上就是在做元組變量的聲明和條件判斷。這塊內(nèi)容的核心價(jià)值在哪里我總結(jié)下來(lái)有三點(diǎn)。第一它是理解查詢優(yōu)化器推理過(guò)程的理論前提。優(yōu)化器要把SQL重寫成執(zhí)行計(jì)劃需要一套嚴(yán)密的等價(jià)變換規(guī)則這些規(guī)則的來(lái)源就是關(guān)系代數(shù)和演算的恒等變換。不懂這套底層邏輯看執(zhí)行計(jì)劃永遠(yuǎn)只能看個(gè)皮毛。第二它是判斷一條SQL是否可優(yōu)化、如何優(yōu)化的思維工具。比如一個(gè)子查詢能不能改寫成JOIN一個(gè)EXISTS能不能換成IN這些問(wèn)題的答案其實(shí)都隱藏在關(guān)系代數(shù)和演算的等價(jià)性里。第三它是你作為數(shù)據(jù)庫(kù)系統(tǒng)工程師區(qū)別于普通CRUD開發(fā)者的知識(shí)壁壘。面試聊到索引、執(zhí)行計(jì)劃、查詢重寫的時(shí)候深度立刻見(jiàn)分曉。這篇文章我就打算從元組演算和域演算的基本概念講起一路聊到它們和SQL的對(duì)應(yīng)關(guān)系再深入到查詢優(yōu)化器的工作機(jī)制最后落到真實(shí)的執(zhí)行計(jì)劃分析場(chǎng)景里。如果你正在備考數(shù)據(jù)庫(kù)系統(tǒng)工程師或者工作中經(jīng)常被慢查詢折磨這篇內(nèi)容應(yīng)該能幫上忙。2. 元組演算和域演算到底在說(shuō)什么從集合論到查詢公式2.1 先搞清楚研究對(duì)象元組、域和關(guān)系要理解元組演算和域演算先得把三個(gè)基礎(chǔ)概念厘清元組、域、關(guān)系。關(guān)系數(shù)據(jù)庫(kù)里的“關(guān)系”在數(shù)學(xué)上就是一組元組的集合。元組可以理解成一張表中的一行它是一組值的有序排列。域則更簡(jiǎn)單——它是元組中某個(gè)分量可能取值的集合。舉個(gè)例子一張學(xué)生表里有學(xué)號(hào)、姓名、年齡三個(gè)列那么每一行就是一個(gè)元組(2024001, 張三, 20)這個(gè)三元組就是元組的具體表現(xiàn)形式。而“學(xué)號(hào)”這一列所有可能的取值的集合就是這個(gè)屬性對(duì)應(yīng)的域。理解了這個(gè)基礎(chǔ)元組演算和域演算的區(qū)別就清晰了元組演算以“行”為基本單位做變量聲明和條件判斷域演算以“列的值”為基本單位。用大白話說(shuō)元組演算是“我一整行一整行地看”域演算是“我按每個(gè)單元格的值來(lái)判斷”。這個(gè)區(qū)別看似簡(jiǎn)單但它決定了兩種演算在表達(dá)能力上的差異也決定了它們?cè)诤髞?lái)數(shù)據(jù)庫(kù)實(shí)現(xiàn)中的命運(yùn)。實(shí)際關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)里SQL的語(yǔ)義實(shí)現(xiàn)更靠近元組演算而域演算則更多地出現(xiàn)在一些形式化驗(yàn)證和邏輯推導(dǎo)的場(chǎng)景中。所以很多教材會(huì)偏重元組演算這是有工程原因的。2.2 元組演算的基本形式存在量詞和全稱量詞元組演算的基本表達(dá)式長(zhǎng)得像這樣{t | P(t)}這個(gè)式子的意思是所有滿足條件P的元組t的集合。理解這個(gè)表達(dá)式是理解整個(gè)元組演算的關(guān)鍵。它其實(shí)用了一階謂詞邏輯的語(yǔ)言來(lái)定義集合——你給我一個(gè)條件我從整個(gè)關(guān)系集合里把所有符合條件的行挑出來(lái)。這里最讓初學(xué)者頭疼的是兩個(gè)量詞存在量詞?和全稱量詞?。存在量詞好理解意思是“存在這樣一行”全稱量詞麻煩一點(diǎn)意思是“對(duì)所有行都成立”。舉一個(gè)特別典型的例子。給定學(xué)生表S學(xué)號(hào), 姓名, 年齡, 系別和選課表SC學(xué)號(hào), 課程號(hào), 成績(jī)要查“選修了全部課程的學(xué)生姓名”。這個(gè)需求用自然語(yǔ)言說(shuō)很簡(jiǎn)單但用元組演算寫就稍微繞一點(diǎn){t[姓名] | S(t) ∧ (?u)(C(u) → (?v)(SC(v) ∧ v[學(xué)號(hào)]t[學(xué)號(hào)] ∧ v[課程號(hào)]u[課程號(hào)]))}這個(gè)式子的邏輯是我先找到學(xué)生元組t然后對(duì)所有課程元組u只要這門課程存在就必須存在一條選課記錄v把t和u關(guān)聯(lián)起來(lái)。外層是對(duì)所有學(xué)生遍歷內(nèi)層是對(duì)所有課程推導(dǎo)。如果某門課沒(méi)有對(duì)應(yīng)的選課記錄這個(gè)學(xué)生的元組就會(huì)被排除。這個(gè)例子考研、軟考、數(shù)據(jù)庫(kù)系統(tǒng)工程師考試都愛(ài)出。很多人考試時(shí)靠死記硬背這個(gè)模板通過(guò)但如果你真的理解了它的推理過(guò)程你會(huì)發(fā)現(xiàn)在“對(duì)所有課程都選了”這種業(yè)務(wù)需求的SQL實(shí)現(xiàn)中你會(huì)有更清晰的改寫思路——用NOT EXISTS還是用COUNT比較背后的邏輯基礎(chǔ)就在這里。2.3 域演算按值域說(shuō)話的另一種視角域演算的表達(dá)式長(zhǎng)這樣{x1, x2, ..., xn | P(x1, x2, ..., xn)}它聲明的是若干個(gè)域變量每個(gè)變量取各自域中的值然后通過(guò)條件P來(lái)約束這些變量的組合。不用聲明整行元組作為變量而是直接針對(duì)列的值做操作。域演算的一個(gè)經(jīng)典例子是“查年齡大于18歲的學(xué)生姓名”{姓名 | (?學(xué)號(hào))(?年齡)(?系別)(學(xué)生(學(xué)號(hào), 姓名, 年齡, 系別) ∧ 年齡 18)}這里的變量是學(xué)號(hào)、年齡、系別這些具體的值而不是一整行。它更像是用邏輯公式描述一張?zhí)摂M的結(jié)果表這一列放姓名那幾列是約束條件涉及的值大家組合起來(lái)。從查詢表達(dá)力的角度看域演算和元組演算是等價(jià)的——能用一個(gè)表達(dá)出來(lái)的查詢另一個(gè)也能表達(dá)。但認(rèn)知方式很不一樣元組演算比較接近“行掃描”的直覺(jué)域演算更接近“列投影”加“篩選”的認(rèn)知。有意思的是SQL的執(zhí)行計(jì)劃中投影操作和過(guò)濾操作確實(shí)是分開的域演算的這種拆分方式反而和物理執(zhí)行過(guò)程有著某種呼應(yīng)。給備考的朋友一個(gè)建議考試中域演算的題目不用摳太深掌握基本轉(zhuǎn)換規(guī)則即可。但元組演算請(qǐng)務(wù)必理解透因?yàn)镾QL的嵌套子查詢語(yǔ)義和它高度同構(gòu)。2.4 安全性問(wèn)題別讓查詢結(jié)果無(wú)窮大學(xué)演算還有一個(gè)繞不開的話題——安全性。由于演算用的是謂詞邏輯如果不加限制你完全可能寫出一個(gè)公式它的結(jié)果集合是無(wú)限的。舉個(gè)例子{t | ?S(t)}的意思是“所有不在學(xué)生表S里的元組的集合”。如果元組的定義域是無(wú)限的自然數(shù)集合這個(gè)查詢的結(jié)果也是無(wú)限的這在工程上毫無(wú)意義也不可能執(zhí)行。所以數(shù)據(jù)庫(kù)提供了“安全表達(dá)式”的概念一個(gè)演算表達(dá)式是安全的當(dāng)且僅當(dāng)它的所有可能結(jié)果都來(lái)自某個(gè)有限的域集合。實(shí)際操作中這個(gè)有限域集合通常來(lái)自關(guān)系實(shí)例中實(shí)際出現(xiàn)的值以及查詢自身用到的常量。SQL之所以是可執(zhí)行的查詢語(yǔ)言根源就在于它隱含了這樣的安全性約束。這事聽(tīng)著抽象但它在SQL中的影子很常見(jiàn)——為什么SQL的SELECT結(jié)果必須是有限的行數(shù)為什么子查詢里的NOT IN在遇到NULL時(shí)會(huì)有詭異的行為這些工程問(wèn)題追溯到底層都和安全語(yǔ)義有關(guān)。理解這些數(shù)學(xué)邊角能幫你更淡定地面對(duì)那些“玄學(xué)”SQL問(wèn)題。3. 演算到SQL的橋梁SEL/SELECT-WHERE結(jié)構(gòu)與查詢樹的推演3.1 SQL的語(yǔ)義核心是“謂詞約束元組投影”現(xiàn)在把視角從數(shù)學(xué)世界拉回工程世界。SQL的SELECT ... FROM ... WHERE ...結(jié)構(gòu)和元組演算的{t | P(t)}存在清晰的對(duì)應(yīng)關(guān)系FROM子句提供了元組遍歷的范圍WHERE子句扮演了謂詞P的角色SELECT子句則決定了最終保留哪些分量。這個(gè)對(duì)應(yīng)關(guān)系不是巧合SQL在設(shè)計(jì)時(shí)就是受了元組演算的啟發(fā)。SQL的創(chuàng)始人Donald Chamberlin和Raymond Boyce在1974年發(fā)表的論文里明確提到了關(guān)系演算對(duì)SEQUEL語(yǔ)言設(shè)計(jì)的影響。所以當(dāng)你寫一條SQL時(shí)其實(shí)你已經(jīng)在用元組演算的思維了只是平時(shí)沒(méi)人提醒你這一點(diǎn)。工程上的一個(gè)特定實(shí)現(xiàn)是SELselect算子和它的參數(shù)化形式。其實(shí)不一定要停留在某個(gè)特定數(shù)據(jù)庫(kù)產(chǎn)品的層面我們可以從通用角度理解一條SQL在執(zhí)行計(jì)劃層面會(huì)被拆成一系列邏輯算子包括掃描算子、過(guò)濾算子、投影算子、連接算子等。這些算子的排列組合形成了查詢樹而查詢樹是優(yōu)化器做邏輯等價(jià)變換的基礎(chǔ)。3.2 查詢樹的等價(jià)變換把公式變成可執(zhí)行的方案我舉個(gè)例子來(lái)說(shuō)明查詢樹推演的過(guò)程。假設(shè)有兩個(gè)表——訂單表O訂單號(hào), 客戶號(hào), 金額, 狀態(tài)和客戶表C客戶號(hào), 姓名, 城市?，F(xiàn)在要查“北京的客戶在2024年下的金額大于1000元的訂單”。SQL寫出來(lái)很直觀SELECT O.訂單號(hào), O.金額, C.姓名 FROM 訂單 O JOIN 客戶 C ON O.客戶號(hào) C.客戶號(hào) WHERE C.城市 北京 AND O.金額 1000 AND O.下單時(shí)間 BETWEEN 2024-01-01 AND 2024-12-31;在關(guān)系代數(shù)層面這條SQL對(duì)應(yīng)的查詢樹是先做訂單表掃描再做客戶表掃描然后做連接操作最后做選擇和投影。但問(wèn)題是這個(gè)執(zhí)行順序是不是最優(yōu)的優(yōu)化器會(huì)做一件關(guān)鍵的事——把可以提前做的過(guò)濾操作盡量下推。比如可以在掃描訂單表的時(shí)候就把金額1000和下單時(shí)間范圍的過(guò)濾條件直接套在表掃描之上只把符合條件的訂單行送入連接操作客戶表也一樣先把城市北京的客戶過(guò)濾出來(lái)。這樣進(jìn)入連接操作的數(shù)據(jù)量會(huì)大幅減少連接代價(jià)也隨之降低。在關(guān)系代數(shù)里這個(gè)操作的理論依據(jù)是選擇和連接的可交換性σ_條件(連接(A, B)) 等價(jià)于 連接(σ_條件A(A), σ_條件B(B))只要過(guò)濾條件只涉及A或只涉及B就可以安全地下推。3.3 一個(gè)反直覺(jué)的改寫案例EXISTS的演算本質(zhì)關(guān)于等價(jià)變換我分享一個(gè)特別有意思的真實(shí)案例。有一次我在優(yōu)化一條SQL業(yè)務(wù)場(chǎng)景是查“有訂單的客戶”。第一版SQL用的是IN子查詢SELECT C.客戶號(hào), C.姓名 FROM 客戶 C WHERE C.客戶號(hào) IN (SELECT O.客戶號(hào) FROM 訂單 O WHERE O.狀態(tài) 已完成);這條SQL在客戶表數(shù)據(jù)量十萬(wàn)、訂單表數(shù)據(jù)量百萬(wàn)的情況下執(zhí)行計(jì)劃出現(xiàn)了很糟糕的選擇——優(yōu)化器選擇了先全量掃描訂單子查詢?cè)賹?duì)客戶表做逐行探測(cè)。雖然訂單上建有客戶號(hào)索引但子查詢需要先過(guò)濾狀態(tài)已完成索引使用效率不高導(dǎo)致執(zhí)行時(shí)間飆到十幾秒。我當(dāng)時(shí)的處理是把IN改寫為EXISTSSELECT C.客戶號(hào), C.姓名 FROM 客戶 C WHERE EXISTS (SELECT 1 FROM 訂單 O WHERE O.客戶號(hào) C.客戶號(hào) AND O.狀態(tài) 已完成);改寫后優(yōu)化器可以更靈活地選擇執(zhí)行策略把客戶表作為驅(qū)動(dòng)表訂單表通過(guò)客戶號(hào)索引進(jìn)行關(guān)聯(lián)探測(cè)執(zhí)行時(shí)間從十幾秒降到了幾百毫秒。那么用演算怎么解釋這個(gè)改寫的正確性IN子查詢的語(yǔ)義是“客戶號(hào)在子查詢結(jié)果集合中”EXISTS的語(yǔ)義是“存在一條訂單記錄滿足條件”。用元組演算的語(yǔ)言來(lái)說(shuō)IN對(duì)應(yīng)的是外層元組的值是否屬于一個(gè)已確定的集合EXISTS對(duì)應(yīng)的是一個(gè)存在量詞推導(dǎo)。這兩個(gè)表達(dá)式的邏輯等價(jià)性正是謂詞邏輯中“元素屬于集合”和“存在量詞斷言”之間的同義變換。這個(gè)案例給我們的啟示是優(yōu)化器雖然能做很多自動(dòng)重寫但它依賴統(tǒng)計(jì)信息、索引結(jié)構(gòu)、代價(jià)模型并不總是智能到能識(shí)別所有等價(jià)的寫法。作為工程師理解底層演算語(yǔ)義能在關(guān)鍵時(shí)刻用人工改寫的方式幫優(yōu)化器一把。4. 優(yōu)化器內(nèi)部視角從邏輯樹到物理計(jì)劃的完整推理鏈路4.1 邏輯優(yōu)化和物理優(yōu)化其實(shí)是兩層不同的決策深入查詢優(yōu)化器內(nèi)部你會(huì)發(fā)現(xiàn)它做的事情遠(yuǎn)不止“等價(jià)重寫”這么簡(jiǎn)單。一個(gè)完整的查詢優(yōu)化流程可以拆成兩個(gè)層面邏輯優(yōu)化和物理優(yōu)化。邏輯優(yōu)化是在不改變查詢語(yǔ)義的前提下對(duì)查詢樹做結(jié)構(gòu)性的等價(jià)變換。常見(jiàn)的手法包括謂詞下推把過(guò)濾盡可能提前、子查詢展開把子查詢改寫成連接、連接重排序決定多表連接的先后順序、視圖合并把視圖的定義并入主查詢。這些變換的理論基礎(chǔ)就是前面說(shuō)的關(guān)系代數(shù)和演算的恒等性。物理優(yōu)化則是在邏輯計(jì)劃確定之后決定每一具體操作的實(shí)現(xiàn)方式表的訪問(wèn)路徑是走全表掃描還是索引掃描連接算法是用嵌套循環(huán)、哈希連接還是排序合并是否需要額外的排序操作并發(fā)執(zhí)行的程度如何設(shè)定這一層依賴的是數(shù)據(jù)庫(kù)系統(tǒng)內(nèi)部的代價(jià)模型、統(tǒng)計(jì)信息和物理存儲(chǔ)結(jié)構(gòu)。打個(gè)比喻邏輯優(yōu)化像是你在規(guī)劃旅行路線時(shí)決定“先去北京再去上海還是先去上海再去北京”物理優(yōu)化則像是決定“這兩座城市之間坐高鐵還是飛機(jī)”。前者關(guān)注的是步驟的先后和內(nèi)容后者關(guān)注的是每一步的執(zhí)行方式。4.2 代價(jià)模型的兩個(gè)核心組件基數(shù)估計(jì)和成本公式理解了這兩層優(yōu)化之后我想帶你看看優(yōu)化器做決策的最關(guān)鍵依據(jù)——代價(jià)模型。絕大多數(shù)現(xiàn)代數(shù)據(jù)庫(kù)的代價(jià)模型都包含兩個(gè)核心組件基數(shù)估計(jì)和成本公式?；鶖?shù)估計(jì)是估算某個(gè)中間結(jié)果集有多少行成本公式則是根據(jù)基數(shù)估算、數(shù)據(jù)分布、物理存儲(chǔ)特性來(lái)算一個(gè)操作的代價(jià)數(shù)值。優(yōu)化器會(huì)枚舉多個(gè)可能的計(jì)劃用代價(jià)模型估算每個(gè)計(jì)劃的總代價(jià)然后選代價(jià)最小的那一個(gè)?；鶖?shù)估計(jì)的難度遠(yuǎn)超想象它需要統(tǒng)計(jì)信息、數(shù)據(jù)分布假設(shè)通常是均勻分布或直方圖、列之間有相關(guān)性假設(shè)。當(dāng)統(tǒng)計(jì)信息過(guò)期、數(shù)據(jù)分布不均勻基數(shù)估計(jì)的結(jié)果會(huì)產(chǎn)生數(shù)量級(jí)偏差優(yōu)化器就會(huì)做出災(zāi)難性的執(zhí)行計(jì)劃選擇。這也是為什么實(shí)際運(yùn)維中要定期做統(tǒng)計(jì)信息更新比如ANALYZE、UPDATE STATISTICS以及為什么一些簡(jiǎn)單的SQL會(huì)莫名奇妙地走錯(cuò)執(zhí)行計(jì)劃。成本公式的細(xì)節(jié)因數(shù)據(jù)庫(kù)產(chǎn)品而異但大框架非常相似。對(duì)全表掃描來(lái)說(shuō)代價(jià)和表的總行數(shù)、塊數(shù)成正比對(duì)索引掃描來(lái)說(shuō)代價(jià)和選擇度、索引層數(shù)、回表次數(shù)相關(guān)對(duì)連接操作來(lái)說(shuō)代價(jià)取決于驅(qū)動(dòng)表行數(shù)、被驅(qū)動(dòng)表探測(cè)代價(jià)、內(nèi)存可用量等。理解這些你才能讀懂執(zhí)行計(jì)劃里那些數(shù)值的含義。4.3 統(tǒng)計(jì)信息對(duì)執(zhí)行計(jì)劃的影響一個(gè)被忽視的殺手我在一線工作中遇到過(guò)太多因?yàn)榻y(tǒng)計(jì)信息問(wèn)題導(dǎo)致的性能事故。有一個(gè)印象很深的例子某業(yè)務(wù)表的數(shù)據(jù)量從十萬(wàn)漲到五千萬(wàn)但因?yàn)樽詣?dòng)統(tǒng)計(jì)信息的閾值設(shè)置不當(dāng)分區(qū)級(jí)的統(tǒng)計(jì)信息沒(méi)有及時(shí)更新優(yōu)化器以為這張表還是十萬(wàn)行于是選擇了一個(gè)對(duì)小表友好的連接順序——大表驅(qū)動(dòng)小表。結(jié)果執(zhí)行計(jì)劃的實(shí)際運(yùn)行時(shí)間從幾十毫秒膨脹到十幾分鐘。當(dāng)時(shí)排查的完整鏈路是這樣的慢查詢?nèi)罩静蹲降揭粭lSQL的執(zhí)行時(shí)間異常拉長(zhǎng)平時(shí)幾毫秒最近穩(wěn)定在十幾分鐘。用EXPLAIN看執(zhí)行計(jì)劃發(fā)現(xiàn)連接順序明顯反直覺(jué)大表作為驅(qū)動(dòng)表小表作為被驅(qū)動(dòng)表。查看統(tǒng)計(jì)信息刷新時(shí)間發(fā)現(xiàn)該表最近一次ANALYZE是在三個(gè)月前當(dāng)時(shí)數(shù)據(jù)量確實(shí)是十萬(wàn)行。手動(dòng)執(zhí)行ANALYZE強(qiáng)制刷新統(tǒng)計(jì)信息。再看執(zhí)行計(jì)劃連接順序已經(jīng)糾正SQL執(zhí)行時(shí)間恢復(fù)到毫秒級(jí)。這個(gè)案例里沒(méi)有任何SQL寫法的問(wèn)題純粹是統(tǒng)計(jì)信息滯后導(dǎo)致的優(yōu)化器判斷失誤。這個(gè)教訓(xùn)我后來(lái)在團(tuán)隊(duì)里反復(fù)強(qiáng)調(diào)遇到SQL性能突變第一件事永遠(yuǎn)先確認(rèn)統(tǒng)計(jì)信息是否新鮮再去懷疑SQL本身的問(wèn)題。4.4 連接順序選擇的天花板當(dāng)優(yōu)化器也無(wú)能為力連接順序的選擇是查詢優(yōu)化中最難的問(wèn)題之一因?yàn)閚個(gè)表的連接順序有n!種可能每一種還需要考慮對(duì)應(yīng)的連接算法組合。對(duì)于7表或者10表以上的連接全枚舉的代價(jià)就已經(jīng)高到不可接受了所以現(xiàn)代優(yōu)化器幾乎都采用動(dòng)態(tài)規(guī)劃和啟發(fā)式搜索相結(jié)合的方式。但這帶來(lái)一個(gè)新的問(wèn)題——啟發(fā)式策略在多數(shù)情況下表現(xiàn)良好但在極少數(shù)邊界場(chǎng)景下會(huì)選出明顯次優(yōu)的計(jì)劃。工程師對(duì)這類查詢的處理方式是分析連接關(guān)系圖譜找出最小、最有選擇性的子集先行連接再逐級(jí)擴(kuò)展或者干脆用查詢提示hint鎖定連接順序。我在給客戶做性能優(yōu)化時(shí)有一條心得對(duì)復(fù)雜查詢與其讓優(yōu)化器做全空間搜索不如人工拆解。把一個(gè)大查詢拆成幾個(gè)中間結(jié)果表每一步都確保執(zhí)行計(jì)劃可控。這種做法的代價(jià)是額外存儲(chǔ)和多一步ETL但換來(lái)的是執(zhí)行計(jì)劃的穩(wěn)定性和可預(yù)測(cè)性。對(duì)生產(chǎn)環(huán)境的穩(wěn)定性要求而言這是值得的。5. 實(shí)戰(zhàn)看執(zhí)行計(jì)劃以EXPLAIN輸出為例的慢查詢定位方法論5.1 執(zhí)行計(jì)劃閱讀的基本順序從嵌套最深到最外層理論知識(shí)說(shuō)了一大堆現(xiàn)在落到最實(shí)務(wù)的部分——怎么通過(guò)執(zhí)行計(jì)劃定位和修復(fù)慢查詢。一個(gè)執(zhí)行計(jì)劃通常以樹狀結(jié)構(gòu)呈現(xiàn)無(wú)論是MySQL的EXPLAIN輸出、PostgreSQL的EXPLAIN還是Oracle的執(zhí)行計(jì)劃輸出核心邏輯是一致的。閱讀執(zhí)行計(jì)劃的正確順序其實(shí)是自內(nèi)向外、自底向上先看每個(gè)表中訪問(wèn)路徑的代價(jià)再看連接操作是不是按合理順序執(zhí)行最后看最外層的結(jié)果集構(gòu)造是否有不必要的開銷。很多新手犯的錯(cuò)誤是一上來(lái)就盯著第一個(gè)節(jié)點(diǎn)看這容易漏掉關(guān)鍵問(wèn)題。我從一次具體的性能排查講起。有一個(gè)場(chǎng)景論壇系統(tǒng)的帖子列表頁(yè)需要展示每個(gè)帖子的標(biāo)題、作者名、最新回復(fù)人和回復(fù)時(shí)間。SQL長(zhǎng)這樣SELECT p.title, u.name AS author_name, r.name AS reply_user, r.reply_time FROM post p JOIN user u ON p.author_id u.id LEFT JOIN LATERAL (SELECT ru.name, r.reply_time FROM reply r JOIN user ru ON r.user_id ru.id WHERE r.post_id p.id ORDER BY r.reply_time DESC LIMIT 1) r ON TRUE WHERE p.status 1 ORDER BY p.update_time DESC LIMIT 20;這條SQL在數(shù)據(jù)量上去之后變得很慢壓測(cè)時(shí)平均響應(yīng)時(shí)間達(dá)到3秒。先看執(zhí)行計(jì)劃的關(guān)鍵部分。5.2 從執(zhí)行計(jì)劃里讀出的三個(gè)問(wèn)題EXPLAIN ANALYZE輸出顯示外層post表掃描走了索引idx_post_status_update沒(méi)問(wèn)題但內(nèi)層LATERAL子查詢對(duì)reply表的探測(cè)居然走了全表掃描。為什么原來(lái)reply表的post_id列上雖然有索引但索引的統(tǒng)計(jì)信息顯示該列的重復(fù)值極多優(yōu)化器估算走索引的回表代價(jià)高于全表掃描。問(wèn)題出在回復(fù)表和帖子表的數(shù)據(jù)傾斜——少量熱帖集中了大量回復(fù)導(dǎo)致優(yōu)化器做了錯(cuò)誤的基數(shù)估計(jì)。第二個(gè)問(wèn)題是ORDER BY p.update_time DESC要求排序而執(zhí)行計(jì)劃的排序節(jié)點(diǎn)使用了臨時(shí)文件排序filesort內(nèi)存排序緩沖區(qū)設(shè)置過(guò)小導(dǎo)致磁盤排序。第三個(gè)問(wèn)題是LEFT JOIN LATERAL在PostgreSQL里是逐行調(diào)用子查詢這在邏輯上是串行的無(wú)法并行化。當(dāng)驅(qū)動(dòng)表需要掃描的行數(shù)很多時(shí)串行代價(jià)會(huì)被線性放大。5.3 修復(fù)方案和實(shí)施步驟針對(duì)這三個(gè)問(wèn)題我當(dāng)時(shí)的處理方案如下第一步修正基數(shù)估計(jì)——重新采集reply表的統(tǒng)計(jì)信息并且給post_id這一列建立覆蓋索引idx_reply_post_user_time (post_id, user_id, reply_time)。覆蓋索引可以讓子查詢中的過(guò)濾和排序都在索引層面完成不需要回表。第二步調(diào)整排序參數(shù)——把排序緩沖區(qū)從默認(rèn)的2MB調(diào)整到32MB同時(shí)檢查是否可以通過(guò)調(diào)整索引讓結(jié)果天然有序。最終是在post表的update_time列和status列上建立了組合索引讓外層查詢的過(guò)濾和排序在同一個(gè)索引掃描中完成直接消除了排序節(jié)點(diǎn)。第三步重寫LATERAL子查詢。這里我能想到的最佳實(shí)踐是如果熱帖的回復(fù)數(shù)量本身就不多可以接受一筆額外的預(yù)聚合如果熱帖集中更適合的方式是把“每個(gè)帖子最近回復(fù)”這個(gè)邏輯做成物化結(jié)果。事實(shí)上在很多高并發(fā)社區(qū)場(chǎng)景里這類“最新回復(fù)”數(shù)據(jù)都是異步寫入緩存或單獨(dú)匯總表的實(shí)時(shí)跑SQL反而是不合理的架構(gòu)。優(yōu)化后的執(zhí)行計(jì)劃里三個(gè)性能瓶頸全部消除SQL平均響應(yīng)時(shí)間從3秒降到80毫秒。這個(gè)案例的典型意義在于它同時(shí)涉及了統(tǒng)計(jì)信息、索引設(shè)計(jì)、參數(shù)配置、SQL結(jié)構(gòu)四個(gè)維度而這些恰恰是查詢優(yōu)化中最常見(jiàn)的四個(gè)切入點(diǎn)。提示看執(zhí)行計(jì)劃時(shí)關(guān)注三種特定的標(biāo)志性字段——filtered比例過(guò)低說(shuō)明索引選擇性差、filesort或sort節(jié)點(diǎn)出現(xiàn)說(shuō)明排序無(wú)法利用索引、臨時(shí)表出現(xiàn)說(shuō)明結(jié)果集產(chǎn)生中間落盤。這三個(gè)信號(hào)基本覆蓋了90%的慢查詢根因。5.4 常見(jiàn)的執(zhí)行計(jì)劃誤讀和我踩過(guò)的坑執(zhí)行計(jì)劃閱讀的坑也值得專門說(shuō)一說(shuō)。第一個(gè)坑把節(jié)點(diǎn)的輸出行數(shù)當(dāng)成實(shí)際行數(shù)。在執(zhí)行計(jì)劃中節(jié)點(diǎn)輸出行數(shù)是優(yōu)化器的基數(shù)估計(jì)值而不是實(shí)際執(zhí)行行數(shù)。如果想看實(shí)際行數(shù)要用EXPLAIN ANALYZE或者EXPLAIN (ANALYZE, BUFFERS)它會(huì)真實(shí)執(zhí)行查詢并回傳實(shí)際行數(shù)和實(shí)際耗時(shí)。只讀估算行數(shù)很容易被誤導(dǎo)。第二個(gè)坑忽略緩沖BUFFERS信息。很多執(zhí)行計(jì)劃的慢不是慢在計(jì)算而是慢在I/O。BUFFERS字段能告訴你這個(gè)節(jié)點(diǎn)訪問(wèn)了多少個(gè)數(shù)據(jù)塊其中多少是命中了共享緩沖區(qū)的。如果某個(gè)節(jié)點(diǎn)讀取的塊數(shù)非常多且命中率低說(shuō)明存在嚴(yán)重的隨機(jī)I/O需要通過(guò)索引調(diào)整來(lái)訪問(wèn)更少的數(shù)據(jù)塊。第三個(gè)坑直接把執(zhí)行計(jì)劃中的總代價(jià)拿來(lái)排序比較。代價(jià)數(shù)值是相對(duì)的不同數(shù)據(jù)庫(kù)、不同版本、不同參數(shù)下的代價(jià)基準(zhǔn)都不一樣。我自己更習(xí)慣的做法是看計(jì)劃結(jié)構(gòu)是否符合直覺(jué)——有沒(méi)有哪個(gè)節(jié)點(diǎn)做了不合理的全表掃描有沒(méi)有多表連接的驅(qū)動(dòng)順序反了有沒(méi)有該走索引卻走了掃描。結(jié)構(gòu)對(duì)加上實(shí)際耗時(shí)驗(yàn)證比糾結(jié)具體數(shù)值更可靠。6. 從演算到現(xiàn)代場(chǎng)景枚舉元組、C#值元組解構(gòu)和畢達(dá)哥拉斯三元組的視角6.1 枚舉元組當(dāng)“元組”遇上現(xiàn)代編程語(yǔ)言聊了很多數(shù)據(jù)庫(kù)系統(tǒng)的內(nèi)容我想再擴(kuò)展一下“元組”這個(gè)詞在現(xiàn)代編程中的含義因?yàn)檫@個(gè)概念對(duì)數(shù)據(jù)庫(kù)工程師來(lái)說(shuō)既熟悉又陌生。在C#、Python、Rust等現(xiàn)代編程語(yǔ)言中元組tuple是一種輕量級(jí)的數(shù)據(jù)結(jié)構(gòu)用于打包一組異構(gòu)的值。C#從7.0開始引入了值元組ValueTuple和解構(gòu)語(yǔ)法這使得開發(fā)者可以這樣寫var (name, age) GetUserInfo(userId); Console.WriteLine(${name} is {age} years old.);這種語(yǔ)法本質(zhì)上是在做元組的解構(gòu)——把一個(gè)元組變量按位置拆成多個(gè)命名變量。這恰好呼應(yīng)了域演算的思維按域變量取值而不是把整個(gè)元組作為一個(gè)整體去操作。對(duì)數(shù)據(jù)庫(kù)開發(fā)者來(lái)說(shuō)理解現(xiàn)代語(yǔ)言中的元組操作有一個(gè)實(shí)際好處當(dāng)你編寫ORM查詢或者做DTO映射時(shí)你實(shí)際上是在關(guān)系的元組和編程語(yǔ)言的元組之間做翻譯。批量枚舉元組、按位置解構(gòu)、按屬性命名這些操作背后的思維模型和關(guān)系數(shù)據(jù)庫(kù)的行列模型是同構(gòu)的。6.2 畢達(dá)哥拉斯三元組一個(gè)經(jīng)典的域演算思維練習(xí)題“noj畢達(dá)哥拉斯3元組”這個(gè)熱搜詞也很有意思。畢達(dá)哥拉斯三元組指的是滿足a2 b2 c2的三個(gè)正整數(shù)比如(3, 4, 5)。用這個(gè)例子來(lái)做域演算的思維練習(xí)特別合適因?yàn)樗枰懵暶魅齻€(gè)域變量然后描述它們之間的約束關(guān)系。用域演算來(lái)表達(dá)“找出所有畢達(dá)哥拉斯三元組”{(a, b, c) | a ∈ N ∧ b ∈ N ∧ c ∈ N ∧ a2 b2 c2 ∧ 1 ≤ a b c ≤ 100}這個(gè)公式本質(zhì)上是一個(gè)約束搜索問(wèn)題的聲明式描述。你聲明三個(gè)整數(shù)變量給出取值范圍和約束條件剩下的交給執(zhí)行器去做。這正好對(duì)應(yīng)了SQL中一個(gè)經(jīng)典問(wèn)題的寫法——生成三個(gè)范圍笛卡爾積后做篩選WITH numbers AS (SELECT generate_series(1, 100) AS n) SELECT a.n AS a, b.n AS b, c.n AS c FROM numbers a, numbers b, numbers c WHERE a.n b.n AND b.n c.n AND a.n * a.n b.n * b.n c.n * c.n;這個(gè)寫法雖然直觀但性能算不上好——范圍小時(shí)還能接受范圍一旦擴(kuò)大笛卡爾積的規(guī)模就是O(n3)優(yōu)化器也無(wú)法把這種約束轉(zhuǎn)換成索引友好的計(jì)劃。工程上的處理方式通常是縮小搜索范圍、用數(shù)學(xué)邊界剪枝、或者事先生成緩存表。這個(gè)例子恰好揭示了聲明式語(yǔ)言的一個(gè)根本矛盾表達(dá)簡(jiǎn)潔不等于執(zhí)行高效優(yōu)化器能做的重寫是有限度的。6.3 數(shù)據(jù)庫(kù)查詢優(yōu)化器在AI和現(xiàn)代數(shù)據(jù)棧中的角色變遷把話題拉回查詢優(yōu)化器本身。在現(xiàn)代數(shù)據(jù)棧里查詢優(yōu)化器早已不局限于傳統(tǒng)關(guān)系型數(shù)據(jù)庫(kù)。Spark SQL、Presto/Trino、ClickHouse這些大數(shù)據(jù)引擎都有自己的一套查詢優(yōu)化策略但底層依然是在做邏輯重寫和物理計(jì)劃選擇。它們面對(duì)的場(chǎng)景更極端——數(shù)據(jù)規(guī)模更大數(shù)據(jù)源更異構(gòu)查詢模式更多樣。值得注意的是近年來(lái)業(yè)界在探索用機(jī)器學(xué)習(xí)技術(shù)改進(jìn)基數(shù)估計(jì)和代價(jià)模型。比如把統(tǒng)計(jì)信息丟給神經(jīng)網(wǎng)絡(luò)去學(xué)習(xí)數(shù)據(jù)分布或者用強(qiáng)化學(xué)習(xí)來(lái)決定連接順序。這些嘗試的方向是對(duì)的但受限于訓(xùn)練數(shù)據(jù)獲取成本、模型解釋性和推理延遲離大規(guī)模落地還有距離。對(duì)一線工程師來(lái)說(shuō)與其等待優(yōu)化器變得更智能不如更勤奮地理解它現(xiàn)有的決策邏輯。在當(dāng)前這個(gè)數(shù)據(jù)環(huán)境下有另一種趨勢(shì)很值得關(guān)注很多團(tuán)隊(duì)開始主動(dòng)繞過(guò)通用優(yōu)化器用物化視圖、預(yù)計(jì)算、增量更新等方式來(lái)回避“查詢時(shí)優(yōu)化”的問(wèn)題。這其實(shí)是一種務(wù)實(shí)的工程折中——既然查詢優(yōu)化器在復(fù)雜場(chǎng)景下難以做到最優(yōu)不如把復(fù)雜計(jì)算挪到寫入端或離線批處理端。能用預(yù)計(jì)算解決的查詢不要在查詢時(shí)挑戰(zhàn)優(yōu)化器。7. 備考復(fù)習(xí)與工作實(shí)踐的結(jié)合路徑我的個(gè)人操作經(jīng)驗(yàn)聊了這么多理論、工程和案例最后分享一些實(shí)際的備考和工作經(jīng)驗(yàn)。如果你是準(zhǔn)備數(shù)據(jù)庫(kù)系統(tǒng)工程師考試的考生我的建議是不要只背符號(hào)公式也不要完全放棄理論。把所有演算表達(dá)式和關(guān)系代數(shù)表達(dá)式翻譯成SQL再翻譯回表達(dá)式來(lái)回做幾遍。這個(gè)過(guò)程的收益遠(yuǎn)超你的想象——它讓你建立的是語(yǔ)義層面的等價(jià)關(guān)系網(wǎng)絡(luò)而考試題考察的恰好就是這種等價(jià)變換能力。我當(dāng)初備考時(shí)的一個(gè)具體做法是把歷年真題里的關(guān)系代數(shù)、元組演算、SQL三者的互轉(zhuǎn)題目自己整理成一張對(duì)應(yīng)表。每種查詢模式選擇、投影、連接、分組、去重、嵌套都寫清楚三種表達(dá)方式的對(duì)應(yīng)規(guī)則。復(fù)習(xí)效果非常好而且這份對(duì)應(yīng)表在后來(lái)工作中分析SQL執(zhí)行計(jì)劃時(shí)依然能派上用場(chǎng)。工作中建議重點(diǎn)掌握幾個(gè)實(shí)操技能EXPLAIN的深度使用、統(tǒng)計(jì)信息的手動(dòng)管理、覆蓋索引的設(shè)計(jì)原則、子查詢和連接的手工改寫。這些技能在慢查詢排查中的實(shí)用性幾乎是日常性的。我給自己團(tuán)隊(duì)定的底線是每個(gè)人都能在十分鐘內(nèi)定位一條慢SQL的根因并給出至少兩種優(yōu)化思路。能做到這一點(diǎn)數(shù)據(jù)庫(kù)系統(tǒng)工程師這個(gè)頭銜才算真正名副其實(shí)。最后再分享一個(gè)小技巧。每次優(yōu)化完一條SQL我會(huì)把優(yōu)化前后的SQL和執(zhí)行計(jì)劃的對(duì)比截圖保存下來(lái)附帶一段文字說(shuō)明根因。半年下來(lái)就是一個(gè)非常有價(jià)值的案例庫(kù)。下次有人問(wèn)“為什么這條SQL變慢了”直接翻案例庫(kù)比重新排查一遍高效太多。這種積累方式對(duì)個(gè)人成長(zhǎng)和團(tuán)隊(duì)沉淀都有好處。