SQL Server查詢優化和事務處理

對于了解掌握SQL的增、刪、改、查的語句操作是最基本的，實際生產環境中，我們還會用到一些比較高級的數據處理和查詢，包括索引、視圖、存儲過程和觸發器。本篇博文主要如何更好的實現對數據庫的操作、診斷及優化。

博文大綱：
一、索引；
二、視圖；
三、存儲過程；
四、觸發器；
五、事務；

一、索引

索引提供指針以指向存儲在表中指定列的數據值，然后根據指定的次序排列這些指針，再跟隨指針到達包含該值的列。

1.什么是索引

數據庫中的索引與書籍中的目錄相似。在一本書中，無需閱讀整本書，利用目錄就可以快速的查找到所需的信息。在數據庫中，索引使數據庫程序無須對整個表進行掃描，就可以在其中找到所需的數據。書中的目錄就是一個詞語列表，其中注明了包含各個詞的頁碼。而數據庫中的索引是某個表中一列或若干列值的集合，以及物理表示這些值得數據業的邏輯指針清單。

索引是SQL Server編排數據的內部方法，它為SQL Server提供一種方法來編排查詢數據的路由。

索引頁是數據庫中存儲索引的數據頁。索引頁存放檢索數據行的關鍵字頁以及該數據行的地址指針。通過使用索引，可以大大提高數據庫的檢索速度，改善數據庫性能。

2.索引分類

在SQL Server中，常用的索引有：

（1）唯一索引

唯一索引不允許兩行具有相同的索引值。

如果現有數據中存在重復的鍵值，則一般情況下大多數數據庫不允許創建唯一索引。當新數據使表中的鍵值重復時，數據庫也拒絕接收此數據。

創建了唯一約束，將自動創建唯一索引。盡管唯一索引有助于找到信息，但是為了獲得最佳性能，建議使用主鍵約束。

（2）主鍵索引

在數據庫關系圖中為表定義一個主鍵將自動創建主鍵索引，主鍵索引是唯一索引的特殊類型。

主建索引要求主鍵中的每個值都是唯一的。當在查詢使用主鍵索引時，它還允許快速訪問數據。

（3）聚集索引

在聚集索引中，表中各行的物理順序與鍵值的邏輯（索引）順序相同。

一個表中只能包含一個聚集索引。

（4）非聚集索引

非聚集索引建立在索引頁上，在查詢數據是可以從索引中找到記錄存放的位置。
非聚集索引使表中各行數據存放的物理順序與鍵值的邏輯順序不匹配。聚集索引比非聚集索引有更快的數據訪問速度。

在SQL Server中，一個表只能創建一個聚集索引，但可以有多個非聚集索引。設置某列為主鍵，該列就默認為聚集索引。

（5）復合索引

在創建索引時，并不是只能對其中一列創建索引，與創建主鍵一樣，可以將多個列組合作為索引，這種索引稱為復合索引。

注意：只有用到復合索引的第一列或整個復合索引列作為條件完成數據查詢時才會用到該索引。

（6）全文索引

全文索引是一種特殊類型的基于標記的功能性索引，由SQL Server中全文引擎服務創建和維護。

全文索引主要用于在大量文本中搜索字符串，此時使用全文索引的效率將大大高于使用T-SQL的LIKE關鍵字的效率。

3.創建和使用索引

創建索引的方法有兩種：使用SSMS和T-SQL語句。

使用SSMS創建索引，如下：



使用SSMS創建索引完成！

select * from chengji
WITH (INDEX=IX_chengji)
where name LIKE '孫%'
//使用創建的索引查詢數據

雖然可以指定SQL Server按哪個索引進行數據查詢，但一般不需要人工指定。SQL Server將會根據所創建的索引，自動優化查詢。

使用索引可加快數據檢索速度，但為每個列都建立索引沒有必要。因為索引本身也是需要維護，并占用一定的資源，可以按照以下標準選擇建立索引的列。

• 頻繁搜索的列；
• 經常用于查詢選擇的列；
• 經常排序、分組的列；
• 經常用于連接的列（主鍵/外鍵）；

不要使用下面的列創建索引：

• 僅包含幾個不同值的列；
• 表中僅包含幾行；

在工作中的經驗：

• 查詢時減少使用“*”返回全部列，不要返回不需要的列；
• 索引應該盡量小，在字節小的列上建立索引；
• where子句中有多個條件表達式時，包含索引列的表達式應置于其他條件表達式之前；
• 避免在order by子句中使用表達式；
• 根據業務數據發生頻率，定期重新生成或重新組織索引，進行碎片整理；

二、視圖

視圖是保存在數據庫中的select查詢。因此，對查詢指定的大多數操作也可以在視圖上進行。

使用視圖的原因有：

• （1）處于安全考慮，用戶不必看到整個數據庫結構，而隱藏部分數據；
• （2）符合用戶日常業務邏輯，使其對數據更容易理解；

1.什么是視圖

視圖是另一種查看數據庫中一個或多個表中的數據的方法。視圖是一種虛擬表，通常是作為來自一個或多個表的行或列的子集創建的。當然，視圖也可以包含全部的行和列。但是，視圖并不是數據庫中存儲的數據值的集合，它的行和列來自查詢中引用的表。在執行時，視圖直接顯示來自表中的數據。

視圖充當著查詢中指定的表的篩選器。定義視圖的查詢可以基于一個或多個表，也可以基于其他視圖、當前數據庫或其他數據庫。

視圖通常用來進行以下三種操作：

• 篩選表中的行；
• 防止未經許可的用戶訪問敏感的信息；
• 將多個物理數據表抽象為一個邏輯數據表。

（1）使用視圖的好處：

（1）對最終用戶的好處：
結果更容易理解；
獲得數據更容易；
（2）對開發人員的好處：
限制數據檢索更容易；
維護應用程序更方便；

（2）創建視圖

語法：

create view aa
as
SELECT     dbo.基本信息表.學號, dbo.成績表.學號 AS Expr1, dbo.基本信息表.姓名, dbo.成績表.成績
FROM         dbo.基本信息表 INNER JOIN
                      dbo.成績表 ON dbo.基本信息表.學號 = dbo.成績表.學號

查看視圖

select * from aa

（3）使用視圖的注意事項

（1）每個視圖可以使用多個表；
（2）與查詢相似，一個視圖可以嵌套另一個視圖，最好不要超過三層；
（3）視圖定義中的select語句不能包含以下內容：

• order by子句，除非子啊select語句的選擇列表中也有一個TOP子句；
• into關鍵字；
• 引用臨時表或表變量；

三、存儲過程

SQL Server使用存儲過程來避免遠程發送并執行SQL代碼帶來的安全隱患。

1.為什么需要存儲過程

當今的軟件大多應用于網絡中，而一般應用程序所運用的數據保存在數據庫中。在沒有使用存儲過程的數據庫應用程序中，用戶大多從本地極端及客戶端通過網絡向服務器端發送SQL代碼編寫的請求，服務器端對接收到SQL代碼進行語法編譯后執行，并經指定結果傳送回客戶端，再由客戶端的應用軟件處理后輸出。如果開發者對服務器的安全性考慮不全面，就會為非法者提供盜取數據的機會。如圖：

未經授權的非法者在網絡中截取用戶想服務器發送的SQL代碼，改寫后的惡意SQL代碼提交到服務器編譯并執行，最后非法者就比較容易地獲得他所需的數據。

從圖中，我們可以看到應用程序執行的過程是不安全的，主要在于以下幾個方面：

• 數據不安全：網絡傳送SQL代碼，容易被未經授權者截取；
• 每次提交SQL代碼都要經過語法編譯后再執行，影響應用程序的運行性能；
• 網絡流量大：對于反復執行的相同SQL代碼，將會在網絡上多次傳送，增加網絡傳輸流量。

為了解決這些問題，我們可以采用存儲過程把對數據庫操作的SQL代碼預先編譯好并保存在服務器端，用戶只需在本機上輸入要執行的存儲過程名稱和必要的數據就可以直接調用執行存儲過程完成行管的操作。這樣。既減少了網絡傳輸流量，又能保證應用程序的運行性能，同時也防止了未經授權者想截獲SQL代碼的行為。

2.什么是存儲過程

存儲過程是SQL語句和控制語句的預編譯集合，保存在數據庫中，可由應用程序調用執行，而且允許用戶聲明變量，邏輯控制語句及其他強大的編程功能。

使用存儲過程的優點：

• 1.模塊化程序設計；
• 2.執行速度快、效率高；減少網絡流量；
• 3.減少網絡流量；
• 4.具有良好的安全性；

3.系統存儲過程

SQL Server提供系統存儲過程，它們是一組預編譯的T-SQL語句。系統存儲過程提供了管理數據庫和更新表的機制，并充當從系統表中檢索信息的快捷方式

（1）常用的系統存儲過程

SQL Server的系統存儲過程的名稱以“sp-”開頭，并存放在Resource數據庫中。如圖：

比如：

exec sp_databases
#列出當前系統中的數據庫
exec sp_helptext aa
#查看視圖的語句文本

若xp_cmdshell作為服務器安全配置的一部分而被關閉，請使用如下語句啟用：

exec sp_configure 'show advanced options',1
#顯示高級配置信息
go
reconfigure
#重新配置
go
exec sp_configure 'xp_cmdshell',1
#打開xp_cmdshell選項
go
reconfigure
#重新配置
go

（2）常用的擴展存儲過程

比如使用這些語句在系統中創建某些文件：

exec xp_cmdshell 'md c:\bank',no_output
#創建文件夾c:\bank
exec xp_cmdshell 'dir c:\'
#列出c盤下的文件等內容

create   proc  oo
as
select 姓名, SUM(成績)  as 總成績
from   基本信息表  left join  成績表  on  基本信息表.學號=成績表.學號
group  by  姓名
#創建存儲過程qq
exec qq
#查看存儲過程qq
create   proc  ww
@shuo  varchar(10)
as
select 姓名, SUM(成績)  as 總成績
from   基本信息表  left join  成績表  on  基本信息表.學號=成績表.學號
group  by  姓名
having  姓名=@shuo
#創建針對每個同學查看的記錄
exec ww 張三
#查看ww存儲過程但是只查看張三

四、觸發器

觸發器分為以下幾種
INSERT觸發器：當向表中插入數據時觸發，自動執行觸發器定義的SQL語句；
UPDATE觸發器：當更新表中某列、多列時觸發，自動執行觸發器所定義的SQL語句；
DELETE觸發器：當刪除表中記錄時觸發，自動執行觸發器定義的SQL語句。
兩個特殊的表由系統管理：

創建觸發器的語句：
第一種

create   trigger   刪除
on  科目表
for  delete
as
begin
delete  from 成績表  
end
#刪除之后不會同步從表中的數據

第二種

create   trigger   自動同步成績
on  科目表
after delete
as
begin
delete  from 成績表  where 科目id=(select   科目id  from deleted)
end
#刪除之后自動同步成績

第三種

create  trigger  禁止刪除
on 基本信息表
for  delete
as
print  '禁止刪除'
rollback  transaction
#禁止刪除數據，如果刪除數據則執行回滾、撤回操作

五、事務

事務：保證數據庫的原子性、一致性、隔離性、持久性，簡稱ACID。
一個小實例

begin transaction
declare @errorsum int
set @errorsum=0
#定義 內部變量，用來保存前一條的執行結果，執行成功為0，執行不成功為非0.
/*--轉帳：張三的帳戶少1000，李四的帳戶多1000元--*/
update bank set currentmoney=currentmoney-1000
where name='zhangsan'
set @errorsum=@errorsum+@@ERROR 
update bank set currentmoney=currentmoney+1000
where name='lisi'
set @errorsum=@errorsum+@@error
print '查看轉賬事務中的余額'
select * from bank
if @errorsum<>0
 begin
  print '交易失敗，回滾事務'
  rollback transaction
 end
else
 begin
   print '交易成功，提交事務，寫入硬盤，永久地保存'
   commit transaction
  end
 go
 print '查看轉賬事務后的余額'
 select * from bank
 go

———————— 本文至此結束，感謝閱讀 ————————