Comparative analysis of InnoDB and MyISAM in MySQL

Comparison between InnoDB and MyISAM

1. Storage structure

MyISAM: Each MyISAM is stored in three files on the disk. They are: table definition files, data files, and index files. 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: MyISAM supports three different storage formats: static table (default, but note that there cannot be spaces at the end of the data, it will be removed ), dynamic tables, compressed tables. After the table is created and data is imported, no modification operations will be performed. You can use compressed tables to greatly reduce disk space usage.

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 table-level locks are supported. 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 you select count() from table; it will be taken out directly. value.

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. The way is the same.

10. CRUD operation

MyISAM: If you execute 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.

11. Foreign key

MyISAM: Not supported

InnoDB: Supported

The above is the detailed content of Comparative analysis of InnoDB and MyISAM in MySQL. For more information, please follow other related articles on the PHP Chinese website!