SQLアンチパターン読書会 「マルチカラムアトリビュート」に参加しました

2013/8/8(木) SQLアンチパターン読書会 「マルチカラムアトリビュート」に参加してきました。

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

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

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

場所はいつもの湯島、アルティネットさんです。
参加者は9人かな。

今回は7章「マルチカラムアトリビュート（複数列属性）」が範囲でした。

---

■模造紙＆付箋
いつもと同じく、ディスカッションしたいネタをみんなで最初に書き出しました。


あと、今回のアジェンダスライドは @naopi さんが作成して説明してくださいました。感謝！

Sqlアンチパターン読書会 #7スライド資料 from Naoya Ishii

以下、写経＆読書会のメモ。
写経用に用意した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 SET tag1 = CASE WHEN 'performance' IN (tag2, tag3) THEN tag1 ELSE COALESCE(tag1, 'performance') END, tag2 = CASE WHEN 'performance' IN (tag1, tag3) THEN tag2 ELSE COALESCE(tag2, 'performance') END, tag3 = CASE WHEN 'performance' IN (tag1, tag2) THEN tag3 ELSE COALESCE(tag3, 'performance') END WHERE bug_id = 3456;

これは面白そうだ！ということで、私も以下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述語の左側のオペランドに定数はダメとのことｗｗｗ
HiRDB、氏ねよｗｗｗ

読書会でも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型カラム

マルチカラムアトリビュートって第１正規形だよね～、とかディスカッションしてる最中にふと思い出したのが、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節に書いてある解決策ですが、具体的な手順までは書いてません。
なので、ディスカッション時のネタとして付箋に書いておいたら、和田さんにご教示いただけました。
今回の考え方としては、横縦変換をする、というイメージですかね。
基本は書籍「データベースリファクタリング」に書いてある手順がベースです。

実際にやってみた。

■手順１：移行用テーブルの準備
まず、Bugsテーブルの移行先として、Bugs_NewテーブルとTags_Newテーブルの２つを用意する。

CREATE TABLE Bugs_New ( bug_id SERIAL PRIMARY KEY, description VARCHAR(1000) ); CREATE Table Tags_New ( bug_id BIGINT UNSIGNED NOT NULL, tag VARCHAR(20), PRIMARY KEY (bug_id, tag), FOREIGN KEY (bug_id) REFERENCES Bugs_New(bug_id) );

■手順２：移行用テーブル Bugs_New に、BugsテーブルのBugデータのみ移行
次に、用意した移行用テーブル Bugs_New に、BugsテーブルのBugデータのみ移行する。

INSERT INTO Bugs_New (bug_id, description) (SELECT bug_id, description FROM Bugs_anti);

確認してみると、Bugsテーブルのバグ情報だけがちゃんと移行できているのがわかります。


■手順３：移行用テーブル Tags_New に、BugsテーブルのTagデータのみ移行
次に、移行用テーブル Tags_New に、BugsテーブルのTagデータのみ移行する。

INSERT INTO Tags_New (bug_id, tag) SELECT bug_id, tag1 FROM Bugs_anti WHERE tag1 IS NOT Null UNION SELECT bug_id, tag2 FROM Bugs_anti WHERE tag2 IS NOT Null UNION SELECT bug_id, tag3 FROM Bugs_anti WHERE tag3 IS NOT Null ORDER BY bug_id;

UNIONを使うのと、Null以外のタグを取ってくるようにするのがポイントです。
確認してみると、Bugsテーブルのタグ情報だけがちゃんとTags_Newテーブルに移行できているのがわかります。


Bugs_NewテーブルとTags_Newテーブルはまだ誰も使ってないので、堂々とINSERTできます。

■手順４：移行期間中の対処
最後に「移行期間」を設け、その期間はBugs, Bugs_New, Tags_Newの3テーブルを共存させます。
んで、移行期間中にアプリ側の改修を行います。（Bugsではなく、Bugs_NewとTags_Newを使うように改修）

移行期間中も移行元のBugsテーブルへは当然アクセスがあります。
なのでBugsテーブルのデータが更新されたら、それをBugs_NewテーブルとTags_Newテーブルに反映させるための仕組みを用意します。
「データベースリファクタリング」で紹介されている手順のうち、トリガー等を使うことで実現できそうです。

---

■ディスカッションのメモ
ディスカッション時に出たお話のメモ。

■なぜマルチカラムアトリビュートのような設計になるのか
・RDBを、Excelのような表計算ソフトの２次元と同じように捉えている。
　→　横に長く表示されてるから、そのまま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個だけ必須（Not Null）にしたい場合がある。
　→　Null不可とNull可の用途で、カラムを分ける？
・それはダメ。Null不可とNull可で、それぞれテーブルを分けるべき。

★そういや今気づいたが、従属テーブルを導入すると、バグに１つ以上のタグを必ず入れさせたい場合の制約が掛けられなくなる・・・？

■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

まさしく！


みなさま、ありがとうございました～