SQLアンチパターン読書会 「メタデータトリブル」 に参加しました

2013/8/29(木) SQLアンチパターン読書会 「メタデータトリブル」に参加してきました。

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

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

SQLアンチパターン (2013/01/26) Bill Karwin 商品詳細を見る

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

参加者は10人です。
初めての方が1名いらっしゃいました。新しい風が入るのは良いですね。
ということで久々に自己紹介をやったんですが、テーマは「夏休み」。
・・・皆さん、夏休みほとんど取れてないようで（笑

今回は8章「メタデータトリブル（メタデータ大増殖）」がターゲットでした。
ちなみに「トリブル」は8.2節にも紹介ありますが、アメリカのSFテレビドラマ「スタートレック」シリーズに登場する、架空の動物だそうです。
こんなのｗ　↓

（出展： http://f.hatena.ne.jp/Kumappus/20080421131814）

8章のタイトル、メタデータがトリブルのように増殖する、というイメージなんでしょうね。

ちなみにちょうど今、映画「スター・トレック イントゥ・ダークネス」も公開中だったりします。
私、偶然ですが夏休みに両親と観てきました。しかも先行公開の初日に。とても面白かった！

「スタートレック」シリーズを知らない人でも問題なく楽しめる作品です。オススメ。
・・・と、私は自己紹介でこの夏休みネタを紹介したのであった。

ちなみに @t_wada さん曰く、洋書は「スターウォーズ」ネタや「スタートレック」ネタが多く出てきて翻訳が大変だそうです（笑

---

■アジェンダ

Sqlアンチパターン（メタデータトリブル） from Tomoaki Uchida

@tonyuchi さんがスライドを作成して発表してくださいました。
検証や考察まで入っていて、素晴らしい出来です。
最後のページの「テストデータトリブル」とか、実に興味深いですねー

---

■ディスカッション

いつものように、ディスカッションしたいネタを各自、付箋に書き出して模造紙に貼りました。


以下、つらつらと個人メモ。

■メタデータへのデータの混入
・「メタデータ」とは、テーブル名やカラム名のことを指す。DBに格納する値自身ではなく、メタなデータ。
・「メタデータへのデータの混入」とは、テーブル名やカラム名に、西暦などのデータが混入すること。
・例：Bugs_2008


■メタデータトリブルとデータ規模
・そもそも扱うデータが少ないと、メタデータトリブルは検討する必要さえないかもしれない。
・例えば企業内システムとか、それほど規模が大きくないシステムとか。


■シャーディング
・データベースを「Shared Nothing Architecture」にすることらしい。
・Googleがスケールアウトするアーキテクチャに「sharding」と命名したことが、広く知られるキッカケになった。
・PostgreSQLはシャーディングの設定が、他のDBMSに比べて大変らしい。
・シャーディングはディスクを物理的に分けないと旨みが無い。
・なおDBを扱う人からは、ディスクが分散されていることが隠蔽され見えない。
・SQL ServerはEnterprise Editionじゃないとシャーディングできないらしい。
・Oracleだとシャーディングのことをパーティショニングと呼ぶようである。
・ググったらこの辺の記事が参考になりそう。

　Agile Cat — in the cloud　　Database Sharding _1
　http://agilecatcloud.com/2009/06/28/database-sharding-_1/


■MyISAMとInoDB
・P.98の8.5.2節に「MyISAMストレージエンジン」という単語が出てくるが詳しくないので質問してみた。
　→ MySQLにはMyISAMとInoDBという二つのストレージエンジンがあるとのこと。
・MyISAM ・・・全文検索は速いがトランザクションや外部キー制約をサポートしない。テーブルロックになる。
・InoDB ・・・ トランザクションをサポート、外部キー制約が装備されている、行ロックでいける。
・最近はMyISAMとInoDBの性能は変わらなくなってきた。
・MyISAMとInoDBの違いについてはこの辺の記事が参考になりそう

　MySQL、MyISAMとInnoDBを選ぶ方法
　http://news.mynavi.jp/news/2009/03/27/048/index.html


■シャーディングとPreparedStatement
・PostgreSQLでシャーディングを採用した際、PreparedStatementのバインド変数に最後まで何が入るか分からず、実行計画がFull Scanになったことがあった。
・サーバ側でSQLの実行計画を立てる場合は、バインド変数に何が入ってくるかわからなのでディスク振り分けができなかったらしい。
・ORACLEだと「地域」と「時間」といった2段階でシャーディングができるらしい。


■水平パーティショニングと垂直パーティショニング
・垂直パーティショニングはテーブル分割が絡むので、設計の話になる。
・水平パーティショニングはSQL標準ではないので、ベンダ依存になるため調べる必要がある。
・ORACLEはALTER TABLEで途中からパーティショニングを導入できるらしい。
・MySQLはテーブルのCREATE時にパーティショニングのオプションを指定する必要がある。
・垂直パーティショニングはディスクがスカスカになるのを避けられる。
　→　BLOBカラムにはデータが入ることが少ないので、別テーブルに切り出すことで必要最小限になる。


■8.2.4節「一意性の保証」
・ROLLBACKの指定は、直前のINSERTを無効化するため。（データ容量が増えないようにする）
・ROLLBACKされても、INSERTされた際に採番されたSIRIALのNext Value（連番ID）は、LAST_INSERT_ID()関数で取得できる。
・8.2.4節は、複数のテーブルに跨って主キーの重複を防ぐための仕組みで、シーケンスオブジェクトが無いMySQL独特のやり方。
・ROLLBACKは無くても動作するが、データが増えるので付けている。
・LAST_INSERT_ID()関数は、BEGINからENDまでのトランザクション内で最後に取れたIDを返す関数。
・一意採番の仕組みはDBMS依存になる。


■8.5節の解決策の３つの選択指針
・3案のどれを採用するかは、性能要件とデータ要件による。
・8.5.1節「水平パーティショニング」は、スケーラビリティの話。
・8.5.1節「水平パーティショニング」と8.5.2節「垂直パーティショニング」は、人力の話。
・これらで出来ない場合の話は、8.5.3節の「従属テーブルの導入」。これは正規化の話。
・水平パーティショニングが出来るか否かは、テーブル構造による。
　→　例えばテーブルが1個だけど水平パーティショニングは楽である。
　→　だが、普通はテーブルをJOINしてデータを取得するので、関連するひとかたまりのデータをシャーディングして近い所に置けるかどうかで決める。
・機械的に分割できるキーがあるかどうかで水平パーティショニングが使えるかどうかが変わる。
・Googleのアーキテクチャは、アグリゲートルート単位でトランザクションが発行される。
・データ構造が、起点とするデータを頂点とするツリー構造になっていれば、水平に分けやすい。
・RDBの機能として暗黙的に分割するかアプリで分割するかによるが、水平パーティショニングは割り易い/割りにくいが圧倒的にある。


■マルチテナントアーキテクチャ
・マルチテナントアーキテクチャでは、同じテーブル上に複数の契約会社のデータを入れる。分割キーは"テナントID"などを使い、キーで内部的に分けている。
・DB操作の際は、必ず"テナントID"がSQLのWhere区に入るので、同じテーブルを共有しているけど、データは絶対に混ざらないようにしている。
・このアーキテクチャでは、テナントIDがシャーディンの際のの分割キーになる。
・シャーディングには何種類かあるが、ハッシュでテナント毎に保存域を散らすように設計する。
・このように、シャーディングしやすいキーがデータとしてあるかどうか、水平分割しやすいデータ構造になっているかで、水平パーティショニングをするかどうかを決める。


■スケーラビリティ
・スケールアウト：　水平パーティショニング
・スケールアップ：　構成は同じまま、CPUとかをパワーアップさせる
・スケールバック：　あまり使わないデータを履歴系テーブルに逃がす


■水平パーティショニング採用時の注意点
・水平パーティショニングという、DBMS固有の機能にあまり期待し過ぎない方が良い。
・1テーブルだけ見れば良いことは多いが、完全に透過的にデータが散るかというと、全体的にはそうはいかない。
・シャーディングを実現するには、MySQL SpiderエンジンのようにOSSの外部で提供されている場合と、OSS同梱の場合とがある。
・DBMS依存の部分は自分で調べたり、検証する必要があり、自己責任となる。

---

★感想：
私も水平パーティショニングは業務で使ったことがありました。
全国26,000の郵便局のデータを一括集計する締め処理をシャーディングで分散させる際、レンジ分割を使ったんですが、新宿郵便局とか特定局のデータがデカすぎて、そこがボトルネックになって性能問題になってしまった。。。
分散するだけじゃ当然ダメで、データ量に合わせてきちんと設計しないとダメな典型ですね。

私の場合、個人的には正規化が染み付いてるので、まずは8.5.3節の「従属テーブル」を第一の採用候補で考えると思います。
たぶん自然と第三正規系にすれば従属テーブルに行き着くはず。

そのうえで、物理的にディスクI/Oを分散したいとか、性能がヤバげな場合に水平分散を検討することになるかなぁ。
その場合、水平分散への構造変換はALTERでできないこともあるようなので、移行手順とかきっちりしとかないとですね。
その際は書籍「データベースリファクタリング」の出番かもしれない。

他に、@t_wada さんオススメの一冊はコレだそうです。

『SQL アンチパターン』読書会は8章「メタデータトリブル（メタデータ大増殖）」を読みました。スケーラビリティとシャーディング設計の説明のため久しぶりに読んだ『実践ハイパフォーマンス MySQL 第2版(原書は第3版もあり)』の第9章が詳しく、これも良書だと再認識。 #sqlap

— Takuto Wada (@t_wada) August 29, 2013

実践ハイパフォーマンスMySQL 第2版 (2009/12/14) Baron Schwartz、Peter Zaitsev 他 商品詳細を見る

参加者の皆様、ありがとうございました～