This article has participated in the good article call order activity, click to see: back end, big front end double track submission, 20,000 yuan prize pool for you to challenge!

This article tests mysql version 5.7+

This article details the differences between MyISAM and InnoDB

Interviewer: What is the difference between MyISAM and InnoDB in mysql

1. Storage structure

MyISAM: Each table is stored in three files: FRM-table definition, MYD(MYData) -data file, and MYI(MYIndex) -index file.

Innodb: All tables are stored in the same datapile (it can be multiple files or separate tablespace files), Innodb table size is limited only by the size of the operating system file, usually 2GB.

2. Storage space

MyISAM: MyISAM can be compressed and has a small storage space.

Innodb: Innodb’s tables require more memory and storage, so it creates its own buffer pool in main memory for caching data and indexes.

3. Portability, backup and recovery

MyISAM: Since MyISAM’s data is stored as a file, it is easy to move data across platforms. You can operate on a single table during backup and restore.

Innodb: Free solutions can be copying data files, backing up binlogs, or using mysqldump, which is relatively painful when the data volume reaches tens of gigabytes.

4. File format

MyISAM: Data and indexes are stored separately, data (.myd) and index (.myi).

Innodb: Data and indexes are stored centrally (.ibd).

5. Record the storage order

MyISAM: Save records in the order they were inserted.

Innodb: Insert in order by primary key size.

6. The foreign key

MyISAM: Not supported.

Innodb: Supported.

7. The transaction

MyISAM: Not supported.

Innodb: Supported.

8. The lock support

MyISAM: Table level lock (lock size is large and concurrency is weak).

Innodb: row level lock, table level lock (lock size is small and concurrency is high).

9. SELECT queries

MyISAM is better.

10. INSERT, UPDATE, DELETE

InnoDB better.

11. select count(*)

MyISAM: faster, because MyISAM internally maintains a counter that can be fetched directly (but if you add a WHERE condition it is the same as InnoDB).

Innodb: No specific number of rows will be saved, you need to traverse the entire table to calculate.

12. Index implementation

MyISAM: B+ tree index, MyISAM is the heap table.

Innodb: B+ tree index, Innodb is index organization table.

13. Hash index

MyISAM: Not supported.

Innodb: Supported. (Custom hash, cannot be created manually)

14. Full-text indexes

MyISAM: Support.

Innodb: Supported (since version 5.6).

What is the difference between MyISAM index and InnoDB index

  • InnoDB indexes are clustered indexes, MyISAM indexes are non-clustered indexes.
  • InnoDB’s primary key index leaves store row data, so primary key indexes are very efficient.
  • The leaf node of the MyISAM index stores the address of the row data and requires an addressing operation to retrieve the data.
  • InnoDB leaves with non-primary key indexes store primary keys and other indexed data, so it’s more efficient to overwrite queries with indexes.

InnoDB- Primary key index

Leaf nodes store specific row data

InnoDB- Non-primary key index

Leaf nodes that are not primary key indexes store primary keys

MyISAM

The leaf node stores the address of the row data, which needs to be addressed once