(轉(zhuǎn)自：http://blog.chinaunix.net/u2/60332/showart_495257.html)

?

關(guān)鍵詞： select，順序，優(yōu)化，備份，掃描，索引

文章摘要：

?? 當(dāng)我們執(zhí)行了 select 語(yǔ)句， select 返回的記錄的順序?qū)ξ覀兙幊谭绞接休^大影響，對(duì)數(shù)據(jù)庫(kù)記錄備份清除以及 sql 性能優(yōu)化都有很大的關(guān)系。因此有必要明確 select 返回記錄的順序。本文按數(shù)據(jù)庫(kù)分類討論 oracle/sybase/sql server 返回記錄的順序，從原理探討三種數(shù)據(jù)庫(kù)各自的特點(diǎn)，并著重探討了這些差異對(duì)數(shù)據(jù)查詢及記錄備份的影響。

縮略語(yǔ)：

IAM ： index allocation map

PFS ： page free space

1. 簡(jiǎn)介

?????? 當(dāng)我們執(zhí)行了 select 語(yǔ)句， select 返回的記錄的順序?qū)ξ覀兙幊谭绞接休^大影響，對(duì)數(shù)據(jù)庫(kù)記錄備份清除以及 sql 性能優(yōu)化都有很大的關(guān)系。因此有必要明確 select 返回記錄的順序。

select 返回記錄的順序與數(shù)據(jù)庫(kù)類型有很大關(guān)系，因此以下按數(shù)據(jù)庫(kù)類型分別討論。本文主要討論了 oracle/sybase/sql server 返回記錄的順序，從原理探討三種數(shù)據(jù)庫(kù)各自的特點(diǎn)，并著重探討了這些差異對(duì)數(shù)據(jù)查詢及記錄備份的影響。

2. oracle

以下假設(shè)數(shù)據(jù)庫(kù)查詢優(yōu)化方式均為基于 rule 的方式， ORACLE 采用兩種訪問(wèn)表中記錄的方式：

　　 a. 全表掃描 ( Full Table Scan)

　　全表掃描就是順序地訪問(wèn)表中每 條記錄. ORACLE采用一次讀入多個(gè)數(shù)據(jù)塊(database block)的方式優(yōu)化全表掃描。

　　b. 通過(guò)ROWID訪問(wèn)表

你可以采用基于 ROWID 的訪問(wèn)方式情況，提高訪問(wèn)表的效率， ROWID 包含了表中記錄的物理位置信息。 ORACLE 采用索引 (INDEX) 實(shí)現(xiàn)了數(shù)據(jù)和存放數(shù)據(jù)的物理位置 (ROWID) 之間的聯(lián)系。通常索引提供了快速訪問(wèn) ROWID 的方法，因此那些基于索引列的查詢就可以得到性能上的提高。通常表現(xiàn)為按索引掃描。 (Index Scan)

2.1全表掃描

如果 select 語(yǔ)句不能使用索引，則 Oracle 按全表掃描方式讀取數(shù)據(jù)塊，對(duì)于返回的結(jié)果集， oracle 按 rowid 的大小順序來(lái)返回記錄。因此 select * from mytable 與 select * from mytable order by rowid 效果是一樣的

?????? 可以通過(guò) select rowid from table 得到 rowid 偽列，數(shù)據(jù)類型為 ROWID 類型。使用查詢語(yǔ)句返回的是 ROWID 的擴(kuò)展格式（ Extended Rowid ）。擴(kuò)展格式的 ROWID 由 18 個(gè)字符組成。這 18 個(gè)字符可以按照 OOOOOO.FFF.BBBBBB.SSS 的格式分為 4 組。分別代表數(shù)據(jù)對(duì)象編號(hào)（ Data Object Number ） , 數(shù)據(jù)文件編號(hào)（ Datafile Number ），數(shù)據(jù)塊編號(hào)（ Data Block Number ） , 記錄或記錄片斷的塊內(nèi)行號(hào)。

?????? 必須說(shuō)明的是，并不是后插入記錄的 rowid 就越大，有可能后插入的記錄 rowid 還要小。下面給出兩個(gè)論點(diǎn)加以證明：

1 ．后插入的記錄塊內(nèi)行號(hào)可能大，也可能小

根據(jù)我們的試驗(yàn)，假設(shè)現(xiàn)在表中有三條記錄假設(shè)文件號(hào)相同，按塊號(hào)，行號(hào)排列如下：

108 0

108 1

108 2

刪除中間一條記錄后，得到

108 0

108 2

再增加一條記錄，可能會(huì)得到

108 0

108 1? <--- 新增加的記錄

108 2

也可能是

108 0

108 2

108 3? <--- 新增加的記錄

兩種情況均有可能出現(xiàn)，取決于 oracle 塊內(nèi)的分配算法。關(guān)于該情況的更深入的分析可以參見(jiàn)文獻(xiàn) 2 。

2 ．后插入的記錄的塊號(hào)有可能大，有可能小

?????? 插入記錄的塊號(hào)并不是線性增加的，而是受 FreeList 控制。有關(guān) FreeList 的理論和算法可以參見(jiàn)文獻(xiàn) 1 。

??????

因此對(duì)于全表掃描可以得出以下結(jié)論：

1.???????? 在 oracle 中 select * from mytable 不能保證返回的記錄順序是按插入的先后順序，而是按 rowid 順序。

rowid 的順序與記錄行存儲(chǔ)的“物理序”一致。在沒(méi)有索引情況下， select 作全表掃描，是按“物理序”，此時(shí) select 返回記錄按“物理序”最快。

2.???????? 對(duì)于已經(jīng)插入的記錄其 ROWID 不會(huì)發(fā)生變化。

如果全表掃描方式下，直接使用 rownum 作為選擇條件，根據(jù)結(jié)論 1 ，兩次得到的記錄可能是不一樣的。如果 sql 有時(shí)間條件或其他條件作為 sql 語(yǔ)句輔助的篩選（排出當(dāng)前插入的值），那么再用 rownum 作為選擇條件，則返回的記錄及記錄的順序均是一樣的。

結(jié)論 2 的特性可用于某些日志表的清除 - 備份機(jī)制中。對(duì)于某些日志表為了提高 insert 性能，可能沒(méi)有索引，并且在存儲(chǔ)過(guò)程中對(duì)這些日志表進(jìn)行清除和備份。利用 insert into select 先將部分記錄選入到備份表中，再用 delete 語(yǔ)句刪除日志表中的記錄。通過(guò) rownum 來(lái)控制操作的行數(shù)，避免回滾段問(wèn)題，通過(guò)時(shí)間條件來(lái)實(shí)施結(jié)論 2 ，保證記錄一致。

2.2按索引掃描

對(duì)于一段范圍的按索引選擇，在 oracle 內(nèi)部表現(xiàn)為索引葉節(jié)點(diǎn)的掃描，索引葉節(jié)點(diǎn)通常已經(jīng)排序并且葉節(jié)點(diǎn)之間存在指針，便于掃描。由于此時(shí) select 按索引掃描表，因此返回的記錄就按“索引序”排列。

利用上述特征，對(duì)于按索引掃描可以有以下的應(yīng)用方式：

1 ．通過(guò)索引可以使返回記錄事先排序。

在 oracle 中使用索引就可以使返回的記錄得到排序，而無(wú)需再使用 order by 。對(duì)于不同的排序方式可以用不同的索引完成，通過(guò) hint/*+*/ 指示可以控制索引按不同的掃描方式工作，從而達(dá)到不同的效果。如 /*+INDEX(TABLE INDEX_NAME)*/ 或 /*+INDEX_DESC(TABLE INDEX_NAME)*/ 指示按索引升序掃描或按索引降序掃描，從而實(shí)現(xiàn)返回的記錄按字段的升序排列或按字段的降序排列。

例如對(duì)于表 T(a int,b int) 在 a 上有索引 index_a ， b 上有索引 b

則 select * from t 得到的記錄

?

A	B
19	43
21	1
3	10
5	8
11	2

select /*+INDEX(T INDEX_A)*/* from t where a>0 或者

select * from t where a>0 order by a

A	B
3	10
5	8
11	2
19	43
21	1

從執(zhí)行計(jì)劃來(lái)看，按索引掃描和按索引 ROWID 方式訪問(wèn)。

select /*+INDEX_DESC(T INDEX_B)*/* from t where b>0 或者

select * from t where b>0 order by b

A	B
21	1
11	2
5	8
3	10
19	43

從執(zhí)行計(jì)劃來(lái)看，按索引掃描和按索引 ROWID 方式訪問(wèn)。

?

2 ．通過(guò)以時(shí)間、流水號(hào)等字段為索引字段，可以使記錄實(shí)現(xiàn)按插入的順序返回

?????? 同樣利用上述特性，來(lái)說(shuō)明 2.1 中的備份問(wèn)題。當(dāng)日志表有索引時(shí)，選擇限定掃描范圍的索引字段，使之保證后插入的記錄是在結(jié)果集后面的，如時(shí)間或流水號(hào)等，該順序就保證了按 rownum 控制行數(shù)時(shí) insert 和 delete 操作的記錄是完全一致的，同時(shí)基于索引的掃描保證了 sql 的性能。

3.sybase

不管你的 select 語(yǔ)句中是否在 where 后面使用了索引， sybase 均可能基于代價(jià)對(duì)索引的使用進(jìn)行調(diào)整。即使沒(méi)有 where 語(yǔ)句也有可能使用索引，即使有 where 語(yǔ)句也有可能不用索引。當(dāng)然，如果表本身就沒(méi)有創(chuàng)建任何索引就肯定不會(huì)使用到索引。

3.1沒(méi)有索引的表

?????? 沒(méi)有索引的表在稱為堆表。堆表在 sysindexes 表中有一條對(duì)應(yīng)的記錄，其 indid=0 。 first 字段表示堆表的首頁(yè)， root 表示堆表的尾頁(yè)。堆表中所有的數(shù)據(jù)頁(yè)形成從 sysindex.first <-> sysindex.root 的雙向鏈表。

?????? 對(duì)于插入記錄，插入到堆表中的所有數(shù)據(jù)會(huì)加到該表的尾部。 sybase 利用 sysindex 表的 indid （ =0 ）和 root 值， 找出該表的最后一個(gè)數(shù)據(jù)頁(yè)。如果在該頁(yè)上有空間，在數(shù)據(jù)的尾部插入新的記錄行。如果最后一頁(yè)上沒(méi)有可獲得的空間時(shí)，如果在該擴(kuò)展單元的下一頁(yè)有可獲得的空 間，這是用它；如果最后一頁(yè)已經(jīng)是擴(kuò)展單元的最后一頁(yè)，則開(kāi)始使用一個(gè)新的擴(kuò)展單元，對(duì)于新加入的頁(yè)總是會(huì)鏈到鏈表的尾部，同時(shí)更新 sysindex.root 的值。

?????? 對(duì)于記錄刪除，當(dāng)刪除一條記錄時(shí)，頁(yè)內(nèi)緊隨被刪除記錄后的記錄向該頁(yè)前部移動(dòng)，所有未使用的空間相鄰地保留在頁(yè)的底部。當(dāng)一頁(yè)中所有行均被刪除，這一頁(yè)就會(huì)脫離該堆表的數(shù)據(jù)鏈。

??? 對(duì)于更新，堆表按下面的原則：

·???????? 如果行的長(zhǎng)度沒(méi)有變化,就在原來(lái)的行上直接更新，并且沒(méi)有頁(yè)內(nèi)數(shù)據(jù)的移動(dòng)。

·???????? 如果行的長(zhǎng)度變化，并且頁(yè)的空閑空間足夠。行還是在頁(yè)上的相同位置，但是其它行將上移或下移以保持頁(yè)內(nèi)行的連續(xù)。

·???????? 如果該頁(yè)不能容納行。在Allpages-locked堆表中，行會(huì)被刪除，并且“新”行插入到最后頁(yè)。Data-only-lockedthe 堆表中，行插入到另外的頁(yè)中，在原來(lái)的位置采用轉(zhuǎn)向指針指到該頁(yè)面，這樣保證行的ID位置不變。

對(duì)于掃描，按 sysindex.first <-> sysindex.root 鏈表方式讀取數(shù)據(jù)頁(yè)。

對(duì)于堆表，根據(jù)上述插入、刪除、更新、掃描特性，可以得到下面的結(jié)論：

1． 對(duì)于不帶任何索引的堆表，如果確保不使用 update ，或確保 update 不產(chǎn)生插入操作，就可以放心的使用 select 完成自然排序，此時(shí)記錄按插入的先后順序返回。

?

3.2有索引的表

對(duì)于sybase執(zhí)行計(jì)劃沒(méi)有帶索引的表,select返回記錄的順序和堆表掃描返回的順序相同。

對(duì)于 sybase 執(zhí)行計(jì)劃帶索引的表， select? 按索引字段的順序返回記錄。 sybase 將索引組織為 B 樹(shù)。索引內(nèi)的每一頁(yè)包含一個(gè)頁(yè)首，頁(yè)首后面跟著索引行。每個(gè)索引行都包含一個(gè)鍵值以及一個(gè)指向較低級(jí)頁(yè)或數(shù)據(jù)行的指針。索引的每個(gè)頁(yè)稱為索引節(jié)點(diǎn)。 B 樹(shù)的頂端節(jié)點(diǎn)稱為根節(jié)點(diǎn)。索引的底層節(jié)點(diǎn)稱為葉節(jié)點(diǎn)。每級(jí)索引中的頁(yè)鏈接在雙向鏈接列表中。

?????? 對(duì)于有索引的表，得到以下結(jié)論：

1 ．以通過(guò)控制索引來(lái)控制查詢方式，從而控制返回順序。

?????? 如我們可以通過(guò) (index index_name) 來(lái)指定對(duì)某個(gè)索引的使用，從而達(dá)到按索引 index_name 排序。也可以使用 (index 0) 指示強(qiáng)制不使用索引，從而使返回的記錄順序按堆表方式。

2 ．如何沒(méi)有強(qiáng)制指定索引，不管你的 select 語(yǔ)句中是否在 where 后面使用了索引， sybase 均可能基于代價(jià)對(duì)索引的使用進(jìn)行調(diào)整。由于 sybase 基于代價(jià)執(zhí)行計(jì)劃會(huì)對(duì)索引的使用進(jìn)行調(diào)整，因此不能像 oracle 那樣利用非聚簇索引完成返回記錄的自然排序，這時(shí)最好加上 order by 以保證排序的準(zhǔn)確。

3 ．如果需要排序的字段是聚簇索引，那么就可以放心使用該索引完成排序。這時(shí)，不論執(zhí)行計(jì)劃怎樣， sybase 均按聚簇索引字段順序返回記錄。對(duì)于聚簇索引表，在插入數(shù)據(jù)時(shí)，會(huì)引起頁(yè)內(nèi)部分記錄（值大的記錄）的移動(dòng)，通過(guò)移動(dòng) sybase 保證了數(shù)據(jù)的物理順序與聚簇索引順序一致。

4.Ms Sql Server

?????? 不管你的 select 語(yǔ)句中是否在 where 后面使用了索引， Sql Server 均可能基于代價(jià)對(duì)索引的使用進(jìn)行調(diào)整。即使沒(méi)有 where 語(yǔ)句也有可能使用索引，即使有 where 語(yǔ)句也有可能不用索引。當(dāng)然，如果表本身就沒(méi)有創(chuàng)建任何索引就肯定不會(huì)使用到索引。

4.1沒(méi)有索引的表

沒(méi)有索引的表在稱為堆表或堆集。堆集使用 IAM 管理擴(kuò)展盤區(qū)，多個(gè) IAM 形成 IAM 鏈。 堆集在 sysindexes 內(nèi)有一行，其 indid = 0。sysindexes.FirstIAM 列指向 IAM 頁(yè)鏈的 IAM 首頁(yè)，IAM 頁(yè)鏈管理分配給堆集的空間。SQL Server 2000 使用 IAM 頁(yè)在堆集中瀏覽。堆集內(nèi)的數(shù)據(jù)頁(yè)和行沒(méi)有任何特定的順序，也不鏈接在一起。數(shù)據(jù)頁(yè)之間唯一的邏輯連接是記錄在 IAM 頁(yè)內(nèi)的連接。

對(duì)于插入操作，當(dāng)SQL Server 2000 需要插入新行而當(dāng)前頁(yè)沒(méi)有可用空間時(shí)，它使用 IAM 和 PFS 頁(yè)查找具有足夠空間容納該行的頁(yè)。SQL Server 使用 IAM 頁(yè)查找分配給對(duì)象的擴(kuò)展盤區(qū)。對(duì)于每個(gè)擴(kuò)展盤區(qū)，SQL Server 搜索 PFS 頁(yè)以查看是否有一頁(yè)具有足夠的空間容納這一行。

SQL Server 只有當(dāng)無(wú)法在現(xiàn)有的擴(kuò)展盤區(qū)內(nèi)快速找到一頁(yè)有足夠空間容納正插入的行時(shí)，才給對(duì)象分配新的擴(kuò)展盤區(qū)。SQL Server 使用按比例分配算法，從文件組內(nèi)的可用擴(kuò)展盤區(qū)中分配擴(kuò)展盤區(qū)。如果一個(gè)文件組有兩個(gè)文件，其中一個(gè)的可用空間是另一個(gè)的兩倍，那么每從后者分配一頁(yè)，就 從前者分配兩頁(yè)。這意味著文件組內(nèi)的每個(gè)文件應(yīng)該有近似的空間使用百分比。

對(duì)于刪除操作，在堆表中，即使刪除了記錄，該記錄所在頁(yè)不會(huì)作頁(yè)內(nèi)移動(dòng)。

對(duì)于數(shù)據(jù)更新，SQL Server可以采用多種方式來(lái)進(jìn)行。更新可能是現(xiàn)場(chǎng)發(fā)生的，也可能是以先刪除然后插入的方式進(jìn)行的，還可以是通過(guò)查詢處理器或存儲(chǔ)引擎來(lái)管理更新。但是 在堆表中，總是采用現(xiàn)場(chǎng)更新方式，對(duì)于更新的內(nèi)容原來(lái)的頁(yè)不能容納的情況，sql server 2000采用轉(zhuǎn)向指針處理，保證了更新后該記錄位置的不變。

通過(guò)掃描 IAM 頁(yè)可以對(duì)堆集進(jìn)行表掃描或串行讀，以找到容納這個(gè)堆集的頁(yè)的擴(kuò)展盤區(qū)。因?yàn)?/span> IAM 按擴(kuò)展盤區(qū)在數(shù)據(jù)文件內(nèi)存在的順序表示它們，所以這意味著串行堆集掃描一律沿每個(gè)文件進(jìn)行。

根據(jù)上述堆表的插入、更新、刪除、掃描原則，可以得到以下的結(jié)論：

1 ．使用 IAM 頁(yè)設(shè)置掃描順序意味著堆集中的行一般不按照插入的順序返回。

2 ．對(duì)于已經(jīng)存在的記錄，記錄的位置（數(shù)據(jù)庫(kù)號(hào)，文件號(hào)，頁(yè)號(hào)，行號(hào)）不會(huì)變化。

?????? 結(jié)論 2 可應(yīng)用到備份 - 清除機(jī)制中。如果日志表是沒(méi)有索引的堆表，就可以通過(guò)時(shí)間、流水號(hào)等字段排除當(dāng)前插入的記錄，使 select 和 delete 兩次操作返回的結(jié)果集及順序完全一致，再通過(guò) set rowcount 來(lái)控制每次操作的記錄條數(shù)，使得備份 - 清除操作能夠安全進(jìn)行。

4.2有索引的表

對(duì)于Sql Server 執(zhí)行計(jì)劃沒(méi)有帶索引的表,select返回記錄的順序和堆表掃描返回的順序相同。

對(duì)于 Sql Server 執(zhí)行計(jì)劃帶索引的表， select?? 按索引字段的順序返回記錄。 SQL Server 將索引組織為 B 樹(shù)。索引內(nèi)的每一頁(yè)包含一個(gè)頁(yè)首，頁(yè)首后面跟著索引行。每個(gè)索引行都包含一個(gè)鍵值以及一個(gè)指向較低級(jí)頁(yè)或數(shù)據(jù)行的指針。索引的每個(gè)頁(yè)稱為索引節(jié)點(diǎn)。 B 樹(shù)的頂端節(jié)點(diǎn)稱為根節(jié)點(diǎn)。索引的底層節(jié)點(diǎn)稱為葉節(jié)點(diǎn)。每級(jí)索引中的頁(yè)鏈接在雙向鏈接列表中。

對(duì)于有索引的表，得到以下結(jié)論：

1 ．可以通過(guò)控制索引來(lái)控制查詢方式，從而控制返回順序。

?????? 如我們可以通過(guò) with(index(index_name)) 來(lái)指定對(duì)某個(gè)索引的使用，從而達(dá)到按索引 index_name 排序。

2 ．如何沒(méi)有強(qiáng)制指定索引，不管你的 select 語(yǔ)句中是否在 where 后面使用了索引， Sql Server 均可能基于代價(jià)對(duì)索引的使用進(jìn)行調(diào)整，即使沒(méi)有 where 語(yǔ)句也有可能使用索引，即使有 where 語(yǔ)句也有可能不用索引。不管你的 delete 語(yǔ)句中是否在 where 后面使用了索引， Sql Server 均可能基于代價(jià)對(duì)索引的使用進(jìn)行調(diào)整，即使沒(méi)有 where 語(yǔ)句也有可能使用索引，即使有 where 語(yǔ)句也有可能不用索引。帶相同 where 語(yǔ)句的 select 和 delete 執(zhí)行計(jì)劃很可能不一樣。

?????? 因此 select 和 delete 得到的記錄順序很可能不一致，如果要選取前 n 條記錄 , 那么得到的記錄集盡管條數(shù)一致但內(nèi)容不一致。盡管我們可以通過(guò) with(index(index_name)) 來(lái)強(qiáng)制 select 對(duì)索引的使用，但 delete 卻不能夠強(qiáng)制指定索引，因?yàn)?/span> delete 涉及對(duì)索引本身的刪除。

?????? 這種情況下，如果數(shù)據(jù)庫(kù)的性能夠好，要備份的數(shù)據(jù)不多，就不要使用 set rowcount 來(lái)控制條數(shù)。但如果確需要控制一次刪除的條數(shù)，可以直接在 where 條件中控制更小的范圍，如時(shí)間范圍控制到小時(shí)，一天的數(shù)據(jù)通過(guò) 24 小時(shí)的循環(huán)來(lái)備份。

?????? 要么采用 DTS 作備份。

3 ．如果需要排序的字段是聚簇索引，那么就可以放心使用該索引完成排序。這時(shí)，不論執(zhí)行計(jì)劃怎樣， sql server 均按聚簇索引字段順序返回記錄。

?

參考文獻(xiàn)和資料：

1．《Oracle Freelist和HWM原理探討及相關(guān)性能優(yōu)化》，游波

2. 《 關(guān)于block中數(shù)據(jù)的存儲(chǔ)和重組的探究 》，http://www.itpub.net

3．《 怎樣按物理順序提取記錄? 》，http://www.itpub.net

4．《 如何找出一個(gè)表的最后一行？物理插入順序 》，http://www.itpub.net

5 ．《 Oracle 9i for windows nt/2000 數(shù)據(jù)系統(tǒng)培訓(xùn)教程 》,清華大學(xué)出版社

6. 《 Microsoft SQL Server 2000 技術(shù)內(nèi)幕 》,北京大學(xué)出版社

7． 《 Heaps of data: tables without clustered indexes 》

上述部分文章在我的blog網(wǎng)站http://blog.csdn.net/youbo2004 上可找到。

總結(jié)

以上是生活随笔為你收集整理的select返回记录的顺序的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。