Проблема медленных SQL запросов

Сайт, який використовує в процесі роботи базу даних, в процесі генерації однієї сторінки сайту звертається до сервера баз даних кілька десятків, а іноді сотень разів. При цьому кожен запит є інструкцією, яку, незважаючи на великий обсяг даних, сервер БД здатний виконати за дуже короткий час - в межах кількох мілісекунд. Загальний час виконання сотень запитів не перевищує однієї – двох секунд, у штатному режимі роботи один сервер баз даних обслуговує до 500 запитів на секунду. Така висока продуктивність – результат технічно налагодженої побудови реляційної структури баз даних, ефективного зберігання інформації, індексації даних. Всі ці заходи допомагають знайти потрібні дані негайно.

Загальне уявлення про процес виконання SQL запиту

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

Звичайне виконання запиту полягає у переборі даних, що знаходяться у таблиці. Наприклад, при пошуку повідомлення на форумі, загальна кількість повідомлень якого займає 20 Мб, найпростішим способом відповіді на таке запитання був би перебір усіх повідомлень з метою пошуку потрібного (порівняння номерів повідомлень із необхідним номером). При цьому з диску зчитується інформація загальним обсягом 20 Мб (для прикладу). Це зайняло б кілька секунд чи навіть кілька десятків секунд.

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

При проектуванні структури бази даних для однієї таблиці може бути створена будь-яка кількість індексів, які дозволяють миттєво відповідати на запити певних типів.
Наприклад, індекс за номером повідомлення дозволяє миттєво отримати повідомлення, знаючи його номер. Індекс автора повідомлення дозволяє швидко отримати список повідомлень, написаних певним автором. Індекс за датою дозволяє швидко отримати номери або текст останніх 10 повідомлень (наприклад).

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

Повільні запити

Трапляються випадки, за яких сервер баз даних не може відповісти на запит у межах короткого часу. Запит вважається повільним, якщо його обробка зайняла понад 10 секунд.
Для кожного повільного запиту фіксуються такі параметри:

• Час виконання запиту – загальний час, який витратив сервер баз даних на виконання вашого запиту, виконуючи певні операції.
   
• Час блокувань - час, який сервер баз даних витратив на виконання вашого запиту, але під час якого ніяких дій не проводилося, т.я. сервер баз даних очікував готовності інших об'єктів чи завершення попередніх або конфліктуючих операцій.
   
• Кількість переглянутих рядків – кількість рядків таблиці, яку довелося зчитати з диска у процесі обробки запиту SQL.
   
• Кількість переглянутих рядків (tmp) – аналогічно до попереднього пункту, але відображає кількість створених та переглянутих рядків тимчасових та допоміжних таблиць, якими довелося скористатися.

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

Випадковий повільний запит

З причин випадкового характеру будь-який запит може виконуватися повільно.

• Запит INSERT, UPDATE може виконатися повільно через очікування закінчення обробки інших запитів або через зайнятість дискової системи сервера в даний момент часу. Це нормальна поведінка сервера і не заважає його роботі.
   
• Запит SELECT міг випадково виконатися повільно також через локальне (миттєве) навантаження на сервері. Ознакою випадковості даного факту є одноразове повторення явища і невелика кількість використаних рядків таблиці (зазвичай десятки, максимум - в межах кількох тисяч).

Випадкові повільні запити не є ознакою проблем із боку скриптів чи структури бази даних користувача.

Проблемний повільний запит

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

Повільні запити такого типу можна класифікувати на такі популярні випадки (наприклад):

• Простий запит виду .. WHERE field=[value], WHERE field>[value], WHERE field<[value], висока кількість переглянутих рядків (від 10 до 100 тис. і більше).
  Зазвичай це означає або відсутність необхідного індексу поля field, або велику кількість рядків як відповідь на запит.
   
• Простий запит виду .. WHERE field LIKE ‘%...%’, помітна кількість переглянутих рядків (більше 1-2 тисяч).
  У процесі виконання запиту LIKE використання індексів неможливе. Відбувається повний перебір всіх даних, що зберігаються в базі.
   
• Складний запит для декількох таблиць, велика кількість переглянутих рядків (від 10 тис до мільйонів).
  У процесі виконання запиту використання індексів неможливо, або оптимізатор mySQL не зміг зрозуміти, як використовувати індекси для відповіді на цей запит. В результаті серверу довелося створити тимчасову таблицю із зазначеною кількістю рядків.

Можливі численні інші випадки, описати їх всі, на жаль, не можливо, т.я. вони різноманітні.

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

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

Проблема відлагодження запиту

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

Шляхи вирішення проблеми

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

Можна запропонувати такі шляхи вирішення проблеми:

• Аналіз потреби даного запиту. Можливо, модуль або частина сайту, яка використовує даний запит, не потрібна або може бути легко замінена на інший, переконфігурована.
   
• Необхідно розглянути можливість скорочення кількості інформації в таблицях, які стосуються запиту. Часто буває, що повільні запити відбуваються під час обробки таблиць, що накопичують статистику відвідувань сайту або іншу інформацію, яка, фактично, може бути скорочена.
   
• Від запитів LIKE можна відмовитись, т.я. пошукова система, заснована на запитах такого виду, у сотні та тисячі разів менш ефективна, ніж будь-яка традиційна пошукова система, що базується на списку слів. Пошук через списки слів реалізований, наприклад, на всіх популярних форумах. Програмістові не важко реалізувати нормальний пошук без використання оператора LIKE, або використати повнотекстовий пошук.
   
• За допомогою оператора EXPLAIN SELECT можна проаналізувати, чи використовуються при відповіді на запит індекси, і якщо так, то в якому обсязі. Можливо, простим способом оптимізації стало б створення необхідних або додаткових індексів, якщо вони ще не створені.

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

Тарифні плани з великою абонентською платою, такі як плани лінійки «Профі» або краще VIP-хостинг, дозволяють певною мірою використовувати повільні запити у повсякденній роботі. Однак це не є рекомендованою практикою, а також можливість виконання повільних запитів не може гарантуватися.

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

Додаткова інформація

Для оптимізації роботи з mySQL ви можете скористатися документацією, яка доступна на сайті http://dev.mysql.com/doc/.
Питання, описані в цій статті, є основними питаннями роботи бази даних і описані в документації набагато докладніше та точно.