五原則
- 減少數(shù)據(jù)訪問(wèn)： 設(shè)置合理的字段類型，啟用壓縮，通過(guò)索引訪問(wèn)等減少磁盤 IO。
- 返回更少的數(shù)據(jù)： 只返回需要的字段和數(shù)據(jù)分頁(yè)處理，減少磁盤 IO 及網(wǎng)絡(luò) IO。
- 減少交互次數(shù)： 批量 DML 操作，函數(shù)存儲(chǔ)等減少數(shù)據(jù)連接次數(shù)。
- 減少服務(wù)器 CPU 開銷： 盡量減少數(shù)據(jù)庫(kù)排序操作以及全表查詢，減少 CPU 內(nèi)存占用。
- 利用更多資源：使用表分區(qū)，可以增加并行操作，更大限度利用 CPU 資源。
Select 語(yǔ)句執(zhí)行順序
從這個(gè)順序中我們可以發(fā)現(xiàn)，所有的查詢語(yǔ)句都是從 FROM 開始執(zhí)行的。在實(shí)際執(zhí)行過(guò)程中，每個(gè)步驟都會(huì)為下一個(gè)步驟生成一個(gè)虛擬表，這個(gè)虛擬表將作為下一個(gè)執(zhí)行步驟的輸入。
SQL 優(yōu)化策略
-
避免無(wú)索引場(chǎng)景
- 盡量避免在字段開頭模糊查詢，會(huì)導(dǎo)致數(shù)據(jù)庫(kù)引擎放棄索引進(jìn)行全表掃描。
- 盡量避免使用 in 和 not in，會(huì)導(dǎo)致引擎走全表掃描。
- 如果是連續(xù)數(shù)值，可以用between代替。
- 如果是子查詢，可以用exists代替。
- 盡量避免使用 or，會(huì)導(dǎo)致數(shù)據(jù)庫(kù)引擎放棄索引進(jìn)行全表掃描。
- 可以用union代替or。
- 盡量避免進(jìn)行 null 值的判斷，會(huì)導(dǎo)致數(shù)據(jù)庫(kù)引擎放棄索引進(jìn)行全表掃描。
- 可以給字段添加默認(rèn)值 0，對(duì) 0 值進(jìn)行判斷。
- 當(dāng)數(shù)據(jù)量大時(shí)，避免使用 where 1 = 1 的條件。通常為了方便拼裝查詢條件，我們會(huì)默認(rèn)使用該條件，數(shù)據(jù)庫(kù)引擎會(huì)放棄索引進(jìn)行全表掃描。
- 用代碼拼裝sql時(shí)進(jìn)行判斷，沒(méi) where 條件就去掉 where，有where條件就加 and。
- 查詢條件不能用 <> 或者 !=
- where 條件僅包含復(fù)合索引非前置列
- 隱式類型轉(zhuǎn)換造成不使用索引
- order by 條件要與 where 中條件一致，否則 order by 不會(huì)利用索引進(jìn)行排序
- 正確使用 hint 優(yōu)化語(yǔ)句
- MySQL中可以使用hint指定優(yōu)化器在執(zhí)行時(shí)選擇或忽略特定的索引。一般而言，處于版本變更帶來(lái)的表結(jié)構(gòu)索引變化，更建議避免使用hint，而是通過(guò)Analyze table多收集統(tǒng)計(jì)信息。但在特定場(chǎng)合下，指定hint可以排除其他索引干擾而指定更優(yōu)的執(zhí)行計(jì)劃。、
-
SELECT語(yǔ)句其他優(yōu)化
- 避免出現(xiàn)select *
- 避免出現(xiàn)不確定結(jié)果的函數(shù)
- 多表關(guān)聯(lián)查詢時(shí)，小表在前，大表在后。
- 使用表的別名
- 當(dāng)在SQL語(yǔ)句中連接多個(gè)表時(shí)，請(qǐng)使用表的別名并把別名前綴于每個(gè)列名上。這樣就可以減少解析的時(shí)間并減少哪些友列名歧義引起的語(yǔ)法錯(cuò)誤。
- 用 where 字句替換 HAVING 字句
- 調(diào)整 Where 字句中的連接順序
- MySQL采用從左往右，自上而下的順序解析where子句。根據(jù)這個(gè)原理，應(yīng)將過(guò)濾數(shù)據(jù)多的條件往前放，最快速度縮小結(jié)果集。
-
增刪改 DML 語(yǔ)句優(yōu)化
- 大批量插入數(shù)據(jù)，建議使用多個(gè)值的INSERT語(yǔ)句，這比使用分開INSERT語(yǔ)句快，一般情況下批量插入效率有幾倍的差別。
- 適當(dāng)使用 commit 可以釋放事務(wù)占用的資源而減少消耗，commit后能釋放的資源如下：
- 事務(wù)占用的 undo 數(shù)據(jù)塊；
- 事務(wù)在 redo log 中記錄的數(shù)據(jù)塊；
- 釋放事務(wù)施加的，減少鎖爭(zhēng)用影響性能。特別是在需要使用delete 刪除大量數(shù)據(jù)的時(shí)候，必須分解刪除量并定期commit。
- 避免重復(fù)查詢更新的數(shù)據(jù)
- 查詢優(yōu)先還是更新（insert、update、delete）優(yōu)先
- 寫入操作優(yōu)先于讀取操作。
- 對(duì)某張數(shù)據(jù)表的寫入操作某一時(shí)刻只能發(fā)生一次，寫入請(qǐng)求按照它們到達(dá)的次序來(lái)處理。
- 對(duì)某張數(shù)據(jù)表的多個(gè)讀取操作可以同時(shí)地進(jìn)行。
-
查詢條件優(yōu)化
- 對(duì)于復(fù)雜的查詢，可以使用中間臨時(shí)表 暫存數(shù)據(jù)。
- 優(yōu)化 group by 語(yǔ)句，默認(rèn)情況下，MySQL 會(huì)對(duì)GROUP BY分組的所有值進(jìn)行排序，如果查詢包括 GROUP BY 但你并不想對(duì)分組的值進(jìn)行排序，你可以指定 ORDER BY NULL禁止排序。
- 優(yōu)化join語(yǔ)句，連接(JOIN)，之所以更有效率一些，是因?yàn)?MySQL 不需要在內(nèi)存中創(chuàng)建臨時(shí)表來(lái)完成這個(gè)邏輯上的需要兩個(gè)步驟的查詢工作。
- 優(yōu)化union查詢，MySQL通過(guò)創(chuàng)建并填充臨時(shí)表的方式來(lái)執(zhí)行union查詢。除非確實(shí)要消除重復(fù)的行，否則建議使用union all。原因在于如果沒(méi)有all這個(gè)關(guān)鍵詞，MySQL會(huì)給臨時(shí)表加上distinct選項(xiàng)，這會(huì)導(dǎo)致對(duì)整個(gè)臨時(shí)表的數(shù)據(jù)做唯一性校驗(yàn)，這樣做的消耗相當(dāng)高。
- 拆分復(fù)雜SQL為多個(gè)小SQL，避免大事務(wù)
- 簡(jiǎn)單的SQL容易使用到MySQL的QUERY CACHE；
- 減少鎖表時(shí)間特別是使用MyISAM存儲(chǔ)引擎的表；
- 可以使用多核CPU。
- 使用truncate代替delete
- 當(dāng)刪除全表中記錄時(shí)，使用delete語(yǔ)句的操作會(huì)被記錄到undo塊中，刪除記錄也記錄binlog，當(dāng)確認(rèn)需要?jiǎng)h除全表時(shí)，會(huì)產(chǎn)生很大量的binlog并占用大量的undo數(shù)據(jù)塊，此時(shí)既沒(méi)有很好的效率也占用了大量的資源。
- 使用truncate替代，不會(huì)記錄可恢復(fù)的信息，數(shù)據(jù)不能被恢復(fù)。也因此使用truncate操作有其極少的資源占用與極快的時(shí)間。另外，使用truncate可以回收表的水位，使自增字段值歸零。
- 使用合理的分頁(yè)方式以提高分頁(yè)效率 針對(duì)展現(xiàn)等分頁(yè)需求，合適的分頁(yè)方式能夠提高分頁(yè)的效率。
-
建表優(yōu)化
- 在表中建立索引，優(yōu)先考慮where、order by使用到的字段。
- 盡量使用數(shù)字型字段（如性別，男：1 女：2），若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型，這會(huì)降低查詢和連接的性能，并會(huì)增加存儲(chǔ)開銷。這是因?yàn)橐嬖谔幚聿樵兒瓦B接時(shí)會(huì) 逐個(gè)比較字符串 中每一個(gè)字符，而對(duì)于數(shù)字型而言只需要比較一次就夠了。
- 查詢數(shù)據(jù)量大的表 會(huì)造成查詢緩慢。主要的原因是掃描行數(shù)過(guò)多。這個(gè)時(shí)候可以通過(guò)程序，分段分頁(yè)進(jìn)行查詢，循環(huán)遍歷，將結(jié)果合并處理進(jìn)行展示。
- 用 varchar/nvarchar 代替 char/nchar
