Оптимизация mysql
В этой статье я хотел бы рассказать о приемах оптимизации 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 Монти Видениусом за кружечкой пива. Я моргнул, а он отвернулся, так что фотка получилась весьма своеобразная 🙂
1. Один большой запрос или несколько маленьких.
Кстати, большинство ORM орудует именно простыми select-запросами, и это хорошо, так как уменьшается время сетеового ожидания, запросы-то быстрее выполняются. А вот бег ORM получать данные небольшими запросами совсем грустно, т.к. их придется структурировать как тебе нужно, а это немало кода получится.
2. Нужно расставлять индексы по всем полям, по которым осуществляется поиск)
3. Это да)
4. А для пагинации не стоит забывать про SQL_CALC_FOUND_ROWS
5. Согласен
6. Жалко только, что MEMORY-таблицы не поддерживают поля типа TEXT, так что длинные данные сессии в них хранить не получится.
Про остальное – согласен)
4. Жаль, что SQL_CALC_FOUND_ROWS поддерживает только MySQL, а единственный кроссплатформенный аналог на данный момент – отдельный COUNT запрос (по крайней мере в Doctrine).
Ну потс по у тебя про mysql) Не зряж картинку с дядькой присобачил)
Ага. Кстати, ты про XtraDB в MariaDB чего слышал хорошего?
Слышал что MariaDb люди юзают в продакшене. Сам – не щупал еще. По мне дак лучше Постгрес заюзать, если что, чем какие-то невнятный на данный момент штуки.