编程知识 cdmana.com

深入理解MySQL系列之索引

#### 索引###### 查詢一條資料的過程先看下InnoDB的邏輯儲存結構:1. 表空間:可以看做是InnoDB儲存引擎邏輯結構的最高層,所有的資料都存放在表空間中。預設有個共享表空間ibdata1。如果啟用innodb_file_per_table引數,需要注意每張表的表空間記憶體放的只是資料、索引和插入緩衝Bitmap頁,其他類的資料,如回滾資訊、插入緩衝索引頁、系統事務資訊、二次寫緩衝等還是存放在原來共享表空間中。2. 段:表空間是由各個段組成,常見的段有資料段、索引段、回滾段等。資料段即為B+樹葉子節點(Leaf node segment),索引段即為B+樹非葉子節點(Non-leaf node segment)區3. 區:是由連續頁組成的空間,在任何情況下每個區大小都為1MB。預設情況下,儲存引擎頁的大小為16KB,即一個區中一共有連續64個連續的頁。而為保證頁的連續性,InnoDB儲存引擎一次從磁碟申請4-5個區。4. 頁:頁(也可以稱塊),是InnoDB磁碟管理的最小單位。預設每個頁大小16KB。1.2x版本後也可以通過引數innodb_page_size設定為4k、8k、16k![](https://img2020.cnblogs.com/blog/2211828/202012/2211828-20201222171110725-1867744008.png)如查一條資料:select * from user where id=5;這裡id是主鍵,我們通過這棵B+樹來查詢,首先會去找到根頁,每張表的根頁位置在表空間檔案中是固定的;找到根頁後通過二分查詢法,定位到id=5的資料應該在指標P5指向的頁中,那麼進一步去page number=5的頁中查詢,同樣通過二分查詢法即可找到id=5的記錄:![](https://img2020.cnblogs.com/blog/2211828/202012/2211828-20201224131808085-2136935822.png)###### 計算一棵B+樹可以存放多少行資料也可以通過命令檢視InnoDB每頁預設16KB:> show variables like 'innodb_page_size';先計算非葉子節點, 假設主鍵ID為bigint型別,長度為8位元組,而指標大小在InnoDB原始碼中設定為6位元組,這樣一共14位元組而一個頁中能存放多少這樣的單元,其實就代表有多少指標,即16384/14=1170。那麼可以算出一棵高度為2的B+樹,能存放1170*16=18720條這樣的資料記錄。根據同樣的原理我們可以算出一個高度為3的B+樹可以存放:1170*1170*16=21902400條這樣的記錄。所以在InnoDB中B+樹高度一般為1-3層,它就能滿足千萬級的資料儲存。###### 索引一些概念1. 聚簇索引(clustered index): 就是將索引和資料放到一起,找到索引也就找到了資料;如下圖葉子節點存放一行所有資料。![](https://img2020.cnblogs.com/blog/2211828/202012/2211828-20201224140117685-1934751501.png)2. 輔助索引(Secondary Index或非聚簇索引): 就是將資料和索引分開,查詢時需要先查詢到索引,然後通過索引回表找到相應的資料。回表:先通過資料庫索引掃描出資料所在的行,再通過行主鍵id取出索引中未提供的資料,即基於非主鍵索引的查詢需要多掃描一棵索引樹。如下圖,輔助索引查詢後,會再回表到聚簇索引,最後找到資料。![](https://img2020.cnblogs.com/blog/2211828/202012/2211828-20201224140048888-833144751.png)> InnoDB有且只有一個聚簇索引,而MyISAM中都是非聚簇索引。3. 聯合索引:指對錶上多個列進行索引。> 聯合索引的最左字首匹配原則: 對多個欄位同時建立的組合索引(有順序,ABC,ACB是完全不同的兩種聯合索引) 以聯合索引(a,b,c)為例,建立這樣的索引相當於建立了索引a、ab、abc三個索引。另外組合索引實際還是一個索引,並非真的建立了多個索引,只是產生的效果等價於產生多個索引。4. 覆蓋索引: 即從輔助索引中就可以得到查詢的記錄,而不需要查詢聚簇索引中的記錄。使用覆蓋好處:- 輔助索引不包含整行記錄的所有資訊,故其大小要遠小於聚簇索引,減少大量IO操作。- 對某些統計(如count(id))並不會通過查詢聚簇索引來進行統計,減少IO操作5. 唯一索引:以唯一列生成的索引,該列不允許有重複值,但允許有空值(NULL)6. 索引下推:MySQL 5.6引入了索引下推優化,可以在索引遍歷過程中,對索引中包含的欄位先做判斷,過濾掉不符合條件的記錄,減少回表字數。###### 為什麼選B+樹,而不是B樹B樹不管葉子節點還是非葉子節點,都會儲存資料,這樣導致在非葉子節點中能儲存的指標數量變少指標少的情況下要儲存大量資料,只能增加樹的高度,導致IO操作變多,查詢效能變低;###### 為什麼InnoDB只有一個聚簇索引,而不將所有索引都使用聚簇索引?因為聚簇索引是將索引和資料都存放在葉子節點中,如果所有的索引都用聚簇索引,則每一個索引都將儲存一份資料,會造成資料的冗餘,在資料量很大的情況下,這種資料冗餘是很消耗資源的。###### 什麼情況下會發生明明建立了索引,但是執行的時候並沒有通過索引呢?查詢優化器。一條SQL語句的查詢,可以有不同的執行方案,至於最終選擇哪種方案,需要通過優化器進行選擇,選擇執行成本最低的方案。優化過程大致如下:- 1、根據搜尋條件,找出所有可能使用的索引- 2、計算全表掃描的代價- 3、計算使用不同索引執行查詢的代價- 4、對比各種執行方案的代價,找出成本最低的那一個 。###### 索引的優缺點索引的優點如下:- 1、唯一索引可以保證每一行資料的唯一性 - 2、提高查詢速度 - 3、加速表與表的連線 - 4、顯著的減少查詢中分組和排序的時間- 5、通過使用索引,可以在查詢的過程中,使用優化隱藏器,提高系統的效能。索引的缺點如下:- 建立索引時,需要對錶加鎖,在鎖表的同時,可能會影響到其他的資料操作 - 雖然索引大大提高了查詢速度,同時卻會降低更新表的速度,如對錶進行 INSERT、UPDATE 和 DELETE。因為更新表時,MySQL 不僅要儲存資料,還要儲存索引檔案。- 建立索引會佔用磁碟空間的索引檔案。一般情況這個問題不算嚴重,但如果你在一個大表上建立了多種組合索引,且伴隨大量資料量插入,索引檔案大小也會快速膨脹。- 如果某個資料列包含許多重複的內容,為它建立索引就沒有太大的實際效果。- 對於非常小的表,大部分情況下簡單的全表掃描更高效。###### 使用索引時的注意事項原則:不應該> 1、索引不是越多越好。索引太多,維護索引需要時間跟空間> 2、 頻繁更新的資料,不宜建索引。> 3、資料量小的表沒必要建立索引。 應該> 1、重複率小的列建議生成索引。因為重複資料少,索引樹查詢更有效率,等價基數越大越好。> 2、資料具有唯一性,建議生成唯一性索引。在資料庫的層面,保證資料正確性> 3、頻繁group by、order by的列建議生成索引。可以大幅提高分組和排序效率> 4、經常用於查詢條件的欄位建議生成索引。通過索引查詢,速度更快索引失效的場景> 1、模糊搜尋:左模糊或全模糊都會導致索引失效,比如'%a'和'%a%'。但是右模糊是可以利用索引的,比如'a%' > 2、隱式型別轉換:比如select * from t where name = xxx , name是字串型別,但是沒有加引號,所以是由MySQL隱式轉換的,所以會讓索引失效> 3、當語句中帶有or的時候:比如select * from t where name=‘sw’ or age=14> 4、不符合聯合索引的最左字首匹配:(A,B,C)的聯合索引,你只where了C或B或只有B,C其他注意事項:> 1. 索引不會包含有 null 值的列,只要列中包含有 null值都將不會被包含在索引中。> 2. 使用短索引。短索引不僅可以提高查詢速度而且可以節省磁碟空間和 I/O 操作> 3. 索引列排序。查詢只使用一個索引,因此如果 where 子句中已經使用了索引的話,那麼 order by 中的列是不會使用索引的。因此資料庫預設排序可以符合要求的情況下不要使用排序操作;儘量不要包含多個列的排序,如果需要最好給這些列建立複合索引。> 4. 不要在列上進行運算,這將導致索引失效而進行全表掃描> 5. 不使用 not in 和 <> 操作,這不屬於支援的範圍查詢條件,不會使用索引。《MySQL技術內幕》https://mp.weixin.qq.com/s/6j64s9W6ogs5Y8BbhhkgnAhttps://mp.weixin.qq.com/s/KB73550tKpNccW-WKxT7-Ahttps://mp.weixin.qq.com/s/ovMx9Dv9NCFxSs

版权声明
本文为[itread01]所创,转载请带上原文链接,感谢
https://www.itread01.com/content/1608815343.html

Scroll to Top