Understand the index in database tables/views/columns
Index的定义:
An index is an on-disk structure associated with a table or view that speeds up retrieval of rows from the table or view.
Index的类型可以有很多种:
UNIQUE - A unique index is one in which no two rows are permitted to have the same index key value.
CLUSTERED - Creates an index in which the logical order of the key values determines the physical order of the corresponding rows in a table.
NONCLUSTERED - Creates an index that specifies the logical ordering of a table. With a nonclustered index, the physical order of the data rows is independent of their indexed order.
为了立即上面这些不同类型的Index首先我们要看一下table的存储方式。
"A heap is a table without a clustered index."
当一个table没有clustered index时, 这个table的rows就不会以sorted/ordered方式存储在disk.这就意味着，如果这个table连non-clustered的index也没有时，如果我们想要找出其中一个column的一个value, 它就需要遍历整个table了。
所以这就是clustered index的作用，同时也是为什么clustered index对每个table只能有一个。
那么什么是non-clustered的index呢?
"Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value."
这就是说non-clustered的index是不会去对table进行sort的。那么对每个column我们都可以创建一个non-clustered的index.
那么我们什么时候应该使用index呢? MSDN 给出这些理由:
"Do not use a heap when the data is frequently returned in a sorted order. A clustered index on the sorting column could avoid the sorting operation.
Do not use a heap when the data is frequently grouped together. Data must be sorted before it is grouped, and a clustered index on the sorting column could avoid the sorting operation.
Do not use a heap when ranges of data are frequently queried from the table. A clustered index on the range column will avoid sorting the entire heap.
Do not use a heap when there are no nonclustered indexes and the table is large. In a heap, all rows of the heap must be read to find any row."
An index is an on-disk structure associated with a table or view that speeds up retrieval of rows from the table or view.
Index的类型可以有很多种:
UNIQUE - A unique index is one in which no two rows are permitted to have the same index key value.
CLUSTERED - Creates an index in which the logical order of the key values determines the physical order of the corresponding rows in a table.
NONCLUSTERED - Creates an index that specifies the logical ordering of a table. With a nonclustered index, the physical order of the data rows is independent of their indexed order.
为了立即上面这些不同类型的Index首先我们要看一下table的存储方式。
"A heap is a table without a clustered index."
当一个table没有clustered index时, 这个table的rows就不会以sorted/ordered方式存储在disk.这就意味着，如果这个table连non-clustered的index也没有时，如果我们想要找出其中一个column的一个value, 它就需要遍历整个table了。
所以这就是clustered index的作用，同时也是为什么clustered index对每个table只能有一个。
那么什么是non-clustered的index呢?
"Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value."
这就是说non-clustered的index是不会去对table进行sort的。那么对每个column我们都可以创建一个non-clustered的index.
那么我们什么时候应该使用index呢? MSDN 给出这些理由:
"Do not use a heap when the data is frequently returned in a sorted order. A clustered index on the sorting column could avoid the sorting operation.
Do not use a heap when the data is frequently grouped together. Data must be sorted before it is grouped, and a clustered index on the sorting column could avoid the sorting operation.
Do not use a heap when ranges of data are frequently queried from the table. A clustered index on the range column will avoid sorting the entire heap.
Do not use a heap when there are no nonclustered indexes and the table is large. In a heap, all rows of the heap must be read to find any row."
评论
发表评论