--- title: "Оптимизация на заявки в бази данни: Майсторски клас" description: "Научете как да четете EXPLAIN и EXPLAIN ANALYZE, да оптимизирате JOIN и CTE, да имплементирате keyset пагинация и да разберете поведението на MVCC при натоварване." published: 2026-05-31 faq: - question: "Каква е разликата между Sequential Scan (последователно сканиране) и Index Scan (сканиране по индекс)?" answer: "Sequential Scan чете всяка страница в таблицата от началото до края, за да намери съвпадащи редове. Index Scan преминава през дървото на индекса, за да локализира съвпадащите записи, и след това извлича само съответните страници от купчината (Heap). За малки таблици или заявки, извличащи голям процент от редовете, Seq Scan често е по-бърз от случайния входно-изходен процес (random I/O) на Index Scan." - question: "Какво представляват Nested Loops, Hash Joins и Merge Joins?" answer: "Nested Loops съединяват таблици чрез сканиране на външната таблица и търсене на съвпадащи редове във вътрешната (оптимално за малки набори). Hash Joins изграждат хеш таблица в паметта от по-малката релация и сканират по-голявата за намиране на съвпадения (оптимално за големи несортирани набори). Merge Joins сортират и двете релации по ключа за съединяване и ги сканират паралелно (оптимално за големи предварително сортирани набори или когато сканирането по индекс избягва сортиране)." - question: "Защо keyset пагинацията е по-бърза от LIMIT/OFFSET?" answer: "Заявките с LIMIT/OFFSET трябва да прочетат и отхвърлят всички редове до стойността на отместването (OFFSET), което води до линейно влошаване на производителността O(N) при по-дълбоки страници. Keyset пагинацията използва WHERE филтър върху сортирана уникална колона (като `WHERE id > ?`), за да скочи директно до целевия ред с помощта на индекс, постигайки константно време за търсене O(log N)." - question: "Какво е бариера за оптимизация на CTE?" answer: "В по-старите версии на Postgres (преди 12) и по избор в по-новите чрез `MATERIALIZED`, Common Table Expressions (CTE) действат като бариера за оптимизация, при която базата данни първо изпълнява CTE, записва резултата във временно пространство и след това го съединява. Това пречи на оптимизатора да прокара филтрите от външната заявка (като WHERE клаузи) навътре в CTE, което може да доведе до бавно изпълнение." --- # Оптимизация на заявки в бази данни: Майсторски клас Една единствена бавна заявка може да срине цяло корпоративно приложение. В реална среда производителността на базата данни рядко зависи от мощността на процесора; тя зависи от това колко ефективно планиращият механизъм (query planner) навигира дисковите страници, изгражда таблици за съединяване в паметта и управлява конкурентния достъп. Когато пишете SQL заявка, вие описвате *какви* данни искате, а не *как* да ги извлечете. Оптимизаторът на заявки на базата данни е отговорен за начертаването на плана за изпълнение. За да пишете високопроизводителни приложения, трябва да се научите да мислите като оптимизатора. В този майсторски клас ще анализираме планове за изпълнение, ще разгледаме алгоритми за съединяване, ще проучим мащабирането на пагинацията и ще анализираме MVCC механиката на PostgreSQL и MySQL. --- ## 1. Декодиране на EXPLAIN и EXPLAIN ANALYZE За да оптимизирате заявка, първо трябва да проверите нейния план за изпълнение с помощта на `EXPLAIN`. Въпреки това, стандартният `EXPLAIN` показва само *оценката* на оптимизатора за цената на заявката. За да видите какво всъщност се е случило по време на изпълнението, трябва да използвате `EXPLAIN ANALYZE` (което действително изпълнява заявката). ### Разбиране на индикаторите за цена (формат на Postgres) Планът за изпълнение показва разходите във формат: `cost=0.00..431.25 rows=10500 width=244`. * **Startup Cost (`0.00`):** Цената, натрупана преди първият ред да бъде върнат (напр. изграждане на хеш таблица или сортиране на индексни възли). * **Total Cost (`431.25`):** Очакваната цена за връщане на всички редове. Измерва се в произволни единици за извличане на страници (обикновено `1.0` за последователно четене на страница, `4.0` за случайно четене на страница). * **Rows (`10500`):** Очакваният брой върнати редове. * **Width (`244`):** Средният размер на върнатите редове в байтове. ### Типове сканиране на възли (Scan Types) 1. **Sequential Scan (Seq Scan):** Енджинът чете цялата таблица от началото до края. Оптимално е за малки таблици или при извличане на $> 20\%$ от редовете на таблицата. 2. **Index Scan:** Енджинът преминава през B-Tree на индекса, извлича съвпадащите TID/PK и след това извлича съответните страници от купчината/таблицата. Това включва случаен дисков достъп. 3. **Index Only Scan:** Ако всички избрани колони се съдържат в самия индекс, базата данни избягва четенето на таблицата изцяло. 4. **Bitmap Index Scan & Bitmap Heap Scan (Postgres):** При извличане на множество съвпадащи редове чрез индекс, Postgres първо изгражда битова карта (bitmap) на съвпадащите страници в паметта (Bitmap Index Scan) и след това чете тези страници в последователен физически ред (Bitmap Heap Scan). Това превръща бавното случайно I/O в по-бързо последователно I/O. ### Алгоритми за съединяване (Join Algorithms) * **Nested Loop:** Базата данни сканира външната таблица и за всеки ред търси съвпадащи редове във вътрешната таблица. Това е изключително бързо за малки набори от данни, особено ако колоната за съединяване на вътрешната таблица е индексирана. * **Hash Join:** Базата данни сканира по-малката таблица, изгражда хеш таблица в паметта от ключовете за съединяване и след това сканира по-голямата таблица, като хешира нейните ключове за незабавно намиране на съвпадения. Оптимално за големи, несортирани набори от данни. * **Merge Join:** И двете таблици се сортират по техните ключове за съединяване и базата данни ги сканира паралелно, като съединява съвпадащите редове. Това е изключително ефективно за много големи набори от данни, особено ако са предварително сортирани от индекси. --- ## 2. Продвинута оптимизация на JOIN и CTE Не всички съединявания са еднакви. Начинът, по който пишете подзаявки и CTE, влияе силно върху способността на оптимизатора да съкращава пътищата за търсене. ### Inner срещу Outer Joins и Anti-Joins Анти-съединяването (anti-join) намира записи в една таблица, които *не* съществуват в друга. Разработчиците често пишат това с `NOT IN`, което има лоша производителност и се проваля, ако подзаявката върне `NULL`. Силно оптимизиран подход е използването на `NOT EXISTS`. ```sql -- database/queries/anti_join_bad.sql -- Лошо: NOT IN сканира подзаявката и се държи неочаквано, ако има NULL стойности SELECT id, name FROM users WHERE id NOT IN (SELECT user_id FROM orders); -- database/queries/anti_join_good.sql -- Добро: NOT EXISTS се превежда до силно оптимизиран Hash Anti Join или Merge Anti Join SELECT u.id, u.name FROM users u WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.id ); ``` ### LATERAL Joins (Корелирани подзаявки) `LATERAL` съединяването действа като SQL `foreach` цикъл. То позволява на подзаявка да реферира колони от предходни таблици в клаузата `FROM`. Това е невероятно полезно за извличане на \"топ N\" записи за всяка група. ```sql -- database/queries/lateral_join.sql -- Извличане на последните 3 поръчки за всеки потребител (Postgres & MySQL 8.0.14+) SELECT u.id, u.name, recent_orders.id AS order_id, recent_orders.amount, recent_orders.created_at FROM users u CROSS JOIN LATERAL ( SELECT o.id, o.amount, o.created_at FROM orders o WHERE o.user_id = u.id ORDER BY o.created_at DESC LIMIT 3 ) AS recent_orders; ``` ### CTE бариери за оптимизация (Optimization Fences) Common Table Expressions (CTEs) правят кода на заявката чист, но исторически са действали като **бариери за оптимизация**. * **Postgres (Преди 12):** CTE-тата винаги се материализираха (изчисляваха се и се записваха във временно пространство) преди изпълнението на външната заявка. Това пречеше на използването на индекси от външните WHERE клаузи. * **Postgres (12+):** CTE-тата вече се вграждат (inline) по подразбиране, освен ако не са рекурсивни или сьс странични ефекти. Можете явно да форсирате или предотвратите материализацията с помощта на `MATERIALIZED` или `NOT MATERIALIZED`. ```sql -- database/queries/cte_fence.sql -- Предотвратяване на материализацията, за да се позволи на планировъчния механизъм да спусне филтъра по user_id надолу WITH user_stats AS NOT MATERIALIZED ( SELECT user_id, COUNT(*) as total_orders, SUM(amount) as total_spent FROM orders GROUP BY user_id ) SELECT u.name, s.total_orders, s.total_spent FROM users u JOIN user_stats s ON u.id = s.user_id WHERE u.id = 42; ``` --- ## 3. Мащабиране на заявки: Keyset пагинация & Партициониране Когато таблиците в базата данни нараснат до милиарди редове, простите заявки ще се забавят, освен ако не промените начина на сканиране. ### Keyset пагинация (Базирана на курсор) срещу LIMIT / OFFSET Пагинацията с отместване (`LIMIT 10 OFFSET 100000`) принуждава базата данни да прочете и отхвърли 100 000 реда само за да върне 10. Производителността се влошава линейно ($O(N)$), което прави дълбоките страници изключително бавни. Keyset пагинацията филтрира вече видяните редове с помощта на where клауза върху сортиран индекс, мащабирайки се с $O(\log N)$ константно време за търсене. ```sql -- database/queries/offset_pagination_bad.sql -- Лошо: Сканира и отхвърля 100 000 записа преди да върне 10 SELECT id, amount, created_at FROM orders ORDER BY created_at DESC, id DESC LIMIT 10 OFFSET 100000; -- database/queries/keyset_pagination_good.sql -- Добро: Скача директно до последния видян набор от ключове чрез сканиране на съставен индекс SELECT id, amount, created_at FROM orders WHERE (created_at, id) < ('2026-05-30 15:30:00', 987654) ORDER BY created_at DESC, id DESC LIMIT 10; ``` > [!NOTE] > Keyset пагинацията изисква детерминирано сортиране. Ако сортирате по неуникална колона като `created_at`, трябва да добавите уникална колона като `id`, за да предотвратите липсващи или дублиращи се редове. ### Партициониране на таблици (Table Partitioning) Партиционирането разделя една голяма таблица на по-малки физически таблици (партиции) въз основа на ключ (напр. диапазони от дати), като същевременно се запазва единен логически интерфейс. * **Query Pruning:** Когато заявката филтрира по ключа за партициониране, планировъчният механизъм напълно игнорира нерелевантните партиции и сканира само съвпадащата партиция. ```sql -- database/migrations/2026_05_31_partitioned_orders.sql -- PostgreSQL декларативно партициониране по диапазон CREATE TABLE orders_partitioned ( id BIGINT NOT NULL, user_id BIGINT NOT NULL, amount DECIMAL(10,2) NOT NULL, created_at TIMESTAMP NOT NULL, PRIMARY KEY (created_at, id) ) PARTITION BY RANGE (created_at); -- Създаване на индивидуални партиционни таблици CREATE TABLE orders_2026_q1 PARTITION OF orders_partitioned FOR VALUES FROM ('2026-01-01 00:00:00') TO ('2026-04-01 00:00:00'); ``` --- ## 4. MVCC, Vacuuming и Purge Lag Конкурентността в съвременните бази данни се постига чрез **Multi-Version Concurrency Control (MVCC)**. Вместо да заключват редове за четене, базите данни поддържат няколко версии на един ред едновременно. ### PostgreSQL MVCC & Autovacuum В Postgres всеки ред (кортеж) се съхранява на диска със системни метаданни, включително `xmin` (ID на транзакцията, създала реда) и `xmax` (ID на транзакцията, изтрила/модифицирала реда). * **Актуализации:** Операцията `UPDATE` не презаписва реда. Тя записва напълно нов кортеж в купчината и задава `xmax` на стария кортеж да сочи към транзакцията за актуализиране. * **Раздуване (Bloat):** Старата версия на реда се превръща в \"мъртъв кортеж\", след като никоя активна транзакция не може да я види. * **Vacuuming:** Postgres изисква `VACUUM` (управляван от демона autovacuum) за сканиране на страниците, възстановяване на пространството, заето от мъртви кортежи, и актуализиране на картата на видимост. Ако autovacuum не може да смогне при интензивен запис, раздуването на таблиците и индексите ще влоши производителността на заявките. ### MySQL InnoDB MVCC & Undo Logs Енджинът InnoDB на MySQL управлява MVCC по различен начин. * **Актуализации на място:** InnoDB извършва актуализациите на място в листовия възел на клъстерирания индекс. * **Undo Logs & Rollback Segments:** Старата версия на реда не се съхранява в основната таблица. Вместо това InnoDB записва историческото състояние на реда в **Undo Log** и съхранява указател за отмяна (rollback pointer) в актуализирания ред. Когато четеща транзакция се нуждае от по-стара версия, тя чете текущия ред и възстановява старото състояние с помощта на undo logs. * **Purge Threads:** С приключването на старите транзакции, фонов процес, наречен **Purge Thread**, изчиства undo logs и изтрива записи, които са били маркирани за изтриване. При голямо натоварване със записи може да възникне изоставане в изчистването (**Purge Lag**), което води до огромно нарастване на undo log файловете и спад в производителността. --- ## 5. Чести грешки ### Грешка 1: Дълбока пагинация с OFFSET **Лоша практика:** ```sql -- database/queries/bad_pagination.sql SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 50000; ``` *Защо е лошо:* Базата данни трябва да сканира през индекса/купчината за 50 000 записа, да ги зареди в паметта и да ги отхвърли, консумирайки процесорно време и дисков I/O. **Добра практика:** ```sql -- database/queries/good_pagination.sql -- Keyset пагинация с използване на последния видян ID (напр. 50000) SELECT * FROM users WHERE id > 50000 ORDER BY id LIMIT 20; ``` *Защо е добро:* Базата данни извършва търсене по индекс (index seek), за да скочи директно до `id > 50000` за време $O(\log N)$, като избягва излишно сканиране на редове. ### Грешка 2: Липса на индекс върху външни ключове (Foreign Keys) **Лоша практика:** ```sql -- database/migrations/bad_foreign_key.sql CREATE TABLE orders ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT, FOREIGN KEY (user_id) REFERENCES users(id) ); ``` *Защо е лошо:* PostgreSQL и MySQL не създават автоматично индекси върху колоните за външни ключове. Ако често съединявате таблици по `user_id` или изтривате потребители (което задейства каскадни проверки), енджинът трябва да извърши последователно сканиране (Seq Scan) на подчинената таблица. --- ## 6. Тест за самопроверка ### Въпрос 1 По време на изпълнение на `EXPLAIN ANALYZE` в Postgres забелязвате възел, означен като `Bitmap Heap Scan`, следващ `Bitmap Index Scan`. Какво показва това?
Кликнете тук, за да видите отговора **Отговор:** Показва, че Postgres е преценил, че директното четене на редове чрез отделни търсения в индекса би довело до твърде бавно случайно I/O. Вместо това: 1. `Bitmap Index Scan` сканира индекса и изгражда битова карта на адресите на страниците от таблицата, съдържащи съвпадащи редове. 2. `Bitmap Heap Scan` след това чете страниците на таблицата в последователен физически ред, достъпвайки съвпадащите редове в тези страници. Това превръща случайното I/O в по-бързо последователно дисково четене.
--- ### Въпрос 2 При голямо натоварване със записи в MySQL InnoDB, защо размерът на табличното пространство за undo log нараства изключително много и как това се отразява на заявките за четене, които се нуждаят от по-стари данни?
Кликнете тук, за да видите отговора **Отговор:** Когато записите са много интензивни, нишката за изчистване (Purge Thread) може да изостане (Purge Lag) при почистването на сегментите за отмяна. В резултат на това пространството за undo log расте бързо, за да проследява по-старите версии на редовете. По-старите транзакции за четене ще изпитат по-бавна производителност, тъй като трябва да преминат през дълга верига от undo logs, за да възстановят състоянието на данните към момента на стартиране на тяхната транзакция.
--- ### Въпрос 3 Защо CTE, дефинирано със стандартна клауза `WITH` в Postgres 10, може да причини тесен участък (bottleneck) в производителността, ако външната заявка филтрира по конкретен ID?
Кликнете тук, за да видите отговора **Отговор:** В Postgres 10 (и по-стари версии) CTE-тата действат като бариера за оптимизация. Енджинът оценява CTE независимо и материализира пълните му резултати в паметта/диска. Едва след това изпълнява външната заявка и прилага филтъра `WHERE id = 42`. В Postgres 12+ (или чрез изписване на `WITH ... AS NOT MATERIALIZED`), оптимизаторът може да вгради (inline) CTE-то, което му позволява да спусне филтъра `WHERE id = 42` надолу в подзаявката, позволявайки сканиране, управлявано от индекси.