聽說讀寫

本文只是一個入門級別的數據庫案例。

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

數據庫案例(一）簡單的用戶表。

業務假設：

用戶表，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()獲取對象名)