Various engines in MySQL

Various engines in MySQL
The storage engine in the database is actually used to set the tables that use the engine. What storage engine is set for the tables in the database, this table has different effects in terms of data storage, data update, data query performance, and whether indexes are supported ". There are multiple engines in the MySQL database (different versions of MySQL databases support different engines). Only by familiarizing yourself with various engines can you develop application engines in software development to develop high-performance software, what are the engines in the MySQL database? MySQL has the following engines: ISAM, MyISAM, HEAP (also known as MEMORY), CSV, BLACKHOLE, ARCHIVE, cece_schema, InnoDB, Berkeley, Merge, Federated, and Cluster/NDB, you can also create your own database engine by referring to MySQL ++ API. The following describes various engines one by one:
ISAM
The engine is fast in reading data and does not occupy a large amount of memory and storage resources. However, ISAM does not support transaction processing, foreign keys, fault tolerance, or indexing. This engine is no longer supported in databases including MySQL 5.1 and later versions.
MyISAM
This engine is based on the ISAM database engine. In addition to providing a large number of functions such as index and field management not available in ISAM, MyISAM also uses a table lock mechanism to optimize multiple concurrent read/write operations, however, you need to run the optimize table command frequently to restore the space wasted by the update mechanism. Otherwise, fragments will increase, which will ultimately affect data access performance. MyISAM also has some useful extensions, such as the MyISAMChk tool used to fix database files and the MyISAMPack tool used to restore wasted space. MyISAM emphasizes fast read operations and is mainly used for high-load select operations. This may also be the main reason for MySQL's Deep Web development: a large number of data operations in Web development are read operations, therefore, most VM providers and Internet platform providers (Internet Presence Provider, IPP) only allow the use of MyISAM format.
MyISAM tables support three different storage structures: static, dynamic, and compressed.
Static type: the size of the defined table column is fixed (that is, it does not include xblob, xtext, varchar, and other variable-length data types), so MySQL will automatically use the static MyISAM format. The performance of tables in static format is relatively high, because the overhead for maintaining and accessing data in a predefined format is very low. However, this high performance is at the cost of space, because it is fixed during definition, no matter how large the value in the column is, the maximum value prevails, occupying the entire space.
Dynamic type: If a column (even if only one column) is defined as dynamic (xblob, xtext, varchar, and other data types), MyISAM automatically uses the dynamic type, although a dynamic table occupies less space than a static table, it reduces the performance because if the content of a field changes, the position of the dynamic table may need to be moved, this will lead to the generation of fragments. As the data changes, the fragmentation increases, and the data access performance decreases.
There are two solutions to the problem of reducing data access due to increased fragmentation:
A. Try to use static data types;
B. The optimize table table_name statement is often used to sort out table fragments and restore Space Loss Caused by table data update and deletion. If the storage engine does not support optimize table table_name, you can dump and reload data, which can also reduce fragmentation;
Compressed type: If you create a read-only table in the database throughout the lifecycle, you should use the compressed MyISAM table to reduce space usage.
HEAP (also known as MEMORY)
The storage engine creates a temporary table in the memory to store data. Each table based on the storage engine actually corresponds to a disk file. The file name and table name are the same, and the type is. frm. This disk file only stores the table structure, and its data is stored in the memory. Therefore, tables using this engine have extremely high insertion, update, and query efficiency. By default, this storage engine uses HASH indexes, which are faster than B-+ Tree indexes, but B-Tree indexes can also be used. Because the data stored by this storage engine is stored in the memory, the stored data is unstable. For example, if the mysqld process is abnormal, restarted, or the computer is shut down, the data will disappear, therefore, the table lifecycle in this storage engine is very short and generally only used once.
CSV (Comma-Separated Values)
The MySQL database table using this engine will generate one in the directory with the same database name as the data folder in the MySQL installation directory. CSV files (which can process CSV files as tables) are common text files, and each data row occupies one line of text. This type of storage engine does not support indexes, that is, tables of this type do not have primary key columns, and fields in the table are not allowed to be null.
BLACKHOLE)
The storage engine supports transactions and mvcc row-level locks. Any data written into this engine table disappears and is mainly used for relay storage of log records or synchronous archiving, this storage engine is not suitable for use unless it has a special purpose. For more information, see the blog "BlackHole storage engine".
ARCHIVE
This storage engine is ideal for storing a large number of independent historical data. Unlike the InnoDB and MyISAM engines, ARCHIVE provides compression and efficient insertion speed. However, this engine does not support indexing, so the query performance is poor.
PERFORMANCE_SCHEMA
This engine is mainly used to collect database server performance parameters. This engine provides the following functions: it provides detailed information about waiting for a process, including lock, mutex variables, and file information. It stores historical event summary information to provide detailed judgment on MySQL server performance; it is easy to add or delete monitoring event points, and you can change the monitoring CYCLE of the mysql server at will, such as CYCLE and MICROSECOND ).
InnoDB
The storage engine provides ACID transaction support, system crash repair capabilities, and Multi-Version Concurrency Control (MVCC Multi-Version Concurrency Control) Row-level locks for MySQL tables; the engine supports auto-increment columns (auto_increment). The value of the auto-increment Column cannot be empty. If it is empty, the value is automatically increased from the existing value. If it is larger than the current value, this value is saved directly. The engine storage engine supports the foreign key (foreign key). The table where the foreign key is located is called a sub-table and the table on which it depends is called a parent table. This engine is the default storage engine in the MySQL database after 5.5.
Berkeley (BDB)
The storage engine supports other transaction features such as COMMIT and ROLLBACK. This engine is no longer supported in databases including MySQL 5.1 and later versions.
Merge
This engine combines a certain number of MyISAM tables into a whole. For more information, see MySQL Merge storage engine.
Federated
The storage engine can be combined with different Mysql servers to form a complete database logically. This storage engine is very suitable for distributed database applications.
Cluster/NDB
This storage engine is used by multiple data machines to provide services to improve overall performance and security. Suitable for scenarios with high data volume, security, and performance requirements.
The above is a summary of the storage engine in the MySQL database. It only highlights the features of different storage engines. If you are not correct, I am very grateful.