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 триггер на каждую таблицу, поэтому, если Вы уже используете триггеры, модифицируйте код.