以下是網上流傳比較廣泛的30種SQL查詢語句優化方法：

應盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描。
對查詢進行優化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。
應盡量避免在 where 子句中對字段進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如：

select id from t where num is null

可以在num上設置默認值0，確保表中num列沒有null值，然后這樣查詢：

select id from t where num=0

盡量避免在 where 子句中使用 or 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描，
如：

select id from t where num=10 or num=20

可以這樣查詢：

select id from t where num=10 union all select id from t where num=20

下面的查詢也將導致全表掃描：(不能前置百分號)

select id from t where name like ‘%c%’

若要提高效率，可以考慮全文檢索。
in 和 not in 也要慎用，否則會導致全表掃描，如：

select id from t where num in(1,2,3)

對于連續的數值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

如果在 where 子句中使用參數，也會導致全表掃描。因為SQL只有在運行時才會解析局部變量，但優化程序不能將訪問計劃的選擇推遲到運行時；它必須在編譯時進行選擇。然 而，如果在編譯時建立訪問計劃，變量的值還是未知的，因而無法作為索引選擇的輸入項。
如下面語句將進行全表掃描：

select id from t where num=@num

可以改為強制查詢使用索引：

select id from t with(index(索引名)) where num=@num

應盡量避免在 where 子句中對字段進行表達式操作，這將導致引擎放棄使用索引而進行全表掃描。
如：

select id from t where num/2=100

應改為:

select id from t where num=100*2

應盡量避免在where子句中對字段進行函數操作，這將導致引擎放棄使用索引而進行全表掃描。
如：

select id from t where substring(name,1,3)=’abc'

生成的id應改為:

select id from t where name like ‘abc%’

如：

select id from t where datediff(day,createdate,’2005-11-30′)=’2005-11-30′

應改為：

select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′

不要在 where 子句中的“=”左邊進行函數、算術運算或其他表達式運算，否則系統將可能無法正確使用索引。
在使用索引字段作為條件時，如果該索引是復合索引，那么必須使用到該索引中的第一個字段作為條件時才能保證系統使用該索引，否則該索引將不會被使 用，并且應盡可能的讓字段順序與索引順序相一致。
不要寫一些沒有意義的查詢，如需要生成一個空表結構：select col1,col2 into #t from t where 1=0這類代碼不會返回任何結果集，但是會消耗系統資源的，應改成這樣：create table #t(…)

很多時候用 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)

并不是所有索引對查詢都有效，SQL是根據表中數據來進行查詢優化的，當索引列有大量數據重復時，SQL查詢可能不會去利用索引，如一表中有字段 sex，male、female幾乎各一半，那么即使在sex上建了索引也對查詢效率起不了作用。

索引并不是越多越好，索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率，因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個表的索引數最好不要超過6個，若太多則應考慮一些不常使用到的列上建的索引是否有 必要。

應盡可能的避免更新 clustered 索引數據列，因為 clustered 索引數據列的順序就是表記錄的物理存儲順序，一旦該列值改變將導致整個表記錄的順序的調整，會耗費相當大的資源。若應用系統需要頻繁更新 clustered 索引數據列，那么需要考慮是否應將該索引建為 clustered 索引。

盡量使用數字型字段，若只含數值信息的字段盡量不要設計為字符型，這會降低查詢和連接的性能，并會增加存儲開銷。這是因為引擎在處理查詢和連接時會 逐個比較字符串中每一個字符，而對于數字型而言只需要比較一次就夠了。

盡可能的使用 varchar/nvarchar 代替 char/nchar (如果一個字段的值是固定長度的，則盡量使用char類型)，因為首先變長字段存儲空間小，可以節省存儲空間，其次對于查詢來說，在一個相對較小的字段內搜索效率顯然要高些。

任何地方都不要使用 select * from t ，用具體的字段列表代替“*”，不要返回用不到的任何字段。

盡量使用表變量來代替臨時表。如果表變量包含大量數據，請注意索引非常有限（只有主鍵索引）。

避免頻繁創建和刪除臨時表，以減少系統表資源的消耗。

臨時表并不是不可使用，適當地使用它們可以使某些例程更有效，例如，當需要重復引用大型表或常用表中的某個數據集時。但是，對于一次性事件，最好使 用導出表。

在新建臨時表時，如果一次性插入數據量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果數據量不大，為了緩和系統表的資源，應先create table，然后insert。

如果使用到了臨時表，在存儲過程的最后務必將所有的臨時表顯式刪除，先 truncate table ，然后 drop table，這樣可以避免系統表的較長時間鎖定。

盡量避免使用游標，因為游標的效率較差，如果游標操作的數據超過1萬行，那么就應該考慮改寫。

使用基于游標的方法或臨時表方法之前，應先尋找基于集的解決方案來解決問題，基于集的方法通常更有效。

與臨時表一樣，游標并不是不可使用。對小型數據集使用 FAST_FORWARD 游標通常要優于其他逐行處理方法，尤其是在必須引用幾個表才能獲得所需的數據時。在結果集中包括“合計”的例程通常要比使用游標執行的速度快。如果開發時 間允許，基于游標的方法和基于集的方法都可以嘗試一下，看哪一種方法的效果更好。

在所有的存儲過程和觸發器的開始處設置 SET NOCOUNT ON ，在結束時設置 SET NOCOUNT OFF 。無需在執行存儲過程和觸發器的每個語句后向客戶端發送 DONE_IN_PROC 消息。

盡量避免向客戶端返回大數據量，若數據量過大，應該考慮相應需求是否合理。

盡量避免大事務操作，提高系統并發能力。

----

---

分享：MySQL數據庫設計總結

作者針對MySQL數據庫設計總結了19條規則，其中對于大表優化，他在規則7中提到： 大表可以考慮水平拆分。

大表影響查詢效率，根據業務特性有很多拆分方式，像根據時間遞增的數據，可以根據時間來分；以id劃分的數據，可以根據id%數據庫個數的方式來拆分。

19條規則摘要如下：

規則1：一般情況可以選擇MyISAM存儲引擎，如果需要事務支持必須使用InnoDB存儲引擎。

注意：MyISAM存儲引擎 B-tree索引有一個很大的限制：參與一個索引的所有字段的長度之和不能超過1000字節。另外MyISAM數據和索引是分開，而InnoDB的數據存儲是按聚簇(cluster)索引有序排列的，主鍵是默認的聚簇(cluster)索引，因此MyISAM雖然在一般情況下，查詢性能比InnoDB高，但InnoDB的以主鍵為條件的查詢性能是非常高的。

規則2：命名規則。

1、數據庫和表名應盡可能和所服務的業務模塊名一致
2、服務與同一個子模塊的一類表應盡量以子模塊名(或部分單詞)為前綴或后綴
3、表名應盡量包含與所存放數據對應的單詞
4、字段名稱也應盡量保持和實際數據相對應
5、聯合索引名稱應盡量包含所有索引鍵字段名或縮寫，且各字段名在索引名中的順序應與索引鍵在索引中的索引順序一致，并盡量包含一個類似idx的前綴或后綴，以表明期對象類型是索引。
6、約束等其他對象也應該盡可能包含所屬表或其他對象的名稱，以表明各自的關系

規則3：數據庫字段類型定義

1、經常需要計算和排序等消耗CPU的字段,應該盡量選擇更為迅速的字段，如用TIMESTAMP(4個字節，最小值1970-01-01 00:00:00)代替Datetime（8個字節，最小值1001-01-01 00:00:00）,通過整型替代浮點型和字符型
2、變長字段使用varchar，不要使用char
3、對于二進制多媒體數據，流水隊列數據(如日志)，超大文本數據不要放在數據庫字段中

規則4：業務邏輯執行過程必須讀到的表中必須要有初始的值。避免業務讀出為負或無窮大的值導致程序失敗

規則5：并不需要一定遵守范式理論，適度的冗余，讓Query盡量減少Join

規則6：訪問頻率較低的大字段拆分出數據表。有些大字段占用空間多，訪問頻率較其他字段明顯要少很多，這種情況進行拆分，頻繁的查詢中就不需要讀取大字段，造成IO資源的浪費。

規則7：大表可以考慮水平拆分。大表影響查詢效率，根據業務特性有很多拆分方式，像根據時間遞增的數據，可以根據時間來分。以id劃分的數據，可根據id%數據庫個數的方式來拆分。

一.數據庫索引

規則8：業務需要的相關索引是根據實際的設計所構造sql語句的where條件來確定的，業務不需要的不要建索引，不允許在聯合索引（或主鍵）中存在多于的字段。特別是該字段根本不會在條件語句中出現。

規則9：唯一確定一條記錄的一個字段或多個字段要建立主鍵或者唯一索引，不能唯一確定一條記錄，為了提高查詢效率建普通索引

規則10：業務使用的表，有些記錄數很少，甚至只有一條記錄，為了約束的需要，也要建立索引或者設置主鍵。

規則11：對于取值不能重復，經常作為查詢條件的字段，應該建唯一索引(主鍵默認唯一索引)，并且將查詢條件中該字段的條件置于第一個位置。沒有必要再建立與該字段有關的聯合索引。

規則12：對于經常查詢的字段，其值不唯一，也應該考慮建立普通索引，查詢語句中該字段條件置于第一個位置，對聯合索引處理的方法同樣。

規則13：業務通過不唯一索引訪問數據時，需要考慮通過該索引值返回的記錄稠密度，原則上可能的稠密度最大不能高于0.2，如果稠密度太大，則不合適建立索引了。

規則14：需要聯合索引(或聯合主鍵)的數據庫要注意索引的順序。SQL語句中的匹配條件也要跟索引的順序保持一致。注意：索引的順勢不正確也可能導致嚴重的后果。

規則15：表中的多個字段查詢作為查詢條件，不含有其他索引，并且字段聯合值不重復，可以在這多個字段上建唯一的聯合索引，假設索引字段為 (a1,a2,...an),則查詢條件(a1 op val1,a2 op val2,...am op valm)m<=n,可以用到索引，查詢條件中字段的位置與索引中的字段位置是一致的。

規則16：聯合索引的建立原則(以下均假設在數據庫表的字段a,b,c上建立聯合索引(a,b,c))

1、聯合索引中的字段應盡量滿足過濾數據從多到少的順序，也就是說差異最大的字段應該房子第一個字段
2、建立索引盡量與SQL語句的條件順序一致，使SQL語句盡量以整個索引為條件，盡量避免以索引的一部分(特別是首個條件與索引的首個字段不一致時)作為查詢的條件
3、Where a=1,where a>=12 and a<15,where a=1 and b<5 ,where a=1 and b=7 and c>=40為條件可以用到此聯合索引；而這些語句where b=10,where c=221,where b>=12 and c=2則無法用到這個聯合索引。
4、當需要查詢的數據庫字段全部在索引中體現時，數據庫可以直接查詢索引得到查詢信息無須對整個表進行掃描(這就是所謂的key-only)，能大大的提高查詢效率。
當a，ab，abc與其他表字段關聯查詢時可以用到索引
5、當a，ab，abc順序而不是b，c，bc，ac為順序執行Order by或者group不要時可以用到索引
6、以下情況時，進行表掃描然后排序可能比使用聯合索引更加有效
a.表已經按照索引組織好了
b.被查詢的數據站所有數據的很多比例。

規則17：重要業務訪問數據表時。但不能通過索引訪問數據時，應該確保順序訪問的記錄數目是有限的，原則上不得多于10.

二.Query語句與應用系統優化

規則18：合理構造Query語句

1、Insert語句中，根據測試，批量一次插入1000條時效率最高，多于1000條時，要拆分，多次進行同樣的插入，應該合并批量進行。注意query語句的長度要小于mysqld的參數 max_allowed_packet
2、查詢條件中各種邏輯操作符性能順序是and,or,in,因此在查詢條件中應該盡量避免使用在大集合中使用in (or in 到底那個性能更好，很多博客上寫的都不一樣)
3、永遠用小結果集驅動大記錄集，因為在mysql中，只有Nested Join一種Join方式，就是說mysql的join是通過嵌套循環來實現的。通過小結果集驅動大記錄集這個原則來減少嵌套循環的循環次數，以減少IO總量及CPU運算次數
4、盡量優化Nested Join內層循環。
5、只取需要的columns，盡量不要使用select *
6、僅僅使用最有效的過濾字段，where 字句中的過濾條件少為好
7、盡量避免復雜的Join和子查詢
Mysql在并發這塊做得并不是太好，當并發量太高的時候，整體性能會急劇下降，這主要與Mysql內部資源的爭用鎖定控制有關，MyIsam用表鎖，InnoDB好一些用行鎖

規則19：應用系統的優化

1、合理使用cache，對于變化較少的部分活躍數據通過應用層的cache緩存到內存中，對性能的提升是成數量級的。
2、對重復執行相同的query進行合并，減少IO次數。
3、事務相關性最小原則

總結

以上是生活随笔為你收集整理的SQL优化方法的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。