Уровень необходимых знаний для понимания стати: общее представление о базах данных и SQL, небольшой практический опыт работы с СУБД.
Вероятно, самое важное, чему только можно научиться для написания эффективных SQL-запросов, – это индексация. Однако на втором месте, с очень небольшим отставанием, идет знание о том, что множество SQL-клиентов требуют от базы данных выполнения массы "ненужной, но обязательной работы".
Повторяйте за мной:
Ненужная, но обязательная работа
Что же такое "ненужная, но обязательная работа"? Как подсказывает нам, Капитан Очевидность, она:
Ненужная
Пусть нашему клиентскому приложению необходимы следующие данные:
Ничего необычного. Мы работаем с базой данных фильмов (например, базой данных Sakila) и хотим отображать пользователям название и рейтинг всех фильмов.
Нужный нам результат может дать следующий запрос:
SELECT title, rating
FROM film
Однако наше приложение (или наш ORM), вместо этого, выполняет вот такой запрос:
SELECT *
FROM film
Что же мы получаем в результате? Угадайте. Мы получаем массу бесполезной информации:
Справа тут даже виднеется какой-то сложный JSON, загружаемый:
c диска
в кэш
по проводам
в память клиента
и, наконец, выбрасывается [за ненадобностью]
Да, мы выбрасываем большую часть этой информации. Все выполненные для извлечения этой информации действия оказались совершенно бесполезными. Правда? Правда.
Обязательная
А теперь – самое плохое. Хотя оптимизаторы сейчас умеют немало, эти действия обязательны для базы данных. База данных никак не может узнать, что клиентскому приложению 95% этих данных совершенно не нужно. И это лишь простейший пример. Представьте себе соединение нескольких таблиц...
Ну и что, – скажете вы, – базы данных же быстрые? Позвольте просветить вас о некоторых вещах, о которых вы, наверное, не задумывались.
Конечно, время выполнения отдельного запроса ни на что особо не влияет. Ладно, он выполнился в полтора раза медленнее, но мы же это переживём, правда? Ради удобства? Иногда это так. Но если жертвовать производительностью ради удобства всегда, эти мелочи начнут накапливаться. Речь пойдёт уже не о производительности (скорости выполнения отдельных запросов), а о пропускной способности (времени отклика системы) и тогда-то начнутся нешуточные проблемы, решить которые не так уж просто. Тогда-то вы и утратите масштабируемость.
Взглянем на планы выполнения, в данном случае, СУБД Oracle:
При выполнении запроса SELECT * вместо SELECT title, rating используется в 8 раз больше памяти в базе данных. Ничего неожиданного, правда? Мы знали, что так и будет. Но все равно мы соглашаемся с этим для многих наших запросов, в которых все эти данные нам просто не нужны. Мы создаем для базы данных ненужную, но обязательную работу, которая все накапливается и накапливается. Мы используем в 8 раз больше памяти, чем нужно (множитель будет меняться, конечно).
А между тем, на всех остальных этапах (дисковый ввод/вывод, передача данных по сети, потребление памяти клиентом) проблемы точно те же, но я их пропущу, а взгляну, вместо этого, на...
Использование индексов
Большинство баз данных, на сегодня, уже оценили по достоинству концепцию покрывающих индексов (covering indexes). Покрывающий индекс – сам по себе не является каким-то особым видом индекса. Но он может оказаться "особым индексом" для конкретного запроса, или "случайно", или потому, что так было задумано.
Рассмотрим следующий запрос:
SELECT *
FROM actor
WHERE last_name LIKE 'A%'
Ничего неожиданного в плане его выполнения не видно. Это простой запрос. Просмотр диапазона по индексу, доступ к таблице – и готово:
-------------------------------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 |
| 1 | TABLE ACCESS BY INDEX ROWID| ACTOR | 8 |
|* 2 | INDEX RANGE SCAN | IDX_ACTOR_LAST_NAME | 8 |
-------------------------------------------------------------------
Хороший план, не правда ли? Ну, если нам правда было нужно вот это, то нет:
Очевидно, что мы зря расходуем память и т. п. Давайте рассмотрим вот такой запрос в качестве альтернативного варианта:
SELECT first_name, last_name
FROM actor
WHERE last_name LIKE 'A%'
Его план таков:
----------------------------------------------------
| Id | Operation | Name | Rows |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 8 |
|* 1 | INDEX RANGE SCAN| IDX_ACTOR_NAMES | 8 |
----------------------------------------------------
Нам удалось полностью исключить доступ к таблице, благодаря наличию удовлетворяющего все потребности нашего запроса индекса... покрывающего индекса. Важно ли это? Еще как! Это подход позволяет ускорить некоторые запросы на порядок (или замедлить их на порядок, когда индекс перестает быть покрывающим после каких-либо изменений).
Покрывающими индексами можно воспользоваться не всегда. За индексы приходится платить и добавлять слишком много их не следует. Но в данном случае, всё очевидно. Давайте оценим производительность:
SET SERVEROUTPUT ON
DECLARE
v_ts TIMESTAMP;
v_repeat CONSTANT NUMBER := 100000;
BEGIN
v_ts := SYSTIMESTAMP;
FOR i IN 1..v_repeat LOOP
FOR rec IN (
-- Наихудший вариант запроса: перерасход памяти ПЛЮС доступ к таблице
SELECT *
FROM actor
WHERE last_name LIKE 'A%'
) LOOP
NULL;
END LOOP;
END LOOP;
dbms_output.put_line('Оператор 1 : ' || (SYSTIMESTAMP - v_ts));
v_ts := SYSTIMESTAMP;
FOR i IN 1..v_repeat LOOP
FOR rec IN (
-- Улучшенный запрос, но все равно с доступом к таблице
SELECT /*+INDEX(actor(last_name))*/
first_name, last_name
FROM actor
WHERE last_name LIKE 'A%'
) LOOP
NULL;
END LOOP;
END LOOP;
dbms_output.put_line('Оператор 2 : ' || (SYSTIMESTAMP - v_ts));
v_ts := SYSTIMESTAMP;
FOR i IN 1..v_repeat LOOP
FOR rec IN (
-- Оптимальный запрос: покрывающий индекс
SELECT /*+INDEX(actor(last_name, first_name))*/
first_name, last_name
FROM actor
WHERE last_name LIKE 'A%'
) LOOP
NULL;
END LOOP;
END LOOP;
dbms_output.put_line('Оператор 3 : ' || (SYSTIMESTAMP - v_ts));
END;
/
В результате получаем:
Оператор 1 : +000000000 00:00:02.479000000
Оператор 2 : +000000000 00:00:02.261000000
Оператор 3 : +000000000 00:00:01.857000000
Обратите внимание, что в таблице actor всего 4 столбца, так что разница в производительности между операторами 1 и 2 не так уж велика, но все же значительна. Замечу также, что я воспользовался подсказками оптимизатора Oracle, чтобы оптимизатор выбирал тот или иной конкретный индекс для запроса. Оператор 3 – безусловный победитель нашего забега. Его производительность намного лучше, а ведь речь идет об исключительно простом запросе.
Опять же, когда мы пишем SELECT *, то создаем для базы данных ненужную, но обязательную работу, которую она не может оптимизировать. Она не выберет покрывающий индекс, потому что у него немного выше накладные расходы, чем у выбранного ей индекса LAST_NAME, и, помимо прочего, ей приходится все равно обращаться к таблице для извлечения никому не нужного столбца LAST_UPDATE, например.
Но чем глубже мы анализируем SELECT *, тем хуже оказываются дела. Поговорим про...
SQL-преобразования
Оптимизаторы демонстрируют столь хорошие результаты потому, что преобразовывают SQL-запросы (в своем недавнем выступлении на конференции Voxxed Days в Цюрихе я рассказывал, как это работает). Например, существует чрезвычайно мощное преобразование "исключение JOIN". Рассмотрим следующее вспомогательное представление, которое нам пришлось создать, чтобы не соединять все эти таблицы каждый раз вручную:
CREATE VIEW v_customer AS
SELECT
c.first_name, c.last_name,
a.address, ci.city, co.country
FROM customer c
JOIN address a USING (address_id)
JOIN city ci USING (city_id)
JOIN country co USING (country_id)
Это представление просто выполняет все "...-к-одному" соединения между таблицей покупателей CUSTOMER и различными таблицами для частей их адреса. Спасибо тебе, нормализация.
Представьте, что, поработав немного с этим представлением, мы к нему привыкли и забыли про лежащие в его основе таблицы. И теперь мы выполняем следующий запрос:
SELECT *
FROM v_customer
Мы получаем в его результате весьма внушительный план:
Ну, конечно. База данных выполняет все эти соединения и полные просмотры таблиц, поскольку именно это мы и приказали ей сделать – извлечь все эти данные.
Теперь, опять же, представьте себе, что на самом деле всё, что нам было нужно – вот это:
Что, серьёзно, да? Теперь вы начинаете понимать, о чём я говорю. Но представьте, что мы кое-чему научились на прошлых ошибках, и выполняем вот такой, более оптимальный запрос:
Разительные изменения к лучшему в плане выполнения. Соединения были исключены, поскольку оптимизатор теперь может убедиться в их бесполезности, а если он способен в этом убедиться (а вы не сделали эту работу обязательной путем выбора *), то может и просто не делать всю эту работу. Почему это так в данном случае?
Внешний ключ CUSTOMER.ADDRESS_ID к первичному ключу ADDRESS.ADDRESS_ID гарантирует ровно одно значение последнего, а значит и то, что операция JOIN будет соединением типа "...-к-одному", которое не увеличивает и не уменьшает количество строк. А раз каких-то строк мы вообще не выбираем и не запрашиваем, то никакого смысла вообще их загружать нет. Удаление JOIN, вероятно, вообще не повлияет на результат запроса.
Базы данных делают подобное постоянно. Можно выполнить следующий запрос на почти любой базе данных:
-- Oracle
SELECT CASE WHEN EXISTS (
SELECT 1 / 0 FROM dual
) THEN 1 ELSE 0 END
FROM dual
-- Более адекватные диалекты SQL, например, PostgreSQL
SELECT EXISTS (SELECT 1 / 0)
В данном случае вы могли бы ожидать генерации арифметического исключения, как при выполнении следующего запроса:
SELECT 1 / 0 FROM dual
Получилось:
ORA-01476: divisor is equal to zero
Но этого не происходит. Оптимизатор (или даже синтаксический анализатор) может убедиться, что никакие элементы списка выборки в предикате EXISTS (SELECT ..) не изменят результат запроса, так что необходимости выполнять его нет. Вот так!
Тем временем...
Одна из самых неприятных проблем ORM заключается в том, что в них так удобно писать запросы SELECT *. Собственно, например, в HQL / JPQL они вообще используются по умолчанию. Можно вообще опустить предложение SELECT, ведь мы же собираемся извлекать всю сущность целиком, правда?
Например:
FROM v_customer
Например, Влад Михалче (Vlad Mihalcea), эксперт и сторонник разработки при помощи библиотеки Hibernate, рекомендует использовать [уточненные] запросы практически всегда, когда вы уверены, что не хотите сохранить какие-либо изменения после извлечения.
ORM сильно облегчают решение задачи сохраняемости графов объектов. Примечание: Сохраняемости. Задачи собственно модификации графов объектов и сохранения изменений неразрывно связаны.
Но если делать этого вы не собираетесь, то зачем утруждать себя извлечением сущности? Почему не написать [уточненный] запрос? Давайте четко разберемся: с точки зрения производительности, написание запроса, специально подогнанного под ваш конкретный сценарий использования, заведомо лучше любого другого варианта. Возможно, вам все равно, поскольку ваш набор данных невелик и это не имеет значения. Отлично. Но когда, в конце концов, вам потребуется масштабируемость, то перепроектировать свои приложения под использование запросов вместо императивного обхода графа сущностей окажется весьма непросто. А у вас будет, чем заняться и без этого.
Подсчет строк для выяснения наличия чего-либо
Один из случаев самого ужасного разбазаривания ресурсов – выполнение запросов COUNT(*) просто, чтобы узнать, имеется ли что-то в базе данных. Например, нам нужно узнать, есть ли у данного пользователя вообще заказы. И мы выполняем запрос:
SELECT count(*)
FROM orders
WHERE user_id = :user_id
Элементарно. Если COUNT = 0, то заказов нет. В противном случае – есть.
Производительность окажется не такой уж плохой, ведь у нас, наверное, есть индекс по столбцу ORDERS.USER_ID. Но как вы думаете, какая будет производительность вышеприведенного запроса по сравнению со следующим вариантом:
-- Oracle
SELECT CASE WHEN EXISTS (
SELECT *
FROM orders
WHERE user_id = :user_id
) THEN 1 ELSE 0 END
FROM dual
-- Более адекватные диалекты SQL, например, PostgreSQL
SELECT EXISTS (
SELECT *
FROM orders
WHERE user_id = :user_id
)
Не нужно быть семи пядей во лбу, чтобы понять, что настоящий предикат существования прекратит поиск дополнительных строк сразу после обнаружения первой же. Так что если результат окажется "заказов нет", то скорость будет сравнимой. Если же, однако, результат будет "да, заказы есть", то в случае, когда не надо считать точное количество, ответ будет получен намного быстрее.
Ведь точное количество нас и не интересует. Тем не менее, мы приказали базе данных вычислить его (ненужная работа), а база данных не знает, что мы игнорируем все результаты больше 1 (обязательная работа).
Конечно, было бы намного хуже, если бы для достижения тех же результатов мы вызывали list.size() для поддерживаемой JPA коллекции.
Я уже писал об этом в своём блоге ранее, и проводил сравнительное тестирование обоих вариантов на р...
Заключение
Эта статья излагает очевидные вещи. Не заставляйте базу данных выполнять ненужную, но обязательную работу.
Она ненужная, поскольку, в соответствии с имеющимися требованиями, вы знаете, что некоторую конкретную часть работы выполнять не нужно. Тем не менее, вы говорите базе данных сделать её.
Она обязательная, поскольку база данных никак не может убедиться, что эта работа ненужная. Эта информация есть только у клиента и недоступна серверу. Так что базе данных приходится её выполнять.
Статья была посвящена SELECT *, в основном потому, что это такой удобный объект для рассмотрения. Но это касается не только баз данных. Это касается всех распределенных алгоритмов, в которых клиент указывает серверу выполнять ненужную, но обязательную работу. Сколько N+1 задач встречается в вашем среднестатистическом AngularJS-приложении, в которых UI организовывает цикл по полученному от сервиса результату A, вызывая сервис B несколько раз, вместо того, чтобы упаковать все обращения к B в единый вызов? Это очень часто встречающееся явление.
Решение всегда одно и то же. Чем больше информации вы предоставляете выполняющей ваши команды сущности, тем быстрее она (теоретически) эти команды выполняет. Пишите оптимальные запросы. Всегда. Вся ваша система будет благодарна вам за это.
Оригинал статьи
ПЕРЕЙДИТЕ В ПОЛНУЮ ВЕРСИЮ