Оптимизация вложенных подзапросов

Делая подзапрос в запросе стало интересно: можно ли как-то оптимизировать по скорости исполнения данные операции?

Пример запроса:

SELECT * 
FROM `t1`
WHERE `id` IN(
  SELECT `t1` 
  FROM `t2` 
  WHERE `status`=0
) 
LIMIT 1

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

Далее читая форумы наткнулся на пост «СУБД сама оптимизирует запросы», но подтверждающей информации сходу не удалось найти, однако казалось очевидно что такую мелочь СУБД просто обязана делать.

Пытливый ум сгенерировал вопрос, на который я не смог не найти ответ: а оптимизирует ли MySQL запросы если для сравнения id каждой строки из таблицы основного запроса делается подзапрос?

Так и появился репозиторий https://github.com/Byurrer/subquery-opt-test с экспериментом, отвечающим на данный вопрос.

Терминология

Википедия дает следующее определение понятию корреляция:

Корреляция (от лат. correlatio «соотношение, взаимосвязь») - статистическая взаимосвязь двух или более случайных величин.

Изменение одной величины ведет к изменению другой

Коррелированный подзапрос это оператор SELECT внутри другого оператора, который синхронизируется с основным запросом (то есть подзапрос выполняется для каждой строки внешнего запроса).

Переделаем предыдущий запрос под коррелированный:

SELECT * 
FROM `t1`
WHERE `id` IN(
  SELECT `t1` 
  FROM `t2` 
  WHERE `status`=0 AND t1.count > 10
)
LIMIT 1

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

Данные эксперимента

Имеется всего 2 таблицы (с индексами):

• t1 содержит столбцы id и text (генерируемый текст, по возможности уникальный)
• t2 содержит столбцы id, t1 (id из t1), status (случайно генерируемое число [0, 2])

Данные для таблиц генерируются скриптом (gen-data.php).

Используется 4 способа получения данных из БД:

• запрос с подзапросом (самое простое)

SELECT * 
FROM `t1` 
WHERE `id` IN(
  SELECT `t1` 
  FROM `t2` 
  WHERE `status`=0
)
LIMIT 1

• формирование запроса с перебросом данных на php (подзапрос выполняется отдельно, его результат преобразуется в список и полученные данные вставляются в основной запрос)

$aInner = $db->query("SELECT  `t1` FROM `t2` WHERE status=0", null, true);
$aInner2 = [];
foreach($aInner as $value)
	$aInner2[] = $value["t1"];
$sInner = implode(",", $aInner2);
$aRes2 = $db->query("SELECT  * FROM t1 WHERE id IN($sInner) LIMIT 1");

• формирование запроса с перебросом данных на php, при этом список создается на стороне СУБД

$aInner3 = $db->query("SELECT  GROUP_CONCAT(`t1`) AS t1 FROM `t2` WHERE status=0", null, true);
$sInner3 = $aInner3[0]["t1"];
$aRes3 = $db->query("SELECT  * FROM t1 WHERE id IN($sInner3) LIMIT 1");

• генерация запроса без вложенного подзапроса на стороне СУБД

$db->query(
	"SET group_concat_max_len = CAST('-1' AS UNSIGNED);
	SET @s1 = CONCAT(
		\"SELECT  * FROM t1 WHERE id IN('\",
			REPLACE(
				(SELECT  GROUP_CONCAT(`t1`) AS t1 FROM `t2` WHERE status=0),
				\",\",
				\"','\"
			),
		\"') LIMIT 1\"
	);
	PREPARE stmt1 FROM @s1;
",
null, false
);
$aRes4 = $db->query("EXECUTE stmt1;", null, true);

Самое интересное было в ходе реализации этого эксперимента ...

Нюансы реализация

Типы данных

Первым делом надо четко понимать что в MySQL есть типы данных (en, ru), пожалуй, отсутствие этого понимания с моей стороны стало веской причиной, по которой этот эксперимент дошел до конца.

GROUP_CONCAT возвращает строковое значение, и использование результатов выполнения этого оператора в операции сравнения с числовым значением, без перекомпиляции запроса, приведет к преобразованию строкового типа к числовому. В этом запросе, ошибочно полагать что @$listId это массив данных, это просто строка:

SELECT @listId:=GROUP_CONCAT(DISTINCT t1) 
FROM `t2` 
WHERE `status`=0;

SELECT * 
FROM `t1` 
WHERE `id` IN(@listId);

Здесь @$listId это '1,2,3,4', а теперь подставим в запрос:

SELECT * 
FROM `t1` 
WHERE id IN('1,2,3,4');

Как видно, данные в @$listId это строка, а не массив как хотелось бы предполагать :)

Конкатенация

Как оказалось, выполнить конкатенацию строк при помощи @#GROUP_CONCAT намного быстрее нежели перегонять данные на php, собирать их в строку и отправлять обратно в запрос. По результатам эксперимента видно, что третий способ (с использованием @#GROUP_CONCAT ) быстрее нежели формирование строки на php (второй способ).

Кстати, чтобы данные полностью вмещались в строку, надо использовать:

SET group_concat_max_len = CAST('-1' AS UNSIGNED)

Генерация запроса

Можно люто извратиться и сделать предыдущий вариант запроса на стороне СУБД (4 способ), при этом результат будет правильным. Это было сделано в четвертом способе. Но это трэш, just for skill :)

Кэш?

В ходе эксперимента меня заклинило на том, что надо делать честные замеры скорости перед каждым запросом очищать кэш. Но как выяснилось это бессмысленная затея, потому что я использовал движок innodb, а в этом случае рекомендуется хранить такие таблицы в ОЗУ для более быстрого доступа. Для этого имеется пул буфферов, на который можно воздействовать в любую сторону, но … надо ли специально пытаться уменьшить скорость запросов, гоняя данные из ПЗУ и ОЗУ?

Вывод

Оказалось СУБД MySQL и MariaDB оптимизируют запросы, и если подзапрос не зависит от внешнего запроса, то он выполняется единожды. И сделать более быстрый запрос, чем может это сделать СУБД, мне не удалось, все 3 попытки оказались медленнее, и более того, это трэш :)