忙碌資料庫反模式

發行項
05/31/2024

將處理卸除至資料庫伺服器可能會導致它花費大量時間執行程序代碼，而不是回應儲存和擷取數據的要求。

問題說明

許多資料庫系統都可以執行程序代碼。範例包括預存程式和觸發程式。通常，在接近數據時執行此處理會更有效率，而不是將數據傳輸至用戶端應用程式進行處理。不過，過度使用這些功能可能會造成效能損害，原因有數個：

資料庫伺服器可能會花費太多時間處理，而不是接受新的用戶端要求和擷取數據。
資料庫通常是共用資源，因此在高使用量期間可能會成為瓶頸。
如果數據存放區已計量，運行時間成本可能會過高。這特別適用於受控資料庫服務。例如，Azure SQL 資料庫資料庫交易單位（DTU）的費用。
資料庫具有相應增加的有限容量，而且水準調整資料庫並不簡單。因此，最好將處理移至計算資源，例如 VM 或 App Service 應用程式，以輕鬆相應放大。

此反模式通常會發生，因為：

資料庫會視為服務，而不是存放庫。應用程式可能會使用資料庫伺服器來格式化數據（例如，轉換成 XML）、操作字串數據，或執行複雜的計算。
開發人員嘗試撰寫查詢，其結果可以直接向用戶顯示。例如，查詢可能會根據地區設定來結合字段或格式日期、時間和貨幣。
開發人員正嘗試藉由將計算推送至資料庫，來更正多餘的擷取反模式。
預存程式可用來封裝商業規則，也許是因為它們被認為更容易維護和更新。

下列範例會擷取指定銷售領域的 20 個最有價值的訂單，並將結果格式化為 XML。它會使用 Transact-SQL 函式來剖析數據，並將結果轉換成 XML。您可以在這裡找到完整的範例。

SELECT TOP 20
  soh.[SalesOrderNumber]  AS '@OrderNumber',
  soh.[Status]            AS '@Status',
  soh.[ShipDate]          AS '@ShipDate',
  YEAR(soh.[OrderDate])   AS '@OrderDateYear',
  MONTH(soh.[OrderDate])  AS '@OrderDateMonth',
  soh.[DueDate]           AS '@DueDate',
  FORMAT(ROUND(soh.[SubTotal],2),'C')
                          AS '@SubTotal',
  FORMAT(ROUND(soh.[TaxAmt],2),'C')
                          AS '@TaxAmt',
  FORMAT(ROUND(soh.[TotalDue],2),'C')
                          AS '@TotalDue',
  CASE WHEN soh.[TotalDue] > 5000 THEN 'Y' ELSE 'N' END
                          AS '@ReviewRequired',
  (
  SELECT
    c.[AccountNumber]     AS '@AccountNumber',
    UPPER(LTRIM(RTRIM(REPLACE(
    CONCAT( p.[Title], ' ', p.[FirstName], ' ', p.[MiddleName], ' ', p.[LastName], ' ', p.[Suffix]),
    '  ', ' '))))         AS '@FullName'
  FROM [Sales].[Customer] c
    INNER JOIN [Person].[Person] p
  ON c.[PersonID] = p.[BusinessEntityID]
  WHERE c.[CustomerID] = soh.[CustomerID]
  FOR XML PATH ('Customer'), TYPE
  ),

  (
  SELECT
    sod.[OrderQty]      AS '@Quantity',
    FORMAT(sod.[UnitPrice],'C')
                        AS '@UnitPrice',
    FORMAT(ROUND(sod.[LineTotal],2),'C')
                        AS '@LineTotal',
    sod.[ProductID]     AS '@ProductId',
    CASE WHEN (sod.[ProductID] >= 710) AND (sod.[ProductID] <= 720) AND (sod.[OrderQty] >= 5) THEN 'Y' ELSE 'N' END
                        AS '@InventoryCheckRequired'

  FROM [Sales].[SalesOrderDetail] sod
  WHERE sod.[SalesOrderID] = soh.[SalesOrderID]
  ORDER BY sod.[SalesOrderDetailID]
  FOR XML PATH ('LineItem'), TYPE, ROOT('OrderLineItems')
  )

FROM [Sales].[SalesOrderHeader] soh
WHERE soh.[TerritoryId] = @TerritoryId
ORDER BY soh.[TotalDue] DESC
FOR XML PATH ('Order'), ROOT('Orders')

顯然，這是複雜的查詢。如稍後所見，結果會在資料庫伺服器上使用大量處理資源。

如何修正問題

將處理從資料庫伺服器移至其他應用層。在理想情況下，您應該限制資料庫執行數據存取作業，只使用資料庫優化的功能，例如關係資料庫管理系統中的匯總（RDBMS）。

例如，先前的 Transact-SQL 程式代碼可以取代為只擷取要處理之數據的語句。

SELECT
soh.[SalesOrderNumber]  AS [OrderNumber],
soh.[Status]            AS [Status],
soh.[OrderDate]         AS [OrderDate],
soh.[DueDate]           AS [DueDate],
soh.[ShipDate]          AS [ShipDate],
soh.[SubTotal]          AS [SubTotal],
soh.[TaxAmt]            AS [TaxAmt],
soh.[TotalDue]          AS [TotalDue],
c.[AccountNumber]       AS [AccountNumber],
p.[Title]               AS [CustomerTitle],
p.[FirstName]           AS [CustomerFirstName],
p.[MiddleName]          AS [CustomerMiddleName],
p.[LastName]            AS [CustomerLastName],
p.[Suffix]              AS [CustomerSuffix],
sod.[OrderQty]          AS [Quantity],
sod.[UnitPrice]         AS [UnitPrice],
sod.[LineTotal]         AS [LineTotal],
sod.[ProductID]         AS [ProductId]
FROM [Sales].[SalesOrderHeader] soh
INNER JOIN [Sales].[Customer] c ON soh.[CustomerID] = c.[CustomerID]
INNER JOIN [Person].[Person] p ON c.[PersonID] = p.[BusinessEntityID]
INNER JOIN [Sales].[SalesOrderDetail] sod ON soh.[SalesOrderID] = sod.[SalesOrderID]
WHERE soh.[TerritoryId] = @TerritoryId
AND soh.[SalesOrderId] IN (
    SELECT TOP 20 SalesOrderId
    FROM [Sales].[SalesOrderHeader] soh
    WHERE soh.[TerritoryId] = @TerritoryId
    ORDER BY soh.[TotalDue] DESC)
ORDER BY soh.[TotalDue] DESC, sod.[SalesOrderDetailID]

然後，應用程式會使用 .NET Framework System.Xml.Linq API 將結果格式化為 XML。

// Create a new SqlCommand to run the Transact-SQL query
using (var command = new SqlCommand(...))
{
    command.Parameters.AddWithValue("@TerritoryId", id);

    // Run the query and create the initial XML document
    using (var reader = await command.ExecuteReaderAsync())
    {
        var lastOrderNumber = string.Empty;
        var doc = new XDocument();
        var orders = new XElement("Orders");
        doc.Add(orders);

        XElement lineItems = null;
        // Fetch each row in turn, format the results as XML, and add them to the XML document
        while (await reader.ReadAsync())
        {
            var orderNumber = reader["OrderNumber"].ToString();
            if (orderNumber != lastOrderNumber)
            {
                lastOrderNumber = orderNumber;

                var order = new XElement("Order");
                orders.Add(order);
                var customer = new XElement("Customer");
                lineItems = new XElement("OrderLineItems");
                order.Add(customer, lineItems);

                var orderDate = (DateTime)reader["OrderDate"];
                var totalDue = (Decimal)reader["TotalDue"];
                var reviewRequired = totalDue > 5000 ? 'Y' : 'N';

                order.Add(
                    new XAttribute("OrderNumber", orderNumber),
                    new XAttribute("Status", reader["Status"]),
                    new XAttribute("ShipDate", reader["ShipDate"]),
                    ... // More attributes, not shown.

                    var fullName = string.Join(" ",
                        reader["CustomerTitle"],
                        reader["CustomerFirstName"],
                        reader["CustomerMiddleName"],
                        reader["CustomerLastName"],
                        reader["CustomerSuffix"]
                    )
                   .Replace("  ", " ") //remove double spaces
                   .Trim()
                   .ToUpper();

               customer.Add(
                    new XAttribute("AccountNumber", reader["AccountNumber"]),
                    new XAttribute("FullName", fullName));
            }

            var productId = (int)reader["ProductID"];
            var quantity = (short)reader["Quantity"];
            var inventoryCheckRequired = (productId >= 710 && productId <= 720 && quantity >= 5) ? 'Y' : 'N';

            lineItems.Add(
                new XElement("LineItem",
                    new XAttribute("Quantity", quantity),
                    new XAttribute("UnitPrice", ((Decimal)reader["UnitPrice"]).ToString("C")),
                    new XAttribute("LineTotal", RoundAndFormat(reader["LineTotal"])),
                    new XAttribute("ProductId", productId),
                    new XAttribute("InventoryCheckRequired", inventoryCheckRequired)
                ));
        }
        // Match the exact formatting of the XML returned from SQL
        var xml = doc
            .ToString(SaveOptions.DisableFormatting)
            .Replace(" />", "/>");
    }
}

注意

此程式代碼有點複雜。對於新的應用程式，您可能偏好使用串行化連結庫。不過，這裡的假設是開發小組正在重構現有的應用程式，因此方法必須傳回與原始程序代碼完全相同的格式。

考量

許多資料庫系統都經過高度優化，可執行特定類型的數據處理，例如計算大型數據集的匯總值。請勿將這些類型的處理移出資料庫。
如果這樣做會導致資料庫透過網路傳輸更多數據，請勿重新放置處理。請參閱外部擷取反模式。
如果您將處理移至應用層，該層可能需要相應放大以處理額外的工作。

如何偵測問題

忙碌資料庫的徵兆包括存取資料庫的作業中輸送量和回應時間不成比例的下降。

您可以執行下列步驟來協助識別此問題：

使用效能監視來識別生產系統執行資料庫活動所花費的時間。
檢查資料庫在這些期間所執行的工作。
如果您懷疑特定作業可能造成太多資料庫活動，請在受控制的環境中執行負載測試。每個測試都應該使用可變用戶負載來執行可疑作業的混合。檢查負載測試中的遙測，以觀察資料庫的使用方式。
如果資料庫活動顯示大量處理，但數據流量很少，請檢閱原始程式碼，以判斷處理是否可以更妥善地在其他地方執行。

如果資料庫活動量較低或回應時間相對較快，則忙碌的資料庫不太可能是效能問題。

診斷範例

下列各節會將這些步驟套用至稍早所述的範例應用程式。

監視資料庫活動的數量

下圖顯示針對範例應用程式執行負載測試的結果，其步驟負載最多為50位並行使用者。要求數量迅速達到限制，並維持在該層級，而平均響應時間會穩步增加。對數刻度用於這兩個計量。

下一個圖表會顯示CPU使用率和 DTU 作為服務配額的百分比。 DTU 會提供資料庫執行多少處理的量值。圖表顯示 CPU 和 DTU 使用率都很快達到 100%。

檢查資料庫所執行的工作

可能是資料庫所執行的工作是真正的數據存取作業，而不是處理，因此請務必瞭解資料庫忙碌時執行的 SQL 語句。監視系統以擷取 SQL 流量，並將 SQL 作業與應用程式要求相互關聯。

如果資料庫作業純粹是數據存取作業，而不需要處理很多，則問題可能是多餘的擷取。

實作解決方案並驗證結果

下圖顯示使用更新的程式代碼進行負載測試。輸送量明顯較高，每秒超過 400 個要求，而不是先前的 12 個要求。平均回應時間也比較低得多，相較於 4 秒以上，僅高於 0.1 秒。

CPU 和 DTU 使用率顯示，儘管輸送量增加，系統仍花費較長的時間達到飽和度。

無關的擷取反模式

共用方式為

忙碌資料庫反模式

問題說明

如何修正問題

考量

如何偵測問題

診斷範例

監視資料庫活動的數量

檢查資料庫所執行的工作

實作解決方案並驗證結果

意見反應

其他資源

共用方式為

忙碌資料庫反模式

問題說明

如何修正問題

考量

如何偵測問題

診斷範例

監視資料庫活動的數量

檢查資料庫所執行的工作

實作解決方案並驗證結果

相關資源

意見反應

其他資源