一般在寫SQL時需要注意哪些問題，可以提高查詢的效率？

12-26

今天的面試題目，被一問說不出來了。我感覺是一個經驗性和習慣性問題，但自己寫的SQL不多，還真沒感覺到這個問題，在實際應用中真的是這樣操作的嘛，求經驗人士來簡單說說。書寫簡單SQL語句需要注意的問題[提高效率的查詢]，看了這篇文章後，感覺有點為難應屆生了。

學SQL語句性能的時候要注意非常重要一點：

不要用程序開發的思維思考資料庫！！

在資料庫中，SQL語句是一個抽象的概念，而不代表具體的實現。我舉一個簡單的例子，比如說A表和B表做連接，具體的Loop Join實現邏輯的偽代碼為：

for each row in t1 matching range {
for each row in t2 matching reference key {
for each row in t3 {
if row satisfies join conditions,
send to client
}
}
}

而寫SQL語句的時候，僅僅需要寫select * from a inner join b on a.clo1=b.col2
，該SQL語句僅僅寫了你希望獲得的結果，而沒有寫任何實現邏輯，因此SQL是無關實現的、抽象的。

那麼具體如何執行在關係資料庫中都有一個所謂的「優化器」實現，現代關係資料庫的優化器是基於成本選擇具體執行步驟（執行計劃）的。因此妨礙優化器選擇最優執行計劃的SQL就不是好SQL。

首先提一下樓主舉出文章的幾點觀點，我一個個糾正：

1. 選擇最有效率的表名順序。

這肯定是不對的，SQL優化器不會關心寫表時，哪個表在前，哪個表在後，再次強調，SQL是抽象的、無關實現的。該語句在邏輯優化階段優化器會自動選擇最優的計劃。

2. WHERE子句中的連接順序

同上。

3. SELECT子句中避免使用『 * 『

這一句話是正確的，但文章中提到：
「實際上,在解析的過程中, 會將』*』依次轉換成所有的列名, 這個工作是通過查詢數據字典完成的, 這意味著將耗費更多的時間.」

這種理解是錯誤的，解析*號的成本幾乎可以忽略不計。而更多的成本在於如下：
1.讀取多餘的列可能導致索引的書籤查找，當讀取條目多時會無法使用特定索引。
2.如果select *作用於表連接，可能造成更大的成本開銷。

4. 計算記錄條數
文章提到：count(*) 比count(1)稍快這屬於以訛傳訛了，count()函數是聚合函數，指的是計算count()中所有非null的條目，count(1)和count(*)都是常量，意味著計算所有非空列。想像一下select 1 from 表，表中有10行的話就會返回10個1。count（）同理，默認一般RDBMS會選擇最窄的非Null列上的索引去統計具體條數。

5. 使用表的別名(Alias)
文中提到使用別名減少解析時間，我只能評論太有想像力了。

6. 用Where子句替換HAVING子句
這點的說法不合適，where和having是完全不同的子句，having的價值是使用聚合函數作為篩選條件中的一部分。沒有誰替代誰一說。

----------------------------
那什麼樣的SQL語句是不好的語句呢：
那就是妨礙優化器更好的實現執行邏輯的SQL語句，這類語句包括：
1.where條件里出現各種花樣百出的代碼，比如函數、運算等。
2.語句過大，大量的表join會導致中間結果集不準確，從而限制優化器選擇較好的執行計劃。

等等.........

------------------
所以嘗試盡量think like query optimizer,而不是think like programmer

答主：mysql dba。平時會有各種各樣的開發小朋友來問各種各樣sql好不好，正好在這裡簡單總結下。
千言萬語會成一句話：學會用資料庫的方式來思考如何執行sql，那麼什麼是好的sql，首先要明白資料庫是如何執行一個sql，一個事務的。

資料庫執行sql的大致流程
粗略流程，所有關係型資料庫都是這幾步，具體前後順序根據不同dbms不同配置下略有小差別，以下過程都需要耗時耗資源
1.應用程序與資料庫伺服器建立鏈接
2.sql發送到資料庫，資料庫驗證是否有執行的許可權
3.進入語法解析器，進行詞法與語法分析
4.進入優化器生成執行計劃，部分dbms會檢查是否有可重用的執行計劃
5.根據執行計劃依次掃描相關表中的行，不在數據緩衝區的走io
6.同時對於被掃描的行可能加鎖，同時也可能會被其他sql阻塞
7.掃描的行足夠放入查詢緩存則開始運算或直接返回，不夠則生成臨時表，可能消耗io
8.對sql結果進行計算（可能）
9.將計算完成的結果全部寫入網路io（可能）
10.如果事務完成則同步事務日誌並釋放鎖，具體方式取決於dbms和當前配置
11.關閉連接（可選）

如何優化
這麼多步驟，每一步都有優化策略，我盡量用簡單的語言來描述
1.應用程序與資料庫伺服器建立鏈接
引入資料庫連接池，避免每次都與資料庫建立連接，提高效率

2.sql發送到資料庫，資料庫驗證是否有執行的許可權
沒撒好說的

3.進入語法解析器，進行詞法與語法分析
也沒撒好說的，想要資料庫在這裡少用點資源就把sql寫的簡單點，但是差別不大

4.進入優化器生成執行計劃，部分dbms會檢查是否有可重用的執行計劃
哦也，最複雜的部分來了，任何資料庫如何生成執行計劃都可以寫一本幾百頁的書，我就簡單說說，複雜的我也說不出來，哈哈哈
關係型資料庫選擇走什麼執行計劃都是基於消耗最小化的思路來的，簡單來說就是走什麼索引，按什麼順序走表，被掃到的數據行最少。如果你的表結構很複雜，有各種混搭的索引，你的join很多，那執行計劃分析的時間就會拉長。所以sql對應的表索引簡單，join或子查詢少就快，複雜了優化器也會得選擇困難症。

5.根據執行計劃依次掃描相關表中的行，不在數據緩衝區的走io
存儲引擎掃描表的性能消耗參考下面的list，消耗從大到小
全表掃描&>全索引掃描&>部分索引掃描&>索引查找&>唯一索引/主鍵查找&>常量/null
要走索引對於sql語句也有要求，不能在謂詞上作任何運算，掃描行數一般不能超過表的17%左右，這對你數據分布又有要求，比如你查select xxx from human where sex ="man"，五五開，還是走掃描。這裡我就不展開了，推薦題主一本書《Relational Database Index Design and the Optimizers》

6.同時對於被掃描的行可能加鎖，同時也可能會被其他sql阻塞
如果掃描的行多，sql執行的時間長，被阻塞的概率就高，阻塞別人的概率也高，然後大家一起等，資料庫就hung住了

7.掃描的行足夠放入查詢緩存則開始運算或直接返回，不夠則生成臨時表，可能消耗io
一次取的盡量少，這不單指返回服務端的行數，應該從嵌套最深的一個子查詢開始算

8.對sql結果進行計算（可能）
少用各種複雜的函數啊，count啊，order by啊等等

9.將計算完成的結果全部寫入網路io（可能）
請盡量少返回一點數據，如果不行請多次分批

10.如果事務完成則同步事務日誌並釋放鎖，具體方式取決於dbms和當前配置
這裡舉兩個代表性栗子:
sql渣：
for i in (1-1000):
start transaction;
insert into table values (1);
commit;
end for
sql贊：
start transaction;
for i in (1-1000):
insert into table values (1);
end for
commit;
sql贊爆：
insert into table values (1)()...........()(1000);
首先，sql語法是我臨時自創的，這個不是關鍵，關鍵在sql渣先生是1000個事務插1000行，日誌flush1000次。sql贊先生是一個事務插1000行，事務日誌flush1次。sql贊爆最nice。
這個例子我想表達的意思是如果你要用sql做一件事，那就要盡量讓這件事佔用的事務總時間最少。

第二個例子
sql渣：
update table where id &> 0 and id &< 1000000;

sql贊:
update table where id &> 0 and id &< 1000;
update table where id &>= 1000 and id &< 2000;
update table where id &>＝ 2000 and id &< 3000;
....
....
這個例子我想表達的意思是如果你要用sql做一件很大的事，那就盡量讓大事化成很多小事。
兩個例子好好體會下，一點不矛盾哦。補充一下，這裡每個update都是單獨事務

11.關閉連接（可選）
同1，別每次都關，關了也許還要重連。不關的話記得commit就好了，千萬要記得commit啊！

最後，題主作為一個應屆生的話，目前不了解資料庫實現細節是很正常的，但是要學會一種思路：如果我是一個資料庫，我會怎麼執行一個sql，我喜歡怎麼樣的sql？
能寫出多好的sql取決於你多了解資料庫，完

其實如你所說SQL優化問題大部分取決於經驗。寫的多了優化多了，經驗自然而然的就多了，優化水平也就高了。舉個例子，在PostgreSQL的執行計划出現nestloop關聯方式的話，我優先就會關閉這種查詢方式，因為在數據足夠大得情況下這種嵌套的查詢方式效率往往不及hashjoin的查詢方式。這些都僅僅是我這一年DBA開發的經驗。如果更高層次DBA可以從中間看出更多的門道。

對於應屆畢業生來說，如果不是純粹的走DBA路線和特地去研究的話，我個人覺得水平可能基本停留在簡單的增刪改查上，對SQL語句的優化或者表結構的優化逗涉及的很少，如果不是走DBA路線，只建議稍微了解一些SQL的優化思想就好了，真面試到也就實話實說唄，畢竟不是主方向。

Oracle 11g 里CBO已經很不錯了.如果表結構設計合理，絕大多數SQL不需考濾性能問題．更重要是SQL寫得清析．因為SQL相對代碼，寫注釋沒那麼方便，如果不寫清析，以後就是坑．

題主給的鏈接里提到的技巧,有一部分在Oracle11g里並不適用.

1.查詢多用索引。譬如你看一本書會先看目錄頁，索引就是資料庫數據的」目錄」。mysql採用B樹組織索引。
2.如果是單列索引，最好理清數據邏輯，把覆蓋目標數據最多的索引條件放在where子句的最左邊。一定要盡量避免對索引的算數運算，或者當做函數參數。那樣操作，會導致索引無效，還是會直接遍歷所有記錄。
3.多列索引操作，可以使用alter語句添加多列索引，特別針對那些and條件~
4.查詢語句盡量簡單，寧願多查找幾次，也不要再一個語句中反覆查找。
還有很多技巧，最重要的是要有索引概念！！

本帖最後由 javarice 於 2015-11-27 10:31 編輯

新的關於資料庫的內容。查詢速度慢的原因很多，常見如下幾種：

1、沒有索引或者沒有用到索引(這是查詢慢最常見的問題，是程序設計的缺陷)

2、I/O吞吐量小，形成了瓶頸效應。

3、沒有創建計算列導致查詢不優化。

4、內存不足

5、網路速度慢

6、查詢出的數據量過大(可以採用多次查詢，其他的方法降低數據量)

7、鎖或者死鎖(這也是查詢慢最常見的問題，是程序設計的缺陷)

8、sp_lock,sp_who,活動的用戶查看,原因是讀寫競爭資源。

9、返回了不必要的行和列

10、查詢語句不好，沒有優化
可以通過如下方法來優化查詢：

1、把數據、日誌、索引放到不同的I/O設備上，資料庫增加讀取速度，以前可以將Tempdb應放在RAID0上，SQL2000不在支持。數據量(尺寸)越大，提高I/O越重要.

2、縱向、橫向分割表，減少表的尺寸(sp_spaceuse)

3、升級硬體

4、根據查詢條件,建立索引,優化索引、優化訪問方式，限制結果集的數據量。注意填充因子要適當(最好是使用默認值0)。索引應該盡量小，使用位元組數小的列建索引好(參照索引的創建),不要對有限的幾個值的欄位建單一索引如性別欄位

5、提高網速;

6、擴大伺服器的內存,Windows2000和SQLServer2000能支持4-8G的內存。配置虛擬內存：虛擬內存大小應基於計算機上並發運行的服務進行配置。運行MicrosoftSQLServer?2000時，可考慮將虛擬內存大小設置為計算機中安裝的物理內存的1.5倍。如果另外安裝了全文檢索功能，並打算運行Microsoft搜索服務以便執行全文索引和查詢，可考慮：將虛擬內存大小配置為至少是計算機中安裝的物理內存的3倍。將SQLServermaxservermemory伺服器配置選項配置為物理內存的1.5倍(虛擬內存大小設置的一半)。

7、增加伺服器CPU個數;但是必須明白並行處理串列處理更需要資源例如內存。使用並行還是串列程是MsSQL自動評估選擇的。單個任務分解成多個任務，就可以在處理器上運行。例如耽擱查詢的排序、連接、掃描和GROUPBY字句同時執行，SQLSERVER根據系統的負載情況決定最優的並行等級，複雜的需要消耗大量的CPU的查詢最適合併行處理。但是更新操作Update,Insert，Delete還不能並行處理。

8、如果是使用like進行查詢的話，簡單的使用index是不行的，但是全文索引，耗空間。like"a%"使用索引like"%a"不使用索引用like"%a%"查詢時，查詢耗時和欄位值總長度成正比,所以不能用CHAR類型，而是VARCHAR。對於欄位的值很長的建全文索引。

9、DBServer和APPLicationServer分離;OLTP和OLAP分離

10、分散式分區視圖可用於實現資料庫伺服器聯合體。聯合體是一組分開管理的伺服器，但它們相互協作分擔系統的處理負荷。這種通過分區數據形成資料庫伺服器聯合體的機制能夠擴大一組伺服器，以支持大型的多層Web站點的處理需要。有關更多信息，參見設計聯合資料庫伺服器。(參照SQL幫助文件"分區視圖")

a、在實現分區視圖之前，必須先水平分區表

b、在創建成員表後，在每個成員伺服器上定義一個分散式分區視圖，並且每個視圖具有相同的名稱。這樣，引用分散式分區視圖名的查詢可以在任何一個成員伺服器上運行。系統操作如同每個成員伺服器上都有一個原始表的複本一樣，但其實每個伺服器上只有一個成員表和一個分散式分區視圖。數據的位置對應用程序是透明的。

答不出來就答不出來唄，我剛畢業找工作都是背了面試題運氣好才找到工作的。
sql優化這個話題太大了。這篇文章寫的都是些簡單的方案。屬於基本知識一類的。建議LZ背下來並簡單理解就差不多了。。。
要真正寫好sql，根源還是要理解資料庫的原理，才能知其所以然。

拋磚引玉吧，只是有時寫過SQL，不擅長不是專家，如有錯誤請指出。
題主那篇鏈接看過了一下，首先有一點沒有寫清楚是什麼資料庫，Oracle、MySQ還是SQLServer。
因為平常用的是Oracle，所以僅從Oracle來講，每個資料庫的解析方式可能會不一樣的。
用Oracle的話，那篇引文的錶鏈接順序就用不上了，因為Oracle會根據執行計劃來自動選擇表的鏈接順序(別的資料庫語言可能是從前到後),而影響執行計劃的因素就很多了。
而in和exists的問題，如果我沒有記錯的話exists是用外表去loop里查詢，而in則是兩者做一個hash鏈接，所以沒有優劣之分，要看使用場景，哪個表數據量大一點。

上面好像有點偏題。
那最後就告訴題主一個大招來提高查詢效率，注意是查詢效率哦。
那就是加索引了，很大一部分查詢問題可以通過加索引來解決，索引加在鏈接鍵上和order by的鍵上，注意索引鍵值不要有null就可以了。
以上。

關鍵在數據的組織

注意百度

我在知乎上發的一篇文章里有一個是sql查詢效率的建議與改進，直接發過來的話，說我違規，鬱悶。。。。。。。你可以去看下，希望能幫到你

Kimberly Tripp說過「三個一定」，在任何時候都是成立的，也是正確的。你工作的越久，經驗越豐富就越有感觸。
* 一定要Know your data
* 一定要Know your workload
* 一定要Know how SQL Server (or other DBMS you are using) works

不要檢索已知的列，如select cust_no, cust_name from CustInfo where cust_no = 『10000050』這句話對么，為什麼？？？

加索引
加分區
存儲過程內用臨時表

sqlserver where 的條件欄位本身不要加函數，欄位值與欄位名的數據類型保持一致，僅select需要的欄位，盡量加 with(nolock)，這題目條件範圍限定的比較小，諸如加索引等都不屬於「一般寫sql」，注意審題

找本資料庫性能的書看看吧。你在知乎問，只會給你些碎片知識。你知道了也理解不深刻。真心的建議。

寫SQL的時候，可以參考《SQL 反模式》這本書。而和編程比較起來，就是必須要轉到面向集合的編程上來，儘可能少用過程編程的思路來寫SQL。

另外就是要對SQL的存儲引擎、優化引擎都有所里理解才好。

加index 排序 limit

當然你寫過最長的sql可能還沒超過3行。數據量也小。。。很難遇到性能問題。。。

關於優化，建議看看PG和MYSQL，