書籍「やさしく学べるMySQL運用・管理入門」のまとめ。
点数
78点
感想
実際の運用方法よりもMySQLの仕様の説明が中心だった。
文字コード、照合順序、システム変数のチューニング、などは参考になった。
MySQL5.7からはストレージエンジンを必ずInnoDBにするべきであり、MyISAMを使う意味はなくなった。
ストレージエンジン
InnoDB
- 特別な理由がない限り、InnoDBを利用するべき。
- 外部キーを使えるのはInnoDBだけ。
- MySQL5.5からデフォルトがInnoDBになった。
- テーブル毎に.ibdファイルが生成され、データとインデックス情報が格納される。
- InnoDBは、MySQL5.6から全文検索が使えるようになり、5.7からは日本語にも対応できるようになった。
MyISAM
以前は参照性能が高いという理由でMyISAMが利用されることもあったが、実装が古いため、マルチコアCPU環境では性能がでないこともあり、最近では利用がほとんどいない。
- MyISAMはデータ更新時にテーブルロックするため同時実行時の性能が悪い。 InnoDBは行ロックである。
- MySQL5.7のシステムテーブルはMyISAMだが、MySQL8からはInnoDBになる。
文字コード
my.cnfでサーバ全体、データベース、テーブル、行、クライアント、の文字コードの設定が可能。
通常はutf8mb4を使うべき。
MySQL5.7のデフォルトは半角英数字と記号のみを使うlatin1だが、MySQL8.0ではutf8mb4になる予定。
照合順序(COLLATION)
utf8mb4で利用できるCOLLATIONは26種類ある。
末尾がciのものは大文字小文字を区別しない。(Case Insensitive)
末尾がcsのものは大文字小文字を区別する。(Case Sensitive)
末尾がbinのものはバイナリとして扱い、全ての文字を区別する。
utf8mb4のデフォルトCOLLATIONはutf8mb4_general_ciである。
- utf8mb4_general_ci: 大文字小文字は区別しない。全角半角を区別する。
- utf8mb4_unicode_ci: 大文字小文字/全角半角を区別しない。
- utf8mb4_bin: 全てを区別する(バイナリとして扱う)。絵文字を使用する場合などに使う。
ログ
バイナリログ
- 更新系の処理の内容、実行時刻などを記録したログ。
- レプリケーションでは必須。
- デフォルトでは出力されないので、システム変数log_binで出力ファイルを指定する。
- MySQL5.7からはserver_idの設定が必須となった。
- expire_logs_daysで設定された日数が経過したバイナリログは自動的に削除される。
- MySQL5.6まではSQL文をログとして記録するStatement baseだったが、MySQL5.7からはRow Baseに変わった。
- バイナリログはそのままテキストエディタで開くことはできず、mysqlbinlogコマンドを使う必要がある。
- Row Baseのバイナリログの場合は-vオプションをつけるとSQL文も出力される。
ex)mysqlbinlog /usr/local/var/mysql/mysql-bin.000001 -v mysqladmin - flush-logsでログのローテーションが行われる。
スロークエリログ
- デフォルトでは出力されない。
- log_slow_queriesを設定して出力するべき。
- long_query_timeで閾値を秒単位で指定可能(デフォルト10秒)
mysqladmin
データベース作成、パスワード変更、processlist表示、稼働状況表示、などができるコマンド。
MySQL Workbench
GUIツール。
E/R図作成機能があり、E/R図からのDDL生成や、MySQLサーバ上のテーブルからのE/R図生成、などが可能。
JSONデータ型
- MySQL5.7からデータ型にJSON型が追加された。
- 格納されるデータ形式が正しいかがチェックされる。
- 文字コードはutfmb4、照合順序はutf8mb4_binとして扱われる。
- 他の文字コードで生成されたはutf8mb4に変換される。
- データはテキストではなくバイナリ化して格納される。
- 検索時は、JSON演算子やJSON_EXTRACT関数を使って、キーや配列のインデックスを指定することができる。
CREATE TABLE pz(menu JSON);INSERTI NTO pz(menu) VALUES('{"Name":"PlainPizza","price":300}');
に対してSELECT JSON_EXTRACT(menu, "$.Name") FROM pz;
またはSELECT menu->"$.Name" FROM pz;
mysqlpump
- MySQL5.7から追加された、mysqldumpの後継コマンド。
- スキーマ毎の並列処理や進捗表示機能などが追加された。
全文検索
MySQL5.6ではInnoDBで全文検索インデックスが可能になったが、日本語や中国語などスペースを区切り文字としない言語ではキーワードを切り出すことができなかった。
そのためアプリケーション側で単語の間にスペースを追加してから登録する必要があった。
MySQL5.7ではスペースを区切り文字としない言語でも全文検索インデックスが使えるようになった。
キーワードを切り出すためのパーサーとしてはN-gramとMeCabがサポートされている。
N-gramは一定の文字数でキーワードを切り出す手法であり、Nには文字数が入り、2文字単位の場合はbi-gramと呼ばれる。
MySQL5.7ではデフォルトでN-gramが利用可能となっている。
MeCabは日本語の辞書を使ってキーワードを切り出すオープンソースの形態素解析エンジンである。
MySQL5.7で使用する場合はプラグインをインストールする必要があるが、必要なモジュールはMySQL5.7のバイナリに含まれているため、簡単な設定で使用可能となる。
稼働状況の確認
mysqlクライアントからSHOW GLOBAL VARIABLES;で設定ファイルの設定値を、SHOW STATUS;でステータス変数を、それぞれ確認することができる。SHOW STATUS LIKE 'Com\_%'のようにLIKE文を使って絞り込みを行う。
ステータス変数は大半が累積値を表示するため、稼働状況を確認したい処理の前後で変数を表示し、差分を確認する必要がある。
主なステータス変数
- Com_select: SELECT回数
- Com_insert: INSERT回数
- Innodeb_buffer_pool_read_requests: 論理読み込みリクエスト数
- Innodeb_buffer_pool_reads: ディスク読み取りリクエスト数
- Threads_connected: 接続中のコネクション数
システム変数のチューニング
sort_buffer_size
- ソート用のメモリ領域。
- このサイズを超えたソート処理はファイルソートとなり時間がかかる。
- ステータス変数Sort_merge_passesでファイルソートが発生しているか確認できる。
- セッション単位で変更できるため「バッチ処理だけ値を大きくする」ようなことも可能。
tmp_table_sizeとmax_heap_table_size
- UNION, ORDER BY, GROUP BYなどで使用する内部的な一時テーブルの領域。
- このサイズを超えるとディスク上に一時テーブルを書き出すため時間がかかる。
- tmp_table_sizeとmax_heap_table_sizeの小さい方の値がメモリ領域のサイズ上限となる。
- ディスク上に書き出しているかはステータス変数Created_tmp_disk_tablesで確認可能。
- セッション単位で変更可能。
thread_cache_size
- クライアントがサーバに接続すると生成されるコネクションスレッドをキャッシュして使い回すための領域。
- ステータス変数Threads_createdに値が入っていること自体は問題ないが、値が増加している場合はキャッシュが足りていないと判断できるためthread_cache_sizeの値を増やすべき。
innodb_buffer_pool_size
- InnoDBがデータとインデックスをキャッシュするメモリー領域のサイズ。
- この設定値まではメモリにキャッシュされるため高速に動く。
- 推奨のサイズは物理メモリの8割、と言われている。
innodb_log_file_size
- トランザクションをコミットした時に保存されるInnoDBログファイルのサイズ。
- 大きくするとダーディページ(メモリ上で変更されたがディスクにはまだ書き出されていないデータ)をたくさんメモリ上に保持できるようになるため、更新処理が多発する環境では設定値を大きくすることでパフォーマンスの向上が見込める。
- innodb_buffer_pool_sizeの4分の1にするべき。
- innodb_log_files_in_groupでログファイルの個数が設定されていてデフォルト値は2であるため、その場合はinnodb_log_file_sizeはinnodb_buffer_pool_sizeの8分の1にするべき。