.net 中的SqlConnection連接池機制詳解

正確的理解這個連接池機制，有助於我們編寫高效的數據庫應用程序。

很多人認為 SqlConnection 的連接是不耗時的，理由是循環執行 SqlConnection.Open 得到的平均時間幾乎為0，但每次首次open 時，耗時又往往達到幾個毫秒到幾秒不等，這又是為什麼呢？

首先我們看一下 MSDN 上的權威文檔上是怎麼說的

Connecting to a database server typically consists of several time-consuming steps. A physical channel such as a socket or a named pipe must be established, the initial handshake with the server must occur, the connection string information must be parsed, the connection must be authenticated by the server, checks must be run for enlisting in the current transaction, and so on.

以上摘自 http://msdn.microsoft.com/en-us/library/8xx3tyca%28VS.80%29.aspx

也就是說物理連接建立時，需要做和服務器握手，解析連接字符串，授權，約束的檢查等等操作，而物理連接建立後，這些操作就不會去做了。這些操作是需要一定的時間的。所以很多人喜歡用一個靜態對象存儲 SqlConnection 來始終保持物理連接，但采用靜態對象時，多線程訪問會帶來一些問題，實際上，我們完全不需要這麼做，因為 SqlConnection 默認打開了連接池功能，當程序 執行  SqlConnection.Close 後，物理連接並不會被立即釋放，所以這才出現當循環執行 Open操作時，執行時間幾乎為0.

下面我們先看一下不打開連接池時，循環執行 SqlConnection.Open 的耗時

復制代碼 代碼如下:
        public static void OpenWithoutPooling()       
 {           
 string connectionString =  "Data Source=192.168.10.2; Initial Catalog=News; Integrated Security=True;Pooling=False;";
 Stopwatch sw = new Stopwatch();          
 sw.Start();           
 using (SqlConnection conn =  new SqlConnection(connectionString))           
 {               
 conn.Open();           
 }           
 sw.Stop();           
 Console.WriteLine("Without Pooling, first connection elapsed {0} ms", sw.ElapsedMilliseconds); 

 sw.Reset();         
 sw.Start();         
 for (int i = 0; i < 100; i++)    
 {              
 using (SqlConnection conn = new SqlConnection(connectionString))  
 {                 
 conn.Open();            
 }         
 }      
 sw.Stop();        
 Console.WriteLine("Without Pooling, average connection elapsed {0} ms", sw.ElapsedMilliseconds / 100); 
 }

復制代碼 代碼如下:
.csharpcode { BACKGROUND-COLOR: #ffffff; FONT-FAMILY: consolas, "Courier New", courier, monospace; COLOR: black; FONT-SIZE: small }
.csharpcode PRE { BACKGROUND-COLOR: #ffffff; FONT-FAMILY: consolas, "Courier New", courier, monospace; COLOR: black; FONT-SIZE: small }
.csharpcode PRE { MARGIN: 0em }
.csharpcode .rem { COLOR: #008000 }
.csharpcode .kwrd { COLOR: #0000ff }
.csharpcode .str { COLOR: #006080 }
.csharpcode .op { COLOR: #0000c0 }
.csharpcode.preproc { COLOR: #cc6633 }
.csharpcode .asp { BACKGROUND-COLOR: #ffff00 }
.csharpcode .html { COLOR: #800000 }
.csharpcode .attr { COLOR: #ff0000 }
.csharpcode .alt { BACKGROUND-COLOR: #f4f4f4; MARGIN: 0em; WIDTH: 100% }
.csharpcode .lnum { COLOR: #606060 }

SqlConnection 默認是打開連接池的，如果要強制關閉，我們需要在連接字符串中加入 Pooling=False

調用程序如下：
復制代碼 代碼如下:
                Test.SqlConnectionTest.OpenWithoutPooling(); 
  Console.WriteLine("Waiting for 10s");         
  System.Threading.Thread.Sleep(10 * 1000);      
  Test.SqlConnectionTest.OpenWithoutPooling();       
  Console.WriteLine("Waiting for 600s");           
  System.Threading.Thread.Sleep(600 * 1000);            
  Test.SqlConnectionTest.OpenWithoutPooling();

下面是測試結果

復制代碼 代碼如下:
Without Pooling, first connection elapsed 13 ms
Without Pooling, average connection elapsed 5 ms
Wating for 10s
Without Pooling, first connection elapsed 6 ms
Without Pooling, average connection elapsed 4 ms
Wating for 600s
Without Pooling, first connection elapsed 7 ms
Without Pooling, average connection elapsed 4 ms

從這個測試結果看，關閉連接池後，平均每次連接大概要耗時4個毫秒左右，這個就是建立物理連接的平均耗時。

下面再看默認情況下的測試代碼

復制代碼 代碼如下:
        public static void OpenWithPooling() 
 {        
 string connectionString =  "Data Source=192.168.10.2; Initial Catalog=News; Integrated Security=True;";
 Stopwatch sw = new Stopwatch();   
 sw.Start();       
 using (SqlConnection conn =  new SqlConnection(connectionString))    
 {             
 conn.Open();   
 }        
 sw.Stop();      
 Console.WriteLine("With Pooling, first connection elapsed {0} ms", sw.ElapsedMilliseconds);
 sw.Reset();   
 sw.Start();    
 for (int i = 0; i < 100; i++)   
 {            
 using (SqlConnection conn = new SqlConnection(connectionString))   
 {                 
 conn.Open();        
 }          
 }        
 sw.Stop();       
 Console.WriteLine("With Pooling, average connection elapsed {0} ms", sw.ElapsedMilliseconds / 100); 
 }

調用代碼

復制代碼 代碼如下:
                Test.SqlConnectionTest.OpenWithPooling();    
  Console.WriteLine("Waiting for 10s");        
  System.Threading.Thread.Sleep(10 * 1000);      
  Test.SqlConnectionTest.OpenWithPooling();      
  Console.WriteLine("Waiting for 600s");    
  System.Threading.Thread.Sleep(600 * 1000); 
  Test.SqlConnectionTest.OpenWithPooling();

測試結果

With Pooling, first connection elapsed 119 ms
With Pooling, average connection elapsed 0 ms
Waiting for 10s
With Pooling, first connection elapsed 0 ms
With Pooling, average connection elapsed 0 ms
Waiting for 600s
With Pooling, first connection elapsed 6 ms
With Pooling, average connection elapsed 0 ms

這個測試結果看，第一次耗時是119ms，這是因為我在測試代碼中，首先運行的是這個測試過程，119 ms 是程序第一次啟動時的首次連接耗時，這個耗時可能不光包括連接數據庫的時間，還有 ado.net 自己初始化的用時，所以這個用時可以不管。10秒以後在執行這個測試過程，首次執行的時間變成了0ms，這說明連接池機制發生了作用，SqlConnection Close 後，物理連接並沒有被關閉，所以10秒後再執行，連接幾乎沒有用時間。

但我們發現一個有趣的現象，10分鐘後，首次連接時間變成了6ms，這個和前面不打開連接池的測試用時幾乎一樣，也就是說10分鐘後，物理連接被關閉了，又重新打開了一個物理連接。這個現象是因為連接池有個超時時間，默認情況下應該在5-10分鐘之間，如果在此期間沒有任何的連接操作，物理連接就會被關閉。那麼我們有沒有辦法始終保持物理連接呢？方法是有的。

連接池設置中有一個最小連接池大小，默認為0，我們把它設置為大於0的值就可以保持若干物理連接始終不釋放了。看代碼

復制代碼 代碼如下:
       public static void OpenWithPooling(int minPoolSize)
       {        
       string connectionString = string.Format("Data Source=192.168.10.2; Initial Catalog=News; Integrated Security=True;Min Pool Size={0}",minPoolSize); 
       Stopwatch sw = new Stopwatch();      
       sw.Start();     
       using (SqlConnection conn =   new SqlConnection(connectionString)) 
       {              
       conn.Open();    
       }       
       sw.Stop();   
       Console.WriteLine("With Pooling Min Pool Size={0}, first connection elapsed {1} ms",minPoolSize, sw.ElapsedMilliseconds);
       sw.Reset();         
       sw.Start();      
       for (int i = 0; i < 100; i++)   
       {               
       using (SqlConnection conn = new SqlConnection(connectionString))   
       {                  
 conn.Open();       
 }    
 }      
 sw.Stop();    
 Console.WriteLine("With Pooling Min Pool Size={0}, average connection elapsed {1} ms",minPoolSize, sw.ElapsedMilliseconds / 100); 
 }

其實只要在連接字符串中加入一個 Min Pool Size=n 就可以了。

調用代碼

復制代碼 代碼如下:
               Test.SqlConnectionTest.OpenWithPooling(1);    
        Console.WriteLine("Waiting for 10s");     
        System.Threading.Thread.Sleep(10 * 1000); 
        Test.SqlConnectionTest.OpenWithPooling(1);       
        Console.WriteLine("Waiting for 600s");            
        System.Threading.Thread.Sleep(600 * 1000);     
        Test.SqlConnectionTest.OpenWithPooling(1);

With Pooling Min Pool Size=1, first connection elapsed 5 ms
With Pooling Min Pool Size=1, average connection elapsed 0 ms
Waiting for 10s
With Pooling Min Pool Size=1, first connection elapsed 0 ms
With Pooling Min Pool Size=1, average connection elapsed 0 ms
Waiting for 600s
With Pooling Min Pool Size=1, first connection elapsed 0 ms
With Pooling Min Pool Size=1, average connection elapsed 0 ms

我們可以看到當 Min Pool Size = 1  時，除了首次連接用時5ms以外，即便過了10分鐘，用時還是0ms，物理連接沒有被關閉。

多線程調用問題
多線程調用我也做了測試，這裡不貼代碼了，我大概講一下結果。如果是多線程訪問 SqlConnection ，注意是通過 new SqlConnection 方式訪問，

那麼這裡有兩個問題，如果後一個線程在前一個線程 Close 前調用了Open操作，那麼 Ado.net 不可能復用一個物理連接，它將為第二個線程分配一個新的物理連接。如果後一個線程 Open  時，前一個線程已經 Close 了，則新的線程使用前一個線程的物理連接。也就是說，如果同時有n個線程連接數據庫，最多情況下會創建n條物理連接，最少情況下為1條。如果創建n條物理連接，則用時理論上等於 n * t / cpu , n 為線程數，t 為每次創建物理連接的用時，前面測試的結果大概是5-10ms左右，cpu 為當前機器的CPU數量。另外網絡，服務器的負荷也影響這個用時。為了保證在大並發時，盡量少的創建新的物理連接，我們可以適當把 Min Pool Size 調大一些，但也不要太大，因為單個機器TCP鏈路的數量是有限的，詳見我另外一篇文章 Windows 下單機最大TCP連接數

連接字符串中關於 連接池方面的參數

見下面鏈接 SqlConnection.ConnectionString Property

IIS 回收應用程序池對連接池的影響
在做 ASP.NET 程序時，我們會發現，如果網站20分鐘不訪問，再次訪問就會比較慢，這是因為IIS默認的 idle timeout 是20分鐘，如果在20分鐘內沒有一個訪問，IIS 將回收應用程序池，回收應用程序池的結果就相當於應用程序被重啟，所有原來的全局變量，session, 物理連接都將清空。回收應用程序池後首次訪問，相當於前面我們看到的程序啟動後第一次訪問數據庫，連接的建立時間將比較長。所以如果網站在某些時段訪問量很少的話，需要考慮 idle timeout 是否設置合理。