Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to understand tables and indexes in MySQL database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The concept of returning to the table

Come to the conclusion first, according to the following experiment. If I want to get ['liu','25'] this record. What steps are needed.

1. First, the primary key id:4 is obtained by ['liu'] record corresponding to the general index index (name).

two。 Then through clustered index, locate the row record. That is, the ['liu','25'] above records data.

Therefore, the above is the back table query, first locate the primary key value, and then locate the row record. Swept the index tree again.

Of course, it consumes more CPU,IO, memory and so on.

1.stu_info table case

Create table stu_info (id int primary key, name varchar (20), age int, index (name)) 2. View the table structure you just created

Mysql > show create table stu_info\ G; * * 1\. Row * * Table: stu_info Create Table: CREATE TABLE `stu_ info` (`id` int (11) NOT NULL, `name` varchar (20) COLLATE utf8_bin DEFAULT NULL, `age` int (11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `name` (`name`) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin 1 row in set (0.00 sec) 3. Insert test data

Insert into stu_info values (1); insert into stu_info values (4); insert into stu_info values (7); insert into stu_info values (10); insert into stu_info values (30); insert into stu_info values (16); insert into stu_info values (28); commit4. Analysis process

Let's analyze the index of these pieces of data. Because we have an index for the column name. So the name index stores are sorted in [axiz] order. Through the select statement, we can get some perceptual knowledge. It is as follows:

Mysql > select name from stu_info; +-+ | name | +-+ | allen | | benjiemin | | huang | | liu | | peter | | roger | | zhang | +-+

The above general index secondary index may be stored in the B+ tree in the following format:

According to the visual B+tree provided by the University of San Francisco.

As shown below:

I'm drawing my own according to the picture above. As shown in the following figure:

You can also see what the B+ tree built by name data looks like. You can also see that if I need to find the element [liu], I need to look it up twice.

However, if my need is to get age in addition to getting name. You need to return to the watch here. Why? Because I can't find the age data.

The leaf node of a normal index, with only the primary key.

So how is the clustered index clustered index saved? Continue to use the above visualization tools to analyze another wave.

The figure above is a schematic diagram of a clustered index. The picture converted to me is as follows:

Therefore, the age of liu queried by name='liu' needs to be returned to the table. First, query the B+ tree of the general index, and then query the B+ tree of the clustered index. Finally, I get the line record of liu.

5. Carry out the plan

We can also analyze it by implementing the plan, as follows:

Mysql > explain select id,name,age from stu_info where name='liu'\ G; * * 1\. Row * * id: 1 select_type: SIMPLE table: stu_info type: ref possible_keys: name key: name key_len: 63 ref: const rows: 1 Extra: Using index condition 1 row in set (0.00 sec)

When we see Using index condition, we use the return table here.

If you do not take age, but only id and name, then. There is no need to return to the watch. In the following experiment, continue to look at the implementation plan:

Mysql > explain select id,name from stu_info where name='liu'\ G; * * 1\. Row * * id: 1 select_type: SIMPLE table: stu_info type: ref possible_keys: name key: name key_len: 63 ref: const rows: 1 Extra: Using where; Using index 1 row in set (0.00 sec)

So, if we don't want to go back to the watch and don't want to do more IO. We can solve this problem by setting up a composite index. Pass through

ALTER TABLE stu_info DROP INDEX name; alter table stu_info add key (name,age)

Let's move on to the implementation plan, as follows:

Mysql > explain select name,age from stu_info where name='liu'\ G; * * 1\. Row * * id: 1 select_type: SIMPLE table: stu_info type: ref possible_keys: name key: name key_len: 63 ref: const rows: 1 Extra: Using where; Using index 1 row in set (0.00 sec)

You can see that the extra information is Using where; Using index instead of Using index condition, so there is no need to return to the table.

This is the end of the content of "how to understand tables and indexes in MySQL database". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.