Sql查詢原理與Select執行順序（詳細）

一切都是為了性能，一切都是為了業務

(1) from left_table

(3) join_type join right_table (2) on join_condition

(4) where where_condition

(5) group by group_by_list

(6) with {cube | rollup}

(7) having having_condition

(8) select (9) distinct (11) top_specification select_list

(9) order by order_by_list

标準的 sql 的解析順序為:

(1) from 子句 組裝來自不同資料源的資料

(2) where 子句 基于指定的條件對記錄進行篩選

(3) group by 子句 将資料劃分為多個分組

(4) 使用聚合函數進行計算

(5) 使用having子句篩選分組

(6) 計算所有的表達式

(7) 使用order by對結果集進行排序

1. from：對from子句中前兩個表執行笛卡爾積生成虛拟表vt1

2. on: 對vt1表應用on篩選器隻有滿足 join_condition 為真的行才被插入vt2

3. outer(join)：如果指定了 outer join保留表(preserved table)中未找到的行将行作為外部行添加到vt2，生成t3，如果from包含兩個以上表，則對上一個聯結生成的結果表和下一個表重複執行步驟和步驟直接結束。

4. where：對vt3應用 where 篩選器隻有使 where_condition 為true的行才被插入vt4

5. group by：按group by子句中的列清單對vt4中的行分組生成vt5

6. cube|rollup：把超組(supergroups)插入vt6，生成vt6

7. having：對vt6應用having篩選器隻有使 having_condition 為true的組才插入vt7

8. select：處理select清單産生vt8

9. distinct：将重複的行從vt8中去除産生vt9

10. order by：将vt9的行按order by子句中的列清單排序生成一個遊标vc10

11. top：從vc10的開始處選擇指定數量或比例的行生成vt11 并傳回調用者

看到這裡，那麼用過linq to sql的文法有點相似啊？如果我們我們了解了sql server執行順序，那麼我們就接下來進一步養成日常sql的好習慣，也就是在實作功能的同時有考慮性能的思想，資料庫是能進行集合運算的工具，我們應該盡量的利用這個工具，所謂集合運算實際就是批量運算，就是盡量減少在用戶端進行大資料量的循環操作，而用sql語句或者存儲過程代替。

傳回資料到用戶端至少需要資料庫提取資料、網絡傳輸資料、用戶端接收資料以及用戶端處理資料等環節，如果傳回不需要的資料，就會增加伺服器、網絡和用戶端的無效勞動，其害處是顯而易見的，避免這類事件需要注意：

a、橫向來看

(1) 不要寫select * 的語句，而是選擇你需要的字段。

(2) 當在sql語句中連接配接多個表時, 請使用表的别名并把别名字首于每個column上。這樣一來,就可以減少解析的時間并減少那些由column歧義引起的文法錯誤。

如有表table1(id,col1)和table2(id,col2)

select a.id, a.col1, b.col2 – select a.id, col1, col2 –不要這麼寫，不利于将來程式擴充 from table1 a inner join table2 b on a.id=b.id where …

b、縱向來看

(1) 合理寫where子句，不要寫沒有where的sql語句。

(2) select top n * – 沒有where條件的用此替代。

a、控制同一語句的多次執行，特别是一些基礎資料的多次執行是很多程式員很少注意的。

b、減少多次的資料轉換，也許需要資料轉換是設計的問題，但是減少次數是程式員可以做到的。

c、杜絕不必要的子查詢和連接配接表，子查詢在執行計劃一般解釋成外連接配接，多餘的連接配接表帶來額外的開銷。

d、合并對同一表同一條件的多次update，比如

update employee set fname=’haiwer’ where emp_id=’ vpa30890f’update employee set lname=’yang’ where emp_id=’ vpa30890f’

這兩個語句應該合并成以下一個語句

update employee set fname=’haiwer’,lname=’yang’where emp_id=’ vpa30890f’

e、update操作不要拆成delete操作+insert操作的形式，雖然功能相同，但是性能差别是很大的。

在複雜系統中，臨時表和表變量很難避免，關于臨時表和表變量的用法，需要注意：

a、如果語句很複雜，連接配接太多，可以考慮用臨時表和表變量分步完成。

b、如果需要多次用到一個大表的同一部分資料，考慮用臨時表和表變量暫存這部分資料。

c、如果需要綜合多個表的資料，形成一個結果，可以考慮用臨時表和表變量分步彙總這多個表的資料。

d、其他情況下，應該控制臨時表和表變量的使用。

e、關于臨時表和表變量的選擇，很多說法是表變量在記憶體，速度快，應該首選表變量，但是在實際使用中發現：

(1) 主要考慮需要放在臨時表的資料量，在資料量較多的情況下，臨時表的速度反而更快。

(2) 執行時間段與預計執行時間(多長)

f、關于臨時表産生使用select into和create table + insert into的選擇，一般情況下：

select into會比create table + insert into的方法快很多，但是select into會鎖定tempdb的系統表sysobjects、sysindexes、syscolumns，在多使用者并發環境下，容易阻塞其他程序。

是以我的建議是，在并發系統中，盡量使用create table + insert into，而大資料量的單個語句使用中，使用select into。

子查詢是一個 select 查詢，它嵌套在 select、insert、update、delete 語句或其它子查詢中。

任何允許使用表達式的地方都可以使用子查詢，子查詢可以使我們的程式設計靈活多樣，可以用來實作一些特殊的功能。但是在性能上，往往一個不合适的子查詢用法會形成一個性能瓶頸。如果子查詢的條件中使用了其外層的表的字段，這種子查詢就叫作相關子查詢。

相關子查詢可以用in、not in、exists、not exists引入。 關于相關子查詢，應該注意：

(1) not in、not exists的相關子查詢可以改用left join代替寫法。比如：

select pub_name from publishers where pub_id notin (select pub_id from titles where type =’business’)

可以改寫成：

select a.pub_name from publishers a leftjoin titles b on b.type =’business’and a.pub_id=b. pub_id where b.pub_id is null

比如not exists：

select title from titles where not exists (select title_id from sales where title_id = titles.title_id)

可以改寫成

select title from titles leftjoin sales on sales.title_id = titles.title_id where sales.title_id isnull

2）如果保證子查詢沒有重複 ，in、exists的相關子查詢可以用inner join 代替。比如：

select pub_name from publishers where pub_id in (select pub_id from titles where type =’business’)

select a.pub_name –select distinct a.pub_name from publishers a innerjoin titles b on b.type =’business’and a.pub_id=b. pub_id

(3) in的相關子查詢用exists代替，比如：

select pub_name from publishers (select pub_id from titles where type =’business’)

可以用下面語句代替：

select pub_name from publishers where exists (select1from titles where type =’business’and pub_id= publishers.pub_id)

4） 不要用count(*)的子查詢判斷是否存在記錄，最好用left join或者exists，比如有人寫這樣的語句：

select job_desc from jobs where (selectcount(*) from employee where job_id=jobs.job_id)=0

應該改成：

select jobs.job_desc from jobs leftjoin employee on employee.job_id=jobs.job_id where employee.emp_id isnull where (select count(*) from employee where job_id=jobs.job_id)

whereexists (select 1 from employee where job_id=jobs.job_id)

建立索引後，并不是每個查詢都會使用索引，在使用索引的情況下，索引的使用效率也會有很大的差别。隻要我們在查詢語句中沒有強制指定索引，索引的選擇和使用方法是sqlserver的優化器自動作的選擇，而它選擇的根據是查詢語句的條件以及相關表的統計資訊，這就要求我們在寫sql語句的時候盡量使得優化器可以使用索引。為了使得優化器能高效使用索引，寫語句的時候應該注意：

（1）不要對索引字段進行運算，而要想辦法做變換，比如：

select id from t where num/2=100

應改為:

select id from t where num=100*2 select id from t where num/2=num1

如果num有索引應改為:

select id from t where num=num1*2

如果num1有索引則不應該改。

（2）發現過這樣的語句：

select 年,月,金額 from 結餘表 where100*年+月=2010*100+10

應該改為

select 年,月,金額 from 結餘表 where 年=2010 and 月=10

（3）不要對索引字段進行格式轉換

日期字段的例子：

whereconvert(varchar(10), 日期字段,120)=’2010-07-15′

where日期字段〉=’2010-07-15′and 日期字段’2010-07-16′

isnull轉換的例子：

whereisnull(字段,”)”應改為:where字段”

whereisnull(字段,”)=”不應修改

whereisnull(字段,’f') =’t'應改為: where字段=’t’

whereisnull(字段,’f')’t'不應修改

（4） 不要對索引字段進行格式轉換

whereleft(name, 3)=’abc’ -或者 where substring(name,1, 3)=’abc’

應改為: where name like’abc%’

日期查詢的例子：

wheredatediff(day, 日期,’2010-06-30′)=0

應改為:where 日期=’2010-06-30′ and 日期 ’2010-07-01′

wheredatediff(day, 日期,’2010-06-30′)0

應改為:where 日期 ’2010-06-30′

應改為:where 日期 ’2010-07-01′

應改為:where 日期=’2010-07-01′

應改為:where 日期=’2010-06-30′

（5）不要對索引字段使用函數

where left(name, 3)=’abc’ 或者where substring(name,1, 3)=’abc’

應改為: where name like ’abc%’

應改為:where 日期=’2010-06-30′and 日期 ’2010-07-01′

（6）不要對索引字段進行多字段連接配接

比如：

where fame+’. ‘+lname=’haiwei.yang’

where fname=’haiwei’ and lname=’yang’

a、多表連接配接的時候，連接配接條件必須寫全，甯可重複，不要缺漏。

b、連接配接條件盡量使用聚集索引

c、注意on、where和having部分條件的差別

on是最先執行， where次之，having最後，因為on是先把不符合條件的記錄過濾後才進行統計，它就可以減少中間運算要處理的資料，按理說應該速度是最快的，where也應該比having快點的，因為它過濾資料後才進行sum，在兩個表聯接時才用on的，是以在一個表的時候，就剩下where跟having比較了。

考慮聯接優先順序：

(1) inner join

(2) left join (注：right join 用 left join 替代)

(3) cross join

其它注意和了解的地方有：

a、在in後面值的清單中，将出現最頻繁的值放在最前面，出現得最少的放在最後面，減少判斷的次數。

b、注意union和union all的差別。– 允許重複資料用union all好

c、注意使用distinct，在沒有必要時不要用。

d、truncate table 與 delete 差別。

e、減少通路資料庫的次數。

還有就是我們寫存儲過程，如果比較長的話，最後用标記符标開，因為這樣可讀性很好，即使語句寫的不怎麼樣，但是語句工整，c# 有region，sql我比較喜歡用的就是：

–startof 查詢在職人數

sql語句

–end of

正式機器上我們一般不能随便調試程式，但是很多時候程式在我們本機上沒問題，但是進正式系統就有問題，但是我們又不能随便在正式機器上操作，那麼怎麼辦呢？我們可以用復原來調試我們的存儲過程或者是sql語句，進而排錯。

begintran

update a set 字段=”

rollback

作業存儲過程我一般會加上下面這段，這樣檢查錯誤可以放在存儲過程，如果執行錯誤復原操作，但是如果程式裡面已經有了事務復原，那麼存儲過程就不要寫事務了，這樣會導緻事務復原嵌套降低執行效率，但是我們很多時候可以把檢查放在存儲過程裡，這樣有利于我們解讀這個存儲過程，和排錯。

begintransaction –事務復原開始 –檢查報錯 <code>if ( @@error0 )</code> <code>begin</code> <code>--復原操作</code> <code>rollbacktransaction</code> <code>raiserror(</code><code>'删除工作報告錯誤'</code><code>, </code><code>16</code><code>, </code><code>3</code><code>)</code> <code>return</code> <code>end</code> –結束事務 committransaction