Хранимая процедура, автоматически выполняемая при INSERT/UPDATE/DELETE.?

Триггер.

Раздел 9: Оптимизация запросов и планы выполнения

Чтение вывода EXPLAIN

EXPLAIN

Команда показывает, как база данных планирует выполнить ваш запрос. Это рентген производительности SQL.

EXPLAIN SELECT * FROM employees WHERE email = 'alice@co.com';

-- PostgreSQL (more detailed)
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM employees WHERE email = 'alice@co.com';

Ключевые столбцы, на которые стоит обратить внимание (MySQL):

type

| Тип соединения |

ALL

= полное сканирование таблицы (плохо). Стремитесь к

const

,

eq_ref

,

ref

,

range

. | |

possible_keys

| Рассматриваемые индексы | Пусто = полезных индексов не существует | |

key

| Индекс фактически использован | NULL = индекс не используется | |

rows

| Строки рассмотрены | Должно быть намного меньше размера таблицы | |

Extra

| Дополнительная информация |

Using filesort

(плохой),

Using temporary

(плохой),

Using index

(отлично!) |

┌──────────────────────────────────────────────────────────────────────────────┐
│                    EXPLAIN OUTPUT CHEAT SHEET                                │
├──────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│  TYPE column (best to worst):                                                │
│                                                                              │
│  const    → PK lookup on constant        [BEST]                              │
│  eq_ref   → PK/unique join              ★★★★★                                │
│  ref      → Non-unique index match      ★★★★☆                                │
│  range    → Index range scan            ★★★☆☆                                │
│  index    → Full index scan             ★★☆☆☆                                │
│  ALL      → Full TABLE scan (NO!)       ★☆☆☆☆                                │
│                                                                              │
│  EXTRA column:                                                               │
│  Using index        → COVERING INDEX (doesn't touch table!) [GREAT]          │
│  Using where        → Filtering after index lookup           [OK]            │
│  Using filesort     → Sorting without index                  [SLOW]          │
│  Using temporary    → Created temp table                     [SLOW]          │
│                                                                              │
└──────────────────────────────────────────────────────────────────────────────┘

10 убийц производительности запросов

1. **

SELECT *

** — Вытягивает ненужные данные; предотвращает покрытие индексов. 2. Функции для индексированных столбцов —

WHERE YEAR(date_col) = 2023

не могу использовать индекс. Использовать

WHERE date_col BETWEEN '2023-01-01' AND '2023-12-31'

. 3. Ведущий подстановочный знак НРАВИТСЯ —

LIKE '%smith'

не могу использовать индекс B-дерева. Вместо этого используйте полнотекстовый поиск. 4. Неявные преобразования — сравнение строкового столбца с числовым приводит к преобразованию; индекс игнорируется. 5. Отсутствуют индексы в столбцах JOIN/FK — Внешние ключи почти всегда должны индексироваться. 6. N+1 запрос — циклическое получение связанных данных. Используйте JOIN или

IN (...)

. 7. Большое СМЕЩЕНИЕ —

OFFSET 1000000

сканирует и отбрасывает 1 млн строк. Используйте пагинацию набора ключей. 8. Сортировка без индекса —

ORDER BY

в неиндексированных столбцах активируется сортировка файлов. 9. Подзапросы в SELECT — коррелирующие подзапросы выполняются один раз для каждой строки; вместо этого используйте JOIN. 10. Не анализировать таблицы. Устаревшая статистика приводит к плохим планам. Бегать

ANALYZE TABLE

.

Раздел 10: Проектирование и нормализация базы данных

1НФ, 2НФ, 3НФ, БКНФ

Нормализация устраняет избыточность и предотвращает аномалии (вставка, обновление, удаление). Думайте об этом как об организации шкафа — у каждой вещи есть одно определенное место.

┌──────────────────────────────────────────────────────────────────────────────┐
│                    NORMALIZATION LEVELS VISUALIZED                           │
├──────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│  BEFORE NORMALIZATION (A Mess)                                             │
│  ┌────────────┬───────────┬────────────────────────────┬──────────┐          │
│  │ Student    │ Course    │ Instructor                 │ Office   │          │
│  ├────────────┼───────────┼────────────────────────────┼──────────┤          │
│  │ Alice      │ Math,Phys │ Prof. Smith, Prof. Jones   │ 101, 205 │          │
│  │ Bob        │ Math      │ Prof. Smith                │ 101      │          │
│  └────────────┴───────────┴────────────────────────────┴──────────┘          │
│  Problems: Repeating groups, redundancy, update anomaly (move office 3x)     │
│                                                                              │
│  1NF → Atomic values, no repeating groups                                    │
│  2NF → No partial dependencies (all non-key columns depend on WHOLE key)     │
│  3NF → No transitive dependencies (no column depends on non-key column)      │
│  BCNF → Every determinant is a candidate key                                 │
│                                                                              │
│  AFTER 3NF (Clean & Organized)                                             │
│  Students(id, name)      Courses(id, name)     Instructors(id, name, office) │
│  Enrollments(student_id, course_id, instructor_id)                           │
│                                                                              │
└──────────────────────────────────────────────────────────────────────────────┘

Подробные правила:

courses: "Math, Physics"

| | 2НФ | Нет частичной зависимости (актуально только для составных ключей) | Цена зависит от

product_id

но не

order_id

в

order_items

стол | | 3НФ | Нет транзитивной зависимости |

employee

таблица имеет

dept_name

когда это должно было быть просто

dept_id

| | БКНФ | Каждый определитель является потенциальным ключом | Стол с

(student, course) → instructor

и

instructor → course

|

Денормализация: когда нарушать правила

Нормализация оптимизирует запись. Иногда чтения настолько часты, что вы намеренно денормализуете:

• Счетчики: рассчитываются заранее.

comment_count

на столе постов в блоге

• Производные столбцы: сохранение.

total_price

когда это

qty * unit_price

• Материализованные представления: предварительно агрегированные отчеты.
• Звездные схемы: хранилища данных намеренно используют денормализованные таблицы фактов/измерений.

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

🎯 Final Interview Tips

Before the Interview

• Практика на бумаге — Многие собеседования проводятся на доске. Пишите запросы без автозаполнения.
• Знайте свой диалект. Если в вакансии указан PostgreSQL, знайте

LIMIT/OFFSET

,

::cast

,

JSONB

, оконные функции.

• Запомните порядок выполнения:

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

• Будьте готовы к оптимизации — «Как бы вы сделали это быстрее?» → Индексы, перезапись запросов, секционирование.

Во время интервью

• Уточните предположения. Спросите об обработке NULL, дублированиях и разрешении конфликтов.
• Начните с простого, затем усовершенствуйте. Сначала напишите рабочий запрос, а затем оптимизируйте его.
• Обсудите свою логику. Объясните, почему вы выбрали JOIN вместо подзапроса или какой индекс вы бы добавили.
• Упомяните крайние случаи. Что, если у двух сотрудников будет равная самая высокая зарплата? Использовать

RANK()

или

DENSE_RANK()

.

Распространенные шаблоны поведения на дому

SUM() OVER

,

AVG() OVER

) | | Топ N в группе |

ROW_NUMBER() OVER (PARTITION BY ...)

| | Пробелы в последовательностях |

LEAD()

/

LAG()

или самостоятельно присоединиться | | Иерархические данные | Рекурсивные CTE | | Сводные данные |

CASE

агрегаты или

PIVOT

(SQL-сервер/Oracle) | | Обнаружение дубликатов |

GROUP BY ... HAVING COUNT(*) > 1

| | Медианный расчет |

PERCENTILE_CONT()

или

NTILE()

|

📚 Ресурсы и дополнительная литература

• Практика: база данных LeetCode, HackerRank SQL, SQLBolt.
• Документация: документация PostgreSQL, справочник MySQL.
• Книги: Объяснение производительности SQL (Маркус Винанд), Изучение SQL (Алан Болье)
• Визуализация: используй Индекс, Люк! — Бесплатное онлайн-руководство по индексированию SQL

«Самый важный навык SQL — это не запоминание синтаксиса, а знание того, как задать правильный вопрос о ваших данных и как база данных ответит на него». Удачи на собеседовании! 🚀

Итог

Ключевой вывод для интервью: автоматическое выполнение хранимой процедуры при INSERT/UPDATE/DELETE?