Повеќето проблеми со перформансите на базата на податоци не започнуваат со очигледно лошо барање. Тие започнуваат со барање што е прифатливо врз мала база на податоци, а потоа станува непредвидливо како што се зголемуваат сообраќајот, бројот на редови и истовремените барања.

Целта не е секое барање да биде паметно. Целта е работата со базата на податоци да биде ограничена, видлива и усогласена со начинот на кој апликацијата навистина чита и запишува податоци. Предвидливите перформанси се својство на дизајнот: тие произлегуваат од избор на шеми на барања чиј трошок останува разбирлив како што расте системот.

Започнете со ограничена работа

Едно барање ретко треба да бара од базата на податоци да разгледа неограничено количество податоци. Барања како SELECT * FROM orders може да се безопасни во развојна средина, но воспоставуваат опасна навика. Продукциските податоци имаат начин удобноста да ја претворат во доцнење, притисок врз меморијата и преоптоварени конекции.

Поставете експлицитно ограничување секогаш кога корисникот навистина не му се потребни сите соодветни записи. Поврзете го тоа ограничување со подредување што е детерминистичко и поддржано од индекс.

SELECT id, customer_id, status, created_at
FROM orders
WHERE customer_id = :customer_id
ORDER BY created_at DESC, id DESC
LIMIT 50;

Ова барање јасно ја соопштува намерата: преземете понов, управлив дел од нарачките на еден клиент. Соодветниот индекс треба да ја одразува шемата на пристап, најчесто почнувајќи со customer_id, а потоа со колоните за подредување каде што е соодветно. Дизајнот на индекси не значи индексирање на секое поле; туку поддршка на филтрите, спојувањата и редоследите на сортирање на кои се потпира апликацијата.

Претпочитајте пагинација со множество клучеви за длабоки множества резултати

Пагинацијата со поместување лесно се пишува:

SELECT id, created_at, title
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 25 OFFSET 10000;

Но големите поместувања бараат од базата на податоци да помине покрај редови што нема да ги врати. Тие може и да создадат незгодни кориснички искуства кога се внесуваат или бришат редови меѓу барањата за страници. Запис може да се појави двапати или да исчезне од низата.

За доводи, текови на активности, ревизорски дневници и други подредени колекции, користете пагинација со множество клучеви. Предадете ги вредностите за подредување од последниот ред како курсор.

SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < (:cursor_created_at, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 25;

Точната синтакса и однесувањето на индексот треба да се потврдат за користениот механизам на база на податоци, но принципот е стабилен: продолжете од позната позиција наместо да прескокнувате сè поголем број редови. Вредностите на курсорот треба да се третираат како дел од API-договорот, внимателно да се валидираат и да се засноваат на стабилно, уникатно подредување.

Елиминирајте ги N+1 барањата на границата

Проблемот со N+1 барања често се крие зад чисто изгледачки код на апликацијата. PHP-циклус вчитува листа со записи, а потоа мрзеливо вчитува поврзан запис за секоја ставка. Педесет редови тивко стануваат педесет и едно патување до базата на податоци.

Прво, одлучете што му е потребно на крајната точка. Потоа намерно вчитајте ги тие податоци. За листа на нарачки на која ѝ требаат имиња на клиенти, спојување може да биде соодветно:

SELECT
    o.id,
    o.status,
    o.created_at,
    c.name AS customer_name
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.created_at >= :from
ORDER BY o.created_at DESC, o.id DESC
LIMIT 100;

Кај односи еден-спрема-многу, спојувањето на сè во еден голем резултат може да дуплира родителски редови и да ги зголеми товарите на одговорот. Во тој случај, две наменски барања може да бидат појасни: преземете ја страницата со родителски ID-а, потоа преземете ги сите потребни деца со ограничено IN барање. Групирајте ги резултатите во кодот на апликацијата. Важната разлика не е „едно барање секогаш е подобро“; туку „бројот и обликот на барањата не треба случајно да растат со бројот на прикажани записи.“

Избирајте податоци намерно

SELECT * е практично, но врзува едно барање за секоја колона во табела. Тоа може да пренесе големи текстуални полиња или blob-ови што крајната точка никогаш не ги користи, да ги направи промените во шемата потешки за разбирање и да прикрие од што зависи апликацијата.

Изберете ги полињата потребни за тековната операција. Ова ја подобрува читливоста исто колку што може да ја подобри ефикасноста. Компактното множество резултати полесно се сериализира, кешира, тестира и прегледува.

Истата дисциплина важи и за агрегатите. Контролната табла не треба да вчита илјадници редови во PHP само за да ги преброи или сумира. Оставете базата на податоци да ја изврши работата заснована на множества, додека опсегот на агрегацијата останува експлицитен.

SELECT status, COUNT(*) AS order_count
FROM orders
WHERE created_at >= :start
  AND created_at < :end
GROUP BY status;

Користете полуотворен временски опсег, како што е прикажано погоре, наместо да се обидувате да создадете временска ознака за „крај на денот“. Тоа избегнува гранични случаи со прецизноста и овозможува соседните прозорци за известување чисто да се вклопат еден со друг.

Направете ги запишувањата мали, атомски и свесни за повторни обиди

Перформансите при читање добиваат внимание, но непредвидливите запишувања често се поштетни. Одржувајте ги трансакциите кратки: започнете ги блиску до првото запишување, извршете ја само работата со базата на податоци потребна за атомската промена и навремено потврдете ја трансакцијата. Не држете трансакција отворена додека повикувате надворешна HTTP-услуга, прикажувате одговор или чекате кориснички внес.

Кога неколку промени мора да успеат или да не успеат заедно, наведете ја таа граница со трансакција. На пример, создавањето нарачка и намалувањето на резервираниот инвентар не треба да остават потврдена само една од тие промени.

$pdo->beginTransaction();

try {
    $reserve = $pdo->prepare(
        'UPDATE inventory
         SET reserved = reserved + :quantity
         WHERE product_id = :product_id
           AND available - reserved >= :quantity'
    );
    $reserve->execute([
        'product_id' => $productId,
        'quantity' => $quantity,
    ]);

    if ($reserve->rowCount() !== 1) {
        throw new RuntimeException('Insufficient inventory.');
    }

    $order = $pdo->prepare(
        'INSERT INTO orders (customer_id, status)
         VALUES (:customer_id, :status)'
    );
    $order->execute([
        'customer_id' => $customerId,
        'status' => 'pending',
    ]);

    $pdo->commit();
} catch (Throwable $error) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }

    throw $error;
}

Во истовремен систем, мртви блокади и минливи неуспеси се можни дури и со разумни барања. Каде што операцијата е безбедна за повторување, додадете ограничена политика за повторни обиди околу целата трансакција. Не обидувајте се слепо повторно за секој исклучок: неуспесите при валидација, прекршувањата на уникатни ограничувања и трајните неуспеси на поврзувањето бараат различно постапување. Клучевите за идемпотентност се особено вредни за надворешно иницирани API за запишување, бидејќи повторниот обид на клиент не треба да создаде втора нарачка.

Користете ограничувања како дел од дизајнот на барањата

Валидацијата во апликацијата е корисна, но базата на податоци останува последниот авторитет за интегритетот на податоците. Надворешни клучеви, уникатни ограничувања, колони што не дозволуваат null и соодветни ограничувања за проверка ги претвораат претпоставките во правила што може да се спроведат.

На пример, уникатно ограничување на надворешна референца за плаќање обезбедува трајна заштита од дуплирана обработка. Надворешен клуч го отежнува создавањето невалидни односи. Ова не се само одбранбени мерки; тие ја поедноставуваат логиката на барањата бидејќи кодот што следи може да се потпре на посилни инваријанти.

Проверувајте планови, не само изминато време

Барање што е брзо денес може да е брзо само затоа што табелата сè уште е мала или кешот е загреан. Кога оценувате важно барање, проверете го неговиот план за извршување со алатките што ги обезбедува базата на податоци. Барајте целосни скенирања на големи табели, скапи сортирања, стратегии за спојување што не одговараат на очекувањата и проценки што остро се разликуваат од вистинскиот број редови, каде што таа информација е достапна.

Потоа тестирајте со реалистични параметри. Барање за еден клиент не е доказ дека истото барање се однесува добро за клиент со години историја. Работата на перформансите се подобрува кога е поврзана со познат облик на барање, обем на редови, индекс и прифатлива големина на резултатот.

Градете API што ја почитуваат реалноста на базата на податоци

Базата на податоци не е пасивен слој за складирање под неограничено API. Дизајнот на крајната точка го одредува однесувањето на барањата. На филтрите им требаат ограничувања, на полињата што може да се пребаруваат им треба план за индексирање, на извозите им требаат асинхрони или тековни работни текови кога множествата резултати се големи, а на крајните точки за листи им требаат стабилни правила за пагинација.

Предвидливите перформанси произлегуваат од изборот на едноставни, експлицитни шеми: тесни читања, стабилни подредувања, поддржани индекси, ограничени страници, намерно вчитување на односи, кратки трансакции и спроведливи ограничувања. Ниту еден од овие избори не е гламурозен. Заедно, тие го прават системот полесен за управување кога е најважно: откако податоците и сообраќајот ќе го направат нагаѓањето скапо.