Введение

Вы когда-нибудь оказывались в ситуации, когда вам требовался большой объем фиктивных данных для тестирования, но вы не хотели тратить часы на написание скриптов или вставку записей вручную? Или, возможно, вам было интересно, как использовать новые функции MySQL 8.0 для оптимизации задач, связанных с базой данных? Что ж, вас ждет угощение! В этом посте мы рассмотрим, как использовать общие табличные выражения (CTE), чтобы легко генерировать и вставлять огромные объемы фиктивных данных в базу данных MySQL.

Представьте, что вам нужно заполнить таблицу миллионами хеш-значений для нагрузочного тестирования или сравнительного анализа производительности. Звучит как кошмар, правда? Уже нет! С появлением CTE в MySQL 8.0 добиться этого можно за считанные секунды. Давайте углубимся в то, как это работает и как вы можете использовать эту мощную функцию для упрощения генерации данных.

ТЛ; ДР

Общие табличные выражения (CTE), новая функция, добавленная в MySQL 8.0, может использоваться для легкого ввода большого количества простых фиктивных данных. Например, если вы хотите ввести 1 миллион фиктивных данных в таблицу под названием хэши, в которой хранятся хэш-значения, вы можете добиться этого, выполнив следующие шаги:

Определения таблиц

Сначала создайте таблицу:

CREATE TABLE hashes (
  id INT PRIMARY KEY AUTO_INCREMENT,
  hash CHAR(64)
);

Выполнение запроса

Установите переменную сеанса, чтобы разрешить более высокую глубину рекурсии:

SET SESSION cte_max_recursion_depth = 1000000;

Затем выполните CTE, чтобы вставить 1 миллион строк:

INSERT INTO hashes(hash)
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n   1 FROM cte WHERE n 



Этот метод использует рекурсивное общее табличное выражение для генерации фиктивных данных.


  
  
  Понимание CTE


Общее табличное выражение (CTE) — это именованный временный набор результатов, на который можно ссылаться в одном операторе несколько раз. CTE особенно полезны для упрощения сложных запросов и улучшения читаемости.


  
  
  Синтаксис



  
  
  Установка глубины рекурсии




SET SESSION cte_max_recursion_depth = 1000000;




Системная переменная cte_max_recursion_eep устанавливает верхний предел рекурсии. По умолчанию это 1000, поэтому, чтобы рекурсировать больше, вам нужно его настроить. Здесь мы установили его на 1 миллион.


  
  
  Запрос CTE




INSERT INTO hashes(hash)
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n   1 FROM cte WHERE n 



Давайте разберем этот запрос:

• WITH RECURSIVE cte (n): начинается определение CTE. cte — это имя временного набора результатов, а n — столбец.

• SELECT 1: это нерекурсивная часть CTE, служащая отправной точкой (начальное значение).

• UNION ALL SELECT n 1 FROM cte WHERE n

• SELECT SHA2(n, 256) FROM cte: эта последняя часть запроса выбирает хэш SHA-256 каждого значения n, генерируя фиктивные данные для вставки.

Как это работает

CTE рекурсивно генерирует числа от 1 до 1 000 000. Для каждого числа он вычисляет хэш SHA-256 и вставляет его в хеш-таблицу. Этот подход эффективен и использует рекурсивные возможности MySQL для беспрепятственной обработки больших объемов данных.

Скорость обработки

Среда проверки

Чтобы понять влияние этой функции, я использовал рабочее пространство Gitpod Enterprise, используя мощную и эфемерную среду, чтобы избежать хлопот с настройкой и установкой. Вот краткий обзор установки:

• Машина: Gitpod Enterprise XXLarge workspace
• ОС: Ubuntu 22.04.4 LTS (Jammy Jellyfish)
• Контейнеризация: Docker версии 26.0.1
• Версия MySQL: Официальный образ Docker MySQL 8.0

Полученные результаты

Для 1 миллиона строк время выполнения запроса составляет примерно 4,46 секунды:

mysql> INSERT INTO hashes(hash)
    -> WITH RECURSIVE cte (n) AS
    -> (
    ->   SELECT 1
    ->   UNION ALL
    ->   SELECT n   1 FROM cte WHERE n  )
    -> SELECT SHA2(n, 256) FROM cte;
Query OK, 1000000 rows affected (4.43 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

Показатели эффективности

Заключение

Использование CTE в MySQL 8.0 меняет правила игры для быстрого создания больших объемов фиктивных данных. Это особенно удобно для нагрузочного тестирования и тестирования производительности. С помощью всего лишь нескольких строк кода вы можете легко заполнить таблицы, а затем вернуться к другим важным частям вашего проекта.

Не стесняйтесь экспериментировать с различными стратегиями генерации данных и глубиной рекурсии, чтобы увидеть, что лучше всего подходит для вас. Чтобы получить дополнительные советы и информацию о безопасности и анализе журналов, подписывайтесь на меня в Твиттере @Siddhant_K_code и будьте в курсе последних и подробных технических материалов, подобных этому. Приятного кодирования!