Базы данных |
БГУИР, ПОИТ |
|
|
6.5.8.Анализ ошибок в подзапросах, оптимизация подзапросов и преобразование подзапросов в запросы с JOIN
Анализ ошибок в подзапросах
Ошибка 1: неподдерживаемый синтаксис подзапросов.
ERROR 1235 (ER_NOT_SUPPORTED_YET)
Как правило, возникает в случае, если в подзапросе реализовано что-то, что (пока?) MySQL не умеет выполнять, например:
SELECT * FROM `t1` WHERE `s1`
IN (SELECT `s2` FROM `t2` ORDER BY `s1` LIMIT 1)
Ошибка 2: неверное количество колонок.
ERROR 1241 (ER_OPERAND_COL)
LIMIT в контексте IN не поддерживается.
Как правило, возникает в случае, если подзапрос возвращает набор данных (колонок) там, где ожидался возврат скалярного значения:
SELECT (SELECT `c1`, `c2` FROM `t2`) FROM `t1`
Так вообще писать не надо!
Ошибка 3: неверное количество рядов.
ERROR 1242 (ER_SUBSELECT_NO_1_ROW)
Как правило, возникает в случае, если подзапрос возвращает набор данных (рядов) там, где ожидался возврат одного ряда:
SELECT * FROM `t1` WHERE `c1` = (SELECT `c1` FROM `t2`)
Такой запрос должен выглядеть так:
SELECT * FROM `t1` WHERE `c1` =
ANY (SELECT `c1` FROM `t2`)
Ошибка 4: неверная работа с таблицами.
Error 1093 (ER_UPDATE_TABLE_USED)
Как правило, возникает в случае, если внешний запрос и подзапрос работают с одной и той же таблицей причём так, что выполнение операции приводит к конфликту или «вечному циклу»:
UPDATE `t1` SET `c1` = (SELECT MAX(`c1`) FROM `t1`)
Операция обновления влияет на результат операции чтения, которая используется для определения значения операции обновления.
Стр: 218/248
Базы данных |
БГУИР, ПОИТ |
|
|
Оптимизация подзапросов
Указывайте сортировку результатов подзапроса:
SELECT * FROM `t1` WHERE `t1`.`c1` IN (SELECT `c1` FROM `t2` ORDER BY `c1`)
Устраняйте дублирование результатов подзапроса:
SELECT * FROM `t1` WHERE `t1`.`c1` IN (SELECT DISTINCT `c1` FROM `t2`)
Сокращайте до минимума количество рядов в подзапросе:
SELECT * FROM `t1` WHERE EXISTS (SELECT * FROM `t2` LIMIT 1)
В некоторых случаях использование подзапросов оказывается более выгодным, чем использование JOIN. Например, такой запрос
SELECT DISTINCT `c1` FROM `t1` WHERE `t1`.`c1`
IN (SELECT `c1` FROM `t2`)
будет работать быстрее, чем такой:
SELECT DISTINCT `t1`.`c1` FROM `t1`, `t2`
WHERE `t1`.`c1` = `t2`.`c1`
Помещайте условия ВНУТРЬ подзапросов. Например, такой подзапрос будет работать быстрее
SELECT * FROM `t1` WHERE `c1` IN (SELECT `c1` FROM `t1`
UNION ALL SELECT `c1` FROM `t2`)
чем такой:
SELECT * FROM `t1` WHERE `c1` IN (SELECT `c1` FROM `t1`)
OR `c1` IN (SELECT `c1` FROM `t2`)
Используйте подзапрос, возвращающий ряд, вместо взаимосвязанных запросов и подзапросов. Например, такой запрос будет работать быстрее
SELECT * FROM `t1` WHERE (`c1`, `c2`)
IN (SELECT `c1`, `c2` FROM `t2`)
чем такой:
SELECT * FROM `t1` WHERE EXISTS (SELECT * FROM `t2` WHERE
`t2`.`c1`=`t1`.`c1` AND `t2`.`c2`=`t1`.`c2`)
На этом множество идей по оптимизации подзапросов не заканчивается. См. подробности здесь: http://dev.mysql.com/doc/refman/5.5/en/optimizing-subqueries.html
Важно помнить, что эти (и другие) советы по оптимизации даны «для общего случая», и в вашей конкретной ситуации могут даже снизить производительность. Потому – проводите исследование на реальных данных в реальной базе данных.
Стр: 219/248
Базы данных |
БГУИР, ПОИТ |
|
|
Преобразование подзапросов в запросы с JOIN
Ранее уже было сказано, что иногда подзапросы эффективнее запросов с JOIN. Но иногда – наоборот, запросы с JOIN оказываются более выгодными по тем или иным критериям.
Рассмотрим пару случаев преобразования подзапросов в запросы с JOIN… Самый частый случай – замена IN и подзапроса, т.е. вместо
SELECT * FROM `t1` WHERE `id` IN (SELECT `id` FROM `t2`)
можно написать:
SELECT DISTINCT `t1`.* FROM `t1`, `t2`
WHERE `t1`.`id`=`t2`.`id`
или
SELECT DISTINCT `t1`.* FROM `t1` JOIN `t2` USING(`id`)
Ещё один случай – использование внешних объединений вместо IN или EXISTS. Например эти два запроса
SELECT * FROM `t1` WHERE `id` NOT IN (SELECT `id` FROM `t2`)
SELECT * FROM `t1` WHERE NOT EXISTS
(SELECT `id` FROM `t2` WHERE `t1`.`id`=`t2`.`id`)
можно записать так:
SELECT `t1`.* FROM `t1` LEFT JOIN `t2`
ON `t1`.`id`=`t2`.`id` WHERE `t2`.`id` IS NULL
6.5.9. Промежуточный итог
Мы рассмотрели большую часть материала, связанного с управлением данными.
В следующих разделах многие фрагменты синтаксиса операторов будут эквивалентны тому, что было рассмотрено в разделах «Оператор SELECT» и «Подзапросы», потому мы просто будем ссылаться на уже изученный материал.
Стр: 220/248