SQL Flashcards
(23 cards)
Индексы в SQL. Что это?
Это ключ, построенный с помощью одного или нескольких столбцов в БД, который ускоряет выборку. При этом, если данные не селективные, то индексы замедлят работу БД.
Чтобы добавить индекс в большую и часто используемую таблицу, не блокируя к ней доступ, нужно сделать так:
CREATE INDEX CONCURRENTLY
По умолчанию в БД установлен B-tree индекс.
Индексы хранят в себе ссылки на данные. Эти ссылки могут быть представлены в виде:
- Указателей на строки в таблице, где хранятся данные
- Идентификаторов записей
Уровни изоляции (read uncommitted, read committed, repeatable read, serializable)
READ UNCOMMITTED (Транзакции могут читать данные, которые еще не зафиксированы другими транзакциями).
READ COMMITTED (Транзакции могут читать только те данные, которые зафиксированы другими транзакциями).
REPEATABLE READ (В пределах одной транзакции, если вы читаете одну и ту же запись несколько раз, данные гарантировано не изменятся).
SERIALIZABLE (Транзакции выполняются друг за другом, эмулируя последовательное выполнение).
Во многих бд по стандарту стоит Read commited,
но в MySQL стоит Repeatable Read B PostgreSQL нет read uncommited
Архитектура MVCC (Multi-Version Concurrency
Control):
PostgreSQL использует механизм МVСС, который позволяет нескольким транзакциям работать с данными одновременно, не блокируя друг друга.
В MVCC каждая транзакция видит снимок данных (snapshot) на момент своего начала. Это означает, что транзакция не может видеть изменения, которые ещё не зафиксированы.
Поэтому нет read uncommited
Оптимизация запросов, какие инструменты?
EXPLAIN (Показывает план выполнения запроса).
EXPLAIN ANALYZE (Выполняет запрос и показывает реальный план выполнения).
Indexes (ускоряют доступ к данным, особенно при использовании в условиях WHERE).
pg_stat_statements (модуль для сбора статистики выполнения запросов).
Если запрос в норме, но выполнение все равно долгое, следует проверить:
- Правильность использования индексов
- План выполнения
- Возможность отрефакторить запрос
- Ресурсные ограничения
Расскажите про ACID
Это набор свойств, согласно которым должны работать транзакции.
- Атомарность: транзакция выполняется полностью или не выполняется вовсе.
- Согласованность: после выполнения транзакции все данные должны соблюдать правила целостности.
- Изолированность: транзакции выполняются изолированно друг от друга.
- Долговечность: изменения, внесенные транзакцией, остаются в БД после коммита.
Виды JOIN. Как работает каждый JOIN?
INNER JOIN — возвращает только общие значения между таблицами.
LEFT JOIN — возвращает все значения из левой таблицы и соответствующие из правой.
RIGHT JOIN — возвращает все значения из правой таблицы и соответствующие из левой.
FULL JOIN — возвращает все записи из обеих таблиц, даже если нет соответствий.
CROSS JOIN — создает декартово произведение двух таблиц.
SELF JOIN — соединяет таблицу сама с собой.
Блокировки и локи. Оптимистичная и Пессимистичная
Блокировки — механизм управления параллельным доступом к данным, предотвращающий конфликтные изменения.
- Оптимистичная блокировка: при сохранении проверяет, не изменились ли данные с момента чтения; при изменении — откатывает транзакцию.
- Пессимистичная блокировка: сразу блокирует данные, чтобы другие транзакции не могли их изменить.
EXPLAIN vs EXPLAIN ANALYZE
EXPLAIN — дает план выполнения запроса без фактического выполнения.
EXPLAIN ANALYZE — выполняет запрос и показывает реальное время, количество обработанных строк (ROWS) и количество повторений шага (LOOPS).
Что такое транзакции?
Это набор логически связанных запросов, которые выполняются атомарно.
Плюсы и минусы индексов
Плюсы:
- Улучшают производительность для SELECT и сортировки по определенным полям.
Минусы:
- Замедляют операции INSERT, UPDATE, DELETE.
- Требуют дополнительного места (чем больше ключ, тем больше весит индекс).
Какие виды индексов знаешь?
Типы индексов:
Кластеризованные - физический порядок хранения строк в таблице, он может быть в ней только один. Primary key автоматически кластерный индекс. Записи на диске будут храниться по возрастанию.
Некластеризованные - отдельная структура, которая ссылается на данные, их может быть много в таблице. Содержит только выбранные колонки + ссылку на основную строку в таблице.
Виды индексов:
B-tree - по стандарту используется, сбалансированное дерево
Composite index - индекс для нескольких столбцов, используется, когда много запросов с фильтрами по этим столбцам
Hash index - использует хэш-функцию для поиска точных значений, не поддерживает диапазоны
Unique index - для обеспечения уникальности
Bitmap index - побитовые карты, нужны для малого кол-ва уникальных значений, типо пол, или регионы заказов
Gist Index - используется для индексации геоданных чаще всего, по-моему в PostGIS
Чем WHERE отличается от HAVING? Можно ли их использовать в одном запросе?
WHERE применяется до выполнения агрегатных функций, а HAVING – после. Да, можно использовать в одном запросе: сначала FILTER с помощью WHERE, затем группировка и условие HAVING.
Нормализация и денормализация. Перечислите формы.
Нормализация — процесс преобразования отношений БД к виду без избыточной информации. Избыточность — когда одни и те же данные хранятся в разных местах.
Нормальные формы:
1NF: одна ячейка — одно значение.
2NF: все неключевые атрибуты зависят от полного ключа.
3NF: каждый неключевой атрибут зависит только от ключа (нет транзитивных зависимостей).
Денормализация — процесс обратный нормализации, используется для ускорения чтения.
Проблемы параллельных транзакций
Потерянное обновление: при одновременном изменении данных вступают в силу только последние изменения. Решение: уровень изоляции REPEATABLE READ и выше.
Грязное чтение: транзакция читает незакомиченные данные другой транзакции. Решение: уровень изоляции READ COMMITTED и выше.
Неповторяющееся чтение: транзакция A читает X, транзакция B изменяет X, транзакция A снова читает X и получает другое значение. Решение: REPEATABLE READ и выше.
Фантомное чтение: транзакция A делает выборку, транзакция B добавляет новую запись, транзакция A повторяет выборку и получает больше строк. Решение: SERIALIZABLE.
Что такое селективность
Это доля строк, которые будут отобраны запросом. Чем меньше строк отбирается, тем более селективен запрос.
Primary key
Идентифицирует строку, гарантирует уникальность, ускоряет поиск и связывает таблицы.
Foreign key
Связывает таблицы и указывает, что значение в одном столбце должно соответствовать значению в другой таблице.
Блокировка строки с помощью SQL
FOR UPDATE
Lateral
Позволяет подзапросу использовать данные из внешней таблицы для каждой строки
Оконные функции. Partition by
Оконные функции позволяют выполнять вычисления над строками, разделенными на групы или на весь набор данных
PARTITION BY - работает с агрегирующими функциями, разбивает данные на групы, но не схлопывает их, а к каждой строке добавляет результат агрегирующих функций
GROUP BY - так же работает с агрегирующими функциями, но схлопывает данные, возвращает только группы и агрегаты
View vs MaterializedView
View - просто хранит sql запрос, который будет выполняться при обращении ко view (данные не хранятся физически)
Materialized view - хранит результат выполненного запроса, необходим в REFRESH для актуализации данных
SELECT FOR UPDATE & SELECT FOR SHARE
Пессимистичная:
SELECT FOR UPDATE - используется для блокировки обновляемой строки (для изменения), пока текущая транзакция выполняет работу другая не сможет её обновить
SELECT FOR SHARE - тоже блокирует строку, только уже для считывания, пока текущая транзакция выполняет работу другая не сможет получить к ней доступ
Табличные выражения
Табличные выражения - временные именованные наборы данных (сохраняют результат запроса в переменную), создается с помощью WITH
Подзапрос - тоже вид табличного выражения
Database Connection Pool. HikariCP
HikariCP - самый популярный пул соединений
Пулы нужны для оптимизации работы с соединениями к базе данных, мы не пересоздаем их каждый раз, а просто кладем обратно в пул.
