導(dǎo)讀推薦學(xué)習(xí)開心到飛起！Alibaba百萬年薪必備—高性能架構(gòu)路線已到手全網(wǎng)獨家的“MySQL高級知識”集合，骨灰級收藏，手慢則無存儲引擎介紹MySQL的存儲引....推薦學(xué)習(xí)

• 開心到飛起！Alibaba百萬年薪必備—高性能架構(gòu)路線已到手
• 全網(wǎng)獨家的“MySQL高級知識”集合，骨灰級收藏，手慢則無

存儲引擎介紹

MySQL的存儲引擎是插件式管理的，我們可以自由選擇，MySQL中常用的存儲引擎有很多種，但是最常用的就是InnoDB和MyISAM，其他存儲引擎不在本文內(nèi)容之列，就不做過多介紹，主要簡單介紹一下InnoDB和MyISAM存儲引擎。

MyISAM引擎

MyISAM存儲引擎不支持行級鎖，只有表級鎖；不支持事務(wù)，也不支持外鍵，主要面向OLAP應(yīng)用，是MySQL數(shù)據(jù)庫5.5.8之前版本默認(rèn)的存儲引擎，MyISAM適用于不需要關(guān)心事務(wù)，讀多寫少的場景。每張MyISAM表在磁盤上會創(chuàng)建三個文件：.frm，.MYD和.MYI，其中.frm文件為表結(jié)構(gòu)，每個存儲引擎都會有這個文件，是用來存儲表結(jié)構(gòu)的，.MYD文件用來存儲數(shù)據(jù)，.MYI用來存儲索引，也就是說MyISAM的數(shù)據(jù)和索引是分開存儲的，這一點和InnoDB不一樣。在MySQL5.0之前，MyISAM默認(rèn)支持的表只有4GB，如果要修改默認(rèn)表大小的話，需要修改參數(shù)MAX_ROWS和AVG_ROW_LENGTH的大小，不過這一點在MySQL5.0之后得到了改善，默認(rèn)大小為256TB，這個大小在絕大部分應(yīng)用應(yīng)該都是可以滿足要求的。

InnoDB引擎

InnoDB存儲引擎支持事務(wù)，主要是為了面向在線事務(wù)處理(OLTP)的應(yīng)用而生，支持行鎖和外鍵，其通過使用多版本并發(fā)控制(MVCC)來提升高并發(fā)性能，實現(xiàn)了SQL標(biāo)準(zhǔn)的4種隔離級別。從MySQL數(shù)據(jù)庫5.5.8版本開始，為MySQL默認(rèn)存儲引擎。每張 InnoDB表在磁盤上會創(chuàng)建兩個文件：.frm 和.ibd，其中.frm文件和MyISAM引擎一樣，用來存儲表結(jié)構(gòu)的，.ibd文件存儲的是索引和數(shù)據(jù)，InnoDB中索引和數(shù)據(jù)放在同一個文件中。

MyISAM索引結(jié)構(gòu)

MyISAM的B 樹里面，葉子節(jié)點存儲的是當(dāng)前索引的值以及當(dāng)前數(shù)據(jù)文件對應(yīng)的磁盤地址。所以如果從索引文件.MYI中找到鍵值后，會根據(jù)其存儲的磁盤地址到數(shù)據(jù)文件.MYD 中獲取相應(yīng)的數(shù)據(jù)記錄，在MyISAM引擎中，主鍵索引和非主鍵索引沒有差別，都是一樣存儲，MyISAM索引大致結(jié)構(gòu)如下圖所示(本人從小就及其不喜歡畫畫，所以這個圖形實在有點丑，好在能表達出大致意思了)：

InnoDB索引結(jié)構(gòu)

InnoDB除了表結(jié)構(gòu).frm文件外，就只有一個.ibd 文件，索引和數(shù)據(jù)存儲在一起，所以在InnoDB的B 樹中葉子節(jié)點直接存儲的是整條數(shù)據(jù)記錄，而不是記錄磁盤地址。InnoDB引擎和MyISAM引擎還有一個最大的不同就是InnoDB引擎是以主鍵索引來組織數(shù)據(jù)的（主鍵索引和非主鍵索引的存儲結(jié)構(gòu)是不同的），InnoDB存儲引擎中這種組織數(shù)據(jù)的方式被稱之為聚集索引組織表（clustered index organize table），主鍵索引也被稱之為聚集索引。

聚集索引

聚集索引(又稱之為聚簇索引),聚集的術(shù)語表示的是索引鍵值和數(shù)據(jù)緊湊的存儲在一起。而數(shù)據(jù)又不會同時存在兩個地方，所以InnoDB每張表都有且只有一個聚集索引，換言之，也就是說每張表都必須有且只有一個主鍵。說到這里可能很多人就要反問了，我建表的時候沒有主鍵索引也可以建表成功，那么這又是為什么呢？

其實如果我們沒有顯示的指定主鍵，InnoDB會選擇一個非空的唯一索引列作為主鍵，如果這個也沒有，那么InnoDB就會選擇一個選擇其自己內(nèi)置 的6字節(jié)長的ROWID自增列作為主鍵。InnoDB中聚集索引葉子節(jié)點直接存儲的是整條數(shù)據(jù)，也就是說索引搜索到葉子節(jié)點之后就可以直接返回數(shù)據(jù)了，無需再去磁盤獲取數(shù)據(jù)。

InnoDB中聚集索引大致結(jié)構(gòu)如下圖所示：

非聚集索引

除了主鍵索引之外的其他索引都是非聚集索引，既然聚集索引的索引鍵值和數(shù)據(jù)行存放在一起，而聚集索引又只有一個，那么非聚集索引又是怎么存儲數(shù)據(jù)的呢？接下來要畫重點了哈：非聚集索引的葉子節(jié)點存儲的是當(dāng)前索引的鍵值和主鍵索引的鍵值。大致結(jié)構(gòu)如下圖所示：

所以非聚集索引查詢數(shù)據(jù)和聚集索引查詢數(shù)據(jù)是不同的，因為非聚集索引的葉子節(jié)點只有當(dāng)前索引的鍵值和主鍵的鍵值，也就是說查詢數(shù)據(jù)的時候獲取到非聚集索引的葉子節(jié)點只能拿到當(dāng)前索引值和主鍵索引值。

回表

什么是回表？回表指的就是非聚集索引從葉子節(jié)點拿到數(shù)據(jù)(主鍵的鍵值)之后，還需要再根據(jù)主鍵鍵值去掃描主鍵索引的B 樹，這種操作就叫做回表，也就是說他需要掃描兩顆B 樹，這也就是為什么在InnoDB中主鍵索引的效率相比較其他索引是最高的。

覆蓋索引

前面我們說到了回表操作，那么就還有有這么一種場景是不需要回表的：比如說我們一個查詢只需要查詢當(dāng)前索引的值和主鍵的值，而不需要查其他數(shù)據(jù)，這時候就不需要回表了，直接就可以返回，這種也稱之為覆蓋索引，所以這也是為什么不要寫select * 的原因，因為select * 肯定無法用到覆蓋索引(除非整張表都是索引)，而覆蓋索引可以少掃描一顆聚集索引的B 樹，而且因為輔助索引不會存儲整條數(shù)據(jù)，所以大小也要遠小于聚集索引，故而對性能有較大的提升。需要注意的是，MyISAM引擎中如果查找的數(shù)據(jù)也包含在索引內(nèi)，不需要去磁盤找數(shù)據(jù)，也認(rèn)為是覆蓋索引。

MySQL對索引的優(yōu)化Index Condition Pushdown(ICP)

Index Condition Pushdown中文含義為：索引條件下推。是在MySQL5.6版本之后引進的優(yōu)化措施。如果沒有ICP，存儲引擎將遍歷索引以定位基表中的行，并將它們返回給MySQLServer層，由Server層計算行的where條件。在啟用ICP的情況下，如果WHERE條件的一部分可以通過只使用索引中的列來計算，那么MySQL服務(wù)器將這一部分WHERE條件下推到存儲引擎。然后，存儲引擎使用索引條目來計算已推入的索引條件，只有滿足這個條件，才從表中讀取行。ICP可以減少存儲引擎必須訪問基表的次數(shù)和MySQL服務(wù)器必須訪問存儲引擎的次數(shù)。

執(zhí)行如下語句：

show variables like optimizer_switch;

會返回如下結(jié)果：

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on

其中:index_condition_pushdown這個參數(shù)就是是否開啟索引下推優(yōu)化的，on表示開啟，off表示關(guān)閉。可以通過如下語句設(shè)置：

SET optimizer_switch=index_condition_pushdown=off;

Multi-Range Read(MRR)

Multi-Range Read和Index Condition Pushdown一樣，也是在MySQL5.6版本之后引進的優(yōu)化措施。MRR優(yōu)化的目的是為了減少磁盤的隨機IO訪問，并且將隨機訪問轉(zhuǎn)化為順序的數(shù)據(jù)訪問，所以MRR優(yōu)化措施對IO-bound型的SQL查詢語句可能帶來極大的性能提升。

和ICP一樣，也是通過【optimizer_switch】變量查詢，找到返回結(jié)果中的下面兩個參數(shù)：

mrr=onmrr_cost_based=on

mrr=on表示啟用，mrr_cost_based 表示是否通過基于開銷的方式來啟用MRR，如果mrr_cost_based=on，則即使?jié)M足了使用MRR的條件，優(yōu)化器也會視當(dāng)前查詢的開銷來決定是否使用MRR，如果我們想總是開啟MRR，則可以將mrr設(shè)置為on,mrr_cost_based設(shè)置為off，如下：

SET optimizer_switch=mrr=off,mrr_cost_based=off;

MRR的工作方式

1、將查詢得到的輔助索引鍵值存放于緩存之中，注意，這時候緩存中的數(shù)據(jù)是根據(jù)輔助索引的鍵值排序的。2、將緩存中的數(shù)據(jù)根據(jù)row ID(主鍵)進行重排序。3、然后再根據(jù)row ID(主鍵)的順序去訪問。

注意2,3中的row ID，《MySQL技術(shù)內(nèi)幕 InnoDB存儲引擎》一書中寫的是RowID，我不太清楚作者當(dāng)時想表達的是按照主鍵，還是MySQL隱藏列ROWID進行排序，但我個人認(rèn)為如果寫成主鍵會更容易理解，因為如果我們自己創(chuàng)表的時候顯示的指定了主鍵，而且排序和ROWID不一致，那么就應(yīng)該是按照我們的主鍵進行排序，否則就達不到實現(xiàn)順序IO訪問的結(jié)果，下面附上MySQL官網(wǎng)原文：

可以看到，官網(wǎng)用的是兩個單詞：row ID，也就是行id，個人認(rèn)為是可以直接理解成主鍵的意思，而并不單單指的是MySQL隱藏列ROWID。這里如果我理解錯了，歡迎給我留言或者私信。

我們想一想，如果我們通過輔助索引查找到了輔助索引的鍵值和主鍵的鍵值，這時候我們需要回表，假如輔助索引和主鍵索引順序相差很大，那么回表查主鍵B 樹的時候，就是隨機訪問磁盤，也就是隨機IO操作，而如果使用了MRR，就會按照主鍵進行重排序，這時候再回表就是順序IO，所以說MRR之所以能優(yōu)化是因為順序IO訪問的效率是遠遠大于隨機IO的。

INDEX MERGE

索引合并優(yōu)化，MySQL在5.0及之后的版本引入了這種優(yōu)化方案。這個意思就是我們在一個表中建立了很多單列索引，然后查詢的時候同時用到了多列作為條件，MySQL能夠識別并分別使用單列索引進行掃描，然后將結(jié)果合并。這種算法一般用于以下三種情況：

• or條件的并集（union 或者 union all）
• and條件的交際
• 綜合前面兩種情況

注意：過多的單列索引大部分情況下并不能提高性能。《高性能MySQL》一書中的作者認(rèn)為，索引合并雖然是MySQL的優(yōu)化方案，但是出現(xiàn)了這種現(xiàn)象，更多是說明索引建的很糟糕。

索引的種類

創(chuàng)建索引語法為：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name [index_type] ON tbl_name (key_part,...) [index_option] [algorithm_option | lock_option] ...

InnoDB引擎支持如下常見的三種索引：

B 樹索引的類型及使用

B 樹索引就是我們常見的主鍵索引，唯一索引等普通索引

普通索引

如：

CREATE INDEX name_index ON test2 (name);

唯一索引

如：

ALTER TABLE test2 DROP INDEX name_index; -- 先刪掉上面創(chuàng)建的索引CREATE UNIQUE INDEX name_index ON test2 (name);

前綴索引

前綴索引只能用在CHAR, VARCHAR, BINARY,VARBINARY及TEXT等字符類型的列上。如下：

ALTER TABLE test2 DROP INDEX name_index; -- 先刪掉上面創(chuàng)建的索引CREATE INDEX name_index ON test2 (name(10));

name(10)就表示只把name中前10位作為索引的列

多列聯(lián)合索引

可以把多列作為共同索引，如下：

CREATE INDEX id_name_index ON test2 (id,name);

全文索引

每張表最多允許創(chuàng)建一個全文索引，目前只有InnoDB和MyISAM兩種存儲引擎支持全文索引。全文索引只能在字符類型的字段創(chuàng)建，比如 char、varchar、text等。如下：

ALTER TABLE test2 DROP INDEX name_index; -- 先刪掉上面創(chuàng)建的索引CREATE FULLTEXT INDEX name_index ON test2 (NAME);

請注意，全文索引的查詢語法和其他索引不一樣，全文索引使用如下語法進行查詢：

MATCH (col1,col2,...) AGAINST (expr [search_modifier])

其中:search_modifier有如下選項：

search_modifier: { IN NATURAL LANGUAGE MODE | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION | IN BOOLEAN MODE | WITH QUERY EXPANSION }

如下示例：

CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body) ) ENGINE=InnoDB; INSERT INTO articles (title,body) VALUES (MySQL Tutorial,DBMS stands for DataBase ...), (How To Use MySQL Well,After you went through a ...), (Optimizing MySQL,In this tutorial we will show ...), (1001 MySQL Tricks,1. Never run mysqld as root. 2. ...), (MySQL vs. YourSQL,In the following database comparison ...), (MySQL Security,When configured properly, MySQL ...); SELECT * FROM articles WHERE MATCH (title,body) AGAINST (database IN NATURAL LANGUAGE MODE);

注意：NATURAL LANGUAGE MODE 表示的是自然語言模式，也是默認(rèn)的全文索引的查詢模式，所以上面示例中的查詢也可以直接這么寫：

SELECT * FROM articles WHERE MATCH (title,body) AGAINST (database);

全文索引不得不說的事

在MySQL 5.7.6之前，MySQL全文索引只支持英文全文索引，不支持中文全文索引(只能把整個中文當(dāng)成一個詞語搜索)，如果需要支持中文則需要使用插件ngram來實現(xiàn)，MySQL從5.7.6開始才內(nèi)置了ngram全文解析器，用來支持中文、日文、韓文分詞。

全文索引還有很多細節(jié)需要注意的地方，本文篇幅有限，就不進一步闡述了！

哈希索引

InnoDB中的哈希索引是一種自適應(yīng)哈希索引，也就是說我們不能直接創(chuàng)建哈希索引，目前MySQL引擎中只有Memory引擎支持創(chuàng)建哈希索引

索引信息分析

我們知道，有些查詢語句是用不到索引的，那么一句查詢語句到底在什么情況下用到索引，什么情況下用不到索引呢？MySQL是如何選擇的呢？新建一張表test：

CREATE TABLE `test` ( `id` int(5) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `company` varchar(20) DEFAULT NULL, `age` tinyint(2) DEFAULT NULL, `create_time` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `name_index` (`name`), KEY `name_age_index` (`name`,`age`)) ENGINE=InnoDB AUTO_INCREMENT=120 DEFAULT CHARSET=utf8

初始化一些數(shù)據(jù)，然后先讓我們執(zhí)行一條語句：

SHOW INDEX FROM test

返回結(jié)果如下：

注意：第三行和第四行是一個多列索引，這里的查詢是按照列顯示的查詢結(jié)果的字段含義如下：

• Table：表名
• Non_unique：是否非唯一索引，0-否(主鍵是唯一索引，所以是0) 1-是
• Key_name：索引名稱
• Seq_in_index：索引所在位置(組合索引的時候可以看出區(qū)別，單列索引都是1)
• Column_name：索引列的名稱
• Collation：列是以什么方式存儲的，值為A或者Null，對于B 樹索引，總是為A，代表排序；而全文索引或者哈希索引則為Null
• Cardinality：索引中唯一值的估計值。這個數(shù)字越接近總數(shù)，則表示索引的選擇性越高，如果這個數(shù)很小，那么可以考慮刪除這個索引，因為重復(fù)值太多，選擇性就不高，用到索引的概率也相對較低。
• Sub_part：是否是列的部分被索引。如果索引全部則為Null，如果是對字段的某一長度索引，則顯示具體長度。
• Packed：索引值如何被壓縮，沒有壓縮則為Null。
• Null：索引列是否允許Null值
• Index_type：索引的類型
• Comment：關(guān)于索引的信息沒有在它自己的列中描述，例如，如果索引已禁用，則禁用索引
• Index_comment：創(chuàng)建索引時的comment屬性值

關(guān)于Cardinality

Cardinality是通過采樣來實現(xiàn)計算的，也就是說并不是一個精確值，而是一個統(tǒng)計值，而且這個值并不會實時更新(親測如果你的表足夠小，是會實時更新的)，如果表夠大，每次更新都會帶來消耗，如果想要手動更新的話，可以使用以下步驟：

• 對InnoDB引擎，可以執(zhí)行ANALYZE TABLE 表名來強制更新()，
• 對MyISAM引擎，則可以執(zhí)行命令：myisamchk 表名，注意,這個命令是要到服務(wù)器中數(shù)據(jù)庫存儲的文件目錄里面（通過：SHOW VARIABLES LIKE datadir’可以查詢到數(shù)據(jù)存儲路徑）去執(zhí)行的，而不是在sql語句里面執(zhí)行，這一點網(wǎng)上有些博客并沒有說清楚。官網(wǎng)介紹還有一種執(zhí)行方式是myisamchk 表名.MYI也可以執(zhí)行，親測后發(fā)現(xiàn)是無法執(zhí)行的，會報錯提示無法打開表，據(jù)其他博主介紹這個是MySQL5.6之后出現(xiàn)的Bug，本人用的是MySQL5.7.26，有興趣的可以自己用低版本MySQL嘗試一下

Cardinality的更新策略

InnoDB存儲引擎內(nèi)部對更新Cardinality信息的策略有兩種：

• 上一次統(tǒng)計Cardinality之后，表中1/M之一的數(shù)據(jù)發(fā)生過變化
• stat_modifier_counter>2,000,000,000：這種情況主要針對的是假如少數(shù)行頻繁的更改，表中的數(shù)據(jù)發(fā)生變化數(shù)達不到1/M的情況，所以在InnoDB引擎內(nèi)部有一個計數(shù)器stat_modifier_counter，用來統(tǒng)計表發(fā)生變化的次數(shù)(注意這不是某一行變化的次數(shù)，而是整體的變化次數(shù))

Cardinality的計算方式

InnoDB默認(rèn)對N個葉子節(jié)點進行抽樣統(tǒng)計，所以如果一張表足夠小的話，每次統(tǒng)計的值是一樣的，采樣統(tǒng)計過程如下：1、獲得葉子節(jié)點的總數(shù)A2、隨機獲取葉子節(jié)點N個，并相加，獲得總數(shù)total3、(total / N) * A 得到采樣的數(shù)據(jù)

在 MySQL 中，有兩種存儲索引統(tǒng)計的方式，可以通過設(shè)置參數(shù) innodb_stats_persistent 的值來選擇：

• 設(shè)置為 on 的時候，表示統(tǒng)計信息會持久化存儲。這時，默認(rèn)的N是20，M是10。這時候N的值由變量innodb_stats_persistent_sample_pages控制
• 設(shè)置為 off 的時候，表示統(tǒng)計信息只存儲在內(nèi)存中。這時，默認(rèn)的 N 是 8，M 是 16。這時候N的值由變量innodb_stats_transient_sample_pages控制

另外，統(tǒng)計的時候?qū)ull值也有特殊處理，由變量innodb_stats_method控制

索引的使用原則離散度

離散度=count(distinct(column_name)) /count(*)，而count(distinct(column_name))實際上就是上文中介紹的Cardinality值。某一列的離散度越高，也就是說越接近1，則被MySQL優(yōu)化器選擇作為索引的概率就越大。

最左匹配原則

MySQL索引遵循最左匹配原則，這又可以分為兩種情況

like和_的最左匹配方式

比如我們在表user中的列name中創(chuàng)建了索引，然后執(zhí)行查詢語句：

select * from user where name like %張三;select * from user where name like _張三;

這兩種因為不是從開頭開始匹配的，等于跳過了索引的開頭部分，根據(jù)索引的最左匹配原則，這種情況就不會使用索引

聯(lián)合索引的最左匹配方式

比如我們在表user中的列name和age中創(chuàng)建了聯(lián)合索引index(name,age)，然后執(zhí)行查詢語句：

select * from user where name=張三;select * from user where age=12;select * from user where name=張三 and age=12;

上面的索引中1和3是可以用到索引的，聯(lián)合索引可以只使用一列，和第二句，因為跳過了name直接搜索age，違反了最左匹配原則，所以一般不支持索引。

其他無法使用索引場景

• 在索引列上使用函數(shù)（replacesubstrconcatsum count avg等），使用表達式或者計算( 、-、*、/)
• 字符串不加引號，會出現(xiàn)隱式轉(zhuǎn)換，相當(dāng)于使用函數(shù)to_char()
• 使用!，<>，not like，not in等反向查詢

這些規(guī)則其實也僅僅只是在一般情況下，然后到底用不用索引，最終還是要優(yōu)化器決定，MySQL優(yōu)化器是基于開銷來決定是否使用索引而不是基于規(guī)則來決定是否使用索引。下面讓我們來看一下無法使用索引中的特例：

無法使用索引中的特例<> 和not in特例

CREATE TABLE `course` ( `cid` int(3) NOT NULL, `cname` varchar(20) DEFAULT NULL, `tid` int(3) DEFAULT NULL, PRIMARY KEY (`cid`), KEY `cname_tid_index` (`cname`,`tid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4insert into `course`(`cid`,`cname`,`tid`) values (1,語文,1),(2,數(shù)據(jù),1),(3,英語,2),(4,物理,3);

我們對這張表執(zhí)行查詢語句：

EXPLAIN SELECT * FROM course WHERE cid <>1;EXPLAIN SELECT * FROM course WHERE cid NOT IN (1);

最左匹配原則特例

還是上面那張表，我們執(zhí)行下面這個sql去看一下結(jié)果：

EXPLAIN SELECT COUNT(*) FROM course GROUP BY tid

可以看到，雖然違反了最左匹配原則，還是用到了索引。

總結(jié)

總之，能不能用到索引，我們不要太依賴這些規(guī)則，還要自己實際去試一試，正所謂耳聽為虛，眼見為實！

作者：雙子孤狼

原文鏈接：https://blog.csdn.net/zwx900102/article/details/106403156