引子

什麼是索引

為什麼需要索引

使用索引

索引優化原理

正確使用索引

什麼是索引

在關係資料庫中，索引是一種單獨的、物理層面的對資料庫表中一列或多列的值進行排序的一種存儲結構; 也稱之為key

有以下幾種:

unique key

primary key

index key

索引的作用相當於圖書的目錄，可以根據目錄中的頁碼快速找到所需的內容。

為什麼需要索引

思考:一個項目正常運行後,對資料庫的操作中,哪些操作是最頻繁的?

對資料庫的寫操作(增加 刪除 修改)頻繁嗎?

對資料庫的讀操作(查詢)頻繁嗎?

相比較下,對數據的讀操作會更加頻繁,比例在10:1左右,也就是說對資料庫的查詢操作是非常頻繁的

隨著時間的推移,表中的記錄會越來越多,此時如果查詢速度太慢的話對用戶體驗是非常不利的

索引是提升查詢效率最有效的手段!

簡單的說索引的就是用幫我們加快查詢速度的

需要註意的是：在資料庫中插入數據會引發索引的重建

小白的誤區

既然索引如此神奇,那以後只要速度慢了就加索引,

這種想法是非常low的,

索引是不是越多越好,並且有了索引後還要考慮索引是否命中

加上索引後對數據的寫操作速度會降低

索引的實現原理

如何能實現加快查詢的效果呢?

來看一個例子:

第一版的新華字典共800頁,那時沒有檢字表,每個字的詳細信息,隨機的羅列在書中,一同學買回來查了一次,在也沒用過,因為沒有任何的數據結構,查字只能一頁一頁往後翻,反了兩小時沒翻著,只能放棄了!

後來出版社發現了這個問題,他們將書中所有字按照拼音音節順序進行了排序,拼音首字母為a的排在最前,首字母為z的排在最後:

如此一來再不再需要一頁一頁的去查字了,而是先查看索引,找出字的拼音首字母到索引中進行對照,例如:找搭字其拼音首字母為d,所以直接找到D對應的索引目錄,很快就能定位到要找的搭字在79頁,查詢速度得到數量級的提升!

需要註意的是,原來內容為800頁現在因為多了索引數據,整體頁數必然增加了

資料庫中的索引,實現思路與字典是一致的,需要一個獨立的存儲結構,專門存儲索引數據

本質上索引是通過不斷的縮小查詢範圍來提高查詢效率

磁碟IO問題(瞭解)

資料庫的數據最終存儲到了硬碟上

機械硬碟由於設計原理,導致查找數據時需要有一個尋道時間與平均延遲時間,常規硬碟尋道為5ms,平均延遲按照每分鐘7200轉來計算,7200/60 = 120 ; 1000/120/2 = 4ms 總共為9ms,那麼9毫秒對於cpu而言已經非常非常的長了,足夠做很多運算操作,目前最新的處理器每秒能處理數萬億次運算,拿一個非常垃圾的處理器來舉例子,假設處理器每秒處理5億次計算,每毫秒是50萬次運算,9ms可以進行450萬次運算,資料庫中成千上萬的數據,每條數據9ms顯然慢到不行!

操作系統預讀取(瞭解)

考慮到磁碟IO是非常高昂的操作，電腦操作系統做了一些優化，當一次IO時，不光把當前磁碟地址的數據，而是把相鄰的數據也都讀取到記憶體緩衝區內，因為局部預讀性原理告訴我們，當電腦訪問一個地址的數據的時候，與其相鄰的數據也會很快被訪問到。每一次IO讀取的數據我們稱之為一頁(page)。具體一頁有多大數據跟操作系統有關，一般為4k或8k，也就是我們讀取一頁內的數據時候，實際上才發生了一次IO，這個理論對於索引的數據結構設計非常有幫助。

索引數據結構剖析

在字典的例子中我們知道了,索引是獨立於真實數據的一個存儲結構,這個結構到底是什麼樣的?

索引最終的目的是要儘可能降低io次數,減少查找的次數,以最少的io找到需要的數據,此時B+樹閃亮登場

光有數據結構還不行,還需要有對應的演算法做支持,就是二分查找法

有了B+數據結構後查找數據的方式就不再是逐個的對比了,而是通過二分查找法來查找(流程演示)

另外,其實大多數文件系統都是使用B+是來完成的!

應該儘可能的將數據量小的欄位作為索引

通過分析可以發現在上面的樹中,查找一個任何一個數據都是3次IO操作, 但是這個3次並不是固定的,它取決於樹結構的高度,目前是三層,如果要存儲新的數據比99還大的數據時,發現葉子節點已經不夠了必須在上面加一個子節點,由於樹根只能有一個所以,整個數的高度會增加,一旦高度增加則 查找是IO次數也會增加,所以:

應該儘可能的將數據量小的欄位作為索引,這樣一個葉子節點能存儲的數據就更多,從而降低樹的高度;

例如:name 和id,應當將id設置為索引而不是name

最左匹配原則*

當b+樹的數據項是複合的數據結構，比如(name,age,sex)的時候(多欄位聯合索引)，b+樹會按照從左到右的順序來建立搜索樹，比如當(張三,20,F)這樣的數據來檢索的時候，b+樹會優先比較name來確定下一步的所搜方向，如果name相同再依次比較age和sex，最後得到檢索的數據；但當(20,F)這樣的沒有name的數據來的時候，b+樹就不知道下一步該查哪個節點，因為建立搜索樹的時候name就是第一個比較因數，必須要先根據name來搜索才能知道下一步去哪裡查詢。比如當(張三,F)這樣的數據來檢索時，b+樹可以用name來指定搜索方向，但下一個欄位age的缺失，所以只能把名字等於張三的數據都找到，然後再匹配性別是F的數據了， 這個是非常重要的性質，即索引的最左匹配特性。

聚集索引*

mysql官方文檔原文: 插入瞭解 或摺疊

MySQL為表把它的數據詞典信息以.frm文件的形式存在資料庫目錄里，這對所有MySQL存儲引擎都是真的。但 是每個InnoDB表在表空間內的InnoDB內部數據詞典里有它自己的條目。當MySQL移除表或資料庫，它不得不 刪除.frm文件和InnoDB數據詞典內的相應條目。這就是為什麼你不能在資料庫之間簡單地移動.frm文件來移 動InnoDB表。

每個InnoDB表有專門索引，被稱為clustered index，對行的數據被存於其中。如果你對你的表定義一 個PRIMARY KEY， 主鍵的索引是集束索引。

如果你沒有為表定義PRIMARY KEY，MySQL拾取第一個僅有NOT NULL列的UNIQUE索引作為主鍵，並 且InnoDB把它當作集束索引來用。如果表中沒有這樣一個索引，InnoDB內部產生一個集束索引，其中 用InnoDB在這樣一個表內指定給行的行ID來排序行。行ID是一個6位元組的域，它在新行被插入的時候簡單地增加。因此被行ID排序的行是物理地按照插入順序排的。

通過集束索引訪問一個行是較快的，因為行數據是在索引搜索引導的同一頁面。如果表是巨大的，當對比於傳 統解決方案，集束索引構架經常節約磁碟I/O。(在許多資料庫，數據傳統地被存在與索引記錄不同的頁)。

在InnoDB中，非集束索引里的記錄(也稱為第二索引)包含對應行的主鍵值。InnoDB用這個 主鍵值來從集束索 引中搜索行。註意，如果主鍵是長的，第二索引使用更多空間。

簡單總結:

聚焦索引的特點:

​ 葉子節點保存的就是完整的一行記錄,如果設置了主鍵,主鍵就作為聚集索引,

​ 如果沒有主鍵,則找第一個NOT NULL 且QUNIQUE的列作為聚集索引,

​ 如果也沒有這樣的列,innoDB會在表內自動產生一個聚集索引,它是自增的

​ 聚集索引中包含了完整的記錄

輔助索引*

除了聚集索引之外的索引都稱之為輔助索引或第二索引,包括 foreign key 與 unique

輔助索引的特點:

​ 其葉子節點保存的是索引數據與所在行的主鍵值,InnoDB用這個 主鍵值來從聚集索引中搜查找數據

覆蓋索引

​ 覆蓋索引指的是需要的數據僅在輔助索引中就能找到:

#假設stu表的name欄位是一個輔助索引
select name from stu where name = "jack";

​ 這樣的話則不需要在查找聚集索引數據已經找到

回表

​ 如果要查找的數據在輔助索引中不存在,則需要回到聚集索引中查找,這種現象稱之為回表

# name欄位是一個輔助索引 而sex欄位不是索引 
select sex from stu where name = "jack";

​ 需要從輔助索引中獲取主鍵的值,在拿著主鍵值到聚集索引中找到sex的值

查詢速度對比:

聚集索引 > 覆蓋索引 > 非覆蓋索引

正確使用索引

案例:

首先準備一張表數據量在百萬級別

create table usr(id int,name char(10),gender char(3),email char(30));
#準備數據
delimiter //
create procedure addData(in num int)
begin 
declare i int default 0;
while  i < num do
    insert into usr values(i,"jack","m",concat("xxxx",i,"@qq.com"));    
set i  = i + 1;
end while;
end//
delimiter ;

#執行查詢語句 觀察查詢時間
select count(*) from usr where id = 1;
#1 row in set (3.85 sec)
#時間在秒級別 比較慢


1.
#添加主鍵
alter table usr add primary key(id);
#再次查詢
select count(*) from usr where id = 1;
#1 row in set (0.00 sec)
#基本在毫秒級就能完成 提升非常大

2.
#當條件為範圍查詢時
select count(*) from usr where id > 1;
#速度依然很慢 對於這種查詢沒有辦法可以優化因為需要的數據就是那麼多
#縮小查詢範圍 速度立馬就快了
select count(*) from usr where id > 1 and id < 10;




#當查詢語句中匹配欄位沒有索引時 效率測試
select count(*) from usr where name = "jack";
#1 row in set (2.85 sec)
# 速度慢


3.
# 為name欄位添加索引
create index name_index on usr(name);
# 再次查詢
select count(*) from usr where name = "jack";
#1 row in set (3.89 sec)
# 速度反而降低了 為什麼?
#由於name欄位的區分度非常低 完全無法區分 ,因為值都相同 這樣一來B+樹會沒有任何的子節點,像一根竹竿每一都匹配相當於,有幾條記錄就有幾次io ,所有要註意 區分度低的欄位不應該建立索引,不能加速查詢反而降低寫入效率,
#同理 性別欄位也不應該建立索引,email欄位更加適合建立索引

# 修改查詢語句為
select count(*) from usr where name = "aaaaaaaaa";
#1 row in set (0.00 sec) 速度非常快因為在 樹根位置就已經判斷出樹中沒有這個數據 全部跳過了

# 模糊匹配時
select count(*) from usr where name like "xxx"; #快
select count(*) from usr where name like "xxx%"; #快
select count(*) from usr where name like "%xxx"; #慢
#由於索引是比較大小 會從左邊開始匹配 很明顯所有字元都能匹配% 所以全都匹配了一遍



4.索引欄位不能參加運算
select count(*) from usr where id * 12 = 120;
#速度非常慢原因在於 mysql需要取出所有列的id 進行運算之後才能判斷是否成立
#解決方案
select count(*) from usr where id = 120/12;
#速度提升了 因為在讀取數據時 條件就一定固定了 相當於
select count(*) from usr where id = 10;
#速度自然快了

5.有多個匹配條件時 索引的執行順序  and 和 or
#先看and
#先刪除所有的索引
alter table usr  drop primary key;
drop index name_index on usr;

#測試
select count(*) from usr where name = "jack" and gender = "m" and id = 1 and email = "[email protected]";
#1 row in set (1.34 sec) 時間在秒級 

#為name欄位添加索引
create index name_index on usr(name);
#測試
select count(*) from usr where name = "jack" and gender = "m" and id = 1 and email = "[email protected]";
#1 row in set (17.82 sec) 反而時間更長了

#為gender欄位添加索引
create index gender_index on usr(gender);
#測試
select count(*) from usr where name = "jack" and gender = "m" and id = 1 and email = "[email protected]";
#1 row in set (16.83 sec) gender欄位任然不具備區分度 

#為id加上索引
alter table usr add primary key(id);
#測試
select count(*) from usr where name = "jack" and gender = "m" and id = 1 and email = "[email protected]";
#1 row in set (0.00 sec) id欄位區分度高 速度提升
#雖然三個欄位都有索引 mysql並不是從左往右傻傻的去查 而是找出一個區分度高的欄位優先匹配

#改為範圍匹配
select count(*) from usr where name = "jack" and gender = "m" and id > 1 and email = "[email protected]";
#速度變慢了

#刪除id索引 為email建立索引
alter table usr drop primary key;
create index email_index on usr(email);
#測試
select count(*) from usr where name = "jack" and gender = "m" and id = 1 and email = "[email protected]";
#1 row in set (0.00 sec) 速度非常快

#對於or條件 都是從左往右匹配 
select count(*) from usr where name = "jackxxxx" or email = "[email protected]";

#註意 必須or兩邊都有索引才會使用索引 

and 語句中只要有一個存在索引就能提高速度  


6.多欄位聯合索引
為什麼需要聯合索引
案例:
select count(*) from usr where name = "jack" and gender = "m" and id  > 3 and email = "[email protected]";
假設所有欄位都是區分度非常高的欄位,那麼除了id為誰添加索引都能夠提升速度,但是如果sql語句中沒有出現索引欄位,那就無法加速查詢,最簡單的辦法是為每個欄位都加上索引,但是索引也是一種數據,會占用記憶體空間,並且降低寫入效率
此處就可以使用聯合索引,

聯合索引最重要的是順序 按照最左匹配原則 應該將區分度高的放在左邊 區分度低的放到右邊
#刪除其他索引
drop index name_index on usr;
drop index email_index on usr;
#聯合索引
create index mul_index on usr(email,name,gender,id);
# 查詢測試
select count(*) from usr where name = "xx" and id = 1 and email = "xx";
只要語句中出現了最左側的索引(email) 無論在前在後都能提升效率 

drop index mul_index on usr;