Вопросы на собеседовании: Администратор БД

База разбирает и проверяет SQL, преобразует его во внутреннее дерево запроса, выбирает план выполнения и запускает этот план.

• Parsing проверяет синтаксис и разрешает ссылки на объекты, столбцы, типы и права.
• Rewriting может раскрывать views или применять правила СУБД до планирования.
• Оптимизатор сравнивает допустимые пути доступа и порядки joins по статистике и cost model.
• Executor запускает выбранные операторы и возвращает строки с учётом transaction visibility.

EXPLAIN показывает оценочный план оптимизатора, а EXPLAIN ANALYZE выполняет statement и добавляет фактические runtime-данные.

• Estimated rows и costs берутся из статистики и cost model СУБД.
• Actual rows, loops и timing показывают отличие выполнения от оценок.
• Поскольку ANALYZE запускает statement, write queries могут изменить данные, если их правильно не обернуть и не откатить.
• Опции PostgreSQL вроде BUFFERS показывают активность cache и I/O вместе со временем.

Эти пути доступа меняют startup cost, random access и массовое чтение страниц в зависимости от оценочной selectivity.

• Sequential scan читает страницы таблицы и может быть эффективен, когда нужна большая её часть.
• Index scan следует по подходящим записям индекса к строкам таблицы и подходит selective predicates.
• Bitmap scan PostgreSQL сначала собирает расположение подходящих строк, затем посещает страницы таблицы более сгруппированно.
• Оптимизатор выбирает по оценкам стоимости страниц, числа строк, нужного порядка и предположений cache.

Три алгоритма join объединяют входы через повторный поиск, хеширование или упорядоченный проход.

• Nested loop сканирует внутренний вход для каждой внешней строки и эффективен при малой внешней стороне и индексированном поиске внутри.
• Hash join строит hash table из одного входа и проверяет её другим, обычно для соединений по равенству.
• Merge join продвигается по двум входам, отсортированным по совместимым join keys, и эффективен для больших упорядоченных наборов.
• Условие join, размеры входов, порядок сортировки, память и оценки определяют доступный и выгодный алгоритм.

Cardinality estimates прогнозируют число строк, а cost estimates сравнивают ожидаемую работу ресурсов в специфичных для СУБД единицах.

• Costs обычно объединяют моделируемый доступ к страницам, CPU processing, sorting и startup операторов.
• Это относительные значения планирования, а не прямые миллисекунды.
• Ошибка числа строк в начале плана может привести к неудачному алгоритму или порядку joins далее.
• Фактические метрики выполнения сравнивают с оценками, чтобы понять, отражают ли статистика и предположения данные.

Планировщик использует сводные данные о распределении и размере таблиц для оценки cardinality predicates и joins.

• Число строк и страниц оценивает размер relation.
• Число distinct values и доля NULL оценивают selectivity равенства и отсутствующих значений.
• Histograms приближённо описывают распределение диапазонов, а списки most-common values представляют skew.
• Extended или multicolumn statistics могут описывать correlation и dependencies, которые пропускает независимая статистика столбцов.

ANALYZE берёт выборку данных таблицы и обновляет planner statistics, чтобы оценки отражали текущее распределение.

• Workers autovacuum PostgreSQL могут автоматически запускать analyze после достаточного числа изменений таблицы.
• MySQL поддерживает persistent или sampled statistics с engine-specific поведением refresh.
• Sampling target уравновешивает стоимость сбора, размер catalog и точность для неравномерных данных.
• Статистика описывает значения, а не навсегда кеширует query plans, поэтому поведение prepared plans остаётся отдельной темой.

Selectivity является долей строк, которые предположительно выполняют предикат, и определяет объём данных для каждого пути доступа.

• Уникальный предикат равенства имеет высокую selectivity, потому что возвращает не более одной строки.
• Boolean-столбец с равномерным делением значений обычно сам по себе неселективен.
• Skew означает, что предикат по одному частому значению ведёт себя иначе, чем по редкому.
• Планировщик оценивает selectivity по статистике и объединяет её со стоимостью I/O, CPU и ordering.

Sargable-предикат позволяет оптимизатору сопоставить условие поиска с упорядоченной или иначе доступной через индекс операцией.

• Прямое сравнение индексированного столбца с совместимым значением обычно является sargable.
• Функция вокруг столбца может помешать обычному индексу, если нет подходящего expression index.
• Неявное преобразование типа на индексированной стороне тоже может заблокировать эффективный поиск.
• Переписывание сначала сохраняет семантику запроса, потому что index-friendly выражение бесполезно при изменении результата.

Оптимизатор обычно переставляет и объединяет безопасные предикаты согласно плану, а не их текстовому порядку.

• SQL описывает требуемый результат, а не императивную последовательность проверок строк.
• Predicate pushdown может перемещать фильтры ближе к scans или ниже joins, когда семантика разрешает.
• Volatile functions, outer joins и выражения с возможными ошибками могут ограничивать допустимые преобразования.
• Производительность нужно оценивать по execution plan, а не выводить из порядка условий в тексте запроса.

Покрывающий индекс содержит все столбцы, необходимые запросу, позволяя СУБД избежать или сократить обращения к строкам таблицы.

• Столбцы поиска и join образуют доступный для поиска ключ индекса.
• Дополнительные выходные столбцы можно включить как non-key payload, если СУБД поддерживает INCLUDE.
• Index-only scans PostgreSQL всё равно зависят от visibility information, определяющей возможность пропустить heap access.
• Широкие индексы требуют больше storage и write bandwidth, поэтому покрытие должно соответствовать устойчивым частым формам запросов.

Порядок столбцов должен следовать условиям равенства, границам диапазона, join keys и нужной сортировке обслуживаемых запросов.

• Ведущие столбцы равенства сужают доступный префикс до следующего столбца диапазона.
• После неограниченного диапазона следующие столбцы могут фильтровать, но часто не могут так же эффективно сузить index scan.
• ORDER BY может выиграть при совпадении последовательности и направления с используемым префиксом индекса.
• Одна cardinality не является универсальным правилом, потому что полезность определяет полная форма предиката и сортировки.

Частичный индекс хранит записи только для строк с фиксированным предикатом, уменьшая размер и обслуживание индекса для целевых запросов.

• Индекс WHERE status = 'open' может обслуживать запросы к тому же подмножеству открытых строк.
• Планировщик должен доказать, что предикат запроса подразумевает предикат индекса.
• Частичные уникальные индексы могут обеспечивать уникальность только в подмножестве, например активных записей.
• Они не подходят, когда запросам нужна большая часть исключённых строк или определение подмножества постоянно меняется.

Индекс по выражению хранит результат детерминированного выражения, чтобы запросы с тем же выражением могли искать напрямую.

• Индекс по lower(email) может поддерживать нормализованный по регистру поиск равенства с той же семантикой.
• Выражение запроса должно достаточно точно совпадать с индексированным, чтобы планировщик его распознал.
• Индексы по выражению также могут обеспечивать уникальность нормализованного представления.
• Вычисление и поддержка выражения добавляют стоимость записи и связывают схему с формой запроса.

Тип индекса определяется оператором и паттерном доступа к данным, а не только типом столбца.

• B-tree поддерживает равенство, диапазоны, сортировку и prefix scans сортируемых значений.
• Hash сосредоточен на равенстве и даёт меньше способов доступа, чем B-tree.
• GIN является inverted index для multivalued content вроде arrays, full text и многих JSONB operators.
• GiST поддерживает расширяемые стратегии поиска, например geometry, ranges, nearest neighbor и некоторые text operations.

Индекс наиболее привлекателен, когда предотвращает достаточно работы с таблицей для покрытия стоимости traversal и получения строк.

• Предикат, возвращающий малую долю строк, часто выигрывает от индекса.
• Для частого значения, возвращающего большинство строк, sequential scan может оказаться дешевле.
• Clustering, состояние cache, ширина строки и index-only coverage могут менять точку перехода.
• Статистика с учётом skew важна, потому что среднее число distinct values может неверно описывать частые и редкие значения.

Индекс может предоставлять подходящие ключи или полезный порядок, когда его ведущие столбцы соответствуют операции.

• Индекс по внешнему ключу может ускорить повторные inner lookups в nested-loop join.
• Упорядоченный доступ к индексу может убрать отдельную сортировку для совместимого ORDER BY.
• GROUP BY может использовать упорядоченный вход для grouped aggregation, хотя hash aggregate всё ещё может быть дешевле.
• Один индекс редко обслуживает все варианты filter, join и order, поэтому семьи запросов требуют явных приоритетов.

Каждый индекс занимает место и добавляет работу при изменении данных, журналировании, кешировании и обслуживании.

• INSERT должен добавить записи во все подходящие индексы.
• UPDATE может менять индексированные ключи или инвалидировать версии, а DELETE оставляет cleanup work согласно СУБД.
• Случайные изменения страниц, page splits и накопившееся пустое пространство могут увеличивать write amplification.
• Избыточные индексы тратят ресурсы и могут перекрываться, не обслуживая отдельное ограничение или паттерн запроса.

Multiversion concurrency control позволяет транзакциям читать согласованные версии строк, пока другие транзакции меняют те же логические данные.

• Readers используют snapshot для определения видимых версий.
• Writers создают или ссылаются на новые версии вместо немедленной перезаписи версии, видимой всем readers.
• Это уменьшает reader-writer blocking, но не устраняет конфликты writers и все locks.
• Старые версии требуют cleanup или повторного использования после того, как ни одна relevant transaction их не видит.

Snapshot транзакции определяет, какие зафиксированные и выполняющиеся изменения видны statement или транзакции.

• Он записывает visibility boundary из transaction identifiers или engine-specific version metadata.
• Read Committed обычно получает новый snapshot для каждого statement.
• Repeatable Read обычно сохраняет одно transaction-level view с учётом семантики СУБД.
• Долгоживущий snapshot может мешать освобождению старых версий строк.