バックアップを取りたいときにうっかり容量が大きいテーブルを取得しようとして大変なことになった…そんな経験はありませんか。
私は最近ローカル環境でやってしまいました。
バックアップ前には、データベースやテーブルのサイズを確認しておくことが大切です。
MySQLの容量を確認したいときのコマンド
SELECT文で取得できます。
SELECT table_schema,
floor(SUM(data_length + index_length) / 1024 / 1024) AS ALL_MB,
floor(SUM((data_length) / 1024 / 1024)) AS DATA_MB,
floor(SUM((index_length) / 1024 / 1024)) AS INDEX_MB
FROM information_schema.tables
GROUP BY table_schema
ORDER BY sum(data_length + index_length) DESC;何もしないとbyte単位で出るので、見やすいようにMBに変換します。
指定のデータベースのテーブルサイズやタイプをみたいときはこれ。
SELECT table_name,
engine AS DBエンジン,
table_rows AS 行数,
avg_row_length AS 平均レコード長,
floor((data_length+index_length) / 1024 / 1024) AS ALL_MB,
floor(data_length / 1024 / 1024) AS DATA_MB,
floor(index_length / 1024 / 1024) AS INDEX_MB
FROM information_schema.tables
WHERE table_schema = '.データベースの名前'
ORDER BY (data_length + index_length) DESC;WHEREの条件で確認したいテーブルのあるデータベースの名前を入れます。
実行例
以前エンジニア婦人ノートで使っていた、WordPressデータベースの容量一覧です（今は使用していません）。
wp_posts、他と比べると結構ありますね。9MB。
MBだと0ばかりでよくわからなかったので、KBでも出してみます。/1024をひとつ減らしました。
みやすくなりました。
データベースの種類
よくみるとDBエンジンに「InnoDB」と「MyISAM」があります。
これは テーブルのストレージエンジンの種類 を示していて、データの扱い方や性能に関わります。
よほど大規模なシステムでもなければあまり気にしないところだとは思いますが（個人開発やWordPressを導入して云々するときに気にしませんよね）、せっかくなのでどういった特徴があるのか、使うときに注意すべき点などを調べてみました。
InnoDB
DBエンジンのInnoDBはトランザクション対応で、データの整合性を絶対に守りたいときに使います。
MySQLのデフォルトはInnoDBになっているようです。
set autocommit = 0;
start transaction;
begin;
~~~
commit;
rollback;この辺りを使えます。
行単位のロック機能も使えます。異常終了したらログを元に戻してくれるとか…
整合性を確実に取りたいデータの時はこちらですね。
ただし読み込みと書き込みがちょっと遅いそうです。
またバックアップ時に容量を大きく取るので、作業中メモリ不足にならないよう注意が必要だそうです。
InnoDBのバックアップを取るときは --single-transaction をつけると安心。
稼働中のテーブルに影響が出ないように、バックアップ開始時点の一貫性のある状態を確実にダンプします。
MyISAM
これに対してMyISAMは途中で操作を取り消すことができないそうです。commit&rollbackが使えないんだそうです。
「INSERT/UPDATEしたけどやっぱりやめよう！」が効かない。
ロックは行単位ではなく、テーブル単位です。
ですが、データの読み取りはInnoDBよりも高速らしいです。
要は基本は読み取りであまりデータを入れたり更新はしない、もし何か間違えたらもう一回やり直せばなんとかなるデータ、というときに向いています（？）。
…あれ？wp_posteって記事情報を入れるテーブルだけど、途中で取り消せないし何かあったときに整合性の保証がないんですね？（※これはちょっと古いWoｒｄPressの情報でした。今はInnoDB標準らしいです）
毎秒何かしらの追加や更新をするわけではなし、サイトにアクセスしたときに早く表示できるように、読み取りを優先しているのですね。
まとめ
データベースやテーブルのサイズを確認するSQL、各データベースエンジンの特徴についてまとめました。
更新や整合性が大事なデータは InnoDBを使って、バックアップは --single-transaction を使います。
読み取り中心のテーブルはMyISAMでもOKです。が、transaction系が使えないことに注意です。
