この記事は アイスタイル Advent Calendar 2022 2日目の記事です。
アイスタイルでDBAを担当している@suzukitoです。
今年のアドベントカレンダーは、主キー制約とインデックスの話にする事にしました。
過去の記事でDB関連のやつはこちらです。良かったら読んでみて下さい。
発端
アイスタイルのメインDBMSはSQL Serverなのですが、現在、DBMSの異機種移行に取り組んでいます。移行先は、MySQL互換の分散RDBMS TiDBです。
異機種移行なので、まずは、テーブル定義をSQL ServerからMySQLへ変換しなくてはなりません。
スキーマ変換自体はツールを使うのでそれほど難しくはありません。
面倒なのは、インデックスの統廃合です。
データベースは参照負荷分散を行っているため、1つのライターノードと、複数のリーダーノードに分かれているのですが、リーダーノードは用途別に複数のパターンを運用していました。
このため、リーダー事にインデックスが異なっているのが現状です。
TiDBに移行すると、リーダー・ライターの区別がなくなるため、インデックスの統合と整理をする必要が出てきました。
インデックスの整理をしていると、
おや？
主キー制約とインデックスの関係を理解してないんかな？
と思える、インデックス定義がけっこうな頻度で出てきました。
そこで、アドベントカレンダー向けに記事を書けば、うちのメンバーにも読んでもらえるので一石二鳥だなと考えた次第です。はい。
主キー制約とは
分かりやすかったので、PostgreSQLの公式ドキュメントから引用します。
主キー制約は、列または列のグループがテーブル内の行を一意に識別するものとして利用できることを意味します。 これには値が一意で、かつNULLでないことが必要となります。
（PostgreSQL 14.5 文書第5章 データ定義 5.4. 制約 5.4.4. 主キー）
主キー制約は、次の二つの制約を、指定した列に適用します。
- ユニーク（一意）制約
- NOT NULL（非NULL）制約
そして、主キー制約を指定した列にはユニークインデックスが自動的（暗黙的）に作成されます。
つまり、主キー制約とは、NOT NULL列に定義されたユニークインデックスなんです。
なので、私がインデックス統廃合を実施している中で見つけた次のケースは、無駄なことをやっています。
- 主キーにインデックスを作成する
- 主キーにユニークインデックスを作成する
- インデックス定義に主キーを含める
主キーにインデックスを作成する
なんのこっちゃと思うかも知れませんが、主キーに指定した列に、更に追加でインデックスを作成している例が何度も出てきました。
追いインデックス？？
念には念をってこと？？
使われもしないし、無駄以外の何者でもありませんよ。
主キーにユニークインデックスを作成する
そもそも、主キーに指定した時点でユニークインデックスが作られるんです。
一意性は保証されています。
大事なことなので二回定義するってこと？？
こちらも使われもしないし、無駄以外の何者でもありませんね。
インデックス定義に主キーを含める
複数列を指定したインデックス内に主キーを指定している例が何度も見つかりました。
しかし、これは不要です。
SQL ServerとMySQLのインデックスは、暗黙的に主キーが含まれます。
明示的に主キーをインデックス定義に入れた場合は、インデックス内の並び替えに使われる事になりますが、そういう意図がない場合は、これまた、無駄でしかありませんな。
公式ドキュメントから引用しておきます。
MySQL
InnoDB では、セカンダリインデックス内の各レコードに、行の主キーカラム、およびセカンダリインデックスに指定されたカラムが含まれます。 InnoDB では、クラスタ化されたインデックス内で行を検索する際に、この主キー値が使用されます。
（MySQL :: MySQL 8.0 リファレンスマニュアル :: 15.6.2.1 クラスタインデックスとセカンダリインデックス）
SQL Server
テーブルにクラスター化インデックスがある場合、またはインデックスがインデックス付きビューにある場合は、行ロケーターが行のクラスター化インデックス キーになります。
（SQL Server と Azure SQL のインデックスのアーキテクチャとデザイン ガイド）
なぜ、暗黙的にインデックス内に主キーを含めるのか
インデックスとは何かを考えると分かります。
インデックスの実態は、テーブルのサブセットです。
テーブルの中から検索に使う列を抜粋して作られたミニテーブルです。
インデックス内を検索して、目当ての値が見つかった後は、他の列が必要になりますよね？
インデックス内には一部の列しかないので。
この時、インデックス内に暗黙的に埋め込まれていた主キーを使ってテーブル本体を検索するんです。
こう言う理由で、インデックス定義内で主キーを明示的に定義する必要はありません。
インデックスの構造図
こちらの資料が分かりやすいです。
使われないインデックスは二重で無駄
インデックスはテーブルのサブセットなので、維持にはコストがかかります。
- インデックスのメンテナンスコスト
- ストレージ使用コスト
インデックスを作成すると、INSERT、UPDATE、DELETE時にDBMSが自動で更新を行います。
まずは、そのコストがかかり続けます。
そして、当然ながらストレージを消費します。
無駄遣いは、みなさん、お嫌いですよね？
インデックスの無駄も無くしていきましょう！
まとめ
主キー制約とユニーク制約は、暗黙的にインデックスを作るってこと。
全てのインデックスには主キーが入っているってことを、覚えておきましょう。
インデックスに興味がわいたら
公式ドキュメントが勉強になるんで、ぜひ読んでみて下さい！
- MySQL :: MySQL 8.0 リファレンスマニュアル :: 8.3 最適化とインデックス
- SQL Server と Azure SQL のインデックスのアーキテクチャとデザイン ガイド - SQL Server | Microsoft Learn
- PostgreSQL 14.5文書パート II. SQL言語 第11章 インデックス
それ以外だとこの本がオススメです。