rdbms.md

今日の内容

• データベースの概念
• MySQLの仕組み
• MySQLの基本的な操作
• INDEXで高速にレコードを検索する
• InnoDBのINDEX
• explain
• テーブル設計（正規化）
• replication

データベースの概念

データベースとは

データの保存や検索などの操作がしやすいように整理、管理されたデータの集合

エクセルとの違い

• 大容量データをメモリに展開せずに検索可能
• 障害時の復旧
• 排他制御
• 整合性の保証

リレーショナルデータベース

１つの情報を保存する上で、属性にわけて格納する方式。（=表） 例えば会員Aさんのプロフィール情報を保存する際には、名前、年齢、性別などの属性にわけて保存する。 リレーショナル≒表だと思えばいい。

参考: なぜ関係モデルという名前なの？

RDBMS

リレーショナル データベース マネージドシステムの略。 リレーショナルデータベースを扱うソフトウェアのこと。

MySQLの仕組み

• InnoDBやMroongaがどのレイヤーの話なのか理解する

MySQl 5.6新機能解説 の6P

MySQLの基本的な操作

下記にテスト用のsqlファイルと演習問題があるので使用する

はてなインターン2015年度版データベースの基礎

INDEXで高速にレコードを検索する

目的のレコードをはやくみつけるために、索引（index）を作成することができる。

INDEXの作成

以下のテーブルがあったとして、、

blog_id	article_count	pv	register_datetime
1	10	100	2016-04-01 00:00:00
2	20	0	2016-04-03 00:00:00
3	20	1000	2016-04-03 00:00:00
4	3	20	2016-04-02 00:00:00
5	1	30	2016-04-02 00:00:00
6	10	20	2016-04-02 00:00:00

記事数とpvでマルチカラムindexを作成した場合、下記のようなテーブルが作成されると思うとわかりやすい。（後述するが、実際のindexは多分木になっている。)

blog_id	article_count	pv	register_datetime
5	1	30	2016-04-02 00:00:00
4	3	20	2016-04-02 00:00:00
6	10	20	2016-04-02 00:00:00
1	10	100	2016-04-01 00:00:00
2	20	0	2016-04-03 00:00:00
3	20	1000	2016-04-03 00:00:00

INDEXが効くクエリ

SELECT * FROM blog WHERE article_count > 10;
SELECT * FROM blog WHERE article_count = 10 AND pv > 100;
SELECT * FROM blog WHERE pv > 100　AND article_count = 10;

INDEXが効かない or 役に立ちにくいクエリ

前段のindexカラムを飛ばす

SELECT * FROM blog WHERE pv = 100;

同値のarticle_countのもとでしかpvはソートされていない。

前段のindexカラムで範囲検索

article_count > 10の結果が大量にある場合はpv = 100で時間がかかるので注意が必要。

SELECT * FROM blog WHERE article_count > 10 AND pv = 100;

前方一致以外のLIKE検索

MySQLのTEXT型などで保存されている文字列は前方からindexを作成しているので、

WHERE sample_text_column LIKE 'hoge�%'

はindexが効くが、

WHERE sample_text_column LIKE '%hoge'
WHERE sample_text_column LIKE '%hoge%'

は効かない。 サービスが小規模のうちにLIKE '%hoge%'で全文検索を実装して、後々障害になるのはお約束。

デメリット

• 索引を作り直す必要があるので更新系が遅くなる。

InnoDBのINDEX

セカンダリインデックスとクラスタインデックス

http://nippondanji.blogspot.jp/2010/10/innodb.html

InnoDBバッファプールとは

http://www.slideshare.net/yoku0825/inno-db-28755870

explain

MySQLの実行計画を表示してくれる。なかなか覚えられないと思うので、都度以下を参照。

• http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html

テーブル設計（正規化）

第１正規化

一つのカラムに複数の値が入っていると、そのカラムを利用したリレーショナルDBの機能が使えなくなるので、複数のレコードに分割する。

before

id	製品名	価格
1	aa,bb <-２つ入ってる	100円
2	cc	200円

after

id	製品名	価格
1	aa	100円
2	bb	100円
3	cc	200円

なぜ必要か

• 100円の製品の数が知りたくて100円で検索しても数としては1件しか見つからない。DBで完結しないので、アプリケーション側で製品名aa,bbという文字列をsplitして製品数をカウントする必要があり、コードが冗長になる。

• 製品名が第1正規化されている他のテーブルが存在する場合、そのテーブルとjoinできない。

第２正規化

複合主キーのテーブルにおいて、片方の主キーだけで一意に特定できるカラムがあるとき、 そのカラムは別テーブルに分離する。

before

会社id	社員id	社員名	会社名
1	0001	Aさん	mixi
2	0099	Bさん	yahoo!
3	0010	Cさん	Gree

after

会社名をしるのに社員idは必要ない

会社id	社員id	社員名
1	0001	Aさん
2	0099	Bさん
3	0010	Cさん

会社id	会社名
1	mixi
2	yahoo!
3	Gree

なぜ必要か

• まだ社員がおらず、事前に会社だけを登録したいときに困る。

第３正規化

同じ種類のカラムを複数のテーブルで保持しない。 アプリケーションでいうところのDRYにしましょうという話。

before

id	社員名	会社名
1	aa	mixi
2	bb	yahoo!
3	cc	Gree

id	求人数	会社名
1	10	mixi
2	20	yahoo!
3	30	Gree

id	会社名
1	mixi
2	yahoo!
3	Gree

after

id	社員名	会社id
1	aa	1
2	bb	2
3	cc	3

id	求人数	会社id
1	10	1
2	20	2
3	30	3

id	会社名
1	mixi
2	yahoo!
3	Gree

レプリケーション

MySQl 5.6新機能解説 の24P

binlog

MySQL 5.6 リファレンスマニュアル

• binlog形式が複数ある
• サーバーの容量を喰う
  • 定期的に消して、直近数日分のbinlogを残す
  • しかしspamやオペレーションで急激にbinlogが増えてアラートが来る

DBのバックアップと復旧

レプリケーションとバックアップは違う

drop tableコマンドを発行したら、レプリケーションされてマスターもスレーブからもテーブルが消える。これはバックアップではない。

バックアップをとる

• mysqldump + binlog が王道
• バックアップ用のスレーブを作る（バックアップ時しかレプリケーションしない）

Q. １日の更新分のレプリケーションorバックアップの完了が１日で終わらない、どうしたらいいですか

さぁ、、、更新系クエリが多いゲーム以外ではあまり経験しない。xflagの人に聞こう。

シャーディング（水平分割）

• 複数のテーブルを作って、レコードを分散させる
• レコードが増えてくると
  • メモリに乗らない
  • 復旧に時間がかかる
• テーブルを分けるとかいたが、大抵DBも分ける
• mixiではL2分散と呼んでいる

デメリット

• DBを超えてjoin,トランザクションが使えない
  • アプリケーションコードが複雑になる

他

• indexサイズとメモリ
• N+1問題
• offset値が大きい
• lockとトランザクション(mixiではほとんど使ってない)
• ASID特性

メモ

ランダムなデータを大量に作成

insert into like_test () values ();

で空レコードを１行作成

insert into like_test (path) select 0 from like_test;

でどんどん倍にしていく。

update like_test set path = SUBSTRING(MD5(RAND()), 1, 10);

のようにupdateなら全レコードに対して一つずつRNAD()が発行されるのでランダム値になる。

毎秒processlit表示

mysqladmin -i 1 processlist

mysqlコンソール上の便利コマンド

実行結果をファイルに保存

tee output_file.txt
notee

ファイル内容実行

source sql_file.txt

EUCのテーブルをUTF-8で見たい場合

mysql> pager nkf -w
or
mysql> pager iconv -f euc-jp -t utf-8

長いクエリ出力を見る場合はpager less

mysql> pager | less
mysql> show full processlist \G
 としていたが、
mysql> pager less -n -i -F -S
長いクエリ出力を含む場合は-Sオプションの方が非常に見やすい

nopagerで終了

1秒以上かかっているクエリを見たい場合

    mysql> SELECT * FROM information_schema.PROCESSLIST WHERE TIME >= 1 and USER != 'repl';

    /usr/local/mysql/bin/mysql -hホスト名 --pager='grep -v Sleep | less -n -i -S -F -X ' DB名

buildオプションの確認

strings /usr/bin/mysqlbug | grep configure

どのmy.cnfが読まれてるか調べる

mysql --help でorderが記載されているが、鵜呑みにはできない。

    $ strace -ff -e open   /usr/local/mysql/bin/mysqld_multi  start 55,56 2>&1 | grep my\.cnf
    [pid 23345] open("/etc/my.cnf", O_RDONLY|O_LARGEFILE) = 3
    [pid 23347] open("/etc/my.cnf", O_RDONLY|O_LARGEFILE) = 3
    [pid 23344] open("/etc/my.cnf", O_RDONLY|O_LARGEFILE) = 3

LT用に過去の資料をひっぱりだした。