Выбор типа таблиц MySQL
MySQL реализует набор различных механизмов хранения (engines, типы памяти), правильный выбор механизма хранения может существенно улучшить производительность приложения.
Основные типы хранилищ
Хранилище | Характерные черты |
---|---|
MyISAM | Используется по умолчанию (до версии 5.5.5), компромисс между производительностью и функциональностью |
InnoDB | Используется по умолчанию (начиная с версии 5.5.5), поддерживает транзакции, производительнее для сложных выборок, надежное восстановление после сбоев |
Memory | Все данные в памяти, используется для анализа данных |
Archive | Используется для хранения архивных данных, логов |
CSV | Данные хранятся в формате файлов CSV |
Maria | Перспективная замена MyISAM |
Falcon | Перспективная замена InnoDB |
MyISAM или InnoDB?
Таблица сравнения MyISAM и InnoDB:
MyISAM | InnoDB |
---|---|
table locking, shared read lock, exclusive write lock | row locking |
быстрый INSERT | |
ручное восстановление при сбоях | высокая надежность при сбоях |
нет транзакций, внешних ключей, хранимых и триггеров | транзакции, внешние ключи, хранимые процедуры, триггеры, multiversion concurrency control |
индексация до 500 символов в TEXT/BLOB, полнотекстовые индексы | |
delayed key writes снижают нагрузку, но при сбое можно потерять индекс | |
сжатие таблиц, обьединение таблиц и т.д. | |
данные в кластеризованном индексe | |
данные могут храниться на разных носителях | |
вторичные индексы ссылаются на первичный |
Теоретически имеет смысл выбирать MyISAM в случае простых SELECT
и INSERT
запросов без транзакций, и InnoDB для сложных транзакционных SELECT
и UPDATE
.
На практике лучше всегда выбирать InnoDB:
- надежнее при сбоях, не нужно делать
repair table
- внешние ключи - более надежный способ сохранения целостности
- выше производительность для сложных запросов с JOIN-ами
- блокировка на уровне записей
- кластерные индексы
Единственный возможный минус - отсутствие полнотекстовых индексов (толку от них все равно мало, лучше использовать Sphinx).
Существующую таблицу можно сконвертировать в InnoDB следующей командой:
ALTER TABLE <tablename> ENGINE=InnoDB;
При наличии дублей в столбце
field
запрос ALTER IGNORE `table` ADD UNIQUE index field_idx(field);
на InnoDB выдаст ошибку, чтобы «сработало» - переконвертировать в MyISAM и обратно :)
Еще много полезного по типам таблиц мускула.