DoorKeeper
http://sqlap.doorkeeper.jp/events/5065
以下の書籍をターゲットとした読書会なのです。
![]() | SQLアンチパターン (2013/01/26) Bill Karwin 商品詳細を見る |
場所はいつもの湯島、アルティネットさんです。
参加者は9人かな。
今回は7章「マルチカラムアトリビュート(複数列属性)」が範囲でした。
■模造紙&付箋
いつもと同じく、ディスカッションしたいネタをみんなで最初に書き出しました。

あと、今回のアジェンダスライドは @naopi さんが作成して説明してくださいました。感謝!
以下、写経&読書会のメモ。
写経用に用意したSQLはコチラ: 20130808_sqlap_Multi-Column.txt
特に、興味深い以下3点を中心に写経してみました。
(1) [7.2.2節] DBMSの違いによるadd-tag.sql挙動調査 (MySQL, ORACLE, HiRDBの3種類)
(2) Array型カラム
(3) [7.5節] 解決策:従属テーブル方式への具体的な移行方法
順に書いてみる。
■[7.2.2節] DBMSの違いによるadd-tag.sql挙動調査
(MySQL, ORACLE, HiRDBの3種類)
今回、写経をしてきた @tonyuchi さんから面白い報告がありました。
7.2.2節の add-tag.sql の挙動が、ORACLEだと書籍どおりにならないとのこと。
■7.2.2節 [add-tag.sql]
UPDATE Bugs |
これは面白そうだ!ということで、私も以下3種類のDBMSを使って、試してみることにしました。
(1) MySQL 5.1.37
(2) Oracle Database Express Edition 11g Release 2
(3) HiRDB Server Version 9 (09-00-T1)
---
(1) MySQL 5.1.37
①SQL実行前
tag1, tag2, tag3にNullをセットしておく。

②SQL実行後

tag1のみ、'performance'が代入されました。
これは書籍どおりの挙動ですね。
(2) Oracle Database Express Edition 11g Release 2
①SQL実行前
tag1, tag2, tag3にNullをセットしておく。

②SQL実行後

おお! @tonyuchi さんが言っていたとおり、tag1~tag3が全部、performanceになりました。
これは書籍とは異なる挙動ですね。
(3) HiRDB Server Version 9 (09-00-T1)
①SQL実行前
tag1, tag2, tag3にNullをセットしておく。

②SQL実行後

ありゃ、エラー・・・
エラーメッセージID "KFPA11417-E" でマニュアルを見てみると、

IN述語の左側のオペランドに定数はダメとのことwww
HiRDB、氏ねよwww
読書会でもP.83の search-two-tags.sql で「IN述語ってこんな使い方もできるんだねー」と話題になってたんですが、
どうやらこれはMySQLとOracleはOKで、HiRDBだとNGらしい。SQL標準じゃないのかな?
---
原因は、トランザクション分離レベル?
HiRDBはさておき、和田さん曰く、MySQLとORACLEで挙動が異なる原因としては「トランザクション分離レベル」の差異が怪しいとのこと。
トランザクション分離レベルは4種類あります。IPAのデータベーススペシャリスト試験でよく出題される奴ですね。
・SERIALIZABLE ( 直列化可能 )
・REPEATABLE READ ( 読み取り対象のデータを常に読み取る )
・READ COMMITTED ( 確定した最新データを常に読み取る )
・READ UNCOMMITTED ( 確定していないデータまで読み取る )
トランザクション分離レベルについて詳しく書かれているORACLEのマニュアルはこちら。
http://docs.oracle.com/cd/E16338_01/server.112/b56306/consist.htm
これについてはちょと調査に時間かかりそうなので、ここではいったん保留する。
なんかわかったら後で追記するかも。
■Array型カラム
マルチカラムアトリビュートって第1正規形だよね~、とかディスカッションしてる最中にふと思い出したのが、Array型カラムです。
そういやHiRDB(笑)にあったなぁと。あとで付箋を追加した件です。
なので試してみる。
■Array型カラムを持つテーブルの作成
こんなカンジでArray型カラムを作れます。tag1,tag2,tag3ではなく、tag Array[3]。

■Array型カラムへのInsert
こんなカンジでInsertできます。

■Array型カラムのSelect
こんなカンジでSelectできます。

HiRDBのArray型カラムのマニュアルはこちら。
http://www.hitachi.co.jp/Prod/comp/soft1/manual/pc/d645140/W4510057.HTM
ちなみに配列(ARRAY)型はSQL99でサポートされているそうです。
■オブジェクト指向、Javaを取り入れた新しい業界標準「SQL99」詳細解説
第二章 柔軟さを増したデータ構造(1)
http://www.atmarkit.co.jp/fnetwork/tokusyuu/01sql99/sql99_2a.html
HiRDBとPostgreSQLではサポートされていることを確認しましたが、MySQLとORACLEはサポートしてないみたい。
■[7.5節] 解決策:従属テーブル方式への具体的な移行方法
書籍の7.5節に書いてある解決策ですが、具体的な手順までは書いてません。
なので、ディスカッション時のネタとして付箋に書いておいたら、和田さんにご教示いただけました。
今回の考え方としては、横縦変換をする、というイメージですかね。
基本は書籍「データベースリファクタリング」に書いてある手順がベースです。
実際にやってみた。
■手順1:移行用テーブルの準備
まず、Bugsテーブルの移行先として、Bugs_NewテーブルとTags_Newテーブルの2つを用意する。
CREATE TABLE Bugs_New ( |
■手順2:移行用テーブル Bugs_New に、BugsテーブルのBugデータのみ移行
次に、用意した移行用テーブル Bugs_New に、BugsテーブルのBugデータのみ移行する。
INSERT INTO Bugs_New (bug_id, description) |
確認してみると、Bugsテーブルのバグ情報だけがちゃんと移行できているのがわかります。

■手順3:移行用テーブル Tags_New に、BugsテーブルのTagデータのみ移行
次に、移行用テーブル Tags_New に、BugsテーブルのTagデータのみ移行する。
INSERT INTO Tags_New (bug_id, tag) |
UNIONを使うのと、Null以外のタグを取ってくるようにするのがポイントです。
確認してみると、Bugsテーブルのタグ情報だけがちゃんとTags_Newテーブルに移行できているのがわかります。

Bugs_NewテーブルとTags_Newテーブルはまだ誰も使ってないので、堂々とINSERTできます。
■手順4:移行期間中の対処
最後に「移行期間」を設け、その期間はBugs, Bugs_New, Tags_Newの3テーブルを共存させます。
んで、移行期間中にアプリ側の改修を行います。(Bugsではなく、Bugs_NewとTags_Newを使うように改修)
移行期間中も移行元のBugsテーブルへは当然アクセスがあります。
なのでBugsテーブルのデータが更新されたら、それをBugs_NewテーブルとTags_Newテーブルに反映させるための仕組みを用意します。
「データベースリファクタリング」で紹介されている手順のうち、トリガー等を使うことで実現できそうです。
■ディスカッションのメモ
ディスカッション時に出たお話のメモ。
■なぜマルチカラムアトリビュートのような設計になるのか
・RDBを、Excelのような表計算ソフトの2次元と同じように捉えている。
→ 横に長く表示されてるから、そのままDBに格納してしまえ。
・RDBは集合論、という認識が無い。
■正規化
・マルチカラムアトリビュートをやってしまう他の原因としては、正規化理論を理解していない点が挙げられる。
・まずは正規化すべき。
・正規化理論は付録Aに書いてある。第1正規形の例としてマルチカラムアトリビュートも紹介されている。
■COBOLとマルチカラムアトリビュート
・COBOLのレガシーシステムを横展開してもってくるとマルチカラムアトリビュートになる。
・というのも、ホストコンピュータの世界はCOBOLが多く、データを固定長で扱うことが多かった。
→ カラムを後から足せない、という制約があった。
→ じゃあ「予め拡張用の幅を事前に持たせておこう」ということで、予備カラムを事前に用意する設計にする。
→ この思想をオープン系にそのまま持ってくるとマルチカラムアトリビュート。。。
■マルチカラムとカラム名
・書籍のBugsテーブルの例だと、tag1とtag2を入れ替えてもシステム的に影響ない。それはtagたちが平等だから。
・タグ付けするという問題と、ユーザを3人紐付ける、という問題は若干違う。
・後者の悪い例としては、user1, user2, user3のようなカラム設計をした場合。
→ 3人のUserの関係性を示すものがカラム名にないので、設計ドキュメントで示すしかない。
→ そうじゃなく、せめて関係性を示すカラム名をつけましょう。
・書籍P.xxiiのBugsテーブルは、reported_by, assigned_to, verified_byと3名の関係を示すカラム名にしている。
・このように3カラムにそれぞれ別名を付けられるなら、各カラムには同じ種類のデータが入ることがわかる。
・user1, user2, user3のように順序性に意味を持たせる設計はダメ。
→ 全部のレコードが各カラムで同じ意味をもつなら、上記のBugsテーブルのように、カラムに意味を持たせましょう。
・レコードによってマルチカラムの各カラムの使い方が変わる場合は、5章のEAVで紹介されていた解決策(テーブル継承)を使いましょう。
■マルチカラムアトリビュートと性能
・DBの設計の基本は、まず正規化。
・でもマルチカラムアトリビュートを正規化すると、SELECT時にJOINが必要になるので性能が落ちるのでは?
→ 今はそうではない。性能が悪いなら非正規化しろ、というのは昭和の発想(笑
・非正規化で性能を出したいくらいなら、RDBではなくキーバリューストアとかのNoSQLを使用すべき。
・非正規化しよう、というのはRDBを知らなかった時代の発想。
・MySQL 5.4ではJOINの性能改善が実施されている。
■SQLに改行を入れておき、ログを見やすくする・・・?
・ログを見やすくするためにSQLに改行を埋めておく、というエンジニアがいた。。。
・SQLに改行を入れてしまうと、デバッグや運用時にログが拾いにくくなる。
→ ログをGrepしても、SQLの断片しかヒットしなくなり、使い勝手が著しく落ちる。
→ ログは1情報を1行で出力しましょう。
■マルチカラムに1つだけは必ずデータを入れさせたい場合は・・・?
・マルチカラムのうち、1個だけ必須(Not Null)にしたい場合がある。
→ Null不可とNull可の用途で、カラムを分ける?
・それはダメ。Null不可とNull可で、それぞれテーブルを分けるべき。
★そういや今気づいたが、従属テーブルを導入すると、バグに1つ以上のタグを必ず入れさせたい場合の制約が掛けられなくなる・・・?
■ORM
・ORMを使うと、更新時は全カラム更新が基本らしい。オブジェクトの方が偉い、という思想に基づくらしい。
・S2JDBCは全カラム更新と部分更新が選べるらしい。
・ORMは内部的にLEFT JOINしてくれる。
・やってはいけないのが、「N+1問題」。
→ 発行されるSQLがN+1回になって、パフォーマンスが凄い事になる。。。
■P.84のremove-tag.sql
・このSQLはイキナリ感があるが、要するに'performance'タグが付いているカラムをNULLに設定するSQL。
・'performance'タグを外したいときに使う。
・どのカラムにperformanceタグが入っているか知らなくても消せるのが利点。
■交差テーブルか従属テーブルかの選択
・7.5節では、解決策として「従属テーブル」を上げている。
→ 従属テーブルは交差テーブルと違い、異なるbug_idに対して似たようなtagがたくさんできる。
→ 複数のbug_idに紐づく同じtagの名称を書き換えたい場合、手動で複数個を修正する必要がある。
・従属テーブルでなく交差テーブルにした場合、Tagsテーブルはカラムとしてtag_idとtag_nameを持つ。
→ tag_nameを書き換えると、そのtag_idに紐づくバグのタグ名がすべて書き換わる。
・タグ名をかえると全部変わるようにするか(= 交差テーブル)、ぜんぶ手動で変更しないといけないようにするか(= 従属テーブル)は、用途に合わせ設計で決める。
■あえてマルチカラムアトリビュートにする場面
・「タグは絶対に3個までなんだ!」という個数情報をカラム数で示したいために、あえて正規化しない判断もあるのでは。
・要するに、あえてマルチカラムアトリビュートにして、カラム数を物理的に制限したい場合とか。
→ カラム数で制限することが有効に働くドメインにあるか、きちんと考えることが重要。
★感想:
今回も大変勉強になりました。
特に、写経の結果がいろいろ面白かったです。
CASE文の結果やIN句の使い方が、DBMSにより実行結果に差が出たりとか、個人的にArray句を試してみたりとか、実際に7.5節の解決策を手順に落としてやってみたりとか。
マルチカラムアトリビュートは経験者が多いということもあり、ディスカッションも盛り上がりました。
本日の『SQL アンチパターン』読書会「7章 マルチカラムアトリビュート(複数列属性)」は、ベタなアンチパターンでみんな身に覚えがあったためか、なかなか盛り上がりました。 #sqlap
— Takuto Wada (@t_wada) August 8, 2013
まさしく!
みなさま、ありがとうございました~
- 関連記事
-
- 「Developers Summit 2013 Summer」に参加しました ~其の1~ 基調講演
- SQLアンチパターン読書会 「マルチカラムアトリビュート」に参加しました
- 「TDD Boot Camp Tokyo 2013-07」に参加しました