database Index

緣起:從散亂的資料找尋想看的數據，需要將整個資料表看完才能找到想要的資料。如果將資料某些欄位排序或者分門別類後建立索引，要尋找就容易多了。

Index在db是一個schema物件。目的是使用索引路徑快速定位資料，並減少硬碟I/O次數。
index描述資料存放架構。



索引結構:SQL server以B-tree(blanced tree)結構來放索引資料，每個葉節點都是一個資料頁(data page)存放多筆資料，除了葉子節點外每個節點都是一個索引頁(index page)。每個資料頁大小為8k。
這邊資料頁好比資料表(data table)一小部分。

分成:叢集索引、非叢集索引



叢集索引?HEAP?:重建索引並編摘要表的過程
create clustered index PK_student on students(id)

非叢集索引:從原來索引建立新的索引摘要表
create nonclustered index idx-tel on students(tel)


scan 在沒有建立索引之下，動用select句子，sql server採用此方式讀取table每筆資料之後撈出資料
seek 建立索引後，server依照索引架構尋找資料


看查詢效能
下指令:set statistics io on

掃描計數 1，邏輯讀取 2，實體讀取 0，讀取前讀取 0，LOB 邏輯讀取 0，LOB 實體讀取 0，LOB 讀取前讀取 0。
邏輯讀取越小表示效能越好
-----------------實作include columns------------
use AdventureWorks
go

-- 建立示範專用的 DemoStore 資料表
select * into dbo.DemoStore from Sales.Store
go

-- 在沒有索引的情況下, 檢視下列指令的執行計畫
-- Hint: 資料表掃描(Table Scan)
Select CustomerID, Name
  from dbo.DemoStore
  where CustomerID = 150

查看邏輯讀取
邏輯讀取是讀取記憶體中的資料頁page次數
次數越少代表效能越好

資料表 'DemoStore'。掃描計數 1，邏輯讀取 102，實體讀取 0，讀取前讀取 0，LOB 邏輯讀取 0，LOB 實體讀取 0，LOB 讀取前讀取 0。

另外以下指令的效能也跟上面一樣
select * from dbo.DemoStore
(查出701筆資料)



-- 建立非叢集索引
Create nonclustered index idx_DemoStore
  on dbo.DemoStore(CustomerID)
go
-- 再次檢視下列指令的執行計畫
-- Hint: 同時使用索引搜尋"Index Seek" 與 RID查閱 "bookmark seek"


Select CustomerID, Name
  from dbo.DemoStore
  where CustomerID = 150

 -- 資料表 'DemoStore'。掃描計數 1，邏輯讀取 3，實體讀取 0，讀取前讀取 0，LOB 邏輯讀取 0，LOB 實體讀取 0，LOB 讀取前讀取 0。


-- 重建一次索引, 這次我們加上 include 選項
drop index idx_DemoStore on dbo.DemoStore
go
Create nonclustered index idx_DemoStore
  on dbo.DemoStore(CustomerID)
  include (Name)
go


-- 再次檢視下列指令的執行計畫
-- Hint: 這次只需索引搜尋"Index Seek"
Select CustomerID, Name
  from dbo.DemoStore
  where CustomerID = 150
go

--資料表 'DemoStore'。掃描計數 1，邏輯讀取 2，實體讀取 0，讀取前讀取 0，LOB 邏輯讀取 0，LOB 實體讀取 0，LOB 讀取前讀取 0。

-- 清除/還原 Lab 環境
drop table dbo.DemoStore
go


索引使用時機：
1.欄位所包含的值太廣
2.欄位包含大量空值
3.一個或多個欄位常常一起被使用於where子句或join
4.表格龐大，且大多數的select只截取不到2~4的資料列


不建議使用索引狀況:
1.欄位不常被select
2.表格常常update
3.表格較小，且大多數的select