MySql索引原理解析-馬龍組PPT課件

MySql索引原理解析（B+tree）,By馬龍-代碼帥，運行快,組員：陳詩華李佳劉偉杰周文兵孔敢,1,.,工作分配,孔敢：數(shù)據(jù)庫索引PPT整體的構(gòu)造和思路陳詩華：展示講解數(shù)據(jù)庫的索引李佳：數(shù)據(jù)庫結(jié)構(gòu)的分類(對應不同的數(shù)據(jù)庫存儲引擎)劉偉潔：物理分類周文兵：邏輯分類、PPT制作,2,.,索引的分類,索引的分類大致可以從邏輯分類，物理分類以及數(shù)據(jù)結(jié)構(gòu)分類這三個方面來闡述：數(shù)據(jù)結(jié)構(gòu)分類：（1）B+樹索引(O(log(n)（底層重點）（2）hash索引（3）FULLTEXT索引（4）R-Tree索引物理分類：（對應于不同的數(shù)據(jù)庫存儲引擎）：（1）聚集索引（clusteredindex）：InnoDB存儲引擎（2）非聚集索引（non-clusteredindex）：MyISAM存儲引擎邏輯分類（重點）:（1）普通索引或者單列索引（2）唯一索引（3）主鍵索引（4）組合索引,3,.,磁盤IO與預讀,磁盤IO：磁盤讀取數(shù)據(jù)靠的是機械運動，每次讀取數(shù)據(jù)花費的時間可以分為尋道時間、旋轉(zhuǎn)延遲、傳輸時間三個部分，尋道時間指的是磁臂移動到指定磁道所需要的時間，主流磁盤一般在5ms以下；旋轉(zhuǎn)延遲就是我們經(jīng)常聽說的磁盤轉(zhuǎn)速，比如一個磁盤7200轉(zhuǎn)，表示每分鐘能轉(zhuǎn)7200次，也就是說1秒鐘能轉(zhuǎn)120次，旋轉(zhuǎn)延遲就是1/120/2=4.17ms；傳輸時間指的是從磁盤讀出或?qū)?shù)據(jù)寫入磁盤的時間，一般在零點幾毫秒，相對于前兩個時間可以忽略不計。那么訪問一次磁盤的時間，即一次磁盤IO的時間約等于5+4.17=9ms左右，聽起來還挺不錯的，但要知道一臺500-MIPS的機器每秒可以執(zhí)行5億條指令，因為指令依靠的是電的性質(zhì)，換句話說執(zhí)行一次IO的時間可以執(zhí)行40萬條指令，數(shù)據(jù)庫動輒十萬百萬乃至千萬級數(shù)據(jù)，每次9毫秒的時間，顯然是個災難。,4,.,磁盤IO與預讀,預讀：考慮到磁盤IO是非常高昂的操作，計算機操作系統(tǒng)做了一些優(yōu)化，當一次IO時，不光把當前磁盤地址的數(shù)據(jù)，而且把相鄰的數(shù)據(jù)也都讀取到內(nèi)存緩沖區(qū)內(nèi)，因為局當計算機訪問一個地址的數(shù)據(jù)的時候，與其相鄰的數(shù)據(jù)也會很快被訪問到。每一次IO讀取的數(shù)據(jù)我們稱之為一頁(page)。部預讀性原理告訴我們，具體一頁有多大數(shù)據(jù)跟操作系統(tǒng)有關，一般為4k或8k，也就是我們讀取一頁內(nèi)的數(shù)據(jù)時候，實際上才發(fā)生了一次IO，這個理論對于索引的數(shù)據(jù)結(jié)構(gòu)設計非常有幫助。,5,.,數(shù)據(jù)庫索引，是數(shù)據(jù)庫管理系統(tǒng)中一個排序的數(shù)據(jù)結(jié)構(gòu)，以協(xié)助快速查詢、更新數(shù)據(jù)庫表中數(shù)據(jù)。索引的實現(xiàn)通常使用B-tree及其變種B+tree。在數(shù)據(jù)之外，數(shù)據(jù)庫系統(tǒng)還維護著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用(指向)數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實現(xiàn)高級查找算法。這種數(shù)據(jù)結(jié)構(gòu)，就是索引。MySQL官方對索引的定義為：索引（Index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。提取句子主干，就可以得到索引的本質(zhì)：索引是數(shù)據(jù)結(jié)構(gòu)。,6,.,什么是索引,索引是一種允許直接訪問數(shù)據(jù)表中某一數(shù)據(jù)行的樹型結(jié)構(gòu)，為了提高查詢效率而引入，是獨立于表的對象，可以存放在與表不同的表空間（TABLESPACE）中。索引記錄中存有索引關鍵字和指向表中數(shù)據(jù)的指針（地址）。對索引進行的I/O操作比對表進行操作要少很多。更通俗的說，數(shù)據(jù)庫索引好比是一本書前面的目錄，能加快數(shù)據(jù)庫的查詢速度。,B+tree索引的結(jié)構(gòu),7,.,索引的本質(zhì),目前大部分數(shù)據(jù)庫系統(tǒng)及文件系統(tǒng)都采用B-Tree或其變種B+Tree作為索引結(jié)構(gòu)。這里主要介紹使用較為廣泛的B+Tree,8,.,B+樹詳解,左邊的圖中，其中17表示一個磁盤文件的文件名；小紅方塊表示這個17文件內(nèi)容在硬盤中的存儲位置；p1表示指向17左子樹的指針。我們假設一個盤塊剛好只能存儲一個結(jié)點，那么左圖中一個結(jié)點就表示一個盤塊，其子樹指針就是指向另一個盤塊的地址。現(xiàn)在我們來模擬查找文件29的過程：根據(jù)根結(jié)點指針找到文件目錄的根磁盤塊1，將其中的信息導入內(nèi)存。【磁盤IO操作1次】此時內(nèi)存中有兩個文件名17、35和三個存儲其他磁盤頁面地址的數(shù)據(jù)。,9,.,B+樹查找過程,根據(jù)算法我們發(fā)現(xiàn)：172935，因此我們找到指針p2。根據(jù)p2指針，我們定位到磁盤塊3，并將其中的信息導入內(nèi)存?！敬疟PIO操作2次】此時內(nèi)存中有兩個文件名26，30和三個存儲其他磁盤頁面地址的數(shù)據(jù)。根據(jù)算法我們發(fā)現(xiàn)：2629=，BETWEEN，IN，以及某些時候的LIKE才會使用索引。例如：SELECTFROMuseruLEFTJOINdeptdON=WHEREu.age=20ANDu.addr=廣西此時就需要對age和addr建立索引，由于dept表的name也出現(xiàn)在了JOIN子句中，也有對它建立索引的必要。,28,.,索引的建立,一般來說，應該在這些列上創(chuàng)建索引：在經(jīng)常需要搜索的列上，可以加快搜索的速度;在作為主鍵的列上，強制該列的唯一性;在經(jīng)常用在連接的列上，這些列主要是一些外鍵，可以加快連接的速度;在經(jīng)常需要根據(jù)范圍進行搜索的列上創(chuàng)建索引，因為索引已經(jīng)排序，其指定的范圍是連續(xù)的;在經(jīng)常需要排序的列上創(chuàng)建索引，因為索引已經(jīng)排序，這樣查詢可以利用索引的排序，加快排序查詢時間;在經(jīng)常使用在WHERE子句中的列上面創(chuàng)建索引，加快條件的判斷速度。,29,.,同樣，對于有些列不應該創(chuàng)建索引。一般來說，不應該創(chuàng)建索引的的這些列具有下列特點：第一，對于那些在查詢中很少使用的列不應該創(chuàng)建索引。這是因為，既然這些列很少使用到，因此有索引或者無索引，并不能提高查詢速度。相反，由于增加了索引，反而降低了系統(tǒng)的維護速度和增大了空間需求。第二，對于那些只有很少數(shù)據(jù)值的列也不應該增加索引。這是因為，由于這些列的取值很少，例如人事表的性別列，在查詢的結(jié)果中，結(jié)果集的數(shù)據(jù)行占了表中數(shù)據(jù)行的很大比例，即需要在表中搜索的數(shù)據(jù)行的比例很大。增加索引，并不能明顯加快檢索速度。第三，對于那些定義為text,image和bit數(shù)據(jù)類型的列不應該增加索引。這是因為，這些列的數(shù)據(jù)量要么相當大，要么取值很少。第四，當修改性能遠遠大于檢索性能時，不應該創(chuàng)建索引。這是因為，修改性能和檢索性能是互相矛盾的。當增加索引時，會提高檢索性能，但是會降低修改性能。當減少索引時，會提高修改性能，降低檢索性能。因此，當修改性能遠遠大于檢索性能時，不應該創(chuàng)建索引。,30,.,索引的優(yōu)點,創(chuàng)建索引可以大大提高系統(tǒng)的性能。第一，通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。第二，可以大大加快數(shù)據(jù)的檢索速度，這也是創(chuàng)建索引的最主要的原因。第三，可以加速表和表之間的連接。第四，在使用分組和排序子句進行數(shù)據(jù)檢索時，同樣可以顯著減少查詢中分組和排序的時間。第五，通過使用索引，可以在查詢的過程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能。,31,.,索引的缺點,增加索引也有許多不利的方面。第一，創(chuàng)建索引和維護索引要耗費時間，這種時間隨著數(shù)據(jù)量的增加而增加。第二，索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會更大。第三，當對表中的數(shù)據(jù)進行增加、刪除和修改的時候，索引也要動態(tài)的維護，這樣就降低了數(shù)據(jù)的維護速度。,32,.,使用索引的注意事項,1.索引不會包含有NULL值的列:只要列中包含有NULL值都將不會被包含在索引中，組合索引中只要有一列含有NULL值，那么這一列對于此組合索引就是無效的。所以我們在數(shù)據(jù)庫設計時不要讓字段的默認值為NULL。2.使用短索引:對串列進行索引，如果可能應該指定一個前綴長度。例如，如果有一個CHAR(255)的列，如果在前10個或20個字符內(nèi)，多數(shù)值是惟一的，那么就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節(jié)省磁盤空間和I/O操作。,33,.,使用索引的注意事項,3.索引列排序:MySQL查詢只使用一個索引，因此如果where子句中已經(jīng)使用了索引的話，那么orderby中的列是不會使用索引的。因此數(shù)據(jù)庫默認排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列創(chuàng)組合索引。4.like語句操作:一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like“%aaa%”不會使用索引而like“aaa