1、為什么要有索引
? 對查詢陳述句的優化，加速查詢
2、什么是索引
? 索引在MySQL中也叫是一種‘鍵’，是存盤引擎用于快速找到記錄的一種資料結構，索引對于良好的性能非常關鍵，尤其是當表中的資料量越來越大時，索引對于性能的影響愈發重要，
? 索引優化應該是對查詢性能優化最有效的手段了，索引能夠輕易將查詢性能提高好幾個數量級，
? 索引相當于字典的音序表，如果要查某個字，如果不使用音序表，則需要從幾百頁中逐頁去查，
3、索引的原理
(1)、 索引原理
? 本質都是：通過不斷地縮小想要獲取資料的范圍來篩選出最終想要的結果，同時把隨機的事件變成順序的事件，也就是說，有了這種索引機制，就可以總是用同一種查找方式來鎖定資料，
(2)、 磁盤IO與預讀
? 磁盤讀取資料靠的是機械運動，每次讀取資料花費的時間可以分為尋道時間、旋轉延遲、傳輸時間三個部分，尋道時間指的是磁臂移動到指定磁道所需要的時間，主流磁盤一般在5ms以下；旋轉延遲就是磁盤轉速，比如一個磁盤7200轉，表示每分鐘能轉7200次，也就是說1秒鐘能轉120次，旋轉延遲就是1/120/2= 4.17ms；傳輸時間指的是從磁盤讀出或將資料寫入磁盤的時間，一般在零點幾毫秒，相對于前兩個時間可以忽略不計，那么訪問一次磁盤的時間，即一次磁盤IO的時間約等于5+4.17= 9ms左右，但一臺500 -MIPS的機器每秒可以執行5億條指令，因為指令依靠的是電的性質，換句話說執行一次IO的時間可以執行約450萬條指令，資料庫動輒十萬百萬乃至千萬級資料，每次9毫秒的時間，顯然是個災難，
? 考慮到磁盤IO是非常高昂的操作，計算機作業系統做了一些優化，當一次IO時，不光把當前磁盤地址的資料，還把相鄰的資料也都讀取到記憶體緩沖區內，因為由區域預讀性原理可知，當計算機訪問一個地址的資料的時候，與其相鄰的資料也會很快被訪問到，每一次IO讀取的資料稱之為一頁(page)，具體一頁有多大資料跟作業系統有關，一般為4k或8k，也就是讀取一頁內的資料時候，實際上才發生了一次IO，這個理論對于索引的資料結構設計非常有幫助，
4、索引的資料結構
(1)、樹
? 樹狀圖是一種資料結構，它是由n（n>=1）個有限結點組成一個具有層次關系的集合，把它叫做“樹”是因為它看起來像一棵倒掛的樹，也就是說它是根朝上，而葉朝下的，
? 它具有以下的特點：每個節點有零個或多個子節點；沒有父節點的節點稱為根節點；每一個非根節點有且只有一個父節點；除了根節點外，每個子節點可以分為多個不相交的子樹，
(2)、B樹
? 平衡樹 balance tree - B樹
(3)、B+樹
? B+樹是通過二叉查找樹，再由平衡二叉樹，B樹演化而來， 是為了更好的處理范圍問題在b樹的基礎上有所優化，mysql 中innodb存盤引擎的所有的索引樹都是b+樹
5、聚集索引與輔助索引
? 在資料庫中，B+樹的高度一般都在2~4層，這也就是說查找某一個鍵值的行記錄時最多只需要2到4次IO，當前一般的機械硬碟每秒至少可以做100次IO，2~4次的IO意味著查詢時間只需要0.02~0.04秒，
? 資料庫中的B+樹索引可以分為聚集索引（clustered index）和輔助索引（secondary index）
（1）、聚集索引與輔助索引的相同點：
? 聚集索引與輔助索引相同的是：不管是聚集索引還是輔助索引，其內部都是B+樹的形式，即高度是平衡的，葉子結點存放著所有的資料，
（2）、聚集索引與輔助索引的不相同點：
? 聚集索引與輔助索引不同的是：葉子結點存放的是否是一整行的資訊，
<1>、 聚集索引/聚簇索引：葉子節點會存盤整行資料 ----- innodb 的主鍵
# InnoDB存盤引擎表是索引組織表，即表中資料按照主鍵順序存放，
而聚集索引（clustered index）就是按照每張表的主鍵構造一棵B+樹，同時葉子結點存放的即為整張表的行記錄資料，也將聚集索引的葉子結點稱為資料頁，
聚集索引的這個特性決定了索引組織表中資料也是索引的一部分，同B+樹資料結構一樣，每個資料頁都通過一個雙向鏈表來進行鏈接，
# 如果未定義主鍵，MySQL取第一個唯一索引（unique）而且只含非空列（NOT NULL）作為主鍵，InnoDB使用它作為聚簇索引，
# 如果沒有這樣的列，InnoDB就自己產生一個這樣的ID值，它有六個位元組，而且是隱藏的，使其作為聚簇索引，
# 由于實際的資料頁只能按照一棵B+樹進行排序，因此每張表只能擁有一個聚集索引，
在多數情況下，查詢優化器傾向于采用聚集索引，因為聚集索引能夠在B+樹索引的葉子節點上直接找到資料，
此外由于定義了資料的邏輯順序，聚集索引能夠特別快地訪問針對范圍值得查詢，
<2>、 輔助索引/非聚集索引：除了主鍵之外的普通索引都是輔助索引，一個索引沒辦法查到整行資料，需要回聚集索引再查一次(回表)
表中除了聚集索引外其他索引都是輔助索引（Secondary Index，也稱為非聚集索引），與聚集索引的區別是：輔助索引的葉子節點不包含行記錄的全部資料，
葉子節點除了包含鍵值以外，每個葉子節點中的索引行中還包含一個書簽（bookmark），該書簽用來告訴InnoDB存盤引擎去哪里可以找到與索引相對應的行資料，
由于InnoDB存盤引擎是索引組織表，因此InnoDB存盤引擎的輔助索引的書簽就是相應行資料的聚集索引鍵，
輔助索引的存在并不影響資料在聚集索引中的組織，因此每張表上可以有多個輔助索引，但只能有一個聚集索引，當通過輔助索引來尋找資料時，InnoDB存盤引擎會遍歷輔助索引并通過葉子級別的指標獲得只想主鍵索引的主鍵，然后再通過主鍵索引來找到一個完整的行記錄，
<3>、聚焦索引和非聚焦索引的區別
# 聚集索引
1.紀錄的索引順序與物理順序相同
因此更適合between and和order by操作
2.葉子結點直接對應資料
從中間級的索引頁的索引行直接對應資料頁
3.每張表只能創建一個聚集索引
# 非聚集索引
1.索引順序和物理順序無關
2.葉子結點不直接指向資料頁
3.每張表可以有多個非聚集索引，需要更多磁盤和內容
多個索引會影響insert和update的速度
6、MySQL索引管理
（1）、索引功能
1. 索引的功能就是加速查找
2. mysql中的primary key，unique，聯合唯一也都是索引，這些索引除了加速查找以外，還有約束的功能
（2）、MySQL常用的索引
普通索引 INDEX：加速查找
唯一索引：
-主鍵索引 PRIMARY KEY：加速查找+約束（不為空、不能重復）
-唯一索引 UNIQUE:加速查找+約束（不能重復）
聯合索引：
-PRIMARY KEY(id,name):聯合主鍵索引
-UNIQUE(id,name):聯合唯一索引
-INDEX(id,name):聯合普通索引
（3）、各個索引的應用場景
# 舉個例子來說，比如你在為某商場做一個會員卡的系統，這個系統有一個會員表，有下列欄位：
會員編號 INT
會員姓名 VARCHAR(10)
會員身份證號碼 VARCHAR(18)
會員電話 VARCHAR(11)
會員住址 VARCHAR(50)
會員備注資訊 TEXT
那么這個 會員編號，作為主鍵，使用 PRIMARY
會員姓名 如果要建索引的話，那么就是普通的 INDEX
會員身份證號碼 如果要建索引的話，那么可以選擇 UNIQUE （唯一的，不允許重復）
# 除此之外還有全文索引，即 FULLTEXT
會員備注資訊 ， 如果需要建索引的話，可以選擇全文搜索，
用于搜索很長一篇文章的時候，效果最好，
用在比較短的文本，如果就一兩行字的，普通的 INDEX 也可以，
但其實對于全文搜索，我們并不會使用MySQL自帶的該索引，而是會選擇第三方軟體如Sphinx，專門來做全文搜索，
# 其他的如空間索引SPATIAL，了解即可，幾乎不用
（4）、索引的兩大型別 hash 與 btree
# 我們可以在創建上述索引的時候，為其指定索引型別，分兩類
hash 型別的索引：查詢單條快，范圍查詢慢
btree 型別的索引：b+樹，層數越多，資料量指數級增長（我們就用它，因為innodb默認支持它）
#不 同的存盤引擎支持的索引型別也不一樣
InnoDB 支持事務，支持行級別鎖定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事務，支持表級別鎖定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事務，支持表級別鎖定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事務，支持行級別鎖定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事務，支持表級別鎖定，不支持 B-tree、Hash、Full-text 等索引；
（5）、操作索引： 創建和洗掉
<1> 創建：create index 索引名 on 表名(欄位名);
create index id on s1(id);
alter table s1 add index ix_sex(sex);
<2> 洗掉： drop index 索引名 on 表名;
drop index id on 表名;
7、測驗索引
（1）、準備資料
# 1. 準備表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);
# 2. 創建存盤程序，實作批量插入記錄
delimiter $$ #宣告存盤程序的結束符號為$$
create procedure auto_insert1()
BEGIN
declare i int default 1;
while(i<30000000)do
insert into s1 values(i,'cai','male',concat('cai',i,'@yong'));
set i=i+1;
end while;
END$$ #$$結束
delimiter ; #重新宣告分號為結束符號
# 3. 查看存盤程序
show create procedure auto_insert1\G
# 4. 呼叫存盤程序
call auto_insert1();
（2）、 在沒有索引的前提下測驗查詢速度
# 無索引：mysql根本就不知道到底是否存在id等于333333333的記錄，只能把資料表從頭到尾掃描一遍，此時有多少個磁盤塊就需要進行多少IO操作，所以查詢速度很慢
mysql> select * from s1 where id=333333333;
Empty set (0.33 sec)
（3）、 在表中已經存在大量資料的前提下，為某個欄位段建立索引，建立速度會很慢
（4）、 在索引建立完畢后，以該欄位為查詢條件時，查詢速度提升明顯
注意：
- mysql先去索引表里根據b+樹的搜索原理很快搜索到id等于333333333的記錄不存在，IO大大降低，因而速度明顯提升
- 可以去mysql的data目錄下找到該表，可以看到占用的硬碟空間多大
- 需要注意，如下圖
（5）、總結
# 1.一定是為搜索條件的欄位創建索引，比如select * from s1 where id = 333;就需要為id加上索引
# 2.在表中已經有大量資料的情況下，建索引會很慢，且占用硬碟空間，建完后查詢速度加快
比如create index idx on s1(id);會掃描表中所有的資料，然后以id為資料項，創建索引結構，存放于硬碟的表中，
建完以后，再查詢就會很快了，
#3. 需要注意的是：innodb表的索引會存放于s1.ibd檔案中，而myisam表的索引則會有單獨的索引檔案table1.MYI
MySAM索引檔案和資料檔案是分離的，索引檔案僅保存資料記錄的地址，而在innodb中，表資料檔案本身就是按照B+Tree（BTree即Balance True）組織的一個索引結構，這棵樹的葉節點data域保存了完整的資料記錄，這個索引的key是資料表的主鍵，因此innodb表資料檔案本身就是主索引，
因為inndob的資料檔案要按照主鍵聚集，所以innodb要求表必須要有主鍵（Myisam可以沒有），如果沒有顯式定義，則mysql系統會自動選擇一個可以唯一標識資料記錄的列作為主鍵，如果不存在這種列，則mysql會自動為innodb表生成一個隱含欄位作為主鍵，這欄位的長度為6個位元組，型別為長整型.
8、正確使用索引
(1).只有對創建了索引的列進行條件篩選的時候效率才會高
(2).索引對應的列做條件不能參與運算、不能使用函式
(3).當某一列的區分度非常小(重復率高)，不適合創建索引
(4).當范圍作為條件的時候，查詢結果的范圍越大越慢，越小越快
(5).like關鍵字：如果使用%/ 開頭都無法命中索引
(6).多個條件：如果只有一部分創建了索引，條件用and相連，那么可以提高查詢效率，（如果用or相連，不能提高查詢效率）
and
select count(*) from s1 where id=1000000 and email = 'eva1000000@oldboy'; # 查詢速度加快
or
select count(*) from s1 where id=1000000 or email = 'eva1000000@oldboy';
(7).聯合索引：聯合索引是指對表上的多個列合起來做一個索引，聯合索引的創建方法與單個索引的創建方法一樣，不同之處僅在于有多個索引列，
creat index ind_mix on s1(id,name,email);
select count(*) from s1 where id=1000000 and email = 'eva1000000@oldboy'; # 快
select count(*) from s1 where id=1000000 or email = 'eva1000000@oldboy'; # 慢 條件不能用or
select count(*) from s1 where id=1000000; # 快
select count(*) from s1 where email = 'eva1000000@oldboy'; # 慢 要服從最左前綴原則
select count(*) from s1 where id>1000000 and email = 'eva1000000@oldboy'; # 慢 從使用了范圍的條件開始之后的索引都失效
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/202459.html
標籤：其他
上一篇：redis五種資料型別的應用
下一篇：redis五種資料型別的應用