Advantages and disadvantages of common MySQL storage engines

View all storage engines

We can use the show engines command to see which engines our MySQL server provides:

show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

InnoDB Storage Engine

InnoDB is the preferred engine for transactional databases, supporting transaction-safe tables (ACID), row locking, and foreign keys. After MySQL 5.5.5, InnoDB is used as the default storage engine. The main features of InnoDB are:

1. InnoDB provides MySQL with a transaction-safe (ACID-compliant) storage engine with commit, rollback, and crash recovery capabilities. InnoDB locks at the row level and also provides an Oracle-like non-locking read in SELECT statements. These features increase multi-user deployment and performance. In SQL queries, you can freely mix InnoDB tables with other MySQL table types, even in the same query.
2. InnoDB is designed to handle huge amounts of data with performance in mind, and its CPU efficiency may be unmatched by any other disk-based relational database engine.
3. The InnoDB storage engine is fully integrated with the MySQL server. The InnoDB storage engine maintains its own buffer pool in main memory to cache data and indexes. InnoDB stores its tables and indexes in a logical tablespace, which can consist of several files (or raw disk partitions). This is different from MyISAM tables, where each table is stored in a separate file. InnoDB tables can be of any size, even on operating systems that limit file size to 2GB.
4. InnoDB supports foreign key integrity constraints (FOREIGN KEY). When storing data in a table, each table is stored in the order of the primary key. If the primary key is not explicitly specified when defining the table, InnoDB will generate a 6B ROWID for each row and use it as the primary key.
5. InnoDB is used on many large database sites that require high performance.
6. InnoDB does not create a directory. When using InnoDB, MySQL will create a 10MB automatically extended data file named ibdata1 under the MySQL data directory, and two 5MB log files named ib_logfile0 and ib_logfile1.
   

MyISAM storage engine

MyISAM is a storage engine based on ISAM and extends it. It is one of the most commonly used storage engines in Web, data storage and other application environments. MyISAM has higher insertion and query speeds, but does not support transactions. In versions prior to MySQL 5.5.5, MyISAM was the default storage engine. The main features of MyISAM are:

1. Large files (up to 63 bits of file length) are supported on file systems and operating systems that support large files.
2. Dynamically sized rows produce less fragmentation when a mix of delete, update, and insert operations is used. This is done automatically by merging adjacent deleted blocks and extending to the next block if the next block is deleted.
3. The maximum number of indexes per MyISAM table is 64, which can be changed by recompiling. The maximum number of columns per index is 16.
4. The maximum key length is 1000B, which can also be changed by compiling. For key lengths greater than 250B, a key greater than 1024B will be used.
5. BLOB and TEXT columns can be indexed.
6. NULL values ​​are allowed in indexed columns. This value occupies 0-1 bytes per key.
7. All numeric key values ​​are stored high byte first to allow a higher index compression.
8. Internal handling of one AUTO_INCREMENT column per table. MyISAM automatically updates this column for INSERT and UPDATE operations. This makes AUTO_INCREMENT columns faster (at least 10%). After the value of a sequence item is deleted it cannot be reused.
9. You can put data files and index files in different directories.
10. Each character column can have a different character set.
11. Tables with VARCHAR can have fixed or dynamic record length.
12. VARCHAR and CHAR columns can be up to 64KB.
13. Using the MyISAM engine to create a database will generate three files. The file name starts with the name of the table, and the extension represents the type of file: frm files store table definitions, myd represents data files, and myi represents index files.
    

MEMORY storage engine

The MEMORY storage engine stores data in a table in memory, providing fast access for querying and referencing other table data. The main features of MEMORY are:

1. MEMORY tables can have up to 32 indexes per table, 16 columns per index, and a maximum key length of 500B.
2. The MEMORY storage engine implements HASH and BTREE indexes.
3. It is possible to have non-unique keys in a MEMORY table.
4. MEMORY tables use a fixed record length format.
5. MEMORY does not support BLOG or TEXT columns.
6. MEMORY supports AUTO_INCREMENT columns and indexes on columns that can contain NULL values.
7. MEMORY tables are shared among all clients (just like any other non-TEMPORARY table).
8. MEMORY table contents are stored in memory, which is shared by MEMORY tables and internal tables created by the server in its spare time during query processing.
9. When the contents of a MEMORY table are no longer needed, to free the memory used by the MEMORY table, you should execute DELETE FROM or TRUNCATE TABLE, or delete the entire table (with DROP TABLE).

The above is the detailed content of the advantages and disadvantages of common MySQL storage engines. For more information about the advantages and disadvantages of MySQL storage engines, please pay attention to other related articles on 123WORDPRESS.COM!