はじめに
MySQL Casual Advent Calendar 2011 - MySQL Casualで24日目を担当します。nihenです。 といいながら、25日になっちゃっています。すみませんすみません。
さて、みなさんはテキスト系カラムのキャラクタセットは何を指定していますか？
- cp932 に決まってるでござる
- いやむしろ sjis でござる
- いやいや5c問題を避けるために ujis でござる
- 当然决定为Big5
- 2010年代に utf8mb4 使わないで許されるのは小学生までだよねー
- 漢はだまって binary
ここから述べることは、カジュアルに"utf8"キャラクタセットを使っている方々へのカジュアル情報です。上記に当てはまった漢のみなさまはそっとブラウザをお閉じください。
診断
SELECT SUM(LENGTH(column) - CHAR_LENGTH(column)*2) FROM table;
おもむろに、上記のクエリを適当なデータ量の多いutf8キャラクタセットにしているカラムに対して実行してみましょう。（column, tableを書き換えて実行してください)
帰ってきた値が正の値だった場合はその値のバイト数だけディスクスペースを節約できる可能性があります。負の値だった場合は残念でした。
方法
簡単です。そのカラムを"ucs2"キャラクタセットに変更するだけです。
ALTER TABLE table CHANGE COLUMN column column VARCHAR(255) CHARACTER SET 'ucs2' COLLATE 'ucs2_bin' NOT NULL;
などの方法で行えますね。これだけでディスクスペースが縮小されます。utf8とucs2は文字集合が共通(unicodeのBMP)なため、round-trip問題は発生しません。
え、でもプログラムからはutf-8でmysqlに接続してるよと思った方はご安心ください。mysqlはカラムのキャラクタセットからcharacter_set_clientに指定されているキャラクタセットに変換を自動で行います。プログラム側に修正は必要ありません。
ucs2にするとutf8の場合の3byteキャラクタ(日本語とかがそうですね!)が2byteになるというメリットがあります。反面、1byteキャラクタ(asciiですね)が2byteに増えるというデメリットがあります。ですので日本語とasciiのどちらが多いかということが判断基準となります
実演
mysqlでのディスクスペースの削減といえば、InnoDB PluginのROW_FORMAT=COMPRESSEDなわけですが、以前id:sh2さんがMySQL InnoDB Pluginのデータ圧縮機能 - SH2の日記においてWikipedia日本語版のデータベースの圧縮を実演されてましたので、真似してみたいと思います。とはいえ時間があまりなかったので変更対象はpageテーブルのpage_titleカラムだけにしています。
xml2sql
wget http://ftp.tietew.jp/pub/wikipedia/xml2sql-0.5.tar.gz tar xzf xml2sql-0.5.tar.gz cd xml2sql-0.5 ./configure make sudo make install
インポート用sql用意
mysqlimport用のデータを作るのがデフォルトなのですが、ucs2でのインポートはできないのでsqlファイルの作成を行っています。
wget http://dumps.wikimedia.org/jawiki/20111217/jawiki-20111217-pages-articles.xml.bz2 bunzip2 jawiki-20111217-pages-articles.xml.bz2 cat jawiki-20111217-pages-articles.xml | sed -e 's/<redirect \/>//' | xml2sql -m
sandbox_dbの用意
wget "http://svn.wikimedia.org/viewvc/mediawiki/trunk/phase3/maintenance/tables.sql?view=co" -O tables.sql echo 'create database sandbox_utf8;' | mysql -u root -p echo 'create database sandbox_ucs2;' | mysql -u root -p mysql -u root -p sandbox_utf8 < tables.sql mysql -u root -p sandbox_ucs2 < tables.sql echo "ALTER TABLE page CHANGE COLUMN page_title page_title VARCHAR(255) COLLATE 'ucs2_bin' NOT NULL;" | mysql -u root -p sandbox_ucs2
インポート
時間かかりますよー。
mysql -u root -p sandbox_utf8 --default-character-set=utf8 < page.sql mysql -u root -p sandbox_ucs2 --default-character-set=utf8 < page.sql
確認
mysql>use sandbox_utf8 mysql> show table status like 'page'; +------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+ | page | InnoDB | 10 | Compact | 1541446 | 98 | 151683072 | 0 | 220758016 | 5242880 | 2484792 | 2011-12-24 22:46:27 | NULL | NULL | utf8_bin | NULL | | | +------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+ 1 row in set (0.00 sec) mysql>use sandbox_ucs2 mysql> show table status like 'page'; +------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+ | page | InnoDB | 10 | Compact | 1553463 | 93 | 145375232 | 0 | 210255872 | 5242880 | 2484792 | 2011-12-25 00:27:57 | NULL | NULL | utf8_bin | NULL | | | +------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+ 1 row in set (0.01 sec)
というわけで、Data_lengthが151683072 => 145375232(6MB,5%弱削減)、Index_lengthが220758016 => 210255872(10MB,5%弱削減)という結果になりました。もうちょい減るかなーと期待してたのですが若干しょぼい結果にはなりましたが削減自体は成功ということで…。この5%を多いとみるか少ないと見るかは…。いややっぱ少ないかなー。まあカジュアル！ということで。
まとめ
utf8のキャラクタセットを採用しているカラムをucs2に変更するとスペースの削減が行える場合があることを紹介しました。ucs2にした場合のベンチマークなどの話は3年前の下記記事に書いてありますので興味のある方は併せてご覧ください
mysqlの内部キャラセットはucs2にするといいんじゃないだろうか | へぼい日記
mysqlでcharsetをucs2にした場合のasciiのみのフィールド | へぼい日記
さ〜て、明日は大トリ、 y_wakai さんです！んがんぐっ！