Shulou(Shulou.com)05/31 Report--

This article mainly shows you the "example analysis of MySQL algorithm", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and study the "example analysis of MySQL algorithm" this article.

A brief Analysis of MySQL algorithm

In MySQL, there is only one Join algorithm, the famous Nested Loop Join, which does not have many other Hash Join and Sort Merge Join. As the name implies, Nested Loop Join actually drives the result set of the table as the basic data loop, and then queries the data in the next table one by one through the data in the result set as a filter condition, and then merges the results. If there is a third participant in the Join, then use the Join result set of the first two tables as the circular base data, and once again query the data in the third table through the circular query condition, and so on.

At present, MySQL can sort data through two algorithms.

1. Take out the fields used for sorting conditions that meet the filtering conditions and the row pointer information that can be directly located to the row data, carry out the actual sorting operation in SortBuffer, and then use the sorted data to return the data of other fields requested by the client to the table according to the row pointer information, and then return it to the client.

two。 According to the filter condition, the data of the sorted field and all other fields requested by the client are taken out at once, and the fields that do not need to be sorted are stored in a memory area, and then the sorted fields and row pointer information are sorted in Sort Buffer. Finally, the sorted row pointer is matched with the row pointer information stored in the memory area and other fields to match and merge the result set, and then returned to the client in order.

Increase the setting of max_length_for_sort_data parameters

In MySQL, the decision to use the first old sorting algorithm or a new improved algorithm is based on the parameter max_length_for_sort_data. When the maximum length of all our returned fields is less than this parameter value, MySQL will choose the improved sorting algorithm, and vice versa, choose the old algorithm. So, if we have enough memory for MySQL to store the unsorted fields that need to be returned, we can increase the value of this parameter to let MySQL choose to use the improved sorting algorithm.

The above is all the content of this article "sample Analysis of MySQL algorithm". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.