複合プライマリーキー vs サロゲートキーでInnoDB読み取りページ数の比較

TL;DR

この考察の単位は ページ数 であって パフォーマンスじゃない 。パフォーマンスを考える時はこれにさらにバッファプールヒット率が関連するはず

• COUNT(*)に関してはページあたりのインデックスレコードの充填数が多いサロゲートキーの方が読むページが少ない

• カバリングインデックスの効くCOUNT(*)に関してはナチュラルキーのサイズの大きさはほとんど関係なさそう

• 行本体を読まなければいけない(=カバリングインデックスで済まない)select_lsitになるとサロゲートキーの充填数の差は目立たなくなる

• ナチュラルキーの左端でレンジスキャンする時がページ読み取り数は最小

• 読みだすページの数だけで比べると , セカンダリキー引きはテーブルスキャンよりも読むページ数は多いことがある

---

日々の覚書: インデックス vs InnoDBログ書き込みのサイズ観測 の続き。

今度は読み取るページ数にのみ着目。

計測方法。

### Before, Afterの累計ページ読み取り数
### After - Beforeがそのクエリで読み取ったページ数になる
SELECT name, count, status FROM information_schema.innodb_metrics WHERE name = 'buffer_pool_read_requests';

## 計測対象クエリ
### COUNT(*) -> リーフページだけ読めば足りるやつ
SELECT COUNT(*) FROM <table>;
SELECT COUNT(*) FROM <table> WHERE user_id = 0825;
SELECT COUNT(*) FROM <table> WHERE updated = ?;

### 全フィールド。クラスターインデックスに触らないといけないやつ
SELECT * FROM <table>;
SELECT * FROM <table> WHEER user_id = 0825;
SELECT * FROM <table> WHEER updated = ?;

## updatedは深く考えずにNOW()で降ってたのでバラツキがあって、
## 結果セットの行数だけ合わせるためにCOUNTしてJOINして値を決めた
mysql> WITH t1_c AS (SELECT updated, COUNT(*) AS c FROM d1.t1 GROUP BY 1),
    -> t2_c AS (SELECT updated, COUNT(*) AS c FROM d1.t2 GROUP BY 1),
    -> t3_c AS (SELECT updated, COUNT(*) AS c FROM d1.t3 GROUP BY 1),
    -> t4_c AS (SELECT updated, COUNT(*) AS c FROM d1.t4 GROUP BY 1),
    -> t5_c AS (SELECT updated, COUNT(*) AS c FROM d1.t5 GROUP BY 1)
    ->
    ->
    -> SELECT c, t1_c.updated AS t1, t2_c.updated AS t2, t3_c.updated AS t3, t4_c.updated AS t4, t5_c.updated AS t5
    -> FROM t1_c JOIN t2_c USING(c) JOIN t3_c USING(c) JOIN t4_c USING(c) JOIN t5_c USING(c);

 c: 567
t1: 2023-02-08 00:32:39
t2: 2023-02-08 00:37:34
t3: 2023-02-08 00:44:08
t4: 2023-02-08 00:51:12
t5: 2023-02-08 00:57:38

---

結果。単位は「ページリクエスト」

COUNT(*)

table	without_WHERE	without_WHERE_key	without_WHERE_vs_t1	WHERE_userid	WHERE_userid_key	WHERE_userid_vs_t1	WHERE_updated	WHERE_updated_key	WHERE_updated_vs_t1	comment
t1	881	PRIMARY	1.00	1286	N/A(type:ALL)	1.00	1286	N/A(type:ALL)	1.00	auto_increment PK + 0 key
t2	881	idx_userid	1.00	7	idx_userid	0.01	1286	N/A(type:ALL)	1.00	auto_increment PK + 1 key
t3	881	idx_userid	1.00	7	idx_userid	0.01	13	idx_updated	0.01	auto_increment PK + 2 key
t4	1140	PRIMARY	1.29	8	PRIMARY	0.01	1373	N/A(type:ALL)	1.07	natural PK + 0 key
t5	1140	idx_updated	1.29	8	PRIMARY	0.01	12	idx_updated	0.01	natural PK + 1 key

all_fields

table	without_WHERE	without_WHERE_key	without_WHERE_vs_t1	WHERE_userid	WHERE_userid_key	WHERE_userid_vs_t1	WHERE_updated	WHERE_updated_key	WHERE_updated_vs_t1	comment
t1	1286	N/A(type:ALL)	1.00	1286	N/A(type:ALL)	1.00	1286	N/A(type:ALL)	1.00	auto_increment PK + 0 key
t2	1286	N/A(type:ALL)	1.00	308	idx_userid	0.24	1286	N/A(type:ALL)	1.00	auto_increment PK + 1 key
t3	1286	N/A(type:ALL)	1.00	308	idx_userid	0.24	1710	idx_updated	1.33	auto_increment PK + 2 key
t4	1373	N/A(type:ALL)	1.07	8	PRIMARY	0.01	1373	N/A(type:ALL)	1.07	natural PK + 0 key
t5	1373	N/A(type:ALL)	1.07	8	PRIMARY	0.01	1708	idx_updated	1.33	natural PK + 1 key

まとめ

この考察の単位は ページ数 であって パフォーマンスじゃない 。パフォーマンスを考える時はこれにさらにバッファプールヒット率が関連するはず

• COUNT(*)に関してはページあたりのインデックスレコードの充填数が多いサロゲートキーの方が読むページが少ない

• カバリングインデックスの効くCOUNT(*)に関してはナチュラルキーのサイズの大きさはほとんど関係なさそう

• 行本体を読まなければいけない(=カバリングインデックスで済まない)select_lsitになるとサロゲートキーの充填数の差は目立たなくなる

• ナチュラルキーの左端でレンジスキャンする時がページ読み取り数は最小
• 読みだすページの数だけで比べると , セカンダリキー引きはテーブルスキャンよりも読むページ数は多いことがある

​