Прежде чем перейти к статье, хочу вам представить, экономическую онлайн игру Brave Knights, в которой вы можете играть и зарабатывать. Регистируйтесь, играйте и зарабатывайте!
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.
Ссылки
Официальная документация