preface

We have talked about the underlying data structure of MySQL index and the failure mechanism of index. So how exactly is the index stored on the hard drive? How to query a piece of real data through index positioning?

What are the differences between the indexes of MyIsam and InnoDB?

Today we are going to reveal it to you

Common differences between MyIsam and InnoDB

affairs

InnoDB supports transactions, MyISAM does not. This is one of the big reasons MySQL changed its default storage engine from MyISAM to InnoDB.

Foreign key aspects

InnoDB supports foreign keys, while MyISAM does not. Converting an InnoDB table with foreign keys to MYISAM will fail.

The index level

InnoDB is a clustered index, MyISAM is a non-clustered index. The differences between these two indexes will be highlighted later.

MyISAM supports FULLTEXT, InnoDB does not support FULLTEXT, but InnoDB can use the sphinx plugin to support FULLTEXT indexing, and the results are better.

Lock granularity

InnoDB’s minimum lock size is row lock, MyISAM’s minimum lock size is table lock.

An update statement locks the entire table, blocking all other queries and updates, and thus limiting concurrent access.

This is one of the main reasons MySQL changed its default storage engine from MyISAM to InnoDB.

Hard Disk Storage Structure

MyISAM is stored as three files on disk. The name of the first file starts with the name of the table, and the extension indicates the file type.

  • .frmFile storageThe definition of the table.
  • The data fileThe extension name of.MYD(MYData).
  • Index fileThe extension name of is.MYI(MYIndex).

Innodb storage engine stores database data and has two files (there is no special file for storing data) :

  • Frm file: Table definition file.
  • Ibd file: Data and index storage files. Data is clustered in primary keys, keeping the real data in leaf nodes.

Clustered index and non-clustered index

Cluster Index (InnoDB)

Put the data store together with the index, and the leaf node of the index structure holds the row data.

Table data is stored in index order, that is, the order of index entries is the same as the physical order of the records in the table.

In InnoDB, indexes created on top of clustered indexes are called secondary indexes, such as composite indexes, prefix indexes, unique indexes, and so on.

  • Cluster indexes default to primary keys,
  • If no primary key is defined in the table, InnoDB selects oneUnique non-empty indexInstead.
  • If there is no such index, InnoDB generates an internal index namedGEN_CLUST_INDEXThe implicit cluster index of.

Non-clustered index (MyISAM)

Data is stored separately from indexes, and the order in which table data is stored is independent of index order.

MyISAM index query data procedure

Non-clustered index storage structure:

From the network

On the leaf node of MyISAM’s B+ tree, the real data is stored.

For example, the MyISAM query process is as follows:

  • Find the corresponding leaf node on the B+ tree based on the ID value
  • Retrieves the data store address on the leaf node
  • According to the data store address, to find the corresponding real data

InnoDB index query data process

Clustered index storage structure:

From the network

InnoDB’s B+ tree leaves record true implementation data.

For example, the InnoDB query process is as follows:

Cluster index (primary key index) :

  • Find the corresponding leaf node on the B+ tree based on the ID value
  • Retrieve the row data on the leaf node and return it

Secondary indexes (other than clustered indexes) :

  • Find the corresponding leaf node on the B+ tree of the corresponding index
  • Fetch the data on the leaf node, which is the primary key ID
  • After obtaining the primary key ID, go to the B+ tree of the primary key index to find the corresponding leaf node
  • Retrieves the row data on the leaf node and returns

conclusion

These are the two engine index query processes. MyIsam and InnoDB have their own advantages and disadvantages.

For example, InnoDB is faster than MyIsam when querying by primary key ID because InnoDB only needs a B+ tree lookup to fetch data. After MyIsam finds the address through the B+ tree, it also needs to query the real data according to the address.

But InnoDB normal index queries are slower than MyIsam because InnoDB does two B+ tree lookups.

For example, during data reconstruction, MyIsam records the address of the data, so the address has to be generated again during data reconstruction, which is also problematic.

InnoDB doesn’t do this when it reconstructs data, because it records the primary key ID, the address changes, the primary key ID doesn’t change.

And why not use select *, why not use overwrite index, can you guess from their index structure, leave you a question?

Every technology has its own advantages and disadvantages. As we always say, we should make full use of the advantages of technology according to our business scenarios.

PS: If you think my share is good, you are welcome to like and forward.