Mysql索引（一篇就夠le）

　　我想很多人對mysql的認知可能就是CRUD（代表建立(Create)、更新(Update)、讀取(Retrieve)和刪除(Delete)操作），也不敢說自己會用和熟悉mysql，當然我就是其中一個，雖然知道mysql有很多東西，但是一直都沒有深入的瞭解和掌握，最近想著好好的把Mysql原理學習下，這篇就是開胃菜吧，以後的慢慢道來。本篇文章內容主是基於mysql的InnoDB儲存引擎。

一、Mysql索引介紹

　　索引是一個單獨的、儲存在磁碟上的資料庫結構，它們包含著對資料表裡所有記錄的引用指標。使用索引用於快速找出在某個或多個列中有一特定值的行，所有MySQL列型別都可以被索引，對相關列使用索引是提高查詢操作速度的最佳途徑。
　　MySQL索引的建立對於MySQL的高效執行是很重要的，索引可以大大提高MySQL的檢索速度。比如我們在查字典的時候，前面都有檢索的拼音和偏旁、筆畫等，然後找到對應字典頁碼，這樣然後就開啟字典的頁數就可以知道我們要搜尋的某一個key的全部值的資訊了。
　　建立索引時，你需要確保該索引是應用在 SQL 查詢語句的條件(一般作為 WHERE 子句的條件)，而不是在select的欄位中，實際上，索引也是一張“表”，該表儲存了主鍵與索引欄位，並指向實體表的記錄，雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對錶進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要儲存資料，還要儲存一下索引檔案，建立索引會佔用磁碟空間的索引檔案。說白了索引就是用來提高速度的，但是就需要維護索引造成資源的浪費，所以合理的建立索引是必要的。

1.1、索引的類別

先去官網文件看看支援的索引型別，索引的實現方式如下圖所示：https://dev.mysql.com/doc/refman/8.0/en/create-index.html（官網）

  由於本文是基於mysql的InnoDB儲存引擎，索引我們主要看第一個表格，其他的表格可以自行的觀看，都不難，從表格我們可以看出來，InnoDB儲存引擎索引只支援BTREE型別的索引，索引的類別有Primary Key，Unique，Key，FULLTEXT和SPATIAL。當然也有其他的分法，按照索引列的數量分為單列索引和組合索引。

1. Primary Key（聚集索引）：InnoDB儲存引擎的表會存在主鍵（唯一非null），如果建表的時候沒有指定主鍵，則會使用第一非空的唯一索引作為聚集索引，否則InnoDB會自動幫你建立一個不可見的、長度為6位元組的row_id用來作為聚集索引。
2. 單列索引：單列索引即一個索引只包含單個列
3. 組合索引：組合索引指在表的多個欄位組合上建立的索引，只有在查詢條件中使用了這些欄位的左邊欄位時，索引才會被使用。使用組合索引時遵循最左字首集合
4. Unique（唯一索引）：索引列的值必須唯一，但允許有空值。若是組合索引，則列值的組合必須唯一。主鍵索引是一種特殊的唯一索引，不允許有空值
5. Key（普通索引）：是MySQL中的基本索引型別，允許在定義索引的列中插入重複值和空值
6. FULLTEXT（全文索引）：全文索引型別為FULLTEXT，在定義索引的列上支援值的全文查詢，允許在這些索引列中插入重複值和空值。全文索引可以在CHAR、VARCHAR或者TEXT型別的列上建立
7. SPATIAL（空間索引）：空間索引是對空間資料型別的欄位建立的索引，MySQL中的空間資料型別有4種，分別是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL關鍵字進行擴充套件，使得能夠用於建立正規索引類似的語法建立空間索引。建立空間索引的列必須宣告為NOT NULL

這裡在說一下組合索引的遵循最左字首原則：

order by使用索引最左字首
- order by a
- order by a,b
- order by a,b,c
- order by a desc, b desc, c desc 

如果where使用索引的最左字首定義為常量，則order by能使用索引
- where a=const order by b,c
- where a=const and b=const order by c
- where a=const and b > const order by b,c

不能使用索引進行排序
- order by a , b desc ,c desc  --排序不一致
- where d=const order by b,c   --a丟失
- where a=const order by c     --b丟失
- where a=const order by b,d   --d不是索引的一部分
- where a in(...) order by b,c --a屬於範圍查詢

建立一個簡單的表：

CREATE TABLE my_test (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `sex` varchar(5) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `birthday` datetime NOT NULL,
  `user_num` int(11) unique,
  PRIMARY KEY (`id`),
  index(username)
);

show index from my_test;

  明明在建表的時候只建立了一個索引，查詢出來的有三個，其實主鍵，唯一約束列，外來鍵這些都自動會生成索引，至於外來鍵大家可以去嘗試下。

上表格中各個列的說明：

table #表名稱
non_unique  #如果索引不能包括重複詞，為0，如果可以，則為1
key_name  #索引的名稱
seq_in_index #索引中的列序號
column_name  #列名稱
collation  #列以什麼方式儲存在索引中，在mysql中，有值'A'（升序）或者NULL（無分類）
cardinality  #索引在唯一值的資料的估值，通過執行analyze table xxx_table;或者 myisamchk -a 可以更新，技術根據被儲存為整數的統計資料來計數，所以即使對於小型表，該值也沒必要是精確的，基數越大，當進行聯合所飲食，mysql使用該索引的機會越大。myisam中，該值是準確的，INNODB中該值資料是估算的，存在偏差
sub_part  #如果列只是部分的編入索引 則為被編入索引的字元的數目，如果整列被編入索引，則為NULL
packed  #指示關鍵詞如何被壓縮，如果沒有被壓縮，則為NULL
NULL   #如果列含有NULL，則含有YES，如果沒有，則該列為NO
index_type  #用過的索引方法（BTREE,FULLTEXT,HASH,RTREE）
comment  #備註
index_comment  #為索引建立時提供了一個註釋屬性的索引的任何評論

1.2、索引的建立原則

1. 索引並非越多越好，一個表中如果有大量的索引，不僅佔用磁碟空間，而且會影響INSERT、DELETE、UPDATE等語句的效能，因為在表中的資料更改的同時，索引也會進行調整和更新
2. 避免對經常更新的表進行過多的索引，並且索引中的列儘可能少。而對經常用於查詢的欄位應該建立索引，但要避免新增不必要的欄位。
3. 資料量小的表最好不要使用索引，由於資料較少，查詢花費的時間可能比遍歷索引的時間還要短，索引可能不會產生優化效果。
4. 在條件表示式中經常用到的不同值較多的列上建立索引，在不同值很少的列上不要建立索引。比如在學生表的“性別”欄位上只有“男”與“女”兩個不同值，因此就無須建立索引。如果建立索引，不但不會提高查詢效率，反而會嚴重降低資料更新速度。
5. 當唯一性是某種資料本身的特徵時，指定唯一索引。使用唯一索引需能確保定義的列的資料完整性，以提高查詢速度。
6. 在頻繁進行排序或分組（即進行group by或order by操作）的列上建立索引，如果待排序的列有多個，可以在這些列上建立組合索引。
7. 搜尋的索引列，不一定是所要選擇的列。換句話說，最適合索引的列是出現在WHERE子句中的列，或連線子句中指定的列，而不是出現在SELECT關鍵字後的選擇列表中的列。
8. 使用短索引。如果對字串列進行索引，應該指定一個字首長度，只要有可能就應該這樣做。例如，有一個CHAR(200)列，如果在前10個或20個字元內，多數值是唯一的，那麼就不要對整個列進行索引。對前10個或20個字元進行索引能夠節省大量索引空間，也可能會使查詢更快。較小的索引涉及的磁碟 IO 較少，較短的值比較起來更快。更為重要的是，對於較短的鍵值，索引快取記憶體中的塊能容納更多的鍵值，因此，MySQL 也可以在記憶體中容納更多的值。這樣就增加了找到行而不用讀取索引中較多塊的可能性。
9. 利用最左字首。在建立一個n列的索引時，實際是建立了MySQL可利用的n個索引。多列索引可起幾個索引的作用，因為可利用索引中最左邊的列集來匹配行。這樣的列集稱為最左字首。
10. 對於InnoDB儲存引擎的表，記錄預設會按照一定的順序儲存，如果有明確定義的主鍵，則按照主鍵順序儲存。如果沒有主鍵，但是有唯一索引，那麼就是按照唯一索引的順序儲存。如果既沒有主鍵又沒有唯一索引，那麼表中會自動生成一個內部列，按照這個列的順序儲存。按照主鍵或者內部列進行的訪問是最快的，所以InnoDB表儘量自己指定主鍵，當表中同時有幾個列都是唯一的，都可以作為主鍵的時候，要選擇最常作為訪問條件的列作為主鍵，提高查詢的效率。另外，還需要注意，InnoDB 表的普通索引都會儲存主鍵的鍵值，所以主鍵要儘可能選擇較短的資料型別，可以有效地減少索引的磁碟佔用，提高索引的快取效果

二、索引的管理和使用

2.1、製造實驗資料

這裡的實驗資料採用的是一個github的一個開源專案，具體的操作流程檢視：https://github.com/wuda0112/mysql-tester

資料製造完成後會有四個資料庫：

   資料可以根據引數進行生成，很簡單，根據步驟來就好了。

2.2、explain使用說明

使用實驗資料我們進行explain的查詢：explain SELECT store_id , count(1) from foundation_item.item  group by store_id limit 10;

   EXPLAIN語句的基本語法如下：

explain select select_option

select_options是SELECT語句的查詢選項，包括FROMWHERE子句等

id: SELECT識別符。這是SELECT的查詢序列號,表示查詢中執行select子句或操作表的順序,id相同，執行順序從上到下,id不同，id值越大執行優先順序越高
select_type：表示SELECT語句的型別。它可以是以下幾種取值：
    SIMPLE:表示簡單查詢，其中不包括連線查詢和子查詢；
    PRIMARY:表示主查詢，或者是最外層的查詢語句，最外層查詢為PRIMARY，也就是最後載入的就是PRIMARY；
    UNION:表示連線查詢的第2個或後面的查詢語句， 不依賴於外部查詢的結果集
    DEPENDENT UNION:連線查詢中的第2個或後面的SELECT語句，依賴於外面的查詢；
    UNION RESULT:連線查詢的結果；
    SUBQUERY:子查詢中的第1個SELECT語句；不依賴於外部查詢的結果集
    DEPENDENT SUBQUERY:子查詢中的第1個SELECT，依賴於外面的查詢；
    DERIVED:匯出表的SELECT（FROM子句的子查詢）,MySQL會遞迴執行這些子查詢，把結果放在臨時表裡。
    DEPENDENT DERIVED:派生表依賴於另一個表
    MATERIALIZED:物化子查詢
    UNCACHEABLE SUBQUERY:子查詢，其結果無法快取，必須針對外部查詢的每一行重新進行評估
    UNCACHEABLE UNION:UNION中的第二個或隨後的 select 查詢，屬於不可快取的子查詢
table:表示查詢的表
partitions:查詢將從中匹配記錄的分割槽。該值適用NULL於未分割槽的表
type:表示表的連線型別
    system:該表是僅有一行的系統表。這是const連線型別的一個特例
    const: 資料表最多隻有一個匹配行，它將在查詢開始時被讀取，並在餘下的查詢優化中作為常量對待。const表查詢速度很快，因為只讀取一次,const用於使用常數值比較PRIMARY KEY或UNIQUE索引的所有部分的場合。
    eq_ref:對於每個來自前面的表的行組合，從該表中讀取一行,可以用於使用=運算子進行比較的索引列 。比較值可以是常量，也可以是使用在此表之前讀取的表中列的表示式
    ref:對於來自前面的表的任意行組合，將從該表中讀取所有匹配的行，ref可以用於使用“＝”或“＜＝＞”操作符的帶索引的列。
    fulltext:使用FULLTEXT 索引執行聯接
    ref_or_null:這種連線型別類似於ref，但是除了MySQL還會額外搜尋包含NULL值的行。此聯接型別優化最常用於解析子查詢
    index_merge:此聯接型別指示使用索引合併優化。在這種情況下，key輸出行中的列包含使用的索引列表，並key_len包含使用的索引 的最長鍵部分的列表
    unique_subquery:型別替換 以下形式的eq_ref某些 IN子查詢,unique_subquery 只是一個索引查詢函式，它完全替代了子查詢以提高效率。
    index_subquery:連線型別類似於 unique_subquery。它代替IN子查詢,但只適合子查詢中的非唯一索引
    range:只檢索給定範圍的行，使用一個索引來選擇行。key列顯示使用了哪個索引。key_len包含所使用索引的最長關鍵元素。當使用＝、＜＞、＞、＞＝、＜、＜＝、IS NULL、＜＝＞、BETWEEN或者IN操作符用常量比較關鍵字列時，型別為range
    index:該index聯接型別是一樣的 ALL，只是索引樹被掃描。這發生兩種方式：1、如果索引是查詢的覆蓋索引，並且可用於滿足表中所需的所有資料，則僅掃描索引樹。在這種情況下，Extra列顯示為 Using index，2、使用對索引的讀取執行全表掃描，以按索引順序查詢資料行。 Uses index沒有出現在 Extra列中。
    ALL:對於前面的表的任意行組合進行完整的表掃描    
possible_keys:指出MySQL能使用哪個索引在該表中找到行。若該列是NULL，則沒有相關的索引。在這種情況下，可以通過檢查WHERE子句看它是否引用某些列或適合索引的列來提高查詢效能。如果是這樣，可以建立適合的索引來提高查詢的效能。
kye:表示查詢實際使用的索引，如果沒有選擇索引，該列的值是NULL。要想強制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX
key_len：表示MySQL選擇的索引欄位按位元組計算的長度，若鍵是NULL，則長度為NULL。注意，通過key_len值可以確定MySQL將實際使用一個多列索引中的幾個欄位
ref：表示使用哪個列或常數與索引一起來查詢記錄。
rows：顯示MySQL在表中進行查詢時必須檢查的行數。
Extra：表示MySQL在處理查詢時的詳細資訊

更詳細說明見官網：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

2.3、建立索引

建立索引的語法（如下都是預設的innodb儲存引擎）：https://dev.mysql.com/doc/refman/8.0/en/create-index.html

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

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
  | ENGINE_ATTRIBUTE [=] 'string'
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}

index_type:
    USING {BTREE | HASH}
algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}
lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

可以在建立的表的時候建立索引，也可以對建立好的表建立索引。

2.3.1、基於建立表時建立索引

CREATE TABLE建立表時，除了可以定義列的資料型別，還可以定義主鍵約束、外來鍵約束或者唯一性約束，而不論建立哪種約束，在定義約束的同時相當於在指定列上建立了一個索引。

1、建立普通索引

CREATE TABLE test.`user2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `sex` varchar(5) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `birthday` datetime NOT NULL,
  PRIMARY KEY (`id`),
  index idx1(username)
);

show index from test.`user2`;

   2、建立唯一索引

CREATE TABLE test.`user2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `sex` varchar(5) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `birthday` datetime NOT NULL,
  `score_num` int(11) NOT NULL UNIQUE,
  PRIMARY KEY (`id`),
  unique index idx1(username)
);

show index from test.`user2`;

   前面兩個索引都是通過主鍵和唯一約束自動建立的

3、建立組合索引

注意：最左字首原則

CREATE TABLE test.`user2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `sex` varchar(5) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `birthday` datetime NOT NULL,
  `score_num` int(11) NOT NULL UNIQUE,
  PRIMARY KEY (`id`),
  index idx1(id,score_num,username)
);

   4、建立全文索引

FULLTEXT全文索引可以用於全文搜尋，並且只為CHAR、VARCHAR和TEXT列建立索引。索引總是對整個列進行，不支援區域性（字首）索引

CREATE TABLE test.`user2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `sex` varchar(5) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `birthday` datetime NOT NULL,
  `score_num` int(11) NOT NULL UNIQUE,
  PRIMARY KEY (`id`),
  fulltext index idx1(username)
);

5、建立空間索引

create table test.test(
    position geometry not null,
    spatial index idx1(position)
);

show index from test.test;

  6、建立字首索引

CREATE TABLE test.t1 (
  col1 VARCHAR(10),
  col2 VARCHAR(20),
  INDEX (col1, col2(10))
);

  字首索引的目的主要是減少字串作為索引佔用的空間，提高查詢速度

2.3.2、基於已建立好的表建立索引

在已經存在的表中建立索引，可以使用ALTER TABLE語句或者CREATE INDEX語句建立索引。

1、使用ALTER TABLE語句建立索引

ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[length],...) [ASC|DESC]

2、使用CREATE INDEX建立索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON  table_name (col_name[length],...) [ASC|DESC]

2.4、刪除索引

1、使用ALTER TABLE刪除索引

ALTER TABLE table_name DROP INDEX index_name

CREATE TABLE test.t1 (
  id int AUTO_INCREMENT primary key,
  col1 VARCHAR(10),
  col2 VARCHAR(20),
  INDEX (col1, col2(10))
);

 alter table test.t1 drop index col1;

alter table test.t1 drop index PRIMARY;  #會報錯：新增AUTO_INCREMENT約束欄位的唯一索引不能被刪除

  2、使用DROP INDEX語句刪除索引

DROP INDEX index_name ON table_name;

2.5、聚集索引和二級索引

1、聚集索引

InnoDB儲存引擎表是索引組織表，即表中資料按照主鍵順序存放。而聚集索引（clustered index）就是按照每張表的主鍵構造一棵B+樹，同時葉子節點中存放的即為整張表的行記錄資料，也將聚集索引的葉子節點稱為資料頁。聚集索引的這個特性決定了索引組織表中資料也是索引的一部分。同B+樹資料結構一樣，每個資料頁都通過一個雙向連結串列來進行連結。

由於實際的資料頁只能按照一棵B+樹進行排序，因此每張表只能擁有一個聚集索引。由於定義了資料的邏輯順序，聚集索引能夠特別快地訪問針對範圍值的查詢。查詢優化器能夠快速發現某一段範圍的資料頁需要掃描。

聚集索引的儲存並不是物理上連續的，而是邏輯上連續的。這其中有兩點：一是前面說過的頁通過雙向連結串列連結，頁按照主鍵的順序排序；另一點是每個頁中的記錄也是通過雙向連結串列進行維護的，物理儲存上可以同樣不按照主鍵儲存。

2、二級索引（輔助索引）

對於輔助索引（Secondary Index），葉子節點並不包含行記錄的全部資料。葉子節點除了包含鍵值以外，每個葉子節點中的索引行中還包含了一個書籤（bookmark）。該書籤用來告訴InnoDB儲存引擎哪裡可以找到與索引相對應的行資料。由於InnoDB儲存引擎表是索引組織表，因此InnoDB儲存引擎的輔助索引的書籤就是相應行資料的聚集索引鍵。

當通過輔助索引來尋找資料時，InnoDB儲存引擎會遍歷輔助索引並通過葉級別的指標獲得指向主鍵索引的主鍵，然後再通過主鍵索引來找到一個完整的行記錄。

2.6、覆蓋索引

InnoDB儲存引擎支援覆蓋索引（covering index，或稱索引覆蓋），即從輔助索引中就可以得到查詢的記錄，而不需要查詢聚集索引中的記錄。使用覆蓋索引的一個好處是輔助索引不包含整行記錄的所有資訊，故其大小要遠小於聚集索引，因此可以減少大量的IO操作。

CREATE TABLE `item` (
  `item_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `store_id` bigint(20) unsigned NOT NULL COMMENT '所屬店鋪ID',
  `type` tinyint(3) unsigned NOT NULL COMMENT '商品型別 . 不同型別的商品, 儲存到各自不同的表中. 參考 https://learnwoo.com/woocommerce-different-product-types/',
  `state` tinyint(3) unsigned NOT NULL COMMENT '狀態',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `create_user_id` bigint(20) unsigned NOT NULL,
  `last_modify_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_modify_user_id` bigint(20) unsigned NOT NULL,
  `is_deleted` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`item_id`),
  KEY `fk_store_id` (`store_id`)
) ENGINE=InnoDB AUTO_INCREMENT=332604631475558863 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='代表所有的物品，之前有把使用者ID放進來，表示該物品所屬的使用者，但是考慮到如果有子賬號的情況，物品難道屬於這個子賬號所屬的使用者嗎？而且記錄了建立人使用者ID，考慮這兩個因素，因此不設定使用者ID列'

檢視：explain select store_id,create_time from item where store_id > 332604504321036698 ;

  這裡使用了全表掃描，沒有走索引，然後我們把查詢語句改為：explain select store_id from item where store_id > 332604504321036698 ;

 這樣就變成了範圍查詢，走索引，因為索引中包含了需要查詢的全部值，所以不需要再查詢聚集索引，減少磁碟IO，這樣就可以提高速度。 

2.7、Multi-Range Read優化

Multi-Range Read優化的目的就是為了減少磁碟的隨機訪問，並且將隨機訪問轉化為較為順序的資料訪問，這對於IO-bound型別的SQL查詢語句可帶來效能極大的提升。Multi-RangeRead優化可適用於range，ref，eq_ref型別的查詢。

Multi-Range Read的好處：

1. MRR使資料訪問變得較為順序。在查詢輔助索引時，首先根據得到的查詢結果，按照主鍵進行排序，並按照主鍵排序的順序進行書籤查詢。
2. 減少緩衝池中頁被替換的次數。
3. 批量處理對鍵值的查詢操作

MRR的工作方式如下：

1. 將查詢得到的輔助索引鍵值存放於一個快取中，這時快取中的資料是根據輔助索引鍵值排序的。
2. 將快取中的鍵值根據RowID進行排序。
3. 根據RowID的排序順序來訪問實際的資料檔案。

實驗：採用前面2.1節匯入的資料：

explain select *  from foundation_item.item where store_id > 332604504249736122 and store_id  < 332604504249736201;

 MySQL5.6版本開始支援Multi-Range Read（MRR）優化，通過引數 optimizer_switch 的標記來控制是否使用MRR，當設定mrr=on時，表示啟用MRR優化。mrr_cost_based 表示是否通過 cost base的方式來啟用MRR.如果選擇mrr=on,mrr_cost_based=off,則表示總是開啟MRR優化。

例如設定：set optimizer_switch='mrr=on,mrr_cost_based=on';然後我們繼續檢視：

explain select *  from foundation_item.item where store_id > 332604504249736122 and store_id  < 332604504249736201;

  如上：MRR優化關閉後沒有啟動了

2.8、Index Condition Pushdown（ICP）優化

MySQL資料庫會在取出索引的同時，判斷是否可以進行WHERE條件的過濾，也就是將WHERE的部分過濾操作放在了儲存引擎層。在某些查詢下，可以大大減少上層SQL層對記錄的索取（fetch），從而提高資料庫的整體效能，優化支援range、ref、eq_ref、ref_or_null型別的查詢，選擇Index Condition Pushdown優化時，可在執行計劃的列Extra看到Using index condition提示。

實驗：資料來源於2.1生成的資料：

首先我們建立一個組合索引：create index idx_email_id_address_state on foundation_commons.email (state,address,email_id);

explain SELECT * from foundation_commons.email where state =0 and address like '%D%' and email_id >='332604504249734136' ;

  以上實驗只是為了顯示這樣的現象

1、當sql需要全表訪問時，ICP的優化策略可用於range, ref, eq_ref, ref_or_null型別的訪問資料方法 。
2. 支援InnoDB和MyISAM表。
3. ICP只能用於二級索引，不能用於主索引。
4. 並非全部where條件都可以用ICP篩選，如果where條件的欄位不在索引列中，還是要讀取整表的記錄到server端做where過濾。

三、索引實現的原理

innodb儲存的索引是基於B+樹實現的，從1.1節中的表格可以看出，不支援hash的實現方式。首先來了解下B+樹的特點；

B+樹的特徵：

1. 有k個子樹的中間節點包含有k個元素（B樹中是k-1個元素），每個元素不儲存資料，只用來索引，所有資料都儲存在葉子節點。
2. 所有的葉子結點中包含了全部元素的資訊，及指向含這些元素記錄的指標，且葉子結點本身依關鍵字的大小自小而大順序連結。
3. 所有的中間節點元素都同時存在於子節點，在子節點元素中是最大（或最小）元素。

B+樹的優勢：

1. 單一節點儲存更多的元素，使得查詢的IO次數更少。
2. 所有查詢都要查詢到葉子節點，查詢效能穩定。
3. 所有葉子節點形成有序連結串列，便於範圍查詢。

在B+樹中，所有記錄節點都是按鍵值的大小順序存放在同一層的葉子節點上，由各葉子節點指標進行連線。先來看一個B+樹，其高度為2，每頁可存放4條記錄，扇出（fan out）為5，如下圖所示：

  索引的設計思考：

• 索引是一種儲存方式，最相關的硬體就是磁碟，索引磁碟的效能會直接影響到資料庫的查詢效率
• 磁碟的效能和讀寫的順序有關，普通磁碟順序讀寫比隨機讀寫快很多，所以儘量避免隨機讀寫。
• 資料都是以行為單位一行一行的儲存的，每一行都包括了所有的列，多行可以連續儲存。
• 每一行資料中，一般都有一個鍵，其他的列可以稱為值，可以理解為鍵值對。innodb必須有唯一非空的主鍵，就是預設的鍵。
• 在鍵值對中，鍵值可以排序，還可以組合鍵值。

索引的設計：

• 磁碟空間會劃分為許多個大小相等的塊或者頁，一個頁中可以儲存多行資料，這樣就可以符合磁碟的順序讀寫，這樣一次IO就可以讀取很多資料到記憶體，可以減少磁碟IO。
• 在一個頁內，所有的資料可能會經常變動，並且大小也是相對固定的，所以內部通過連結串列或者陣列管理。
• 每個鍵值可以排序，所以在一個塊內的所有資料也可以是有序的，這樣通過二分法查詢可以很快的在一個頁內找到指定鍵對應的資料
• 一個頁設計好之後，可以把頁作為B+樹的節點，通過頁來承載資料，通過B+數來組織不同頁之間的關係
• B+樹的特點是在內節點儲存鍵來提高搜尋的效能，所以很自然的，內節點用來儲存資料行的鍵，葉子節點儲存所有資料行，可以很好的提升效能

接下來在結合2.5節的聚集索引和二級索引來說：

表中資料按照主鍵順序存放。而聚集索引（clustered index）就是按照每張表的主鍵構造一棵B+樹，同時葉子節點中存放的即為整張表的行記錄資料，也將聚集索引的葉子節點稱為資料頁。聚集索引的這個特性決定了索引組織表中資料也是索引的一部分。同B+樹資料結構一樣，每個資料頁都通過一個雙向連結串列來進行連結。如下圖所示：

 

 

 

 

　　上圖所示的是一個深度為2的B+樹，也是我們所稱的索引，這裡假設頁有隨機唯一的編號，根頁號為20。這裡只有一個內節點（根節點），其他的都是葉子節點，也是資料節點，對於內節點來說，存有key和pageno的指標資訊，對於葉子節點來說，只存有完整的資料。對於聚集索引，data部分存有除主鍵外的其他列的組合，如果是二級索引，則這裡存放就是這行記錄對應主鍵的組合，用於回表。

　　最左邊的MIN為了很好的組織樹形結構的指標，和其他的內節點一樣，主要用來標記它是最小記錄Min，還有就是一個pageno指標指向下層最左邊的Min記錄，其他節點的Min記錄用於判斷搜尋是否到了邊界。每個頁都有頁頭頁尾用來管理和標記頁面的狀態，頁面中的資料是如何儲存，有沒有空閒的空間，以什麼樣的順序儲存等。

　　上圖中所有的葉子節點從左到右都是從小到大的順序以雙向連結串列的方式儲存的，所以當我們需要遍歷全部的資料，只需要通過B+樹找到最小的位置，然後通過遍歷連結串列則可以查詢到所有的資料，還有就是10,16,25這三條記錄在內節點和葉子節點均存在，這既是B+數的特點，葉子節點會存有所有的key和值。而內節點只儲存了key，不儲存其他的資料，只有用來索引。葉子節點除了第一條記錄會有上一層重複的儲存，其他資料不會有這樣的現象，所以浪費的空間也不大，由於每一個頁的大小是固定的（16k），在內節點上只儲存key，不儲存其他資料，一個頁就可以儲存更多的key，這樣檢索也能減少磁碟的IO，由於頁儲存Key增多，這樣就可以使得B+樹的深度減少，這樣也可以減少磁碟的IO，提高查詢效能。

例如一個三層的B+數，每一個頁能存1000個key，所以第二層就有1000*（1+1000）個key，第三層就可以有1000*1001*1001=1002001000（十億級別），一個簡單的三層B+資料就可以存十億級別的資料，很強大。

　　上面說到的“回表”其實就是在使用二級索引進行搜尋時，因為二級索引只儲存了部分列的資料，如果需要獲取鍵值不包括的列的資料時，需要通過二級索引的指標（書籤：用於指向聚集索引的指標）來找到聚集索引的全部資料，然後返回需要查詢的列的值。如果使用二級索引不能找到需要的值（需要回表），稱為非覆蓋索引，否則為2.6節介紹的覆蓋索引。非覆蓋索引需要回表，增加IO，所以效能會差一些。所以可以根據業務需求建立組合索引來避免回表。但是也要權衡索引帶來的利是否大於弊。所以在統計行總數的時候可以通過二級索引來統計，這樣速度會快一些。大概圖形如下：

 

 　　這裡附帶的說一些不能走索引的情況，但是不多說，因為優化這個東西太多，後期準備寫一兩篇優化的文章，所以這裡只是提一下，走索引的強大；雖然可能建立了很多索引，很多情況都不走索引，比如：like '%query_name%' ，where端使用or條件連線，where端使用函式等，在group by和order by使用的時候要注意組合索引的最左字首原則。

 

參考：

《InnoDB儲存引擎（第2版）》

《mysql運維內參》

《mysql8入門到精通》

https://blog.csdn.net/qq_26222859/article/details/80631121
https://zhuanlan.zhihu.com/p/29118331