Какой движок таблиц поддерживает транзакции в mysql

Конвертация движка таблицы в MySQL

Иногда нужно конвертировать движок таблицы MySQL в уже существующей базе данных.

Например, мне понадобилось создать связи между таблицами. Для их создания таблицы должны быть InnoDB (чтобы поддерживать foreign key).

Старые WordPress (и не только) проекты используют MyISAM, потому что до версии MySQL 5.5.4 это был движок по умолчанию. Но он не поддерживает множество полезных функций, таких как внешние ключи, транзакции и т.д. Все эти функции поддерживает более современный движок InnoDB, который теперь используется по умолчанию.

Но это не есть большой проблемой, так как таблицы можно конвертировать. Для конвертирования одной таблицы достаточно SQL запроса (который можно выполнить, например, через phpmyadmin или командную строку mysql):

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

А потом уже их исполнить (Через тот же phpmyadmin или командную строку).

Или сделать одной командой с помощью bash и MySQL интерпретатора (используя консоль):

Пример слегка запутан, поэтому разберем его детально.

передает через пайп запрос mysql интерпретатору, обратите внимание на опции -u и -p — это пользователь и пароль до БД.

Результат будет, например, следующий:

Первая строка — это название поля. Для того, чтобы этот результат передать mysql интерпретатору, удаляем первую строку:

Дальше все это передаем опять mysql:

Здесь уже нужно указать имя базы, так как его нету в сгенерированных запросах.

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

Tech blog by @dizballanze

Здравствуйте, сегодня мы поговорим о типах таблиц в MySQL. Архитектура MySQL позволяет подключать разные движки таблиц. На данный момент MySQL поддерживает множество различных типов таблиц, каждый из которых имеет свои преимущества и недостатки. Я перечислю и коротко опишу основные типы таблиц, а затем проведу небольшой тест производительности наиболее часто используемых типов — myisam и innodb.

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

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

Engine	Support	Comment	Transactions	XA	Savepoints
FEDERATED	NO	Federated MySQL storage engine	NULL	NULL	NULL CSV
MyISAM	YES	MyISAM storage engine	NO	NO	NO
BLACKHOLE	YES	/dev/null storage engine (anything you write to it disappears)	NO	NO	NO
MRG_MYISAM	YES	Collection of identical MyISAM tables	NO	NO	NO
MEMORY	YES	Hash based, stored in memory, useful for temporary tables	NO	NO	NO
ARCHIVE	YES	Archive storage engine	NO	NO	NO
InnoDB	DEFAULT	Supports transactions, row-level locking, and foreign keys	YES	YES	YES
PERFORMANCE_SCHEMA	YES	Performance Schema	NO	NO	NO

Нас в основном будет интересовать столбец support, который содержит информацию о поддержке типа таблицы и может принимать значения: NO — не поддерживается, YES — поддерживается, DEFAULT -используется по-умолчанию. Начиная с версии 5.5.5 по-умолчанию выбран тип innodb, ранее стандартным типом был myisam.

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

• InnoDB — движок с поддержкой транзакций, откатов и защитой от потери данных. В данном типе таблиц используются блокировки на уровне записи и не блокирующее чтение, что позволило улучшить производительность при многопользовательском режиме работы. InnoDB сохраняет пользовательские данные в кластерных индексах, что позволяет компенсировать в/в для простых запросов основанных на первичных ключах.
• MyISAM — движок таблиц MySQL используемый в основном в Web-приложениях, хранилищах данных и других программных средах. Данный тип таблиц поддерживается всеми инсталляциями MySQL.
• Memory — хранит данные в оперативной памяти для очень быстрого доступа. Также известен как HEAP (куча).
• Merge — используется для логического объединения одинаковых MyISAM таблиц и обращение к ним, как к единому объекту. Хорошо подойдет для очень больших хранилищ данных.
• Archive — идеальное решение для хранения больших объёмов информации, к которой не требуется частый доступ.
• Federated — предоставляет возможность объединять различные MySQL сервера для создания одной логической базы данных из нескольких физических машин. Идеально подойдет для архитектур, которые поддерживают распределенное хранение данных.
• CSV — хранит пользовательские данные в текстовых файлах разделяя значения запятыми. Используется если необходим простой обмен с приложениями, которые умеют экспортировать/импортировать данные из CSV формата.
• Blackhole — принимает, но не возвращает никаких данных. Результатами любых запросов из таких хранилищ будут пустые выборки.
• Example — тестовый движок, не выполняет никаких функций, будет полезен только разработчикам, которые собираются писать свой движок, в качестве примера.

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

Функция	MyISAM	Memory	InnoDB	Archive
Максимальный объём хранимых данных	256TB	RAM	64TB	Нет
Транзакции	Нет	Нет	Да	Нет
Блокировки	Таблица	Таблица	Запись	Запись
MVCC	Нет	Нет	Да	Нет
B-деревья	Да	Да	Да	Нет
Хэш индексы	Нет	Да	Нет	Нет
Индексы полнотекстового поиска	Да	Нет	Нет	Нет
Кластерные индексы	Нет	Нет	Да	Нет
Кэширование данных	Нет	Н/д	Да	Нет
Кэширование индексов	Да	Н/д	Да	Нет
Сжатие данных	Да	Нет	Да	Да
Шифрование данных	Да	Да	Да	Да
Поддержка кластерных БД	Нет	Нет	Нет	Нет
Репликация	Да	Да	Да	Да
Внешние ключи	Нет	Нет	Да	Нет
Бэкап	Да	Да	Да	Да
Кэширование запросов	Да	Да	Да	Да

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

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

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

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

Тест	InnoDB	MyISAM
Вставка данных(insert)	15.697 с	1.591 с
Выборка по ключу	1.678 с	1.603 с
Выборка по не ключевому полю	149.961 c	95.984 c

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

MyISAM: подсистема хранения базы данных MySQL

Будучи подсистемой хранения по умолчанию в MySQL 5.1 и более ранних верси­ях, MyISAM предоставляет большой список функций, таких как полнотекстовое индексирование, сжатие и пространственные функции (для геоинформационных систем, ГИС). MyISAM не поддерживает транзакции или построчные блокировки. Ее самым слабым местом, несомненно, является то, что она не имеет даже удаленного механизма защиты от сбоев. Из-за подсистемы MyISAM MySQL до сих пор имеет репутацию СУБД без транзакций, хотя позволяет использовать транзакции уже более десяти лет! Тем не менее MyISAM не так уж плоха для нетранзакционной, не отказоустойчивой подсистемы хранения. Если вам нужны данные только для чтения или если ваши таблицы невелики и их восстановление не будет чересчур сложным, то не должно возникнуть вопросов по использованию MyISAM. (Но, пожалуйста, не применяйте ее по умолчанию. Вместо этого задействуйте InnoDB).

Хранение

MyISAM обычно хранит каждую таблицу в двух файлах — в файле данных и ин­дексном файле. Эти файлы имеют расширения .MYD и .MYI соответственно. Таблицы типа MyISAM могут содержать как динамические, так и статические строки (строки фиксированной длины). MySQL решает, какой формат использовать, основываясь на определении таблицы. Количество строк в таблице типа MyISAM ограничено в первую очередь доступным дисковым пространством на сервере базы данных и мак­симальным размером файла, допустимым в операционной системе.

Таблицы MyISAM со строками переменной длины, создаваемые в версии MySQL 5.0, по умолчанию настроены на поддержку 256 Тбайт данных с использованием шести­байтных указателей на записи с данными. В более ранних версиях MySQL указатели по умолчанию были четырехбайтными с максимальным объемом данных 4 Гбайт. Все версии MySQL могут поддерживать размер указателя до 8 байт. Чтобы изме­нить размер указателя в таблице MyISAM (уменьшить или увеличить), вы должны изменить таблицу и задать новые значения параметров MAX_ROWS и AVG_ROW_LENGTH , которые дают приблизительную оценку необходимого пространства. Это приведет к перезаписи таблицы и всех ее индексов, что может занять много времени.

Особенности MyISAM

Как одна из самых старых подсистем хранения MySQL, MyISAM может выполнять много функций, которые за годы использования СУБД были разработаны для решения различных задач.

• Блокирование и конкурентный доступ. MyISAM может блокировать только таблицы целиком, не построчно. Запросы на чтение получают разделяемые (на чтение) блокировки всех таблиц, которые им нужно прочитать. Запросы на за­пись получают монопольные (на запись) блокировки. Однако вы можете встав­лять новые строки в таблицу в то время, когда исполняются запросы на выборку данных из таблицы (конкурентные вставки).
• Исправление данных. MySQL поддерживает ручные и автоматические проверку и исправление таблиц типа MyISAM, но не стоит путать эти функции с транзак­циями или аварийным восстановлением. После исправления таблицы вы, скорее всего, обнаружите, что некоторые данные просто исчезли. К тому же исправление работает очень медленно. Вы можете применять команды CHECK TABLE mytable и REPAIR TABLE mytable для проверки таблицы на предмет наличия ошибок и их устранения. А также использовать командную утилиту myisamchk для проверки и исправления таблиц, когда сервер находится в автономном (offline) режиме.
• Особенности индексирования. Вы можете создавать индексы по первым 500 сим­волам столбцов типа BLOB и TEXT в таблицах MyISAM. MyISAM поддерживает полнотекстовые индексы, которые индексируют отдельные слова для сложных операций поиска.
• Отложенная запись ключей. Таблицы MyISAM, созданные с пометкой DELAY_KEY_ WRITE , не записывают измененные индексы на диск в конце запроса. Вместо этого MyISAM сохраняет изменения в буфере памяти для ключей. Сброс индексных блоков на диск происходит при переполнении буфера или закрытии таблицы. Это позволяет увеличить производительность, но в случае сбоя сервера или системы индексы наверняка будут повреждены и потребуют восстановления. Вы можете на­строить отложенную запись ключей как для всей базы, так и для отдельных таблиц.

Сжатые таблицы MyISAM

Некоторые таблицы после создания и заполнения данными никогда больше не из­меняются. Такие таблицы хорошо подходят для сжатия средствами MyISAM.

Для сжатия (или упаковки) таблиц существует специальная утилита myisampack. Сжатые таблицы невозможно изменить (хотя при необходимости можно распаковать их, внести изменения и снова сжать), но они занимают на диске гораздо меньше места, чем неупакованные. В результате их использования увеличивается произво­дительность, поскольку из-за небольшого размера таких таблиц требуется меньше операций поиска на диске для нахождения требуемых записей. Сжатые таблицы MyISAM могут иметь индексы, но они также доступны только для чтения.

Издержки на распаковку данных для чтения незначительны для большинства при­ложений, установленных на современном оборудовании, а значительная экономия достигается из-за сокращения операций ввода/вывода. Строки сжимаются по от­дельности, поэтому для получения одной строки MySQL не нужно распаковывать всю таблицу (или даже страницу).

Производительность MyISAM

Благодаря компактному хранению данных и невысоким издержкам, обусловленным простотой архитектуры, MyISAM в некоторых случаях может обеспечить хорошую производительность. У нее есть серьезные ограничения по масштабируемости, включая мьютексы, установленные на ключевых кэшах. MariaDB предлагает сег­ментированный кэш-ключ, который позволяет избежать этой проблемы. Однако наиболее распространенной проблемой MyISAM, мешающей добиться высокой про­изводительности, является блокировка таблиц. Если запросы застревают в статусе «Заблокировано», вы страдаете от блокировки на уровне таблицы.

Что такое движки баз данных MySQL?

Я огляделся и обнаружил, что некоторые из движков MySQL являются innodb и MyISAM. Возможно, их еще немного. Мой вопрос в том, что это за движки баз данных?

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

6 ответов:

Я лично всегда использую InnoDB, если мне нужно использовать MySQL. Он поддерживает транзакции и внешние ключи, а MyISAM-нет.

1. InnoDB

   • MySQL default Storage Engine (для v5.7) и самый универсальный механизм хранения.
   • транзакционно-безопасный (совместимый с кислотой) механизм хранения
   • обеспечивает фиксацию, откат и аварийное восстановление возможности
   • хранит кластеризованные индексы данных для уменьшения ввода / вывода
   • поддерживает ограничения внешнего ключа

2. MyISAM

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

4. CSV

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

5. архиве

   • столы компактны и неиндексированные
   • предназначен для хранения и извлечения исторических, архивных, или аудит данные

6. черная дыра

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

7. NDB

   • также известный как NDBCLUSTER
   • кластерный компонент database engine
   • полезно для приложений, которые требуют высокой надежности и высокой доступности.

8. слияние

   • полезно для логической группировки ряда одинаковых таблиц MyISAM
   • хорошо для сред VLDB, таких как хранилище данных.

9. федеративных

   • обеспечивает возможность свяжите отдельные серверы MySQL для создания одной логической базы данных из физических серверов
   • хорошо для распределенных или данных mart сред.

10. пример

    • «заглушка», которая ничего не делает
    • только таблицы могут быть созданы
    • никакие данные не могут быть сохранены в них или извлечены

• Понимание Механизмов Хранения MySQL

MyISAM и InnoDB являются наиболее часто используемыми двигателями.

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

но вы можете использовать лучшее из обоих и создать столы с различными механизмами хранения. Некоторые программы (WordPress, я думаю) используют Inno для большинства данных, таких как отношения между страницы, версии и т. д. Записи для записей содержат идентификатор, который ссылается на запись в отдельной таблице содержимого, использующей MyISAM. Таким образом, информация хранится в таблице, которая имеет лучшие возможности поиска, в то время как большинство других данных хранится в таблицах, которые обеспечивают целостность данных.

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

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

различные механизмы хранения доступны, есть несколько причин не использовать либо MyISAM или InnoDB типы двигателей. MyISAM будет делать в большинстве ситуаций, но если у вас есть большое количество обновлений или вставок по сравнению с вашими поисками и выбирает, то вы получите лучшую производительность из InnoDB engine. Чтобы получить максимальную производительность от InnoDB вам нужно настроить параметры для вашего сервера, в противном случае нет причин не использовать его.

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

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

для MySQL 5.5 и позже, механизм хранения по умолчанию InnoDB. Механизм хранения по умолчанию для MySQL до версии 5.5 был MyISAM. Выбор правильного механизма хранения является важным стратегическим решением, которое повлияет на будущее развитие. В в этом уроке мы будем использовать MyISAM, InnoDB, память и механизмы хранения CSV. Если вы новичок в MySQL и изучаете систему управления базами данных MySQL, то это не очень беспокоит. Если вы планируете производственную базу данных, то все становится сложнее.