mysql學習11:第六章:索引
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 查詢欄位上有索引,但使用了函式運算。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29519108/viewspace-2219114/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL學習 - 索引MySql索引
- MySQL學習之索引MySql索引
- MYSQL學習(三) --索引詳解MySql索引
- MySQL學習筆記:索引失效MySql筆記索引
- MySql 學習筆記二:索引MySql筆記索引
- MySQL學習筆記:組合索引-索引下推MySql筆記索引
- 再一次學習 MySQL 索引MySql索引
- MySQL優化學習筆記之索引MySql優化筆記索引
- MySQL 學習之索引篇和查詢MySql索引
- MySQL 事務、日誌、鎖、索引學習總結,MySql索引
- MySQL學習筆記:組合索引-最左原則MySql筆記索引
- Elasticsearch 學習索引Elasticsearch索引
- 《MySQL實戰45講》學習筆記4——MySQL中InnoDB的索引MySql筆記索引
- MySQL學習之影響優化器選擇索引因素MySql優化索引
- 索引學習二—blevel索引
- 索引學習筆記索引筆記
- Css基礎學習—索引CSS索引
- PostgreSQL學習手冊(索引)SQL索引
- Bootstrap~學習筆記索引boot筆記索引
- 學習MySQLMySql
- MySQL 學習MySql
- Mysql學習MySql
- 分割槽索引學習筆記索引筆記
- ITPUB 知識索引--ASM 學習索引ASM
- 全面學習分割槽表及分割槽索引(11)--合併表分割槽索引
- Laravel 第六章學習——註冊使用者Laravel
- MYSQL學習(二) --MYSQL框架MySql框架
- mysql索引之字首索引MySql索引
- ElasticSearch 索引 VS MySQL 索引Elasticsearch索引MySql
- MySQL — 索引MySql索引
- mysql索引MySql索引
- [Mysql]索引MySql索引
- MySQL 索引MySql索引
- mysql 索引( mysql index )MySql索引Index
- 2011-02-19 , 開始正式學習MySQLMySql
- MYSQL學習筆記11: DQL查詢執行順序MySql筆記
- MySQL深度學習MySql深度學習
- Mysql學習教程MySql