Table of Contents
Comparison between InnoDB and MyISAM
1. Storage structure
2. Storage space
3. Portability, backup and recovery
4. Transaction support
5. AUTO_INCREMENT
6. Table lock differences
7. Full-text index
8. Table primary key
9. The specific number of rows in the table
10. CRUD operation
11. Foreign key
Home Database Mysql Tutorial Comparative analysis of InnoDB and MyISAM in MySQL

Comparative analysis of InnoDB and MyISAM in MySQL

Jun 02, 2023 pm 04:16 PM
mysql innodb myisam

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!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot Article Tags

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

PHP's big data structure processing skills PHP's big data structure processing skills May 08, 2024 am 10:24 AM

PHP's big data structure processing skills

How to optimize MySQL query performance in PHP? How to optimize MySQL query performance in PHP? Jun 03, 2024 pm 08:11 PM

How to optimize MySQL query performance in PHP?

How to use MySQL backup and restore in PHP? How to use MySQL backup and restore in PHP? Jun 03, 2024 pm 12:19 PM

How to use MySQL backup and restore in PHP?

How to insert data into a MySQL table using PHP? How to insert data into a MySQL table using PHP? Jun 02, 2024 pm 02:26 PM

How to insert data into a MySQL table using PHP?

What are the application scenarios of Java enumeration types in databases? What are the application scenarios of Java enumeration types in databases? May 05, 2024 am 09:06 AM

What are the application scenarios of Java enumeration types in databases?

How to fix mysql_native_password not loaded errors on MySQL 8.4 How to fix mysql_native_password not loaded errors on MySQL 8.4 Dec 09, 2024 am 11:42 AM

How to fix mysql_native_password not loaded errors on MySQL 8.4

How to use MySQL stored procedures in PHP? How to use MySQL stored procedures in PHP? Jun 02, 2024 pm 02:13 PM

How to use MySQL stored procedures in PHP?

Performance optimization strategies for PHP array paging Performance optimization strategies for PHP array paging May 02, 2024 am 09:27 AM

Performance optimization strategies for PHP array paging

See all articles