Оптимизация MySQL, optimize table — как не надо делать

Оптимизация MySQL, optimize table - как не надо делатьВ блогосфере советы по ускорению работы сайта и оптимизации разносятся с бешеной скоростью. При этом мало кто из авторов проверяет реальную полезность полученных советов. Это вдвойне странно, потому как возможность проверить правильность той или иной идеи и сравнить разные мнения по одному и тому же вопросу в Интернете представлена очень широко.

В одном из блогов встретила вот такой текст:

Конкретный пример из жизни: 2 таблицы по 50 000 записей, в которых постоянно идут update, insert, delete. Ясное дело, что при таком подходе данные фрагментируются. Поэтому, когда мы делаем JOIN, то запрос выполняется 0.2 секунды — довольно много для базы данных. После optimize table запрос стал выполнятся 0.015 сек. Никаких дополнительных индексов, покупки железа — просто упорядочили данные на диске. Команду можно поставить в cron раз в сутки, например.


Мое внимание сразу зацепилось за то, что автор делает данную процедуру раз в сутки, а другие читатели только на цифры скорости выполнения запроса внимание обращают. А между тем, у автора написано, что у него в сутки по 50 тысяч записей меняются, может ли этим похвастаться ваш блог? Нет? Тогда зачем вы применяете совет, который вам не подходит?

И вот тысячи блогеров постят себе полезную команду optimize table и рекомендуют всем своим читателям пользоваться ею не реже раза в неделю. Кто-нибудь из них почитал зачем нужна эта команда, что она делает, чего она не может сделать и стоит ли ее применять для блогов?

Заглянем сюда — MySQL — справочное руководство на русском. И найдем такой пункт, касающийся optimize table, цитирую:

4.5.1. Синтаксис команды OPTIMIZE TABLE
OPTIMIZE TABLE tbl_name[,tbl_name]…

Команда OPTIMIZE TABLE должна использоваться после удаления большей части таблицы или если в таблице было внесено много изменений в строки переменной длины (таблицы, в которых есть столбцы VARCHAR, BLOB или TEXT). Удаленные записи поддерживаются при помощи связного списка, и последующие операции INSERT повторно используют позиции старых записей. Чтобы перераспределить неиспользуемое пространство и дефрагментировать файл данных, можно воспользоваться командой OPTIMIZE TABLE.
На данный момент команда OPTIMIZE TABLE работает только с таблицами MyISAM и BDB. Для таблиц BDB команда OPTIMIZE TABLE выполняет ANALYZE TABLE.
Можно применить OPTIMIZE TABLE к таблицам других типов, запустив mysqld с параметром —skip-new или —safe-mode, но в этом случае OPTIMIZE TABLE лишь только выполняет ALTER TABLE.
Команда OPTIMIZE TABLE работает следующим образом:
* Если в таблице есть удаленные или разделенные строки, восстанавливает таблицу.
* Если индексные страницы не отсортированы — сортирует их.
* Если статистические данные не обновлены (и восстановление нельзя осуществить путем сортировки индексов), обновляет их.

Команда OPTIMIZE TABLE для MyISAM представляет собой эквивалент выполнения myisamchk —quick —check-only-changed —sort-index —analyze над таблицей.
Обратите внимание: во время работы OPTIMIZE TABLE таблица заблокирована!

На такие ссылки блогеры обижаются, топают ногами и кричат, что новичкам никто не помогает. Новички, учитесь помогать себе сами, все самые важные и полезные знания можно почерпнуть из профессиональной литературы, а не в блогах таких же новичков.

В определении все четко и ясно написано. Команда optimize table применяется для таблиц, в которые было внесено много изменений. Много — это не 12 комментариев и 4 поста, это несколько тысяч. А фраза про то, что во время выполнения команды optimize table таблица будет заблокирована нам о чем говорит? Если таблица заблокирована, значит и сайт, работающий с этой таблицей тоже будет не работоспособен. Таблица будет заблокирована полностью, даже если оптимизироваться будет только тот столбец, что отвечает за комментарии. Отсюда вопрос, зачем запускать эту команду каждый день?

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

Когда стоит применять команду optimize table для блога

1. Если у вас удаляется или изменяется большое количество статей или комментариев (большое количество — это несколько тысяч).
2. В часы, когда посещаемость вашего сайта самая низкая.

P.S. Не бойтесь обращаться за советом к первоисточникам и профессиональной литературе.

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

  1. Zen 24.10.2013 / 03:16

    Круто … Детали интересные.
    ненавижу язык SQL и все его разновидности … поставил модуль, нажал на кнопку, получил скорость и индексы.

    А чего с блокировкой? MyISAM все равно надежнее для MySQL.

  2. Olunka 20.04.2014 / 00:30

    MySQL и SQL совершенно разные вещи, так же как Java и JavaScript.
    Просто названия похожи.

    Про блокировку вроде написала, на время оптимизации таблиц сайт будет нерабочий.

  3. max 19.02.2015 / 19:47

    Блокировка таблицы — это, конечно, неприятно. Но как думаете, сколько миллисекунд будет оптимизироваться таблица из 12ти комментариев и 4х постов? Да еще и оптимизированных за предыдущие сутки :)

    Вот если таблица весит несколько гигов, то блокировка может быть и станет заметна. Но тогда очень вероятно, что таблица активно изменяется и оптимизация уже нужна.

    Хотите уменьшить фрагментацию и время оптимизации? Не используйте varchar где ни попадя :)

  4. Olunka 16.03.2015 / 12:37

    Таблицу из 4 постов и оптимизировать не надо, пустая трата времени.
    Если у вас тормозит сайт из 4 страниц — то проблема не в таблицах явно.

  5. Артем 04.01.2016 / 14:40

    Немного поправлю.

    MySQL — это база
    SQL — это язык запросов

  6. Zodiac 31.03.2016 / 12:08

    Чем же MyISAM надежней InnoDB при отсутствии транзакций, бинарных логов и инкрементных бэкапов?
    Может она удобнее глобальными локами при обновлении данных или своими некластерными индексами при поиске?

Комментарии к этой статье отключены.