Various engines in MySQL

The storage engine in the database is actually a set of tables that use the engine, and the table in the database sets what storage engine, so the table has different "effects" in terms of how the data is stored, how the data is updated, the performance of the data query, and whether it supports indexing. There are multiple engines in the MySQL database (different versions of the MySQL database support the engine), familiar with the various engines in software development to apply the engine, so as to develop high-performance software, MySQL database of the engine? In general, MySQL has the following engines: ISAM, MyISAM, HEAP (also known as memory), CSV, Blackhole, ARCHIVE, Performance_schema, InnoDB, Berkeley, Merge, Federated and Cluster/ndb, we can also create our own database engine with reference to the mysql++ API. Here's a look at the various engines:
ISAM
The engine is fast in reading data and does not consume large amounts of memory and storage resources, but ISAM does not support transactional processing, foreign keys are not supported, fault tolerance is not supported, and indexes are not available. The engine is no longer supported in databases that include MySQL 5.1 and later.
MyISAM
The engine is based on the ISAM database engine, in addition to providing a number of functions such as index and field management not available in ISAM, MyISAM also uses a table-locking mechanism to optimize multiple concurrent read and write operations, but it is necessary to run the Optimize Table command frequently to restore space wasted by the updated mechanism , the fragmentation will also increase, ultimately affecting data access performance. MyISAM also has some useful extensions, such as the Myisamchk tool for repairing database files and the Myisampack tool for recovering wasted space. MyISAM emphasizes fast read operations, primarily for high-load SELECT, which may also be the main reason for Web development by MySQL: A large number of data operations in Web development are read operations, Therefore, most virtual hosting providers and Internet Platform providers (Internet presence Provider,ipp) only allow the use of the MyISAM format.
MyISAM types of tables support three different storage structures: static, dynamic, and compact.
Static type: Refers to the defined table column size is fixed (that is, does not contain: Xblob, xtext, varchar and other variable length data types), so that MySQL will automatically use the static MyISAM format. Tables that use a static format are relatively performant because the overhead of maintaining and accessing data in a predetermined format is low, but this high performance is at the cost of space, because it is fixed at the time of definition, so no matter how large the value in the column is, it takes up the entire space, whichever is the maximum value.
Dynamic: If the column (even if only one column) is defined as dynamic (Xblob, xtext, varchar, and other data types), then MyISAM automatically uses the dynamic type, although the dynamic table consumes less space than the static type table, but it brings the performance decrease. Because if the content of a field changes, its position is likely to need to move, which can lead to fragmentation, and as the data changes, the fragmentation increases and data access performance decreases.
There are two solutions to the problem of reducing data access due to the increase of fragmentation:
A. Use static data types whenever possible;
B. Frequently use the OPTIMIZE table TABLE_NAME statement to defragment the table to recover space lost due to update and deletion of table data. If the storage engine does not support optimize table table_name, you can dump and reload the data, which can also reduce fragmentation;
Compression type: If you create a read-only table in the database for the entire life cycle, you should use a compressed table of MyISAM to reduce space usage.
HEAP (also known as memory)
The storage engine stores data by creating temporary tables in memory. Each table that is based on the storage engine actually corresponds to a disk file that has the same file name and table name, and the type is. frm. The disk file stores only the structure of the table, and its data is stored in memory, so tables with that engine have very high efficiency in insert, UPDATE, and query. The storage engine uses a hash index by default, which is faster than using the B-+tree type, but can also use a B-tree index. Because the storage engine stores data stored in memory, the data it holds is unstable, such as if the mysqld process is abnormal, restarted, or the computer shuts down, and so on, so the life cycle of the table in this storage engine is very short and is typically used only once.
CSV (comma-separated values comma separated value)
The MySQL database table using the engine generates a. csv file in the MySQL installation directory data folder, in the same directory as the database name in which the table resides (so it can process files of the CSV type as a table), which is a plain text file that occupies one line of text per row of data. This type of storage engine does not support indexing, that is, a table with that type does not have a primary key column, and the fields in the table are not allowed to be null.
Blackhole (black hole engine)
The storage engine supports transactions, and MVCC row-level locks are supported, and any data written to this engine table disappears, primarily for logging or synchronous archiving of trunk storage, a storage engine that is not intended for use unless it has a special purpose. See blog "Blackhole Storage Engine"
ARCHIVE
The storage engine is ideal for storing a large number of independent, historical data. Unlike the two engines, InnoDB and MyISAM, Archive provides compression with efficient insertion speed, but the engine does not support indexing, so query performance is poor.
Performance_schema
The engine is primarily used to collect database server performance parameters. This engine provides the following features: Provides details of the process wait, including locks, mutexes, file information, historical event summary information, to provide a detailed assessment of MySQL server performance, and to add and delete monitoring incidents are very easy, and can arbitrarily change the MySQL server monitoring cycle, For example (CYCLE, microsecond).
InnoDB
The storage engine provides a row-level lock for the MySQL table with ACID transaction support, system crash repair capability, and multiple versions of concurrency control (that is, MVCC multi-version Concurrency control), which supports the self-growing column (auto_increment), The value of the self-growth column cannot be empty, and if it is empty at the time of use, it will automatically increment from the existing value, and if it is larger than the current one, save the value directly; The engine storage engine supports foreign keys (foreign key), and the table on which the foreign key resides is called a child table and depends on the table called the parent table. The engine is the default storage engine in the MySQL database after 5.5.
Berkeley (BDB)
The storage engine supports other transactional features such as commit and rollback. The engine is no longer supported in databases that include MySQL 5.1 and later.
Merge
The engine unites a certain number of MyISAM tables into a single whole. See the blogMySQL Merge Storage engine》
Federated
The storage engine can be combined with different MySQL servers to logically compose a complete database. This storage engine is ideal for database distributed applications.
Cluster/ndb
The storage engine is used in conjunction with multiple data machines to provide services to improve overall performance and security. Ideal for scenarios with high data volumes, security, and performance requirements.
The above is a summary of the storage engine in MySQL database, but the focus summarizes a variety of different storage engine features, the wrong place also hope you correct, greatly appreciated.

Various engines in MySQL