Выбор типа таблиц 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 и обратно :)

Еще много полезного по типам таблиц мускула.