Mysql-高性能索引
索引一種數據結構,其目的是為了更快的查詢數據,在數據量很大的表中,建立良好的索引能夠提升極大的性能。
磁盤io與預讀
因為數據庫存儲數據量大,是不可能存儲在內存中以供查詢的,所以對于數據的查詢必然會跟磁盤打交道,所以只有了解了磁盤io和預讀的基本知識,我們才能真正的理解索引的原理。
磁盤讀取數據靠的是機械運動,每次讀取數據花費的時間可 以分為尋道時間、旋轉延遲、傳輸時間三個部分,尋道時間指的是磁臂移動到指定磁道所需要的時間,主流磁盤一般在5ms以下;旋轉延遲就是我們經常聽說的磁 盤轉速,比如一個磁盤7200轉,表示每分鐘能轉7200次,也就是說1秒鐘能轉120次,旋轉延遲就是1/120/2 = 4.17ms;傳輸時間指的是從磁盤讀出或將數據寫入磁盤的時間,一般在零點幾毫秒,相對于前兩個時間可以忽略不計。那么訪問一次磁盤的時間,即一次磁盤 IO的時間約等于5+4.17 = 9ms左右,聽起來還挺不錯的,但要知道一臺500MIPS的機器每秒可以執行5億條指令,因為指令依靠的是電的性質,換句話說執行一次IO的時間可以執行40萬條指令,數據庫動輒十萬百萬乃至千萬級數 據,每次9毫秒的時間,顯然是個災難。考 慮到磁盤IO是非常高昂的操作,計算機操作系統做了一些優化,當一次IO時,不光把當前磁盤地址的數據,而是把相鄰的數據也都讀取到內存緩沖區內,因為局 部預讀性原理告訴我們,當計算機訪問一個地址的數據的時候,與其相鄰的數據也會很快被訪問到。每一次IO讀取的數據我們稱之為一頁(page)。具體一頁 有多大數據跟操作系統有關,一般為4k或8k,也就是我們讀取一頁內的數據時候,實際上才發生了一次IO。
索引的數據結構
之前說到了磁盤讀取數據的方式,那么我們的索引是如何配合這個方式更快的搜索到數據呢?首先,我們要了解索引的數據結構。我們這里講到的索引B+TREE的索引,因為這個索引我們最常用,實際上索引還有哈希索引,空間數據索引,全文索引。
首先我們來理解B+TREE的數據結構: B+Tree是一種多路搜索樹(并不是二叉的):
B+的特性:
B+TREE索引性能分析:
B+TREE的深度最多是O(log[M/2]N),在路徑上的每個節點需要用O(logM)s時間復雜度來確定是哪個分支(使用二分查找),inset和delete可能需要O(M)的工作量來調整該節點上的所有信息,所以insert和delete的運行時間最壞情況是O(Mlog[M]N),而每次的查詢只需要花費O(logN)。從剛剛的時間復雜度可以看出當在內存中查詢時,Mzuihaode選擇是3或者4,再增大時速度就會增加。但是我們的數據存儲是在磁盤中的,相比讀取一個存儲器所花費的時間,M增大所增加的時間花費不值一提。此時M的值選擇為使得一個內部節點能夠裝入一個磁盤區塊的最大值,所以M取值范圍為[32,256],這樣當一片樹葉上元素是滿的,而且樹葉是滿的那么硬盤上一個區塊就被裝滿了。這樣意味著,一個記錄總可以在很少的磁盤訪問中被找到,因為此時B樹深度只有2或3,而根可以直接加載到內存中,所以整個訪問速度就會很快。
所以我們再來看上圖: 如果要查找數據項30,那么首先會把磁盤塊1由磁盤加載到內存,此時發生一次IO,在內存中用二分查找確 定29在28和65之間,鎖定磁盤塊1的P2指針,內存時間因為非常短(相比磁盤的IO)可以忽略不計,通過磁盤塊1的P2指針的磁盤地址把磁盤塊3由磁 盤加載到內存,發生第二次IO,30在28和35之間,鎖定磁盤塊3的P2指針,通過指針加載磁盤塊8到內存,發生第三次IO,同時內存中做二分查找找到 30,結束查詢,總計三次IO。真實的情況是,3層的b+樹可以表示上百萬的數據,如果上百萬的數據查找只需要三次IO,性能提高將是巨大的,如果沒有索引,每個數據項都要發生一次IO,那么總共需要百萬次的IO,顯然成本非常非常高。
高性能的索引策略
獨立的列
有些查詢不當的使用索引,使得Mysql無法使用已有的索引。比如查詢中列不是獨立的,則Mysql不會使用索引。獨立的列指的是:索引不能是表達式的一部分,更不能是函數的參數,例如:
select app_id from app where app_id + 1 = 5; 復制代碼MySQL無法解析app_id + 1這個表達式,所以無法使用索引。
前綴索引和索引選擇性
有時候索引的字段特別的長,這會讓索引變得又大又慢。這種情況可以通過只取出字段前面幾個字符來做索引,這樣可以節約索引空間,從而提高索引的效率,但是這樣會減少索引的選擇性。索引的選擇性,指的是不重復的索引值(基數)跟數據表的總數的比值。索引選擇性越高查詢的效率就越快,因為這樣索引能幫助Mysql查找時過濾掉更多的行。比如主鍵和唯一索引,這種時候性能最好。所以在選擇索引長度時要同時考慮索引選擇性才能達到性能最優化。
多列索引
大多數對于索引理解不夠的,所以容易犯以下兩個:
聚簇索引
聚簇索引并不是一個單獨的索引形式,而是Mysql在B+TREE索引上的數據存儲形式。InnoDB的聚簇索引實現就是在統一結構里面保存了B+TREE索引和數據行如圖:
聚簇索引有時候對性能很有幫助,但有時候也對性能造成嚴重的問題。下面我們用幾張圖來分辨下非聚簇索引的表和聚簇索引的表的區別: 非聚簇索引表的數據如上圖 非聚簇索引表的主鍵索引圖 聚簇索引的主鍵索引圖從上面幾張圖可以看出,非聚簇索引表的主鍵索引跟普通的索引沒有區別,他直接就是索引里有個指向數據所在指針的形式,但是聚簇索引表的主鍵索引“就是”一張表,所以不需要非聚簇索引表那樣數據的獨立行儲存。我們直接來看兩者的對比圖:
了解了他們的區別我們接著來討論聚簇索引的優點:
下面我們來看看聚簇索引的缺點:
覆蓋索引
覆蓋索引指的是包含了一個查詢所有需要查詢字段的值。覆蓋索引是一個非常有用的工具,能夠極大的提升效率,因為索引的葉子節點已經包含了所有需要的數據,無需再去讀取數據行。其優點如下:
因為覆蓋索引是索引中存儲了列的值,所以覆蓋索引的適用范圍僅適用于B+TREE的時候.
索引掃描來做排序
掃描索引本身很快,因為只需要一條索引記錄移動到下一條索引記錄就行了。但是如果索引不能覆蓋查詢的所有列,那就只能每一條索引記錄都要去查詢一次對應的行。這基本上都是隨機IO,所以按索引順序讀取數據的速度反而要比順序的全表掃描慢,尤其是IO密集型的工作負載時。 只有當索引的列順序和order by的字句順序完全一致,并且所有列的排序方向(正序倒序)都一樣時,Mysql才能使用索引來對結果做排序。當然如果最左前綴在where條件中已經是一個常量了,可以不用滿足最左前綴的order by子句。
索引和鎖
索引可以讓查詢鎖定更少的行。比如之前我們遇到過得for update語句如果用了主鍵的索引,那么就只鎖定一行,而其他的就會鎖表。還有很多情況查詢的時候只鎖定索引查出來的行,這樣的話能減少很多鎖的開銷。還有個InnoDB的細節需要注意: InnoDB在二級索引(輔助索引)上用的是共享鎖(讀鎖),但是訪問主鍵索引就用的是排他鎖(寫鎖)(其實也就是之前我們工作中遇到的那個用主鍵的索引就是行鎖,但是其他索引就是表鎖)。這種情況就沒法使用之前講到的覆蓋索引(二級索引訪問主鍵索引),并且使用 for update 比share in share mode或非鎖定查詢要慢得多。
一些微小的建議
數據庫的優化是一個非常重要的工作,很多時候不能犯教條主義錯誤,最主要的方式還是要通過自己操作來驗證到底該如何優化。很可能一次優化在100M的數據量的時候起作用了,但是1G的時候又會變成慢查詢,這種情況就要去思考如何才能避免再出這樣的問題。數據庫可以存儲一些歷史數據作為記錄,但是大多數情況我們需要的是最近的數據或者是少數的幾個熱數據,這種情況下就需要用高速緩存的方式來完成這方面的工作,而不是一味的只用數據庫。這才設計構思的時候很重要。還有索引不是萬能的,千萬不要亂建索引,有時候還會造成速度變得更低,而且還浪費了空間,再建立索引的時候也要好好思考一下這個索引的必要性和用處。另外優化慢查詢有時候也未必非要加索引,很多時候通過一些語句的構造也能實現優化的目的。以上內容主要來自我之前閱讀的一本書籍《高性能MySQL》,配合一些算法知識再加上我自身的一些經驗,寫在這里只是起到拋磚引玉的作用,數據庫優化的路還很長,坑還很多,希望與大家一起學習,共同進步。
總結
以上是生活随笔為你收集整理的Mysql-高性能索引的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: 15、【 商品管理模块开发】——后台获取
- 下一篇: 在 C/C++ 中使用 TensorFl