Detailed explanation of the differences between InnoDB and MyISAM indexes in Mysql (Latest Compilation)

InnoDB's index differs significantly in implementation and features from other storage engines in MySQL, such as MyISAM. Here are the main differences:

1. Index type and data storage method

InnoDB

• Clustered Index

The table data of InnoDB itself is stored in primary key order (that is, the leaf nodes indexed by the primary key directly contain data rows). If the primary key is not explicitly defined, InnoDB will automatically generate a hidden one.ROW_IDAs a clustered index.

• Secondary Index

The leaf nodes of the secondary index store the primary key value (not the physical address of the data row), which is required during querying.Return to the table(Find full data through primary key value into clustered index).

MyISAM

• Non-clustered Index

All indexes (including primary key indexes) are stored in the leaf nodesThe physical address of the data row(such as file offset). Index and data files (.MYD) Completely separate, index file (.MYI) Only pointers to data are stored.

2. Transaction and concurrency control

InnoDB

• Support transactions andMVCC (multi-version concurrent control), the index structure contains transaction ID and rollback pointer, which is used to implement non-locked reads and rollback operations.
• Supports row-level locking, and achieves efficient concurrent writes through indexing.

MyISAM

• Transactions are not supported, only table-level locks are provided, and the performance is low when writing in high concurrently.

3. Covering Index

InnoDB

• If the query only needs to pass the secondary index, it can obtain the required fields (such as the index overridesSELECT) There is no need to return the table, which is more efficient. But explicit design of indexes is required.

MyISAM

• All indexed leaf nodes point directly to data rows, and even if the query requires a return to the table, the efficiency loss is small (but limited by table-level locks).

4. Primary key constraints

InnoDB

• Force primary key (if there is no explicit definition, it will be created implicitlyROW_ID）。
• The primary key should be as short and ordered as possible (such as increasing integers) to avoid performance degradation due to frequent page splits.

MyISAM

• Allow tables without primary keys to exist, and all indexes are secondary indexes.

5. Foreign keys and constraints

InnoDB

• Supports foreign key constraints and automatically creates indexes for foreign keys.

MyISAM

• Foreign keys are not supported, and data consistency can only be maintained through the application layer.

6. Physical storage structure

InnoDB

• Data and indexes are stored in tablespace files (.ibd) supports more efficient space management (such as page splitting and merging).

MyISAM

• Data file (.MYD) and index files (.MYI) separation, and manual optimization is required when fragmentation is severe (such asOPTIMIZE TABLE）。

Example comparison

Assume that the table structure is:

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    KEY idx_age (age)
);

• **InnoDB
  • Primary key indexidThe leaf node stores the complete data row.
  • Secondary indexidx_ageLeaf node storageageand correspondingid, you need to pass the queryidReturn to the table to get data.
• MyISAM
  • Primary key indexidThe leaf node stores the physical address of the data row.
  • Secondary indexidx_ageLeaf node storageageand the corresponding physical address, you can directly access the data.

Summarize

Core DifferencesOriginating from InnoDB as a transactional engine, the design goal is high reliability and concurrency performance; while MyISAM is suitable for read-only or read-more or less read-more. In actual applications, InnoDB is the default engine of MySQL, and it is recommended to use it first.

This is the article about the detailed explanation of the differences between InnoDB and MyISAM indexes in Mysql. For more information about Mysql InnoDB and MyISAM indexes, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!