把Oracle上跑得順滑的SQL原封不動(dòng)扔到SQL Server里結(jié)果慢了十幾倍客戶當(dāng)場(chǎng)質(zhì)疑你是不是換了一臺(tái)渣服務(wù)器——這種事我經(jīng)歷過(guò)不止一次。換成MySQL表現(xiàn)可能又不一樣。鍋從來(lái)不在“機(jī)器性能”而在于每個(gè)數(shù)據(jù)庫(kù)引擎各自那套存儲(chǔ)模型、統(tǒng)計(jì)信息、鎖機(jī)制和優(yōu)化器邏輯。本文把主流引擎MySQL InnoDB、SQL Server、Oracle、PostgreSQL的SQL優(yōu)化方案放到同一個(gè)舞臺(tái)上拆開講核心目的只有一個(gè)讓你搞清楚同一類SQL在不同引擎里為什么會(huì)有截然不同的命運(yùn)以及當(dāng)慢SQL報(bào)出來(lái)時(shí)該怎么按引擎對(duì)癥下藥。內(nèi)容會(huì)涉及索引設(shè)計(jì)、執(zhí)行計(jì)劃、等待事件、深翻頁(yè)、去重、窗口函數(shù)、并行度這些高頻場(chǎng)景適合正在做跨數(shù)據(jù)庫(kù)開發(fā)的工程師、剛接手?jǐn)?shù)據(jù)庫(kù)優(yōu)化任務(wù)的DBA以及那些被“換個(gè)庫(kù)就翻車”折磨過(guò)的人。1. 為什么同一句SQL在不同引擎里表現(xiàn)天差地別——優(yōu)化思路的起點(diǎn)1.1 存儲(chǔ)模型決定數(shù)據(jù)“怎么被找到”很多人習(xí)慣把“優(yōu)化SQL”當(dāng)成一套萬(wàn)能公式加索引、避免SELECT *、減少子查詢。這些確實(shí)通用但它們只是戰(zhàn)術(shù)真正決定上限的是數(shù)據(jù)庫(kù)底層怎么存數(shù)據(jù)。MySQL InnoDB是典型的聚簇索引表。整張表就是一棵B樹葉子節(jié)點(diǎn)直接放行數(shù)據(jù)。主鍵就是聚簇索引二級(jí)索引的葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵值。所以走主鍵查詢等于直接定位走二級(jí)索引要先查一遍索引拿到主鍵再回聚簇索引取完整數(shù)據(jù)這就是“回表”。設(shè)計(jì)主鍵時(shí)如果用了UUID之類的隨機(jī)值插入時(shí)會(huì)發(fā)生大量頁(yè)分裂和日志寫入放大這也是為什么InnoDB從業(yè)務(wù)角度都建議用自增主鍵。SQL Server不一樣。它可以建堆表也可以為表指定聚簇索引。堆表的數(shù)據(jù)頁(yè)之間沒(méi)有邏輯順序通過(guò)IAM頁(yè)追蹤聚簇索引表則按聚簇鍵物理排序。在頻繁插入且聚簇鍵變化大的場(chǎng)景堆表反而能減少頁(yè)分裂但大多數(shù)生產(chǎn)場(chǎng)景下合理的聚簇索引對(duì)范圍查詢幫助極大。這里沒(méi)有絕對(duì)的“誰(shuí)更好”只有“你的查詢模式更適合哪種”。Oracle和PostgreSQL都是堆表結(jié)構(gòu)。Oracle通過(guò)ROWID直接定位物理行索引葉子節(jié)點(diǎn)存ROWIDPostgreSQL的索引存的是行指針并且靠可見性映射Visibility Map來(lái)加速M(fèi)VCC判斷。堆表的回表代價(jià)并不一定比聚簇索引高因?yàn)閿?shù)據(jù)頁(yè)可能已經(jīng)在Buffer Pool里。但這也意味著“回表”這件事在每個(gè)引擎里的成本模型是完全不同的。你拿MySQL的經(jīng)驗(yàn)去判斷Oracle的回表開銷從一開始就錯(cuò)了。1.2 優(yōu)化器邏輯與統(tǒng)計(jì)信息的“個(gè)性化差異”SQL優(yōu)化不能只談存儲(chǔ)執(zhí)行計(jì)劃由優(yōu)化器生成而優(yōu)化器吃的是統(tǒng)計(jì)信息。MySQL 8.0的優(yōu)化器比老版本強(qiáng)了不少支持直方圖但整體對(duì)復(fù)合索引、OR條件的處理仍然偏保守。經(jīng)典翻車場(chǎng)景一張表兩個(gè)單列索引WHERE a 1 OR b 2MySQL經(jīng)常直接放棄索引合并做全表掃描而Oracle通常能走INDEX合并或BITMAP轉(zhuǎn)換。這跟引擎能力有關(guān)不是你的SQL寫錯(cuò)了。SQL Server的CBO非常成熟尤其是基數(shù)估計(jì)Cardinality Estimation2014年之后的默認(rèn)CE模型對(duì)“多列獨(dú)立謂詞”的預(yù)估更接近真實(shí)分布。但它也有自己的坑參數(shù)嗅探。第一次執(zhí)行的參數(shù)值決定了執(zhí)行計(jì)劃后面換個(gè)參數(shù)值可能讓計(jì)劃變得極差。Oracle的CBO是目前最復(fù)雜的優(yōu)化器之一支持自適應(yīng)計(jì)劃、統(tǒng)計(jì)信息自動(dòng)收集但綁定變量窺視、分區(qū)裁剪失效這些老問(wèn)題仍然存在。PostgreSQL則給用戶留了很大的自定義空間seq_page_cost、random_page_cost這些成本參數(shù)可以直接影響優(yōu)化器選擇。這些差異告訴我們一個(gè)核心道理跨引擎優(yōu)化第一步永遠(yuǎn)是重新評(píng)估執(zhí)行計(jì)劃而不是把上一個(gè)庫(kù)的“成功經(jīng)驗(yàn)”直接搬過(guò)來(lái)。1.3 “優(yōu)化”的本質(zhì)不是抄方案而是拆場(chǎng)景我接觸過(guò)的很多團(tuán)隊(duì)把SQL優(yōu)化做成了“經(jīng)驗(yàn)搬運(yùn)”MySQL慢就把Oracle那套調(diào)優(yōu)寶典拿來(lái)試試不通就怪?jǐn)?shù)據(jù)庫(kù)。實(shí)際上一個(gè)SQL慢下來(lái)你第一件要做的事不是改語(yǔ)句而是分清楚瓶頸類型IO密集型全表掃描、回表過(guò)多、排序落盤、日志寫入慢CPU密集型大量表達(dá)式計(jì)算、嵌套循環(huán)在超大集上運(yùn)行、并行度過(guò)高導(dǎo)致爭(zhēng)用鎖/等待密集型鎖塊、鎖升級(jí)、死鎖重試、日志同步等待這三個(gè)類型的優(yōu)化手段幾乎不重疊。IO瓶頸看索引和執(zhí)行計(jì)劃CPU瓶頸看表達(dá)式和算子等待瓶頸要看等待事件和并發(fā)配置。接下來(lái)幾節(jié)我會(huì)把索引、慢SQL定位、實(shí)戰(zhàn)場(chǎng)景和配置陷阱逐一展開。2. 索引設(shè)計(jì)的分水嶺從B樹到列存各引擎的索引脾氣2.1 主鍵與聚簇索引InnoDB的“必選”與SQL Server的“可選”索引設(shè)計(jì)是SQL優(yōu)化里最容易被低估的一環(huán)。很多人以為“建了索引就快了”但索引建錯(cuò)了效果可能比不建還差。MySQL InnoDB里聚簇索引是躲不掉的沒(méi)有主鍵時(shí)引擎會(huì)挑第一個(gè)非空唯一索引實(shí)在沒(méi)有就生成一個(gè)隱藏的rowid列。這意味著你在MySQL里設(shè)計(jì)主鍵本質(zhì)上是在設(shè)計(jì)整張表的物理存儲(chǔ)形態(tài)。隨機(jī)主鍵會(huì)導(dǎo)致頁(yè)分裂讓插入性能斷崖式下跌過(guò)長(zhǎng)的主鍵比如字符串型業(yè)務(wù)單號(hào)會(huì)讓每個(gè)二級(jí)索引都變得臃腫因?yàn)槎?jí)索引葉子節(jié)點(diǎn)要存主鍵值。SQL Server給了你選擇權(quán)。堆表和聚簇索引表各有適用場(chǎng)景如果數(shù)據(jù)是流水型追加寫入范圍查詢少、既沒(méi)有主鍵的排序需求堆表可能更合適但如果存在大量區(qū)間掃描或需要按特定順序輸出聚簇索引能把隨機(jī)IO變成順序IO。SQL Server還要關(guān)注填充因子Fill Factor和碎片率。索引碎片率超過(guò)30%時(shí)即便SQL走對(duì)了索引IO也可能高得離譜。運(yùn)維周期性重建索引這件事在MySQL里不常見但在SQL Server里是常規(guī)操作。Oracle和PostgreSQL的主鍵索引只是普通索引不承擔(dān)數(shù)據(jù)存儲(chǔ)職責(zé)。它們的表數(shù)據(jù)按插入順序放在堆里索引負(fù)責(zé)指向行的物理位置。這種架構(gòu)讓“插入”更輕量但也要注意堆表上頻繁更新會(huì)使行遷移舊位置留下轉(zhuǎn)發(fā)指針查詢會(huì)多一次IO。PostgreSQL的UPDATE會(huì)生成新版本行如果表膨脹嚴(yán)重索引掃描會(huì)掃描大量死元組導(dǎo)致查詢速度越來(lái)越慢——所以autovacuum的配置對(duì)PostgreSQL來(lái)說(shuō)不是“可選優(yōu)化”而是“保命設(shè)置”。2.2 覆蓋索引、索引下推與搜索條件寫法索引設(shè)計(jì)的高級(jí)玩法是讓索引“覆蓋”查詢避免回表。每個(gè)引擎都支持覆蓋索引但觸發(fā)條件不一樣。MySQL里如果SELECT的字段全部在二級(jí)索引中優(yōu)化器會(huì)用Index Only ScanExtra列顯示Using index回表完全省掉。SQL Server里叫“覆蓋索引Covering Index”常用INCLUDE語(yǔ)句把不需要參與排序、但需要輸出的列掛到索引葉子上。Oracle支持在索引里額外放一些列即“Include Column”讓索引能覆蓋更多查詢。PostgreSQL同樣支持INCLUDE。還有一種被忽略的能力是索引下推。MySQL的ICPIndex Condition Pushdown會(huì)在索引遍歷階段就過(guò)濾部分條件減少回表行數(shù)Extra列出現(xiàn)Using index condition就說(shuō)明下推生效。Oracle對(duì)復(fù)合索引的“謂詞推入”也有類似行為但要看執(zhí)行計(jì)劃的Predicate信息。寫SQL時(shí)把條件能寫成區(qū)間就寫成區(qū)間能避免函數(shù)包裹列就避免函數(shù)包裹列。這個(gè)原則四個(gè)引擎都適用但MySQL的感知最強(qiáng)烈因?yàn)樗膬?yōu)化器沒(méi)有Oracle那么“會(huì)兜底”。2.3 列存索引與分析型查詢另一個(gè)維度的“優(yōu)化”說(shuō)到索引不能不提SQL Server 2016之后主推的列存儲(chǔ)索引Columnstore。它把同一列的數(shù)據(jù)連續(xù)存放配合批模式執(zhí)行和頁(yè)壓縮分析類聚合查詢的IO量可以比行存少一個(gè)數(shù)量級(jí)。同樣的思路也是ClickHouse、Spark SQL這類分析引擎提速的核心。從OLTP角度做優(yōu)化時(shí)我們的思維是“減少行的訪問(wèn)”而分析型場(chǎng)景的思維是“減少列的訪問(wèn)”。如果你手上有一批報(bào)表SQL在SQL Server里給大表加一個(gè)列存索引通常比瘋狂優(yōu)化SQL寫法有效得多。Oracle也有列存選項(xiàng)Exadata上的Cell多級(jí)緩存、In-Memory列式格式MySQL則主要靠第三方引擎補(bǔ)位。這個(gè)例子最能說(shuō)明“不同數(shù)據(jù)庫(kù)引擎優(yōu)化方案”的差異方案不是靠某一條金句總結(jié)出來(lái)的而是靠理解引擎各自的強(qiáng)項(xiàng)。3. 慢SQL定位三板斧執(zhí)行計(jì)劃、統(tǒng)計(jì)信息與等待事件3.1 執(zhí)行計(jì)劃怎么看先看實(shí)際行數(shù)與估算行數(shù)的偏差慢SQL排查很多人第一反應(yīng)是看SQL文本試圖“用眼睛優(yōu)化”。但真正專業(yè)的第一動(dòng)作永遠(yuǎn)是拽出執(zhí)行計(jì)劃。四個(gè)引擎看計(jì)劃的方式完全不同你至少要會(huì)用自己手上的那個(gè)MySQLEXPLAIN或者EXPLAIN ANALYZE查看實(shí)際執(zhí)行時(shí)間與行數(shù)。重點(diǎn)看typeALL代表全表掃描range代表索引范圍掃描ref代表普通等值匹配、rows預(yù)估值、Extra里的Using filesort、Using temporary。SQL ServerSET STATISTICS IO ON和SET STATISTICS TIME ON可以輸出邏輯讀和耗時(shí)更直觀的是在SSMS里開啟“包含實(shí)際執(zhí)行計(jì)劃”。重點(diǎn)看每個(gè)操作符的Estimated vs Actual Row數(shù)、IO統(tǒng)計(jì)。OracleEXPLAIN PLAN FOR SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR)如果要看實(shí)際行數(shù)需要設(shè)置STATISTICS_LEVELALL然后再執(zhí)行這樣才能看到A-Rows和E-Rows的對(duì)比。PostgreSQLEXPLAIN (ANALYZE, BUFFERS)最實(shí)用能同時(shí)看到實(shí)際行數(shù)、啟動(dòng)成本和Buffer讀寫信息。我看執(zhí)行計(jì)劃有一個(gè)固定習(xí)慣先對(duì)比操作符的“估算行數(shù)”和“實(shí)際行數(shù)”。如果兩者偏差巨大比如估算1萬(wàn)行、實(shí)際跑了100萬(wàn)行那十有八九是統(tǒng)計(jì)信息過(guò)期。這時(shí)候再怎么改SQL都是治標(biāo)不治本。3.2 統(tǒng)計(jì)信息過(guò)期如何坑掉一條好SQL統(tǒng)計(jì)信息的采集機(jī)制在四個(gè)引擎里各有套路但“過(guò)期”帶來(lái)的問(wèn)題都一樣災(zāi)難性。舉個(gè)我排查過(guò)的真實(shí)案例一張?jiān)吕塾?jì)訂單表平時(shí)1000萬(wàn)行月底批量清洗后只剩10萬(wàn)行。優(yōu)化器不知道表已經(jīng)“瘦身”仍然按1000萬(wàn)行估算給一個(gè)十幾行的結(jié)果集選了哈希連接加全表掃描接口響應(yīng)從20毫秒暴漲到5秒。引擎的自動(dòng)更新機(jī)制并不總是及時(shí)。MySQL的自動(dòng)統(tǒng)計(jì)更新基于變化行數(shù)超過(guò)表大小的閾值指數(shù)級(jí)變化時(shí)通常能觸發(fā)但如果你是用大批量DELETE清數(shù)據(jù)后馬上查還是建議手動(dòng)執(zhí)行ANALYZE TABLE。SQL Server的自動(dòng)更新閾值在舊版本里也是基于百分比頻繁小量更新時(shí)統(tǒng)計(jì)信息可能長(zhǎng)期滯后定期維護(hù)計(jì)劃里加上UPDATE STATISTICS是DBA的基本功。Oracle的自動(dòng)統(tǒng)計(jì)任務(wù)一般在夜間窗口白天大批量導(dǎo)入數(shù)據(jù)后也需要手動(dòng)DBMS_STATS.GATHER_TABLE_STATS。PostgreSQL的autovacuum在默認(rèn)配置下對(duì)大多數(shù)場(chǎng)景夠用但高頻UPDATE的短表仍然容易統(tǒng)計(jì)失真。排查慢SQL時(shí)我建議把“刷新統(tǒng)計(jì)信息”放在前面做掉——成本低、見效快還不會(huì)像改SQL那樣引入新風(fēng)險(xiǎn)。做完再重新抓執(zhí)行計(jì)劃往往問(wèn)題就消失了。3.3 等待事件SQL Server的writelog與Oracle的log file sync有些慢SQL執(zhí)行計(jì)劃完美索引全都用上了但就是快不起來(lái)。這時(shí)候要看的不是執(zhí)行計(jì)劃而是時(shí)間花在哪里了。SQL Server里有一個(gè)很常見的等待類型WRITELOG。它表示會(huì)話在提交事務(wù)時(shí)需要等待日志記錄被寫入磁盤。凡是高頻小事務(wù)場(chǎng)景——比如循環(huán)逐行INSERT、頻繁UPDATE單行——都能看到大量WRITELOG等待。根因通常是磁盤的fsync延遲太高HDD、共享云盤、日志文件與數(shù)據(jù)文件混用或者日志文件初始化太小導(dǎo)致頻繁自動(dòng)增長(zhǎng)。優(yōu)化辦法把事務(wù)日志文件放到低延遲獨(dú)立磁盤、合并小事務(wù)為批量提交、合理預(yù)分配日志文件大小。別小看這個(gè)等待它經(jīng)常是“CPU不忙、磁盤不忙、但接口就是慢”的元兇。Oracle里對(duì)應(yīng)的等待是log file sync和log file parallel write背后邏輯高度相似提交事務(wù)時(shí)LGWR進(jìn)程要確保日志緩沖寫入聯(lián)機(jī)日志文件。排查方法比SQL Server稍微復(fù)雜可以從AWR報(bào)告的Top 5 Timed Events入手確認(rèn)Wait Event是不是log file sync再檢查redo log所在磁盤IO能力。MySQL的對(duì)應(yīng)參數(shù)是innodb_flush_log_at_trx_commit1時(shí)的每次提交刷盤如果業(yè)務(wù)允許改成2會(huì)有數(shù)量級(jí)的性能提升但代價(jià)是丟最多1秒的事務(wù)日志。這個(gè)取舍沒(méi)有標(biāo)準(zhǔn)答案要看業(yè)務(wù)對(duì)數(shù)據(jù)安全的要求。等待事件分析的價(jià)值在于它幫你把“SQL慢”拆成了“SQL自己慢”和“環(huán)境讓它慢”。前者靠索引和執(zhí)行計(jì)劃解決后者靠配置和磁盤布局解決。很多DBA只盯著SQL文本忽略等待類型這是排查慢SQL最容易走的彎路。4. 實(shí)戰(zhàn)拆解去重、分頁(yè)、窗口函數(shù)在四大引擎中的優(yōu)化做法4.1 去重DISTINCT、GROUP BY與ROW_NUMBER的代價(jià)差異“去重”是搜索引擎里最常見的SQL需求但不同引擎對(duì)去重的執(zhí)行方式完全不同。很多人以為DISTINCT就是簡(jiǎn)單的“選不同”實(shí)際它的代價(jià)往往被嚴(yán)重低估。DISTINCT在四個(gè)引擎里核心執(zhí)行方式無(wú)非兩種哈希去重或排序去重。沒(méi)有索引時(shí)MySQL可能使用臨時(shí)表SQL Server可能走Sort算子并觸發(fā)tempdb溢出Oracle默認(rèn)傾向HASH UNIQUEPostgreSQL在work_mem不足時(shí)會(huì)把HashAgg退化成SortGroupAggregate。實(shí)操中我遇到過(guò)最坑的寫法是先JOIN再DISTINCT。用訂單表和訂單明細(xì)表關(guān)聯(lián)最終結(jié)果希望得到“有哪些客戶下單”第一版SQL往往寫成SELECT DISTINCT c.customer_id ... FROM customers c JOIN orders o ON ...。這種寫法會(huì)把明細(xì)表放大后才去重中間結(jié)果驚人。正確的做法是改成EXISTS子查詢SELECT customer_id FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id c.customer_id)。執(zhí)行計(jì)劃直接從HASH JOIN變成SEMI JOIN行數(shù)少了一個(gè)量級(jí)。不同去重手段的選擇也要看業(yè)務(wù)語(yǔ)義需求推薦方式原因簡(jiǎn)單取唯一值SELECT DISTINCT col引擎有專門算子寫法直觀按某字段分組取其他字段GROUP BY 聚合函數(shù)不依賴窗口函數(shù)執(zhí)行計(jì)劃更清晰每組內(nèi)取最新一條/前N條ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...)窗口函數(shù)語(yǔ)義最強(qiáng)SQL Server 2012/MySQL 8.0/Oracle/PostgreSQL均支持注意一個(gè)細(xì)節(jié)DISTINCT對(duì)NULL的處理是“去重后只保留一個(gè)NULL”GROUP BY會(huì)把NULL當(dāng)成一個(gè)分組常規(guī)業(yè)務(wù)上兩者等價(jià)但如果你寫的是多列去重務(wù)必確認(rèn)NULL列的處理是否符合預(yù)期這一塊容易出隱蔽的語(yǔ)義Bug。4.2 深翻頁(yè)OFFSET不慢慢的是丟棄分頁(yè)是所有業(yè)務(wù)系統(tǒng)躲不開的SQL場(chǎng)景。淺分頁(yè)沒(méi)壓力深翻頁(yè)才是真正的性能殺手。四個(gè)引擎都支持LIMIT/OFFSET或等價(jià)語(yǔ)法但原理一樣先掃描出從第1行到第(offsetlimit)行的全部數(shù)據(jù)丟棄前面的offset行返回最后limit行。頁(yè)數(shù)越深掃描和丟棄的行越多。MySQL的經(jīng)典寫法是LIMIT 1000000, 20它會(huì)掃到第100萬(wàn)行再丟掉SQL Server用OFFSET 1000000 ROWS FETCH NEXT 20 ROWS ONLY底層也是排序后跳過(guò)Oracle老版本用ROWNUM嵌套子查詢12c以后有FETCH FIRST但深分頁(yè)的本質(zhì)沒(méi)有變化。PostgreSQL的LIMIT/OFFSET同樣避免不了這個(gè)問(wèn)題。解決深翻頁(yè)最有效的方案是Keyset Pagination也叫游標(biāo)分頁(yè)。不用OFFSET而是帶一個(gè)排序鍵的WHERE條件-- 傳統(tǒng)深翻頁(yè)慢 SELECT * FROM orders ORDER BY id OFFSET 1000000 ROWS FETCH NEXT 20 ROWS ONLY; -- Keyset Pagination快 SELECT * FROM orders WHERE id 1000000 ORDER BY id FETCH FIRST 20 ROWS ONLY;Keyset方式的執(zhí)行計(jì)劃是典型的索引范圍掃描理論上可以做到翻到第N頁(yè)都只有20行的開銷。前提是排序鍵絕對(duì)唯一且穩(wěn)定。如果業(yè)務(wù)排序需要多字段比如ORDER BY created_at DESC, id DESCWHERE條件也要按同樣順序帶上游標(biāo)值。字符串排序、日期排序同樣適用。實(shí)測(cè)數(shù)據(jù)最直觀一張200萬(wàn)行訂單表傳統(tǒng)OFFSET翻到第1000頁(yè)每頁(yè)20行耗時(shí)約為280毫秒改為Keyset后穩(wěn)定在0.5毫秒左右。所以如果你正在負(fù)責(zé)一個(gè)有深翻頁(yè)需求的接口建議趁早改造不要等用戶報(bào)告“越翻越慢”。4.3 窗口函數(shù)與并行分析需求在不同引擎里的落地姿勢(shì)窗口函數(shù)是SQL優(yōu)化工具箱里的高頻武器。SQL Server從2012版本開始支持ROW_NUMBER、RANK、DENSE_RANK、SUM() OVER()等再早只能靠自連接實(shí)現(xiàn)MySQL從8.0才開始支持之前只能用變量模擬Oracle和PostgreSQL自帶完整的窗口函數(shù)支持它們的執(zhí)行器對(duì)這一類算子優(yōu)化也更成熟。舉一個(gè)十分常見的例子“查每個(gè)客戶最近一筆訂單”。用窗口函數(shù)可以寫成SELECT customer_id, order_id, order_date FROM ( SELECT customer_id, order_id, order_date, ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY order_date DESC) AS rn FROM orders ) t WHERE rn 1;這段SQL在四個(gè)引擎里都能跑但注意如果orders表非常大窗口函數(shù)的PARTITION BYORDER BY需要一次全局排序。優(yōu)化點(diǎn)是建立復(fù)合索引(customer_id, order_date DESC)讓窗口排序直接走索引有序掃描避免顯式排序。MySQL 8.0對(duì)索引有序性的依賴最強(qiáng)索引建立不對(duì)時(shí)執(zhí)行計(jì)劃會(huì)出現(xiàn)Using filesort數(shù)據(jù)量大時(shí)性能差距能達(dá)幾十倍。SQL Server則可能更依賴內(nèi)存里的Sort算子配合列存索引時(shí)有時(shí)能獲得更激進(jìn)的批處理加速。再說(shuō)并行SQL優(yōu)化。Oracle的并行DML、并行查詢能力很強(qiáng)可以在SQL上加PARALLEL Hint讓一個(gè)復(fù)雜聚合查詢同時(shí)跑多個(gè)并行服務(wù)進(jìn)程SQL Server用MAXDOP設(shè)置并行度PostgreSQL從9.6開始引入了并行順序掃描和并行聚合但并行度受限于planning參數(shù)。MySQL至今沒(méi)有原生的“一條SQL自動(dòng)并行”能力一個(gè)高成本查詢只能單線程執(zhí)行。這個(gè)差異意味著同樣的分析SQL在Oracle和PG上可以通過(guò)調(diào)整并行度實(shí)現(xiàn)質(zhì)的飛躍在MySQL上則必須靠?jī)?yōu)化語(yǔ)句本身、建物化視圖或引入分析引擎來(lái)解決問(wèn)題?？缫鎯?yōu)化時(shí)必須先認(rèn)清有些特性是這個(gè)引擎天生沒(méi)有的與其死磕不如改變架構(gòu)方案。5. 常見陷阱與規(guī)避從SQL寫法到引擎配置的教訓(xùn)5.1 參數(shù)化與SQL注入安全底線也是性能底線搜索引擎熱詞榜里永遠(yuǎn)有SQL注入這不是偶然。很多運(yùn)維和開發(fā)把“SQL注入防護(hù)”當(dāng)成純安全議題實(shí)際上它與SQL優(yōu)化是同一件事。參數(shù)化查詢Prepared Statement除了能防注入還能提升執(zhí)行計(jì)劃復(fù)用率。以SQL Server為例如果業(yè)務(wù)代碼每次都拼接一個(gè)新的SQL文本提交每次都需要硬解析生成新的執(zhí)行計(jì)劃CPU壓力升高、計(jì)劃緩存命中率下降改成參數(shù)化寫法后同一個(gè)計(jì)劃模板可以被反復(fù)復(fù)用。Oracle的綁定變量、MySQL的PREPARE、PostgreSQL的PREPARE也都是一樣的邏輯。反過(guò)來(lái)說(shuō)SQL注入的根因就是非參數(shù)化的字符串拼接?；ヂ?lián)網(wǎng)上流傳的所謂“繞過(guò)手法”本質(zhì)上都是利用拼接邏輯的缺陷做字符串逃逸。修復(fù)方式?jīng)]有捷徑全部改成參數(shù)化查詢數(shù)據(jù)庫(kù)賬號(hào)按庫(kù)表權(quán)限最小化應(yīng)用層再做一層白名單校驗(yàn)。參數(shù)化一上注入漏洞攻擊面立刻歸零計(jì)劃緩存利用率同步提升——一次改造安全性和性能雙收益。5.2 隱式轉(zhuǎn)換與函數(shù)包裹讓索引瞬間失效的寫法這是跨引擎優(yōu)化里最統(tǒng)一的一條經(jīng)驗(yàn)在索引列上做函數(shù)運(yùn)算或隱式類型轉(zhuǎn)換絕大多數(shù)情況下會(huì)讓索引失效。MySQL里最常見的翻車現(xiàn)場(chǎng)是字段類型是VARCHARSQL寫成了WHERE phone 13800138000MySQL會(huì)先把字段轉(zhuǎn)成數(shù)值再比較索引直接報(bào)廢。解決辦法就是讓參數(shù)類型與字段類型保持一致寫成13800138000字符串。SQL Server也有類似行為字符列與數(shù)值常量比較時(shí)常會(huì)觸發(fā)隱式轉(zhuǎn)換執(zhí)行計(jì)劃里會(huì)出現(xiàn)CONVERT_IMPLICIT掃描行數(shù)瞬間暴增。日期函數(shù)包裹列是另一個(gè)通病。WHERE DATE(create_time) 2024-01-01這種寫法會(huì)把create_time列塞進(jìn)函數(shù)里即便該列有索引也掃不了。改成WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00既能走索引邏輯上也等價(jià)。引擎差異在這里也有體現(xiàn)。Oracle里你還能用函數(shù)索引如TO_CHAR(create_time, YYYY-MM-DD)建索引兜底MySQL 8.0也支持函數(shù)索引SQL Server可以通過(guò)計(jì)算列加索引實(shí)現(xiàn)類似效果。但我的習(xí)慣仍然是優(yōu)先改SQL寫法而不是為不合理的寫法建特殊索引。因?yàn)楹瘮?shù)索引對(duì)寫入有額外維護(hù)成本而且應(yīng)用一旦換寫法索引就浪費(fèi)了。5.3 系統(tǒng)性“偽慢SQL”監(jiān)聽、連接池與臨時(shí)文件最后一類慢SQL其實(shí)SQL本身是無(wú)辜的。比如Oracle報(bào)錯(cuò)ORA-12518“監(jiān)聽程序無(wú)法分發(fā)”這個(gè)錯(cuò)誤的本質(zhì)往往不是SQL性能問(wèn)題而是監(jiān)聽進(jìn)程無(wú)法fork新的服務(wù)器進(jìn)程——常見原因是processes參數(shù)打滿、操作系統(tǒng)進(jìn)程數(shù)限制、SGA/PGA內(nèi)存不足。排查方向是調(diào)大processes、sessions參數(shù)限制應(yīng)用的空閑連接數(shù)檢查系統(tǒng)內(nèi)存。如果你只盯著SQL調(diào)優(yōu)永遠(yuǎn)看不到問(wèn)題。連接池配置同理。很多接口慢不是SQL慢而是連接池里線程都在排隊(duì)等連接。HikariCP、Druid這類連接池的maximumPoolSize設(shè)置過(guò)小高并發(fā)時(shí)請(qǐng)求全部阻塞在獲取連接階段設(shè)置過(guò)大數(shù)據(jù)庫(kù)端又會(huì)資源爭(zhēng)用。我在實(shí)際優(yōu)化項(xiàng)目里見過(guò)接口P99從800毫秒降到80毫秒的案例改動(dòng)僅僅是調(diào)整連接池大小和空閑超時(shí)SQL一行沒(méi)改。臨時(shí)文件和日志文件也經(jīng)常被忽視。SQL Server的tempdb如果默認(rèn)配置且與其他庫(kù)共用硬盤排序和哈希連接一旦落盤就會(huì)拖慢所有查詢MySQL的tmp_table_size過(guò)小時(shí)GROUP BY會(huì)轉(zhuǎn)到磁盤臨時(shí)表PostgreSQL的work_mem直接決定Sort/Hash操作是走內(nèi)存還是走磁盤默認(rèn)4MB對(duì)一個(gè)稍大排序來(lái)說(shuō)小得離譜。所以當(dāng)你面對(duì)一條“查了半小時(shí)還不出來(lái)”的SQL排查順序我建議是統(tǒng)計(jì)信息→等待事件→執(zhí)行計(jì)劃→SQL改寫→索引調(diào)整→系統(tǒng)配置。前面幾步可以快速排除環(huán)境因素后面幾步才是真正的SQL優(yōu)化。順序反了很容易在一個(gè)錯(cuò)誤的方向上耗費(fèi)半天。做數(shù)據(jù)庫(kù)優(yōu)化這些年我最深的體會(huì)是優(yōu)化不是背答案而是理解每個(gè)引擎是怎么存、怎么找、怎么鎖的。同一套經(jīng)驗(yàn)換一個(gè)數(shù)據(jù)庫(kù)往往就失真所以每次跨庫(kù)排查我都默認(rèn)自己是個(gè)新手從頭看執(zhí)行計(jì)劃和等待事件。很多團(tuán)隊(duì)迷信所謂的“大廠調(diào)優(yōu)參數(shù)”拿一套配置到處套結(jié)果連基礎(chǔ)的數(shù)據(jù)文件布局都沒(méi)看。老老實(shí)實(shí)按“統(tǒng)計(jì)信息是否新鮮、等待事件是否異常、執(zhí)行計(jì)劃是否合理、索引是否被有效使用”這個(gè)順序走一遍多數(shù)慢SQL問(wèn)題都能在半小時(shí)內(nèi)定位。最后再分享一個(gè)實(shí)戰(zhàn)習(xí)慣每次只改一個(gè)變量。改完SQL跑一次驗(yàn)證建完索引再看一遍執(zhí)行計(jì)劃。多個(gè)優(yōu)化點(diǎn)一起上出了問(wèn)題你根本不知道是誰(shuí)的鍋。