preface

Understanding overwrite indexes starts with understanding the difference between primary and secondary indexes and how the engine works when querying.

Of course, all of this is based on the innoDB engine.

The difference between a primary and secondary index

I believe that you have also understood the knowledge of this area, here will not expand, directly on the summary.

The primary key index

The leaf node holds the data,

Secondary index

The leaf node holds the primary key

How does querying a piece of data work

First, the query process:

Since the secondary index only stores the value of the primary key, if you use the secondary index to search for data, you must first fetch the value of the primary key from the secondary index, and then use the value of the primary key to search on the primary key index until the data on the leaf node is found and returned. —- This is also known as “return table”

So how do you avoid back table queries?

If the data we need already exists on the secondary index, the engine will not search for the data on the primary key. —- This is what’s called a “covered index”

So let’s prove it.

Back to the table query

Suppose we had a table like this:

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_age_name` (`age`,`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Copy the code

We add an index to age, and then insert a few random pieces of data

Insert into test (` id `, ` age `, ` name `) VALUES (1, 10, "Ming"), (2, 11, and "little red"), (3, 12, "small white");Copy the code

Query a piece of data

select * from test where age = 10
Copy the code

Check the time

Analyze the statement:

desc select * from test where age = 10
Copy the code

View the execution plan:

As you can see, the extra column is empty, and the key index is idx_age. The approximate query time is about 0.024 seconds.

Is this query fast?

Can you believe me when I say I can make it better? – Lu Xun (I never said)

Cover index

We only need to change the query field slightly to see the difference.

select age,name from test where age = 10

Copy the code

Check the time:

You can see the time is reduced!

So what happens? Let’s analyze the statement again, okay

desc select age,name from test where age = 10
Copy the code

The extra column contains a using index. The extra column contains a using index.

conclusion

Practice is the sole criterion for testing principles. By doing this, you have already fully understood and experienced the concept of overwriting indexes and what they mean. The core is to only get data from secondary indexes. Then, plain (single-field) and union indexes, as well as unique indexes, can perform the function of overwriting indexes.

Your praise is the motivation of my creation! Three in a row.

I am three seas, dedicated to use the most concise words about backstage development technology ape man.