淺析Sql Server參數化查詢

說來慚愧，工作差不多4年了，直到前些日子被DBA找上門讓我優化一個CPU占用很高的複雜SQL語句時，我才突然意識到了參數化查詢的重要性。

相信有很多開發者和我一樣對于參數化查詢認識比較模糊，沒有引起足夠的重視

錯誤認識1.不需要防止sql注入的地方無需參數化

　　參數化查詢就是為了防止SQL注入用的，其它還有什麼用途不知道、也不關心，原則上是能不用參數就不用參數，為啥？多麻煩，我隻是做公司内部系統不用擔心SQL注入風險，使用參數化查詢不是給自己找麻煩，簡簡單單拼SQL，萬事OK

錯誤認識2.參數化查詢時是否指定參數類型、參數長度沒什麼差別

　　以前也一直都覺的加與不加參數長度應該沒有什麼差別，僅是寫法上的不同而已，而且覺得加參數類型和長度寫法太麻煩，最近才明白其實兩者不一樣的，為了提高sql執行速度，請為SqlParameter參數加上SqlDbType和size屬性，在參數化查詢代碼編寫過程中很多開發者忽略了指定查詢參數的類型，這将導緻托管代碼在執行過程中不能自動識别參數類型，進而對該字段内容進行全表掃描以确定參數類型并進行轉換，消耗了不必要的查詢性能所緻。根據MSDN解釋：如果未在size參數中顯式設定Size，則從dbType參數的值推斷出該大小。如果你認為上面的推斷出該大小是指從SqlDbType類型推斷，那你就錯了，它實際上是從你傳過來的參數的值來推斷的，比如傳遞過來的值是"username"，則size值為8，"username1"，則size值為9。那麼，不同的size值會引發什麼樣的結果呢？且經測試發現，size的值不同時，會導緻資料庫的執行計劃不會重用，這樣就會每次執行sql的時候重新生成新的執行計劃，而浪費資料庫執行時間。

下面來看具體測試

首先清空查詢計劃

DBCC FREEPROCCACHE      

傳值username，不指定參數長度，生成查詢計劃

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    SqlCommand comm = new SqlCommand();
    comm.Connection = conn;
    comm.CommandText = "select * from Users where UserName=@UserName";
    //傳值 username，不指定參數長度
    //查詢計劃為(@UserName varchar(8))select * from Users where UserName=@UserName
    comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar) { Value = "username" });
    comm.ExecuteNonQuery();
}      

傳值username1，不指定參數長度，生成查詢計劃

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    SqlCommand comm = new SqlCommand();
    comm.Connection = conn;
    comm.CommandText = "select * from Users where UserName=@UserName";
    //傳值 username1，不指定參數長度
    //查詢計劃為(@UserName varchar(9))select * from Users where UserName=@UserName
    comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar) { Value = "username1" });
    comm.ExecuteNonQuery();
}      

傳值username，指定參數長度為50，生成查詢計劃

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    SqlCommand comm = new SqlCommand();
    comm.Connection = conn;
    comm.CommandText = "select * from Users where UserName=@UserName";
    //傳值 username，指定參數長度為50
    //查詢計劃為(@UserName varchar(50))select * from Users where UserName=@UserName
    comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar,50) { Value = "username" });
    comm.ExecuteNonQuery();
}      

傳值username1，指定參數長度為50，生成查詢計劃

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    SqlCommand comm = new SqlCommand();
    comm.Connection = conn;
    comm.CommandText = "select * from Users where UserName=@UserName";
    //傳值 username1，指定參數長度為50
    //查詢計劃為(@UserName varchar(50))select * from Users where UserName=@UserName
    comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar,50) { Value = "username1" });
    comm.ExecuteNonQuery();
}      

使用下面語句檢視執行的查詢計劃

SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects 
WHERE sql LIKE '%Users%'  and sql not like '%syscacheobjects%'      

結果如下圖所示

 可以看到指定了參數長度的查詢可以複用查詢計劃，而不指定參數長度的查詢會根據具體傳值而改變查詢計劃，進而造成性能的損失。

這裡的指定參數長度僅指可變長資料類型，主要指varchar，nvarchar,char,nchar等，對于int,bigint,decimal,datetime等定長的值類型來說，無需指定(即便指定了也沒有用)，詳見下面測試,UserID為int類型，無論長度指定為2、20、-1查詢計劃都完全一樣為(@UserIDint)select*from Users whereUserID=@UserID

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    SqlCommand comm = new SqlCommand();
    comm.Connection = conn;
    comm.CommandText = "select * from Users where UserID=@UserID";
    //傳值 2，參數長度2
    //執行計劃(@UserID int)select * from Users where UserID=@UserID
    comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.Int, 2) { Value = 2 });
    comm.ExecuteNonQuery();
}
using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    SqlCommand comm = new SqlCommand();
    comm.Connection = conn;
    comm.CommandText = "select * from Users where UserID=@UserID";
    //傳值 2，參數長度20
    //執行計劃(@UserID int)select * from Users where UserID=@UserID
    comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.Int, 20) { Value = 2 });
    comm.ExecuteNonQuery();
}
using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    SqlCommand comm = new SqlCommand();
    comm.Connection = conn;
    comm.CommandText = "select * from Users where UserID=@UserID";
    //傳值 2，參數長度-1
    //執行計劃(@UserID int)select * from Users where UserID=@UserID
    comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.Int, -1) { Value = 2 });
    comm.ExecuteNonQuery();
}      

這裡提一下，若要傳值varchar(max)或nvarchar(max)類型怎麼傳，其實隻要設定長度為-1即可

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    SqlCommand comm = new SqlCommand();
    comm.Connection = conn;
    comm.CommandText = "select * from Users where UserName=@UserName";
    //類型為varchar(max)時，指定參數長度為-1
    //查詢計劃為 (@UserName varchar(max) )select * from Users where UserName=@UserName
    comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar,-1) { Value = "username1" });
    comm.ExecuteNonQuery();
}      

當然了若是不使用參數化查詢，直接拼接SQL，那樣就更沒有查詢計劃複用一說了，除非你每次拼的SQL都完全一樣

總結，參數化查詢意義及注意點

1.可以防止SQL注入

2.可以提高查詢性能(主要是可以複用查詢計劃)，這點在資料量較大時尤為重要