資料庫索引結構
Ⅰ 什麼是索引索引類型有幾種，各有什麼特點
索引是對資料庫表中一列或多列的值進行排序的一種結構，使用索引可快速訪問資料庫表中的特定信息。是一種單獨的、物理的對資料庫表中一列或多列的值進行排序的一種存儲結構，它是某個表中一列或若干列值的集合和相應的指向表中物理標識這些值的數據頁的邏輯指針清單。
索引有三種類型：
唯一索引：唯一索引是不允許其中任何兩行具有相同索引值的索引。當現有數據中存在重復的鍵值時，大多數資料庫不允許將新創建的唯一索引與表一起保存。
主鍵索引：資料庫表經常有一列或多列組合，其值唯一標識表中的每一行。該列稱為表的主鍵。在資料庫關系圖中為表定義主鍵將自動創建主鍵索引，主鍵索引是唯一索引的特定類型。該索引要求主鍵中的每個值都唯一。
聚焦索引：在聚集索引中，表中行的物理順序與鍵值的邏輯（索引）順序相同。一個表只能包含一個聚集索引。如果某索引不是聚集索引，則表中行的物理順序與鍵值的邏輯順序不匹配。
(1)資料庫索引結構擴展閱讀：
通過建立索引可以極大地提高在資料庫中獲取所需信息的速度，同時還能提高伺服器處理相關搜索請求的效率，從這個方面來看它具有以下優點：
在設計資料庫時，通過創建一個惟一的索引，能夠在索引和信息之間形成一對一的映射式的對應關系，增加數據的惟一性特點。
能提高數據的搜索及檢索速度，符合資料庫建立的初衷。
能夠加快表與表之間的連接速度，這對於提高數據的參考完整性方面具有重要作用。
在信息檢索過程中，若使用分組及排序子句進行時，通過建立索引能有效的減少檢索過程中所需的分組及排序時間，提高檢索效率。
建立索引之後，在信息查詢過程中可以使用優化隱藏器，這對於提高整個信息檢索系統的性能具有重要意義。
Ⅱ 資料庫基礎：講解MySQL索引的概念及資料庫索引的應用[1]
資料庫引入了索引
用戶對資料庫最頻繁的操作是進行數據查詢 一般情況下 資料庫在進行查詢操作時需要對整個表進行數據搜索 當表中的數據很多時 搜索數據就需要很長的時間 這就造成了伺服器的資源浪費 為了提高檢索數據的能力 資料庫引入了索引機制
有關 索引 的比喻
從某種程度上 可以把資料庫看作一本書 把索引看作書的目錄 通過目錄查找書中的信息 顯然較沒有目錄的書方便 快捷
資料庫索引實際是什麼告滲?(兩部分組成)
索引是一個單獨的 物理的資料庫結構 它是某個表中一列或若干列值的集合和相應的指向表中物理標識這些值的數據頁的邏輯指針清單
索引在表中的角色
一個表的存儲是由兩部分組成的 一部分用來存放表的數據頁面 另一部分存放索引頁面 索引就存放在索引頁面上
索引高效原理
通常 索引頁面相對於數據頁面來說小得多 當進行數據檢索時 系統先搜索索引頁面 從中找到所需數據的指針 再直接通過指針從數據頁面中讀取數據
索引的分類
在SQL Server 的資料庫中按存儲結構的不同將索引分為兩類 簇索引(Clustered Index)和非簇索引(Nonclustered Index)
( )簇索引對表的物理數據頁中的數據按列進行排序 然後再重新存儲到磁碟上 即簇索引與數據是混為一體 的它的葉節點中存儲的是實際的數據 由於簇索引對表中的數據一一進行了排序 因此用簇索引查找數據很快 但由於簇索引將表的所有數據完全重新排列了 它所需要的空間也就特別大 大概相當於表中數據所佔空間的 % 表的數據行只能以一種排序方式存儲在磁碟上 所以一個表只能有一個簇索引
( )非簇索引具有與表的數據完全分離的結構 使用非簇索引不用將物理數據頁中的數據按列襪友鍵排序 非簇索引的葉節點中存儲了組成非簇索引的關鍵字的值和行定位器 行定位器的結構和存儲內容取決於數據的存儲方式 如果數據是以簇索引方式存儲的 則行定位器中存儲的是簇索引的索引鍵;如果數據不是以簇索引方式存儲的 這種方式又稱為堆存儲方式(Heap Structure) 則行定位器存儲的是指向數據行的指針 非簇索引將行定位器按關鍵字的值用一定的方式排序 這個順序與表的行在數據頁中的排序是不匹配的 由於非簇索引使用索引頁存儲因此它比簇索引需要更多的存儲空間且檢索效率較低但一個表只能建一個簇索引 當用戶需要建立多個索引時就需要使用非簇索引了
小結 Clustered Index 是與物理數據混在一起並對物理數據進重排 就像使用拼音查字典;Unclustered Index 是與物理數據完全分離的 利用額外空間對關鍵字進行重排 就像使用部首查字典
資料庫索引應用
一 索引的概念
索引就是加快檢索表中數據的方法 資料庫的索引類似於書籍的索引 在書籍中 索引允許用戶不必翻閱完整個書就能迅速地找到所需要的信息 在資料庫中 索引也允許資料庫程序迅速地找到表中的數據 而不必掃描整個資料庫
二 索引的特點
索引可以加快資料庫的檢索速度
索引降低了資料庫插入 修改 刪除等維護任務的速度
索引創建在表上 不能創建在視圖上
索引既可以直接創建 也可以間接創建
可以在優化隱藏中 使用索引
使用查詢處理器執行SQL語句 在一個表上 一次只能使用一個索引
其他
三 索引的優點
創建唯一性索引 保證資料庫表中每一行數據的唯一性
大大加快數據的檢索速度 這也是創建索引的最主要的原因
加速表和表之間的連接 特別是在實現數據的參考完整性方面特別有意義
在使用分組和排序子句進行數據檢索時 同樣可以顯著減少查詢中分組和排序的時間
通過使用索引 可以在查詢告巧的過程中使用優化隱藏器 提高系統的性能
四 索引的缺點
創建索引和維護索引要耗費時間 這種時間隨著數據量的增加而增加
索引需要佔物理空間 除了數據表占數據空間之外 每一個索引還要佔一定的物理空間 如果要建立聚簇索引 那麼需要的空間就會更大
當對表中的數據進行增加 刪除和修改的時候 索引也要動態的維護 降低了數據的維護速度
lishixin/Article/program/MySQL/201311/29604
Ⅲ 資料庫索引有哪幾種,怎樣建立索引
資料庫索引的種類：
1、按照索引列值的唯一性，索引可分為唯一索引和非唯一索引
非唯一索引：B樹索引
create index 索引名 on 表名（列名） tablespace 表空間名;
唯一索引：建立主鍵或者唯一約束時會自動在對應的列上建立唯一索引
2、索引列的個數：單列索引和復合索引
3、按照索引列的物理組織方式
B樹索引
create index 索引名 on 表名（列名） tablespace 表空間名;
點陣圖索引
create bitmap index 索引名 on 表名（列名） tablespace 表空間名;
反向鍵索引
create index 索引名 on 表名（列名） reverse tablespace 表空間名;
函數索引
create index 索引名 on 表名（函數名(列名)） tablespace 表空間名;
刪除索引
drop index 索引名
重建索引
alter index 索引名 rebuild
索引的創建格式：
CREATE UNIUQE | BITMAP INDEX <schema>.<index_name>
ON <schema>.<table_name>
(<column_name> | <expression> ASC | DESC,
<column_name> | <expression> ASC | DESC,...)
TABLESPACE <tablespace_name>
STORAGE <storage_settings>
LOGGING | NOLOGGING
COMPUTE STATISTICS
NOCOMPRESS | COMPRESS<nn>
NOSORT | REVERSE
PARTITION | GLOBAL PARTITION<partition_setting>
UNIQUE | BITMAP：指定UNIQUE為唯一值索引，BITMAP為點陣圖索引，省略為B-Tree索引。
<column_name> | <expression> ASC | DESC：可以對多列進行聯合索引，當為expression時即「基於函數的索引」
TABLESPACE：指定存放索引的表空間(索引和原表不在一個表空間時效率更高)
STORAGE：可進一步設置表空間的存儲參數
LOGGING | NOLOGGING：是否對索引產生重做日誌(對大表盡量使用NOLOGGING來減少佔用空間並提高效率)
COMPUTE STATISTICS：創建新索引時收集統計信息
NOCOMPRESS | COMPRESS<nn>：是否使用「鍵壓縮」(使用鍵壓縮可以刪除一個鍵列中出現的重復值)
NOSORT | REVERSE：NOSORT表示與表中相同的順序創建索引，REVERSE表示相反順序存儲索引值
PARTITION | NOPARTITION：可以在分區表和未分區表上對創建的索引進行分區
使用USER_IND_COLUMNS查詢某個TABLE中的相應欄位索引建立情況
使用DBA_INDEXES/USER_INDEXES查詢所有索引的具體設置情況。
在Oracle中的索引可以分為：B樹索引、點陣圖索引、反向鍵索引、基於函數的索引、簇索引、全局索引、局部索引等，下面逐一講解：
一、B樹索引：
最常用的索引，各葉子節點中包括的數據有索引列的值和數據表中對應行的ROWID，簡單的說，在B樹索引中，是通過在索引中保存排過續的索引列值與相對應記錄的ROWID來實現快速查詢的目的。其邏輯結構如圖：
反向鍵索引是一種特殊的B樹索引，在存儲構造中與B樹索引完全相同，但是針對數值時，反向鍵索引會先反向每個鍵值的位元組，然後對反向後的新數據進行索引。例如輸入2008則轉換為8002，這樣當數值一次增加時，其反向鍵在大小中的分布仍然是比較平均的。
反向鍵索引的創建示例：
createindex ind_t on t1(id) reverse;
註：鍵的反轉由系統自行完成。對於用戶是透明的。
四、基於函數的索引：
有的時候，需要進行如下查詢：select * from t1 where to_char(date,'yyyy')>'2007';
但是即便在date欄位上建立了索引，還是不得不進行全表掃描。在這種情況下，可以使用基於函數的索引。其創建語法如下：
create index ind_t on t1(to_char(date,'yyyy'));
註：簡單來說，基於函數的索引，就是將查詢要用到的表達式作為索引項。
五、全局索引和局部索引：
這個索引貌似很復雜，其實很簡單。總得來說一句話，就是無論怎麼分區，都是為了方便管理。
具體索引和表的關系有三種：
1、局部分區索引：分區索引和分區表1對1
2、全局分區索引：分區索引和分區表N對N
3、全局非分區索引：非分區索引和分區表1對N
創建示例：
首先創建一個分區表
createtable student
(
stuno number(5),
sname vrvhar2(10),
deptno number(5)
)
partition by hash (deptno)
(
partition part_01 tablespace A1,
partition part_02 tablespace A2
);
創建局部分區索引(1v1)：
create index ind_t on student(stuno)
local(
partition part_01 tablespace A2,
partition part_02 tablespace A1
);--local後面可以不加
創建全局分區索引(NvN)：
create index ind_t on student(stuno)
globalpartition by range(stuno)
(
partition p1 values less than(1000) tablespace A1,
partition p2 values less than(maxvalue) tablespace A2
);--只可以進行range分區
創建全局非分區索引(1vN)
createindex ind_t on student(stuno) GLOBAL;
Ⅳ 資料庫建立索引怎麼利用索引查詢
1.合理使用索引x0dx0a索引是資料庫中重要的數據結構，它的根本目的就是為了提高查詢效率。現在大多數的資料庫產品都採用IBM最先提出的ISAM索引結構。x0dx0a索喚漏引的使用要恰到好處，其使用原則如下：x0dx0a在經常進行連接，但是沒有指定為外鍵的列上建立索引，而不經常連接的欄位則由優化器自動生成索引。x0dx0a在頻繁進行排序或分組（即進行group by或order by操作）的列上建立索引。x0dx0a在條件表達式中經常用到的不同值較多的列上建立檢索，在不同值少的列上不要建立索引。比如在雇員表的「性別」列上只有「男」與「女」兩個不同值，因此就無必要建立索引。如果建立索引不但不會提高查詢效率，反而會嚴重降低更新速度。x0dx0a如果待排序的列有多個，可以在這些列上建立復合索引（compound index）。x0dx0a使用系統工具。如Informix資料庫有一個tbcheck工具，可以在可疑的索引上進行檢查。在一些資料庫伺服器上，索引可能失效或者因為頻繁操作而 使得讀取效率降低，如果一個使用索引的查詢不明不白地慢下來，可以試著用tbcheck工具檢查索引的完整性，必要時進行修復。另外，當資料庫表更新大量 數據後，刪除並重建索引可以提高查詢速度。x0dx0a(1)在下面兩條select語句中:x0dx0aSELECT * FROM table1 WHERE field1<=10000 AND field1>=0; x0dx0aSELECT * FROM table1 WHERE field1>=0 AND field1<=10000;x0dx0a如果數據表中的數據field1都>=0,則第一條select語句要比第二條select語句效率高的多，因為第二條select語句的第一個條件耗費了大量的系統資源。x0dx0a第一個原則：在where子句中應把最具限制性的條件放在最前面。x0dx0a(2)在下面的select語句中:x0dx0aSELECT * FROM tab WHERE a=? AND b=? AND c=?;x0dx0a若有索引index(a,b,c)，則where子句中欄位的順序應和索引中欄位順序一致。x0dx0a第二個原則：where子句中欄位的順序應和索引中欄位順序一致。x0dx0a—————————————————————————— x0dx0a以下假設在field1上有唯一索引I1，在field2上有非唯一索引I2。 x0dx0a—————————————喊鏈段————————————— x0dx0a(3) SELECT field3,field4 FROM tb WHERE field1='sdf' 快 x0dx0aSELECT * FROM tb WHERE field1='sdf' 慢[/cci]x0dx0a因為後者在索引掃描後要多一步ROWID表訪問。x0dx0a(4) SELECT field3,field4 FROM tb WHERE field1>='sdf' 快 x0dx0aSELECT field3,field4 FROM tb WHERE field1>'sdf' 慢x0dx0a因為前者可以迅速定位索引。x0dx0a(5) SELECT field3,field4 FROM tb WHERE field2 LIKE 'R%' 快 x0dx0aSELECT field3,field4 FROM tb WHERE field2 LIKE '%R' 慢，x0dx0a因為後者不使用索引。x0dx0a(6) 使用函鄭譽數如： x0dx0aSELECT field3,field4 FROM tb WHERE upper(field2)='RMN'不使用索引。x0dx0a如果一個表有兩萬條記錄，建議不使用函數；如果一個表有五萬條以上記錄，嚴格禁止使用函數！兩萬條記錄以下沒有限制。x0dx0a(7) 空值不在索引中存儲，所以 x0dx0aSELECT field3,field4 FROM tb WHERE field2 IS[NOT] NULL不使用索引。x0dx0a(8) 不等式如 x0dx0aSELECT field3,field4 FROM tb WHERE field2!='TOM'不使用索引。 x0dx0a相似地， x0dx0aSELECT field3,field4 FROM tb WHERE field2 NOT IN('M','P')不使用索引。x0dx0a(9) 多列索引，只有當查詢中索引首列被用於條件時，索引才能被使用。x0dx0a(10) MAX，MIN等函數，使用索引。 x0dx0aSELECT max(field2) FROM tb 所以，如果需要對欄位取max，min，sum等，應該加索引。x0dx0a一次只使用一個聚集函數，如： x0dx0aSELECT 「min」=min(field1), 「max」=max(field1) FROM tb x0dx0a不如：SELECT 「min」=(SELECT min(field1) FROM tb) , 「max」=(SELECT max(field1) FROM tb)x0dx0a(11) 重復值過多的索引不會被查詢優化器使用。而且因為建了索引，修改該欄位值時還要修改索引，所以更新該欄位的操作比沒有索引更慢。x0dx0a(12) 索引值過大（如在一個char(40)的欄位上建索引），會造成大量的I/O開銷（甚至會超過表掃描的I/O開銷）。因此，盡量使用整數索引。 Sp_estspace可以計算表和索引的開銷。x0dx0a(13) 對於多列索引，ORDER BY的順序必須和索引的欄位順序一致。x0dx0a(14) 在sybase中，如果ORDER BY的欄位組成一個簇索引，那麼無須做ORDER BY。記錄的排列順序是與簇索引一致的。x0dx0a(15) 多表聯結（具體查詢方案需要通過測試得到） x0dx0awhere子句中限定條件盡量使用相關聯的欄位，且盡量把相關聯的欄位放在前面。 x0dx0aSELECT a.field1,b.field2 FROM a,b WHERE a.field3=b.field3x0dx0afield3上沒有索引的情況下: x0dx0a對a作全表掃描，結果排序 x0dx0a對b作全表掃描，結果排序 x0dx0a結果合並。 x0dx0a對於很小的表或巨大的表比較合適。x0dx0afield3上有索引 x0dx0a按照表聯結的次序，b為驅動表，a為被驅動表 x0dx0a對b作全表掃描 x0dx0a對a作索引范圍掃描 x0dx0a如果匹配，通過a的rowid訪問x0dx0a(16) 避免一對多的join。如： x0dx0aSELECT tb1.field3,tb1.field4,tb2.field2 FROM tb1,tb2 WHERE tb1.field2=tb2.field2 AND tb1.field2=『BU1032』 AND tb2.field2= 『aaa』 x0dx0a不如： x0dx0adeclare @a varchar(80) x0dx0aSELECT @a=field2 FROM tb2 WHERE field2=『aaa』 x0dx0aSELECT tb1.field3,tb1.field4,@a FROM tb1 WHERE field2= 『aaa』x0dx0a(16) 子查詢 x0dx0a用exists/not exists代替in/not in操作 x0dx0a比較： x0dx0aSELECT a.field1 FROM a WHERE a.field2 IN(SELECT b.field1 FROM b WHERE b.field2=100) x0dx0aSELECT a.field1 FROM a WHERE EXISTS( SELECT 1 FROM b WHERE a.field2=b.field1 AND b.field2=100) x0dx0aSELECT field1 FROM a WHERE field1 NOT IN( SELECT field2 FROM b) x0dx0aSELECT field1 FROM a WHERE NOT EXISTS( SELECT 1 FROM b WHERE b.field2=a.field1)x0dx0a(17) 主、外鍵主要用於數據約束，sybase中創建主鍵時會自動創建索引，外鍵與索引無關，提高性能必須再建索引。x0dx0a(18) char類型的欄位不建索引比int類型的欄位不建索引更糟糕。建索引後性能只稍差一點。x0dx0a(19) 使用count(*)而不要使用count(column_name)，避免使用count(DISTINCT column_name)。x0dx0a(20) 等號右邊盡量不要使用欄位名，如： x0dx0aSELECT * FROM tb WHERE field1 = field3x0dx0a(21) 避免使用or條件，因為or不使用索引。x0dx0a2.避免使用order by和group by字句。x0dx0a因為使用這兩個子句會佔用大量的臨時空間(tempspace),如果一定要使用，可用視圖、人工生成臨時表的方法來代替。 x0dx0a如果必須使用，先檢查memory、tempdb的大小。 x0dx0a測試證明，特別要避免一個查詢里既使用join又使用group by，速度會非常慢！x0dx0a3.盡量少用子查詢，特別是相關子查詢。因為這樣會導致效率下降。x0dx0a一個列的標簽同時在主查詢和where子句中的查詢中出現，那麼很可能當主查詢中的列值改變之後，子查詢必須重新查詢一次。查詢嵌套層次越多，效率越低，因此應當盡量避免子查詢。如果子查詢不可避免，那麼要在子查詢中過濾掉盡可能多的行。x0dx0a4．消除對大型錶行數據的順序存取x0dx0a在 嵌套查詢中，對表的順序存取對查詢效率可能產生致命的影響。 x0dx0a比如採用順序存取策略，一個嵌套3層的查詢，如果每層都查詢1000行，那麼這個查詢就要查詢 10億行數據。 x0dx0a避免這種情況的主要方法就是對連接的列進行索引。 x0dx0a例如，兩個表：學生表（學號、姓名、年齡??）和選課表（學號、課程號、成績）。如果兩個 表要做連接，就要在「學號」這個連接欄位上建立索引。 x0dx0a還可以使用並集來避免順序存取。盡管在所有的檢查列上都有索引，但某些形式的where子句強迫優化器使用順序存取。 x0dx0a下面的查詢將強迫對orders表執行順序操作： x0dx0aSELECT ＊ FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008 x0dx0a雖然在customer_num和order_num上建有索引，但是在上面的語句中優化器還是使用順序存取路徑掃描整個表。因為這個語句要檢索的是分離的行的集合，所以應該改為如下語句： x0dx0aSELECT ＊ FROM orders WHERE customer_num=104 AND order_num>1001 x0dx0aUNION x0dx0aSELECT ＊ FROM orders WHERE order_num=1008 x0dx0a這樣就能利用索引路徑處理查詢。x0dx0a5．避免困難的正規表達式x0dx0aMATCHES和LIKE關鍵字支持通配符匹配，技術上叫正規表達式。但這種匹配特別耗費時間。例如：SELECT ＊ FROM customer WHERE zipcode LIKE 「98_ _ _」 x0dx0a即使在zipcode欄位上建立了索引，在這種情況下也還是採用順序掃描的方式。如果把語句改為SELECT ＊ FROM customer WHERE zipcode >「98000」，在執行查詢時就會利用索引來查詢，顯然會大大提高速度。 x0dx0a另外，還要避免非開始的子串。例如語句：SELECT ＊ FROM customer WHERE zipcode[2，3] >「80」，在where子句中採用了非開始子串，因而這個語句也不會使用索引。x0dx0a6．使用臨時表加速查詢x0dx0a把表的一個子集進行排序並創建臨時表，有時能加速查詢。它有助於避免多重排序操作，而且在其他方面還能簡化優化器的工作。例如： x0dx0aSELECT cust.name，rcvbles.balance，??other COLUMNS x0dx0aFROM cust，rcvbles x0dx0aWHERE cust.customer_id = rcvlbes.customer_id x0dx0aAND rcvblls.balance>0 x0dx0aAND cust.postcode>「98000」 x0dx0aORDER BY cust.name x0dx0a如果這個查詢要被執行多次而不止一次，可以把所有未付款的客戶找出來放在一個臨時文件中，並按客戶的名字進行排序： x0dx0aSELECT cust.name，rcvbles.balance，??other COLUMNS x0dx0aFROM cust，rcvbles x0dx0aWHERE cust.customer_id = rcvlbes.customer_id x0dx0aAND rcvblls.balance>;0 x0dx0aORDER BY cust.name x0dx0aINTO TEMP cust_with_balance x0dx0a然後以下面的方式在臨時表中查詢： x0dx0aSELECT ＊ FROM cust_with_balance x0dx0aWHERE postcode>「98000」 x0dx0a臨時表中的行要比主表中的行少，而且物理順序就是所要求的順序，減少了磁碟I/O，所以查詢工作量可以得到大幅減少。 x0dx0a注意：臨時表創建後不會反映主表的修改。在主表中數據頻繁修改的情況下，注意不要丟失數據。x0dx0a7．用排序來取代非順序存取x0dx0a非順序磁碟存取是最慢的操作，表現在磁碟存取臂的來回移動。SQL語句隱藏了這一情況，使得我們在寫應用程序時很容易寫出要求存取大量非順序頁的查詢。