Primary keyの選び方

この記事は最終更新から1年以上経過しています。内容が古くなっている可能性があります。

Primary keyの選び方

テーブル定義を行うときに一つのテーブルの中にPrimary keyの候補(一意制約あり、NOT NULL制約あり)が複数存在する場合、InnoDBのテーブルではどのカラムをPrimary Indexとして選択するのが望ましいのでしょうか?

前提知識としてのInnoDBのデータ構造(Primary keyとSecondary index)

どのカラムをPrimary keyとして選択するべきなのか判断するためには、InnoDBについて少し内部の構造を知る必要があります。

InnoDBのPrimary keyはクラスタインデックスと呼ばれています。内部ではPrimary keyの値をキーとして、(サイズの大きなBLOB型などの例外はありますが)基本的には1行分のデータをまとめて管理されています。

InnoDBのPrimary key以外のインデックスをSecondary indexと呼び、Primary keyに対してルックアップを行うインデックスです。そのためSecondary indexはインデックス対象のカラムとPrimary keyのカラム両方の値を持っています。

Primary keyを選ぶ基準

複数のPrimary key候補の中から次の順で評価していくと多くのシステムで比較的適切なPrimary keyが選択されると思います

  1. Primary keyの値を変更する頻度
  2. データの検索条件と取得カラム
  3. Primary keyのデータ長
目次

Primary keyの値を変更する頻度

1番目の"Primary keyの値を変更する頻度"に関しては、設計時の想定としてPrimary keyとなったカラムに対して値の変更が無いことが望ましいです。もし頻繁に値の変更が想定されるカラムをPrimary keyとして選ぶと性能に問題が出る可能性が高くなります。

なぜなら、Primary keyはクラスタインデックスなので、Primary keyの値が変更されるとメモリやストレージ上に保存されてる行データの場所を移動させる必要があります。併せて対象のテーブルに定義されている全てのSecondary indexも変更する必要もあり、Secondary indexが定義されているカラムの値を変更する場合に比べて非常に負荷が高くなります。

Secondary indexがあるカラムの値を変更する場合、対象行データの変更以外にインデックスの変更も行われますが、変更のなかったカラムに対して張られたインデックスは変更する必要がないためPrimary keyの値の変更に比べて負荷は少なくて済みます。

データの検索条件と取得カラム

複数のPrimary key候補の中からどれが最も適しているかは、そのテーブルを検索する際の条件としてどのカラムがどの程度の頻度で検索条件に指定されるのかで大体決まってきます。

そのような検索条件の傾向と頻度からPrimary keyを検討する場合に、Secondary indexにはPrimary keyの値が含まれていることも頭の片隅に入れておくとより良い選択ができるかもしれません。

これを説明するには具体的な例をあげたほうが方がわかりやすいと思うので、以下のようなPrimary keyとUnique Indexの設定だけが異なる3つのテーブルを作成します。

テストデータは100万件分で、account_idを1からの連番を振り、account_nameはaccount_idの10桁ゼロ埋め文字列、mail_addressはaccount_nameに"@dummmy.mail.example.com"の文字列を足したもの、group_idはaccount_idを100で割った余りを値として入れてあります。

以下はデータのサンプルです。

この3つのテーブルでaccount_idを検索条件にaccount_nameを取得するSELECT文を実行した場合、当たり前ですがExplainの結果は異なります。(テスト環境では実行結果と実行時間に差がなかったので省略します)

ここで注目するべきなのはaccounts_02テーブルではExtraに出てきた"Using index"です。これはSecondary indexに含まれるPrimary keyの値だけを使って結果を返していて、対象の行データ全体を使っていません。

行データとセットになっているPrimary keyに比べて、Secondary indexは必要とされるデータ量が少ないので特定のカラムの値だけが必要な場合には、こちらの方が効率的な場面が出てきます。

もちろんPrimary key以外のカラムも取得しようとすると行データへアクセスする必要があるのでExtraで"Using index"が消えてしまいます。

取得するデータ量が少ない条件ではあまり差が出ませんが、ある程度以上のデータ量を取得する場合、実行速度に差が出ます。(下記の例ではInnoDB buffer poolのキャッシュにヒットするように同じSQLの2回目の実行時間を記載しています)

テーブル同士の結合条件など必要とするカラムが限られる場面では、Secondary indexを使うことを意識したテーブル設計が行えるとより良いパフォーマンスが得られるかもしれません。

Primary keyのデータ長

Secondary indexの中にPrimary keyの値が入るので、Primary keyのデータ長が長ければSecondary indexが必要とするデータ量も増えます

そのため同じデータが入っているテーブルでも必要とされるストレージ容量は異なり、Primary key のデータ長が長いほど必要なストレージ容量が増え、I/O負荷も増します。

また同じサイズのInnoDB buffer poolの場合、Primary keyのデータ長が長い方がキャッシュ上に乗る行数が減るためキャッシュヒット率は悪化する傾向があります。

まとめ

RDBMS製品によってはPrimary keyとUnique indexで実用上の差が少なく、Primary keyの候補が複数あった場合にどれを選んでも実行性能が大きく違わない製品もありますが、MySQL(InnoDB)は違います。

複数のPrimary key候補の中からどれを選ぶのが適切なのかはシステムの特徴によって異なりますが、性能を意識してテーブル設計を行う場合に重要な要素になります。

クラウド上にシステムを構築する場合、MySQLのキャッシュヒット率やストレージ容量は使用するインスタンスサイズやストレージ(容量やスループット、IOPS etc…)などの選択に影響を与えるためPrimary keyの選択次第ではシステムの運用費が変わってくるかもしれません。

MySQL(InnoDB)の特徴を知ってより良いテーブル設計を行う参考になれば幸いです。

参考資料

スマートスタイルTECHブログについて

スマートスタイルTECHブログでは、日頃MySQLのサポート業務に従事している有資格者で構成された技術サポートチームがMySQLに関する技術情報を発信しています。データベースのお困りごとはお気軽にご相談下さい。

よかったらシェアしてね!
  • URLをコピーしました!
目次