Бази данни под натоварване: заявки, индекси, мащабиране и компромиси

Историите за производителност са скучни, докато не спрат да бъдат. Таблото е зелено на деветдесет милисекунди, после идва кампания, справка събира шест джойна и изведнъж плащането и нулирането на парола чакат на една и съща опашка зад едно и също хранилище. Рядко помага едно копче — по-често са по-малко round-trip-ове, индекси според реалните WHERE, честна сметка за капацитет и понякога признание, че една логическа база не е безкрайна.

Свързани материали: Високонатоварени потоци от събития · Сравнение на опашки и брокери · Sail: бази и Docker услуги

Съдържание

Първо мерете, после „оптимизирайте“

Перцентилите на латентността са по-информативни от средната стойност. Средно 40 ms може да крие опашка, където един процент от заявките минават две секунди заради заключвания или студен кеш.

Практични сигнали:

  • Лог на бавни заявки с праг, който преглеждате с растежа на данните — не „всичко“, за да не свикнете с шума.
  • EXPLAIN (Postgres) / EXPLAIN (MySQL 8+) с реалните параметри от продукшън.
  • Брой връзки и пълен пул; много инциденти „базата е бавна“ са чакане за connection.

Базата е споделено състояние. Натоварването на CPU, заключвания и I/O на първичния възел се надпреварва с всичко критично.

Оптимизация на заявки с примери

Вземайте само нужните колони

Широкият SELECT * по „дебели“ редове кара СУБД да влачи байтове, които приложението хвърля. Изричен списък от колони е важен при голям текст или JSON.

-- По-слабо: всички колони
SELECT * FROM users WHERE country = 'DE' LIMIT 50;

-- По-добре
SELECT id, email, display_name, created_at
FROM users
WHERE country = 'DE'
ORDER BY created_at DESC
LIMIT 50;

Форма на джойна и мощност на връзките

Планировъкът избира стратегия според двигателя и статистиката. Ако джойнът надува редове заради много-към-много без ограничения, оправете модела, не само таймаута.

Пагинация без сканиране на целия офсет

При голям OFFSET двигателят често обхожда прескочените редове.

SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

Keyset пагинация с последния ключ на сортиране:

SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Нужен е индекс в същия ред (напр. (created_at DESC, id DESC)).

EXISTS срещу IN за „има ли ред“

SELECT o.id, o.total_cents
FROM orders o
WHERE EXISTS (
  SELECT 1 FROM order_items i
  WHERE i.order_id = o.id AND i.sku = 'GOLD-001'
);

Сляпо пренаписване на всеки IN (подзаявка) не е правило — потвърдете с EXPLAIN.

Агрегации и отчети

Тежък GROUP BY върху сурови OLTP таблици удря опашката на латентността. Предварително обобщение в обобщени таблици, materialized views (Postgres) или OLAP слой (вж. гайда за потоци от събития).

Схема, която не отмъщава след година

  • Типове за пари — малки единици като цяло число или DECIMAL с ясна точност, не float, ако има сверка.
  • NULL — когато „неизвестно“ е част от домейна; като мързелив default усложнява индекси и статистика.
  • Външни ключове — малко струват на запис и дават референтна честност. Изключването „за скорост“ често води до сираци и недетерминизъм в приложението.
  • Нормализация срещу четене — чистата теория не знае честотата на заявките ви. Съзнателна денормализация с документация може да победи джойнове — цената е сложност при запис и инварианти.

Видове индекси и кога помагат

Идея Типична употреба Бележки
B-tree (подразбиране) Равенство и интервал Повечето индекси; при съставен редът на колоните е критичен — водещите трябва да съвпадат с типичните WHERE / ORDER BY.
Hash Точно равенство (където се поддържа) При Postgres в стари версии имаше нюанси с репликация — вижте версията. При MySQL hash е по-близо до MEMORY и вътрешности на InnoDB.
Пълнотекст Търсене по токени InnoDB FTS срещу tsvector + GIN в Postgres.
GIN / GiST (Postgres) JSONB, масиви, пълнотекст, част от геото Мощно; следете раздуване и цена на построяване.
Пространствени Геозаявки Често еталонът е PostGIS в Postgres; MySQL има свои правила за типове.

Съставен индекс: ред на колоните

Ако почти винаги филтрирате по tenant_id, той обикновено е първи:

-- Добре за WHERE tenant_id = ? AND status = 'open'
CREATE INDEX orders_tenant_status_idx ON orders (tenant_id, status);

Заявка само по status може да не използва индекса ефективно — понякога е нужен втори индекс или съзнателен компромис.

Покриващ индекс

Ако индексът съдържа всички колони от SELECT, планът може да се задоволи само с него (в Postgres — Index Only Scan; в InnoDB — при подходящ клъстерен ключ).

CREATE INDEX sessions_lookup_idx ON sessions (user_id) INCLUDE (last_seen_at);
-- INCLUDE е Postgres 11+; в MySQL често съставен (user_id, last_seen_at).

Частичен индекс (Postgres)

CREATE INDEX invoices_open_due_idx
ON invoices (due_at)
WHERE status = 'open' AND due_at IS NOT NULL;

В MySQL 8+ има индекси по изрази; частичните индекси са по-удобни в Postgres — наближаване с генерирана колона + индекс.

Защо тежката логика в базата удря екипа

Съхранени процедури, тригъри и сложни изгледи не са „зло“ — те са избор за деплой и собственост.

Какво често се чупи:

  • Версиониране — кодът на приложението минава през Git и rolling deploy; рутините живеят отделно; дрейф между среди боли.
  • Тестване — бизнес правилата в PHP/Java се покриват по-лесно; вериги от тригъри са по-трудни за изолация.
  • Преносимост — логиката в приложението оцелява при смяна на СУБД; PL/pgSQL или MySQL процедури вързват и усложняват blue/green.
  • Наблюдаемост — APM е центриран в приложението; тригърите дават непрозрачна латентност без допълнителна инструментация.

Кога логиката в СУБД има смисъл: CHECK, външни ключове, уникалност по бизнес ключ, тънък одит с документация и тестове на миграции.

Полезен лозунг: не „никога в базата“, а „тежките бизнес процеси са в код със същия release pipeline като останалата система“.

MySQL срещу PostgreSQL на практика

И двете са production-grade. Разликите ухапват, ако ги третирате като взаимозаменяеми.

Тема MySQL (обикновено InnoDB) PostgreSQL
Физическо подреждане Клъстериран първичен ключ; вторичните индекси сочат към PK Heap таблици; индексите са отделно; CLUSTER е операция по поддръжка
MVCC / боклук Undo; закъснение на purge при дълги транзакции Мъртви версии; VACUUM / autovacuum е част от експлоатацията
Възможности на SQL Солидно ядро По-богати CTE, прозоречни функции, LATERAL, оператори за JSONB, диапазонни типове
Разширения По-тесни в ядрото PostGIS, pgvector, citext и др.
Репликация Зрял binlog стрийминг Физическа и логическа; публикации/абонаменти за избирателни потоци
Изолация Подразбиранията понякога изненадват идващите от Postgres Позната на мнозина MVCC картина; остават аномалии при сериализуемост
JSON JSON; в 8.x — функционални индекси JSONB, GIN, богати оператори за съдържание

Postgres — съдържание в JSONB:

SELECT id FROM events
WHERE payload @> '{"kind":"purchase"}'::jsonb;

MySQL 8.x (често с generated STORED колона + индекс под натоварване):

SELECT id FROM events
WHERE JSON_UNQUOTE(JSON_EXTRACT(payload, '$.kind')) = 'purchase';

Евристики за избор

  • Postgres — богат SQL, JSONB заявки, гео, разширения, логическа репликация между слоеве.
  • MySQL/MariaDB — ако хостингът, екипът или екосистемата вече са там.

И двете възнаграждават дисциплина с индекси и поддръжка (vacuum/purge).

Пътища за мащабиране и проблемите им

Вертикално мащабиране

Просто, докато не ударите еднопоточно прилагане на репликация, пропускливост на диска и NUMA. Плюс един домен на отказ.

Реплики за четене

Плюсове: разтоварване на четене, бекъпи, клонове за отчети. Минуси: лаг на репликацията — четенията са остарели; маршрутизацията трябва да е явна (Laravel може да раздели read/write в конфигурацията).

Ако кодът очаква да прочете собствената си запис от реплика — потребителят вижда „запазих — опресних — изчезна“.

Пул на връзките

Отваряне на TCP+auth на всяка HTTP заявка не се мащабира. PgBouncer (Postgres) или пулери при managed MySQL стоят между приложението и базата. Внимание към prepared statements и transaction pooling — ORM понякога предполага сесийна връзка.

Кеш (Redis и др.)

Кешът маскира горещи ключове, не лекува усилване на записа върху primary. Нужни са TTL, инвалидация и защита срещу cache stampede.

Декомпозиция без приказки

Отделна база на сервис означава по-малко случайни джойнове и по-ясен радиус на отказ. Цената са разпределени транзакции или саги, когато едно действие на потребителя реално минава през няколко хранилища.

Работещи модели: таблица outbox при собственика на събитието и пренасочване към брокер (връзка с гайда за опашки); read модели, преизградени от събития, с честен eventual consistency в UX.

Антипатерн: два сервиса пишат една таблица — това е разпределен монолит с излишни мрежови извиквания отгоре.

Шардиране: ключ, кръстосано-шардова болка, ребаланс

Шардирането разпределя редове между няколко първични възела по ключ (често user_id, tenant_id, регион).

Какво подобрява: таван на запис на машина; blast radius може да се свие при изолирани сбои — ако приложението понася частична деградация.

Какво влошава: кръстосано-шардови джойнове и глобална уникалност — нужни са координация, 2PC (скъпо) или правила в приложението; ребаланс при горещ наемател е миграционен проект; оперативна сложност — N копия на бекъп, кръпки и миграции на схема.

-- Еднаква схема на всеки шард; данни за tenant_id = 42 на шард от hash(tenant_id).
-- Отчет „за всички наематели“ става fan-out по всички шардове със сливане — скъпо и крехко.

Повечето екипи трябва да изчерпат индекси, заявки, кеш, реплики и архив/партициониране преди клиентско шардирано OLTP.

Чеклист преди шардове и железо

  1. Показва ли EXPLAIN последователно сканиране, което честен индекс премахва?
  2. Има ли N+1 на ниво ORM независимо от скоростта на диска?
  3. Тесното място връзки, CPU на базата или заключвания от дълги транзакции?
  4. Измерен ли е лагът на репликите, ако четенето отиде на реплики?
  5. Може ли ограничаване на растежа чрез задържане и архив на партиции по-евтино от нова топология?

Базите награждават скучна коректност и измерване. Индексите и изборът на двигател купуват време; архитектурата — опашки, отделни read модели и понякога шардове — купува таван. Изберете най-малката промяна, която премахва измерения тесен участък, и мерете отново — утрешният bottleneck рядко съвпада с вчерашния.