1.35 InnoDB and MyISAM Index Comparison

1. InnoDB

• MySQL’s default storage engine.
• InnoDB divides data into pages. Each page is 16KB, and pages are the basic unit of interaction between disk and memory.
  • That is, a read reads at least one page and a write writes at least one page.

1.1. Index Implementation

1.1.1. Primary-Key Index

• The data in leaf nodes stores the actual data.
  • 
• Because the index stores the actual data, there is only one ibd file.
  • 

1.1.2. Secondary Index

The data in leaf nodes stores the primary-key index.

2. MyISAM

• The difference from InnoDB is that indexes and data are separated.
  • Data file: row number - record.
    • 
  • Index file: index - row number.
• This is equivalent to all MyISAM indexes being secondary indexes.

As shown in the figure, because indexes and data are separated, there are MYD and MYI files.

2.1. Index Implementation

2.1.1. Primary-Key Index

The data in leaf nodes stores the address of the data.

2.1.2. Secondary Index

Like the primary-key index, the data in leaf nodes stores the address of the data.