目的
MySQL へ新しい関数を追加するためには下記の３つの手法があります
1. ユーザ定義関数（UDF）を作成する手法
2. ストアドファンクションを作成する手法
3. ネイティブ（組み込み）MySQL 関数を作成する手法
この内、３に関しては MySQL のソースコードを変更し組み込む必要があるため、非常に敷居が高いです
そのため、今回は、１と２のそれぞれで Pascal Case を行う関数を作成し、速度面の検証を行ってみたいと思います
環境
[shell]
$ cat /etc/lsb-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=17.10
DISTRIB_CODENAME=artful
DISTRIB_DESCRIPTION="Ubuntu 17.10"
$ mysql –version
mysql Ver 14.14 Distrib 5.7.20, for Linux (x86_64) using EditLine wrapper
[/shell]
OS は Ubuntu Server 17.10 とし、ほぼまっさらな状態です
open_sshd_server と mysql-server, mysql-client のみ別途インストールしています
my.cnf もインストール直後のデフォルトのまま変更していません
UDF のソースコード
[c]
#include <mysql.h>
#include <m_string.h>
extern "C" {
my_bool pascal_case_udf_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
void pascal_case_udf_deinit(UDF_INIT *initid);
char *pascal_case_udf(UDF_INIT *initid, UDF_ARGS *args, char *result,
unsigned long *length, char *is_null, char *error);
}
char *s1 = NULL;
my_bool pascal_case_udf_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
if(args->arg_count == 1 && args->arg_type[0]==STRING_RESULT) {
s1 = new char [args->lengths[0] + 1];
return 0;
} else {
strcpy(message, "Expected exactly one string type parameter" );
return 1;
}
}
void pascal_case_udf_deinit(UDF_INIT *initid __attribute__((unused)))
{
if (NULL != s1) {
delete[] s1;
}
}
char *pascal_case_udf(UDF_INIT *initid __attribute__((unused)),
UDF_ARGS *args, char *result, unsigned long *length,
char *is_null, char *error __attribute__((unused)))
{
bool active = true;
const char* word = args->args[0];
if (!word) {
assert(args->lengths[0] == 0);
*is_null=1;
return NULL;
}
for(int i = 0, j = 0; i < args->lengths[0]; i++) {
if(std::isalpha(word[i])) {
if(active) {
s1[j++] = std::toupper(word[i]);
active = false;
} else {
s1[j++] = std::tolower(word[i]);
}
} else if(word[i] == ‘ ‘) {
s1[j++] = ‘ ‘;
active = true;
}
s1[j] = ‘\0’;
}
memcpy(result, s1, strlen(s1));
*length= (unsigned long)(strlen(s1));
return result;
}
[/c]
UDF を作成する場合には、マニュアルにかかれている xxx() や xxx_init() 関数を実装する必要があります
今回は最低限必要な xxx_init() , xxx_deinit() , xxx() の３つの処理を実装し検証を行っています
※それぞれの関数の役割等に関してはマニュアルを参照していただきたいと思います
※また、メモリ管理等は深く追求していないサンプルコードです
コンパイル
[shell]
$ gcc -I/usr/include/mysql -shared -o pascal_case.so -fPIC pascal_case.cc
$ ls -l
合計 16
-rw-rw-r– 1 kitada kitada 1654 10月 25 14:09 pascal_case.cc
-rwxrwxr-x 1 kitada kitada 8480 10月 25 14:09 pascal_case.so
[/shell]
ここでのポイントとしては、MySQL のヘッダファイルをきちんとインクルードする必要があり、忘れるとコンパイルエラーになります
MySQL で利用可能にする
まずはプラグインディレクトリを調べます
[shell]
$ mysql -u root -p -e "SHOW VARIABLES LIKE ‘plugin_dir’"
Enter password:
+—————+————————+
| Variable_name | Value |
+—————+————————+
| plugin_dir | /usr/lib/mysql/plugin/ |
+—————+————————+
[/shell]
プラグインディレクトリにコンパイルしたオブジェクトをコピーします
[shell]
$ sudo cp pascal_case.so /usr/lib/mysql/plugin/
$ ls -l /usr/lib/mysql/plugin/pascal_case.so
-rwxr-xr-x 1 root root 8480 10月 25 14:09 /usr/lib/mysql/plugin/pascal_case.so
[/shell]
MySQL に組み込む
[SQL]
mysql> CREATE FUNCTION pascal_case_udf RETURNS STRING SONAME ‘pascal_case.so’;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT pascal_case_udf(“foo bar baz”) FROM DUAL;
+——————————-+
| pascal_case_udf(“foo bar baz”) |
+——————————-+
| Foo Bar Baz |
+——————————-+
1 row in set (0.00 sec)
[/SQL]
削除する場合
[SQL]
mysql> DROP FUNCTION IF EXISTS `pascal_case_udf`;
Query OK, 0 rows affected (0.01 sec)
[/SQL]
ストアドファンクションの登録
[SQL]
DELIMITER //
CREATE FUNCTION `pascal_case_sf`(str varchar(255)) RETURNS varchar(255)
BEGIN
DECLARE i, pos INT DEFAULT 1;
DECLARE sub, result VARCHAR(255) DEFAULT ”;
if length(trim(str)) > 0 then
WHILE pos > 0 DO
set pos = locate(‘ ‘,trim(str),i);
if pos = 0 then
set sub = lower(trim(substr(trim(str),i)));
else
set sub = lower(trim(substr(trim(str),i,pos-i)));
end if;
set result = concat_ws(‘ ‘, result, concat(upper(left(sub,1)),substr(sub,2)));
set i = pos + 1;
END WHILE;
end if;
RETURN trim(result);
END
//
DELIMITER ;
mysql> SELECT pascal_case_sf(“foo bar baz”) FROM DUAL;
+——————————+
| pascal_case_sf(“foo bar baz”) |
+——————————+
| Foo Bar Baz |
+——————————+
1 row in set (0.01 sec)
[/SQL]
削除する場合
[SQL]
mysql> DROP FUNCTION IF EXISTS `pascal_case_sf`;
Query OK, 0 rows affected (0.01 sec)
[/SQL]
UDF vs StoredFunction 速度対決
計測方法
[shell]
time $(for i in $(seq 1 10000); do mysql –login-path=root test –execute ‘SELECT pascal_case_udf("foo bar baz") FROM dual;’ > /dev/null; done)
time $(for i in $(seq 1 10000); do mysql –login-path=root test –execute ‘SELECT pascal_case_sf("foo bar baz") FROM dual;’ > /dev/null; done)
[/shell]
それぞれ 10,000 ✕ 3 回実行
結果
| real | user | sys | |
|---|---|---|---|
| UDF (1回目) | 22.935s | 17.740s | 4.646s |
| UDF (2回目) | 23.514s | 18.014s | 4.955s |
| UDF (3回目) | 23.670s | 18.103s | 5.031s |
| StoredFunction (1回目) | 25.341s | 17.967s | 5.206s |
| StoredFunction (2回目) | 25.491s | 18.384s | 4.941s |
| StoredFunction (3回目) | 24.379s | 17.729s | 4.599s |
考察
僅かに UDF の方が高速な結果だが、誤差の範囲と言えないレベルの差です
C/C++ の処理をもっと見直せば、高速になる余地は少なからずあるとは考えられるが、多少早くなる レベルだと思われます
他には、UDF はデータベースに依存せずに使用可能だが、ストアドファンクションはデータベースや実行ユーザの権限にも依存するといった点があるため、実際の利用にはセキュリティ等にも考慮する必要があります
