- データベース技術の歴史は長い
- それでもDB由来の問題が発生する
- データベースの理解が十分でないから
データベースがないと何が困るのか
- 技術的に高度かどうかは売れるかどうかに直結しない
- いい商品コンセプトカどうかが大事
- 変更があることが前提のソフトウェア工学
- データベースに詳しいという若い人は少ない
- そもそも何のために必要か理解できていない
- 現実ではデータベースを使わないアプリは皆無
- 実務で一度失敗してから理解する
- 本質部分がわかっていないと対処療法的になる
- わかっていると一過性のブームか見分けられる
データベースがないと困ること
- 大量データから高速に返せない
- 「サーチ（探索）」という技術領域
- Excelではできない
- 大量データはメモリだけでは無理！
- 障害時の迅速な復旧ができない
- トランザクションなど
- 並列性の制御が難しい
- 複数で同時に書き込み
- 銀行口座の取引
- 排他制御と速度のバランス
- データ整合性の保証が難しい
- 同時アクセス
- 履歴管理
- 参照整合性制約
データベースはレガシーなテーマではない。「複数人」で「大量のデータ」を扱い「壊れたら大変なことになる」アプリでは必須。これは業務でしか経験しない。
インデックスで高速アクセス
キーと値のペア管理
- 全件検索はデータが大量だとムリ！
- 線形探索
- データがN倍になれば時間もN倍
- 一瞬でたどり着く方法は？
- 単純に固定長で管理
- 必ずおさまる保証はない
- 拡張性がない
- インデックスを導入
- 索引
- 「キーと値が何バイト目から始まるか」が書いてある
- これがインデックスファイル
- キーとバイト位置の２つだけ
- データ量を気にしなくていい
- 高速検索
- ハッシュインデックス
- キー値はいろんな種類が入る
- ハッシュ値を使えば同じサイズになる
- 固定長にできる
- ハッシュインデックスが苦手なもの
- 10,000円以下のお中元
- 「Final」で始まるゲーム名
- 投稿を新しい順に
B+Treeインデックス
上からルートブロック、ブランチブロック、リーフブロック。ルート→ブランチ→リーフと辿っていく。
- 多分木と二分木
- B+Treeは多分木
- 二分木は階層が深くなる
- B+TReeとB-Tree
- B-Treeはリーフ以外も値をもつ
- B+TreeがRDBMSのデファクト
RDBMSでの最適化
- 一意性
- ハッシュインデックスは一意性の保証もできる
- 値が同じになるから
- マルチカラムインデックス
- ユーザーIDと最終更新日のAND検索
- インデックスだけを読む検索
- インデックスマージ
- インデックスで拾ってきてからANDやORで検索
- ランダムアクセスはHDDでは半回転
更新コスト削減
- ディスクへのまとめ書き
- 更新順だとランダムアクセス発生
- メモリやファイルに一時保存
- まとめてリーフを更新
- MySQL
- 並列更新性能を高める
- インデックス組み替え終了までブロック
- 並列性上がらない
- マルチコアCPUでの要求増す
- パーティション表を使う
- 内部的に複数に分割管理
インデックスはデータサイズを増やしてしまう。余計な処理を増える。何にインデックスをつけるのか判断が大事。
テーブル設計とリレーション
データモデリング技術の重要性
- 「どのようなデータ項目が必要か」が大事
- 「そのデータを何に使うのか」という業務要件
- あとで追加しやすい設計
従業員、組織、部門
- ヒアリングの結果
- 社員番号は一意
- 同姓同名の社員もいる
- フリガナが同じの社員もいる
- メールアドレスは一意
- メールアドレスがない社員もいる
- どこかの部署には所属している
- 部門名は一意
- 部門の電話番号がまちまち
- データ項目と関連性
- Excelでいいのか？
- 万単位では不可能
- 数百でも整合性確認は目視
- 何をしたら整合性が崩れるのか
- オーソドックスなテーブル
- 社員番号(emp_id)、これが主キーとなる
- 社員名(emp_name)
- 社員ローマ字(emp_roman)
- メールアドレス(emp_email)
- 部門名(dept_name)
- 部門別電話番号(dept_tel)
- これだと同じ部門名、電話番号があちこちに出てくる
ポイント1. テーブルを分割する
重複した情報を持たせるということは、コピペを多用したプログラムと同じ。変更が変更を呼んでいつのまにかぐちゃぐちゃになる。
従業員、部門、別々のテーブルにしてはどうか。部門にidを持たせて主キーとする。部門名や部門番号の打ち間違いがなくなる。やった。
- 外部キー制約
- 部門コードの打ち間違いをどうするか。「100」を「1000」
- 存在するか自動チェック
- 整合性を管理してくれる
- 万能ではない
- 存在するものだと通ってしまう
- リモートサーバーだとチェックできない
- アプリ側でチェックする
- インデックス検索で素早く存在確認
ポイント2. テーブル設計の妥当性を検証
１人の社員は１つの部門しか登録できない。部門ごとに違う社員IDを使うようにする。アカウントを２つ覚えておかないといけない。めんどくさい。ではどうするのか？
- 連番の列を導入
- 部門を３つまで登録できるようにする
- ３つ以上になったときに破綻する
- 列がどんどん増えるときはイマイチ
- １：Nを導入
- マッピングテーブル（中間テーブル）を作る
- 主キーの値がよく変わる
- 兼務の開始日終了日を追加して常に追記にもできる
- レコード数が増える
- 数千万ユーザーのマッチングだとツライ
正規化理論の基本
- 第１正規形でない
- コピペだらけのテーブル
- 重複値がたくさん
- カンマなどで値が２つ入っている
- 第２正規形でない
- 主キーが複数列ある
- dept_idとdept_nameがempのテーブルにある
- 部分的に決まる値がある
- 第３正規形でない
- 主キーでないキーで決まる値がある
第３正規形まで条件を満たしても部署の兼務ができないという問題は克服できていない。どうやったら問題に気付けるか。これはテーブル設計に慣れるしかない。
SQL文の特徴と使いこなし
テーブル操作
- テーブル作成
- CREATE TABLE
- DB相互の互換性はあまりない
- RDBMS間の差異
- 特にデータ型
- 過度に標準化しないほうがいい
- 一度作ると変更は難しい
- 設計が大事
- データ操作
- INSERT
- SELECT
- UPDATE
- DELETE
- TRUNCATE TABLEはテーブル定義残る
- DROP TABLE
- ジョイン
- 社員の所属する部署は？
- 中間テーブルがあると３回のアクセス
- DBへのアクセスはリモートアクセス
- 重たい処理
- 分散データベース環境とジョイン
- テーブル単位で複数のサーバーにする
- Shardingと呼ぶ
- ジョインができない
ジョインが遅いのではなくSQL文の書き方が悪いから遅くなる。ジョインで一回のところSELECT１００回のときもある。
SQL文の実行効率を意識
- 適切なインデックスが使われているか
- インデックスされていない列をWHEREで使わない
- EXPLAINでDBが判断してくれる
- rowsの積がざっくりDBアクセス数
- 各テーブル１だともっとも効率がいい
- EXPLAINをかける前にクエリ分析ツールで見つける
- 管理系コマンド
- 秒間何回実行しているか確認
- DBの安定性や性能がわかる
SQLの長所と短所
- 習得がカンタン
- チューニングの奥は深い
- 機能面
- ロジックはプログラミング言語、データ操作はSQL
可用性とデータの複製
- 典型的な障害
- ソフトウェアの障害
- OSの障害
- デバイスドライバの不具合
- 高負荷処理が一定時間発生後など
- 最新ドライバへアップデートを心がける
- ある程度枯れた技術を使う
- ハードウェアの障害
- 操作ミス
- ディスクの冗長化によって防ぐ
- 障害を前提に設計
- RAID
- ホットスワップ
- サーバの冗長化
レプリケーション
- 片方向レプリケーション
- 片方向/非同期
- マスタを更新したらスレーブに非同期に伝播する
- 「反映されていない」ときがある
- スレーブで最後まで受信されていない
- OSが生きていればSSH接続
- バイナリログの実行が終わっていない
- スレーブのバイナリログで遅延解消
- 最後まで実行されたか確認してから更新する
- 片方向/準同期
- バイナリログの受信を同期にする
- 更新情報受信が保証される
- レスポンスは遅くなる
- 片方向/同期
- すぐスレーブでサービス再開できる