下記の記事を見て、PKにUUIDを使った際に内部的にどうなっているのかを確認してみました
比較対象として、PKにULIDを使った場合も調べてみました。
ULIDはUUIDと互換性がある、ソート可能な識別子です。
MySQLのバージョン
% mysql --version mysql Ver 8.0.19 for osx10.14 on x86_64 (Homebrew)
スキーマ
mysql> SHOW CREATE TABLE innodb_auto_increment\G
*************************** 1. row ***************************
Table: innodb_auto_increment
Create Table: CREATE TABLE `innodb_auto_increment` (
`id` int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.01 sec)
mysql> SHOW CREATE TABLE innodb_integer\G
*************************** 1. row ***************************
Table: innodb_integer
Create Table: CREATE TABLE `innodb_integer` (
`id` int NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.01 sec)
mysql> SHOW CREATE TABLE innodb_uuid\G
*************************** 1. row ***************************
Table: innodb_uuid
Create Table: CREATE TABLE `innodb_uuid` (
`id` char(36) COLLATE utf8mb4_general_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.01 sec)
mysql> SHOW CREATE TABLE innodb_ulid\G
*************************** 1. row ***************************
Table: innodb_ulid
Create Table: CREATE TABLE `innodb_ulid` (
`id` char(36) COLLATE utf8mb4_general_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)
UUIDとULID
UUIDはRubyの SecureRandom.uuid (バージョン 4 の UUID)を使って生成しました。
module SecureRandom (Ruby 3.0.0 リファレンスマニュアル)
ULIDは下記のライブラリを利用して生成しました。
それぞれのテーブルに10万行INSERTしたときの様子
エクステント、ページの可視化
InnoDBの各テーブルスペースはデフォルトでは 16KBのページで構成されます。
MySQL インスタンス内のテーブルスペースはすべて同じページサイズを持っています。
これらのページは、サイズ 1MBのエクステント (連続した 64 個の 16KBのページ) にグループ化されます。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.10.2 ファイル領域管理
このエクステントの状態を可視化してみます。
https://github.com/jeremycole/innodb_ruby/wiki#space-extents-illustrate
| innodb_auto_increment | innodb_integer | innodb_uuid | innodb_ulid |
|---|---|---|---|
 |
 |
 |
 |
UUID以外の場合は、ページの中にデータが埋まっている状態ですが、UUIDだと、不完全にデータが埋まっておりページ数、テーブルサイズの増大を引き起こしています。
個々のページが正方形できれいに整っている => ページが埋まっている
個々のページが凸凹になっている => ページが埋まりきっていない
innodb_ruby/index.rb at 9661178f0919876df09c209b3fa9080e10084852 · jeremycole/innodb_ruby · GitHub
インデックスレコードが順次 (昇順または降順) に挿入されると、インデックスページの約 15/16 までがいっぱいになります。レコードがランダムに挿入された場合は、ページの 1/2 から 15/16 までがいっぱいになります。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.2.13.4 InnoDB インデックスの物理構造 から引用
また、ランダムINSERTではページに入りきらなくなったページを分割する処理が頻発するので、CPU、IOの負荷が上がります。
LSNの可視化
MySQL InnoDBのpageの中にあるLSN(Log Sequence Number)は増加し続ける値で、InnoDBがredoログに50バイト書き込めば、LSNも50バイト進むようになっています。
https://dev.mysql.com/doc/refman/5.6/ja/glossary.html#LSN
InnoDBログシーケンス番号(LSN)を進める方法 | Yakst
MySQL :: MySQL Internals Manual :: 22.2.1.1 Fil Header
このLSN(Log Sequence Number)についてinnodb_rubyを使って可視化しました。
https://github.com/jeremycole/innodb_ruby/wiki#space-lsn-age-illustrate
| innodb_auto_increment | innodb_integer | innodb_uuid | innodb_ulid |
|---|---|---|---|
 |
 |
 |
 |
青色であるほど過去に更新されたことを、赤色であるほど最近更新されたことを示しています。
この比較とみると、UUID以外はLSNの更新はページが新しいほど直近に更新されているのですが、UUIDだけはほぼ全てのページが直近に更新されています。
これはランダムINSERTにより、各ページに均等に更新が入っているためです。
10万行を追加していく処理にかかった時間の計測
UUIDをPKに使った場合段々とINSERTする時間が少しずつ長くなっています。
しかし、下記ブログのベンチマークと比較すると、すでにINSERTされている行数が7500万行になってもUUIDにおいてINSERTする時間の増大が顕著ではありません。
これはベンチマークに使ったマシンスペック(Mac)によるものと、後述するバイナリログを出していた影響の両方と思っています。
ストレージはSSDです。
HDDと比較するとSSDのランダムアクセスのパフォーマンスがよいから劣化しにくいというのもあるかもしれません。
マシンスペック
% system_profiler SPHardwareDataType
Model Name: MacBook Pro
Model Identifier: MacBookPro13,3
Processor Name: Quad-Core Intel Core i7
Processor Speed: 2.6 GHz
Number of Processors: 1
Total Number of Cores: 4
L2 Cache (per Core): 256 KB
L3 Cache: 6 MB
Hyper-Threading Technology: Enabled
Memory: 16 GB
ibdファイルのサイズを見てみると、UUIDをULIDと比べても容量を多く使うことになりそうです。 https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_ibd_file
ls -alh /usr/local/var/mysql/bench_work total 15G drwxr-x--- 10 hoge hoge 320 2 26 04:25 . drwxr-xr-x 137 hoge hoge 4.3K 3 3 00:04 .. -rw-r----- 1 hoge hoge 1.8G 3 3 00:09 innodb_auto_increment.ibd -rw-r----- 1 hoge hoge 1.8G 3 3 00:09 innodb_integer.ibd -rw-r----- 1 hoge hoge 4.3G 3 3 00:09 innodb_ulid.ibd -rw-r----- 1 hoge hoge 6.9G 3 3 00:09 innodb_uuid.ibd
SELECT COUNT(*) にかかった秒数も残しておきます。
| innodb_auto_increment | innodb_integer | innodb_uuid | innodb_ulid |
|---|---|---|---|
| 1.66 sec | 1.51 sec | 39.08 sec | 3.63 sec |
こちらはULIDと比べると格段にパフォーマンスが落ちました。
ちなみに、MySQL 8以降だと、fmtファイルはなくなりデータディクショナリテーブルに格納されるようになりました。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 14 ♪MySQL データディクショナリ
INSERTに使ったスクリプト
require 'mysql2'
require 'securerandom'
require 'ulid'
require 'benchmark'
client = Mysql2::Client.new(host: "127.0.0.1", username: "root", database: "bench_work")
count = 100000
res_auto = []
res_int = []
res_uuid = []
res_ulid = []
counter = 0 # ここは更新しながらこのスクリプトを実行
points = 200
points.times do |p|
count.times do |t|
res_auto << Benchmark.realtime { client.query("insert into innodb_auto_increment values ();") }
counter += 1
res_int << Benchmark.realtime { client.query("insert into innodb_integer values (#{counter});") }
uuid = SecureRandom.uuid
res_uuid << Benchmark.realtime { client.query("insert into innodb_uuid values ('#{uuid}');") }
ulid = ULID.generate
res_ulid << Benchmark.realtime { client.query("insert into innodb_ulid values ('#{ulid}');") }
end
puts "#{count}\t#{res_auto.sum}\t#{res_int.sum}\t#{res_uuid.sum}\t#{res_ulid.sum}"
end
あとで気がついたこと
ベンチマークとっている最中に気がついたのですが、MySQL 8からバイナリログ出すのがデフォルトになっています。。。。
From MySQL 8.0, binary logging is enabled by default,
mysql> select @@global.log_bin; +------------------+ | @@global.log_bin | +------------------+ | 1 | +------------------+ 1 row in set (0.01 sec)
offにするには、my.cnfに skip-log-bin を入れて再起動します。
MySQL :: Re: Inserts in MySQL 8 are slower than Inserts in MySQL 5.7
mysql> select @@global.log_bin; +------------------+ | @@global.log_bin | +------------------+ | 0 | +------------------+ 1 row in set (0.00 sec)
バイナリログ onでベンチマークとっていたので、INSERT自体のパフォーマンスは全体的にそこまで良くなかったです。
参考値にバイナリログ offにして、7500万行入っているテーブルに10万件のINSERTしたときの結果を貼っておきます。
バイナリログ offにすることで、UUIDにおけるINSERTのパフォーマンスはそれ以外と差が広がりました。
2021/03/05 追記
innodb_uuid テーブルのSELECT COUNT(*) をもう少し速くできないかなと思ってセカンダリインデックスを追加してみました。
mysql> ALTER TABLE innodb_uuid ADD COLUMN for_index INT DEFAULT 1, ADD INDEX (for_index); Query OK, 0 rows affected (8 min 21.81 sec) Records: 0 Duplicates: 0 Warnings: 0
その結果
mysql> explain select count(*) from innodb_uuid; +----+-------------+-------------+------------+-------+---------------+-----------+---------+------+----------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+-------+---------------+-----------+---------+------+----------+----------+-------------+ | 1 | SIMPLE | innodb_uuid | NULL | index | NULL | for_index | 5 | NULL | 75209750 | 100.00 | Using index | +----+-------------+-------------+------------+-------+---------------+-----------+---------+------+----------+----------+-------------+ 1 row in set, 1 warning (0.01 sec)
となり、
mysql> select count(*) from innodb_uuid; +----------+ | count(*) | +----------+ | 75500000 | +----------+ 1 row in set (11.03 sec)
大体実行時間1/3くらいになりました。
performance_schema.file_summary_by_instanceなどをみてみましたが、バッファープールにデータが乗り切らずファイルI/Oがでていました。
2021/03/05 追記その2
MySQL8はUUIDのサポートが入っているのでこれはこれで試してみました。
mysql> SHOW CREATE TABLE innodb_uuid_bin\G
*************************** 1. row ***************************
Table: innodb_uuid_bin
Create Table: CREATE TABLE `innodb_uuid_bin` (
`id` binary(16) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)
UUID_TO_BIN() の第2引数はswap_flagで、UUID の1番目と3番目のグループを入れ替えてくれます。
UUID v1だと3番目にタイムスタンプの上位部分が入っているので入れ替えるとソートされた状態にできるためです。
今回使うUUIDはv4なので、swapしても意味ないので0にしています。
エクステント
| innodb_auto_increment | innodb_integer | innodb_uuid | innodb_uuid_bin | innodb_ulid |
|---|---|---|---|---|
| |
|
|
 |
|
LSN
| innodb_auto_increment | innodb_integer | innodb_uuid | innodb_uuid_bin | innodb_ulid |
|---|---|---|---|---|
| |
|
|
 |
|
サイズは小さくなっていますね。
ランダムINSERTなのは変わらずです。
2021/03/05 追記その3
ULIDを使う理由についてまとめました。
なぜPKにULIDを使うか。
— はるやま | Makoto Haruyama (@Spring_MT) 2021年3月4日
一番大きい理由は大量のデータがあるテーブルにおいて古い順に削除したいとき、
作成日でインデックスが効かない(uuid, created_atのindexとか)
ALTERも今更打てない
そんなときにPKがソート可能だと助かることがあること。
大体3年位たって発覚する。
パフォーマンスも含めて、UUID or ULIDならULIDを選ぶほうがよいと思います。
2021/03/08 追記その4
UUID_TO_BIN()を使ってベンチマークとりました。
mysql> SHOW CREATE TABLE innodb_uuid_bin\G
*************************** 1. row ***************************
Table: innodb_uuid_bin
Create Table: CREATE TABLE `innodb_uuid_bin` (
`id` binary(16) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)
クエリは INSERT INTO innodb_uuid_bin VALUES (UUID_TO_BIN('#{uuid}')); としています。
UUID(BIN)以外は同じ条件ではベンチマークを実行していないので参考程度に考えてもらえればと思います。
UUIDをchar(36)で保存するときに比べてパフォーマンスの劣化が起こってないように見えます。
UULDを使う場合よりはパフォーマンスは落ちそうです。
ibdファイルのサイズは7500万行INSERTして4.4GBでした。
DBのサイズはULIDと同じくらいのサイズになりました。
参照
MySQL :: MySQL Internals Manual :: 22.2.1 High-Altitude View
https://blog.jcole.us/category/mysql/innodb/
UUIDs are Popular, but Bad for Performance — Let’s Discuss - Percona Database Performance Blog
MySQL with InnoDB のインデックスの基礎知識とありがちな間違い - クックパッド開発者ブログ
PK/Unique KEY が UUID v4 だと INDEX 作成効率が悪い - id:onk のはてなブログ
mysqlでUUIDをprimary keyにしたいんだけど知見が無いのでどんなもんなのか雑に知見が欲しい。良い/悪い, どうしたらうまくいく、どういうのは苦手、とか。
— シンノスケ (@cnosuke) 2019年8月28日
