Skip to content

Instantly share code, notes, and snippets.

@tokubass
Created March 22, 2022 02:57
Show Gist options
  • Save tokubass/6141dd331ee7894732a7c76204163cc3 to your computer and use it in GitHub Desktop.
Save tokubass/6141dd331ee7894732a7c76204163cc3 to your computer and use it in GitHub Desktop.

今日の内容

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

データベースの概念

データベースとは

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

エクセルとの違い

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

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

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

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

RDBMS

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

MySQLの仕組み

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

MySQl 5.6新機能解説 の6P

MySQLの基本的な操作

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

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

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

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

INDEXの作成

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

blog_idarticle_countpvregister_datetime
1101002016-04-01 00:00:00
22002016-04-03 00:00:00
32010002016-04-03 00:00:00
43202016-04-02 00:00:00
51302016-04-02 00:00:00
610202016-04-02 00:00:00

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

blog_idarticle_countpvregister_datetime
51302016-04-02 00:00:00
43202016-04-02 00:00:00
610202016-04-02 00:00:00
1101002016-04-01 00:00:00
22002016-04-03 00:00:00
32010002016-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の実行計画を表示してくれる。なかなか覚えられないと思うので、都度以下を参照。

テーブル設計(正規化)

第1正規化

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

before

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

after

id製品名価格
1aa100円
2bb100円
3cc200円

なぜ必要か

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

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

第2正規化

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

before

会社id社員id社員名会社名
10001Aさんmixi
20099Bさんyahoo!
30010CさんGree

after

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

会社id社員id社員名
10001Aさん
20099Bさん
30010Cさん
会社id会社名
1mixi
2yahoo!
3Gree

なぜ必要か

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

第3正規化

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

before

id社員名会社名
1aamixi
2bbyahoo!
3ccGree
id求人数会社名
110mixi
220yahoo!
330Gree
id会社名
1mixi
2yahoo!
3Gree

after

id社員名会社id
1aa1
2bb2
3cc3
id求人数会社id
1101
2202
3303
id会社名
1mixi
2yahoo!
3Gree

レプリケーション

MySQl 5.6新機能解説 の24P

binlog

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

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

DBのバックアップと復旧

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

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

バックアップをとる

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

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

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

シャーディング(水平分割)

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

デメリット

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

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

メモ

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

insert into like_test () values ();

で空レコードを1行作成

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
@tokubass
Copy link
Author

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment