MySQL > MySQL > MySQL > MySQL > MySQL > MySQL > MySQL

Shen, I see different data on the Internet, some say InnoDB supports hash index, some say not, which is correct?

InnoDB hash index:

(1) InnoDB users cannot create hash indexes manually. InnoDB does not support hash indexes at this level.

(2) InnoDB is self-tuning. If it judges that the Adaptive Hash Index (AHI) can improve query efficiency, InnoDB will build relevant Hash indexes by itself. InnoDB also supports hash indexes;

So what is Adaptive Hash Index (AHI)? How does it work? Let’s start with an example.

We might as well have InnoDB tables:

t(id PK, name KEY, sex, flag)

_ Voiceover: _ID is the primary key, and name has a common index.

Suppose there are four records in the table:

1, shenjian, m, A

3, zhangsan, m, A

5, lisi, m, A

9, wangwu, f, B

InnoDB creates a Clustered Index for primary key IDS, a leaf stores the records themselves, a Secondary Index for name, and a leaf stores primary key values.

When initiating a primary key ID query, you can directly locate the row record by clustering the index.

select * from t where name=’ls’;

To initiate a normal index query:

(1) The primary key will be queried from the normal index (right);

(2) Again from the primary key, from the clustered index twice traversed to locate the record (above left).

The Search Path for record location is long for both clustered and plain indexes.

During MySQL running, if InnoDB finds that there are a lot of SQL with long pathfinding, and many SQL hits the same page, InnoDB will create an area in its own Buffer. Build an adaptive hash of all AHI to speed up queries.

In this sense, InnoDB’s self-use hash index is more like an “index of indexes”, after all, its purpose is to speed up index pathfinding.

Since it’s a hash, what’s key? What’s value?

Key is an index key value (or a key prefix).

Value is the index that records the page location.

Why “adaptive ****” hash index?

The system determines that it “should be able to speed up the query” and does not need to be manually set up, so it is called “adaptive”.

Does the system misjudge, does it accelerate?

It’s not always going to accelerate, sometimes it’s going to misjudge.

In the following scenarios:

  • Many single-line record queries (e.g. passport, user center, etc.)

  • Index range query (where AHI can quickly locate the first row record)

  • All the record memory can fit

AHI tends to be effective. _ Voiceover: _ Any technical solution that deviates from the business is a hooligan.

When services have a large number of like or Joins, AHI maintenance may become a burden and reduce system efficiency. In this case, you can manually disable the AHI function. A small knowledge point, I hope to answer the water friend’s question. Know what it is and why.

Related article: “Database indexes, What is it?” Assignment of “Index Difference between MyISAM and InnoDB” : Online check whether your business has opened AHI? _ Voiceover: _ Read the original, there are surprises.