# 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を発行している