Краткий обзор движков таблиц 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