聚集索引（InnoDB，使用B+Tree作為索引結(jié)構(gòu)）

創(chuàng)新互聯(lián)建站成立于2013年，是專業(yè)互聯(lián)網(wǎng)技術服務公司，擁有項目成都網(wǎng)站制作、成都網(wǎng)站設計、外貿(mào)營銷網(wǎng)站建設網(wǎng)站策劃，項目實施與項目整合能力。我們以讓每一個夢想脫穎而出為使命，1280元汝南做網(wǎng)站,已為上家服務,為汝南各地企業(yè)和個人服務,聯(lián)系電話:18980820575

在一個結(jié)構(gòu)中保存了b-tree索引和數(shù)據(jù)行；按照主鍵的順序存儲在葉子頁上；

主鍵索引：葉節(jié)點存儲key-value為（主鍵數(shù)據(jù)：所有剩余列數(shù)據(jù)）

二級索引（非聚簇索引）：葉節(jié)點存儲key-value為（索引列數(shù)據(jù)：主鍵數(shù)據(jù)）

非葉節(jié)點只存儲 索引列

優(yōu)點：

可以把相關數(shù)據(jù)保存在一起，如根據(jù)用戶id聚集電子郵箱信息，只需要讀取少數(shù)的數(shù)據(jù)頁就能獲取某個id用戶的全部郵件；

數(shù)據(jù)訪問更快，將索引和數(shù)據(jù)保存在同一個b-tree中；

使用覆蓋索引掃描的查詢可以直接使用葉節(jié)點中的主鍵值；

缺點：

插入速度嚴重依賴于插入順序，按照主鍵的順序插入是加載數(shù)據(jù)到innodb表中速度最快的方式；

插入新行可能面臨頁分裂的問題，頁分裂導致表占用更多磁盤空間；

通過二級索引需要兩次查找，存儲引擎找到二級索引的葉子節(jié)點獲得對應的主鍵值，根據(jù)這個值去聚簇索引中找到對應的行

主鍵：

如果表沒有什么數(shù)據(jù)需要被聚集（如上述郵件用戶id），那么可以定義一個代理鍵作為主鍵，使用auto_increment自增列；

非聚集索引（MyISAM使用B+Tree作為索引結(jié)構(gòu)）

按照數(shù)據(jù)插入順序存儲在磁盤上，訪問數(shù)據(jù)需要一次系統(tǒng)調(diào)用；

主鍵索引/二級索引：葉節(jié)點存儲（索引列數(shù)據(jù)：數(shù)據(jù)在磁盤上的行號）

對比：

InnoDB提供事務支持事務，外鍵等功能；MyISAM不支持。

InnoDB支持行級鎖；MyISAM只支持表級鎖

InnoDB要求必須有主鍵；MyISAM允許沒有任何索引和主鍵的表存在，索引都是保存行的地址。

覆蓋索引

一個索引包含（或者說覆蓋）所有需要查詢的字段的值

覆蓋索引要存儲索引列的值，只能用b-tree索引做覆蓋索引（不能用哈希索引，全文索引等）

優(yōu)點

1. MyISAM存儲引擎在內(nèi)存中只存儲索引，覆蓋索引不需要進行系統(tǒng)調(diào)用；

2. innodb存儲引擎的聚簇索引機制，二級主鍵如果能覆蓋查詢，可以避免對主鍵索引的二次查詢；

全文索引

希望通過關鍵字的匹配來進行查詢過濾，而不是通過常規(guī)的數(shù)值比較，范圍過濾操作；

MyISAM的全文索引是一類特殊的B-Tree索引，共有兩層，第一層是所有關鍵字，對于每一個關鍵字的第二層，包含的是一組相關的“文檔指針”；

對于文檔對象中的所有詞語的過濾條件：

1. 停用詞列表中的詞語都不會被索引

2. 長度大于指定范圍和小于指定范圍的詞語不會被索引

另外，全文索引不會存儲關鍵字具體匹配在哪一列。

1. 自然語言全文索引

根據(jù)where子句中的MATCH AGAINST區(qū)分是否使用全文索引

Eg.在表file_text的字段title，description上建立fulltext全文索引

Select film_id,title,right(description,25)

Match(title,description) against(‘factory  casualties’) as relevance

From file_text where Match(title,description) against(‘factory  casualties’);

結(jié)果：

Film_id         title                             right(description,25)                     relevance

831           spirited casualties           a car is a baloon factory                  8.4692449702

126           casualties encino            face a boy in a monastery                 5.2615661621

... ...

函數(shù)match()將返回關鍵詞的匹配相關度，是一個浮點數(shù)。

2. 布爾全文索引

用戶可以自定義被搜索詞語的相關性；用戶可以通過一些前置修飾符定制搜索：
example meaning

Dinosaur 包含dinosaur的行rank值更高

-dinosaur 包含dinosaur的行rank值更低

+dinosaur 行記錄必須包含dinosaur

-dinosaur 行記錄不能包含dinosaur

Dino* 包含以dino開頭的單詞的行rank值更高

Eg. Select film_id,title,right(description,25)

From file_text where Match(title,description) against(‘+factory  +casualties’ in boolean mood);

結(jié)果：

Film_id title right(description,25)

831         spirited casualties   a car is a baloon factory

搜索的關鍵詞是不常見的詞語時，比LIKE操作要快得多，因為是直接從索引中過濾記錄。

全文索引的限制：

1. MySQL的全文索引只有一種判斷相關性的方法：詞頻。沒有其他相關性排序算法，如存儲的位置。

2. 只有全文索引全部在內(nèi)存中時，性能才能非常好

3. 其他的where條件，只能在mysql完成全文搜索返回記錄后才能進行

4. 全文索引不能存儲列的實際值，不能用作覆蓋掃描

5. 除了相關性排序，不能用作其他排序

配置和優(yōu)化

1. 停用詞表

2. 允許最小詞長

在搜索的精度和搜索的效率之間找到合適的平衡點。

索引和鎖

      索引可以讓查詢鎖定更少的行，innodb只有在訪問行時才會對其加鎖，而索引可以減少innodb訪問的行數(shù)，從而減少鎖的數(shù)量；

但是，只有當innodb在存儲引擎層能夠過濾掉不需要的行時才有效，如果無法過濾，那么在innodb檢索到數(shù)據(jù)并返回給服務器層，mysql才能應用where語句進行過濾，而innodb已經(jīng)鎖住了這些行，直到服務器層過濾完成后釋放鎖；

如：select actor_id from sakila.actor where actor_id < 5 (范圍)and actor_id <> 1 (過濾) for update;

執(zhí)行explain命令，顯示type為range，表示mysql為該查詢選擇的執(zhí)行計劃是索引范圍查詢，即在存儲引擎層只執(zhí)行了actor_id < 5的條件，查詢結(jié)果：2,3,4；而被鎖定的數(shù)據(jù)行：1,2,3,4；

即使使用索引，也可能鎖住一些不需要的行，但是不使用索引查找的話mysql會全表掃描并鎖住所有的行。