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

Как проверить тип системы хранения MySQL на Linux и Windows

Используются две основные системы хранения MySQL: MyISAM и Innodb. MyISAM является не транзакционной, и, следовательно, может быть быстрее для чтения, в то время как InnoDB полностью поддерживает транзакции (например, совершение/откат) с блокировкой отдельных элементов. Когда вы создаёте новую таблицу MySQL вы выбираете её тип (так называемую систему хранения, движок базы данных). Если вы не делаете выбор, то вы просто будете использовать движок, который в конфигурации указан как дефолтный.

Если вы хотите узнать тип существующей таблицы базы данных MySQL, есть несколько способов сделать это.

Метод первый

Если у вас есть доступ в phpMyAdmin, мы можете узнать тип баз данных из него. Просто выберите базу данных из phpMyAdmin, чтобы видеть список её таблиц. В колонке «Тип» вы увидите тип базы данных для каждой таблицы.

Метод второй

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

mysql> SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME = 'my_table';

Эта команда покажет тип системы хранения таблицы под названием ‘my_table’ в базе данных ‘my_database’.

‘my_database’ — название базы данных, в которой находится интересующая вас таблица

‘my_table’ — название интересующей вас таблицы

Метод третий

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

Эта команда отобразит информацию о конкретной базе данных. В колонке «Engine» вы увидите систему хранения для каждой таблицы.

$ mysqlshow -u -p -i

Узнать тип системы хранения MySQL на Windows

Все вышеописанные методы применимы и для Windows. Небольшая коррекция есть только для третьего способа. Файл имеет имя mysqlshow.exe и, скорее всего, придётся прописывать полный путь до него. Например, я вызываю его таким образом:

C:Serverbinmysql-5.6binmysqlshow.exe -u root -p -i db_wordpress

Tech blog by @dizballanze

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

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

SHOW ENGINES;

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

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. Сейчас мы проведем сравнительный тест производительности этих типов таблиц. Для этого сначала создадим две одинаковые по структуре таблицы, но с разным типом движка хранения:

CREATE TABLE `inno` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, `data` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; CREATE TABLE `myisam` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, `data` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM;

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

 // . class timer public $value=0; public function start() $this->value = microtime(true); > public function end() return microtime(true) - $this->value; > > $timer = new timer(); mysql_connect('localhost','root'); mysql_select_db('test'); $data = array(); $select_query = 'SELECT data FROM myisam WHERE >; for ($i = 0; $i  10000; $i++) $result = mysql_query(str_replace(':val:', $i, $select_query)); $tmp = mysql_fetch_array($result); $data[] = $tmp[0]; > echo '
MyISAM select
'; $select_query = 'SELECT * FROM myisam WHERE data = :val:'; $timer->start(); foreach ($data as $one) mysql_query(str_replace(':val:', $one, $select_query)); > echo $timer->end() . ' s
'; echo '
InnoDB select
'; $select_query = 'SELECT * FROM inno WHERE data = :val:'; $timer->start(); foreach ($data as $one) mysql_query(str_replace(':val:', $one, $select_query)); > echo $timer->end() . ' s
'; /* $data = array(); for ($i = 0; $i <= 10000; $i++) $data[] = mt_rand(0, 100500); > echo '
MyISAM select by key
'; $select_query = 'SELECT * FROM myisam WHERE >$timer->start(); for ($i = 0; $i <= 10000; $i++) mysql_query(str_replace(':val:', $i, $select_query)); > echo $timer->end() . ' s
'; echo '
InnoDB select by key
'; $select_query = 'SELECT * FROM inno WHERE >$timer->start(); for ($i = 0; $i <= 10000; $i++) mysql_query(str_replace(':val:', $i, $select_query)); > echo $timer->end() . ' s
'; */ /* $data = array(); for ($i = 0; $i <= 10000; $i++) $data[] = mt_rand(0, 100500); > echo '
MyISAM insert
'; $insert_query = 'INSERT INTO myisam VALUES (NULL,\':val:\')'; $timer->start(); foreach ($data as $one)  mysql_query(str_replace(':val:', $one, $insert_query)); > echo $timer->end() . ' s
'; echo 'InnoDB insert
'; $insert_query = 'INSERT INTO inno VALUES (NULL,\':val:\')'; $timer->start(); foreach ($data as $one)  mysql_query(str_replace(':val:', $one, $insert_query)); > echo $timer->end() . ' s
'; */

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

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

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

Краткий обзор движков таблиц MySQL

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

MyISAM

транзакций нет
макс. диск: 256Тб
блокировка таблица
полнотекстовый поиск
работа в кластере: нет
поддержание целостности, внешние ключи: нет
репликация: да
макс. индексов: 64
макс. записей: 2^32
макс. длина ключа: 1000 байт
ключи занимают место на диске до (макс.): (key_length+4)/0.67
чувствительные к «падению» сервера, сложно восстанавливать
при отсутствии «дырок» (gaps) — вставки не конкурентные (блокировок не происходит)
возможно хранить файлы данных и индексов на разных устройствах
каждый столбец может иметь свою кодировку
макс. сумма длин VARCHAR и CHAR: 64к

Static (Fixed-length) формат таблиц

автоматически, если нет VARCHAR, VARBINARY, BLOB, TEXT столбцов
быстрее, безопаснее (устойчивее), лучше кешируется, требует больше места на диске
если указать принудительно, VARCHAR и CHAR заполняются пробелами, VARBINARY — нулями

Dynamic length формат таблиц

все строки длиной до 4 — VARCHAR
пустые строки и ноль (0) не занимают места на диске (NULL это не ноль)
запись (строка) фрагментируется автоматически при апдейтах (нужно запускать OPTIMIZE TABLE для дефрагментации)
сложнее восстановить при сбоях

Compressed

создается утилитой myisampack
read-only
рекомендуется для очень медленных носителей
может быть и fixed-length и dynamic-length
посмотрите в сторону Archive table engine

говорят, что MyISAM таблицы обязательно «ломаются» рано или поздно, так что будте готовы 😉
не убивайте сервер во время записи
не изменяйте таблицы несколькими серверами одновременно
не изменяйте таблицы утилитой и сервером одновременно

InnoDB

макс. диск: 64Тб
полная поддержка транзакций (4 уровня изоляции)
блокировка записи (не таблицы), два вида блокировок (SHARED, EXCLUSIVE)
полнотекстовый индекс: нет
безопасная для транзакций
индексы кластеризуются для «типичных запросов»
поддержка целостности (внешние ключи)
может использоваться на ОС с ограниченным размером файла
множество настроек для оптимизации
позволяет использовать Raw Disk для таблиц в обход ФС
по умолчанию включен AUTOCOMMIT (SET autocommit=1)
автоматически детектит дэдлоки (deadlocks)

SELECT (*) FROM table работает гораздо медленнее, чем MyISAM — создавайте триггеры если нужно
бэкап простым копирование файлов невозможен
mysqldump работает медленно, для бэкапа используйте InnodDb Hot Backup
следите за индексами, выгода InnoDB теряется, если для запросов нет индексов

MERGE

Используется для объединения одинаковых таблиц в одну
таблицы должны иметь идентичную структуру
порядок столбцов должен совпадать
DROP не удаляет исходных таблиц
таблицы могут быть в другой базе данных
можно использовать для алиасов (для одной таблицы)
нельзя пользоваться FULLTEXT search
нельзя смешивать временные и не временные таблицы
медленная при чтении по ключу
REPLACE не работает
не отслеживаются изменения в структуре исходных таблиц (таблица будет поломана)

HEAP (MEMORY)

транзакций нет
блокировка таблицы
репликация: да
макс. длина ключа: 500 байт
все данные теряются при остановке сервера (сама таблица остаётся)
формат хранения: всегда fixed-length row
память не высвобождается при удалении записи (используется для вставки новых)

большие таблицы «свопяться» на диск и выигрыш теряется

Рекомендации: Локальные вычисления, временные данные

CSV

хранит таблицы в CSV формате
позволяет редактировать таблицы внешними приложениями
плохо документирован, есть открытые баги

FEDERATED

Представляет собой «прозрачное» подключение к другому серверу (не репликация). Есть множество ограничений, планируется возможность подключения к отличным от MySQL серверам.

BLACKHOLE

данные идут «вникуда»
двоичные логи пишуться

Тренды, MySQL 6.0

Maria — «улучшенный MyISAM»
Falcon — «улучшенный InnoDb», улучшенная производительность, для Web серверов

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

В MySQL движки таблиц, которые поддерживают транзакции, называются «транзакционные движки». Один из таких движков — InnoDB, который является самым популярным и рекомендуемым движком для большинства приложений. Чтобы использовать транзакции с таблицами InnoDB, необходимо сначала убедиться, что движок InnoDB правильно настроен в MySQL. Затем можно использовать следующие SQL-запросы для работы с транзакциями:

BEGIN; -- начало транзакции -- выполняйте свои запросы здесь COMMIT; -- фиксация (завершение) транзакции

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

ROLLBACK; -- откат изменений и завершение транзакции

Вот как можно использовать транзакции с таблицами InnoDB в MySQL.

Детальный ответ

Какой движок таблиц в MySQL поддерживает транзакции? В MySQL существуют несколько различных движков таблиц, которые предоставляют различные возможности и функциональности для хранения и обработки данных. Какой движок выбрать зависит от конкретных требований вашего проекта. Однако, если вы нуждаетесь в поддержке транзакций, вам рекомендуется использовать движок InnoDB. InnoDB: InnoDB является одним из наиболее распространенных движков таблиц в MySQL, особенно среди профессиональных разработчиков. Он поддерживает транзакции, что позволяет сгруппировать несколько операций в одну единицу работы. Если в ходе выполнения транзакции происходит ошибка, можно откатить все изменения, чтобы база данных оставалась в консистентном состоянии. Транзакции полезны, когда необходимо гарантировать целостность данных и избежать некорректных результатов при параллельном доступе к базе данных. Для создания таблицы с движком InnoDB вам нужно явно указать это при создании таблицы:

 CREATE TABLE my_table ( id INT PRIMARY KEY, name VARCHAR(50) ) ENGINE = InnoDB;

MyISAM: не поддерживает транзакции. Он является очень быстрым и хорошим выбором для приложений, где производительность и скорость чтения являются приоритетом.
Memory (HEAP): таблицы, хранящиеся в оперативной памяти. Не поддерживает транзакции.
CSV: хранит данные в формате CSV (Comma-Separated Values). Не поддерживает транзакции.

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

 SHOW CREATE TABLE my_table;

В выводе будут отображены сведения о текущем движке таблицы.

Вывод:

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