我們有沒(méi)有遇到過(guò)這樣的情況：同樣的查詢(xún)，別人的代碼瞬間出結(jié)果，我們的卻要卡半天？其實(shí)，有時(shí)，SQL性能的差距往往藏在那些看似不起眼的SQL表達(dá)式里。
簡(jiǎn)單來(lái)說(shuō)，SQL表達(dá)式就像數(shù)據(jù)庫(kù)的“計(jì)算公式”，如：查詢(xún)條件、計(jì)算邏輯等等。這些表達(dá)式寫(xiě)得好不好，直接決定了數(shù)據(jù)庫(kù)要做多少“無(wú)用功”。重復(fù)計(jì)算、索引用不上、類(lèi)型不匹配……這些小問(wèn)題累積起來(lái)，就會(huì)讓查詢(xún)速度大打折扣。
SQL表達(dá)式優(yōu)化并不復(fù)雜，只要掌握幾個(gè)基礎(chǔ)優(yōu)化原則就能讓我們少走很多彎路：比如別在索引列上隨便用函數(shù)，避免讓數(shù)據(jù)庫(kù)反復(fù)計(jì)算同一個(gè)值，處理好空值和類(lèi)型轉(zhuǎn)換的坑。無(wú)論是日常辦公的簡(jiǎn)單查詢(xún)，還是支撐業(yè)務(wù)的復(fù)雜報(bào)表，優(yōu)化SQL表達(dá)式都能幫我們節(jié)省時(shí)間、減少服務(wù)器壓力。
接下來(lái)，就讓我們一起來(lái)看看如何通過(guò)簡(jiǎn)單調(diào)整，就能讓我們的SQL從“能跑”變成“跑得快”，進(jìn)而，一步步不“修仙”也能得道。
一、基礎(chǔ)優(yōu)化原則
| | | |
|---|
| 減少計(jì)算量 | 消除重復(fù)計(jì)算、常量折疊、拆分嵌套函數(shù)，避免運(yùn)行時(shí)重復(fù)求值 | | 優(yōu)化前：WHERE a*2 > 10 AND a*2 < 20
優(yōu)化后：WHERE a BETWEEN 5 AND 9（常量折疊 + 范圍合并）
? 使用CTE暫存復(fù)雜表達(dá)式：WITH calc AS (SELECT expensive_expr AS val FROM t) |
| 提升索引利用率 | 避免在索引列上使用函數(shù)或表達(dá)式，防止索引失效；可創(chuàng)建表達(dá)式索引補(bǔ)償 | EXPLAIN | 優(yōu)化前：WHERE YEAR(created_at) = 2023（全表掃描）
優(yōu)化后：WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01'（可走索引）
? 建議：對(duì)高頻函數(shù)使用表達(dá)式索引：CREATE INDEX idx_year ON tbl (EXTRACT(YEAR FROM created_at)) |
| 簡(jiǎn)化邏輯結(jié)構(gòu) | 合理組織CASE順序（高頻分支前置），優(yōu)先使用簡(jiǎn)潔函數(shù)替代冗長(zhǎng)條件 | | 優(yōu)化前：
CASE WHEN a IS NULL THEN b WHEN b IS NULL THEN c ELSE d END
優(yōu)化后：COALESCE(a, b, c, d)
? 對(duì)于枚舉映射，可用DECODE()（Oracle）或CASE表達(dá)式預(yù)定義為視圖 |
| 確保類(lèi)型一致 | 避免隱式類(lèi)型轉(zhuǎn)換導(dǎo)致索引失效或性能下降，尤其是字符串與數(shù)字、日期比較 | | 優(yōu)化前：WHERE id = '123'（若id為INT，則觸發(fā)隱式轉(zhuǎn)換，可能不走索引）
優(yōu)化后：WHERE id = 123 或 WHERE id = CAST('123' AS INT)
?? 注意：字符串比較時(shí)注意字符集和排序規(guī)則一致性 |
| 善用數(shù)據(jù)庫(kù)特性 | 利用表達(dá)式索引、虛擬列（生成列）、函數(shù)索引等高級(jí)特性固化復(fù)雜邏輯 | PostgreSQL、MySQL 8.0+、Oracle、SQL Server | 示例：
CREATE INDEX idx_email_lower ON users (LOWER(email))
查詢(xún)：WHERE LOWER(email) = 'user@example.com' 可使用該索引
? MySQL中可用VIRTUAL或STORED生成列實(shí)現(xiàn)持久化表達(dá)式 |
二、中級(jí)優(yōu)化方法
| | | |
|---|
| 布爾短路優(yōu)化 | 多條件過(guò)濾，尤其含昂貴函數(shù)（如：正則、JSON解析） | ?? 依賴(lài)優(yōu)化器實(shí)現(xiàn) | 優(yōu)化前：WHERE expensive_func(a) AND b > 10
優(yōu)化后：WHERE b > 10 AND expensive_func(a)
? 原理：多數(shù)數(shù)據(jù)庫(kù)支持短路求值（如：PostgreSQL、Oracle），但不能完全依賴(lài)，應(yīng)結(jié)合統(tǒng)計(jì)信息確保高選擇性條件前置 |
| 避免標(biāo)量子查詢(xún) | 主查詢(xún)每行都執(zhí)行一次子查詢(xún)，性能極差 | | 優(yōu)化前：
SELECT u.name, (SELECT MAX(score) FROM tests WHERE user_id = u.id) FROM users u
優(yōu)化后：
SELECT u.name, MAX(t.score) OVER (PARTITION BY u.id) FROM users u LEFT JOIN tests t ON u.id = t.user_id
? 或使用關(guān)聯(lián)子查詢(xún)+索引，或改寫(xiě)為LATERAL JOIN |
| 處理NULL參與運(yùn)算 | 數(shù)值表達(dá)式中字段可能為NULL，導(dǎo)致結(jié)果為NULL | | 優(yōu)化前：SELECT price * quantity FROM items（若任一為NULL，結(jié)果為NULL）
優(yōu)化后：SELECT COALESCE(price, 0) * COALESCE(quantity, 0)
? 注意：根據(jù)業(yè)務(wù)決定默認(rèn)值，有時(shí)應(yīng)保留NULL表示“未知”而非“0” |
| 位運(yùn)算替代狀態(tài)判斷 | 狀態(tài)字段使用位掩碼（如：權(quán)限、標(biāo)簽組合） | | 優(yōu)化前：
WHERE status = 1 OR status = 2 OR status = 4
優(yōu)化后：WHERE (status & 7) != 0（7 = 1|2|4）
? 建議：配合表達(dá)式索引：CREATE INDEX idx_status_mask ON tbl ((status & 7)) |
| 正則替代LIKE OR | | | 優(yōu)化前：
WHERE name LIKE '%abc%' OR name LIKE '%def%'
優(yōu)化后（PostgreSQL）：
`WHERE name ~ 'abc |
| 移除冗余DISTINCT | 在已唯一字段上誤用DISTINCT，增加排序開(kāi)銷(xiāo) | | 優(yōu)化前：
SELECT COUNT(DISTINCT id) FROM orders（id為主鍵）
優(yōu)化后：SELECT COUNT(id) FROM orders
? 說(shuō)明：COUNT(*) 比 COUNT(id) 更快（無(wú)需判空） |
| 使用NULLIF防除零 | 安全進(jìn)行除法運(yùn)算，避免運(yùn)行時(shí)錯(cuò)誤 | | 優(yōu)化前：SELECT total / quantity FROM sales（quantity=0時(shí)報(bào)錯(cuò)）
優(yōu)化后：SELECT total / NULLIF(quantity, 0)
? 返回NULL而非報(bào)錯(cuò)，便于后續(xù)處理（如：COALESCE(..., 0)） |
三、高階優(yōu)化技巧
| | |
|---|
| 避免JSON解析函數(shù) | 頻繁解析JSON字段（如：->>）無(wú)法利用索引，性能差 | 優(yōu)化前：
WHERE data->>'status' = 'active'
優(yōu)化后：
ALTER TABLE t ADD COLUMN status TEXT
GENERATED ALWAYS AS (data->>'status') STORED;
CREATE INDEX idx_status ON t(status);
? 適用于MySQL 5.7+、PostgreSQL、SQL Server |
| 惰性求值優(yōu)化 | 利用布爾短路機(jī)制，延遲執(zhí)行高成本表達(dá)式 | WHERE (a > 1000 OR (a < 100 AND expensive_func(b)))
? 當(dāng)a > 1000為真時(shí)，跳過(guò)expensive_func(b)
?? 注意：并非所有數(shù)據(jù)庫(kù)都保證短路順序，需測(cè)試驗(yàn)證 |
| 布爾表達(dá)式替代CASE | 簡(jiǎn)化簡(jiǎn)單二元判斷，減少函數(shù)調(diào)用開(kāi)銷(xiāo) | 優(yōu)化前：
CASE WHEN a > 10 THEN 'high' ELSE 'low' END
優(yōu)化后（PostgreSQL）：
(CASE WHEN a > 10 THEN 'high' ELSE 'low' END) → 可用：
('low', 'high')[ (a > 10)::int + 1 ] 或更簡(jiǎn)潔：
(a > 10)::TEXT（返回'true'/'false'）
? 適用于標(biāo)簽化輸出，但可讀性略降 |
| 優(yōu)化日期間隔計(jì)算 | 避免在列上加減時(shí)間導(dǎo)致索引失效 | 優(yōu)化前：
WHERE NOW() - created_at > INTERVAL '7 days'
優(yōu)化后：
WHERE created_at < NOW() - INTERVAL '7 days'
? 此改寫(xiě)使created_at可走索引，極大提升性能 |
| 利用生成列 | 將復(fù)雜表達(dá)式固化為列，支持索引與查詢(xún)加速 | ALTER TABLE products
ADD COLUMN total_price NUMERIC
GENERATED ALWAYS AS (price * (1 + tax_rate)) STORED;
CREATE INDEX idx_total ON products(total_price);
? 支持MySQL、PostgreSQL、Oracle、SQL Server |
| 向量化友好表達(dá)式 | 面向列式存儲(chǔ)（如：ClickHouse、Snowflake）優(yōu)化 | 避免：
ROW_NUMBER() OVER (ORDER BY x)（行級(jí)操作）
優(yōu)先使用：
SUM(x) FILTER (WHERE cond)、COUNTIF()等聚合函數(shù)
? 列式數(shù)據(jù)庫(kù)可并行處理整列數(shù)據(jù)，向量化函數(shù)執(zhí)行更快 |
四、前沿優(yōu)化思想
| | |
|---|
| 短路求值與布爾代數(shù) | 化簡(jiǎn)復(fù)雜條件表達(dá)式，降低計(jì)算復(fù)雜度 | WHERE (a OR b) AND (a OR c)
等價(jià)于：WHERE a OR (b AND c)
? 減少一次邏輯判斷，提升可讀性與執(zhí)行效率 |
| 處理浮點(diǎn)精度問(wèn)題 | 浮點(diǎn)數(shù)比較存在精度誤差，應(yīng)使用精確類(lèi)型 | 優(yōu)化前：
WHERE amount = 0.1（可能因浮點(diǎn)誤差不匹配）
優(yōu)化后：
WHERE amount = 0.1::NUMERIC 或 ABS(amount - 0.1) < 1e-9
? 金融類(lèi)應(yīng)用必須使用DECIMAL或NUMERIC |
| 表達(dá)式統(tǒng)計(jì)信息 | 幫助優(yōu)化器生成更優(yōu)執(zhí)行計(jì)劃 | PostgreSQL中執(zhí)行 ANALYZE tbl;
會(huì)收集列和表達(dá)式索引的統(tǒng)計(jì)信息
? 可手動(dòng)更新統(tǒng)計(jì)信息以反映數(shù)據(jù)分布變化 |
| 分布式表達(dá)式下推 | 在分布式數(shù)據(jù)庫(kù)中，將過(guò)濾、計(jì)算下推到數(shù)據(jù)節(jié)點(diǎn) | 示例：
在Greenplum、ClickHouse中，WHERE條件自動(dòng)下推至Segment/Partition節(jié)點(diǎn)執(zhí)行
? 減少網(wǎng)絡(luò)傳輸與中心節(jié)點(diǎn)壓力 |
| 函數(shù)確定性 | 使用IMMUTABLE函數(shù)提升緩存與并行能力 | 定義函數(shù)時(shí)指定：
CREATE FUNCTION f(x int) RETURNS int IMMUTABLE ...
? IMMUTABLE函數(shù)可被優(yōu)化器提前計(jì)算、緩存結(jié)果；VOLATILE函數(shù)每次調(diào)用都執(zhí)行 |
| SQL宏與模板 | 統(tǒng)一高頻復(fù)雜表達(dá)式，提升可維護(hù)性 | Oracle 23c+：
CREATE MACRO discount_price(p) AS p * 0.9;
使用：SELECT discount_price(price) FROM products;
? 類(lèi)似C語(yǔ)言宏，編譯時(shí)展開(kāi)，無(wú)運(yùn)行時(shí)開(kāi)銷(xiāo) |
五、優(yōu)化檢查清單
- [ ] 是否存在重復(fù)計(jì)算？→ 用CTE或變量緩存
- [ ] 是否在索引列上使用函數(shù)？→ 改寫(xiě)為列與常量比較
- [ ] 是否有隱式類(lèi)型轉(zhuǎn)換？→ 確保類(lèi)型一致（尤其字符串與數(shù)字）
- [ ] 是否使用了標(biāo)量子查詢(xún)？→ 用窗口函數(shù)、JOIN或LATERAL替代
- [ ] 是否有NULL參與運(yùn)算？→ 使用
COALESCE或NULLIF安全處理 - [ ] 是否頻繁解析JSON/XML？→ 使用生成列+索引固化路徑
- [ ] 是否可用布爾表達(dá)式替代
CASE WHEN？→ 提升執(zhí)行效率（簡(jiǎn)單場(chǎng)景） - [ ] 日期計(jì)算是否可參數(shù)化？→ 避免運(yùn)行時(shí)函數(shù)調(diào)用影響索引
- [ ] 復(fù)雜表達(dá)式是否可物化？→ 使用生成列（GENERATED COLUMN）
- [ ] 是否適用于列式數(shù)據(jù)庫(kù)？→ 優(yōu)先使用向量化聚合函數(shù)（如：
SUMIF） - [ ] 是否使用了隨機(jī)函數(shù)？→ 確保一致性，避免重復(fù)調(diào)用（如：
RAND()） - [ ] 是否可通過(guò)表達(dá)式索引加速？→ 創(chuàng)建
FUNCTIONAL INDEX - [ ] 布爾條件順序是否合理？→ 高選擇性、低成本條件前置
- [ ] 是否存在除零風(fēng)險(xiǎn)？→ 使用
NULLIF處理分母 - [ ] 多狀態(tài)判斷是否可用位運(yùn)算？→ 適合狀態(tài)掩碼場(chǎng)景（權(quán)限、標(biāo)簽）
- [ ] 文本匹配是否可改用正則或全文檢索？→ 復(fù)雜模式更高效
- [ ]
DISTINCT是否必要？→ 避免冗余排序（主鍵/唯一字段無(wú)需） - [ ] 分布式環(huán)境下表達(dá)式是否下推？→ 減少數(shù)據(jù)傳輸與中心負(fù)載
- [ ] 是否可使用物化視圖？→ 對(duì)復(fù)雜聚合表達(dá)式進(jìn)行預(yù)計(jì)算
- [ ] 是否啟用查詢(xún)重寫(xiě)或自動(dòng)索引建議？→ 利用數(shù)據(jù)庫(kù)內(nèi)置優(yōu)化工具（如：SQL Server DTA、Oracle SQL Tuning Advisor）
六、優(yōu)化的三層境界
| | |
|---|
| 初級(jí) | | 寫(xiě)出能運(yùn)行的SQL表達(dá)式，不關(guān)心性能 |
| 中級(jí) | | 合理使用索引、避免函數(shù)調(diào)用、優(yōu)化條件順序、消除標(biāo)量子查詢(xún) |
| 高級(jí) | | 等價(jià)邏輯轉(zhuǎn)換、使用生成列、表達(dá)式索引、分布式下推策略 |
| 超高級(jí) | | 延遲計(jì)算、無(wú)副作用設(shè)計(jì)、編譯執(zhí)行優(yōu)化、SQL宏抽象、與數(shù)據(jù)建模協(xié)同演進(jìn) |
說(shuō)明：
- 延遲計(jì)算：僅在真正需要時(shí)才求值（如：CTE的非物化、窗口函數(shù)延遲）
- 無(wú)副作用：避免
VOLATILE函數(shù)、隨機(jī)數(shù)、會(huì)話(huà)狀態(tài)依賴(lài) - 編譯優(yōu)化：現(xiàn)代數(shù)據(jù)庫(kù)（如：Oracle、SQL Server）具備JIT編譯能力，可將SQL表達(dá)式編譯為機(jī)器碼執(zhí)行
七、終極理念
SQL表達(dá)式優(yōu)化的本質(zhì)是：通過(guò)更精準(zhǔn)的語(yǔ)義表達(dá)，減少計(jì)算量，提升執(zhí)行效率，同時(shí)保證代碼的可維護(hù)性與系統(tǒng)的可擴(kuò)展性。
我們必須清楚，優(yōu)化不是一次性的任務(wù)，而是一個(gè)持續(xù)迭代的過(guò)程，需要結(jié)合：
- 業(yè)務(wù)語(yǔ)義（何時(shí)為空？精度要求？）
- 數(shù)據(jù)特征（基數(shù)、分布、傾斜度）
- 數(shù)據(jù)庫(kù)特性（優(yōu)化器能力、索引類(lèi)型、執(zhí)行引擎）
- 架構(gòu)模式（單機(jī)、分布式、列式、HTAP）
只有我們綜合運(yùn)用上述原則與技巧，方能實(shí)現(xiàn)數(shù)據(jù)庫(kù)從“能運(yùn)行”到“高性能、高可維護(hù)”的躍遷。
結(jié)語(yǔ)：
最好的優(yōu)化，是讓數(shù)據(jù)庫(kù)“少做一點(diǎn)事”。
最優(yōu)雅的SQL，是讓邏輯清晰、執(zhí)行高效、未來(lái)可期。?
該文章在 2025/8/19 10:50:20 編輯過(guò)
400 186 1886
