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

This article introduces the relevant knowledge of "how to query the table in MySQL". 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!

Let's first take a look at what is a return table?

Generally speaking, if the column of the index is in the column that select needs to obtain (because the index in mysql is sorted according to the value of the index column, so there is part of the value of the column in the index node) or according to an index query to get the record, there is no need to return to the table. If the select needs to get a large number of non-index columns in the column, the index needs to find the corresponding column information in the table, which is called back to the table.

According to this concept, when you use Explain to execute the query plan, when Extra appears using index, using where, using index condition, etc., you think that using the filter condition, using the index, SQL optimization is good. This is actually a wrong understanding.

Because the use of an index does not mean that the query is optimal. From using index condition, using index & using where, and so on, we can see that this SQL statement actually returns to the table.

There are also times when you look at the Explain execution plan and find that the index is clearly gone, why is it still slow? There may be a return to the table. Let's use an example to illustrate what a table return is. Create a table first, with the following sql statement:

Create table xttblog (

Id int primary key

K int not null

Name varchar (16)

Index (k)

) engine = InnoDB

Then, we execute the following SQL statement to insert a few pieces of test data.

Suppose, now we want to query the data with an id of 2. Then execute select * from xttblog where ID = 2; this SQL statement does not need to return to the table. The reason is that according to the way the primary key is queried, you only need to search the ID B+ tree. The primary key is unique, and according to this unique index, MySQL can determine the search record.

But when we use the k index to query the records of k = 2, we need to use the back table. Select * from xttblog where k = 2; the reason is that through k, a common index query, you need to search the k index tree first, and then get a value of 1 for the primary key ID, and then search the ID index tree. Although indexes are used in this process, the underlying layer actually performs two index queries, which is called returning the table.

In other words, queries based on non-primary key indexes need to scan one more index tree. Therefore, we should try our best to use primary key query in our application.

The amount of data in my table is relatively small, if the amount of data is large, you can clearly see the time taken by the two queries, and it is obvious that using the primary key to query is more efficient.

This is the end of the content of "how to query the table in MySQL". Thank you for 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.