1、避免使用SELECT *

在阿里的編碼規(guī)范中也強制了數(shù)據(jù)庫查詢不能使用SELECT *，因為SELECT *方式走的都是全表掃描，導(dǎo)致的結(jié)果就是查詢效率非常低下，其原因為當(dāng)我們使用SELECT *方式時，SQL會有一個格式化的階段，這個階段會將所有表字段都取出(將*號解析成表的各個字段)，增加了查詢解析器的成本

2、小表驅(qū)動大表

小表驅(qū)動大表指的是使用數(shù)據(jù)量較小，索引比較完備的表，然后使用它的索引和條件對大表的數(shù)據(jù)進行關(guān)聯(lián)和篩選，從而減少數(shù)據(jù)的計算量，提升查詢效率

例如當(dāng)下我又兩張表，分別為學(xué)生表(有45條數(shù)據(jù))，分?jǐn)?shù)表(有1000條數(shù)據(jù))，它們需要進行關(guān)聯(lián)查詢，我們可能會用到LEFT JOIN或者RIGHT JOIN ，在FROM我們跟的表一般為主表，此時如果我們的SQL語句為

“……FROM student LEFT JOIN score……”

就叫做小表驅(qū)動大表；反之就是大表驅(qū)動小表，因為它需要將分?jǐn)?shù)表中的1000條數(shù)據(jù)都查出來之后在去一一關(guān)聯(lián)學(xué)生表

3、用連接查詢代替子查詢

在MySQL進行兩張或兩張以上的表進行聯(lián)查時，可以使用連接查詢和嵌套查詢，盡可能減少嵌套查詢的次數(shù)，其原因為：

• ①嵌套查詢需要執(zhí)行兩次數(shù)據(jù)庫查詢，一次是外部查詢，一次是嵌套子查詢，而是用連接查詢可以減少數(shù)據(jù)庫查詢次數(shù)從而提升查詢效率
• ②連接查詢可以更好地用上數(shù)據(jù)庫的索引，而嵌套查詢通常需要掃描整個表，因此連接查詢可以跟快的執(zhí)行查詢操作

當(dāng)然以上說法也并不絕對，在嵌套查詢中我們依舊可以通過合理的使用IN或EXIST關(guān)鍵字來提升查詢效率(遵循小表驅(qū)動大表原則)。

IN關(guān)鍵字通常應(yīng)用在嵌套查詢的嵌套條件前，例如“……FROM xxx WHERE xxx IN(嵌套SQL)”

如果sql語句中包含了IN關(guān)鍵字，則它會優(yōu)先執(zhí)行IN里面的子查詢語句，然后再執(zhí)行IN外面的語句。如果IN里面的數(shù)據(jù)量很少，作為條件查詢速度更快。

EXISTS關(guān)鍵字也使用在嵌套查詢條件前，例如

“……FROM xxx WHERE EXISTS (嵌套SQL語句)”

如果sql語句中包含了exists關(guān)鍵字，它優(yōu)先執(zhí)行exists左邊的語句（即主查詢語句）。然后把它作為條件，去跟右邊的語句匹配。如果左側(cè)SQL語句查詢數(shù)據(jù)量較少，依舊能夠提升查詢效率。

4、提升GROUP BY的查詢效率

如果沒有為GROUP BY的字段設(shè)置索引，則查詢可能會變得非常慢(這里面涉及到了一個B+樹的概念，它會為我們有序的排序索引的數(shù)據(jù))，當(dāng)為需要分組的字段建立索引后，數(shù)據(jù)就是有序的，這些有序的數(shù)據(jù)會排列在一起

5、使用批量插入

MySQL本身就支持批量插入數(shù)據(jù)，例如：

而在代碼中通過持久層訪問數(shù)據(jù)庫插入數(shù)據(jù)時，我們也不建議一條一條或者通過循環(huán)的形式插入，因為每次調(diào)用循環(huán)里的插入方法 相當(dāng)于都要和數(shù)據(jù)庫進行一次；宜采取的方式是將數(shù)據(jù)封裝在一個集合中，通過集合一次性插入數(shù)據(jù)，例如：

6、當(dāng)一次查詢數(shù)據(jù)量過多時，一定要使用LIMIT進行分頁

一個查詢返回成干上萬的數(shù)據(jù)行，不僅占用了大量的系統(tǒng)資源，也會占用更多的網(wǎng)絡(luò)帶寬，影響查詢效率也有可能造成內(nèi)存溢出。使用LIMIT可以限制返回的數(shù)據(jù)行數(shù)，減輕了系統(tǒng)負(fù)擔(dān)，提高了查詢效率。

使用LIMIT可以達到以下結(jié)果：

• 避免過度提取數(shù)據(jù)：對于大型數(shù)據(jù)庫系統(tǒng)，從數(shù)據(jù)庫中提取大量的數(shù)據(jù)可能會導(dǎo)致系統(tǒng)崩潰。使用LIMIT可以限制提取的數(shù)據(jù)量，避免過度提取數(shù)據(jù)，保護系統(tǒng)不受影響。
• 優(yōu)化分頁查詢：分頁查詢需要查詢所有的數(shù)據(jù)才能進行分頁處理，這會浪費大量的系統(tǒng)資源和時間。使用LIMIT優(yōu)化分頁查詢可以只章詢需要的數(shù)據(jù)行，縮短查詢時間，減少資源的浪費。
• 簡化查詢結(jié)果：有時我們只需要一小部分?jǐn)?shù)據(jù)來得出決策，而不是整個數(shù)據(jù)集。使用LIMIT可以使結(jié)果集更加精簡和易于閱讀和理解。
• 限制行數(shù)非常有用，因為它可以提高查詢性能、減少處理需要的時間，并且只返回我們關(guān)心的列

7、需要合并數(shù)據(jù)時盡可能使用UNION ALL而非UNION

UNION ALL和UNION通常在數(shù)據(jù)合并中使用，例如：

“(SELECT * FROM user WHERE id=1) UNION (SELECT * FROM user WHERE id=2)”

SQL語句使用UNION關(guān)鍵字后，可以獲取去重后的數(shù)據(jù)，而如果使用UNION ALL關(guān)鍵字，可以獲取所有數(shù)據(jù)，包含重復(fù)的數(shù)據(jù)。在業(yè)務(wù)允許出現(xiàn)重復(fù)數(shù)據(jù)的情況下，我們更推薦使用UNION ALL，因為UNION去重需要經(jīng)過數(shù)據(jù)的遍歷、排序和比較，計算無疑是更耗費性能和CPU資源的。

除非是有些特殊的場景，比如UNION ALL之后，結(jié)果集中出現(xiàn)了重復(fù)數(shù)據(jù)，而業(yè)務(wù)場景中是不允許產(chǎn)生重復(fù)數(shù)據(jù)的，這時可以使用UNION；或者在UNION前利用索引提升查詢效率

8、盡可能減少表關(guān)聯(lián)的次數(shù)(減少JOIN的使用)

對于這個優(yōu)化策略，我的理解是，減少JOIN的次數(shù)并非減少必要使用次數(shù)，JOIN終究在查詢效率上還是遠(yuǎn)高于IN的，這個減少的含義應(yīng)該是減少表的冗余關(guān)聯(lián)字段來減少表的鏈接(即需要找到多表能夠進行關(guān)聯(lián)的最少字段，用最少的字段進行關(guān)聯(lián))。

減少JOIN的使用次數(shù)更多的原因還是在SQL語句的編寫上，很容易造成關(guān)聯(lián)錯誤。

總結(jié)

以上就是關(guān)于SQL調(diào)優(yōu)的策略介紹，總結(jié)來說，SQL調(diào)優(yōu)的原則就是減少數(shù)據(jù)掃描、減少與數(shù)據(jù)庫反復(fù)的交互次數(shù)、減少內(nèi)存開銷，而大部分的優(yōu)化策略都是在索引的基礎(chǔ)上是實現(xiàn)的。

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論