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

In this issue, Xiaobian will bring you about how to sort in MySQL. The article is rich in content and analyzed and described from a professional perspective. After reading this article, I hope you can gain something.

When we execute Mysql's Explain statement, we often see such a Using filesort. So, is Mysql sorted in memory or on disk? If we were the designers of Mysql, what would we do? First of all, sorting in memory is certainly much faster than sorting on disk. But memory resources are limited, after all, if we scan enough rows, this time the size of the data may have exceeded the memory, it is very difficult to sort in memory, this time we can only use disk to sort.

Yes, Mysql is also designed like this. Mysql has a configuration item, sort_buffer_size. If the amount of data we select is less than this number, then the data will be sorted in memory. Otherwise, Mysql will split the data into many temporary files. The size of each temporary file will be less than sort_buffer_size. In other words, if the sort_buffer_size is smaller, the more temporary files will be split, which is why we choose to store the machine memory as large as possible. Mysql sorted multiple temporary files, and finally do a merge sort, you can arrange all the records.

I believe that if you have a large number of columns in your database, try not to use Select * but only what fields you need, especially in the sorting of databases. If we have a particularly large number of columns and a large number of rows that meet the conditions, Mysql will have to sort with a more extreme sorting algorithm. For each row of data, only the primary key id and the sorted field will be taken. Then sort, and finally, take the results to meet the conditions back to the table query other fields, and then return the results. Compared with the original scheme above, this Rowid sorting method has one more return to the table, so the query efficiency is greatly reduced.

So, what can we do to optimize the order? We all know that Innodb's index is actually a multi-fork sort tree, so wouldn't it be nice if we could get results on existing sort trees?! Therefore, if we want to query the fields that have been sorted are all on the existing index and satisfy the leftmost prefix principle, then we can reduce the return table once, thus greatly improving efficiency. So, what if your Sql statement satisfies this optimization? If your statement contains OrderBy, but the result of Explain is only UseIndex, it means that the index is overwritten.

The above is how to sort in MySQL shared by Xiaobian. If there is a similar doubt, please refer to the above analysis for understanding. If you want to know more about it, please pay attention to 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.