Краткий обзор движков таблиц MySQL

Цель этой статьи — дать краткий, очень сжатый обзор движков, для того, чтобы статьей можно было пользоваться при выборе движка на этапе проектирования \ создания \ оптимизации таблицы. Предполагается, что читатель знает суть вопроса по крайней мере поверхностно и способен сам отыскать всю дополнительную информацию (вопросы в комментах можно задавать всегда :) )

MyISAM

• транзакций нет
• макс. диск: 256Тб
• блокировка таблица
• полнотекстовый поиск
• работа в кластере: нет
• поддержание целостности, внешние ключи: нет
• репликация: да
• макс. индексов: 64
• макс. записей: 2^32
• макс. длина ключа: 1000 байт
• ключи занимают место на диске до (макс.): (key_length+4)/0.67
• чувствительные к «падению» сервера, сложно восстанавливать
• при отсутствии «дырок» (gaps) — вставки не конкурентные (блокировок не происходит)
• возможно хранить файлы данных и индексов на разных устройствах
• каждый столбец может иметь свою кодировку
• макс. сумма длин VARCHAR и CHAR: 64к

Static (Fixed-length) формат таблиц

• автоматически, если нет VARCHAR, VARBINARY, BLOB, TEXT столбцов
• быстрее, безопаснее (устойчивее), лучше кешируется, требует больше места на диске
• если указать принудительно, VARCHAR и CHAR заполняются пробелами, VARBINARY — нулями

Dynamic length формат таблиц

• все строки длиной до 4 — VARCHAR
• пустые строки и ноль (0) не занимают места на диске (NULL это не ноль)
• запись (строка) фрагментируется автоматически при апдейтах (нужно запускать OPTIMIZE TABLE для дефрагментации)
• сложнее восстановить при сбоях

Compressed

• создается утилитой myisampack
• read-only
• рекомендуется для очень медленных носителей
• может быть и fixed-length и dynamic-length
• посмотрите в сторону Archive table engine

Советы:

• говорят, что MyISAM таблицы обязательно «ломаются» рано или поздно, так что будте готовы ;)
• не убивайте сервер во время записи
• не изменяйте таблицы несколькими серверами одновременно
• не изменяйте таблицы утилитой и сервером одновременно

Рекомендации: справочники

InnoDB

• макс. диск: 64Тб
• полная поддержка транзакций (4 уровня изоляции)
• блокировка записи (не таблицы), два вида блокировок (SHARED, EXCLUSIVE)
• полнотекстовый индекс: нет
• безопасная для транзакций
• индексы кластеризуются для «типичных запросов»
• поддержка целостности (внешние ключи)
• может использоваться на ОС с ограниченным размером файла
• множество настроек для оптимизации
• позволяет использовать Raw Disk для таблиц в обход ФС
• по умолчанию включен AUTOCOMMIT (SET autocommit=1)
• автоматически детектит дэдлоки (deadlocks)

Движок был разработан специально для больших таблиц. Разработчики заявляют, что InnoDB — самый быстрый из всех известных движков для БД основанных на дисках (множественные тесты это подтверждают)

Советы:

• SELECT (*) FROM table работает гораздо медленнее, чем MyISAM — создавайте триггеры если нужно
• бэкап простым копирование файлов невозможен
• mysqldump работает медленно, для бэкапа используйте InnodDb Hot Backup
• следите за индексами, выгода InnoDB теряется, если для запросов нет индексов

Рекомендации: высоконагруженные сайты, финансовые транзакции

MERGE

• Используется для объединения одинаковых таблиц в одну
• таблицы должны иметь идентичную структуру
• порядок столбцов должен совпадать
• DROP не удаляет исходных таблиц
• таблицы могут быть в другой базе данных
• можно использовать для алиасов (для одной таблицы)
• нельзя пользоваться FULLTEXT search
• нельзя смешивать временные и не временные таблицы
• медленная при чтении по ключу
• REPLACE не работает
• не отслеживаются изменения в структуре исходных таблиц (таблица будет поломана)

Рекомендации: «удобная» (ре)организация таблиц

HEAP (MEMORY)

• транзакций нет
• блокировка таблицы
• репликация: да
• макс. длина ключа: 500 байт
• все данные теряются при остановке сервера (сама таблица остаётся)
• формат хранения: всегда fixed-length row
• память не высвобождается при удалении записи (используется для вставки новых)

Советы:

большие таблицы «свопяться» на диск и выигрыш теряется

Рекомендации: Локальные вычисления, временные данные

ARCHIVE

• макс. диск: нет ограничения
• блокировка записи
• не работает DELETE, REPLACE, UPDATE, ORDER BY, тип BLOB
• INSERT буферизируется и «сливается» с большой задержкой
• очень медленный SELECT

Рекомендации: логирование операций (аудит, статистические данные, счетчики)

CSV

• хранит таблицы в CSV формате
• позволяет редактировать таблицы внешними приложениями
• плохо документирован, есть открытые баги

FEDERATED

Представляет собой «прозрачное» подключение к другому серверу (не репликация). Есть множество ограничений, планируется возможность подключения к отличным от MySQL серверам.

BLACKHOLE

• данные идут «вникуда»
• двоичные логи пишуться

Рекомендации: оптимизация репликации (мастер-сервер не пишет данные на диск)

Тренды, MySQL 6.0

• Maria — «улучшенный MyISAM»
• Falcon — «улучшенный InnoDb», улучшенная производительность, для Web серверов

Ссылки:

MySQL Documentation, Chapter 13. Storage Engines
MySQL Storage Engines by Mike Peters