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

1. Один большой запрос или несколько маленьких.

Очень часто бизнес-логика приложения требует загрузить сразу много связанных данных. Например это могут быть 10 товаров, отсортированных по дате, вместе (JOIN) со странами этих товаров, вместе (JOIN) с переводами этих стран и т.д. Возникает искушение перенести всю эту логику на уровень БД, сделав большой запрос с кучей JOIN’ов. Но у этого способа есть большой минус – плохая производительность. Для этой операции движку БД нужно сделать декартово произведение исходных отношений (таблиц), – это расход процессорного времени. Во-вторых, при этом загружается большое количество данных. Очень частой ошибкой бывает отсутствие условия для JOIN, например при работе с языком DQL (Doctrine Query Language). В этом случае, его легко проглядеть, а чтобы поймать, надо делать дамп получивщегося SQL запроса. Во многих случаях проще сделать несколько маленьких запросов к БД.

2. Индексы

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

3. Запросы в цикле

Никогда не делайте запросов в цикле. Гораздо правильнее будет в цикле получить условия выборки, а потом сделать групповой запрос с условием WHERE … IN (..) Тоже самое относится не только к выборкам, но и к вставкам и обновлениям в БД.

4.  COUNT – запрос

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

SELECT COUNT(*)

а не

SELECT *

5. Извлекайте только реально нужные данные

Забудьте о SELECT *. Извлекайте только те данные, которые вам реально нужны. Это поможет сберечь память на сервере.

6. Используйте MEMORY таблицы, где это возможно

В MySQL есть несколько движков хранения данных. Среди них есть “MEMORY”, который хранит данные в RAM. Таблицу с таким движком можно использовать, например, для хранения сессий. ОБновляются они часто, если все сотрутся – это не будет большой проблемой. Тут некотоыре могут возразить, что можно хранить сессии в Memcached. Им я советую прочитать мою статью на эту тему.

7.  Используйте контроль целостности

Движок InnoDB использует контроль целостности ключей, что позволяет избежать, например, добавление товара без указания цены и номера валюты (внещний ключ), или с валютой, которой нет.

8. Не используйте ORDER BY RAND()

На больших таблицах вас ждут большие тормоза. Лучше сначала посчитать массив id’шников, а потом выборку данных.

9. Используйте LIMIT

Нужны ли вам все данные таблицы? Скорее всего нет. Этим вы сократите объем данных, и сэкономите память на сервере.

10. Думайте головой

Старайтесь всегда предугадать условия, в которых будет работать ваш код. Тогда и багов будет меньше!)

Ну и напоследок фотка меня вместе с разработчиком MySQL Монти Видениусом за кружечкой пива. Я моргнул, а он отвернулся, так что фотка получилась весьма своеобразная 🙂