# MySQLの基礎
クラスタインデックスとプライマリキー
2023-11-01 社内LT会
iinuma
---
### ある日、ネットニュースを見ていると…
<img src="https://hackmd.io/_uploads/HkrirA3G6.png" width="50%"/>
https://gigazine.net/news/20231023-uuid-v7/
---
### プライマリキーにしてもパフォーマンス問題が起きないUUIDv7
↔ UUIDv4ではパフォーマンス問題が起きる
🤔 なぜ？
※ アンチパターンとして避けていたが、理由をちゃんと理解してないので調べました。
---
## そもそもUUIDv4をプライマリキーにするユースケースとは？
---
### パフォーマンス観点
- シーケンスを使うと同期処理が必要になりボトルネックになる
- UUID(v4)を使うことで値が競合しない
⇒ 大規模分散システムで同時書き込みが多いケース
（どれくらい？）
---
### セキュリティ観点
- 公開URLに利用するため推測不可能な値が欲しい
- カスタマー数や商品数などの重要指標を推測されたくない
---
## UUIDv4はなぜダメなのか？
---
前提1：MySQL(InnoDB)はクラスタインデックスを利用する
<img src="https://hackmd.io/_uploads/ryt_1xpG6.png" width="50%"/>
インデックスのLeafにデータを保持
⇒インデックスと同じ順序で連続して保持
出典：SQLパフォーマンス詳解
---
（参考）非クラスタインデックスの場合
<img src="https://hackmd.io/_uploads/r1DZfg6MT.png" width="50%"/>
Leafには実データではなくrowid
https://docs.oracle.com/cd/E96517_01/cncpt/indexes-and-index-organized-tables.html
---
前提2：バッファプールの存在
<img src="https://hackmd.io/_uploads/SJhdjlTzp.png" width="50%"/>
頻繁にアクセスされるテーブルとインデックスのキャッシュすることで処理速度を向上
https://dev.mysql.com/doc/refman/8.0/ja/innodb-buffer-pool.html
https://medium.com/datadenys/tuning-mysql-8-0-server-for-better-performance-238e463f5583
---
バッファプールでのキャッシュの単位は「ページ」
⇒ 同じページへのIOはキャッシュが使える
<img src="https://hackmd.io/_uploads/H1lDdKgTza.png" width="50%"/>
https://qiita.com/SH2/items/654d89759e7e39d999b5
---
結論:UUIDv4を使うと、書き込み先がランダムなページになり、バッファプールのキャッシュヒット率が低くなる
⇒ Insertの性能劣化
<img src="https://hackmd.io/_uploads/ByJ39gTMT.png" width="60%"/>
※ イメージ
---
## UUIDv7
<img src="https://hackmd.io/_uploads/ByDa0pazT.png" width="60%"/>
- 先頭48bitがタイムスタンプ → 時系列順
- その後ろにUUID versionと疑似乱数のセクション → 衝突を防ぐ
https://www.ietf.org/archive/id/draft-peabody-dispatch-new-uuid-format-04.html#name-uuid-version-7
---
## UILD
<img src="https://hackmd.io/_uploads/S1x42T6Mp.png" width="45%"/>
人間にとっての視認性が考慮されている
- string表現で26桁 ※ UUIDは16進数で32桁
- Crockford's base32：誤認しやすい `I`, `L`, `O`, `U` を除いている
一方、base32とかMySQL標準ではないので、自前で準備が必要
https://github.com/ulid/spec
---
## 私の見解
現状の私たちのシステムでUUIDv7やULIDが必要なケースはほぼないのでは？（正確にはベンチマークで確認したほうが良い）
- auto incrementもしくはナチュラルキーで性能問題は起きてない
- URLを予測不可能にしたいなら別カラムにUUIDv4を設定すればOK
---
## まとめ
- クラスタインデックスを使うMySQL/InnoDBでランダム性の高いIDをプライマリキーに設定すると、書き込み先のページがランダムになりバッファプールのキャッシュヒット率が低下し、性能劣化に繋がる。
- UUIDv7やULIDという選択肢もあるが、必要性はよく考えて、ベンチマークで検証してから使いましょう。
---
参考
- [SQLパフォーマンス詳解](https://sql-performance-explained.jp/)
- [MySQLでプライマリキーをUUIDにする前に知っておいて欲しいこと](https://techblog.raccoon.ne.jp/archives/1627262796.html)：UUIDv4でも順序を担保した保存方法を紹介している
- [MySQLとPostgreSQLと主キー](https://speakerdeck.com/hmatsu47/mysqltopostgresqltozhu-ki)：UUIDをchar(32), binary(16)で保存したケースをベンチマークで比較している
- [MySQLでもULIDを発行した〜い！ので検証してみた](https://tech.layerx.co.jp/entry/2023/07/14/105902)：MySQLのFunctionでULIDを発行している
{"title":"クラスタインデックスとプライマリキー","description":"https://hackmd.io/@lSN7K4E9Q92Y19-uisHVtg/Sybc1C3G6","lang":"ja-JP","contributors":"[{\"id\":\"95237b2b-813d-43dd-98d7-dfae8ac1d5b6\",\"add\":5701,\"del\":2917}]"}