Pull to refresh

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

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

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
Tags:
Hubs:
+93
Comments 73
Comments Comments 73

Articles