Руководство для начинающих по MySQL Storage Engine

При создании базы данных часто упускается из виду, но критически важным фактором производительности является механизм хранения (особенно по мере роста базы данных). Во многих случаях соблазн просто принять значение по умолчанию и продолжить разработку проекта. Это может привести к неожиданному негативному влиянию на производительность, резервное копирование и целостность данных на более поздних этапах жизненного цикла приложения, например, когда ваша команда реализует аналитику и информационные панели MySQL.

Чтобы избежать этих потенциальных ловушек, мы подробнее рассмотрим некоторые из наиболее широко используемых механизмов хранения, поддерживаемых MySQL (начиная с версии 5.7).

Поддерживаемые двигатели хранения

Какие у меня варианты?

По умолчанию MySQL 5.7 поддерживает десять механизмов хранения (InnoDB, MyISAM, Memory, CSV, Archive, Blackhole, NDB, Merge, Federated и Example). Чтобы увидеть, какие из них доступны и поддерживаются вашим сервером, используйте эту команду:

mysql> SHOW ENGINES\G

Это выведет список механизмов хранения и скажет, какие из них доступны, недоступны или для которых в настоящее время установлено значение по умолчанию. В столбце «Поддержка:» будет отображаться «ДА», «НЕТ» или «ПО УМОЛЧАНИЮ» соответственно.

В некоторых приложениях может возникнуть необходимость иметь разные механизмы хранения для разных таблиц в одной и той же базе данных. Это пример того, почему вам нужно тщательно планировать модель данных для вашего приложения. В большинстве случаев, однако, потребуется только один механизм хранения.

Возможности механизма хранения

В чем они хороши?

Давайте подробнее рассмотрим некоторые из наиболее часто используемых механизмов хранения. Это даст нам представление о том, для чего предназначен каждый движок и как его лучше всего использовать для достижения наших бизнес-целей.

InnoDB: опция по умолчанию в MySQL 5.7, InnoDB — надежный механизм хранения, который предлагает:

• Полное соответствие кислоте.
• Фиксация, откат и восстановление после сбоя.
• Блокировка на уровне строк.
• FOREIGN KEY ограничения ссылочной целостности.
• Увеличьте многопользовательский параллелизм (с помощью неблокирующих операций чтения).

С вышеупомянутой функциональностью, которую предлагает InnoDB, очевидно, почему это движок по умолчанию в MySQL. Это механизм, который хорошо работает и предлагает множество обязательных атрибутов, которые понадобятся любой базе данных. Однако всестороннее обсуждение всех его возможностей выходит за рамки этой статьи. Это двигатель, который, скорее всего, будет использоваться в большинстве приложений.

MyISAM: Функциональность, которая отличает MyISAM, заключается в его способности:

• Индексы полнотекстового поиска.
• Блокировка на уровне стола.
• Отсутствие поддержки транзакций.

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

NDB (или NDBCLUSTER): если в кластерной среде будет работать ваша база данных, NDB является предпочтительным механизмом хранения. Лучше всего, когда вам нужно:

• Распределенных вычислений.
• Высокая избыточность.
• Высокая доступность.
• Максимально возможное время безотказной работы.

Обратите внимание, что поддержка NDB не включена в дистрибутив стандартных двоичных файлов MySQL Server 5.7. Вам придется обновить до последней бинарной версии MySQL Cluster. Хотя, если вы разрабатываете в кластерной среде, у вас, вероятно, есть необходимый опыт для решения этих задач.

CSV : полезный механизм хранения, когда данные должны использоваться совместно с другими приложениями, использующими данные в формате CSV. Таблицы хранятся в виде текстовых файлов с разделенными запятыми значениями. Хотя это облегчает совместное использование данных со сценариями и приложениями, одним из недостатков является то, что файлы CSV не индексируются. Таким образом, данные должны храниться в таблице InnoDB до стадии импорта / экспорта процесса.

Blackhole : этот механизм принимает, но не хранит данные. Как и в UNIX / dev / null, запросы всегда возвращают пустой набор. Это может быть полезно в среде распределенных баз данных, где вы не хотите хранить данные локально или в производительности или других ситуациях тестирования.

Архив : Как и следует из названия, этот движок отлично подходит для редко упоминаемых исторических данных. Таблицы не индексируются, и сжатие происходит после вставки. Транзакции не поддерживаются. Используйте этот механизм хранения для архивирования и извлечения прошлых данных.

Федеративный: Этот механизм хранения предназначен для создания единой локальной логической базы данных, связывая несколько разных физических серверов MySQL. На локальном сервере не хранятся данные, и запросы автоматически выполняются на соответствующем удаленном сервере. Он идеально подходит для сред с распределенными витринами данных и может значительно повысить производительность при использовании MySQL для аналитических отчетов .

Назначение хранилища

Как изменить используемый механизм хранения?

Используемый механизм хранения устанавливается при создании таблицы. Как указывалось ранее, InnoDB является механизмом хранения по умолчанию в MySQL версии 5.5 и выше. Если вы хотите использовать другой, лучше всего сделать это в своем CREATE TABLEзаявлении. Например, предположим, что вы определили таблицу, которая должна использовать механизм хранения CSV. Ваше чрезмерно упрощенное CREATE TABLEутверждение может выглядеть так:

mysql> CREATE TABLE Shared_Data (
    -> Data_ID INTEGER NOT NULL, 
    -> Name VARCHAR(50) NOT NULL, 
    -> Description VARCHAR(150)
    -> ) ENGINE='CSV’;

mysql> INSERT INTO Shared_Data VALUES
    -> (1,’device one’, ‘the latest version of the best tech’),
    -> (2,’device two’, ‘the fastest one on the market’);