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

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

Дизајнирајте според обрасците на пристап, а не само според ентитетите

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

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

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

CREATE INDEX idx_orders_customer_created_at
ON orders (customer_id, created_at DESC);

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

Индексите се договори со вашите барања

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

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

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

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

CREATE UNIQUE INDEX users_email_unique
ON users (email);

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

Почитувајте ја кардиналноста пред да ве изненади

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

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

Решението не е секогаш „пишувајте директен SQL“. Зрелото користење на ORM вклучува разбирање на неговото однесување при вчитување. Користете однапред вчитување, спојувања или пакетни барања каде што е соодветно и проверете го генерираниот SQL за патеките со голем сообраќај. Чистиот објектен модел е вреден, но не го ослободува кодот од трошоците на базата на податоци.

Страницирањето има слична замка. Страницирањето со поместување е лесно за имплементација:

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

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

Чувајте го интегритетот на податоците блиску до податоците

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

Користете примарни клучеви, странски клучеви каде што одговараат на оперативниот модел, ограничувања NOT NULL, разумни стандардни вредности, ограничувања за проверка каде што се поддржани и соодветни, и уникатни ограничувања за деловни инваријанти. Апликацијата и понатаму ги валидира влезовите, но базата на податоци останува конечниот авторитет за тоа дали зачуваните податоци се кохерентни.

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

Внимателно избирајте флексибилни полиња

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

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

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

Мерете ги плановите за извршување пред да ја менувате шемата

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

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

Исто така, тестирајте со обем и распределба слични на продукцијата. Развојна база на податоци со рамномерно распределени примерочни редови не може да го открие истото однесување како табела во која еден закупец ги поседува повеќето записи или во која вредност на статус се совпаѓа со речиси секој ред.

Направете ги миграциите безбедни оперативни промени

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

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

Дизајнот на базата на податоци станува одржлив кога се третира како дел од архитектурата на апликацијата. Јасно моделирајте ги податоците, индексирајте ги начините на кои навистина се пристапува до нив, спроведувајте ги инваријантите таму каде што припаѓаат и мерете пред да оптимизирате. Најдоброто време за решавање на перформансите не е откако ќе се активира пејџерот. Тоа е кога едноставна одлука за шемата сè уште има моќ да го одржи утрешниот систем едноставен.