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

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

存儲(chǔ)引擎介紹

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

MyISAM引擎

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

InnoDB引擎

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

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

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

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

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

聚集索引

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

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

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

非聚集索引

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

所以非聚集索引查詢數(shù)據(jù)和聚集索引查詢數(shù)據(jù)是不同的，因?yàn)榉蔷奂饕娜~子節(jié)點(diǎn)只有當(dāng)前索引的鍵值和主鍵的鍵值，也就是說(shuō)查詢數(shù)據(jù)的時(shí)候獲取到非聚集索引的葉子節(jié)點(diǎn)只能拿到當(dāng)前索引值和主鍵索引值。

回表

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

覆蓋索引

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

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

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

執(zhí)行如下語(yǔ)句：

show variables like optimizer_switch;

會(huì)返回如下結(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這個(gè)參數(shù)就是是否開(kāi)啟索引下推優(yōu)化的，on表示開(kāi)啟，off表示關(guān)閉。可以通過(guò)如下語(yǔ)句設(shè)置：

SET optimizer_switch=index_condition_pushdown=off;

Multi-Range Read(MRR)

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

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

mrr=onmrr_cost_based=on

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

SET optimizer_switch=mrr=off,mrr_cost_based=off;

MRR的工作方式

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

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

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

我們想一想，如果我們通過(guò)輔助索引查找到了輔助索引的鍵值和主鍵的鍵值，這時(shí)候我們需要回表，假如輔助索引和主鍵索引順序相差很大，那么回表查主鍵B 樹(shù)的時(shí)候，就是隨機(jī)訪問(wèn)磁盤，也就是隨機(jī)IO操作，而如果使用了MRR，就會(huì)按照主鍵進(jìn)行重排序，這時(shí)候再回表就是順序IO，所以說(shuō)MRR之所以能優(yōu)化是因?yàn)轫樞騃O訪問(wèn)的效率是遠(yuǎn)遠(yuǎn)大于隨機(jī)IO的。

INDEX MERGE

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

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

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

索引的種類

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

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

InnoDB引擎支持如下常見(jiàn)的三種索引：

B 樹(shù)索引的類型及使用

B 樹(shù)索引就是我們常見(jiàn)的主鍵索引，唯一索引等普通索引

普通索引

如：

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)建一個(gè)全文索引，目前只有InnoDB和MyISAM兩種存儲(chǔ)引擎支持全文索引。全文索引只能在字符類型的字段創(chuàng)建，比如 char、varchar、text等。如下：

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

請(qǐng)注意，全文索引的查詢語(yǔ)法和其他索引不一樣，全文索引使用如下語(yǔ)法進(jìn)行查詢：

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

其中:search_modifier有如下選項(xiàng)：

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 表示的是自然語(yǔ)言模式，也是默認(rèn)的全文索引的查詢模式，所以上面示例中的查詢也可以直接這么寫：

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

全文索引不得不說(shuō)的事

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

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

哈希索引

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

索引信息分析

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

SHOW INDEX FROM test

返回結(jié)果如下：

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

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

關(guān)于Cardinality

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

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

Cardinality的更新策略

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

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

Cardinality的計(jì)算方式

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

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

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

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

索引的使用原則離散度

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

最左匹配原則

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

like和_的最左匹配方式

比如我們?cè)诒韚ser中的列name中創(chuàng)建了索引，然后執(zhí)行查詢語(yǔ)句：

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

這兩種因?yàn)椴皇菑拈_(kāi)頭開(kāi)始匹配的，等于跳過(guò)了索引的開(kāi)頭部分，根據(jù)索引的最左匹配原則，這種情況就不會(huì)使用索引

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

比如我們?cè)诒韚ser中的列name和age中創(chuàng)建了聯(lián)合索引index(name,age)，然后執(zhí)行查詢語(yǔ)句：

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

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

其他無(wú)法使用索引場(chǎng)景

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

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

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

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

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

最左匹配原則特例

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

EXPLAIN SELECT COUNT(*) FROM course GROUP BY tid

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

總結(jié)

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

作者：雙子孤狼

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