Сделано разработчиками и не разработчиками
20 марта я проведу онлайн-сессию, вдохновленную этой статьей. На сессии будут представлены советы по продуктивности и производительности SQL, а также будут рассмотрены типичные ошибки в SQL.
Для получения дополнительной информации и регистрации перейдите в Следующие шаги SQL: оптимизация
Первоначально опубликовано на https://hakibenita.com 21 ноября 2019 г.
Прочтите эту статью в блоге с правильным выделением синтаксиса.
Большинство языков программирования предназначены для профессиональных разработчиков со знанием алгоритмов и структур данных. В SQL все по-другому.
SQL используют аналитики, специалисты по обработке данных, менеджеры по продуктам, дизайнеры и многие другие. Эти профессионалы имеют доступ к базам данных, но не всегда обладают интуицией и пониманием, чтобы писать эффективные запросы.
Стремясь помочь моей команде лучше писать SQL, я просмотрел отчеты, написанные не разработчиками, и обзоры кода, и собрал типичные ошибки и упущенные возможности оптимизации в SQL.
Будьте осторожны при делении целых чисел
В PostgreSQL деление целого числа на целое дает целое число:
// DONT!
db=# (
SELECT tax / price AS tax_ratio
FROM sale
);
tax_ratio
----------
0
Чтобы получить ожидаемый результат деления, вам нужно привести одно из значений к float:
// DO db=# ( SELECT tax / price::float AS tax_ratio FROM sale ); tax_ratio ---------- 0.17
Неспособность распознать эту ловушку может привести к ужасно неверным результатам.
Остерегайтесь ошибок «деления на ноль»
Нулевое деление - это печально известная ошибка в производстве:
// DONT db=# SELECT 1 / 0 ERROR: division by zero
Деление на ноль - это логическая ошибка, и ее следует не просто «обойти», а исправить, чтобы у вас вообще не было делителя нуля. Однако бывают ситуации, когда возможен нулевой знаменатель. Один из простых способов защиты от ошибок деления нуля в таких случаях - сделать все выражение нулевым, установив знаменатель равным нулю, если он равен нулю:
// DO
db=# SELECT 1 / NULLIF(0, 0);
?column?
----------
-
Функция NULLIF возвращает null, если первый аргумент равен второму аргументу. В этом случае, если знаменатель равен нулю.
При делении любого числа на NULL результатом будет NULL. Чтобы задать какое-то значение, вы можете обернуть все выражение COALESCE и предоставить запасное значение:
// DO
db=# SELECT COALESCE(1 / NULLIF(0, 0), 1);
?column?
----------
1
Функция COALESCE очень полезна. Он принимает любое количество аргументов и возвращает первое значение, которое не является нулевым.
Знайте разницу между UNION и UNION ALL
Классический вопрос на собеседовании начального уровня для разработчиков и администраторов баз данных: «В чем разница между UNION и UNION ALL».
UNION ALL объединяют результаты одного или нескольких запросов. UNION делает то же самое, но также удаляет повторяющиеся строки:
// DONT
db=# (
SELECT created_by_id FROM sale
UNION
SELECT created_by_id FROM past_sale
);
QUERY PLAN
-------------------------------------------------------------------
Unique (cost=2654611.00..2723233.86 rows=13724572 width=4)
-> Sort (cost=2654611.00..2688922.43 rows=13724572 width=4)
Sort Key: sale.created_by_id
-> Append (cost=0.00..652261.30 rows=13724572 width=4)
-> Seq Scan on sale (cost=0.00..442374.57 rows=13570157 width=4)
-> Seq Scan on past_sale (cost=0.00..4018.15 rows=154415 width=4)
В плане выполнения видно, что после добавления двух запросов база данных отсортировала результаты и удалила повторяющиеся строки.
Если вам не нужно удалять повторяющиеся строки, лучше всего использовать UNION ALL:
// DO
db=# (
SELECT created_by_id FROM sale
UNION ALL
SELECT created_by_id FROM past_sale
);
QUERY PLAN
-------------------------------------------------------------------
Append (cost=0.00..515015.58 rows=13724572 width=4)
-> Seq Scan on sale (cost=0.00..442374.57 rows=13570157 width=4)
-> Seq Scan on past_sale (cost=0.00..4018.15 rows=154415 width=4)
План выполнения намного проще. Результаты добавляются, сортировка не требуется.
Будьте осторожны при подсчете столбцов, допускающих значение NULL
При использовании агрегатных функций, таких как COUNT, важно понимать, как они обрабатывают нулевые значения.
Например, возьмем следующую таблицу:
db=# \pset null NULL
Null display is "NULL".
db=# WITH tb AS (
SELECT 1 AS id
UNION ALL
SELECT null AS id
)
SELECT *
FROM tb;
id
------
1
NULL
Столбец id содержит нулевое значение. Подсчет столбца id:
// DONT
db=# WITH tb AS (
SELECT 1 AS id
UNION ALL
SELECT null AS id
)
SELECT COUNT(id)
FROM tb;
count
-------
1
В таблице две строки, но COUNT вернул 1. Это связано с тем, что нулевые значения игнорируются COUNT.
Для подсчета строк используйте COUNT(*):
// DO db=# WITH tb AS ( SELECT 1 AS id UNION ALL SELECT null AS id ) SELECT COUNT(*) FROM tb; count ------- 2
Эта функция также может быть полезной. Например, если поле с именем modified содержит null, если строка не была изменена, вы можете вычислить процент измененных строк следующим образом:
db=# ( SELECT COUNT(modified) / COUNT(*)::float AS modified_pct FROM sale ); modified_pct --------------- 0.98
Другие агрегатные функции, такие как SUM, игнорируют значения NULL. Чтобы продемонстрировать, SUM поле, которое содержит только нулевые значения:
db=# WITH tb AS ( SELECT null AS id UNION ALL SELECT null AS id ) SELECT SUM(id::int) FROM tb; sum ------- NULL
Все это задокументированное поведение, так что имейте в виду!
Помните о часовых поясах
Часовой пояс всегда является источником путаницы и подводных камней. PostgreSQL неплохо справляется с часовыми поясами, но на некоторые вещи все же нужно обращать внимание.
Распространенная ошибка, которую я вижу бесчисленное количество раз, - это усечение меток времени без указания часового пояса. Допустим, мы хотим узнать, сколько продаж было совершено каждый день:
// DONT SELECT created_at::date, COUNT(*) FROM sale GROUP BY 1
Без явной настройки часового пояса вы можете получить разные результаты в зависимости от часового пояса, установленного клиентским приложением:
db=# SELECT now()::date;
now
------------
2019-11-08
db=# SET TIME ZONE 'australia/perth';
SET
db=# SELECT now()::date;
now
------------
2019-11-09
Если вы не уверены, с каким часовым поясом работаете, возможно, вы делаете это неправильно.
При усечении метки времени сначала конвертируйте в нужный часовой пояс:
SELECT (timestamp at time zone 'asia/tel_aviv')::date, COUNT(*) FROM sale GROUP BY 1;
За установку часового пояса обычно отвечает клиентское приложение. Например, чтобы получить часовой пояс, используемый psql:
db=# SHOW timezone;
TimeZone
----------
Israel
(1 row)
db=# SELECT now();
now
-------------------------------
2019-11-09 11:41:45.233529+02
(1 row)
Чтобы установить часовой пояс в psql:
db=# SET timezone TO 'UTC';
SET
db=# SELECT now();
now
-------------------------------
2019-11-09 09:41:55.904474+00
(1 row)
Еще одна важная вещь, о которой следует помнить, - это то, что часовой пояс вашего сервера может отличаться от часового пояса вашего локального компьютера. поэтому, если вы запускаете запросы на своем локальном компьютере, они могут дать разные результаты в производственной среде. Чтобы избежать ошибок, всегда указывайте часовой пояс явно.
Часовые пояса в PostgreSQL: чтобы получить полный список имен часовых поясов в PostgreSQL, запросите view pg_timezone_names.
Избегайте преобразований в индексированных полях
Использование функций в индексированном поле может помешать базе данных использовать индекс в этом поле:
// DONT
db=# (
SELECT *
FROM sale
WHERE created at time ZONE 'asia/tel_aviv' > '2019-10-01'
);
QUERY PLAN
-------------------------------------------------------------------
Seq Scan on sale (cost=0.00..510225.35 rows=4523386 width=276)
Filter:timezone('asia/tel_aviv', created)>'2019-10-01 00:00:00'::timestamp without time zone
Поле created проиндексировано, но поскольку мы преобразовали его с помощью timezone, индекс не использовался.
Один из способов использования индекса в этом случае - это вместо этого применить преобразование с правой стороны:
// DO
db=# (
SELECT *
FROM sale
WHERE created > '2019-10-01' AT TIME ZONE 'asia/tel_aviv'
);
QUERY PLAN
-------------------------------------------------------------------
Index Scan using sale_created_ix on sale (cost=0.43..4.51 rows=1 width=276)
Index Cond: (created > '2019-10-01 00:00:00'::timestamp with time zone)
Другой распространенный вариант использования дат - фильтрация определенного периода:
// DONT
db=# (
SELECT *
FROM sale
WHERE created + INTERVAL '1 day' > '2019-10-01'
);
QUERY PLAN
--------------------------------------------------------------------
Seq Scan on sale (cost=0.00..510225.35 rows=4523386 width=276)
Filter: ((created + '1 day'::interval) > '2019-10-01 00:00:00+03'::timestamp with time zone)
Как и раньше, функция интервала в поле created не позволяла базе данных использовать индекс. Чтобы база данных использовала индекс, примените преобразование справа вместо поля:
// DO
db=# (
SELECT *
FROM sale
WHERE created > '2019-10-01'::date - INTERVAL '1 day'
);
QUERY PLAN
-------------------------------------------------------------------
Index Scan using sale_created_ix on sale (cost=0.43..4.51 rows=1 width=276)
Index Cond: (created > '2019-10-01 00:00:00'::timestamp without time zone)
Используйте BETWEEN только для инклюзивных диапазонов
Распространенная ошибка, которую я вижу очень часто, - это фильтрация диапазона дат с помощью BETWEEN:
// DONT SELECT * FROM sales WHERE created BETWEEN '2019-01-01' AND '2020-01-01';
Интуитивно вы можете подумать, что этот запрос получает все продажи в 2019 году, но на самом деле он получает все продажи, сделанные в 2019 и в первый день 2020 года. BETWEEN является включительным, поэтому запрос выше эквивалент этого запроса:
SELECT * FROM sales WHERE created >= '2019-01-01' AND created <= '2020-01-01';
Чтобы отфильтровать результаты в 2019 году, вы можете написать следующее:
SELECT * FROM sales WHERE created BETWEEN '2019-01-01' AND '2019-12-31';
Или еще лучше:
// DO SELECT * FROM sales WHERE created >= '2019-01-01' AND created < '2020-01-01';
Неправильное использование BETWEEN может привести к дублированию результатов, например, при двукратном подсчете продаж за два разных периода.
Добавить «ложные» предикаты
Одним из наиболее важных компонентов в реализации баз данных и обычно тем, который делает одну базу данных лучше другой, является оптимизатор запросов.
Оптимизатор запросов просматривает ваш SQL и генерирует план выполнения. План выполнения описывает, как база данных будет обращаться к данным, необходимым для удовлетворения запроса. Например, оптимизатор решает, использовать ли определенный индекс или нет, в каком порядке выполнять соединение, какую таблицу фильтровать первой и так далее.
Чтобы создать хороший план выполнения, оптимизатор использует метаданные и статистику по вашим данным. Например, если вы применяете фильтр к столбцу с уникальным ограничением, оптимизатор знает, что он может ожидать ровно одну строку для каждого значения. В этом случае можно сделать вывод, что имеет смысл использовать индекс, а не сканировать всю таблицу.
В некоторых случаях вы знаете свои данные, которых у оптимизатора нет или не может быть. Вы можете повысить производительность запроса, предоставив оптимизатору дополнительную информацию, используя то, что я называю «ложным предикатом».
Возьмем, к примеру, этот запрос:
// DONT
db=# (
SELECT *
FROM sale
WHERE modified < '2019-01-01 asia/tel_aviv'
);
QUERY PLAN
-------------------------------------------------------------------
Seq Scan on sale (cost=0.00..510225.35 rows=1357 width=276)
Filter: (modified < '2019-01-01 00:00:00+02'::timestamp with time zone)
Запрос выбирает продажи, которые были изменены до 2019 года. В этом поле нет индекса, поэтому оптимизатор генерирует план выполнения для сканирования всей таблицы.
Допустим, у вас есть еще одно поле в этой таблице, в котором указано время создания продажи. Поскольку невозможно изменить продажу до ее создания, добавление аналогичного условия в поле created не изменит результат запроса. Однако оптимизатор может использовать эту информацию для создания лучшего плана выполнения:
// DO
db=# (
SELECT *
FROM sale
WHERE modified < '2019-01-01 asia/tel_aviv'
AND created < '2019-01-01 asia/tel_aviv';
);
QUERY PLAN
--------------------------------------------------------------------
Index Scan using sale_created_ix on sale (cost=0.44..4.52 rows=1 width=276)
Index Cond: (created < '2019-01-01 00:00:00+02'::timestamp with time zone)
Filter: (modified < '2019-01-01 00:00:00+02'::timestamp with time zone)
После того, как мы добавили «Faux Predicate», оптимизатор решил использовать индекс в поле created, и запрос стал намного быстрее! Обратите внимание, что предыдущий предикат для поля modified все еще оценивается, но теперь он применяется к гораздо меньшему количеству строк.
«Ложный предикат» не должен изменять результат запроса. Его следует использовать только для предоставления оптимизатору дополнительной информации, которая может улучшить производительность запроса. Помните, что база данных должна оценивать все предикаты, поэтому добавление слишком большого числа может замедлить выполнение запроса.
Встроенный CTE *
До PostgreSQL 12 были материализованы общие табличные выражения (также известные как CTE). Это изменилось в PostgreSQL 12, где CTE больше не материализуются и обрабатываются как подзапросы.
В версиях до PostgreSQL 12 неправильное использование CTE может привести к увеличению использования памяти и снижению производительности:
// DONT
db=# WITH cte AS (
SELECT *
FROM sale
)
SELECT *
FROM cte
WHERE created_by_id = 1;
QUERY PLAN
--------------------------------------------------------------------
CTE Scan on cte (cost=442906.19..748632.12 rows=67939 width=1148)
Filter: (created_by_id = 1)
CTE cte
-> Seq Scan on sale (cost=0.00..442906.19 rows=1999999999 width=276)
(4 rows)
Общая стоимость выполнения плана кажется очень высокой. Это связано с тем, что база данных сначала материализовала результат общего табличного выражения и только затем применила предикат. База данных не смогла использовать индекс для поля, и запрос оказался не очень эффективным.
Для повышения производительности встроите CTE (или обновите до PostgreSQL 12 😉):
// DO
db=# (
SELECT *
FROM (
SELECT *
FROM sale
) AS inlined
WHERE created_by_id = 1
);
QUERY PLAN
--------------------------------------------------------------------
Index Scan using sale_created_by_ix on sale (cost=0.43..714.70 rows=277 width=276)
Index Cond: (created_by_id = 1)
Чтобы узнать больше о CTE в PostgreSQL и о том, как он влияет на план выполнения запроса, прочтите Будьте осторожны с CTE в PostgreSQL.
Получите только то, что вам нужно!
Базы данных действительно хороши в хранении и извлечении данных. Другое приложение, не очень. Если вы загружаете данные в Excel, SASS, R, Pandas или любой другой инструмент отчетности, лучше всего получать только то, что вам нужно.
Например, иногда вы хотите получить представление о данных и можете сделать это:
// DONT
db=# SELECT * FROM sale;
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on sale (cost=0.00..442374.57 rows=13570157 width=276)
Этот запрос получит всю таблицу и, скорее всего, вызовет ненужную нагрузку, если вам нужно всего несколько строк.
Некоторые клиентские приложения автоматически получают данные на страницах или ограничивают набор результатов, но на всякий случай лучше установить LIMIT самостоятельно:
// DO
db=# (
SELECT *
FROM sale
LIMIT 10
);
QUERY PLAN
--------------------------------------------------------------------
Limit (cost=0.00..0.33 rows=10 width=276)
-> Seq Scan on sale (cost=0.00..442374.57 rows=13570157 width=276)
Другой распространенный случай, когда ненужные данные извлекаются из базы данных, - это когда пользователь, менее знакомый с SQL, загружает данные в какой-либо другой инструмент, такой как Excel или Pandas, только для того, чтобы немедленно применить к ним какой-то фильтр или агрегирование. Обычно это можно решить с помощью некоторого обучения.
Позиция ссылочного столбца в GROUP BY и ORDER BY
Приятной особенностью PostgreSQL является то, что на столбцы в GROUP BY и ORDER BY можно ссылаться по их положению в предложении SELECT:
SELECT first_name || ' ' || last_name AS full_name, COUNT(*) as sales FROM sale GROUP BY 1 ORDER BY 2 DESC
Предложение GROUP BY ссылается на выражение в первой позиции предложения SELECT, full_name. Предложение ORDER BY ссылается на второе выражение, счетчик sales. Ссылаясь на позицию, мы избегаем повторения выражения. Помимо экономии нескольких кликов, если выражение изменится в будущем, мы сможем отредактировать его только в одном месте.
Я понимаю, что этот совет может быть спорным, потому что позиция столбца в предложении SELECT не имеет значения и сама может измениться при редактировании запроса. Однако я обнаружил, что это повышает производительность при написании специальных запросов.
Отформатируйте свой запрос
Читаемость имеет значение. Выберите тот стиль, который вам и вашей команде удобнее всего, и придерживайтесь его.
Когда я начинал много лет назад, я писал такие запросы:
SELECT col1, col2, COUNT(col3) FROM t1 JOIN t2 ON ta.pk = t2.fk WHERE col1 = col2 AND col3 > col4 GROUP BY col1, col2, HAVING COUNT(col3) > 1
Я начал так, потому что это примерно тот формат, который Oracle использовал в своей документации.
За эти годы я столкнулся с множеством разных стилей. Например:
SELECT col1, col2, COUNT(col3)
FROM t1
JOIN t2 ON ta.pk = t2.fk
WHERE col1 = col2
AND col3 > col4
GROUP BY col1, col2,
HAVING COUNT(col3) > 1
Я не могу придумать причину, по которой кто-то мог бы писать так, форматировать это вручную утомительно (но выглядит неплохо ...)
В настоящее время моя команда и я использую такой формат:
SELECT col1, col2, COUNT(col3) FROM t1 JOIN t2 ON ta.pk = t2.fk WHERE col1 = col2 AND col3 > col4 GROUP BY col1, col2 HAVING COUNT(col3) > 1
Он удобочитаемый, гибкий и, что самое главное, отлично работает с git diff, что упрощает проверку кода.
Заключение
Применение приведенных выше советов в повседневной работе помогает нам поддерживать работоспособность базы данных с минимальными потерями. Мы обнаружили, что обучение разработчиков и не разработчиков тому, как лучше писать SQL, может иметь большое значение.
Если у вас есть еще какие-то советы по SQL, которые я мог пропустить, дайте мне знать, и я буду рад добавить их сюда.
Первоначально опубликовано на https://hakibenita.com 21 ноября 2019 г.
