首先說說索引的 優(yōu)點 ：最大的好處無疑就是提高查詢效率。有的索引還能保證數(shù)據(jù)的唯一性，比如唯一索引。
專注于為中小企業(yè)提供成都網(wǎng)站設計、成都網(wǎng)站制作、外貿(mào)網(wǎng)站建設服務,電腦端+手機端+微信端的三站合一,更高效的管理,為中小企業(yè)太子河免費做網(wǎng)站提供優(yōu)質(zhì)的服務。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動了1000+企業(yè)的穩(wěn)健成長，幫助中小企業(yè)通過網(wǎng)站建設實現(xiàn)規(guī)模擴充和轉(zhuǎn)變。
而它的 壞處 也很明顯：索引也是文件，我們在創(chuàng)建索引時，也會創(chuàng)建額外的文件，所以會占用一些硬盤空間。其次，索引也需要維護，我們在增加刪除數(shù)據(jù)的時候，索引也需要去變化維護。當一個表的索引多了以后，資源消耗是很大的，所以必須結(jié)合實際業(yè)務再去確定給哪些列加索引。
再說說索引的基本結(jié)構。一說到這里肯定會脫口而出：B+樹！了解B+樹前先要了解二叉查找樹和二叉平衡樹。 二叉查找樹 ：左節(jié)點比父節(jié)點小，右節(jié)點比父節(jié)點大，所以二叉查找樹的中序遍歷就是樹的各個節(jié)點從小到大的排序。 二叉平衡樹 ：左右子樹高度差不能大于1。B+樹就是結(jié)合了它們的特點，當然，不一定是二叉樹。
為什么要有二叉查找樹的特點？？ 因為查找效率快，二分查找在這種結(jié)構下，查找效率是很快的。 那為什么要有平衡樹的特點呢？ 試想，如果不維護一顆樹的平衡性，當插入一些數(shù)據(jù)后，樹的形態(tài)有可能變得很極端，比如左子樹一個數(shù)據(jù)沒有，而全在右子樹上，這種情況下，二分查找和遍歷有什么區(qū)別呢？而就是因為這些特點需要去維護，所以就有了上面提到的缺點，當索引很多后，反而增加了系統(tǒng)的負擔。
接著說B+樹。 它的結(jié)構如下 ：
可以發(fā)現(xiàn)，葉子節(jié)點其實是一個 雙向循環(huán)鏈表 ，這種結(jié)構的好處就是，在范圍查詢的時候，我只用找到一個數(shù)據(jù)，就可以直接返回剩余的數(shù)據(jù)了。比如找小于30的，只用找到30，其余的直接通過葉子節(jié)點間的指針就可以找到。再說說其他特點： 數(shù)據(jù)只存在于葉子節(jié)點 。當葉子節(jié)點滿了，如果再添加數(shù)據(jù)，就會拆分葉子節(jié)點，父節(jié)點就多了個子節(jié)點。如果父節(jié)點的位置也滿了，就會擴充高度，就是拆分父節(jié)點，如25 50 75拆分成：25為左子樹，75為右子樹，50變成新的頭節(jié)點，此時B+樹的高度變成了3。它們的擴充的規(guī)律如下表，Leaf Page是葉子節(jié)點，index Page是非葉子節(jié)點。
再說說B樹 ，B樹相比較B+樹，它所有節(jié)點都存放數(shù)據(jù)，所以在查找數(shù)據(jù)時，B樹有可能沒到達葉子節(jié)點就結(jié)束了。再者，B樹的葉子節(jié)點間不存在指針。
最后說說Hash索引 ，相較于B+樹，Hash索引最大的優(yōu)點就是查找數(shù)據(jù)快。但是Hash索引最大的問題就是不支持范圍查詢。試想，如果查詢小于30的數(shù)據(jù)，hash函數(shù)是根據(jù)數(shù)據(jù)的值找到其對應的位置，誰又知道小于30的有哪幾個數(shù)據(jù)。而B+樹正好相反，范圍查詢是它的強項。
附錄： Hash到底是啥？？ 哈希中文名散列，哈希只是它的音譯。 為啥都說Hash快？？ 首先有一塊哈希表（散列表），它的數(shù)據(jù)結(jié)構是個數(shù)組，一個任意長度的數(shù)據(jù)通過hash函數(shù)都可以變成一個固定長度的數(shù)據(jù)，叫hash值。然后通過hash值確定在數(shù)組中的位置，相同數(shù)據(jù)的hash值是相同的，所以我們存儲一個數(shù)據(jù)以后，只需O(1)的時間復雜度就可以找到數(shù)據(jù)。 那hash函數(shù)又是啥？？ 算術運算或位運算，很多應用里都有hash函數(shù)，但實際運算過程大不一樣。這是Java里String的hashCode方法：
publicint hashCode() {
}
還有一個問題，hash函數(shù)計算出來的hash值有可能存在碰撞，即兩個不同的數(shù)據(jù)可能存在相同的hash值，在MySQL或其他的應用中，如Java的HashMap等，如果存在碰撞就會以當前數(shù)組位置為頭節(jié)點，轉(zhuǎn)變成一個鏈表。
說到這里也清楚了為啥Java中引用類型要同時重寫hashCode和equals了。兩個對象，實例就算一模一樣，它們的hash值也不相等， 為啥不相等？？ 默認的Object的hashCode方法會根據(jù)對象來計算hash值的，實例相同，但它們還是兩個不同的對象啊，所以我們重寫hashCode時，最簡單的方法就是調(diào)用Object的hashCode方法，然后傳入該引用類型的屬性，讓hashCode方法只根據(jù)這幾個屬性來計算，那么實例相同的話，它們的hash值也會相等。等hashCode比較完后，如果相等再比較實例內(nèi)容，也就是equals，確保不是hash碰撞。
索引的分類
如果我們指定了一個主鍵，那么這個主鍵就是主鍵索引。如果我們沒有指定，Mysql就會自動找一個非空的唯一索引當主鍵。如果沒有這種字段，Mysql就會創(chuàng)建一個大小為6字節(jié)的自增主鍵。如果有多個非空的唯一索引，那么就讓第一個定義為唯一索引的字段當主鍵，注意，是第一個定義，而不是建表時出現(xiàn)在前面的。
對于輔助索引來說，它們的B+樹結(jié)構稍微有點特殊，它們的葉子節(jié)點存儲的是主鍵，而不是整個數(shù)據(jù)。所以在大部分情況下，使用輔助索引查找數(shù)據(jù)，需要二次查找。但并不是所有情況都需要二次查找。比如查找的數(shù)據(jù)正好就是當前索引字段的值，那么直接返回就行。這里提一句，B+樹的key就是對應索引字段的內(nèi)容。
而輔助索引又有一些分類：唯一索引：不能出現(xiàn)重復的值，也算一種約束。普通索引：可以重復、可以為空，一般就是查詢時用到。前綴索引：只適用于字符串類型數(shù)據(jù)，對字符串前幾個字符創(chuàng)建索引。全文索引：作用是檢測大文本數(shù)據(jù)中某個關鍵字，這也是搜索引擎的一種技術。
注意，聚集索引、非聚集索引和前面幾個索引的分類并不是一個層面上的。上面的幾個分類是從索引的作用來分析的。聚集、非聚集索引是從索引文件上區(qū)分的。主鍵索引就屬于聚集索引，即索引和數(shù)據(jù)存放在一起，葉子節(jié)點存放的就是數(shù)據(jù)。數(shù)據(jù)表的.idb文件就是存放該表的索引和數(shù)據(jù)。
輔助索引屬于非聚集索引，說到這也就明白了。索引和數(shù)據(jù)不存放在一起的就是非聚集索引。在MYISAM引擎中，數(shù)據(jù)表的.MYI文件包含了表的索引， 該表的 葉子節(jié)點存儲索引和索引對應數(shù)據(jù)的指針，指向.MYD文件的數(shù)據(jù)。
索引的幾點使用經(jīng)驗
經(jīng)常被查詢的字段；經(jīng)常作為條件查詢的字段；經(jīng)常用于外鍵連接或普通的連表查詢時進行相等比較字段；不為null的字段；如果是多條件查詢，最好創(chuàng)建聯(lián)合索引，因為聯(lián)合索引只有一個索引文件。
經(jīng)常被更新的字段、不經(jīng)常被查詢的字段、存在相同功能的字段
從explain開始說起吧，很顯然第一個sql語句壓根沒用任何索引（key列內(nèi)什么都沒有）！第二個倒是用到索引，卻是主鍵索引，并非你添加的fulltext索引！
接下來，分析下原因：
sql1：執(zhí)行步驟：先s_a和s_a_t兩表笛卡爾集，然后篩選滿足on條件的，接著在從結(jié)果集中篩選滿足where字句的；該過程中處理的記錄條目為69*105479，并且未用到任何索引，未用到的原因可能是你先定義了一個復合索引a_concent_split（a_title_split，a_content_split），然后又定義了一個a_content_split2（a_content_split），當引擎執(zhí)行查找優(yōu)化時候會先用到a_content_split，可是又由于復合索引是從最左邊開始（不能跳過第一個字段），而你卻忽略了a_title_split字段，故未能正常使用索引。
sql2：執(zhí)行步驟：先調(diào)用where字句對s_a表進行篩選形成新的s_a表，然后與s_a_t表笛卡爾積，再利用on字句篩選，最后再次利用where字句形成最終結(jié)果集；經(jīng)過第一個where，該過程處理結(jié)果集會大幅少于sql1，并且該過程還用到了主鍵索引。你所設置的fulltext索引再次沒有用到，原因是like字句中開始部分為模糊匹配%時候用不了全文索引，這與fulltext存儲機制有關。
另，你說的刪除速度慢，原因：設置fulltext字段設置太多，fulltext索引在更新刪除大量數(shù)據(jù)時候，需要同步更改索引，你的三個fulltext壓力太大！
改進方法：1、刪除a_content_split索引重試 2、在刪除時候打開delay_key_write變量
有關fulltext比較復雜，用的時候要謹慎設置，還有很多參數(shù)也對其有影響
另外sql語句中外連接有關on where字句也是個比較繞的地方，兩者你都占了，唉，所以我寫的略復雜，前天看到該問題，思忖兩天這才作答
望有結(jié)果了予以回復交流！
先正面回答你的問題
數(shù)據(jù)是否重復不是建立索引的重要依據(jù)，甚至都不是依據(jù)。
只要不完全重復（所有元組的該元素都一樣），那么建立索引就是有意義的。
即使當前數(shù)據(jù)完全重復，也不是不能建立索引，這種情況有點復雜，不細說了。
對于你后面的疑問，可以給你一個如何建立索引的忠告，“如何查就如何建”。
索引的建立，唯一的原因就是為了查詢（廣義的查詢），實際上建立索引會使得數(shù)據(jù)存儲所占空間變大，有時索引所占的空間會查過數(shù)據(jù)本身的空間。索引的建立也會使得數(shù)據(jù)插入時變慢，特殊情況下，慢的難以忍受，所以dba的重要工作之一，就是檢查索引層級并優(yōu)化。
索引建立的唯一好處，就是按照索引查詢時，變快了。type，status這2個字段是否適合建立索引，就要看你是否要按照這2個字段進行檢索。而檢索的順序決定了如何建立索引。
對于索引類型和索引方式，我建議就
normal
和
btree
就適用于大多數(shù)情況。若你參與的是一個大數(shù)據(jù)處理項目，對數(shù)據(jù)存儲和檢索有特別要求，那么需要分析多個層面，比如數(shù)據(jù)吞吐量、數(shù)據(jù)的方差、平均差等等很多參數(shù)才考慮是否用聚集索引等（mysql好像還沒聚集索引），至于是否是唯一索引，我建議不使用，即使能判定數(shù)據(jù)是唯一的也不要用，全文索引也沒有必要。
MySQL支持全文索引和搜索功能。在MySQL中可以在CHAR、VARCHAR或TEXT列使用FULLTETXT來創(chuàng)建全文索引。
FULLTEXT索引主要用MATCH()...AGAINST語法來實現(xiàn)搜索：
MySQL的全文搜索存在以下局限：
通常來說MySQL自帶的全文搜索使用起來局限性比較大，性能和功能都不太成熟，主要適用于小項目，大項目還是建議使用elasticsearch來做全文搜索。
ElasticSearch是一個分布式的開源搜索和分析引擎，適用于所有類型的數(shù)據(jù)，包括文本、數(shù)字、地理空間、結(jié)構化和非結(jié)構化數(shù)據(jù)，以下簡稱ES。
Elasticsearch 在 Apache Lucene 的基礎上開發(fā)而成，Elasticsearch 以其簡單的 REST 風格 API、分布式特性、速度和可擴展性而聞名，是 Elastic Stack 的核心組件。Elastic Stack 是適用于數(shù)據(jù)采集、充實、存儲、分析和可視化的一組開源工具。
Elasticsearch 的實現(xiàn)原理主要分為以下幾個步驟，首先用戶將數(shù)據(jù)提交到Elasticsearch 數(shù)據(jù)中心，再通過分詞控制器去將對應的數(shù)據(jù)分詞，將其權重和分詞結(jié)果一并存入數(shù)據(jù)，當用戶搜索數(shù)據(jù)時候，再根據(jù)權重將結(jié)果排名，打分，再將返回結(jié)果呈現(xiàn)給用戶。
由于ES是基于RESTfull Web接口的，因此我們直接按照慣例傳遞JSON參數(shù)調(diào)用接口即可實現(xiàn)增刪改查，并且不需要我們做額外的管理操作就可以直接索引文檔，ES已經(jīng)內(nèi)置了所有的缺省操作，可以自動幫我們定義類型。
再次執(zhí)行PUT，會對庫中已有的id為1的數(shù)據(jù)進行覆蓋，每修改一次_version字段的版本號就會加1。
默認搜索會返回前10個結(jié)果：
返回的幾個關鍵詞：
查詢字符串搜索，可以像傳遞URL參數(shù)一樣傳遞查詢語句。
精確查詢：
全文搜索：
以上兩種方法都需要考慮數(shù)據(jù)更改后如何與ES進行同步。
1、InnoDB存儲引擎
Mysql版本=5.5 默認的存儲引擎，MySQL推薦使用的存儲引擎。支持事務，行級鎖定，外鍵約束。事務安全型存儲引擎。更加注重數(shù)據(jù)的完整性和安全性。
存儲格式 : 數(shù)據(jù)，索引集中存儲，存儲于同一個表空間文件中。
InnoDB的行鎖模式及其加鎖方法： InnoDB中有以下兩種類型的行鎖：共享鎖（讀鎖： 允許事務對一條行數(shù)據(jù)進行讀?。┖?互斥鎖（寫鎖： 允許事務對一條行數(shù)據(jù)進行刪除或更新）， 對于update，insert，delete語句，InnoDB會自動給設計的數(shù)據(jù)集加互斥鎖，對于普通的select語句，InnoDB不會加任何鎖。
InnoDB行鎖的實現(xiàn)方式： InnoDB行鎖是通過給索引上的索引項加鎖來實現(xiàn)的，如果沒有索引，InnoDB將通過隱藏的聚簇索引來對記錄加鎖。InnoDB這種行鎖實現(xiàn)特點意味著：如果不通過索引條件檢索數(shù)據(jù)，那么InnoDB將對表中的所有記錄加鎖，實際效果跟表鎖一樣。
（1）在不通過索引條件查詢時，InnoDB會鎖定表中的所有記錄。
（2）Mysql的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果使用相同的索引鍵，是會出現(xiàn)沖突的。
（3）當表有多個索引的時候，不同的事務可以使用不同的索引鎖定不同的行，但都是通過行鎖來對數(shù)據(jù)加鎖。
優(yōu)點：
1、支持事務處理、ACID事務特性；
2、實現(xiàn)了SQL標準的四種隔離級別（ 原子性（ Atomicity ）、一致性（ Consistency ）、隔離性（Isolation ）和持續(xù)性（Durability ））；
3、支持行級鎖和外鍵約束；
4、可以利用事務日志進行數(shù)據(jù)恢復。
5、鎖級別為行鎖，行鎖優(yōu)點是適用于高并發(fā)的頻繁表修改，高并發(fā)是性能優(yōu)于 MyISAM。缺點是系統(tǒng)消耗較大。
6、索引不僅緩存自身，也緩存數(shù)據(jù)，相比 MyISAM 需要更大的內(nèi)存。
缺點：
因為它沒有保存表的行數(shù)，當使用COUNT統(tǒng)計時會掃描全表。
使用場景：
(1)可靠性要求比較高，或者要求事務；(2)表更新和查詢都相當?shù)念l繁，并且表鎖定的機會比較大的情況。
2、 MyISAM存儲引擎
MySQL= 5.5 MySQL默認的存儲引擎。ISAM：Indexed Sequential Access Method(索引順序存取方法)的縮寫，是一種文件系統(tǒng)。擅長與處理，高速讀與寫。
功能：
（1）支持數(shù)據(jù)壓縮存儲,但壓縮后的表變成了只讀表，不可寫；如果需要更新數(shù)據(jù)，則需要先解壓后更新。
（2）支持表級鎖定，不支持高并發(fā)；
（3）支持并發(fā)插入。寫操作中的插入操作，不會阻塞讀操作（其他操作）；
優(yōu)點：
1.高性能讀??；
2.因為它保存了表的行數(shù)，當使用COUNT統(tǒng)計時不會掃描全表；
缺點：
1、鎖級別為表鎖，表鎖優(yōu)點是開銷小，加鎖快；缺點是鎖粒度大，發(fā)生鎖沖動概率較高，容納并發(fā)能力低，這個引擎適合查詢?yōu)橹鞯臉I(yè)務。
2、此引擎不支持事務，也不支持外鍵。
3、INSERT和UPDATE操作需要鎖定整個表；
使用場景：
(1)做很多count 的計算；(2)插入不頻繁，查詢非常頻繁；(3)沒有事務。
InnoDB和MyISAM一些細節(jié)上的差別：
1、InnoDB不支持FULLTEXT類型的索引，MySQL5.6之后已經(jīng)支持（實驗性）。
2、InnoDB中不保存表的 具體行數(shù)，也就是說，執(zhí)行select count() from table時，InnoDB要掃描一遍整個表來計算有多少行，但是MyISAM只要簡單的讀出保存好的行數(shù)即可。注意的是，當count()語句包含 where條件時，兩種表的操作是一樣的。
3、對于AUTO_INCREMENT類型的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中，可以和其他字段一起建立聯(lián)合索引。
4、DELETE FROM table時，InnoDB不會重新建立表，而是一行一行的刪除。
5、LOAD TABLE FROM MASTER操作對InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，導入數(shù)據(jù)后再改成InnoDB表，但是對于使用的額外的InnoDB特性（例如外鍵）的表不適用。
6、另外，InnoDB表的行鎖也不是絕對的，如果在執(zhí)行一個SQL語句時MySQL不能確定要掃描的范圍，InnoDB表同樣會鎖全表。
1．索引概述
利用關鍵字，就是記錄的部分數(shù)據(jù)（某個字段，某些字段，某個字段的一部分），建立與記錄位置的對應關系，就是索引。索引的關鍵字一定是排序的。索引本質(zhì)上是表字段的有序子集，它是提高查詢速度最有效的方法。一個沒有建立任何索引的表，就相當于一本沒有目錄的書，在每次查詢時就會進行全表掃描，這樣會導致查詢效率極低、速度也極慢。如果建立索引，那么就好比一本添加的目錄，通過目錄的指引，迅速翻閱到指定的章節(jié)，提升的查詢性能，節(jié)約了查詢資源。
2．索引種類
從索引的定義方式和用途中來看：主鍵索引，唯一索引，普通索引，全文索引。
無論任何類型，都是通過建立關鍵字與位置的對應關系來實現(xiàn)的。索引是通過關鍵字找對應的記錄的地址。
以上類型的差異：對索引關鍵字的要求不同。
關鍵字：記錄的部分數(shù)據(jù)（某個字段，某些字段，某個字段的一部分）。
普通索引,index：對關鍵字沒有要求。
唯一索引,unique index：要求關鍵字不能重復。同時增加唯一約束。
主鍵索引,primary key：要求關鍵字不能重復，也不能為NULL。同時增加主鍵約束。
全文索引,fulltext key：關鍵字的來源不是所有字段的數(shù)據(jù)，而是從字段中提取的特別關鍵詞。
PS：這里主鍵索引和唯一索引的區(qū)別在于：主鍵索引不能為空值，唯一索引允許空值；主鍵索引在一張表內(nèi)只能創(chuàng)建一個，唯一索引可以創(chuàng)建多個。主鍵索引肯定是唯一索引，但唯一索引不一定是主鍵索引。
3.索引原則
如果索引不遵循使用原則，則可能導致索引無效。
（1）列獨立
如果需要某個字段上使用索引，則需要在字段參與的表達中，保證字段獨立在一側(cè)。否則索引不會用到索引, 例如這條sql就不會用到索引:select * from A where id+1=10;
（2）左原則
Like：匹配模式必須要左邊確定不能以通配符開頭。例如:select * from A where name like '%小明%' ，不會用到索引，而select * from A where name like '小明%' 就可以用到索引（name字段有建立索引），如果業(yè)務上需要用到'%小明%'這種方式,有兩種方法: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 '%小明%' 這三種情況都會用到name的索引;
復合索引：一個索引關聯(lián)多個字段，僅僅針對左邊字段有效果，添加復合索引時，第一個字段很重要，只有包含第一個字段作為查詢條件的情況才會使用復合索引(必須用到建索引時選擇的第一個字段作為查詢條件,其他字段的順序無關),而且查詢條件只能出現(xiàn)and拼接,不能用or,否則則無法使用索引.
（3）OR的使用
必須要保證 OR 兩端的條件都存在可以用的索引，該查詢才可以使用索引。
（4）MySQL智能選擇
即使?jié)M足了上面說原則，MySQL也能棄用索引，例如：select * from A where id 1;這里棄用索引的主要原因：查詢即使使用索引，會導致出現(xiàn)大量的隨機IO，相對于從數(shù)據(jù)記錄的第一條遍歷到最后一條的順序IO開銷，還要大。
4.索引的使用場景
（1）索引檢索：檢索數(shù)據(jù)時使用索引。
（2）索引排序: 如果order by 排序需要的字段上存在索引，則可能使用到索引。
（3）索引覆蓋: 索引擁有的關鍵字內(nèi)容，覆蓋了查詢所需要的全部數(shù)據(jù)，此時，就不需要在數(shù)據(jù)區(qū)獲取數(shù)據(jù)，僅僅在索引區(qū)即可。覆蓋就是直接在索引區(qū)獲取內(nèi)容，而不需要在數(shù)據(jù)區(qū)獲取。例如: select name from A where name like '小明%';
建立索引索引時，不能僅僅考慮where檢索，同時考慮其他的使用場景。（在所有的where字段上增加索引，就是不合理的）
5.前綴索引
前綴索引是建立索引關鍵字一種方案。通常會使用字段的整體作為索引關鍵字。有時，即使使用字段前部分數(shù)據(jù)，也可以去識別某些記錄。就比如一個班級里，我要找王xx，假如姓王的只有1個人，那么就可以建一個關鍵字為'王'的前綴索引。語法：Index `index_name` (`index_field`(N))使用index_name前N個字符建立的索引。
6.索引失效
（1） 應盡量避免在 where 子句中使用 != 或 操作符，否則將引擎放棄使用索引而進行全表掃描；
（2） 應盡量避免在 where 子句中使用 or 來連接條件，如果一個字段有索引，一個字段沒有索引，將導致引擎放棄使用索引而進行全表掃描；
（3） 應盡量避免在 where 子句中對字段進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描；
（4）應盡量避免在 where 子句中對字段進行表達式操作，這將導致引擎放棄使用索引而進行全表掃描；如select id from t where num/2 = 100；
（5） 應盡量避免在where子句中對字段進行函數(shù)操作，這將導致引擎放棄使用索引而進行全表掃描；如：select id from t where substring(name,1,3) = ’abc’ ；
（6）應盡量避免在where子句中對字段進行類型轉(zhuǎn)換，這將導致引擎放棄使用索引而進行全表掃描； 如果列類型是字符串，那一定要在條件中將數(shù)據(jù)使用引號引用起來,如select id from t where id = 1；如果id字段在表設計中是varchar類型，那么即使id列上存的是數(shù)字，在查詢時也一定要用varchar去匹配，sql應改為select id from t where id = '1'；
（7）應盡量避免在where子句中單獨引用復合索引里非第一位置的索引；
join 的兩種算法：BNL 和 NLJ
NLJ（Nested Loop Join）嵌套循環(huán)算法；以如下 SQL 為例：
select * from t1 join t2 on t1.a=t2.a
SQL 執(zhí)行時內(nèi)部流程是這樣的：
1. 先從 t1（假設這里 t1 被選為驅(qū)動表）中取出一行數(shù)據(jù) X；
2. 從 X 中取出關聯(lián)字段 a 值，去 t2 中進行查找，滿足條件的行取出；
3. 重復1、2步驟，直到表 t1 最后一行循環(huán)結(jié)束。
這就是一個嵌套循環(huán)的過程，如果在被驅(qū)動表上查找數(shù)據(jù)時可以使用索引，總的對比計算次數(shù)等于驅(qū)動表滿足 where 條件的行數(shù)。假設這里 t1、t2都是1萬行，則只需要 1萬次計算，這里用到的是Index Nested-Loops Join（INLJ，基于索引的嵌套循環(huán)聯(lián)接）。
如果 t1、t2 的 a 字段都沒有索引，還按照上述的嵌套循環(huán)流程查找數(shù)據(jù)呢？每次在被驅(qū)動表上查找數(shù)據(jù)時都是一次全表掃描，要做1萬次全表掃描，掃描行數(shù)等于 1萬+1萬*1萬，這個效率很低，如果表行數(shù)更多，掃描行數(shù)動輒幾百億，所以優(yōu)化器肯定不會使用這樣的算法，而是選擇 BNL 算法；
BNLJ（Block Nested Loop Join）塊嵌套循環(huán)算法；
1. 把 t1 表（假設這里 t1 被選為驅(qū)動表）滿足條件的數(shù)據(jù)全部取出放到線程的 join buffer 中；
2. 每次取 t2 表一行數(shù)據(jù)，去 joinbuffer 中進行查找，滿足條件的行取出，直到表 t2 最后一行循環(huán)結(jié)束。
這個算法下，執(zhí)行計劃的 Extra 中會出現(xiàn) Using join buffer(Block Nested Loop)，t1、t2 都做了一次全表掃描，總的掃描行數(shù)等于 1萬+1萬。但是由于 joinbuffer 維護的是一個無序數(shù)組，每次在 joinbuffer 中查找都要遍歷所有行，總的內(nèi)存計算次數(shù)等于1萬*1萬。另外如果 joinbuffer 不夠大放不下驅(qū)動表的數(shù)據(jù)，則要分多次執(zhí)行上面的流程，會導致被驅(qū)動表也做多次全表掃描。
BNLJ相對于NLJ的優(yōu)點在于，驅(qū)動層可以先將部分數(shù)據(jù)加載進buffer，這種方法的直接影響就是將大大減少內(nèi)層循環(huán)的次數(shù)，提高join的效率。
例如：
如果內(nèi)層循環(huán)有100條記錄，外層循環(huán)也有100條記錄，這樣的話，每次外層循環(huán)先將10條記錄放到buffer中，內(nèi)層循環(huán)的100條記錄每條與這個buffer中的10條記錄進行匹配，只需要匹配內(nèi)層循環(huán)總記錄數(shù)次即可結(jié)束一次循環(huán)（在這里，即只需要匹配100次即可結(jié)束），然后將匹配成功的記錄連接后放入結(jié)果集中，接著，外層循環(huán)繼續(xù)向buffer中放入10條記錄，同理進行匹配，并將成功的記錄連接后放入結(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)連接,或等值連接）：取得兩個表中存在連接匹配關系的記錄。
LEFT JOIN（左連接）：取得左表（table1）完全記錄，即是右表（table2）并無對應匹配記錄。
RIGHT JOIN（右連接）：與 LEFT JOIN 相反，取得右表（table2）完全記錄，即是左表（table1）并無匹配對應記錄。
注意：mysql不支持Full join,不過可以通過UNION 關鍵字來合并 LEFT JOIN 與 RIGHT JOIN來模擬FULL join。
mysql 多表連接查詢方式，因為mysql只支持NLJ算法,所以如果是小表驅(qū)動大表則效率更高；反之則效率下降；因此mysql對內(nèi)連接或等值連接的方式做了一個優(yōu)化,會去判斷join表的數(shù)據(jù)行大小,然后取數(shù)據(jù)行小的表為驅(qū)動表。
INNER JOIN、JOIN、WHERE等值連接和STRAIGHT_JOIN都能表示內(nèi)連接，那平時如何選擇呢？一般情況下用INNER JOIN、JOIN或者WHERE等值連接，因為MySQL 會按照"小表驅(qū)動大表的策略"進行優(yōu)化。當出現(xiàn)需要排序時，才考慮用STRAIGHT_JOIN指定某張表為驅(qū)動表。
兩表JOIN優(yōu)化
a.當無order by條件時，根據(jù)實際情況，使用left/right/inner join即可，根據(jù)explain優(yōu)化 ；
b.當有order by條件時，如select * from a inner join b where 1=1 and other condition order by a.col；使用explain解釋語句；
1）如果第一行的驅(qū)動表為a，則效率會非常高，無需優(yōu)化；
2）否則，因為只能對驅(qū)動表字段直接排序的緣故，會出現(xiàn)using temporary，所以此時需要使用STRAIGHT_JOIN明確a為驅(qū)動表，來達到使用a.col上index的優(yōu)化目的；或者使用left join且Where條件中不含b的過濾條件，此時的結(jié)果集為a的全集，而STRAIGHT_JOIN為inner join且使用a作為驅(qū)動表。注：使用STRAIGHT_JOIN雖然不會using temporary，但也不是一定就能提高效率，如果a表數(shù)據(jù)遠遠超過b表，那么有可能使用STRAIGHT_JOIN時比原來的sql效率更低，所以怎么使用STRAIGHT_JOIN，還是要視情況而定。
在使用left join（或right join）時，應該清楚的知道以下幾點：
(1). on與 where的執(zhí)行順序
ON 條件（“A LEFT JOIN B ON 條件表達式”中的ON）用來決定如何從 B 表中檢索數(shù)據(jù)行。如果 B 表中沒有任何一行數(shù)據(jù)匹配 ON 的條件,將會額外生成一行所有列為 NULL 的數(shù)據(jù),在匹配階段 WHERE 子句的條件都不會被使用。僅在匹配階段完成以后，WHERE 子句條件才會被使用。它將從匹配階段產(chǎn)生的數(shù)據(jù)中檢索過濾。
所以我們要注意：在使用Left (right) join的時候，一定要在先給出盡可能多的匹配滿足條件，減少Where的執(zhí)行。
(2).注意ON 子句和 WHERE 子句的不同
即使右表的數(shù)據(jù)不滿足ON后面的條件,也會在結(jié)果集拼接一條為NULL的數(shù)據(jù)行,但WHERE后面的條件不一樣,右表不滿足WHERE的條件,左表關聯(lián)的數(shù)據(jù)也會被過濾掉。
(3).盡量避免子查詢，而用join
往往性能這玩意兒，更多時候體現(xiàn)在數(shù)據(jù)量比較大的時候，此時，我們應該避免復雜的子查詢。
（1）in 和 not in 要慎用，如：select id from t where num in(1,2,3)對于連續(xù)的數(shù)值，能用 between 就不要用 in：select id from t where num between 1 and 3很多時候用 exists 代替 in 是一個好的選擇：select num from a where num in(select num from b)用下面的語句替換：select num from a where exists(select 1 from b where num=a.num)
（2）Update 語句，如果只更改1、2個字段，不要Update全部字段，否則頻繁調(diào)用會引起明顯的性能消耗，同時帶來大量日志。
（3）join語句,MySQL里面的join是用小表去驅(qū)動大表,而由于MySQL join實現(xiàn)的原理就是做循環(huán)，比如left join就是對左邊的數(shù)據(jù)進行循環(huán)去驅(qū)動右邊的表,左邊有m條記錄匹配,右邊有n條記錄那么就是做m次循環(huán),每次掃描n行數(shù)據(jù),總掃面行數(shù)是m*n行數(shù)據(jù)。左邊返回的結(jié)果集的大小就決定了循環(huán)的次數(shù),故單純的用小表去驅(qū)動大表不一定的正確的,小表的結(jié)果集可能也大于大表的結(jié)果集,所以寫join的時候盡可能的先估計兩張表的可能結(jié)果集,用小結(jié)果集去驅(qū)動大結(jié)果集.值得注意的是在使用left/right join的時候,從表的條件應寫在on之后,主表應寫在where之后.否則MySQL會當作普通的連表查詢；
（4）select count(*) from table；這樣不帶任何條件的count會引起全表掃描，并且沒有任何業(yè)務意義，是一定要杜絕的；
（5）select * from t 這種語句要盡量避免，使用具體的字段代替*，更有實際意義，需要什么字段就返回什么字段；
（6）數(shù)據(jù)量大的情況下，limit要慎用，因為使用limit m，n方式分頁時，mysql每次都是查詢前m+n條，然后舍棄前m條，所以m越大，偏移量越大，性能就越差。比如：select * from A limit 1000000,20這鐘，查詢效率就會非常低，當分頁的頁數(shù)大于一定的數(shù)量之后，就可以換種方式來分頁：select * from A a join （select id from A limit 1000000，20） b on a.id=b.id;
InnoDB的全文索引使用反向索引的設計。反向索引存儲了一個單詞（word）列表，對于每個單詞，都有一個文檔的列表，來標識這個單詞出現(xiàn)的地方。為了支持臨近搜索（proximity search），每個單詞的位置信息也以字節(jié)偏移的方式存儲。
當創(chuàng)建了InnoDB全文索引，一系列的索引表會一同被創(chuàng)建，見下面的例子：
最前面的六個表包含了反向索引，它們被稱作附屬索引表（auxiliary index table）。當輸入的表被索引（tokenized）后，每個獨立的單詞（亦稱作“tokens”）會被攜帶其DOC_ID和位置信息插入到索引表中。根據(jù)單詞第一個字符的字符集排序權重，在六個索引表中對單詞進行完全排序和分區(qū)。
反向索引分區(qū)到六個附屬索引表以支持并行的索引創(chuàng)建。默認有2個線程復制索引（Tokenize）、排序、插入單詞和關聯(lián)數(shù)據(jù)到索引表中。工作的線程的數(shù)量由 innodb_ft_sort_pll_degree 配置項控制的。對于大表的全文索引，可以考慮增加線程數(shù)量。
如果主表創(chuàng)建在 xx表空間，索引表存儲在它們自己的表空間中。反之，索引表存儲于其索引的表空間中。
前面例子展示的另外一種索引表被稱作通用索引表，它們被用于全文索引的“刪除處理（deletion handing）”和存儲內(nèi)部狀態(tài)。不同于為每個全文索引都各自創(chuàng)建的反向索引表，這組表對特定表的所有全文索引都是共用的。
即使全文索引刪掉了，通用索引（Common Index）也會被保留，當全文索引刪除后，為這個索引而創(chuàng)建的FTS_DOC_ID列依然保留，因為移除FTS_DOC_ID列會導致重構之前被索引的表。管理FTS_DOC_ID列需要用到通用索引表。
為了防止大量并發(fā)讀寫附屬表，InnoDB使用全文索引緩存去臨時緩存最近的插入行。在存滿并刷入磁盤之前，緩存的內(nèi)容一直存儲在內(nèi)存之中，可以通過查詢 INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE 表去查看最近緩存的插入行。
緩存和批處理刷新行為避免了對輔助索引表的頻繁更新，頻繁更新可能會在繁忙的插入和更新期間導致并發(fā)訪問問題。批處理還避免了對同一個word的多次插入，最大化的減少了重復的條目。相同的word會先merge再刷入到磁盤中，而不是為每個word單獨插入，這樣提高了插入效率并且使得索引附屬表盡可能的小。
全文索引緩存只緩存最近插入的行，查詢時，已經(jīng)刷入磁盤（附屬索引表）的數(shù)據(jù)不會再回到索引緩存中。附屬索引表中的內(nèi)容是直接查詢的，最終返回的結(jié)果返回前需要將附屬索引表的結(jié)果和索引緩存中的結(jié)果合并。
InnoDB使用被稱作DOC_ID的唯一文件描述符，將全文索引中的單詞與該單詞在文檔中的記錄映射起來。映射關系需要索引表中的 FTS_DOC_ID 列。在創(chuàng)建全文索引時，如果沒有定義 FTS_DOC_ID 列，InnoDB會自動的加入一個隱藏的 FTS_DOC_ID 列。下面是一個例子，
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
重點看一下這一行： [HY000][124] InnoDB rebuilding table to add column FTS_DOC_ID ，InnoDB重新構建了這個表，并且添加了一個列 FTS_DOC_ID 。
在CREATE TABLE的過程中添加 FTS_DOC_ID 的時間成本要低于在已經(jīng)有數(shù)據(jù)的表上建立全文索引。如果在表加載數(shù)據(jù)之前定義 FTS_DOC_ID 列，這個表和它的索引都不需要為了新增列而重新構建。如果你不需要考慮 CREATE FULLTEXT INDEX 的性能，可以讓InnoDB為你創(chuàng)建 FTS_DOC_ID 列。InnoDB會新增一個隱藏的 FTS_DOC_ID 列，并且在 FTS_DOC_ID 上建立唯一索引（FTS_DOC_ID_INDEX）。如果你想自行創(chuàng)建 FTS_DOC_ID 列，這個列必須定義為 BIGINT UNSIGNED NOT NULL 且命名為FTS_DOC_ID（全大寫），如下例子：
如果你自行定義 FTS_DOC_ID 列的話，你需要負責管理這個列，避免空值（empty）或者重復值。 FTS_DOC_ID 的值是不能被重復利用的，所以也就是說 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會自動創(chuàng)建。
在MySQL 5.7.13前，允許最大FTS_DOC_ID與最新的FTS_DOC_ID之間的間隔為10000，在MySQL 5.7.13及之后的版本中，這個允許的間隔為65535。
為了避免重新構建表，F(xiàn)TS_DOC_ID列在刪除了全文索引之后依然被保留。
刪除被索引文件的一個記錄，可能會在附屬索引表中產(chǎn)生非常多的小的刪除項，在并發(fā)訪問時，會產(chǎn)生熱點問題。為了避免這個問題，每當被索引表中的記錄被刪除時，會將被刪文檔的DOC_ID記錄在一個特別的 FTS_*_DELETED 表中，同時全文索引中已經(jīng)索引了的記錄依然被保存。在返回查詢結(jié)果前，使用 FTS_*_DELETED 中的信息去過濾掉已經(jīng)刪除掉了的DOC_ID。這種設計的優(yōu)勢在于刪除速度快且消耗低。不好的地方在于索引的大小不能隨著記錄的刪除而立即減少。為了刪除已刪除記錄在全文索引中的項，需要對被索引的表執(zhí)行OPTIMIZE TABLE，配合[ innodb_optimize_fulltext_only=ON ]，去重構全文索引。
細節(jié)略，有例子：
全文搜索只能看到已經(jīng)提交了的數(shù)據(jù)。
你可以通過查詢下面的INFORMATION_SCHEMA表，來監(jiān)控或測試InnoDB的一些特殊文本處理。
默認的分詞器不支持中文，不能檢索到中文中的英文單詞。
InnoDB默認的Stopwords：
select * from information_schema.INNODB_FT_DEFAULT_STOPWORD;
SQL中的關鍵詞（保留關鍵字）：
Shell中的關鍵詞：for，while，echo
其他：###, ***, --,
被索引表數(shù)據(jù)量、索引表數(shù)據(jù)量
模糊匹配與嚴格匹配的性能差距
需要將 innodb_optimize_fulltext_only 配置為ON，這里是否需要DBA在MySQL鏡像中修改？
innodb_optimize_fulltext_only 設置為ON后，對系統(tǒng)有何影響需要評估。
innodb_optimize_fulltext_only
執(zhí)行的時間、頻率。
MySQL內(nèi)建的全文檢索解析器使用單詞之間的空白作為分隔符以標識單詞的頭尾，但是這里有個限制，對于表意文字，它是沒有單詞分隔符的。為了解決這個限制，MySQL提供了支持中文、日語、韓語的 ngram 解析器。ngram解析器支持InnoDB和MyISAM。
Ngram是內(nèi)建在服務中的插件，像其他自建在服務中的插件一樣，服務啟動時會自動加載它。全文檢索的語法參考上面（ Section 12.10, “Full-Text Search Functions” ），這里只討論一些不同的地方。除了單詞的最小、最大長度配置項（[ innodb_ft_min_token_size ]innodb_ft_max_token_size,ft_min_word_len,ft_max_word_len，全文檢索依賴一些配置項都是可以使用的。
Ngram默認索引的單詞（token）的大小為2（ 2bigram ）。例如，索引的大小為2，Ngram解析器解析字符串“abc def”為四個單詞元素(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 只能在啟動配置或者配置文件中指定
與默認的解析器相差不大，多了一句： xxx WITH PARSER ngram
Ngram在解析時去除空格，如
MySQL內(nèi)建的默認全文檢索解析器將單詞與Stopword列表中的做對比，如果單詞與Stopword列表中的元素相同的話，這個單詞則不會被索引。對于Ngram解析器，Stopword的處理方式不同。Ngram解析器不排除與stopword列表中的條目相等的token，而是排除包含stopwords的token。例如，假設 ngram_token_size=2 ，包含“a,b”的文檔將被解析為 “a,” h和“,b”。如果將逗號（“,”）定義為停止字，則 “a,”和“,b”都將不會加入索引中，因為它們包含逗號。
例子：
默認Ngram解析器使用默認的Stopword列表，這里面含有英文的Stopword。如果需要中文的Stopword，需要你自己創(chuàng)建。
Stopword的長度超過 ngram_token_size則會被忽略。
有兩個文檔，一個包含“ab”，另一個包含“abc”。對于搜索文本“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解析器好處是支持了中文的檢索
重慶分公司
重慶分公司
