失敗から学ぶRDBの正しい歩き方読書会(1)に参加

いつもより人が沢山。

冒頭から「4.1 アンチパターンの解説」まで読んだ*1 次回は「4.2 INDEXの役割」から

作者: 曽根壮大
出版社/メーカー: 技術評論社
発売日: 2019/03/06
メディア: 単行本（ソフトカバー）
この商品を含むブログを見る

SQL アンチパターンと似てる。紛らわしい。
昔はALTER TABLEを発行することは少なかった
データベースの寿命はアプリケーションよりも長い
- いろんな人が言ってる(t_wadaさんなど)
MySQLではCheck制約の機能はないが、CHECK制約を作るSQLはエラーにならない
- なぜ？
  - MySQLはエラーにしない主義の人が作っていた
チェック制約
- 画面やアプリのバリデーションと重複するのでは？
- 複数のアプリが参照するDBの場合はチェック制約が有効。
軽減税率　10月から
ISBN
雑誌で10年内で枯渇する(なんのID?)
岩波はISBNを上書きしている
- https://togetter.com/li/1053696
履歴データ
- マテリアルビュー
- 遅延レプリケーション(履歴データの保存に使うべきではない)
  - 遅延レプリケーションはあくまでバックアップ用
    - 論理的に壊れるとレプリケーションも壊れる
    - バックアップは別途とる必要がある。
- 消さない
  - 遅くなる
- RDBと履歴は相性がない。
JOIN
- JOINが禁止の業界もある

また、 JOIN は掛け算と言われます。テーブルスキャンの場合、100 行と 100 行の JOIN の場合は 10,000 行のテーブルスキャン相当ですが、 10,000行と10,000行ではなんと100,000,000行です。

CROSS JOINのことか?
RIGHT OUTER JOIN
- 使わない
- かなりレア
アルゴリズム
- NLJ(Nested Loop Join)
- Hash Join
- Sort Merge Join
MySQLはNLJのみ
Zig Zag Joinというもを最近知った(話題に出してみた)
- 誰も知らなかった
  - http://www.mathcs.emory.edu/~cheung/Courses/554/Syllabus/4-query-exec/index=zig-zag-join.html
JOINを多用したクエリは何が変わった？
オリジナル

 SELECT
単価表1.単価 AS 単価1
, 会員1.会員 id AS 会員1id , 単価表2.単価 AS 単価2
, 会員2.会員id AS 会員2id
FROM
単価表 AS 単価表1
INNER JOIN 単価表 AS 単価表2
ON 単価表1.単価id < 単価表2.単価id
AND 単価表1.会員id <> 単価表2.会員id INNER JOIN 会員 AS 会員1
ON 単価表1.会員id = 会員1.会員id INNER JOIN 会員 AS 会員2
ON 単価表2.会員id = 会員2.会員id INNER JOIN 都道府県 AS 都道府県1
ON 会員1.出身県id = 都道府県1.県id INNER JOIN 都道府県 AS 都道府県2
ON 会員2.出身県id = 都道府県2.県id INNER JOIN 会社 AS 会社1
ON 会員1.会社id = 会社1.会社id INNER JOIN 会社 AS 会社2
ON 会員2.会社id = 会社2.会社id WHERE
(単価表1.単価 + 単価表2.単価) > : 合計単価; AND 会社1.会社名 = '株式会社そーだい'
AND 会社2.会社名 = '株式会社そーだい'

料金関係の計算を事前に行った結果と結合する

 SELECT 単価1
, 会員1id , 単価2
, 会員2id
FROM (
SELECT
単価表1.単価 AS 単価1
, 単価表1.会員id AS 会員1id , 単価表2.単価 AS 単価2
, 単価表2.会員id AS 会員2id
FROM
単価表 AS 単価表1
INNER JOIN 単価表 AS 単価表2
ON 単価表1.単価id < 単価表2.単価id
AND 単価表1.会員id <> 単価表2.会員id WHERE
(単価表1.単価 + 単価表2.単価) > :合計単価 ) AS 単価組合せ表
INNER JOIN 会員
ON 会員.会員id = 単価組合せ表.会員1id
AND 会員.会社id = (SELECT 会社id FROM 会社 WHERE 会社名 =
'株式会社そーだい')

Viewを使ったクエリ

CREATE VIEW 単価組み合わせ表 AS SELECT
単価表1.単価 AS 単価1
, 単価表1.会員id AS 会員1id
, 単価表2.単価 AS 単価2
, 単価表2.会員id AS 会員2id
, (単価表1.単価 + 単価表2.単価) AS 単価合計
FROM
単価表 AS 単価表1
INNER JOIN 単価表 AS 単価表2
ON 単価表1.単価id < 単価表2.単価id AND 単価表1.会員id <> 単価表2.会員id;
-- View(単価組み合わせ表)を利用したクエリ SELECT
単価組み合わせ表.単価1 AS 単価1
, 単価組み合わせ表.会員1id AS 会員1 , 単価組み合わせ表.単価2 AS 単価2
 , 単価組み合わせ表.会員2id AS 会員2 FROM
単価組み合わせ表 INNER JOIN 会員
ON 会員.user_id = 単価組み合わせ表.会員1id INNER JOIN 会社
ON 会員.会社id = 会社.会社id
AND 会社.会社名 = '株式会社そーだい' WHERE 単価合計 > : 合計単価

以下の記述があるMySQLの解説本はいい加減なので買っていはいけない。
- サブクエリが使えない
- バイナリバックアップができない
- トランザクションがない
以下の記述が雑

また、このように SQL の結果を実体のある View にする機能としてマテリアライズド・ビューがあります。マテリアライズド・ビューはクエリの結果のテーブルを作ることと一緒ですが、再作成のときにテーブルの作りなおしが不要で、共有ロックのリフレッシュのみで良い、などのメリットがあります。SQL Serverなどの商用DBでは有料機能ですが、 PostgreSQLでは9.3以降から無料で使えますので、PostgreSQLをお使いの方はぜひお試しください。