Запросы сразу к нескольким базам данных MySQL

Представьте, что вы создаёте SaaS-продукт (связанный с электронной торговлей), где каждый пользователь занимает свою собственную маленькую базу данных (MySQL). Можно гарантировать, что данные пользователя останутся исключительно его данными. И в то же время эта система использует разделение данных на совершенно новом уровне. Всё вроде гладко, но…

SaaS-продукт завоёвывает популярность, вызывая интерес новых пользователей. У вас их уже 100 000, и неплохо бы выяснить, как все эти пользователи используют ваше ПО. Сколько продуктов ими добавлено, сколько сделано заказов их пользователями и т.д.? Чтобы получить все эти цифры, придётся делать запросы ко всем базам данных всех пользователей. Как же быть?

Вот если бы была одна большая таблица, внутри которой находились бы таблицы всех пользователей! Достаточно прописать запросы к половине этих таблиц, и вот у вас уже готовы (благодаря JOIN, GROUP BY и т.д.) запросы сразу ко всем таблицам в этой одной гигантской таблице. «Но как же создать такую таблицу?» — спросите вы.

Предлагаю использовать инструментарий реляционной СУБД MySQL. Один из таких инструментов — это представления (VIEW), позволяющие по-новому взглянуть на основополагающие структуры данных. Для начала нужно создать представление с таблицами всех пользователей. Все эти таблицы нужно будет соединить друг с другом.

Именно ОБЪЕДИНЕНИЕ всех таблиц в одном VIEW мы и будем создавать. Проделывая всё это с каждой таблицей, мы добьёмся того, чтобы все данные баз данных были у нас под рукой. Как же будет выглядеть такое представление?

CREATE VIEW sample_table AS (
  SELECT "tenant_1" AS tenant, t.* FROM tenant_1.sample_table AS t
  UNION ALL
  SELECT "tenant_2" AS tenant, t.* FROM tenant_2.sample_table AS t
  ...  
  UNION ALL
  SELECT "tenant_N" AS tenant, t.* FROM tenant_N.sample_table AS t
)

В каждом представлении ко всем колонкам исходных таблиц добавляется ещё одна колонка арендатор (пользователь) с именем БД пользователя, из которой берётся запись. Это, конечно, очень удобно: вы легко можете узнать, чьи это данные. Но надо быть осторожным при написании запросов JOIN, ведь к условиям JOIN тоже необходимо добавлять эту колонку:

SELECT 
    pp.tenant, pp.name, ppg.num
FROM
    products__products AS pp
        LEFT JOIN
    products__product_gtins AS ppg ON (pp.id = ppg.product_id
        AND pp.tenant = ppg.tenant)

Теперь остаётся создать такое представление для всех таблиц в базе данных. Для этой задачи сгодится любой язык программирования, но главное — надо использовать хранимые процедуры. Причины:

нет зависимостей (в СУБД уже есть среда выполнения);
обновлять представления не сложнее, чем запускать SQL-команду с любимого клиента.

Последовательность действий проста: сначала создаём хранимую процедуру для представления одной таблицы, затем выполняем итеративный обход всех таблиц, создавая представление для каждой из них.

Хранимая процедура для создания представления одной таблицы

CREATE PROCEDURE `update_table_view`(IN tbl_name VARCHAR(100), IN db_pattern_re VARCHAR(100))
BEGIN
    DECLARE all_dbs_view LONGTEXT;
    DECLARE all_dbs_done INT DEFAULT 0;
    DECLARE all_dbs_indx INT DEFAULT 0;
    DECLARE cur_tenant_db VARCHAR(100);

    -- (A)
    DECLARE all_dbs_cur CURSOR FOR SELECT `schema_name` FROM information_schema.schemata WHERE `schema_name` REGEXP db_pattern_re;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET all_dbs_done = 1;

    -- (B)
    SET all_dbs_view = CONCAT("CREATE VIEW ", tbl_name, " AS ");

    SET all_dbs_done = 0;
    SET all_dbs_indx = 0;

    -- (C)
    OPEN all_dbs_cur;
    all_dbs_loop: LOOP
        FETCH all_dbs_cur INTO cur_tenant_db;
        IF all_dbs_done = 1 THEN LEAVE all_dbs_loop; END IF;

        -- (D)
        IF all_dbs_indx > 0 THEN SET all_dbs_view = CONCAT(all_dbs_view, " UNION ALL "); END IF;
        SET all_dbs_view = CONCAT(all_dbs_view, "SELECT \"", cur_tenant_db, "\" AS tenant, t_", all_dbs_indx, ".* FROM `", cur_tenant_db, "`.`", tbl_name, "` AS t_", all_dbs_indx);
        SET all_dbs_indx = all_dbs_indx + 1;

    END LOOP all_dbs_loop;
    CLOSE all_dbs_cur;

    -- (E)
    SET @drop_view = CONCAT("DROP VIEW IF EXISTS ", tbl_name);
    PREPARE drop_view_stm FROM @drop_view; EXECUTE drop_view_stm; DEALLOCATE PREPARE drop_view_stm;

    -- (F)
    SET @all_dbs_view_v = all_dbs_view;
    PREPARE all_dbs_view_stm FROM @all_dbs_view_v; EXECUTE all_dbs_view_stm; DEALLOCATE PREPARE all_dbs_view_stm;
END

Хранимая процедура принимает два аргумента:

tbl_name: имя таблицы, для которой создаётся VIEW;
db_pattern_re: регулярное выражение для исключающей фильтрации баз данных, которые будут включены в VIEW.

A) Сначала надо объявить курсор для итеративного обхода всех БД, включённых в представление. Чтобы отфильтровать только те БД, которые нам нужны, используем регулярное выражение db_pattern_re. Обработчик CONTINUE HANDLER позаботится о том, чтобы цикл остановился после итеративного обхода всех найденных БД.

B) Инициализируем переменную all_dbs_view, содержащую нашу полную инструкцию CREATE VIEW. Переменная может оказаться довольно длинной (LONGTEXT), в зависимости от числа прошедших фильтрацию БД.

C) Теперь открываем курсор и начинаем итеративный обход каждой прошедшей фильтрацию БД. Оператор IF выполнит проверку на наличие переменной all_dbs_done на каждом этапе. При запуске обработчика CONTINUE HANDLER переменная будет иметь значение 1.

D) Первая итерация не требует ставить в начало UNION ALL, а вот все последующие будут ставить. Следующая строчка конкатенирует, то есть добавляет текущий оператор SELECT, содержащий все записи из таблицы БД конкретного пользователя:

SELECT "tenants_DB" AS tenant, t.* FROM tenants_DB.some_table AS t

E) Прежде чем создавать VIEW, необходимо убедиться, что предыдущее (если оно существовало) удалено.

F) И, наконец, запускаем саму инструкцию, создающую VIEW.

Теперь можно выполнить процедуру создания представления VIEW одной таблицы:

CALL update_table_view("sample_table", "tenant_[0-9]+");

Хранимая процедура для итеративного обхода всех таблиц

CREATE PROCEDURE `update_all_views`(IN db_first VARCHAR(100), IN db_pattern_re VARCHAR(100))
BEGIN
    DECLARE all_tbls_done INT DEFAULT 0;
    DECLARE cur_tbl VARCHAR(100);

    -- A
    DECLARE all_tbls_cur CURSOR FOR SELECT `table_name` FROM information_schema.tables WHERE table_schema = db_first;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET all_tbls_done = 1;

    SET all_tbls_done = 0;
    OPEN all_tbls_cur;
    -- B
    all_tbls_loop: LOOP
        FETCH all_tbls_cur INTO cur_tbl;
        IF all_tbls_done = 1 THEN LEAVE all_tbls_loop; END IF;

        -- C
        CALL update_table_view(cur_tbl, db_pattern_re);

    END LOOP all_tbls_loop;
    CLOSE all_tbls_cur;
END

Хранимая процедура принимает два аргумента:

db_first: имя базы данных, которое будет использовано для вывода списка всех таблиц. Это может быть любая ваша БД;
db_pattern_re: регулярное выражение для исключающей фильтрации баз данных, которые будут включены в VIEW.

A) Объявляем курсор для итеративного обхода всех таблиц, найденных в базе данных db_first. Обработчик CONTINUE HANDLER позаботится о том, чтобы цикл остановился после итеративного обхода всех обнаруженных таблиц.

B) Открываем курсор и начинаем итеративный обход каждой найденной таблицы. Оператор IF выполнит проверку на наличие переменной all_tbls_done на каждом этапе. При запуске обработчика CONTINUE HANDLER переменная будет иметь значение 1.

C) Получив значение текущей таблицы, хранимой в переменной cur_tbl, можно выполнить хранимую процедуру, которая создаст VIEW специально для cur_tbl.

При выполнении хранимой процедуры:

CALL update_all_views("tenant_1", "tenant_[0-9]+");

получаем:

список всех представлений со всеми таблицами из базы данных tenant_1;
ОБЪЕДИНЕНИЕ одинаковых таблиц всех арендаторов, чем и является, по сути, каждое представление.

Вот и всё!

Доводы «за»

нет зависимостей (всё выполняется в сервере MySQL);
может использоваться из любого клиента MySQL;
привычная модель данных (по сути, одна и та же);
легко устанавливать, обновлять, использовать;
можно выполнять произвольный запрос.

Доводы «против»

при объединении таблиц необходимо добавлять дополнительное условие;
запросы не отличаются большой скоростью (зависит от количества арендаторов), но довольно быстрые;
масштабирование ограничено: до 100 000 арендаторов.

Заключение

В созданном нами VIEW сейчас приблизительно 340 таблиц и 250 арендаторов. Никаких тестов на его масштабирование пока не проводилось. Отклик на запросы довольно быстрый, а вообще было интересно: мы много чего узнали, выполняя эти простые запросы.

Вряд ли стоит использовать это представление на большом количестве арендаторов — тысячах и тем более миллионах — а вот на небольшой выборке (скажем, с 34-го арендатора по 76-й) вполне можно:

CALL update_all_views("tenant_34", "tenant_(3[4-9]|[4-6][0-9]|7[0-6])$");

А вообще это очень удобный инструмент для проведения анализа ваших арендаторов с целью получения ответов на конкретные вопросы. Нужно всего лишь выполнить несколько простых запросов и немного поэкспериментировать.

Внимание

Как и в любом программном решении, здесь есть подводные камни, о которых мы ещё не упоминали.

1) При выполнении команды-SQL для создания VIEW сервер MySQL может выбрасывать исключение вроде такого:

Prepared statement needs to be re-prepared

Наше решение было таким:

SET GLOBAL table_definition_cache = 2800;

У вас это значение (2800) может отличаться в зависимости от количества арендаторов.

2) При выполнении запросов с условием на конкретном арендаторе

SELECT * FROM sample_table WHERE tenant = "tenant_1"

можете получить исключения (в зависимости от того, как вы создавали базу данных):

Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_unicode_ci,IMPLICIT) for operation ‘=’

Решение — смена кодировки (символов) имени базы данных при создании представления. Добавляем вызов функции CONVERT в операторе SELECT:

SELECT CONVERT("tenants_DB" USING utf8mb4) AS tenant, t.* FROM tenants_DB.some_table AS t

Вместо utf8mb4 используйте значение, заданное вами для кодирования при создании базы данных.

3) Время ожидания подключения тоже может стать проблемой. На создание всех этих представлений может потребоваться время. В нашем случае на это ушло 5 минут. Так что позаботьтесь о том, чтобы время ожидания подключения в вашем клиенте было достаточным.

4) Лучше готовить VIEW и выполнять запросы на сервере реплики, чтобы не перегружать рабочие серверы. Иначе запросы очень быстро могут стать тяжёлыми.

5) Для тестирования этого метода использовался сервер MySQL Community Server 5.7.20.

Перевод статьи Valentin Iljaž: Querying multiple MySQL databases at once

Поделиться статьей:

Вернуться к статьям