fc2ブログ

makopi23のブログ

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

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

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

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

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

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

商品詳細を見る


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

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


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

あと、今回のアジェンダスライドは @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をセットしておく。
20130808_sqlap_addtag_mysql_before.png

②SQL実行後
20130808_sqlap_addtag_mysql_after.png

tag1のみ、'performance'が代入されました。
これは書籍どおりの挙動ですね。

(2) Oracle Database Express Edition 11g Release 2

①SQL実行前
tag1, tag2, tag3にNullをセットしておく。
20130808_sqlap_addtag_oracle_before.png

②SQL実行後
20130808_sqlap_addtag_oracle_after.png

おお! @tonyuchi さんが言っていたとおり、tag1~tag3が全部、performanceになりました。
これは書籍とは異なる挙動ですね。

(3) HiRDB Server Version 9 (09-00-T1)

①SQL実行前
tag1, tag2, tag3にNullをセットしておく。
20130808_sqlap_addtag_hirdb_before.png

②SQL実行後
20130808_sqlap_addtag_hirdb_after.png

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

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]。
20130808_sqlap_array1.png


■Array型カラムへのInsert

こんなカンジでInsertできます。
20130808_sqlap_array2.png


■Array型カラムのSelect

こんなカンジでSelectできます。
20130808_sqlap_array3.png

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 (
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)
);


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

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


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

■手順3:移行用テーブル 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テーブルに移行できているのがわかります。
20130808_sqlap_ikou2.png

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節の解決策を手順に落としてやってみたりとか。

マルチカラムアトリビュートは経験者が多いということもあり、ディスカッションも盛り上がりました。


まさしく!


みなさま、ありがとうございました~
関連記事

コメント

PostgreSQL でもテストしてみました

こんにちは。はじめまして。

「7.2.2節の add-tag.sql の挙動」について、ちょっと興味を持ったので PostgreSQL でもテストしてみました。

結果、やっぱり Oracle と同じ挙動になります。(つまり、3つとも 'performance'が代入されます)

Oracle と PostgreSQL とが同じ結果で MySQL だけ違う、ということなので、さらに CASE 式を省いてより単純な形にした↓のような SQL を実行したらどうなるかをテストしてみました。

UPDATE t SET c1 = c1+1, c2 = C1+1;

c1 も c2 も 1 を入力した状態で↑の SQL を実行してみます(c2 の値は何であっても関係ないのですが)。

c1 が 1 なので、c1+1 は 2 になりますね。SQL は集合指向の言語なので、一度にすべてのカラムが更新されるはずで、実行結果は c1/c2 共に 2 になるはずです。実際 PostgreSQL で実行するとそのような結果になります。

…が、MySQL だと 2 と 3 になります。

おそらく c1 に 2 が代入されて、その c1 に 1 を足した結果の 3 が続けて c2 に代入されているのではないでしょうか。
実際↓のようにカラムの順番を入れ替えて実行すると、PostgreSQL と同じ結果になります。

UPDATE t SET c2 = c1+1, c1 = C1+1;

テストの結果を見ると、MySQL はカラム単位で順次実行しており、Oracle や PostgreSQL は SQL ステートメント単位で整合のとれた一括実行をしているように思われます。
CASE 式は関係なく、MySQL のこの動きが違いにつながっているように思われますが、どうでしょう?

ひょっとして書籍と MySQL が間違っている、というようなことはないのでしょうか??

あと、トランザクションは複数 SQL ステートメントの制御にかかわるもので、その制御(ロック)の単位は行なので、↑のように行の中でカラムの順番によって結果がかわることの説明にはならないように思いますね。
http://dev.mysql.com/doc/refman/5.1/ja/innodb-lock-modes.html

  • 2013/09/05(木) 02:29:04 |
  • URL |
  • hidehi #-
  • [ 編集 ]

Re: PostgreSQL でもテストしてみました

コメントありがとうございます。
カラム単位の順次実行と、SQL ステートメント単位の一括実行とは、実に興味深い考察ですね。
トランザクションに関するご意見も、確かにそのとおりのような気がします。

ちょと週末とか時間があるときに調べてみて、何かわかったら追記しようと思います。

  • 2013/09/05(木) 23:09:01 |
  • URL |
  • makopi23 #JalddpaA
  • [ 編集 ]

Re[2]:PostgreSQL でもテストしてみました

> hidehiさん

SQLアンチパターン読書会のFacebookで、今回コメントいただいた内容をみなさんに紹介させていただきました。
ありがとうございます~

  • 2013/09/25(水) 01:36:10 |
  • URL |
  • makopi23 #mQop/nM.
  • [ 編集 ]

コメントの投稿


管理者にだけ表示を許可する

トラックバック

トラックバック URL
http://makopi23.blog.fc2.com/tb.php/97-95f9b76c
この記事にトラックバックする(FC2ブログユーザー)

-->