In the last article, we briefly introduced the index type of InnoDB engine. In this article, we will continue to learn about InnoDB index, talk about index strategy, better use of index, improve database performance, mainly talk about overwrite index, the most left prefix principle, index push down.

Cover index

Overwriting an index means that the results of a query can be found in the normal index tree without having to search again in the primary key index tree.

Create the following table to demonstrate the overwriting index:

mysql> create table T (
ID int primary key,
age int NOT NULL DEFAULT 0, 
name varchar(16) NOT NULL DEFAULT '',
index age(age))
engine=InnoDB;
Copy the code

Select * from T where age between 13 and 25 select * from T where age between 13 and 25

  • Select * from age where age = 13; select * from age where age = 13
  • 2. Find all the information needed on the primary key index based on the value of the id
  • 3. Remove the age tree and repeat steps 1 and 2 until the age does not meet the requirements.

If we change the statement to SELECT ID from T where age between 13 and 25, the value of ID can be queried in the AGE index tree when executing this statement, eliminating the previous table back operation, thus reducing the search times and improving the query efficiency.

At this time, the AGE index tree can already meet our query requirements, and the AGE index is called the overwrite index.

Overwriting index is a common data query optimization technique, which can greatly improve database performance for the following reasons:

  • Reduce the number of tree searches and significantly improve query performance.
  • Indexes are stored in order of values, so it’s much less of an I/ O-intensive range query than randomly reading each row of I/O from disk.
  • The index entries are much smaller than the data entries, so reading in the index tree will greatly reduce the database access.

Left-most prefix rule

The left-most prefix rule is based on the associative index. If we create a associative index, we do not need to use the entire definition of the index, but only use the left-most field in the index. This is why b-tree indexes support the left-most prefix rule.

Create the following table to explain the leftmost prefix rule:

mysql> create table T (
ID int primary key,
age int NOT NULL DEFAULT 0, 
name varchar(16) NOT NULL DEFAULT '',
ismale tinyint(1) DEFAULT NULL,
email varchar(64),
address varchar(255),
KEY `name_age` (`name`,`age`))
engine=InnoDB;
Copy the code

We have created the federated index name_age. Now, suppose we have the following three query scenarios:

  • 1. Find out the age of the person whose user name begins with “Zhang”. Select * from ‘where name like’ %’ where name like ‘%’
  • 2, investigate the age of the user name containing the word “Zhang”. Select * from ‘where name like ‘% zhang %’
  • 3. Find out the age of the person whose user name ends in “Zhang”. Select * from ‘where name like ‘% zhang’

In the first of the three cases, we can use the name_age joint index to speed up the query. As we can see, we do not use the full definition of the index, as long as the left-most prefix is satisfied, we can use the index to speed up the search. The leftmost prefix can be the leftmost N field of a union index or the leftmost M characters of a string index.

If we change the index order to KEYname_age(age,name), the union index will not be used in any of the above three cases.

Index maintenance is costly, so sometimes we can reduce the number of indexes by using the left-most prefix principle.

An index pushdown

Index push-down optimization is introduced in MySQL 5.6. In the index traversal process, it can judge the fields contained in the index first and directly filter out the records that do not meet the conditions to reduce the number of times back to the table.

Create the following table to explain index push-down:

mysql> create table T (
ID int primary key,
age int NOT NULL DEFAULT 0, 
name varchar(16) NOT NULL DEFAULT '',
ismale tinyint(1) DEFAULT NULL,
email varchar(64),
address varchar(255),
KEY `name_age` (`name`,`age`))
engine=InnoDB;
Copy the code

Select * from T where name like ‘zhang %’ and age=10 and ismale=1; select * from T where name like’ zhang %’ and age=10 and ismale=1; Name_age = name_age = name_age = name_age = name_age = name_age

Select * from primary key where age = 10 and name = ‘zhang’; select * from primary key where name = ‘zhang’; Not all name_age tree look-ups whose names start with ‘zhang’ are returned to the primary key tree, thus reducing unnecessary queries.

Suppose our data looks like the figure below:

Select * from name_age; select * from name_age; select * from name_age; In the name_age index tree, you only need to check whether age is equal to 10. In this way, the number of times for retrieving the table is reduced and query performance is improved.

The above is about InnoDB engine index strategy, thank you for reading, hope this article is helpful to your study or work.

The last

At present, many big names on the Internet have MySQL related articles, if there is the same, please forgive. The original is not easy, the code word is not easy, but also hope you support. If there are mistakes in the article, please also put forward, thank you.

Welcome to scan the code to follow the wechat public number: “Technology blog of Brother Flathead”. Brother Flathead can learn together and make progress together.