ucs2エンコーディングのススメ

はじめに

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 さんです！んがんぐっ！