Analysis of commonly used storage engines in MySQL

The content of this article is about the analysis of commonly used storage engines in MySQL. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

MyISAM: Each MyISAM is stored as three files on disk. The name of the first file begins with the name of the table, and the extension indicates the file type. .frm files store table definitions. The extension of the data file is .MYD

(MYData). The extension of the index file is .MYI (MYIndex).

MyISAM is the default database engine of MySQL (before version 5.5), which was improved from the early ISAM (Indexed Sequential Access Method: indexed sequential access method). Although the performance is excellent, it has a disadvantage: it does not support transaction processing.

InnoDB is one of the database engines of MySQL and one of the binary standards released by MySQL AB. Compared with traditional ISAM and MyISAM, the biggest feature of InnoDB is that it supports transactions.

What is the difference between MyISAM and InnoDB? The summary is as follows:

1. Storage structure

MyISAM: Each MyISAM is stored in three files on the disk. The name of the first file begins with the name of the table, and the extension indicates the file type. .frm files store table definitions. The data file extension is .MYD (MYData). The extension of the index file is .MYI (MYIndex).

InnoDB: All tables are stored in the same data file (or multiple files, or independent table space files). The size of the InnoDB table is only limited by the size of the operating system file. Generally 2GB.

2. Storage space

MyISAM: It can be compressed and has smaller storage space. Supports three different storage formats: static table (default, but please note that there must be no spaces at the end of the data, it will be removed), dynamic table, and compressed table.

InnoDB: Requires more memory and storage, it will establish its own dedicated buffer pool in main memory for caching data and indexes.

3. Portability, backup and recovery

MyISAM: Data is stored in the form of files, so it is very convenient for cross-platform data transfer. You can perform operations on a table individually during backup and recovery.

InnoDB: Free solutions include copying data files, backing up binlog, or using mysqldump, which is relatively painful when the data volume reaches dozens of gigabytes.

4. Transaction support

MyISAM: The emphasis is on performance. Each query is atomic and its execution times are faster than the InnoDB type, but it does not provide transactions. support.

InnoDB: Provides transaction support, foreign keys and other advanced database functions. Transaction-safe (ACID compliant) tables with transaction (commit), rollback (rollback), and crash recovery capabilities.

5. AUTO_INCREMENT

MyISAM: You can create a joint index with other fields. The engine's automatic growth column must be an index. If it is a combined index, the automatic growth column does not need to be the first column. It can be sorted according to the previous columns and then incremented.

InnoDB: InnoDB must contain an index with only this field. The engine's auto-growing column must be an index, and if it is a composite index, it must also be the first column of the composite index.

6. Table lock differences

MyISAM: only supports table-level locks. When users operate myisam tables, select, update, delete, and insert statements will all be automatically assigned to the table. Locking, if the locked table satisfies insert concurrency, new data can be inserted at the end of the table.

InnoDB: Supporting transactions and row-level locks is the biggest feature of innodb. Row locks greatly improve the performance of multi-user concurrent operations. However, InnoDB's row lock is only valid on the primary key of WHERE. Any non-primary key WHERE will lock the entire table.

7. Full-text index

MyISAM: supports FULLTEXT type full-text index

InnoDB: does not support FULLTEXT type full-text index, but innodb can use it The sphinx plug-in supports full-text indexing and the effect is better.

8. Table primary key

MyISAM: Allows tables without any indexes and primary keys to exist. The indexes are the addresses where rows are saved.

InnoDB: If the primary key or non-empty unique index is not set, a 6-byte primary key (invisible to the user) will be automatically generated. The data is part of the primary index, and the additional index saves the primary index. value.

9. The specific number of rows in the table

MyISAM: Saves the total number of rows in the table. If count() from table; is selected, the value will be taken out directly.

InnoDB: The total number of rows in the table is not saved. If you use select count() from table; it will traverse the entire table, which consumes a lot of money. However, after adding the wehre condition, myisam and innodb process it in the same way. .

10. CURD operation

MyISAM: If you perform a large number of SELECTs, MyISAM is a better choice.

InnoDB: If your data performs a large number of INSERTs or UPDATEs, you should use an InnoDB table for performance reasons. DELETE is better in terms of performance than InnoDB, but when DELETE FROM table, InnoDB will not re-create the table, but will delete it row by row. If you want to clear a table with a large amount of data on InnoDB, it is best to use the truncate table command.

11. Foreign keys

MyISAM: Not supported

InnoDB: Supported

Through the above analysis, it can basically be considered InnoDB is used to replace the MyISAM engine, but in actual applications, the specific situation can be considered by yourself.

The above is the detailed content of Analysis of commonly used storage engines in MySQL. For more information, please follow other related articles on the PHP Chinese website!