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

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

存儲引擎介紹

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

MyISAM引擎

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

InnoDB引擎

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

MyISAM索引結構

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

InnoDB索引結構

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

聚集索引

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

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

InnoDB中聚集索引大致結構如下圖所示：

非聚集索引

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

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

回表

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

覆蓋索引

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

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

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

執行如下語句：

show variables like optimizer_switch;

會返回如下結果：

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這個參數就是是否開啟索引下推優化的，on表示開啟，off表示關閉。可以通過如下語句設置：

SET optimizer_switch=index_condition_pushdown=off;

Multi-Range Read(MRR)

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

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

mrr=onmrr_cost_based=on

mrr=on表示啟用，mrr_cost_based 表示是否通過基于開銷的方式來啟用MRR，如果mrr_cost_based=on，則即使滿足了使用MRR的條件，優化器也會視當前查詢的開銷來決定是否使用MRR，如果我們想總是開啟MRR，則可以將mrr設置為on,mrr_cost_based設置為off，如下：

SET optimizer_switch=mrr=off,mrr_cost_based=off;

MRR的工作方式

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

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

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

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

INDEX MERGE

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

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

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

索引的種類

創建索引語法為：

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; -- 先刪掉上面創建的索引CREATE UNIQUE INDEX name_index ON test2 (name);

前綴索引

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

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

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

多列聯合索引

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

CREATE INDEX id_name_index ON test2 (id,name);

全文索引

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

ALTER TABLE test2 DROP INDEX name_index; -- 先刪掉上面創建的索引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 表示的是自然語言模式，也是默認的全文索引的查詢模式，所以上面示例中的查詢也可以直接這么寫：

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

全文索引不得不說的事

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

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

哈希索引

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

索引信息分析

我們知道，有些查詢語句是用不到索引的，那么一句查詢語句到底在什么情況下用到索引，什么情況下用不到索引呢？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

初始化一些數據，然后先讓我們執行一條語句：

SHOW INDEX FROM test

返回結果如下：

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

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

關于Cardinality

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

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

Cardinality的更新策略

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

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

Cardinality的計算方式

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

在 MySQL 中，有兩種存儲索引統計的方式，可以通過設置參數 innodb_stats_persistent 的值來選擇：

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

另外，統計的時候對Null值也有特殊處理，由變量innodb_stats_method控制

索引的使用原則離散度

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

最左匹配原則

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

like和_的最左匹配方式

比如我們在表user中的列name中創建了索引，然后執行查詢語句：

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

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

聯合索引的最左匹配方式

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

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

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

其他無法使用索引場景

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

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

無法使用索引中的特例<> 和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,數據,1),(3,英語,2),(4,物理,3);

我們對這張表執行查詢語句：

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

最左匹配原則特例

還是上面那張表，我們執行下面這個sql去看一下結果：

EXPLAIN SELECT COUNT(*) FROM course GROUP BY tid

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

總結

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

作者：雙子孤狼

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