Трохи про підвищення продуктивності БД: Практичні поради



/ фото Ozzy Delaney CC

Ми 1cloud багато розповідаємо про власному досвіді роботи над провайдером віртуальної інфраструктури і тонкощах організації внутрішніх процесів. Сьогодні ми вирішили трохи поговорити про оптимізацію БД.

Багато СУБД здатні не тільки зберігати і управляти даними, але і виконувати код на сервері. Прикладом цього служать збережені процедури і тригери. Проте лише одна операція зміни даних може запустити кілька тригерів і збережених процедур, які, в свою чергу, «розбудять» ще парочку. В якості прикладу можна навести каскадне видалення в базах даних SQL, коли виключення одного рядка в таблиці призводить до зміни багатьох інших пов'язаних записів.

Очевидно, що користуватися розширеною функціональністю слід обережно, щоб не навантажувати сервер, бо все це може позначитися на продуктивності клієнтських програм, що використовують дану БД.

Погляньте на графік нижче. На ньому зображені результати виконання навантажувального тестування програми, коли число користувачів (синій графік), що працюють з БД, поступово збільшується до 50. Кількість запитів (помаранчевий), з якими система може впоратися, швидко досягає свого максимуму і перестає рости, тоді як час відповіді (жовтий) поступово збільшується.



При роботі з великими базами даних навіть найменша зміна здатне чинити серйозний вплив на продуктивність, причому як в позитивну, так і негативну сторону. В організаціях середнього і великого розміру налаштуванням баз даних займається адміністратор, але частенько ці завдання лягають на плечі розробників. Тому далі ми дамо кілька практичних порад, які допоможуть підвищити продуктивність баз даних SQL.

Використовуйте індекси

Індексація – це ефективний спосіб налаштування бази даних, яким часто нехтують під час розробки. Індекс прискорює запити, надаючи швидкий доступ до рядків даних в таблиці, аналогічно тому, як предметний покажчик у книзі допомагає вам швидко знайти потрібну інформацію.

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

Однак варто звернути увагу, що, якщо ваші таблиці «бомбардуються» методами INSERT, UPDATE і DELETE, до індексації потрібно поставитися обережно – вона може призвести до ухудшению продуктивності, так як після проведення зазначених вище операцій всі індекси повинні бути змінені.

Більше того, коли потрібно додати в таблицю велика кількість рядків (наприклад більше мільйона) відразу, адміністратори БД часто скидають індекси для ускорения процесу вставки (після вставки індекси створюються заново). Індексація – це велика і цікава тема, для ознайомлення з якою недостатньо настільки короткого опису. Більше інформації по цій темі ви знайдете здесь.

Не використовуйте цикли з великою кількістю ітерацій

Уявіть ситуацію, коли на вашу БД послідовно приходить 1000 запитів:

for (int i = 0; i < 1000; i++)
{
SqlCommand cmd = new SqlCommand("INSERT INTO TBL (A,B,C) VALUES...");
cmd.ExecuteNonQuery();
}

Такі цикли писати не рекомендується. Приклад вище можна переробити, використовуючи один INSERT або UPDATE з кількома параметрами:

INSERT INTO TableName (A,B,C) VALUES (1,2,3),(4,5,6),(7,8,9)
UPDATE TableName SET A = B CASE
WHEN 1 THEN 'NEW VALUE'
WHEN 2 THEN 'NEW VALUE 2'
WHEN 3 THEN 'NEW VALUE 3'
END
WHERE B in (1,2,3
)

Переконайтеся, що операція WHERE не перезаписує однакові значення. Така проста оптимізація може прискорити виконання SQL-запиту, зменшивши кількість оновлюваних рядків з тисяч до сотень. Приклад перевірки:

UPDATE TableName
SET A = @VALUE
WHERE
B = 'YOUR CONDITION'
AND A <> @VALUE – VALIDATION

Уникайте корелюють підпорядкованого

Корелює підзапит називають такий підзапит, який використовує значення батьківського запиту. Він виконується порядково, один раз для кожного рядка, поверненої зовнішнім (батьківським) запит, що знижує швидкість роботи БД. Ось простий приклад коррелирующего подзапроса:

SELECT c.Name, 
c.City,
(SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName 
FROM Customer c

Тут проблема в тому, що внутрішній запит (SELECT CompanyName...) виконується для кожного рядка, яку повертає зовнішній запит (SELECT c.Ім'я...). Щоб підвищити продуктивність, можна переписати підзапит через JOIN:

SELECT c.Name, 
c.City, 
co.CompanyName 
FROM Customer c 
LEFT JOIN Company co
ON c.CompanyID = co.CompanyID

Намагайтеся не використовувати SELECT *

Намагайтеся не використовувати SELECT *! Замість цього варто підключати кожен стовпець окремо. Звучить просто, але на цьому моменті спотикаються багато розробники. Уявіть таблицю з сотнями стовпців і мільйонами рядків. Якщо додатку потрібно лише кілька стовпців, немає сенсу запитувати всю таблицю – це велика трата ресурсів.

Наприклад, що краще: SELECT * FROM Employees або SELECT FirstName, City, Country FROM Employees?

Якщо вам дійсно потрібні всі стовпці, вкажіть кожен в явному вигляді. Це допоможе уникнути помилок і додаткового налаштування БД в майбутньому. Наприклад, якщо ви використовуєте INSERT… SELECT..., а у вихідній таблиці з'явився новий стовпець, можуть виникнути помилки, навіть якщо цей стовпець не потрібен в кінцевій таблиці:

INSERT INTO Employees SELECT * FROM OldEmployees
Msg 213, 16 Level, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

Щоб уникнути таких помилок, потрібно прописувати кожен стовпець:

INSERT INTO Employees (FirstName, City, Country)
SELECT Name, CityName, CountryName
FROM OldEmployees

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

Користуйтеся тимчасовими таблицями з розумом

Тимчасові таблиці найчастіше ускладнюють структуру запиту. Тому їх краще не використовувати, якщо є можливість оформити простий запит.

Але якщо ви пишете збережену процедуру, яка виконує якісь дії з даними, які неможливо оформити в одному запиті, то використовуйте тимчасові таблиці як «посередників», які допомагають отримати кінцевий результат.

Припустимо, вам треба зробити вибірку з умовами з великої таблиці. Щоб збільшити продуктивність БД, варто перевести свої дані у тимчасову таблицю і виконати JOIN вже з нею. Тимчасова таблиця буде менше вихідної, тому об'єднання відбудеться швидше.

Не завжди зрозуміло, у чому різниця між тимчасовими таблицями і подзапросами. Тому наведемо приклад: уявіть таблицю покупців з мільйонами записів, з якої треба зробити вибірку по регіону. Один з варіантів реалізації – використовувати SELECT INTO з подальшим об'єднанням у тимчасову таблицю:

SELECT * INTO #Temp FROM Customer WHERE RegionID = 5
SELECT r.RegionName, t.Name FROM Region r JOIN #Temp t ON t.RegionID = r.RegionID

Але замість тимчасових таблиць можна використовувати підзапит:

SELECT r.RegionName, t.Name FROM Region r 
JOIN (SELECT * FROM Customer WHERE RegionID = 5) AS t 
ON t.RegionID = r.RegionID

У попередньому пункті ми обговорювали, що варто прописувати в підзапитів тільки потрібні нам стовпці, тому:

SELECT r.RegionName, t.Name FROM Region r 
JOIN (SELECT Name, RegionID FROM Customer WHERE RegionID = 5) AS t 
ON t.RegionID = r.RegionID

Кожен з трьох прикладів поверне один і той же результат, але у випадку з тимчасовими таблицями ви отримуєте можливість використовувати індексацію. Для більш повного розуміння принципів роботи тимчасових таблиць і підпорядкованого можете почитати тему Stack Overflow.

Коли робота з тимчасової таблицею закінчена, краще видалити її і звільнити ресурси tempdb, ніж чекати, поки відбудеться автоматичне видалення (коли ваше з'єднання з сервером БД закриється):

DROP TABLE #temp

Використовуйте EXISTS()

Якщо необхідно перевірити існування запису, краще використовувати оператор EXISTS() замість COUNT(). Тоді як COUNT() проходить по всій таблиці, EXISTS() припиняє роботу після знаходження першого збігу. Цей підхід повышает продуктивність і покращує читаність коду:

IF (SELECT COUNT(1) FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') > 0
PRINT 'YES' 

або

IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%')
PRINT 'YES'

Замість висновку
Користувачі програм люблять, коли їм не потрібно довго дивитися на значок завантаження, коли все працює чітко і швидко. Застосування описаних у цьому матеріалі прийомів дозволить вам підвищити продуктивність бази даних, що позитивно позначиться на користувальницькому досвіді.

Хотілося б підвести невеликий підсумок і повторити ключові моменти, описані в статті:

  1. Використовуйте індекси, щоб прискорити проведення пошуку і сортування.
  2. Не використовуйте цикли з великою кількістю ітерацій для вставки даних – використовуйте INSERT або UPDATE.
  3. Обходьте стороною корелюють підпорядкований.
  4. Обмежуйте кількість параметрів оператора SELECT – вказуйте тільки потрібні таблиці.
  5. Використовуйте тимчасові таблиці тільки як «посередників» для об'єднання великих таблиць.
  6. Для перевірки на наявність запису користуйтеся оператором EXISTS(), який закінчує роботу після визначення першого збігу.
Якщо вам цікава тема продуктивності баз даних, то на Stack Exchange є обсуждение, в якому зібрано велику кількість корисних ресурсів, – вам варто звернути на нього увагу. Ще можете почитати наш матеріал про те, як працюють з даними великі світові компанії.

Свіжі матеріали із нашого блогу на Хабре:

Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.