Оптимизация mysql

// Ноябрь 25th, 2010 // Doctrine, Memcached, 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 Монти Видениусом за кружечкой пива. Я моргнул, а он отвернулся, так что фотка получилась весьма своеобразная :-)

Share

Спасибо!


Если вам помогла статья, или вы хотите поддержать мои исследования и блог - вот лучший способ сделать это:


5 Responses to “Оптимизация mysql”

  1. Andy:

    1. Один большой запрос или несколько маленьких.
    Кстати, большинство ORM орудует именно простыми select-запросами, и это хорошо, так как уменьшается время сетеового ожидания, запросы-то быстрее выполняются. А вот бег ORM получать данные небольшими запросами совсем грустно, т.к. их придется структурировать как тебе нужно, а это немало кода получится.

    2. Нужно расставлять индексы по всем полям, по которым осуществляется поиск)

    3. Это да)

    4. А для пагинации не стоит забывать про SQL_CALC_FOUND_ROWS

    5. Согласен

    6. Жалко только, что MEMORY-таблицы не поддерживают поля типа TEXT, так что длинные данные сессии в них хранить не получится.

    Про остальное — согласен)

  2. Andy:

    Слышал что MariaDb люди юзают в продакшене. Сам — не щупал еще. По мне дак лучше Постгрес заюзать, если что, чем какие-то невнятный на данный момент штуки.

Комментировать