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

This article mainly introduces the relevant knowledge of "how much is the performance consumption of MySQL return table". The editor shows you the operation process through an actual case, the operation method is simple and fast, and it is practical. I hope that this article "how much performance consumption of MySQL return table is" can help you solve the problem.

1 performance consumption of return table

Whether a single-column index or a federated index, an index corresponds to a separate B+ index tree, and the index tree node contains only:

Field values in the index

Primary key value

Even if you find the required data according to the conditions according to the index tree, it is only the values and primary key values of a few fields in the index. In case you create a select *, you need to go back to the table and look for it in the clustered index according to the primary key. The leaf node of the clustered index is the data page. Only when you find the data page can you read out all the field values of a row of data.

So it's similar.

Select * from table order by xx1,xx2,xx3

We have to extract all the data in order from the index tree of the federated index, and then take a clustered index search of a primary key for each piece of data, which has low performance.

Sometimes the MySQL execution engine may think that if you are similar

Select * from table order by xx1,xx2,xx3

It is equivalent to having to scan all the data of the federated index and the clustered index, so it is better to scan the whole table without the federated index, so that only one primary key index needs to be scanned.

But if it looks like:

Select * from table order by xx1,xx2,xx3 limit 10

Then the execution engine knows that you first scan the index tree of the federated index, get 10 pieces of data, and then look for 10 pieces of data in the clustered index 10 times, then you will still take the federated index.

2 overlay index

An overlay index is not an index, but a way to query based on an index, that is, for a similar

Select xx1,xx2,xx3 from table order by xx1,xx2,xx3

If you only need the values of a few fields in the federated index, you only need to scan the index tree of the federated index without going back to the table to find other fields.

So when you use a federated index, pay attention to whether it may cause a large number of tables to be returned to the clustered index. If you return to the clustered index too many times, it may give you a full table scan instead of the federated index.

As far as possible, specify only the fields you need in SQL, rather than select *, it's best to overwrite the index directly.

Even if you inevitably want to return the table, you should limit the number of times to return the table with limit and where as much as possible, filter a small amount of data from the federated index, and then return to the table for better performance.

This is the end of the content about "what is the performance consumption of MySQL return table". Thank you for your reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

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.