ukman Jul 31 2019 at 09:27

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

7 min

21K

High performance*PostgreSQL*SQL*Server optimization*Database Administration*

+12

Comments 12

puyol_dev2 Jul 31 2019 at 09:53

Я конечно понимаю, что это такая локальная success story, но вы пишете элементарные вещи. Например выборка из маленькой таблицы с условным отбором из большой вместо соединения большой таблицы с маленькой (where exists against inner join). Да еще и distinct, который убивает сервер на большой выборке. select distinct big table inner join small stable — торжество быдлкодинга на sql. Надеюсь индексы на колонках id были, а то был бы совсем ад кромешный

ukman Jul 31 2019 at 12:53

Буду рад, если вы посоветуете, что нам еще сделать, чтобы ускорить нашу БД.

puyol_dev2 Jul 31 2019 at 13:13

Накатал на коленке перевод неплохой заметки

habr.com/ru/post/461997

Можете посмотреть там

Naves Jul 31 2019 at 09:58

Хорошо, когда можно влезть в потроха приложения и переписать SQL-запросы, не у всех есть такая возможность.
Будет ли какая-то разница, если в подзапросах для exists использовать select top 1?

SELECT P.ID, P.FIRST_NAME, P.LAST_NAME
FROM PERSON
WHERE EXISTS(SELECT top 1 D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)

Интересно поведение для других СУБД.

unfilled Jul 31 2019 at 10:17

Если интересно про другие СУБД — отвечу про MS SQL Server. Для него разницы не будет — его оптимизатор знает конструкцию EXISTS и игнорирует список столбцов в SELECT. Можно написать, например WHERE EXISTS (SELECT 1/0 FROM ...) и это будет прекрасно работать.

dss_kalika Jul 31 2019 at 11:14

Мне кажется он спрашивал о немного другом (не о списке столбцов, а о поиске ближайшего/всех значений в подзапросе в EXISTS).
Для MS SQL Server разницы не будет, потому что он для EXISTS и так ищет только первую строку, после чего прекращает поиск.

Это легко проверить посмотрев планы запроса для вариантов

… WHERE EXISTS (SELECT TOP 1 1 FROM ...)
… WHERE EXISTS (SELECT 1 FROM ...)
… WHERE EXISTS (SELECT * FROM ...)

unfilled Jul 31 2019 at 11:47

всё, что написано в SELECT, игнорируется. Выразился не очень корректно, да.

Naves Jul 31 2019 at 12:26

Проверил в Firebird 3 и в, внезапно, InterBase 9.
Действительно, всё, что написано в SELECT, игнорируется. А я все как-то по привычке в таких случаях писал select top 1

eefadeev Jul 31 2019 at 17:22

Чтобы не связывать в каждом запросе все таблицы, мы продублировали ссылку на заявку в таблице Доставка

А вот это вы сделали зря…
Во-первых вы добавили лишнюю атрибутику туда, где она не нужна.
Во-вторых вы, вероятно, имеете ненулевые шансы через какое-то время наступить на грабли марки «у нас расходятся данные, потому что два возможных пути ведут не в одну точку»

Если уж вам помогало подобное решение следовало сделать, например, соответствующее материализованное представление.

ukman Jul 31 2019 at 17:36

У нас эта связка никогда не меняется- доставка не имеет шансов быть привязанной к другому счету-запросу-заявке, счет к другому запросу и запрос к другой заявке… Так что тут все оправдано.

DrRaznomazov Dec 14 2022 at 09:56

С интересом прочитал. Действительно в текущей ситуации покупать новый сервер и масштабироваться горизонтально трудно из-за санкций.

Решения, которые вы предлагаете могут иметь место (наверное). Но они не должны становиться обьектом знания 2х человек в команде. Если мы говорим про энтерпрайз, и в команду заходит новый человек. Есть риск что он ни в чем не разберется. Как решали проблему с документацией?

ukman Jan 5 2023 at 09:49

Я вышел из того проекта, про который писал статью, но уже поучаствовал в подобной работе еще в одном проекте. Все было очень похоже и с похожим результатом. До меня этот новый проект развивался 7 лет и никто системно не занимался оптимизацией запросов. Периодически оптимизировали явные косяки, после жалоб клиентов, но все это было наскоками. За 2 месяца я сумел снизить нагрузку на БД со среднесуточной в 30-40% (часовые пики до 70%) до среднесуточных 15-20% (часовые пики до 40%). Уже два года эта нагрузка держится на этом уровне без дополнительных усилий.
Поэтому, я думаю, что во многих проектах такую работу можно проводить нечасто, особенно после того как проект становится зрелым и активная разработка проекта превращается в поддержку. Хотя, в каждом проекте своя ситуация и свои особенности. Так что как с любыми "узко заточенными" специалистами решать проблему можно либо через out-source, если необходимость в таком специалисте возникает нечасто. Либо держать такого спеца в команде всеми силами, если его знания нужны постоянно и готовить ему дублера из молодых инженеров.