我總結52條SQL語句性能優化政策，千萬要收藏好

英文 | https://mobileappcircular.com/summarize-52-sql-statement-performance-optimization-strategies-youd-better-save-to-favorites-57bed2a2dd19

翻譯 | 楊小愛

在今天的文章中，我總結了52條SQL語句性能優化政策，希望對您有所幫助，文章有點長，一次沒有閱讀完的話，可以收藏起來，有空的時候，拿出來慢慢研究，如果有不對的地方，也歡迎留言區交流學習，大家一起共同進步。

下面是正文開始。

1、優化查詢，盡量避免全表掃描，首先，考慮在where和order by涉及的列上建立索引。

2、盡量避免where子句中字段的空值判斷。

建立表時，NULL是預設值，但大多數時候，您應該使用NOT NULL，或者使用一個特殊的值，比如0，-1作為預設值。

3、盡量避免在 where 子句中使用 != 或 <> 運算符。MySQL 僅對以下運算符使用索引：<、<=、=、>、>=、BETWEEN、IN，有時還有 LIKE。

4、盡量避免在where子句中使用in來連接配接條件，否則會導緻引擎放棄使用索引而進行全表掃描。

您可以使用 UNION 來合并查詢：select id from t where num=10 union all select id from t where num=20。

5、in和notin要慎用，否則會造成全表掃描。對于連續值，如果可以使用 between，則不要使用 in：從 t 中選擇 id，其中 num 介于 1 和 3 之間。

6、下面的查詢也會導緻全表掃描：select id from t where name like '%abc%' 或者 select id from t where name like '%abc' 如果想提高效率，可以考慮full-文本搜尋。

并從 t 中選擇 id，其中像 ‘abc%’ 這樣的名稱僅使用索引。

7、如果在where子句中使用參數，也會引起全表掃描。

8、應盡量避免對where子句中的字段進行表達式操作，應盡量避免對where子句中的字段進行函數操作。

9、在很多情況下，使用exists而不是in，是一個不錯的選擇：

select num from a where num in (select num from b)。      

替換為以下語句：

select num from a where exists(select 1 from b where num=a.num)。      

10、索引雖然可以提高對應select的效率，但是，也會降低insert和update的效率，因為insert或者update的時候可能會重建索引，是以，如何建索引需要慎重考慮，具體取決于具體情況。

一個表的索引個數不要超過6個，如果太多，就要考慮是否需要在不常用的列上建索引。

11、盡量避免更新聚簇索引資料列，因為聚簇索引資料列的順序就是表記錄的實體存儲順序。

一旦列值發生變化，整個表中記錄的順序就會發生調整，這會消耗相當大的資源。如果應用系統需要頻繁更新聚集索引資料列，那麼，就需要考慮是否應該将索引建構為聚集索引。

12、盡量使用數字字段，盡量不要将隻包含數字資訊的字段設計成字元類型，這樣會降低查詢和連接配接的性能，增加存儲開銷。

13、盡量使用varchar/nvarchar代替char/nchar，因為變長字段的存儲空間小，可以節省存儲空間。

其次，對于查詢而言，在相對較小的領域内的搜尋效率明顯更高。

14、最好不要用“”傳回全部：從t中選擇，使用特定的字段清單代替“*”，不要傳回任何未使用的字段。

15、盡量避免向用戶端傳回大量資料。如果資料量過大，就要考慮相應的需求是否合理。

16、使用表别名（Alias）：在SQL語句中連接配接多個表時，請使用表别名，并在每一列上加上别名字首。這樣可以減少解析時間，減少Column歧義引起的文法錯誤。

17、使用“臨時表”臨時存儲中間結果：

簡化 SQL 語句的一個重要方法是使用臨時表來臨時存儲中間結果，但臨時表的好處遠不止這些。

臨時結果臨時存放在臨時表中，後續查詢在tempdb中，可以避免程式中的多次掃描主表也大大減少了程式執行過程中的“共享鎖”阻塞和“更新鎖”，減少了阻塞，并提高并發性能。

18、一些SQL查詢語句應該加入Nolock。讀和寫會互相阻塞。為了提高并發性能，可以在一些查詢中加入nolock，這樣可以在讀的時候允許寫，但缺點是可能讀了不送出髒資料。

使用 nolock 有 3 個原則：

• 如果查詢結果用于“插入、删除、修改”，則不能添加nolock；
• 查詢表屬于頻繁分頁，慎用nolock；
• 臨時表可以用來儲存“資料前影”，其功能類似于Oracle的undo表空間。臨時表可以用來提高并發性能，不要用nolock。

19、常見的簡化規則如下：

不要超過5個表連接配接（JOIN），考慮使用臨時表或表變量來存儲中間結果。謹慎使用子查詢，視圖嵌套不應太深。通常，視圖的嵌套不應超過兩個。

20、将需要查詢的結果預先計算好放入表中，查詢時再選擇。這是SQL7.0之前最重要的方法，比如醫院住院費的計算。

21、OR的寫法可以分解成多個查詢，多個查詢可以通過UNION連接配接起來。它們的速度隻與是否使用索引有關。如果查詢需要使用聯合索引，UNION all 的執行效率更高。

多個 OR 子句不使用索引，是以以 UNION 的形式重寫并嘗試比對索引。一個關鍵問題是是否使用索引。

22、在IN後面的值清單中，将出現頻率最高的值放在最上面，将出現次數最少的值放在最後，減少判斷次數。

23、盡量把資料處理放在伺服器上，減少網絡開銷，比如使用存儲過程。

存儲過程是經過編譯、優化、組織成執行計劃并存儲在資料庫中的 SQL 語句。它是控制流語言的集合，當然速度也很快。

對于重複執行的動态SQL，可以使用臨時存儲過程，将程序（臨時表）放在Tempdb中。

24、當伺服器有足夠的記憶體時，配置線程數=最大連接配接數+5，這樣可以最大化效率；否則，使用配置線程數<最大連接配接數啟用SQL SERVER線程池來解決問題，如果還是數量=最大連接配接數+5，嚴重損害伺服器性能。

25、查詢關聯的寫法：

select a.personMemberID, * from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID = ‘JCNPRH39681’ (A = B ,B = ‘number’)
select a.personMemberID, * from chineseresume a,personmember b where a.personMemberID = b.referenceid and a.personMemberID =’JCNPRH39681' and b.referenceid =’JCNPRH39681' (A = B ,B =’number’, A = ‘ Number’)
select a.personMemberID, * from chineseresume a,personmember b where b.referenceid = ‘JCNPRH39681’ and a.personMemberID = ‘JCNPRH39681’ (B = ‘Number’, A = ‘Number’)      

26、盡量使用exists而不是select count(1)來判斷是否有記錄。count函數隻在對表中所有行進行計數時使用，count(1)比count(*)效率更高。

27、嘗試使用“>=”而不是“>”。

28、名額使用标準：

應結合應用程式考慮建立索引。建議大型OLTP表不要超過6個索引；

盡量使用索引字段作為查詢條件，尤其是聚集索引。如果需要，可以使用 index index_name 來強制指定索引；

查詢大表時避免表掃描，必要時考慮建立索引；

使用索引字段作為條件時，如果索引是聯合索引，那麼必須以索引中的第一個字段作為條件，保證系統使用該索引，否則不會使用該索引；

注意索引的維護，定期重建索引，重新編譯存儲過程。

29、以下SQL條件語句中的列都正确索引，但執行速度很慢：

SELECT * FROM record WHERE substrINg(card_no,1,4)=’5378’ (13 seconds)
SELECT * FROM record WHERE amount/30< 1000 (11 seconds)
SELECT * FROM record WHERE convert(char(10),date,112)=’19991201’ (10 seconds)      

分析：

WHERE 子句中對列的任何操作的結果都是在 SQL 運作時逐列計算的，是以它必須在不使用該列的索引的情況下執行表搜尋。

如果在編譯查詢時能得到這些結果，就可以通過SQL優化器進行優化，使用索引，避免查表，是以将SQL改寫如下：

SELECT * FROM record WHERE card_no like ‘5378%’ (< 1 second)
SELECT * FROM record WHERE amount< 1000*30 (< 1 second)
SELECT * FROM record WHERE date= ‘1999/12/01’ (< 1 second)      

30、當有批量插入或更新時，使用批量插入或批量更新，從不更新每條記錄。

31、在所有存儲過程中，都可以使用SQL語句，我絕不會使用循環來實作。

例如：要列出上個月的每一天，我将使用connect by來遞歸查詢，我永遠不會使用從上個月的第一天到最後一天的循環。

32、選擇最有效的表名順序（僅在基于規則的優化器中有效）：

Oracle 的解析器按從右到左的順序處理 FROM 子句中的表名。最後在FROM子句中寫入的表（基本表驅動表）會先處理，在FROM子句中包含多個表。在這種情況下，必須選擇記錄數最少的表作為基表。

如果表連接配接查詢超過3個，則需要選擇交表作為基表。交叉表是指被其他表引用的表。

33、通過在GROUP BY之前過濾掉不需要的記錄來提高GROUP BY語句的效率。以下兩個查詢傳回相同的結果，但第二個查詢顯然要快得多。

低效：

SELECT JOB, AVG(SAL)
FROM EMP
GROUP BY JOB
HAVING JOB =’PRESIDENT’
OR JOB =’MANAGER’      

高效的：

SELECT JOB, AVG(SAL)
FROM EMP
WHERE JOB =’PRESIDENT’
OR JOB =’MANAGER’
GROUP BY JOB      

34、SQL語句使用大寫，因為Oracle總是先解析SQL語句，然後将小寫字母轉換為大寫字母再執行。

35、别名的使用。别名是大型資料庫的一種應用技術。查詢中表名和列名用字母作别名，查詢速度比建連接配接表快1.5倍。

36、避免死鎖。在存儲過程和觸發器中始終以相同的順序通路同一個表；交易盡量短，交易涉及的資料量盡量減少；從不等待使用者在交易中輸入。

37、 避免使用臨時表。除非你真的需要它們，否則你應該盡量避免使用臨時表。相反，您可以使用表變量；大部分時間（99%），表變量駐留在記憶體中，是以速度比臨時表快，臨時表駐留在TempDb資料庫中，是以對臨時表的操作需要跨庫通信，速度為自然慢。

38、最好不要使用觸發器：

觸發觸發器，執行觸發器事件本身就是一個消耗資源的過程；

如果可以使用限制來實作，盡量不要使用觸發器；

不要對不同的觸發事件（插入、更新和删除）使用相同的觸發器；

不要在觸發器中使用事務代碼。

39、索引建立規則：

• 表的主鍵和外鍵必須有索引；
• 超過300個資料量的表應該有索引；
• 對于經常與其他表連接配接的表，應在連接配接字段上建立索引；
• Where子句中經常出現的字段，尤其是大表的字段，應該進行索引；
• 索引應該建立在高度選擇性的字段上；
• 索引應該建立在小字段上。不要為大文本字段甚至長字段建立索引；
• 建立複合索引需要仔細分析，盡量考慮用單字段索引代替；
• 正确選擇複合索引中的主列字段，一般是選擇性較好的字段；
• AND模式下，一個複合索引的多個字段是否經常同時出現在Where子句中？是否有很少或沒有單字段查詢？如果是這樣，你可以建立一個複合索引；否則，考慮單字段索引；
• 如果複合索引中包含的字段經常單獨出現在Where子句中，則會分解為多個單字段索引；
• 如果複合索引包含3個以上的字段，則慎重考慮其必要性，考慮減少複合字段；
• 如果這些字段上既有單字段索引又有複合索引，一般可以删除複合索引；
• 不要為頻繁執行資料操作的表建立過多的索引；
• 删除無用索引，避免對執行計劃産生負面影響；
• 在表上建立的每個索引都會增加存儲開銷，索引也會增加插入、删除和更新操作的處理開銷。另外，複合索引太多，在單字段索引的情況下，一般沒有價值；反之，在資料增删時也會降低性能，尤其是對于頻繁更新的表，負面影響更大。
• 盡量不要索引資料庫中包含大量重複值的字段。

40、MySQL查詢優化總結：

使用慢查詢日志查找慢查詢，使用執行計劃來判斷查詢是否正常運作，始終測試您的查詢，看看它們是否運作在最佳狀态。

性能總是會随着時間而改變。避免在整個表上使用 count(*)。它可能會鎖定整個表并保持查詢一緻，以便後續類似的查詢可以使用查詢緩存。在适當的情況下使用 GROUP BY 而不是 DISTINCT。

在 WHERE、GROUP BY 和 ORDER BY 子句中使用索引列，保持索引簡單，不要在多個索引中包含同一列。

有時 MySQL 會使用錯誤的索引。在這種情況下，使用 USE INDEX 并檢查使用 SQL_MODE=STRICT 的問題。對于少于 5 條記錄的索引字段，在 UNION 時使用 LIMIT 而不是 OR。

為了避免更新前的SELECT，使用INSERT ON DUPLICATE KEY或INSERT IGNORE，不要使用UPDATE來實作，不要使用MAX，使用索引字段和ORDER BY子句，LIMIT M,N在某些情況下，實際上可以減慢查詢速度, 謹慎使用。

在 WHERE 子句中使用 UNION 而不是子查詢。重新開機MySQL後記得預熱資料庫，保證資料在記憶體中，查詢速度快。考慮持久連接配接而不是多個連接配接，減少開銷。

基準查詢包括使用伺服器上的負載。有時，一個簡單的查詢會影響其他查詢。當伺服器上的負載增加時，使用 SHOW PROCESSLIST 檢視緩慢和有問題的查詢。在開發環境生成的鏡像資料中測試所有可疑查詢。

41、MySQL備份過程：

• 從輔助複制伺服器備份；
• 備份時停止複制，避免資料依賴和外鍵限制不一緻；
• 完全停止 MySQL 并從資料庫檔案備份；
• 如果使用MySQL dump進行備份，請同時備份二進制日志檔案——確定複制不被中斷；
• 不要相信LVM快照，這很可能造成資料不一緻，給你以後帶來麻煩；
• 為了更容易進行單表恢複，以表為機關導出資料——如果資料與其他表隔離。
• 使用mysqldump時請使用-opt；
• 備份前對表進行檢查和優化；
• 為了更快地導入，在導入過程中暫時禁用外鍵限制。；
• 為了更快的導入，導入時暫時關閉唯一性檢測；
• 計算每次備份後的資料庫、表和索引的大小，以便更好地監控資料大小的增長；
• 通過自動排程腳本監控複制執行個體的錯誤和延遲；
• 定期執行備份。

42、查詢緩沖區不會自動處理空格。是以，在編寫SQL語句時，應盡量減少空格的使用，尤其是在SQL的開頭和結尾（因為查詢緩沖區不會自動截取首尾空格）。

43、會員使用mid作為分表查詢的标準友善嗎？一般業務需求基本都是以使用者名作為查詢依據。通常，使用者名應該用作哈希模數來劃分表。

在分表的情況下，MySQL的分區函數就是這樣做的，對代碼是透明的；在代碼級别實作它似乎不合理。

44、我們應該為資料庫中的每個表設定一個ID作為其主鍵，最好是INT類型（推薦UNSIGNED），并設定自動添加的AUTO_INCREMENT标志。

45、在所有存儲過程和觸發器的開頭設定 SET NOCOUNT ON，并在結尾設定 SET NOCOUNT OFF。執行完存儲過程和觸發器的每個語句後，無需向用戶端發送 DONE_IN_PROC 消息。

46、MySQL查詢可以啟用高速查詢緩存。這是提高資料庫性能的有效 MySQL 優化方法之一。當多次執行同一個查詢時，從緩存中提取資料并直接從資料庫傳回資料要快得多。

47、EXPLAIN SELECT查詢用于跟蹤檢視效果：

使用 EXPLAIN 關鍵字讓您知道 MySQL 如何處理您的 SQL 語句。這可以幫助您分析查詢或表結構的性能瓶頸。EXPLAIN 查詢結果還将告訴您如何使用索引主鍵，如何搜尋和排序資料表。

48、當隻需要一行資料時使用LIMIT 1：

當您查詢一段時間的表時，你已經知道隻會有一個結果，但是，因為您可能需要擷取遊标，或者您可能會檢查傳回的記錄數。

在這種情況下，添加 LIMIT 1 可以提高性能。這樣MySQL資料庫引擎在找到一條資料後就會停止搜尋，而不是繼續尋找下一條與記錄比對的資料。

49、為表選擇合适的存儲引擎：

myisam：主要應用是讀和插入操作，隻有少量的更新和删除，對事務的完整性和并發性要求不是很高。

InnoDB：事務處理，并發條件下需要資料一緻性。除了插入和查詢之外，還包括許多更新和删除。（InnoDB 有效減少了删除和更新導緻的鎖）。

對于支援事務的InnoDB類型的表，影響速度的主要原因是AUTOCOMMIT的預設設定是開啟的，程式沒有顯式調用BEGIN啟動事務，導緻每次插入都自動送出，這嚴重影響速度。

可以在執行SQL前調用begin，多條SQL組成一個事務（即使開啟了autocommit），性能會大大提升。

50、優化表的資料類型，選擇合适的資料類型：

原則：通常越小越好，越簡單越好，所有字段必須有預設值，盡量避免為空。

例如：設計資料庫表時，盡量使用較小的整數類型，以占用較小的磁盤空間。（mediumint 比 int 更合适）

例如時間字段：datetime和timestamp，datetime占8個位元組，timestamp占4個位元組，隻占一半，timestamp範圍1970-2037适合更新時間

MySQL 可以很好地支援大量資料的通路，但一般來說，資料庫中的表越小，在其上執行查詢的速度就越快。

是以，在建立表時，為了獲得更好的性能，我們可以将表中字段的寬度設定得盡可能小。

例如：定義郵政編碼字段時，如果設定為CHAR(255)，顯然給資料庫增加了不必要的空間。即使使用 VARCHAR 也是多餘的，因為 CHAR(6) 可以很好地完成任務。

同樣，如果可能的話，我們應該使用MEDIUMINT而不是BIGIN來定義整數字段，并且我們應該嘗試将字段設定為NOT NULL，以便資料庫在以後執行查詢時不需要比較NULL值。

對于某些文本字段，例如“省”或“性别”，我們可以将它們定義為 ENUM 類型。因為在 MySQL 中，ENUM 類型被視為數字資料，數字資料的處理速度比文本類型快得多。這樣，我們就可以提高資料庫的性能。

51、字元串資料類型：char、varchar、文本選擇差別。

52、對列的任何操作都會引起表掃描，包括資料庫函數、計算表達式等，查詢時盡量将操作移到等号的右邊。