MySQL: отличия между MyISAM и InnoDB
Отличия между системами хранения данных MyISAM и InnoDB простым языком. В данной таблице я попробовал показать разницу между MyISAM и InnoDB на простых примерах...
Самые популярные типы хранение в базе MySQL – это MyISAM и InnoDB.
Неправильный выбор типа хранения приводит к тем же последствиям, что и неправильная структура таблиц, неправильные индексы и неправильные запросы. Другими словами – к падению производительности.
MyISAM
Интересен тем, что дает просто безумную скорость на select-ах и insert-ах. С другой стороны, он не поддерживает транзакционность и блокировку на уровне строк, что в свою очередь приводит к страшным тормозам при использовании deleteupdate. Проще говоря, на таблицу допускается только одна одновременная delete или update операция, и остальные вынуждены ждать завершения текущей операции, что на больших объемах данных приводит к серьезным проблемам.
К преимуществам движка можно отнести поддержку полнотекстовый поиск, компрессию и GIS функции. Под хранение каждой таблицы отводятся два файла – имя_таблицы.MYD ( данные ) и имя_таблицы.MYI ( индексы ). Формат данных платформенно независимый, что позволяет переносить данные с сервера на сервер простым копированием таблиц – это еще один плюс.
InnoDB
Был спроектирован для обработки транзакций, в частности для большого количества короткоживущих транзакций, которые чаще комитятся чем откатываются. Это не единственный движок, поддерживающий транзакционность, но на сегодня он является самым популярным для этой цели.
Кроме транзакционности, к преимуществам этого движка можно отнести row-level блокировки, правда тут стоит учесть тот факт, что table-level блокировки тоже имеют место быть, например при использовании автоинкрементных полей. С этим борятся и время от времени выходят фиксы, уменьшающие количество table-level блокировок, но никто не отменял регулярный просмотр логов на предмет проблем с блокировками.
Таблицы по умолчанию хранятся в одном innodb файле, но при желании их можно разместить в разных файлах, причем по одному на таблицу, если указать это явно.
Что может повлиять на выбор тиха хранения данных? Ну во-первых необходимость транзакционности, ибо если пишется серьезный финансовый софт, например тот же биллинг или процессинг, то тут майсам использовать никак нельзя.
Кроме того, специфика работы с данными, накладывает свой отпечаток на выбор движка. Если речь идет о какой-то статистике, которую все просто напросто просматривают, то лучше майсам с этим никто не справится.
Про конкурентность забывать тоже не стоит. Если работа с данными идет всего в несколько потоков, то это вполне может нивелировать недостатки myisam в плане updatedelete в пользу быстрых select.
При выборе движка есть еще ряд нюансов, которые не так важны как главные критерии, но которые тоже стоит учитывать.
Описание | MyISAM | InnoDB |
Транзакционный движек?Транзакция (Transaction) — блок операторов SQL , который в случае ошибки в одном запросе, возвращается к предыдущему состоянию (Rollback), и только в случае выполнения всех запросов подтверждается (Commit) | Нет | Да |
Поддержка внешних ключейВнешние ключи — это способ связать записи в двух таблицах по определенным полям так, что при обновлении поля в родительской автоматически происходит определенное изменение поля в дочерней (дочернюю и родительскую выбираешь при создании ключа; точнее, создаешь ключ в дочерней, который ссылается на родительскую). | Нет | Да |
Блокировка.Блокировка на уровне строк, т.е. если процессу нужно обновить строку в таблице, то он блокирует только эту строку, позволяя другим обновлять другие строки параллельно | Блокировка на уровне таблиц | Блокировка на уровне строк |
Одновременные запросы к разным частям таблицы. | Медленнее | Быстрее |
При смешанной нагрузке в таблице (select/update/delete/insert) | Медленнее | Быстрее |
Операция Insert | Быстрее | Медленнее, ибо есть оверхед на транзакцию, но это цена надежности |
Если преобладают операции чтения (SELECT) | Работает быстрее | Работает медленнее |
DeadlockDeadlock — ситуация в многозадачной среде или СУБД, при которой несколько процессов находятся в состоянии бесконечного ожидания ресурсов, захваченных самими этими процессами. | Не возникают | Возможны. |
Поддержка полнотекстового поиска | Да | Нет (доступен начиная с версии MySQL 5.6.4) |
Запрос Count(*) | Быстрее | Медленнее |
Поддержка mysqlhotcopyУтилита mysqlhotcopy представляет собой Perl-сценарий, использующий SQL-команды LOCK TABLES, FLUSH TABLES и Unix-утилиты cp или scp для быстрого получения резервной копии базы данных. | Да | Нет |
Файловое хранение таблиц | Каждой таблице отдельный файл | Данные при настройках по умолчанию хранятся в больших совместно используемых файлах |
Бинарное копировании таблиц?Табличные файлы можно перемещать между компьютерами разных архитектур и разными операционными системами без всякого преобразования. | Да | Нет |
Размер таблиц в БД | Меньше | Больше |
Поведение в случае сбоя | Крашится вся таблица | По логам можно все восстановить |
В случае хранения «логов» и подобного | Лучше | Хуже |
Выводы:
- Использовать MyISAM лучше в таблицах, которых преобладает один вид доступа: чтение (новостной сайт) или запись (например, логирование) ;
- Использование InnoDB имеет смысл во всех остальных случаях и случаях повышенных требований по сохранности данных.
- Многие жалуются на частые поломки MyISAM. Лично мне ни разу не доводилось с этим сталкиваться, потому ничего не могу сказать по этому поводу. Но надежность данных – это еще один аргумент в пользу innodb, который и крешит реже и восстанавливается быстрее.
- Каждый движок требует свой “кусок пирога”. Мало перекинуть данные в myisam – нужно чтобы сервер был сконфигурирован так, чтобы этому движку было выделено достаточно ресурсов, иначе поимеем те же самые тормоза. Впрочем, сводных данных для отчетов по статистике не обязательно будет много!
- Если данных немного, например той же статистики, то можно использовать тот же движок что и вся остальная база. По крайней мере лишите себя гемора поддержки двух движков.