SQLアンチパターン読書会 「ジェイウォーク」に参加しました

2013/4/11(木) SQLアンチパターン読書会 「ジェイウォーク」に参加してきました。

DoorKeeper（告知サイト）
http://sqlap.doorkeeper.jp/events/3416

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

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

場所は御徒町（湯島）の株式会社アルティネットさんです。
参加者は全部で8名くらいでしょうか。
今回、主催者の @natsu_nanana さんにお誘いいただいて参加させていただきました。感謝！

先日2/26(火)、Devlove主催の「SQLアンチパターン・レトロスペクティブ　- データベース危篤患者の救出 -」というイベントがあり、これに参加してきました。
そこで「これは！」と思い、会場で販売していたこの書籍を即購入したのでした。
んで、その時に講演されてたのが、この書籍の訳者でもある @t_wada さんです。
そんとき書いたブログはこちら。

「SQLアンチパターン・レトロスペクティブ　- データベース危篤患者の救出 -」に参加しました
http://makopi23.blog.fc2.com/blog-entry-54.html

今回、この書籍をターゲットとした読書会が新たに始まるとのことで、とても楽しみでした。
しかも前日になって @t_wada さんも参加されると知って、テンションは急上昇！

ということで、頑張って写経してから参加することにしました。
以下、写経メモと読書会メモ。
ちなみに環境は、OSがWindows7(x64), DBMSがMySQL 5.1.37, DBMS操作用のGUIが MySQL Workbench 5.2.47CE です。

---

■ディスカッション前に、参加者全員で議論ネタを付箋に書き出しました。



■1.1節から1.2節へのProductsテーブル変更の方法
・最初、DropしてからCreateし直すのかな？と思いましたが、Alter文でできました。

(1) account_id列に貼られている外部参照の名称を調べるために、以下のSQLを発行する。

show create table products;

　→　表示された結果のCONSTRAINT欄を見ると、参照制約の名称は products_ibfk_1という名称であることがわかる。

(2) 次に、account_id列に貼った参照制約を削除する。

alter table products drop foreign key products_ibfk_1;

(3) 最後に、account_id列の型をvarchar(100)に変更する。

alter table products modify column account_id varchar(100);

以上(1)～(3)の手順で、account_id列の型をvarchar(100)に変更できました。
ポイントは、外部参照制約を外さないとalterできないことですかね。

ちなみにproductsテーブルを一度dropしてcreateし直す場合、productsテーブルのproduct_id列は
他のテーブル（P.xxiiiのBugsProductsテーブル）から外部参照が貼られているので、そのままdropできません。
なのでいずれにせよ、account_id列の外部参照制約を外す必要があります。

■1.2.1節の正規表現について
最初 REGEXP '[[:<:]]12[[:>:]]'の部分は LIKE '%12%' でもいいんじゃね？と思ってました。
ですが読書会で質問してみると、どうやらREGEXPの方は、単語境界を認識してパターンマッチする構文だそうです。
知らなかった。ググッても見つからなかったし。
ご教示ありがとうございます @t_wada さん。。。


■1.2.2節のSELECT文(regexp_2.sql)のインデックス使用について

書籍に「両方のテーブルをすべてスキャンして、クロス積を生成し」という説明があります。
これがどうも腑に落ちませんでした。
WHERE句のp.product_id列は主キーなので、インデックスが自動で貼られているはず。
そうなると、Productsテーブルは主キー検索で1件に絞られるはず。
だとすると、ON句では1対Nのスキャンになるはず。

んで、この件を読書会で質問してみたら、@t_wada さん曰く、どうやら原著でもミスがあった箇所だそうで、
和書の方も修正するかも、とのことでした。
どうやら、ミスを見つけたということで、ちょとお役に立てたようです！

気づいた点を質問したら、原著のミスらしい。本にも反映されるかも！ちょとお役に立てて嬉しい！ #sqlap

— makopi23さん (@makopi23) 2013年4月11日

ちなみにexplain句で検索パスも調べてみました。


type列を見ると、Productsテーブル側が const となっており、インデックスアクセスです。
ちなみにAccountsテーブル側は ALL となっており、フルテーブルスキャン。

あと、SQLのファイル名称が regexp.sql と書かれてますが、実際はregexp_2.sql ですね。誤植かな？

■1.2.3節のSELECT文(count.sql)について
これは何をするSQLなのかな？と、最初読んだときはピンときませんでした。
んで写経して動かしてみたら、これはCOUNT関数ですね。
account_id列にカンマで区切られた数字が何個入っているかを、product_id毎に表示します。

■データベースの構造変更について
1.2節の最初に「データベースの構造に対する変更を最小限に抑えるために」とあるが、
1.5で交差テーブルを作成する時点で、Productテーブルをalter tableして
account_id列を削除しなければならない。
ただしバイト位置指定でデータを取り出す実装にアプリ側がなっている場合、カラム削除は影響デカい。。。
でも必要なことなので、痛みを伴うがリファクタリングすべき。


■多対多の状況に出くわした場合の対処方法
@t_wada さんが「データベースリファクタリング」という書籍を使って説明してくださいました。

データベース・リファクタリング (2008/03/26) スコット W アンブラー、ピラモド・サダラージ 他 商品詳細を見る

該当箇所はP.123の「関連テーブルによる1対多関係の置き換え」ですね。

大御所が説明してくださる醍醐味・・・！なんて贅沢な読書会なんだ。
ポイントは、関連テーブル（いわゆる交差テーブル）を用意するのと、同期用トリガーを導入して移行期間を設ける点ですかね。
ちなみに私、この書籍も持っていて、読書会にも参加してます。（前回は出れませんでしたが。。。）


■1,5.1のSELECT文(join.sql)のインデックスの使われ方について
「このクエリは結合の際にインデックスを効果的に使う」と書いてありますが、Contactsテーブルのproduct_id列は主キーじゃないのでインデックススキャンできないのでは？と最初思いました。
んで読書会で質問してみたら、Contactsテーブルのproduct_id列には外部キーを付与しているため、MySQLだと自動的にインデックスが貼られるのだそうです。
ちなみにPostgresqlだと外部キーを設定しても自動でインデックスは貼られないのだそうな。
DBMS依存なんですねー

■ジェイウォークを選択してしまうような場面とは
以下の２パターンが考えられるとのこと。
(1) 苦肉の策で、追い詰められてやる場合
(2) 正規化を理解していない場合

■ジェイウォークを選択してしまう心理
プログラミング言語だと、カンマ区切り構造は普通なら配列で持とうとするので、自然な発想ではある。
それに対し、RDBMSは多対多というのは特殊な状況に当てはまる。
プログラミング言語でアプリ側でやる方が早い場合は選択肢としてはアリ。
ただしその場合のデメリットとして、参照整合性はアプリ側で頑張る必要がある。

■交差テーブルの導入について
交差テーブルにIDのペアしか持たせないなら、論理設計としては意味が無く、単なる物理設計で仕方なく用意した位置づけとなる。
交差テーブルに、IDのペア以外にカラムを持たせることがある。
例えばCustomerとPolicyというキーのペアに加え、日付のカラムを持たせて日付情報を付与するパターンとか。
あとは履修管理システムとかだと、「Lesson(授業)」と「Student(学生)」の多対多の関係を維持する交差テーブルに、「履修登録した」という事実を持たせるために「Registration(履修)」というカラムを持たせる場合が該当する。

■日々使うSQLの先頭にEXPLAINを付けるとアクセスパスを見ることができるので勉強になる。

■最近はDBMSが実行計画をよく考えてくれる。
・コンパイラの最適化の世界と一緒で、SQLも素直に書いたほうが良い。
・各テーブルはメタデータを持っていて、データの偏りを判断してインデックスの使い方を制御したりしてくれる。
・性能のしきい値を設けて、それを超過した時にexplainで調査するくらいが良いのでは。


■SQLやDBに関するオススメ書籍
@t_wada さんからは以下のような書籍が紹介されました。

・中級者向け

達人に学ぶ SQL徹底指南書 (CodeZine BOOKS) (2008/02/07) ミック 商品詳細を見る

・書いて覚えようという趣旨の書籍（著者の一人は @t_wada さんのパパ）

改訂新版 反復学習ソフト付き SQL書き方ドリル (WEB+DB PRESS plusシリーズ) (2007/05/18) 羽生 章洋、和田 省二 他 商品詳細を見る

SQL ゼロからはじめるデータベース操作 (CD-ROM付) (プログラミング学習シリーズ) (2010/06/29) ミック 商品詳細を見る

データベースパフォーマンスアップの教科書 基本原理編 (2006/07/07) エンコアコンサルティング 商品詳細を見る

■勉強会の途中から @inda_re さんも飛び入り参戦。
しかもなぜか以下の書籍を手にしてて、奥深い名言を一言。「ジェイウォークなんかfoldrで一発でしょ！」

型システム入門 −プログラミング言語と型の理論− (2013/03/26) Benjamin C. Pierce 商品詳細を見る

ちなみに私、この原著の読書会にも去年まで参加してました。
あと、訳者の一人が私の大学院の研究室時代の1年後輩なのです。
というわけで、私にとってもこの書籍は思い入れのある書籍なのです。

---

★感想：
写経の段階で出た疑問も解決できましたし、皆さんとディスカッションで多くの気づきを得られて大変有意義でした。
@t_wada さんが参加してくださっているのも豪華すぎですし。
これからも継続して参加したいと思います。

あと、終わったあとに参加者さんとご飯を食べに行ったのですが、遅れて @yokatsuki さんが合流されました。
次回、@t_wada さんと @yokatsuki さんの2名が参加されたら、更に勉強会のクオリティが上がりそうで、贅沢な限りです！

最後に、貴重な場を提供してくださった @natsu_nanana さんはじめ皆様、ありがとうございました。