Webエンジニアのためのデータベース技術[実践]入門を読んだ
データベース技術[実践]入門
Webエンジニアのための データベース技術[実践]入門 (Software Design plus)
- 作者: 松信嘉範
- 出版社/メーカー: 技術評論社
- 発売日: 2012/03/09
- メディア: 単行本(ソフトカバー)
- 購入: 20人 クリック: 486回
- この商品を含むブログを見る
データベースに関して基礎からトレンドまで懇切丁寧に説明しています。 設定内容だけでなくリレーショナルデータベースの設計をする上で必要な知識がしっかりと身につくオススメ本です。
データベースがないと何が困るのか
インデックスで高速アクセスを実現する
B+Treeインデックス
- ルートブロック、ブランチブロック、リーフブロック
- 二分木と多分木がある
- B-Treeではブランチが値を持つこともある
- B+Treeなら範囲が広いときにもブランチをたどらなくていい
RDBMSではどのように一意性を実現しているのか
- ハッシュインデックスならハッシュ値が一緒になるし、B+Treeなら同じリーフにたどりつく
- つまり重複のチェックが低コスト
- マルチカラムインデックスを使えば、2要素での検索に便利(あらかじめユーザIDと最終ログインのインデックスをつくっておく)
- index only index / covering index インデックスを読んで件数などの処理を完結させる
- 2要素検索ではインデックスをそれぞれ取って何行目が該当したかの結果をAND / ORで処理する
- なるべくディスクにまとめて書きたい。ランダムリードは遅いし。
- MySQL(InnoDB)では別専用ファイルに一時書き込みしてDB領域にまとめて書くことで実現
- 複数クライアントからの処理でリーフ分割が大量に発生した場合、パーティション表などの仕組みが解決する
- 内部的には分割されていてインデックスも別。でもユーザからは表が一つに見える。
- 参照;http://liginc.co.jp/programmer/archives/3832
テーブル設計とリレーション
- 主キー
- テーブルを分割する(社員番号をキーとした名簿本体と部門をキーとした電話番号表)
- テーブルを跨いで入力キーの誤りを確認
- キーは10で割った余りが一桁目の数字に一致するなどの法則をもたせて誤りを検知する
- 部門に複数所属する際にどうするか。サブを作る?
- 何個まで作ればいいのか、NULLばっかりが格納されるためにそんなに列を作るの?
- 一列に複数入れる場合は整数ではなくなるし
- 正規形の話
SQL分の特徴とその使いこなし方
- MySQLでテーブルを操作してみよう
- CREATE TABLE, INTEGER, VARCHAR, PRIMARY KEY
- データベース製品間の互換性は難しい。データ型の名前やサポートしているものの違い
- 標準化にこだわるよりパフォーマンスを重視すべし。無理にしてCHAR型にしてバイトを増やすとかどうなの
- 列の定義変更やインデックスの追加削除などは後から困難なので最初にしっかり設計すること
- SELECTは負荷をかけない効率的な条件をうまく使うこと。全文検索などデータベースが重くなる原因になる。
- WHEREがないUPDATEは全スキャンが走るので注意
- TRUNCATEを使えばレコードを0にできる。定義情報は残す
- DROP TABLEで定義情報を消す
- 3個のSELECT分を打つよりもAND条件を使って1文で書いた方がよい。いわゆるジョインDB側で効率のよいアクセスをしてくれる
- シャーディング(テーブルを複数のサーバに分割)しているとサーバ跨いだジョインができないのでアプリケーション側と設計をすりあわせる必要あり
- データリンク機能とか持っているDBもある
- EXPLAINを使うとクエリを流す前に効率性を検証できる
- とくにrowsに注目し、各テーブルで1が出ていればインデックスを用いた効率の良いアクセスとなる
- ビジネスロジックはストアプロシージャーで実装する。が、最近はやらない。昔はクエリをまとめたりで活躍。純粋にメールを送出するなのどのロジックはもはやアプリケーションでいいでしょという発想
トランザクション、整合性、耐障害性
- 一つのSQL分で一つのユーザ処理が終わることはほんとんどない
- 商品を買うだけでもitem / user_item / user / purchase_histrory
- コミットとロールバック
- 一つのSQL文の場合でも同様にコミットをしておくと、長い処理の途中で止まった時にロールバックができる
- REDOログ。クラッシュリカバリ。MyISAMはない(?)
- REDOはシーケンシャルライトだから負担が少ない
- NoSQLはトランザクションの概念をほぼ持っていない。アプリケーションロジックで対応しないといけない。
- ロックして排他制御
- InnoDBはレコード単位でロック。MyISAMはテーブル単位
- スレーブはレプリケーションをどこまで実行したかslave_relay_log_infoに記述する
レプリケーション
- 片方向/非同期の話
- バイナリログの受信(I/Oスレッド)と実行(SQLスレッド)
- どちらの遅延なのかで障碍時のインパクトは違う
- 片方向/準同期の話
- 受信を同期にする
- 片方向で言えることはスレーブが単一スレッドになる
- 双方向レプリケーション
- クラスタ、バックアップとポイントインタイムリカバリの話
ストレージ技術の変革とデータベースへの影響
- スペックがあがると同時に1台の処理要求があがる。1coreでの処理負担も増える
- バッチとかレプリケーションの負担があがる
- SATA SSD -> PCI Express SSD
- クリティカルセクション(1スレッドアクセスになるよう排他制御する処理区間)の改善
- MySQL5.1以降でI/Oの並列性が大幅に向上
- MySQLO5.5以降でパージ処理(物理的に削除する処理)とチェックポイントなどのバックグラウンド処理を並列化して処理できるように
- レプリケーションの処理はこれから
- Tx/Rx Multi QueueがRed Hat EnterpriseLinux6以降で実装
データベース運用の勘所
- CPU使用率
- ディスクI/O(%iowait)
- システム空間での使用率(%system)
- ユーザ空間での使用率(%user)
ストール
- 1秒間隔のSQLコマンド実行数の監視が必要
- その1秒のために増強するのはスペック的に厳しい
- gdbなどのデバッガを用いてプロセスのスタックトレースをとる
- 例として新規接続のスレッド生成のためのcloneに時間がかかってるなら、接続を貼りっぱなしにすればいい
MySQL 導入のポイント
ストレージエンジン
- クライアントからの接続を受け付けて専用のスレッドを割り当て
- キャッシュにヒットすればクエリキャッシュから返す
- キャッシュにヒットしない場合は構文解析(パース)処理をする
- SQL文の実行計画、インデックスの利用可否、ジョインの際にどのテーブルを使うか
- ストレージエンジンはテーブル単位で選択できる
- ACID特性
- Atomicity(全部コミットされるかロールバックされるか)
- Consistency(複数テーブルにまたがっている場合でもエラーで片側が更新されることはない)
- Isolation(参照・更新が競合した場合でも矛盾した状態にならないように排他制御が可能)
- Durability(コミットした結果は確実に保存され電源断が起きても復旧が可能)
- テーブル.frmにメタ情報が格納される。5.1からは日本語名も。
- エラーログ、スロークエリログ、バイナリログ
- log-bin, slow-query-log, long-query-log
- max_connection
- innodb_buffer_file_size
- innodb_flush_method, O_DIRECT
- innodb_data_file_path, auto extend
バックアップとリカバリ
- コールドバックアップ、オンランバックアップ
- データベースファイル、バイナリファイル、設定ファイル
- 論理バックアップと物理バックアップ
- 論理バックアップはSQL文形式で人間が読める状態でバックアップされる。mysqldumpを使用
- MyISAMをInnoDBに変換することも可能
- 論理バックアップは時間がかかるがデータサイズも少なく圧縮効率が非常によい。
- 物理バックアップはディスク側の機能を使うのがよい
- リカバリはバックアップ+バイナリログ
コールドバックアップ
$ mysqladmin shutdown -uroot $ cp -rp /var/lib/mysql /backup/ $ mysafe_safe&
バイナリログによるリカバリ
$ mysqlbinlog hostname.000011 > recover.sql $ mysql -uroot < recover.sql
- ステートメントベースと行ベースがある
- ステートメントベースだとSQL文そのままなのでランダムな数値が入るようなUUID()などの関数が入っている場合に値がおかしくなる
- 5.1以降は行ベースが可能になっている
- mysqldump -uroot —lock-all-tables —master-data=2 > dump.sql
- dump.sqlの中にバイナリファイルのどこまで書き込まれているか記載するようにオプションで指定
- InnoDBの場合、全体のスナップショットを取る時だけロックしてバックアップする方法がとれる(--single-transaction)
- FLUSH TABLE WITH READ LOCKが走るので重いクエリで待ち時間が発生しないように注意する
- 不要なバイナリログはPUARGE MASTER LOGS TO ‘hostname.000042'
- 物理バックアップの場合はテーブルロックしてsyncコマンドでディスクに書き出してロック解除する
データベース技術の現在と未来
- レンジパーティションイング
- テーブルやインデックスを物理的に1個でまとめて管理する
- 特定のパーティションに入れて連続的にメモリに乗せる
- twitterのようなサービスでは1週間や1ヶ月分単位でパーティンションを切る
- TokuDBではFractal Treeというインデックスを持っていてレンジパーティションイング不要で性能を維持できる
その他
- DELETE後はOPTIMIZE TABLEで再編成
- できなかったとしてINSERTの時の再利用を考えてDELTEは推奨
- DELETE時のスレーブ遅延を防ぐには、スレーブ側に対象をSELECTしておくとよい
- MySQL5.5以降で「change buffering」が実装。DELETE時のインデックス論理削除はバックグラウンドで。
- 秒間のupdate回数はCom_update, 内部レコードの更新回数はInnodb_rows_updated
- スレーブがどれくらい書き込めるかは、一度レプリを止めて再開して秒間の更新を見る
- Thread_runningで並列度が取れる。SHOW GLOBAL_STATUSクエリを実行している分もカウントされている
- マスタの場合、BINLOG_DUMPもカウントされている
- Event Schedulerを使用している場合はカウントされている
- vmstat/sarではMySQLが待ち状態のストールになっていても加算されない
- スロークエリログ、SHOW FULL PROCESSLIST
- tcpdumpやすべてのクエリをスローログに出してmk-query-degitで解析する
- SET GLOBAL long_query_time=0
- 1+N型はINを使って1+1型へ
- クエリは軽いがトランザクションのロック時間が長いものをどう見つけるか
- SHOW ENGINE INNODB STATUSを見れば長く待たされているトランザクションが、長く実行されているトランザクションがわかる
- MySlowTranCaptureを使えば楽にトランザクションと該当クエリの特定が可能
- innnodb_lock_wait_timeoutはデフォルト50秒。長い。
- コネクションプーリングで外部とのTCP/IP接続は済ませておくこと
- サーバをまたいだデッドロックには注意。innodbは同一サーバ内の自動検出
- ユーザA->ユーザBの順に昇順にロックを統一するなど
シェアして頂けると嬉しいです。
参考になったという方がいれば是非お願いしますm(_ _ )m
モチベーション維持の観点で非常に励みになります。
Webエンジニアのための データベース技術[実践]入門 (Software Design plus)
- 作者: 松信嘉範
- 出版社/メーカー: 技術評論社
- 発売日: 2012/03/09
- メディア: 単行本(ソフトカバー)
- 購入: 20人 クリック: 486回
- この商品を含むブログを見る
理論から学ぶデータベース実践入門 ~リレーショナルモデルによる効率的なSQL (WEB+DB PRESS plus)
- 作者: 奥野幹也
- 出版社/メーカー: 技術評論社
- 発売日: 2015/03/10
- メディア: 単行本(ソフトカバー)
- この商品を含むブログ (16件) を見る