[MySQL] MySQL storage engine

When creating a table, you can specify the type of the table, which is the storage engine of the table. The storage engine of a table determines how data is stored and accessed, as well as how transactions are stored. The storage engine of a table greatly affects the storage space and speed required to process SQL statements. Different storage engines have different characteristics. Some storage engines are very suitable for processing many complex SELECT statements, while others are more suitable for achieving fast updates.

InnoDB

InnoDB is MySQL’s default transactional engine and the most important and widely used storage engine. It is designed to handle a large number of short-lived transactions. Short-lived transactions are submitted normally in most cases and are rarely rolled back. InnoDB's performance and automatic crash recovery features make it popular for non-transactional storage needs.

Unless there are very special reasons to use other storage engines, the InnoDB engine should be given priority. ————"High-Performance MySQL"

• InnoDB uses MVCC to support high concurrency and implements four standard isolation levels. Its default level is REPEATABLE READ, and the gap lock strategy prevents phantom reads.

• InnoDB indicates that

• built based on a clustered index supports foreign key constraints.

• Supports automatic addition of column AUTO_INCREMENT attribute.

• Affairs. The InnoDB storage engine is a standard MySQL storage engine that supports transactions.

• There is no need to copy the entire table data when deleting or adding an index.

InnoDB has made many internal optimizations, including predictable read-ahead when reading data from disk, and an adaptive hash index that can automatically create a hash index in memory to accelerate operations. , and an insert buffer that speeds up insert operations.

InnoDB tables are built based on clustered indexes. The index structure of InnoDB is very different from other MySQL engines. Clustered indexes have high performance for primary key queries. However, the secondary index must contain the primary key column, so if the primary key column is large, all other indexes will be large. Therefore, if there are many indexes on the table, the primary key should be as small as possible.

MyISAM

MyISAM provides a large number of features, including full-text indexing, compression, spatial functions (GIS), etc., but MyISAM does not support transactions and row-level locks, and there is no doubt that The disadvantage is that it cannot be safely restored after a crash. In MySQL 5.1 and previous versions, MyISAM is the default storage engine. It is precisely because of this engine that even though MySQL has supported transactions for a long time, many people still think that MySQL is a non-transactional database.

• MyISAM locks the entire table, not the rows.

• Supports full-text indexing.

• Supports compressed tables. Compressed tables cannot be modified and can greatly reduce disk space usage, thus reducing disk I/O operations and thereby improving query performance.

Storage

MyISAM will store the table in two files: the data file and the index file, with .MYD and .MYI extensions respectively. MyISAM tables can contain dynamic or static rows. MySQL will decide which row format to use based on the table definition.
In MySQL5.0, if the MyISAM table has variable-length rows, the default configuration can only handle 256TB of data

Features

As one of the earliest storage engines of MySQL, there are still some features.

Locking and Concurrency – Table Lock

MyISAM locks the entire table, not the rows. When reading, shared locks will be added to all tables that need to be read, and exclusive locks will be added to the tables when writing. However, while the table has read queries, new records can also be inserted into the table (concurrent insertion).

Repair

For MyISAM tables, MySQL can perform inspection and maintenance operations manually or automatically, but the repair mentioned here is a different concept from transaction recovery and crash recovery.

Index Features

For MyISAM tables, even long fields such as BLOB and TEXT can create indexes based on their first 500 characters. MyISAM also supports full-text indexing, which is an index created based on word segmentation and can support complex queries.
Delayed update of index keys
When creating a MyISAM table, if the DELAY_KEY_WRITE option is specified, the modified index data will not be written to the disk immediately when each modification is completed.
　

MyISAM performance

The MyISAM engine is designed to be simple and the data is stored in a compact format, so the performance in certain scenarios is very good. However, the existence of table locks has a great impact on performance.

Archive engine

Archive storage engine only supports INSERT and SELECT. The Archive engine caches all writes and uses zlib to compress inserts, so it requires less disk I/O than MyISAM tables. But every SELECT query needs to perform a full table scan. Therefore, Archive tables are suitable for log and data collection applications, which often require full table scans for data analysis.

The Archive engine supports row-level locks and dedicated buffers, so high-concurrency insertions can be achieved. Archive will prevent other SELECTs from executing until all rows that exist in the table are returned before a query is started to achieve consistent reads. In addition, batch inserts are also implemented that are invisible to read operations until they are completed. This mechanism mimics some features of transactions and MVCC, but the Archive engine is not a transactional engine, but an engine optimized for high-speed insertion and compression.

CSV Storage Engine

This engine can process ordinary CSV files as MySQL tables, but this table does not support indexes. You only need to copy the CSV file to the data directory of the CSV storage engine, and it can be opened and used using the rules listed in MySQL.

Memory Engine

If you need to access data quickly, and the data will not be modified or lost after restarting, then using the Memory table is very useful. Memory tables are an order of magnitude faster than MyISAM tables because all data is stored in memory and no disk I/O operations are required. The structure of the Memory table will be retained after restarting, but the data will be lost.

• Supports Hash index, so the search operation is very fast.

• is a table-level lock, so concurrent write performance is low.

• does not support BLOB or TEXT type columns, and the length of each row is fixed. Even if varchar is specified, it will be converted to char in actual storage.

Explanation

Unless you need to use some features that InnoDB does not have, and there is no other way to replace it, you should give priority to the InnoDB engine—— "High-Performance MySQL"

In addition, the above only lists some commonly encountered storage engines, which is not comprehensive.

When creating a table, you can specify the type of the table, which is the storage engine of the table. The storage engine of a table determines how data is stored and accessed, as well as how transactions are stored. The storage engine of a table greatly affects the storage space and speed required to process SQL statements. Different storage engines have different characteristics. Some storage engines are very suitable for processing many complex SELECT statements, while others are more suitable for achieving fast updates.

InnoDB

InnoDB is MySQL’s default transactional engine and the most important and widely used storage engine. It is designed to handle a large number of short-lived transactions. Short-lived transactions are submitted normally in most cases and are rarely rolled back. InnoDB's performance and automatic crash recovery features make it popular for non-transactional storage needs.

Unless there are very special reasons to use other storage engines, the InnoDB engine should be given priority. ————"High-Performance MySQL"

• InnoDB uses MVCC to support high concurrency and implements four standard isolation levels. Its default level is REPEATABLE READ (repeatable read), and the gap lock strategy prevents phantom reads.

• InnoDB indicates that

• built based on a clustered index supports foreign key constraints.

• Supports automatic addition of column AUTO_INCREMENT attribute.

• Affairs. The InnoDB storage engine is a standard MySQL storage engine that supports transactions.

• There is no need to copy the entire table data when deleting or adding an index.

InnoDB has made many internal optimizations, including predictable read-ahead when reading data from disk, and an adaptive hash index that can automatically create a hash index in memory to accelerate operations. , and an insert buffer that speeds up insert operations.

InnoDB tables are built based on clustered indexes. The index structure of InnoDB is very different from other MySQL engines. Clustered indexes have high performance for primary key queries. However, the secondary index must contain the primary key column, so if the primary key column is large, all other indexes will be large. Therefore, if there are many indexes on the table, the primary key should be as small as possible.

MyISAM

MyISAM provides a large number of features, including full-text indexing, compression, spatial functions (GIS), etc., but MyISAM does not support transactions and row-level locks, and there is no doubt that The drawback is that it cannot be safely restored after a crash. In MySQL 5.1 and previous versions, MyISAM is the default storage engine. It is precisely because of this engine that even though MySQL has supported transactions for a long time, many people still think that MySQL is a non-transactional database.

• MyISAM locks the entire table, not the rows.

• Supports full-text indexing.

• Supports compressed tables. Compressed tables cannot be modified and can greatly reduce disk space usage, thus reducing disk I/O operations and thereby improving query performance.

Storage

MyISAM will store the table in two files: the data file and the index file, with .MYD and .MYI extensions respectively. MyISAM tables can contain dynamic or static rows. MySQL will decide which row format to use based on the table definition.
In MySQL5.0, if the MyISAM table has variable-length rows, the default configuration can only handle 256TB of data

Features

As one of the earliest storage engines of MySQL, there are still some features.

Locking and Concurrency – Table Lock

MyISAM locks the entire table, not the rows. When reading, shared locks will be added to all tables that need to be read, and exclusive locks will be added to the tables when writing. However, while the table has read queries, new records can also be inserted into the table (concurrent insertion).

Repair

For MyISAM tables, MySQL can perform inspection and maintenance operations manually or automatically, but the repair mentioned here is a different concept from transaction recovery and crash recovery.

Index Features

For MyISAM tables, even long fields such as BLOB and TEXT can create indexes based on their first 500 characters. MyISAM also supports full-text indexing, which is an index created based on word segmentation and can support complex queries.
Delayed update of index keys
When creating a MyISAM table, if the DELAY_KEY_WRITE option is specified, the modified index data will not be written to the disk immediately when each modification is completed.
　

MyISAM performance

The MyISAM engine is designed to be simple and the data is stored in a compact format, so the performance in certain scenarios is very good. However, the existence of table locks has a great impact on performance.

Archive engine

Archive storage engine only supports INSERT and SELECT. The Archive engine caches all writes and uses zlib to compress inserts, so it requires less disk I/O than MyISAM tables. But every SELECT query needs to perform a full table scan. Therefore, Archive tables are suitable for log and data collection applications, which often require full table scans for data analysis.

The Archive engine supports row-level locks and dedicated buffers, so high-concurrency insertions can be achieved. Archive will prevent other SELECTs from executing until all rows that exist in the table are returned before a query is started to achieve consistent reads. In addition, batch inserts are also implemented that are invisible to read operations until they are completed. This mechanism mimics some features of transactions and MVCC, but the Archive engine is not a transactional engine, but an engine optimized for high-speed insertion and compression.

CSV Storage Engine

This engine can process ordinary CSV files as MySQL tables, but this table does not support indexes. You only need to copy the CSV file to the data directory of the CSV storage engine, and it can be opened and used using the rules listed in MySQL.

Memory Engine

If you need to access data quickly, and the data will not be modified or lost after restarting, then using the Memory table is very useful. Memory tables are an order of magnitude faster than MyISAM tables because all data is stored in memory and no disk I/O operations are required. The structure of the Memory table will be retained after restarting, but the data will be lost.

• Supports Hash index, so the search operation is very fast.

• is a table-level lock, so concurrent write performance is low.

• does not support BLOB or TEXT type columns, and the length of each row is fixed. Even if varchar is specified, it will be converted to char in actual storage.

Explanation

Unless you need to use some features that InnoDB does not have, and there is no other way to replace it, you should give priority to the InnoDB engine—— "High-Performance MySQL"

In addition, the above only lists some commonly encountered storage engines, which is not comprehensive.

The above is the content of [MySQL] MySQL storage engine. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!