MySQL——關(guān)于索引的總結(jié)

首先說說索引的 優(yōu)點(diǎn) ：最大的好處無(wú)疑就是提高查詢效率。有的索引還能保證數(shù)據(jù)的唯一性，比如唯一索引。

專注于為中小企業(yè)提供成都網(wǎng)站設(shè)計(jì)、成都網(wǎng)站制作、外貿(mào)網(wǎng)站建設(shè)服務(wù),電腦端+手機(jī)端+微信端的三站合一,更高效的管理,為中小企業(yè)太子河免費(fèi)做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動(dòng)了1000+企業(yè)的穩(wěn)健成長(zhǎng)，幫助中小企業(yè)通過網(wǎng)站建設(shè)實(shí)現(xiàn)規(guī)模擴(kuò)充和轉(zhuǎn)變。

而它的 壞處 也很明顯：索引也是文件，我們?cè)趧?chuàng)建索引時(shí)，也會(huì)創(chuàng)建額外的文件，所以會(huì)占用一些硬盤空間。其次，索引也需要維護(hù)，我們?cè)谠黾觿h除數(shù)據(jù)的時(shí)候，索引也需要去變化維護(hù)。當(dāng)一個(gè)表的索引多了以后，資源消耗是很大的，所以必須結(jié)合實(shí)際業(yè)務(wù)再去確定給哪些列加索引。

再說說索引的基本結(jié)構(gòu)。一說到這里肯定會(huì)脫口而出：B+樹！了解B+樹前先要了解二叉查找樹和二叉平衡樹。 二叉查找樹 ：左節(jié)點(diǎn)比父節(jié)點(diǎn)小，右節(jié)點(diǎn)比父節(jié)點(diǎn)大，所以二叉查找樹的中序遍歷就是樹的各個(gè)節(jié)點(diǎn)從小到大的排序。 二叉平衡樹 ：左右子樹高度差不能大于1。B+樹就是結(jié)合了它們的特點(diǎn)，當(dāng)然，不一定是二叉樹。

為什么要有二叉查找樹的特點(diǎn)？？ 因?yàn)椴檎倚士?，二分查找在這種結(jié)構(gòu)下，查找效率是很快的。 那為什么要有平衡樹的特點(diǎn)呢？ 試想，如果不維護(hù)一顆樹的平衡性，當(dāng)插入一些數(shù)據(jù)后，樹的形態(tài)有可能變得很極端，比如左子樹一個(gè)數(shù)據(jù)沒有，而全在右子樹上，這種情況下，二分查找和遍歷有什么區(qū)別呢？而就是因?yàn)檫@些特點(diǎn)需要去維護(hù)，所以就有了上面提到的缺點(diǎn)，當(dāng)索引很多后，反而增加了系統(tǒng)的負(fù)擔(dān)。

接著說B+樹。 它的結(jié)構(gòu)如下 ：

可以發(fā)現(xiàn)，葉子節(jié)點(diǎn)其實(shí)是一個(gè) 雙向循環(huán)鏈表 ，這種結(jié)構(gòu)的好處就是，在范圍查詢的時(shí)候，我只用找到一個(gè)數(shù)據(jù)，就可以直接返回剩余的數(shù)據(jù)了。比如找小于30的，只用找到30，其余的直接通過葉子節(jié)點(diǎn)間的指針就可以找到。再說說其他特點(diǎn)： 數(shù)據(jù)只存在于葉子節(jié)點(diǎn) 。當(dāng)葉子節(jié)點(diǎn)滿了，如果再添加數(shù)據(jù)，就會(huì)拆分葉子節(jié)點(diǎn)，父節(jié)點(diǎn)就多了個(gè)子節(jié)點(diǎn)。如果父節(jié)點(diǎn)的位置也滿了，就會(huì)擴(kuò)充高度，就是拆分父節(jié)點(diǎn)，如25 50 75拆分成：25為左子樹，75為右子樹，50變成新的頭節(jié)點(diǎn)，此時(shí)B+樹的高度變成了3。它們的擴(kuò)充的規(guī)律如下表，Leaf Page是葉子節(jié)點(diǎn)，index Page是非葉子節(jié)點(diǎn)。

再說說B樹 ，B樹相比較B+樹，它所有節(jié)點(diǎn)都存放數(shù)據(jù)，所以在查找數(shù)據(jù)時(shí)，B樹有可能沒到達(dá)葉子節(jié)點(diǎn)就結(jié)束了。再者，B樹的葉子節(jié)點(diǎn)間不存在指針。

最后說說Hash索引 ，相較于B+樹，Hash索引最大的優(yōu)點(diǎn)就是查找數(shù)據(jù)快。但是Hash索引最大的問題就是不支持范圍查詢。試想，如果查詢小于30的數(shù)據(jù)，hash函數(shù)是根據(jù)數(shù)據(jù)的值找到其對(duì)應(yīng)的位置，誰(shuí)又知道小于30的有哪幾個(gè)數(shù)據(jù)。而B+樹正好相反，范圍查詢是它的強(qiáng)項(xiàng)。

附錄： Hash到底是啥？？ 哈希中文名散列，哈希只是它的音譯。 為啥都說Hash快？？ 首先有一塊哈希表（散列表），它的數(shù)據(jù)結(jié)構(gòu)是個(gè)數(shù)組，一個(gè)任意長(zhǎng)度的數(shù)據(jù)通過hash函數(shù)都可以變成一個(gè)固定長(zhǎng)度的數(shù)據(jù)，叫hash值。然后通過hash值確定在數(shù)組中的位置，相同數(shù)據(jù)的hash值是相同的，所以我們存儲(chǔ)一個(gè)數(shù)據(jù)以后，只需O(1)的時(shí)間復(fù)雜度就可以找到數(shù)據(jù)。 那hash函數(shù)又是啥？？ 算術(shù)運(yùn)算或位運(yùn)算，很多應(yīng)用里都有hash函數(shù)，但實(shí)際運(yùn)算過程大不一樣。這是Java里String的hashCode方法：

publicint hashCode() {

}

還有一個(gè)問題，hash函數(shù)計(jì)算出來(lái)的hash值有可能存在碰撞，即兩個(gè)不同的數(shù)據(jù)可能存在相同的hash值，在MySQL或其他的應(yīng)用中，如Java的HashMap等，如果存在碰撞就會(huì)以當(dāng)前數(shù)組位置為頭節(jié)點(diǎn)，轉(zhuǎn)變成一個(gè)鏈表。

說到這里也清楚了為啥Java中引用類型要同時(shí)重寫hashCode和equals了。兩個(gè)對(duì)象，實(shí)例就算一模一樣，它們的hash值也不相等， 為啥不相等？？ 默認(rèn)的Object的hashCode方法會(huì)根據(jù)對(duì)象來(lái)計(jì)算hash值的，實(shí)例相同，但它們還是兩個(gè)不同的對(duì)象啊，所以我們重寫hashCode時(shí)，最簡(jiǎn)單的方法就是調(diào)用Object的hashCode方法，然后傳入該引用類型的屬性，讓hashCode方法只根據(jù)這幾個(gè)屬性來(lái)計(jì)算，那么實(shí)例相同的話，它們的hash值也會(huì)相等。等hashCode比較完后，如果相等再比較實(shí)例內(nèi)容，也就是equals，確保不是hash碰撞。

索引的分類

如果我們指定了一個(gè)主鍵，那么這個(gè)主鍵就是主鍵索引。如果我們沒有指定，Mysql就會(huì)自動(dòng)找一個(gè)非空的唯一索引當(dāng)主鍵。如果沒有這種字段，Mysql就會(huì)創(chuàng)建一個(gè)大小為6字節(jié)的自增主鍵。如果有多個(gè)非空的唯一索引，那么就讓第一個(gè)定義為唯一索引的字段當(dāng)主鍵，注意，是第一個(gè)定義，而不是建表時(shí)出現(xiàn)在前面的。

對(duì)于輔助索引來(lái)說，它們的B+樹結(jié)構(gòu)稍微有點(diǎn)特殊，它們的葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵，而不是整個(gè)數(shù)據(jù)。所以在大部分情況下，使用輔助索引查找數(shù)據(jù)，需要二次查找。但并不是所有情況都需要二次查找。比如查找的數(shù)據(jù)正好就是當(dāng)前索引字段的值，那么直接返回就行。這里提一句，B+樹的key就是對(duì)應(yīng)索引字段的內(nèi)容。

而輔助索引又有一些分類：唯一索引：不能出現(xiàn)重復(fù)的值，也算一種約束。普通索引：可以重復(fù)、可以為空，一般就是查詢時(shí)用到。前綴索引：只適用于字符串類型數(shù)據(jù)，對(duì)字符串前幾個(gè)字符創(chuàng)建索引。全文索引：作用是檢測(cè)大文本數(shù)據(jù)中某個(gè)關(guān)鍵字，這也是搜索引擎的一種技術(shù)。

注意，聚集索引、非聚集索引和前面幾個(gè)索引的分類并不是一個(gè)層面上的。上面的幾個(gè)分類是從索引的作用來(lái)分析的。聚集、非聚集索引是從索引文件上區(qū)分的。主鍵索引就屬于聚集索引，即索引和數(shù)據(jù)存放在一起，葉子節(jié)點(diǎn)存放的就是數(shù)據(jù)。數(shù)據(jù)表的.idb文件就是存放該表的索引和數(shù)據(jù)。

輔助索引屬于非聚集索引，說到這也就明白了。索引和數(shù)據(jù)不存放在一起的就是非聚集索引。在MYISAM引擎中，數(shù)據(jù)表的.MYI文件包含了表的索引， 該表的 葉子節(jié)點(diǎn)存儲(chǔ)索引和索引對(duì)應(yīng)數(shù)據(jù)的指針，指向.MYD文件的數(shù)據(jù)。

索引的幾點(diǎn)使用經(jīng)驗(yàn)

經(jīng)常被查詢的字段；經(jīng)常作為條件查詢的字段；經(jīng)常用于外鍵連接或普通的連表查詢時(shí)進(jìn)行相等比較字段；不為null的字段；如果是多條件查詢，最好創(chuàng)建聯(lián)合索引，因?yàn)槁?lián)合索引只有一個(gè)索引文件。

經(jīng)常被更新的字段、不經(jīng)常被查詢的字段、存在相同功能的字段

mysql全文索引 很慢，速度不如like的百分之一

從explain開始說起吧，很顯然第一個(gè)sql語(yǔ)句壓根沒用任何索引（key列內(nèi)什么都沒有）！第二個(gè)倒是用到索引，卻是主鍵索引，并非你添加的fulltext索引！

接下來(lái)，分析下原因：

sql1：執(zhí)行步驟：先s_a和s_a_t兩表笛卡爾集，然后篩選滿足on條件的，接著在從結(jié)果集中篩選滿足where字句的；該過程中處理的記錄條目為69*105479，并且未用到任何索引，未用到的原因可能是你先定義了一個(gè)復(fù)合索引a_concent_split（a_title_split，a_content_split），然后又定義了一個(gè)a_content_split2（a_content_split），當(dāng)引擎執(zhí)行查找優(yōu)化時(shí)候會(huì)先用到a_content_split，可是又由于復(fù)合索引是從最左邊開始（不能跳過第一個(gè)字段），而你卻忽略了a_title_split字段，故未能正常使用索引。

sql2：執(zhí)行步驟：先調(diào)用where字句對(duì)s_a表進(jìn)行篩選形成新的s_a表，然后與s_a_t表笛卡爾積，再利用on字句篩選，最后再次利用where字句形成最終結(jié)果集；經(jīng)過第一個(gè)where，該過程處理結(jié)果集會(huì)大幅少于sql1，并且該過程還用到了主鍵索引。你所設(shè)置的fulltext索引再次沒有用到，原因是like字句中開始部分為模糊匹配%時(shí)候用不了全文索引，這與fulltext存儲(chǔ)機(jī)制有關(guān)。

另，你說的刪除速度慢，原因：設(shè)置fulltext字段設(shè)置太多，fulltext索引在更新刪除大量數(shù)據(jù)時(shí)候，需要同步更改索引，你的三個(gè)fulltext壓力太大！

改進(jìn)方法：1、刪除a_content_split索引重試 2、在刪除時(shí)候打開delay_key_write變量

有關(guān)fulltext比較復(fù)雜，用的時(shí)候要謹(jǐn)慎設(shè)置，還有很多參數(shù)也對(duì)其有影響

另外sql語(yǔ)句中外連接有關(guān)on where字句也是個(gè)比較繞的地方，兩者你都占了，唉，所以我寫的略復(fù)雜，前天看到該問題，思忖兩天這才作答

望有結(jié)果了予以回復(fù)交流！

mysql索引有什么用？

先正面回答你的問題

數(shù)據(jù)是否重復(fù)不是建立索引的重要依據(jù)，甚至都不是依據(jù)。

只要不完全重復(fù)（所有元組的該元素都一樣），那么建立索引就是有意義的。

即使當(dāng)前數(shù)據(jù)完全重復(fù)，也不是不能建立索引，這種情況有點(diǎn)復(fù)雜，不細(xì)說了。

對(duì)于你后面的疑問，可以給你一個(gè)如何建立索引的忠告，“如何查就如何建”。

索引的建立，唯一的原因就是為了查詢（廣義的查詢），實(shí)際上建立索引會(huì)使得數(shù)據(jù)存儲(chǔ)所占空間變大，有時(shí)索引所占的空間會(huì)查過數(shù)據(jù)本身的空間。索引的建立也會(huì)使得數(shù)據(jù)插入時(shí)變慢，特殊情況下，慢的難以忍受，所以dba的重要工作之一，就是檢查索引層級(jí)并優(yōu)化。

索引建立的唯一好處，就是按照索引查詢時(shí)，變快了。type，status這2個(gè)字段是否適合建立索引，就要看你是否要按照這2個(gè)字段進(jìn)行檢索。而檢索的順序決定了如何建立索引。

對(duì)于索引類型和索引方式，我建議就

normal

和

btree

就適用于大多數(shù)情況。若你參與的是一個(gè)大數(shù)據(jù)處理項(xiàng)目，對(duì)數(shù)據(jù)存儲(chǔ)和檢索有特別要求，那么需要分析多個(gè)層面，比如數(shù)據(jù)吞吐量、數(shù)據(jù)的方差、平均差等等很多參數(shù)才考慮是否用聚集索引等（mysql好像還沒聚集索引），至于是否是唯一索引，我建議不使用，即使能判定數(shù)據(jù)是唯一的也不要用，全文索引也沒有必要。

全文搜索之MySQL與ElasticSearch搜索引擎

MySQL支持全文索引和搜索功能。在MySQL中可以在CHAR、VARCHAR或TEXT列使用FULLTETXT來(lái)創(chuàng)建全文索引。

FULLTEXT索引主要用MATCH()...AGAINST語(yǔ)法來(lái)實(shí)現(xiàn)搜索：

MySQL的全文搜索存在以下局限：

通常來(lái)說MySQL自帶的全文搜索使用起來(lái)局限性比較大，性能和功能都不太成熟，主要適用于小項(xiàng)目，大項(xiàng)目還是建議使用elasticsearch來(lái)做全文搜索。

ElasticSearch是一個(gè)分布式的開源搜索和分析引擎，適用于所有類型的數(shù)據(jù)，包括文本、數(shù)字、地理空間、結(jié)構(gòu)化和非結(jié)構(gòu)化數(shù)據(jù)，以下簡(jiǎn)稱ES。

Elasticsearch 在 Apache Lucene 的基礎(chǔ)上開發(fā)而成，Elasticsearch 以其簡(jiǎn)單的 REST 風(fēng)格 API、分布式特性、速度和可擴(kuò)展性而聞名，是 Elastic Stack 的核心組件。Elastic Stack 是適用于數(shù)據(jù)采集、充實(shí)、存儲(chǔ)、分析和可視化的一組開源工具。

Elasticsearch 的實(shí)現(xiàn)原理主要分為以下幾個(gè)步驟，首先用戶將數(shù)據(jù)提交到Elasticsearch 數(shù)據(jù)中心，再通過分詞控制器去將對(duì)應(yīng)的數(shù)據(jù)分詞，將其權(quán)重和分詞結(jié)果一并存入數(shù)據(jù)，當(dāng)用戶搜索數(shù)據(jù)時(shí)候，再根據(jù)權(quán)重將結(jié)果排名，打分，再將返回結(jié)果呈現(xiàn)給用戶。

由于ES是基于RESTfull Web接口的，因此我們直接按照慣例傳遞JSON參數(shù)調(diào)用接口即可實(shí)現(xiàn)增刪改查，并且不需要我們做額外的管理操作就可以直接索引文檔，ES已經(jīng)內(nèi)置了所有的缺省操作，可以自動(dòng)幫我們定義類型。

再次執(zhí)行PUT，會(huì)對(duì)庫(kù)中已有的id為1的數(shù)據(jù)進(jìn)行覆蓋，每修改一次_version字段的版本號(hào)就會(huì)加1。

默認(rèn)搜索會(huì)返回前10個(gè)結(jié)果：

返回的幾個(gè)關(guān)鍵詞：

查詢字符串搜索，可以像傳遞URL參數(shù)一樣傳遞查詢語(yǔ)句。

精確查詢：

全文搜索：

以上兩種方法都需要考慮數(shù)據(jù)更改后如何與ES進(jìn)行同步。

MySQL簡(jiǎn)單介紹——換個(gè)角度認(rèn)識(shí)MySQL

1、InnoDB存儲(chǔ)引擎

Mysql版本=5.5 默認(rèn)的存儲(chǔ)引擎，MySQL推薦使用的存儲(chǔ)引擎。支持事務(wù)，行級(jí)鎖定，外鍵約束。事務(wù)安全型存儲(chǔ)引擎。更加注重?cái)?shù)據(jù)的完整性和安全性。

存儲(chǔ)格式 : 數(shù)據(jù)，索引集中存儲(chǔ)，存儲(chǔ)于同一個(gè)表空間文件中。

InnoDB的行鎖模式及其加鎖方法： InnoDB中有以下兩種類型的行鎖：共享鎖（讀鎖： 允許事務(wù)對(duì)一條行數(shù)據(jù)進(jìn)行讀?。┖?互斥鎖（寫鎖： 允許事務(wù)對(duì)一條行數(shù)據(jù)進(jìn)行刪除或更新）， 對(duì)于update，insert，delete語(yǔ)句，InnoDB會(huì)自動(dòng)給設(shè)計(jì)的數(shù)據(jù)集加互斥鎖，對(duì)于普通的select語(yǔ)句，InnoDB不會(huì)加任何鎖。

InnoDB行鎖的實(shí)現(xiàn)方式： InnoDB行鎖是通過給索引上的索引項(xiàng)加鎖來(lái)實(shí)現(xiàn)的，如果沒有索引，InnoDB將通過隱藏的聚簇索引來(lái)對(duì)記錄加鎖。InnoDB這種行鎖實(shí)現(xiàn)特點(diǎn)意味著：如果不通過索引條件檢索數(shù)據(jù)，那么InnoDB將對(duì)表中的所有記錄加鎖，實(shí)際效果跟表鎖一樣。

（1）在不通過索引條件查詢時(shí)，InnoDB會(huì)鎖定表中的所有記錄。

（2）Mysql的行鎖是針對(duì)索引加的鎖，不是針對(duì)記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果使用相同的索引鍵，是會(huì)出現(xiàn)沖突的。

（3）當(dāng)表有多個(gè)索引的時(shí)候，不同的事務(wù)可以使用不同的索引鎖定不同的行，但都是通過行鎖來(lái)對(duì)數(shù)據(jù)加鎖。

優(yōu)點(diǎn)：

1、支持事務(wù)處理、ACID事務(wù)特性；

2、實(shí)現(xiàn)了SQL標(biāo)準(zhǔn)的四種隔離級(jí)別（ 原子性（ Atomicity ）、一致性（ Consistency ）、隔離性（Isolation ）和持續(xù)性（Durability ））；

3、支持行級(jí)鎖和外鍵約束；

4、可以利用事務(wù)日志進(jìn)行數(shù)據(jù)恢復(fù)。

5、鎖級(jí)別為行鎖，行鎖優(yōu)點(diǎn)是適用于高并發(fā)的頻繁表修改，高并發(fā)是性能優(yōu)于 MyISAM。缺點(diǎn)是系統(tǒng)消耗較大。

6、索引不僅緩存自身，也緩存數(shù)據(jù)，相比 MyISAM 需要更大的內(nèi)存。

缺點(diǎn)：

因?yàn)樗鼪]有保存表的行數(shù)，當(dāng)使用COUNT統(tǒng)計(jì)時(shí)會(huì)掃描全表。

使用場(chǎng)景：

(1)可靠性要求比較高，或者要求事務(wù)；(2)表更新和查詢都相當(dāng)?shù)念l繁，并且表鎖定的機(jī)會(huì)比較大的情況。

2、 MyISAM存儲(chǔ)引擎

MySQL= 5.5 MySQL默認(rèn)的存儲(chǔ)引擎。ISAM：Indexed Sequential Access Method(索引順序存取方法)的縮寫，是一種文件系統(tǒng)。擅長(zhǎng)與處理，高速讀與寫。

功能：

（1）支持?jǐn)?shù)據(jù)壓縮存儲(chǔ),但壓縮后的表變成了只讀表，不可寫；如果需要更新數(shù)據(jù)，則需要先解壓后更新。

（2）支持表級(jí)鎖定，不支持高并發(fā)；

（3）支持并發(fā)插入。寫操作中的插入操作，不會(huì)阻塞讀操作（其他操作）；

優(yōu)點(diǎn)：

1.高性能讀??；

2.因?yàn)樗４媪吮淼男袛?shù)，當(dāng)使用COUNT統(tǒng)計(jì)時(shí)不會(huì)掃描全表；

缺點(diǎn)：

1、鎖級(jí)別為表鎖，表鎖優(yōu)點(diǎn)是開銷小，加鎖快；缺點(diǎn)是鎖粒度大，發(fā)生鎖沖動(dòng)概率較高，容納并發(fā)能力低，這個(gè)引擎適合查詢?yōu)橹鞯臉I(yè)務(wù)。

2、此引擎不支持事務(wù)，也不支持外鍵。

3、INSERT和UPDATE操作需要鎖定整個(gè)表；

使用場(chǎng)景：

(1)做很多count 的計(jì)算；(2)插入不頻繁，查詢非常頻繁；(3)沒有事務(wù)。

InnoDB和MyISAM一些細(xì)節(jié)上的差別：

1、InnoDB不支持FULLTEXT類型的索引，MySQL5.6之后已經(jīng)支持（實(shí)驗(yàn)性）。

2、InnoDB中不保存表的 具體行數(shù)，也就是說，執(zhí)行select count() from table時(shí)，InnoDB要掃描一遍整個(gè)表來(lái)計(jì)算有多少行，但是MyISAM只要簡(jiǎn)單的讀出保存好的行數(shù)即可。注意的是，當(dāng)count()語(yǔ)句包含 where條件時(shí)，兩種表的操作是一樣的。

3、對(duì)于AUTO_INCREMENT類型的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中，可以和其他字段一起建立聯(lián)合索引。

4、DELETE FROM table時(shí)，InnoDB不會(huì)重新建立表，而是一行一行的刪除。

5、LOAD TABLE FROM MASTER操作對(duì)InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，導(dǎo)入數(shù)據(jù)后再改成InnoDB表，但是對(duì)于使用的額外的InnoDB特性（例如外鍵）的表不適用。

6、另外，InnoDB表的行鎖也不是絕對(duì)的，如果在執(zhí)行一個(gè)SQL語(yǔ)句時(shí)MySQL不能確定要掃描的范圍，InnoDB表同樣會(huì)鎖全表。

1．索引概述

利用關(guān)鍵字，就是記錄的部分?jǐn)?shù)據(jù)（某個(gè)字段，某些字段，某個(gè)字段的一部分），建立與記錄位置的對(duì)應(yīng)關(guān)系，就是索引。索引的關(guān)鍵字一定是排序的。索引本質(zhì)上是表字段的有序子集，它是提高查詢速度最有效的方法。一個(gè)沒有建立任何索引的表，就相當(dāng)于一本沒有目錄的書，在每次查詢時(shí)就會(huì)進(jìn)行全表掃描，這樣會(huì)導(dǎo)致查詢效率極低、速度也極慢。如果建立索引，那么就好比一本添加的目錄，通過目錄的指引，迅速翻閱到指定的章節(jié)，提升的查詢性能，節(jié)約了查詢資源。

2．索引種類

從索引的定義方式和用途中來(lái)看：主鍵索引，唯一索引，普通索引，全文索引。

無(wú)論任何類型，都是通過建立關(guān)鍵字與位置的對(duì)應(yīng)關(guān)系來(lái)實(shí)現(xiàn)的。索引是通過關(guān)鍵字找對(duì)應(yīng)的記錄的地址。

以上類型的差異：對(duì)索引關(guān)鍵字的要求不同。

關(guān)鍵字：記錄的部分?jǐn)?shù)據(jù)（某個(gè)字段，某些字段，某個(gè)字段的一部分）。

普通索引,index：對(duì)關(guān)鍵字沒有要求。

唯一索引,unique index：要求關(guān)鍵字不能重復(fù)。同時(shí)增加唯一約束。

主鍵索引,primary key：要求關(guān)鍵字不能重復(fù)，也不能為NULL。同時(shí)增加主鍵約束。

全文索引,fulltext key：關(guān)鍵字的來(lái)源不是所有字段的數(shù)據(jù)，而是從字段中提取的特別關(guān)鍵詞。

PS：這里主鍵索引和唯一索引的區(qū)別在于：主鍵索引不能為空值，唯一索引允許空值；主鍵索引在一張表內(nèi)只能創(chuàng)建一個(gè)，唯一索引可以創(chuàng)建多個(gè)。主鍵索引肯定是唯一索引，但唯一索引不一定是主鍵索引。

3.索引原則

如果索引不遵循使用原則，則可能導(dǎo)致索引無(wú)效。

（1）列獨(dú)立

如果需要某個(gè)字段上使用索引，則需要在字段參與的表達(dá)中，保證字段獨(dú)立在一側(cè)。否則索引不會(huì)用到索引, 例如這條sql就不會(huì)用到索引:select * from A where id+1=10;

（2）左原則

Like：匹配模式必須要左邊確定不能以通配符開頭。例如:select * from A where name like '%小明%' ，不會(huì)用到索引，而select * from A where name like '小明%' 就可以用到索引（name字段有建立索引），如果業(yè)務(wù)上需要用到'%小明%'這種方式,有兩種方法:1.可以考慮全文索引,但mysql的全文索引不支持中文；2.只查詢索引列或主鍵列,例如:select name from A where name like '%小明%' 或 select id from A where name like '%小明%' 或 select id,name from A where name like '%小明%' 這三種情況都會(huì)用到name的索引;

復(fù)合索引：一個(gè)索引關(guān)聯(lián)多個(gè)字段，僅僅針對(duì)左邊字段有效果，添加復(fù)合索引時(shí)，第一個(gè)字段很重要，只有包含第一個(gè)字段作為查詢條件的情況才會(huì)使用復(fù)合索引(必須用到建索引時(shí)選擇的第一個(gè)字段作為查詢條件,其他字段的順序無(wú)關(guān)),而且查詢條件只能出現(xiàn)and拼接,不能用or,否則則無(wú)法使用索引.

（3）OR的使用

必須要保證 OR 兩端的條件都存在可以用的索引，該查詢才可以使用索引。

（4）MySQL智能選擇

即使?jié)M足了上面說原則，MySQL也能棄用索引，例如：select * from A where id 1;這里棄用索引的主要原因：查詢即使使用索引，會(huì)導(dǎo)致出現(xiàn)大量的隨機(jī)IO，相對(duì)于從數(shù)據(jù)記錄的第一條遍歷到最后一條的順序IO開銷，還要大。

4.索引的使用場(chǎng)景

（1）索引檢索：檢索數(shù)據(jù)時(shí)使用索引。

（2）索引排序: 如果order by 排序需要的字段上存在索引，則可能使用到索引。

（3）索引覆蓋: 索引擁有的關(guān)鍵字內(nèi)容，覆蓋了查詢所需要的全部數(shù)據(jù)，此時(shí)，就不需要在數(shù)據(jù)區(qū)獲取數(shù)據(jù)，僅僅在索引區(qū)即可。覆蓋就是直接在索引區(qū)獲取內(nèi)容，而不需要在數(shù)據(jù)區(qū)獲取。例如: select name from A where name like '小明%';

建立索引索引時(shí)，不能僅僅考慮where檢索，同時(shí)考慮其他的使用場(chǎng)景。（在所有的where字段上增加索引，就是不合理的）

5.前綴索引

前綴索引是建立索引關(guān)鍵字一種方案。通常會(huì)使用字段的整體作為索引關(guān)鍵字。有時(shí)，即使使用字段前部分?jǐn)?shù)據(jù)，也可以去識(shí)別某些記錄。就比如一個(gè)班級(jí)里，我要找王xx，假如姓王的只有1個(gè)人，那么就可以建一個(gè)關(guān)鍵字為'王'的前綴索引。語(yǔ)法：Index `index_name` (`index_field`(N))使用index_name前N個(gè)字符建立的索引。

6.索引失效

（1） 應(yīng)盡量避免在 where 子句中使用 != 或 操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描；

（2） 應(yīng)盡量避免在 where 子句中使用 or 來(lái)連接條件，如果一個(gè)字段有索引，一個(gè)字段沒有索引，將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描；

（3） 應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行 null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描；

（4）應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描；如select id from t where num/2 = 100；

（5） 應(yīng)盡量避免在where子句中對(duì)字段進(jìn)行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描；如：select id from t where substring(name,1,3) = ’abc’ ；

（6）應(yīng)盡量避免在where子句中對(duì)字段進(jìn)行類型轉(zhuǎn)換，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描； 如果列類型是字符串，那一定要在條件中將數(shù)據(jù)使用引號(hào)引用起來(lái),如select id from t where id = 1；如果id字段在表設(shè)計(jì)中是varchar類型，那么即使id列上存的是數(shù)字，在查詢時(shí)也一定要用varchar去匹配，sql應(yīng)改為select id from t where id = '1'；

（7）應(yīng)盡量避免在where子句中單獨(dú)引用復(fù)合索引里非第一位置的索引；

join 的兩種算法：BNL 和 NLJ

NLJ（Nested Loop Join）嵌套循環(huán)算法；以如下 SQL 為例：

select * from t1 join t2 on t1.a=t2.a

SQL 執(zhí)行時(shí)內(nèi)部流程是這樣的：

1. 先從 t1（假設(shè)這里 t1 被選為驅(qū)動(dòng)表）中取出一行數(shù)據(jù) X；

2. 從 X 中取出關(guān)聯(lián)字段 a 值，去 t2 中進(jìn)行查找，滿足條件的行取出；

3. 重復(fù)1、2步驟，直到表 t1 最后一行循環(huán)結(jié)束。

這就是一個(gè)嵌套循環(huán)的過程，如果在被驅(qū)動(dòng)表上查找數(shù)據(jù)時(shí)可以使用索引，總的對(duì)比計(jì)算次數(shù)等于驅(qū)動(dòng)表滿足 where 條件的行數(shù)。假設(shè)這里 t1、t2都是1萬(wàn)行，則只需要 1萬(wàn)次計(jì)算，這里用到的是Index Nested-Loops Join（INLJ，基于索引的嵌套循環(huán)聯(lián)接）。

如果 t1、t2 的 a 字段都沒有索引，還按照上述的嵌套循環(huán)流程查找數(shù)據(jù)呢？每次在被驅(qū)動(dòng)表上查找數(shù)據(jù)時(shí)都是一次全表掃描，要做1萬(wàn)次全表掃描，掃描行數(shù)等于 1萬(wàn)+1萬(wàn)*1萬(wàn)，這個(gè)效率很低，如果表行數(shù)更多，掃描行數(shù)動(dòng)輒幾百億，所以優(yōu)化器肯定不會(huì)使用這樣的算法，而是選擇 BNL 算法；

BNLJ（Block Nested Loop Join）塊嵌套循環(huán)算法；

1. 把 t1 表（假設(shè)這里 t1 被選為驅(qū)動(dòng)表）滿足條件的數(shù)據(jù)全部取出放到線程的 join buffer 中；

2. 每次取 t2 表一行數(shù)據(jù)，去 joinbuffer 中進(jìn)行查找，滿足條件的行取出，直到表 t2 最后一行循環(huán)結(jié)束。

這個(gè)算法下，執(zhí)行計(jì)劃的 Extra 中會(huì)出現(xiàn) Using join buffer(Block Nested Loop)，t1、t2 都做了一次全表掃描，總的掃描行數(shù)等于 1萬(wàn)+1萬(wàn)。但是由于 joinbuffer 維護(hù)的是一個(gè)無(wú)序數(shù)組，每次在 joinbuffer 中查找都要遍歷所有行，總的內(nèi)存計(jì)算次數(shù)等于1萬(wàn)*1萬(wàn)。另外如果 joinbuffer 不夠大放不下驅(qū)動(dòng)表的數(shù)據(jù)，則要分多次執(zhí)行上面的流程，會(huì)導(dǎo)致被驅(qū)動(dòng)表也做多次全表掃描。

BNLJ相對(duì)于NLJ的優(yōu)點(diǎn)在于，驅(qū)動(dòng)層可以先將部分?jǐn)?shù)據(jù)加載進(jìn)buffer，這種方法的直接影響就是將大大減少內(nèi)層循環(huán)的次數(shù)，提高join的效率。

例如：

如果內(nèi)層循環(huán)有100條記錄，外層循環(huán)也有100條記錄，這樣的話，每次外層循環(huán)先將10條記錄放到buffer中，內(nèi)層循環(huán)的100條記錄每條與這個(gè)buffer中的10條記錄進(jìn)行匹配，只需要匹配內(nèi)層循環(huán)總記錄數(shù)次即可結(jié)束一次循環(huán)（在這里，即只需要匹配100次即可結(jié)束），然后將匹配成功的記錄連接后放入結(jié)果集中，接著，外層循環(huán)繼續(xù)向buffer中放入10條記錄，同理進(jìn)行匹配，并將成功的記錄連接后放入結(jié)果集。后續(xù)循環(huán)以此類推，直到循環(huán)結(jié)束，將結(jié)果集發(fā)給client為止。

可以發(fā)現(xiàn)，若用NLJ，則需要100 * 100次才可結(jié)束，BNLJ則需要100 / block_size * 100 = 10 * 100次就可結(jié)束,大大減少了循環(huán)次數(shù)。

JOIN 按照功能大致分為如下三類：

JOIN、STRAIGHT_JOIN、INNER JOIN（內(nèi)連接,或等值連接）：取得兩個(gè)表中存在連接匹配關(guān)系的記錄。

LEFT JOIN（左連接）：取得左表（table1）完全記錄，即是右表（table2）并無(wú)對(duì)應(yīng)匹配記錄。

RIGHT JOIN（右連接）：與 LEFT JOIN 相反，取得右表（table2）完全記錄，即是左表（table1）并無(wú)匹配對(duì)應(yīng)記錄。

注意：mysql不支持Full join,不過可以通過UNION 關(guān)鍵字來(lái)合并 LEFT JOIN 與 RIGHT JOIN來(lái)模擬FULL join。

mysql 多表連接查詢方式，因?yàn)閙ysql只支持NLJ算法,所以如果是小表驅(qū)動(dòng)大表則效率更高；反之則效率下降；因此mysql對(duì)內(nèi)連接或等值連接的方式做了一個(gè)優(yōu)化,會(huì)去判斷join表的數(shù)據(jù)行大小,然后取數(shù)據(jù)行小的表為驅(qū)動(dòng)表。

INNER JOIN、JOIN、WHERE等值連接和STRAIGHT_JOIN都能表示內(nèi)連接，那平時(shí)如何選擇呢？一般情況下用INNER JOIN、JOIN或者WHERE等值連接，因?yàn)镸ySQL 會(huì)按照"小表驅(qū)動(dòng)大表的策略"進(jìn)行優(yōu)化。當(dāng)出現(xiàn)需要排序時(shí)，才考慮用STRAIGHT_JOIN指定某張表為驅(qū)動(dòng)表。

兩表JOIN優(yōu)化

a.當(dāng)無(wú)order by條件時(shí)，根據(jù)實(shí)際情況，使用left/right/inner join即可，根據(jù)explain優(yōu)化 ；

b.當(dāng)有order by條件時(shí)，如select * from a inner join b where 1=1 and other condition order by a.col；使用explain解釋語(yǔ)句；

1）如果第一行的驅(qū)動(dòng)表為a，則效率會(huì)非常高，無(wú)需優(yōu)化；

2）否則，因?yàn)橹荒軐?duì)驅(qū)動(dòng)表字段直接排序的緣故，會(huì)出現(xiàn)using temporary，所以此時(shí)需要使用STRAIGHT_JOIN明確a為驅(qū)動(dòng)表，來(lái)達(dá)到使用a.col上index的優(yōu)化目的；或者使用left join且Where條件中不含b的過濾條件，此時(shí)的結(jié)果集為a的全集，而STRAIGHT_JOIN為inner join且使用a作為驅(qū)動(dòng)表。注：使用STRAIGHT_JOIN雖然不會(huì)using temporary，但也不是一定就能提高效率，如果a表數(shù)據(jù)遠(yuǎn)遠(yuǎn)超過b表，那么有可能使用STRAIGHT_JOIN時(shí)比原來(lái)的sql效率更低，所以怎么使用STRAIGHT_JOIN，還是要視情況而定。

在使用left join（或right join）時(shí)，應(yīng)該清楚的知道以下幾點(diǎn)：

(1). on與 where的執(zhí)行順序

ON 條件（“A LEFT JOIN B ON 條件表達(dá)式”中的ON）用來(lái)決定如何從 B 表中檢索數(shù)據(jù)行。如果 B 表中沒有任何一行數(shù)據(jù)匹配 ON 的條件,將會(huì)額外生成一行所有列為 NULL 的數(shù)據(jù),在匹配階段 WHERE 子句的條件都不會(huì)被使用。僅在匹配階段完成以后，WHERE 子句條件才會(huì)被使用。它將從匹配階段產(chǎn)生的數(shù)據(jù)中檢索過濾。

所以我們要注意：在使用Left (right) join的時(shí)候，一定要在先給出盡可能多的匹配滿足條件，減少Where的執(zhí)行。

(2).注意ON 子句和 WHERE 子句的不同

即使右表的數(shù)據(jù)不滿足ON后面的條件,也會(huì)在結(jié)果集拼接一條為NULL的數(shù)據(jù)行,但WHERE后面的條件不一樣,右表不滿足WHERE的條件,左表關(guān)聯(lián)的數(shù)據(jù)也會(huì)被過濾掉。

(3).盡量避免子查詢，而用join

往往性能這玩意兒，更多時(shí)候體現(xiàn)在數(shù)據(jù)量比較大的時(shí)候，此時(shí)，我們應(yīng)該避免復(fù)雜的子查詢。

（1）in 和 not in 要慎用，如：select id from t where num in(1,2,3)對(duì)于連續(xù)的數(shù)值，能用 between 就不要用 in：select id from t where num between 1 and 3很多時(shí)候用 exists 代替 in 是一個(gè)好的選擇：select num from a where num in(select num from b)用下面的語(yǔ)句替換：select num from a where exists(select 1 from b where num=a.num)

（2）Update 語(yǔ)句，如果只更改1、2個(gè)字段，不要Update全部字段，否則頻繁調(diào)用會(huì)引起明顯的性能消耗，同時(shí)帶來(lái)大量日志。

（3）join語(yǔ)句,MySQL里面的join是用小表去驅(qū)動(dòng)大表,而由于MySQL join實(shí)現(xiàn)的原理就是做循環(huán)，比如left join就是對(duì)左邊的數(shù)據(jù)進(jìn)行循環(huán)去驅(qū)動(dòng)右邊的表,左邊有m條記錄匹配,右邊有n條記錄那么就是做m次循環(huán),每次掃描n行數(shù)據(jù),總掃面行數(shù)是m*n行數(shù)據(jù)。左邊返回的結(jié)果集的大小就決定了循環(huán)的次數(shù),故單純的用小表去驅(qū)動(dòng)大表不一定的正確的,小表的結(jié)果集可能也大于大表的結(jié)果集,所以寫join的時(shí)候盡可能的先估計(jì)兩張表的可能結(jié)果集,用小結(jié)果集去驅(qū)動(dòng)大結(jié)果集.值得注意的是在使用left/right join的時(shí)候,從表的條件應(yīng)寫在on之后,主表應(yīng)寫在where之后.否則MySQL會(huì)當(dāng)作普通的連表查詢；

（4）select count(*) from table；這樣不帶任何條件的count會(huì)引起全表掃描，并且沒有任何業(yè)務(wù)意義，是一定要杜絕的；

（5）select * from t 這種語(yǔ)句要盡量避免，使用具體的字段代替*，更有實(shí)際意義，需要什么字段就返回什么字段；

（6）數(shù)據(jù)量大的情況下，limit要慎用，因?yàn)槭褂胠imit m，n方式分頁(yè)時(shí)，mysql每次都是查詢前m+n條，然后舍棄前m條，所以m越大，偏移量越大，性能就越差。比如：select * from A limit 1000000,20這鐘，查詢效率就會(huì)非常低，當(dāng)分頁(yè)的頁(yè)數(shù)大于一定的數(shù)量之后，就可以換種方式來(lái)分頁(yè)：select * from A a join （select id from A limit 1000000，20） b on a.id=b.id;

MySQL的全文索引Fulltext Index | 包括ngram

InnoDB的全文索引使用反向索引的設(shè)計(jì)。反向索引存儲(chǔ)了一個(gè)單詞（word）列表，對(duì)于每個(gè)單詞，都有一個(gè)文檔的列表，來(lái)標(biāo)識(shí)這個(gè)單詞出現(xiàn)的地方。為了支持臨近搜索（proximity search），每個(gè)單詞的位置信息也以字節(jié)偏移的方式存儲(chǔ)。

當(dāng)創(chuàng)建了InnoDB全文索引，一系列的索引表會(huì)一同被創(chuàng)建，見下面的例子：

最前面的六個(gè)表包含了反向索引，它們被稱作附屬索引表（auxiliary index table）。當(dāng)輸入的表被索引（tokenized）后，每個(gè)獨(dú)立的單詞（亦稱作“tokens”）會(huì)被攜帶其DOC_ID和位置信息插入到索引表中。根據(jù)單詞第一個(gè)字符的字符集排序權(quán)重，在六個(gè)索引表中對(duì)單詞進(jìn)行完全排序和分區(qū)。

反向索引分區(qū)到六個(gè)附屬索引表以支持并行的索引創(chuàng)建。默認(rèn)有2個(gè)線程復(fù)制索引（Tokenize）、排序、插入單詞和關(guān)聯(lián)數(shù)據(jù)到索引表中。工作的線程的數(shù)量由 innodb_ft_sort_pll_degree 配置項(xiàng)控制的。對(duì)于大表的全文索引，可以考慮增加線程數(shù)量。

如果主表創(chuàng)建在 xx表空間，索引表存儲(chǔ)在它們自己的表空間中。反之，索引表存儲(chǔ)于其索引的表空間中。

前面例子展示的另外一種索引表被稱作通用索引表，它們被用于全文索引的“刪除處理（deletion handing）”和存儲(chǔ)內(nèi)部狀態(tài)。不同于為每個(gè)全文索引都各自創(chuàng)建的反向索引表，這組表對(duì)特定表的所有全文索引都是共用的。

即使全文索引刪掉了，通用索引（Common Index）也會(huì)被保留，當(dāng)全文索引刪除后，為這個(gè)索引而創(chuàng)建的FTS_DOC_ID列依然保留，因?yàn)橐瞥鼺TS_DOC_ID列會(huì)導(dǎo)致重構(gòu)之前被索引的表。管理FTS_DOC_ID列需要用到通用索引表。

為了防止大量并發(fā)讀寫附屬表，InnoDB使用全文索引緩存去臨時(shí)緩存最近的插入行。在存滿并刷入磁盤之前，緩存的內(nèi)容一直存儲(chǔ)在內(nèi)存之中，可以通過查詢 INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE 表去查看最近緩存的插入行。

緩存和批處理刷新行為避免了對(duì)輔助索引表的頻繁更新，頻繁更新可能會(huì)在繁忙的插入和更新期間導(dǎo)致并發(fā)訪問問題。批處理還避免了對(duì)同一個(gè)word的多次插入，最大化的減少了重復(fù)的條目。相同的word會(huì)先merge再刷入到磁盤中，而不是為每個(gè)word單獨(dú)插入，這樣提高了插入效率并且使得索引附屬表盡可能的小。

全文索引緩存只緩存最近插入的行，查詢時(shí)，已經(jīng)刷入磁盤（附屬索引表）的數(shù)據(jù)不會(huì)再回到索引緩存中。附屬索引表中的內(nèi)容是直接查詢的，最終返回的結(jié)果返回前需要將附屬索引表的結(jié)果和索引緩存中的結(jié)果合并。

InnoDB使用被稱作DOC_ID的唯一文件描述符，將全文索引中的單詞與該單詞在文檔中的記錄映射起來(lái)。映射關(guān)系需要索引表中的 FTS_DOC_ID 列。在創(chuàng)建全文索引時(shí)，如果沒有定義 FTS_DOC_ID 列，InnoDB會(huì)自動(dòng)的加入一個(gè)隱藏的 FTS_DOC_ID 列。下面是一個(gè)例子，

CREATE FULLTEXT INDEX ft_index ON xxxxxxxx(CONTEXT)

[2021-11-12 18:14:04] [HY000][124] InnoDB rebuilding table to add column FTS_DOC_ID

重點(diǎn)看一下這一行： [HY000][124] InnoDB rebuilding table to add column FTS_DOC_ID ，InnoDB重新構(gòu)建了這個(gè)表，并且添加了一個(gè)列 FTS_DOC_ID 。

在CREATE TABLE的過程中添加 FTS_DOC_ID 的時(shí)間成本要低于在已經(jīng)有數(shù)據(jù)的表上建立全文索引。如果在表加載數(shù)據(jù)之前定義 FTS_DOC_ID 列，這個(gè)表和它的索引都不需要為了新增列而重新構(gòu)建。如果你不需要考慮 CREATE FULLTEXT INDEX 的性能，可以讓InnoDB為你創(chuàng)建 FTS_DOC_ID 列。InnoDB會(huì)新增一個(gè)隱藏的 FTS_DOC_ID 列，并且在 FTS_DOC_ID 上建立唯一索引（FTS_DOC_ID_INDEX）。如果你想自行創(chuàng)建 FTS_DOC_ID 列，這個(gè)列必須定義為 BIGINT UNSIGNED NOT NULL 且命名為FTS_DOC_ID（全大寫），如下例子：

如果你自行定義 FTS_DOC_ID 列的話，你需要負(fù)責(zé)管理這個(gè)列，避免空值（empty）或者重復(fù)值。 FTS_DOC_ID 的值是不能被重復(fù)利用的，所以也就是說 FTS_DOC_ID 的值是需要一直增加的。

或者，你可以在 FTS_DOC_ID 列上創(chuàng)建所必須的唯一索引FTS_DOC_ID_INDEX（全大寫）。

mysql CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on opening_lines(FTS_DOC_ID);

如果你沒有創(chuàng)建FTS_DOC_ID_INDEX，InnoDB會(huì)自動(dòng)創(chuàng)建。

在MySQL 5.7.13前，允許最大FTS_DOC_ID與最新的FTS_DOC_ID之間的間隔為10000，在MySQL 5.7.13及之后的版本中，這個(gè)允許的間隔為65535。

為了避免重新構(gòu)建表，F(xiàn)TS_DOC_ID列在刪除了全文索引之后依然被保留。

刪除被索引文件的一個(gè)記錄，可能會(huì)在附屬索引表中產(chǎn)生非常多的小的刪除項(xiàng)，在并發(fā)訪問時(shí)，會(huì)產(chǎn)生熱點(diǎn)問題。為了避免這個(gè)問題，每當(dāng)被索引表中的記錄被刪除時(shí)，會(huì)將被刪文檔的DOC_ID記錄在一個(gè)特別的 FTS_*_DELETED 表中，同時(shí)全文索引中已經(jīng)索引了的記錄依然被保存。在返回查詢結(jié)果前，使用 FTS_*_DELETED 中的信息去過濾掉已經(jīng)刪除掉了的DOC_ID。這種設(shè)計(jì)的優(yōu)勢(shì)在于刪除速度快且消耗低。不好的地方在于索引的大小不能隨著記錄的刪除而立即減少。為了刪除已刪除記錄在全文索引中的項(xiàng)，需要對(duì)被索引的表執(zhí)行OPTIMIZE TABLE，配合[ innodb_optimize_fulltext_only=ON ]，去重構(gòu)全文索引。

細(xì)節(jié)略，有例子：

全文搜索只能看到已經(jīng)提交了的數(shù)據(jù)。

你可以通過查詢下面的INFORMATION_SCHEMA表，來(lái)監(jiān)控或測(cè)試InnoDB的一些特殊文本處理。

默認(rèn)的分詞器不支持中文，不能檢索到中文中的英文單詞。

InnoDB默認(rèn)的Stopwords：

select * from information_schema.INNODB_FT_DEFAULT_STOPWORD;

SQL中的關(guān)鍵詞（保留關(guān)鍵字）：

Shell中的關(guān)鍵詞：for，while，echo

其他：###, ***, --,

被索引表數(shù)據(jù)量、索引表數(shù)據(jù)量

模糊匹配與嚴(yán)格匹配的性能差距

需要將 innodb_optimize_fulltext_only 配置為ON，這里是否需要DBA在MySQL鏡像中修改？

innodb_optimize_fulltext_only 設(shè)置為ON后，對(duì)系統(tǒng)有何影響需要評(píng)估。

innodb_optimize_fulltext_only

執(zhí)行的時(shí)間、頻率。

MySQL內(nèi)建的全文檢索解析器使用單詞之間的空白作為分隔符以標(biāo)識(shí)單詞的頭尾，但是這里有個(gè)限制，對(duì)于表意文字，它是沒有單詞分隔符的。為了解決這個(gè)限制，MySQL提供了支持中文、日語(yǔ)、韓語(yǔ)的 ngram 解析器。ngram解析器支持InnoDB和MyISAM。

Ngram是內(nèi)建在服務(wù)中的插件，像其他自建在服務(wù)中的插件一樣，服務(wù)啟動(dòng)時(shí)會(huì)自動(dòng)加載它。全文檢索的語(yǔ)法參考上面（ Section 12.10, “Full-Text Search Functions” ），這里只討論一些不同的地方。除了單詞的最小、最大長(zhǎng)度配置項(xiàng)（[ innodb_ft_min_token_size ]innodb_ft_max_token_size,ft_min_word_len,ft_max_word_len，全文檢索依賴一些配置項(xiàng)都是可以使用的。

Ngram默認(rèn)索引的單詞（token）的大小為2（ 2bigram ）。例如，索引的大小為2，Ngram解析器解析字符串“abc def”為四個(gè)單詞元素(tokens)：“ab”, “bc”, “de” and “ef”。

ngram token size is configurable using the ngram_token_size configuration option, which has a minimum value of 1 and maximum value of 10.

作為只讀變量, ngram_token_size 只能在啟動(dòng)配置或者配置文件中指定

與默認(rèn)的解析器相差不大，多了一句： xxx WITH PARSER ngram

Ngram在解析時(shí)去除空格，如

MySQL內(nèi)建的默認(rèn)全文檢索解析器將單詞與Stopword列表中的做對(duì)比，如果單詞與Stopword列表中的元素相同的話，這個(gè)單詞則不會(huì)被索引。對(duì)于Ngram解析器，Stopword的處理方式不同。Ngram解析器不排除與stopword列表中的條目相等的token，而是排除包含stopwords的token。例如，假設(shè) ngram_token_size=2 ，包含“a,b”的文檔將被解析為 “a,” h和“,b”。如果將逗號(hào)（“,”）定義為停止字，則 “a,”和“,b”都將不會(huì)加入索引中，因?yàn)樗鼈儼禾?hào)。

例子：

默認(rèn)Ngram解析器使用默認(rèn)的Stopword列表，這里面含有英文的Stopword。如果需要中文的Stopword，需要你自己創(chuàng)建。

Stopword的長(zhǎng)度超過 ngram_token_size則會(huì)被忽略。

有兩個(gè)文檔，一個(gè)包含“ab”，另一個(gè)包含“abc”。對(duì)于搜索文本“abc”將轉(zhuǎn)換成“ab”，“bc”。

略。

For example, The search phrase “abc” is converted to “ab bc”, which returns documents containing “abc” and “ab bc”.

The search phrase “abc def” is converted to “ab bc de ef”, which returns documents containing “abc def” and “ab bc de ef”. A document that contains “abcdef” is not returned.

使用Ngram解析器好處是支持了中文的檢索

當(dāng)前文章：mysql全文索引怎么樣,全文索引 mysql
URL分享：http://jinyejixie.com/article38/dssspsp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供面包屑導(dǎo)航、品牌網(wǎng)站建設(shè)、網(wǎng)站營(yíng)銷、網(wǎng)頁(yè)設(shè)計(jì)公司、網(wǎng)站建設(shè)、全網(wǎng)營(yíng)銷推廣

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)