What is the performance consumption of MySQL table return?

1 Performance consumption of table return

Regardless of single column index or joint index, an index corresponds to an independent B index tree. The index tree nodes only contain:

• Field values ​​in the index

• Primary key value

Even if the required data is found according to the conditions according to the index tree, It is only the values ​​​​of a few fields in the index and the primary key value. If you make a select *, then there will be other fields, and you need to go back to the table and look for them in the clustered index based on the primary key. , the leaf node of the clustered index is the data page. Only by finding the data page can all the field values ​​of a row of data be read out.
So it is similar to

select * from table order by xx1,xx2,xx3

We have to retrieve all the data in order from the index tree of the joint index, and then perform a primary key clustered index search for each piece of data, which is not very performant.
SometimesMySQLThe execution engine may think that if you are like

select * from table order by xx1,xx2,xx3

it is equivalent to scanning all the data of the joint index and clustered index, both indexes, then it is not as good as Instead of using the joint index, you can directly scan the entire table, so you only need to scan one primary key index.

But if the shape is like:

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

The execution engine will know that you first scan the index tree of the joint index, get 10 pieces of data, and then compare the 10 pieces of data in Just search 10 times in the clustered index, then the joint index will still be used.

2 Covering index

Covering index is not an index, it is just a method based on index query, that is, only a few fields in the joint index are needed for things like

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

value, then you only need to scan the index tree of the joint index without going back to the table to find other fields. This query method is a covering index.
So when you use a joint index, pay attention to whether it may cause a large number of tables to be returned to the clustered index. If the number of times to return the table to the clustered index is too many, you may be directly scanned of the whole table without using the joint index. .
If possible, specify only the fields you need in SQL instead of violently select *. It is best to go directly to the covering index.
Even if it is unavoidable to return the table, you should try your best to use limit and where to limit the number of table returns, and then filter a small number of data from the joint index, and then return table, so the performance is better.

The above is the detailed content of What is the performance consumption of MySQL table return?. For more information, please follow other related articles on the PHP Chinese website!