GUID 叢集索引測試 3 - 觀察索引重組與索引重建對寫入動作的影響

接連兩篇談完 GUID 叢集索引會造成 INSERT 變慢以及資料表虛胖，簡單有效解決是另建 BIGINT 或 INT 自動跳號欄位當叢集索引，GUID 仍可做為 Primary Key。(參考：GUID Primary Key 資料庫避雷守則)

對上線運轉多年的現有系統來說，Schema 很難說改就改，異動資料表欄位如同飛航過程修改飛機骨架，光用想的就頭皮發麻，尋求可以減緩 GUID 叢集索引負面影響的對策才是較務實的做法。套句廣告詞：先求不傷身體，再講求效果。

要改善 SQL Server 索引碎片化問題，主要可透過索引重建(Rebuild)或索引重組(Reorganize)，而索引重建再分為離線作業及線上作業(加上 ONLINE = ON 參數) ，故共有三種選項：

• ALTER INDEX ... ON ... REBUILD
  離線執行重建，所需時間最短，但過程需卸除和重新建立索引，期間資料庫無法使用
• ALTER INDEX ... ON ... REBUILD (ONLINE = ON)
  線上索引重建，期間資料表可繼續使用，但修改資料時需更新索引的額外副本，影響效能 參考
• ALTER INDEX ... ON ... REORGANIZE
  重新組織索引耗費的資源最少，是官方最建議的做法，期間資料表可繼續查詢或更新，但耗費時間較久

延伸閱讀：

• 將索引維護最佳化以改善查詢效能並降低資源耗用量
• SQL Server 上大型資料表的索引效能調整與維護 by CaryHsu

離線重建索引是最快的，但營運中的系統也不太可能為了重建索引停用資料表，我試在 INSERT 過程執行 ALTER INDEX ... ON ... REBUILD，不意外地當下那筆作被卡住 15 秒直到重建完成，

1000001: 0ms
1000002: 0ms
1000003: 0ms
1000004: 0ms
1000005: 15596ms
1000006: 0ms
1000007: 0ms
1000008: 0ms

不管是線上索引重建或索引重組，執行同時一定會影響效能，所以我修改了先前的測試程式，計劃完成三組測試：

1. 每 10 萬筆觸發索引重組(若前次沒做完就跳過)，在 GUID 叢集索引資料表 INSERT 1000 萬筆
2. 每 10 萬筆暫停寫入，執行索引重組(想像成離峰時間執行，不影響日間作業)再繼續，在 GUID 叢集索引資料表 INSERT 1000 萬筆 3. 每 10 萬筆觸發線上索引重建(若前次沒做完就跳過)，在 GUID 叢集索引資料表 INSERT 1000 萬筆

程式如下，每 10 萬筆觸發另起 Task 同步跑 ALTER INDEX ... ON ... REORGANIZE，用 bool Running 偵測前次若在執行中就略過。

using System.Diagnostics;
using Microsoft.Data.SqlClient;
public class SqlRunner
{
    const string cs = "data source=.;initial catalog=FragPerfTest;integrated security=True;encrypt=false";
    public enum TableNameEnum
    {
        GuidClustIdx,
        IntClustIdx
    }

    static int CurrRowIdx = 0;
    static int NextReorgIdx = 0;

    public static void InsertData(TableNameEnum tableName, long rowCount, int reorgCount)
    {
        NextReorgIdx = reorgCount;
        using var cn = new SqlConnection(cs);
        cn.Open();
        using var cmd = new SqlCommand($"INSERT INTO {tableName} (PK, LongText) VALUES (@pk, @text)", cn);
        cmd.CommandTimeout = 300;
        var pPK = cmd.Parameters.Add("@pk", System.Data.SqlDbType.UniqueIdentifier);
        var pText = cmd.Parameters.Add("@text", System.Data.SqlDbType.NVarChar, -1);
        pText.Value = new string('x', 1000);
        var sw = new System.Diagnostics.Stopwatch();
        Directory.CreateDirectory("logs");
        Func<string> getFileName = () => $"logs\\Test-{DateTime.Now:MMdd-HHmmss}.txt";
        StreamWriter logger = new StreamWriter(getFileName());
        for (var i = 0; i < rowCount; i++)
        {
            CurrRowIdx = i;
            pPK.Value = Guid.NewGuid();
            sw.Restart();
            cmd.ExecuteNonQuery();
            sw.Stop();
            if (i % 100 == 0) Console.WriteLine($"{i:n0} Rows Inserted");
            if (i % 500_000 == 0)
            {
                logger.Flush();
                logger.Dispose();
                logger = new StreamWriter(getFileName());
            }
            logger.WriteLine($"{i + 1}: {sw.ElapsedMilliseconds}ms");
            if (i > NextReorgIdx)
            {
                Task.Run(() => ReoraganizeIndex(tableName));
                NextReorgIdx += reorgCount;
            }
        }
        logger.Flush();
        logger.Dispose();
    }
    static bool Running = false;
    const string logPath = "results\\ReorgIndex.log";
    public static void ReoraganizeIndex(TableNameEnum tableName)
    {
        if (Running) return;
        try
        {
            Running = true;
            Console.WriteLine("Reorganizing Index");
            int startRowIdx = CurrRowIdx;
            var sw = new Stopwatch();
            sw.Start();
            using var cn = new SqlConnection(cs);
            cn.Open();
            using var cmd = new SqlCommand($"ALTER INDEX PK_{tableName} ON {tableName} REORGANIZE", cn);
            cmd.CommandTimeout = 0; // No Timeout
            cmd.ExecuteNonQuery();
            int endRowIdx = CurrRowIdx;
            sw.Stop();
            Console.WriteLine($"Reorganize Index Elapsed: {sw.ElapsedMilliseconds}ms");
            File.AppendAllText(logPath, $"{tableName} from {startRowIdx} to {endRowIdx} rows, Elapsed: {sw.ElapsedMilliseconds}ms{Environment.NewLine}");
            Running = false;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            File.AppendAllText(logPath, ex.Message + Environment.NewLine);
        }
    }
}

先看沒有索引重組或線上索引重建的數據：

邊索引重組邊 INSERT 的數據如下，最大值不時會拉長到 1 秒以上，但 99% (綠色) 仍壓在 0.5 秒以下，不算太嚴重。1000 萬筆寫入時間變長兩倍多，由兩小時出頭延長到六小時。

將 Task.Run(() => ReoraganizeIndex(tableName)); 改為 ReoraganizeIndex(tableName);，模擬每天離峰索引重組，看看結果比不重組改善多少。有點意外，我沒看出明顯改善：

第三組測試比照邊索引重組邊 INSERT，但改成一邊線上索引重建(ALTER INDEX ... ON ... REBUILD (ONLINE = ON) )一邊 INSERT。線上索引重建對寫入效的影響比較明顯，最大值不時突破 10 秒，大部分的寫入還是可以零秒完成，但平均值跟 99 百分位數都拉高了，而 1000 萬筆寫入時間拉長 10 倍跑了 10 小時才完成。

而另一個值得觀察的點是邊 INSERT 邊重組、專心專組、邊 INSERT 邊重建耗費的時間。

邊 INSERT 邊索引重組與資料筆數大致成線性關係，由於需待前次做完進行下次，執行時機不固定，最後一次發生在 680 萬筆左右，耗時超過一小時 (4472 秒, 1h14m)。

停止 INSERT 專心索引重組耗時與資料筆數也是呈線性關係，但時間大幅縮短一半，1000 萬筆耗時 1352 秒，只要 22 分鐘。

若不考慮執期間影響寫入作業的效能，線上索引重建比線上重組有效率多了，1000 萬也只要 502 秒，八分多鐘，快了 N 倍。

做個結論：

1. 索引重組或索引重建對 GUID 叢集索引 INSERT 作業的效能改善不明顯(但原本也不嚴重就是了)，但減少資料分頁數量、消除碎片化及節省磁碟空間有益無害。
2. 如果系統有明確的離峰時段(當下極少人使用，且能容忍延遲)，線上索引重建是最有效率的做法；由測試結果，索引重組對 INSERT 效能的影響比預期小，邊重組邊寫入造成的效能拖累不明顯。

不過，如果可以，避免用 GUID 當叢集索引(當 PK 沒問題，但好像不少把兩件事綁在一起)是更好的解法。