1
Q
Медленный запрос к БД, что делать?
A
Используйте EXPLAIN ANALYZE для запроса, чтобы вывести оценки стоимости и времени выполнения для каждого этапа запроса.
Используя инф-ю из “pg_stat_statements”, вы можете определить медленные запросы, которые требуют оптимизации, и улучшить производительность вашей БД.
Оптимизируйте запрос, добавляя или изменяя индексы, переписав неэффективные запросы или используя другие приемы оптимизации. Обновите статистику БД.
2
Q
Что такое СУБД? Какие бывают типы?
A
Система управления базами данных, используемая для создания, хранения, поиска и изменения данных.
СУБД бывают:
- Реляционные
- Нереляционные
- Иерархические
- In-memory (в оперативной памяти)
3
Q
Что такое ACID?
A
Это аббревиатура, которая описывает 4 основных свойства транзакций в реляционных БД:
- Atomicity: Транзакция считается атомарной, что значит, что она выполняется как единое целое
- Consistency: Транзакция должна приводить БД из одного согласованного состояния в другое с соблюдением всех целостностных ограничений и правил, заданных в БД
- Isolation: Транзакции должны выполняться в изоляции друг от друга
- Durability: После успешного завершения транзакции, ее результаты должны сохраняться даже в случае сбоя системы
4
Q
Какие уровни изоляции транзакций?
A
Уровни изоляции транзакций определяют, как взаимодействуют между собой транзакции при одновременном доступе к данным в реляционных БД.Они определяют уровни видимости и блокировки данных между транзакциями. Основные уровни:
– Read Uncommited – В этом уровне транзакции могут видеть неподтвержденные изменения других транзакций
– Read Commited (по умолчанию в PG) – Транзакции видят только подтвержденные изменения других транзакций, а неподтвержденных они не видят
– Repeatable Read – Транзакции видят только данные, которые были считаны до начала транзакции, и эти данные остаются постоянными в течение всей транзакции
– Serializable – Транзакции выполняются одна за другой без параллельного выполнения
5
Q
Что делает команда GROUP BY?
A
Группирует строки, имеющие одинаковые значения в указанных столбцах, в агрегированные данные, такие как суммы или средние значения
6
Q
Зачем нужны HAVING и WHERE в SQL?
A
HAVING фильтрует результаты после группировки; можно использовать с агрегатными функциями, такими как SUM, AVG, COUNT
WHERE фильтрует строки до группировки; нельзя использовать с агрегатными функциями, такими как SUM, AVG, COUNT
7
Q
Что такое transaction race?
A
Это явление, которое может возникнуть при использовании изоляции Read Commited в БД. Гонка возникает, когда несколько транзакций конфликтуют из-за чтения и обновления одних и тех же данных
8
Q
Индексы. Какие бывают? Примеры из жизни!
A
Это структуры данных, которые ускоряют поиск и извлечение данных из таблицы. Они представляют собой отсортированные структуры данных, которые содержат ключи и указатели на строки в таблице, где эти ключи хранятся.
Основные индексы:
– B-Tree индекс
– Хэш-индекс
– GIN-индекс
– BRIN-индекс
– GiST-индекс
– SP-GiST-индекс
– SP-GiST и GIN для полнотекстового поиска
Примеры из жизни:
– Поиск по фамилии в контактной книге
– Интернет-магазин. Сначала поиск по категориям, а потом по цене
– Номерной знак авто в базе ГИБДД
9
Q
B-Tree index (как устроен, где используется)?
A
Это древовидная структура, использующаяся для быстрого поиска, вставки и удаления отсортированных данных за логарифмическое время.
Все листовые узлы находятся на одном уровне. Дерево содержит узлы, каждое из которых включает несколько ключей и указатели на дочерние узлы. Ключи хранятся в отсортированном порядке. Каждый узел может содержать сотни ключей.
Где используется:
– Для создания индексов
– Для быстрого поиска по первичному ключу
– Для запросов с ORDER BY, GROUP BY
10
Q
B-Tree vs Hash indexes
A
BTREE индекс:
- Применение: Подходит для индексации даных, которые используются в равенственных и диапазонных операциях сравнения - Поиск: Обеспечивает эффективный поиск, особенно по точному совпадению - Сортировка: Поддерживает сортировку данных и ускоряет выполнение операций ORDER BY - Пример: Индексация столбцов, содержащие числовые и текстовые данные, ID и т.д
Когда использовать:
- Для поиска по точному значению или диапазону значений - Когда нужна поддержка сортировки данных - Когда индексируемые данные имеют повторяющиеся значения Hash индекс: - Применение: Подходит для индексации данных, когда требуется быстрый поиск точного совпадения - Поиск: Обеспечивает поиск данных за постоянное время для точных совпадений - Сортировка: Не поддерживает сортировку данных - Индексация столбцов с уникальными значениями, такими как ID сущностей или хэши
Когда использовать:
- Для поиска по уникальным и практически уникальным значениям
- Когда запросы основаны на равенственных операциях и не требуют сортировки или диапазонных запросов
- Когда хэширование данных имеет смысл
11
Q
Что такое составной индекс?
A
Это индексы, которые включают более одного столбца таблицы.
Преимущества: 1. Улучшенная производительность запросов: Индексы ускоряют выполнение запросов, которые фильтруют или сортируют данные по нескольким столбцам 2. Покрытие индексов: То есть содержать все данные, необходимые для выполнения запроса, без обращения к основной таблице
12
Q
Explain и Explain ANALYZE?
A
Используя Explain, вы можете увидеть, как БД будет выполнять ваш запрос, включая подробности об использовании индексов, типах соединений и порядке сканирования таблиц. Эта информация может помочь оптимизировать ваши запросы для повышения производительности
Различия: Это две разные команды, используемые для анализа плана выполнения SQL-запроса. - EXPLAIN предоставляет приблизительный план выполнения, - EXPLAIN ANALYZE фактически выполняет запрос и выдает подробный отчет о фактическом плане выполнения
13
Q
Что такое SELECT FOR UPDATE?
A
Используется для управления конкурентным доступом к данным. Этот механизм позволяет блокировать строки, которые будут обновляться, предотвращая возможные конфликты при параллельном доступе к тем же данным
14
Q
Какой тип данных обычно делают PK?
A
Выбирают типы, которые гарантируют уникальность, занимают мало места и быстро индексируются:
1. INT / BIGINT (Целые числа) - Занимают мало места (4-8 байт), очень быстро сравниваются и автоматически увеличиваются 2. UUID / GUID – 128-битный уникальный ID - Позволяет генерировать ID на стороне клиента, не спрашивая базу. Удобен для распределенных систем 3. VARCHAR / TEXT (Строки) – редко и нежелательно - Только если если есь естественный уникальный ключ, например, email
15
Q
Что такое шардирование и репликация, чем они отличаются?
A
Шардирование – это техника масштабирования БД, при которой данные разбиваются на несколько логических или физических частей, и каждая часть хранится на отдельном физическом сервере. Целью шардирования является равномерное распределение нагрузки на БД и обеспечение горизонтального масштабирования, что позволяет обрабатывать большие объемы данных и высокую нагрузку
- Разрезание таблицы на части по серверам - Уникальные фрагменты данных - Масштабируемость записи и объема - Часть данных становится недоступна - Требует изменения логики (сложно)
Репликация – это создание копий вашей БД на нескольких серверах
- Дублирование всей БД по разным серверам
- Идентичные копии данных
- Отказоустойчивость и скорость чтения
- Система работает на копии (Высокая доступность)
- Настраивается силами СУБД
16
Q
CTE? Какой оператор используется в CTE запросах?
A
Сommon Table Expression, также известное как WITH-запрос, представляет собой временное именованное подзапросное выражение, которое можно использовать внутри других SQL-запросов. CTE обеспечивает чистоту и читаемость SQL-запросов, делая их более структурированными и понятными.
В CTE используется оператор WITH, за которым следует одно или несколько подзапросных выражений
17
Q
Transaction problems?
A
Это аномалии, возникающие при одновременном доступе нескольких пользователей к одним и тем же данным. Четыре главных проблемы:
1. Грязное чтение (Dirty Read) 2. Неповторяющееся чтение (Non-repeatable Read) 3. Фантомное чтение (Phanthom Read) 4. Потерянное обновление (Lost Update)
18
Q
Что такое подзапрос?
A
Это SQL-запрос, вложенный внутрь другого запроса
19
Q
Что такое транзакция?
A
Представляет собой логическую единицу работы, состоящую из одного или нескольких SQL-индексов. Транзакция начинается с начала выполнения первого запроса и заканчивается успешным завершением (коммит) или откатом всех внесенных изменений (роллбэк) после выполнения последнего запроса. Транзакции обеспечивают целостность данных и позволяют избегать потери или несогласованности данных при одновременном доступе нескольких пользователей к БД
20
Q
Что такое внешний ключ? Сослаться можно на любое поле или нет (кроме pk)?
A
Foreing Key устанавливает связь между столбцами текущей таблицы и столбцами в другой таблице. Гарантирует согласованность данных между связанными таблицами.
Он может повторяться, может быть пустым, в таблице может быть много внешних ключей
21
Q
Задачка 1
У нас есть магазин книг по продажам книг. У книг есть автор, и можем продавать по акций.
```sql
CREATE TABLE authors (
author_id INT PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
CREATE TABLE books (
book_id INT PRIMARY KEY,
title VARCHAR(255),
author_id INT,
genre VARCHAR(255),
price DECIMAL,
discount DECIMAL,
FOREIGN KEY (author_id) REFERENCES authors(author_id)
);
CREATE TABLE solds (
sold_id INT PRIMARY KEY
book_id INT,
count INT,
date TIMESTAMP
);
CREATE TABLE ratings (
rating_id INT PRIMARY KEY,
book_id INT,
rating DECIMAL,
review TEXT
);
~~~
A
22
Q
CAP-теорема?
A
Эта теорема гласит, что любая распределенная система может соответствовать только двум их трех требований%
1. Consistency – все клиенты одновременно видят одни и те же данные после изменения, к какому бы узлу они не подключались
2. Availability – любой клиент, запрашивающий данные, получает ответ, даже если некоторые из узлов недоступны. Этого можно достигнуть за счет балансировщика нагрузки
3. Partition tolerance – система продолжает работать при нарушении связи между ее узлами
23
Q
Deadlock?
A
Это состояние, при котором, два или более процессов одновременно бесконечно ожидают освобождения ресурсов занятых друг другjv
24
Q
MVCC?
A
Multi Version Concurrency Control – это механизм управления параллельным доступом к данным БД, который широко используется в PostgreSQL и других СУБД для поддержки одновременных транзакций. Позволяет видеть БД в разных версиях, что обеспечивает высокую степень изоляции и согласованности
25
Как получить текущую дату и время в SQL?
SELECT now(), current_date, clock_timestamp()
26
Разница между DELETE и TRUNCATE?
DELETE – удаляет записи по одной, сканируя таблицу. Операция типа DML.
TRUNCATE – это DDL-операция, позволяет удалять мгновенно все данные из одной или нескольких таблиц, сохраняя их структуру. Работает быстрее, чем DELETE
27
Отличия Primary key и Foreign key?
Primary Key – уникальный ключ для идентификации каждой записи в таблице.
- Идентифицирует строку в таблице
- Обязан быть уникальным
- Запрещены NOT NULL
- Только один на таблицу
- Индекс создается автоматически
Foreign Key – устанавливает связь между текущей таблицей и столбцами в другой таблице. Гарантирует согласованность данных между связанными таблицами.
- Связывает таблицы между собой
- Может повторяться
- Разрешены NULL, если связь необязательна
- Может быть несколько в одной таблице
- Индекс нужно создавать вручную
28
Что такое contsraint?
Это ограничения, которые накладываются на столбцы и таблицы для обеспечения точности, целостности и надежности данных
29
Уникальный ключ (UNIQUE)?
Гарантирует, что все значения в столбце являются уникальными
30
Почему uuid медленнее чем serial?
UUID проигрывает классическому SERIAL по трем основным причинам: **размер**, **сортировка** и **индексация**
1. Объемы данных
- SERIAL занимает всего 4 байта
- UUID занимает 16 байт
2. Фрагментация индекса
- SERIAL генерирует значения последовательно
- UUID генерируется случайно
3. Нагрузка на CPU
Сравнивать два целых числа (SERIAL) — это элементарная операция для процессора. Сравнивать 128-битные UUID — задача более ресурсоемкая, что замедляет операции `JOIN` и `ORDER BY`.
31
Разница между INNER JOIN и OUTER JOIN?
INNER JOIN – возвращает строки, которые имеют соответствующие значения в обеих объединяемых таблицах
OUTER JOIN (LEFT, RIGHT, FULL) – включает все строки из одной таблицы или обеих таблиц, заполняя отсутствующие блоки значениями NULL
32
DeadTuple и Vacuum?
DeadTuple – (мертвый кортеж) относится к стокам данных, которые были удалены или обновлены, но все еще занимают место в таблице до тех пор, пока не будет выполнена команда VACUUM
Vacuum – это "мусорщик", который проходит по таблице и ище Dead Tuples. Просто помечает место, занятое мертвыми строками, как "свободное"
33
Как посмотреть количество текущих коннектов к базе?
С помощью команды pg_stat_activity.
**Зачем проверять?** – У каждой базы есть лимит `max_connections`. Если количество коннектов приблизится к нему, новые пользователи не смогут подключиться
34
Как посмотреть примерное количество записей в таблице, не используя COUNT?
Для получения мгновенного, но приблизительного значения используйте системный каталог `pg_class`.
1. Быстрый – Через статистику
Этот запрос берет данные из планировщика. Они обновляются при каждом `ANALYZE` или `AUTOVACUUM`
```sql
SELECT reltuples AS estimate
FROM pg_class
WHERE relname = 'имя_вашей_таблицы';
```
2. Более точный способ (через системное представление)
Этот вариант обращается к статистике схемы, что часто удобнее
```sql
SELECT n_live_tup
FROM pg_stat_user_tables
WHERE relname = 'имя_вашей_таблицы';
```
35
Задачка. Оптимизируйте запрос
Основные этапы и методы оптимизации:
1.Анализ запроса (EXPLAIN ANALYZE):
- Используйте EXPLAIN (ANALYZE, BUFFERS) query;
- Ищите узлы с наибольшим временем (actual time) и стоимостью (cost).
- Проверьте, не происходит ли полное сканирование таблицы (Seq Scan) там, где должен работать индекс.
2. Индексация:
- Создавайте индексы на столбцы, используемые в WHERE, JOIN и ORDER BY.
- Используйте B-tree для точного поиска и диапазонов, GIN — для JSONB и полнотекстового поиска.
- Пример: CREATE INDEX idx_name ON table_name(column_name);.
3. Оптимизация SQL-кода:
- Избегайте SELECT *, указывайте только нужные столбцы.
- Избегайте функций в условиях WHERE, так как это мешает использованию индексов.
- Используйте LIMIT для ограничения выборки.
- Заменяйте сложные подзапросы на соединения (JOIN) или временные таблицы при необходимости.
4. Обслуживание базы данных:
- Регулярно запускайте ANALYZE для обновления статистики, чтобы планировщик выбирал оптимальный путь.
- Убедитесь, что autovacuum включен и работает корректно, чтобы предотвратить раздувание таблиц (bloat).
5. Настройка конфигурации (postgresql.conf):
- work_mem: Увеличьте для сложных запросов с сортировкой или хэш-соединениями.
- random_page_cost: Снизьте до 1.1–1.5, если используете SSD (по умолчанию 4.0 для HDD), чтобы планировщик чаще использовал индексы.
- effective_cache_size: Установите значение, отражающее объем памяти, доступный для кэширования (обычно 50-75% от общего объема RAM).
6. Мониторинг:
- Используйте pg_stat_statements для поиска самых медленных и частых запросов.
- Анализируйте логи медленных запросов (log_min_duration_statement)
36
Почему иногда лучше char, чем varchar?
CHAR выигрывает в специфических сценариях благодаря **предсказуемости** и **архитектуре** хранения.
Вот основные причины:
1. Фиксированная длина = Скорость
СУБД точно знает, сколько байт отведено под каждую строку, что упрощает расчет смещения при чтении данных с диска
2. Избежание "раздувания" (Row Overheads)
CHAR не хранит дополнительные 1 или 4 байта, чтобы записать длину строки
3. Предотвращение фрагментации (в некоторых СУБД)
Использование CHAR в таблицах с фиксированной длиной строки позволяет избежать фрагментации файлов
4. Наглядность и валидация
CHAR(10) – это своего рода constraint. Он гарантирует, что данные будут определенного формата
37
Запроc. Получите все магазины с количеством заказов за сентябрь 2023 года больше 100
SELECT * FROM Shops WHERE COUNT(orders) > 100
38
Что такое N+1 проблема? Как решается?
Возникает в контексте выполнения запросов к БД, когда при получении связанных данных, несколько раз выполняются дополнительные запросы к БД для каждой записи или объекта, полученного в первоначальном запросе. Это приводит к избыточному количеству запросов и снижению производительности приложения.
Решение проблемы N+1 в PostgreSQL:
1. Использование JOIN
– Вы можете объединить таблицы с помощью оператора JOIN в одном запросе, чтобы избежать дополнительных запросов.
2. Использование подзапросов (Subqueries)
3. Использование оконных функций (Window Function)
4. Использование CTE (Common Table Expressions)
39
Как использовать неблокирующее создание индексов?
Этот метод позволяет строить индексы без блокировки записи (INSERT, UPDATE, DELETE) в таблицу, обеспечивая доступность данных для приложений.
CREATE INDEX CONCURRENTLY имя_индекса ON имя_таблицы (столбец);
40
Как добавлять первичный ключ с минимальной блокировкой?
К счастью, вы можете сначала выполнить всю тяжелую работу, используя
CREATE UNIQUE INDEX CONCURRENTLY
, а затем использовать уникальный индекс в качестве первичного ключа, что является быстрой операцией.
CREATE UNIQUE INDEX CONCURRENTLY items_pk ON items (id); -- занимает много времени, но не блокирует запросы
ALTER TABLE items ADD CONSTRAINT items_pk PRIMARY KEY USING INDEX items_pk; -- блокирует запросы, но ненадолго
Разбиение создания первичного ключа на два этапа практически не отражается на пользователе.
41
Что такое pg_advisory_lock?
pg_advisory_lock – это механизм блокировки, предоставляемый Системой Управления Базами Данных PostgreSQL (PostgreSQL DBMS), который позволяет приложениям управлять собственными пользовательскими блокировками на уровне базы данных. Он полезен в сценариях, где вам нужно координировать доступ к ресурсам или операциям в многопользовательской среде.
42
Как работают Bitmap Index в Explain?
Bitmap Index в EXPLAIN (обычно в PostgreSQL) — это метод эффективного поиска, при котором база данных создает битовую карту (0 или 1 для каждой строки) на основе индекса, а затем использует ее для выборки данных. Он состоит из двух этапов: Bitmap Index Scan (создание карты) и Bitmap Heap Scan (чтение строк из таблицы), что позволяет ускорить запросы с множественными условиями.
Как это работает в плане выполнения (EXPLAIN):
1.Bitmap Index Scan (сканирование индекса): База сканирует индекс (например, по колонке с низкой кардинальностью) и создает в оперативной памяти битовый массив, где 1 означает, что строка соответствует условию, а 0 — нет.
2. BitmapAnd / BitmapOr (объединение): Если в запросе несколько условий (например, AND или OR), база данных выполняет побитовые операции над несколькими картами, получая итоговый массив.
3. Bitmap Heap Scan (сканирование кучи): Полученная битовая карта используется для обращения к таблице. Важно, что данные считываются не подряд, а в упорядоченном по физическому расположению (на страницах) виде, что уменьшает количество случайных операций ввода-вывода (random I/O).
4. Recheck Cond (перепроверка): Поскольку битовая карта указывает на страницу данных, а не на конкретную строку, Bitmap Heap Scan часто выполняет перепроверку условий
5. Recheck Cond для строк внутри страницы, чтобы исключить ложные срабатывания.
43
Что такое нормальная форма? И какие 3 первые бывают?
Нормальные формы (Normal Forms) - это система правил и стандартов, которые определяют, как организовать структуру реляционных баз данных, чтобы обеспечить целостность данных и уменьшить избыточность информации.
1. Первая нормальная форма (1NF):
2. Вторая нормальная форма (2NF):
3. Третья нормальная форма (3NF):
44
Что такое представления (Views)? Зачем нужны?
–Что это: Логические представления данных, основанные на результатах запроса SQL. Представления не хранят данные сами по себе, а предоставляют способ доступа к данным, хранящимся в одной или нескольких таблицах.
– Зачем нужны: Упрощают сложные запросы, обеспечивают уровень абстракции для безопасности и удобства.
```sql
CREATE VIEW ActiveUsers AS
SELECT user_id, username
FROM users
WHERE status = 'active';
```sql
45
Что такое хранимые процедуры? Зачем нужны?
– Это предварительно скомпилированный набор SQL-операторов, хранящийся на сервере базы данных.. Они могут включать SQL запросы и другие логические конструкции (циклы, условия).
– Зачем нужны: Улучшают производительность, повторное использование кода, упрощают управление транзакциями и обеспечивают безопасность.
```sql
CREATE PROCEDURE UpdateUserStatus(IN userId INT, IN newStatus VARCHAR(20))
BEGIN
UPDATE users
SET status = newStatus
WHERE id = userId;
END;
```
46
Что такое триггеры? Зачем нужны?
– Что это: Автоматические действия, выполняемые при определённых событиях в таблице, таких как вставка, обновление или удаление строк.
– Зачем нужны: Поддерживают целостность данных, автоматизируют задачи, выполняют аудит изменений.
– Пример:
```sql
CREATE TRIGGER BeforeInsertUser
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
SET NEW.created_at = NOW();
END;
```
47
Что такое констрейнты (ограничения)? Какие наиболее распространенные?
1. `PRIMARY KEY` (Первичный ключ):
2. `UNIQUE` (Уникальность):
3. `NOT NULL` (Не пустое значение):
4. `FOREIGN KEY` (Внешний ключ):
5. `CHECK` (Проверка):
6. `DEFAULT` (Значение по умолчанию):
48
Какие связи бывают?
Связи
**Один-к-одному (One-to-One):**
- Описание: Каждая запись в одной таблице связана с одной и только одной записью в другой таблице.
**Один-ко-многим (One-to-Many):**
- Описание: Каждая запись в одной таблице связана с одной или более записями в другой таблице. Таблица с одной стороны связи называется "родительской" таблицей, а с другой стороны - "дочерней" таблицей.
**Многие-ко-многим (Many-to-Many):**
- Описание: Множество записей в одной таблице связано с множеством записей в другой таблице через промежуточную таблицу, называемую "связующей" или "промежуточной" таблицей.
**Самосвязь (Self-Referencing):**
- Описание: Таблица может быть связана сама с собой. Это используется, когда сущность имеет отношение к другим сущностям того же типа.
**Композиция (Composition):**
- Описание: В данном случае, одна сущность является частью другой и не имеет самостоятельного смысла без родительской сущности.
**Агрегация (Aggregation):**
- Описание: Похожа на композицию, но агрегированный объект имеет собственную сущность и может существовать независимо от родительской сущности.
49
Что такое Cascade Delete?
— это механизм, который автоматически удаляет связанные записи в других таблицах при удалении записи в основной таблице. Этот механизм используется для поддержания целостности данных и избежания "висячих" ссылок, то есть ситуаций, когда данные в одной таблице ссылаются на несуществующие данные в другой таблице.
50
Что такое Pagination (пагинация)?
— это метод разбиения большого результирующего набора данных на более мелкие, управляемые части («страницы»), что повышает производительность запросов и улучшает пользовательский опыт.
51
Что такое Offset (page-based) пагинация?
Работает с помощью ключевых слов `OFFSET` и `LIMIT`. Например, размер одной страницы 10 элементов и нам нужна вторая страница:
```sql
select * from users order by `id` asc limit 10 offset 10;
```
Распространенной проблемой при offset-пагинации является дублирование или отсутствие контента, особенно для наборов данных с высокой частотой записи. Эта проблема возникает, когда добавляются или удаляются одна или несколько записей на предыдущей странице.
52
Что такое курсорная (cursor-based) пагинация?
```sql
select * from users where `id` > 10 order by `id` asc limit 10;
```
**Использует** `WHERE` вместо `OFFSET`, чтобы пропустить элементы с прошлых страниц. В условии `WHERE` указываются значения последнего элемента на прошлой странице. Пример:
Курсорная пагинация **работает быстрее, чем offset**-пагинация за счет того, что offset сканирует все предыдущие данные. Это означает, что при смещении в 100 000 записей БД по-прежнему будет сканировать эти 100 000 записей. А cursor-пагинация можно сразу перейти к нужной записи, при условии, что настроен индекс для поля в order by.
**Ограничения курсорной пагинации:**
- Не поддерживает нумерацию страниц
- Необходим индекс для поля в `order by`
- Поля в `order by` должны быть уникальны. Если сортировка по не уникальным полям, можно добавить в сравнение поле `id` или `created_at`:
53
Какие Джоины (Join) бывают?
**INNER JOIN:**
- Описание: Возвращает строки, которые имеют соответствующие значения в обеих объединяемых таблицах.
**LEFT JOIN (или LEFT OUTER JOIN):**
- Описание: Возвращает все строки из левой таблицы и соответствующие строки из правой таблицы. Если в правой таблице нет соответствия, то будут возвращены NULL-значения.
**RIGHT JOIN (или RIGHT OUTER JOIN):**
- Описание: Возвращает все строки из правой таблицы и соответствующие строки из левой таблицы. Если в левой таблице нет соответствия, то будут возвращены NULL-значения.
**FULL JOIN (или FULL OUTER JOIN):**
- Описание: Возвращает все строки из обеих таблиц. Если нет соответствующего значения в одной из таблиц, будут возвращены NULL-значения.
**CROSS JOIN (или CARTESIAN JOIN):**
- Описание: Возвращает декартово произведение строк из двух таблиц. То есть каждая строка из первой таблицы объединяется с каждой строкой из второй таблицы.
54
Что такое CTE?
Также известное как WITH-запрос, представляет собой временное именованное подзапросное выражение, которое можно использовать внутри других SQL-запросов. CTE обеспечивает чистоту и читаемость SQL-запросов, делая их более структурированными и понятными.
55
Что такое Window function (оконная функция)
Оконная функция (window function) в SQL - это мощный инструмент для анализа данных, который позволяет выполнять вычисления на подмножествах результирующего набора строк, называемых "окнами" (windows), в пределах определенного порядка. Оконные функции позволяют вычислять агрегатные функции, ранги, и другие операции на наборе строк, сгруппированных и упорядоченных по определенным столбцам.
56
Какие основные компоненты плана выполнения Explain Analyze?
Основные компоненты плана выполнения
- **Seq Scan (Sequential Scan)**:
- **Index Scan**:
- **Bitmap Index Scan** и **Bitmap Heap Scan**:
- **Nested Loop**:
- **Hash Join**:
- **Merge Join**:
- **Sort**:
- **Aggregate**:
57
Что такое селективность индекса?
— это показатель того, насколько хорошо индекс помогает сузить круг возможных строк, которые нужно проверить при выполнении запроса. Другими словами, селективность определяет, насколько "выборочным" будет запрос, когда используется индекс.
58
Что такое покрывающий индекс?
— это такой индекс, который содержит все данные, необходимые для выполнения запроса. Это значит, что база данных может получить все нужные данные только из индекса, не обращаясь к самой таблице. Это позволяет значительно ускорить запросы, потому что обращение к индексу быстрее, чем к основной таблице.
59
Что такое блокировки? Виды? Как работают?
— это механизмы управления одновременным доступом к данным, которые ограничивают доступ к строкам или таблицам базы данных (БД) во время выполнения транзакций. Они гарантируют целостность и согласованность данных (ACID), предотвращая одновременное изменение одной записи разными пользователями. Данные, на которые наложена блокировка, недоступны для чтения или записи другими пользователями до её снятия.
Блокировки строк (Row Locks)
**Блокировки строк** применяются к отдельным строкам в таблице. Они позволяют более гранулярно контролировать доступ к данным, что улучшает параллелизм и производительность при работе с большим количеством транзакций.
Блокировки таблиц (Table Locks)
**Блокировки таблиц** применяются ко всей таблице. Они используются для операций, которые затрагивают множество строк или структуру таблицы, таких как добавление индексов или изменения схемы.
60
Какие существуют виды блокировок таблиц?
**Блокировки таблиц** применяются ко всей таблице. Они используются для операций, которые затрагивают множество строк или структуру таблицы, таких как добавление индексов или изменения схемы.
**Виды блокировок таблиц:**
1. **Access Share Lock**:
- **Описание**: Позволяет другим транзакциям также устанавливать access share-блокировки.
2. **Row Share Lock**:
- **Описание**: Блокирует exclusive-блокировки, но позволяет shared-блокировки.
3. **Row Exclusive Lock**:
- **Описание**: Блокирует row share и exclusive блокировки, но позволяет access share блокировки.
4. **Share Update Exclusive:**
5. **Share Lock**:
- **Описание**: Позволяет другим транзакциям устанавливать только access share блокировки.
6. **Share Row Exclusive Lock**:
- **Описание**: Блокирует другие share и exclusive блокировки.
7. **Exclusive Lock**:
- **Описание**: Полностью блокирует таблицу для других операций.
8. **Access Exclusive Lock**:
- **Описание**: Самая строгая блокировка, блокирует все другие блокировки.
61
Что такое взаимоблокировка (deadlock)?
— это ситуация, когда две или более транзакции бесконечно ожидают друг от друга освобождения блокировок на ресурсы (таблицы, строки), удерживая свои собственные.
62
Что такое SELECT FOR UPDATE?
Механизм SELECT FOR UPDATE в базах данных PostgreSQL используется для управления конкурентным доступом к данным. Этот механизм позволяет блокировать строки, которые будут обновляться, предотвращая возможные конфликты при параллельном доступе к тем же данным.
63
Что такое транзакции?
Транзакция в контексте баз данных представляет собой логическую единицу работы, состоящую из одного или нескольких SQL-запросов.
Транзакция начинается с начала выполнения первого запроса и заканчивается успешным завершением (фиксацией | COMMIT) или откатом (отменой | ROLLBACK) всех внесенных изменений в базу данных после выполнения последнего запроса. Транзакции обеспечивают целостность данных и позволяют избегать потери или несогласованности данных при одновременном доступе нескольких пользователей к базе данных.
64
Как транзакции должны удовлетворять ACID?
1. **Атомарность (Atomicity)**: Транзакция считается атомарной, что означает, что она выполняется как единое целое. Все изменения в базе данных либо выполняются полностью, либо не выполняются вообще. Если одна часть транзакции завершается неудачно, все изменения отменяются (откат), и база днных остается в состоянии, аналогичном тому, как если бы транзакция вообще не выполнялась.
2. **Согласованность (Consistency)**: Транзакция должна приводить базу данных из одного согласованного состояния в другое с соблюдением всех целостностных ограничений и правил, заданных в базе данных.
3. **Изолированность (Isolation)**: Транзакции должны выполняться в изоляции друг от друга, так что результат одной транзакции не виден другим транзакциям до тех пор, пока первая не завершится. Это обеспечивает предотвращение конфликтов при одновременном доступе к данным нескольких пользователей.
4. **Долговечность (Durability)**: После успешного завершения транзакции ее результаты должны сохраняться даже в случае сбоя системы. Данные должны быть сохранены на постоянной основе и доступны после восстановления системы.
65
Что такое transaction race?
– это явление, которое может возникнуть при использовании уровня изоляции Read Committed в базе данных. Уровень изоляции Read Committed позволяет одной транзакции читать только фиксированные (зафиксированные) значения данных, но эти значения могут изменяться другими транзакциями. Гонка транзакций возникает, когда несколько транзакций конфликтуют из-за чтения и обновления одних и тех же данных, и это может привести к нежелательным результатам.
66
Как происходит transaction race? Пример:
Вот как это может происходить:
1. **Транзакция A читает данные**: Транзакция A начинает чтение данных из базы данных, например, выбирая значение столбца "X".
2. **Транзакция B обновляет данные**: В то время как транзакция A еще не завершилась, транзакция B выполняет обновление данных, изменяя значение столбца "X".
3. **Транзакция A завершается**: Транзакция A завершает свою работу, но она видит измененное значение столбца "X", которое было сделано транзакцией B.
4. **Нежелательные результаты**: Таким образом, транзакция A видит несогласованные данные, которые могут быть неправильными или недействительными для ее цели.
> Это называется "гонкой транзакций", потому что две транзакции "соревнуются" за доступ к данным, и результаты могут быть неопределенными или некорректными. Гонки транзакций могут приводить к проблемам с целостностью данных и нежелательным последствиям в вашем приложении.
>
67
Уровни изоляции транзакций?
Уровни изоляции транзакций определяют, как взаимодействуют между собой транзакции при одновременном доступе к данным в реляционных базах данных. Они определяют уровень видимости и блокировки данных между транзакциями.
–**Read Uncommitted (Чтение неподтвержденных данных)**:
–**Read Committed (Чтение подтвержденных данных) default in PG**:
–**Repeatable Read (Повторяемое чтение)**:
–**Serializable (Сериализуемое выполнение)**:
68
Проблемы, которые решаются сменой уровня изоляции транзакции?
1. **Грязное чтение (Dirty Read)**:
- Проблема: Грязное чтение возникает, когда одна транзакция читает данные, которые были временно изменены другой транзакцией и ещё не были подтверждены (зафиксированы).
2. **Неповторяющееся чтение (Non-Repeatable Read)**:
- Проблема: Неповторяющееся чтение возникает, когда одна и та же транзакция читает данные дважды и видит разные значения во втором чтении из-за изменений, внесенных другой транзакцией.
3. **Потерянное обновление (Lost Update)**:
- Проблема: Потерянное обновление происходит, когда две транзакции одновременно читают и обновляют одни и те же данные, и обновление одной из них перезаписывает изменения, внесенные другой.
4. **Фантомное чтение (Phantom Read)**:
- Проблема: Фантомное чтение возникает, когда одна транзакция выполняет запрос, который возвращает набор данных, и затем другая транзакция вставляет, обновляет или удаляет данные, что приводит к изменению результата первого запроса.
69
MVCC? Как работает?
**MVCC (Multi-Version Concurrency Control)** – это механизм управления параллельным доступом к данным в базе данных, который широко используется в PostgreSQL и других СУБД для поддержки одновременных транзакций. MVCC позволяет разным транзакциям видеть базу данных в разных “версиях” (или состояниях), что обеспечивает высокую степень изоляции и консистентности данных.
Как это работает:
1. **Создание версий данных**: Когда транзакция вносит изменения в базу данных, PostgreSQL не изменяет существующие строки, а создает новые версии строк с обновленными данными. Это позволяет другим транзакциям видеть старые версии данных, пока изменения не будут окончательно зафиксированы.
2. **Версионирование по времени**: Каждая версия строки имеет информацию о времени начала и окончания её действия. Это позволяет транзакциям видеть данные в соответствии с моментом времени начала транзакции, что обеспечивает изоляцию.
3. **Удаление устаревших данных**: PostgreSQL может автоматически удалять устаревшие версии данных, когда транзакция, которая их создала, успешно завершается (коммитится). Это помогает предотвратить накопление большого объема старых данных.
70
DeadTuples?
Относится к строкам данных, которые были удалены или обновлены, но все еще занимают место в таблице до тех пор, пока не будет выполнена команда VACUUM.
71
Vacuum и Autovacuum?
**VACUUM FULL** — это команда в PostgreSQL, которая выполняет полную очистку таблицы, освобождая неиспользуемое дисковое пространство и восстанавливая его операционной системе. В отличие от обычного VACUUM, который просто помечает "мертвые" строки (dead tuple) как доступные для перезаписи, VACUUM FULL перемещает все оставшиеся строки на новые страницы, эффективно сжимая таблицу.
**Autovacuum** отвечает за удаление старых версий строк, помеченных как удаленные и обновление статистики о распределении данных в таблицах, что помогает планировщику запросов принимать более эффективные решения.
72
Что такое SQL-инекция?
Суть атаки - введение в пользовательские данные, передаваемые через GET, POST или Cookie, произвольного SQL кода. Если ресурс уязвим, то можно с помощью такой атаки творить с пользовательской БД все что угодно
**Возможные варианты инъекций:**
1. Сворачивание условий WHERE запросов к результату всегда TRUE\
2. Присоединение к запросу результатов другого запроса с помощью оператора UNION
**Защита:**
1. Использовать параметризованные запросы - не подставлять данные в запросы напрямую, а форматировать с помощью параметров.
2. Использовать хранимые процедуры - код процедуры хранится в БД, а не в коде приложения
3. Использовать whitelist для таблиц - ограничить список допустимых значений
4. Всегда валидировать пользовательский ввод
5. Системная учетка, от имени которой производятся действия на сервере должна иметь как можно меньше прав
73
Какие подходы к оптимизации запросов?
- Оптимизация медленного запроса – это итеративный процесс, который в 2025 году строится вокруг анализа планов выполнения и структуры данных.
Вот стандартный алгоритм:
1. Анализ плана выполнения (EXPLAIN ANALYZE). Запустите запрос с командой EXPLAIN (ANALYZE, BUFFERS):
- **ANALYZE** – выполняет запрос и показывает реальное время, а не только догадки планировщика
- **BUFFERS** – показывает работу с кэшем и диском
2. Индексация. Если вы видите Seq Scan в месте, где ожидается быстрый поиск:
- B-tree индексы: создайте индексы для колонок в условиях WHERE, JOIN, ORDER BY, GROUP BY
- Составные индексы: Если поиск идет по нескольким полям
- Index-Only Scan: если в индексе есть все даные, нужные для запроса, PostgreSQL не будет обращаться к самой таблице
3. Переписывание запроса. Иногда проблема в самой структуре SQL:
- Избегайте `SELECT *`: Запрашивайте только нужные колонки, чтобы уменьшить запрос передаваемых данных
- Заменяйте подзапросы на JOIN: Коррелированные подзапросы часто медленнее, чем эффективный JOIN
- Используйте EXISTS вместо IN: Для проверки наличия записей в других таблицах EXISTS часто работает быстрее
- Фильтруйте рано: Накладывайте условиях WHERE как можно раньше, чтобы уменьшить объем данных для последующих соединений
4. Работа с данными и схемой
- Партицирование: Если таблица огромная (миллионы строк), разбейте ее на части (например, по месяцам). Это позволит базе сканировать только нужный кусок
- Обновление статистики: Выполните VACUUM ANALYZE, чтобы планировщик имел актуальные данные о распределении значений в таблице
- Типы данных: Убедитесь, что типы колонок при сравнении совпадают, иначе индекс может не сработать
74
Что такое PG Bouncer и зачем он нужен?
**PgBouncer** — это легковесный прокси-сервер для PostgreSQL, который реализует управление пулом подключений. Он используется для оптимизации работы с базой данных, особенно в сценариях с высоким числом подключений, что может быть трудно для PostgreSQL.
**Зачем нужен PgBouncer**
1. **Снижение нагрузки на сервер**: Подключения к PostgreSQL требуют значительных ресурсов, включая память и CPU. PgBouncer снижает количество одновременных подключений, сохраняя их минимально необходимыми.
2. **Повышение производительности**: За счёт повторного использования подключений уменьшается время, затрачиваемое на установление новых соединений.
3. **Управление подключениями**: PgBouncer поддерживает различные режимы пуллинга подключений, оптимизируя работу для различных типов приложений.
75
Что такое Redis?
– это высокопроизводительная система управления данными в оперативной памяти, которая часто используется как кэш, брокер сообщений или база данных.
76
Redis. Основные характеристики:
**1. Хранение в памяти**
- Все данные находятся в RAM
- Обеспечивает сверхбыстрый доступ
- Опционально может сохранять данные на диск для надежности
**2. Структуры данных**
Strings → Простые строки или числа
Lists → Упорядоченные наборы строк
Sets → Неупорядоченные наборы уникальных строк
Hashes → Хеш-таблицы пар ключ-значение
Sorted Sets → Упорядоченные множества с рейтингом
**3. Атомарные операции**
- Все операции выполняются последовательно
- Нет риска повреждения данных при параллельном доступе
- Поддержка транзакций
77
Redis. Как работать со строками?
# Установка значения
Работа со строками (Strings):
**INCR key:** Увеличивает числовое значение ключа на 1.
**DECR key: **Уменьшает числовое значение ключа на 1.
**APPEND key value: **Добавляет значение к существующей строке.
**STRLEN key:** Возвращает длину строки.
78
Redis. Как работать с ключами?
Работа с ключами (Key Management):
**SET key value:** Устанавливает значение для ключа.
**GET key:** Получает значение ключа.
**DEL key [key ...]:** Удаляет один или несколько ключей.
**EXISTS key:** Проверяет, существует ли ключ (возвращает 1 или 0).
**KEYS pattern:** Поиск ключей по шаблону.
**TYPE key: **Возвращает тип данных, хранящихся в ключе.
**EXPIRE key seconds:** Устанавливает время жизни (TTL) для ключа в секундах.
**TTL key:** Показывает оставшееся время жизни ключа.
**FLUSHALL: **Удаляет все данные из текущей базы данных.
79
Redis. Как работать с хэшами?
Работа с хешами (Hashes) — полезно для объектов:
**HSET key field value: **Устанавливает поле в хеше.
**HGET key field:** Получает значение поля в хеше.
**HGETALL key:** Получает все поля и значения хеша.
80
Redis. Как работать со списками?
Работа со списками (Lists):
**LPUSH key value: **Добавляет элемент в начало списка.
**RPUSH key value: **Добавляет элемент в конец списка.
**LPOP key:** Удаляет и возвращает первый элемент списка.
**LRANGE key start stop:** Получает диапазон элементов списка
81
Redis. Какие типичные сценарии использования?
Типичные сценарии использования Redis:
- Кэширование данных
- Управление сессиями
- Очереди сообщений
- Pub / Sub
- Таблицы лидеров и счетчики
- Геопространственные данные
- Ограничение частоты запросов (Rate Limiting)
82
В чем основное отличие Redis от традиционных баз данных?
Redis хранит данные в памяти, что обеспечивает более быстрый доступ,
но требует больше RAM и может терять данные при отключении питания
(если не настроена персистентность).
83
Какие структуры данных поддерживает Redis?
Strings, Lists, Sets, Hashes, Sorted Sets
Каждая структура имеет свои специфические команды и применения
84
Что такое TTL и зачем он нужен?
TTL - это время жизни ключа. Используется для автоматической
очистки устаревших данных (например, кэша или сессий)
85
Redis. Как использовать осмысленные имена ключей?
```redis
# Хорошо
SET user:1234:session "abc123"
Плохо
SET u1234s "abc123"
```
86
Redis. Как устанавливать TTL для временных данных?
`SET temp:data "value" EX 3600 # Удалится через час`
87
Redis. Как использовать транзакции для сложных операций?
Транзакции в Redis (команды MULTI/EXEC) позволяют объединить группу команд в один атомарный блок, гарантируя последовательное выполнение без вмешательства других клиентов. Для сложных операций, требующих проверки данных, используйте WATCH для оптимистичной блокировки, что предотвращает конфликты и гарантирует целостность данных
```redis
WATCH balance_user1
GET balance_user1
# (В приложении проверяем, что баланс > 100)
MULTI
DECRBY balance_user1 100
INCRBY balance_user2 100
EXEC
```
88
Python для Собесов
flashcards
Decks in class (5)
# Cards
