An article explains MySQL's clustering/joining/covering index, return table, and index pushdown

Your interviewer is approaching, a middle-aged man in a plaid shirt with a beer belly and a severely receding hairline.
Holding a thermos cup soaked in wolfberry in his hand, a MacBook under his arm, and the company slogan on the MacBook: "Working overtime makes me happy."

Interviewer: Seeing that you have used MySQL on your resume, let me ask you a few simple questions. What are clustered and nonclustered indexes?

This question can't stop me. Before I come, let me take a look at the MySQL eight-legged text.

Me: For example: there is such a user table

 CREATE TABLE `user` (
  `id` int COMMENT '主键ID',
  `name` varchar(10) COMMENT '姓名',
  `age` int COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8 COMMENT='用户表';

This data is stored in the users table:

id	nane	age
1	a lamp	18
2	Zhang San	twenty two
3	Li Si	twenty one
4	Wang Er	19
5	pock	20

So in the index, how is this data stored?

The B+ tree structure used by indexes in MySQL's InnoDB engine.

Don't ask why the root node stores two elements (1, 4), the left child node stores three elements (1, 2, 3), and there are three leaf nodes below, and the leaf nodes are connected by an ordered linked list. ?

The question is about the characteristics of the B+ tree. If you don't understand it, you can read the article in the previous issue.

As shown in the figure above, the index of all elements stored in the leaf node is the clustered index .
Generally, the primary key index is a clustered index . If there is no primary key in the table, MySQL will also create a hidden primary key as the primary key index by default.

What is a nonclustered index?

Suppose we build an ordinary index on the age (age) field, and the index storage structure on the age field is as follows:

Only the current index field and primary key ID are stored in the leaf node, and such a storage structure is a non-clustered index.

Interviewer: So what is a joint index?

Me: An index composed of multiple fields is a joint index.

Interviewer: [Halo] What are the benefits of building a joint index? How is it different from indexing on a single field?

Me: Suppose there is such a query statement.

 select * from user where age = 18 and name = '张三';

If we build two indexes on the age and name fields respectively, this query will only use one of the indexes.

But we build a joint index (age, name) in the age and name fields, and its storage structure becomes like this.

If you only build an index on age, the non-clustered index on age will be queried first, there are three records with age=18, the primary key IDs are 1, 4, and 5, and then these three IDs will be used to query the clustering of the primary key ID. index.

If a joint index is built on age and name, the non-clustered index on age and name will be queried first, a record with a primary key ID of 1 is matched, and then this ID will be used to query the clustered index of the primary key ID.

It can be concluded that the advantage of the joint index: greatly reduce the number of scanned rows.

Interviewer: Can you tell me what is the leftmost matching principle?

Me: The leftmost matching principle means that when establishing a joint index, the leftmost priority is followed, and any consecutive index can be matched with the leftmost as the starting point.

When we create a joint index on (age, name), only age can use the index in the where condition, and both age and name can also use the index. But when there is only name, the index cannot be used.

Why does this happen?

Looking at the above figure, you can understand that the joint index of (age, name) is first sorted by age, and then the rows with equal age are sorted by name. If the where condition has only one name, of course the index cannot be used.

Interviewer: What are covering indexes and back-table queries?

Me: This is even simpler. This knowledge point has been mentioned above.

When we build an index on age, the query SQL is like this:

 select id from user where age = 18;

The covering index will be used, because the ID field has been found out when we use the age index, and there is no need to query the table again.

But when the query SQL is like this:

 select * from user where age = 18;

If you want to query all fields, you need to query the table twice. Because we only found the primary key ID when we used the age index for the first time, we also need to use the primary key ID to return to the table to query all fields.

Interviewer: One more question, do you know what index pushdown is?

You can ask such unpopular questions. You really want to interview to build a rocket!

Me: Index Condition Pushdown is an index-optimized feature introduced in MySQL 5.6.

Example:

When a joint index is built on (age, name), and the query SQL is like this:

 select * from user where age = 18 and name = '张三';

If there is no index pushdown, three records with age = 18 will be matched first, and then the ID return table query will be used to filter out the records with name = 'Zhang San'.

If you use index pushdown, three records with age = 18 will be matched first, then one record with name = 'Zhang San' will be filtered out, and finally the ID will be used to return the table query.

From this, the advantage of index pushdown is that it reduces the number of scanned rows back to the table.

Interviewer: Young man, Baguwen is quite slippery. I will give you a practical question to see if you are ready. How to create a joint index for the following query SQL?

 select a from table where b = 1 and c = 2;

Deliberately making things difficult for me? Do you think you can't memorize eight-legged essays for practical questions?

Me: I already mentioned this knowledge point when I talked about the joint index. The where condition has the equivalent query of b and c, and the joint index will be built (b, c). Since there is a after the select, we will build (b, c). The joint index of c,a), and covering index can be used, the query speed is faster.

Interviewer: Boy, there is something. I will send you an offer in a while, and I will come to work tomorrow with double salary.

The article is continuously updated, and you can search for "One Light Architecture" on WeChat to read more technical dry goods as soon as possible.