Shulou(Shulou.com)06/01 Report--

Why SQL optimization? Obviously, when we write the sql statement:

1 will find a low performance of 2. 5%. The execution time is too long, 3. Or the waiting time is too long, the 4.sql statement is poor, and our index fails 5. 5. Unreasonable setting of server parameters

Analysis of the execution process of SQL statement

1. The authoring process:

The writing process is the process that we usually write sql statements, which can also be understood as the writing order. Here is the writing order:

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

Although we write it in this way, its mysql engine doesn't parse it in the order we wrote above.

It doesn't 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 below.

two。 Parsing process:

From on join where group by having select order by limit

This is the parsing process of mysql, and we found that it is completely inconsistent with the process we wrote!

Indexes

What is an index? In a nutshell, it is the catalogue of books.

For example, I'm going to look up the word "Wang" through a dictionary. If you look for the word "Wang" without a catalog, you need to turn the dictionary from beginning to end. If there are a thousand pages, you have to turn it page by page until you find it.

The index is equivalent to the catalogue. Before looking up the "Wang", we first look through the catalogue and find that "W" is on page 300. Because the initials of the king are "W", we go directly to look for it on page 300, so it is very fast to find it.

Index is the keyword insex in the database. In the official definition, index is the data structure that helps MySQL to obtain data quickly and efficiently.

An index is a data structure, which is used to query data efficiently.

So what kind of data structure is it?

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

B-tree index

First I draw a diagram, pretend this is a data table, and add an index to the age column:

Think of this index as a directory, that is, the one with age 50 points to the first row, and the one with age 33 points to the fifth line.

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

When we add an index column to age, it will, like a tree, put the small ones on the left and the large ones on the right, the first column 50, the smaller ones on the left, 23, and the smaller ones on the left.

33 is bigger than 23, just arrange to the left 20 smaller than 22, then continue to the left after 22, and so on!

For example, we now need to check 33:

Select * From table name where age = 33

If it is not indexed, it will start from 50, 50 is not 23, not 22 is not.... without indexing, we will look for it one by one.

If you add an index, look for 33 and find that 33 is smaller than 50. The first time, go to 23 again, the second time, 33 is bigger than 23, and the third time, it only takes three times to find it:

The disadvantages of index

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

two。 Indexes are not applicable in all cases, such as fields that are updated frequently with a small amount of data (if a column in a data table changes frequently, then this column is not suitable for indexing)

3. Indexing does improve query efficiency, but it also reduces the efficiency of additions, deletions and modifications, such as:

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

Some people think that it is not cost-effective, promote one and reduce three, so it is not cost-effective, in fact, it is very cost-effective!

Because we are querying in most cases, adding, deleting and changing very little, because the query has a great impact on performance, it is very necessary to use it.

Advantages of indexing

1. Improve the query efficiency

Client to server, link server through IO, through input and output stream, so to improve query efficiency is to reduce the utilization rate of IO.

two。 Reduce CPU utilization

For example, in my sql, there is an order by desc in descending or ascending order according to age. If there is no index, you need to sort all the age, but if you have an index, you do not need to sort. The B-tree itself is a sorted structure, which must be the smallest on the left and the largest on the right:

You just need to go through it according to certain rules.

The above are the relevant knowledge points of the B-number index. Thank you for your reading and support.

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.