Everything about database,how to,example,guide,

The operation with the most frequent to the database user is to undertake data inquires.Usually,The need when the database is undertaking inquiring operating undertakes to whole watch data is searched.The data in should be being expressed is very much when,Search data needs very long time,This is wasted with respect to the resource that caused a server.To increase the capacity that retrieves data,The database introduced index mechanism.This chapter reachs the idea that introduces index his to found with management.

The concept that 8.1.1 indexes
Index is alone, database structure of physics,It is row or the one a certain number of rows are worth gather in a certain watch and label of corresponding the physics in pointing to a watch the logistic index detailed account of the data page of these values.Index is to rely on what the watch builds,It was offerred weave in the database the in-house method of the data in the watch.The memory of a watch is by what two parts comprise,One part uses the data page that deposits a watch,Another part deposits index page.Index is deposited on index page,Normally,Index page photograph is small to data page much.When undertaking data retrieval,The system searchs index page first,The needle that finds place to require data from which,Pass pressing with a finger to read access to occupy from inside data page directly again.From go up somehow,Can regard the database a book,the catalog that index interprets a book,The information in searching a book through catalog,The book that does not have catalog relatively apparently is convenient, quick.

Index and key word and tie have bigger connection.Key word can be divided it is two kinds:One kind is logistic key word,Be in namely " database base order " of section intermediary carry on;Another kind is physical key word,It is listed with what will define index,Also index namely.

The structure that 8.1.2 indexes
(1) the B- of index establishs a structure
The index in SQL Server is with what B- tree structure will safeguard,Place of the 8-1 that be like a graph is shown.B- tree is a mutiple level, structure that maintains oneself.Tree of a B- includes layer of a top,Call a node (Root Node) ;0 arrives many the layer intermediate (Intermediate) ;A ground floor (Level 0) ,A certain number of leaf node include in ground floor (Leaf Node) .In graph 8-1,Every pane represents an index page,The width that index lists is bigger,The deepness that B- cultivates is deeper,Namely administrative levels is more,



Read the index page that takes record place to want a visit more.That is to say,The function that data inquires will list administrative levels numerary to increase along with index and reduce.The B- that graph 8-1 indexes establishs a structure
Differ to be index cent two kinds in what memory structure presses in the database of SQL Server:Bunch indexes (Clustered Index) index with blame bunch (Nonclustered Index) .

(2) bunch indexes bunch indexes the data in the physical data page to the watch presses a row to have sort,Store afresh again next to disk,Form a cluster namely index and data are to mix for an organic whole,what store in its foliaceous node is real data.Because bunch indexes,had sort one by one to expressing medium data,Because this uses bunch index to search data very fast.But all data that will express as a result of bunch index are completely new arranged,The space that it needs is particularly large also,The 120% that is equivalent to place of the data in the watch occupying a space probably.The data travel of the watch can store with means of a kind of sort only on disk,So a watch can have a bunch to index only.

(3) the structure that blame bunch indexes the index that be not bunch has as complete as the data of the watch depart,Index of use blame bunch need not press the data in physical data page a sort.The value of the key word that stored composition is not bunch to index in the foliaceous node of the index that be not bunch and travel positioner.The structure of travel positioner and memory content depend on the memory means of data.If data is,store with bunch index means,The index that what store in travel positioner is bunch index bolts;If data is not,store with bunch index means,This kind of means calls caboodle memory way again (Heap Structure) ,Go what positioner stores is the finger that points to data travel.The means sort with be not bunch to index the value that presses travel positioner key word is used certain,The goes in data page sort of this order and watch is do not match.Because be not bunch to index use index page stores accordingly it compares bunch,index needs more storage spaces and retrieve efficiency inferior but a watch can build a bunch to index only,Used the index that be not bunch with respect to need when the user needs to build many index.From theoretic tell,A watch can build 249 at most the index that be not bunch.