索引是用來(lái)迅速定位并且找到特定的數(shù)據(jù)集涉馁。 如果沒(méi)有索引, 數(shù)據(jù)庫(kù)會(huì)從頭遍歷整個(gè)表, 采用索引的情況下, 數(shù)據(jù)庫(kù)可以從數(shù)據(jù)中端定位并且開(kāi)始尋找需要的數(shù)據(jù)集门岔。
聚集索引 Cluster Index
根據(jù)真實(shí)地址存儲(chǔ)和排序(asc/desc)， 每個(gè)表中只能有一個(gè)Cluster Index. 只有Cluster Index 被創(chuàng)建了以后, 表中數(shù)據(jù)才會(huì)依據(jù)排序后的順序存儲(chǔ)烤送。否則表中存儲(chǔ)數(shù)據(jù)的結(jié)構(gòu)在無(wú)序的堆中寒随。
索引的葉結(jié)點(diǎn)是數(shù)據(jù)節(jié)點(diǎn)
非聚集索引 Nonclustered Index
索引的葉結(jié)點(diǎn)是索引節(jié)點(diǎn), 保存一個(gè)指針指向數(shù)據(jù)塊
最左匹配
(col1), (col1, col2), and (col1, col2, col3) 可以hit到
(col1, col2, col3) index
B樹(shù)索引
B-tree index 會(huì)在以下操作生效：
=, > , >=, <, <=, BETWEEN, LIKE (1. 只能應(yīng)用于不以%開(kāi)頭的字符串常量)
SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%'; (hit)
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';
'Pat' <= key_col < 'Pau' 只有這樣的才會(huì)用到索引
SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%'; 前% 不會(huì)用到索引
SELECT * FROM tbl_name WHERE key_col LIKE other_col; 非字符串常量,不會(huì)用到索引
以下成功應(yīng)用索引
... WHERE index_part1=1 AND index_part2=2 AND other_column=3
index = 1 OR index = 2
... WHERE index=1 OR A=10 AND index=2
optimized like "index_part1='hello'"
... WHERE index_part1='hello' AND index_part3=5
Can use index on index1 but not on index2 or index3
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;
以下不會(huì)應(yīng)用索引
index_part1 is not used
... WHERE index_part2=1 AND index_part3=2
index is not used in both parts of the WHERE clause
... WHERE index=1 OR A=10
No index spans all rows
... WHERE index_part1=1 OR index_part2=10
Hash 索引
只生效與 = 或者 <> , 速度很快
