目前，越來越多人選擇將系統建置在雲端的環境上，已取得較佳的水平擴展來應付高流量的情境。然而，在雲端的環境下AP Server很容易達成水平擴展，但是在資料庫的部分，卻還是還難實現水平擴展。其原因有，資料同步的即時性以及資料庫資源的昂貴，這兩項限制，讓我們不得不小心地使用資料庫資源。但是，在目前廣泛使用ORM Framework的情況下，工程師很容易不注意踩到ORM的效能地雷，其中一項就是著名的 "Select N+1"。這是一項既普遍又嚴重的地雷，當 "Select N+1"發生的時候，就等同於系統在DDOS系統自己的資料庫，造成效能不彰即費用增加。那這麼嚴重的問題該如何預防呢?很簡單，寫個測試來預防 "Select N+1"不就好了嗎。
如果要對系統撰寫 "Select N+1"測試，那麼唯一的方法就是進行整合測試。透過測試時，實際對資料庫進行資料查詢，並且記錄系統對資料庫送出的查詢次數，來比對查詢實際送出的次數與預期查詢送出的次數使否相等，來達到預防 "Select N+1"的發生，底下將說明該如何對 "Select N+1"進行測試。
產生DbContext
實際在資料庫中建立兩個資料別，分別為:
- Product
- ProductSKU
Product記錄著商品的ID、名稱與價錢，ProductSKU則記錄了每項商品各尺寸的庫存量。再利用 Code generator產生 DbContext與各資料表的類別，產生出來的類別，兩個資料類別為
Product.cs
public class Product
{
public long ProductId { get; set; } // Product_Id (Primary key)
public string ProductName { get; set; } // Product_Name
public decimal ProductPrice { get; set; } // Product_Price
// Reverse navigation
public virtual System.Collections.Generic.ICollection<ProductSku> ProductSkus { get; set; } // ProductSKU.FK__ProductSK__Produ__145C0A3F
public Product()
{
ProductSkus = new System.Collections.Generic.List<ProductSku>();
}
}
ProductSKU.cs
public class ProductSku
{
public long ProductSkuId { get; set; } // ProductSKU_Id (Primary key)
public long ProductSkuProductId { get; set; } // ProductSKU_ProductId
public string ProductSkuSize { get; set; } // ProductSKU_Size
public decimal ProductSkuStock { get; set; } // ProductSKU_Stock
// Foreign keys
public virtual Product Product { get; set; } // FK__ProductSK__Produ__145C0A3F
}
Select N+1
在程式碼中，Product與 ProductSKU存在彼此的 Navigation property，在這個結構下就很容易誤踩 "Select N+1"的雷，來看看以下的實際案例。在系統中會有一個 Product data access object為 ProductRepository，並且在這個類別中提供一個功能為查詢低於某個價錢的所有商品。此外，在實務上很常會將資料表的對應類別轉換為系統所使用的 DTO類別。所以，在上述的程式碼中也會將取得的商品集合 Product轉換為另一個 DTO類別 ProductEntity。
/// <summary>
/// Gets the products by less price.
/// </summary>
/// <param name="price">The price.</param>
/// <returns></returns>
public List<ProductEntity> GetProductsByLessPrice(long price)
{
var productEntitys = new List<ProductEntity>();
var products = this._dbContext.Products.Where(p => p.ProductPrice < 500).ToList();
foreach (var product in products)
{
var productEntity = new ProductEntity()
{
Id = product.ProductId,
Name = product.ProductName,
Price = Convert.ToInt64(product.ProductPrice),
};
foreach (var productSku in product.ProductSkus)
{
productEntity.Stocks.Add(productSku);
}
productEntitys.Add(productEntity);
}
return productEntitys;
}
ProductEntity.cs
public class ProductEntity
{
public ProductEntity()
{
this.Stocks = new List<ProductSku>();
}
/// <summary>
/// 商品Id
/// </summary>
public long Id { get; set; }
/// <summary>
/// 商品售價
/// </summary>
public long Price { get; set; }
/// <summary>
/// 商品名稱
/// </summary>
public string Name { get; set; }
/// <summary>
/// 商品個尺寸的庫存數量
/// </summary>
public List<ProductSku> Stocks { get; set; }
}
接著，來看看剛剛所寫的功能，實際上要查詢商品售價小於500的商品，並且將 Product轉換為 ProductEntity的情況下，會送出幾次資料庫查詢。預期應該是只對資料庫送出一次查詢，取得結果後在系統中進行類別的轉換。但是，實際結果卻是
可以看到在左邊的程式碼中只對資料庫進行了一次的查詢，但是在右邊 SQL Server Profiler中卻錄到了三次查詢，這也就是著名的 "Select N+1"。而造成的原因為，當對資料庫進行商品查詢後取得的資料其實並不包含 ProductSKU，因為在Product的類別中，ProductSKU為導覽屬性，在一般的情況下進行 Product查詢，並不會一起取得 ProductSKU的資料，從 SQL Server Profiler中看到也是如此。
當取得查詢結果後，在程式碼中要將 Product轉換為 ProductEnity時，又需要使用到 ProductSKU，所以系統又自動的對資料庫發出查詢，已取得 ProductSKU資料，這也就是後面兩個查詢發生的原因。那該如何避免這樣的情況呢，很簡單，只需要在查詢 Product的時候指名將 ProductSKU資料一起取回即可。
Select N+1測試
在說明完 "Select N+1"是如何發生的和該如何避免後，再來談談如何對 "Select N+1"進行測試呢?接下來將以上面的例子來說明如何撰寫整合測試。
首先，先利用 Specflow將測試案例寫出
接著，因為是進行整合測試，所以需要產生 LocalDB，並且在產生 LocalDB前，先進行一次刪除 LocalDB以確保資料庫的乾淨度。
/// <summary>
/// Initializes the database.
/// </summary>
private void InitializeDB()
{
this._dbContext = new StoreDBContext(@"Data Source=(LocalDb)\v11.0;Initial Catalog=StoreDB;Integrated Security=True");
this._dbContext.Database.Delete();
this._dbContext.Database.CreateIfNotExists();
}
將整合測試使用的資料新增到剛剛新增的資料庫中
private void InitializeData()
{
var helloKittyTShirt = new Product()
{
ProductName = "hello kitty t-shirt",
ProductPrice = 299,
};
var helloKittySKUs = new List<ProductSku>()
{
new ProductSku()
{
ProductSkuProductId = 1,
ProductSkuSize = "S",
ProductSkuStock = 200
},
new ProductSku()
{
ProductSkuProductId = 1,
ProductSkuSize = "M",
ProductSkuStock = 300
},
new ProductSku()
{
ProductSkuProductId = 1,
ProductSkuSize = "L",
ProductSkuStock = 150
},
new ProductSku()
{
ProductSkuProductId = 1,
ProductSkuSize = "XL",
ProductSkuStock = 250
},
new ProductSku()
{
ProductSkuProductId = 1,
ProductSkuSize = "XXL",
ProductSkuStock = 100
}
};
//// 巧虎上衣
var ericTShirt = new Product()
{
ProductName = "eric t-shirt",
ProductPrice = 249,
};
var ericTShirtSKUs = new List<ProductSku>()
.......程式碼過長省略
helloKittyTShirt.ProductSkus = helloKittySKUs;
ericTShirt.ProductSkus = ericTShirtSKUs;
this._dbContext.Products.Add(helloKittyTShirt);
this._dbContext.Products.Add(ericTShirt);
this._dbContext.SaveChanges();
}
在進行主要查詢前先幫Entity Framework熱機一下
this._dbContext.Products.Where(p => p.ProductId == 1).ToList();
再來建立一個繼承 IDbCommandInterceptor的類別，並且在類別中宣告一個查詢計數器，在系統每送出一次查詢就時，。就將計數器加1，並且將該類別註冊到 DbInterception。
public class SelectPerformanceTestInterceptor : IDbCommandInterceptor
{
public int SendQueryCount { get; set; }
public SelectPerformanceTestInterceptor()
{
this.SendQueryCount = 0;
}
public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
this.SendQueryCount++;
}
public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
}
public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
}
public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
}
public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
}
public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
}
}
註冊 IDbCommandInterceptor到 DbInterception
this._dbContextInterceptor = new SelectPerformanceTestInterceptor();
DbInterception.Add(this._dbContextInterceptor);
建立測試對象，並且呼叫查詢方法
var target = new ProductRepository(this._dbContext);
var result = target.GetProductsByLessPrice(500);
從 SelectPerformanceTestInterceptor中取得送出查詢的次數，並且與預期次數比對是否相同。
var actualQueryCount = this._dbContextInterceptor.SendQueryCount;
Assert.AreEqual(expectedQueryCount, actualQueryCount);
接著，實際執行測試
查詢語法有使用 "Include("ProductSkus")"，實際送出的查詢為一次，成功通過測試，沒有 "Select N+1"問題
查詢語法沒有使用 "Include("ProductSkus")"，實際送出的查詢為三次，沒有通過測試，有 "Select N+1"問題
成功的對 "Select N+1"完成測試。
小結
透過上述的方式，扣除產生資料庫與產生測試資料的程式碼，實際為了 "Select N+1"測試而撰寫的程式碼大概只有四行，就算為了整個系統的 DAO進行測試，也不會需要寫很多的程式碼，所以筆者覺得這一是項CP值非常高的投資。透過簡單的測試能夠預防不小心踩到的效能地雷，或是，防止後人修改已經在線上運行的功能不小心寫出了效能地雷而沒有人發現。
效能地雷很多人或許知道，但是總會有不小心的時候，而測試就是在預防這個不小心，並且防止不小心發生了而沒有人知道。
參考資料
免責聲明:
"文章一定有好壞，文章內容有對有錯，使用前應詳閱公開說明書"
