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

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-приложениях.

What are the different MySQL database engines ?

Database engines are MySQL components that can handle SQL operations like create, read, update data from a database. There are two types of engines in MySQL: transactional and non-transactional.InnoDB is the default engine for MySQL 5.5 and above versions. Major DBMS uses an application programming interface(API) to enable the interaction of users with database engines. It is very necessary to know about the engines for production databases and it also impacts future development. To access the list of available MySQL engines we run SHOW ENGINES; query.

There are 2 types of database engines :

• Transactional Databases: In this type, we can roll back the write operations on the database if they are left incomplete. These operations are known as transactions. Majorly, modern engines are transactional.
• Non-Transactional Databases: Unlike transactional databases, they don’t provide Rollback/Commit. Instead, we need to manually code to perform the rollback operations.

The below image illustrates the different available engines in MySQL:

List of database engines in MySQL

In this article, we will learn about InnoDB, MyISAM, MEMORY, MERGE, CSV, and SEQUENCE engines, their features, along with knowing their advantages & disadvantages.

InnoDB: InnoDB is a storage engine for DBMS MySQL and MariaDB.It is the default storage engine for MySQL versions 5.5 and higher. It replaced MyISAM as the default engine.

Features:

• It provides standard ACID-compliant transaction features, along with foreign key support. Oracle recommends InnoDB for tables.
• It supports multi-version concurrency control, crash-recovery, and rollback operations. Multi-user performance is possible due to row-level locking.
• It is used in the MariaDB server too. To maintain data integrity and security, InnoDB supports foreign key constraints.
• InnoDB supports buffers that cache data as well as indexes.
• It offers a table locking method, which means only one user can alter the table at a time.

Advantages:

• Supports ACID (Atomicity, Consistency, Isolation, and Durability) properties for securing user’s data.
• Access to multiple users with high performance and consistent reads in Oracle-style.
• Supports usage of foreign keys that help in the consistent update, insert and delete operations. Also, it helps in maintaining integrity in the contents of the tables of the database.

Disadvantages:

• Does not support full-text search.
• If performance is a priority then it is not used because it is slower than MyISAM.
• Usage of foreign key relationships makes it complex to use.

MyISAM: It is the default engine for MySQL DBMS versions prior to 5.5.MyISAM is a high-speed storage and retrieval storage engine. It doesn’t support transactions. It is easy to copy between systems and has a small data footprint. Mostly used in Web and Data Warehousing.

Features:

• MyISAM is stored in 3 files: .frm – Stores table format, .MYD – Data files, .MYI – index file.
• It supports three storage formats: Fixed, Dynamic, and Compressed.
• The maximum key length is 1000 bytes.
• The maximum number of indexes per table and columns per index are 64 and 16 respectively.

Advantages:

• MyISAM is simpler than other engines.
• It is faster than any other general-purpose database engine. It also provides a full-text searching

Disadvantages:

• It can easily lead to corruption of the table. However, we can use the REPAIR TABLE query to recover it.
• It does not support foreign key constraints or transactions.

CSV: It is used to store CSV format in a text file. It is always compiled into the MySQL server. It is lightweight and can be easily imported into spreadsheet programs.

Features:

• CSV tables use CSV format.Thus, it can be used for data exchange for eg. using the spreadsheet.
• Editing data can be performed even if the MySQL server is down using standard file editors
• Instantaneous loading of massive data in the MySQL server.

Advantages:

• Metafile is created that stores the state of the table and the number of rows that exist in the table after creating the CSV file.
• Data is stored in a text file using the common-separated value format.

Disadvantages:

• CSV engine does not support indexing.
• It does not support partitioning.
• It must have NOT NULL attributes on all columns.

MERGE: Tables created using the MERGE engine are used to handle a large volume of data easily. It is a collection of identical MyISAM tables that can be used as one. Thus, it is also known as the MRG_MyISAM engine. It is good for data warehousing environments.

Features:

• It was introduced in MySQL 3.23.25 version.
• UNION and INSERT_METHOD are two unique features of MERGE tables.
• MERGE tables do not have PRIMARY KEY or UNIQUE indexes as they cannot enforce uniqueness over all tables.

Advantages:

• It is a collection of identical MyISAM tables that can be used as one table. Memory is saved due to it.
• It can perform the most efficient repair of individual tables rather than repairing a single large table which is made by merging these individual tables.
• Merge tables are not limited to the file size of the operating system, unlike MyISAM tables.

Disadvantages:

• Only identical MyISAM tables can be used to merge.
• If the MERGE table is nontemporary, it is necessary for merge tables to be nontemporary. If the MERGE table is temporary, the MyISAM tables can be a mixture of temporary and nontemporary.
• Some MyISAM features are not available in MERGE tables.

MEMORY: It is considered to be the fastest engine used to usually create temporary tables in memory (also known as HEAP). Thus data is lost when the database is restarted. It is non-transactional. It is useful for quick looks up of references and other identifying data. It stores all data in RAM for faster access rather than storing data in disks. It is widely used for read-only caches of data from tables or for temporary usage.

Features:

• It does not create any files on disk.
• MEMORY tables cannot contain BLOB or TEXT columns.
• AUTO_INCREMENT columns are supported.
• VARCHAR is stored using fixed-length i.e. it uses fixed-length row-storage format.

Advantages:

• Data is encrypted which is implemented in the server via encryption functions.
• Supports B-treeindexes.
• Offers low-level locking and multiple-thread operation for low contention between clients.

Disadvantages:

• MEMORY tables cannot be partitioned.
• Memory can’t be reclaimed if you delete individual rows from a MEMORY table

SEQUENCE: It is the transactional engine used for creating ascending or descending sequences of positive integers with given initial, end, and increment values. It is read-only and supports XA.

alt=»Блог Михаила Григорьева» />

28 Ноя 2017 20:11:17 | 0 comments

Сравнение движков InnoDB и MyISAM

MySQL поддерживает два самых популярных движка InnoDB и MyISAM, но в чем же их отличия?

Давайте посмотрим более подробно и более понятно.

В таблице ниже я попробовал показать разницу между MyISAM и InnoDB собрав данные с официальных и неофициальных источников вместе:

Описание	MyISAM	InnoDB
Транзакции	Нет	Да
Внешние ключи	Нет	Да
Блокировки	На уровне таблиц	На уровне строк
Одновременные запросы к разным частям таблицы	Медленнее	Быстрее
При смешанной нагрузке в таблице (SELECT/UPDATE/DELETE/INSERT)	Медленнее	Быстрее
Операция INSERT	Быстрее	Медленнее
Если преобладают операции чтения (SELECT)	Работает быстрее	Работает медленнее
Deadlock	Не возникают	Возможны
Полнотекстовый поиск	Да	Нет (Доступен начиная с MySQL 5.6.4)
Запрос вида SELECT count(*)	Быстрее	Медленнее
Файловое хранение таблиц	Каждая таблица в отдельном файле	По умолчанию данные хранятся в больших совместно используемых файлах, но возможно хранение каждой таблицы в отдельном файле
Размер занимаемого места на диске	Меньше	Больше (примерно в 1,5 раза)
Поведение в случае сбоя	Ломается вся таблица	Можно восстановить по логам транзакций

Более детальное описание терминов:
Транзакция (Transaction) – блок операторов SQL, который в случае ошибки в одном запросе, возвращается к предыдущему состоянию (Rollback), и только в случае выполнения всех запросов подтверждается (Commit);
Внешние ключи – это способ связать записи в двух таблицах по определенным полям так, что при обновлении поля в родительской автоматически происходит определенное изменение поля в дочерней (создается ключ в дочерней таблице, который ссылается на родительскую);
Блокировка на уровне строк — в ситуации когда процессу нужно обновить строку в таблице, то он блокирует только эту строку, позволяя другим обновлять другие строки параллельно;
Deadlock — ситуация в многозадачной среде или СУБД, при которой несколько процессов находятся в состоянии бесконечного ожидания ресурсов, захваченных самими этими процессами;

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

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

MySQL storage engines

In this chapter, we will talk about MySQL storage engines.

A storage engine is a software module that a database management system uses to create, read, update data from a database. There are two types of storage engines in MySQL: transactional and non-transactional.

For MySQL 5.5 and later, the default storage engine is InnoDB. The default storage engine for MySQL prior to version 5.5 was MyISAM. Choosing the right storage engine is an important strategic decision, which will impact future development. In this tutorial, we will be using MyISAM, InnoDB, Memory and CSV storage engines. If you are new to MySQL and your are studying the MySQL database management system, then this is not much of a concern. If you are planning a production database, then things become more complicated.

List of storage engines

• InnoDB
• MyISAM
• Memory
• CSV
• Merge
• Archive
• Federated
• Blackhole
• Example

InnoDB is the most widely used storage engine with transaction support. It is an ACID compliant storage engine. It supports row-level locking, crash recovery and multi-version concurrency control. It is the only engine which provides foreign key referential integrity constraint. Oracle recommends using InnoDB for tables except for specialized use cases.

MyISAM is the original storage engine. It is a fast storage engine. It does not support transactions. MyISAM provides table-level locking. It is used mostly in Web and data warehousing.

Memory storage engine creates tables in memory. It is the fastest engine. It provides table-level locking. It does not support transactions. Memory storage engine is ideal for creating temporary tables or quick lookups. The data is lost when the database is restarted.

CSV stores data in CSV files. It provides great flexibility because data in this format is easily integrated into other applications.

Merge operates on underlying MyISAM tables. Merge tables help manage large volumes of data more easily. It logically groups a series of identical MyISAM tables, and references them as one object. Good for data warehousing environments.

Archive storage engine is optimised for high speed inserting. It compresses data as it is inserted. It does not support transactions. It is ideal for storing and retrieving large amounts of seldom referenced historical, archived data.

The Blackhole storage engine accepts but does not store data. Retrievals always return an empty set. The functionality can be used in distributed database design where data is automatically replicated, but not stored locally. This storage engine can be used to perform performance tests or other testing.

Federated storage engine offers the ability to separate MySQL servers to create one logical database from many physical servers. Queries on the local server are automatically executed on the remote (federated) tables. No data is stored on the local tables. It is good for distributed environments.

The SHOW ENGINES command shows all available engines that the server supports.

Choosing the right engine

No storage engine is ideal for all circumstances. Some perform best under certain conditions and perform worse in other situations. There are tradeoffs than must be considered. A more secure solution takes more resources; it might be slower, take more CPU time, and disk space. MySQL is very flexible in the fact that it provides several different storage engines. Some of them, like the Archive engine, are created to be used in specific situations.

In some cases the answer is clear. Whenever we are dealing with some payment systems, we are obliged to use the most secure solution. We cannot afford to loose such sensitive data. InnoDB is the way to go. If we want full-text search, then we can choose either MyISAM or InnoDB.. Only InnoDB supports foreign key referential integrity constraint and if we plan to use this constraint, then the choice is clear.

Specifying and altering storage engines

The storage engine is specified at the time of the table creation.

The ENGINE keyword specifies the storage engine used for this particular table.

If we do not specify the storage engine explicitly, then the default storage engine is used. Prior to MySQL 5.5 the default storage engine was MyISAM. For MySQL 5.5 and later, the default storage engine is InnoDB.

It is possible to migrate to a different storage engine. Note that migrating a large table might take a long time. Also we might run into some problems when migrating tables. Some features might not be supported in both tables.

This SQL statement finds out the storage engine used for a Cars table in mydb database. We could also use SELECT CREATE TABLE Cars SQL statement. The information_schema is a table which stores technical information about our tables.

This SQL statement changes the storage engine of the Cars table to MyISAM.