sql index




數據庫索引如何工作? (6)

SQL索引與加速SQL數據庫中的搜索有關。 索引允許程序員非常快地從數據庫檢索數據。 假設你是一名學生或一些書讀者。 你的書包含50,000頁。 第一天你讀第二天一些話題“ABC”你想讀一些另一個話題“xyz”。 你將永遠不會手動逐頁瀏覽。 在這種情況下你要做的是使用Book索引來查看某個特定的主題,然後直接跳轉到你的主題。 索引節省了大量的時間來搜索主題。 在SQL索引中相同,Index允許從數據庫中非常快地搜索數百萬條記錄。

鑑於indexing是如此重要,因為你的數據集的規模增加,有人可以解釋如何索引工作在database-agnostic水平?

有關索引字段的查詢的信息,請查看如何索引數據庫列


為什麼需要?

當數據存儲在基於磁盤的存儲設備上時,它將作為數據塊存儲。 這些塊被完整訪問,使它們成為原子磁盤訪問操作。 磁盤塊的結構與鏈接列表非常相似, 都包含數據部分,指向下一個節點(或塊)位置的指針,並且都不需要連續存儲。

由於許多記錄只能在一個字段上排序,所以我們可以聲明,在未排序的字段上搜索需要一個線性搜索,它需要N/2塊的訪問(平均),其中N是表跨越的塊數。 如果該字段是非關鍵字段(即不包含唯一條目),則必須在N塊訪問時搜索整個表空間。

而對於排序字段,可以使用二進制搜索,其具有log2 N塊訪問。 此外,由於數據是在給定非關鍵字段的情況下進行排序的,因此一旦找到更高的值,表格的其餘部分就不需要搜索重複值。 因此,性能增加是相當大的。

什麼是索引?

索引是一種對多個字段上的多個記錄進行排序的方法。 在表中的字段上創建索引會創建另一個保存字段值的數據結構,以及指向與其相關的記錄的指針。 然後對索引結構進行排序,從而對其執行二進制搜索。

索引的缺點是這些索引需要額外的磁盤空間,因為索引使用MyISAM引擎一起存儲在表中,如果同一表中的許多字段被索引,則該文件可以快速達到底層文件系統的大小限制。

它是如何工作的?

首先,讓我們概述一個示例數據庫表模式;

Field name       Data type      Size on disk
id (Primary key) Unsigned INT   4 bytes
firstName        Char(50)       50 bytes
lastName         Char(50)       50 bytes
emailAddress     Char(100)      100 bytes

注意 :字符被用來代替varchar以允許磁盤值的準確大小。 此示例數據庫包含500萬行,並且是未索引的。 現在將分析幾個查詢的性能。 這些是使用id (排序的關鍵字段)的查詢和使用firstName (非關鍵字未排序的字段)的查詢。

示例1 - 分類與未排序的字段

假設我們的樣本數據庫的r = 5,000,000個固定大小的記錄給出了R = 204個字節的記錄長度,並且它們使用MyISAM引擎存儲在表中,該引擎使用默認塊大小B = 1,024字節。 該表的阻塞因子是每個磁盤塊的bfr = (B/R) = 1024/204 = 5記錄。 保存該表所需的塊的總數是N = (r/bfr) = 5000000/5 = 1,000,000個塊。

如果id字段是關鍵字段,那麼對id字段進行線性搜索將需要平均N/2 = 500,000塊訪問來查找值。 但是由於id字段也被排序,所以可以進行二分搜索,要求平均log2 1000000 = 19.93 = 20塊訪問。 瞬間我們可以看到這是一個巨大的改進。

現在firstName字段既沒有排序也沒有關鍵字段,所以二進制搜索是不可能的,並且值也不是唯一的,因此該表需要搜索到最終的N = 1,000,000塊的訪問。 索引編制旨在糾正這種情況。

鑑於索引記錄僅包含索引字段和指向原始記錄的指針,因此它會比它指向的多字段記錄更小。 所以索引本身需要的磁盤塊比原始表要少,因此需要更少的塊訪問來遍歷。 firstName字段上的索引模式概述如下;

Field name       Data type      Size on disk
firstName        Char(50)       50 bytes
(record pointer) Special        4 bytes

注意 :根據表的大小,MySQL中的指針長度為2,3,4或5個字節。

示例2 - 索引

假設我們的樣本數據庫的索引記錄長度為R = 54個字節,並且使用缺省塊大小B = 1,024字節的r = 5,000,000條記錄。 索引的阻塞因子為每個磁盤塊的bfr = (B/R) = 1024/54 = 18記錄。 保存索引所需的塊總數為N = (r/bfr) = 5000000/18 = 277,778個塊。

現在,使用firstName字段的搜索可以利用索引來提高性能。 這允許以平均log2 277778 = 18.08 = 19塊訪問的索引進行二分搜索。 要找到實際記錄的地址,需要進一步的塊讀取權限,使總數達到19 + 1 = 20塊訪問,與在非索引表中查找firstName匹配所需的1,000,000個塊訪問相差甚遠。

什麼時候應該使用它?

鑑於創建索引需要額外的磁盤空間(上述示例中增加277,778個塊,增加約28%),並且索引太多會導致文件系統大小限制引起的問題,因此必須仔細考慮選擇正確的字段進行索引。

由於索引僅用於加快在記錄中搜索匹配字段的速度,因此,僅用於輸出的索引字段在進行插入或刪除操作時僅僅是浪費磁盤空間和處理時間,因此是理所當然的應該避免。 同樣考慮到二進制搜索的性質,數據的基數或唯一性也很重要。 對基數為2的字段進行索引會將數據分成兩半,而基數為1,000則會返回大約1,000條記錄。 如果基數低,效果會降低到線性排序,如果基數小於記錄數的30%,查詢優化器將避免使用索引,從而有效地使索引浪費空間。


我第一次讀這篇文章對我非常有幫助。 謝謝。

從那時起,我對創建索引的缺點有了一些了解:如果使用一個索引寫入表( UPDATEINSERT ),則實際上在文件系統中有兩個寫操作。 一個用於表格數據,另一個用於索引數據(以及對它的引用(以及 - 如果聚集 - 表格數據的求和))。 如果表和索引位於同一塊硬盤上,則花費更多時間。 因此,沒有索引(堆)的表將允許更快的寫入操作。 (如果你有兩個索引,最終會有三個寫操作,依此類推)

但是,在兩個不同的硬盤上為索引數據和表格數據定義兩個不同位置可以減少/消除增加時間成本的問題。 這需要在所需硬盤上定義附加文件組和相應文件,並根據需要定義表格/索引位置。

索引的另一個問題是插入數據時隨時間的碎片。 REORGANIZE幫助,你必須編寫例程來完成它。

在某些情況下,堆比帶索引的表更有用,

例如: - 如果您有很多相對的寫作,但只有一個晚上在工作時間以外進行報告。

而且,聚集索引和非聚集索引之間的區別也非常重要。

幫助我: - 聚集索引和非聚集索引實際上意味著什麼?


現在,假設我們想運行查詢來查找名為'Abc'的所有員工的所有詳細信息?

SELECT * FROM Employee 
WHERE Employee_Name = 'Abc'

沒有索引會發生什麼?

數據庫軟件從字面上必須查看Employee表中的每一行,以查看該行的Employee_Name是否為'Abc'。 而且,因為我們希望每一行里面都有'Abc'這個名字,所以一旦我們找到一行名為'Abc'的行,我們就不能停止查找,因為可能有其他行的名稱為Abc 。 因此,直到最後一行為止的每一行都必須被搜索 - 這意味著在這種情況下成千上萬行將被數據庫檢查以查找名稱為'Abc'的行。 這就是所謂的全表掃描

數據庫索引如何提高性能

具有索引的全部要點是通過基本上減少需要檢查的表中的記錄/行數來加速搜索查詢。 索引是一種數據結構(最常見的是B-樹),用於存儲表中特定列的值。

B樹索引如何工作?

B樹是索引中最流行的數據結構的原因是由於它們具有時間效率 - 因為查找,刪除和插入都可以在對數時間內完成。 而且,B-樹更常用的另一個主要原因是因為可以對存儲在B-樹內的數據進行排序。 RDBMS通常確定哪個數據結構實際用於索引。 但是,在某些使用某些RDBMS的場景中,實際上可以指定在創建索引時希望數據庫使用哪種數據結構。

哈希表索引如何工作?

使用哈希索引的原因是因為散列表在查找值時非常高效。 因此,如果使用散列索引,那麼與字符串進行比較的查詢可以非常快速地檢索值。

例如,我們前面討論的查詢可以從Employee_Name列上創建的哈希索引中受益。 散列索引的工作方式是列值將成為哈希表的鍵值,映射到該鍵值的實際值只是指向表中的行數據的指針。 由於哈希表基本上是一個關聯數組,因此典型的條目看起來像“Abc => 0x28939”,其中0x28939是對Abc存儲在內存中的表行的引用。 在哈希表索引中查找像“Abc”這樣的值並取回對內存中行的引用顯然要比掃描表找到Employee_Name列中值為“Abc”的所有行要快得多。

哈希索引的缺點

哈希表不是有序的數據結構,並且有許多類型的查詢哪些哈希索引甚至無法幫助。 例如,假設你想找出所有40歲以下的員工。 你怎麼能用哈希表索引來做到這一點? 那麼,這是不可能的,因為散列表只適用於查找關鍵值對 - 這意味著查詢檢查是否相等

數據庫索引究竟是什麼? 因此,現在您知道數據庫索引是在表中的列上創建的,並且索引將該值存儲在該特定列中。 但是,理解數據庫索引不會將值存儲在同一表的其他列中很重要。 例如,如果我們在Employee_Name列上創建索引,這意味著Employee_Age和Employee_Address列值不會同時存儲在索引中。 如果我們只是將所有其他列存儲在索引中,那麼就像創建整個表的另一個副本一樣 - 這會佔用太多空間並且效率非常低。

數據庫如何知道何時使用索引? 當運行“SELECT * FROM Employee WHERE Employee_Name ='Abc'”這樣的查詢時,數據庫將檢查被查詢的列是否存在索引。 假設Employee_Name列確實有索引創建,數據庫將不得不決定使用索引來查找正在搜索的值是否有意義 - 因為在某些情況下,使用數據庫索引實際上效率較低,並且更有效地掃描整個表格。

擁有數據庫索引的成本是多少?

它佔用空間 - 桌子越大,指數越大。 索引的另一個性能是,無論何時添加,刪除或更新相應表中的行,都必須對索引執行相同的操作。 請記住,索引需要包含與索引覆蓋的表列中的任何內容相同的分鐘數據。

一般來說,如果索引列中的數據將經常被查詢,則只應在表上創建索引。

也可以看看

  1. 哪些列通常能夠創建好的索引?
  2. 數據庫索引如何工作


經典示例“書籍索引”

考慮一個1000頁的“書”,除以100個部分,每部分包含X頁。

簡單,是吧?

現在,在沒有索引頁的情況下,要找到以字母“S”開頭的特定部分,除了掃描整本書外,沒有別的選擇。 即:1000頁

但是一開始就有索引頁面,你就在那裡。 更重要的是,要閱讀任何重要的部分,您只需每次查看索引頁面一遍又一遍。 找到匹配索引後,您可以通過跳過其他部分高效跳轉到該部分。

但是,除了1000頁之外,您還需要另外10頁才能顯示索引頁,因此總共需要1010頁。

因此,索引是一個單獨的部分,用於存儲索引列的值+指向已索引行的指針,以便進行有效查找。

事情在學校很簡單,不是嗎? :P





database-indexes