MYSQL（二）數據庫聚集/非聚集索引，索引和鎖

聚集索引（InnoDB，使用B+Tree作為索引結構）

在一個結構中保存了b-tree索引和數據行；按照主鍵的順序存儲在葉子頁上；

主鍵索引：葉節點存儲key-value為（主鍵數據：所有剩余列數據）

二級索引（非聚簇索引）：葉節點存儲key-value為（索引列數據：主鍵數據）

非葉節點只存儲 索引列

優點：

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

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

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

缺點：

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

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

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

主鍵：

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

非聚集索引（MyISAM使用B+Tree作為索引結構）

按照數據插入順序存儲在磁盤上，訪問數據需要一次系統調用；

主鍵索引/二級索引：葉節點存儲（索引列數據：數據在磁盤上的行號）

對比：

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

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

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

覆蓋索引

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

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

優點

1. MyISAM存儲引擎在內存中只存儲索引，覆蓋索引不需要進行系統調用；

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

全文索引

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

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

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

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

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

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

1. 自然語言全文索引

根據where子句中的MATCH AGAINST區分是否使用全文索引

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’);

結果：

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

... ...

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

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);

結果：

Film_id title right(description,25)

831         spirited casualties   a car is a baloon factory

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

全文索引的限制：

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

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

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

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

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

配置和優化

1. 停用詞表

2. 允許最小詞長

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

索引和鎖

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

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

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

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

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