データベース / 2023 ニフティ新人研修

2023年度ニフティエンジニア新人研修の講義資料です。

Transcript

1. Copyright © NIFTY Corporation All Rights Reserved. エンジニア定例2023DB回
2. Copyright © NIFTY Corporation All Rights Reserved. この資料について 2 • DBの基礎的な知識 ◦ DB概要 ◦ DBMS • RDBの設計の⽅法 • 正規化 • DBチューニングに関する知識 ◦ クエリチューニング ◦ DBチューニング • 基礎的なSQL ◦ データ操作 ◦ データ定義操作 • NoSQLの基礎
3. Copyright © NIFTY Corporation All Rights Reserved. DB概要 3 DBとは データベースとは • ⼀⾔で表すとデータの集まり ◦ もう少し詳しく⾔うと、 検索や蓄積が簡単にできるように整理されたデータの集まりのこと ⾝近に使われている場⾯ • 銀⾏の通帳 • クレジットカード • ⼾籍 ◦ などなど
4. Copyright © NIFTY Corporation All Rights Reserved. DB概要 4 DBの役割 役割 概要 データ保存・整理 ⻑期的かつ安全にデータを保管・関連性やカテゴリごとにデータを分類・整理 検索・参照機能 必要なデータを素早く簡単に抽出・参照 更新・編集機能 データの追加、更新、削除などの操作を⾏うことができ、正確さと整合性を保つ リレーショナル管理 複数のテーブル間でデータの関係性を設定し、効率的なデータ管理 データ処理・分析 データベース内の情報をもとに集計・分析ができる これ以外にもセキュリティの保持や、同時アクセス対応などの機能が存在する
5. Copyright © NIFTY Corporation All Rights Reserved. DB概要 5 DBMS DBMSとは︖ • データベース管理システム（DBMS）は、データベースにアクセスするためのソフトウェア • データの追加、検索、更新、削除などを⾏うためのインターフェースを提供する。 ◦ データベースの構造や操作に関する知識がなくても、簡単にデータベースを活⽤できる 図書館で例えると… • データベース→書庫 • データベース管理システム→司書さん ※「データベースを使おう」となったら、基本的にDBMSを使うのと同義
6. Copyright © NIFTY Corporation All Rights Reserved. DB概要 6 DBの種類 名前 特徴 代表DBMS リレーショナル型 テーブルで情報を管理する 個々のテーブルが他のテーブルとの関係性(リレーション)を持 っている もっともメジャーなDB Oracle MySQL PostgreSQL Microsoft SQL Server NoSQL リレーショナル以外のDBを指す。 MongoDB Redis
7. Copyright © NIFTY Corporation All Rights Reserved. DB概要 7 主なRDBMSのプロダクト Oracle Database • 商⽤のリレーショナルデータベース管理システム • ⼤規模なデータ処理を⾏うことができる Microsoft SQL Server • Microsoftが開発したリレーショナルデータベース 管理システム • 主にWindows環境で利⽤される
8. Copyright © NIFTY Corporation All Rights Reserved. DB概要 8 主な製品 MySQL • オープンソースのリレーショナルデータベース管理システム • Webアプリケーションや⼩規模なデータ処理に広く利⽤される PostgreSQL • オープンソースのリレーショナルデータベース管理システム • 拡張性や信頼性が⾼いと評価されている Amazon Aurora • クラウドベースのリレーショナルデータベース管理システム • パフォーマンスや可⽤性・耐久性など⾼い • 簡易化された管理機能が特徴
9. Copyright © NIFTY Corporation All Rights Reserved. RDBの設計 9 DB設計の⽬的 DB設計とは • 必要な情報をどういった構造でDB化するかを決めて、実際に設計する⼯程のこと なぜDB設計が必要︖ • DBの役割を発揮させるために必要な⼯程 ◦ データがバラバラに存在していたり整理されていなかったりする →必要な時に情報を上⼿く利⽤することが出来ない
10. Copyright © NIFTY Corporation All Rights Reserved. RDBの設計 10 良いDBにするためのポイント シンプルさ 複雑な操作や構造などがない シンプルで使いやすいデータベース設計がされている 知識の継承 データベース設計がドキュメント化されている 新しいメンバーが簡単に理解できる状態になっている セキュリティ ユーザーのデータが安全に保管されている データ漏洩や不正アクセスが起きないような設計がされている 拡張性 アプリケーションの要件やユーザー数が増えた際にデータベース構造を簡単に変更やスケールアップできる 保守性 データベース設計が変更やアップデートがしやすいようになっている ⻑期的な保守が容易に⾏える 良いDBにするために取り⼊れたいポイント（⼀例）
11. Copyright © NIFTY Corporation All Rights Reserved. RDBの設計 11 キーの種類 DBには様々なキーが存在する キー名 説明 主キー(プライマーキー) レコードを⼀意に識別できるように指定される項⽬のこと PKとも呼ばれる 候補キー 主キーとなりうるカラム 複合キー 複数の候補キーからなる主キー 代理キー 主キーに選ばれなかったらカラム ⾮キー 主キーになり得ないカラム これ以外にも⾃然キーや⼈⼯キー、スーパーキーなどが存在する
12. Copyright © NIFTY Corporation All Rights Reserved. RDBの設計 12 正規化とは データの重複をなくし整合的にデータを取り扱えるようにDBを設計することを正規化と呼ぶ 正規化は以下の6種類 • 第1正規化 • 第2正規化 • 第3正規化 ←ここまでが⼀般的に使う正規化 • ボイス-コッド正規化(第3.5正規化) • 第4正規化 • 第5正規化 基本的に⾏われる正規化は第3正規化まで ボイス-コッド 正規化以降は第3正規化まで⾏うと⾃然に満たされていることが多い
13. Copyright © NIFTY Corporation All Rights Reserved. RDBの設計 13 正規化 ~第⼀正規化~ 1つの枠に1つのデータが⼊っている状態にすること サークル 活動場所 ⾳楽研究会,絵画同好会 4208号室、3206号 室 国際ボランティアサークル,フォークソング研究 会 3104号室、講堂 アナウンス研究会,モダンジャズ研究会 美術室、4105号室 サークル 活動場所 ⾳楽研究会 4208号室 絵画同好会 3206号室 国際ボランティアサークル 3104号室 フォークソング研究会 講堂 アナウンス研究会 美術室 モダンジャズ研究会 4105号室
14. Copyright © NIFTY Corporation All Rights Reserved. RDBの設計 14 正規化 ~第ニ正規化~ 第1正規化が満たされた状態かつ部分関数従属が解消された状態にすること 部分関数従属とは 「ある属性が他の属性の⼀部に依存している状態」のこと 以下の例だと、{学籍番号}->{⽒名},{サークル}->{活動場所} が部分関数従属であると⾔える 学籍番号 ⽒名 サークル 活動場所 21A2122028 今井 直⼈ ⾳楽研究会 4208号室 21A2122028 今井 直⼈ 絵画同好会 3206号室 21B1012045 平岡 直⼦ 国際ボランティア サークル 3104号室 21B1012045 平岡 直⼦ フォークソング研 究会 講堂 21C1415321 前⽥ 洋⼀ アナウンス研究会 美術室 21C1415321 前⽥ 洋⼀ モダンジャズ研究 会 4105号室 学籍番号 ⽒名 21A2122028 今井 直⼈ 21B1012045 平岡 直⼦ 21C1415321 前⽥ 洋⼀ サークル 活動場所 ⾳楽研究会 4208号室 絵画同好会 3206号室 国際ボランティ アサークル 3104号室 フォークソング 研究会 講堂 アナウンス研究 会 美術室 学籍番号 サークル 21A2122028 ⾳楽研究会 21A2122028 絵画同好会 21B1012045 国際ボランティア サークル 21B1012045 フォークソング研 究会 21C1415321 アナウンス研究会 学籍番号・サークル {サークル}->{活動場所} {学籍番号}->{⽒名}
15. Copyright © NIFTY Corporation All Rights Reserved. RDBの設計 15 正規化 ~第三正規化~ 第2正規化が満たされた状態かつ推移的関数従属が解消された状態にすること 第２正規化は主キーから⾒て関数従属であるかを判断したか、第3正規化は主キー以外からみて関数従属である部分を抜き出す 主キーが絡まない関数従属として、 {学部} -> {キャンパス} の関係が⾒える 学籍番号 学部 キャンパス 21A2122028 ⽂学部 多摩 21B1012045 経済学部 多摩 21C1415321 理⼯学部 後楽園 第２正規化が終わっている表 主キーは学籍番号 学籍番号 学部 21A2122028 ⽂学部 21B1012045 経済学部 21C1415321 理⼯学部 学籍番号 ⽒名 21A2122028 今井 直⼈ 21B1012045 平岡 直⼦ 21C1415321 前⽥ 洋⼀ 学籍番号 ⽒名 21A2122028 今井 直⼈ 21B1012045 平岡 直⼦ 21C1415321 前⽥ 洋⼀ 学籍番号 ⽒名 21A2122028 今井 直⼈ 21B1012045 平岡 直⼦ 21C1415321 前⽥ 洋⼀ 学部 キャンパス ⽂学部 多摩 経済学部 多摩 理⼯学部 後楽園
16. Copyright © NIFTY Corporation All Rights Reserved. SQL 16 SQLとは SQL • RDBMSを操作するための⾔語のこと • SQLを⽤いて書かれたDBを操作する命令のことをクエリ（SQL⽂）という 特徴 • DBの中にある数万・数百万もの膨⼤なデータを効率的に操作することが可能 • 国際標準化されているため様々なDBで利⽤可能 ◦ 先程紹介したOracle、MySQL、PostgreSQL、Microsoft SQL Serverでも使える ◦ ただしRDBMSによって⽂法の違いやサポートされていない演算⼦がある
17. Copyright © NIFTY Corporation All Rights Reserved. SQL 17 SQLの種類 ~データ操作⾔語①~ id title 1 恐⻯図鑑 2 ⽣物図鑑 3 昆⾍図鑑 4 植物図鑑 SELECT（データの検索 ） 「book」テーブルの中で、idが2のデータを全て表⽰ 「book」テーブル SELECT * FROM book WHERE id = 2;
18. Copyright © NIFTY Corporation All Rights Reserved. SQL 18 SQLの種類 ~データ操作⾔語①~ id title 1 恐⻯図鑑 2 ⽣物図鑑 3 昆⾍図鑑 4 植物図鑑 SELECT * FROM book WHERE id = 2; SELECT（データの検索 ） id title 2 ⽣物図鑑 「book」テーブルの中で、idが2のデータを全て表⽰ 「book」テーブル
19. Copyright © NIFTY Corporation All Rights Reserved. SQL 19 SQLの種類 ~データ操作⾔語①~ id title 1 恐⻯図鑑 2 ⽣物図鑑 3 昆⾍図鑑 4 植物図鑑 INSERT（データの追加） 「book」テーブルに、idが5で⿂図鑑のデータを追加 「book」テーブル INSERT INTO book (id, title) VALUES (5, ʻ⿂図鑑);
20. Copyright © NIFTY Corporation All Rights Reserved. SQL 20 SQLの種類 ~データ操作⾔語①~ id title 1 恐⻯図鑑 2 ⽣物図鑑 3 昆⾍図鑑 4 植物図鑑 INSERT（データの追加） 「book」テーブルに、idが5で⿂図鑑のデータを追加 「book」テーブル INSERT INTO book (id, title) VALUES (5, ʻ⿂図鑑); id title 1 恐⻯図鑑 2 ⽣物図鑑 3 昆⾍図鑑 4 植物図鑑 5 ⿂図鑑
21. Copyright © NIFTY Corporation All Rights Reserved. SQL 21 SQLの種類 ~データ操作⾔語①~ id title 1 恐⻯図鑑 2 ⽣物図鑑 3 昆⾍図鑑 4 植物図鑑 UPDATE（データの更新） idが1のデータを、titleが恐⻯図鑑のデータに更新 「book」テーブル UPDATE book SET title = ʻ⿂図鑑ʼ WHERE id = 1;
22. Copyright © NIFTY Corporation All Rights Reserved. SQL 22 SQLの種類 ~データ操作⾔語①~ id title 1 恐⻯図鑑 2 ⽣物図鑑 3 昆⾍図鑑 4 植物図鑑 UPDATE（データの更新） idが1のデータを、titleが恐⻯図鑑のデータに更新 「book」テーブル UPDATE book SET title = ʻ⿂図鑑ʼ WHERE id = 1; id title 1 ⿂図鑑 2 ⽣物図鑑 3 昆⾍図鑑 4 植物図鑑
23. Copyright © NIFTY Corporation All Rights Reserved. SQL 23 SQLの種類 ~データ操作⾔語①~ id title 1 恐⻯図鑑 2 ⽣物図鑑 3 昆⾍図鑑 4 植物図鑑 DELETE（データの削除） idが2のデータを削除 「book」テーブル DELETE FROM book (id, title) WHERE id = 2;
24. Copyright © NIFTY Corporation All Rights Reserved. SQL 24 SQLの種類 ~データ操作⾔語①~ id title 1 恐⻯図鑑 2 ⽣物図鑑 3 昆⾍図鑑 4 植物図鑑 DELETE（データの削除） idが2のデータを削除 「book」テーブル DELETE FROM book (id, title) WHERE id = 2; id title 1 恐⻯図鑑 3 昆⾍図鑑 4 植物図鑑
25. Copyright © NIFTY Corporation All Rights Reserved. SQL 25 SQLの種類 ~データ定義⾔語~ CREATE（テーブルの作成） id(int)とtitle(varchar)を持つ「book」テーブルを「mydb」 データベースの中に作成 CREATE TABLE mydb.book ( id INT , title VARCHAR(128) );
26. Copyright © NIFTY Corporation All Rights Reserved. SQL 26 SQLの種類 ~データ定義⾔語~ CREATE（テーブルの作成） id(int)とtitle(varchar)を持つ「book」テーブルを「mydb」 データベースの中に作成 CREATE TABLE mydb.game ( id INT , title VARCHAR(128) ); id title
27. Copyright © NIFTY Corporation All Rights Reserved. SQL 27 SQLの種類 ~データ定義⾔語~ id title 1 恐⻯図鑑 2 ⽣物図鑑 3 昆⾍図鑑 4 植物図鑑 ALTER（テーブルの設定変更） 「book」テーブルにint型でplaceというカラムを追加 「book」テーブル ALTER TABLE book ADD place INT;
28. Copyright © NIFTY Corporation All Rights Reserved. SQL 28 SQLの種類 ~データ定義⾔語~ id title 1 恐⻯図鑑 2 ⽣物図鑑 3 昆⾍図鑑 4 植物図鑑 ALTER（テーブルの設定変更） 「book」テーブルにint型でplaceというカラムを追加 「book」テーブル ALTER TABLE book ADD place INT; id title place 1 恐⻯図鑑 2 ⽣物図鑑 3 昆⾍図鑑 4 植物図鑑
29. Copyright © NIFTY Corporation All Rights Reserved. SQL 29 SQLの種類 ~データ定義⾔語~ DROP（テーブルの削除） 「book」テーブルを削除 DROP TABLE book; id title 1 恐⻯図鑑 2 ⽣物図鑑 3 昆⾍図鑑 4 植物図鑑
30. Copyright © NIFTY Corporation All Rights Reserved. SQL 30 SQLの種類 ~データ定義⾔語~ DROP（テーブルの削除） 「book」テーブルを削除 DROP TABLE book; id title 1 恐⻯図鑑 2 ⽣物図鑑 3 昆⾍図鑑 4 植物図鑑 テーブル削除
31. Copyright © NIFTY Corporation All Rights Reserved. SQL 31 SQLの種類 ~データ定義⾔語~ TRUNCATE（テーブルデータの削除） 「book」テーブルのtableデータをデータを削除 TRUNCATE TABLE book; id title 1 恐⻯図鑑 2 ⽣物図鑑 3 昆⾍図鑑 4 植物図鑑
32. Copyright © NIFTY Corporation All Rights Reserved. SQL 32 SQLの種類 ~データ定義⾔語~ TRUNCATE（テーブルデータの削除） 「book」テーブルのテーブルデータを削除 TRUNCATE TABLE book; id title 1 恐⻯図鑑 2 ⽣物図鑑 3 昆⾍図鑑 4 植物図鑑 id title
33. Copyright © NIFTY Corporation All Rights Reserved. チューニング 33 クエリチューニング① ~スロークエリ~ スロークエリとは • 実⾏時間が⼀定の基準を超える遅いクエリのこと • 基準時間はシステムによって様々(⼀般的には1秒以上) • スロークエリはログに出⼒できる(スロークエリログ) スロークエリの原因 • クエリに何かしらの問題がある場合(不要なデータを参照しているなど) ◦ 対策︓期待された結果から変わらないようにクエリを書き換える • 巨⼤なテーブルデータ全体を読み取っている（フルスキャン）場合 ◦ 対策︓カラムにインデックスを貼ってフルスキャンを回避 ◦ インデックスについては次のスライドで説明
34. Copyright © NIFTY Corporation All Rights Reserved. チューニング 34 クエリチューニング② ~インデックス~ インデックスとは • データベースの検索性能を向上させる⽅法の⼀種で索引のようなもの インデックスが有効なケース • 項⽬数／データ数が多い⼤規模な表の1%〜15%程の⾏を頻繁にアクセスする • WHERE句やORDER BY句、GROUP BY句等で頻繁に使⽤されている列である ◦ 例︓SELECT * FROM employees WHERE = (SELECT MIN() FROM employees); ▪ WHERE句やMIN関数で使⽤されているbirth_dateカラムにインデックスを貼る • 列の値が⽐較的⼀意である（検索で絞り込みやすい） ◦ カーディナリティが⾼い（データの種類が多い） ▪ 例︓都道府県 → カーディナリティ=47
35. Copyright © NIFTY Corporation All Rights Reserved. チューニング 35 クエリチューニング② ~インデックス~ インデックスは万能ではない インデックスが有効ではないケース • データ登録件数が少ない表である。 ◦ データ件数が少ない場合、フルスキャンのほうが速いことがある。 • NULL値が多く、NULL以外の値を検索しない。 • インデックスを作成した⽅がいいケースとそうでないケースがある
36. Copyright © NIFTY Corporation All Rights Reserved. チューニング 36 DBチューニング データベースの実⾏環境をチューニングし、レスポンスを上げる • DBのサーバスペックを上げる • サーバをDB向きのタイプに変更する • DBの利⽤状況に応じてディスクの性能を⾒直す ◦ 書き込みが多いDBの場合 ◦ 読み込みが多いDBの場合 • DBMSの設定を⾒直す ◦ バッファの量を増やす(減らす) • ストアドプログラムを⾒直す etc…
37. Copyright © NIFTY Corporation All Rights Reserved. NoSQL 37 NoSQLとは RDBMS以外のDBMSのことをNoSQLと呼ぶ • RDBにはカラムの追加に対して柔軟性がない ◦ テーブル設計が必要 ◦ 各カラム(列)の型が厳密に決まっている ◦ データ量に⽐例して処理時間も増加していく • NoSQLを使⽤することで、膨⼤な量のデータを処理することができるようになる
38. Copyright © NIFTY Corporation All Rights Reserved. NoSQL 38 メリット・デメリット メリット • 拡張性 ◦ ⽔平スケーリング(マシンの台数を増やすスケーリング⽅法)に適しているため、あとからサ ーバの台数を増やしやすい ◦ RBDMSは主に垂直スケーリング(マシンのスペックを上げるスケーリング⽅法)で対応する • ⾼速性 ◦ ⼤量のデータの処理を捌くのが得意 ◦ RDBはデータ量が増えるほど、処理時間も増加していく • 柔軟性 ◦ テーブルの構造を厳密に指定せず、そのままの形で拡張可能 • 分散処理 ◦ 複数のノード(サーバ)に処理を分散することができる • ⾼可⽤性 ◦ 複数ノード運⽤に適しているため、マルチゾーンの構成にしやすい ◦ そのため耐障害性が⾼い
39. Copyright © NIFTY Corporation All Rights Reserved. NoSQL 39 メリット・デメリット デメリット • RDBで使える⼀部機能が使えない ◦ 後述のKVSやドキュメント指向型では集計クエリやソートが使えない • ⼀部のモデルではトランザクションが使えない ◦ 処理中にシステムがダウンすると、中途半端な状態になってしまう ◦ 同時並⾏で処理が実⾏された場合、整合性が取れなくなる可能性がある • 命名規則などを決めないと闇鍋になる ◦ 何でも⼊るからと⾔って、命名規則なしにデータを⼊れるとデータの管理や検索が⼤変 • トラブルシューティングに技術とシステムに対するドメイン知識が必要 ◦ RDBの⽤に厳密なスキーマ定義などが決められているわけではない → システムに合わせた対策が必要 • 要件定義(ユースケース)をはっきりさせてから設計しなくてはいけない
40. Copyright © NIFTY Corporation All Rights Reserved. NoSQL 40 種類 ドキュメント指向型 カラム指向型(ワイドカラム) グラフ キーバリュー(KVS) 階層型 • ドキュメントでデータを管理するNoSQL • XMLやJSONなどの形でデータを保存することが可能 • 「列」単位でデータを管理する • 圧縮や分散処理に向いている • データをグラフ(ノード、エッジ、プロパティの３要素で構成される)で管理 • データと⼀緒にキーを保存する形式 • IoTセンサーのデータ記録や株価・為替データの管理などに向いている • 親ノードと⼦ノードを使ってツリーでデータを管理
41. Copyright © NIFTY Corporation All Rights Reserved. NoSQL 41 具体的なプロダクト ｶﾞﾐｨ
42. Copyright © NIFTY Corporation All Rights Reserved. まとめ 42 • DBの基礎的な知識 ◦ DB概要 ◦ DBMS • RDBの設計の⽅法 • 正規化 • DBチューニングに関する知識 ◦ クエリチューニング ◦ DBチューニング • 基礎的なSQL ◦ データ操作 ◦ データ定義操作 • NoSQLの基礎
43. Copyright © NIFTY Corporation All Rights Reserved.