Before MySQL5.5, the default engine was "MyISAM"; starting from MySQL5.5, the default engine is "InnoDB". This engine fully supports ACID compliance and transactions, and supports foreign keys, commit, rollback, For rollforward operations, the table size can be up to 64TB. In MySQL, you can use the "SHOW ENGINES;" command to view the engine types and default engines supported by the system; in the output results, the engine identified by the DEFAULT keyword is the current default storage engine.
The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.
The database storage engine is the underlying software component of the database. The database management system uses the data engine to create, query, update and delete data operations. In short, the storage engine refers to the type of table.
The storage engine of the database determines how the table is stored in the computer. Different storage engines provide different storage mechanisms, indexing techniques, locking levels and other functions. Using different storage engines, you can also obtain specific functions.
MySQL provides various storage engines for its tables, such as InnoDB, MyISAM, Memory, Merge, Archive, CSV, BLACKHOLE, etc.
Prior to MySQL 5.5, MyISAM was the default storage engine when you created a table without explicitly specifying a storage engine. Starting with version 5.5, MySQL uses InnoDB as the default storage engine.
You can use theSHOW ENGINES;statement to view the engine types supported by the system. The result is as shown in the figure.
The value of the Support column indicates whether a certain engine can be used. YES means it can be used, NO means it cannot be used, and DEFAULT means that the engine is the current default storage engine.
It can be seen that the current default storage engine is InnoDB.
MyISAM Engine
MyISAM extends the previous ISAM storage engine. MyISAM tables are optimized for compression and speed. MyISAM tables are also portable between platforms and operating systems.
The size of MyISAM tables can reach 256TB, which is huge. Additionally, MyISAM tables can be compressed into read-only tables to save space. On startup, MySQL checks MyISAM tables for corruption and even repairs them if errors occur. MyISAM tables are not transaction safe.
InnoDB Engine
InnoDB tables fully support ACID compliance and transactions. They are also the best choice for performance. InnoDB tables support foreign key, commit, rollback, and rollforward operations. InnoDB tables can be up to 64TB in size.
Like MyISAM, InnoDB tables are portable between different platforms and operating systems. If necessary, MySQL also checks and repairs InnoDB tables on startup.
MERGE Engine
The MERGE table is a virtual table that combines multiple MyISAM tables that have features similar to one table Structure. The MERGE storage engine is also called the MRG_MyISAM engine. The MERGE table does not have its own index; it uses the index of the component table.
Using MERGE tables can speed up performance when joining multiple tables. MySQL only allows you to perform SELECT, DELETE, UPDATE and INSERT operations on MERGE tables. If DROP TABLE uses the MERGE statement on the MERGE table, only the specification is deleted. The underlying tables are not affected.
Memory Engine
Memory tables are stored in memory and use hash indexes, so they are faster than MyISAM tables. The life cycle of in-memory table data depends on the uptime of the database server. The in-memory storage engine was formerly known as HEAP.
Archive Engine
The Archive Storage Engine allows you to store large numbers of records (for archiving) in a compressed format to save disk space. The archive storage engine compresses records when inserting and uses the zlib library to decompress them when reading.
Archive tables only allow INSERT and SELECT statements. ARCHIVE tables do not support indexes, so a full table scan is required to read rows from the table.
CSV
The CSV storage engine stores data in the Comma Separated Values (CSV) file format. CSV tables provide a convenient way to migrate data to non-SQL applications such as spreadsheet software.
CSV tables do not support the NULL data type. Additionally, read operations require a full table scan.
FEDERATED
FEDERATED storage engine allows you to manage data from remote MySQL servers without using clustering or replication technology. Local union tables do not store any data. When you query data from a local federated table, the data is automatically extracted from the remote federated table.
[Related recommendations:mysql video tutorial]
The above is the detailed content of What is the default engine of mysql. For more information, please follow other related articles on the PHP Chinese website!
