Содержание
Пример запроса:
sqlSELECT * 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 внутри другого оператора, который синхронизируется с основным запросом (то есть подзапрос выполняется для каждой строки внешнего запроса).
Переделаем предыдущий запрос под коррелированный:
sqlSELECT * FROM `t1` WHERE `id` IN( SELECT `t1` FROM `t2` WHERE `status`=0 AND t1.count > 10 ) LIMIT 1
Теперь подзапрос ссылается на поле таблицы из внешнего запроса, значит, он коррелированный и результаты его выполнения зависят от данных во внешнем запросе.
Имеется всего 2 таблицы (с индексами):
Используется 4 способа получения данных из БД:
sqlSELECT * 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");
sql$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);
Самое интересное было в ходе реализации этого эксперимента ...
GROUP_CONCAT возвращает строковое значение, и использование результатов выполнения этого оператора в операции сравнения с числовым значением, без перекомпиляции запроса, приведет к преобразованию строкового типа к числовому. В этом запросе, ошибочно полагать что @$listId это массив данных, это просто строка:
sqlSELECT @listId:=GROUP_CONCAT(DISTINCT t1) FROM `t2` WHERE `status`=0; SELECT * FROM `t1` WHERE `id` IN(@listId);
Здесь @$listId это '1,2,3,4', а теперь подставим в запрос:
sqlSELECT * FROM `t1` WHERE id IN('1,2,3,4');
Как видно, данные в @$listId это строка, а не массив как хотелось бы предполагать :)
Кстати, чтобы данные полностью вмещались в строку, надо использовать:
sqlSET group_concat_max_len = CAST('-1' AS UNSIGNED)