我見過太多人這樣建索引:查詢要過濾A、B、C三列,那就直接給A、B、C建個(gè)復(fù)合索引。聽起來合理,但PostgreSQL優(yōu)化器可能根本不買賬。
復(fù)合B-tree索引的生效邏輯,遠(yuǎn)比"列全匹配"復(fù)雜。優(yōu)化器會(huì)同時(shí)權(quán)衡謂詞類型、列順序、選擇度、表大小,以及最終生成的執(zhí)行計(jì)劃。這意味著你精心設(shè)計(jì)的索引,可能從頭到尾不會(huì)被掃描一次。
![]()
三個(gè)實(shí)操細(xì)節(jié)決定索引生死:
![]()
第一,等值謂詞要放前面。WHERE a = 1 AND b > 100的查詢,索引列順序應(yīng)該是(a, b)而非(b, a)。B-tree的物理存儲(chǔ)特性決定了,等值匹配能精確定位起始位置,范圍掃描則需要在有序區(qū)間內(nèi)滑動(dòng)。順序顛倒,前綴壓縮失效,隨機(jī)I/O暴漲。
第二,n_distinct統(tǒng)計(jì)值直接影響選擇度估算。PostgreSQL依賴pg_stats中的列統(tǒng)計(jì)信息判斷過濾后剩余行數(shù)。如果統(tǒng)計(jì)信息過期或采樣偏差,優(yōu)化器會(huì)低估索引收益,直接轉(zhuǎn)向全表掃描。ANALYZE不是可選項(xiàng),是索引生效的前提。
第三,執(zhí)行計(jì)劃比索引定義更重要。EXPLAIN ANALYZE能看到實(shí)際行數(shù)與估算行數(shù)的偏差,也能暴露索引未被使用的原因——可能是并行順序掃描成本更低,也可能是索引回表代價(jià)過高。沒有執(zhí)行計(jì)劃驗(yàn)證的索引設(shè)計(jì),都是紙上談兵。
![]()
pgAssistant的做法是把這套判斷流程自動(dòng)化:解析EXPLAIN ANALYZE輸出,結(jié)合planner統(tǒng)計(jì)信息,輸出可落地的索引建議。工具的價(jià)值不在于替代DBA判斷,而是把"為什么這個(gè)索引沒用"的排查時(shí)間,從小時(shí)級(jí)壓縮到分鐘級(jí)。
索引優(yōu)化沒有銀彈。列多不等于覆蓋全,理論優(yōu)不等于執(zhí)行快。讓優(yōu)化器說話,讓數(shù)據(jù)說話。
特別聲明:以上內(nèi)容(如有圖片或視頻亦包括在內(nèi))為自媒體平臺(tái)“網(wǎng)易號(hào)”用戶上傳并發(fā)布,本平臺(tái)僅提供信息存儲(chǔ)服務(wù)。
Notice: The content above (including the pictures and videos if any) is uploaded and posted by a user of NetEase Hao, which is a social media platform and only provides information storage services.