Analysis of database index progression

The basics of indexing can be moved to: Database indexing;

This article focuses on indexing advanced content.

Joint index

A joint index is used to index multiple columns in a table. A joint index is also a B+ tree, where the number of index keys is greater than or equal to 2.

Create statement:

create TABLE com_table(    id int not null,    name VARCHAR(20) not null,    age int not null,    constraint comb_key PRIMARY KEY(id,name,age));
Copy the code

Insert three pieces of data:

insert into com_table values(1,'lili',10); insert into com_table values(2,'xiaowang',20); insert into com_table values(3,'liming',30);Copy the code

After inserting data, the index looks like this:

When you talk about federated indexes, make sure you talk about leftmost matching;

What is leftmost matching:

MySQL will adhere to the left-most prefix matching principle when creating a federated index, that is, the left-most first, when retrieving data from the left-most of the federated index matching.

Let’s do some practice with the table above and the federated index:

1. The index takes effect

select * from com_table where age = 30;
Copy the code

Here is a full table scan, there is no index, synchronization only use age does not use index;

select * from com_table where id= 3 and age = 30;
Copy the code

If the index is matched, the left-most prefix rule is met.

2, use,andThe joint query

select * from com_table where age = 30 and id = 3;
Copy the code

SQL > select * from age,id,name where (age,id,name);

3. Range query

select * FROM com_table where id > 2;
Copy the code

Indicates a range query with no index hit.

select * FROM com_table where id = 2 and age > 10;
Copy the code

The statement matches the index;

When a range query is encountered (>, <, between, like), the index will stop matching, but the index will stop matching on the fields that use the range, such as age>10, and the index will stop matching on the fields that use the range.

Table back, index overwrite, index push down

To cover the above three points, let’s create a table and an example to better illustrate the details.

Create a table:

CREATE TABLE T_table(    id int not NULL AUTO_INCREMENT PRIMARY KEY ,    name VARCHAR(20) not null,    age int not null)engine=Innodb;
Copy the code

Create a normal index:

 create index index_T on T_table(name);
Copy the code

Table structure:

Insert data:

insert into T_table(name,age) values('lili',10); insert into T_table(name,age) values('xiaowang',20); insert into T_table(name,age) values('liming',30); insert into T_table(name,age) values('yuli',50); insert into T_table(name,age) values('kilo',70);Copy the code

At this point, our index tree has two entries, namely:

We can divide the index into primary key index and non-primary key index according to the content of leaf node and the type of index.

The one on the left is a primary key index. A leaf node stores the entire row of data. The one on the right is a non-primary key index, which is also called a secondary index.

Back to the table:

Let’s start with two statements:

select * from T_table where id = 3; select * from T_table where age = 30;Copy the code

  • The first statement can be queried directly using the primary key index. The primary key index can be retrieved directly from the row.

  • The second statement queries data through a non-clustered index, first through the primary key index, and then again to find the row data for the clustered index.

The second type above is actually a back-table operation, which means that a non-primary key index queries one more tree than a primary key index.

Index coverage:

As mentioned above, when we use non-clustered indexes, we need to query one more tree, which actually results in low efficiency, so when can we avoid back table operation?

Let’s use the same statement as before:

select id from T_table where age = 30;
Copy the code

SQL > select id from index tree; SQL > select id from index tree; SQL > select ID from index tree;

In this query, index AGE already overrides the query request, so this is also called index overrides.

Index push down:

(name,age); (name,age);

Select * from T_table where name like 'l%' and age >10;Copy the code

There are two cases of this, respectively:

1. Query all indexes that start with ‘L %’ according to the joint index (name,age), then query all data back to the table, and filter data again. In this case, if the name field matches multiple data, the table needs to be returned for multiple times, which is inefficient.

2. Query all indexes that start with ‘L %’ according to the joint index (name,age), and filter them according to the age field, and then perform table back operation, which greatly improves efficiency.

In case 2 above, this is index push-down, which is the default after mysql5.6. In tables using InnoDB’s storage engine, index push-downs can only be used for secondary indexes.

Wechat official account: background server development

Wechat official account: background server development

Language: C++, JAVA, python

● Scan code to pay attention to us