「スッキリわかるSQL入門」読書会レポート vol.12

株式会社リゾーム 技術部 システム開発 第1グループの小田です。今回は「スッキリわかるSQL入門」読書会の第12回レポートです。 過去のレポートはこちらからご覧いただけます。

tech.rhizome-e.com

書籍について

前回に引き続き「スッキリわかるSQL入門 第3版」を題材としています。

第12回レポート

今回は第12章「テーブルの設計」の後半部分(5~7節)のレポートになります。 前回は正規化の概要について触れましたが、今回は正規化の具体的な手順やテーブルの物理設計について学習しました。

参加者は5名でした。

12.5 正規化の手順

正規化によってテーブルが適切に分割された状態を正規形と言います。 正規形は第1正規形から第5正規形まで存在しますが、本書では通常のシステム開発業務で求められる第3正規形までを解説されていました。 第1~第3正規形に変形するための具体的な手順は以下になります。

  • 非正規形 → 第1正規形:繰り返し列の排除
  • 第1正規形 → 第2正規形:複合主キーの一部への関数従属の排除
  • 第2正規形 → 第3正規形:間接的な関数従属の排除

また、正規化後にはER図を作成するのが一般的です。 ER図にはお客様の理想・要件を起点とする設計の流れで作成するものと、お客様の今の現実を起点とする設計の流れで作成するものの2種類があり、それぞれ「トップダウン・アプローチ」と「ボトムアップ・アプローチ」と呼ばれています。 前者のER図を基本にしつつ後者のER図から得られる情報を適切に取り込んでいくことで、新たな要件を満たしつつも可能な限り仕様漏れを防ぐことが出来ます。

参加者の意見・感想
  • 「きたない関数従属*1」は2種類あって、それぞれを解消するのが第2正規形と第3正規形という説明が図で整理されていてわかりやすかった
  • 今まで「ここ、冗長だから別テーブルにしたほうが良いのでは?」という感じで正規化をやっている部分があった。「ここが部分関数従属で~」とか、「ここが推移関数従属で~」とかはあんまり考えていなかったので、今後は意識してみようと思う
  • トップダウン・アプローチとボトムアップ・アプローチはどちらも重要。理想と現実をうまくすり合わせていくことが大事なんだな

12.6 物理設計

物理設計とは論理設計後に行う工程です。物理設計では論理モデルを元に、使用するDBMS製品独自の制約を考慮しつつ全テーブルの物理モデルを作成します。 また、作成した物理モデルはそのままDDL(主にCREATE文)に変換できる内容になります。 具体的には以下のような内容を確定させていきます。

  • テーブルやカラムの物理名の決定
  • カラムの型や制約の決定
  • インデックスの決定
参加者の意見・感想
  • 数値なら精度、文字列なら長さを決める必要があるし、DBMSによって型の仕様も違ったりする。よく考えて決めなければ
  • テーブル名、列名を英語で名前付けする際、たとえ意味が正しくてもマイナーな単語を使うとかえって分かりづらくなることがある。名前の正確さとわかりやすさのバランスを取るのが難しい
  • 書籍に書いてある「性能のためにあえて正規化を崩す」というのは聞いたことがある。参照頻度の高いカラムなどはキッチリ正規化して別テーブルに持たせるより、ある程度正規化を崩して同一テーブルに持たせた方がJOINの回数を減らせるので結果的にパフォーマンスが上がることもあるらしい

12.7 正規化されたデータの利用

整合性を維持しつつ効率よくデータを管理するためには、データを複数のテーブルに分けて格納する必要があります。 一方でデータを便利に利用してもらうためには、複数のテーブルの内容を1つの結果として見せたり、それをさらに集計したりする必要があります。 それら2つの形態を変換する技術が正規化と結合です。 正規化や結合といった技術を駆使することで、データベース上では整合性を保ちやすい効率的で安全なデータ管理ができ、お客様からはデータを見やすく分かりやすいシステムを作ることができます。

参加者の意見・感想
  • データの管理は複数テーブルで行い、利用時には1つのテーブルに見える形にするのがよい
  • 書籍の「そもそも私たち人間は、曖昧で、ある程度の冗長を含む情報に取り囲まれて生活しています。(...) 人間にとってはあまり正規化されていない情報のほうが取り扱いやすいのでしょう。」という内容について、Excelシートの結合を見かけるたびにうすうすそんな気がしていた
  • Excelや紙だと集計しにくくなるのを承知の上で見やすさや把握しやすさ優先のキタナイ表にすることもあるが、DBの場合はビューなどを使用した人間向けの画面と、正規化された中身で分けて運用できる

まとめ

今回は論理設計や物理設計の具体的な手順について学習しました。 この辺りの内容については体系的に学習したことのない参加者も居たため、非常に良い機会となりました。 また、書籍に書かれていた「性能のためにあえて正規化を崩す」というのは、SQLのパフォーマンス面で躓いた際に手段の1つとして思い出せるようにしておこうと思いました。

今回の第12回レポートで「スッキリわかるSQL入門」の内容は終了となります。 他にも本書の巻末には陥りやすいエラーや落とし穴とその対処法であったり、SQLのドリルも付いています。 それらも含め、特にSQL初学者には非常にオススメできる1冊だったように思います。 今後私が新入社員教育を担当することがあれば、SQLの学習にはぜひ本書を採用したいと思います。

*1:本書独自の言い回し。第2正規形の場合は複合主キーの一部への関数従属、第3正規形の場合は主キーへの間接的な関数従属がそれぞれ「きたない関数従属」に当たる。

「スッキリわかるSQL入門」読書会レポート vol.11

株式会社リゾーム 技術部 システム開発 第1グループの岩﨑です。今回は「スッキリわかるSQL入門」読書会の第11回レポートです。

過去のレポートはこちらからご覧いただけます。

tech.rhizome-e.com

書籍について

前回に引き続き「スッキリわかるSQL入門 第3版」を題材としています。

第11回レポート

第12章「テーブルの設計」の前半部分(1~4節)のレポートになります。前回まで様々なSQLの文法やDBMSの機能について学んできましたが、今回は少し異なり「概念設計」「論理設計」などのテーブル設計の手順について学習しました。

参加者は4名でした。

12.1 システムとデータベース

まずデータベース設計作業の一通りの流れをざっくりと学習しました。データベース設計は多くの場合、以下の流れで行われます。

  1. お客様の要望聴取
  2. 概念設計
  3. 論理設計
  4. 物理設計
  5. DDLDBMSの設定に落とし込む

実際にSQLを書くのは5番目の段階であることから、これまでに学習したSQL文法やDBMSの機能を理解し扱えることとシステムを適切に設計できるということは、関連があるにしてもイコールではないということがわかりました。

参加者の意見・感想
  • テーブルを設計するにはSQLの知識とともに、その業務のドメイン知識が必要不可欠
  • 今後新しい製品や機能を作る際には「今、自分はデータベース設計のどの段階にいるのか?」ということを意識するようにしたい

12.2 家計管理データベースの要件

システムには利用者がいるため、利用者側の要件をしっかりと洗い出すことが大切です。本節では「家計管理データベース」という具体的な例から、要件の聞き取りにおいての注意点を学習できました。

参加者の意見・感想
  • 関係者間の意識合わせをしておかないと、後々仕様が大きく変わる可能性がある
    • 複数の要件間で矛盾が出ないよう注意する
  • 「お客様の要望=お客様が本当に必要なもの」とは限らないため、難しいけど注意する必要がある

12.3 概念設計

概念設計では、要件を実現するためにデータベースで管理する情報を抽象化します。概念設計は結局どうすれば正解なのかとつい考えてしまいますが、唯一の正解があるわけではないため、練習や実践を重ねたり実例を沢山見て慣れていくことが重要そうです。

参加者の意見・感想
  • 資格試験で学習したときには概念的なもののイメージを掴むのにかなり苦戦した覚えがあるが、本書で書かれているヒントをもとにイメージしていくことで理解が進んだ
  • 最初の頃はエンティティが何を指しているのか理解するのが大変だった思い出
    • 本書では「情報の塊」「テーブルの原石」という表現で書かれており、イメージしやすい
  • 各エンティティの関係を明らかにしておくと全体の構造が分かりやすくなる
  • 形のない概念をエンティティにするのが難しそう……
  • 二重構造エンティティ(エンティティの中に別のエンティティを作ること)はできない
    • 今まで自然とやっていたが、そういう場合は別のエンティティとして外部に取り出す必要があるということを改めて学んだ

また、意見交換ではデータベースからER図を自動生成するツール「A5:SQL Mk-2」についての話も挙がりました。ER図を一から書かなくとも良いというのはとても便利ですね!

12.4 論理設計

論理設計では、概念設計で抽象化したものをデータベースが扱いやすい構造に変換するため、キーの整理、正規化(詳しくは次回レポート)などを行います。本節では「主キーは重複しないだけでなく、非NULL性、不変性も満たす必要がある」など、普段自然とやってきたことを例をもとに改めて学習することができました。

参加者の意見・感想
  • きれいに正規化されたテーブルだとアプリ画面での操作も楽なはず
  • 多対多になっているエンティティは中間テーブルを追加することで1対多になるよう変換する
  • 主キーはちゃんと考えて付けないと、いざ運用が始まってから主キーを変えるとなるとかなり大変
  • RailsはデフォルトでIDが主キーになるので主キーを考える手間が省ける

まとめ

今回はデータベース設計の要望聴取~論理設計を学習しました。「SQLの学習」といえばどうしても文法ばかりになってしまうため、今回の範囲でデータベースの設計作業を学習することができて良かったです。今後データベースの設計作業に関わることもあるかと思いますが、苦手意識を持たずポジティブな気持ちで挑みたいと思います。
次回の範囲は第12章「テーブルの設計」の後半(5~7節)です。

「スッキリわかるSQL入門」読書会レポート vol. 10

株式会社リゾーム 技術部 システム開発 第3グループの岡部です。今回は「スッキリわかるSQL入門」読書会の第10回のレポートです。

過去のレポートはこちらからご覧いただけます。

tech.rhizome-e.com

書籍について

使用した書籍は「スッキリわかるSQL入門 第3版 - IT入門書籍 スッキリシリーズ」です。

第10回レポート

今回の学習範囲は第11章「さまざまな支援機能」です。データベースを「より速く」「より便利に」「より安全に」使うための機能を学びました。

参加者は5名でした。

11.1 データベースをより速くする

本に索引(インデックス)があると、特定のキーワードが登場するページを素早く調べることができます。データベースにおけるインデックスもそれとよく似ており、目的のレコードを高速に検索することができます。このセクションでは、そんなインデックスの使い方や注意点について学びました。

インデックスが有効な例

インデックス による高速化の効果が得られやすい例として以下の3つが挙げられていました。

  1. WHERE句による絞り込み
  2. ORDER BYによる並び替え
  3. JOINによる結合の条件
参加者の意見・感想
  • 「WHEREの絞り込みが早くなるんだろうなぁ」程度の認識しかなかったが、ORDER BYやJOINでも効果を発揮することが分かって良かった
  • WHERE句による絞り込みについて、前方一致でもインデックスが利用されることがあるのは知らなかった
    • ただし部分一致や後方一致の場合は基本的にインデックスが利用されない。気を付けよう
  • 今までインデックスを自分で設定したり運用したりする機会は無かったが、今後は使いどころを意識していきたい
インデックスの注意点

便利なインデックスですが、以下のようにデメリットもあります。

  • ディスク容量を消費する
  • INSERT、UPDATE、DELETEのオーバーヘッドが増える
参加者の意見・感想
  • なんでもかんでもインデックスにすればいい、というわけではないんだなぁ
  • 頻繁にINSERTやUPDATEが発生するテーブルにインデックスを貼った結果、処理性能が落ちることがあった
    • ある環境ではデータが非常に大きいため、INSERTする際にインデックスを一旦削除し、INSERT後に再度インデックスを作成するという運用をしていると聞いた
その他の意見

ほかにも、インデックスについて以下のような意見がありました。

  • 主キー制約を設定すると、内部的にインデックスも作成されるとは知らなかった(※DBMSにもよる)
  • SQL Serverでは実行プランを確認することで具体的にどの辺りが遅いか分かるため、パフォーマンスの改善を行う際には必ずチェックしている
    • SQL Server Management Studioを使えば、実行プランをツリー形式でグラフィカルに表示することもできる

11.2 データベースをより便利にする

データベースをより便利にする仕組みとして、ビューや自動採番機能が紹介されていました。

ビューのメリット

書籍では以下のようなメリットが挙げられていました。

  • SQL文がシンプルで分かりやすくなる
  • データを参照できる範囲を柔軟に制限できる
参加者の意見・感想
  • 業務でもビューはよく使うのでメリットを実感している
    • 例:特定期間のデータのみを取得するビュー
  • 個人的には、データの参照範囲を制限できる点が大きなメリットだと思う
  • 少し特殊な使い方だが、別DBのテーブルを参照するビューを作ることで、あたかも自分のDB内のテーブルのように参照できる。プログラムを書き換えることなく、参照先のDBを変えることができて便利(SQL Serverではシノニムでも同じことができる)
ビューのデメリット

ビューはテーブルのように扱えるとはいえ、その実体は「名前付きのSELECT文」なので、以下のようなデメリットがあるようです。

  • ビューに対するINSERTやUPDATEは制限される(条件付きで可能ではあるが、DBMSによって異なる)
  • SQL文自体はシンプルなのに、実行される処理の負荷が想像以上に高い場合がある
参加者の意見・感想
  • 濫用しないようにしたいが、どこまでが適切な使用なのかを見極めるのが難しそう
  • プログラミングでも、呼び出した関数が実は重い処理をしていて…、という経験があったのを思い出した
  • 性能重視の場合はマテリアライズドビューも検討したい
    • マテビューの話題は第6章の集計テーブルでも出ましたね
重複しない番号の管理

主キーに連番を振ることはよくありますが、値が重複しないよう自力で管理するのは結構大変そうです。そこで、DBMSに用意されている機能を使って連番を管理する方法を学びました。

代表的なものは以下の2つです。

  1. 連番が自動的に振られる特殊な列を定義する
  2. 連番を管理してくれる「シーケンス(SEQUENCE)」を用いる
参加者の意見・感想
  • 自動採番列にはかなりお世話になっています🙇🏻‍♂️自前で管理する必要が無くて楽だし、なにより確実なのがありがたい
  • シーケンスは使ったことがなかった。今のところIDENTITY列で間に合っている
  • 自分の携わっているシステムでは、自動採番で管理しているものも、採番テーブルで管理しているものも両方あった。どういう使い分けをしているんだろう?

11.3 データベースをより安全に使う

トランザクション処理で必要とされる4つの特性をまとめてACID特性と呼びます。

  • Atomicity(原子性)
  • Consistency(一貫性)
  • Isolation(独立性)
  • Durability(永続性)

これまでコミットやロールバック、制約、ロック、分離レベルなどの安全機構を学んできましたが、これらはA・C・Iを実現するためのものでした。ではDはどうでしょうか?データベースに格納されたデータは物理的なストレージに記録されるため、故障によって読み書きができなくなる可能性をゼロにすることはできません。それを踏まえて、Durability(永続性)を確保するための仕組みがDBMSには用意されています。

データベースのバックアップ

データベースの全内容をファイルに出力することができます。そのファイルをデータベースから独立した別のストレージにコピーし保管する、というのが基本的なバックアップのやり方です。オフラインバックアップとオンラインバックアップの2つの方式があります。

ログファイルのバックアップ

データベースのバックアップは時間がかかる処理なので、あまり頻繁にはできません。そのため、データベースのログファイルを高頻度でバックアップするのが一般的だそうです。

データベースのログはREDOログやアーカイブログ、あるいはトランザクションログと呼ばれますが、その内容はそれまでにデータベースを更新したすべてのSQL文にほかなりません。最新のDBバックアップを復元し、そのバックアップ以降に実行されたSQLを実行する(※これをロールフォワードという)ことで、障害が発生する直前の状態までデータを復元することができます。

参加者の意見・感想
  • ロールフォワードという言葉は聞いたことがあったが、具体的な仕組みは知らなかったので学べてよかった
  • データベースのバックアップとトランザクションログのバックアップをうまく組み合わせることが大事なんだな
  • ストレージ容量やシステム負荷の関係上、完全にスキの無いバックアップを取るのは難しい。バックアップの頻度や保存期間をうまく調整する必要がある
  • 定型業務の中でバックアップや復元作業を行っているが、今でも緊張して冷や汗をかいている…
    • 下手に慣れて油断しちゃうより、緊張感を持ってやるくらいがいいかもね

まとめ

今回の章で紹介された機能は実際の業務でも使っているので身近に感じますが、今まで理解が浅いまま使っていた部分がありました。あらためて体系的に勉強できてよかったです。

次回の範囲は第12章「テーブルの設計」の前半(1~4)です。

「スッキリわかるSQL入門」読書会レポート vol.9

株式会社リゾーム 技術部 システム開発 第1グループの宮野です。
「スッキリわかるSQL入門」読書会の第9回レポートです。

「スッキリわかるSQL入門」の過去のレポートや、他の読書会のレポートはカテゴリ「読書会」でご覧いただけます。

tech.rhizome-e.com

読書会の題材

前回に引き続き「スッキリわかるSQL入門」を題材としています。

9回目レポート

9回目は第10章「テーブルの作成」のレポートになります。参加者は4名でした。

それぞれの感想・意見交換

10.1 SQL命令の種類

10.1.1 データベースを使う2つの立場
  • DBMSに対して、①データの操作を指示する立場 と、②データベース自体の操作を指示する立場 の2つがある。これらを分けて考えることが重要なんだな。今まであまり意識したことがなかった
10.1.2 4種類の命令
  • DMLDDLといったSQL命令の分類については考えてみたことがなかったかもしれない
  • DMLDDLの二つしか知らなかった。TCLは使っているけど名前は知らなかった。DCLはほとんど使ったことがない
10.1.3 DCLとは
  • DCL(GRANT, REVOKE)は普段、業務で権限を付与したりすることがないため一度も使ったことがない
    • PostgreSQLの勉強をした際に権限の付与をしたぐらい
    • SQLServerでは下記のような記述らしい
      • GRANT 権限 ON スキーマ名かオブジェクト名 TO ユーザー名
      • REVOKE 権限 ON スキーマ名かオブジェクト名 FROM ユーザー名
  • GRANTやREVOKEを実際に使うとしたらどんな状況か?
    • 開発・テスト環境ではあまり意識する必要はない
    • 設定するとしたら最初の環境構築時?
    • 特定の権限(例えばINSERTとか)を剥奪できるのは使いようがありそうだと思った
    • 既存のデータで集計するだけのユーザーにはINSERTやUPDATEを制限しておく
    • 必要がないときは閲覧権限だけのユーザーで入るようにしておくと事故防止になる
  • 普段は開発環境しか触らないので、権限周りは何も考えずデータベース管理者にしてしまっている...
  • サーバやDBの設定周りの知識は殆ど無いため、今後身に付けていきたい

10.2 テーブルの作成

10.2.1 テーブル作成の基本
  • 頻繁にテーブル作らないので構文忘れがち
  • CREATE TABLEはテスター業務ではあまり使わない
    • テーブルがリリース媒体に含まれない改修のテストの際に、自分でCREATE TABLEしたことがあるくらい
  • CREATE TABLEはよく使う
    • 現在の業務では動的にSQLを組み立てることが多いため、実行されるSQLを確認する用に適当なテーブルを作成して、そこに格納し出来上がったクエリを格納しておき確認したりする
  • RailsではActiveRecord経由でテーブルの作成やテーブル定義の変更を行うため、直接SQL文を使う機会はあまりない
10.2.2 デフォルト値の指定
  • DEFAULT制約は各種フラグやステータスコード(0:未設定、1:男性、2:女性とか)を表すカラムによく設定する
10.2.3 DROP TABLE文
  • たとえローカルDBでも、一度DROPしてから再度新しいテーブル定義でCREATEし直す方が間違いがなくて安心する
  • (P319のコラムの内容に関して)現在の業務でテーブルが存在するときのみ削除するには、OBJECT_ID関数を使って存在するかどうかを確認している
  • SQL ServerだとDDLロールバックは行えるらしい
  • DDLであるDROP TABLEをロールバックできるかがベンダーによって異なるのが怖い。少なくとも、私が触ったことのあるSQL Server/PostgreSQLではサポートされていたが、「トランザクションがあるから安心でしょ」と油断してはいけない
  • 一部DBMSOracle DB等)ではDDLの操作はロールバック出来ないらしい!今後もしかすると他のDBMSも触る機会があるかもしれないので覚えておこう
10.2.4 ALTER TABLE文
  • ALTER TABLEを使ったことはほぼない(あってもローカルDBだけ)

10.3 制約

10.3.1 人為的ミスに備える
  • 制約はトランザクションとは異なり人為的ミスに対して有効
  • 人為的ミスを防ぐために制限を課すというのは、SQLに限らず一般的な考え方。人はミスをする生き物だから
  • テストする側も制約のことまで気を付ければ無駄な作業(NOT NULL制約の項目で「NULLの場合は…」というテストケースを書いてしまうなど)がなくなるので気を付けていきたい
10.3.2 基本的な3つの制約
  • CHECK、UNIQUEは使ったことがない(NOT NULL、主キー制約はよく使う)
  • そういえばUNIQUE制約は現在の業務ではあまり見ないような…(NULLを許可する必要がある場合が少ない?)
  • 主キー制約、NOT NULL制約、外部キー制約を一番よく使ってたと思う
  • UNIQUE制約を付けていても、NULLは重複できる。NULL同士を比較してもイコールじゃないから。なるほど…
  • 特別なカラムでない限り、基本的にはNOT NULL制約をつけるようにしている
    • そもそもNULLを許容したくないため
    • プログラムの中で値がNULLだった場合の条件分岐をなくすることができる
    • NULLが入ること前提の設計の場合もある(カスタム次第で特定のカラムを使用したりしなかったり。使用しない場合はすべてNULL)
  • 制約でカラムに入りうる値を制御するケースについて
    • 練習問題で血液型カラムに入る候補をCHECK制約で制限しているが、これだと後で候補を変える必要が出たとき、DBの設定変更になってしまう
    • 自分なら血液型マスタテーブルを作成して外部キー参照にする
    • ビジネスロジックがかかわる場合、CHECK制約は結構使いにくいかも
10.3.3 主キー制約
  • 主キーの指定はカラム定義の横に記載する単独列指定ではなく、CREATE TABLE文の末尾に記載する複合主キー指定の書き方をよく使う(主キーが1カラムだけの場合も後者を使うよう統一している)
  • (P331のコラムの内容に関して)主キーとして用いている列には主キー制約を明示的に付けるべきだと感じた

10.4 外部キーと参照整合性

10.4.1 参照整合性の崩壊
  • データ自体がおかしくなってしまうとそれの復旧作業などで製品の稼働を止めてしまうので気をつけるところ
  • テーブル設計を行う上でとても重要な概念だと思う
  • 整合性が崩れると調査もかなり大変だろうなぁ
10.4.2 崩壊の原因
  • 参照整合性の崩壊は「存在しない行を参照する」パターンより「ほかの行から参照されている」行を編集することで起きるパターンのほうが意図せず起きそう
  • データの更新、削除をする際には念には念を入れて確認することが大切
    • それでもミスをする可能性はあるので、10.4.3の外部キー制約が重要になってくる
10.4.3 外部キー制約
  • テーブルの内容によっては外部キー制約をつけずに他テーブルのIDを保存する場合もある
    • 履歴テーブルなど(元IDが削除されても履歴は残っていてほしい)
  • 「主キー制約が設定されていなくても、利用者が「行を識別するための列」として利用する列があれば、それは主キー列」
    • これは盲点だった。今後テーブル設計に携わる際には上記を意識していきたい

章末

まとめ

「いつもはこんなことに気を付けている」「自分ならこう書く」といった日々の業務に基づいた発言が多く聞かれました。
また、この章で扱われている内容はミスしたときにDB全体に及ぶ影響が大きいため、念を入れて確認しながら操作しなくては…という声も多かったです。
次回は第11章「さまざまな支援機能」を読んでいきます。

「スッキリわかるSQL入門」読書会レポート vol.8

株式会社リゾーム 技術部 システム開発 第3グループの土井です。 「スッキリわかるSQL入門」読書会の第8回レポートです。
「スッキリわかるSQL入門」の過去のレポートや他の読書会のレポートはこちらからご覧いただけます。

tech.rhizome-e.com

読書会の題材

前回までに引き続き「スッキリわかるSQL入門」を題材としています。

8回目レポート

8回目は第9章「トランザクション」のレポートになります。参加者は5名でした。

それぞれの感想・意見交換

正確なデータ操作
  • データの取り扱いについては製品を提供する側として特に重要なところ
  • 製品提供の前提条件として正常なデータ操作ができるというのもある
  • 特にお金にかかわる部分でデータの矛盾が生じるとまずいよね…
  • トランザクションは、データを取り扱う上で無くてはならない仕組み
  • UPDATEやDELETE文の動作確認でもトランザクションをよく使う
  • 普段はテスト用のDBにテストのためにアクセスするだけなこともあって、DBは大勢が同時にアクセスするものだという前提を普段はあまり意識しない
    • お客様のデータを預かるためのものという意識をしっかり持ちたい
  • トランザクションは重要とわかりつつも、これまで本格的に学習したことが無かった気がする…
    • とにかく重要なので、今後も学習したい
コミットとロールバック
  • テスト用にデータを編集する際には、冒頭にBEGIN TRAN してから編集し、意図した結果になっているか・意図した以外の範囲を変更してしまってないかを確認したらコミットしている
  • ローカル以外の環境ではUPDATEなどでDBを更新する場合には必ず書くようにしている
    • 更新された件数を確認しコミットする
  • 複数テーブルにまたがる処理をする際にはよくトランザクションをきる
  • SQL文の作成はデータ抽出の作業ぐらいで直接触る機会はあまりないため、SQL文にトランザクションの指定をしたことはない
  • RailsだとRails側でデータの操作を行うため、トランザクションを掛ける際は専用のメソッドがあるのでそれを使っている
  • アプリのログを見ていると登録・更新処理でトランザクションが掛かっていることがわかる
  • 自動コミットモード→そもそも指定していなくてもトランザクションとして扱われている(トランザクションは指定したときだけだと思っていた)
  • 多くのツールは「自動コミットモード」で動作するというのは、普段あまり意識していなかったためなるほどと思った
  • SQL Server だと BEGIN だけじゃダメで、BEGIN TRANSACTION か BEGIN TRAN と書く必要があった気がする。COMMIT と ROLLBACK はふつうに書ける。
  • 普段は「BEGIN TRAN」「COMMIT TRAN」と書くようにしている
  • 不可分なものとして扱われる性質のことを原子性という(原子はこれ以上分割できないから、なるほど)
    • 余談ではあるがアトミックデザインだったり原子から命名されるものがほかにもある
トランザクションの分離
  • 分離レベルを指定してトランザクションをかけたことはない
  • トランザクションの分離レベルは設定したことがなく、普段意識していない。これを機に意識していきたい
  • 分離レベルまで意識してSQLを書けると良さそう
  • 複数人で同時アクセスした際に起こる問題について、3つに整理して説明されていてわかりやすかった
  • DBMSシステムの違いは使える関数や用語の違いがまず目につくが、分離レベル(READ UNCOMMITTEDがDBMSによっては無効である話)のコラムを読むと、思った以上に根本的な考え方のようなものが違うんだなと思った
  • PostgreSQLはデフォルトだとREAD COMMITTED
  • 副作用の種類はなんとなく聞いたことがあったけど、しっかり学べてよかった
    • ダーティリード: 別のトランザクションでコミットされていないデータが読み取れてしまう
    • 反復不能読み取り: 別のトランザクションで更新された後のデータを読み取ってしまう(こいつだけ名前が微妙だな… → ファジーリードとも呼ばれるそう、絶対そっちの方がかっこええ)
    • ファントムリード: 別のトランザクションで挿入されたデータが見えてしまう
  • トランザクション分離レベルは普段あまり意識したことがなかったので、今後は気を付けていきたい
    • READ UNCOMITTED (コミットされていないものを参照する → ダーティリードが起こりうる)
    • READ COMMITTED (コミットされたものを参照する → ファジーリードは起こりうる)
    • REPEATABLE READ (反復可能な参照 → ファントムリードは起こりうる)
    • SERIALIZABLE (逐次化 → トランザクションは必ずひとつずつ順番に実行される → 副作用が起こりえない)
ロックの活用
  • 排他ロックを表ごとにかけてしまうと影響範囲が大きいため、必要な行だけにするなどの注意が必要
  • ロックの範囲と強さは最低限にすることが大事
  • 幸い今まで製品でトラブルの原因になったことはないが、デッドロックが起きそうな場所は意識していきたい
  • デッドロックとかの話は資格試験で勉強したが、自動的にデッドロックを解決する仕組みがあることは知らなかった
  • 明示的にロックというのは使ったことがない。お客様のデータに入るようなときは使うのかな?
  • 複数対象にロックをかける際には同じ順番でロックをかけることでデッドロックを回避できる
  • SQL Serverでは SELECT * FROM table WITH (UPDLOCK, ROWLOCK) WHERE ~ のように WITH を使った書き方になる(行ロックもテーブルロックも)
  • つい最近ストアド実行時にテーブルをロックするクエリを書いた(というか既存の処理をコピーした)
    • 「SELECT~FROM~WITH (TABLOCKX)」
  • 実際にロックを使用する際には時間によるタイムアウトだったりを組み合わせる必要がありそう

まとめ

今回はトランザクションに関する章でした。安全な運用のためには必須の知識であることに加えて、これまで分離レベルなどは意識していなかったため今後は意識していきたいです。また、個人的には知らない間に自動コミットモードが作用していた部分が印象に残りました。 次回は第10章「テーブルの作成」になります。

「スッキリわかるSQL入門」読書会レポート vol.7

株式会社リゾーム 技術部 システム開発 第4グループの平松です。今回は「スッキリわかるSQL入門」読書会の第7回のレポートです。

「スッキリわかるSQL入門」の過去のレポートや他の読書会のレポートはカテゴリーでまとめていますので、こちらからご覧いただけます。

tech.rhizome-e.com

読書会の題材

前回に引き続き「スッキリわかるSQL入門」を題材としています。

7回目レポート

7回目は第8章「複数テーブルの結合」のレポートになります。参加者は4名でした。

それぞれの感想・意見交換

「リレーショナル」の意味

  • 基本情報の勉強でやったところ
  • テスターは自分でテーブルを設計する機会はないが、テスト用に各テーブルを結合して抽出して…とやっていると、根本的な設計の大事さはなんとなくわかる
  • データの関係性によって、どのようなテーブルにするのかは分かれる
  • どのテーブルに外部キーを持たせるかは目的によって変わる
  • テーブルを複数持てることの恩恵が改めて分かった
  • 単一のテーブルでデータを管理するのは考えたくないな…。絶対どこかで不整合が生まれそう
  • 複数テーブルに分けるメリットは座学で習うだけだとあやふやだったけど、実際に業務でSQLを触っていくうちに感覚として納得した

テーブルの結合

  • SQLで一番初めに躓いたところ
  • 初めて使った時は表計算ソフトっぽいというのが頭の中にあったため、結合のイメージがしづらかった覚えがある
  • 初めて習った時は「おおー」となった。データベースを扱ってるという実感があった
  • 結合はイメージが湧き難いので、書籍では紙工作で結合を体験できるようになっていて初学者には非常に分かりやすくて良いと思った
  • 当たり前のことだけど、結合結果を頭の中でイメージするのは難しいので、実際に結合結果を見て理解するのが確実
  • 結合も種類があるため、必要なデータによって内部結合を使うか、外部結合を使うか変わってくる
  • 条件を満たしている行に繋がっていくイメージはうまくできている
  • 製品内部にも必ずと言っていいほど使われているし、テストでも使う
  • テーブルの結合は業務でよく使う
  • 「INNER JOIN」を省略して「JOIN」と書くことは滅多に無い

結合条件の取り扱い

  • 完全外部結合(FULL OUTER JOIN)は使ったことがない
  • 例で出た「WHERE NULL = …」みたいなことはさすがにやらないが、「この書き方だったらNULLはどうなるか」はやはり気を付けないといけないところ
  • INNER JOIN と LEFT JOIN の2つを覚えておけば大体の状況に対応できそう
  • 内部結合の動作がややこしい
    • 内部結合の説明を読んだ後だとLEFT JOIN, RIGHT JOIN, FULL JOINの動作に納得した
  • 結合相手が複数行の場合、結合前より行数が増えるというのが混乱する原因な気がする
  • INNER JOIN, RIGHT JOIN, LEFT JOINとかの違いはベン図で考えるのが一番理解しやすかった
  • JOINにいろいろ種類があるのがややこしい
    • 特に省略表記できるのが逆に厄介だと思う
    • 個人的には混乱しがちなポイント
  • 業務では結合条件の列がNULLであってもデータを取得したいことが多いので主に LEFT JOIN を使う
  • 混乱を避けるため RIGHT JOIN は使わず、LEFT JOIN のテーブルの順番を入れ替えるようにしている
    • そういうルールで運用しているチームは多いイメージ
  • FULL JOINじゃなくてUNIONを使うことのほうが多い
  • 個人的にINNER JOIN, LEFT JOIN, RIGHT JOINには「OUTER」は付けないが、何故か完全外部結合のときだけ「FULL OUTER JOIN」にしてしまう

結合に関するさまざまな構文

  • カラム名にテーブル名がなくてエラーになるのはよくやる(エディタの下線に助けられている)
  • 3テーブル以上やサブクエリを結合すると、どうしても見づらくなるので、インデントやコメントをしっかりつけたい
  • 特定のカラムだけを抜き出したテーブルを結合させたことがある
  • 同じテーブルを結合するのはしたことないかもしれない
  • 自己結合で同じ家計簿同士を繋げたいことはあるのか?と考えながら読んでいたらテキストの例を見てなるほどなってなった
  • 3テーブル以上の結合や、サブクエリ結果との結合、同じテーブル同士での結合は使う機会はなかった
  • 同じテーブルを結合するのは自分で書いたことはないがPull Requestなどでよく見る
  • 基本的に結合する場合は、テーブル名を明示的に指定している
    • 列名を記述する際もエイリアス名を必ず書くようにしている
    • 個人的にその方が読みやすいし、自分の想定していないテーブルの列が参照されるような想定外の挙動になることを防ぐことができる
  • 副問い合わせの結果を結合するのはよくやる

まとめ

今回は、RDBMSの目玉の結合に関する章でした。意見交換の場でも実際の業務に関わる話が多かった印象です。結合について学ぶことでSQLでできることの幅が一気に広がると思います。結合はよく使う機能ですが私自身も知らなかった使い方があったので今後、SQLを触る際に活用していきたいと思います。

次回は、第9章「トランザクション」になります。次章から第三部「データベースの知識を深めよう」の内容になっていきます。

「スッキリわかるSQL入門」読書会レポート vol.6

株式会社リゾーム 技術部 システム開発 第1グループの小田です。 今回は「スッキリわかるSQL入門」読書会の第6回のレポートです。

過去のレポートはこちらからご覧いただけます。

「スッキリわかるSQL入門」読書会レポート vol.1 - リゾームのテックブログ

「スッキリわかるSQL入門」読書会レポート vol.2 - リゾームのテックブログ

「スッキリわかるSQL入門」読書会レポート vol. 3 - リゾームのテックブログ

「スッキリわかるSQL入門」読書会レポート vol. 4 - リゾームのテックブログ

「スッキリわかるSQL入門」読書会レポート vol.5 - リゾームのテックブログ

読書会の題材

前回に引き続き「スッキリわかるSQL入門」を題材としています。

6回目レポート

6回目は第7章「副問い合わせ」のレポートになります。

今回の参加者は6名でした。

参加者の感想・意見まとめ

7.1 検索結果に基づいて表を操作する

  • 「副問い合わせ」より「サブクエリ」という呼称の方がよく使う。
  • 副問い合わせのSELECT文自体は単純な構造だけど、入れ子にすることで複雑な集計をさせられる。
    • 括弧とインデントでサブクエリのブロックを意識することと、サブクエリの結果が値に化けるというイメージは大事。
    • クエリが複雑になるほど、可読性を意識して書くのが大切になってくる。
  • 安直に副問い合わせを書いてしまうと可読性が下がってしまったり、パフォーマンスに影響があるかもしれないのでイマイチだけど、業務ではよく使う。
    • 場合によってはJOINの方がパフォーマンスが良いケースもある。
    • 副問い合わせとJOINどちらがパフォーマンスが良いかについては、RDBMSの種類によって速度に大きな違いが出るのでどちらが良いか一概には言えないらしい。SQLのパフォーマンスチューニングは奥が深い...。
  • (コラムについて)データ構造の基本をスカラーベクターマトリックスと呼ぶのは初めて知った。
    • 3パターンに分けて勉強するのは分かりやすくて良いと思った。

7.2 単一の値の代わりに副問い合わせを用いる

  • 1つの値になるから比較演算子を用いて比較できる。
  • 集計関数と合わせると効果的に使えそう。
  • 副問い合わせの内容が長くなりがちで、かつそれがSELECT句の途中に入るので可読性の工夫が要ると思う。
  • INSERT時、IDを採番する際にレコードのIDに+1した値をセットする副問い合わせを書いたりする。

7.3 複数の値の代わりに副問い合わせを用いる

  • 複数の値のため比較にはANYやALL、INを用いる必要がある。
  • テーブルに直接値が入っている場合は使えそう。
  • 「NOT INまたは<>ALLで判定する副問合せの結果にNULLが含まれると全体の結果もNULLとなる」
    • NULLに注意が必要!NOT INの動作は罠だなぁ。
    • 普段NOT INを使うことはあまり無いが、いざという時嵌りそうなので覚えておきたい。
    • NULLを除外する方法:
      • サブクエリのWHERE句でIS NOT NULLを指定する。
      • COALESCEでNULLを別の値に置き換える。
  • ひねりの効いた使い方というか、演算子との組み合わせ方やNULLへの配慮など考えないといけないが使いこなせると便利だと思う。

7.4 表の代わりに副問い合わせを用いる

  • 製品の検証作業時に分析結果の確認などでよく使うパターン。
  • FROM句やINSERT文では本当によく使う。特に複数データ登録するときにINSERT~SELECT~は超便利。
    • INSERT-SELECTは本にも書かれているけど、厳密には副問い合わせではない。INSERTがサポートしている特殊な構文。複数のテストデータを用意するときに使った記憶がある。
    • INSERT文の特殊用法は読みづらいけど、1塊ごとに考えていくのを意識していきたい。
    • FROM句で長い副問い合わせを書くと、書いた本人でも読みにくいなと思うためあまり書きたくない。最近WITH句を覚えたので、できればそっちを使ったりしていきたい。
  • 外側のSQL文の列を使う相関副問い合わせは使う機会はあまりなさそう。
    • 相関副問い合わせなんてものがあるんだな…。同じような書き方だけど内部的な動きやコストが違うのは罠だな。複雑な処理を書いていると、気づかないうちに相関になっていた、なんてことがあったりするのかな。
    • 大体はテーブルで関連付けをしてるからJOINを使うほうが多そう。
  • エイリアスを付け忘れて中々エラー解決できずに泣いたのはいい思い出…。
    • ベンダーによっては必須ではないが、副問い合わせの結果には必ず別名を付けるのがよさそう。
  • これも複雑になるとあとで見直すとき大変なので、インデントやコメント大事だと思う。

まとめ

書籍では副問い合わせの検索結果が単一の値・1次元配列・表の3つにパターン分けした上で学習を行うよう章立てされており、参加者からも「理解しやすい」と好評でした。

参加者から多く出た意見としては、副問い合わせは便利な一方で可読性やパフォーマンスが落ちることがままあるため、使用方法やインデントに十分注意する必要があるというものでした。

副問い合わせは私自身も使う機会が多いのですが、やはり度々パフォーマンスが問題になったり構造が複雑なクエリになっていることがあるので、JOINやWITH句を用いることでより簡潔でパフォーマンスの良いクエリにできないか都度検討するよう心がけたいと思います。

次回は第8章「複数テーブルの結合」です。テーブルの結合はSQLの肝となる重要な機能なので、しっかりと学習を行いたいです。