各位老鐵們，大家好，今天由我來為大家分享mysql索引詳解，以及mysql四種索引類型的相關問題知識，希望對大家有所幫助。如果可以幫助到大家，還望關注收藏下本站，您的支持是我們最大的動力，謝謝大家了哈，下面我們開始吧！
MySQL全文索引應用簡明教程
全文檢索在MySQL里面很早就支持了，只不過一直以來只支持英文。緣由是他從來都使用空格來作為分詞的分隔符，而對于中文來講，顯然用空格就不合適，需要針對中文語義進行分詞。
這不，從MySQL5.7開始，MySQL內置了ngram全文檢索插件，用來支持中文分詞，并且對MyISAM和InnoDB引擎有效。
在使用中文檢索分詞插件ngram之前，先得在MySQL配置文件里面設置他的分詞大小，比如，[mysqld]ngram_token_size=2這里把分詞大小設置為2。要記住，分詞的SIZE越大，索引的體積就越大，所以要根據自身情況來設置合適的大小。
示例表結構：
CREATETABLEarticles(idINTUNSIGNEDAUTO_INCREMENTNOTNULLPRIMARYKEY,titleVARCHAR(200),bodyTEXT,FULLTEXT(title,body)WITHPARSERngram)ENGINE=InnoDBCHARACTERSETutf8mb4;示例數據，有6行記錄。mysql>select*fromarticles\G**************************
*1.row***************************id:1title:數據庫管理body:在本教程中我將向你展示如何管理數據庫***************************2.row***************************id:2title:數據庫應用開發body:學習開發數據庫應用程序***************************3.row***************************id:3title:MySQL完全手冊body:學習MySQL的一切***************************4.row***************************id:4title:數據庫與事務處理body:系統的學習數據庫的事務概論***************************5.row***************************id:5title:NoSQL精髓body:學習了解各種非結構化數據庫***************************6.row***************************id:6title:SQL語言詳解body:詳細了解如果使用各種SQL6rowsinset(0.00sec)顯式指定全文檢索表源mysql>SETGLOBALinnodb_ft_aux_table="new_feature/articles";QueryOK,0rowsaffected(0.00sec)通過系統表，就可以查看到底是怎么劃分articles里的數據。
mysql>SELECT*FROMinformation_schema.INNODB_FT_INDEX_CACHELIMIT20,10;+------+--------------+-------------+-----------+--------+----------+|WORD|FIRST_DOC_ID|LAST_DOC_ID|DOC_COUNT|DOC_ID|POSITION|+------+--------------+-------------+-----------+--------+----------+|中我|2|2|1|2|28||習m|4|4|1|4|21||習了|6|6|1|6|16||習開|3|3|1|3|25||習數|5|5|1|5|37||了解|6|7|2|6|19||了解|6|7|2|7|23||事務|5|5|1|5|12||事務|5|5|1|5|40||何管|2|2|1|2|52|+------+--------------+-------------+-----------+--------+----------+10rowsinset(0.00sec)這里可以看到，把分詞長度設置為2，所有的數據都只有兩個一組。上面數據還包含了行的位置，ID等等信息。
接下來，我來進行一系列檢索示范，使用方法和原來英文檢索一致。1.自然語言模式下檢索：
A，得到符合條件的個數，mysql>SELECTCOUNT(*)FROMarticles->WHEREMATCH(title,body)AGAINST('數據庫'INNATURALLANGUAGEMODE);+----------+|COUNT(*)|+----------+|4|+----------+1rowinset(0.05sec)B，得到匹配的比率，mysql>SELECTid,MATCH(title,body)AGAINST('數據庫'INNATURALLANGUAGEMODE)ASscoreFROMarticles;+----+----------------------+|id|score|+----+----------------------+|1|0.12403252720832825||2|0.12403252720832825||3|0||4|0.12403252720832825||5|0.062016263604164124||6|0|+----+----------------------+6rowsinset(0.00sec)2.布爾模式下搜索，這個就相對于自然模式搜索來的復雜些：A，匹配既有管理又有數據庫的記錄，mysql>SELECT*FROMarticlesWHEREMATCH(title,body)->AGAINST('+數據庫+管理'INBOOLEANMODE);+----+------------+--------------------------------------+|id|title|body|+----+------------+--------------------------------------+|1|數據庫管理|在本教程中我將向你展示如何管理數據庫|+----+------------+--------------------------------------+1rowinset(0.00sec)B，匹配有數據庫，但是沒有管理的記錄，mysql>SELECT*FROMarticlesWHEREMATCH(title,body)->AGAINST('+數據庫-管理'INBOOLEANMODE);+----+------------------+----------------------------+|id|title|body|+----+------------------+----------------------------+|2|數據庫應用開發|學習開發數據庫應用程序||4|數據庫與事務處理|系統的學習數據庫的事務概論||5|NoSQL精髓|學習了解各種非結構化數據庫|+----+------------------+----------------------------+3rowsinset(0.00sec)C，匹配MySQL，但是把數據庫的相關性降低，mysql>SELECT*FROMarticlesWHEREMATCH(title,body)->AGAINST('>數據庫+MySQL'INBOOLEANMODE);+----+---------------+-----------------+|id|title|body|+----+---------------+-----------------+|3|MySQL完全手冊|學習MySQL的一切|+----+---------------+-----------------+1rowinset(0.00sec)3，查詢擴展模式，比如要搜索數據庫，那么MySQL，oracle，DB2也都將會被搜索到，mysql>SELECT*FROMarticles->WHEREMATCH(title,body)->AGAINST('數據庫'WITHQUERYEXPANSION);+----+------------------+--------------------------------------+|id|title|body|+----+------------------+--------------------------------------+|1|數據庫管理|在本教程中我將向你展示如何管理數據庫|4|數據庫與事務處理|系統的學習數據庫的事務概論|2|數據庫應用開發|學習開發數據庫應用程序||5|NoSQL精髓|學習了解各種非結構化數據庫||6|SQL語言詳解|詳細了解如果使用各種SQL||3|MySQL完全手冊|學習MySQL的一切|+----+------------------+--------------------------------------+6rowsinset(0.01sec)當然，我這里只是功能演示，更多的性能測試，大家有興趣可以進行詳細測試。由于N-grm是中文檢索常用的分詞算法，已經在互聯網大量使用，這次集成到mysql中，想必效果上不會有太大的問題。
mysql索引生效時間
MySQL索引生效的時間是在執行查詢時。當查詢語句中包含索引列，并且索引列與查詢條件相匹配時，MySQL會使用索引來加速查詢操作。索引可以幫助MySQL快速定位符合查詢條件的數據，提高查詢性能。但是，如果查詢語句中沒有包含索引列或者索引列與查詢條件不匹配，那么索引將不會起作用，MySQL將會進行全表掃描，性能會受到影響。
所以，編寫合適的查詢語句，包含適當的索引列，可以提高查詢性能。
mysql索引底層原理
一、定義
索引定義：索引（Index）是幫助MySQL高效獲取數據的數據結構。本質：索引是數據結構。
二、B-Tree
m階B-Tree滿足以下條件：1、每個節點至多可以擁有m棵子樹。2、根節點，只有至少有2個節點（要么極端情況，就是一棵樹就一個根節點，單細胞生物，即是根，也是葉，也是樹)。3、非根非葉的節點至少有的Ceil(m/2)個子樹(Ceil表示向上取整，如5階B樹，每個節點至少有3個子樹，也就是至少有3個叉)。4、非葉節點中的信息包括[n,A0,K1,A1,K2,A2,…,Kn,An]，，其中n表示該節點中保存的關鍵字個數，K為關鍵字且Ki<Ki+1，A為指向子樹根節點的指針。5、從根到葉子的每一條路徑都有相同的長度（葉子節點在相同的層）
B-Tree特性：
1、關鍵字集合分布在整顆樹中；2、任何一個關鍵字出現且只出現在一個節點中；3、每個節點存儲date和key；4、搜索有可能在非葉子節點結束；5、一個節點中的key從左到右非遞減排列；6、所有葉節點具有相同的深度，等于樹高h。
B-Tree上查找算法的偽代碼如下：
三、B+Tree
B+Tree與B-Tree的差異在于：1、B+Tree非葉子節點不存儲data，只存儲key；2、所有的關鍵字全部存儲在葉子節點上；3、每個葉子節點含有一個指向相鄰葉子節點的指針，帶順序訪問指針的B+樹提高了區間查找能力；4、非葉子節點可以看成索引部分，節點中僅含有其子樹（根節點）中的最大（或最小）關鍵字；
四、B/B+樹索引的性能分析
依據：使用磁盤I/O次數評價索引結構的優劣主存和磁盤以頁為單位交換數據，將一個節點的大小設為等于一個頁，因此每個節點只需一次I/O就可以完全載入。根據B樹的定義，可知檢索一次最多需要訪問h個節點漸進復雜度:O(h)=O(logdN)dmax=floor(pagesize/(keysize+datasize+pointsize))一般實際應用中，出度d是非常大的數字，通常超過100，因此h非常?。ㄍǔ２怀^3，3層可存大約一百萬數據）B-Tree中一次檢索最多需要h-1次I/O（根節點常駐內存）B+Tree內節點不含data域，因此出度d更大，則h更小，I/O次數少，效率更高，故B+Tree更適合外存索引。
五、MySQL索引實現1、MyISAM引擎使用B+Tree作為索引結構，葉節點的data域存放的是數據記錄的地址；MyISAM主索引和輔助索引在結構上沒有任何區別，只是主索引要求key是唯一的，而輔助索引的key可以重復；
2、InnoDB的數據文件本身就是索引文件，葉節點包含了完整的數據記錄，這種索引叫做聚集索引。因為InnoDB的數據文件本身要按主鍵聚集，所以InnoDB要求表必須有主鍵（MyISAM可以沒有），如果沒有顯式指定，則MySQL系統會自動選擇一個可以唯一標識數據記錄的列作為主鍵，如果不存在這種列，則MySQL自動為InnoDB表生成一個隱含字段作為主鍵。InnoDB的輔助索引data域存儲相應記錄主鍵的值而不是地址；輔助索引搜索需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然后用主鍵到主索引中檢索獲得記錄；
3、頁分裂問題
如果主鍵是單調遞增的，每條新記錄會順序插入到頁，當頁被插滿后，繼續插入到新的頁；
如果寫入是亂序的，InnoDB不得不頻繁地做頁分裂操作，以便為新的行分配空間。頁分裂會導致移動大量數據，一次插入最少需要修改三個頁而不是一個頁。
如果頻繁的頁分裂，頁會變得稀疏并被不規則地填充，所以最終數據會有碎片。
六、總結
了解不同存儲引擎的索引實現方式對于正確使用和優化索引都非常有幫助
1、為什么不建議使用過長的字段作為主鍵？
2、為什么選擇自增字段作為主鍵？
3、為什么常更新是字段不建議建立索引？
4、為什么選擇區分度高的列作為索引？區分度的公式是count(distinctcol)/count(*)
5、盡可能的使用覆蓋索引
七、優化LIMIT分頁查詢
SELECT*FROMtablewhereconditionLIMIToffset,rows;上述SQL語句的實現機制是：1、從“table”表中讀取offset+rows行記錄。2、拋棄前面的offset行記錄，返回后面的rows行記錄作為最終的結果。覆蓋索引：selecta.id,sid,parent_s_idfromcashpool_account_relationshipajoin(selectidfromcashpool_account_relationshipLIMIT1000000,10)bona.id=b.id;selectid,sid,parent_s_idfromcashpool_account_relationshipwhereid>=(selectidfromcashpool_account_relationshipLIMIT1000000,1)LIMIT10;
八、Q&A
1、InnoDB支持hash索引嗎？--馬欣InnoDB是支持hash索引的，不過其支持的hash索引是自適應的，InnoDB存儲引擎會根據表的使用情況自動為表生成hash索引，不能人為干預是否在一張表中生成hash索引。2、InnoDB主鍵索引的葉節點含完整的數據記錄，那主鍵索引文件要比數據文件大嗎？--徐財厚1）.在Innodb引擎中，主鍵索引中的葉子結點包含記錄數據，主鍵索引文件即為數據文件。2）.在tables表中統計的data_length數據為主鍵索引大小，index_length為統計的這個表中所有輔助索引（二級索引）索引的大小。
mysql創建索引的sql語句
MySQL創建索引的SQL語句如下：
創建單列索引：
scssCopycode
CREATEINDEXindex_nameONtable_name(column_name);
例如，為employees表的salary列創建索引，語句如下：
scssCopycode
CREATEINDEXsalary_indexONemployees(salary);
創建多列索引：
scssCopycode
CREATEINDEXindex_nameONtable_name(column1_name,column2_name,...);
例如，為employees表的department和salary列創建索引，語句如下：
scssCopycode
CREATEINDEXdepartment_salary_indexONemployees(department,salary);
需要注意的是，索引可以提高查詢的速度，但也會增加數據的存儲和修改的成本，因此需要根據實際情況進行權衡。同時，在對表進行修改和更新時，也需要考慮索引的影響。
mysql全局索引的作用
索引可以加快數據檢索操作，但會使數據修改操作變慢。每修改數據記錄，索引就必須刷新一次。為了在某種程度上彌補這一缺陷，許多SQL命令都有一個DELAY_KEY_WRITE項。
這個選項的作用是暫時制止MySQL在該命令每插入一條新記錄和每修改一條現有之后立刻對索引進行刷新，對索引的刷新將等到全部記錄插入/修改完畢之后再進行。在需要把許多新記錄插入某個數據表的場合，DELAY_KEY_WRITE選項的作用將非常明顯。
關于mysql索引詳解的內容到此結束，希望對大家有所幫助。
