關鍵詞：select，順序，優化，備份，掃描，索引
文章摘要：
當我們執行了select語句，select返回的記錄的順序對我們編程方式有較大影響，對數據庫記錄備份清除以及sql性能優化都有很大的關系。因此有必要明確select返回記錄的順序。本文按數據庫分類討論oracle/sybase/sql server返回記錄的順序，從原理探討三種數據庫各自的特點，并著重探討了這些差異對數據查詢及記錄備份的影響。
縮略語：
IAM：index allocation map
PFS：page free space
1.簡介
當我們執行了select語句，select返回的記錄的順序對我們編程方式有較大影響，對數據庫記錄備份清除以及sql性能優化都有很大的關系。因此有必要明確select返回記錄的順序。
select返回記錄的順序與數據庫類型有很大關系，因此以下按數據庫類型分別討論。本文主要討論了oracle/sybase/sql server返回記錄的順序，從原理探討三種數據庫各自的特點，并著重探討了這些差異對數據查詢及記錄備份的影響。
2. oracle
以下假設數據庫查詢優化方式均為基于rule的方式，ORACLE 采用兩種訪問表中記錄的方式：
a. 全表掃描 (Full Table Scan)
全表掃描就是順序地訪問表中每條記錄. ORACLE采用一次讀入多個數據塊(database block)的方式優化全表掃描。
b. 通過ROWID訪問表
你可以采用基于ROWID的訪問方式情況，提高訪問表的效率，ROWID包含了表中記錄的物理位置信息。ORACLE采用索引(INDEX)實現了數據和存放數據的物理位置(ROWID)之間的聯系。通常索引提供了快速訪問ROWID的方法，因此那些基于索引列的查詢就可以得到性能上的提高。通常表現為按索引掃描。(Index Scan)
2.1全表掃描
如果select語句不能使用索引，則Oracle按全表掃描方式讀取數據塊，對于返回的結果集，oracle按rowid的大小順序來返回記錄。因此 select * from mytable 與 select * from mytable order by rowid效果是一樣的
可以通過select rowid from table得到rowid偽列，數據類型為ROWID類型。使用查詢語句返回的是ROWID的擴展格式（Extended Rowid）。擴展格式的ROWID由18個字符組成。這18個字符可以按照OOOOOO.FFF.BBBBBB.SSS的格式分為4組。分別代表數據對象編號（Data Object Number）,數據文件編號（Datafile Number），數據塊編號（Data Block Number）,記錄或記錄片斷的塊內行號。
必須說明的是，并不是后插入記錄的rowid就越大，有可能后插入的記錄rowid還要小。下面給出兩個論點加以證明：
1．后插入的記錄塊內行號可能大，也可能小
根據我們的試驗，假設現在表中有三條記錄假設文件號相同，按塊號，行號排列如下：
108 0
108 1
108 2
刪除中間一條記錄后，得到
108 0
108 2
再增加一條記錄，可能會得到
108 0
108 1 <---新增加的記錄
108 2
也可能是
108 0
108 2
108 3 <---新增加的記錄
兩種情況均有可能出現，取決于oracle塊內的分配算法。關于該情況的更深入的分析可以參見文獻2。
2．后插入的記錄的塊號有可能大，有可能小
插入記錄的塊號并不是線性增加的，而是受FreeList控制。有關FreeList的理論和算法可以參見文獻1。
因此對于全表掃描可以得出以下結論：
1. 在oracle中 select * from mytable不能保證返回的記錄順序是按插入的先后順序，而是按rowid順序。
rowid的順序與記錄行存儲的“物理序”一致。在沒有索引情況下，select作全表掃描，是按“物理序”，此時select 返回記錄按“物理序”最快。
2. 對于已經插入的記錄其ROWID不會發生變化。
如果全表掃描方式下，直接使用rownum作為選擇條件，根據結論1，兩次得到的記錄可能是不一樣的。如果sql有時間條件或其他條件作為sql語句輔助的篩選（排出當前插入的值），那么再用rownum作為選擇條件，則返回的記錄及記錄的順序均是一樣的。
結論2的特性可用于某些日志表的清除-備份機制中。對于某些日志表為了提高insert性能，可能沒有索引，并且在存儲過程中對這些日志表進行清除和備份。利用insert into select 先將部分記錄選入到備份表中，再用delete語句刪除日志表中的記錄。通過rownum來控制操作的行數，避免回滾段問題，通過時間條件來實施結論2，保證記錄一致。
2.2按索引掃描
對于一段范圍的按索引選擇，在oracle內部表現為索引葉節點的掃描，索引葉節點通常已經排序并且葉節點之間存在指針，便于掃描。由于此時select按索引掃描表，因此返回的記錄就按“索引序”排列。
利用上述特征，對于按索引掃描可以有以下的應用方式：
1．通過索引可以使返回記錄事先排序。
在oracle中使用索引就可以使返回的記錄得到排序，而無需再使用order by。對于不同的排序方式可以用不同的索引完成，通過hint/*+*/指示可以控制索引按不同的掃描方式工作，從而達到不同的效果。如/*+INDEX(TABLE INDEX_NAME)*/或/*+INDEX_DESC(TABLE INDEX_NAME)*/指示按索引升序掃描或按索引降序掃描，從而實現返回的記錄按字段的升序排列或按字段的降序排列。
例如對于表T(a int,b int)在a上有索引index_a，b上有索引b
則select * from t得到的記錄
AB
1943
211
310
58
112
select /*+INDEX(T INDEX_A)*/* from t where a>0 或者
select * from t where a>0 order by a
AB
310
58
112
1943
211
從執行計劃來看，按索引掃描和按索引ROWID方式訪問。
select /*+INDEX_DESC(T INDEX_B)*/* from t where b>0 或者
select * from t where b>0 order by b
AB
211
112
58
310
1943
從執行計劃來看，按索引掃描和按索引ROWID方式訪問。
2．通過以時間、流水號等字段為索引字段，可以使記錄實現按插入的順序返回
同樣利用上述特性，來說明2.1中的備份問題。當日志表有索引時，選擇限定掃描范圍的索引字段，使之保證后插入的記錄是在結果集后面的，如時間或流水號等，該順序就保證了按rownum控制行數時insert和delete操作的記錄是完全一致的，同時基于索引的掃描保證了sql的性能。
3.sybase
不管你的select 語句中是否在where后面使用了索引，sybase均可能基于代價對索引的使用進行調整。即使沒有where語句也有可能使用索引，即使有where語句也有可能不用索引。當然，如果表本身就沒有創建任何索引就肯定不會使用到索引。
3.1沒有索引的表
沒有索引的表在稱為堆表。堆表在sysindexes表中有一條對應的記錄，其indid=0。first字段表示堆表的首頁，root表示堆表的尾頁。堆表中所有的數據頁形成從sysindex.first <-> sysindex.root的雙向鏈表。
對于插入記錄，插入到堆表中的所有數據會加到該表的尾部。sybase 利用sysindex表的indid（=0）和root值，找出該表的最后一個數據頁。如果在該頁上有空間，在數據的尾部插入新的記錄行。如果最后一頁上沒有可獲得的空間時，如果在該擴展單元的下一頁有可獲得的空間，這是用它；如果最后一頁已經是擴展單元的最后一頁，則開始使用一個新的擴展單元，對于新加入的頁總是會鏈到鏈表的尾部，同時更新sysindex.root的值。
對于記錄刪除，當刪除一條記錄時，頁內緊隨被刪除記錄后的記錄向該頁前部移動，所有未使用的空間相鄰地保留在頁的底部。當一頁中所有行均被刪除，這一頁就會脫離該堆表的數據鏈。
對于更新，堆表按下面的原則：
如果行的長度沒有變化,就在原來的行上直接更新，并且沒有頁內數據的移動。
如果行的長度變化，并且頁的空閑空間足夠。行還是在頁上的相同位置，但是其它行將上移或下移以保持頁內行的連續。
如果該頁不能容納行。在Allpages-locked堆表中，行會被刪除，并且“新”行插入到最后頁。Data-only-lockedthe 堆表中，行插入到另外的頁中，在原來的位置采用轉向指針指到該頁面，這樣保證行的ID位置不變。
對于掃描，按sysindex.first <-> sysindex.root鏈表方式讀取數據頁。
對于堆表，根據上述插入、刪除、更新、掃描特性，可以得到下面的結論：
1．對于不帶任何索引的堆表，如果確保不使用update，或確保update不產生插入操作，就可以放心的使用select 完成自然排序，此時記錄按插入的先后順序返回。
3.2有索引的表
對于sybase執行計劃沒有帶索引的表,select返回記錄的順序和堆表掃描返回的順序相同。
對于sybase執行計劃帶索引的表，select 按索引字段的順序返回記錄。sybase將索引組織為 B 樹。索引內的每一頁包含一個頁首，頁首后面跟著索引行。每個索引行都包含一個鍵值以及一個指向較低級頁或數據行的指針。索引的每個頁稱為索引節點。B 樹的頂端節點稱為根節點。索引的底層節點稱為葉節點。每級索引中的頁鏈接在雙向鏈接列表中。
對于有索引的表，得到以下結論：
1．以通過控制索引來控制查詢方式，從而控制返回順序。
如我們可以通過(index index_name)來指定對某個索引的使用，從而達到按索引index_name排序。也可以使用(index 0)指示強制不使用索引，從而使返回的記錄順序按堆表方式。
2．如何沒有強制指定索引，不管你的select 語句中是否在where后面使用了索引，sybase均可能基于代價對索引的使用進行調整。由于sybase基于代價執行計劃會對索引的使用進行調整，因此不能像oracle那樣利用非聚簇索引完成返回記錄的自然排序，這時最好加上order by以保證排序的準確。
3．如果需要排序的字段是聚簇索引，那么就可以放心使用該索引完成排序。這時，不論執行計劃怎樣，sybase均按聚簇索引字段順序返回記錄。對于聚簇索引表，在插入數據時，會引起頁內部分記錄（值大的記錄）的移動，通過移動sybase保證了數據的物理順序與聚簇索引順序一致。
4.Ms Sql Server
不管你的select 語句中是否在where后面使用了索引，Sql Server均可能基于代價對索引的使用進行調整。即使沒有where語句也有可能使用索引，即使有where語句也有可能不用索引。當然，如果表本身就沒有創建任何索引就肯定不會使用到索引。
4.1沒有索引的表
沒有索引的表在稱為堆表或堆集。堆集使用 IAM管理擴展盤區，多個IAM形成IAM鏈。堆集在 sysindexes 內有一行，其 indid = 0。sysindexes.FirstIAM 列指向 IAM 頁鏈的 IAM 首頁，IAM 頁鏈管理分配給堆集的空間。SQL Server 2000 使用 IAM 頁在堆集中瀏覽。堆集內的數據頁和行沒有任何特定的順序，也不鏈接在一起。數據頁之間唯一的邏輯連接是記錄在 IAM 頁內的連接。
對于插入操作，當SQL Server 2000 需要插入新行而當前頁沒有可用空間時，它使用 IAM 和 PFS 頁查找具有足夠空間容納該行的頁。SQL Server 使用 IAM 頁查找分配給對象的擴展盤區。對于每個擴展盤區，SQL Server 搜索 PFS 頁以查看是否有一頁具有足夠的空間容納這一行。
SQL Server 只有當無法在現有的擴展盤區內快速找到一頁有足夠空間容納正插入的行時，才給對象分配新的擴展盤區。SQL Server 使用按比例分配算法，從文件組內的可用擴展盤區中分配擴展盤區。如果一個文件組有兩個文件，其中一個的可用空間是另一個的兩倍，那么每從后者分配一頁，就從前者分配兩頁。這意味著文件組內的每個文件應該有近似的空間使用百分比。
對于刪除操作，在堆表中，即使刪除了記錄，該記錄所在頁不會作頁內移動。
對于數據更新，SQL Server可以采用多種方式來進行。更新可能是現場發生的，也可能是以先刪除然后插入的方式進行的，還可以是通過查詢處理器或存儲引擎來管理更新。但是在堆表中，總是采用現場更新方式，對于更新的內容原來的頁不能容納的情況，sql server 2000采用轉向指針處理，保證了更新后該記錄位置的不變。
通過掃描 IAM 頁可以對堆集進行表掃描或串行讀，以找到容納這個堆集的頁的擴展盤區。因為 IAM 按擴展盤區在數據文件內存在的順序表示它們，所以這意味著串行堆集掃描一律沿每個文件進行。
根據上述堆表的插入、更新、刪除、掃描原則，可以得到以下的結論：
1．使用 IAM 頁設置掃描順序意味著堆集中的行一般不按照插入的順序返回。
2．對于已經存在的記錄，記錄的位置（數據庫號，文件號，頁號，行號）不會變化。
結論2可應用到備份-清除機制中。如果日志表是沒有索引的堆表，就可以通過時間、流水號等字段排除當前插入的記錄，使select和delete兩次操作返回的結果集及順序完全一致，再通過set rowcount來控制每次操作的記錄條數，使得備份-清除操作能夠安全進行。
4.2有索引的表
對于Sql Server 執行計劃沒有帶索引的表,select返回記錄的順序和堆表掃描返回的順序相同。
對于Sql Server 執行計劃帶索引的表，select 按索引字段的順序返回記錄。SQL Server將索引組織為 B 樹。索引內的每一頁包含一個頁首，頁首后面跟著索引行。每個索引行都包含一個鍵值以及一個指向較低級頁或數據行的指針。索引的每個頁稱為索引節點。B 樹的頂端節點稱為根節點。索引的底層節點稱為葉節點。每級索引中的頁鏈接在雙向鏈接列表中。
對于有索引的表，得到以下結論：
1．可以通過控制索引來控制查詢方式，從而控制返回順序。
如我們可以通過with(index(index_name))來指定對某個索引的使用，從而達到按索引index_name排序。
2．如何沒有強制指定索引，不管你的select 語句中是否在where后面使用了索引，Sql Server均可能基于代價對索引的使用進行調整，即使沒有where語句也有可能使用索引，即使有where語句也有可能不用索引。不管你的delete 語句中是否在where后面使用了索引，Sql Server均可能基于代價對索引的使用進行調整，即使沒有where語句也有可能使用索引，即使有where語句也有可能不用索引。帶相同where語句的select 和 delete 執行計劃很可能不一樣。
因此select 和 delete 得到的記錄順序很可能不一致，如果要選取前n條記錄,那么得到的記錄集盡管條數一致但內容不一致。盡管我們可以通過with(index(index_name))來強制select對索引的使用，但delete卻不能夠強制指定索引，因為delete涉及對索引本身的刪除。
這種情況下，如果數據庫的性能夠好，要備份的數據不多，就不要使用set rowcount來控制條數。但如果確需要控制一次刪除的條數，可以直接在where條件中控制更小的范圍，如時間范圍控制到小時，一天的數據通過24小時的循環來備份。
要么采用DTS作備份。
3．如果需要排序的字段是聚簇索引，那么就可以放心使用該索引完成排序。這時，不論執行計劃怎樣，sql server均按聚簇索引字段順序返回記錄。