select返回記錄的順序

來源:互聯網
上載者:User
select
select返回記錄的順序

中興通訊重慶研究所 遊波 吳育紅

關鍵詞: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得到的記錄

 

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

從執行計畫來看,按索引掃描和按索引ROWID方式訪問。

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

從執行計畫來看,按索引掃描和按索引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均按聚簇索引欄位順序返回記錄。

 

參考文獻和資料:

1.《Oracle Freelist和HWM原理探討及相關效能最佳化》,遊波

2.《關於block中資料的儲存和重組的探究》,http://www.itpub.net

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

4.《如何找出一個表的最後一行?物理插入順序》,http://www.itpub.net

5.《Oracle 9i for windows nt/2000資料系統培訓教程》,清華大學出版社

6.《Microsoft SQL Server 2000技術內幕》,北京大學出版社

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

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


相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。