Summary of B-tree index knowledge points in MySQL optimization

Why do we need to optimize SQL? Obviously, when we write SQL statements:

• 1 You will find that the performance is low
• 2. The execution time is too long,
• 3. Or waiting time is too long
• 4. The SQL statement is not good and our index is invalid
• 5. The server parameter settings are unreasonable

SQL statement execution process analysis

1. Writing process:

The writing process is the process of writing SQL statements in our daily life, which can also be understood as the writing order. The following is our writing order:

select from join on where condition group by grouping having filter group order by sort limit limit the number of queries

Although we wrote it this way, the MySQL engine does not follow the order we wrote above when parsing it;

It does not parse select first but parse from first, that is to say, our parsing process is inconsistent with the writing process, so let's look at the parsing order issued

2. Parsing process:

from on join where group by having select order by limit

The above is the parsing process of MySQL. We found that it is completely inconsistent with the process we wrote!

index

What is an index? Simply put, it is the catalog of the book;

For example, if I want to look up the word "王" in a dictionary, if you want to find the word "王" without a table of contents, you need to flip through the dictionary from beginning to end. If there are a thousand pages, you have to flip through them page by page until you find it.

The index is equivalent to a directory. Before looking up the word "王", we first look through the directory and find that "W" is on page 300. Since the first letter of "王" is "W", we can directly look for it in page 300. This way, we can find it very quickly.

The keyword "index" in the database is "insex". According to the official definition, an index is a data structure that helps MySQL obtain data quickly and efficiently.

An index is a data structure that is used to efficiently query data.

So what data structure is it?

In fact, it is a tree. We use B-tree and Hash tree more often. In MySQL, B-tree index is used.

B-Tree Index

First, I draw a picture, pretending that this is a data table, and add an index to the age column:

Just think of this index as a directory, that is, age 50 points to the first row, age 33 points to the fifth row;

Next I will draw the B-tree index to see how it is indexed:

After we add an index column to age, it will be like a tree, with smaller values ​​on the left and larger values ​​on the right. The first column is 50, and values ​​smaller than 50 are on the left, and 23 and values ​​smaller than 23 continue to be arranged to the left.

Since 33 is larger than 23, it will be arranged to the left. Since 20 is smaller than 22, it will continue to be arranged to the left after 22, and so on!

For example, we need to check 33 now:

select * From table name where age = 33;

Without an index, the search will start from 50. 50 is not 23, not 22, not .... Without an index, the search will start one by one.

If we add an index, we search for 33 and find that 33 is smaller than 50. The first time, we search for 23. The second time, 33 is larger than 23. The third time, we find it in just three times:

Disadvantages of indexing

1. The index itself takes up a lot of space and can be stored in memory/hard disk (usually)

2. Indexes are not applicable in all situations, such as small amounts of data and frequently updated fields (if a column in a data table changes frequently, then this column is not suitable for indexing)

3. Indexes can indeed improve query efficiency, but at the same time they will reduce the efficiency of additions, deletions, and modifications, for example:

We don't have an index. If you change 44 to 45, it's easy to change. If you have an index, I need to change not only 44 in the table, but also 44 in the B-tree:

Some people think it is not cost-effective, raising one and lowering three, which is not cost-effective. In fact, it is very cost-effective!

Because we are mostly querying, adding, deleting and modifying very rarely, because querying has a great impact on performance, it is very necessary to use it

Advantages of Indexes

1. Improved query efficiency

The client connects to the server through IO, through input and output streams, so improving query efficiency means reducing IO usage.

2. Reduce CPU usage

For example, if I have an order by desc statement in SQL, which sorts by age in descending or ascending order, if there is no index, you need to sort all the ages. However, if there is an index, you do not need to sort them. The B-tree itself is a sorted structure. The leftmost must be the smallest, and the rightmost must be the largest:

You just need to traverse it according to certain rules.

The above are the relevant knowledge points about B-number index. Thank you for your reading and support to 123WORDPRESS.COM.