看Sybase官方手冊(cè)學(xué)索引工作原理

Sybase數(shù)據(jù)庫(kù)簡(jiǎn)介

Sybase公司成立于1984年，5自學(xué)網(wǎng)，公司名稱“Sybase”取自“system”和“database”相結(jié)合的含義。Sybase公司的第一個(gè)關(guān)系數(shù)據(jù)庫(kù)產(chǎn)品是1987年5月推出的Sybase SQLServer1.0。Sybase首先提出Client/Server 數(shù)據(jù)庫(kù)體系結(jié)構(gòu)的思想，并率先在Sybase SQLServer 中實(shí)現(xiàn)。Sybase覺(jué)得單靠一家力量，難以把SQLServer(那時(shí)不叫ASE)做到老大，于是聯(lián)合微軟，共同開(kāi)發(fā)。后來(lái)1994年，兩家公司合作終止。截止此時(shí)，應(yīng)該是都擁有一套完全相同的SQLServer代碼。Sybase SQLServer后來(lái)為了與微軟的MS SQL Server相區(qū)分，改名叫：Sybase ASE(Adaptive Server Enterprise)，其實(shí)，應(yīng)該改名字的是微軟。Sybase ASE仍然保持著大型數(shù)據(jù)庫(kù)廠商的地位。在電信、交通、市政、銀行等領(lǐng)域，擁有強(qiáng)大的市場(chǎng)。

不過(guò)似乎多是大公司的遺留系統(tǒng)。正是上面的歷史原因，Sybase中不少語(yǔ)法跟MS SQLServer的T-SQL很像。現(xiàn)在網(wǎng)上的Sybase資料和文檔比較少，多是很多年以前的了。這個(gè)Sybase的在線幫助手冊(cè)算是比較完整的了，地址是。

以下是手冊(cè)里第十二章索引如何工作的，對(duì)Sybase索引的工作原理講解的比較易懂。并且大部分理論應(yīng)該同樣適用于其它數(shù)據(jù)庫(kù)，所以還是比較有參考價(jià)值的。

Chapter 12： How indexes work

Indexes are the most important physical design element in improving database performance:

In addition to their performance benefits, indexes can enforce the uniqueness of data.

Indexes are database objects that can be created for a table to speed direct access to specific data rows. Indexes store the values of the key(s) that were named when the index was created, and logical pointers to the data pages or to other index pages.

Although indexes speed data retrieval, they can slow down data modifications, since most changes to the data also require updating the indexes.

索引可以防止全表掃描，對(duì)某些查詢無(wú)需訪問(wèn)數(shù)據(jù)頁(yè)（復(fù)合索引），聚集索引避免頻繁插入新數(shù)據(jù)到最后一頁(yè)，避免排序。

Types of indexes

Adaptive Server provides two types of indexes:

You can create only one clustered index on a table because there is only one possible physical ordering of the data rows. You can create up to 249 nonclustered indexes per table.

A table that has no clustered index is called a heap. The rows in the table are in no particular order, and all new rows are added to the end of the table. Chapter 8, “Data Storage,” discusses heaps and SQL operations on heaps.

聚集索引的數(shù)據(jù)頁(yè)上的數(shù)據(jù)是根據(jù)索引鍵排好序的，因此一張表只能有一個(gè)聚集索引。沒(méi)有聚集索引的表也叫堆。

Index pages

Index entries are stored as rows on index pages in a format similar to the format used for data rows on data pages. Index entries store the key values and pointers to lower levels of the index, to the data pages, or to individual data rows.

Adaptive Server uses B-tree indexing, so each node in the index structure can have multiple children.

Index entries are usually much smaller than a data row in a data page, and index pages are much more densely populated than data pages. If a data row has 200 bytes (including row overhead), there are 10 rows per page.

An index on a 15-byte field has about 100 rows per index page (the pointers require 4–9 bytes per row, depending on the type of index and the index level).

Indexes can have multiple levels:

B-tree平衡樹(shù)，即父節(jié)點(diǎn)可以有多個(gè)子節(jié)點(diǎn)（不像二叉樹(shù)只有兩個(gè)）。

Root level

The root level is the highest level of the index. There is only one root page. If an allpages-locked table is very small, so that the entire index fits on a single page, there are no intermediate or leaf levels, and the root page stores pointers to the data pages.

Data-only-locked tables always have a leaf level between the root page and the data pages.

For larger tables, the root page stores pointers to the intermediate level index pages or to leaf-level pages.

對(duì)于很小的表，只需一個(gè)根索引頁(yè)即可。大表可能會(huì)有很多中間頁(yè)。

Leaf level

The lowest level of the index is the leaf level. At the leaf level, the index contains a key value for each row in the table, and the rows are stored in sorted order by the index key:

頁(yè)級(jí)別的索引頁(yè)包含每行數(shù)據(jù)的名值對(duì)，并且索引頁(yè)上的索引項(xiàng)是按索引鍵排好序的。

對(duì)于聚集索引來(lái)說(shuō)，頁(yè)級(jí)別索引頁(yè)就是數(shù)據(jù)頁(yè)。對(duì)于非聚集索引，頁(yè)級(jí)別包含所有數(shù)據(jù)行的索引項(xiàng)。（具體原因繼續(xù)向下看）

Intermediate level

All levels between the root and leaf levels are intermediate levels. An index on a large table or an index using long keys may have many intermediate levels. A very small allpages-locked table may not have an intermediate level at all; the root pages point directly to the leaf level.

Index Size

 describes the new limits for index size for APL and DOL tables:

Page size

User-visible index row-size limit

Internal index row-size limit

2K (2048 bytes)

600

650

4K (4096bytes)

1250

1310

8K (8192 bytes)

2600

2670

16K (16384 bytes)

5300

5390

Because you can create tables with columns wider than the limit for the index key, these columns become non-indexable. For example, if you perform the following on a 2K page server, then try to create an index on c3, the command fails and Adaptive Server issues an error message because column c3 is larger than the index row-size limit (600 bytes).

“Non-indexable” does not mean that you cannot use these columns in search clauses. Even though a column is non-indexable (as in c3, above), you can still create statistics for it. Also, if you include the column in a where clause, it will be evaluated during optimization.

列的長(zhǎng)度不能大于索引項(xiàng)的最大長(zhǎng)度，否則會(huì)報(bào)錯(cuò)。

Clustered indexes on allpages-locked tables

In clustered indexes on allpages-locked tables, leaf-level pages are also the data pages, and all rows are kept in physical order by the keys.

Physical ordering means that:

On the root and intermediate pages, each entry points to a page on the next level.

Clustered indexes and select operations

生活不易，碼農(nóng)辛苦
如果您覺(jué)得本網(wǎng)站對(duì)您的學(xué)習(xí)有所幫助,可以手機(jī)掃描二維碼進(jìn)行捐贈(zèng)