Как работает SQL-запрос и какие планы выполнения существуют?
Что проверяют
Знаете ли вы, что происходит между отправкой SQL из Java-кода и получением строк, и умеете ли читать план. Следом обычно дают медленный запрос и спрашивают, что вы будете искать в EXPLAIN.
Как отвечать
Путь запроса на примере PostgreSQL: парсер строит дерево, анализатор сверяет таблицы и колонки с каталогом, переписчик раскрывает представления, планировщик перебирает варианты и выбирает самый дешёвый по оценке стоимости, исполнитель выполняет план. Оценки строятся на статистике, которую собирает ANALYZE. Устаревшая статистика частая причина плохого плана.
Узлы плана, которые стоит назвать:
- Seq Scan: чтение всей таблицы. Нормален для маленьких таблиц и когда нужна заметная доля строк;
- Index Scan: поиск по индексу и переход в таблицу за каждой строкой;
- Index Only Scan: все нужные колонки есть в индексе, в таблицу ходить не надо;
- Bitmap Heap Scan: сначала по индексам собирается карта нужных страниц, потом они читаются по порядку;
- Nested Loop, Hash Join, Merge Join: способы соединения. Первый хорош на малом внешнем наборе с индексом на внутреннем, второй на больших неотсортированных наборах, третий на уже отсортированных.
EXPLAIN показывает план с оценками, EXPLAIN (ANALYZE, BUFFERS) реально выполняет запрос и выводит фактические строки, время и чтения страниц. Главное, что ищут: расхождение оценки и факта по строкам на порядок и больше.
Связь с Java: SQL от Hibernate смотрят через логгер org.hibernate.SQL или datasource-proxy. Драйвер PostgreSQL после пяти выполнений одного PreparedStatement (параметр prepareThreshold) переходит на серверную подготовку, и сервер может выбрать общий план, одинаковый для любых параметров. Отсюда жалобы «в консоли быстро, из приложения медленно».
Где спотыкаются
Считают, что индексный доступ всегда лучше полного скана. При выборке большой доли строк Seq Scan дешевле, и планировщик прав.
Запускают EXPLAIN ANALYZE на DELETE или UPDATE в проде, забыв, что запрос выполнится. Такое оборачивают в транзакцию с ROLLBACK.
Смотрят на итоговую стоимость и не сравнивают оценку rows с actual rows, а причина обычно там.