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が選択されると思います
- Primary keyの値を変更する頻度
- データの検索条件と取得カラム
- 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つのテーブルを作成します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
CREATE TABLE accounts_01 ( account_id int NOT NULL, /* Primary key候補 システム内部で採番 */ account_name varchar(32) NOT NULL, /* Primary key候補 アカウント名 */ mail_address varchar(255) NOT NULL, /* Primary key候補 メールアドレス */ group_id int DEFAULT NULL, upd_date datetime NOT NULL, reg_date datetime NOT NULL, PRIMARY KEY (account_id), UNIQUE KEY uq_idx01 (account_name), UNIQUE KEY uq_idx02 (mail_address), KEY idx_01 (group_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE accounts_02 ( account_id int NOT NULL, account_name varchar(32) NOT NULL, mail_address varchar(255) NOT NULL, group_id int DEFAULT NULL, upd_date datetime NOT NULL, reg_date datetime NOT NULL, PRIMARY KEY (account_name), UNIQUE KEY uq_idx01 (account_id), UNIQUE KEY uq_idx02 (mail_address), KEY idx_01 (group_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE accounts_03 ( account_id int NOT NULL, account_name varchar(32) NOT NULL, mail_address varchar(255) NOT NULL, group_id int DEFAULT NULL, upd_date datetime NOT NULL, reg_date datetime NOT NULL, PRIMARY KEY (mail_address), UNIQUE KEY uq_idx01 (account_id), UNIQUE KEY uq_idx02 (account_name), KEY idx_01 (group_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ; |
テストデータは100万件分で、account_idを1からの連番を振り、account_nameはaccount_idの10桁ゼロ埋め文字列、mail_addressはaccount_nameに"@dummmy.mail.example.com"の文字列を足したもの、group_idはaccount_idを100で割った余りを値として入れてあります。
以下はデータのサンプルです。
|
1 2 3 4 5 6 7 8 9 |
mysql> SELECT * FROM accounts_01 LIMIT 1 \G *************************** 1. row *************************** account_id: 1 account_name: 0000000001 mail_address: 0000000001@dummy.mail.example.com group_id: 1 upd_date: 2023-01-01 00:00:00 reg_date: 2023-01-01 00:00:00 1 row in set (0.00 sec) |
この3つのテーブルでaccount_idを検索条件にaccount_nameを取得するSELECT文を実行した場合、当たり前ですがExplainの結果は異なります。(テスト環境では実行結果と実行時間に差がなかったので省略します)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
mysql> SELECT account_name FROM accounts_01 WHERE account_id IN( 10, 11); +--------------+ | account_name | +--------------+ | 0000000010 | | 0000000011 | +--------------+ 2 rows in set (0.00 sec) mysql> EXPLAIN SELECT account_name FROM accounts_01 WHERE account_id IN( 10, 11); +----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | accounts_01 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where | +----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT account_name FROM accounts_02 WHERE account_id IN( 10, 11); +----+-------------+-------------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | accounts_02 | NULL | range | uq_idx01 | uq_idx01 | 4 | NULL | 2 | 100.00 | Using where; Using index | +----+-------------+-------------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT account_name FROM accounts_03 WHERE account_id IN( 10, 11); +----+-------------+-------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | accounts_03 | NULL | range | uq_idx01 | uq_idx01 | 4 | NULL | 2 | 100.00 | Using index condition | +----+-------------+-------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) |
ここで注目するべきなのはaccounts_02テーブルではExtraに出てきた"Using index"です。これはSecondary indexに含まれるPrimary keyの値だけを使って結果を返していて、対象の行データ全体を使っていません。
行データとセットになっているPrimary keyに比べて、Secondary indexは必要とされるデータ量が少ないので特定のカラムの値だけが必要な場合には、こちらの方が効率的な場面が出てきます。
もちろんPrimary key以外のカラムも取得しようとすると行データへアクセスする必要があるのでExtraで"Using index"が消えてしまいます。
|
1 2 3 4 5 6 7 |
mysql> EXPLAIN SELECT account_name, group_id FROM accounts_02 WHERE account_id IN( 10, 11); +----+-------------+-------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | accounts_02 | NULL | range | uq_idx01 | uq_idx01 | 4 | NULL | 2 | 100.00 | Using index condition | +----+-------------+-------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) |
取得するデータ量が少ない条件ではあまり差が出ませんが、ある程度以上のデータ量を取得する場合、実行速度に差が出ます。(下記の例ではInnoDB buffer poolのキャッシュにヒットするように同じSQLの2回目の実行時間を記載しています)
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> SELECT account_name FROM accounts_01 WHERE group_id = 1; ～(略)～ 10000 rows in set (0.29 sec) mysql> SELECT account_name FROM accounts_02 WHERE group_id = 1; ～(略)～ 10000 rows in set (0.00 sec) mysql> SELECT account_name FROM accounts_03 WHERE group_id = 1; ～(略)～ 10000 rows in set (0.23 sec) |
テーブル同士の結合条件など必要とするカラムが限られる場面では、Secondary indexを使うことを意識したテーブル設計が行えるとより良いパフォーマンスが得られるかもしれません。
Primary keyのデータ長
Secondary indexの中にPrimary keyの値が入るので、Primary keyのデータ長が長ければSecondary indexが必要とするデータ量も増えます
そのため同じデータが入っているテーブルでも必要とされるストレージ容量は異なり、Primary key のデータ長が長いほど必要なストレージ容量が増え、I/O負荷も増します。
|
1 2 3 4 |
$ ls -lFh accounts_*.ibd -rw-r-----. 1 mysql mysql 196M Mar 22 09:36 accounts_01.ibd -rw-r-----. 1 mysql mysql 212M Mar 22 09:37 accounts_02.ibd -rw-r-----. 1 mysql mysql 260M Mar 22 09:45 accounts_03.ibd |
また同じサイズのInnoDB buffer poolの場合、Primary keyのデータ長が長い方がキャッシュ上に乗る行数が減るためキャッシュヒット率は悪化する傾向があります。
まとめ
RDBMS製品によってはPrimary keyとUnique indexで実用上の差が少なく、Primary keyの候補が複数あった場合にどれを選んでも実行性能が大きく違わない製品もありますが、MySQL(InnoDB)は違います。
複数のPrimary key候補の中からどれを選ぶのが適切なのかはシステムの特徴によって異なりますが、性能を意識してテーブル設計を行う場合に重要な要素になります。
クラウド上にシステムを構築する場合、MySQLのキャッシュヒット率やストレージ容量は使用するインスタンスサイズやストレージ(容量やスループット、IOPS etc…)などの選択に影響を与えるためPrimary keyの選択次第ではシステムの運用費が変わってくるかもしれません。
MySQL(InnoDB)の特徴を知ってより良いテーブル設計を行う参考になれば幸いです。
