Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

DE > SQL > Flashcards

SQL Flashcards

1
Q

Что такое партиционирование таблиц в SQL, какие могут быть проблемы при выборе ключа (по какому алгоритму будут выбираться партиции и какому атрибуту), и доводилось ли вам с ними работать?

Теги: #ЛигаЦифровогоИнтернета

A
  • Что такое партицироване: Это техника, которая позволяет разбить таблицу на несколько физических или логических разделов. Каждая такая разделенная часть называется партицией. Разделение может основываться на различных критериях или ключах, таких как диапазон значений столбца, хеш-значение, список значений и т. д. Существует два основных варианта партиционирования в SQL — горизонтальное и вертикальное. Хорошей практикой при выборе патриции является выбор колонки с высокой селективностью, чтобы данные равномерно распределялись по партициям.

Проблемы выбора ключа:

Партиционирование по дате
Плюсы:
* Простота понимания.
* Стабильное количество строк в партиции.
Минусы:
* Необходима регулярная поддержка (добавление новых партиций).
* Поиск по имени или ID сканирует все партиции.

Партиционирование по ID
Плюсы:
* Простота понимания.
* Стабильное количество строк в партиции.
Минусы:
* Требует добавления новых партиций.
* Поиск по имени или ID сканирует все партиции.
Партиционирование по первой букве имени
Плюсы:
* Простота понимания.
* Фиксированный набор партиций, не требует поддержки.
Минусы:
* Постоянный рост объема данных в партициях.
* Неровное распределение данных (некоторые буквы популярнее).
* Поиск по ID сканирует все партиции.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Какие существуют физические типы джойнов? Каковы их особенности

#ЛеруаМерлен #Yandex #wildberries #okko #ЛигаЦифровогоИнтернета #cобес

A
  • Nested Loop:
    o Когда использовать: Для небольших наборов данных, где одна таблица значительно меньше другой.
    o Механизм работы:
    1. Внешняя таблица (outer table): перебирается построчно.
    2. Внутренняя таблица (inner table): для каждой строки из внешней таблицы выполняется проверка всех строк внутренней таблицы на соответствие условию соединения.
    o Особенности:
    1. Не использует оперативную память
    2. Эффективен для малых объемов данных.
    3. Медленный для больших таблиц.
  • Merge Join:
    o Когда использовать: Для отсортированных данных или если сортировка может быть выполнена заранее.
    o Механизм работы:
    1. Оба набора данных должны быть отсортированы по ключам соединения. Далее два отсортированных потока данных «сливаются» (как в процессе слияния отсортированных списков):
    2. Сравниваются текущие элементы обоих потоков.
    3. Если значение из первой таблицы меньше, указатель первой таблицы сдвигается вперёд.
    4. Если значение из второй таблицы меньше, сдвигается указатель второй таблицы.
    5. Если значения равны — происходит соединение, и оба указателя сдвигаются вперёд.

o Особенности:
1. Эффективен для больших объемов данных, если они отсортированы.
2. Требует дополнительных затрат на сортировку, если данные не отсортированы.

  • Hash Join:
    o Когда использовать: Для больших наборов данных, особенно если одна из таблиц помещается в память.
    o Механизм работы:
    1. Выбирается меньшая таблица и загружается в память.
    2. Строится хеш-таблица на основе ключевого столбца соединения.
    3. Для каждой строки из второй таблицы выполняется поиск совпадений в хеш-таблице.
    o Если совпадение найдено, строки объединяются.
    o Особенности:
    1. Быстрый для неотсортированных данных.
    2. Требует выделения памяти для хеш-таблицы.
    3. Неэффективен при ограничении ресурсов памяти.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Что такое ACID и почему это важно в контексте СУБД?

Теги: #SQL #cобес

A
  • Atomicity (Атомарность): Транзакция либо выполняется полностью, либо не выполняется.
  • Consistency (Согласованность): Данные переходят из одного непротиворечивого состояния в другое.
  • Isolation (Изоляция): Результаты транзакций не влияют друг на друга до завершения.
  • Durability (Надёжность): Изменения, зафиксированные транзакцией, сохраняются даже при сбоях.

Почему ACID важен в контексте СУБД?

Обеспечивает целостность данных – данные остаются корректными даже в условиях сбоев.
Позволяет безопасно работать с параллельными транзакциями – критично для многопользовательских систем.
Предотвращает повреждение данных – особенно важно для банковских, медицинских, финансовых и других критически важных систем.
Обеспечивает восстановление данных – в случае сбоев данные остаются в целостном состоянии.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Какие существуют уровни изоляции транзакций в разных СУБД (PgSQL, Oracle, MS SQL) и в чем их суть ?

Теги: #SQL #Yandex #🐺 #cобес

A

READ UNCOMMITTED
* Самый низкий уровень изоляции.
* Допускаются «грязные» (не зафиксированные) чтения, а также все другие аномалии (non-repeatable read, phantom read).
READ COMMITTED
* Запрещены «грязные» чтения.
* Но по-прежнему возможны non-repeatable read и phantom read.
REPEATABLE READ
* Запрещены «грязные» чтения и non-repeatable read.
* Однако возможны «фантомные» чтения (появление новых строк при повторном запросе).
SERIALIZABLE
* Самый строгий уровень по стандарту.
* Исключены все три аномалии (нет dirty read, non-repeatable read, phantom read).
* Поведение эквивалентно последовательному (serial) выполнению транзакций.

  • PostgreSQL: READ UNCOMMITTED (работает как READ COMMITTED), READ COMMITTED, REPEATABLE READ, SERIALIZABLE (SSI).
  • Oracle: READ COMMITTED, SERIALIZABLE (плюс механизмы многоверсионности).
  • MS SQL: READ UNCOMMITTED, READ COMMITTED (по умолчанию), REPEATABLE READ, SERIALIZABLE, а также SNAPSHOT ISOLATION.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Что такое индексы в SQL, чем отличаются «индексирование» и «индексация», и в каких случаях они применяются?

Теги: #SQL #ЛигаЦифровогоИнтернета #wildberries

A
  • Что такое: Индекс в базе данных — это структура данных, которая хранит значения из одного или нескольких столбцов таблицы и указатели на соответствующие строки в исходной таблице, где данные фактически хранятся.
  • Отличия «индексирование» и «индексация»: «Индексирование» — процесс создания/обновления индексов; «индексация» — более общий термин, связанный с использованием индексов.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

В чём отличие JOIN от UNION?

Теги: #yandex #РСХБ #t1

A
  • JOIN: Объединяет таблицы по столбцам (строки «достраиваются» дополнительными полями).
  • UNION: Складывает результаты двух запросов (строки) друг за другом в общий набор.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

В чём разница между UNION и UNION ALL?

Теги: #yandex #РСХБ

A
  • UNION: Убирает дубликаты, требует дополнительной сортировки/сравнения.
  • UNION ALL: Возвращает все строки (включая дубли), быстрее за счёт отсутствия «чистки» дубликатов.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

В чём отличие между WHERE и HAVING в SQL?

Теги: #yandex

A
  • WHERE: Фильтрует строки до группировки.
  • HAVING: Фильтрует уже сформированные группы (используется вместе с GROUP BY).
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

В чём отличие GROUP BY и DISTINCT в SQL?

Теги: #yandex

A
  • GROUP BY: Группирует строки по заданным столбцам, позволяя применять агрегатные функции к каждой группе.
  • DISTINCT: Убирает точные дубликаты строк в результате, не выполняя при этом группировку с агрегатами.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Что такое GROUPING SETS в SQL и для чего они используются?

Теги: #yandex

A
  • Что это: это механизм в SQL, который позволяет в одном запросе получить агрегации (итоговые данные) сразу по нескольким наборам группировок.
  • Как это работает:
    1. Вы указываете GROUP BY GROUPING SETS ((столбец1), (столбец2), (столбец1, столбец2)).
    2. SQL в результате вернёт строки с агрегированными данными по каждому набору столбцов внутри скобок.
  • Для чего: Получать агрегированные результаты по разным комбинациям группировок (например, по годам, по месяцам, по обоим сразу) в одном запросе.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Каково поведение разных форм COUNT (COUNT(*), COUNT(1), COUNT(id), COUNT(‘id’), COUNT(NULL))?

Теги: #yandex

A
  • COUNT(*) и COUNT(1): Считают все строки. Результат обычно одинаков.
  • COUNT(id): Считает строки, где id не NULL.
  • COUNT(‘id’): Зависит от СУБД, обычно эквивалентно COUNT(*), так как ‘id’ — строка, не NULL.
  • COUNT(NULL): Всегда 0, поскольку NULL-значения не учитываются.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Какие инструменты вы используете (или использовали) для просмотра и анализа SQL-запросов?

Теги: #РСХБ

A
  1. PostgreSQL
    1.1. EXPLAIN / EXPLAIN ANALYZE
    * EXPLAIN показывает предполагаемый план запроса.
    * EXPLAIN ANALYZE выполняет запрос и показывает реальный план с фактическим временем выполнения.
    1.2. Графические клиенты
    * pgAdmin
    * DBeaver
    * Любые другие клиенты, позволяющие просматривать план выполнения (через EXPLAIN) и статистику.
    1.3. Системные представления и логи
    * pg_stat_activity – можно увидеть активные запросы, их статус.
    * Логи PostgreSQL (postgresql.log) – позволяют отследить долгие запросы при включённом логировании log_min_duration_statement.
    1.4. Средства профилирования и мониторинга
    * auto_explain (расширение PostgreSQL) – записывает планы запросов в лог.
    * Сторонние инструменты:
    o pgBadger – анализ логов.
    o Prometheus + Grafana – метрики и дашборды по производительности.
  2. Oracle
    2.1. EXPLAIN PLAN / DBMS_XPLAN
    * EXPLAIN PLAN FOR <запрос> – позволяет сформировать план выполнения. Результаты хранятся во временной таблице PLAN_TABLE.
    * Пакет DBMS_XPLAN (например, DBMS_XPLAN.DISPLAY) – удобный способ посмотреть и отформатировать план.
    2.2. Графические клиенты
    * SQL Developer – позволяет получать графическое представление плана, профилировать SQL.
    * DBeaver – также умеет работать с Oracle и показывать план выполнения.
    2.3. Системные представления
    * V$SQL, V$SQLAREA, V$SESSION – дают информацию по выполняющимся/выполнявшимся SQL-запросам, планам, статистике по использованию ресурсов.
    * AWR (Automatic Workload Repository) и Statspack – отчёты по производительности и «горячим» запросам.
    * OEM (Oracle Enterprise Manager) – веб-интерфейс для мониторинга, даёт доступ к подробной статистике по SQL.
    2.4. Средства профилирования
    * SQL Trace и TKPROF – инструменты для детального трейсинга и форматирования информации о выполнении запросов (показывают время выполнения, ожидания, использование ресурсов).</запрос>
  3. Microsoft SQL Server
    3.1. EXPLAIN (Display Estimated Execution Plan / Actual Execution Plan)
    * В SSMS (SQL Server Management Studio) можно включить отображение Estimated Execution Plan (план оптимизатора) и Actual Execution Plan (реальный план по факту выполнения).
    3.2. Графические клиенты
    * SQL Server Management Studio (SSMS) – основной клиент.
    * Azure Data Studio, DBeaver – альтернативные клиенты с просмотром плана.
    3.3. Логи и системные представления
    * system_health Extended Event – встроенный сбор базовых событий и ошибок.
    * Просмотр долгих запросов через Extended Events или при помощи Profiler (хотя Profiler считается устаревающим).
    * Dynamic Management Views (DMVs) (например, sys.dm_exec_query_stats, sys.dm_exec_sql_text) – позволяют анализировать планы, статистику использования CPU/памяти и т.д.
    3.4. Средства профилирования и мониторинга
    * SQL Profiler / SQL Trace – классический инструмент (хотя и рекомендуют переходить на Extended Events).
    * Query Store (начиная с SQL Server 2016) – хранит историю выполнения запросов, планы, статистику по времени/ресурсам.
    * Сторонние инструменты (Redgate, SolarWinds и т.д.) – для детального мониторинга производительности.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Напишите пример оконной функции в SQL (на ваше усмотрение).

Теги: #РСХБ

A

SELECT
employee_id,
salary,
SUM(salary) OVER (PARTITION BY department_id ORDER BY employee_id) AS running_total
FROM employees;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Какие основные параметры необходимы для подключения к базе данных

Теги: #РСХБ

A
  • host (адрес сервера)
  • port (порт подключения)
  • user (пользователь)
  • password (пароль)
  • dbname (имя базы)
  • (опционально: schema, sslmode и др.)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Что такое CTE (Common Table Expression) в SQL, каков его синтаксис и для чего применяется?

Теги: #РСХБ

A

Это виртуальная таблица создаваемая в памяти.
WITH cte_name AS (
SELECT …
)
SELECT …
FROM cte_name;
Для чего: Упрощает логику запросов, делает код чище, позволяет переиспользовать промежуточные результаты.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Есть две таблицы: tab1 (10 записей) и tab2 (100 записей). Какое минимальное и максимальное число строк может вернуть LEFT JOIN этих таблиц?

Теги: #РСХБ

A
  • Минимум: 10 (если в tab2 нет совпадений для строк tab1).
  • Максимум: 10 * 100 = 1000 (если каждая строка tab1 сопоставляется со 100 строками tab2).
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Если отчёт в базе данных начал сильно тормозить, с чего вы начнёте диагностику как DBA?

Теги: #РСХБ

A
  • Посмотреть план запроса (EXPLAIN, EXPLAIN ANALYZE).
  • Проверить индексы, обновлённость статистики, наличие блокировок.
  • Оценить нагрузку на ресурсы (CPU, RAM, диск).
  • Проверить недавние изменения в базе (структура, индексы, конфигурация).
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Каков порядок выполнения операторов в SQL

Теги: #Yandex #озон #т-банк

A

CTE
FROM/JOIN
WHERE
GROUP BY
HAVING
UNION / UNION ALL
SELECT (здесь же оконные функции)
ORDER BY
LIMIT/OFFSET

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Для чего используются операторы SAVEPOINT, ROLLBACK TO SAVEPOINT
RELEASE SAVEPOINT, SET TRANSACTION, ROLLBACK и COMMIT в SQL?

Теги: #Yandex

A
  • COMMIT – фиксирует изменения в БД, делая их постоянными.
  • ROLLBACK – отменяет все изменения в текущей транзакции.
  • SAVEPOINT – создает точку сохранения внутри транзакции.
  • ROLLBACK TO SAVEPOINT – откатывает изменения до указанного SAVEPOINT.
  • RELEASE SAVEPOINT – удаляет точку сохранения.
  • SET TRANSACTION – устанавливает параметры транзакции (изоляция, доступ).
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

В чём отличие оконных функций (window functions) от группировки (GROUP BY)?

Теги: #Yandex #озон #ЛигаЦифровогоИнтернета

A
  • Оконные функции: Позволяют вычислять агрегаты (и другие функции) «поверх» набора строк без коллапса строк в одну. Можно одновременно видеть и детальные, и агрегированные данные.
  • GROUP BY: Объединяет строки в группы, каждая группа возвращает одну строку агрегированных значений (детальные данные теряются в результате).
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Как вы обычно проводите оптимизацию SQL-запросов и в чём разница между EXPLAIN и EXPLAIN ANALYZE?

Теги: #t1

A
  • Оптимизация:
    1. Анализ плана (EXPLAIN).
    2. Проверка статистики, индексов, актуальности планов.
    3. Переписывание запроса (убираем лишние JOIN, используем правильные условия и т.д.).
    4. EXPLAIN ANALYZE для замера фактического времени выполнения.
    5. Индексы
    6. Патриции
    7. Контроль памяти
    8. Контроль раздутия
  • EXPLAIN показывает предполагаемый план и оценки затрат.
  • EXPLAIN ANALYZE запускает запрос реально и выводит фактическое время выполнения каждого шага.
  • Создание и использование индексов.
  • Переписывание запроса (оптимизация JOIN, WHERE, GROUP BY).
  • Анализ и тюнинг плана (Nested Loop vs. Hash Join и т.д.).
  • Партиционирование таблиц.
  • Настройка параметров СУБД (buffers, cache).
  • Материализованные представления (при необходимости).
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Использовали ли вы хранимые процедуры и функции в SQL? Какие у них преимущества и недостатки?

Теги: #t1

A

Функции (Functions)

Использовались для выполнения вычислений и преобразований внутри SQL-запросов.

Скалярные функции
    Расчёт бизнес-метрик: fn_calc_ltv(customer_id), fn_get_churn_rate(period)
    Конвертация данных: fn_format_date(input_date, format_mask)
    Обработка строк: fn_normalize_text(input_text), fn_remove_special_chars(text)

Табличные функции (RETURN TABLE)
    Разбор JSON: fn_parse_json_orders(json_column)
    Генерация временных диапазонов: fn_generate_date_series(start_date, end_date, interval)
    Обработка массивов: fn_split_string_to_table(input_string, delimiter)

Функции для работы с аналитическими расчётами
    fn_calc_moving_average(metric, period)
    fn_rank_customers_by_revenue(year, region)

Процедуры (Procedures)

Использовались для модификации данных, администрирования и ETL-процессов.

Процедуры для массовых операций
    sp_update_customer_status() – массовое обновление статусов клиентов.
    sp_refresh_materialized_views() – обновление агрегированных данных.
    sp_archive_old_orders() – перемещение старых данных в архивные таблицы.

Процедуры с транзакциями
    sp_transfer_funds(account_from, account_to, amount) – операция с проверкой баланса и откатом при ошибке.
    sp_bulk_insert_orders(data_batch) – загрузка данных с логированием ошибок.

Процедуры для ETL
    sp_load_daily_sales_data() – загрузка ежедневных продаж из внешних источников.
    sp_clean_temp_tables() – очистка временных таблиц по расписанию.
    sp_log_etl_run(job_name, status, message) – запись статуса выполнения ETL-процесса.

Процедуры для администрирования
    sp_rebuild_indexes() – перестроение индексов.
    sp_analyze_table_statistics() – обновление статистики для оптимизатора запросов.
    sp_manage_partitions() – автоматическое создание новых партиций.

Функции (Functions)
Преимущества:
1. Возвращают значение: Функции всегда возвращают одно значение (скалярное или табличное).
2. Использование в запросах: Можно вызывать в SELECT, WHERE, JOIN, ORDER BY.
3. Детерминированность: Чаще детерминированные (при одинаковом вводе — одинаковый результат).
4. Повторное использование: Отличный способ инкапсуляции повторяющейся логики.
5. Композиция: Функции можно вызывать внутри других функций.
Недостатки:
1. Ограниченный функционал: Нельзя изменять данные в БД (нет INSERT, UPDATE, DELETE).
2. Нет транзакций: Невозможно использовать COMMIT и ROLLBACK.
3. Производительность: Может быть медленнее процедур в сложных вычислениях.
4. Ограниченные побочные эффекты: Не может вызывать DDL-команды.
________________________________________
Процедуры (Procedures)
Преимущества:
1. Гибкость: Поддерживают DML (INSERT, UPDATE, DELETE) и DDL (в некоторых СУБД).
2. Транзакции: Можно использовать BEGIN TRANSACTION, COMMIT, ROLLBACK.
3. Несколько выходных параметров: Поддержка OUT и INOUT параметров.
4. Оптимизация логики: Удобны для сложных бизнес-правил и пакетной обработки данных.
Недостатки:
1. Нет возврата значений напрямую: Используются выходные параметры или курсоры.
2. Нельзя использовать в SELECT: Процедуру нельзя вызвать внутри SQL-запроса.
3. Менее читаемы в запросах: Сложнее интегрировать с простыми SQL-операциями.
4. Повышенная сложность: Более громоздкий синтаксис для простой логики.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Каким одним словом в SQL можно убрать дубликаты?

Теги: #t1

A

DISTINCT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

На какой слой DWH (Data Warehouse) обычно загружаются инкрементальные данные ?

Теги: #t1

A

Обычно staging (промежуточный слой).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
26. На каком слое хранилища данных (DWH) обычно хранятся суррогатные ключи? Теги: #t1
* Staging: ❌ Не создаются. * Integration/Core DWH: ✅ Создаются и управляются. * Data Marts: ✅ Используются для связей, могут быть скрыты от пользователей.
26
Что такое FRAME (рамка) в оконных функциях SQL и какие есть типы фреймов? Теги: #t1
* FRAME определяет набор строк (окно) относительно текущей строки (например, «от начала группы до текущей строки»). * Основные типы: * ROWS (по количеству строк), * RANGE (по логическому диапазону значений) С указанием `order by` окно по умолчанию – `range between unbounded preceding and current row` Без указания `order by` окно по умолчанию – `rows between unbounded preceding and unbounded following` Rows – фиксированное количество строк, range – заданный через условие диапазон.
27
Что можно увидеть в плане выполнения запроса (EXPLAIN-план)? Теги: #wildberries
* Метод соединения (Nested Loop, Hash, Merge). * Используемые индексы. * Оценку количества строк. * Прогноз затрат (cost). * Последовательность операций (таблицы, подзапросы).
28
Как работает хеш-джоин (Hash Join) при FULL JOIN? Сколько хеш-таблиц строится? Теги: #wildberries
При FULL JOIN с использованием Hash Join строится две хеш-таблицы – по одной для каждой входной таблицы. Процесс работы: Строятся хеш-таблицы по обеим таблицам на основе ключевого поля. Выполняется сопоставление (probe) по хеш-значениям для поиска совпадений. Возвращаются: Совпадающие строки из обеих таблиц. Левые строки без совпадений (NULL справа). Правые строки без совпадений (NULL слева). Таким образом, требуется больше памяти, чем при LEFT или INNER JOIN, так как хранятся обе таблицы
29
Что такое идемпотентность в контексте SQL? Теги: #wildberries
Идемпотентность в контексте SQL — это свойство операции, при котором многократное выполнение запроса приводит к тому же результату, что и однократное. Идемпотентные операции: 1. SELECT — всегда идемпотентен (чтение данных без изменения). 2. DELETE FROM table WHERE id = 5 — идемпотентен, так как повторное выполнение не изменит результат (если запись уже удалена). 3. UPDATE table SET status = 'active' WHERE id = 1 — идемпотентен, если значение остаётся неизменным при повторном выполнении. Неидемпотентные операции: 4. INSERT INTO table (id, name) VALUES (1, 'John') — неидемпотентен, если нет проверки дубликатов (может вызвать ошибку или создать дубли). 5. UPDATE table SET counter = counter + 1 — неидемпотентен, так как каждое выполнение изменяет состояние.
30
Какие основные виды JOIN существуют в SQL и как они работают? Теги: #ozon #Yandex #cобес
* INNER JOIN: возвращаются только совпадающие строки. * LEFT JOIN: все строки из левой таблицы + совпадения из правой. * RIGHT JOIN: все строки из правой таблицы + совпадения из левой. * FULL JOIN: все строки из обеих таблиц (совпавшие и несовпавшие). * CROSS JOIN: декартово произведение (каждая строка левой ко всем строкам правой).
31
Если в таблице есть дубликаты, как они отразятся на результате INNER JOIN? Теги: #озон
* Каждый дубль при совпадении условием JOIN даёт дополнительную строку в результате (дубли на входе → дубли в выходе).
32
Какие существуют агрегатные и оконные функции в SQL, и в чём их ключевые отличия? Теги: #озон
* Агрегатные (SUM, COUNT, AVG, MIN, MAX): сводят группу строк в одну строку результата, используются с GROUP BY. * Оконные (RANK, ROW_NUMBER, LAG и др.): работают «поверх» набора строк без схлопывания (каждая строка сохраняется, а функция вычисляется с учётом фрейма).
33
Какие существуют виды оконных функций в SQL? Теги: #т-банк
* RANK / DENSE_RANK / ROW_NUMBER — для нумерации строк и определения положения в наборе. * LAG / LEAD — дают доступ к «предыдущей» или «следующей» строке. * FIRST_VALUE / LAST_VALUE — значения из первой/последней строки фрейма. * SUM, AVG, MIN, MAX, COUNT (в оконном контексте) — агрегаты внутри окна.
34
Две таблицы: 10 и 100 строк. FULL JOIN. Какое минимальное и максимальное количество строк может получиться на выходе? Теги: #т-банк
* Минимум: 100 (если все 10 строк из первой включены в эти 100, и нет «уникальных» строк, кроме этих десяти). * Максимум: 1000 (все одинаковое)
35
В каком виде JOIN перенос условия из WHERE в ON не влияет на результат? Теги: #т-банк
* В INNER JOIN (не влияeт, так как строки без совпадений всё равно отбрасываются).
36
Может ли ORDER BY изменить количество строк, возвращаемых запросом, если нет дополнительных условий (LIMIT и пр.)? Теги: #т-банк
* Нет, ORDER BY лишь меняет порядок, но не уменьшает/увеличивает число строк.
37
Как обрабатываются NULL-значения в агрегирующих функциях (SUM, COUNT, AVG)? Теги: #Yandex
* SUM/AVG: Игнорируют NULL (не учитывают в сумме или количестве для среднего). * COUNT(col): Считает только ненулевые значения. * COUNT(*): Считает все строки, независимо от NULL.
38
Что такое VIEW (представление) в SQL? Теги: #ЛигаЦифровогоИнтернета
VIEW (представление) в SQL — это виртуальная таблица, созданная на основе результата выполнения запроса SELECT. Она не хранит данные физически, а отображает данные из одной или нескольких таблиц в удобной форме. Особенности: * Обновляется автоматически при изменении исходных таблиц. * Используется для упрощения сложных запросов, повышения безопасности (ограничение доступа к данным) и улучшения читаемости.
39
Где физически хранятся индексы в базе данных в разных субд (оракл пг скюл мс ссервер)? Теги: #ЛигаЦифровогоИнтернета
Oracle Database * Хранение: Индексы хранятся в сегментах данных в таблицных пространствах (tablespaces) отдельно от таблиц. * Формат: Обычно используется структура B-tree*. Также поддерживаются Bitmap-индексы и другие типы PostgreSQL * Хранение: Индексы хранятся как отдельные файлы в файловой системе внутри директории данных PostgreSQL. * Расположение: o В каталоге $PGDATA/base//. o Каждый индекс — это отдельный файл с уникальным OID (объектным идентификатором). * Формат: По умолчанию используется B-tree, но поддерживаются и другие типы (GIN, GiST, BRIN, Hash). Microsoft SQL Server * Хранение: Индексы хранятся в файлах данных (.mdf, .ndf) в структурированной форме внутри filegroup. * Формат: o B-tree для кластерных и некластерных индексов. o Кластерные индексы физически определяют порядок хранения строк в таблице.
40
Какая функция используется для «разворачивания» массива в строки в разных субд?
PostgreSQL: unnest() Oracle: TABLE() MS SQL Server: OPENJSON(), STRING_SPLIT()
41
Какая функция используется для определения объёма памяти, требуемого для хранения заданного значения в разных субд? Теги: #open
* PostgreSQL: pg_column_size(), octet_length() * Oracle: VSIZE(), LENGTHB() * MS SQL Server: DATALENGTH()
42
Какой оператор используется для массовой вставки данных? Теги: #open
* PostgreSQL: INSERT INTO ... VALUES (...), (...), (...), COPY, UNNEST * Oracle: INSERT ALL, INSERT INTO ... SELECT ..., SQL*Loader * MS SQL Server: INSERT INTO ... VALUES (...), (...), (...), BULK INSERT
43
Какова асимптотическая сложность поиска в хеш-таблице и почему на практике это может быть не всегда одинаковая? Теги: #okko
переработать. Теоретически O(1) при равномерном распределении. На практике возможны коллизии, переполнение, цепочки, поэтому может деградировать до O(n) Коллизии – когда разные ключи хешируются в одно и то же место. Если применяется метод цепочек (separate chaining) – поиск может занять O(k), где k — длина списка в данной ячейке. В худшем случае, когда все элементы попадают в один бакет, сложность становится O(n). При методе открытой адресации (open addressing) поиск может занять до O(n) в худшем случае из-за линейного пробинга или квадратичного пробинга. Переполнение таблицы – если коэффициент загрузки (load factor) становится слишком высоким, увеличивается число коллизий. Это приводит к увеличению длины цепочек в методе цепочек или к более длинному поиску свободного места в открытой адресации. Неравномерная хеш-функция – плохая хеш-функция может сгруппировать ключи в небольшом количестве бакетов, приводя к увеличению количества сравнений.
44
На каких структурах данных обычно строятся индексы в разных субд)? Теги #cобес
B-Tree (B-дерево) Hash (Хеш-таблицы) GiST (Generalized Search Tree) GIN (Generalized Inverted Index) BRIN (Block Range Index) Bitmap индексы R-Tree (для пространственных данных) Columnstore XML
45
45. Как можно принудительно сделать выбор того или иного вида физического джойна в разных субд теги: #ЛигаЦифровогоИнтернета
* В некоторых СУБД (Oracle, MS SQL) — с помощью Hints (например, HASH JOIN, MERGE JOIN). * В PostgreSQL — выключая/включая соответствующие параметры (enable_hashjoin, enable_mergejoin и т.п.) или используя планировочные настройки.
46
расскажи про архитектуру постгре sql #Я
1. Клиент (способы взаимодействия пользователя с БД) Первый уровень архитектуры PostgreSQL – это клиент, посредством которого человек или приложение взаимодействует с базой данных. Существует множество клиентских инструментов и библиотек, среди которых: psql – консольная утилита, позволяющая напрямую отправлять SQL-запросы на сервер PostgreSQL. pgAdmin – графический интерфейс для управления и администрирования серверов и баз данных PostgreSQL. Приложения на разных языках программирования (Java, Python, C#, Go и т.д.), которые используют драйверы (JDBC, psycopg2, Npgsql, pgx, и т.д.). Когда пользователь или приложение устанавливают соединение с сервером PostgreSQL (обычно по протоколу TCP на порту 5432), начинается создание «сессии». 2. Сессия (установление соединения) После того как клиент обращается к PostgreSQL, Postmaster (главный управляющий процесс, часто называемый просто postgres) принимает запрос на новое соединение. Если аутентификация (пароль, сертификаты и т. п.) проходит успешно, Postmaster создаёт backend-процесс. Этот бекэнд (backend) будет «представителем» конкретного клиента на серверной стороне, обрабатывая запросы и возвращая результаты. Таким образом, для каждого активного подключения к серверу существует отдельный серверный процесс (backend). Этот подход – ключевая особенность архитектуры PostgreSQL (многопроцессная модель). 3. Серверная часть (основная логика PostgreSQL) Когда соединение установлено, все SQL-команды клиента передаются в его backend-процесс, который: Принимает запрос. Анализирует/разбирает (parse) и планирует (plan) запрос (если план не закэширован). Выполняет (execute) запрос, обращаясь к памяти и к файлам на диске. Возвращает результаты клиенту. Backend-процесс (сессия) Каждый backend ведёт транзакции, обрабатывает операторы SELECT, INSERT, UPDATE и пр., а при необходимости взаимодействует с общей и локальной памятью, а также с фоновыми процессами. Фоновые процессы (Background processes) Помимо backend-процессов, PostgreSQL запускает ряд фоновых (background) процессов, которые обслуживают систему в целом. Основные из них: WAL Writer: записывает данные из буфера WAL (Write-Ahead Logging) на диск (в файлы WAL) для обеспечения надёжности транзакций. Background Writer: помогает сбрасывать «грязные» (изменённые) страницы из памяти на диск проактивно, разгружая моменты интенсивной записи. Checkpointer: периодически устанавливает «точку сохранения» (checkpoint) в журнале, сбрасывая изменённые страницы на диск и гарантируя, что в случае сбоя система начнёт восстановление с этой точки. Autovacuum: автоматически запускает процесс VACUUM и ANALYZE для удаления устаревших версий строк (из-за MVCC) и сбора статистики оптимизатора. Logger (syslogger): записывает сообщения журнала (логов) в файлы. И другие (stats collector, logical replication launcher и т. д.), в зависимости от конфигурации. 4. Память: Shared Memory и Local Memory Shared Memory (общая память) Это выделенная при запуске сервера область памяти, к которой могут обращаться все процессы PostgreSQL (включая backend’ы и фоновые процессы). В ней хранятся: Shared Buffers: кэш для страниц таблиц и индексов, загружаемых с диска. Позволяет ускорять повторные чтения и записи. WAL Buffers: буфер для журнала транзакций (WAL), куда сначала складываются все изменения данных, прежде чем будут зафиксированы на диске. Lock Table / Semaфор: структуры для организации межпроцессной синхронизации (блокировки, семафоры и т.д.). Объём shared_buffers обычно один из важнейших параметров настройки производительности PostgreSQL. Local Memory (локальная память) Помимо общей памяти, у каждого backend-процесса есть своя локальная память, где хранятся: work_mem: память под сортировки, хеш-операции (если не хватает, идёт на диск) maintenance_work_mem: VACUUM, CREATE INDEX и т. п. temp_buffers: для временных таблиц в конкретной сессии 5. Хранилище данных Понятие кластера В PostgreSQL «кластером» называют совокупность всех баз данных, обслуживаемых одним серверным инстансом (то есть одним запущенным экземпляром postgres). При инициализации (командой initdb) в файловой системе создаётся каталог, часто именуемый «Data Directory» или PGDATA, в котором и хранится всё необходимое для работы кластера: Файлы конфигурации. Подкаталоги с данными. Файлы журналов (WAL) и т. д. Важно: Один кластер PostgreSQL может содержать в себе несколько баз данных. Структура каталога PGDATA и что в нём хранится Чаще всего в каталоге PGDATA мы увидим: postgresql.conf – основной конфигурационный файл, где задаются параметры (shared_buffers, wal_level, listen_addresses и т. д.). pg_hba.conf – файл настроек аутентификации и сетевого доступа (Host-Based Authentication). pg_ident.conf – сопоставления системных и PostgreSQL-ролей (при необходимости). base/ – подкаталог со служебными поддиректориями, где находятся файлы «по умолчанию» используемого табличного пространства. global/ – информация о самом кластере, файлах контрольной информации, системных каталогах. pg_wal/ (ранее pg_xlog) – журнал транзакций (WAL файлы). pg_stat/, pg_stat_tmp/, pg_subtrans/ и др. – служебные подкаталоги для статистики, субтранзакций и прочего. pg_tblspc/ – символические ссылки на другие табличные пространства (если они используются). Табличные пространства (Tablespaces) В PostgreSQL табличное пространство – это логический объект, указывающий на конкретную директорию в файловой системе, где хранятся файлы базы/таблиц/индексов. По умолчанию все объекты размещаются в пространстве pg_default (физически это поддиректория внутри PGDATA), однако администратор может создавать собственные табличные пространства на разных дисках/разделах для распределения нагрузки или оптимизации. 6. Понятие базы данных и схемы в PostgreSQL База данных Логическая единица В PostgreSQL одна база данных — это независимый набор схем (schemas) и объектов (таблицы, индексы и т. д.), обособленный от других баз в том же кластере. Размещение Физически каждая база данных хранится в подкаталоге base/ внутри каталога данных (PGDATA). Несколько баз в одном кластере В каждом кластере PostgreSQL по дефолту есть postgres, template1, template0. Внутри одного кластера может быть несколько баз данных (databases). Каждая БД хранится в отдельных физических файлах, но все они управляются единым постмастером и общими конфигурациями. Схема (Schema) В каждой базе данных есть набор схем (по умолчанию создаётся схема public). Схема – это логическая область имён для объектов: таблиц, представлений, функций, индексов, последовательностей и т. д. Иными словами, внутри одной базы может существовать много схем, чтобы группировать объекты по логическим частям. Основные объекты внутри схемы Таблицы и Индексы Представления (Views) Функции (Functions) / Хранимые процедуры Синонимы, последовательности (Sequences) Триггеры, Ограничения и т. п. 7. Каталог PostgreSQL (System Catalog) PostgreSQL хранит информацию о самих объектах базы данных (таблицах, столбцах, индексах и т. д.) в системном каталоге. Это набор служебных таблиц и представлений (например, pg_class, pg_attribute, pg_type, pg_index и многие другие). Любая DDL-операция (CREATE, DROP, ALTER) меняет записи в системном каталоге, а также создаёт/удаляет соответствующие файлы или структуры. Работа с системным каталогом обычно скрыта от пользователя – для получения метаданных можно использовать служебные команды (например, \d в psql) или вьюхи-обёртки (information_schema, pg_catalog.*). 8. Ключевые файлы конфигурации В каталоге PGDATA находится несколько основных конфигурационных файлов: postgresql.conf – глобальные настройки сервера (порты, пути, лимиты памяти и т. д.). pg_hba.conf – настройки клиентской аутентификации и доступа по сети (определяет, кто и откуда может подключаться). pg_ident.conf – сопоставление системных пользователей и ролей PostgreSQL (используется реже). Изменения в postgresql.conf могут потребовать перезапуска или посыла сигнала конфигурации (SIGHUP). Некоторые параметры (например, размеры памяти) меняются только при перезапуске. 9. Архитектурная схема (как всё работает вместе) Подведём итог, описывая общий «поток» работы: Клиент (psql, pgAdmin или приложение) подключается к серверу по TCP (порт 5432). Postmaster (главный управляющий процесс) аутентифицирует соединение и порождает backend-процесс. Backend обслуживает запросы данного клиента: анализирует SQL, получает или модифицирует данные. Для выполнения операций, backend пользуется: Shared Memory (где кэшируются данные и хранятся блоки WAL), Local Memory (локальные структуры для плана запроса, временных данных). Записи о всех изменениях сначала попадают в WAL (pg_wal). Параллельно фоновые процессы (WAL Writer, Checkpointer, Background Writer) обеспечивают надёжную и эффективную запись на диск. Данные хранятся внутри каталога PGDATA, который представляет собой кластер PostgreSQL. В нём есть поддиректории для баз, служебных файлов и сам журнал WAL. При желании администратор может использовать табличные пространства, ссылаясь на внешние каталоги. Внутри кластера находятся несколько баз данных, каждая из которых может содержать несколько схем, а внутри схем – таблицы, индексы, функции и прочие объекты. Все метаданные об этих объектах хранятся в системном каталоге (pg_catalog). Autovacuum периодически «убирает» (убивает устаревшие версии строк) и собирает статистику, что важно для многоверсионной природы PostgreSQL (MVCC). Таким образом, архитектура PostgreSQL состоит из чёткого разделения: Клиентский уровень (где пользователь/приложение отправляет запросы). Серверный уровень (postmaster, процессы-бекэнды и фоновые процессы). Область общей памяти (Shared Memory) и локальной памяти (Local Memory). Физическое хранилище (директория PGDATA, табличные пространства). Всё это вместе образует надёжную и расширяемую систему управления базами данных, обеспечивая транзакционность (ACID), многоверсионность (MVCC) и достаточно гибкие возможности администрирования. Коротко о главном Клиент устанавливает соединение → запускается backend-процесс. Backend обрабатывает запросы, используя Shared Memory (кэши, WAL) и свою Local Memory. Фоновые процессы (Background Writer, WAL Writer, Autovacuum и др.) поддерживают систему «в форме». Все данные кластера PostgreSQL физически размещаются в PGDATA, состоящей из конфигурационных файлов, подкаталогов с базами, журналом транзакций (pg_wal) и системных служебных структур. Внутри одного кластера – несколько баз данных, каждая из них может иметь множество схем. Системный каталог (pg_catalog) хранит информацию обо всех объектах. Эта многоуровневая архитектура и есть «основа» PostgreSQL.
47
расскажи про архитектуру Орак sql #Я
1. Клиент (способы взаимодействия пользователя с БД) Пользователи и приложения могут взаимодействовать с Oracle Database через различные инструменты и библиотеки: SQL*Plus – консольная утилита для отправки SQL и PL/SQL команд. SQL Developer, Toad, PL/SQL Developer и др. – графические клиенты для администрирования и разработки. Приложения (Java, .NET, Python и т.п.) используют соответствующие драйверы (JDBC, ODP.NET, cx_Oracle и др.). Для установления связи используется сетевой протокол Oracle Net (ранее назывался SQL*Net), который обычно работает через «листенер» (Listener) – отдельную службу, принимающую подключения. 2. Сессия (установление соединения) Когда клиентское приложение или утилита пытается подключиться к базе данных: Listener (процесс tnslsnr) на стороне сервера принимает запрос на подключение. Если аутентификация (логин/пароль/сертификаты и т. п.) проходит успешно, Listener либо: Создаёт или перенаправляет к dedicated server process (в режиме «один серверный процесс на одно подключение»), Или подключает клиента к shared server process через диспетчеры (Dispatcher) — если настроен режим Shared Server. Таким образом, формируется сессия. С этого момента приложение может отправлять SQL/PLSQL-запросы, а серверный процесс будет их обрабатывать. 3. Серверная часть (основная логика Oracle) Server Process (Dedicated или Shared) Server Process (или server-side process) – это процесс на стороне сервера, который фактически выполняет от имени клиента: парсинг SQL-запросов, выполнение команд (SELECT, INSERT, UPDATE и т. п.), взаимодействие с памятью (SGA/PGA) и с файлами данных. В режиме Dedicated для каждой пользовательской сессии обычно создаётся свой процесс. В режиме Shared (Shared Server Architecture) несколько пользовательских сеансов обслуживаются пулом общих серверных процессов, а «Dispatcher»-процессы распределяют запросы клиентов между этими серверами. Фоновые процессы (Background Processes) Помимо основных серверных процессов, Oracle запускает целый ряд фоновых (background) процессов. Ключевые из них: DBWn (Database Writer) – записывает изменённые (dirty) блоки из буфера данных (Buffer Cache) на диск (в datafiles). LGWR (Log Writer) – записывает записи изменений (redo entries) из Redo Log Buffer в файлы Redo Log. CKPT (Checkpoint) – фиксирует контрольные точки (checkpoint) в контрольных файлах (Control Files) и инициирует запись метаданных о прогрессе. SMON (System Monitor) – восстанавливает базу (instance recovery) после сбоев, очищает временные сегменты. PMON (Process Monitor) – следит за состоянием пользовательских процессов, освобождает «зависшие» ресурсы и транзакции, если сессия внезапно оборвалась. ARCn (Archiver) – копирует заполненные Redo Log-файлы в архивные логи (archive logs) при использовании режима ARCHIVELOG. RECO, MMAN, MMNL, MMON и другие – служебные процессы, отвечающие за распределение памяти, мониторинг, управление кластером (RAC) и т. д. 4. Память: SGA и PGA SGA (System Global Area) SGA – это общая область памяти, доступная всем фоновым и серверным процессам, запущенным в рамках одного экземпляра (instance) Oracle. Включает несколько ключевых подпулов: Database Buffer Cache: кэширует блоки данных, считанные с диска (таблицы, индексы и т.д.). Shared Pool: хранит кэш скомпилированных запросов (кэш SQL/PLSQL), библиотечные кэши и метаданные. Redo Log Buffer: буферизирует записи изменений (redo entries) перед их записью в файлы Redo Log. Large Pool, Java Pool, Streams Pool (опционально): используются для определённых операций (бэкапы RMAN, параллельные запросы, Java-объекты, репликация). Размеры SGA задаются параметрами конфигурации Oracle (как вручную, так и с помощью Automatic Memory Management). PGA (Program Global Area) PGA – индивидуальная область памяти для каждого серверного или фонового процесса, в которой хранятся: Данныe для сортировки, хеш-соединений, буферизации курсоров. Локальные стеки вызовов PL/SQL-функций и т. д. При включённом Automatic PGA Management сервер Oracle динамически выделяет/освобождает память в PGA в зависимости от нагрузки. 5. Хранилище данных Instance (экземпляр) – совокупность всех процессов Oracle + SGA (общая память). Database (база данных) – физические файлы на диске (datafiles, control files, redo log files). Когда мы «поднимаем» Oracle, мы монтируем (mount) и открываем (open) базу данных, тем самым запуская instance и подключая её к набору физических файлов. В случае RAC (Real Application Clusters) несколько инстансов (каждый со своей SGA и процессами) могут обслуживать одну физическую базу данных. База данных В Oracle Database понятие «база данных» (Database) отличается от некоторых других СУБД (например, PostgreSQL или MS SQL Server). В Oracle база данных — это физическое хранилище данных, включающее файлы на диске, а также связанные с ними контрольные и журнальные файлы. В классическом Oracle Database (до версии 12c) внутри одного инстанса может быть только одна база данных (Database). В отличие от PostgreSQL, где один сервер (PostgreSQL Instance) может содержать много баз данных, в Oracle всё хранится в единой базе, но организовано с помощью схем (Schemas). Instance и Database: два ключевых понятия Что изменилось в Oracle 12c+? (Контейнерная архитектура CDB/PDB) Начиная с Oracle 12c, в Oracle появилась многобазовая архитектура (Multitenant Architecture), где один инстанс может содержать несколько баз данных (PDBs — Pluggable Databases). Теперь у нас есть Container Database (CDB) – основная база, которая содержит Pluggable Databases (PDB). PDB – это отдельная логическая база данных, похожая на базу в PostgreSQL. Внутри PDB могут быть свои схемы, и они полностью изолированы. Ранее схожая функциональность достигалась через Schemas, но теперь PDB позволяет более гибко разделять данные, например, для SaaS-систем. Основные файлы Datafiles (файлы данных) – хранят собственно таблицы, индексы, другие объекты. Redo Log Files – журналы изменений (redo logs), куда LGWR пишет все операции, необходимые для восстановления. Control Files – содержат метаданные о структуре базы данных: список datafiles, логи, текущее состояние SCN (System Change Number) и т. д. Tempfiles – для временных сегментов (операции сортировки, хеш-соединения). Archived Redo Logs – архивированные логи изменений, используемые для восстановления в режиме ARCHIVELOG. Табличные пространства (Tablespaces) Логический уровень хранения в Oracle – это табличное пространство (tablespace). Каждое табличное пространство ссылается на один или несколько datafiles. Основные из них: SYSTEM и SYSAUX – системные табличные пространства, где лежат служебные объекты (каталоги, метаданные). UNDOTBS – табличное пространство для UNDO-сегментов (хранит старые версии данных для реализации транзакционной целостности и MVCC). TEMP – для временных данных (сортировки). USERS, DATA, INDEX (или любые другие пользовательские) – рабочие схемы для таблиц и индексов. 6. Организация схемы, пользователей и объектов В Oracle принципиально каждый пользователь (User) является и схемой. То есть создание пользователя USER_A означает появление схемы USER_A, в которой могут находиться таблицы, индексы, процедуры и другие объекты. Основные системные пользователи: SYS – «суперадмин», владелец базовых системных объектов. SYSTEM – тоже админ, но с чуть более ограниченными правами, чем SYS. Объекты внутри схемы Таблицы и Индексы, Представления (Views), Синонимы (Synonyms), Пакеты (Packages), Хранимые процедуры (Stored Procedures), Функции, Последовательности (Sequences), Триггеры и т.д. 7. Системный каталог (Data Dictionary) В Oracle информация обо всех объектах базы данных (таблицах, индексах, правах доступа и т. д.) хранится в системных таблицах (Data Dictionary). Для пользователей предусмотрены представления (views) над этим Data Dictionary: USER_* – объекты, принадлежащие текущему пользователю. ALL_* – объекты, доступные текущему пользователю (в его схеме и в чужих). DBA_* – объекты, видимые администратору (вся база данных целиком). Например, DBA_TABLES, ALL_TABLES, USER_TABLES и т. п. – представления, позволяющие увидеть списки таблиц. 8. Ключевые файлы конфигурации Основные конфигурационные файлы Oracle находятся вне самих datafiles и располагаются в «Oracle Home» (директории установки). Некоторые из ключевых: Initialization Parameter File: init.ora (текстовый вариант) или spfile.ora (серверная бинарная версия). Здесь задаются параметры SGA, PGA, имена баз, пути к файлам и пр. listener.ora – определяет, как работает Listener (порт, протокол, база и прочее). tnsnames.ora – позволяет клиентам (и иногда самому серверу) находить нужные сервера БД по «tns-именам». sqlnet.ora – общие настройки Oracle Net, шифрования, параметров сети. 9. Общая архитектурная схема (как всё работает вместе) Клиент (SQL*Plus, SQL Developer или приложение) запрашивает соединение, используя Oracle Net и «tnsnames.ora». Listener на стороне сервера принимает подключение. Если аутентификация успешна, создаётся Session, которую обслуживает Dedicated Server Process либо Shared Server через Dispatcher. Server Process взаимодействует с SGA, где хранится буфер кэш данных (Buffer Cache), кэш SQL запросов (Shared Pool), а записи изменений попадают в Redo Log Buffer. При выполнении SQL-операций, серверный процесс читает/записывает нужные блоки данных. Изменённые блоки сначала лежат в Buffer Cache, а записи redo «ждут» в Redo Log Buffer. LGWR (Log Writer) периодически (или при коммите) сбрасывает redo в Redo Log Files. DBWn (Database Writer) в нужный момент записывает сами блоки в Datafiles. SMON и PMON поддерживают целостность, очищают временные объекты, восстанавливают после сбоев. CKPT (Checkpoint) отмечает контрольные точки в Control Files, фиксируя метаданные о прогрессе записи. ARCn (Archiver) при включённом ARCHIVELOG-моде копирует устаревшие Redo Logs в архив. Все физические файлы (datafiles, control files, redo logs, tempfiles) вместе образуют Database, а совокупность процессов и памяти (SGA/PGA) – это Instance. Внутри базы мы имеем Табличные пространства (SYSTEM, SYSAUX, USERS и др.), а в них — схемы (каждая схему владельца-пользователя). Data Dictionary (системные таблицы) содержит информацию обо всех объектах, правах и состояниях внутри базы. Короткое резюме Клиент → запрашивает соединение у Listener. Listener → формирует сеанс (Session) и «привязывает» его к Dedicated или Shared серверному процессу. Server Process → выполняет SQL-запросы, используя SGA (общий кэш, журнал, метаданные) и свою собственную PGA (локальные структуры, сортировки). Фоновые процессы (DBWn, LGWR, CKPT, SMON, PMON, ARCn и пр.) обеспечивают надёжность, поддержку транзакций, восстановление, ведение журнала и архивирование. Instance (процессы + память) и Database (файлы) вместе образуют работающую систему. Табличные пространства содержат Datafiles, где фактически лежат данные. У каждого пользователя (User) есть собственная схема, которая может содержать таблицы, индексы, представления и другие объекты. Системный каталог (Data Dictionary) аккумулирует метаданные о всех объектах, лежащих в базе. Эта многоуровневая архитектура Oracle даёт высокую производительность, гибкость (RAC, Data Guard) и богатый функционал для корпоративных сценариев.
48
расскажи про архитектуру мс сервера #Я
1. Клиент (способы взаимодействия) Пользователи и приложения работают с SQL Server через разные инструменты и драйверы: SQL Server Management Studio (SSMS) – основная консольная/графическая среда администрирования от Microsoft. sqlcmd – консольная утилита, позволяющая запускать запросы T-SQL и скрипты. Различные GUI-клиенты и средства разработки (Visual Studio, Azure Data Studio, сторонние утилиты). Драйверы для языков .NET, Java (JDBC), Python (pyodbc, pymssql), PHP (SQLSRV) и т. д. По умолчанию соединения идут по TCP-порту 1433, хотя это может быть перенастроено. 2. Сессия (установление соединения) Когда клиентская программа (например, SSMS) пытается подключиться к SQL Server: SQL Server (работающий как служба sqlservr.exe на Windows или процесс/служба в Linux) слушает соответствующий порт (обычно 1433) или именованный канал (pipes). Если аутентификация (Windows Authentication или SQL Server Authentication) проходит успешно, создаётся сессия (Session). Далее в рамках этой сессии клиент получает «логический канал» для отправки запросов T-SQL и получения результатов. 3. Серверная часть (ядро SQL Server и планировщик) В отличие от PostgreSQL и Oracle, где создаются отдельные процессы (или выделяются отдельные процессы для сеансов), в Microsoft SQL Server вся логика выполняется внутри одного основного процесса/службы – sqlservr.exe. Этот процесс многопоточен и включает: SQL OS (внутренний планировщик и диспетчер ресурсов) Отвечает за управление потоками, планирование задач, синхронизацию, распределение памяти и операции ввода-вывода (I/O). Это упрощённая «микро-ОС», оптимизированная под нагрузку СУБД. Query Processor (процессор запросов) Принимает T-SQL запросы, выполняет разбор (парсинг) и оптимизацию (построение плана), а затем отдаёт на выполнение операторам (physical operators). Storage Engine (движок хранения) Управляет чтением и записью данных на диск, доступом к журналу транзакций, поддержкой индексов и страниц данных. Фактически, когда клиент инициирует SQL-запрос, внутри sqlservr.exe поднимается (или берётся из пула) один из рабочих потоков, который от имени этой сессии выполняет запрос, обращается к памяти, кэшам, журналам и файлам базы данных. 4. Память в SQL Server Buffer Pool (основной кэш) Основная часть памяти называется Buffer Pool. Он хранит закэшированные страницы данных (по 8 КБ), а также некоторые другие структуры. При чтении таблицы или индекса нужные страницы подгружаются в Buffer Pool, при обновлениях изменённые («грязные») страницы вначале остаются в кэше, а на диск сбрасываются фоновым процессом. Plan Cache (процедурный кэш) Процессор запросов кэширует скомпилированные планы (Execution Plans) в отдельном сегменте памяти, который тоже логически находится в Buffer Pool (но часто называют Plan Cache). При повторном выполнении того же запроса SQL Server может повторно использовать уже готовый план. Рабочие области (Work/Temp areas) Для временных операций (сортировки, хеш-соединения, создание индексов) SQL Server использует как оперативную память (выделяемую внутри общего пула), так и tempdb – специальную базу данных для временных объектов. 5. Хранилище данных SQL Server физически хранит данные и журналы транзакций в разных файлах: Основные файлы данных (MDF) – основной файл, в котором содержатся объекты базы данных (таблицы, индексы и пр.). Вторичные файлы данных (NDF) – могут использоваться для распределения хранения (например, на другие диски) и для больших баз. Файлы журнала транзакций (LDF) – тут хранятся все операции (WAL), позволяющие в случае сбоя восстановить целостное состояние данных. tempdb Отдельная специальная база, которая автоматически пересоздаётся при каждом перезапуске SQL Server. Используется для хранения временных таблиц, объектов, сортировок (spills), версий строк при Snapshot Isolation и т.д. 6. Понятие базы данных и схемы в Microsoft SQL Server SQL Server Instance – это один запущенный экземпляр (служба sqlservr.exe), который может содержать множество баз данных (Databases). Каждая база данных физически представлена набором файлов (mdf/ndf/ldf). База данных В Microsoft SQL Server база данных (Database) – это изолированная единица хранения данных, содержащая схемы, таблицы, индексы, процедуры и другие объекты. В отличие от PostgreSQL, где база данных – это часть кластера, в SQL Server каждая база данных существует независимо и управляется сервером. В рамках одной инсталляции SQL Server есть системные базы (master, model, msdb, tempdb) и пользовательские (созданные администратором или приложениями). master хранит важную информацию о конфигурации сервера, списке баз, логинах. msdb используется SQL Server Agent для расписания задач (jobs), хранения историй бэкапов и т. д. model – «шаблон» для вновь создаваемых баз. tempdb – временные объекты. Как устроены табличные пространства в SQL Server? (И почему их нет как в Oracle и PostgreSQL) В Microsoft SQL Server нет явного понятия "табличного пространства", как в Oracle и PostgreSQL. Вместо этого SQL Server использует файловые группы (Filegroups), которые выполняют схожую функцию. 💡 Главное отличие: В PostgreSQL и Oracle табличные пространства (Tablespaces) используются для хранения данных в определённых местах файловой системы. В SQL Server вместо этого есть файлы данных (MDF/NDF), организованные в файловые группы (Filegroups). Схема (Schema) Внутри базы данных объекты (таблицы, представления, процедуры) организованы по схемам. По умолчанию многие объекты создаются в схеме dbo (database owner). Схемы позволяют логически группировать объекты и управлять правами доступа. 7. Системный каталог (System Catalog) SQL Server хранит метаданные обо всех объектах (таблицах, столбцах, индексах, связях, правах) в системных таблицах. Однако для удобства администратора и разработчиков предоставляются служебные базы и представления: master – хранит «глобальные» сведения о базах и логинах. Resource database (скрыта) – содержит «встроенные» системные объекты. Системные представления в каждой базе – sys.tables, sys.columns, sys.indexes и т. д. INFORMATION_SCHEMA – стандартные представления для получения метаданных о таблицах, столбцах, ограничениях. 8. Ключевые файлы конфигурации В SQL Server основные настройки обычно хранятся внутри самого сервера (в системных таблицах master), а не в текстовых конфигурационных файлах. Часть параметров задаются при установке или через SQL Server Configuration Manager. Тем не менее в среде Windows/Linux могут быть: SQL Server Configuration Manager (Windows) – управляет службами, протоколами, портами, наладками сети. Registry (Windows) или конфигурационные файлы (Linux) – где могут храниться пути к папкам, некоторые параметры. Error Logs – логи при запуске/работе SQL Server, можно найти в директории, заданной настройками (по умолчанию LOG папка в каталоге установки). 9. Общая архитектурная схема (как всё работает вместе) Клиент (SSMS, sqlcmd или приложение) открывает соединение (TCP 1433 или Named Pipes). SQL Server (процесс sqlservr.exe) аутентифицирует пользователя (через Windows Auth или SQL Auth) и создаёт сессию. Все запросы T-SQL в рамках сессии попадают к Query Processor, который парсит и оптимизирует запрос, используя метаданные в системном каталоге (sys.* или INFORMATION_SCHEMA) и кэш планов (Plan Cache). Запросы к данным обрабатываются Storage Engine, который обращается к страницам (8 КБ) в Buffer Pool. При изменении данных: Все операции логируются в Transaction Log (LDF). «Грязные» страницы (dirty pages) в Buffer Pool будут позже сброшены на диск процессом Lazy Writer или контрольной точкой (Checkpoint). Для временных объектов (темп-таблицы, сортировки, версии строк при Snapshot Isolation) используется база tempdb. Системные базы (master, model, msdb, tempdb) обеспечивают инфраструктуру для хранения конфигурации, заданий (jobs), историй бэкапов, временных таблиц и т. д. Пользовательские базы данных (каждая со своими *.mdf, *.ndf, *.ldf файлами) содержат объекты, распределённые по схемам. SQL OS внутри процесса sqlservr.exe управляет потоками, взаимными блокировками (latches, locks), очередями заданий, распределением CPU, планированием контекстных переключений (cooperative scheduling). Таким образом, вся логика – от сетевого приёма соединения до операций чтения/записи – происходит внутри одного многопоточного процесса (службы). Это даёт тесную интеграцию с Windows (а теперь и с Linux) и позволяет SQL Server оптимизировать работу с системными ресурсами (память, процессоры, диски) под типичные нагрузки СУБД. Короткое резюме Клиент подключается → создаётся сессия в рамках единого процесса sqlservr.exe. Query Processor анализирует запрос, использует Plan Cache и метаданные (системный каталог) для генерации/выбора плана выполнения. Storage Engine оперирует данными, используя Buffer Pool и журнал транзакций (LDF). Фоновые и внутренние механизмы (Lazy Writer, Checkpoints, Log Writer) работают в отдельных потоках внутри того же процесса. Данные хранятся в файлах MDF/NDF (основные/вторичные) и LDF (журнал), отдельно есть база tempdb для временных операций. База данных внутри SQL Server содержит схемы (dbo, произвольные) и различные объекты (таблицы, индексы, функции, представления). Системные базы (master, msdb, model, tempdb) обеспечивают жизнедеятельность всего сервера. Так устроена многопоточная архитектура Microsoft SQL Server, которая традиционно работает на Windows, но начиная с SQL Server 2017 официально поддерживает Linux и контейнеры Docker.
49
Сравни архитектруы оракла сервера и постгреса #Я
Архитектура PostgreSQL Многопроцессная модель: для каждого подключения создаётся отдельный процесс (backend). Есть главный процесс (Postmaster) и фоновые процессы (WAL Writer, Checkpointer, Autovacuum и др.). Общая память (Shared Memory) + локальная память каждого backend’а. Как это работает в PostgreSQL (многопроцессная модель)? 📌 Главное правило PostgreSQL: Каждый новый клиент создаёт отдельный процесс (а процесс занимает целое ядро или часть его времени). Процесс не делится на потоки – он полностью использует ресурсы ядра. Oracle Многопоточная архитектура: один «инстанс» (SGA + фоновые процессы: DBWn, LGWR, SMON и др.). Dedicated или Shared Server-процессы для клиентских сессий. Поддержка RAC (Real Application Clusters) для распределённой работы нескольких инстансов с одной БД. Как это работает в Oracle (многопоточная модель)? 📌 Главное правило Oracle: Oracle использует один общий процесс, внутри которого клиенты запускают потоки. Потоки делят ресурсы процессора эффективнее, чем процессы PostgreSQL. Microsoft SQL Server Монолитный многопоточный процесс (служба sqlservr.exe): внутри – «SQL OS», Query Processor, Storage Engine. Нет отдельных процессов для каждого подключения; для каждой сессии задействуются потоки из общего пула. Глубокая интеграция с Windows, теперь доступен и на Linux/в Docker. Как это работает в SQL Server (монолитный многопоточный процесс)? 📌 Главное правило SQL Server: В SQL Server всё работает внутри одного процесса sqlservr.exe. Клиенты не создают новые процессы, а просто получают потоки из общей очереди. SQL Server сам управляет потоками и перераспределяет задачи по загруженности. Хранение и транзакции PostgreSQL Табличные файлы в каталоге кластера, журнал WAL в pg_wal. MVCC с сохранением старых версий строк до VACUUM. Конфигурационные файлы (postgresql.conf, pg_hba.conf) в каталоге PGDATA. Oracle Datafiles, Redo Log Files, Control Files, Undo-сегменты для старых версий строк. Мощный механизм Flashback (восстановление данных поUndo). Параметры инстанса в init.ora/spfile.ora; Listener управляет входящими подключениями. MS SQL Server MDF/NDF (данные) + LDF (лог транзакций). По умолчанию блокировки (READ COMMITTED), возможно включить Snapshot Isolation (старые версии в tempdb). Конфигурация чаще в системных таблицах master, настройки сети – через SQL Server Configuration Manager. Сценарии применения (краткий вывод) PostgreSQL Оптимален для проектов, где важны открытость, гибкость, отсутствие лицензионных затрат. Хорош для разнообразных сценариев (OLTP, аналитика, JSON, GIS через PostGIS). Простая в освоении архитектура, активное сообщество. Oracle Корпоративная БД для крупных систем с высокими требованиями к масштабированию, отказоустойчивости и производительности. Расширенные возможности (RAC, Data Guard, Flashback, Partitioning), но высокая стоимость и сложность. Принята во многих крупных организациях как стандарт. MS SQL Server Удобен в экосистеме Microsoft (Windows AD, .NET, Azure). Сильные инструменты администрирования (SSMS, BI-пакет), богатые возможности по аналитике и отчетам. Коммерческий продукт; сейчас есть кроссплатформенность (Linux, Docker), что расширяет области применения.
50
Что делают команды делит, транкейт и дроп? Чем отличаются транкейт Теги: #тг
DELETE – удаляет строки из таблицы с возможностью фильтрации (WHERE), но оставляет структуру и автоинкремент. TRUNCATE – полностью очищает таблицу, удаляя все строки, но оставляет структуру. Быстрее, чем DELETE, так как не фиксирует удаление построчно. DROP – удаляет таблицу (или базу данных) полностью, включая структуру и данные. Truncate это DDL операция (нужны соотв. права), выполняется мгновенно, т.к. не оставляет записей в журнале операций БД. В большинстве баз данных не откатывается в случае неуспешной транзакции. Delete это DML операция, выполняется медленнее, т.к. для каждой удалённой строки оставляет по записи в журнале БД. В случае удаления построчно в big data системах может быть очень дорогой и медленной, поэтому её заменяют insert-only строчкой с флагом is_deleted или перезаписывают партицию целиком.
51
Назови основные типы данных в SQL. За основу возьми pgsql #
Классические категории типов данных (пример на PostgreSQL, но в других СУБД будет похоже): Числовые Целые: SMALLINT, INT (или INTEGER), BIGINT С плавающей точкой: FLOAT, REAL, DOUBLE PRECISION, DECIMAL(p, s) / NUMERIC(p, s) Строковые CHAR(n) (фиксированная длина) VARCHAR(n) (переменная длина, с ограничением) TEXT (переменная длина, без явного ограничения) Дата и время DATE TIME [WITHOUT TIME ZONE / WITH TIME ZONE] TIMESTAMP [WITHOUT TIME ZONE / WITH TIME ZONE] INTERVAL (в PostgreSQL) Логический тип BOOLEAN (в некоторых СУБД может отсутствовать, тогда используют BIT или TINYINT(1) в MySQL) Бинарные / двоичные (varbinary) – например, BYTEA в PostgreSQL, BLOB в Oracle/MySQL и т.д. В зависимости от СУБД могут быть ещё и дополнительные специализированные типы (JSON, GIS-тип, XML и пр.).
52
Назови "нестандартные" типы данных, которые ты знаешь. # Владимир
«Нестандартные» или специфичные для конкретных движков (или для SQL-стандарта, но редко используемые) могут включать: JSON / JSONB (PostgreSQL поддерживает JSON и JSONB, MySQL – JSON, Oracle – JSON CLOB/BLOB и др.) Массивы (PostgreSQL позволяет integer[], text[] и т.д.) HSTORE (специфический тип ключ-значение в PostgreSQL) GEOMETRY / GEOGRAPHY (пространственные данные: PostGIS расширение для PostgreSQL, Spatial Extensions для MySQL, Oracle Spatial и т.д.) XML (некоторые СУБД имеют нативную поддержку XML-типов) UUID (PostgreSQL, MySQL 8.0+ – тоже поддержка uuid или binary(16) под uuid)
53
Различие строковых типов данных CHAR, VARCHAR, TEXT. #Владимир
CHAR(n): Фиксированная длина в n символов. Если фактическая длина строки меньше n, она будет дополняться пробелами до n. Используется редко (например, для кодов определённой длины), так как зачастую это менее эффективно по памяти и может приводить к неожиданностям с пробелами. VARCHAR(n): Переменная длина, ограниченная максимумом n. Хранит ровно столько символов, сколько занимает строка, плюс небольшой overhead на хранение длины. Это наиболее распространённый способ хранить «обычные» строки, где есть верхний лимит. TEXT (или CLOB/LONGTEXT в разных СУБД): Переменная длина без (или с очень большим) явным ограничением. Удобен для хранения длинных текстовых полей (описания, статьи, логи и пр.), когда жёсткий лимит не нужен. С точки зрения производительности в современных СУБД (например, PostgreSQL) разница между VARCHAR(n) и TEXT обычно минимальна, но формально VARCHAR(n) даёт возможность валидировать длину. В Oracle и некоторых других движках для больших текстовых полей обычно используют тип CLOB.
54
Что такое spill таблиц? #Владимир
В разных контекстах термин “spill” может использоваться по-разному. Чаще всего в разговоре о СУБД (особенно при анализе планов выполнения запросов) “spill” означает, что при выполнении определённой операции (например, сортировки или хэш-джойна) объём данных не помещается в памяти, и поэтому промежуточные результаты выгружаются («spilled») во временные файлы на диск. Это может сильно замедлять запросы, поскольку чтение/запись на диск на порядок медленнее операций в памяти. «Spill таблицы» (или «spill to temp tables») – временные структуры/файлы, которые возникают автоматически, когда движку не хватает памяти. В Oracle и PostgreSQL вы можете увидеть упоминания «temp spill» или «disk spill» в плане выполнения (в PostgreSQL – через EXPLAIN (ANALYZE, BUFFERS) и т.п.), в Oracle – в плане может указываться «TEMP TABLESPACE» использование и т.д. Почему это важно? Если запросы часто «спиллятся» на диск, значит надо либо оптимизировать запрос/план, либо увеличить соответствующие буферы памяти (например, work_mem в PostgreSQL, pga_aggregate_target в Oracle) или оптимизировать структуру данных.
55
Что такое статитика в скюл и как ее посмотреть в оракле и пгскюл. Зачем она нужна? #Я
Статистика в СУБД – это совокупность данных о распределении значений в таблицах и индексах (количество строк, селективность, гистограммы, наиболее частые значения и т.д.). Оптимизатор запросов использует её, чтобы определить самый эффективный план (какой тип соединения выбрать, какой индекс, и т.п.). В Oracle посмотреть и обновить статистику можно с помощью: DBMS_STATS пакет (например, DBMS_STATS.GATHER_TABLE_STATS, GATHER_SCHEMA_STATS). Просмотреть можно в словарях (например, USER_TAB_STATISTICS, ALL_TAB_STATISTICS) и через DBA_* представления, или в AUTOTRACE EXPLAIN STATISTICS. В PostgreSQL статистика собирается демоном autovacuum. Можно вручную запустить ANALYZE или VACUUM ANALYZE. Посмотреть статистику – в системном каталоге (например, pg_stat_all_tables, pg_stats, pg_stat_user_tables) и через EXPLAIN. Зачем нужна статистика? Правильная статистика – ключ к оптимизации. Если СУБД имеет неточные данные о количестве строк/распределении, план запросов может строиться неоптимально (может выбрать неверный индекс, сделать неправильный тип соединения и т.д.).
56
Какие есть блокировки в оракле и пгскюл и когда происходят?#Я
Блокировка — это метод ограничения доступа к данным для обеспечения корректной обработки транзакций. Серверы баз данных используют блокировки, чтобы управлять одновременным доступом к данным, чтобы пока одна транзакция работает с данными, другие транзакции не могли их изменять. Когда данные в базе блокируются, другие пользователи, которые хотят изменить или прочитать эти же данные, должны подождать, пока блокировка не будет снята. Общее: в реляционных СУБД есть несколько уровней блокировок: Блокировки на уровне строк (row-level locks) Блокировки на уровне страниц (page-level) – чаще встречается в SQL Server, PostgreSQL может использовать page-level в отдельных случаях Блокировки на уровне таблиц (table-level) Различные режимы блокировок (share, exclusive и промежуточные варианты) Oracle Базовый принцип – MVCC (многоверсионность). Основная рабочая блокировка DML – это row-level exclusive lock (при обновлении/вставке/удалении). Есть также TM locks (table locks) – share или exclusive, возникают для операций, изменяющих структуру таблицы (DDL) или при некоторых специфических случаях (например, FK constraints). “Select for update” ставит exclusive-блокировку на выбранные строки. PostgreSQL Также MVCC. При обычном SELECT блокировок «на чтение» строк нет (каждый видит свою версию). При UPDATE/DELETE СУБД ставит row-exclusive lock (запись «версия»), и все параллельные транзакции, пытающиеся изменить те же строки, вынуждены ждать. При ALTER TABLE / DDL – блокировка уровня таблицы (Access Exclusive Lock). Есть и другие режимы (ShareRowExclusive, Exclusive, AccessShare и т.д.), которые возникают в зависимости от типа операции.
57
Что такое шардирование в скюл и какие есть похожие механизмы в разынх субд #Я
Что такое шардирование в SQL и какие есть похожие механизмы в разных СУБД? Шардирование (sharding) – это распределение одной большой логической таблицы/базы на несколько физических узлов или сегментов (шардов). Каждый шард хранит часть данных (например, по диапазонам ключей, по хэшу и т.п.). Цель – масштабировать горизонтально и обрабатывать большие объёмы данных. PostgreSQL: нативно масштабирование раньше делалось через шардинг на уровне приложения или расширения (Citus, Postgres-XL). В PostgreSQL 14+ есть встроенные Partition и FDW, но полноценное шардирование «из коробки» всё ещё требует доп. инструментов. MySQL: популярный вариант – MySQL Sharding (обычно Shard-Proxy / MySQL Fabric / Vitess). Oracle: чаще используют механизмы Partitioning (разделы в одной базе) + Real Application Clusters (RAC), хотя это чуть другой подход (общая СУБД на нескольких узлах). Есть и Sharding (Oracle Sharding) как отдельная фича. Альтернативы шардированию: Партиционирование (partitioning) внутри одной базы – логическое разделение таблицы, но всё ещё в рамках одного инстанса или кластера. Репликация – копии всей базы на нескольких узлах. Не совсем «шардирование», т.к. все узлы содержат полный набор данных, а не фрагменты.
58
Что выведет следующая команда: SELECT NULL + 5, 5 - NULL, 10 * NULL, 10 / NULL, NULL / 10, NULL || 'какая-то прикольная фраза' #Владимир
В большинстве СУБД (Oracle, PostgreSQL, MySQL) арифметические операции с NULL возвращают NULL. Исключение – если СУБД или режим SQL настроены по-другому (например, в некоторых старых диалектах могли быть «конвертированные» значения, но это нетипично). NULL + 5 => NULL 5 - NULL => NULL 10 * NULL => NULL 10 / NULL => либо NULL, либо ошибка деления на NULL (зависит от СУБД, но чаще просто NULL) NULL / 10 => NULL А вот конкатенация строк (||) с NULL в большинстве случаев даёт: В Oracle и PostgreSQL NULL при конкатенации превращается в '' (пустую строку). Итоговый результат: Столбцы с арифметическими операциями = NULL В Oracle и PostgreSQL NULL при конкатенации превращается в '' (пустую строку).
59
В таблице 100 млн строк, необходимо удалить 90 млн. Как ты это сделаешь и почему? #Владимир
Обычный DELETE 90 млн строк может быть: Очень долгим (операция построчная, каждая удаляемая запись идёт в транзакционный лог, требуются блокировки, обновление индексов и т.д.). Потребует много ресурсов и может вызывать «блокировки» или «долгое удержание транзакции». Оптимальные варианты (зависят от СУБД и структуры данных): Использовать CTAS (Create Table As Select) + переименование: Создать новую таблицу: CREATE TABLE new_table AS SELECT * FROM old_table WHERE <условие для 10 млн, которые надо оставить>; Удалить/переименовать старую таблицу, затем переименовать новую в старое имя (и при необходимости пересоздать индексы). Преимущество: вместо массового DELETE мы просто выбираем нужные строки. Это может быть быстрее и «чище» для больших объёмов. Партиционирование: Если таблица заранее партиционирована по дате или по какому-то ключу, и 90 млн строк находится в конкретных партициях, можно просто DROP PARTITION или TRUNCATE PARTITION. Это очень быстро и не генерирует огромного объёма в журнале транзакций. Batch Delete (пакетами) или обнуление + вставка обратно (менее элегантно, но иногда используется, если нельзя пересоздавать таблицу). Почему так? Основная причина – производительность и блокировки. Массовое DELETE на 90% строк – крайне тяжёлая операция, может «забить» лог транзакций, занять много времени и привести к проблемам с конкурирующими запросами. Гораздо эффективнее либо пересоздать таблицу с нужными данными, либо обрезать нужные партиции.
60
Является ли следующая команда транзакцией? SELECT * FROM t1 WHERE id > 5; #Владимир
С точки зрения SQL-стандарта «транзакцией» называют последовательность инструкций (в том числе SELECT, INSERT, UPDATE, DELETE, DDL и т.д.), заключённых между BEGIN (или START TRANSACTION) и COMMIT/ROLLBACK. Один SELECT не модифицирует данные, и если СУБД работает в автокоммит-режиме, формально каждый запрос идёт в своей «мини-транзакции», но это не та «транзакция», которая что-то меняет. На большинстве собеседований правильный ответ – “Нет, это не является транзакцией, это просто SELECT”. Но уточнить, что «формально любая команда может выполняться в рамках транзакции (явной или автокоммит), но данная команда не изменяет состояние данных».
61
Есть запрос, который работает ночью и строит отчет. Ежедневно он работал нормально и создавал отчет за 2 часа. Сегодня утром ты пришел на работу, а отчета нет. Смотришь свой пайплайн, а он все еще крутится на чтении запроса. Что могло произойти? #Владимир
Возможные причины: Изменение объёма данных или данных в таблицах За прошедшую ночь объём данных внезапно вырос (например, из‑за большого загрузочного джоба или технического сбоя), и запросу не хватает памяти/ресурсов. Появились «нестандартные» данные, которые ломают план запроса (из-за изменения распределения). Неправильная/устаревшая статистика в СУБД Если статистика (ANALYZE) давно не обновлялась, оптимизатор мог выбрать неправильный план (например, перепутать низко- и высокоселективные индексы). Изменение индексов или структуры таблицы Индекс был случайно удалён, повреждён или стал неактуальным. Запрос вдруг начал делать полные сканы вместо индексных. Блокировки (lock contention) Другой долгий процесс (например, массовый update/delete) держит блокировку, и ваш запрос «висит» в очереди. Нужно проверить, нет ли конкурентной транзакции, которая не завершилась. Проблемы в инфраструктуре Может быть «проседание» производительности диска (I/O), проблемы с сетью (если данные подгружаются из внешнего источника), падение производительности кластера (например, Spark, если используется). Переполнен временный tablespace и идут «spills» на диск, что резко замедляет работу. Как искать причину: Посмотреть план выполнения (EXPLAIN (ANALYZE)) или лог активности сервера, проверить наличие блокировок (pg_locks, information_schema.innodb_locks и т.д. в зависимости от СУБД). Проверить свежесть статистики (ANALYZE, DBMS_STATS в Oracle). Посмотреть системные метрики (CPU, RAM, I/O). Уточнить, не вносились ли изменения в схему (добавляли ли индексы, колонки).
62
Как быстро посчитать количество строк в таблице, имеющей нормальную структуру, например, в 3 НФ, где много колонок и пусть в ней даже JSON'овский сырец хранится? Структура таблицы пусть будет следующая: CREATE TABLE t1 ( id int primary key, col1 text, FK_id int NOT NULL, col2 text, col3 text, .... col100 jsonb ) #Владимир
Если нужна точность – SELECT COUNT(*) (с возможным параллелизмом, если поддерживается), но это может быть медленно на очень больших объёмах. Если нужна просто примерная оценка – можно взять данные из статистики (pg_class, ALL_TABLES и т.д.). JSONB/много колонок обычно не сильно влияет на простое COUNT(*), так как движок может делать индекс только по месту хранения метаданных (или «heap»), не выбирая все поля таблицы (зависит от реализации)
63
Что такое и для чего нужен подзапрос? Что такое коррелируемый и не коррелируемый подзапрос? В чем отличиие СТЕ от подзапроса? #Владимир
Что такое подзапрос? Что такое коррелируемый (correlated) и некоррелируемый (non-correlated) подзапрос? В чём отличие CTE от подзапроса? Подзапрос (subquery) Это запрос (SELECT), который встраивается внутрь другого запроса (например, в секции WHERE, FROM, SELECT, HAVING). Подзапрос может вернуть одно значение, набор значений, или целую таблицу. Коррелируемый подзапрос (correlated subquery) Это подзапрос, который зависит от внешнего запроса: SELECT a.col1 FROM table_a a WHERE a.col2 > (SELECT AVG(b.col2) FROM table_b b WHERE b.id = a.id); Здесь подзапрос ссылается на a.id из внешнего запроса. На каждую строку из table_a будет выполняться свой подзапрос. В отличие от обычного подзапроса, CTE улучшает читаемость, позволяет использовать одну и ту же промежуточную выборку несколько раз в основном запросе. В некоторых СУБД (например, PostgreSQL) CTE по умолчанию всегда материализуется (хранится во временном буфере), но начиная с версии 12+ при определённых условиях CTE может «inline»-иться, то есть вести себя как подзапрос. Подзапрос более «локален» и чаще имеет смысл, когда используется один раз или необходим «на месте». CTE удобен для шаг за шагом построения логики, упрощает сложные запросы.
64
Для чего нужна временная таблица, если есть CTE? #Владимир
Для чего нужна временная таблица, если есть CTE? Несмотря на схожесть (и то, и другое создаёт «промежуточный набор данных»), временные таблицы и CTE имеют разные цели: Объём и повторное использование Временная таблица создаётся физически (или логически, в зависимых tablespace) и может быть использована в нескольких разных запросах в рамках одной сессии или транзакции. CTE виден только в рамках одного запроса (хотя сам запрос может быть многошаговым). Материализация и индексы Во временной таблице можно создавать индексы, что ускоряет повторные операции. CTE обычно «материализуется» (в PostgreSQL до версии 12 – всегда, в более новых версиях – зависит от оптимизатора), но не поддерживает собственных индексов. Разделение логики Иногда удобно создать временную таблицу, наполнить её данными (например, результатом сложной выборки), а затем в несколько шагов анализировать, модифицировать, фильтровать и т.д. CTE не позволяет делать «UPDATE/DELETE» внутри него. Жизненный цикл Временная таблица существует в сессии (или транзакции), и вы можете к ней обращаться многократно. CTE «живет» только в момент выполнения одного запроса. Таким образом, иногда временная таблица нужна, если вам: Нужны индексы, сортировки, дополнительная обработка данных «по шагам». Планируется использовать временные данные в нескольких отдельных запросах. Нужен более «длительный» объект, чем скоуп одного SELECT (например, для ETL-процедур).
65
Какими конструкциями дополняется ORDER BY, чтобы значения NULL стояли в начале и в конце таблицы? #Владимир
Какими конструкциями дополняется ORDER BY, чтобы значения NULL стояли в начале или в конце таблицы? Стандарт SQL поддерживает фразу NULLS FIRST / NULLS LAST: Не во всех диалектах эта конструкция одинаково выглядит, но в PostgreSQL, Oracle, SQLite она есть. В MySQL (до версии 8) нет нативной конструкции NULLS FIRST/NULLS LAST, но можно обойтись костылём, например:
66
Что такое представление?
In a database, a view is the result set of a stored query that presents a limited perspective of the database to a user.
67
Что такое первичный ключ?
В SQL первичный ключ – это поле или комбинация полей, которые однозначно идентифицируют определенную строку в таблице. Первичный ключ гарантирует отсутствие дубликатов строк в таблице, а также позволяет эффективно выполнять запросы и индексировать таблицу.
68
Что такое временная таблица?
Временная таблица – это тип таблицы, которая создается и существует только на время сеанса или транзакции. Она не хранится в базе данных постоянно и удаляется автоматически. Временные таблицы можно использовать для хранения промежуточных результатов или для разбиения сложных запросов на более простые шаги. Они особенно полезны, когда запрос требует нескольких шагов или сложных вычислений, так как помогают повысить производительность запроса и упростить его синтаксис. Временные таблицы можно создать с помощью оператора CREATE TEMPORARY TABLE. Они могут быть созданы в памяти или на диске, в зависимости от системы базы данных и конфигурации. ременные таблицы можно использовать как обычные таблицы в SQL-запросах и заполнять данными с помощью операторов INSERT. Их также можно объединять с другими таблицами или использовать в подзапросах. Одним из распространенных вариантов использования временных таблиц является хранение и обработка промежуточных результатов в сложных запросах, особенно в тех, которые включают соединения или агрегирование. Например, временная таблица может использоваться для хранения результатов операции соединения, которые затем могут быть использованы для дальнейших манипуляций или соединения с другими таблицами на последующих этапах запроса.
69
Что такое вторичный ключ?
Внешний ключ – это столбец или набор столбцов, которые ссылаются на первичный ключ другой таблицы. Он используется для установления связи между двумя таблицами. Добавление внешнего ключа в таблицу создает связь между данными в этой таблице и данными в другой таблице. Эта связь гарантирует, что данные в двух таблицах всегда будут соответствовать друг другу.
70
Какие есть ограничения (constrains)?
PRIMARY KEY – первичный ключ, уникальный идентификатор строки в таблице. FOREIGN KEY – внешний ключ, обеспечивает ссылочную целостность между таблицами. UNIQUE – гарантирует, что значения в указанном столбце (или нескольких столбцах) уникальны. NOT NULL – запрещает хранение NULL в данном столбце. CHECK – проверяет соблюдение заданного условия. DEFAULT – устанавливает значение по умолчанию, если не указано иное. INDEX – создаёт индекс для ускорения поиска (не является ограничением целостности, но используется для оптимизации). AUTO_INCREMENT / SERIAL – автоматически увеличивает значение при вставке новой строки (актуально для MySQL, PostgreSQL, SQL Server). EXCLUDE (только PostgreSQL) – запрещает пересечение значений в указанном диапазоне. DEFERRABLE (только PostgreSQL) – позволяет отложить проверку ограничения до конца транзакции.
71
Что такое кластерный индекс и чем он отличается от некластерного?
Кластерный индекс (Clustered Index) Определяет физический порядок хранения строк в таблице. В таблице может быть только один кластерный индекс. Быстрее при диапазонных запросах и поиске по ключу. Некластерный индекс (Non-clustered Index) Хранит только ссылки на данные, сами строки остаются в произвольном порядке. В одной таблице может быть много некластерных индексов. Оптимален для выборок небольших подмножеств данных. СУБД Кластерный индекс Некластерный индекс PostgreSQL Реализуется через CLUSTER, но не обновляется автоматически Обычные CREATE INDEX, содержат ссылки на строки (TID) SQL Server Создаётся автоматически на PRIMARY KEY, если не указано иначе CREATE NONCLUSTERED INDEX, отдельно от данных MySQL (InnoDB) PRIMARY KEY автоматически кластеризует данные CREATE INDEX, хранит указатели на кластерный индекс
72
Что такое триггер и как он используется?
Триггеры используются для автоматического запуска серии операторов в ответ на определенные события. Эти события могут включать такие действия, как вставка, обновление или удаление таблицы. По сути это тип хранимой процедуры, которая выполняется автоматически в ответ на определенные события или изменения в базе данных. События, вызывающие триггер, могут включать операции вставки, обновления или удаления таблицы. Триггеры обычно используются для соблюдения бизнес-правил, поддержания ссылочной целостности или регистрации изменений в БД. CREATE TRIGGER trigger_name {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name [REFERENCING NEW AS new OLD AS old] [FOR EACH ROW] WHEN (condition) DECLARE {Variable declarations} BEGIN {SQL statements} END;
73
Можно ли увидеть на физическом плане запроса операцию Hash Join тут? SELECT t1.ip FROM Table1 t1 JOIN Table2 T2 on t1.ip >= t2.ip_from AND t1.ip <= t2.ip_to
нет. нельзя по неравенству сравнивать

DE (17 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions