6.1 Введение

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

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

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

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

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

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

6.2 Индексы

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

Java-университет

Здесь будет несколько практические советы по использованию MySQL.

Проблемы с использованием MySQL можно разбить на три группы по значимости:

  1. Использование индексов неправильно или вообще не используется.
  2. Структура БД неправильная.
  3. Запросы SQL неправильные или неоптимальные.

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

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

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

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

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

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

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

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

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

6.4 SQL запросы.

Часто возникает желание переписать все запросы на native SQL для достижения максимальной скорости. Если вы решитесь на это, вот несколько советов:

  • Избегайте запросов в цикле. SQL — это язык множеств, поэтому при написании запросов следует подходить не как к функциям, а как к множествам.
  • Избегайте использования звездочек в запросах. Укажите именно те поля, которые вы выбираете. Это сократит количество пересылаемых данных. Также не забудьте о покрывающих индексах. Даже если вы выбираете все поля в таблице, лучше их перечислить.
  • Во-первых, это повысит читаемость кода. Без звездочек можно узнать какие поля есть в таблице. Во-вторых, использование звездочек может привести к проблемам при добавлении новых полей в таблицу.
  • Для получения общего количества записей при постраничном выборе используйте SQL_CALC_FOUND_ROWS и SELECT FOUND_ROWS(). SQL_CALC_FOUND_ROWS MySQL кеширует количество строк, а SELECT FOUND_ROWS() отдает это значение без необходимости другого запроса.
  • Не забывайте о синтаксисе INSERT для вставки нескольких записей. Один запрос быстрее, чем много в цикле.
  • Используйте LIMIT если не нужны все данные.
  • Вместо REPLACE используйте INSERT… ON DUPLICATE KEY UPDATE…
  • Не забывайте о функции GROUP_CONCAT, которая поможет со сложными запросами.