データベースのキャッチアップ
ゆずRDBとnon RDB
non RDBの色々
Key-value store ... スキーマのないデータモデル。キーと値のペアで構成されている。キーはショッピングカートのIDのようなもので、値はそのカート内のアイテムの配列です。このモデルは、ユーザーのセッション情報をキャッシュや保存するのに適していますが、複数のレコードを同時に取得するには不向きです。RedisとMemcachedがこのモデルを持つオープンソースのデータベースの例です。
Document store … データをJSONやXML形式のドキュメントとして保存し、半構造化データの管理に役立つ。これにより、データの使用時に翻訳が少なくて済み、柔軟性が向上しますが、複雑なトランザクションではデータの破損が起こる可能性があります。一般的な用途には、コンテンツ管理システムやユーザープロファイルがあり、MongoDBが代表的な例です。
ゆずRDBのメリット
リレーショナルデータベースの主なメリットは、テーブルを結合してデータ間の関係を理解し、意味のある情報を生成できることです。
SQLを使用することで、データの集計やフィルタリング、並び替えが容易に行え、複雑なクエリも簡単に実行できます。
さらに、リレーショナルデータベースはデータの冗長性を削減するための正規化をサポートし、データの一貫性と整合性を保ちます。
また、バックアップや災害復旧が容易で、クラウド環境での柔軟性も向上しています。これらの特性により、リレーショナルデータベースはビジネスで最も人気のあるクエリツールとなっています。
ゆずPostgreSQLの環境構築
自分はMacを使っているので、homebrewからインストールする
brew formulae | grep postgresql@
postgresql@11
postgresql@12
postgresql@13
postgresql@14
postgresql@15
postgresql@16
postgresql@17
Homebrewは、macOS用のパッケージマネージャーで、brew formulaeはHomebrewで利用可能なすべてのパッケージ（フォーミュラ）のリストを表示できるコマンド。
grepを使って、postgresql@で絞り込み
brew install postgresql@16
エラーが出ていなければインストールは成功しているはずです
実行結果をみると、どうやら過去にもpostgresqlをインストールしていたようで、今インストールしているバージョンを使用したい場合のコマンドなどが書かれています。
If you need to have postgresql@16 first in your PATH, run:
fish_add_path /usr/local/opt/postgresql@16/bin
For compilers to find postgresql@16 you may need to set:
set -gx LDFLAGS "-L/usr/local/opt/postgresql@16/lib"
set -gx CPPFLAGS "-I/usr/local/opt/postgresql@16/include"
For pkg-config to find postgresql@16 you may need to set:
set -gx PKG_CONFIG_PATH "/usr/local/opt/postgresql@16/lib/pkgconfig"
To start postgresql@16 now and restart at login:
brew services start postgresql@16
Or, if you don't want/need a background service you can just run:
LC_ALL="C" /usr/local/opt/postgresql@16/bin/postgres -D /usr/local/var/postgresql@16
下記コマンドを実行して環境変数PATHに登録します。
その後、psqlを使って現在使用可能なバージョンを確認するとバージョン16になっていました。
fish_add_path /usr/local/opt/postgresql@16/bin
psql --version
psql (PostgreSQL) 16.6 (Homebrew)
今回DB操作を行うツールはDBreverというものを使います(というか実務で使ってるので)
ゆず正規化
RDBでは正規化をおこなうことでデータの整合性を保っている
しかし、正規化をするとテーブルを複合してデータを取得する必要が出てくる
その場合 Joinと呼ばれる処理を行う必要がでてくるが、joinにはとても負荷がかかり処理が遅くなる
例えば30回ほどjoinするとデータがぜんぜん返ってこなくなる
TODO:やってみる
ゆずインデックス
インデックスを作ることで検索速度の向上をさせることができる
しかしインデックスを作る事自体が、RDBには負荷となる
内部的には、インデックス用の別テーブルの作成、それの最新化などを常に行っているかららしい
TODO: これも確認できると良いかも
- インデックスの最新化には、コンピュータのリソースを
- インデックス自体もディスクスペースを消費することになる
テーブルの変更を行う際は、データだけでなくインデックスも変更する必要がある（削除など）
また、頻度も重要で月に一度しか使わなくインデックスであれば、そのときにインデックスを作成し
終わったら削除するなど工夫した方が良い
そのため、使うインデックスだけが整備された状態にしておくのが望ましい
TODO: そういう処理を書くなどするといいかも
ゆずビュー
自身ではデータを持たない仮想テーブル
毎回検索をせずともビューを保存しておくことで、それに対してアクセスできるようになる
特徴
- 抽象化 ... 複雑なselect文を打たなくても必要な情報を取得できる
- データアクセス制限 ... テーブルの全データではなく、特定の行、列にしぼってみせることができる
- 便利性 ... 頻繁に使うものビューにできる
CREAYE_VIEWなどのコマンドで作成できる
ゆずSQL入門
データベース構造を定義する言語（DDL）のコマンド
| コマンド | 説明 |
|---|---|
CREATE |
新しいデータベース、テーブル、ビュー、インデックスを作成する。 |
DROP |
データベース、テーブル、ビュー、インデックスを削除する。 |
ALTER |
データベース、テーブル、ビュー、インデックスを更新する。 |
TRUNCATE |
テーブルからデータを削除する（取り消せない）。 |
RENAME |
テーブルやカラムの名前を変更する。 |
COMMENT |
テーブルやカラムにコメントを追加する。 |
データ操作言語（DML）コマンド
| コマンド | 説明 |
|---|---|
SELECT |
データベースからデータを取得する。 |
INSERT |
新しいデータをテーブルに挿入する。 |
UPDATE |
既存のデータを更新する。 |
DELETE |
テーブルからデータを削除する。 |
MERGE |
テーブルに対して条件に基づいてINSERT、UPDATE、DELETEを行う。 |
CALL |
ストアドプロシージャを呼び出す。 |
EXPLAIN |
クエリの実行計画を表示する。 |
データに関するアクセス制御を行う（DCL）コマンド
| コマンド | 説明 |
|---|---|
GRANT |
ユーザーやロールに権限を付与する。 |
REVOKE |
ユーザーやロールから権限を取り消す。 |
基本クエリ
| 記述順 | クエリ | 説明 | 実行順 |
|---|---|---|---|
| 1 | SELECT |
検索するテーブルの列を決めます。 | 6 |
| 2 | FROM |
検索する対象のテーブルを指定し、結合の方法を決めます。 | 1 |
| 3 | JOIN |
複数のテーブルを結合します。 | 2 |
| 4 | WHERE |
データをフィルタリングします。 | 3 |
| 5 | GROUP BY |
共通の列の値に基づいて行をグループ化します。 | 4 |
| 6 | HAVING |
グループをフィルタリングします。 | 5 |
| 7 | ORDER BY |
列に基づいて行を並び替えます。 | 7 |
| 8 | LIMIT |
取得する行数を制限します。 | 8 |
| 9 | OFFSET |
取得する行の開始位置を指定します。 | 9 |
フィルタリング
| コマンド | 説明 |
|---|---|
WHERE first_name = 'Bob' |
first_nameがBob |
WHERE age > 20 |
20歳より上 |
WHERE age BETWEEN 40 AND 65 |
40歳から65歳 |
WHERE last_name LIKE 'A%' |
last_nameがAで始まる |
WHERE city IN ('Tokyo', 'Chiba', 'Kanagawa') |
cityがTokyo, Chiba, Kanagawaのいずれか |
WHERE salary IS NULL |
salaryがNULL |
WHERE salary IS NOT NULL |
salaryがNULLでない |
WHERE NOT (age < 18) |
18歳未満ではない |
WHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = customers.id) |
ordersテーブルに関連するレコードが存在する |
WHERE date >= '2023-01-01' |
日付が2023年1月1日以降 |
WHERE UPPER(first_name) = 'BOB' |
first_nameを大文字に変換してBobと一致する |
WHERE LENGTH(description) > 100 |
descriptionの長さが100文字を超える |
WHERE price BETWEEN 100 AND 200 |
priceが100から200の範囲内 |
WHERE category NOT IN ('Electronics', 'Toys') |
categoryがElectronicsやToysではない |
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days' |
作成日が過去7日以内 |
WHERE REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z]{2,}$') |
emailが正規表現に一致する |
WHERE COALESCE(discount, 0) > 0 |
discountがNULLの場合は0として扱い、0より大きい |
WHERE jsonb_exists(data, 'key') |
JSONBフィールドdataにkeyが存在する |
WHERE ARRAY[1, 2, 3] <@ numbers |
配列numbersが1, 2, 3をすべて含む |
WHERE CAST(age AS TEXT) LIKE '2%' |
ageを文字列に変換して2で始まる |
集合
| コマンド | 説明 | 結果 |
|---|---|---|
Table1 UNION Table2 |
両方のテーブルから重複を除いて結合する。 | A, B, C |
Table1 INTERSECT Table2 |
両方のテーブルに共通する行を取得する。 | C |
Table1 EXCEPT Table2 |
Table1にあり、Table2にない行を取得する。 | A |
(Table1 UNION Table2) EXCEPT (Table1 INTERSECT Table2) |
両方のテーブルの結合から共通部分を除く。 | A, B |
| クエリ | 説明 |
|---|---|
SELECT AgeRange, COUNT(*) AS VisitorCount FROM CustomerBuy GROUP BY AgeRange ORDER BY AgeRange; |
年代別の来場者数を集計し、年代順に並べる。 |
SELECT AVG(BuyUnit) AS AverageBuyUnit FROM CustomerBuy; |
平均購入額を計算する。 |
グループ化と集計
集計関数に対して AS を使い別名を設定しています
CustomerBuyテーブル
| CustomerID | AgeRange | BuyUnit |
|---|---|---|
| 1 | 20 | 100 |
| 2 | 30 | 150 |
| 3 | 40 | 300 |
| 4 | 20 | 200 |
| 5 | 50 | 150 |
| 6 | 30 | 400 |
| 集計関数 | 説明 | 使用例 |
|---|---|---|
MAX() |
集合内の最大値を取得する。 | SELECT MAX(BuyUnit) AS MaxBuyUnit FROM CustomerBuy; |
MIN() |
集合内の最小値を取得する。 | SELECT MIN(BuyUnit) AS MinBuyUnit FROM CustomerBuy; |
AVG() |
集合内の平均値を計算する。 | SELECT AVG(BuyUnit) AS AverageBuyUnit FROM CustomerBuy; |
SUM() |
集合内の合計を計算する。 | SELECT SUM(BuyUnit) AS TotalBuyUnit FROM CustomerBuy; |
COUNT() |
集合内の値の個数を数える。 | SELECT COUNT(*) AS TotalCount FROM CustomerBuy; |
サブクエリ
SQLクエリの中に埋め込まれたSQLクエリ、通常( )で囲まれているサブクエリを使えばより複雑な条件でデータ抽出ができる
CustomerBuy テーブル
| CustomerID | AgeRange | BuyUnit |
|---|---|---|
| 1 | 20 | 100 |
| 2 | 30 | 150 |
| 3 | 40 | 300 |
| 4 | 20 | 200 |
| 5 | 50 | 150 |
| 6 | 30 | 400 |
| 使用例 | 説明 |
|---|---|
SELECT CustomerID FROM CustomerBuy WHERE BuyUnit > (SELECT AVG(BuyUnit) FROM CustomerBuy); |
平均購入額を上回ったCustomerIDを取得する。 |
SELECT * FROM CustomerBuy WHERE AgeRange IN (SELECT AgeRange FROM CustomerBuy WHERE BuyUnit > 200); |
購入額が200を超える年齢層のすべてのレコードを取得する。 |
| サブクエリの使用例（WHERE句以外） | 説明 |
|---|---|
SELECT CustomerID, (SELECT MAX(BuyUnit) FROM CustomerBuy) AS MaxBuyUnit FROM CustomerBuy; |
各CustomerIDと全体の最大購入額を表示する。 |
SELECT CustomerID, BuyUnit FROM CustomerBuy ORDER BY BuyUnit DESC LIMIT 1 OFFSET (SELECT COUNT(*) FROM CustomerBuy) / 2; |
中央値の購入額を持つCustomerIDを取得する。 |
INSERT INTO HighSpenders (CustomerID) SELECT CustomerID FROM CustomerBuy WHERE BuyUnit > (SELECT AVG(BuyUnit) FROM CustomerBuy); |
平均購入額を上回るCustomerIDをHighSpendersテーブルに挿入する。 |
ゆずこちらの研修動画を参考にしています
ゆずSQL入門2
(長くなってきたので、分割)
ストアドプロシージャ
DB内部に作成するスクリプト
ストアドプロシージャは、データベース内に保存され、必要に応じて実行できるSQLコードの集合です。以下にその概要を示します。
特徴
-
事前コンパイル:
- ストアドプロシージャは事前にコンパイルされるため、実行が高速です。
-
パラメータ化:
- パラメータを受け取ることができ、柔軟な処理が可能です。
-
再利用性:
- 一度作成すれば、何度でも呼び出して使用できるため、再利用が容易です。
-
ビジネスロジックの効率化:
- 複雑なビジネスロジックをデータベース側で効率的に実行できます。
メリット
- 高速な実行: 事前コンパイルにより、クエリの実行が迅速です。
- アプリケーションの簡素化: アプリケーション側で複雑なロジックを持たずに済みます。
デメリット
- DBMS依存: データベース管理システム（DBMS）間の互換性が低く、特定のDBMSにロックインされる可能性があります。
ストアドプロシージャは、効率的なデータ処理とビジネスロジックの実装に役立ちますが、DBMS依存性に注意が必要です。
TODO: とAIに聞いたら出てくるけど、もうすこし情報がほしいので、別途調べてみる
ゆず非リレーショナル・データベースについて
もしスケールが必要な場合に、RDBとnonRDBだと手法が異なってくる
| 項目 | スケールアップ（垂直スケール） | スケールアウト（水平スケール） |
|---|---|---|
| 説明 | インスタンススペックを増やす（大きくする）こと。 | インスタンス自体を増やすこと。 |
| メリット | リソースが余っていれば比較的容易にアップ可能。 | 仕組みができていれば、インスタンス追加だけでスケール可能。 |
| 注意点 | DBMSのパラメータも一緒に変える必要あり。 | インスタンス間の同期や整合性が必要。 |
| デメリット | 物理的な変更が難しい。システム停止を伴うのが基本。 | 急激なアクセス増に対応可能。 |
| RDBの特性 | スケールアップが得意。データの整合性とトランザクション管理が重要なため。 | - |
| 非リレーショナルDBの特性 | - | スケールアウトが得意。データの分散と可用性を重視するため。 |
ゆずNoSQL
IoT、AIの進化によるビッグデータ需要の高まり、SNSによるリアルタイムデータ処理の必要性がでてきたため、NoSQLの活躍が増えてきた。
SQLを使わないのでNoSQLと呼ぶ
RDBSの特徴であった、正規化、ACID特性は存在が薄くなる
スパイクアクセスに強い ... キャンペーンの受付開始や告知直後など、短期間で急激にアクセスが集中する現象のこと
非リレーションデータベースの特徴
キーバリューストア
| 項目 | 内容 |
|---|---|
| 長所 | - シンプルな操作 - APIがシンプルで扱いやすい - パフォーマンス - 高速なデータ構造 - 柔軟性 - データモデルの変更が容易 - 拡張性 - 分散アーキテクチャで高拡張性 |
| 短所 | - 複雑なクエリが苦手 - 複雑なクエリやJOINは避ける - 弱い一貫性 - 分散アーキテクチャのトレードオフ - トランザクション管理 - 複雑なトランザクションならRDB |
| ユースケース | セッション管理、キャッシュ、ショッピングカート、チャットなどのリアルタイム処理 |
| 代表的な製品 | Redis, Memcached, etcd, Amazon DynamoDB, Cloud Datastore |
Redis使ってチャットのリアル処理やってみたい
ドキュメントデータベース
| 項目 | 内容 |
|---|---|
| 長所 | - 開発の効率化 - アプリケーションのオブジェクトと似たデータ構造 - スケーラビリティ - 分散アーキテクチャで拡張性が高い - データ表現の豊かさ - 複雑なデータ型やネスト構成が可能 - スキーマ変更の容易さ - スキーマフリー |
| 短所 | - 集約操作が苦手 - JOINするような用途には向かない - トランザクションが限定的 - トランザクション前提な用途ではない - 弱い一貫性 - 分散アーキテクチャのトレードオフ |
| ユースケース | CMS、商品カタログ、ユーザープロファイル、多様なデバイスから多様なデータを受信するケース |
| 代表的な製品 | MongoDB, Couchbase, Amazon DocumentDB, Cloud Firestore |
ワイドカラム
| 項目 | 内容 |
|---|---|
| 長所 | - 大規模データ運用 - ペタバイト規模の分散処理 - 柔軟なデータモデル - 動的なスキーマ変更が可能 - 効率的なストレージ活用 - 使用されるカラムのみ保存される - 高速なクエリ - カラムへのアクセスや集計が高速 |
| 短所 | - 設計が複雑になりがち - RDBとは異なるアプローチが必要 - 整合性が苦手 - 一貫性はアプリで保証（したほうが良い） - 独自のクエリ - 複雑なクエリやJOINは苦手 |
| ユースケース | ビッグデータのリアルタイム処理、リコメンデーションエンジン、大量のイベントログ管理 |
| 代表的な製品 | Cassandra, Google Bigtable, Amazon Keyspaces, ScyllaDB |
インメモリ
| 項目 | 内容 |
|---|---|
| 長所 | - 処理速度の向上 - 高スループット、低レイテンシー - スケーラビリティ - 水平スケーリングに対応 |
| 短所 | - コスト - RAMはディスクに比べて高額 - データの揮発性 - 電源喪失でデータは消える - データ永続化処理が別途必要 - リソース制約 - ディスクに比べるとリソース増が難しい |
| ユースケース | キャッシュ、セッションストア、ゲームバックエンド、チャット |
| 代表的な製品 | Redis, Memcached, SAP HANA, Apache Ignite |
グラフ
| 項目 | 内容 |
|---|---|
| 長所 | - 時系列処理のパフォーマンス - 時系列の収集・保存・分析に特化 - データの洞察力 - 高度な分析機能を有していることがほとんど - スケーラビリティ - 分散アーキテクチャの利点 |
| 短所 | - 汎用性は無い - 非時系列データには向かない - クエリの制限 - JOINなどの複雑なクエリは不向き - 特殊性 - ニーズを満たすには条件がある |
| ユースケース | IoTデバイスデータの収集、金融マーケット、メトリクス、ログ |
| 代表的な製品 | InfluxDB, TimescaleDB, Amazon Timestream, Google Bigtable |
NewSQL
RDBの水平スケールができない課題を解決するために生まれた
RDBMSの特性を維持しつつ、スケーラビリティも備えるGoogle Spanner、TiDB、ｍCokroachDBがある
| 項目 | 内容 |
|---|---|
| 長所 | - スケーラビリティ - 分散アーキテクチャの利点 - 高いトランザクション性能 - トランザクション処理の実現 - 強力なデータ整合性 - ACID特性の維持 - リレーショナルデータモデル - 既存データモデルの活用 |
| 短所 | - 複雑性 - DBMSの管理が難しくなりがち - 成熟度 - まだ新しい技術 - 既存からの移行 - 移行にかかるコストは無視できない |
| ユースケース | 金融取引、eコマース、ゲーム、ソーシャルネットワーク |
| 代表的な製品 | Google Spanner, TiDB, CockroachDB |
ゆずAWSが提供しているクラウドデータベース
| データベースのタイプ | 例 | AWS のサービス |
|---|---|---|
| リレーショナル | 従来のアプリケーション、ERP、CRM、eコマース | Amazon Aurora, Amazon RDS, Amazon Redshift |
| Key-Value | トラフィックの多いウェブアプリケーション、eコマースシステム、ゲームアプリケーション | Amazon DynamoDB |
| インメモリ | キャッシュ、セッション管理、ゲームのリーダーボード、地理空間アプリケーション | Amazon ElastiCache, Amazon MemoryDB for Redis |
| ドキュメント | コンテンツ管理、カタログ、ユーザープロファイル | Amazon DocumentDB (MongoDB 互換) |
| ワイドカラム | 高スケールの業界アプリケーション、設備のメンテナンス、多数の装置の管理、ルートの最適化 | Amazon Keyspaces |
| グラフ | 不正検出、ソーシャルネットワーク、レコメンデーションエンジン | Amazon Neptune |
| タイムシリーズ | モノのインターネット (IoT) アプリケーション、DevOps、産業用テレメトリ | Amazon Timestream |
| レジャー | 記録システム、サプライチェーン、銀行トランザクション | Amazon 台帳データベースサービス (QLDB) |
GoogleCloudのクラウドサービス
| データベースの種類 | GOOGLE CLOUD サービス | ユースケースの例 |
|---|---|---|
| リレーショナル | Cloud SQL | CRM、ERP、eコマースとウェブ、SaaSアプリケーション |
| AlloyDB for PostgreSQL | 異種環境間の移行、レガシーアプリケーション、エンタープライズワークロード、ハイブリッドクラウド、マルチクラウド、エッジ | |
| Spanner | ゲーム、小売、グローバルの財務元帳、サプライチェーン/在庫管理 | |
| Oracle 向け Bare Metal Solution | マルチクラウド分析、リアルタイム処理、組み込みのML | |
| Key-Value | Bigtable | パーソナライズ、アドテック、レコメンデーションエンジン、不正行為の検出 |
| ドキュメント | Firestore | モバイル/ウェブ/IoTアプリケーション、リアルタイム同期、オフライン同期 |
| Firebase Realtime Database | モバイルログイン、パーソナライズしたアプリケーションと広告、アプリ内チャット | |
| インメモリ | Memorystore | キャッシュ、ゲーム、リーダーボード、ソーシャルチャットまたはニュースフィード |
| その他の NoSQL | MongoDB Atlas | モバイル/ウェブ/IoTアプリケーション、ゲーム、コンテンツ管理、シングルビュー |
| Google Cloud Partner Services | 既存の投資を活用 |
マネージドサービス
ほとんどの処理を自動で行ってくれるため、開発者はデータベースの業務から離れることができる
| 担当者 | タスク |
|---|---|
| You | スキーマのデザイン、クエリの作成、データベースの最適化 |
| AWS | 自動化されたフェイルオーバー、バックアップとリカバリ、分離とセキュリティ |
| コンプライアンスへの準拠、数クリックでスケール、自動化されたパッチ適用 | |
| 拡張モニタリング、OS、DBのインストール |
Aurora
クラウドデータベースならグローバル展開しやすい
スケーラビリティ
自動でスケールしてくれるシステムが有る
料金
なにをするとお金がかかってしまうのか理解すること
注意点
ゆずデータベース選定方法
判断がつかない場合はPoCを行う
本番以外でアプリを動かしてみてテスト、計測を行う
Amazonにはどれを使えばよいかのシュミレータのようなものがある
選択に役立つ質問
マイクロサービスとデータベース
少し前は、大きなサービスに対して、大きなデータベースを用意していたが、いまは小さなマイクロサービㇲごとにDBをもつ、サービスごとに異なるDBを設定する
一つのDBを使うのではなく、すべてのDBの特性を理解して使う必要がある
DBとDBで連携したい場合はAPIで取得する
分散システム
ゆずDBセキュリティ
SQLインジェクション対策
