Расставляй правильно приоритеты и не отвлекайся на мелочи

MySQL движки хранения данных

Вольный перевод: MySQL Storage Engines
Автор: Mike Peters

Одна из великолепных возможностей MySQL, отличная от бесплатности, широкой поддержки и быстроты, заключается в выборе различных движков хранения данных (storage engines) для различных таблиц.

MySQL предлагает 7 движков хранения данных, включая "example" - который позволяет Вам реализовать собственную библиотеку хранения.

Что-же такого великолепного в обладании всеми этими вариантами?

Каждый движок хранения данных полностью различен и спроектирован для конкретных нужд приложений.

Не зацикливаясь на одном движке (как Oracle), Вы тем самым можете оптимизировать и выбирать лучший инструмент для своей работы.

Совет: Хорошо спроектированное приложение, активно использующее MySQL, должно использовать различные движки хранения данных для различных таблиц. Если Вы все еще завязли на MyISAM таблицах, может теперь стоит, что-то пересмотреть.

Обзор движков хранения данных MySQL

MyISAM: Движок по умолчанию. Не поддерживает транзакций, средняя надежность хранения данных. Превосходная производительность чтения данных для интенсивных приложений. Большинство веб сервисов и хранилищ данных активно используют MyISAM.

HEAP: Все в памяти. Очень быстрый поиск данных, однако все они хранятся только в памяти и будут потеряны при остановке сервера. Великолепно подходит для временных таблиц.

Archive: Используется для хранения больших объемов данных без индексов, занимая меньший размер.

Merge: Коллекция MyISAM таблиц логически объединенных вместе для единого представления.

InnoDB: Транзакционный тип движка, применяемый при интенсивных операциях записи, спасибо возможности блокировки уровня строк таблицы. Великолепная восстанавливаемость и высокая надежность хранения данных. Движок InnoDB был приобретен Oracle в 2005 году.

NDB: Кластерный движок - данные автоматически разделяются и реплицируются по различным машинам, именуемым - дата узлы. Применяется для приложений, которые требуют высокой производительности с наивысшей степенью доступности. NDB хорошо работает на системах требующих высокой отдачи на операциях чтения. Для "тяжелых" приложений требующих активной записи в конкурирующей среде рассмотрите вариант с InnoDB.

Что-бы лучше понять уникальные характеристики каждого движка хранения данных, посмотрите на эту "магическую" диаграмму:

Примеры:

Ниже приведены несколько примеров использования наиболее подходящих движков хранения для различных задач:

  • Поисковый движок - NDB кластер
  • Логирование веб статистики - Обычные файлы для логирования с оффлайновым обработчиком и записью всей статистики в InnoDB таблицы
  • Финансовые транзакции - InnoDB
  • Сессионные данные - MyISAM или NDB кластер
  • Локальные расчеты - HEAP
  • Словари - MyISAM

Важные замечания по MyISAM таблицам:

  • Таблицы могут быть повреждены. Ежедневно архивируйте Ваши данные или установите еще один MySQL сервер для выполнения репликаций.
  • Включите авто-восстановление (auto-repair) в настройках Вашего сервера (my.cnf):
    myisam-recover=backup,force
    или рассмотрите возможность выполнения ежедневной автоматической проверки таблиц баз данных.
  • Очень быстрое чтение данных (через SELECT)
  • Конкурирующие записи полностью блокируют таблицы. Переключите все, что возможно, на оффлайн обработку записей сериями, что-бы не загружать движок сервера баз данных. (Оффлайн обработка - золотое правило, применимое для всех типов таблиц)

Важные замечания по HEAP/Memory таблицам:

Пока этот тип таблиц предлагает супер быстрый возврат данных, который работает только для небольших временных таблиц.

Если Вы загружаете слишком большие объемы данных в Memory таблицы, MySQL начинает свопировать информацию на диск и тем самым Вы теряете все преимущества Memory движка.

Важные замечания по InnoDB таблицам:

  • Поддержка ACID транзакций. Встроенная отказоустойчивость данных, равная надежности 99.999%. Блокировка уровня строк (сравните с полной блокировкой всей таблицы в MyISAM) означает обеспечение быстрой записи конкурирующих операций.
  • Выполнение "SELECT Count(*) FROM table" без индексов выполняется в InnoDB очень медленно и требует сканирования всей таблицы. (Для MyISAM эта операция ничего не стоит, потому что он хранит внешние записи счетчиков для каждой таблицы).
    Если Вам часто необходима операция "SELECT COUNT(*)" на таблицах InnoDB, создайте MySQL триггер на вставку/удаление, который будет увеличивать/уменьшать счетчик, когда данные добавляются или удаляются из таблицы.
  • Резервирование (бакапирование)
    Простое архивирование всех файлов таблиц для InnoDB невозможно.
    MySQLDump резервирует InnoDB очень медленно. (Если Вы настаиваете на таком резервировании, включите флаг: --opt --compress)
    Быстрое жизнеспособное резервирование, которое так-же может быть использовано как новая "ведомая" (slave) машина, это InnoDB Hot Backup.
  • Восстановление
    В InnoDB встроена поддержка восстановления, которая работает в 99% случаев автоматически. Никогда не трогайте .frm или .ibd файлы в надежде "помочь" восстановлению базы данных. Если встроенное восстановление не сработало, переключайтесь на "ведомый" сервер и восстанавливайте основной из архивов.
  • LOAD DATA INFILE в InnoDB работает очень медленно. Для операций LOAD DATA присмотритесь к использованию MyISAM таблиц.
  • InnoDB меньше, чем MyISAM, прощает выполнение запросов построенных не на индексах. InnoDB отправит Вас в "школу", что-бы быть уверенным, что каждый запрос или обновление будет запущено на индексах. Выполните непроиндексированный запрос и Вы поплатитесь за это временем исполнения.
  • Никогда не изменяйте my.cnf InnoDB лог файл, когда запущен сервер баз данных. Вы разрушите последовательный лог-номер (log sequence number) оставшись без возможность восстановления.
  • Для увеличения производительности InnoDB, присмотритесь к использованию следующих настроек (my.cnf):

    innodb_open_files = 500
    innodb_file_per_table
    innodb_buffer_pool_size = 250M
    innodb_flush_log_at_trx_commit = 2
    innodb_thread_concurrency =8
    innodb_lock_wait_timeout = 500
    interactive_timeout = 20
    back_log = 75
    table_cache = 300
    thread_cache = 32
    thread_concurrency = 8
    wait_timeout = 30
    connect_timeout = 10

Расширяемость

Каждое успешное веб приложение, в конце концов, перерастает возможности сервера баз данных размещающегося на одной машине. В этом случае, обычно, Вы имеете две возможности - Репликации или Кластер NDB. Выбор зависит от требований Вашего приложения.

Для активно-читающей (read-heavy) среды, используйте NDB кластер или установите репликации для n MyISAM ведомых read-only машин.

Для активно-пишущей (write-heavy) среды, InnoDB с активно/пассивными репликациями будет лучшим типовым решением. Но Вы можете поэкспериментировать с NDB кластером. NDB кластер обычно медленнее чем InnoDB в операциях с активной записью, но он предлагает наивысший уровень доступности.

Полезные срипты

  • Конвертирование таблиц в InnoDB
  • MySQLDump одной базы в другую (разработано для случая, когда Вы не можете использовать InnoDB Hot backup и у Вас есть множество таблиц/баз для импорта. Скрипт автоматически пересоздает, переоткрывает соединение и импортирует одну таблицу за раз)
  • Добавление триггеров к таблицам для быстрого count(*) в InnoDB
    Вы можете установить только один delete/insert триггер на каждую таблицу, поэтому, если Вы уже используете триггеры, модифицируйте код.