MySQL предоставляет широкий набор встроенных функций, которые покрывают значительную часть ежедневных задач. В ситуациях, когда необходимо реализовать что-то специфичное для вашего проекта - можно создать Хранимую Функцию (Stored Function). Однако, при всей своей гибкости, не все задачи можно легко написать на SQL. В таких ситуациях на помощь приходят User Defined Functions - компилируемые в нативный код функций загружаемых из shared library.
Создать свою UDF, в целом, не сложно надо реализовать несколько методов си-API.
Для самой простой UDF достаточно реализовать всего лишь одну функцию:
extern "C" double xxx(UDF_INIT *initid,
UDF_ARGS *args,
char *is_null,
char *error) {
return 0.0;
}Для функций, возвращающих INTEGER или STRING сигнатура будет чуть отличаться.
Входящие аргументы можно достать из полей структуры UDF_ARGS - просто по индексу args->args[0] (количество аргументов хранится в args->arg_count, а их типы в массиве args->arg_type). Возвращаемое значение можно вернуть в MySQL - путем возвращения значения из функции :)
Чтобы вызвать нашу функцию из SQL - ее надо собрать в shared library, и подложить в каталог для плагинов (полный путь можно получить выполнив select @@plugin_dir;). После чего функцию нужно загрузить CREATE FUNCTION xxx RETURNS REAL SONAME "xxx.dylib"; После успешной загрузки, нашу функцию можно будет вызывать из консоли MySQL: SELECT xxx();
Ура! У нас есть работающая функция!
Жизненный цикл UDF
MySQL вызывает UDF-функцию на каждой строке. Если хочется сохранять какое-то состояние между вызовами функции - состояние надо где-то хранить. Создавать изменяемые глобальные переменные для UDF-функций не рекомендуется, так как UDF должны быть потоко-безопасными. MYSQL предоставляет возможность хранить разделяемое состояние за указателем UDF_INIT->ptr, а для большего удобства MySQL вызовет для нас функцию xxx_init() перед выполнением SQL statement-а и xxx_deinit()- по окончании. В этих методах мы сможем выделять и освобождать ресурсы.
extern "C" bool xxx_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
extern "C" void xxx_deinit(UDF_INIT *intd);Помимо аллокации ресурсов, в xxx_init() мы можем валидировать входные параметры: если мы не хотим работать с типами, с которыми функция вызвана - надо вернрнуть true - MySQL прервет выполнение SQL Statement-а и показажет message в качестве текста ошибки.
Для Агрегатных UDF-функций жизненный цикл чуть более сложный:
-
Вызывается
xxx_init() -
Выполняется запрос, строки разбиваются на группы согласно
GROUP BY. -
Для сброса накопленной статистики на первой строке группы вызывается
xxx_clear() -
Для каждой строки в группе вызывается
xxx_add() -
В конце вызывается
xxx()для получения значения агрегатной функции -
Шаги 3-5 повторяются
-
Вызывается
xxx_deinit()
Let’s code!
Попробуем реализовать агрегатную функцию рассчета квантилей. Воспользуемся алгоритмом и структурой данных t-digest ( https://github.com/tdunning/t-digest/ ) а точнее - его реализацию на языке Си( https://github.com/RedisBloom/t-digest-c ). Этот алгоритм позволяет для потока входящих значений с высокой точностью оценить квантили, при этом использую небольшое количество памяти. (Для наших целей этот алгоритм даже избыточен - т.к. мы высчитываем только один квантиль, а t-digest позволяет делать любое число запросов к накопленной статистике).
Начнем с инициализации:
#include "tdigest.h"
#include "include/mysql.h"
typedef struct {
td_histogram_t *tdigest;
} Data;
extern "C" bool mysql_tdigest_init(
UDF_INIT *initid,
UDF_ARGS *args,
char *message) {
if(args->arg_count != 2) {
{1} strlcpy(message, "2 args expected", MYSQL_ERRMSG_SIZE);
return true;
}
// force arguments to double
{2} args->arg_type[0]=REAL_RESULT;
args->arg_type[1]=REAL_RESULT;
{3} Data data =(Data)malloc(sizeof(Data));
data->tdigest = td_new(100); // 100 is recommended by author
initid->ptr =(char*)data;
return false; // This function should return 1 if something goes wrong.
}-
messages- указатель на буфер, куда можно вывести текст ошибки длиной доMYSQL_ERRMSG_SIZEбайт. Рекомендуется ограничиваться 80-ю символвами для лучшего UX. -
args->arg_typeсодержит массив изargs->arg_countэлементов, указывающих тип аргументов. Можно самостоятельно валидировать эти значения (если наша функция поддерживает различные типы входящих аргументов) или можно указать желаемые типы данных - MySQL сам проверит типы и по возможности приведет к нужному типу. -
Создаем структурку, в которой будем хранить разделяемое состояние
В конце работы, надо освободить все занятые ресурсы. Напишем deinit-функцию:
extern "C" void mysql_tdigest_deinit(UDF_INIT *initid) {
Data *data = (Data*) (initid->ptr);
td_free(data->tdigest);
free(data);
initid->ptr = NULL;
}Жизненный цикл UDF-функции отлично ложится на API библиотеки t-digest-c, реализация этих методов тоже тривиальна:
extern "C" void mysql_tdigest_clear(
UDF_INIT *initid,
unsigned char *is_null,
unsigned char *error) {
Data *data =(Data*) (initid->ptr);
td_reset(data->tdigest);
}
extern "C" void mysql_tdigest_add(
UDF_INIT *initid,
UDF_ARGS *args,
unsigned char *is_null,
unsigned char *error) {
Data data =(Data) (initid->ptr);
double value = ((double) (args->args[0]));
td_add(data->tdigest, value, 1);
}
extern "C" double mysql_tdigest(
UDF_INIT *initid,
UDF_ARGS *args,
unsigned char *is_null,
unsigned char *error) {
Data data =(Data) (initid->ptr);
double quantile = *((double*) (args->args[1]));
return td_quantile(data->tdigest, quantile);
}
Собираем:
clang -dynamiclib -lm -lc -Lt-digest-c/build/src -ltdigest_static -I percona-server/bld/include src/main.cc -o mysql_tdigest.dylib
Подкладываем нашу библиотеку к MySQL:
cp mysql_tdigest.dylib /usr/local/opt/mysql/lib/plugin/
Создаем функцию:
CREATE AGGREGATE FUNCTION mysql_tdigest RETURNS REAL SONAME "mysql_tdigest.dylib";На примере тестовой базы доступной в интернете посчитаем квантили:
mysql> SELECT avg(amount_charged), mysql_tdigest(amount_charged, 0.5), mysql_tdigest(amount_charged, 0.9) FROM orders GROUP BY user_id LIMIT 5;
+---------------------+------------------------------------+------------------------------------+
| avg(amount_charged) | mysql_tdigest(amount_charged, 0.5) | mysql_tdigest(amount_charged, 0.9) |
+---------------------+------------------------------------+------------------------------------+
| 1661.3750 | 1247.1 | 3809.0 |
| 1079.3158 | 908.0 | 2740.0 |
| 1331.5581 | 1280.5 | 1991.0 |
| 2987.0000 | 2796.5 | 6235.0 |
| 1150.0909 | 1289.0 | 1630.0 |
+---------------------+------------------------------------+------------------------------------+
Удалить функцию:
DROP FUNCTION mysql_tdigest;Вроде, не сложно =)
Прочие возможности
На самом деле никто не заставляет писать UDF на чистом Си - главное, поддерживать C calling convention. Например, можно использовать C++ wrappers ( https://jira.percona.com/browse/PS-7348 ), а если не бояться unsafe и raw-pointers - то функции на Rust FFI тоже отлично работают:
#[no_mangle]
pub unsafe extern "C" fn my_summ(
initid: *mut UDF_INIT,
args: *mut UDF_ARGS,
is_null: *mut c_uchar,
error: *mut c_uchar,
) -> f64 {
0.0
}Ограничения
-
Не получится заставить работать в Managed Database - ни один Cloud Provider не разрешит загружать пользовательскую shared library
-
для работы со Statment Based Replication наши UDF должны быть на 100% детерминированными и установленными на всех репликах. Из хороших новостей - SBR используется не часто, а UDF не требуют дополнительной сопроводительной работы - после первоначальной настройки, при последующих рестартах UDF функции будут автоматически загружены из библиотеки.
-
Ограниченый набор типов входящих аргументов и возвращаемых значений
-
segfault в UDF так же уронит и весь MySQL
Выводы
API довольно краток и создать UDF не сложно. UDF может стать палочкой-выручалочкой, когда других выходов расширить стандартный набор функций в MySQL не осталось. А может стать проклятием при заезде в облако. Можно надеяться, что в MySQL, как и в PostgreSQL, завезут поддержку интерпретируемых языков - Python или ECMAScript.
Ссылки
Автор: Nikolay Antonov
