Проблема медленных 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/.
Вопросы, описанные в этой статье, являются основными вопросами работы базы данных и описаны в документации гораздо более подробно и точно.