淺談 SQL Server 查詢優化與事務處理

之前我們簡單了解了各種查詢的用法，然而在實際開發中還會用到一些比較高級的數據處理和查詢，包括索引、視圖、存儲過程和觸發器。從而能夠更好地實現對數據庫的操作、診斷及優化。

什么是索引呢，索引是 SQL Server 編排數據的內部方法，他為 SQL Server 提供了一種方法來編排查詢數據的路由，從而達到通過使用索引來提高數據庫的檢索速度、改善數據庫性能。

索引也是分為以下六類：
1、唯一索引：不允許兩行具有相同的索引值，創建了唯一約束，將會自動創建唯一索引。
2、主鍵索引：是唯一索引的特殊類型，將表定義一個主鍵時將自動創建主鍵索引，他要求主鍵中的每個值都是唯一的。
3、聚集索引：在聚集索引中，表中各行的物理順序和鍵值的邏輯索引順序相同。（注意：一個表中只能包含一個聚集索引）
4、非聚集索引：建立在索引頁上，在查詢數據時可以從索引中找到記錄存放的位置，聚集索引比非聚集索引有更快的數據訪問速度。
5、復合索引：可以將多個列組合為索引。
6、全文索引：是一種特殊類型的基于標記的功能性索引，主要用于在大量文本中搜索字符串。
創建唯一索引：（不可有重復值）

create unique nonclustered index U_cardID 
on TStudent (cardID)

查看表上的索引：

Select * from sys.sysindexes 
where id=(select object_id from sys.all_objects where name='Tstudent')

按照指定的索引進行查詢：

SELECT * FROM xueyuan
    WITH (INDEX = IX_name)
    WHERE 學員姓名 LIKE '孫%'

視圖是一種虛擬表，通常是作為來自一個或多個表的行或列的子集創建的。
視圖的作用就是：
1、篩選表中的數據
2、防止未經允許的用戶訪問敏感數據
3、將多個物理數據表抽象為一個邏輯數據表
對用戶的好處就是：結果更容易理解、獲得數據更容易
對開發人員的好處就是：限制數據檢索更容易、維護應用程序更方便
注意事項：
1、每個視圖中可以使用多個表
2、與查詢相似，一個視圖可以嵌套另一個視圖，最好不要超過三層
3、試圖定義的 select 語句不能包括以下：

• ORDER BY 子句，除非在 select 語句中的選擇列表中也有一個 TOP 子句
• INTO 關鍵字
• 引用臨時表或表變量
  創建視圖：

  create view netstudent
  as
  select Sname,sex,Class from dbo.TStudent where Class='網絡班'

  從視圖中查找數據：

  select * from netstudent 
  where sex='男'

  創建視圖、更改列的表頭：

  create view V_Tstudent1
  as
  select StudentID  學號,Sname 姓名,sex  性別,cardID  ×××號碼,Birthday  生日,Class  班級 from dbo.TStudent
  select * from V_Tstudent1

  什么是存儲過程，存儲過程就是 SQL 語句和控制語句的預編譯集合，保存在數據庫里，可由應用程序調用執行。
  那為什么需要存儲過程呢，因為從客戶端（client）通過網絡向服務器（server）發送 SQL 代碼并執行是不妥當的，導致數據可能會泄露不安全，印象了應用程序的運行性能，而且網絡流量大。
  使用存儲過程的優點就是：
  1、模塊化程序設計
  2、執行速度快、效率高
  3、減少網絡流量
  4、具有良好的安全性
  存儲過程分為兩類：系統存儲過程和用戶自定義的存儲過程
  系統存儲過程：
  是一組預編譯的T-SQL語句，提供了管理數據庫的更新表的機制，并充當從系統表中檢索信息的快捷方式
  以“sp” 開頭，存放在 Resource數據庫中，常用的系統存儲過程有如下：
  
  使用 T-SQL 語句調用執行存儲過程的語法：

  EXEC [UTE] 存儲過程名 [參數值]
  EXEC為EXECUTE的簡寫

  常用系統存儲過程的用法：

  exec  sp_databases      --列出當前系統中的數據庫
  exec  sp_renamedb  'mybank','bank'   --改變數據庫名稱(單用戶訪問)
  use  MySchool
  go 
  exec sp_tables                       --當前數據庫中可查詢對象的列表
  exec sp_columns student            --查看表student中列的信息
  exec  sp_help student               --查看表student的所有信息
  exec sp_helpconstraint student       --查看表student表的約束
  exec sp_helptext view_student_result   --查看視圖的語句文本
  exec sp_stored_procedures      --返回當前數據庫中的存儲過程列表

  根據系統存儲過程的不同作用，系統存儲過程可以分為不同類，擴展存儲過程是 SQL Server 提供的各類系統存儲過程中的一類。
  允許使用其他編程語言（如C#）創建外部存儲過程，提供從 SQL Server 實例到外部程序的接口
  以“xp”開頭，以DLL形式單獨存在
  一個常用的擴展存儲過程為 xp_cmdshell 他可完成DOS命令下的一些操作，就以它為例舉
  語法為：
  **EXEC xp_cmdshell DOS命令 [NO_OUTPUT]**
  一般 xp_cmdshell 作為服務器安全配置的一部分被關閉，應使用如下語句啟用：

  exec sp_configure  'show advanced options', 1   --顯示高級配置選項（單引號中的只能一個空格隔開）
  go 
  reconfigure                                    --重新配置
  go 
  exec sp_configure  'xp_cmdshell',1                 --打開xp_cmdshell選項
  go
  reconfigure                                    --重新配置

  啟用之后執行如下語句：

  exec  xp_cmdshell  'mkdir  c:\bank',no_output  --創建文件夾c:\bank
  exec  xp_cmdshell  'dir c:\bank\'               --查看文件 

  用戶自定義的存儲過程：
  一個完整的存儲過程包括

• 輸入參數和輸出參數
• 在存儲過程中執行的T-SQL語句
• 存儲過程的返回值
  用SSMS創建存儲過程
  一個完整的存儲過程包括以下三部分：
  1、輸入和輸出參數
  2、在存儲過程中執行的 T-SQL 語句
  3、存儲過程的返回值
  使用 T-SQL 語句創建存儲過程的語法為：

  CREATE  PROC[EDURE]  存儲過程名 
      [ {@參數1  數據類型 } [= 默認值] [OUTPUT],
         ……,
        {@參數n  數據類型 } [= 默認值] [OUTPUT]
      ]
  AS
      SQL語句

  刪除存儲過程的語法為：
  DROP PROC[EDURE] 存儲過程名
  舉個例子，實現查詢該課程最近一次考試的平均分：

  use schoolDB
  go
  if exists  (select  *  from  sysobjects where name='usp_getaverageresult')
  drop  procedure  usp_getaverageresult
  go
  create  procedure  usp_getaverageresult
  as
  declare  @subjectid  nvarchar(4)
  select  @subjectid=subjectid  from  dbo.TSubject where subJectName='網絡管理'
  declare  @avg decimal (18,2)
  select  @avg=AVG(mark)  from  dbo.TScore where subJectID=@subjectid
  print  '網絡管理專業平均分是:'+convert(varchar(5),@avg)
  go

  編寫完畢之后執行：exec usp_getaverageresult
  觸發器：
  是在對表進行增、改或刪操作時自動執行的存儲過程
  用于強制業務規則，可以定義比用 CHECK 約束更為復雜的約束
  通過事件觸發而被執行的
  觸發器分為三類：
  INSERT觸發器：當向表中插入數據時觸發
  UPDATE觸發器：當更新表中某列、多列時觸發
  DELETE觸發器：當刪除表中記錄時觸發
  inserted表和deleted表
  由系統管理，存儲在內存而不是數據庫中，因此，不允許用戶直接對其修改
  臨時存放對表中數據行的修改信息
  當觸發器工作完成，它們也被刪除
  

觸發器的作用就是：強化約束、跟蹤變化、級聯運行
創建觸發器的語法為：

create trigger *triggername(觸發器名)*
on *tablename(表名)*
[with encryption]
for {[delete，insert，update]}
as SQL 語句

例：創建觸發器，禁止修改admin表中的數據

create  trigger  reminder
on  admin
for  update
as
print  '禁止修改，請聯系DBA'
rollback  transaction
go

然后執行語句查看錯誤信息：

update Admin set  LoginPwd='123'  where  LoginId='benet'
select  *  from  Admin

事務（一般用在銀行交易這一方面，如轉賬）
是一個不可分割的工作邏輯單元
一組命令，要么都執行，要么都不執行
事務作為單個邏輯工作單元執行的一系列操作，一個邏輯單元必須具備四個屬性：原子性、一致性、隔離性、持久性，這些特性通常簡稱為ACID。
舉個例子，以轉賬為準
首先創建表名為bank：

為 Currentmoney列的Check約束：

插入兩條數據：

INSERT INTO bank(customerName,currentMoney) VALUES('張三',1000)
INSERT INTO bank(customerName,currentMoney) VALUES('李四',1)

然后輸入代碼事務執行：

select customername,currentmoney as 轉帳事務前的余額 from bank    --查看轉賬事務前的余額
go
begin transaction     -- 開始事務（指定事務從此開始，后續的T-SQL語句是一個整體）
declare @errorsum int   --定義變量，用于累計事務執行過程中的錯誤 
set @errorsum=0    --初始化為0，即無錯誤
update bank set currentmoney=currentmoney-1000  --轉賬，張三賬戶少1000 李四賬戶多1000 
where customername='張三'
set @errorsum=@errorsum+@@ERROR    --累計是否有錯誤
update bank set currentmoney=currentmoney+1000
where customername='李四'
set @errorsum=@errorsum+@@ERROR   --累計是否有錯誤
select customername,currentmoney as 轉帳事務過程中的余額 from bank    --查看那轉賬過程中的余額
if @errorsum<>0  --如果有錯誤
begin 
print '交易失敗，回滾事務'
rollback transaction 
end
else 
begin 
print '交易成功，提交事務，寫入硬盤，永久的保存'
commit transaction 
end
go
select customername,currentmoney as 轉帳事務后的余額 from bank   --查看轉賬后的余額

轉賬失敗：

轉賬成功：

鎖：
多用戶能夠同時操縱同一個數據庫中的數據，會發生數據不一致的現象，鎖就是能夠在多用戶環境下保證數據的完整性和一致性
鎖的三種模式：
共享鎖（S鎖）：用于讀取資源所加的鎖。
排他鎖（X鎖）：和其他鎖不兼容，包括其他排他鎖。
更新鎖（U鎖）：U鎖可以看做S鎖和X鎖的結合，用于更新數據。
查看鎖：
使用sys.dm_tran_locks動態管理視圖
使用Profiler來捕捉鎖信息
死鎖
死鎖的本質是一種僵持狀態，是由多個主體對資源的爭用而導致的。
形成死鎖的條件是：
1、互斥條件：主體對資源是獨占的
2、請求與等待條件
3、不剝奪條件
4、環路等待條件
預防死鎖：
破壞互斥條件
破壞請求與等待條件
破壞不剝奪條件