InnoDBの圧縮テーブルの設定

一つのモデルに対する作成可能なカラム数に制限はありませんが、何も考えずにカラムの数を増やしてしまうと、MySQL側の制限にかかることがあります。

MySQL :: MySQL 5.6 リファレンスマニュアル :: D.10.4 テーブルカラム数と行サイズの制限

すべてのテーブル (ストレージエンジンには無関係) の最大行サイズは 65,535 バイトです。ストレージエンジンではこの制限に対してさらなる制約を加えられる場合があり、有効な最大行サイズは少なくなります。

すべてのカラムの合計長は最大行サイズを超えられないので、このサイズはカラム数 (およびサイズの可能性もあり) を制約します。たとえば、utf8 文字では、文字あたり最大 3 バイトが必要になるので、CHAR(255) CHARACTER SET utf8 カラムの場合、サーバーは値ごとに 255 × 3 = 765 バイトを割り当てる必要があります。この結果、テーブルにはこのようなカラムを 65,535 / 765 = 85 以上は含められません。

この制限にかかってしまうと、カラムを作成する際に以下のようなエラーが出て、ALTER TABLEに失敗することがあります。

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs, ALTER TABLE...

また、画像ファイルなどを大量に扱うウェブサイトでは、データベースの容量が大きくなってしまうことがあります。

このような場合、InnoDBの圧縮テーブルの設定を行うのが良いでしょう。InnoDBの圧縮テーブルの設定は以下の手順で行うことができます(MySQLのバージョンは5.6以上である必要があります)。

※ この設定をインストール後に行う場合は、データベースのバックアップを作成し、作業中は更新作業を行わないようにしてください。

あらかじめ、MySQLにて以下の設定がされているか確認し、設定されていない場合は、設定を追加してください。

innodb_file_per_table=1
innodb_file_format=Barracuda

db-config.php への設定の追加

以下の設定を行うことで、InnoDBの圧縮テーブルを利用するようになります。

define( 'PADO_DB_COMPRESS', true );

テーブル圧縮スクリプトの実行

アプリケーションディレクトリで、以下のコマンドを実行します。

php ./tools/PADO/compressDB.php

以上で、設定は完了です。

その他の注意点

一つのモデルにあまりたくさんのカラムを追加すると、モデルの保存時にPHPの「max_input_vars」設定(デフォルトは1000)の上限にひっかかってしまうことがあります。

カラム数に対してmax_input_varsの設定値が不足している場合、管理画面に警告が表示されますので、適宜設定を変更するようにしてください。なお、必要以上に列を長くしないようにするには、以下の設計方法も検討の余地があります。

  • インデックスを必要としないカラムは「TEXT(255)」型ではなく、「TEXT」型で作成する
  • 「BLOB」型、編集表示「ファイル」のカラムの代わりに、「添付ファイル」機能を使う
  • 項目数の多い選択型のカラムは、別にモデルを作成してリレーションを設定する
  • カスタムフィールドを利用する(カスタムフィールドは、次期バージョンで正式サポートとなります)