Работа с несколькими базами данных 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)

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

1. Отсутствие зависимостей: В СУБД уже присутствует среда выполнения, что облегчает выполнение хранимых процедур.
2. Простота обновления представлений: Обновление представлений не представляет сложности и сводится к выполнению 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) При выполнении запросов с условием на конкретном арендаторе

ELECT * 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.