溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
B+tree是由二叉樹》平衡二叉樹》B-tree演化而來。
二叉樹每個節點最多兩個子節點，左子樹鍵值永遠小于右子樹，并小于根鍵值。
平衡二叉樹在二叉樹結構基礎上提高，必須滿足左右兩個子樹的高度差的絕對值不超過1，且左子樹和右子樹都是一顆平衡二叉樹，，隨時要保證插入后的整棵二叉樹是平衡的，通郭左旋或右旋使不平衡的樹變平衡。
B-tree又稱Btree，每個節點最多4個子節點，除了根節點和葉子節點，其他節點最少2個子節點。所有葉子節點在同一層，葉子節點不包括任何關鍵字信息。
B+tree使Btree的變體，是一種多路搜索樹，所有關鍵字和數據都保存在葉子節點中，并且包含關鍵字記錄的指針。
總結：B+tree索引是雙向鏈表結構，檢索比B-tree快，訪問關鍵字的順序是連續性的，不用再訪問上一個節點，且葉子節點包含所有的數據信息。
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. 經常排序分組的類。
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)。
主鍵索引就是聚集索引，每表只能有一個。必須滿足三個條件：
l 主鍵值必須唯一。
l 不能包含null值。
l 一定要保證該值是自增屬性。可以保證寫入數據的順序也是自增的，提高存取效率。
創建主鍵語法：
alter table tab_name add primary key(col);
唯一索引，不允許有重復值，但允許空值，可以有多個唯一索引。
語法：
alter table tab_name add unique(col);
數據在索引中，查到索引不必再回表查詢數據。執行計劃extra列中會出現Using index。
如使用覆蓋索引，一定要讓select列出所需要的列，堅決不能直接寫出select *
對于BLOB、TEXT或很長的varchar類型的列，為他們前幾個字符建立的索引，就是前綴索引。不能再ORDER BY 或GROUP BY中使用前綴索引，也不能用作覆蓋索引。
alter table tab_name add key(col_name(prefix_length));
注意：最關鍵的參數prefix_length，這個值需要根據實際表的內容來得到合適的索引選擇性。
聯合索引又叫復合索引，是表中兩個或兩個以上的列創建的索引。
create index idx_c1_c2 on t(c1,c2);
選擇性高的列放前面。
哈希索引采用哈希算法，把鍵值換算成新的哈希值。哈希索引只能進行等值查詢，不能進行排序、模糊查找、范圍查詢等。檢索時不需要像B+tree那樣從根節點到葉子節點逐級查找，只需一次哈希算法即可立即定位到相應的位置。
索引優點
l 提高數據檢索效率
l 提高據合函數效率
l 提高排序效率
l 使用覆蓋索引可以避免回表
索引創建四個不要
l 選擇性低的字段不要創建索引
l 很少查詢的列不要創建索引
l 大數據類型字段不要創建索引
l 盡量避免不要使用NULL，應指定列為NOT NULL。
使用不到索引的情況
l 通過索引掃描的行記錄數超過全表30%，優化器不會走索引，而走全表掃描。
l 聯合索引中，第一個查詢條件不是最左側列。
l 聯合索引中，第一個索引列使用范圍查詢，只能使用到部分索引，有ICP出現。
l 聯合索引中，第一個查詢條件不是最左前綴列。
l 模糊查詢條件列最左以通配符%開始。
l 兩個單列索引，一個用于檢索，一個用戶排序。只能使用到一個索引，因為查詢語句最多只能使用一個索引，考慮建立聯合索引。
l 查詢字段上有索引，但使用了函數運算。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
