Mysql索引（一篇就夠le）

2020 年 10 月 16 日
筆記
MySQL

　　我想很多人對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、索引的類別

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

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

Primary Key（聚集索引）：InnoDB存儲引擎的表會存在主鍵（唯一非null），如果建表的時候沒有指定主鍵，則會使用第一非空的唯一索引作為聚集索引，否則InnoDB會自動幫你創建一個不可見的、長度為6位元組的row_id用來作為聚集索引。
單列索引：單列索引即一個索引只包含單個列
組合索引：組合索引指在表的多個欄位組合上創建的索引，只有在查詢條件中使用了這些欄位的左邊欄位時，索引才會被使用。使用組合索引時遵循最左前綴集合
Unique（唯一索引）：索引列的值必須唯一，但允許有空值。若是組合索引，則列值的組合必須唯一。主鍵索引是一種特殊的唯一索引，不允許有空值
Key（普通索引）：是MySQL中的基本索引類型，允許在定義索引的列中插入重複值和空值
FULLTEXT（全文索引）：全文索引類型為FULLTEXT，在定義索引的列上支援值的全文查找，允許在這些索引列中插入重複值和空值。全文索引可以在CHAR、VARCHAR或者TEXT類型的列上創建
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、索引的創建原則

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

二、索引的管理和使用

2.1、製造實驗數據

這裡的實驗數據採用的是一個github的一個開源項目，具體的操作流程查看：//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在處理查詢時的詳細資訊

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

2.3、創建索引

創建索引的語法（如下都是默認的innodb存儲引擎）：//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 '商品類型 . 不同類型的商品, 保存到各自不同的表中. 參考 //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的好處：

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

MRR的工作方式如下：

將查詢得到的輔助索引鍵值存放於一個快取中，這時快取中的數據是根據輔助索引鍵值排序的。
將快取中的鍵值根據RowID進行排序。
根據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+樹的特徵：

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

B+樹的優勢：

單一節點存儲更多的元素，使得查詢的IO次數更少。
所有查詢都要查找到葉子節點，查詢性能穩定。
所有葉子節點形成有序鏈表，便於範圍查詢。

在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入門到精通》

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

Tags: MySQL