【高效能web開發】 SQL Server入門（一）使用者表

本文只是一個入門層級的資料庫案例。

希望能通過一些經典案例的分析，大家能共同討論和分享。

資料庫案例(一）簡單的使用者表。

 

業務假設：

使用者表，10個列，無外鍵， 200萬資料 （如果資料量再大一般就考慮分表了）

以下是假設的操作分布 （僅供參考）

　　50% 按照使用者Id查詢

　　40%按照使用者名稱查詢

　　8%按照Email查詢

　　1.5%修改使用者的資料，例如狀態，最後登入時間

　　0.5%添加使用者資料

 操作特徵：一般都只有單條資料的查詢  

(如果有分析和統計，一般弄一個同步庫出來，在那個單獨的庫上做較大資料量的分析)

(某些操作，例如使用者排名，最近使用者操作等，一般是用其他的方式實現，而不是直接壓在使用者表上) 

(當然，如果資料量要求不大。。。。其實你做什麼都沒關係) 

 

軟硬體環境：

CPU： I5

記憶體：4GB

OS：Windows 7 x64 旗艦版

SqlServer 2008R2 企業版

（不是伺服器環境，有些配置沒有達到最佳化）

 

先建立使用者表，插入200萬+資料  （Id為叢集索引，而且連續分布，經常刪除資料會導致資料不連續降低效能，所以有些時候選擇通過狀態位假刪資料）

CREATE TABLE [dbo].[User](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](255) NOT NULL,
[Password] [varchar](255) NOT NULL,
[Email] [varchar](255) NOT NULL,
[Age] [smallint] NULL,
[Gender] [smallint] NULL,
[Signature] [varchar](255) NULL,
[CreatedTime] [date] NOT NULL,
[LastActivityTime] [date] NOT NULL,
[Status] [int] NULL,
[UserNameCode] [binary](16) NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

由於只有3個列支援條件查詢，而Id列是預設的叢集索引，所以只要建立兩個索引，分別在UserName列和Email列 （一般來說所有可能出現在where語句中的列 都應該建立索引）

總所周知，過多的索引會降低修改效能，而該案例中查詢遠比修改來的多

 

 

第一：按照Id查詢 （叢集索引，Id連續）

Declare @Stopwatch datetime 
declare @number int
Set @Stopwatch=GetDate()
set @number=0
while (@number<1000)
begin
select  * from [user] where id=cast(rand()*2000000 as int)
set @number=@number+1
end

Print DateDiff(ms, @Stopwatch, GetDate());   -- 查詢1000次 輸出結果為 22773 毫秒

第二：按照使用者名稱查詢 （Email是一樣的）

1.無索引，無資料緩衝，（查詢1次 15秒左右）

 DBCC DROPCLEANBUFFERS  --這句清除資料的緩衝
select  * from [user] where username =  'user_'+cast(cast(rand()*2000000 as int) as varchar)  --使用者名稱的規則是 user_id  實在不好找其他的高命中率的例子了 勉強用吧

 

2.無索引 ，有資料緩衝 （1000次隨機查詢，71473毫秒） 

Declare @Stopwatch datetime 
declare @number int
Set @Stopwatch=GetDate()
set @number=0
while (@number<1000)
begin
select  * from [user] where username =  'user_'+cast(cast(rand()*2000000 as int) as varchar)
set @number=@number+1
end

Print DateDiff(ms, @Stopwatch, GetDate());

-- 查詢1000次 輸出結果為71473 毫秒

Sql Server，自動給這個列建立了非叢集索引，看以下 

3.有非叢集索引

在UserName列上建立從立非叢集索引 （整表佔用的索引空間從1M增加到80M，表本身空間還是461M，索引大小和列裡面資料的大小有直接關係）

以下是執行計畫，使用username 查詢和使用id查詢的對比， 可以看使用username的查詢大約比id查詢多消耗一倍的資源，實際情況惡劣的多，因為使用者名稱無規律而且長度還高

有的解決方案是為username產生一個HashCode，雜湊值經過最佳化以後可以實現較小體積 （32/64位） 和均勻分布上等最佳化 (Hash可是號稱0(1)的查詢時間複雜度。。)

 

第三種：修改使用者狀態，密碼，最終登入時間和餘額等

本例中，只有UserName和Email建立了索引，但是這兩個列在邏輯中都考慮為不可修改的

所有可以修改的列都沒有建立索引 （主要是修改的成本太高了）

考慮了修改粒度儘可能小，SQL Server 2005版本和以上支援行鎖

 

第四：添加新使用者資料

要注意的是添加新資料加的是表鎖。。。

 

第五：混合操作

這東西一靠基本功，例如瞭解鎖類型，鎖粒度，隔離等級等概念，這篇文章應該是比較有用處的，http://msdn.microsoft.com/en-us/library/ms190615.aspx

 二靠工具類比和測試，壓力測試工具，效能測試工具，SqlServer Profiler

 

附錄：

1.清除緩衝

DBCC FREEPROCCACHE - plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool. -- This can be used for freeing procedure cahce

DBCC DROPCLEANBUFFERS - Removes all clean buffers from the buffer pool. -- Memory cache

 

2.重建索引

ALTER INDEX ALL ON [User] REBUILD
ALTER INDEX ALL ON [user] REORGANIZE

 

3.SQL Server資料庫產生HashKey的函數

 HashBytes('md5', Username)

 

4.查詢目前的lock: sp_lock   (sp_who, sp_who2也是很有用的，還有object_name()擷取對象名)