淺談 SQL Server 查詢優(yōu)化與事務(wù)處理

之前我們簡(jiǎn)單了解了各種查詢的用法，然而在實(shí)際開發(fā)中還會(huì)用到一些比較高級(jí)的數(shù)據(jù)處理和查詢，包括索引、視圖、存儲(chǔ)過(guò)程和觸發(fā)器。從而能夠更好地實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)的操作、診斷及優(yōu)化。

什么是索引呢，索引是 SQL Server 編排數(shù)據(jù)的內(nèi)部方法，他為 SQL Server 提供了一種方法來(lái)編排查詢數(shù)據(jù)的路由，從而達(dá)到通過(guò)使用索引來(lái)提高數(shù)據(jù)庫(kù)的檢索速度、改善數(shù)據(jù)庫(kù)性能。

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

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')

按照指定的索引進(jìn)行查詢：

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

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

• ORDER BY 子句，除非在 select 語(yǔ)句中的選擇列表中也有一個(gè) TOP 子句
• INTO 關(guān)鍵字
• 引用臨時(shí)表或表變量
  創(chuàng)建視圖：

  create view netstudent
  as
  select Sname,sex,Class from dbo.TStudent where Class='網(wǎng)絡(luò)班'

  從視圖中查找數(shù)據(jù)：

  select * from netstudent 
  where sex='男'

  創(chuàng)建視圖、更改列的表頭：

  create view V_Tstudent1
  as
  select StudentID  學(xué)號(hào),Sname 姓名,sex  性別,cardID  ×××號(hào)碼,Birthday  生日,Class  班級(jí) from dbo.TStudent
  select * from V_Tstudent1

  什么是存儲(chǔ)過(guò)程，存儲(chǔ)過(guò)程就是 SQL 語(yǔ)句和控制語(yǔ)句的預(yù)編譯集合，保存在數(shù)據(jù)庫(kù)里，可由應(yīng)用程序調(diào)用執(zhí)行。
  那為什么需要存儲(chǔ)過(guò)程呢，因?yàn)閺目蛻舳耍╟lient）通過(guò)網(wǎng)絡(luò)向服務(wù)器（server）發(fā)送 SQL 代碼并執(zhí)行是不妥當(dāng)?shù)?，?dǎo)致數(shù)據(jù)可能會(huì)泄露不安全，印象了應(yīng)用程序的運(yùn)行性能，而且網(wǎng)絡(luò)流量大。
  使用存儲(chǔ)過(guò)程的優(yōu)點(diǎn)就是：
  1、模塊化程序設(shè)計(jì)
  2、執(zhí)行速度快、效率高
  3、減少網(wǎng)絡(luò)流量
  4、具有良好的安全性
  存儲(chǔ)過(guò)程分為兩類：系統(tǒng)存儲(chǔ)過(guò)程和用戶自定義的存儲(chǔ)過(guò)程
  系統(tǒng)存儲(chǔ)過(guò)程：
  是一組預(yù)編譯的T-SQL語(yǔ)句，提供了管理數(shù)據(jù)庫(kù)的更新表的機(jī)制，并充當(dāng)從系統(tǒng)表中檢索信息的快捷方式
  以“sp” 開頭，存放在 Resource數(shù)據(jù)庫(kù)中，常用的系統(tǒng)存儲(chǔ)過(guò)程有如下：
  
  使用 T-SQL 語(yǔ)句調(diào)用執(zhí)行存儲(chǔ)過(guò)程的語(yǔ)法：

  EXEC [UTE] 存儲(chǔ)過(guò)程名 [參數(shù)值]
  EXEC為EXECUTE的簡(jiǎn)寫

  常用系統(tǒng)存儲(chǔ)過(guò)程的用法：

  exec  sp_databases      --列出當(dāng)前系統(tǒng)中的數(shù)據(jù)庫(kù)
  exec  sp_renamedb  'mybank','bank'   --改變數(shù)據(jù)庫(kù)名稱(單用戶訪問(wèn))
  use  MySchool
  go 
  exec sp_tables                       --當(dāng)前數(shù)據(jù)庫(kù)中可查詢對(duì)象的列表
  exec sp_columns student            --查看表student中列的信息
  exec  sp_help student               --查看表student的所有信息
  exec sp_helpconstraint student       --查看表student表的約束
  exec sp_helptext view_student_result   --查看視圖的語(yǔ)句文本
  exec sp_stored_procedures      --返回當(dāng)前數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程列表

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

  exec sp_configure  'show advanced options', 1   --顯示高級(jí)配置選項(xiàng)（單引號(hào)中的只能一個(gè)空格隔開）
  go 
  reconfigure                                    --重新配置
  go 
  exec sp_configure  'xp_cmdshell',1                 --打開xp_cmdshell選項(xiàng)
  go
  reconfigure                                    --重新配置

  啟用之后執(zhí)行如下語(yǔ)句：

  exec  xp_cmdshell  'mkdir  c:\bank',no_output  --創(chuàng)建文件夾c:\bank
  exec  xp_cmdshell  'dir c:\bank\'               --查看文件 

  用戶自定義的存儲(chǔ)過(guò)程：
  一個(gè)完整的存儲(chǔ)過(guò)程包括

• 輸入?yún)?shù)和輸出參數(shù)
• 在存儲(chǔ)過(guò)程中執(zhí)行的T-SQL語(yǔ)句
• 存儲(chǔ)過(guò)程的返回值
  用SSMS創(chuàng)建存儲(chǔ)過(guò)程
  一個(gè)完整的存儲(chǔ)過(guò)程包括以下三部分：
  1、輸入和輸出參數(shù)
  2、在存儲(chǔ)過(guò)程中執(zhí)行的 T-SQL 語(yǔ)句
  3、存儲(chǔ)過(guò)程的返回值
  使用 T-SQL 語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法為：

  CREATE  PROC[EDURE]  存儲(chǔ)過(guò)程名 
      [ {@參數(shù)1  數(shù)據(jù)類型 } [= 默認(rèn)值] [OUTPUT],
         ……,
        {@參數(shù)n  數(shù)據(jù)類型 } [= 默認(rèn)值] [OUTPUT]
      ]
  AS
      SQL語(yǔ)句

  刪除存儲(chǔ)過(guò)程的語(yǔ)法為：
  DROP PROC[EDURE] 存儲(chǔ)過(guò)程名
  舉個(gè)例子，實(shí)現(xiàn)查詢?cè)撜n程最近一次考試的平均分：

  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='網(wǎng)絡(luò)管理'
  declare  @avg decimal (18,2)
  select  @avg=AVG(mark)  from  dbo.TScore where subJectID=@subjectid
  print  '網(wǎng)絡(luò)管理專業(yè)平均分是:'+convert(varchar(5),@avg)
  go

  編寫完畢之后執(zhí)行：exec usp_getaverageresult
  觸發(fā)器：
  是在對(duì)表進(jìn)行增、改或刪操作時(shí)自動(dòng)執(zhí)行的存儲(chǔ)過(guò)程
  用于強(qiáng)制業(yè)務(wù)規(guī)則，可以定義比用 CHECK 約束更為復(fù)雜的約束
  通過(guò)事件觸發(fā)而被執(zhí)行的
  觸發(fā)器分為三類：
  INSERT觸發(fā)器：當(dāng)向表中插入數(shù)據(jù)時(shí)觸發(fā)
  UPDATE觸發(fā)器：當(dāng)更新表中某列、多列時(shí)觸發(fā)
  DELETE觸發(fā)器：當(dāng)刪除表中記錄時(shí)觸發(fā)
  inserted表和deleted表
  由系統(tǒng)管理，存儲(chǔ)在內(nèi)存而不是數(shù)據(jù)庫(kù)中，因此，不允許用戶直接對(duì)其修改
  臨時(shí)存放對(duì)表中數(shù)據(jù)行的修改信息
  當(dāng)觸發(fā)器工作完成，它們也被刪除
  

觸發(fā)器的作用就是：強(qiáng)化約束、跟蹤變化、級(jí)聯(lián)運(yùn)行
創(chuàng)建觸發(fā)器的語(yǔ)法為：

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

例：創(chuàng)建觸發(fā)器，禁止修改admin表中的數(shù)據(jù)

create  trigger  reminder
on  admin
for  update
as
print  '禁止修改，請(qǐng)聯(lián)系DBA'
rollback  transaction
go

然后執(zhí)行語(yǔ)句查看錯(cuò)誤信息：

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

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

為 Currentmoney列的Check約束：

插入兩條數(shù)據(jù)：

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

然后輸入代碼事務(wù)執(zhí)行：

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

轉(zhuǎn)賬失?。?br/>
轉(zhuǎn)賬成功：

鎖：
多用戶能夠同時(shí)操縱同一個(gè)數(shù)據(jù)庫(kù)中的數(shù)據(jù)，會(huì)發(fā)生數(shù)據(jù)不一致的現(xiàn)象，鎖就是能夠在多用戶環(huán)境下保證數(shù)據(jù)的完整性和一致性
鎖的三種模式：
共享鎖（S鎖）：用于讀取資源所加的鎖。
排他鎖（X鎖）：和其他鎖不兼容，包括其他排他鎖。
更新鎖（U鎖）：U鎖可以看做S鎖和X鎖的結(jié)合，用于更新數(shù)據(jù)。
查看鎖：
使用sys.dm_tran_locks動(dòng)態(tài)管理視圖
使用Profiler來(lái)捕捉鎖信息
死鎖
死鎖的本質(zhì)是一種僵持狀態(tài)，是由多個(gè)主體對(duì)資源的爭(zhēng)用而導(dǎo)致的。
形成死鎖的條件是：
1、互斥條件：主體對(duì)資源是獨(dú)占的
2、請(qǐng)求與等待條件
3、不剝奪條件
4、環(huán)路等待條件
預(yù)防死鎖：
破壞互斥條件
破壞請(qǐng)求與等待條件
破壞不剝奪條件