[MySQL] storage engine comparison

[MySQL] The storage engine comparison table below shows the features of various storage engines: the two most common storage engines are MyISAM and InnoDB, which may be somewhat surprised when they are new to MySQL, there is a storage engine that does not support transactions. Anyone who has studied the relational database theory knows that transactions are the core of relational databases. However, in practical applications (especially the Internet), transactions can be discarded in some scenarios to improve performance. The following describes various storage engines: MyISAM storage engine MyISAM is the default storage engine officially provided by MySQL. It features no support for transactions, table locks, and full-text indexes. For some OLAP systems, fast operation. Each MyISAM is stored as three files on the disk. The file names are the same as the table names. The extensions are. frm (storage table definition),. MYD (MYData, storage data), and. MYI (MYIndex, storage index ). Note that MyISAM does not cache data files, but only indexes. The InnoDB Storage engine supports transactions and is mainly oriented to OLTP applications. It features row lock settings, supports foreign keys, and supports non-locked reads similar to Oracle, that is, no locks are generated for read by default. InnoDB stores data in a logical tablespace (similar to Oracle ). InnoDB achieves high concurrency through multi-version concurrency control and achieves four isolation levels of the ANSI standard. The default value is Repeatable. It uses a policy called next-key locking to avoid phantom read. InnoDB Stores Table data in a way similar to Oracle index organization table Clustered. The InnoDB Storage engine provides transaction security with commit, rollback, and crash recovery capabilities. However, compared with the storage engine of Myisam, InnoDB writes less efficiently and occupies more disk space to retain data and indexes. NDB storage engine NDB is a Cluster Storage engine similar to Oracle RAC, but it is a Share Nothing architecture, so it can provide higher availability and scalability. NDB stores all data in the memory, so it is very fast to search through the primary key. Note that the join Operation of NDB is completed at the MySQL database layer rather than at the storage engine layer, which means that, complex join operations require a huge amount of network overhead, resulting in slow query speeds. Memory (Heap) Storage Engine Memory storage engine (formerly known as Heap) Stores Table data in the Memory. If the database restarts or crashes, the data is lost, so it is very suitable for storing temporary data. As shown in its name, Archive is ideal for storing Archive data, such as log information. It only supports INSERT and SELECT operations, and is designed to provide high-speed INSERT and compression functions. Federated storage engine does not store data. It at least points to a table on a remote MySQL database server, which is very similar to the transparent gateway of Oracle. The Maria storage engine is a new engine designed to replace the original MyISAM storage engine and become the default storage engine for MySQL. At last, it should be emphasized that, although MySQL has many storage engines, There is no good or bad relationship between them. Instead, it selects a storage engine suitable for its own business based on different applications.