Подобряване на производителността на базата данни: Практически съвети

Ние от 1cloud говорим много за собствения си опит при работа с доставчик на виртуална инфраструктура и тънкостите при организирането на вътрешни процеси. Днес решихме да поговорим малко за оптимизацията на базата данни.

Много СУБД са способни не само да съхраняват и управляват данни, но и да изпълняват код на сървъра. Съхранените процедури и задействания са примери за това. Само една операция за промяна на данни обаче може да стартира няколко задействания и съхранени процедури, които от своя страна ще „събудят“ още няколко.

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

Очевидно е, че трябва внимателно да използвате разширената функционалност, за да не зареждате сървъра, защото всичко това може да повлияе на производителността на клиентските приложения, използващи тази база данни.

Погледнете графиката по-долу. Той показва резултатите от извършване на тестване на натоварване на приложение, когато броят на потребителите (синя графика), работещи с базата данни, постепенно се увеличава до 50. Броят на заявките (оранжев), които системата може да обработи, бързо достига своя максимум и спира да расте, докато времето за реакция (жълто) постепенно се увеличава.

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

Ето защо по-долу ще дадем няколко практически съвета, които да помогнат за подобряване на производителността на SQL бази данни.

Използвайте индекси

Индексирането е ефективен начин за настройка на вашата база данни и често се пренебрегва по време на разработката. Индексът ускорява заявките, като осигурява бърз достъп до редове данни в таблица, подобно на това как индексът в книга ви помага бързо да намерите информацията, която искате.

Например, ако създадете индекс на първичен ключ и след това търсите ред данни, като използвате стойностите на първичния ключ, SQL Server първо намира стойността на индекса и след това го използва за бързо намиране на реда с данни. Без индекс ще бъде извършено пълно сканиране на всички редове на таблици и това е загуба на ресурси.

Трябва обаче да се отбележи, че ако вашите таблици са „бомбардирани“ с методите INSERT, UPDATE и DELETE, индексирането трябва да се предприема с повишено внимание - това може да доведе до влошаване на производителността, тъй като след извършване на горните операции, всички индекси трябва да бъдат променени.

Освен това, когато голям брой редове (например повече от един милион) трябва да бъдат добавени към таблица наведнъж, DBA често нулират индексите, за да ускорят процеса на вмъкване (индексите се създават отново след вмъкването). Индексирането е обширна и интересна тема и краткото описание не е достатъчно, за да започнете. Повече информация по тази тема можете да намерите тук.

Не използвайте цикли с много итерации

Представете си ситуация, когато 1000 заявки идват последователно във вашата база данни:

Не се препоръчва да пишете такива цикли. Примерът по-горе може да бъде повторен, като се използва един INSERT или UPDATE с множество параметри:

ВЪВЕЖДАНЕ В ИМЕ на Име на таблица (A, B, C) СТОЙНОСТИ (1,2,3), (4,5,6), (7,8,9)

АКТУАЛИЗИРАНЕ ИМЕ НА ТАБЛИЦАТА A = СЛУЧАЙ B
КОГА 1 ТОГАВА "НОВА СТОЙНОСТ"
КОГА 2 ТОГАВА "НОВА СТОЙНОСТ 2"
КОГА 3 ТОГАВА "НОВА СТОЙНОСТ 3"
КРАЙ

КЪДЕ B в (1,2,3)

Уверете се, че клаузата WHERE не замества дублирани стойности. Тази проста оптимизация може да ускори изпълнението на SQL заявката, като намали броя на актуализираните редове от хиляди на стотици. Пример за проверка:

АКТУАЛИЗИРАНЕ ИМЕ НА ТАБЛИЦАТА
КОМПЛЕКТ A = @VALUE
КЪДЕТО
B = "ВАШЕТО СЪСТОЯНИЕ"
И A <> @VALUE - ВАЛИДАЦИЯ

Избягвайте корелирани подзаявки

Корелирана подзаявка е подзаявка, която използва стойностите на родителската заявка. Изпълнява се ред по ред, веднъж за всеки ред, върнат от външната (родителска) заявка, което забавя скоростта на базата данни. Ето един прост пример за корелирана подзаявка:

ИЗБЕРЕТЕ c.Name, c.City,
(ИЗБЕРЕТЕ ИМЕ НА КОМПАНИЯТА ОТ КОМПАНИЯ, КЪДЕ ИД = c.CompanyID) КАТО Име на компанията
ОТ клиент c

Проблемът тук е, че вътрешната заявка (SELECT CompanyName ...) се изпълнява за всеки ред, който външната заявка връща (SELECT c.Name ...). За да подобрите производителността, можете да пренапишете подзаявката чрез JOIN:

ИЗБЕРЕТЕ в. Име,
в. Град,
co.CompanyName
ОТ клиент c
LEFT JOIN Company co
ВКЛ. C.CompanyID = co.CompanyID

Опитайте се да не използвате SELECT *

Опитайте се да не използвате SELECT *! Вместо това си струва да свържете всяка колона поотделно. Звучи просто, но много разработчици се спъват в този момент. Представете си таблица със стотици колони и милиони редове. Ако вашето приложение се нуждае само от няколко колони, няма смисъл да заявявате цялата таблица - това е голяма загуба на ресурси.

Например, кое е по-добро: SELECT * FROM Служители или SELECT FirstName, City, Country FROM Служители?

Ако наистина искате всички колони, посочете изрично всяка от тях. Това ще помогне да се избегнат грешки и допълнителна конфигурация на базата данни в бъдеще. Например, ако използвате INSERT ... SELECT. и има нова колона в изходната таблица, могат да възникнат грешки, дори ако тази колона не е необходима в целевата таблица:

ВМЪКНЕТЕ В СЛУЖИТЕЛИ ИЗБЕРЕТЕ * ОТ OldEfficiees
Съобщение 213, ниво 16, състояние 1, ред 1
Грешка при вмъкване: Името на колоната или броят на предоставените стойности не съответства на дефиницията на таблицата.

За да избегнете подобни грешки, трябва да регистрирате всяка колона:

ВЪВЕЖДАНЕ В СЛУЖИТЕЛИ (Име, град, държава)
ИЗБЕРЕТЕ Име, Име на град, Име на държава
ОТ стари служители

Трябва да се отбележи обаче, че има ситуации, в които използването на SELECT * е законно. Пример за това биха били временните таблици.

Използвайте разумно временните таблици

Временните таблици често усложняват структурата на заявката. Ето защо е по-добре да не ги използвате, ако е възможно да отправите проста заявка.

Но ако напишете съхранена процедура, която изпълнява някои действия с данни, които не могат да бъдат изпълнени в една заявка, използвайте временни таблици като "посредници", за да получите окончателния резултат.

Да предположим, че трябва да направите селекция с условия от голяма таблица. За да увеличите производителността на базата данни, трябва да прехвърлите данните си във временна таблица и да извършите JOIN вече с нея. Временната таблица ще бъде по-малка от оригинала, така че обединяването ще бъде по-бързо.

Не винаги е ясно каква е разликата между временните таблици и подзаявките. Ето един пример: Представете си таблица с купувачи с милиони записи, които искате да вземете по региони. Една от опциите за внедряване е да се използва SELECT INTO, последвано от присъединяване във временна таблица:

ИЗБЕРЕТЕ * В # Темп ОТ КЛИЕНТА, КЪДЕ RegionID = 5
ИЗБЕРЕТЕ r.RegionName, t.Name FROM Region r JOIN #Temp t ON t.RegionID = r.RegionID

Но вместо временни таблици можете да използвате подзаявка:

ИЗБЕРЕТЕ r.RegionName, t.Name ОТ Регион r
ПРИСЪЕДИНЕТЕ СЕ (ИЗБЕРЕТЕ * ОТ клиент, КЪДЕ RegionID = 5) КАТО t
ON t.RegionID = r.RegionID

В предишния параграф обсъдихме, че си струва да регистрираме само колоните, от които се нуждаем, в подзаявката, следователно:

ИЗБЕРЕТЕ r.RegionName, t.Name ОТ Регион r
ПРИСЪЕДИНЕТЕ СЕ (ИЗБЕРЕТЕ Име, ИД на регион ОТ клиента, КЪДЕ ИД на регион = 5) КАТО t
ON t.RegionID = r.RegionID

Всеки от трите примера ще върне един и същ резултат, но в случай на временни таблици получавате възможността да използвате индекси за ускоряване на нещата. За по-пълно разбиране на това как работят временните таблици и подзаявки, можете да прочетете темата за Stack Overflow.

Когато приключите с временната таблица, по-добре е да я изтриете и да освободите ресурси tempdb, отколкото да изчакате автоматичното изтриване (когато връзката ви със сървъра на базата данни е затворена):

Използвайте EXISTS ()

Ако трябва да проверите за съществуването на запис, по-добре е да използвате оператора EXISTS () вместо COUNT (). Докато COUNT () пресича цялата таблица, EXISTS () спира след намирането на първото съвпадение. Този подход подобрява производителността и подобрява четливостта на кода:

АКО (ИЗБЕРЕТЕ БРОЙ (1) ОТ СЛУЖИТЕЛИ, КЪДЕТО ИМЕ КАТО „% JOHN% ')> 0
ПЕЧАТЕТЕ „ДА“
или
АКО СЪЩЕСТВУВА (ИЗБЕРЕТЕ ПЪРВОТО ИМЕ ОТ СЛУЖИТЕЛИ, КЪДЕ ИМЕ ИМЕ КАТО „% JOHN%“)
ПЕЧАТЕТЕ „ДА“

Вместо заключение

Потребителите на приложения обичат, когато не им се налага да се взират дълго в иконата за зареждане, когато всичко е ясно и бързо. Използването на техниките, описани в тази статия, ще ви помогне да подобрите производителността на базата данни, което ще има положително въздействие върху потребителския опит ">.

Бих искал да обобщя и повторя ключовите моменти, описани в статията:

Използвайте индекси, за да ускорите търсенето и сортирането.
Не използвайте цикли с много итерации за вмъкване на данни - използвайте INSERT или UPDATE.
Байпас на корелирани подзаявки.
Ограничете броя на параметрите на оператора SELECT - посочете само таблиците, от които се нуждаете.
Използвайте временни таблици само като посредници за присъединяване на големи таблици.
За да проверите за запис, използвайте оператора EXISTS (), който излиза след намирането на първото съвпадение.

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

Можете също да прочетете материала, изготвен от експерти на 1cloud за това как големите световни компании работят с данни.