SQL Server排序算法 [英] SQL Server Sorting Algorithm

问题描述

有很多排序算法，但是我想知道当我们使用Order by而没有Order by时在SQL Server中使用哪种算法.

There are so many sorting algorithms, but I want to know which algorithm is used in SQL Server when we use Order by and without Order by.

推荐答案

我想这取决于您选择按BY排序的列.如果为，则整数算法与用于字符串的算法不同.另一个猜测是该列是否具有索引也至关重要.

I guess it depends on the column that you choose to order BY. If is integer is a different algorithm than for strings. Another guess will be that having or not having indexes for that column will also be of vital importance.

这是用于按文本顺序进行排序的算法mysql .

原始的文件排序算法有效 如下所示:根据 键或通过表扫描.行 与WHERE子句不匹配的是 跳过了.对于每一行，存储一对 缓冲区中的值(排序键和 行指针).的大小 缓冲区是值 sort_buffer_size系统变量.什么时候 缓冲区已满，运行qsort (快速排序)并存储结果 在一个临时文件中.保存一个指向 排序的块. (如果所有对都适合 进入排序缓冲区，没有临时 文件已创建.)重复上述步骤 步骤，直到读取了所有行. 最多合并MERGEBUFF (7)区域到另一个区域 临时文件.重复直到全部 第一个文件中的块位于 第二个文件.重复以下步骤 直到少于MERGEBUFF2 (15)个街区.在最后 多次合并，仅指向 行(排序键的最后一部分)是 写入结果文件.阅读 使用行按排序顺序排列行 结果文件中的指针.到 优化这一点，我们大篇幅地阅读 行指针，对其进行排序，然后使用 他们按排序顺序读取行 放入行缓冲区.的大小 缓冲区是值 read_rnd_buffer_size系统变量. 此步骤的代码在 sql/records.cc源文件.

The original filesort algorithm works as follows: Read all rows according to key or by table scanning. Rows that do not match the WHERE clause are skipped. For each row, store a pair of values in a buffer (the sort key and the row pointer). The size of the buffer is the value of the sort_buffer_size system variable. When the buffer gets full, run a qsort (quicksort) on it and store the result in a temporary file. Save a pointer to the sorted block. (If all pairs fit into the sort buffer, no temporary file is created.) Repeat the preceding steps until all rows have been read. Do a multi-merge of up to MERGEBUFF (7) regions to one block in another temporary file. Repeat until all blocks from the first file are in the second file. Repeat the following until there are fewer than MERGEBUFF2 (15) blocks left. On the last multi-merge, only the pointer to the row (the last part of the sort key) is written to a result file. Read the rows in sorted order by using the row pointers in the result file. To optimize this, we read in a big block of row pointers, sort them, and use them to read the rows in sorted order into a row buffer. The size of the buffer is the value of the read_rnd_buffer_size system variable. The code for this step is in the sql/records.cc source file.

这篇关于SQL Server排序算法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！