用戶數(shù)據(jù)中出現(xiàn)了學(xué)生成績相關(guān)的數(shù)據(jù)庫設(shè)計正好可以結(jié)合 SQL 數(shù)據(jù)過濾的實操場景來講解。這里我先從最常用的成績單查詢場景出發(fā)講清楚過濾條件的組合與去重邏輯再看復(fù)雜查詢的優(yōu)化技巧。希望這篇的思路和示例代碼能直接幫到你。1. 數(shù)據(jù)過濾的底層邏輯先定位再輸出1.1 過濾的本質(zhì)是行級篩選數(shù)據(jù)過濾說白了就是在一張“滿是數(shù)據(jù)的大表格”里按照你給出的條件一行一行地把不需要的記錄擋在外面只保留符合條件的行。我習(xí)慣把它想象成一個漏斗漏斗口寬進來的數(shù)據(jù)多漏斗頸細篩掉的數(shù)據(jù)多。WHERE 子句就是那個漏斗頸。這個“逐行篩選”的過程是理解 SQL 過濾的第一步。很多新手會把 WHERE 和 SELECT 的順序搞混認為先寫 SELECT 就先生效其實數(shù)據(jù)庫執(zhí)行的時候是先從磁盤或內(nèi)存里把整張表的記錄拿出來然后用 FROM 后面的表按 WHERE 條件做逐行判斷符合條件的才進入下一步。等到 SELECT 真正輸出的時候數(shù)據(jù)已經(jīng)是“篩選后剩下的部分”了。我剛?cè)胄械臅r候做過一張學(xué)生成績表表里有 3000 多條記錄每天要按班級、科目、分數(shù)段導(dǎo)出一份成績單。當(dāng)時我圖的簡單直接用 SELECT 把所有列都輸出再放到 Excel 里手工篩選。后來有一次數(shù)據(jù)量突然翻到 3 萬條Excel 直接卡死逼著我老老實實學(xué) WHERE?，F(xiàn)在回頭看數(shù)據(jù)過濾這個技能越早主動掌握越好因為它是所有 SQL 查詢的骨架。1.2 SQL 執(zhí)行順序決定了你能在 WHERE 里干什么SQL 語句表面上是從 SELECT 開始寫的但數(shù)據(jù)庫的引擎執(zhí)行順序并不是按你書寫的順序來跑的。對于一條最常見的查詢來說實際的邏輯順序大致是FROM確定要從哪張表取數(shù)WHERE對 FROM 取到的每一行記錄做條件判斷GROUP BY按字段分組HAVING對分組后的結(jié)果做二次過濾SELECT挑選輸出哪些列ORDER BY排序LIMIT / TOP控制返回行數(shù)這個順序看起來枯燥但我建議你把它當(dāng)成一個“心法”來背。因為它直接決定了一個新手最容易踩的坑WHERE 子句里不能用 SELECT 里定義的列別名。比如SELECT stu_name, score AS s FROM score WHERE s 60;在 MySQL、PostgreSQL、SQL Server 里這條語句通常都會報錯“找不到列 s”因為執(zhí)行 WHERE 的時候SELECT 還沒開始處理輸出列AS s 還沒有誕生。遇到這種情況要么在 WHERE 里直接寫原始列名SELECT stu_name, score AS s FROM score WHERE score 60;要么套一層子查詢SELECT t.stu_name, t.s FROM ( SELECT stu_name, score AS s FROM score ) t WHERE t.s 60;很多人會覺得套子查詢非常啰嗦但當(dāng)你遇到復(fù)雜報表的時候這反而是最干凈的寫法因為內(nèi)層查出來的臨時結(jié)果集本身就是一張“新的表”外層可以隨意過濾它。同樣的邏輯在 JOIN 里也成立。如果在 LEFT JOIN 之后用 WHERE 去過濾右表的字段會發(fā)現(xiàn) LEFT JOIN 被“改成”了 INNER JOIN。因為 LEFT JOIN 會把右表沒有匹配的行保留下來這些行的右表字段全是 NULL而你用 WHERE 一過濾NULL 不滿足條件就又被刪掉了。這里我后續(xù)在講 NULL 時還會展開但先把這個“執(zhí)行順序決定行為”的意識建立起來。2. 條件表達式的編排從單條件到多條件組合2.1 比較運算符與“不等于”的寫法差異過濾條件最基礎(chǔ)的就是比較運算。、、、、 這五個符號幾乎沒有歧義誰都能看懂。真正容易出問題的是“不等于”SQL 里有兩個寫法 和 !。 是 SQL 標(biāo)準的寫法幾乎在所有數(shù)據(jù)庫里都能用! 是編程語言風(fēng)格的寫法MySQL 里能用SQL Server 里也能用PostgreSQL 里也能用但有些老牌的數(shù)據(jù)庫或某些兼容模式下就不支持。我自己寫的時候一律用 不是因為它多高級純粹是為了減少換數(shù)據(jù)庫時的意外報錯。還得提醒一個容易忽略的值類型問題比較運算符要求兩邊的類型能互相換算。如果拿字符串類型跟數(shù)值比較數(shù)據(jù)庫通常會做隱式轉(zhuǎn)換。比如SELECT * FROM score WHERE score 60;score 是數(shù)值列60 是字符串MySQL 會先把 60 轉(zhuǎn)成數(shù)值 60再把 score 列轉(zhuǎn)成數(shù)值比較?？雌饋聿挥绊懡Y(jié)果但是一旦 score 列里面的值比較復(fù)雜或者字段類型本身是 VARCHAR隱式轉(zhuǎn)換就可能造成無法命中索引這個我們在后面性能部分會重點說。2.2 NULL 的坑三值邏輯NULL 是 SQL 里一個非常特殊的存在它不表示 0也不表示空字符串它表示“不知道”或“不存在”。這就引出了一個叫“三值邏輯”的概念在 SQL 里比較運算的結(jié)果除了 TRUE 和 FALSE還有 UNKNOWN。所有對 NULL 做普通比較的表達式返回的都是 UNKNOWN被 WHERE 過濾掉。所以SELECT * FROM score WHERE score NULL;這條語句永遠一條記錄都查不出來。哪怕 score 列里真的有 NULL 值也不會被匹配。正確寫法是SELECT * FROM score WHERE score IS NULL;同理如果要篩選“分數(shù)不為空”的記錄SELECT * FROM score WHERE score IS NOT NULL;我見過很多新手在這里反復(fù)踩坑最典型的就是把 IS NULL 直接改成 NULL然后排查半天也找不到原因。這里有一個日常類比NULL 就像你并不知道一個人的聯(lián)系方式你在通訊錄里找“聯(lián)系方式等于空的人”系統(tǒng)并不知道該怎么匹配“空”不是一個具體的聯(lián)系人它只能靠“查一下聯(lián)系人這一欄是否為空”這個特殊動作來確定。除此之外NULL 還有一個算術(shù)行為任何數(shù)值和 NULL 做加減乘除結(jié)果都是 NULL不是原值。比如想要算總分時SELECT score_1 score_2 score_3 AS total_score FROM score;只要 score_2 是 NULL整個 total_score 就是 NULL。遇到這種情況要么用 COALESCE 函數(shù)把 NULL 轉(zhuǎn)成 0要么用 IS NULL 先做條件判斷把缺失的字段單獨挑出來處理。這個細節(jié)在做成績匯總時特別重要否則你會莫名其妙地發(fā)現(xiàn)“明明成績都不低總分卻不出數(shù)”。2.3 AND / OR 優(yōu)先級與括號多條件組合時AND 和 OR 的優(yōu)先級是個老掉牙但永遠有人踩的坑。AND 的優(yōu)先級高于 OR也就是數(shù)據(jù)庫先處理 AND再處理 OR。這跟數(shù)學(xué)里的“先乘除后加減”邏輯很像。舉個例子。我們要篩選“三班且成績大于 60或者五班且成績大于 80”的學(xué)生SELECT * FROM score WHERE class_id 3 AND score 60 OR class_id 5 AND score 80;這樣的寫法因為 AND 優(yōu)先結(jié)果是對的。但如果漏掉了想表達的意思比如把“班級為 3 班的成績大于 60 分或者成績大于 80 分”寫成SELECT * FROM score WHERE class_id 3 AND score 60 OR score 80;它的意思是class_id 3 且 score 60 的所有記錄再加上 score 80 的全部記錄。后者已經(jīng)包含了所有班級完全不是“三班 80 分”這種窄范圍。要避免這種歧義最穩(wěn)妥的辦法就是加括號SELECT * FROM score WHERE class_id 3 AND (score 60 OR score 80);我自己的習(xí)慣是只要條件里同時出現(xiàn) AND 和 OR一律加括號哪怕有時括號是多余的。寫代碼是給人看的清晰比聰明重要。數(shù)據(jù)庫不會因為少一個括號就報錯但業(yè)務(wù)同事會因為多了一個錯誤條件而出錯誤報表兩相比較括號的成本太低了。2.4 BETWEEN、IN、LIKE 的邊界行為這三個運算符在實際過濾中非常常用但邊界行為很容易被忽略。BETWEEN 是包含邊界值的。BETWEEN 60 AND 80 實際上等價于 score 60 AND score 80。注意它不是等價于 score 60 AND score 80。我之前做成績區(qū)間統(tǒng)計用 BETWEEN 60 AND 79 表示“及格”結(jié)果 79 分的被算了進去80 分的沒被算進去導(dǎo)致統(tǒng)計口徑跟領(lǐng)導(dǎo)預(yù)期的“80 分以上才算優(yōu)秀”對不上來回對了好幾遍才發(fā)現(xiàn)是邊界包含的問題。IN 的作用是匹配一組固定值。比如篩選一班、三班、五班SELECT * FROM score WHERE class_id IN (1, 3, 5);這個大家都會用但它有一個非常隱蔽的坑如果 IN 后面的列表里有 NULL倒不會出錯但如果子查詢返回的值包含 NULL那么配合 NOT IN 就會出大問題。SELECT * FROM score WHERE class_id NOT IN (1, 3, 5);這條看起來是“查不屬于 1、3、5 班的數(shù)據(jù)”但如果把 1、3、5 換成從另一個子查詢得來的集合而子查詢結(jié)果里存在 NULL那么整個 NOT IN 會返回空集——不會報錯但一條數(shù)據(jù)都沒有。原因是當(dāng) class_id 4而集合里有一個 NULL 時4 不等于 NULL這個判斷結(jié)果是 UNKNOWNUNKNOWN 會被過濾掉所以一行都留不下來。如果你真的想表達“排除某些班級”而且要安全處理 NULL最好用 NOT EXISTS 替代 NOT IN這個我們在后續(xù)章節(jié)講子查詢時再提。LIKE 是模糊匹配的關(guān)鍵工具。% 表示任意長度的任意字符_ 表示一個任意字符。比如SELECT * FROM student WHERE stu_name LIKE 張%;會查出所有姓張的學(xué)生。如果想查第二個字是“三”的姓名SELECT * FROM student WHERE stu_name LIKE _三%;LIKE 還有一個轉(zhuǎn)義問題如果數(shù)據(jù)本身包含 % 或 _ 字符比如課程名稱叫“C語言_基礎(chǔ)”直接寫 LIKE %C語言_基礎(chǔ)%下劃線會被當(dāng)成通配符匹配出一個奇怪的集合。解決方法是顯式指定轉(zhuǎn)義字符SELECT * FROM course WHERE course_name LIKE %C語言\_基礎(chǔ)% ESCAPE \;ESCAPE 后面的字符表示緊跟其后的通配符按普通字符處理。這個用法用得不多但真遇到了卡住你半天的情況。因為沒人會告訴你數(shù)據(jù)里還有這些特殊字符你只會奇怪“明明有這條記錄為什么查不出來”。3. 去重過濾DISTINCT 與窗口函數(shù)的實戰(zhàn)選擇3.1 DISTINCT 只能做顯式去重數(shù)據(jù)過濾有時候不只是“去掉不滿足條件的行”還需要“去掉重復(fù)的行”。最直接的就是 DISTINCTSELECT DISTINCT class_id FROM score;這樣能查出所有出現(xiàn)的班級不會重復(fù)。但 DISTINCT 有一個關(guān)鍵限制它對 SELECT 后面出現(xiàn)的整組列做去重。什么意思呢比如SELECT DISTINCT class_id, subject FROM score;它會把 class_id 和 subject 的組合認為是“一行”來去重。一班語文、一班數(shù)學(xué)算是兩條不同記錄因為 subject 不同所以不會被合并。如果你只是想看“有哪些班級”卻誤寫成查兩個字段結(jié)果會比你預(yù)想的多。DISTINCT 還有一個我認為更值得注意的局限它只能去掉整行完全一樣的重復(fù)項并不能按某列去重并保留一行的完整信息。比如學(xué)生選修課表里每個學(xué)生有多條選課記錄現(xiàn)在想取每個學(xué)生最新選課的那條DISTINCT 就完全做不到。這時候就需要窗口函數(shù)。3.2 排名窗口函數(shù)的“最新記錄”過濾窗口函數(shù) ROW_NUMBER() 是處理“分組取最新”場景的利器。它可以在不合并行的情況下給每一行按分組編號。比如學(xué)生選課表 enrollstudent_idcourse_nameenroll_date101數(shù)學(xué)2024-09-01101英語2024-09-10102數(shù)學(xué)2024-09-02要取每個學(xué)生最新的一條選課記錄寫法如下SELECT student_id, course_name, enroll_date FROM ( SELECT student_id, course_name, enroll_date, ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY enroll_date DESC) AS rn FROM enroll ) t WHERE rn 1;內(nèi)層查詢給每個學(xué)生按 enroll_date 倒序編號日期最新的編號為 1外層用 WHERE rn 1 過濾就拿到了每個學(xué)生最新的一條記錄。這個寫法比 DISTINCT 靈活得多因為它保留了所有原始列信息而且篩選條件可以隨意改。比如要取每個學(xué)生“第二個最早”的記錄就把 rn 2 即可。這種技巧在處理數(shù)據(jù)遷移、對賬、埋點日志去重時非常常見。同樣用“去重”這個熱詞時我還推薦另一種思路如果確認數(shù)據(jù)里存在完全重復(fù)的行可以用 GROUP BY 把所有判斷重復(fù)的列分組再聚合其他列。比如統(tǒng)計班級數(shù)SELECT class_id, COUNT(*) AS cnt FROM score GROUP BY class_id;這既能去重又能計數(shù)一步到位。難點在于你要搞清楚“去重的鍵”是什么是單個字段還是多個字段的組合。這個決定了用 DISTINCT、GROUP BY 還是 ROW_NUMBER()。3.3 去重場景的選型建議我在實際業(yè)務(wù)里總結(jié)的選型邏輯很簡單只要整行完全重復(fù)想直接去掉優(yōu)先用 DISTINCT要看某個字段值的分布比如有哪些班級、哪些科目用 DISTINCT 或 GROUP BY 均可要按組保留一條明細記錄比如每位用戶最新登錄、每張訂單最后狀態(tài)用 ROW_NUMBER() 外層過濾要去重后還要做聚合統(tǒng)計比如按班級算平均分用 GROUP BY。這套選型邏輯聽上去很基礎(chǔ)但很多工作了三五年的開發(fā)都可能搞混。我見過一個線上報表原意是統(tǒng)計每個商品的最近一次售價有人用 DISTINCT 做出來數(shù)據(jù)錯亂又有人用子查詢做出來性能極差最后改成窗口函數(shù)一次解決。窗口函數(shù)不復(fù)雜只是平時沒有機會練手建議你在本地數(shù)據(jù)庫里建一張 1 萬行的表多試幾組 PARTITION BY 和 ORDER BY 組合很快就熟了。4. 過濾語句的性能索引、通配符與執(zhí)行計劃4.1 索引命中三件事等值、范圍、前綴過濾條件寫得對結(jié)果對但跑得很慢同樣是個大問題。我在處理慢 SQL 優(yōu)化時第一件事永遠都是看 WHERE 條件能不能命中索引。只要過濾字段上有合適的索引數(shù)據(jù)庫就不用從頭到尾掃全表性能能提升一個量級以上。讓索引生效的前提主要有三個第一過濾列盡量用等值條件比如 WHERE student_id 101這里 student_id 只要建了索引直接按索引樹查找。范圍條件也能用索引但效率比等值稍低比如 WHERE score 60 會走索引范圍掃描。第二不要在索引列上做函數(shù)或運算。比如SELECT * FROM score WHERE YEAR(create_time) 2024;這條語句在 create_time 有索引的前提下依然會慢。因為數(shù)據(jù)庫要對每一行的 create_time 先執(zhí)行 YEAR() 函數(shù)計算再拿結(jié)果跟 2024 比原來的索引就用不上了。正確寫法是SELECT * FROM score WHERE create_time 2024-01-01 AND create_time 2025-01-01;這樣一來create_time 就變成了一個范圍條件索引就能正常走。第三避免隱式類型轉(zhuǎn)換。比如字段 score 是 VARCHAR卻用 WHERE score 60 去查數(shù)據(jù)庫會嘗試把 score 列的值全部轉(zhuǎn)成數(shù)值再跟 60 比這就相當(dāng)于在索引列上做了函數(shù)運算。正確做法是讓參數(shù)的類型和字段類型保持一致如果字段是 VARCHAR就寫 WHERE score 60。這三個原則不算高深但做起來需要養(yǎng)成習(xí)慣。尤其是你會發(fā)現(xiàn)很多 ORM 框架會自動幫程序員把字段包一層函數(shù)導(dǎo)致線上 SQL 慢得離譜最后排查發(fā)現(xiàn)就是這層函數(shù)把索引毀掉的。4.2 LIKE 模糊匹配的前綴優(yōu)先原則LIKE 是過濾中性能差距最大的一個點。規(guī)則很簡單WHERE name LIKE 張% -- 前綴匹配能走索引 WHERE name LIKE %三% -- 包含匹配無法走索引 WHERE name LIKE %三 -- 后綴匹配無法走索引數(shù)據(jù)庫的索引本質(zhì)上是一棵排好序的樹前綴是確定的樹可以按順序查找前綴不確定樹就沒法定位起點只能全表掃描。所以寫模糊條件時要盡量把常量放在前面把通配符放在后面優(yōu)先命中前綴匹配。如果業(yè)務(wù)需求真的需要包含匹配比如搜索姓名里含某個關(guān)鍵詞前綴匹配解決不了有兩個思路一是用全文索引MySQL 的 FULLTEXT適合大量文本場景二是盡量縮小其他先決條件的范圍比如先按班級、按狀態(tài)過濾把數(shù)據(jù)量先減下來再做 LIKE避免在小范圍里全表掃。我實際調(diào)優(yōu)過一張 1000 萬行的商品表原本搜索詞是 %手機%查詢耗時 8 秒后來業(yè)務(wù)方改了需求允許只按前綴搜索查詢耗時直接降到 0.05 秒。性能差別就是這么大。在跟業(yè)務(wù)方提方案時可以優(yōu)先把精度要求擺在前面想辦法讓他們接受前綴搜索實在不行再考慮索引方案。4.3 慢查詢定位看執(zhí)行計劃寫 SQL 的時候誰都說不準優(yōu)化器到底會怎么跑最好的辦法就是直接看執(zhí)行計劃。MySQL 里在查詢前面加 EXPLAINSQL Server 里用 SET SHOWPLAN_ALLPostgreSQL 里用 EXPLAIN ANALYZE。例如EXPLAIN SELECT * FROM score WHERE class_id 1 AND score 80;執(zhí)行計劃里最重要的字段是 type從好到差大致是 const、eq_ref、ref、range、index、ALL。ALL 代表全表掃描是性能最差的情況。還有 rows它顯示優(yōu)化器預(yù)估掃描的行數(shù)這個數(shù)字越接近表的總行數(shù)越說明過濾沒生效。我第一次用 EXPLAIN 是被一個線上報表逼的。一張訂單表 800 萬行按商戶編號和創(chuàng)建時間過濾查詢要 20 多秒。EXPLAIN 一看type 是 ALLrows 顯示 800 萬說明索引完全沒走到。后來檢查發(fā)現(xiàn)過濾字段上根本沒有建索引。建了組合索引之后type 變成了 rangerows 降到 5 萬查詢時間降到 0.3 秒以內(nèi)。從此以后凡是慢查詢我第一時間先看執(zhí)行計劃不看執(zhí)行計劃瞎優(yōu)化就是盲人摸象。5. 動態(tài)過濾條件的安全底線SQL 注入的攻與防5.1 注入是怎么發(fā)生的字符串拼接數(shù)據(jù)過濾既然經(jīng)常要處理動態(tài)條件比如用戶在前端輸入一個班級名稱后端拼接成 SQL 去查那就必須正視 SQL 注入這個問題。雖然聽起來像網(wǎng)絡(luò)安全專題但做數(shù)據(jù)查詢的人如果不懂早晚會把線上數(shù)據(jù)搞到不可收拾。危險代碼長這樣以 Python 為例sql SELECT * FROM student WHERE class_name class_name cursor.execute(sql)如果 class_name 是用戶傳進來的攻擊者在表單里填這一段 OR 11拼出來的 SQL 就是SELECT * FROM student WHERE class_name OR 11這個條件永遠為真整張表的學(xué)生信息都會被查出來。如果攻擊者再結(jié)合 UNION 查詢其他表甚至可以直接讀取用戶表、訂單表后果不堪設(shè)想。這個攻擊之所以能成功根源在于 “用戶輸入被當(dāng)成了 SQL 代碼的一部分”執(zhí)行。過濾條件的本意是只讓數(shù)據(jù)按預(yù)期輸出但因為拼接輸入里的單引號改變了 SQL 的結(jié)構(gòu)。我早期練手寫管理系統(tǒng)時曾經(jīng)在登錄功能里直接用字符串拼接驗證某個賬號密碼sql SELECT * FROM user WHERE username username AND password password 當(dāng)時根本沒有安全意識直到后來讀到“萬能密碼”示例才冒冷汗。攻擊者在用戶名框輸入 admin --密碼框隨便填拼出來的 SQL 變成SELECT * FROM user WHERE username admin -- AND password xxx在 SQL 注釋符 -- 之后的所有內(nèi)容都會被忽略于是這條 SQL 退化成只校驗用戶名不校驗密碼。只要知道一個用戶名是 admin就能直接登錄系統(tǒng)。這就是熱詞里“萬能密碼繞過”的原理。它讓 SQL 的過濾邏輯徹底失效從一個“按條件篩選”變成了“無條件通過”。5.2 參數(shù)化查詢才是正解針對 SQL 注入最有效的防御不是寫各種過濾函數(shù)而是使用參數(shù)化查詢。它的核心思想是把 SQL 的結(jié)構(gòu)和用戶輸入的數(shù)據(jù)分開傳遞數(shù)據(jù)庫先解析 SQL 結(jié)構(gòu)再把參數(shù)當(dāng)成純數(shù)據(jù)注入而不是當(dāng)成 SQL 代碼來解釋。Python 的寫法是用占位符sql SELECT * FROM student WHERE class_name %s cursor.execute(sql, (class_name,))Java 用 PreparedStatementPreparedStatement ps conn.prepareStatement(SELECT * FROM student WHERE class_name ?); ps.setString(1, className);PHP 用 PDO$stmt $pdo-prepare(SELECT * FROM student WHERE class_name ?); $stmt-execute([$className]);我在給團隊做代碼評審時有一條硬性規(guī)定動態(tài) SQL 一律禁止直接拼接字符串必須用參數(shù)化。因為再完善的過濾函數(shù)都有可能被繞過單引號、雙引號、注釋符、十六進制編碼等花式變體能玩出各種花樣而參數(shù)化查詢是從語法層面杜絕了注入的可能性。這里有個小技巧如果一個查詢語句里既有固定條件又有用戶可控的排序字段、表名這種無法參數(shù)化的部分那就要單獨做一個白名單校驗而不是盲目拼接。比如排序字段只能從 name、score、create_time 里選就在代碼里先校驗后再拼進 SQL。5.3 過濾條件的權(quán)限邊界最后補充一點數(shù)據(jù)過濾不只是技術(shù)層面的 WHERE 條件還隱含了權(quán)限控制。一個普通的業(yè)務(wù)查詢?nèi)绻脩魝髁?class_id 1后端應(yīng)該同時帶上當(dāng)前登錄用戶的查看權(quán)限范圍。比如只允許查看自己所屬班級的數(shù)據(jù)那 SQL 就應(yīng)該是WHERE class_id %s AND region %sdata_filter 的粒度越細越能防止“越權(quán)查詢”。我見過不少系統(tǒng)功能看著都有但接口沒有做行級權(quán)限過濾導(dǎo)致一個普通用戶傳入大范圍的條件就能把全公司的數(shù)據(jù)拉出來。這類問題在數(shù)據(jù)層面比 SQL 注入更難發(fā)現(xiàn)因為 SQL 本身沒有語法錯誤只是少了一個條件。6. 常見問題速查與排坑實錄6.1 過濾條件常見錯誤速查表錯誤寫法問題表現(xiàn)正確寫法WHERE score NULL查不到記錄WHERE score IS NULLWHERE class_id NOT IN (子查詢有 NULL)返回空集WHERE NOT EXISTSWHERE YEAR(create_time) 2024索引失效慢查詢WHERE create_time 2024-01-01 AND create_time 2025-01-01WHERE name LIKE %關(guān)鍵詞%全表掃描數(shù)據(jù)量大時極慢盡可能改為前綴匹配或配合其他等值條件縮小范圍WHERE class_id 1 OR class_id 2 AND score 80結(jié)果集不符合預(yù)期WHERE (class_id 1 OR class_id 2) AND score 80WHERE s 60s 是 SELECT 別名報錯找不到列WHERE score 60 或嵌套子查詢SELECT DISTINCT col1, col2組合去重單列不重復(fù)明確需求按需選擇列這張表是我做內(nèi)部分享時一直保留的每次講數(shù)據(jù)過濾總有人能從里面找到自己剛踩過的坑。6.2 我踩過的三個真實案例第一個案例是成績匯總時大量 NULL 導(dǎo)致總分為空。當(dāng)時導(dǎo)出的成績表里缺考學(xué)生的成績是 NULL不是 0。我用最簡單的方式去算總分結(jié)果缺考學(xué)生的總分為空整個排名表錯亂。后來我在匯總前先做了 COALESCE(score, 0)才算對。第二個案例是 NOT IN 的 NULL 陷阱。查詢某個班級的補考名單排除掉一些特殊狀態(tài)的數(shù)據(jù)子查詢返回值里有一個 NULL整個查詢結(jié)果為空我一度以為是數(shù)據(jù)被誤刪了排查了兩個小時才發(fā)現(xiàn)是 NULL 的問題。從那以后我寫 NOT IN 之前都會下意識檢查子查詢結(jié)果是否存在 NULL。第三個案例是 LIKE 模糊搜索導(dǎo)致報表超時。當(dāng)時數(shù)據(jù)量 500 萬搜索姓名的 %XX% 寫法約 6 秒才能出結(jié)果前端一直轉(zhuǎn)圈。后來把搜索需求改成了前綴匹配響應(yīng)時間降到百毫秒級別同時把搜索詞做了長度和白名單限制。有時候不是你 SQL 寫得不好而是業(yè)務(wù)方的需求本來就可以調(diào)整優(yōu)化。6.3 數(shù)據(jù)過濾的調(diào)試方法調(diào)試過濾條件時我有幾個固定的操作習(xí)慣先把 WHERE 條件摘出來單獨跑一遍看返回行數(shù)是否符合預(yù)期。比如疑似多查了數(shù)據(jù)先確認條件數(shù)量再分析哪個條件放得太寬。用 EXPLAIN 看執(zhí)行計劃和預(yù)估行數(shù)確認過濾條件有沒有命中索引。這是排查“慢”的唯一可靠手段不要憑感覺猜。對復(fù)雜條件從內(nèi)到外拆分驗證。比如多層子查詢先查最內(nèi)層的結(jié)果集再逐層往外查每查一層都停下來看數(shù)據(jù)是否符合預(yù)期。這個習(xí)慣能幫你快速定位是子查詢寫錯了、還是內(nèi)外層關(guān)聯(lián)條件沒帶全。我自己在處理數(shù)據(jù)任務(wù)時還會用臨時表分步處理先 SELECT 到臨時表再對臨時表做過濾和二次加工。這種方式雖然多寫幾步但每一步的結(jié)果都看得見出了錯容易追蹤。等邏輯完全驗證正確后再考慮合并成一條復(fù)雜 SQL 去做性能優(yōu)化。收尾一個小技巧最后分享一個我常用的實用習(xí)慣復(fù)雜的過濾條件建議盡量寫成“范圍條件”的形式不要把邏輯全部堆在 OR 里面。例如篩選指定班級、指定科目、指定分數(shù)段優(yōu)先寫成多個 AND 條件拼接而不是每個值都用 OR 窮舉。這樣優(yōu)化器更容易命中組合索引SQL 可讀性也更強。數(shù)據(jù)過濾這個主題看起來是最基礎(chǔ)的 SQL 功能但真正寫好的關(guān)鍵在于對數(shù)據(jù)本身的敏感度——你知道哪些列可能為 NULL哪些字段存在特殊字符哪些條件能走索引。多練多想多記錄排坑經(jīng)驗這些積累會慢慢變成你的肌肉記憶。