Zenn
😽

DB のチューニングで実際に成果が出た 3 つの施策

2024/03/16に公開
C#
DB
SQL Server
efcore
tech

最近は DB のチューニングをやっていました。

DB のチューニングと聞くと「適切なところにインデックスを張る」のが基本ですが、中規模以上のトランザクションになるとこれだけでは対処しきれない問題が出てきます。

そこで本記事では、実際に DB のチューニングで成果が出た 3 つの施策について残しておこうと思います。

また、技術的には以下を使いました。

  • C#
  • SQL Server（Azure SQL Database）
  • EF Core

そのまま他の領域に応用できない内容もあるかもしれませんが、少しでも参考になれば嬉しいです。

本記事の対象者

  • 中規模以上のトラフィックをさばくシステムを構築している人
  • パッと調べただけでは出てこない DB チューニングのノウハウを知りたい人
  • 同じ現象で苦しまないよう事前に知識として知っておきたい人

DB のチューニングで実際に成果が出た 3 つの施策

以下の３つです。

  • Isolation Level の変更
  • 付加列インデックスの追加
  • ラッチの競合の回避

ひとつずつ見ていきます。

Isolation Level の変更

Isolation Level は「トランザクション分離レベル」のことで、ざっくりトランザクション中のロックの強さを表すものです。

C# では TransactionScope を使うと簡単にトランザクション管理ができます。

しかし、TransactionScope の Isolation Level の初期値が一番強いロックがかかる Serializable になっています。

Serializable は一番安全に処理できますが、ロックの解除待ちで処理に時間がかかるケースが出てくるため、必要がなければロックの強度を落とすことが望ましいです。

ちなみに Azure SQL Database では最適化されたロックが適応されているため、ロックの待機時間がより少なくなる仕組みになっています。

しかしこの場合でも、LCK_M_S_XACT_MODIFY のような Update の待機が発生してしまっていました。

私が担当したケースではユーザー単位に自分のデータしか読み取らないケースがほとんどであったため、Isolation Level を ReadUncommitted に変更することで、待機時間を大きく削減することができました。

TransactionScope を使う場合は次のように指定できます。

var options = new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted };
using (var tx = new TransactionScope(TransactionScopeOption.Required, options, TransactionScopeAsyncFlowOption.Enabled))
{
    tx.Complete();
}

「ReadUncommitted を使うなんて危険だ」と勝手に思い込んでいましたがそれは用途次第であり、ロックについては「大は小を兼ねない」ことを学びました。

付加列インデックスの追加

Where 条件には非クラスター化インデックスを張るのは基本ですが、データ量が多くなると SELECT が非常に遅くなることがあります。

適切にインデックスを張っていても遅い場合は、付加列インデックスを追加することで改善される可能性があります。

付加列インデックスは、非クラスター化インデックスに対して取得列を指定できるものです。
インデックスが効いている処理で、かつ取得列が付加列インデックスに含まれている場合に威力を発揮します。

以下が EF Core での指定例です。

public class CoreDbContext(DbContextOptions<CoreDbContext> options) : DbContext(options)
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<TestTable>()
            .HasIndex(static x => new
            {
                x.PlayerId,
            })
            .IncludeProperties(static x => new
            {
                x.Name,
            });
    }
}

通常の非クラスター化インデックスではインデックスに紐づく情報として主キーしか持っていないため、それ以外の列を取得する場合はさらに主キーをもとに必要な情報を取りに行くことになり、ここで時間がかかってしまいます。

  1. 非クラスター化インデックスで検索
  2. 主キーを見つける
  3. 主キーをもとに他の情報を検索

付加列インデックスは、主キー以外にもインデックスに紐づく情報を追加できる機能なので、もう一度主キーで取りに行く手間がなくなり高速化されます。

  1. 非クラスター化インデックスで検索
  2. 主キーと一緒に必要な情報があるためここで終わり

ではすべてを付加列インデックスにすればいいのかというとそうではなく、保存する情報が増える分 DB の容量も増えてしまうので、必要なところだけ適用するのが望ましいです。

ラッチの競合の回避

ラッチは SQL Server が内部でやっているデータの排他制御の仕組みのことです。

私が遭遇したのは PAGELATCH_EX というもので、同じテーブルに対して短い時間で大量に INSERT されると、ラッチが競合して待機時間が発生してしまいます。

幸い、Microsoft 公式ドキュメントに「SQL Serverの最終ページ挿入PAGELATCH_EX競合を解決する」というピンポイントの内容があったので、こちらを参考に対策をしました。

その中でさまざまな解決方法が載っていたので調べたところ、「テーブルのパーティション分割と、ハッシュ値を持つ計算列を使用する」のが一番効果がありそうだということがわかったので、こちらを採用しました。
参考：Last-Page Insert Contention in SQL Server

公式ドキュメントからの転記ですが、以下のようにすればよいと書かれています。

USE testdb;

CREATE TABLE Customers
( CustomerID BIGINT IDENTITY(1,1) NOT NULL,
CustomerLastName VARCHAR (32) NOT NULL,
CustomerFirstName VARCHAR(32) NOT NULL );

ALTER TABLE Customers
ADD [HashID] AS CONVERT(TINYINT, ABS(CustomerID % 16)) PERSISTED NOT NULL;

CREATE PARTITION FUNCTION pf_hash (TINYINT) AS RANGE LEFT FOR VALUES (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) ;

CREATE PARTITION SCHEME ps_hash AS PARTITION pf_hash ALL TO ([PRIMARY]);

CREATE UNIQUE CLUSTERED INDEX CIX_Hash
ON Customers (CustomerID, HashID) ON ps_hash(HashID);

ただ上記は EF Core に書き換える必要があったので、その場合にどうしたらいいのかを残しておこうと思います。

パーティションまわりの関数を作るところについては自動化する手段が思いつかず、生クエリを流しました。
毎回同じクエリを流しても問題ないように存在確認をしています。

IF( NOT EXISTS( SELECT * FROM sys.partition_functions WHERE name = 'pf_hash' ) )
BEGIN 
	CREATE PARTITION FUNCTION pf_hash (TINYINT) AS RANGE LEFT FOR VALUES (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) ;
END
IF( NOT EXISTS( SELECT * FROM sys.partition_schemes WHERE name = 'ps_hash' ) )
BEGIN
	CREATE PARTITION SCHEME ps_hash AS PARTITION pf_hash ALL TO ([PRIMARY]);
END

テーブル定義では DatabaseGenerated の属性をつけます。
HashId は計算式を埋め込むので Computed を指定します。

public sealed class TestTable
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long Id { get; init; }

    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public byte HashId { get; init; }
}

どうやら EF Core ではパーティションを含めた主キーの指定が対応していないようで、この辺は自前で頑張るしかなさそうでした。
参考：Support for table partitioning #1035

主キーはマイグレーションファイルで直に指定するので、DbContext では主キーがなくても OK なようにしておきます。
また、HashId の具体的な計算式も定義します。

public class CoreDbContext(DbContextOptions<CoreDbContext> options) : DbContext(options)
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<TestTable>()
            .HasNoKey();

        modelBuilder.Entity<TestTable>()
            .Property(static x => x.HashId)
            .HasComputedColumnSql("CONVERT(TINYINT, Id % 16)", stored: true);
    }
}

あとは、生成したマイグレーションファイルに migrationBuilder.Sql のメソッドでパーティションを含めた複合主キーを作成します（もっといい方法があれば募集中です）。

using System;
using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace Test.Migrations
{
    /// <inheritdoc />
    public partial class AddTestTables : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "TestTables",
                schema: "dbo",
                columns: table => new
                {
                    Id = table.Column<long>(type: "bigint", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    HashId = table.Column<byte>(type: "tinyint", nullable: false, computedColumnSql: "CONVERT(TINYINT, Id % 16)", stored: true),
                },
                constraints: table =>
                {
                });

            // 手動で追記
            migrationBuilder.Sql(
                """
                CREATE UNIQUE CLUSTERED INDEX IX_T_TestTables_Hash ON TestTables (Id, HashId) ON ps_hash(HashId);
                """);
        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "TestTables",
                schema: "dbo");
        }
    }
}

上記でひととおり完了です。

補足：パーティション化した場合の非クラスター化インデックスについて

パーティション化した場合の非クラスター化インデックスについて、最後に補足しておこうと思います。

例えば PlayerId に非クラスター化インデックスを張り、そこで検索した場合はそのインデックスが効いている状態が望ましいです。

ところが、パーティション化したあとはクラスター化インデックスのほうが優先して適用され、PlayerId の非クラスター化インデックスは適用されていない、ということがありました。

その場合は非常に低速になってしまうので、ヒント句を使用して強制的に非クラスター化インデックスを使うように指定しました。

SELECT *
FROM TestTables
WITH (INDEX(IX_T_TestTables_PlayerId))
WHERE PlayerId = '100'

参考：ヒント (Transact-SQL) - Query

INSERT が早くなって安心していたら思わぬところで遅くなっていたりもしたので、そのあたりも注意してください。

最後に

DB のチューニングで実際に成果が出た 3 つの施策について、備忘録がてら残してみました。

そのまま他の領域に応用できない内容もあるかもしれませんが、少しでも参考になれば嬉しいです。

Discussion