1.1.   二叉索引

B+tree是由二叉樹》平衡二叉樹》B-tree演化而來。

二叉樹每個節點最多兩個子節點，左子樹鍵值永遠小於右子樹，並小於根鍵值。

1.2.   平衡二叉樹結構

平衡二叉樹在二叉樹結構基礎上提高，必須滿足左右兩個子樹的高度差的絕對值不超過1，且左子樹和右子樹都是一顆平衡二叉樹，，隨時要保證插入後的整棵二叉樹是平衡的，通郭左旋或右旋使不平衡的樹變平衡。

1.3.   B-tree結構

B-tree又稱Btree，每個節點最多4個子節點，除了根節點和葉子節點，其他節點最少2個子節點。所有葉子節點在同一層，葉子節點不包括任何關鍵字資訊。

1.4.   B+tree

B+tree使Btree的變體，是一種多路搜尋樹，所有關鍵字和資料都儲存在葉子節點中，並且包含關鍵字記錄的指標。

總結：B+tree索引是雙向連結串列結構，檢索比B-tree快，訪問關鍵字的順序是連續性的，不用再訪問上一個節點，且葉子節點包含所有的資料資訊。

1.4.1.   聚集索引和普通索引

B+tree分為兩大類，一類叫聚集索引，一類叫非聚集索引（普通索引）。

InnoDB儲存引擎是索引組織表，聚集索引是一種索引組織表形式，索引鍵值的邏輯順序決定了表資料行的物理儲存順序。

聚集索引葉子節點存放表中所有行資料記錄的資訊，即資料即索引、索引即資料。建立表時建主鍵（聚集索引），如不建主鍵則InnoDB會選擇第一個不包含由Null值得唯一索引作為主鍵，如果唯一索引沒有，則預設為該表生成一個6位元組得rowid為主鍵。

普通索引在葉子節點不包含所有行得資料記錄，只在葉子節點存有自己本身鍵值和主鍵得值。檢索資料，透過普通索引葉子節點上主鍵來獲取想要查詢的行資料記錄。

                                             

普通索建立語法：

alter table tab_name add index index_name(col1);

或：

create index inde_name on tab_name(col1);

檢視錶中有哪些索引；

show index from tab_name;

 

索引建立實驗

l   建立測試庫

mysql> create database test;

l   建立測試表

DROP TABLE IF EXISTS `t`;

CREATE TABLE `t` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(20) NOT NULL,

  `address` varchar(20) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

 

l   檢視錶結構

[test]>desc t;

+---------+-------------+------+-----+---------+----------------+

| Field   | Type        | Null | Key | Default | Extra          |

+---------+-------------+------+-----+---------+----------------+

| id      | int(11)     | NO   | PRI | NULL    | auto_increment |

| name    | varchar(20) | NO   |     | NULL    |                |

| address | varchar(20) | NO   |     | NULL    |                |

+---------+-------------+------+-----+---------+----------------+

 

l   建立儲存過程

DELIMITER $$

DROP PROCEDURE IF EXISTS `proc_auto_insertdata`$$

CREATE PROCEDURE `proc_auto_insertdata`()

BEGIN

        DECLARE init_data INTEGER DEFAULT 1;

        WHILE init_data <= 60000 DO

        INSERT INTO test.t VALUES(CONCAT('name', init_data), init_data + 10);

        SET init_data = init_data + 1;

        END WHILE;

END$$

DELIMITER ;

 

l   呼叫儲存過程插入資料

CALL proc_auto_insertdata();

資料插入完成，看資料檔案10M

 

l   檢視執行計劃

test> explain select * from t where name='name11';

l   建立索引

create index idx_tname on t(name);

l   再次檢視執行計劃

 

最佳化方法

l   執行計劃檢視方法：

1.      看查詢型別type，如出現all，代表全表掃描；

2.      看key列，看是否使用l 索引。null表示沒有使用索引；

3.      看rows列，SQL執行過程中被掃描的行數；

4.      看extra列，觀察是否有Using filesort或Using temporary，這些影響效能。

5.      看filtered列，（5.7增加，5.6用explain extended增加此列），代表返回結果的行佔需要讀取行的百分比。

 

l   SQL最佳化思路：

1.      檢視錶的資料型別是否設計的合理，是否遵守選區資料型別越簡單越小的原則。

2.      表中碎片是否整理。

3.      表的統計資訊是否收集。

4.      檢視執行計劃如沒用到索引，需建立。

5.      建立索引前，檢視索引的選擇性，判斷欄位是否合適建立索引。選擇性指不重複的索引值（基數，cardinality）和記錄總數的比值，比值越高越好。

6.      建立索引後，再看執行計劃，比對前後。

 

l   合理建立索引：

1.      經常被查詢的列。

2.      經常用於表連線的列。

3.      經常排序分組的類。

 

1.4.2.   ICP、MRR和BKA

ICP(Index Condition Pushdown) 是mysql使用索引從表重檢索行資料的一種最佳化方式。5.6開始支援。之前儲存引擎取所有資料給server使用索引過濾處理。使用ICP之後，可以使用索引的話，儲存引擎過濾完資料再給server層。ICP能減少引擎層訪問基表的次數和server層訪問儲存引擎的次數。

透過optimizer_switch引數中的index_condition_pushdow來控制，預設開啟。

[mysql]>show variables like '%pushdown%';

關閉：

set optimizer_switch=”index_condition_pushdown=on|off”;

使用ICP最佳化時，執行計劃extra列會顯示Using index condition。

5.7中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,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on |

 

MBR(Multi-Range Read Optimization) ,5.6後增加。透過optimizer_switch引數中兩個選項控制，引數預設開啟。

mrr_cost_basd：透過基於成本的演算法來確定開啟mrr特性，on自動，off強制開啟。

MBR作用：把普通索引上的葉子節點上找到的主鍵值的集合儲存到read_rnd_buffer中，然後再該buffer中對主鍵值排序，然後用排序號的主鍵值集合去訪問表中的資料，將隨機IO程式設計順序IO，降低查詢過程IO開銷。

使用MBR最佳化時，執行計劃extra列會顯示Using MBR。

 

BKA（Batched Key Access）,提高表join效能的演算法，作用是讀取被join表的記錄時候使用順序IO。

BKA原理：多表join語句，使用索引訪問第二個join表時，使用一個join buffer來收集第一個操作物件生成的相關列值，BKA構建好key後，批次傳給引擎層做索引查詢，key透過MBR介面提交給引擎。

透過optimizer_switch引數的batched_key_access選項控制，預設關閉。

要開啟該引數，必須強制使用MBR才行。

SET global optimizer_switch=’mrr=on,mrr_cost_based=off’;

SET global optimizer_switch=’batched_key_access=on’;

當BKA使用時，執行計劃extra列會顯示Using join buffer(Batched Key Access)。

1.4.3.   主鍵索引和唯一索引

主鍵索引就是聚集索引，每表只能有一個。必須滿足三個條件：

l   主鍵值必須唯一。

l   不能包含null值。

l   一定要保證該值是自增屬性。可以保證寫入資料的順序也是自增的，提高存取效率。

建立主鍵語法：

alter table tab_name add primary key(col);

 

唯一索引，不允許有重複值，但允許空值，可以有多個唯一索引。

語法：

alter table tab_name add unique(col);

1.4.4.   覆蓋索引

資料在索引中，查到索引不必再回表查詢資料。執行計劃extra列中會出現Using index。

如使用覆蓋索引，一定要讓select列出所需要的列，堅決不能直接寫出select *

1.4.5.   字首索引

對於BLOB、TEXT或很長的varchar型別的列，為他們前幾個字元建立的索引，就是字首索引。不能再ORDER BY 或GROUP BY中使用字首索引，也不能用作覆蓋索引。

alter table tab_name add key(col_name(prefix_length));

注意：最關鍵的引數prefix_length，這個值需要根據實際表的內容來得到合適的索引選擇性。

1.4.6.   聯合索引

聯合索引又叫複合索引，是表中兩個或兩個以上的列建立的索引。

create index idx_c1_c2 on t(c1,c2);

選擇性高的列放前面。

1.5.   雜湊索引

雜湊索引採用雜湊演算法，把鍵值換算成新的雜湊值。雜湊索引只能進行等值查詢，不能進行排序、模糊查詢、範圍查詢等。檢索時不需要像B+tree那樣從根節點到葉子節點逐級查詢，只需一次雜湊演算法即可立即定位到相應的位置。

1.6.   索引總結

索引優點

l   提高資料檢索效率

l   提高據合函式效率

l   提高排序效率

l   使用覆蓋索引可以避免回表

 

索引建立四個不要

l   選擇性低的欄位不要建立索引

l   很少查詢的列不要建立索引

l   大資料型別欄位不要建立索引

l   儘量避免不要使用NULL，應指定列為NOT NULL。

 

使用不到索引的情況

l   透過索引掃描的行記錄數超過全表30%，最佳化器不會走索引，而走全表掃描。

l   聯合索引中，第一個查詢條件不是最左側列。

l   聯合索引中，第一個索引列使用範圍查詢，只能使用到部分索引，有ICP出現。

l   聯合索引中，第一個查詢條件不是最左字首列。

l   模糊查詢條件列最左以萬用字元%開始。

l   兩個單列索引，一個用於檢索，一個使用者排序。只能使用到一個索引，因為查詢語句最多隻能使用一個索引，考慮建立聯合索引。

l   查詢欄位上有索引，但使用了函式運算。