fc2ブログ

makopi23のブログ

makopi23が日々の生活で感じたことを気ままに綴るブログです。

SQLアンチパターン読書会 「シュードキー：ニートフリーク(疑似キー潔癖症)」に参加しました

2014/8/19(火) SQLアンチパターン読書会 「シュードキー：ニートフリーク(疑似キー潔癖症)」に参加してきました。

DoorKeeper
http://sqlap.doorkeeper.jp/events/13963

以下の書籍をターゲットとした読書会なのです。

SQLアンチパターンSQLアンチパターン
(2013/01/26)
Bill Karwin

商品詳細を見る


場所はいつもの湯島、株式会社アルティネットさんです。
いつも会場提供ありがとうございます。

参加者は11人かな。新規の方がお一人参加されました。
この日は21章「シュードキー：ニートフリーク(疑似キー潔癖症)」がターゲットでした。

■ 発表
今回は @a_suenami さんが発表担当でした。
シュードキーニートフリーク from Akira Suenami

事業部再編でID付け替えが大変だった体験談や、PostgreSQLのUUIDの話とか、本にない話題が良いですね～

■ ディスカッション
今回もディスカッションしたいネタをみんなで付箋に書き出しました。
20140819_sqlap1.jpg

以下、個人メモ。
---

■ 自動採番

■ GUID (UUID)
  • UUIDが必要になるほどの厳密さを求められる場面はそれほど多くなく、AUTO_INCREMENTで十分なことが多い。
  • オートインクリメントだと、トラフィックが多くなると採番がボトルネックになって待ち行列に並ぶことがある。
  • Twitterはオートインクリメントを当初採用していたが、性能的に立ちいかなくなった。
  • 連番じゃなくていいいから時系列に並んでほしい、という場合に、UUIDかSERIALか、どちらを選ぶかの勝負になる。
  • UUIDはバージョンが5つある。バージョン１とバージョン４がよく使われる。
    • バージョン１：　MACアドレスとUUIDの生成日時によるもの。時系列性がある。
    • バージョン４：　疑似乱数によるもの。
  • UUIDが重複する可能性は0ではなく、天文学的な大きい数字を分母にすることで重複する確率を0に近づけている。
  • UUIDは16バイト必要なので、容量が大きくなることが欠点。


■ 読書会の前日くらいにQiitaで賑わっていた採番ネタ

■ 採番とソート順序
  • SELECT文で複数件を取得する際、必ずしもInsert順や採番順に並ぶとは限らない。
  • MySQLのInnoDBはクラスタインデックスなので大体は挿入順に表示されるが、MyISAMはそうとは限らない。
  • 挿入順序と表示順序をわけるため、表示順序制御用のカラムを追加することがある。
  • あとは、更新日付のカラムでソートする等。


■ 採番値と業務上の意味付け
  • 採番されたIDが顧客に見えると、とんでもないことになることがある・・・
  • 「社長はIDが１じゃないとダメだから、IDを更新して」みたいに、採番IDに職群等級を反映させようとしたり・・・
  • なので、自動採番されたIDは顧客から隠して、顧客に見せるためのIDを別に用意することがある。
  • これは本章の最後にもあるように、「技術でなく、コミュニケーションの問題」である。


■ システム統廃合と採番
  • システムの統廃合があると、IDが破綻することが多い。
  • システム移行で新旧システムを並行稼働させる場合、どちらにも採番しないといけないので大変・・・
  • 新システムの方はIDを100万から開始で連番を振る、といったように、範囲で新旧を分けることはよくやる。
    • ただし、欠番が発生する。
    • 範囲の設計をミスると新側が旧側の採番に追いつかれたりとか・・・・ｗ


■ マルチテナントアーキテクチャと採番
  • マルチテナントアーキテクチャの場合、同一テーブルに複数テナントが同居するので、テナントIDとかカンパニーIDで分ける。
  • そのため、各社がIDを奪い合って、必ずしも連番にはならない。
  • 他にも、契約会社ごとに採番テーブルを用意しないといけなかったりすることがある。
  • 結果として、５章のEAVとかになることがある・・・


■ シーケンスブロックのアーキテクチャパターン
  • まあまあ連番であってほしいけど並行性も高くしたい、という場合に使用できる。
  • 複数のノードを用意し、各ノードがIDをブロック（例えば10個とか）の単位で事前に採番し、キャッシュしておく。
  • クライアントは、各ノードに採番を依頼する。ノードが複数あるので、採番のアクセスを散らすことが可能。
  • ノードの採番キャッシュが尽きると、再度、そのノードは複数のIDをブロックとして纏めて採番し、キャッシュする。
  • 各ノードが1回で確保するIDのブロック幅は調整可能。
  • 利点は、ノードを複数用意することで一列待ちを発生させないようにできること。
  • 欠点は、シャットダウンするとキャッシュにある採番値が消えるので、欠番が発生すること。なのでブロック幅で調整する。
  • ググると、J2EEデザインパターンの「PK Block Generatorパターン」に似ているっぽい？


■ I/O競合の低減
  • 採番もI/O競合の低減が重要だが、I/O競合の低減を目的とした技術としてOracleの「逆キーインデックス」がある。
  • 採番テーブルをハッシュパーティションにすることで、論理的には連番だけど物理的にI/Oを散らすことが出来る。


■ 行のナンバリング（21.5.1節）
  • Limit句はSQL標準ではないが、使い方が簡単なので多くのDBMSに採用された。
★感想：
弊社のフレームワークにも、欠番を許す採番部品と欠番を許さない採番部品の2種類があります。
その使い分けについてはこれまであんまり考えたことがなかったのですが、この章を読んで腑に落ちた。
「擬似キーの欠番は埋めない」と言い切ってる点、あと、上司の説得の仕方まで言及してくれている点が秀逸。
あと、最後の節でコミュニケーションにまで解決策として言及している点が素晴らしいですね。
他にも、擬似キーと自然キーの使い分けについても考えさせられたりと、とても勉強になりました。

今回で21章が終わり、だいぶ終わりが見えてきた感じです。
参加者のみなさま、ありがとーございました。


■おまけ：過去の「SQLアンチパターン読書会」ブログ

1章：SQLアンチパターン読書会 「ジェイウォーク」に参加しました
　http://makopi23.blog.fc2.com/blog-entry-65.html

2章：SQLアンチパターン読書会 「ナイーブツリー」に参加しました
　http://makopi23.blog.fc2.com/blog-entry-70.html

3章：SQLアンチパターン読書会 「IDリクワイアド」に参加しました
　http://makopi23.blog.fc2.com/blog-entry-73.html

3章：SQLアンチパターン読書会 「続・IDリクワイアド」 に参加しました
　http://makopi23.blog.fc2.com/blog-entry-77.html

4章；SQLアンチパターン読書会 「キーレスエントリー」 に参加しました
　http://makopi23.blog.fc2.com/blog-entry-84.html

5章：SQLアンチパターン読書会 「EAV(エンティティ・アトリビュート・バリュー)」に参加しました
　http://makopi23.blog.fc2.com/blog-entry-90.html

6章：SQLアンチパターン読書会 「ポリモーフィック関連」 に参加しました
　http://makopi23.blog.fc2.com/blog-entry-94.html

7章：SQLアンチパターン読書会 「マルチカラムアトリビュート」に参加しました
　http://makopi23.blog.fc2.com/blog-entry-97.html

8章：SQLアンチパターン読書会 「メタデータトリブル」 に参加しました
　http://makopi23.blog.fc2.com/blog-entry-105.html

9章：SQLアンチパターン読書会 「ラウンディングエラー」 に参加しました
　http://makopi23.blog.fc2.com/blog-entry-109.html

10章：SQLアンチパターン読書会 「サーティーワンフレーバー」に参加しました
　http://makopi23.blog.fc2.com/blog-entry-115.html

11章：SQLアンチパターン読書会 「ファントムファイル」に参加しました
　http://makopi23.blog.fc2.com/blog-entry-118.html

12章：SQLアンチパターン読書会 「インデックスショットガン」に参加しました
　http://makopi23.blog.fc2.com/blog-entry-121.html

13章：SQLアンチパターン読書会 「フィア・オブ・ジ・アンノウン」に参加しました
　http://makopi23.blog.fc2.com/blog-entry-128.html

14章：SQLアンチパターン読書会 「アンビギュアスグループ」に参加しました
　http://makopi23.blog.fc2.com/blog-entry-130.html

15章：SQLアンチパターン読書会 「ランダムセレクション」に参加しました
　http://makopi23.blog.fc2.com/blog-entry-133.html

16章：SQLアンチパターン読書会 「プアマンズ・サーチエンジン」に参加しました
　http://makopi23.blog.fc2.com/blog-entry-134.html

17章：SQLアンチパターン読書会 「スパゲッティクエリ」に参加しました
　http://makopi23.blog.fc2.com/blog-entry-136.html

18章：SQLアンチパターン読書会 「インプリシットカラム」に参加しました
　http://makopi23.blog.fc2.com/blog-entry-138.html

19章：SQLアンチパターン読書会 「リーダブルパスワード」に参加しました
　http://makopi23.blog.fc2.com/blog-entry-140.html

20章：SQLアンチパターン読書会 「SQLインジェクション」に参加しました
　http://makopi23.blog.fc2.com/blog-entry-144.html


-->