6.1 Введение

А теперь перейдем от теории к практике.

«В теории между теорией и практикой отличий нет. На практике они есть.»

Мы живем в реальном мире и все программные продукты создаются в конечном счете для живых людей. И этих живых людей очень раздражают сайты, которые медленно грузятся, и программы, которые подтормаживают.

И если запрос к базе данных выполняется больше секунды – это неприемлемо. Пользователи просто не будут пользоваться таким продуктом, где есть страницы/функционал, который настолько тормозит.

А ведь часто, чтобы отобразить одну страницу, нужно выполнить несколько десятков запросов к базе. И если они выполняются последовательно, то лимит у вас уже не секунда, а скажем 100мс на запрос.

Ниже я приведу 5 основных способов, которыми программисты ускоряют работу запросов к базе данных:

  1. Добавляем индексы к таблицам в базе данных.
  2. Переписываем и оптимизируем запросы.
  3. Включаем (и настраиваем) кеширование на стороне БД.
  4. Включаем кеширование на стороне клиента.
  5. Выполняем денормализацию базы данных.

Вы уже по большей части знакомы со всеми этими вещами, поэтому дальше будут только практические советы.

6.2 Индексы

Ни для кого не секрет, что работа с базой данных занимает большую часть работы практически любого сайта. И именно работа с БД чаще всего является узким местом веб-приложений.

В этой статье хотелось бы дать практические советы использования MySQL.

Сразу оговорюсь:

  • данная статья написана про MySQL, хотя общие вещи скорее всего справедливы для любой СУБД.
  • все написанное в статье является моей личной точкой зрения, и не является истиной в последней инстанции.
  • советы не претендуют на новизну и являются результатом обобщения прочтенной литературы и личного опыта.
  • в рамках данной статьи я не буду касаться вопросов конфигурирования MySQL.

Проблемы при использовании MySQL можно разделить на следующие три группы (в порядке значимости):

  1. Неиспользование или неправильное использование индексов.
  2. Неправильная структура БД.
  3. Неправильные \ неоптимальные SQL запросы.

Остановимся на каждой из этих групп подробнее.

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

Неиспользование или неправильное использование индексов — это то, что чаще всего замедляет запросы. Для тех, кто мало знаком с механизмом работы индексов или еще не читал об этом в мануале, очень советую почитать.

Советы по использованию индексов:

  • Не нужно индексировать все подряд. Довольно часто, не понимая смысла, люди просто индексируют все поля таблицы. Индексы ускоряют выборки, но замедляют вставки и обновления строк, поэтому выбор каждого индекса должен быть осмыслен.
  • Один из основных параметров, характеризующий индекс — селективность (selectivity) — количество разных элементов в индексе. Нет смысла индексировать поле, в котором два-три возможных значения. Пользы от такого индекса будет мало.
  • Выбор индексов должен начинаться с анализа всех запросов к данной таблице. Очень часто после такого анализа вместо трех-четырех индексов можно сделать один составной.
  • При использовании составных индексов порядок полей в индексе имеет определяющее значение.
  • Не забывайте про покрывающие(covering) индексы. Если все данные в запросе могут быть получены из индекса, то MySQL не будет обращаться непосредственно к таблице. Подобные запросы будут выполняться очень быстро. Например для запроса SELECT name FROM user WHERE login='test' при наличии индекса (login, name) обращения к таблице не потребуется. Порой имеет смысл добавить в составной индекс дополнительное поле, которое сделает индекс покрывающим и ускорит запросы.
  • Для индексов по строкам часто достаточно индексировать лишь часть строки. Это может значительно уменьшить размер индекса.
  • Если % стоит в начале LIKE(SELECT * FROM table WHERE field LIKE '%test') индексы использоваться не будут.
  • FULLTEXT индекс используется только с синтаксисом MATCH … AGAINST.

6.3 Структура БД

Грамотно спроектированная БД — залог быстрой и эффективной работы с базой. С другой стороны, плохо продуманная БД — это всегда головная боль для разработчиков.

Советы по проектированию БД:

  1. Используйте минимально возможные типы данных. Чем больше тип данных, тем больше таблица, тем больше обращений к дискам нужно для получения данных. Используйте очень удобную процедуру: SELECT * FROM table_name PROCEDURE ANALYSE(); для определения минимально возможных типов данных.
  2. На этапе проектирования соблюдайте нормальные формы. Часто программисты прибегают к денормализации уже на этом этапе. Однако в большинстве случаев в начале проекта далеко не очевидно, чем это может вылиться. Денормализовать таблицу гораздо проще, чем страдать от неоптимально денормализованной. Да и JOIN порой работает быстрее, чем неверно денормализованные таблицы.
  3. Не используйте NULL столбцы кроме случаев, когда они вам осознанно нужны.

6.4 SQL запросы.

Так же часто возникает желание переписать все запросы на native SQL, чтобы запрос был максимально быстрым. Если вы решитесь на такое, то вот вам несколько советов:

  1. Избегайте запросов в цикле. SQL — язык множеств и к написанию запросов нужно подходить не языком функций, а языком множеств.
  2. Избегайте * (звездочки) в запросах. Не поленитесь перечислить именно те поля, которые вы выбираете. Это сократит количество выбираемых и пересылаемых данных. Кроме этого, не забывайте про покрывающие индексы. Даже если вы действительно выбираете все поля в таблице, лучше их перечислить. Во-первых, это повышает читабельность кода. При использовании звездочек невозможно узнать какие поля есть в таблице без заглядывания в нее. Во-вторых, сегодня в вашей таблице пять INT столбцов, а через месяц добавилось еще одно TEXT и BLOB, а звездочка как была, так и осталась.
  3. При постраничном выборе для получения общего количества записей используйте SQL_CALC_FOUND_ROWS и SELECT FOUND_ROWS(); При использовании SQL_CALC_FOUND_ROWS MySQL кеширует выбранное количество строк(до применения LIMIT) и при SELECT FOUND_ROWS() только отдает это закешированное значение без необходимости повторного выполнения запроса.
  4. Не забывайте, что у INSERT есть синтаксис для множественной вставки. Один запрос будет выполняться на порядок быстрее, чем множество запросов в цикле.
  5. Используйте LIMIT там, где вам не нужны все данные.
  6. Используйте INSERT… ON DUPLICATE KEY UPDATE… вместо выборки и INSERT или UPDATE после нее, а также часто вместо REPLACE.
  7. Не забывайте про замечательную функцию GROUP_CONCAT. Она может выручить при сложных запросах.
Java-университет