MYSQL（二）數(shù)據(jù)庫聚集/非聚集索引，索引和鎖
聚集索引（InnoDB，使用B+Tree作為索引結(jié)構(gòu)）
創(chuàng)新互聯(lián)建站成立于2013年，是專業(yè)互聯(lián)網(wǎng)技術(shù)服務(wù)公司，擁有項(xiàng)目成都網(wǎng)站制作、成都網(wǎng)站設(shè)計(jì)、外貿(mào)營銷網(wǎng)站建設(shè)網(wǎng)站策劃，項(xiàng)目實(shí)施與項(xiàng)目整合能力。我們以讓每一個(gè)夢想脫穎而出為使命，1280元汝南做網(wǎng)站,已為上家服務(wù),為汝南各地企業(yè)和個(gè)人服務(wù),聯(lián)系電話:18980820575
在一個(gè)結(jié)構(gòu)中保存了b-tree索引和數(shù)據(jù)行；按照主鍵的順序存儲(chǔ)在葉子頁上；
主鍵索引：葉節(jié)點(diǎn)存儲(chǔ)key-value為（主鍵數(shù)據(jù)：所有剩余列數(shù)據(jù)）
二級索引（非聚簇索引）：葉節(jié)點(diǎn)存儲(chǔ)key-value為（索引列數(shù)據(jù)：主鍵數(shù)據(jù)）
非葉節(jié)點(diǎn)只存儲(chǔ) 索引列
優(yōu)點(diǎn)：
可以把相關(guān)數(shù)據(jù)保存在一起，如根據(jù)用戶id聚集電子郵箱信息，只需要讀取少數(shù)的數(shù)據(jù)頁就能獲取某個(gè)id用戶的全部郵件；
數(shù)據(jù)訪問更快，將索引和數(shù)據(jù)保存在同一個(gè)b-tree中；
使用覆蓋索引掃描的查詢可以直接使用葉節(jié)點(diǎn)中的主鍵值；
缺點(diǎn)：
插入速度嚴(yán)重依賴于插入順序，按照主鍵的順序插入是加載數(shù)據(jù)到innodb表中速度最快的方式；
插入新行可能面臨頁分裂的問題，頁分裂導(dǎo)致表占用更多磁盤空間；
通過二級索引需要兩次查找，存儲(chǔ)引擎找到二級索引的葉子節(jié)點(diǎn)獲得對應(yīng)的主鍵值，根據(jù)這個(gè)值去聚簇索引中找到對應(yīng)的行
主鍵：
如果表沒有什么數(shù)據(jù)需要被聚集（如上述郵件用戶id），那么可以定義一個(gè)代理鍵作為主鍵，使用auto_increment自增列；
非聚集索引（MyISAM使用B+Tree作為索引結(jié)構(gòu)）
按照數(shù)據(jù)插入順序存儲(chǔ)在磁盤上，訪問數(shù)據(jù)需要一次系統(tǒng)調(diào)用；
主鍵索引/二級索引：葉節(jié)點(diǎn)存儲(chǔ)（索引列數(shù)據(jù)：數(shù)據(jù)在磁盤上的行號）
對比：
InnoDB提供事務(wù)支持事務(wù)，外鍵等功能；MyISAM不支持。
InnoDB支持行級鎖；MyISAM只支持表級鎖
InnoDB要求必須有主鍵；MyISAM允許沒有任何索引和主鍵的表存在，索引都是保存行的地址。
覆蓋索引
一個(gè)索引包含（或者說覆蓋）所有需要查詢的字段的值
覆蓋索引要存儲(chǔ)索引列的值，只能用b-tree索引做覆蓋索引（不能用哈希索引，全文索引等）
優(yōu)點(diǎn)
1. MyISAM存儲(chǔ)引擎在內(nèi)存中只存儲(chǔ)索引，覆蓋索引不需要進(jìn)行系統(tǒng)調(diào)用；
2. innodb存儲(chǔ)引擎的聚簇索引機(jī)制，二級主鍵如果能覆蓋查詢，可以避免對主鍵索引的二次查詢；
全文索引
希望通過關(guān)鍵字的匹配來進(jìn)行查詢過濾，而不是通過常規(guī)的數(shù)值比較，范圍過濾操作；
MyISAM的全文索引是一類特殊的B-Tree索引，共有兩層，第一層是所有關(guān)鍵字，對于每一個(gè)關(guān)鍵字的第二層，包含的是一組相關(guān)的“文檔指針”；
對于文檔對象中的所有詞語的過濾條件：
1. 停用詞列表中的詞語都不會(huì)被索引
2. 長度大于指定范圍和小于指定范圍的詞語不會(huì)被索引
另外，全文索引不會(huì)存儲(chǔ)關(guān)鍵字具體匹配在哪一列。
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()將返回關(guān)鍵詞的匹配相關(guān)度，是一個(gè)浮點(diǎn)數(shù)。
2. 布爾全文索引
用戶可以自定義被搜索詞語的相關(guān)性；用戶可以通過一些前置修飾符定制搜索：
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
搜索的關(guān)鍵詞是不常見的詞語時(shí)，比LIKE操作要快得多，因?yàn)槭侵苯訌乃饕羞^濾記錄。
全文索引的限制：
1. MySQL的全文索引只有一種判斷相關(guān)性的方法：詞頻。沒有其他相關(guān)性排序算法，如存儲(chǔ)的位置。
2. 只有全文索引全部在內(nèi)存中時(shí)，性能才能非常好
3. 其他的where條件，只能在mysql完成全文搜索返回記錄后才能進(jìn)行
4. 全文索引不能存儲(chǔ)列的實(shí)際值，不能用作覆蓋掃描
5. 除了相關(guān)性排序，不能用作其他排序
配置和優(yōu)化
1. 停用詞表
2. 允許最小詞長
在搜索的精度和搜索的效率之間找到合適的平衡點(diǎn)。
索引和鎖
索引可以讓查詢鎖定更少的行，innodb只有在訪問行時(shí)才會(huì)對其加鎖，而索引可以減少innodb訪問的行數(shù)，從而減少鎖的數(shù)量；
但是，只有當(dāng)innodb在存儲(chǔ)引擎層能夠過濾掉不需要的行時(shí)才有效，如果無法過濾，那么在innodb檢索到數(shù)據(jù)并返回給服務(wù)器層，mysql才能應(yīng)用where語句進(jìn)行過濾，而innodb已經(jīng)鎖住了這些行，直到服務(wù)器層過濾完成后釋放鎖；
如：select actor_id from sakila.actor where actor_id < 5 (范圍)and actor_id <> 1 (過濾) for update;
執(zhí)行explain命令，顯示type為range，表示mysql為該查詢選擇的執(zhí)行計(jì)劃是索引范圍查詢，即在存儲(chǔ)引擎層只執(zhí)行了actor_id < 5的條件，查詢結(jié)果：2,3,4；而被鎖定的數(shù)據(jù)行：1,2,3,4；
即使使用索引，也可能鎖住一些不需要的行，但是不使用索引查找的話mysql會(huì)全表掃描并鎖住所有的行。
網(wǎng)站題目：MYSQL（二）數(shù)據(jù)庫聚集/非聚集索引，索引和鎖
文章鏈接：http://jinyejixie.com/article32/jjpopc.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供搜索引擎優(yōu)化、網(wǎng)站內(nèi)鏈、外貿(mào)網(wǎng)站建設(shè)、網(wǎng)站建設(shè)、、網(wǎng)站制作
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)
