データベースの基礎

言語の基礎
データベース操作 ← いまここ
WAF によるウェブアプリケーション開発
JavaScript で学ぶイベントドリブン
自由課題

アンケート

SQL書いたことある
プログラミング言語から使ったことある

今日の講義

基本編
- データベースの基本的な概念や使い方を紹介します
実践編
- Perl/ScalaでMySQLにアクセスする方法を学ぶ
- RDBMSを使った簡単なブックマーク管理ツールの作り方をなぞります
課題の解説

ご注意

駆け足で進みますのでがんばってついてきてください
質問があれば途中でも聞いてください
- わからないところをメモっておいて後で聞くのも良いです

データベースとは

データ (data) とは
- = コンピュータで取り扱う情報
データベース (database) とは
- = データを集めて取り扱いやすくしたもの

簡単なデータベースの例

2ちゃんねるの機能
- 多くの鯖に分散されたスレッド
- スレッドを閲覧
- スレッドの最後にレスを追加
- スレッドを立てる
データストレージ = dat ファイル (1 行 1 レス)

名無しさん<>sage<>2011/08/19(金) 06:19:10.13 <> &gt;&gt;1乙 <>
名無しさん<>sage<>2011/08/19(金) 06:21:30.21 <> こんにちは <>

このようなデータベースは簡単だが、デメリットはあるだろうか？

2ちゃんねる+αを考えてみる

ユーザページ: 過去の書き込みを一覧できるように
- → dat ファイルにユーザ名を記録して、一覧するときに全部検索？
耐障害性: マシンが一台故障してもサービスが継続できるように
- → dat ファイルを複数のマシンにコピーする？

ウェブサービスのデータは増え続けます

データは大量・増える一方
アクセスも増える一方
サービスは 24 時間 365 日提供したい
データは消えてはならない
昨日作ったdiary-file.plを思い出してみよう

そのためのデータベース管理です

データベース管理システム (DataBase Management System = DBMS)
データの抽象化
- データがディスクにどのように格納されているかを意識する必要はない
効率が良い
- 用途に合わせて最適な構造でデータを記録できる
並列アクセス可能に
- トランザクション・ロック機構がある
- 並列にアクセスするアプリでも、利用するときは一つの接続のみを考えていれば良い
クラッシュ時復帰 (データ損失を防ぐ)
- 停電などによりサーバが死ぬとか起こりえる
- ファイルシステムにそのまま記録する場合、書込み中だと書き込もうとした内容が中途半端だったり、消失したりすることが起こりえる

いろんなDBMS知ってますか?

リレーショナルDBMS
- MySQL / PostgreSQL / SQLite
- http://www.postgresql.org/files/postgresql.mp3
カラム指向DBMS
- BigTable / Apache Cassandra / Apache HBase
ドキュメント指向DBMS
- MongoDB / Apache CouchDB / Elasticsearch
グラフDBMS
- Neo4j
キーバリューストア
- Memcached/Redis/Riak

関係データベース

もっとも広く使われているデータベースの一種
関係モデルに基づいたデータベースシステム
- MySQL/PostgreSQL/Oracle

関係モデル

関係モデルとは
- データを関係として表現し取り扱うモデル
関係とは?
- 属性を持った組 (タプル) の集合で表される

R: (ID, 名前, 誕生日) = {
  (1, 初音ミク, 2007-08-31),
  (2, 鏡音リン, 2007-12-27),
  (3, 鏡音レン, 2007-12-27),
  (4, 巡音ルカ, 2009-01-30)
}

関係には和、差、直積、射影、結合などの演算を数学的に定義できる
関係はわかりやすさのために「テーブル (表)」と呼ばれる

関係モデルに基づいたデータベース

= RDBMS
データベースは複数の「テーブル (表)」を持つ = 関係
データは「レコード (列)」で表される = 組
- レコードは「カラム (属性) 」を持つ
SQL と呼ばれる言語に基づいて、テーブルを定義したりテーブルに対して演算を行うことができる

関係データベースにおけるテーブル

表とレコードとカラム

artist テーブル:

id	name	birthday
1	初音ミク	2007-08-31
2	鏡音リン	2007-12-27
3	鏡音レン	2007-12-27
4	巡音ルカ	2009-01-30

album テーブル:

id	artist_id	name	released_on
1	1	みくのかんづめ	2008-12-03

SQL

関係データベースに問い合わせを行うための言語
SQLは標準化されており、ほとんどのRDBMSで使うことができる
- データの定義
- データの作成/読込/更新/削除

SQLで関係を定義する

artist テーブル:

id	name	birthday
1	初音ミク	2007-08-31
2	鏡音リン	2007-12-27
3	鏡音レン	2007-12-27
4	巡音ルカ	2009-01-30

CREATE TABLE artist (
  id INTEGER NOT NULL AUTO_INCREMENT,
  name VARCHAR(32),
  birthday DATE
);

album テーブル:

id	artist_id	name	released_on
1	1	みくのかんづめ	2008-12-03

CREATE TABLE album (
  id INTEGER NOT NULL AUTO_INCREMENT,
  artist_id INTEGER,
  name VARCHAR(128),
  released_on DATE
);

SQLでテーブルのCRUD

CRUD = Create & Read & Update & Delete
基本的なデータベース操作をできるようになろう

INSERT INTO artist (id, name, birthday) VALUES (5, '重音テト', '2008-04-01');
INSERT INTO artist SET id = 5, name = '重音テト', birthday = '2008-04-01';

SELECT birthday FROM artist WHERE name = '初音ミク';
SELECT * FROM artist WHERE birthday < '2009-01-01' ORDER BY birthday DESC;

UPDATE artist SET birthday = '2008-07-18' WHERE name LIKE '鏡音%';

DELETE FROM artist WHERE id = 4;

動詞 (SELECT, INSERT, UPDATE, DELETE)
対象: WHERE …

SELECT文

最も使う文だが、かなり複雑
使いこなせると便利!
See MySQL 5.5 Reference Manual :: 13.2.9 SELECT Syntax.

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

練習問題1: WHERE節

idが4のartistは？

id	name	birthday
1	初音ミク	2007-08-31
2	鏡音リン	2007-12-27
3	鏡音レン	2007-12-27
4	巡音ルカ	2009-01-30

WHERE 使えますか

SELECT * FROM artist WHERE id = 4;

id	name	birthday
4	巡音ルカ	2009-01-30

練習問題2: WHERE節 (2)

nameが「巡音ルカ」ではないartistのnameは？

id	name	birthday
1	初音ミク	2007-08-31
2	鏡音リン	2007-12-27
3	鏡音レン	2007-12-27
4	巡音ルカ	2009-01-30

not equal は !=

SELECT name FROM artist WHERE name != '巡音ルカ';
SELECT name FROM artist WHERE name <> '巡音ルカ'; -- 同じ

name
初音ミク
鏡音リン
鏡音レン

「*」ではなく必要なフィールドのみ指定するほうが転送量が減って良い

練習問題3: WHERE節 (3)

idが1か2か4であるartistは？

id	name	birthday
1	初音ミク	2007-08-31
2	鏡音リン	2007-12-27
3	鏡音レン	2007-12-27
4	巡音ルカ	2009-01-30

論理演算子OR

SELECT * FROM artist
WHERE id = 1 OR id = 2 OR id = 4;

id	name	birthday
1	初音ミク	2007-08-31
2	鏡音リン	2007-12-27
4	巡音ルカ	2009-01-30

WHERE id IN (...) と書くと複数のマッチングが同時にできる

SELECT * FROM artist
WHERE id IN (1, 2, 4);

練習問題4: ORDER BY句

最もbirthdayが若いartistは誰か？

id	name	birthday
1	初音ミク	2007-08-31
2	鏡音リン	2007-12-27
3	鏡音レン	2007-12-27
4	巡音ルカ	2009-01-30

ソートする必要がある = ORDER BY

SELECT * FROM artist ORDER BY birthday DESC LIMIT 1;

id	name	birthday
4	巡音ルカ	2009-01-30

DESCは降順。ASCは昇順 (デフォルト)。
- 昇順の場合は普通何も指定しません
1件しか必要ない場合はLIMITを使うこと

練習問題5: LIMIT句, OFFSET句

2番目にbirthdayが若いartistは誰か？

id	name	birthday
1	初音ミク	2007-08-31
2	鏡音リン	2007-12-27
3	鏡音レン	2007-12-27
4	巡音ルカ	2009-01-30

OFFSET Nを指定するとN行分読み飛ばす

SELECT * FROM artist ORDER BY birthday DESC LIMIT 1 OFFSET 1;

id	name	birthday
3	鏡音レン	2007-12-27

練習問題6: GROUP BY

sexごとにartist数を出せ

id	name	sex	birthday
1	初音ミク	Female	2007-08-31
2	鏡音リン	Female	2007-12-27
3	鏡音レン	Male	2007-12-27
4	巡音ルカ	Female	2009-01-30

カラムの値によって分離したいときはGROUP BYを使う
数を集計したい場合はCOUNT句を使う

SELECT sex, COUNT(*) AS number_of_artists
FROM artist
GROUP BY sex;

sex	number_of_artists
Male	1
Female	3

フィールドに AS ... を指定すると結果テーブルのカラム名が変わる

LEFT JOIN

「初音ミク」のalbum一覧が欲しい
albumテーブルにはartist_idしかない
LEFT JOIN

SELECT album.name
FROM album LEFT JOIN artist ON album.artist_id = artist.id
WHERE artist.name = '初音ミク';

albumテーブル				artistテーブル
id	artist_id	name	released_on	id	name	birthday
1	1	みくのかんづめ	2008-12-03	1	初音ミク	2007-08-31

他に RIGHT JOIN、INNER JOIN、OUTER JOIN などがあります

トランザクション処理

トランザクションは不可分な処理のまとまり
途中で失敗することが許されないデータアクセス群
ACID特性をもつ
- 原子性 atomicity
- 一貫性 consistency
- 独立性 isolation
- 耐久性 durability

例: 銀行の送金システム

元口座から1,000円引く
送金先の口座に1,000円足す

いずれかが失敗するとデータに不整合が生じる
同時に送金されたときに正しく動く?

〜〜〜ここまで基礎知識〜〜〜

応用編
- より良いスキーマ設計
- パフォーマンス
- インデックス

より良いスキーマ設計をするために

カラムのデータ型

カラムのデータ型、特に数値型は桁あふれに気をつけること
MySQL 5.5の場合
- INT: -2147483648 〜 2147483647
- 21億レコードは意外とすぐに到達します
- idはBIGINT UNSIGNEDにしておくのが安全
  - 18446744073709551615 (1844京)
ref MySQL 5.5 Reference Manual :: 11 Data Types

制約

レコードに必ず存在するカラムにはNOT NULL制約をつける
カラムがテーブル内で一意の場合はUNIQUE KEY制約をつける

-- より良い定義
CREATE TABLE artist (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(32) NOT NULL,
  `birthday` DATE NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (id),
  UNIQUE KEY (name)
);

CREATE TABLE album (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `artist_id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(128) NOT NULL,
  `released_on` DATE NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (id),
  UNIQUE KEY (artist_id, name)
);

PRIMARY KEY

テーブル内でレコードを一意に識別することができるカラム (任意)
- 他のレコードと被ってはいけない (UNIQUE制約)
- 値がなければいけない (NOT NULL制約)
テーブルに1つだけ設定できる
「インデックス」(後述) として使える
「id」という名前

テーブル間のリレーション

一対多のリレーション

このスキーマではalbumとartistは「一対多」
- 一つのalbumに一人のartistしか対応づけられない
- 一人のartistは複数のalbumを作れる

多対多のリレーション

オムニバス形式のalbumを登録するには？

album_artist_relationテーブル

CREATE TABLE album_artist_relation (
  `album_id` BIGINT UNSIGNED NOT NULL,
  `artist_id` BIGINT UNSIGNED NOT NULL,

  PRIMARY KEY (album_id, artist_id)
);

パフォーマンス

データベースはWebサービスにおいてボトルネックになりやすい
失敗するとサービスダウンにも
気をつけましょう

データベースがボトルネックになる理由

RDBMSはスケールがしずらい
- 複数のサーバ間で一貫性と可用性を保つためデータを分散させにくい
- ヒント: CAP定理
アプリケーションサーバはスケールしやすい
- マシンリソースが必要な処理はアプリケーションサーバでやるほうが良い

推測するな計測せよ

勘で対処してはいけない
- 無意味に複雑になるだけに終わる
問題が起こったときに計測し、ボトルネックを潰そう
EXPLAIN文を使う

EXPLAIN SELECT album.name
FROM album LEFT JOIN artist ON album.artist_id = artist.id
WHERE artist.name = '初音ミク';

パフォーマンス対策

クエリ数に気をつける
- ワンクエリで取れるところはワンクエリで
  - ループ内でクエリ投げるとかやりがち
不要なクエリは投げない
遅くなりがちなクエリに気をつける
- インデックス使ってない
- 無茶なJOIN
- 無茶なサブクエリ

インデックス

カラムの組み合わせについてインデックス (索引) を作成することができる
Bツリーがよく使われる
計算量
- インデックスがない: O(n)
- インデックスあり: O(log n)

-- インデックスをつけてみる
CREATE TABLE artist (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARBINARY(32) NOT NULL,
  `birthday` DATE NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (id),
  UNIQUE KEY (name),
  KEY (birthday)
);

CREATE TABLE album (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `artist_id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(128) NOT NULL,
  `released_on` DATE NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (id),
  UNIQUE KEY (artist_id, name),
  KEY (name),
  KEY (released_on)
);

インデックスのデメリット

インデックスを張ると、更新・削除時にオーバーヘッドがある
一般的なアプリケーションでは参照処理＞更新処理なのであまり問題にならない

語られなかったこと

サブクエリ
DISTINCT
UNION句
外部キー (Foreign Key) 制約
TRIGGER
DBMSのユーザ管理と権限

mysqlコマンドの使い方

インタラクティブシェルを使う

データベースに対して直接SQLを実行したい場合は以下のようmysqlコマンドのインタラクティブシェルを使うと便利です。

$ mysql -unobody -pnobody intern_diary_$USER # mysqlのインタラクティブシェルに入る
mysql> show tables; # 定義されているテーブル一覧をみる
mysql> describe users; # usersテーブルの定義を調べる
mysql> show create table users; # usersテーブルを定義しているSQLを表示する
mysql> SELECT * FROM users LIMIT 10; # SQLを実行する(SELECT)
mysql> INSERT INTO users (id, name) VALUES (0, "tarou"); # SQLを実行する(INSERT)
mysql> CREATE TABLE user ( # 複数行のSQLをペーストしてまとめて実行することもできます
    ->   id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   name VARCHAR(32) NOT NULL
    -> );

ファイルに書かれたSQLを読み込んで実行する

db/schema.sqlに書かれたSQLを一度に読み込みたいときに利用すると便利です。

$ cat db/schema.sql | mysql -unobody -pnobody intern_diary_$USER

参考リンク

MySQL 5.5 Reference Manual
MySQL 5.5 Reference Manual :: 13 SQL Statement Syntax

この作品はクリエイティブ・コモンズ表示 - 非営利 - 継承 2.1 日本ライセンスの下に提供されています。

Files

database-programming.md

Latest commit

History

database-programming.md

File metadata and controls

データベースの基礎

アンケート

今日の講義

ご注意

データベースとは

簡単なデータベースの例

2ちゃんねる+αを考えてみる

ウェブサービスのデータは増え続けます

そのための データベース管理 です

いろんなDBMS知ってますか?

関係データベース

関係モデル

関係モデルに基づいたデータベース

関係データベースにおけるテーブル

artist テーブル:

album テーブル:

SQL

SQLで関係を定義する

artist テーブル:

album テーブル:

SQLでテーブルのCRUD

SELECT文

練習問題1: WHERE節

練習問題2: WHERE節 (2)

練習問題3: WHERE節 (3)

練習問題4: ORDER BY句

練習問題5: LIMIT句, OFFSET句

練習問題6: GROUP BY

LEFT JOIN

トランザクション処理

例: 銀行の送金システム

〜〜〜 ここまで基礎知識 〜〜〜

より良いスキーマ設計をするために

カラムのデータ型

制約

PRIMARY KEY

テーブル間のリレーション

一対多のリレーション

多対多のリレーション

album_artist_relationテーブル

パフォーマンス

データベースがボトルネックになる理由

推測するな計測せよ

パフォーマンス対策

インデックス

インデックスのデメリット

語られなかったこと

mysqlコマンドの使い方

インタラクティブシェルを使う

ファイルに書かれたSQLを読み込んで実行する

参考リンク

そのためのデータベース管理です

〜〜〜ここまで基礎知識〜〜〜