共通機能
ストレージエンジンに依存せず共通で提供されている機能
- 接続
- 認証（権限）
- オプティマイザ
- ログ
- バイナリログ
- スロークエリログ
- 一般クエリログ
- エラーログ
- レプリケーション
接続
接続はスレッド単位で管理される
新規接続時はスレッドが作成されるが、一度作成されたスレッドをキャッシュしておくことで、
スレッド生成によるパフォーマンス劣化を防ぐことができる。
最大接続数とスレッドキャッシュ
以下パラメータで最大接続数、スレッドキャッシュの数を調整できる
max_connections： 最大接続数の調整
thread_cache_size: スレッドのキャッシュ数の調整
認証（権限）
特定のホストから接続するユーザーを認証
ユーザーをデータベースにおける権限に関連付ける
ユーザーID は、接続元のホストおよび指定したユーザー名によって決定される
user＠hostで1ユーザーID
権限の種類
管理権限:
特定のDBに固有でないグローバルな権限DB権限:
DB,DBオブジェクトに対して設定DBオブジェクト権限:
table,index,viewなどに対し設定
権限の付与
GRANT文により権限の付与を行う
GRANT USAGE ON [db_name].[table_name] TO `user`@`host` IDENTIFIED BY '[passwd]';
接続元ホストは'%'を指定することで全ホストからのアクセスを許可できる
対象DB、テーブルは'*'を指定することで対象オブジェクト全てに適用できる
オプティマイザ
SQLでは実際にどういう手順でデータを取得するかは指定しない
オプティマイザにより、データを実際にどのように取るかを決定し、
データを取得する。＝ 実行計画の作成
コストベースオプティマイザ
データの件数やデータの偏りから実行プランを生成する。
インデックスの統計情報(データの偏り)を元に実行プランが生成される。
実行プラン
渡されたSQL文に対し構築されたデータ取得方式
joinの方法、順序、使用するIndex、全件scanをするかなど
実行プランの確認
EXPLAIN句で実行プランを確認できる
EXPLAIN [SQL]
インデックスの統計情報
実行プラン生成のために使用される、データの偏りの度合いを示した情報
ランダムにページを８回抽出し、そのページ内に含まれる行データを検査して、
インデックスの統計情報を近似（データの偏りを推測）する
統計情報の更新タイミング。
- テーブルがオープンされたとき
- テーブル統計の情報が更新された後、テーブルの全行数の1/16が更新されたとき
- テーブル統計情報の更新後、20億行以上の行が更新されたとき
- ANALYZE TABLEが実行されたとき
- SHOW TABLE STATUS, SHOW INDEX FROM …が実行されたとき
InnoDBのテーブル統計情報について marqs blog
非永続的オプティマイザ統計のパラメータの構成
ログ
バイナリログ
DBへの変更のイベントが書かれるログファイル
レプリケーション、ポイントインタイム (増分) リカバリの際に使用される
書き込まれるデータ形式により３種類のフォーマットがある
- STATEMENT：更新SQL文がそのまま書かれている
- ROW：更新後データのバイナリ情報が書かれる
- MIX：STATEMENTとROWのミックス
スロークエリログ
long_query_timeで指定した秒数以上掛かったクエリが出力されるログ
マイクロ秒単位で指定可能。
クエリ実行完了時に書き込まれる
一般クエリーログ
発行された全てのクエリを出力するログ
クエリを受け取った時点で書き込まれる
エラーログ
MySQLのエラー情報を出力するログ
レプリケーション
1 つの MySQL データベースサーバー (マスター) のデータを、
複数の MySQL データベースサーバー (スレーブ) に複製する
マスターDBサーバー上のDB更新がバイナリログに記載される
スレーブDBサーバーのIOスレッドがバイナリログを取得しリレーログに書き込む
スレーブDBサーバーのSQLスレッドがリレーログの更新クエリを実行することでデータを同期する
デフォルトは非同期
バイナリログをリレーログに書き込むところまでを保証する
準同期レプリケーションも設定可能。
※完全同期はなし
共通機能のメモリ設定
接続スレッド毎のバッファ
接続スレッド毎に作成されるバッファ領域
join_buffer_size:
indexを使用しないjoinに利用するバッファ。
バッファ領域が一杯になるまでデータをメモリに読み取り、joinを繰り返すsort_buffer_size：
sortに利用するメモリ。足りない場合はtmpdirにfileが作成されるbinlog_cache_size:
トランザクション中にバイナリログへの変更を保持するキャッシュのサイズmax_allowed_packet:
クライアントからMySQL サーバーに送信されるパケットの最大サイズ
パケット => 単一のSQLステートメント、マスターからスレーブに渡るバイナリログtmp_table_size:
一時テーブルの最大サイズ。
共通のバッファ
table_open_cache:
同時にメモリ上に展開できるテーブル数
一時テーブルについての補足
ユーザー、MySQLが内部処理で作成するメモリ上のテーブル
使用後は自動で削除される
tmp_table_sizeまでMemoryストレージエンジンのテーブルとしてメモリ上で作成され、
それを超える場合はMyISAMに変換されDisk上に作成される。
（※5.7ではInnoDB）
そのためtmp_table_sizeを大きくする場合は、
max_heap_table_sizeも同時に大きくする必要がある。
一時テーブルが使用される内部処理
- サブクエリの結果
- UNION
- DISTINCTとORDER BYの組み合わせ
- ORDER BYと別のGROUP BYの組み合わせ
- SQL_SMALL_RESULTを使用した場合
- 複数テーブルのUPDATE
- GROUP_CONCAT() または COUNT(DISTINCT) 評価。