SQL筆記:WHERE 1=1會拖累效能嗎?
0 |
稱不上DB咖的我,反常地連寫兩篇SQL筆記,其實都是研究「動態產生SQL查詢條件」議題的副產品,這篇才算步入正題,鴨架子湯先來兩碗,烤鴨才上桌,哈!但這樣安排是對的,以下探討有一部分需要先前筆記的基礎才好聊下去。
兩週前,參加一場Code Review討論,會中大家剛好聊到「動態產生SQL查詢條件」這檔事兒。它的情境是:使用者在操作介面上有多項條件選擇,例如:日期、類別、關鍵字,每個條件使用者可選擇輸入或不輸入(不輸入代表不限定)。從程式的角度,使用者依輸入條件不同,可能形成以下幾種SQL查詢條件:
- 全部都不填
不需WHERE條件,查詢全部資料 - 填日期
WHERE Date = @date - 填類別
WHERE Catg = @catg - 填關鍵字
WHERE Subject LIKE '%' + @keywd + '%' - 填日期+填類別
WHERE Date = @date AND Catg = @catg - 填日期+填關鍵字
WHERE Date = @date AND Subject LIKE '%' + @keywd + '%' - 填類別+填關鍵字
WHERE Catg = @catg AND Subject LIKE '%' + @keywd + '%' - 填日期+填類別+填關鍵字
WHERE Date = @date AND Catg = @catg AND Subject LIKE '%' + @keywd + '%'
為了應付這種情境,老鳥們應該都看過一種寫法:
static void DynaWhereSample(DateTime? date, string catg, string keywd)
{
SqlCommand cmd = new SqlCommand();
string sql = "SELECT * FROM MyTable WHERE 1=1 ";
if (date != null)
{
sql += "AND Date = @date";
cmd.Parameters.Add("@date", SqlDbType.DateTime).Value = date.Value;
}
if (!string.IsNullOrEmpty(catg))
{
//嚴正提醒:catg等參數來自使用者輸入,請使用SqlParameter,禁止直接串接
//錯誤示範 => sql += "AND Catg='" + catg + "'"
//以上寫法隱藏SQL Injection風險,招致難以想像的災難(嚴重者可至家破人亡)
//開發者犯此錯誤,依江湖聯合幫規第127條,處唯一死刑-阿魯巴到死!
sql += "AND Catg = @catg";
cmd.Parameters.Add("@catg", SqlDbType.VarChar).Value = catg;
}
if (!string.IsNullOrEmpty(keywd))
{
sql += "AND Subject LIKE '%' + @keywd + '%'";
cmd.Parameters.Add("@keywd", SqlDbType.NVarChar).Value = keywd;
}
cmd.CommandText = sql;
//執行SQL,以下省略
}
程式依date, catg, keywd是否有值動態組裝出不同的WHERE條件組合(再次提醒,請使用SqlParameter,切忌直接內容直接串成SQL指令,否則…),若使用者什麼都沒填,SQL查詢指令會變成SELECT * FROM MyTable WHERE 1=1,效果與SELEC * FROM MyTable相同。
「WHERE 1=1」差不多已經是這種寫法的代名詞吧?我發現只要說出「WHERE 1=1再動態接條件」,許多老鳥都會點頭會心一笑,足見此招在江湖流傳之廣。(不信大家可以用Google查「WHERE 1=1」 XD)
提到WHERE 1=1,不意外地就有人起疑「多加這種條件會不會影響效能?會不會該查詢變成Table Scan(Index Scan)?」
依我的認知,市面上的主流資料庫早已歷經千鎚百鍊,不致呆頭呆腦被1=1騙,編譯SQL最佳化時自有能力排除這類永遠成立或永遠不成立的無意義條件。過去,對於這點主張只能想當然爾,在搞懂Index Scan與Index Seek的意義及發生時機後,現在我們能靠檢查執行計劃驗證:
如上圖所示,加上WHRE 1=1,仍然走Index Seek,證明SQL未受1=1混淆,仍選擇適合的執行計劃。
等等,難道動態組條件一定非得WHERE 1=1?
寫WHERE 1=1的好處是邏輯簡單,只要無腦地有給值就串字串AND Col…,即使完全不限條件也能靠WHERE 1=1全選。若不這麼做,得判斷第一個條件加WHERE Col=…,第二個以後的條件加AND Col=…,全無條件時連WHERE都不要加,邏輯當真複雜不少,這也難怪WHERE 1=1這招能風行一時。不過,都到了LINQ時代,出現好多新武器,用短短幾行程式碼就能寫出沒有贅肉的動態WHERE條件組裝邏輯:
static void DynaWhereSample(DateTime? date, string catg, string keywd)
{
SqlCommand cmd = new SqlCommand();
List<string> cond = new List<string>();
if (date != null)
{
cond.Add("Date = @date");
cmd.Parameters.Add("@date", SqlDbType.DateTime).Value = date.Value;
}
if (!string.IsNullOrEmpty(catg))
{
cond.Add("Catg = @catg");
cmd.Parameters.Add("@catg", SqlDbType.VarChar).Value = catg;
}
if (!string.IsNullOrEmpty(keywd))
{
cond.Add("Subject LIKE '%' + @keywd + '%'");
cmd.Parameters.Add("@keywd", SqlDbType.NVarChar).Value = keywd;
}
cmd.CommandText = string.Format(
"SELECT * FROM MyTable{0}{1}",
cond.Count > 0 ? " WHERE " : "",
string.Join(" AND ", cond.ToArray()));
//執行SQL,以下省略
Console.WriteLine(cmd.CommandText);
}
幾行簡潔程式碼就能組出有馬甲線的精實SQL指令,何苦再用怪招?大家就忘了WHERE 1=1吧!:P
Comments
Be the first to post a comment