Оптимизация SQL (в частности MySQL) довольно обширная тема и нет единого рецепта, который бы дал ответ на ваш конкретный вопрос. Поэтому попробую отталкиваясь от собственного опыта оптимизации сайтов поделиться своими знаниями в этом материале. Стоит также отметить, что речь сейчас будет только о запросах SELECT, остальные запросы не так критичны в плане скорости работы зачастую.
Перво-наперво вычленяете итоговый SQL запрос, который отправляет в базу со всеми подставленными переменными.
Перед запросом лепим волшебное слово EXPLAIN и загоняем в ваш любимый PMA или любой другой редактор запросов или вообще консоль базы. И видим непонятную табличку. В непонятной табличке в первую очередь нас должно заинтересовать поле key. Это поле отвечает за то, какой индекс используется для обработки вашего запроса. Смотрим какой индекс используется базой, сверяем с тем, который там должен быть и если не совпадает, то в запрос модифицируем приблизительно так:
SELECT ... FROM table FORCE INDEX (index_name) ....
как вы могли догадаться добавилась часть FORCE INDEX (index_name) которая заставляет базу использовать нужный нам индекс. Само собой, индекс должен быть предварительно создан и желательно с тем набором полей, которые встречаются в вашем запросе.
Следующим пунктом оптимизации запросов я бы назвал всё-таки оптимизацию приложения.
Как ни крути, но если вы написали тяжеленный запрос в огромную таблицу (или таблицы), то индексы не смогут вас спасти. Будет всё тормозить и тупить, а это нехорошо. Поэтому надо пересматривать логику приложения и избегать следующее:
- SELECT COUNT(*) — бесполезный по сути запрос. Используется зачастую для создания пагинаторов, но так ли часто на самом деле эти пагинаторы меняются, как мы думаем? Если у вас есть главная страница с пагинатором и несколько страниц рубрик, то имеет смысл хранить кол-во материалов на странице в каком-то быстром хранилище, (например в redis, можно memory table в mysql или на худой конец просто в файле на диске) и делать запрос к подобному «кешу». Это будет в разы быстрее, чем каждый раз заставлять базу пересчитывать записи. Формировать кеш можно раз в час отдельным скриптом в cron.
- SELECT … LIMIT 5000, 100 — аналогично предыдущему пункту относится к пагинации и создает всегда огромные тормоза. Как убрать этот запрос? И снова ответ прост: быстрые хранилища. Только хранить там уже будем ID записей, которые должны быть отображены на конкретной странице. Аналогично обновлять данные в своем кеше можно по расписанию
- Стоит отказаться от постоянных выборок «самого-самого» на сайте. Например, владельцы сайтов размещают самые комментируемые статьи, самые читаемые или ещё какие-нибудь. Эти данные по сути тоже необходимо подвергать кешированию и аналогично предыдущим пунктам их нужно обновлять по расписанию и из базы брать по идентификатору. Тогда будет быстро.
- Если того позволяет логика вашего сайта, то кешируйте материалы/разделы/рубрики сайта при помощи nginx. Nginx будет отдавать ранее сгенерированную страницу пользователю очень быстро и не будет нагружать сервер. Точно также стоит добавить в кеширование файлы robots.txt (в случае, если он создается динамически), сайтпамы и прочие страницы типа «контакты», «о сайте» и прочие подобные, которые меняются раз в пару лет. Подобное кеширование сильно упрощает жизнь процессору