mregexp

mregexpとは

目的

今のところ(mysql 4.0.18, 4.1.1)、MySQLのREGEXP関数は日本語を正しく処理できない(一方、LIKEやSUBSTRINGなどは日本語の処理に対応している)。そこで日本語を正しく扱える正規表現関数、mregexpというものをユーザー定義関数(UDF=User Defined Function)という仕組みを用いて作った。

ただし、 MySQLの正規表現エンジンは見直されるという情報があり、近い将来REGEXP関数で正しく日本語が扱えるようになるかもしれない。

機能

REGEXP関数と同じように、WHERE句でレコードの抽出に使うことができる。

REGEXP関数は日本語などのマルチバイト文字を考慮していないため、以下のような問題がある。

パターン'あ.う'が文字列'あいう'にマッチしない。
EUC-JPの場合、パターン'い'が文字列'イあ'にマッチしてしまう。これは「い」のコードポイントが0xA4A4で、「イあ」のが0xA5A4A4A2であるために起こる。

mregexpの正規表現の処理には鬼車(Oniguruma)を使用している。 Onigurumaは日本語の文字エンコーディングであるEUC-JP, Shift_JIS, UTF-8をはじめ、様々な文字エンコーディングに対応しているため、このような問題は解決される。

ライセンス

ライセンスは修正済BSDライセンスとする。ライセンス条件を満たす限り、商用・非商用、クローズ・オープンソースに関わらず再配布、使用することができる。詳細なライセンスは配布物に含まれるファイルCOPYINGを参照のこと。参考までに、FreeBSD Projectのサイトで修正済BSDライセンスの日本語訳が公開されている。

ダウンロード

name	version	date	size
mregexp-0.3.tar.gz	0.3	2004-08-26 00:18:55	13.1 KB
mregexp-0.2.tar.gz	0.2	2004-04-13 16:59:00	12.8 KB

インストール方法

必要なもの

MySQLの開発環境 (MySQL-devel-VERSION.i386.rpmなど)
Oniguruma 2.X系

開発環境

以下のような環境で開発しているので、これらと同じような環境であれば動作すると思われる。

Linux, kernel-2.4.26, glibc-2.3.3
MySQL-4.1.3-beta-Max-log
Oniguruma-2.2.8
gcc-3.3.3

Linux, kernel-2.6.8.1, glibc-2.2.5
MySQL-4.0.20-log
Oniguruma-2.3.1
gcc-2.95.4

インストール

以降ではUNIX likeな環境でのインストール方法を示す。 WindowsでVC++でのインストール方法は、MySQL ML [mysql 09205] <http://www.mysql.gr.jp/mysqlml/mysql/msg/9205> と [mysql 09206] <http://www.mysql.gr.jp/mysqlml/mysql/msg/9206> とで報告されているのでそちらを参照のこと。

Makefileの INSTALL_LIBDIRとDEFAULT_ENCODINGの値を適宜変更する。
INSTALL_LIBDIRはUDFのオブジェクトファイルを配置するディレクトリ (以下、ここで指定したディレクトリをINSTALL_LIBDIRとする)である。
DEFAULT_ENCODINGで指定したものは、 mregexp関数の実行時に文字エンコーディングが指定されなかった場合の初期値となる。my.cnfの[mysqld]セクションのdefault-character-setと同じ文字エンコーディングにするとよい。 MakefileにはShift_JIS, EUC-JP, UTF8の例があるが、これ以外の文字エンコーディング(例えばEUC-KR)をここで指定したい場合は、 OnigurumaのAPIにあるONIG_ENCODING_XXXを指定すること。

コンパイル後、rootになってインストールする。

$ make
# make install

環境変数LD_LIBRARY_PATHにINSTALL_LIBDIRを指定してmysqldを起動し直す。
MySQL AB提供のrpmパッケージを使っている場合は、mysqldの起動スクリプト/etc/init.d/mysqlを以下のように変更してから/etc/init.d/mysql restartと実行して起動し直せばよい。

--- /etc/init.d/mysql   2004/04/12 08:40:57     1.1
+++ /etc/init.d/mysql   2004/04/12 08:47:27
@@ -151,6 +151,7 @@
     then
       # Give extra arguments to mysqld with the my.cnf file. This script may
       # be overwritten at next upgrade.
+      env LD_LIBRARY_PATH=/usr/local/lib/mysql \
       $bindir/mysqld_safe --datadir=$datadir --pid-file=$pid_file >/dev/null 2>&1 &
       # Make lock for RedHat / SuSE
       if test -w /var/lock/subsys

mysqldが起動したら、mysqlのユーザーrootで接続し、以下のSQL文を発行する。

CREATE FUNCTION mregexp RETURNS INTEGER SONAME 'mregexp.so';
CREATE FUNCTION mregexp_version RETURNS STRING SONAME 'mregexp.so';

以上でmregexp関数が使えるようになった。

必須ではないが、次のようにして動作確認を行うことができる。

MySQL 4.0.x でdefault-character-set = ujisの場合: make test_ujis
MySQL 4.0.x でdefault-character-set = sjisの場合: make test_sjis
MySQL 4.1.x の場合: make test41

動作確認のために、デフォルトではmysql -s testでデータベースtestに接続し、 DROP TABLE, CREATE TABLE, INSERT, SELECTを発行する。接続ユーザーなどを変更したい場合は、make実行時にT_DOSQLの値を指定することで制御できる。
例えば、ユーザーscott、パスワードtiger、データベースempに接続して動作確認する場合は、 make T_DOSQL='mysql -s -uscott -ptiger emp' test_ujis と実行する。

使い方

文法

mregexp(expr,pat[,encoding])

文字列exprに対して、正規表現patのパターンマッチを実行する。デフォルトでは大文字小文字を区別してパターンマッチを実行する。
encodingで指定できる文字列は以下の通り。 encodingの指示子は大文字小文字を区別しない。つまり、'euc-jp'と'Euc-Jp'とは同値である。省略時はコンパイル時にDEFAULT_ENCODINGで指定したものとなる。

Shift_JIS, sjis
EUC-JP, ujis
UTF-8, utf8
ASCII
ISO-8859-1, latin1
ISO-8859-2, latin2
ISO-8859-3
ISO-8859-4
ISO-8859-5
ISO-8859-6
ISO-8859-7, greek
ISO-8859-8, hebrew
ISO-8859-9, latin5
ISO-8859-10
ISO-8859-11
ISO-8859-13, latin7
ISO-8859-14
ISO-8859-15
ISO-8859-16
EUC-TW
EUC-KR, euckr
EUC-CN
KOI8
KOI8-R, koi8r
Big5

mregexp関数の返値は、パターンにマッチした場合は1、マッチしなかった場合は0になる。

もう一つ、 mregexpのバージョン、mregexp関数のencodingの初期値、Onigurumaのバージョンを含む文字列を返す関数mregexp_versionがある。

mregexp_version()

mysql> select mregexp_version();
mregexp-0.2 [UTF-8] (oniguruma-2.2.7)

Onigurumaのバージョン番号がおかしいのは気にしないこと。これはOniguruma-2.2.7で修正される予定である。

使用例

以下に使用例を示す。使用できる正規表現の詳細は、 Oniguruma 正規表現を参照のこと。 mregexpで使用している正規表現の種類はRuby(= ONIG_SYNTAX_DEFAULT = ONIG_SYNTAX_RUBY)である。

カラムs1をEUC-JPとしてパターンマッチを実行する。: SELECT * FROM table_name WHERE mregexp(s1, '^あ', 'ujis');
「あ」で始まり「お」で終わるカラムを抽出する。: SELECT * FROM table_name WHERE mregexp(s1, '^あ.*お$');
「あいう」もしくは「かきく」を含むカラムを抽出する。: SELECT * FROM table_name WHERE mregexp(s1, '(あいう|かきく)');
「あ」か「か」で始まるカラムを抽出する。: SELECT * FROM table_name WHERE mregexp(s1, '^[あか]');
数字を含むカラムを抽出する。: SELECT * FROM table_name WHERE mregexp(s1, '\\d');
\dではなく\\dである点に注意。
大文字小文字を区別して「abc」を含むカラムを抽出する。: SELECT * FROM table_name WHERE mregexp(s1, 'abc');; SELECT * FROM table_name WHERE mregexp(s1, '(?-i)abc');
大文字小文字を区別しないで「abc」を含むカラムを抽出する。: SELECT * FROM table_name WHERE mregexp(s1, '(?i)abc');

注意点

カラムの型がCHARの場合、カラム値の末尾の連続したスペース(0x20)は無視してパターンマッチングを行う。つまり、CHAR(5)の値'abcd '(末尾にスペースが1つある)は、パターン'd$'にマッチするが、'd $'や'd\\s$'にはマッチしない。
理由は次の通り。 UDFに渡されるVARCHAR型の文字列はその最大長になるように末尾にスペースが詰められているため、固定長のCHAR型でスペースが詰められているのと区別できない。独断で、CHAR型を固定長として使うよりVARCHAR型を使う頻度が高いと判断し、このような実装にした。
扱える正規表現はOnigurumaのRuby型(ONIG_SYNTAX_RUBY)である。
MySQL 4.1.1では、初期化関数(mregexp_init)でエラーが発生するとmysqldが再起動してしまう。これはmysqldのバグ(Bug #2697)で、4.1.2では修正される見込みとなっている。

性能について

目的

組み込みのREGEXPやLIKEと比べ、mregexpがどの程度の性能となるかを調べる。
一般的に、性能の評価は非常に難しく、ハードウエアやmysqldのパラメータなどに大きく左右される。ここでは絶対的な評価ではなく、ある環境の中でのmregexpの傾向を見ることを主眼とする。

環境

ハードウエア

Intel Celeron 2.00GHz
メモリ 512MB

ソフトウエア

Linux, kernel-2.6.5, glibc-2.2.5
MySQL-4.0.18-log
Oniguruma-2.2.6
gcc-2.95.4

テーブルは以下のような単純なもの。

CREATE TABLE `t_perm` (
  `c1` varchar(12) default NULL,
  KEY `idx_c1` (`c1`)
) TYPE=InnoDB

データは177,409行格納されている。参考までに、このデータは青空文庫で公開されている芥川龍之介の『文芸的な、余りに文芸的な』『偸盗』『羅生門』『路上』『素戔嗚尊』をMeCab (和布蕪)で字句解析した結果の文字列である。

結果

テストしたクエリは以下の通り。

select * from t_perm where c1 like 'タ%';
select * from t_perm where c1 regexp '^タ';
select * from t_perm where mregexp(c1,'^タ');
select * from t_perm where mregexp(c1,'^タ.テ');
select * from t_perm where c1 like 'タ%' and mregexp(c1,'^タ.テ');

クエリが完了するまでに要した時間は以下の通り。 1回目はmysqldが起動して初めて発行した場合で、2回目は1回目に続けて同じクエリを発行した場合。

クエリ番号	1回目[sec]	2回目[sec]
1	0.09	0.06
2	0.84	0.06
3	1.09	0.86
4	1.07	1.01
5	0.06	0.06

前出のクエリに対してEXPLAINを実行した結果のうち、type、possible_keys、rowsのみを抜き出したものは以下の通りとなった。

クエリ番号	type	possible_keys	rows
1	range	idx_c1	10459
2	index	NULL	176937
3	index	NULL	176937
4	index	NULL	176937
5	range	idx_c1	10450

考察

まず、クエリ3のEXPLAINの結果を見ると、mregexpでは前方一致の正規表現であってもインデックスが使われていないことがわかる。ではREGEXPはどうかというと、クエリ2のEXPLAINの結果の通り、たとえ組み込み関数で前方一致の正規表現であってもインデックスは使用されない。
しかし、クエリ2と3で1回目と2回目の所要時間を見ると、クエリ2では2回目の所要時間が大幅に短くなっている。これはクエリのキャッシュが効いているためで、試しにmysqldのquery_cache_sizeを極端に小さくして試してみると、2回目でも1回目と同程度の所要時間になったことからも裏付けられる。一方、クエリ3では、2回目は多少所要時間が短くなっているが、クエリ2ほど大幅なものではない。推測するに、UDFを使ったクエリの結果はキャッシュされないのではないだろうか。
この推測が正しいと、UDFであるmregexpは前方一致であってもインデックスが使われないし結果のキャッシュもされないことになり、性能が非常に悪いということになる。

そこでmregexpで処理するレコード数を少なくすることによって、所要時間にどのような変化があらわれるか見てみた。これがクエリ4と5である。
クエリ5はLIKEとmregexpを使っているのでクエリ4に比べてコストが高いように見えるが、所要時間を比べてみると圧倒的に二度手間のクエリ5の方が速いという結果になった。これは、まず最初にLIKEが適用され、クエリ4に比べてmregexpが処理するレコードが大幅に少なくなったためだと思う。また、この場合でのLIKEではインデックスが適用されるのでLIKEの処理は短い時間で完了する。

以上より、mregexpはLIKEやREGEXPに比べると性能面では劣るが、LIKEとmregexpを組み合わせて使うことで高い性能が得られることがわかった。
正規表現を使いたい全ての局面でLIKEと組み合わせられるわけではないが、クエリのチューニングの際にはこのようなケースも考慮すべきだと思う。

変更履歴

2004-08-25: version 0.3をリリース。; args->lengths[i]ではなくてstrlen(args->args[i])を使っていたのを修正。(Thanks to Yuzo Kimpara)
2004-04-13: version 0.2をリリース。
2004-04-07: 日本のMySQL MLに初版を投稿。

このページ

2004-08-25: 開発環境の情報を更新。
2004-04-18: Windows(VC++)でのインストール方法へのリンクを追加。
2004-04-13: 公開。

mregexp - MySQLで日本語の正規表現を扱う