🕌

idをautoincrementして何が悪いの?

2022/02/06に公開約3,100字12件のコメント

idをautoincrementしない方が良い理由

こんにちは。株式会社プラハCEO松原です。

最近プラハチャレンジの参加者とお話している際に

「PKのidはautoincrementするとして...」

とナチュラルにid=autoincrementするものという前提が見えたので、「本当にidをautoincrementしても良いものだろうか?」と気になったことを書いてみようと思います。もしフレームワークが自動的にautoincrementでテーブルを作るからなんとなく使っているという方がいたらご一読いただいた後、それでも連番を使いたい理由があれば教えて欲しいです・・!

不必要に情報を晒すことになる

スクレイピングされたり

もしも僕が某大手に勤めているエンジニアで「競合サービスAにのってる物件情報、全部コピーして新しいサービス作ろうぜ」と指示されたらですよ?「人としてそれはやっちゃダメです」って反対したのに「やったら年収3000万円にしてあげる」って言われて渋々競合サービスをスクレイピングしようとした時ですよ?

hoge.com/bukken/1
hoge.com/bukken/2
hoge.com/bukken/3

みたいなurlだったらめちゃくちゃスクレイピングしやすくてハッピーですよね。普通はurlのidが推察できないから「一覧画面->物件クリック->抜き取る->一覧画面」みたいな挙動を繰り返す必要があるのに、idが連番になってたら物件情報がなくなるまで簡単なfor文を回せば済むのでめちゃくちゃ楽に3000万円ゲットできます

「URLのidとDBのidが必ずしも対応しているとは限らないのでは?」と言われたら確かにそうなんですが、そんな面倒な対応表(urlのidとDBのidのマップ)を作るならuuidで良さそう

競合に色々ばれる

そこまで悪どいことをする会社じゃなかったとしても、競合調査は普通にやりますよね。そんな時も連番idなら競合サービスに何件物件が登録されているかわかるので非常に便利です。何なら毎週連番idが何番で途切れるかみておけば競合の営業スピードも分かります

炎上のタネになる

あと「会員登録者数No.1!」みたいな広告の謳い文句が実は「会員登録者(退会者、仮会員登録、LINEともだち、昔別ブランドで運営してたサービスの登録者を含んだら)No.1!」みたいなケースも結構あります。こういう時に連番idだとすぐ実態がバレて「(悲報)Aサービス実際は308人しか登録してない件www」とか5chのネタにされます

攻撃しやすい

URLが予測しやすいとセキュリティ上の問題もありそうです。/user/1に成功した攻撃を繰り返そうと思えば単純なfor文で /user/2, /user/3, /user/4に攻撃すれば良いわけですから。URLさえ分かれば攻撃可能なところまで来たらお終い感はあるので些細な違いですが、予測しづらいidだったらidを特定するひと手間が加わるので少しはマシかもしれません

検知の観点からもuuidだったら何百万回もidを取っ替え引っ替えアクセスしないとヒットしないので(モニタリングしていれば)すぐ不正アクセスに気づけます。これが連番だと生みの親すら使っていない悲しいサービスを除いてid=1は必ず1回目の挑戦でヒットして、以降100件のデータがあれば/user/101でヒットしなくなるまでデータ件数+1程度の少ない試行回数で済んでしまうので、不正アクセスを検知しづらそうです

サービス合併した時どないする?

企業Aが企業Bを買収してサービスを合併したい時、もしuuidだったら双方のDBで衝突していなければそのまま同じidを使いまわせるけど、それぞれ連番だとidの振り直しが必要になる可能性は高そうです。連番はあくまで自分のサービスのDB内でのみuniqueなので

もちろんuuidも衝突する可能性はあるんですが、uuidの衝突可能性は限りなくゼロにちかいのに対して連番はほぼ確実に衝突しますよね。あらゆるサービスがid=1から始まるので

insertが完了するまでidがわからない

処理に時間がかかる場合、先にidだけ返して永続化処理は非同期に実施することが時々ありますよね(画像アップロードとか)

こういう時もDB側での採番だと実際DBにinsertするまではidが確定しないので、本当は今この瞬間にinsertする必要がなくても採番するためだけにDBとのI/Oが発生することになります。同時にたくさんのファイルをたくさんのユーザーがアップロードするよ!みたいな機能を作りたい時にちょっとパフォーマンスが心配ですよね

また、必要なデータが用意できていなくても採番のためにinsertしなければいけない状態になるとカラムのNULL許容が必要になったり、テーブル設計面にも影響が及びそうです

本当にuniqueか?

「autoincrementされたidがuniqueであることはドキュメントに書いてあります。何が悪いんですか?言いがかりですか?人としてみっともないとは思いませんか?」と思われるかもしれませんが、サービスが大きくなってきて複数のDBインスタンスが稼働し始めた時(例えばテーブルを時系列にパーティショニングして古いデータを安価なDBインスタンスで稼働させるとか)、uniqueなidであることを複数のDBを横断して担保する必要があります。ユーザーが9999人登録しているサービスがあって、DB1とDB2に同時にinsertされて、どちらもid=10000を採番したらPKが被ってしまいますから

DB側の機能で複数DB間でもautoincrementの整合性が自動的に取れるかもしれませんが、僕はあまりDBに詳しくないためネットワーク越しに連番を保証する際にどれだけパフォーマンス劣化があるのか、深く理解できていません

(どでかいトランザクションの中でDB1のuserにinsertするとして、同時にDB2がuserにinsertしようとしたらDB2の採番はDB1のトランザクションのコミットまで待たされるようだとだいぶ遅くなりそう。それともDB1のinsertは成功するものと見越して次のidを渡してくれるのか?DB3にinsertしようとしたらDB1の成功とDB2の成功を見越して次の次のidを渡すのか?だとしたらDB1のinsertに失敗した時にidが歯抜けになるから連番の意味はないのではないか?とか)

それなら連番であることを担保する必要のないuuidあたりをidとしてPKに使ったほうが良いんじゃないかな・・・と思ったり

そもそもなんで連番使うんだっけ?

僕は上記のデメリットから連番idをサービスに採用することは無く、大体uuidを使いますが、逆に連番を採用するメリットがあればコメントで教えてもらえると嬉しいです!

Discussion

逆に連番を採用するメリットがあればコメントで教えてもらえると嬉しいです!

例えばMySQL(InnoDB)の場合Secondary indexにPrimary値を挿入しますが、intの方がUUIDよりもバイトサイズが小さいのでB木の高さが減るなどパフォーマンス上有利です。

またUUIDの場合、値がほぼランダムにリーフに格納されるため、時系列でデータを取得する場合はリーフを参照する回数が格段に増加します。基本的にパフォーマンスとしてはauto incrementの方が有利で、それも一般に数十%ほどの違いがあると言われています。

例えばMySQL(InnoDB)の場合Secondary indexにPrimary値を挿入しますが、intの方がUUIDよりもバイトサイズが小さいのでB木の高さが減るなどパフォーマンス上有利です。

ありがとうございます!

またUUIDの場合、値がほぼランダムにリーフに格納されるため、時系列でデータを取得する場合はリーフを参照する回数が格段に増加します。基本的にパフォーマンスとしてはauto incrementの方が有利で、それも一般に数十%ほどの違いがあると言われています。

時系列でデータを取得する必要がある場合は作成日を格納したカラム等にindexを貼って対応していたのですが、こうした追加のインデックスがパフォーマンス的に許容できない時にauto incrementのpk値を使って時系列検索をする、という理解で合っているでしょうか・・・?

こうした追加のインデックスがパフォーマンス的に許容できない時にauto incrementのpk値を使って時系列検索をする、という理解で合っているでしょうか・・・?

セカンダリインデックスはPKを保持し、最終的にPKからリーフノードを特定するため、時刻でソートされたデータを取得するためのオーバーヘッドはセカンダリインデックスを追加しても存在します。
またUUIDのメリットを受容しつつ、ランダムにPKが点在することによるオーバーヘッドを防ぐためULIDがあります。

記事の寄稿ありがとうございます。
非常に楽しませてもらいました。

https://techblog.raccoon.ne.jp/archives/1627262796.html

こちらの記事が非常にUUIDを採択する際のメリデリについて書かれてたので、一読されるといいかもしれません。

結論として、インサートのパフォーマンス落としてしまうのはどうしても、トレードオフにはなりますよね。

セカンダリインデックスはPKを保持し、最終的にPKからリーフノードを特定するため、時刻でソートされたデータを取得するためのオーバーヘッドはセカンダリインデックスを追加しても存在します。

こちらの記事が非常にUUIDを採択する際のメリデリについて書かれてたので、一読されるといいかもしれません。

ありがとうございます!参考記事も拝読し、大変勉強になりました!

ありがとうございます。 UUID を採用する時に参考にしたい記事です。

もう一つ、私がメリットとして感じているのは、 join する時にミスると気づきやすい点かなと思います。
例えば

select * from users
inner join posts on users.id = posts.id -- posts.user_id が正しい

とか書いても間違いに気づかない可能性ありますが、 ( id = 1 の posts と users が存在すると、それっぽい結果が返ってきてしまう)、 UUID だと結果セットが 0 行になるはずなので

衝突可能性はこういうところにも影響するんですね、ありがとうございます!

特に意識したことは無かったですが

ID int
CreatedAt time.Time
UpdatedAt time.Time

あたりの変数は利用しているORMの仕様上、自動で作成されるためスキーマにIDは採用してました
ですが、IDとは別にデータを一意にするためのAccountIDなり、UserID, UIDをUniqueで入れてます
基本的にAccountIDを使ってデータ連携してますね

はじめまして。連番をPKとして外部に露出した際に考えられるリスクについて、とても参考になる記事でした。

逆に連番を採用するメリットがあればコメントで教えてもらえると嬉しいです!

パフォーマンス上のメリットが考えられるのは他の方のコメントの通りだと思います。それに加えて、アプリケーションの要件によっては「連番を外部に露出した方が便利」な場合もあると思います。

私も以下の記事を読んで気付いたのですが、例えば githubやRedmine等のissueやチケットの番号 は、コミットメッセージに転記したりチャットでやりとりしたりする都合上、長いuuidよりも連番IDの方が使いやすいです。

https://bytebase.com/blog/choose-primary-key-uuid-or-auto-increment

そもそもユーザーがPKを直接見たり入力したりするユースケースが、開発者向けツールや業務システムに限定されそうではありますが、アプリケーションの要件によっては連番の方が優れている場面もあるのかなと思いました。

アプリケーションの要件によっては連番の方が優れている場面もあるのかなと思いました。

おっしゃる通り連番がメリットになるケースもありそうですね!PKを考慮する際はUXも観点に入れて考慮してみようと思います、ありがとうございます!

この記事で挙げられている連番であることの問題を解消しつつ、64bit数値かつ登録順でソートできるID生成戦略として、Instagramの方法はなかなか参考になりますよ(当然ながら、ここで挙げられているUUIDのパフォーマンス問題とも無縁です)。

少々古い記事ですが、ご参考まで。

A Better ID Generator For PostgreSQL

https://rob.conery.io/2014/05/29/a-better-id-generator-for-postgresql/
Sharding & IDs at Instagram
https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c

instagramの事例も大変勉強になりました。大規模サービスを実際に運営されている知見は本当に参考になりますね、ありがとうございます!!

ログインするとコメントできます