Оптимизация работы с MySQL

• MySQL

Ни для кого не секрет, что работа с базой данных занимает большую часть работы практически любого сайта. И именно работа с БД чаще всего является узким местом веб-приложений.
В этой статье хотелось бы дать практические советы использования 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.

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

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

SQL запросы.

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