【技術書メモ】現場で使えるMySQL 〜毎週アウトプットチャレンジ⑧〜

毎週 １冊技術書を読んでブログでアウトプットするチャレンジの第８弾ですー！

今回は、現場で使えるMySQL を読んでまとめました。

MySQLを現場で使うための知識を、分かりやすく解説している技術書です。

現場で使える MySQL (DB Magazine SELECTION)

Chapter1 MySQLの概要と環境構築

• データベース領域：インスタンスと1対1に対応した、データベースの物理領域。1つのデータベース領域の中に複数のデータベースが格納される
• 権限データベース：mysqlという名前のデータベース。インスタンスへ接続するためのユーザー情報や、各データベース、テーブルにアクセスするための権限情報が複数のテーブル内に格納されている
• 初期パラメータファイル：インスタンスの使用メモリやサイズやファイル配置などを決めるためのファイル
• MySQLユーザー：MySQLインスタンスに接続するためのDBアカウント

 

Chapter2 データ型とSQL

• データや値の範囲チェックはアプリケーション側の責任であるという考え方に基づいて実装されている。例えば、どのデータ型でも、範囲を超えた値を格納しようとしてもエラーにはならず、削られたり最大値や0に変換されたりするので注意が必要
• 文字列型で最大長を超える文字を格納しようとした場合、オーバーした部分は削られた上で正常終了する
• ソートや文字列比較において、デフォルトでは半角英字の大文字／小文字を区別しない
• 数値型のNUMERIC型は、NUMERIC(p,n)という形で定義され、pは全体の有効桁数、nは小数点以下の有効桁数を意味する。p,nどちらも指定しなかった場合、NUMERIC(10,0)となる
• 日付／時刻型としては、YEAR/DATE/TIME/DATETIME/TIMESTAMPの5種類がある
• 列挙型とは、あらかじめ復すの文字列値を定義しておいて、その中からしか選択できないようにするためのデータ型で、ENUMとSETの2種類がある。ENUM型は定義値の中から1つのみ選択できて、SET型は複数選択できる

 

Chapter3 テーブル定義

• 主なストレージエンジンとその特徴
• • MyISAM：非常に高速。ただしトランザクションに対応していないため、ロールバックできないことやインスタンス障害時にデータが消失するおそれがある
  • InnoDB：トランザクション機能、インスタンス障害時の自動リカバリなどをサポートしている。通常はMyISAMより低速で、より多くのディスク領域を必要とする
• 整合性制約
• • 主キー制約：制約対象列にNULL値を入れないことと、値が重複しないことを保証する。これにより、制約対象列の値からテーブル内の行を一意に識別できる
  • 一意キー制約：制約対象列の値が重複しないことを保証する。NULL値は格納でき、NULL値同士は異なる値であると見なされる
  • 参照整合性制約（外部キー制約）：子テーブルの制約対象列の値が、親テーブルの参照キーに含まれていることを保証するもので、子テーブルに定義する。利用するためには、親テーブル、子テーブルともInnoDBである必要がある
• シーケンス：連続的な整数値を生成するオブジェクト。シーケンスから得られる数値のことをシーケンス番号、連番などと呼ぶ。一般的に、同一のシーケンスから得られるシーケンス番号は重複しないため、テーブルの主キーや一意キーとして使用されることが多い

 

Chapter4 トランザクション

• 自動コミット機能：この機能を有効にするか無効にするかによって、トランザクションの開始／終了の方法が変化する
• • 有効にした場合：トランザクションは単一のSQL文を実行した時点で自動的に、または、START TRANSACTION文によって明示的に開始する。START TRANSACTION文を実行していない場合は、単一のSQL文が実行された時点で自動的にコミットし、ロールバックできない。一方、START TRANSACTION文を実行している場合は、単一のSQL文が終了しただけではコミットせず、COMMIT文を実行した時点ではじめてコミットする。また、ROLLBACK文を実行するとロールバックする
  • 無効にした場合：トランザクションは、SQL文の実行によって暗黙的に、またはSTART TRANSACTION文で明示的に開始する。いずれの場合も自動的にコミットしない。COMMIT文を実行した時点でコミットし、ROLLBACK文を実行した時点でロールバックする
• デフォルトでは自動コミット機能が有効となっている
• ロックとは、複数のトランザクション間での排他制御を実現するための仕組み。InnoDBでは、テーブルの行（レコード）単位でロックをかけられる。
• フルスキャンはすべての行にロックがかかってしまうため、インデックス検索させるべきところにはきちんとインデックスを作成すべき
• デッドロックは即座に検出される

 

Chapter6 日本語処理

• クライアントとサーバーの文字コードをすべてutf8に統一できる環境ならば問題はないが、そうでなければ様々な注意事項が存在する

 

Chapter8 アーキテクチャ／ファイル構成

• インスタンス：MySQLサーバー側で動作するプロセス／スレッド群と、それらが使用するメモリの総称
• MySQLは、複数のクライアント側プロセスと1つのサーバー側プロセス「mysqld」で構成される。多くのRDBMSがマルチプロセスで動作するのとは対照的に、MySQLのサーバー側はマルチスレッドで動作する
• プロセスとは、UNIXコマンドで言えばcatやlsといった個々のプログラムの実行単位である。OS上の各プロセスには、それぞれの固有のメモリ空間が割り当てられる。一方スレッドとは、簡単に言えば1つのプロセス内で並行処理を行うためのものである。スレッドはプロセスの内部で生成され、プロセスのメモリ空間を共有する
• ストレージエンジンはアプリケーション側から隠蔽されている。アプリケーション側から発行するSQL文は、インスタンス側で動作するパーサーと呼ばれるプログラムによって解析され、どのインデックスを使用するか、といった実行計画が作成された上で、はじめて対象のストレージエンジンに処理が渡る。アプリケーションから見ると、ストレージエンジンが変わったとしても、発行するSQLが変わるわけではない

 

Chapter9 ユーザ管理／セキュリティ

• MySQLにアクセスするにはまず認証を行う。認証とは、アクセス元が本人に間違いないことを確認することで、ユーザ名・アクセス元のホスト名／IPアドレス・パスワードの3つで認証する
• OSユーザーとは別にMySQLユーザーという概念があり、MySQLインスタンスにアクセスする際に使用する。管理者ユーザーという特別なユーザーもあり、デフォルトではrootという名前になっている。MySQLユーザーは認証や権限管理のために存在する
• 権限は、グローバル（全体）、データベース、テーブル、列の4種類ごとに設定する
• 発行されたすべてのSQL文をファイルに記録する機能があるため、監査機能として使うことができる

 

Chapter10 バックアップ／リカバリ（前編）

• バックアップ方法
• • コールドバックアップ：インスタンスを停止した状態で全体バックアップを取得する方法。したがって、バックアップの最中はインスタンスに対して一切アクセスできない。tarやcpなどのOSコマンドで取得する。
  • オンラインバックアップ：インスタンスを起動した状態でバックアップを取得する方法。
• バックアップ／リカバリの基本的な流れは、バックアップ→メディア障害発生→リストア→リカバリである。
• • メディア障害とは、ディスクの故障やオペレーションミスなどにより、MySQLの実データが失われる障害のこと
  • リストアとはバックアップの時点まで復旧すること
  • リカバリは障害直前の時点まで復旧すること

 

Chapter11 バックアップ／リカバリ（後編）

• MySQLのオンラインバックアップでの定番は「mysqldump」。データベース領域全体でも、テーブルやデータベース単位でもバックアップでき、バックアップデータはテキスト形式で、SQL文がそのまま記述される。MySQLにインスタンスに接続→バックアップ対象のすべてのテーブルに対しSELECT文を実行し、テキスト形式で出力→すべてのバックアップが終了したら接続を終了、という流れで動作する

 

Chapter12 レプリケーション

• レプリケーションとは、テーブルなどの各種オブジェクトの複製を他のサーバー上に生成するという技術。レプリケーションによって、負荷分散と可用性の向上を実現できる
• 1台のマスターに対して更新を行うと、複数のスレーブにも同じ更新結果が反映される。検索は基本的にスレーブに対して行えばよいので、検索の負荷分散になる
• レプリケーションは、スレーブにバックアップ機としての役割を持たせることによって、バックアップ／リカバリの手段としても利用できる
• マスターに障害が発生した場合は、スレーブをマスターに昇格させてサービスを継続することで、サービス停止時間を短くすることができる

 

Chapter13 パフォーマンスチューニング（前編）

• RDBMSのチューニングは単体チューニングとシステムチューニングの2つに大別できる
• 単体チューニングとは、個々のSQL文の実行性能を改善する作業のことで、具体的にはインデックスの作成やSQL文の修正、アプリケーションロジックの見直しによるSQL発行回数を減らすことが含まれる
• システムチューニングとは、システム全体の観点から、MySQLの初期化パラメータやOSのカーネルパラメータの設定を行うことが含まれる
• 単体チューニングでは下記2つの手段を組み合わせる
• • スロークエリログから、実行時間の長いSQLや非効率なフルスキャンを行なっているSQL文の特定
  • 上記SQL文の実行計画をEXPLAIN文で確認し、必要に応じた改善

 

Chapter14 パフォーマンスチューニング（後編）

• 基本的にデータ型は使用バイト数が少ないデータ型を選ぶことを心がけると良い。そうでないと、ファイルサイズが大きくなるだけでなく、多くのI/Oが発生するため性能面でも悪影響がある
• SELECT／UPDATE／DELETE文のWHERE句で指定する列に対しては、インデックスを定義することでフルスキャンを防ぐのが一般的である。ただし、インデックスを作ると更新時にオーバーヘッドがかかることに注意する
• バルクINSERT文とは、1つのSQL文で複数のレコードをまとめてINSERTする構文であり、通常のINSERT文を繰り返すのに比べ、大きな性能向上が図れる
• テーブルのレコードデータをすべて削除する場合は、「DELETE FROM テーブル名;」よりも、「TRUNCATE TABLE テーブル名;」の方がはるかに高速

 

おわりに

ここまで読んでいただきありがとうございます。

MySQL、とても奥が深いですね、、、！

現場でMySQLを使っている方には特に本書オススメですー！

現場で使える MySQL (DB Magazine SELECTION)

次もMySQLに関する技術書を読む予定です。

来週も頑張ります！