MySQL performance optimization requirements are increasingly high, and the index aspect is the focus of performance optimization direction, so a deep understanding of MySQL index for future optimization plays a very important role, today to share with you an index, it is a descending index.

In addition, I have compiled the interview questions for 20 years, including Spring, concurrency, database, Redis, distributed, Dubbo, JVM, microservices and other aspects of the summary, if you need to get:Tencent document

What is a descending index

You may be familiar with indexes, but you may not be familiar with descending indexes, which are actually subsets of indexes. We usually use the following statement to create an index:

create index idx_t1_bcd on t1(b,c,d);
Copy the code

Select * from t1 where table b,c,d; select * from T1 where table B, C,d;

Unbeknownst to you, the above SQL is actually equivalent to the following SQL:

create index idx_t1_bcd on t1(b asc,c asc,d asc);
Copy the code

Asc stands for ascending order. An index created using this syntax is called an ascending index. In other words, when we create an index, we create an ascending index.

As you might imagine, when creating an index, you can set asC for the field. Is it possible to set DESC for the field? Of course it can, as in the following three statements:

create index idx_t1_bcd on t1(b desc,c desc,d desc);
create index idx_t1_bcd on t1(b asc,c desc,d desc);
create index idx_t1_bcd on t1(b asc,c asc,d desc);
Copy the code

This syntax is also supported in mysql, and indexes created using this syntax are called descending indexes. The key problem is that prior to Mysql8.0 it was only syntactically supported, not really at the bottom level.

Select * from Mysql7; select * from Mysql8; select * from Mysql7;

create table t1 (
a int primary key,
b int,
c int,
d int,
e varchar(20)
) engine=InnoDB;
Copy the code

Then create a descending index:

create index idx_t1_bcd on t1(b desc,c desc,d desc);
Copy the code

After creating the index, use the following SQL to view the index information:

show index from t1;
Copy the code

In Mysql7 you will get the result:In Mysql8 you will get the result:

Select * from idx_t1_bcd where Key_name = idx_t1_bcd; select * from idx_t1_bcd where Key_name = idx_t1_bcd;

  • In Mysql7, the result of the Collation field is A,A,A, which indicates that the Collation mode of b, C, and D is ASC
  • Select * from Mysql8 where Collation = D,D,D

Mysql > select * from Mysql7; mysql > select * from Mysql7; mysql > select * from Mysql7; mysql > select * from Mysql7; In Mysql8, descending indexes are really supported from the bottom up.

At this point, you should have a general understanding of ascending and descending indexes, but you don’t really understand them, because you don’t really know how they are implemented underneath.

Ascending index underlying implementation

We know that indexes are used to speed up queries, but why do indexes speed up queries?

Given a sequence of numbers, say [1,3,7,9,2,5,4,6,8], which is an unordered sequence or array, what would you do first if you wanted to speed up the query of that sequence?

I believe most of us can think of sorting first, sorting the disordered sequence from smallest to largest, such as [1,2,3,4,5,6,7,8,9], with this ordered sequence, we can use such as dichotomy and other algorithms to improve the query speed of this sequence.

The point of this example is that if you want to speed up the query of a collection of data, you can first sort the data.

So, for the data stored in a Mysql table is the same, if we want to increase the speed of query for the table, we can first sort data on this table, then a row of table data includes a lot of fields, we now want to sort the data bank, we should according to which fields to determine the order? This is the index, and the columns you specify when creating the index are used to sort the rows in the table.

For example, we still use the t1 table created above, and insert 8 data into t1:

insert into t1 values(4.3.1.1.'d');
insert into t1 values(1.1.1.1.'a');
insert into t1 values(8.8.8.8.'h');
insert into t1 values(2.2.2.2.'b');
insert into t1 values(5.2.3.5.'e');
insert into t1 values(3.3.2.2.'c');
insert into t1 values(7.4.5.5.'g');
insert into t1 values(6.6.4.4.'f');
Copy the code

Then the data must be stored in a file, so the format for storing the data in the file is roughly as follows, in the same order as the insertion order:

4311d
1111a
8888h
2222b
5235e
3322c
7455g
6644f
Copy the code

Note that T1 is Innodb’s storage engine, and the A field is the primary key, so Innodb’s storage engine will sort the inserted data by the primary key. The format for storing the data in the file I mentioned above is inaccurate, so I don’t want to get too long.

If we were to search for the data based on the above storage method, for example, to search for the row a=3, we would need to search from the first row, so we would need to search 6 times, and if we were to sort the data by the size of the field A:

1111a
2222b
3322c
4311d
5235e
6644f
7455g
8888h
Copy the code

So once we’ve sorted this out, if we’re still looking for the row where a is equal to 3, we only have to look it up 3 times. And it has a benefit is that if we now need to find a = 3.5 this row data, if we based on the way to store before ordering, we need to query all lines 8 data ultimately determine a = 3.5 this data does not exist, and if we use storage after sorted, we only need to check 4 times, Because when you check the record of 4311d, you will find that 4>3.5, and it can be confirmed that the record of a=3.5 does not exist.

If we now create an index on T1, as we did above, if we write the following SQL:

create index idx_t1_bcd on t1(b,c,d);
Copy the code

Select * from t1 where id = 1; select * from t1 where id = 1; select * from T1 where id = 1; select * from t1 where id = 1;

1111a
2222b
5235e
4311d
3322c
7455g
6644f
8888h
Copy the code

For example, the value of b, C, and D in 1111A is 111, while the value of B, C, and D in 2222b is 222. 111 is less than 222, so the corresponding row is ranked first.

So what’s the advantage of ordering data this way? Select * from a where b=4 and c=4 and d=4; select * from b where c=4 and d=4;

We create an index for a table, which is to sort the data in the table, and the sorted data can improve the query speed.

Sort and it is important to note that there are a lot of way, or that can make use of some data structures, such as binary tree, red and black tree, B + tree, the data structure is actually the data sorting, just sort in the form of each are not identical, each data structure has its characteristics, and everyone should know, One of the most used things in Mysql is the B+ tree

Order by (index by ()); order by (); order by (); For example, if we want to order t1 by B ASC, C ASc,d asC; SQL > select * from table T1 where b, C, and D are sorted in ascending order. SQL > select * from table T1 where B, C, and D are sorted in ascending order.

Order by b desc, C desc, d desc, c asc,d asc,c asc, D asc,c asc, D asc,c asc, D asc,c asc, D asc, C asc, D asc, C asc, D asc C) order by B desc, C) desc, D) desc

Order by B ASc, C desc, D desc Order by (b, C,d);

This is where descending indexes are needed.

Descending index low-level implementation

We spend a lot of time to introduce the implementation principle of ascending index, summed up is the data in the table according to the specified field comparison size ascending sort.

What’s the ascending order? If you compare the size of the data, the small ones are up and the big ones are down, or if you have a B+ tree the small ones are on the left and the big ones are on the right. The descending order is the big ones up and the little ones down, or in the case of B+ trees the big ones on the left and the little ones on the right. So, for the raw data above:

4311d
1111a
8888h
2222b
5235e
3322c
7455g
6644f
Copy the code

If we order this data in order of A desc, it is:

8888h
7455g
6644f
5235e
4311d
3322c
2222b
1111a
Copy the code

A desc B desc C desc D desc

8888h
6644f
7455g
3322c
4311d
5235e
2222b
1111a
Copy the code

Very simple, but what if we wanted to rank this data in order b desc, C ASC, d desc? Is that a little confusing?

In fact, sorting is actually a comparison of the size of the data, let’s use the following three rows of data to simulate:

3322c
7455g
4311d
Copy the code

B desc, C desc, D desc

7455g
3322c
4311d
Copy the code

B desc, C asc, D desc

7455g
4311d
3322c
Copy the code

If the data is equal, the c field will start to compare, and the C field is in ascending order, that is, the C field is in ascending order, and the c field is in ascending order, and the C field is in ascending order. So we get the result above.

This is a descending index.

conclusion

In fact, ascending and descending indexes are different sorting methods. With the implementation of descending indexes in Mysql8, we have more flexibility in creating indexes according to the sorting rules required by the business, which can make your queries faster.

Of course, this article only describes the principle, you must be aware that Mysql uses B+ tree, rather than the simple way OF the above example, but even using B+ tree principle is the same, just compare the size of the data.

Also, only the Innodb storage engine now supports descending indexing.