數據庫表設計索引外鍵設計

Introduction:

介紹：

The attributes whose values are required inequality or range conditions and those that are keys or that participate in join conditions require access paths.

其值為必需的不等式或范圍條件的屬性以及作為鍵或參與聯接條件的屬性需要訪問路徑。

The performance of queries largely depends upon what indexes or hashing schemas exist to expedite the processing of selections and joins. On the other hand, when we do insertion, deletion, or updating operations, the existence of indexes adds to the overhead. This overhead need to be justified in terms of the increase in efficiency by expediting queries and also the transactions.

查詢的性能很大程度上取決于存在哪些索引或哈希模式以加快選擇和聯接的處理。 另一方面，當我們執行插入，刪除或更新操作時，索引的存在會增加開銷。 需要通過加快查詢以及事務的效率來證明這種開銷。

The physical design decision for indexing falls into the following categories:

索引的物理設計決策可以歸為以下幾類 ：

1)是否也索引屬性 (1) Whether too index an attribute)

The attribute must be a key or there must be some query that uses that attribute either in a selection condition or in a join. One factor.

該屬性必須是鍵，或者必須存在一些在選擇條件或聯接中使用該屬性的查詢。 一個因素。

In favor of setting up many indexes is that some queries can be processed by just scanning the indexes without retrieving any data.

設置許多索引的好處是可以通過僅掃描索引而無需檢索任何數據來處理某些查詢。

2)要索引哪些屬性或屬性 (2) What attributes or attributes to index on)

An index can be constructed one or multiple attributes. If there are multiple attributes from one relation that are involved together in several queries,

索引可以構造一個或多個屬性。 如果一個查詢中有多個屬性同時包含在多個查詢中，

A multiattribute index is warranted. The ordering of attributes within a multiattribute index must correspond to the queries. For example, the above index assumes that queries would be based on an ordering of colors within a GARMENT_style_ #rather than vice-versa.

多屬性索引是必要的。 多屬性索引中的屬性順序必須與查詢相對應。 例如，以上索引假設查詢將基于GARMENT_style_＃內的顏色順序，而不是相反。

3)是否建立聚簇索引 (3) Whether to set up a clustered index)

At most, one index per table can be primary or clustering index because this implies that the file is physically ordered on that attribute. In most RDBMS this is specified by the keyword CLUSTER.

每個表最多只能有一個索引是主索引或聚簇索引，因為這意味著該文件在該屬性上是物理排序的。 在大多數RDBMS中，這由關鍵字CLUSTER指定。

If a table requires several indexes, the decision about which one should be a clustered index depends upon whether keeping the table ordered on that attribute is needed. Range queries benefit a great deal from clustering. If several attributes require the range queries, relative benefits must be evaluated before deciding which attribute to cluster on. A clustering index may be set up as a multi-attribute index if range retrieval by that composite key is useful in report creation.

如果一個表需要多個索引，則關于哪個索引應為聚集索引的決定取決于是否需要將該表保持在該屬性上。 范圍查詢從群集中受益匪淺。 如果幾個屬性需要范圍查詢，則必須在確定要聚類的哪個屬性之前評估相對利益。 如果該復合鍵的范圍檢索在報表創建中很有用，則可以將聚類索引設置為多屬性索引。

4)是否在樹索引上使用哈希索引 (4) Whether to use a hash index over a tree index )

In general, RDBMS use B+ trees for indexing. However, ISAM and hash indexes are also provided in some systems.B+ trees support equality and range queries on the attribute used as the search key. Hash indexes work very well with equality conditions, to find a matching record during joins.

通常，RDBMS使用B +樹進行索引。 但是，某些系統中還提供了ISAM和哈希索引.B +樹支持對用作搜索關鍵字的屬性進行相等性和范圍查詢。 哈希索引在相等條件下可以很好地工作，可以在聯接期間找到匹配的記錄。

5)是否對文件使用動態哈希 (5) Whether to use dynamic hashing for the file)

For files that are very volatile, that is those that grow and shrink continuously one of the dynamic hashing schemas would be suitable. Currently, they are not offered by commercial RDBMSs.

對于非常易失的文件，即那些不斷增長和縮小的文件，動態哈希方案之一將是合適的。 當前，商業RDBMS不提供它們。

翻譯自: https://www.includehelp.com/dbms/design-decision-about-indexing.aspx

數據庫表設計索引外鍵設計