インデックスには主キーが含まれる
MySQL のセカンダリインデックスには、主キーが自動的に含まれます。セカンダリインデックスを使って特定のレコードを見つけ出す流れは、以下の二段階です。
ちなみに、OracleやPostgreSQLのような他のデータベースでは、主キーではなく「ROWID」や「TID」と呼ばれる行を識別するための情報が代わりに保存されます。MySQL（InnoDB）の仕組みは、クラスターインデックスだからこその特徴と言えます。
インデックスに主キーを指定すると重複する？
さて、ここで一つの疑問が浮かびます。
MySQLのセカンダリインデックスは主キーを自動的に含んでいます。では、インデックス定義の末尾に主キーを明示的に加えた場合、インデックス内で主キーの情報は重複して保存されてしまうのでしょうか？
-- 通常のインデックス（主キーは自動追加） CREATE INDEX idx ON table_name (c1); -- 主キーを明示的に末尾に含めたインデックス CREATE INDEX idx_with_pk ON table_name (c1, id); -- id が主キー
🧪 実験：テーブルサイズを比較
もし重複して保存されるなら、後者のインデックスを持つテーブルのサイズは、前者のインデックスを持つテーブルよりも大きくなるはずです。 以下のパターンで確認してみました。
| No. | テーブル名 | インデックスの定義 | 説明 |
|---|---|---|---|
| 1 | no_redundant_idx_tbl | c1 | 主キーを含めない標準的な定義 |
| 2 | redundant_idx_asc_tbl | c1, id | 主キーを昇順で明示的に指定 |
| 3 | redundant_idx_desc_tbl | c1, id DESC | 主キーを降順で指定 |
もし、主キーが重複して保存されるのであれば、No.1 と No.2 のテーブルサイズに差がでるはずですが、
すべてのテーブルサイズが完全に一致しました（ALTER TABLE xxx ENGINE=InnoDB で再構築済みのサイズ）。
$ sudo ls -al /var/lib/mysql/t total 1179660 drwxr-x---. 2 mysql mysql 105 Oct 24 11:41 . drwxr-x--x. 8 mysql mysql 4096 Oct 24 09:30 .. -rw-r-----. 1 mysql mysql 402653184 Oct 24 09:09 no_redundant_idx_tbl.ibd -rw-r-----. 1 mysql mysql 402653184 Oct 24 11:41 redundant_idx_asc_tbl.ibd -rw-r-----. 1 mysql mysql 402653184 Oct 24 11:41 redundant_idx_desc_tbl.ibd
この結果から、MySQL（InnoDB）は、セカンダリインデックスの末尾に主キーが指定された場合、主キーが重複して保存されるのを自動的に回避していることが分かります。
明示的な主キー指定が意味を持つケース
では、主キーを明示的にインデックスに含める行為は、全く無意味なのでしょうか？
→ いいえ。降順インデックスを利用するケースでは、明示的に指定する意味があります。
明示的に主キーを降順で定義したインデックス (No.3) は、ORDER BY c1, id DESC を最適化可能です（Extra に Using filesort が出力されてないことからソートがインデックスで解決されていることがわかる）。
mysql> EXPLAIN select * from redundant_idx_desc_tbl order by c1, id DESC limit 10; +----+-------------+------------------------+------------+-------+---------------+-----------------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------------------+------------+-------+---------------+-----------------+---------+------+------+----------+-------+ | 1 | SIMPLE | redundant_idx_desc_tbl | NULL | index | NULL | redundant_idx_2 | 1027 | NULL | 10 | 100.00 | NULL | +----+-------------+------------------------+------------+-------+---------------+-----------------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
一方で、c1のみのインデックス（No.1）、または主キーを昇順で定義したインデックス（No.2）では、id DESC の部分をインデックスでカバーできず、filesort が発生します。
-- redundant_idx_asc_tbl mysql> EXPLAIN select * from redundant_idx_asc_tbl order by c1, id DESC limit 10; +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+----------------+ | 1 | SIMPLE | redundant_idx_asc_tbl | NULL | ALL | NULL | NULL | NULL | NULL | 2082711 | 100.00 | Using filesort | +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+----------------+ 1 row in set, 1 warning (0.01 sec)
ソースコード
重複が排除されるよう制御しているソースコードを探してみます。
storage/innobase/dict/dict0dict.cc の dict_index_build_internal_non_clust にありました (MySQL 8.0.44) 。
indexed[field->col->ind] = true でインデックスに含まれているカラムにフラグを立てて、主キーのうち、フラグが立っていないカラムのみ dict_index_add_col を呼んでいます。
** Builds the internal dictionary cache representation for a non-clustered
index, containing also system fields not defined by the user.
@return own: the internal representation of the non-clustered index */
static dict_index_t *dict_index_build_internal_non_clust(
const dict_table_t *table, /*!< in: table */
dict_index_t *index) /*!< in: user representation of
a non-clustered index */
{
<snip>
/* Mark the table columns already contained in new_index */
for (i = 0; i < new_index->n_def; i++) {
field = new_index->get_field(i);
if (field->col->is_virtual()) {
continue;
}
/* If there is only a prefix of the column in the index
field, do not mark the column as contained in the index */
if (field->prefix_len == 0) {
indexed[field->col->ind] = true;
}
}
/* Add to new_index the columns necessary to determine the clustered
index entry uniquely */
for (i = 0; i < clust_index->n_uniq; i++) {
field = clust_index->get_field(i);
if (!indexed[field->col->ind]) {
dict_index_add_col(new_index, table, field->col, field->prefix_len,
field->is_ascending);
} else if (dict_index_is_spatial(index)) {
/*For spatial index, we still need to add the
field to index. */
dict_index_add_col(new_index, table, field->col, field->prefix_len,
field->is_ascending);
}
}
まとめ
MySQLのセカンダリインデックスについて、今回の実験で分かったことは以下の2点です。
- インデックスの末尾に主キーを指定したとしても、主キーが重複してインデックスに含まれることはない。主キーを末尾に明示的に書いても書かなくても同じインデックスが作られる。
- ただし、降順インデックスを活用したい場合、明示的に主キーを含めるのは意味がある。
