Перейти к содержимому

Ghoster AI 3.6.6 — аккаунт, подписка и личный кабинет

DBA / Database

216 вопросов в базе. Ниже — разобранные вопросы с ответами.

21 разобрано
01Какие задачи вы выполняли на последнем месте и чего успели достичь за время работы?Middle — Middle+

Я работал в роли DBA около 3 лет, занимаясь администрированием и оптимизацией баз данных PostgreSQL и MySQL. В мои обязанности входило: • Настройка и мониторинг производительности баз данных. • Оптимизация запросов и индексов для ускорения работы. • Резервное копирование и восстановление данных. • Обеспечение безопасности и разграничения доступа. • Миграция данных и обновление версий СУБД. В одном из проектов я внедрил автоматизированный мониторинг с помощью Prometheus и Grafana, что позволило своевременно выявлять узкие места и снижать время простоя. Также участвовал в проекте по миграции с Oracle на PostgreSQL, где занимался переносом схем и данных с минимальным простоем.

02Что можно сделать, если индекс не применяется из-за функции на столбце?Middle

Если индекс не применяется из-за использования функции на столбце в условии (например, WHERE UPPER(column) = 'VALUE' ), то индекс не может быть использован напрямую, так как функция изменяет данные. Возможные решения: • Создать функциональный (expression-based) индекс на результат функции, если СУБД это поддерживает. Например, в Oracle или PostgreSQL можно создать индекс на UPPER(column) . • Избегать применения функций в условиях фильтрации, переписав запрос так, чтобы сравнивать с исходным значением, например, привести константу к нужному регистру. • Использовать вычисляемые/виртуальные столбцы с индексами на них. Пример создания функционального индекса в PostgreSQL: CREATE INDEX idx_upper_column ON table_name ( UPPER ( column )); Это позволит использовать индекс при запросах с UPPER(column) .

03Что такое транзакция? Назови принципы ACID.Middle

Транзакция — это последовательность операций с базой данных, которые выполняются как единое целое. Либо все операции успешно завершаются (commit), либо при ошибке все изменения откатываются (rollback), обеспечивая целостность данных. Принципы ACID, гарантирующие надежность транзакций: • Atomicity (Атомарность) — транзакция выполняется полностью или не выполняется вовсе. • Consistency (Согласованность) — после завершения транзакции база данных остается в корректном состоянии, удовлетворяющем всем ограничениям. • Isolation (Изолированность) — параллельные транзакции не влияют друг на друга, как если бы выполнялись последовательно. • Durability (Долговечность) — после подтверждения транзакции изменения сохраняются даже при сбоях системы. Пример на SQL: BEGIN ; UPDATE accounts SET balance = balance - 100 WHERE id = 1 ; UPDATE accounts SET balance = balance + 100 WHERE id = 2 ; COMMIT ; Здесь перевод денег между счетами либо пройдет полностью, либо не произойдет вовсе, что предотвращает потерю или дублирование средств.

04В чём разница между синхронным и асинхронным режимом репликации в AlwaysOn?Middle+ — Senior

В AlwaysOn Availability Groups (Microsoft SQL Server) разница между синхронным и асинхронным режимом репликации заключается в гарантии доставки и задержках: • Синхронный режим : транзакция считается завершённой только после того, как данные записаны на первичном и вторичном репликах. Это обеспечивает высокую согласованность и минимальную потерю данных при сбое, но может увеличить задержку транзакций из-за ожидания подтверждения. • Асинхронный режим : транзакция завершается сразу после записи на первичной реплике, а данные отправляются на вторичную реплику с задержкой. Это снижает задержку на первичной стороне, но может привести к потере данных при аварии, так как вторичная реплика может отставать. Выбор режима зависит от требований к отказоустойчивости и производительности: • Синхронный — для критичных данных и минимальной потери. • Асинхронный — для географически распределённых систем с высокой задержкой сети.

05Отличия процедуры от функцииMiddle

Процедура и функция — это подпрограммы в базах данных и программировании, но между ними есть ключевые отличия: • Функция всегда возвращает значение и может использоваться в выражениях, например, в SELECT. • Процедура может выполнять действия (например, изменять данные), но не обязана возвращать значение напрямую. Пример на SQL (PL/pgSQL): -- Функция возвращает сумму двух чисел CREATE FUNCTION add_numbers(a INT , b INT ) RETURNS INT AS $$ BEGIN RETURN a + b; END ; $$ LANGUAGE plpgsql; -- Процедура вставляет запись в таблицу CREATE PROCEDURE insert_user(name TEXT) AS $$ BEGIN INSERT INTO users(username) VALUES (name); END ; $$ LANGUAGE plpgsql; Функции обычно используются для вычислений и возвращают результат, процедуры — для выполнения операций, часто с побочными эффектами.

06Как обрабатывать исключения внутри триггера? Лучше сообщать об ошибке или замолчать?Middle

Обработка исключений внутри триггера должна быть продуманной, так как триггеры работают на уровне базы данных и влияют на целостность данных. Лучше не «замалчивать» ошибки, а корректно их обрабатывать и, если возможно, логировать или передавать информацию о проблеме. Если ошибка в триггере происходит и не обрабатывается, это может привести к откату транзакции и потере данных. Рекомендуется: • Использовать конструкции обработки исключений (например, в PL/SQL EXCEPTION WHEN ... THEN ... ), чтобы перехватывать ошибки. • Логировать ошибки в отдельную таблицу для последующего анализа. • При необходимости, выбрасывать ошибку дальше, чтобы приложение или пользователь получили уведомление. Пример на PL/SQL: BEGIN -- основная логика триггера EXCEPTION WHEN OTHERS THEN INSERT INTO error_log (error_message, error_time) VALUES (SQLERRM, SYSDATE); RAISE; -- передать ошибку дальше END; Таким образом, ошибки не игнорируются, а фиксируются и могут быть обработаны на уровне приложения.

07Что такое BULK COLLECT и FORALL, почему они работают быстрее?Middle

BULK COLLECT и FORALL — это конструкции PL/SQL для эффективной работы с большими объемами данных. • BULK COLLECT позволяет загружать сразу множество строк из курсора или запроса в коллекцию за одну операцию, уменьшая количество переключений между SQL и PL/SQL. • FORALL выполняет DML-операции (INSERT, UPDATE, DELETE) для всех элементов коллекции за один вызов, вместо цикла с отдельными операциями. Они работают быстрее, потому что снижают количество контекстных переключений между SQL и PL/SQL, уменьшая накладные расходы на обработку каждой строки по отдельности. Пример: DECLARE TYPE t_ids IS TABLE OF employees.employee_id%TYPE; l_ids t_ids; BEGIN SELECT employee_id BULK COLLECT INTO l_ids FROM employees WHERE department_id = 10; FORALL i IN l_ids.FIRST .. l_ids.LAST UPDATE employees SET salary = salary * 1.1 WHERE employee_id = l_ids(i); END;

08Работал ли с триггерами? Для чего использовал? Какие типы триггеров знаешь (ROW vs STATEMENT)?Middle

Триггеры в базах данных используются для автоматического выполнения определённых действий при наступлении событий (INSERT, UPDATE, DELETE) в таблицах. Я применял триггеры для аудита изменений данных, автоматического обновления связанных таблиц и контроля целостности. Существует два основных типа триггеров по уровню срабатывания: • ROW-level (по строке) — триггер срабатывает для каждой изменяемой строки. Например, при обновлении 10 строк триггер выполнится 10 раз. • STATEMENT-level (по оператору) — триггер срабатывает один раз на весь SQL-запрос, независимо от количества затронутых строк. Пример создания триггера на уровне строки в PostgreSQL: CREATE FUNCTION audit_changes() RETURNS trigger AS $$ BEGIN INSERT INTO audit_log(table_name, operation, changed_at) VALUES (TG_TABLE_NAME, TG_OP, now()); RETURN NEW ; END ; $$ LANGUAGE plpgsql; CREATE TRIGGER audit_trigger AFTER INSERT OR UPDATE OR DELETE ON my_table FOR EACH ROW EXECUTE FUNCTION audit_changes();

09Что такое пакет процедур? Из чего он состоит?Middle

Пакет процедур — это логическая группа связанных процедур и функций в базе данных, объединённых под одним именем. Он служит для организации и инкапсуляции кода, улучшая его повторное использование и управление. Основные компоненты пакета процедур: • Спецификация (Specification) — объявление всех процедур, функций, типов данных и переменных, доступных извне. Это интерфейс пакета. • Тело (Body) — реализация всех объявленных в спецификации процедур и функций. Здесь описывается логика. Пакеты позволяют: • Скрыть внутреннюю реализацию (инкапсуляция). • Объявлять глобальные переменные и константы. • Улучшать производительность за счёт компиляции и кэширования. Пример (Oracle PL/SQL): CREATE OR REPLACE PACKAGE my_package AS PROCEDURE proc1(param IN NUMBER); FUNCTION func1 RETURN VARCHAR2; END my_package; / CREATE OR REPLACE PACKAGE BODY my_package AS PROCEDURE proc1(param IN NUMBER) IS BEGIN -- реализация END proc1; FUNCTION func1 RETURN VARCHAR2 IS BEGIN RETURN 'result'; END func1; END my_package; /

10Как посмотреть план выполнения SQL-запроса?Middle — Senior

В выводе EXPLAIN: • type: ALL означает, что выполняется полное сканирование таблицы (full table scan), что обычно медленно при больших объемах данных. • rows: 200000 — количество строк, которые MySQL планирует прочитать. • Using temporary — используется временная таблица для обработки запроса, например, при сортировке или группировке. • Using filesort — сортировка выполняется не по индексу, а с помощью дополнительной операции сортировки. Как оптимизировать: • Добавить или улучшить индексы — чтобы избежать полного сканирования таблицы, создайте индексы по колонкам, участвующим в условиях WHERE, JOIN и ORDER BY. • Переписать запрос — возможно, изменить логику запроса, чтобы уменьшить объем обрабатываемых данных. • Избегать сложных операций, вызывающих временные таблицы — например, разбить запрос на несколько или использовать агрегатные функции с индексами. • Проверить статистику и обновить индексы — чтобы оптимизатор имел актуальную информацию. Пример: если запрос сортирует по колонке без индекса, добавьте индекс: ALTER TABLE table_name ADD INDEX idx_column_name(column_name); Это может значительно ускорить выполнение и убрать Using filesort.

11А если триггер BEFORE INSERT — сохранится ли результат его работы при откате процедуры?Middle

Триггер BEFORE INSERT выполняется до вставки записи в таблицу и работает в рамках транзакции, которая инициировала вставку. Если в ходе выполнения процедуры, внутри которой сработал этот триггер, происходит откат (rollback), то все изменения, включая действия триггера, также отменяются. То есть результат работы триггера BEFORE INSERT не сохранится при откате, так как триггер — часть транзакции. Это гарантирует целостность данных и согласованность состояния базы.

12Что такое AlwaysOn Availability Groups и для чего используется эта технология?Middle — Middle+

AlwaysOn Availability Groups — это технология высокой доступности и аварийного восстановления в Microsoft SQL Server. Она позволяет создавать группы баз данных, которые синхронизируются между несколькими экземплярами SQL Server (репликами). Основное назначение — обеспечить непрерывную работу приложений и минимизировать простой при сбоях. Ключевые особенности: • Поддержка нескольких вторичных реплик для чтения и резервирования. • Автоматическое переключение (failover) между репликами. • Репликация на уровне групп баз данных, а не отдельных баз. Пример использования: в крупной системе с критичными данными AlwaysOn позволяет иметь основную базу и несколько вторичных, которые могут использоваться для чтения или быстро переключаться в случае отказа основной.

13Если пользователю нужно только выполнять хранимую процедуру и больше ничего — какие права ему выдать?Senior

Если пользователю нужно только выполнять хранимую процедуру и больше ничего, то ему следует выдать минимально необходимые права для безопасности и удобства: • Право на EXECUTE для конкретной хранимой процедуры. • Отсутствие прав на чтение или изменение таблиц напрямую. Например, в SQL Server это можно сделать так: GRANT EXECUTE ON dbo.ProcedureName TO UserName; Это позволит пользователю запускать процедуру, но не даст доступа к другим объектам базы данных. Такой подход соответствует принципу наименьших привилегий и повышает безопасность системы.

14Какие типы резервного копирования вы знаете в MS SQL Server? Расскажите о полном, инкрементальном и log backup.Middle

В MS SQL Server существуют несколько типов резервного копирования: • Полное (Full) резервное копирование — сохраняет всю базу данных целиком. Это основа для восстановления, содержит все данные на момент создания. • Дифференциальное резервное копирование — сохраняет только изменения, сделанные с момента последнего полного бэкапа. Позволяет быстрее восстанавливаться, применяя сначала полный бэкап, затем дифференциальный. • Журнальное (Log) резервное копирование — сохраняет транзакционные логи, то есть все изменения, которые произошли после последнего лог-бэкапа. Позволяет восстановить базу до конкретного момента времени. Пример сценария восстановления: • Восстановить последний полный бэкап • Применить последний дифференциальный бэкап (если есть) • Применить все последующие лог-бэкапы Это обеспечивает гибкость и минимизацию потери данных.

15Как настраивается AlwaysOn Availability Groups и какова роль Windows Server Failover Cluster в этом процессе?Middle+

AlwaysOn Availability Groups (AG) в SQL Server обеспечивают высокую доступность и отказоустойчивость баз данных. Настройка включает несколько ключевых шагов: • Создание Windows Server Failover Cluster (WSFC) — это основа для AG. WSFC обеспечивает кластеризацию серверов, позволяя им работать как единое целое и автоматически переключаться при сбоях. • Настройка SQL Server Instances на каждом узле кластера. • Создание Availability Group с выбором баз данных для репликации. • Настройка реплик — основная (primary) и вторичные (secondary), с выбором режима синхронизации (синхронный или асинхронный). • Настройка Listener — виртуальный сетевой адрес, через который клиенты подключаются к AG. Роль WSFC в этом процессе критична: он отвечает за мониторинг состояния узлов и автоматическое переключение ролей при сбоях, обеспечивая непрерывность работы баз данных в AG. Без WSFC AG работать не может, так как кластер обеспечивает координацию и управление состоянием узлов.

16Что такое индексы в базе данных?Middle — Senior

Индексы в базе данных — это специальные структуры данных, которые ускоряют поиск и выборку данных из таблиц. Они работают как указатели на строки таблицы, позволяя быстро находить нужные записи без полного сканирования таблицы. Индексы создаются по одному или нескольким столбцам и могут быть разных типов: B-дерево, хеш-индексы и др. Основное преимущество — повышение производительности запросов SELECT. Однако индексы занимают дополнительное место и замедляют операции вставки, обновления и удаления, так как индекс тоже нужно обновлять. Пример создания индекса в SQL: CREATE INDEX idx_customer_name ON customers (last_name); Этот индекс ускорит поиск клиентов по фамилии.

17Как был организован кластер PostgreSQL? Схема репликации и отказоустойчивость?Middle+ — Senior

Организация кластера PostgreSQL для обеспечения отказоустойчивости и репликации обычно включает следующие компоненты: • Основной сервер (Primary) — принимает все записи и изменения данных. • Реплики (Standby/Replica) — получают данные с основного сервера в режиме реального времени или с небольшой задержкой. Схемы репликации: • Streaming Replication — потоковая репликация, где реплики получают WAL (Write-Ahead Log) записи напрямую от основного сервера. • Logical Replication — репликация на уровне логических изменений, позволяет более гибко настраивать подписки и фильтрацию. Для отказоустойчивости обычно используют: • Failover — автоматическое или ручное переключение на реплику при сбое основного сервера. • Мониторинг и менеджеры кластера (например, Patroni, repmgr) — обеспечивают автоматизацию failover и контроль состояния. Пример: • Основной сервер настроен с wal_level = replica • Реплики подключаются по streaming replication • Используется Patroni для автоматического failover и управления кластером Таким образом, кластер обеспечивает высокую доступность и минимизирует простой при сбоях.

18Расскажите о вашем опыте миграции баз данных. Какие шаги вы выполняете при minor и major обновлениях PostgreSQL?Middle — Senior

При миграции баз данных PostgreSQL важно учитывать тип обновления: minor (небольшие патчи) или major (существенные версии). Minor обновления (например, 13.1 → 13.2): • Обычно не требуют полной миграции данных. • Можно обновлять бинарные файлы PostgreSQL поверх существующей установки. • Рекомендуется сделать бэкап на всякий случай. • Проверить совместимость расширений и настроек. • Перезапустить сервер после обновления. Major обновления (например, 12.x → 13.x): • Требуют миграции данных, так как формат хранения может измениться. • Основные шаги: • Сделать полный бэкап базы (pg_dumpall или pg_dump). • Установить новую версию PostgreSQL. • Использовать утилиту pg_upgrade для ускоренной миграции или восстановить из дампа. • Проверить и обновить расширения, конфигурационные файлы. • Тестировать работоспособность и производительность. В обоих случаях важно: • Планировать миграцию в окно обслуживания. • Тестировать процесс на тестовой среде. • Обеспечить возможность отката в случае проблем.

19Что такое оконные функции? Какие использовал? Чем отличаются от GROUP BY?Middle

Оконные функции (window functions) в SQL позволяют выполнять вычисления по набору строк, связанных с текущей строкой, без группировки данных в одну строку, как это делает GROUP BY. Они работают поверх набора строк, сохраняя при этом исходное количество строк в результате. Пример оконной функции — ROW_NUMBER(), RANK(), LAG(), LEAD(), SUM() OVER(), AVG() OVER() и т.д. Отличия от GROUP BY: • GROUP BY агрегирует строки, сводя несколько строк в одну, а оконные функции возвращают результат для каждой строки, учитывая контекст окна. • Оконные функции используют предложение OVER(), где можно определить окно (partition by, order by), по которому происходит вычисление. Пример: SELECT employee_id, department_id, salary, AVG (salary) OVER ( PARTITION BY department_id) AS avg_dept_salary FROM employees; Здесь для каждой строки вычисляется средняя зарплата по отделу, но строки не агрегируются. Использовал функции: ROW_NUMBER(), RANK(), LAG(), LEAD(), SUM() OVER(), AVG() OVER().

20Какие ещё типы индексов знаешь?Middle

Помимо стандартных B-Tree индексов, существуют и другие типы индексов, которые используются в базах данных для оптимизации разных видов запросов: • Хэш-индексы — эффективны для точного поиска по ключу, но не поддерживают диапазонные запросы. • Bitmap-индексы — хорошо подходят для столбцов с низкой кардинальностью (например, пол, статус). • GiST (Generalized Search Tree) — универсальные индексы, применяемые для полнотекстового поиска, геоданных и других сложных структур. • SP-GiST — специализированные индексы для пространственных и других специфичных данных. • R-Tree — используются для индексирования геометрических данных и пространственных запросов. • Full-text индексы — для быстрого поиска по тексту. Выбор типа индекса зависит от характера данных и типов запросов, которые нужно оптимизировать.

21DBA / DatabaseMiddle

Ответ доступен в приложении.