tokubass/rdbms.md

## rdbms.md

      
    Raw
  

              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
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