engine innodb что значит

MySQL Storage Engines

Двигатели хранения

Механизмы хранения (базовый программный компонент) являются компонентами MySQL, которые могут обрабатывать операции SQL для различных типов таблиц для хранения и управления информацией в базе данных. InnoDB в основном используется механизм хранения общего назначения и начиная с MySQL 5.5 и более поздних версий это механизм по умолчанию. В MySQL доступно множество механизмов хранения, и они используются для разных целей.

Версия: MySQL 5.6

Системы хранения MySQL

Это механизм хранения по умолчанию для MySQL 5.5 и выше. Он предоставляет безопасные транзакции (ACID-совместимые) таблицы, поддерживает ограничения ссылочной целостности FOREIGN KEY. Он поддерживает функции фиксации, отката и восстановления после сбоя для защиты данных. Он также поддерживает блокировку на уровне строк. Это «согласованное чтение без блокировки» повышает производительность при использовании в многопользовательской среде. Он хранит данные в кластерных индексах, что уменьшает количество операций ввода-вывода для запросов на основе первичных ключей.

Другие темы:

Список модулей хранения, поддерживаемых вашей установкой MySQL

Следующая команда отображает информацию о состоянии механизмов хранения сервера.

Настройка механизма хранения

В CREATE TABLE STATEMENT вы можете добавить опцию ENGINE table, чтобы упомянуть механизм хранения. Смотрите следующие операторы CREATE TABLE, где использовались разные движки:

Вы можете установить механизм хранения по умолчанию для текущего сеанса, установив переменную default_storage_engine с помощью команды set.

Если вы хотите преобразовать таблицу из одного механизма хранения в другой, используйте инструкцию ALTER TABLE. Смотрите следующее утверждение:

MySQL: механизм хранения InnoDB

Особенности движка хранения InnoDB:

Преимущества хранилища InnoDB

Создание таблиц InnoDB:
Используйте оператор CREATE TABLE, чтобы создать таблицу InnoDB без каких-либо специальных предложений. Начиная с MySQL 5.5, это стандартный механизм хранения MySQL. В MySQL 5.6 при выполнении оператора CREATE TABLE без предложения ENGINE = создается таблица InnoDB. Вот пример:

Следующий оператор SHOW TABLE STATUS показывает свойства таблиц (принадлежит базе данных «tutorial»).

Обработка AUTO_INCREMENT в InnoDB :

InnoDB предоставляет метод, который улучшает масштабируемость и производительность операторов SQL, которые вставляют строки в таблицы со столбцами AUTO_INCREMENT. Чтобы использовать механизм AUTO_INCREMENT с таблицей InnoDB, столбец AUTO_INCREMENT (в данном случае col1) должен быть определен как часть индекса. Смотрите следующий пример:

Обработка ограничений FOREIGN KEY в InnoDB :

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

Ограничение: таблица InnoDB :

MySQL: механизм хранения MyISAM

Механизм хранения MyISAM основан на более старом механизме хранения ISAM (сейчас недоступен), но имеет много полезных расширений.

Особенности механизма хранения MyISAM:

Каждая таблица MyISAM хранится на диске в трех файлах.

Создание таблиц MyISAM:
Используйте оператор CREATE TABLE, чтобы создать таблицу MyISAM с предложением ENGINE. Начиная с MySQL 5.6, необходимо использовать предложение ENGINE, чтобы указать механизм хранения MyISAM, потому что InnoDB является механизмом по умолчанию. Вот пример:

Следующий оператор SHOW TABLE STATUS показывает свойства таблиц (принадлежит базе данных «tutorial»).

Основные характеристики таблиц MyISAM:

Поврежденные таблицы MyISAM:

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

MySQL: MEMORY Storage Engine

Механизм хранения MEMORY создает таблицы, которые хранятся в памяти. Поскольку данные могут быть повреждены из-за проблем с оборудованием или электропитанием, эти таблицы можно использовать только как временные рабочие области или кэши только для чтения для данных, извлеченных из других таблиц. Когда сервер MySQL останавливается или перезапускается, данные в таблицах MEMORY теряются.

Особенности MEMORY Storage Engine:

Создание таблиц MEMORY:
Используйте оператор CREATE TABLE для создания таблицы MEMORY с предложением ENGINE. Начиная с MySQL 5.6, необходимо использовать предложение ENGINE, чтобы указать механизм хранения MEMORY, потому что InnoDB является механизмом по умолчанию. В следующем примере показано, как создать и использовать таблицу MEMORY:

Следующий оператор SHOW TABLE STATUS показывает свойства таблиц (принадлежит базе данных «tutorial»).

Удалить таблицу MEMORY:

Индексы: Механизм хранения MEMORY поддерживает индексы HASH и BTREE. Добавляя предложение USING, вы можете указать одно или другое для данного индекса. Смотрите следующие примеры:

Когда использовать механизм хранения MEMORY:

MySQL: MERGE Storage Engine

Механизм хранения MERGE (также известный как MRG_MyISAM) представляет собой набор идентичных таблиц MyISAM (идентичные столбцы и индексные данные в одинаковом порядке), которые можно использовать как одну таблицу. У вас должны быть привилегии SELECT, DELETE и UPDATE для таблиц MyISAM, которые вы сопоставляете с таблицей MERGE.

Создание таблиц MERGE:
Чтобы создать таблицу MERGE, необходимо указать параметр UNION = (список таблиц) (указывает, какие таблицы MyISAM использовать) в операторе CREAE TABLE. В следующем примере сначала мы создали три таблицы с двумя строками, затем объединили их в одну таблицу, используя механизм хранения MERGE:

Следующий оператор SHOW TABLE STATUS показывает свойства таблиц (принадлежит базе данных «tutorial»).

Проблема безопасности: если у пользователя есть доступ к таблице MyISAM, скажем, t1, он может создать таблицу MERGE m1, которая обращается к t1. Однако, если администратор аннулирует привилегии пользователя на t1, пользователь может продолжить доступ к данным с t1 по m1.

MySQL: CSV Storage Engine

Вы можете прочитать, изменить файл ‘color.CSV’ с помощью приложений для работы с электронными таблицами, таких как Microsoft Excel или StarOffice Calc.

Ограничения CSV:

Следующий оператор SHOW TABLE STATUS показывает свойства таблиц (принадлежит базе данных «tutorial»).

MySQL: ARCHIVE Storage Engine

Особенности хранилища ARCHIVE:

АРХИВ поддерживает механизм хранения

ARCHIVE хранилище не поддерживает

АРХИВ хранилища: хранение и поиск

MySQL: ПРИМЕР Механизма хранения

MySQL: BLACKHOLE Storage Engine

Следующий оператор SHOW TABLE STATUS показывает свойства таблиц (принадлежит базе данных «tutorial»).

MySQL: FEDERATED Storage Engine

Создать FEDERATED таблицу

Вы можете создать таблицу FEDERATED следующими способами:

Использование CONNECTION : Чтобы использовать этот метод, необходимо указать строку CONNECTION после типа механизма в инструкции CREATE TABLE. Смотрите следующий пример:

Формат строки подключения выглядит следующим образом:

Использование CREATE SERVER: чтобы использовать этот метод, необходимо указать строку CONNECTION после типа механизма в инструкции CREATE TABLE. Смотрите следующий пример:

Имя_сервера используется в строке подключения при создании новой таблицы FEDERATED.

Различия между InnoDB и MyISAM

Источник

Tech blog by @dizballanze

Для того что-бы посмотреть какие типы поддерживает ваша инсталляция MySQL необходимо выполнить следующий SQL запрос:

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

В версии MySQL 5.5 поддерживается 9 различных типов таблиц.

Сравнительная таблица основных типов таблиц

Тестирование производительность InnoDB и MyIASM

Наибольший интерес для web-разработчика составляют innodb и myisam. Сейчас мы проведем сравнительный тест производительности этих типов таблиц. Для этого сначала создадим две одинаковые по структуре таблицы, но с разным типом движка хранения:

Напишем небольшой скрипт который будет выполнять 3 теста: запись данных (insert), выборка по ключу, выборка по не ключевому полю.

Для того что-бы выполнить тест, нужно раскоментить один соответствующий блок кода. И собственно, то что у меня получилось в результате тестирования:

Как мы видим myisam работает значительно быстрее, особенно это заметно при вставке данных. Хотя innodb и дает ряд новых возможностей и преимуществ, такая медлительность не позволяет ему конкурировать с myisam, особенно в web-приложениях.

Источник

Правильная миграция с MyISAM на InnoDB

Давайте я отвлеку вас от котиков и расскажу, основываясь на своём опыте, какие подводные камни появляются при переходе с MyISAM на InnoDB, и как их избежать. Код приложения будет на PHP.

Этот пост я решил написать, прочитав огромное количество неправильных ответов на запрос из сабжа в интернете. По всему интернету разбросаны неграмотные или не полные ответы, в результате чего складывается впечатление о том, что смигрировать вашу базу данных на InnoDB — это очень просто. Нет, это не просто! Итак, начнем!

Зачем переходить на InnoDB

С этим вопросом, я думаю, всем всё ясно. Объяснять не буду — преимуществам InnoDB посвящены куча статей в интернете. Если ты читаешь эти строки, то значит ты осознанно пришел к этой мысли о переводе своего хозяйства на InnoDB, и ты, хабраюзер, гуглишь) Надеюсь, эта статья — то, что тебе надо.

Подготовительный этап

1. Из банального — это обеспечить необходимое количество свободного места на диске, где у нас развернута база. InnoDB занимает примерно в 1,5 раза больше места, чем MyISAM.

2. Очень важный момент — он вам пригодится в будущем при траблшутинге перформанс ишшусов в базе. Нужно прокомментировать каждый SQL запрос в вашем приложении с использованием уникального идентификатора, например, порядкового номера. Если у вас сотни или тысячи SQL запросов, то как вы жили до сих пор без этого?

Если так сделать, то запросы вида SHOW PROCESSLIST, а также дампы запросов в slow лог файлы будут содержать подсказку для вас — номер SQL запроса, и потом вы мгновенно сможете найти этот запрос в коде и оптимизировать его.

3. Прописываем в конфиг-файле my.cnf:

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

4. Настройка размера кэшей для InnoDB — в том же my.cnf файле:

5. Настройка способа работы базы с транзакциями

Я на своем приложении выставил уровень изоляции транзакций READ-COMMITTED, вместо выставляющегося по умолчанию REPEATABLE-READ, поскольку в противном случае в базе было бы чрезмерное количество дедлоков. Я для себя решил, что мое приложение может прочитать не самые свежие данные, ценой более быстрой работы, вместо абсолютно актуальных данных, но отягощенных множеством блокировок. Впрочем, для mission-критикал транзакции в коде можно повысить её уровень изоляции — этот эффект будет действовать только на одну транзакцию:

Следующий параметр — таймаут, который я специально снизил с 50 до 5 секунд, чтобы он не подвешивал клиентские сессии на очень долго при наличии блокировок.

innodb_rollback_on_timeout очень важен относительно того, как именно ваш код обрабатывает ошибки. С этим моментом я не встречал ясности, поэтому расскажу.

— если этого флага нет, то InnoDB, при наступлении таймаута (Error code 1205) будет откатывать только один этот затаймаутившийся стейтмент в вашей транзакции. То есть, вам нужно будет повторить только его, а не всю транзакцию с начала. Для меня этот вариант показался сложнее в реализации.

— если флаг выставлен, то откатывается вся транзакция, точно так же, как это делается при выявлении дедлока (Error code 1213). Я выбрал именно этот вариант, потому что это позволяет сделать код обработки ошибок унифицированным, т.е. повторять транзакцию с первого стейтмента, с начала, при получении любой из этих двух ошибок.

innodb_log_file_size придется увеличить из-за подводного камня №3 (ниже), поскольку этот лог должен быть достаточным для хранения как минимум нескольких записей, а при наличии записей типа MEDIUMTEXT их размер может превысить несколько мб, поэтому дефолтное значение в 5мб крайне мало. После изменения этого параметра базу нужно остановить, старые файлы ib_logfile0 и ib_logfile1 нужно удалить, и только потом поднимать базу.

Чего бояться в InnoDB

Собственно, в InnoDB нужно внимательно смотреть только за этими двумя кодами ошибок: 1205 (таймаут) и 1213 (дедлок), которых не было в MyISAM. При настройке сервера, приведенной выше, он будет сам откатывать ваши транзакции в обоих случаях. Вам надо будет их повторить сначала. При этом ваш прикладной код может состоять как из отдельных стейтментов — транзакций (при autocommit=1), так и из транзакций, состоящих из нескольких SQL стейтментов — в этом случае транзакция начинается с
и завершается

(Про mysqli_begin_transaction() знаю, но он только для MySQL >= 5.6, а не везде такие новые MySQL сервера).

Если у вас какой-то вызов mysqli_query() не обернут в for($i=0;$i

Источник

Подсистемы (движки) хранения в базе данных MySQL 8

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

Подсистема хранения данных помогает обрабатывать различные операции SQL для различных типов таблиц. Каждая подсистема хранения имеет свои пре­имущества и недостатки. Выбор подсистемы хранения всегда будет зависеть от потребностей. Важно понимать особенности каждой подсистемы хранения и вы­бирать наиболее подходящую для ваших таблиц, чтобы максимизировать произ­водительность базы данных. В MySQL всякий раз, когда мы создаем новую табли­цу, подсистемой хранения данных по умолчанию является InnoDB.

InnoDB

В MySQL 8 подсистема хранения данных InnoDB используется по умолчанию и яв­ляется наиболее широко применяемой из всех других доступных подсистем хра­нения. Подсистема InnoDB была выпущена вместе с MySQL 5.1 как плагин в 2008 году, и она рассматривается как подсистема хранения по умолчанию, начиная с версии 5.5 и выше. Поддержка подсистемы хранения InnoDB была перенята кор­порацией Oracle в октябре 2005 года у финской компании Innobase Oy.

Таблицы InnoDB поддерживают ACID-совместимые фиксации транзакций, от­кат и возможности аварийного восстановления для защиты пользовательских данных. InnoDB также поддерживает блокировку на уровне строк, что помогает улучшить параллелизм и производительность. InnoDB хранит данные в кластери­зованных индексах, чтобы уменьшить операции ввода-вывода для всех запросов SQL на выборку данных на основе первичного ключа. InnoDB также поддержи­вает ограничения внешнего ключа, которые обеспечивают лучшую целостность данных в базе данных. Максимальный размер таблицы InnoDB может масшта­бироваться до 256 Тб, что должно быть вполне достаточным во многих случаях использования больших данных.

Важные замечания по InnoDB

MyISAM

Подсистема хранения данных MyISAM использовалась по умолчанию для MySQL вплоть до версии 5.5 1. В отличие от InnoDB, таблицы подсистемы хранения данных MylSAM не поддерживают ACID-совместитмость. Таблицы MylSAM поддерживают только блокировку уровня таблицы, поэтому таблицы MyISAM небезопасны для транзакций. Таблицы MyISAM оптимизированы для сжатия и скорости. MyISAM обычно используется, когда вам нужно иметь в основном операции чтения с ми­нимальными транзакционными данными. Максимальный размер таблицы My- ISAM может достигать 256 Тб, что помогает в таких случаях, как анализ данных.

Важные примечания относительно таблиц MyISAM

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

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

Memory

Подсистема хранения в памяти (подсистема оперативного хранения данных) обычно называется подсистемой хранения данных на основе кучи. Она исполь­зуется для чрезвычайно быстрого доступа к данным. Эта подсистема хранения содержит данные в оперативной памяти, поэтому ей не нужны операции ввода- вывода. Поскольку она хранит данные в оперативной памяти, все данные теря­ются при перезапуске сервера. Такая подсистема в основном используется для временных таблиц или таблицы подстановки. Эта подсистема поддерживает бло­кировку на уровне таблицы, которая ограничивает параллелизм с высокой часто­той записи.

Ниже приведены важные примечания об оперативных таблицах Memory.

Archive

Blackhole

Эта подсистема хранения данных принимает данные, но их не сохраняет. Вместо сохранения данных она отбрасывает (уничтожает) их после каждой вставки.

В следующем ниже примере показана работа таблицы BLACKHOLE :

Эта подсистема хранения полезна для репликации с большим количеством сер­веров. Подсистема хранения данных Blackhole работает в качестве фильтрующе­го сервера между ведущим и ведомым серверами, который не хранит никаких данных, но который применяет только правила replicate-do-* и replicate-ignore-* и пишет двоичные журналы. Эти двоичные журналы используются для выполне­ния репликации на ведомых серверах. Мы обсудим это подробно в главе 6 «Репли­кация для построения высокодоступных решений».

Merge

В следующем ниже примере показано, как создавать таблицы MERGE:

Как правило, эта подсистема используется для управления таблицами, связан­ными с журналом регистрации событий. В отдельных таблицах MyISAM можно задавать различные месяцы журналов и объединять эти таблицы с помощью под­системы хранения данных MERGE.

Таблицы MyISAM имеют ограничение по объему хранения для операционной системы, но коллекция таблиц MyISAM (MERGE) не имеет таких ограничений. Та­ким образом, использование подсистемы MERGE позволит вам разделять данные на многочисленные таблицы MyISAM, что может помочь в преодолении ограни­чений по объему хранения.

С помощью подсистемы MERGE трудно выполнять разделение, следовательно, таблицами MERGE оно не поддерживается, и мы не можем реализовать раздел на таблице MERGE или любой таблице MyISAM.

Federated

Давайте создадим таблицу FEDERATED.

В поле CONNECTION содержится следующая ниже информация для вашей справки:

NDB Cluster

Кластерная подсистема хранения данных NDB Cluster может конфигуриро­ваться с помощью ряда параметров аварийного переключения и балансиров­ки нагрузки, но проще всего начать с подсистемы хранения на уровне кла­стера. NDB Cluster использует подсистему хранения NDB и содержит полный набор данных, который зависит только от других наборов данных, доступных в кластере.

Кластерная часть NDB Cluster настроена независимо от серверов MySQL. В NDB Cluster каждая часть кластера считается узлом.

Как выбрать движок (подсистему хранения) MySQL?

Следующая ниже схема поможет вам понять, какую подсистему хранения дан­ных вам нужно использовать для ваших потребностей:

Каждая подсистема хранения данных имеет свое преимущество и удобство ис­пользования:

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

Источник