相信不少人跟我一樣SQL語(yǔ)法這門課上學(xué)時(shí)候背了工作之后忘了真到寫查詢的時(shí)候全靠搜索引擎和過(guò)往代碼片段拼湊。尤其是你手里的數(shù)據(jù)庫(kù)還不止一種今天對(duì)付MySQL明天切換SQL Server后天領(lǐng)導(dǎo)又扔過(guò)來(lái)一份PG的慢查詢?nèi)罩尽@時(shí)候最需要的不是一本完整的SQL教程而是一篇能直接對(duì)照著干活的語(yǔ)法實(shí)戰(zhàn)筆記。這篇文章就是干這個(gè)用的。我會(huì)從最常用的SELECT語(yǔ)法全景講起把去重、分頁(yè)、日期處理這些高頻場(chǎng)景的寫法逐個(gè)拆開(kāi)再往前走一步聊SQL注入的防護(hù)姿勢(shì)和慢查詢優(yōu)化的排查思路順手把SQL Server、MySQL、PostgreSQL這幾種主流數(shù)據(jù)庫(kù)的方言差異做個(gè)對(duì)照。適用對(duì)象是剛?cè)腴T想系統(tǒng)梳理語(yǔ)法的新手以及寫過(guò)一陣子SQL但總在細(xì)節(jié)上卡殼的開(kāi)發(fā)者運(yùn)維同事看了也能當(dāng)速查手冊(cè)。1. 先搞懂SQL的定位它不是拿來(lái)背的是拿來(lái)用的很多人學(xué)SQL語(yǔ)法有個(gè)誤區(qū)覺(jué)得把SELECT、INSERT、UPDATE、DELETE這四類語(yǔ)句背得滾瓜爛熟就算學(xué)會(huì)了。實(shí)際工作中你會(huì)發(fā)現(xiàn)這四類是骨架真正讓SQL發(fā)揮價(jià)值的是你組合使用它們的方式以及你對(duì)數(shù)據(jù)結(jié)構(gòu)的理解深度。1.1 SQL在技術(shù)棧里到底站在哪一層SQLStructured Query Language是操作關(guān)系型數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)語(yǔ)言不管是MySQL、SQL Server、PostgreSQL還是Oracle核心語(yǔ)法都遵循同一套標(biāo)準(zhǔn)。你在A數(shù)據(jù)庫(kù)上寫的SELECT搬到B數(shù)據(jù)庫(kù)上大概率能跑只是某些函數(shù)名和專用語(yǔ)法會(huì)有差異——這個(gè)后面專門講。它在整個(gè)技術(shù)棧里的位置在應(yīng)用層和存儲(chǔ)層之間。應(yīng)用發(fā)來(lái)請(qǐng)求你寫一段SQL去數(shù)據(jù)庫(kù)里取數(shù)、改數(shù)或者刪數(shù)。這也就意味著SQL的好壞直接決定了接口快不快、報(bào)表出不出數(shù)、任務(wù)會(huì)不會(huì)超時(shí)。你可能Java寫得很好Python也很溜但SQL寫成一坨性能照樣拉胯。1.2 為什么語(yǔ)法看似簡(jiǎn)單寫出來(lái)的東西卻總不對(duì)我見(jiàn)過(guò)太多人卡在同一個(gè)地方邏輯對(duì)順序錯(cuò)。比如在WHERE里用SELECT子句中才定義的別名或者在GROUP BY之后試圖用原始列做條件過(guò)濾。這些問(wèn)題的根子都在于沒(méi)有真正理解SQL各子句的執(zhí)行順序而不僅僅是語(yǔ)法本身。舉一個(gè)典型例子SELECT department_id, COUNT(*) AS emp_count FROM employees WHERE salary 5000 GROUP BY department_id HAVING COUNT(*) 10 ORDER BY emp_count DESC;看起來(lái)沒(méi)什么問(wèn)題但如果你在WHERE里寫成WHERE emp_count 10那必報(bào)錯(cuò)。原因很簡(jiǎn)單WHERE是在SELECT之前執(zhí)行的此時(shí)別名emp_count還不存在。學(xué)SQL語(yǔ)法核心不是背關(guān)鍵字而是掌握它的執(zhí)行順序和邏輯層次。順序搞明白了寫復(fù)雜嵌套查詢才能穩(wěn)。2. 一張覆蓋日常90%工作量的SELECT語(yǔ)法全景圖說(shuō)來(lái)說(shuō)去日常開(kāi)發(fā)里我們最常用的還是查詢語(yǔ)句。SELECT的語(yǔ)法結(jié)構(gòu)說(shuō)復(fù)雜也復(fù)雜說(shuō)簡(jiǎn)單也簡(jiǎn)單但很多人對(duì)它的理解是碎片化的。2.1 完整SELECT語(yǔ)法結(jié)構(gòu)與執(zhí)行順序一條完整的查詢語(yǔ)句長(zhǎng)這樣SELECT [DISTINCT] 列1, 列2, ... FROM 表1 [INNER | LEFT | RIGHT] JOIN 表2 ON 連接條件 WHERE 過(guò)濾條件 GROUP BY 分組列 HAVING 分組后的過(guò)濾條件 ORDER BY 排序列 [ASC | DESC] LIMIT 偏移量, 返回行數(shù)這里面最容易被忽略的是執(zhí)行順序。我畫過(guò)無(wú)數(shù)次給新人看這里直接寫給你FROM / JOIN先確定數(shù)據(jù)源把多張表連接起來(lái)生成中間結(jié)果集WHERE對(duì)中間結(jié)果集做逐行過(guò)濾GROUP BY把過(guò)濾后的行按指定列分組HAVING對(duì)分組結(jié)果做過(guò)濾SELECT投影需要的列計(jì)算表達(dá)式生成最終的目標(biāo)列ORDER BY對(duì)最終結(jié)果排序LIMIT截取指定范圍的行記住這個(gè)順序你就明白兩個(gè)高頻報(bào)錯(cuò)的根源為什么WHERE不能用SELECT里的別名因?yàn)镾ELECT還沒(méi)執(zhí)行別名不存在。為什么HAVING能用聚合函數(shù)WHERE不能因?yàn)閃HERE是在GROUP BY之前執(zhí)行的此時(shí)還沒(méi)分組聚合無(wú)從談起。2.2 WHERE過(guò)濾的藝術(shù)不只是等于和大于WHERE子句看起來(lái)最簡(jiǎn)單實(shí)際最容易踩坑的都在這里。幾個(gè)我工作中經(jīng)常發(fā)現(xiàn)同事寫錯(cuò)的地方空值判斷必須用IS NULL不能寫 NULL。這是SQL里最經(jīng)典的坑。NULL不是一個(gè)值它表示“未知”所以任何與NULL的等值比較結(jié)果都是未知永遠(yuǎn)不會(huì)為真。正確寫法是WHERE column IS NULL或者WHERE column IS NOT NULL。字符串比較的隱式轉(zhuǎn)換問(wèn)題。在MySQL里如果某列是字符串類型你寫WHERE phone 13800138000MySQL會(huì)嘗試把列值轉(zhuǎn)成數(shù)字再比較如果這一列有非數(shù)字字符可能會(huì)匹配出意料之外的結(jié)果。穩(wěn)妥的寫法是給字符串類型加引號(hào)WHERE phone 13800138000。IN和EXISTS的選擇。小表驅(qū)動(dòng)大表時(shí)EXISTS往往比IN更高效。原因在于EXISTS是逐行判斷、遇到匹配就停止短路而IN通常要把子查詢結(jié)果完整物化出來(lái)再比對(duì)。當(dāng)然現(xiàn)代優(yōu)化器已經(jīng)有了很多改寫優(yōu)化但習(xí)慣上我仍然建議子查詢結(jié)果集很小用IN外部表小、內(nèi)部表大用EXISTS。2.3 JOIN的連接邏輯INNER、LEFT、RIGHT的語(yǔ)義邊界連接的語(yǔ)義一定要搞清楚。很多人把LEFT JOIN當(dāng)成“附加列”的工具卻常常忽略掉它產(chǎn)生的NULL行。用最直白的方式解釋INNER JOIN只保留兩邊都滿足條件的行其他丟棄LEFT JOIN左邊的表FROM后面的表所有行都保留右邊表有匹配就帶上沒(méi)匹配就補(bǔ)NULLRIGHT JOIN道理一樣以右邊的表為基準(zhǔn)保留全部行FULL OUTER JOIN兩邊都保留沒(méi)匹配的補(bǔ)NULL——但要注意MySQL原生不支持需要UNION模擬實(shí)際項(xiàng)目里L(fēng)EFT JOIN用得最多。但有個(gè)細(xì)節(jié)值得注意LEFT JOIN之后如果在WHERE里加了對(duì)右表字段的過(guò)濾條件這個(gè)JOIN很可能會(huì)被優(yōu)化器改寫成INNER JOIN。因?yàn)閃HERE條件是最終結(jié)果集的硬性過(guò)濾一旦右表字段不滿足條件就必須剔除那LEFT JOIN保留的NULL行反正也過(guò)不了過(guò)濾等價(jià)于內(nèi)連接。這個(gè)坑我踩過(guò)不止一次。業(yè)務(wù)方要“左表全量右表補(bǔ)充”結(jié)果開(kāi)發(fā)在WHERE里加了右表的條件數(shù)據(jù)直接變少還排查了很久。血的教訓(xùn)對(duì)左連接保留語(yǔ)義的過(guò)濾條件應(yīng)該寫在ON子句里而不是WHERE里。3. 高頻實(shí)戰(zhàn)寫法去重、分頁(yè)、日期與字符串處理這一節(jié)我給你整理幾組真正天天要用的SQL語(yǔ)法寫法每一個(gè)都附帶適用場(chǎng)景和注意事項(xiàng)。這些內(nèi)容不是教科書上的名詞解釋而是我從實(shí)際項(xiàng)目中提煉出來(lái)、反復(fù)驗(yàn)證過(guò)的可靠方案。3.1 去重的三條路DISTINCT、GROUP BY、窗口函數(shù)搜索熱詞里“sql語(yǔ)句去重”出現(xiàn)了不止一次可見(jiàn)這是多常見(jiàn)又多變的需求。去重這件事情看起來(lái)簡(jiǎn)單實(shí)際上根據(jù)“去重到什么粒度”和“需要保留哪些信息”寫法的差別很大。場(chǎng)景一完全重復(fù)的行只留一條這種最簡(jiǎn)單的去重直接SELECT DISTINCT col1, col2 FROM table。它返回的是組合列不重復(fù)的所有行。場(chǎng)景二按某個(gè)字段去重但需要返回其他字段的完整信息比如每個(gè)用戶最近一條訂單或者每個(gè)部門工資最高的人。DISTINCT就不好使了因?yàn)樗荒鼙ＷC整行組合不重復(fù)沒(méi)法指定“按user_id保留最新一條”。此時(shí)用窗口函數(shù)是最優(yōu)雅的SELECT user_id, order_id, order_amount, order_time FROM ( SELECT user_id, order_id, order_amount, order_time, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_time DESC) AS rn FROM orders ) t WHERE rn 1;窗口函數(shù)的邏輯是先把數(shù)據(jù)按user_id分區(qū)在每個(gè)分區(qū)內(nèi)按order_time降序編號(hào)然后取每組編號(hào)為1的行。這一步既完成了去重又保留了“最新一條”的業(yè)務(wù)含義可讀性也很好。場(chǎng)景三統(tǒng)計(jì)去重后的數(shù)量計(jì)算活躍用戶數(shù)、獨(dú)立訪客數(shù)直接用COUNT(DISTINCT user_id)這個(gè)用法也經(jīng)常出現(xiàn)在報(bào)表SQL里。注意COUNT(DISTINCT)在數(shù)據(jù)量大的時(shí)候性能并不好因?yàn)樗枰~外的排序或哈希操作。如果只是要知道大概的數(shù)量級(jí)用APPROX_COUNT_DISTINCTSQL Server支持或HyperLogLog方案可能更務(wù)實(shí)。3.2 分頁(yè)查詢的正確姿勢(shì)LIMIT的偏移量陷阱分頁(yè)是每個(gè)后端必寫的功能。MySQL的寫法很直接SELECT * FROM orders ORDER BY create_time DESC LIMIT 20 OFFSET 0; -- 第1頁(yè) SELECT * FROM orders ORDER BY create_time DESC LIMIT 20 OFFSET 20; -- 第2頁(yè)OFFSET越大查詢?cè)铰@是LIMIT分頁(yè)的經(jīng)典問(wèn)題。原因是數(shù)據(jù)庫(kù)需要掃描并丟棄前面所有的行才能拿到目標(biāo)數(shù)據(jù)。數(shù)據(jù)量在幾十萬(wàn)以內(nèi)還好上了百萬(wàn)深分頁(yè)會(huì)直接拖垮接口。更穩(wěn)健的替代方案是鍵集分頁(yè)Keyset Pagination也就是利用排序條件里的唯一鍵來(lái)做游標(biāo)-- 假設(shè)上一頁(yè)最后一條記錄的create_time是2024-06-01 12:00:00id是1024 SELECT * FROM orders WHERE create_time 2024-06-01 12:00:00 OR (create_time 2024-06-01 12:00:00 AND id 1024) ORDER BY create_time DESC, id DESC LIMIT 20;它的思路是拿“上一頁(yè)的最后一條”作為邊界不斷往后翻。因?yàn)樗饕梢跃_命中起點(diǎn)所以不管翻到第幾頁(yè)性能都穩(wěn)定。如果你的項(xiàng)目有深分頁(yè)的需求這個(gè)寫法值得掌握。SQL Server的分頁(yè)語(yǔ)法不一樣用的是OFFSET FETCHSELECT * FROM orders ORDER BY create_time DESC OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY;從語(yǔ)法上看邏輯是一樣的先跳過(guò)20行再取接下來(lái)的20行。3.3 日期與時(shí)間處理的三個(gè)高頻函數(shù)日期處理是SQL語(yǔ)法里繞不開(kāi)的部分幾乎每個(gè)報(bào)表都涉及。MySQL里最常用的三個(gè)DATE_FORMAT(date, %Y-%m-%d)把日期格式化成指定字符串DATEDIFF(date1, date2)計(jì)算兩個(gè)日期相差的天數(shù)DATE_SUB(date, INTERVAL n DAY)日期加減按月份分組的經(jīng)典寫法SELECT DATE_FORMAT(create_time, %Y-%m) AS month, COUNT(*) AS order_count, SUM(order_amount) AS total_amount FROM orders WHERE create_time DATE_SUB(CURDATE(), INTERVAL 6 MONTH) GROUP BY DATE_FORMAT(create_time, %Y-%m) ORDER BY month DESC;處理日期有個(gè)非常重要的細(xì)節(jié)能用日期范圍過(guò)濾就盡量別在WHERE里套函數(shù)。比如要查2024年6月的訂單寫成WHERE create_time 2024-06-01 AND create_time 2024-07-01索引能用得上寫成WHERE DATE_FORMAT(create_time, %Y-%m) 2024-06索引就廢了因?yàn)楹瘮?shù)改變了列的值優(yōu)化器沒(méi)法直接走索引。這是慢查詢排查中最常見(jiàn)的根因之一后文還會(huì)詳述。3.4 字符串聚合與拼接GROUP_CONCAT和STRING_AGG“把多行的某個(gè)字段拼成一段字符串”這個(gè)需求用得也不少。MySQL的寫法是GROUP_CONCATSELECT department_id, GROUP_CONCAT(employee_name ORDER BY employee_name SEPARATOR 、) AS names FROM employees GROUP BY department_id;SQL Server沒(méi)有GROUP_CONCAT對(duì)應(yīng)的是STRING_AGG用法類似SELECT department_id, STRING_AGG(employee_name, 、) WITHIN GROUP (ORDER BY employee_name) AS names FROM employees GROUP BY department_id;兩邊的參數(shù)有些差異但思路一致。寫的時(shí)候注意GROUP_CONCAT默認(rèn)長(zhǎng)度限制是1024字節(jié)拼接內(nèi)容長(zhǎng)的時(shí)候需要先設(shè)置group_concat_max_len。4. 寫SQL前先看安全注入原理與防御姿勢(shì)“SQL注入”這個(gè)關(guān)鍵詞在網(wǎng)絡(luò)熱詞里多次出現(xiàn)又是ctfshow里的高頻考點(diǎn)又是安全測(cè)試?yán)锢@不開(kāi)的環(huán)節(jié)。它到底是什么說(shuō)白了用戶的輸入被當(dāng)成SQL代碼執(zhí)行了。4.1 注入發(fā)生的根因與萬(wàn)能密碼原理先看一段最原始的登錄查詢SELECT * FROM users WHERE username admin AND password 123456;如果代碼里是直接把用戶輸入的username和password拼接進(jìn)這個(gè)字符串攻擊者在密碼框輸入 OR 11那么實(shí)際執(zhí)行的語(yǔ)句就變成SELECT * FROM users WHERE username admin AND password OR 11;因?yàn)镺R 11恒為真整條WHERE條件的結(jié)果就是真攻擊者就繞過(guò)了密碼校驗(yàn)。這就是所謂的“萬(wàn)能密碼”原理本質(zhì)上不是SQL有什么漏洞而是拼接字符串的代碼留下了注入點(diǎn)。再比如熱詞里提到的“fofa查詢sql注入”FOFA這類網(wǎng)絡(luò)空間搜索引擎能搜到暴露在公網(wǎng)的資產(chǎn)很多帶有SQL注入漏洞的系統(tǒng)就是這么被找出來(lái)的。這提醒我們?nèi)魏螘r(shí)候都不要把用戶輸入直接拼進(jìn)SQL這是底線。4.2 參數(shù)化查詢唯一可靠的防御方式防御SQL注入的辦法其實(shí)很簡(jiǎn)單而且是語(yǔ)言層面的標(biāo)準(zhǔn)方案參數(shù)化查詢。它讓SQL模板和用戶輸入徹底分離數(shù)據(jù)庫(kù)把輸入的“值”當(dāng)數(shù)據(jù)處理而不是當(dāng)SQL代碼執(zhí)行。Python里用MySQL驅(qū)動(dòng)時(shí)的寫法cursor.execute( SELECT * FROM users WHERE username %s AND password %s, (username, password) )Java里用JDBC的PreparedStatementString sql SELECT * FROM users WHERE username ? AND password ?; PreparedStatement ps conn.prepareStatement(sql); ps.setString(1, username); ps.setString(2, password);參數(shù)化查詢之外的其他防護(hù)都不夠硬。比如黑名單過(guò)濾、轉(zhuǎn)義特殊字符這些方案都能被繞過(guò)。黑名單永遠(yuǎn)不完整轉(zhuǎn)義規(guī)則在不同字符集和數(shù)據(jù)庫(kù)方言下可能有差異一個(gè)考慮不周就漏了。所以我在團(tuán)隊(duì)里反復(fù)強(qiáng)調(diào)能寫參數(shù)化查詢就別手動(dòng)轉(zhuǎn)義。4.3 安全自查清單結(jié)合我參與過(guò)的一些安全測(cè)試經(jīng)驗(yàn)整理一份可以日常對(duì)照的清單所有SQL執(zhí)行入口統(tǒng)一走參數(shù)化查詢禁止字符串拼接存儲(chǔ)過(guò)程內(nèi)部如果拼接SQL同樣要參數(shù)化處理或嚴(yán)格校驗(yàn)入?yún)?shù)據(jù)庫(kù)賬號(hào)遵循最小權(quán)限原則應(yīng)用賬號(hào)只擁有業(yè)務(wù)必需的表權(quán)限SELECT、INSERT、UPDATE、DELETE按需分配錯(cuò)誤信息不要直接拋給前端避免暴露SQL片段、表名、字段名定期掃描接口用自動(dòng)化工具檢測(cè)注入點(diǎn)說(shuō)句實(shí)話SQL注入在OWASP里這么多年一直排在最危險(xiǎn)漏洞前列不是因?yàn)樗卸嚯y修而是很多團(tuán)隊(duì)根本沒(méi)有把參數(shù)化查詢當(dāng)成默認(rèn)約定。只要約定成俗Code Review把關(guān)這個(gè)坑基本就堵住了。5. 慢SQL排查索引失效、執(zhí)行計(jì)劃與優(yōu)化順序熱搜詞里“慢sql優(yōu)化”、“sql優(yōu)化”、“并行sql優(yōu)化”扎堆出現(xiàn)說(shuō)明大家在實(shí)際工作中遇到的性能問(wèn)題遠(yuǎn)比語(yǔ)法問(wèn)題多。語(yǔ)法沒(méi)寫錯(cuò)但就是慢這才是最磨人的。5.1 先從一次典型的慢查詢排查講起我之前遇到過(guò)一張訂單表數(shù)據(jù)量在800萬(wàn)行左右一條統(tǒng)計(jì)SQL跑了幾十秒接口直接超時(shí)。SQL大致長(zhǎng)這樣SELECT user_id, COUNT(*) AS order_count, SUM(order_amount) AS total_amount FROM orders WHERE DATE_FORMAT(create_time, %Y-%m) 2024-05 GROUP BY user_id;從語(yǔ)法角度看它完全正確。問(wèn)題出在哪WHERE DATE_FORMAT(create_time, %Y-%m) 2024-05。create_time列上明明有索引但因?yàn)閷?duì)列用了函數(shù)索引自然失效。優(yōu)化器沒(méi)法用二分查找定位“2024年5月”的范圍只能全表掃描然后每一行都套一個(gè)DATE_FORMAT計(jì)算再跟目標(biāo)值比對(duì)。800萬(wàn)行就這么硬掃了一遍。改法很簡(jiǎn)單SELECT user_id, COUNT(*) AS order_count, SUM(order_amount) AS total_amount FROM orders WHERE create_time 2024-05-01 AND create_time 2024-06-01 GROUP BY user_id;同樣的業(yè)務(wù)語(yǔ)義性能天差地別原因就是讓索引回到了可用狀態(tài)。5.2 讀懂執(zhí)行計(jì)劃EXPLAIN的關(guān)鍵列排查慢SQL第一步永遠(yuǎn)是看執(zhí)行計(jì)劃。MySQL里就是EXPLAINSQL Server對(duì)應(yīng)的是“顯示估計(jì)的執(zhí)行計(jì)劃”PostgreSQL是EXPLAIN ANALYZE。不依賴執(zhí)行計(jì)劃去猜性能問(wèn)題那只能是瞎蒙。以MySQL的EXPLAIN為例幾個(gè)關(guān)鍵輸出列表格列名關(guān)注點(diǎn)說(shuō)明type至少要到range最好到ref或const從全表掃描到索引查找能看到訪問(wèn)路徑好壞key實(shí)際用到的索引如果為NULL說(shuō)明沒(méi)走索引rows預(yù)估掃描行數(shù)數(shù)值越小越好Extra重點(diǎn)是Using filesort、Using temporary出現(xiàn)這兩個(gè)詞通常意味著排序/分組沒(méi)走索引數(shù)據(jù)量大就會(huì)慢看到Using filesort要警覺(jué)order by沒(méi)走索引數(shù)據(jù)庫(kù)要把結(jié)果集拉到內(nèi)存或磁盤上排序。看到Using temporary同理GROUP BY經(jīng)常觸發(fā)表格文件像臨時(shí)表的操作。5.3 索引失效的七大常見(jiàn)場(chǎng)景整理一份對(duì)照表給你排查的時(shí)候命中一個(gè)就檢查一個(gè)對(duì)列使用函數(shù)WHERE DATE_FORMAT(create_time, ...) ...索引失效隱式類型轉(zhuǎn)換字符串列和數(shù)字比較索引可能失效前導(dǎo)模糊匹配LIKE %關(guān)鍵詞無(wú)法走索引LIKE 關(guān)鍵詞%則可以聯(lián)合索引不滿足最左前綴原則比如索引是(a, b, c)查詢條件里只寫了b和c走不了索引在索引列上做運(yùn)算WHERE price * 1.1 100優(yōu)化器沒(méi)法利用索引OR條件連接非索引列WHERE a 1 OR b 2如果b沒(méi)有索引可能全表掃描NULL值判斷的邊界情況索引列大量NULL時(shí)IS NULL的優(yōu)化效果不如預(yù)期真實(shí)項(xiàng)目里聯(lián)合索引的最左前綴原則是很多人栽跟頭的地方。比如建了索引(idx_user_id, idx_create_time)查詢條件是WHERE create_time ?沒(méi)有帶上user_id那這個(gè)聯(lián)合索引就用不上必須老老實(shí)實(shí)建一個(gè)create_time的單列索引或者調(diào)整SQL讓條件包含user_id。5.4 優(yōu)化順序先搞清楚瓶頸再動(dòng)手我見(jiàn)過(guò)不少同事拿到慢SQL二話不說(shuō)就加索引結(jié)果加了索引還是慢。正確的排查順序應(yīng)該是定位瓶頸SQL全表掃描慢還是排序慢還是連接關(guān)系里中間結(jié)果太大看執(zhí)行計(jì)劃確認(rèn)實(shí)際是否走了索引有沒(méi)有Using filesort、Using temporary優(yōu)化SQL結(jié)構(gòu)改寫WHERE條件、減少不必要的列、優(yōu)化JOIN順序才考慮索引調(diào)整加索引、調(diào)整聯(lián)合索引順序、覆蓋索引最后提一句索引不是越多越好。每個(gè)索引都占用寫入開(kāi)銷插入、更新、刪除時(shí)都要同步維護(hù)。一張表建了七八個(gè)索引寫性能必然受影響。取舍的標(biāo)準(zhǔn)永遠(yuǎn)是看真實(shí)業(yè)務(wù)查詢場(chǎng)景而不是把所有列都建一遍。6. 多數(shù)據(jù)庫(kù)方言差異與常見(jiàn)報(bào)錯(cuò)排查大家在搜索里頻繁搜到sql server相關(guān)的關(guān)鍵詞sql server writelog、sql server 2012密碼到期、sql server express下載、solidworks electrical無(wú)法連接到sql server。這說(shuō)明很多人在工作中被數(shù)據(jù)庫(kù)環(huán)境的坑卡住了。SQL語(yǔ)法雖然標(biāo)準(zhǔn)化但不同數(shù)據(jù)庫(kù)的“方言”和“環(huán)境問(wèn)題”確實(shí)千差萬(wàn)別。6.1 SQL標(biāo)準(zhǔn)與各數(shù)據(jù)庫(kù)的語(yǔ)法差異對(duì)照我整理了一份高頻差異對(duì)照表適合日常查詢時(shí)參考表格能力項(xiàng)MySQLSQL ServerPostgreSQL字符串拼接CONCAT(a, b)a ba || b分頁(yè)LIMIT offset, countOFFSET n ROWS FETCH NEXT m ROWS ONLYLIMIT count OFFSET offset自增主鍵AUTO_INCREMENTIDENTITY(1,1)SERIAL 或 IDENTITY取前N條LIMIT NSELECT TOP NLIMIT N字符串聚合GROUP_CONCATSTRING_AGGSTRING_AGG當(dāng)前日期CURDATE() / NOW()GETDATE()CURRENT_DATE / NOW()如果不存在則更新存在則忽略INSERT ... ON DUPLICATE KEY UPDATEMERGE 語(yǔ)句INSERT ... ON CONFLICT DO UPDATE舉例來(lái)說(shuō)剛剛講過(guò)MySQL分頁(yè)的LIMIT寫法同一條SQL拿到SQL Server里就會(huì)語(yǔ)法報(bào)錯(cuò)這是我在項(xiàng)目里見(jiàn)得最多的“跨庫(kù)遷移兼容性”問(wèn)題。除此之外SQL Server的默認(rèn)排序規(guī)則Collation也?？尤酥形淖侄闻判蛟贑hinese_PRC_CI_AS和Latin1_General_CI_AS下的表現(xiàn)不一樣查詢結(jié)果順序可能跟預(yù)期不同。6.2 SQL Server的常見(jiàn)環(huán)境報(bào)錯(cuò)與排查思路SQL Server相關(guān)的問(wèn)題在搜索詞里出現(xiàn)頻率極高我把兩個(gè)典型的拿出來(lái)拆解案例一sql server writelog 慢或持續(xù)高活躍Writelog是SQL Server的日志寫入進(jìn)程。如果它長(zhǎng)時(shí)間處于高活躍狀態(tài)通常說(shuō)明事務(wù)日志寫入壓力大?？赡茉蛴袛?shù)據(jù)庫(kù)的恢復(fù)模式是FULL且日志沒(méi)有定期備份、長(zhǎng)事務(wù)持有日志空間不釋放、磁盤本身寫入性能差。排查思路檢查DBCC SQLPERF(LOGSPACE)看日志文件空間使用率檢查日志備份頻率FULL恢復(fù)模式下必須定期備份日志才能截?cái)嗳罩疚募挪殚L(zhǎng)事務(wù)用DBCC OPENTRAN查看最早的活動(dòng)事務(wù)檢查磁盤IO延遲指標(biāo)尤其要關(guān)注日志文件的物理盤是不是和數(shù)據(jù)庫(kù)文件混在一起案例二sql server 2012密碼到期導(dǎo)致登錄失敗搜這個(gè)關(guān)鍵詞的人大概率遇到了類似報(bào)錯(cuò)Login failed for user 某某. Reason: The password of the account has expired.這是SQL Server 2012默認(rèn)開(kāi)啟了密碼過(guò)期策略導(dǎo)致的問(wèn)題。處理方式有兩個(gè)用Windows認(rèn)證方式或sa賬號(hào)登錄后修改該登錄名的密碼并取消密碼過(guò)期策略或者直接通過(guò)屬性面板取消“強(qiáng)制密碼過(guò)期”勾選。要更穩(wěn)妥的話還可以關(guān)掉整個(gè)服務(wù)器級(jí)別的密碼過(guò)期策略但這要看公司安全規(guī)范怎么要求。6.3 第三方軟件連不上SQL Server的排查順序搜索詞里還有“solidworks electrical無(wú)法連接到sql server”這類問(wèn)題的本質(zhì)是客戶端連接SQL Server實(shí)例失敗跟具體業(yè)務(wù)軟件關(guān)系不大排查路徑基本一致。按照從低到高的排查順序網(wǎng)絡(luò)層ping數(shù)據(jù)庫(kù)服務(wù)器IP通不通端口層SQL Server默認(rèn)端口1433是否監(jiān)聽(tīng)telnet通不通實(shí)例名層如果是命名實(shí)例確認(rèn)實(shí)例名是否正確比如服務(wù)器IP\實(shí)例名驅(qū)動(dòng)層確認(rèn)軟件內(nèi)置的SQL Server驅(qū)動(dòng)版本是否太舊協(xié)議層SQL Server Configuration Manager里確認(rèn)TCP/IP協(xié)議是否啟用因?yàn)槟J(rèn)情況某些版本只啟用了Shared Memory認(rèn)證層Windows認(rèn)證還是混合認(rèn)證用錯(cuò)認(rèn)證模式也會(huì)連接失敗很多“軟件連不上SQLServer”的問(wèn)題最后都出在TCP/IP協(xié)議沒(méi)啟用或者實(shí)例名拼錯(cuò)而不是服務(wù)器真的掛了。先按這個(gè)順序排查能省掉大量時(shí)間。6.4 關(guān)于數(shù)據(jù)庫(kù)環(huán)境的最后提醒遇到環(huán)境類報(bào)錯(cuò)我個(gè)人的習(xí)慣是先確認(rèn)版本再確認(rèn)配置最后碰代碼。很多SQL Server、MySQL的報(bào)錯(cuò)信息在版本之間存在顯著差異網(wǎng)上搜到的方案是基于舊版本的直接套用可能適得其反。比如SQL Server 2012的密碼策略問(wèn)題和2019的有些細(xì)節(jié)就不一樣必須先定準(zhǔn)環(huán)境再動(dòng)手。7. 兩代人的SQL使用習(xí)慣從手寫語(yǔ)句到ORM的利與弊最近幾年ORM框架越來(lái)越流行寫代碼的時(shí)候直接鏈?zhǔn)秸{(diào)用方法底層自動(dòng)生成SQL。很多新人確實(shí)沒(méi)怎么手寫過(guò)SQL了。但搜索詞里“sql面試題”、“sql基礎(chǔ)知識(shí)”的搜索量一直居高不下說(shuō)明面試和工作里對(duì)SQL能力的要求并沒(méi)降低。我在這里也聊聊我對(duì)ORM和手寫SQL的真實(shí)看法。ORM最大的價(jià)值是提升了開(kāi)發(fā)效率和代碼可維護(hù)性。在簡(jiǎn)單CRUD場(chǎng)景下ORM比手寫SQL少了很多樣板代碼還能自動(dòng)映射實(shí)體避免拼字符串導(dǎo)致的低級(jí)語(yǔ)法錯(cuò)誤。比如用Python的SQLAlchemy或者Java的MyBatis-Plus寫簡(jiǎn)單的插入、更新、單表查詢確實(shí)直觀高效。但ORM的問(wèn)題同樣明顯復(fù)雜查詢的SQL生成不可控。我見(jiàn)過(guò)一個(gè)案例業(yè)務(wù)方用ORM拼了一個(gè)多層子查詢生成的SQL嵌套了五六層執(zhí)行計(jì)劃里嵌套循環(huán)層數(shù)爆炸一個(gè)查詢跑了三分鐘。后來(lái)我手動(dòng)改寫成兩個(gè)JOIN加一個(gè)臨時(shí)表三秒出結(jié)果。這種場(chǎng)景下ORM的抽象反而成了性能優(yōu)化的阻礙。所以我的立場(chǎng)一直很明確簡(jiǎn)單查詢交給ORM復(fù)雜查詢堅(jiān)持手寫SQL。這跟語(yǔ)法能力有什么關(guān)系關(guān)系大了——你要手寫就得真正掌握SQL語(yǔ)法得會(huì)看執(zhí)行計(jì)劃得知道子查詢會(huì)不會(huì)被優(yōu)化器改成JOIN得明白窗口函數(shù)該怎么用。沒(méi)有這個(gè)底子遇到性能問(wèn)題就只能干瞪眼。從學(xué)習(xí)的角度說(shuō)我依然建議把SQL語(yǔ)法的基礎(chǔ)打牢。現(xiàn)在你搜“sql語(yǔ)法”能找到一堆教程但真正系統(tǒng)的做法是拿一套官方文檔我推薦PostgreSQL或者M(jìn)ySQL的官方手冊(cè)把SELECT那部分的每個(gè)子句逐條看一遍然后到本地庫(kù)建兩張表反復(fù)練習(xí)。語(yǔ)法這東西就像開(kāi)車看一百遍不如自己開(kāi)十遍來(lái)得扎實(shí)。