Почему огромные базы начинают тормозить

«Большая база» в разговоре команды обычно означает разное. Для одних это десятки миллионов строк в заказе; для других — сотни гигабайт индексов; для третьих — одна таблица движений на два миллиарда записей в контуре ERP. Практичнее мыслить порогами поведения, а не ярлыками. Пока рабочий набор горячих данных умещается в буферный пул и фильтры селективны, объём на диске почти не ощущается. Боль начинается, когда план читает широкие диапазоны, оценка кардинальности врёт на порядки, или конкуренция за горячие страницы превращает быстрый одиночный запрос в очередь.

Типичный сценарий знаком с площадок внедрения: учётный контур живёт годы, таблица документов или значений атрибутов растёт без архива, отчёты за период сканируют историю, а «временное» решение с индексом на каждый новый фильтр уже никто не пересматривает. Добавление CPU и SSD на время снижает дисковую очередь — и маскирует тот же плохой план. Через квартал нагрузка догоняет железо. Связанный контекст монолита и накопленной сложности: почему ERP становятся монолитами и легаси на пятнадцать лет.

Мифы об оптимизации держатся, потому что иногда срабатывают. «Добавим индекс» помогает, если фильтр селективен и порядок столбцов совпал. «Купим SSD» помогает при чистом случайном чтении страниц. «Перепишем запрос» помогает, когда лишние соединения и неявные преобразования реально в плане. Ошибка — делать любой из этих шагов без измерения: какой ресурс ждёт поток, сколько страниц читается, совпадает ли оценка строк с фактом.

Диагностика: сначала измеряем, потом оптимизируем

Что измерять

Зафиксируйте проблемный сценарий так, чтобы его можно было повторить: конкретный запрос или API, объём входных параметров, время суток. Смотрите не только среднее. Для пользователя важны хвосты: p50, p95, p99. Рядом — пропускная способность (запросы или транзакции в секунду), число логических и физических чтений страниц, CPU, давление на память, длина дисковой очереди, ожидания блокировок и дедлоки. Отдельно зафиксируйте размер таблицы, индексов, журнала транзакций (WAL) и объём временных файлов при сортировках и хеш-соединениях.

Инструменты по движкам

В PostgreSQL базовый контур: EXPLAIN (ANALYZE, BUFFERS) на воспроизводимом запросе и pg_stat_statements для топа по суммарному времени. В SQL Server — Actual Execution Plan, Query Store для регрессий планов, DMV по ожиданиям и при необходимости Extended Events. В MySQL 8 — EXPLAIN ANALYZE, Performance Schema и журнал медленных запросов с осмысленным порогом. На уровне приложения полезны трассы с длительностью SQL и счётчиком кругов к серверу: иначе N+1 выглядит как «медленная база», хотя база отвечает за миллисекунды.

Как читать план

Ищите полное сканирование там, где ожидали узкий индекс; Nested Loop на больших сторонах соединения; Hash Join или Merge Join с неожиданной сортировкой; выгрузку во временные файлы или TempDB. Главный сигнал — расхождение оценочного и фактического числа строк на порядки: устаревшая статистика, корреляции столбцов, параметр, для которого план «нюхнули» один раз. Алгоритм на 15–30 минут: воспроизвести → снять план с фактом → назвать доминирующее ожидание (CPU / I/O / блокировки) → проверить селективность фильтра и актуальность статистики → только потом менять индекс или текст SQL.

Дерево быстрого решения выглядит так. Много логических чтений и широкий диапазон — индекс, покрытие, ранний фильтр. Оценка строк врёт — статистика и параметры. Запрос сам по себе быстрый, под нагрузкой нет — блокировки и горячие страницы. Тяжёлый отчёт по истории — агрегаты, материализация, вынос в аналитическое хранилище, а не ещё один B-tree на OLTP.

Практический проход за четверть часа: (1) воспроизвести на копии или в тихом окне с теми же параметрами; (2) снять план с фактом (ANALYZE / фактический план); (3) выписать главное ожидание за интервал проблемы; (4) сравнить оценку и факт на самых дорогих узлах; (5) решить, это селективность, статистика, блокировка или объём результата. Только после пятого шага открывайте DDL.

Оптимизация SQL-запросов

Начинайте с формы результата. SELECT * на широкой таблице тянет лишние страницы и мешает покрывающему индексу. Фильтруйте как можно раньше: в WHERE и в подзапросах, которые отсекают сущности до соединений. Коррелированные подзапросы часто превращаются в Nested Loop по миллионам строк; сравните с JOIN, EXISTS или полусоединением на реальном плане, а не по вкусу. IN со списком констант и IN с подзапросом — разные звери для оптимизатора.

N+1 в ORM на больших коллекциях убивает p95 даже при идеальных индексах: один список из тысячи id порождает тысячу кругов к серверу. Агрегации и сортировки дороги, если сортируемый набор не умещается в память — смотрите выгрузку на диск. CTE в разных движках ведут себя по-разному: где-то это подсказка оптимизации, где-то материализация; временная таблица с индексом иногда дешевле «красивого» CTE на огромном промежуточном результате. Неявные преобразования (WHERE indexed_col = @nvarchar при int в колонке) снимают индекс молча. В SQL Server отдельно проверяйте подстановку параметров (parameter sniffing): план, хороший для редкого параметра, губит частый. Пагинация через большой OFFSET заставляет движок пройти и отбросить миллионы строк; keyset-пагинация / позиционирование по последнему ключу масштабируется иначе. Функции на индексируемом столбце (WHERE YEAR(date) = 2024) обычно запрещают индексный поиск — вынесите вычисление на константу или используйте индекс по выражению, если движок позволяет. Массовые «починить всё одной транзакцией» операции ломают журнал и блокировки; пакеты с контролируемым размером предсказуее.

Плохой набросок и направление улучшения:

-- было: функция на колонке, SELECT *, фильтр статуса после широкого чтения
SELECT *
FROM movements m
JOIN entities e ON e.id = m.entity_id
WHERE YEAR(m.created_at) = 2024
  AND e.status = 'active';

-- стало: диапазон дат, узкая проекция; статус — через селективный путь
SELECT m.id, m.entity_id, m.amount, m.created_at
FROM movements m
JOIN entities e ON e.id = m.entity_id
WHERE m.created_at >= TIMESTAMP '2024-01-01'
  AND m.created_at <  TIMESTAMP '2025-01-01'
  AND e.status = 'active';

Пагинация «вглубь» списка:

-- дорого на больших таблицах: движок читает и отбрасывает offset строк
SELECT id, title FROM tickets
ORDER BY id
OFFSET 500000 LIMIT 50;

-- keyset: продолжить после последнего ключа предыдущей страницы
SELECT id, title FROM tickets
WHERE id > :last_id
ORDER BY id
LIMIT 50;

EXISTS против агрегирующего DISTINCT на больших сторонах часто выигрывает, когда нужна только проверка наличия. Сравнивайте планы на прод-подобном объёме: стенд из тысячи строк врёт и по выбору Join, и по памяти. Экономика структур данных за такими выборами — в статье про структуры данных как инвестиции; короткий разбор шести тактик — в адаптации про стратегии оптимизации БД.

Индексы: от базовых до случаев, когда они не помогают

B-tree остаётся рабочим конём OLTP. Кластерный / первичный индекс определяет порядок строк в SQL Server и InnoDB (кластер по первичному ключу); в PostgreSQL куча (heap) + вторичные индексы устроены иначе — не переносите привычки один в один. Составной индекс работает слева направо: (status, created_at) поможет фильтру по status и диапазону дат, но почти не поможет одному created_at. Покрывающий индекс (INCLUDE в SQL Server / PostgreSQL) убирает повторное чтение строки из кучи, если все нужные столбцы уже в индексе. Частичные и фильтрованные индексы выгодны, когда горячий срез маленький («только открытые», «только активные»). Индексы по выражениям и вычисляемым столбцам спасают от функций в WHERE. Для JSON и полнотекста нужны отдельные механизмы, не «ещё один B-tree на текст целиком». В PostgreSQL на огромных упорядоченных по времени таблицах BRIN даёт дешёвую метаинформацию о диапазонах блоков — при условии физической корреляции. В SQL Server columnstore уместен для аналитических сканов, не как замена узкому OLTP-поиску по индексу.

Селективность и свежая статистика решают, возьмёт ли оптимизатор индекс. Избыточные и неиспользуемые индексы всё равно оплачиваются при INSERT/UPDATE/DELETE. Фрагментация и перестроение — тема обслуживания, не первая кнопка при медленном SELECT; сначала план.

Индекс «не помогает», когда селективность низкая (статус с двумя значениями на половину таблицы), порядок столбцов не совпал с запросом, статистика устарела, тип сравнивается через преобразование, запрос возвращает слишком большую долю таблицы или оптимизатор выбрал другой план из-за параметров. Тогда «добавить индекс» без разбора — лотерея с налогом на запись.

Схема данных и особый случай EAV в ERP

Нормализация убирает аномалии обновления; контролируемая денормализация покупает дешёвое чтение ценой согласованности при записи. Узкие типы и отказ от «на всякий случай nvarchar(max)» уменьшают строки и плотность страниц. Суррогатные ключи удобны для соединений; естественные — иногда лучше для партиционирования по смыслу, если стабильны. Внешние ключи на миллиардах строк стоят проверок при записи — осознавайте стоимость. Широкая «карточка на сто колонок» бьёт по буферному пулу; вертикальное разделение холодных атрибутов уменьшает горячий ряд. Архив истории и материализованные агрегаты часто дешевле бесконечного наращивания индексов на OLTP.

EAV (сущность–атрибут–значение) раздувается до миллиардов строк, потому что каждое поле карточки — отдельная запись. Фильтр «цвет = X и вес > Y» превращается в несколько соединений или группировок по одной таблице значений. Индекс вида (entity_id, attribute_id, value) помогает точечному чтению атрибута, но плохо закрывает аналитику по многим атрибутам сразу. Типизированные таблицы значений (отдельно числа, строки, даты) улучшают сравнение и статистику. Часто используемые атрибуты выносят в обычные колонки или в узкую таблицу «горячих полей». Кэш карточки, предварительные агрегаты и постепенная миграция без одномоментного «большого взрыва» — рабочие пути; одномоментная переделка ядра ERP обычно дороже года деградации. Детали механики и границ применимости — в разборе EAV; рамка платформы — в проектировании современной ERP.

Партиционирование и таблицы на миллиардах строк

Горизонтальное разбиение режет строки (часто по времени или диапазону ключа); вертикальное — столбцы. Списки и хеш используют, когда диапазон по времени не подходит. Отсечение партиций (partition pruning) срабатывает, когда предикат явно ограничивает ключ партиции; функция на ключе или параметр «невидимый» для отсечения снова читает всё. Локальные и глобальные индексы устроены по-разному в SQL Server, PostgreSQL и MySQL — сверяйтесь с документацией движка, а не с общим слайдом. Стареющие партиции отсоединяют и архивируют без тяжёлого DELETE построчно. Партиционирование ускоряет сопровождение и некоторые диапазонные запросы; оно не лечит плохую селективность внутри партиции и добавляет накладные расходы планировщику. Перестройка монолита в партиции делается по схеме «расширить — сузить»: новая структура рядом, догрузка истории пакетами, двойная запись или CDC, переключение чтения, затем записи.

Массовые операции, журнал и обслуживание

Массовый INSERT и пакеты фиксированного размера предсказуемее одного INSERT на миллион строк в одной транзакции. То же для UPDATE/DELETE: короткие транзакции, контроль роста журнала и блокировок. В PostgreSQL следите за autovacuum и раздуванием (bloat) на горячих таблицах; «вакуум не успевает» проявляется раздутыми таблицами и устаревшими картами видимости. В SQL Server — обслуживание статистики и осмысленное перестроение индексов по факту фрагментации и нагрузки, не по календарю ради галочки. В MySQL — актуальность статистики и стратегии OPTIMIZE и онлайн-изменения схемы по версии. Политика хранения: удаляйте или отсоединяйте устаревшее по политике продукта. Онлайн-изменение схемы снижает простой, но всё равно нагружает I/O и репликацию — планируйте окно и откат.

Транзакции, блокировки и конкуренция

Уровни изоляции и MVCC по-разному платят за согласованность: кто-то блокирует читателей, кто-то раздувает версии строк. Длинная транзакция держит замки и мешает vacuum / очистке. Дедлоки часто лечатся стабильным порядком захвата ресурсов, а не «повтором наугад» без лимита. Оптимистичная конкуренция хорошо работает, когда конфликты редки; пессимистичная — когда столкновения постоянны. Горячая строка счётчика или «одна очередь в таблице» упирается в страницу: иногда помогает пакетная выдача номеров или очередь вне строки. Разделение чтения и записи (реплика, CQRS) снижает конфликт, если бизнес допускает задержку чтения. Если запрос в одиночку быстрый, а под нагрузкой нет — смотрите ожидания и блокировки, а не только CPU плана.

Память, диски и конфигурация сервера

Буферный пул / shared_buffers должны закрывать горячий рабочий набор; иначе вы лечите SQL, а страдаете от физического чтения. NVMe хорошо снимает случайный I/O, но не исправляет полное сканирование истории каждый раз. TempDB и временные файлы — зеркало тяжёлых сортировок и хешей. Параллелизм запросов ускоряет одних и душит других при перегрузке CPU. Пул соединений без потолка превращает базу в очередь; слишком узкий пул — в отказы приложения. Checkpoint, WAL и журналы настраивают под профиль записи, а не «как в блоге для другой СУБД». NUMA важна на больших многосокетных машинах, когда память и процессоры «не рядом». Правило: не копируйте цифры настроек между PostgreSQL, SQL Server и MySQL; и не поднимайте лимиты памяти вслепую — это может увеличить давление и регресс.

Отличить нехватку ресурсов от плохого SQL просто в измерении: высокий CPU при малом числе логических чтений намекает на вычисления и плохие планы; огромные чтения при простое CPU — на I/O и селективность; ожидания блокировок — на транзакции.

Кэширование и предварительные вычисления

Кэш в приложении и Redis спасают повторные чтения горячих карточек и справочников. Кэш результата запроса требует явной политики инвалидации: иначе вы чините скорость ценой тихих рассинхронов. Материализованные представления и таблицы агрегатов переносят стоимость с момента чтения отчёта на момент обновления. CQRS разделяет модель записи и модель чтения, когда отчёты пожирают OLTP. Цель одна: не считать один и тот же тяжёлый отчёт с нуля на каждый клик руководителя.

Масштабирование архитектуры

Вертикальное масштабирование оправдано, пока узкое место — CPU/память одного здорового контура и лицензии позволяют. Реплики чтения разгружают отчёты, если допустима задержка. Шардирование по ключу распределяет данные и боль сопровождения: без стабильного ключа доступа и без готовности к запросам между шардами вы купите простой в другом месте. Федерация по доменам часто здравее «одного шарда на всё». OLTP и OLAP смешивать на одном инстансе дорого: вынесите аналитику, питайте CDC или периодическими выгрузками. Распределённая архитектура раньше времени добавляет сетевые сбои, согласованность и операционную сложность — переходите, когда измерения показывают исчерпание вертикали и локальных техник.

Цепочка от браузера до хранения в современном вебе описана в обзоре пути запроса; здесь фокус уже на том, что происходит внутри СУБД и рядом с ней.

Что важно в разных СУБД и движках

Победителя среди движков нет: есть совпадение механизма и характера нагрузки. Колоночное хранилище плохо заменяет узкий OLTP; документная модель плохо заменяет жёсткие регламентные соединения ERP без цены.

Практические кейсы

Запрос 40 секунд

Сценарий: отчёт «открытые позиции» внезапно ушёл с двух секунд на сорок. План показывает Nested Loop и фактические строки в миллионах при оценке в тысячи. Обновление статистики и переписывание фильтра по диапазону дат возвращают поиск по индексу; логические чтения падают на порядок, CPU — вместе с ними. Урок: сначала кардинальность, потом новый индекс «на всякий случай».

Таблица на 2 млрд строк

Оценка: фильтры по времени составляют 90% нагрузки, удаления истории блокируют таблицу. Партиции по месяцу, индекс внутри партиции под горячий фильтр, отсоединение старых месяцев вместо DELETE. Запросы за текущий квартал перестают трогать холодное.

ERP с EAV

Карточка с двадцатью атрибутами собирается десятью соединениями. Вынос пяти самых частых атрибутов в узкую таблицу и кэш карточки на чтение снимают пик; полная миграция с EAV откладывается и делается по схеме «расширить — сузить». Без этого индексы на value дают локальные победы и глобальный налог на запись.

После индекса стало хуже

Добавили составной индекс под редкий отчёт. OLTP-вставка в ту же таблицу удлинилась, изменился план частого UPDATE, выросли блокировки. Индекс оставили на реплике отчётности / копии на columnstore, с OLTP сняли. Локально правильное решение без цены записи ломает систему.

Миграция и реорганизация без остановки

Схема «расширить — сузить»: добавьте новое рядом со старым, пишите в оба или догоняйте CDC, читайте постепенно из нового, уберите старое. Индексы создавайте онлайн-средствами движка в окне с контролем I/O. Догрузка истории — пакетами с троттлингом. Проверяйте полноту сверками контрольных сумм и выборок, не «глазами». План отката готовьте до переключения записи. На продуктивной системе важнее скорость выкладки — скорость, с которой вы можете остановиться.

Типичные ошибки и чек-лист

Частые промахи: индексы без плана; вера, что партиции ускорят любой SELECT; память «с запасом» без профиля ожиданий; переписывание SQL без воспроизводимого теста; удаление «редких» индексов, которые держат месячный закрывающий отчёт; одна транзакция на миллионы строк; шарды до уборки локального хаоса; бенчмарк на игрушечном объёме; игнор стоимости записи.

Чек-лист одного прохода:

1. Зафиксировать базовые метрики и проблемные запросы.
2. Назвать главное ожидание: CPU, I/O, блокировки, память.
3. Снять планы и сверить оценки кардинальности.
4. Проверить статистику, индексы, селективность.
5. Оценить влияние на чтение и запись.
6. Проверить поведение под конкуренцией.
7. Рассмотреть партиции и архив.
8. Рассмотреть кэш и агрегаты.
9. Только потом — архитектурный вынос и шарды.
10. Повторить тест и записать результат.

Матрица применимости методов

Читайте матрицу сверху вниз: пока строка выше не исчерпана по измерениям, прыгать вниз рано. Исключение — когда уже доказано, что отчёты систематически бьют запись: тогда вынос чтения можно планировать параллельно с локальным тюнингом, а не вместо него.

Частые вопросы

Что почитать дальше

Смежные материалы на сайте:

• Почему EAV уничтожает производительность ERP
• Почему ERP становятся монолитами
• Современная ERP-платформа в 2026
• Стратегии оптимизации БД, которые режут время запросов
• Экономика структур данных
• От браузера до базы данных
• ИИ и проект с 15-летней историей

Заключение

У большой базы нет одной кнопки «стало быстро». Есть цикл: измерить → найти класс ожидания → изменить запрос, индекс, схему или контур данных → проверить чтение и запись под нагрузкой → закрепить мониторингом. Иногда хватает индекса и честной статистики. Иногда нужен архив и партиции. Иногда — вынести отчётность и перестать строить ядро на EAV. Выбирайте по измерениям, не по моде на технологии. На этой неделе достаточно одного шага: взять самый медленный запрос из топа, снять план с буферами или фактический план и записать, чего он ждёт на самом деле.