MySQL algorithm analysis _ MySQL

MySQL algorithm analysis
MySQL algorithm analysis in MySQL, there is only one Join algorithm, that is, the famous Nested Loop Join. it does not have Hash Join provided by many other databases, nor does it have Sort Merge Join. As the name suggests, Nested Loop Join uses the result set of the driving table as the basic data of the Loop, and then uses the data in the result set one by one as the filter condition to query data in the next table, then merge the results. If the third Join clause is used, the Join result set of the first two tables is used as the basic cyclic data, and the data in the third table is queried again through the cyclic query condition. Currently, MySQL can use two algorithms to sort data. 1. retrieve 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, and perform the actual sorting operation in SortBuffer, then, the sorted data is used to return data from other fields requested by the client to the table based on the row pointer information, and then returned to the client; 2. retrieve the data of sorting fields and all other fields requested by the client at a time based on the filter conditions, and store the fields that do not need to be sorted in a memory area, then, Sort the sorting field and row pointer information in Sort Buffer, and then use the sorted row pointer to match and merge the result set with the row pointer information stored in the memory area and other fields, then return to the client in order. Increase the setting of the max_length_for_sort_data parameter. in MySQL, it is determined by the max_length_for_sort_data parameter to use the first old-fashioned sorting algorithm or the new improved algorithm. When the maximum length of all returned fields is smaller than this parameter value, MySQL selects the improved sorting algorithm. Otherwise, it selects the old algorithm. Therefore, if we have enough memory for MySQL to store non-sorted fields to be returned, we can increase the value of this parameter so that MySQL can choose to use the sort algorithm of the latest version. Author bengdabitsCN.com