您現在的位置: 365建站網 > 365學習 > mysql數據庫索引的作用 原理 優缺點和用法

mysql數據庫索引的作用 原理 優缺點和用法

文章來源:365jz.com     點擊數:1659    更新時間:2019-06-23 01:03   參與評論

 什么是索引:

索引就像是書的目錄,是與表或視圖關聯的磁盤上結構,可以加快從表或視圖中檢索行的速度。索引中包含由表或視圖中的一列或多列生成的鍵。這些鍵存儲在一個結構(BTree)中,使SQL可以快速有效地查找與鍵值關聯的行。

1、為什么要創建索引呢?

這是因為,創建索引可以大大提高系統的性能。 
第一,通過創建唯一性索引,可以保證數據庫表中每一行數據的唯一性。 
第二,可以大大加快 數據的檢索速度,這也是創建索引的最主要的原因。 
第三,可以加速表和表之間的連接,特別是在實現數據的參考完整性方面特別有意義。 
第四,在使用分組和排序 子句進行數據檢索時,同樣可以顯著減少查詢中分組和排序的時間。 
第五,通過使用索引,可以在查詢的過程中,使用優化隱藏器,提高系統的性能。

2、也許會有人要問:增加索引有如此多的優點,為什么不對表中的每一個列創建一個索引呢?

這種想法固然有其合理性,然而也有其片面性。雖然,索引有許多優點, 但是,為表中的每一個列都增加索引,是非常不明智的。這是因為,增加索引也有許多不利的一個方面。

第一,創建索引和維護索引要耗費時間,這種時間隨著數據 量的增加而增加。 
第二,索引需要占物理空間,除了數據表占數據空間之外,每一個索引還要占一定的物理空間,如果要建立聚簇索引,那么需要的空間就會更大。 
第三,當對表中的數據進行增加、刪除和修改的時候,索引也要動態的維護,這樣就降低了數據的維護速度。

為什么要建立索引,即索引的優點:

①  建立索引的列可以保證行的唯一性,生成唯一的rowId

②  建立索引可以有效縮短數據的檢索時間

③  建立索引可以加快表與表之間的連接

④  為用來排序或者是分組的字段添加索引可以加快分組和排序順序

 索引的缺點:

①  創建索引和維護索引需要時間成本,這個成本隨著數據量的增加而加大

②  創建索引和維護索引需要空間成本,每一條索引都要占據數據庫的物理存儲空間,數據量越大,占用空間也越大(數據表占據的是數據庫的數據空間)

③  會降低表的增刪改的效率,因為每次增刪改索引需要進行動態維護,導致時間變長


3、索引是建立在數據庫表中的某些列的上面

因此,在創建索引的時候,應該仔細考慮在哪些列上可以創建索引,在哪些列上不能創建索引。一般來說,應該在這些列 上創建索引,例如:

在經常需要搜索的列上,可以加快搜索的速度; 
在作為主鍵的列上,強制該列的唯一性和組織表中數據的排列結構; 
在經常用在連接的列上,這 些列主要是一些外鍵,可以加快連接的速度; 
在經常需要根據范圍進行搜索的列上創建索引,因為索引已經排序,其指定的范圍是連續的; 
在經常需要排序的列上創 建索引,因為索引已經排序,這樣查詢可以利用索引的排序,加快排序查詢時間; 
在經常使用在WHERE子句中的列上面創建索引,加快條件的判斷速度。

什么樣的表跟列要建立索引:

①  總的來說就是數據量大的,經常進行查詢操作的表要建立索引

②  表中字段建立索引應該遵循幾個原則:

1)        越小的數據類型通常更好:越小的數據類型通常在磁盤、內存中都需要更少的空間,處理起來更快。

2)          簡單的數據類型更好:整型數據比起字符,處理開銷更小,因為字符串的比較更復雜,處理起來也更耗時。

3)        盡量避免NULL:應該指定列為NOT NULL。含有空值的列很難進行查詢優化,因為它們使得索引、索引的統計信息以及比較運算更加復雜。你應該用0、一個特殊的值或者一個空串代替空值。

4)        對非唯一的字段,例如“性別”這種大量重復值的字段,增加索引也沒有什么意義,所以索引的建立應當更多的選取唯一性更高的字段。

③  表與表連接用于多表聯合查詢的約束條件的字段應當建立索引

④  用于排序的字段可以添加索引,用于分組的字段應當視情況看是否需要添加索引。

⑤  添加多列索引的時候,對應的多條件查詢可以觸發該索引的同時,索引最左側的列的單條件查詢也可以觸發。

⑥  如果有些表注定只會進行查詢所有,也就沒必要添加索引,因為查詢全部只能進行全量搜索即掃描全表。


4、同樣,對于有些列不應該創建索

一般來說,不應該創建索引的的這些列具有下列特點:

第一,對于那些在查詢中很少使用或者參考的列不應該創建索引。這是因 為,既然這些列很少使用到,因此有索引或者無索引,并不能提高查詢速度。相反,由于增加了索引,反而降低了系統的維護速度和增大了空間需求。 
第二,對于那 些只有很少數據值的列也不應該增加索引。這是因為,由于這些列的取值很少,例如人事表的性別列,在查詢的結果中,結果集的數據行占了表中數據行的很大比 例,即需要在表中搜索的數據行的比例很大。增加索引,并不能明顯加快檢索速度。 
第三,對于那些定義為text, image和bit數據類型的列不應該增加索引。這是因為,這些列的數據量要么相當大,要么取值很少。 
第四,當修改性能遠遠大于檢索性能時,不應該創建索 引。這是因為,修改性能和檢索性能是互相矛盾的。當增加索引時,會提高檢索性能,但是會降低修改性能。當減少索引時,會提高修改性能,降低檢索性能。因 此,當修改性能遠遠大于檢索性能時,不應該創建索引。

索引的原理:

索引的原理大致概括為以空間換時間,數據庫在未添加索引的時候進行查詢默認的是進行全量搜索,也就是進行全局掃描,有多少條數據就要進行多少次查詢,然后找到相匹配的數據就把他放到結果集中,直到全表掃描完。而建立索引之后,會將建立索引的KEY值放在一個n叉樹上(BTree)。因為B樹的特點就是適合在磁盤等直接存儲設備上組織動態查找表,每次以索引進行條件查詢時,會去樹上根據key值直接進行搜索,次數約為log總條數,底數為頁面存儲數,例如一個100萬數據的表,頁面存儲數為100,那么有索引的查詢次數為3次log1000000100,但是全量搜索為100萬次搜索,這種方式類似于二分法,但是這個是n分法。

索引對增刪改的影響實際數據修改測試:

一個表有字段A、B、C,同時進行插入10000行記錄測試

在沒有建索引時平均完成時間是2.9秒

在對A字段建索引后平均完成時間是6.7秒

在對A字段和B字段建索引后平均完成時間是10.3秒

在對A字段、B字段和C字段都建索引后平均完成時間是11.7秒

從以上測試結果可以明顯看出索引對數據修改產生的影響


5、創建索引的方法

創建索引有多種方法,這些方法包括直接創建索引的方法和間接創建索引的方法。直接創建索引,例如使用CREATE INDEX語句或者使用創建索引向導,間接創建索引,例如在表中定義主鍵約束或者唯一性鍵約束時,同時也創建了索引。雖然,這兩種方法都可以創建索引,但 是,它們創建索引的具體內容是有區別的。

使用CREATE INDEX語句或者使用創建索引向導來創建索引,這是最基本的索引創建方式,并且這種方法最具有柔性,可以定制創建出符合自己需要的索引。在使用這種方式 創建索引時,可以使用許多選項,例如指定數據頁的充滿度、進行排序、整理統計信息等,這樣可以優化索引。使用這種方法,可以指定索引的類型、唯一性和復合 性,也就是說,既可以創建聚簇索引,也可以創建非聚簇索引,既可以在一個列上創建索引,也可以在兩個或者兩個以上的列上創建索引。

通過定義主鍵約束或者唯一性鍵約束,也可以間接創建索引。主鍵約束是一種保持數據完整性的邏輯,它限制表中的記錄有相同的主鍵記錄。在創建主鍵約束時,系 統自動創建了一個唯一性的聚簇索引。雖然,在邏輯上,主鍵約束是一種重要的結構,但是,在物理結構上,與主鍵約束相對應的結構是唯一性的聚簇索引。換句話 說,在物理實現上,不存在主鍵約束,而只存在唯一性的聚簇索引。同樣,在創建唯一性鍵約束時,也同時創建了索引,這種索引則是唯一性的非聚簇索引。因此, 當使用約束創建索引時,索引的類型和特征基本上都已經確定了,由用戶定制的余地比較小。

當在表上定義主鍵或者唯一性鍵約束時,如果表中已經有了使用CREATE INDEX語句創建的標準索引時,那么主鍵約束或者唯一性鍵約束創建的索引覆蓋以前創建的標準索引。也就是說,主鍵約束或者唯一性鍵約束創建的索引的優先 級高于使用CREATE INDEX語句創建的索引。

6、索引的特征

索引有兩個特征,即唯一性索引復合索引。

唯一性索引保證在索引列中的全部數據是唯一的,不會包含冗余數據。如果表中已經有一個主鍵約束或者唯一性鍵約束,那么當創建表或者修改表時,SQL Server自動創建一個唯一性索引。然而,如果必須保證唯一性,那么應該創建主鍵約束或者唯一性鍵約束,而不是創建一個唯一性索引。當創建唯一性索引 時,應該認真考慮這些規則:當在表中創建主鍵約束或者唯一性鍵約束時,SQL Server自動創建一個唯一性索引;如果表中已經包含有數據,那么當創建索引時,SQL Server檢查表中已有數據的冗余性;每當使用插入語句插入數據或者使用修改語句修改數據時,SQL Server檢查數據的冗余性:如果有冗余值,那么SQL Server取消該語句的執行,并且返回一個錯誤消息;確保表中的每一行數據都有一個唯一值,這樣可以確保每一個實體都可以唯一確認;只能在可以保證實體 完整性的列上創建唯一性索引,例如,不能在人事表中的姓名列上創建唯一性索引,因為人們可以有相同的姓名。

復合索引就是一個索引創建在兩個列或者多個列上。在搜索時,當兩個或者多個列作為一個關鍵值時,最好在這些列上創建復合索引。當創建復合索引時,應該考慮 這些規則:最多可以把16個列合并成一個單獨的復合索引,構成復合索引的列的總長度不能超過900字節,也就是說復合列的長度不能太長;在復合索引中,所 有的列必須來自同一個表中,不能跨表建立復合列;在復合索引中,列的排列順序是非常重要的,因此要認真排列列的順序,原則上,應該首先定義最唯一的列,例 如在(COL1,COL2)上的索引與在(COL2,COL1)上的索引是不相同的,因為兩個索引的列的順序不同;為了使查詢優化器使用復合索引,查詢語 句中的WHERE子句必須參考復合索引中第一個列;當表中有多個關鍵列時,復合索引是非常有用的;使用復合索引可以提高查詢性能,減少在一個表中所創建的 索引數量。


索引是個既穩定又開放的信息結構,它有十一種功能。

1、分解功能

把文獻中的資料單元(如篇名、機構、短語、概念、物名、地名、書名、人名、字詞、符號等)一一分解,這就是索引的分解功能。它是索引工作的起跑線和索引編纂的基礎,沒有對文獻內容的這種分解功能,就沒有索引。

過去有些反對索引的人說,索引是把古人的著書“凌遲碎割”。他們對索引法的反對,實出于對流傳已久的那種落后的皓首窮經的陋習的偏愛和對新的治學方法的無知,洪業曾鄙視他們為臥于涸轍的鮒魚,以升斗之水濟命,而不知西江水之可羨。雖然如此,但他們所謂的索引是把古人著書“凌遲碎割”的形象說法,卻從反面十分正確地道破了索引的分解功能。

分解功能是索引作用于文獻的特殊功能,是它和其他檢索工作不同之處。

2、梳理功能

每種文獻都包容著許多不同性質的資料單元,它們在文獻中基本呈無序的狀態。把這些無序狀態的資料單元按外表特征或內容性質進行各歸其類的整理,這就是索引的梳理功能。章學誠早就發現了這種功能,他在給《族孫守一論史表》信中要求其在治二十四史年表時一并把廿二史列傳中的人名編成索引,兩者互為經緯,這樣便可使考古之士,于紛如亂絲之資料中,忽得梳通櫛理。

梳理功能是索引分解的后繼。如果只有分解功能而沒有梳理的功能,那么分解功能就沒有價值。

梳理是對資料單元的初分。如是字序,只要按筆劃或音序歸類即可;如是類序只要按大類歸納即可。就像小姑娘梳頭,先把長發梳順,而編什么辮子或梳什么發型則是下一步的要求了。

3、組合功能

把梳理后的資料單元按照分類的要求,嚴密地組織它們的類別層次以及類目下的專題和同類目下款目的序列關系;或按字序的要求,嚴密地把標目的結構正裝或倒裝、考慮限定詞對標目的限定和修飾的級數、或考慮字序和類序相結合的可能。此外,不論是類序或字序都要考慮參照系統的建立方案,使相關款目形成網絡,使用戶檢索的眼界得以拓寬。這些,都是索引的組合功能。

過去,國外的同行曾把圣經的頁邊索引以“串珠”命名;我國有人曾把本草的方劑編成索引,以“針線”命名,“串珠”和“針線”是索引組合功能很形象的描繪。它使文獻資料單元成為一串串的明珠,成為被針線貫穿起來的資料單元的珍品。

4、結網功能

對某個領域的文獻進行有計劃的索引編纂,利用類型的結構從各種不同的角度和層次對這些文獻的內容進行縱橫交錯和多維的揭示和組合,使之形成一個檢索這些文獻中的各種不同性質的資料單元的網絡。這就是索引的結網功能。

由“主表”和“詞族索引”、“范疇索引”、“英漢對照索引”等所組成的《漢語主題詞表》是由幾種不同性質的索引構建的一個主題詞間的聯系、辨析主題詞詞義和被標引的文獻主題概念是否精確的一個隱含的語義網絡,它對文獻中的資料單元產生族性檢索和擴大檢索途徑的作用。這個網絡的結構和作用就是運用索引結網功能的一個范例。

《古今圖書集成》囊括了清初以前絕大部分的文獻,是我國現存最大的一部類書,廣西大學林仲湘等根據它的特點,分別編了經線索引、緯線索引、圖表索引、人物傳記索引、職方典匯考引和禽蟲草木三典釋名索引,從該書的各級類目和內容等不同角度交叉編結了一個嚴密的揭示網絡,這又是索引結網功能的具體運用的例證。

5、揭示功能

分解功能只是客觀地對文獻進行解剖,而揭示功能有較強的目的性,它最常出現的是按專業的需要挑選某些資料單元。

在主題索引的編制中,主題概念的分析和主題詞的選用,包括對隱性主題的析出是這種功能的體現。

各種專題索引或各種專業的文摘索引也是索引揭示功能的運用。清朝徐克祺稱贊蔡烈先的《本草萬方針線》為鏡考、為燭照,這是對索引揭示功能很生動的形容。

6、鑒別功能

文獻是作者思維的物化,包含了作者用以施展其聰明才智的特有的言語軌跡及其思想氣質。在文獻的出版史中,出于各種不同的動機和原因,真偽之作疊出,為杜偽辨真,不少人引經據典,注疏考證,一字之微,動千萬言,甚至引發了??睂W的出現。近年來又產生了利用索引進行鑒別的方法,通過逐字索引分解被鑒別的文獻,即同時從兩種版本中分析作者用字(詞)的規律和頻率以甄別其真偽。

英國的女研究學家凱洛萊因·斯彼琴曾對莎士比亞的劇作進行過有趣的用詞統計和分析,她發現,莎氏使用的許多詩意的語言都和大自然以及鄉村生活有關。又有人在研究司馬遷的《史記·貨殖列傳》時,發現這篇不到5000字的文章竟使用49次“富”字,由此得出,致“富”是這篇論文的主題。這些事例證明通過對文獻中用詞的解剖可以發現作者的特有氣質和語言習慣,索引可利用這種潛在的規律辨析文獻的真偽。

7、追蹤功能

索引像只嗅覺靈敏的警犬一樣能追蹤各種領域的事物在文獻中留下來的痕跡。

一部舊時代報紙的索引,能追蹤那個時代的重大歷史事件,并清晰地再現這些重大的歷史事件脈絡;一部類書的索引,能追蹤許多名人的各方面的成就和言行;一種專業期刊的刊后索引,能追蹤當年該專業學科研究的動態;一部跨多年度的報刊回溯索引,就是一部多功能的追蹤記錄。

追蹤,是索引的功能之一。

8、導航功能

導航就是指引,它帶有較強的評價取向,索引具有這種功能。

過去,我國有一些所謂新產品,在技術人員的積極努力下問市了,但到國際市場上卻被打回票,因為這種新產品在別的國家已成老產品。技術人員的情報鼻子不靈,其中原因之一是不查閱有關的索引,不依靠這個情報導航工具,以致迷失了生產的方向。

在哲學社會科學的領域中,索引經常能提供在某個時期某個專業的理論動向和水平的第一手材料,許多科研工作者就依靠索引的導航,找到自己出發的港灣和要達到的彼岸。

洪業在《引得說》中指出:“引得者,予學者以游翔于載籍之舟車也。舟車愈善,則其所游愈廣,所入愈深?!边@就是索引的導航功能。

9、執簡功能

您感到在信息爆炸的文獻環境中很難伸展自己的科研手腳吧?那么,請您先為自己的研究對象編編索引,那就可以執簡御繁了。我國的史學家顧頡剛曾說:“我常想暫不辦學術研究所而先辦材料整理所?!薄八饕?,也是研究的基礎的一種,它能給您一個鑰匙,使您在許多頭緒不清的材料中找出頭緒,而得到你所需要的東西?!?/p>

國外盛行的那種快速反映科研情報的KWIC、KWOC,就是發揮索引執簡功能的工具,把眾多科研期刊中的關鍵詞以最簡要的格式顯現,使用戶能享受“索引在手,千刊掌握”的好處,而避免花去一半的科研時間在眾多的資料堆中苦苦求索。因而,不少科研工作者贊譽索引有“天增歲月人增壽”的妙用。

10、檢索功能

索引誕生在文獻檢索困難的歷史背景里,它的檢索功能隨索引的誕生同步而生。在近二千年的索引發展史中,檢索功能的變化和發展就是它的主要內容。所以,檢索功能是索引最基本的功能。

任何文獻記錄或工作記錄一旦和索引方法掛上鉤,它就會從山窮水盡疑無路的困境中解脫出來,很快得到檢索方便的好處,機關或企業中人事檔案和工資單的人員的索引就是如此。又如,最令旅游者頭疼的是,到了一個旅游點,買了地圖,但要尋找一個地區、一條街道或里弄,那真像大海撈針一樣的困難。當這幅地圖加上了地名索引后,所要之地名便一檢即得了。

11、預測功能

索引中有不少數學現象,如從引文索引中統計到被引作者的論文頻率,便基本上能預測到這個專業的學術帶頭人的出現;從題錄索引中統計的課題內容,便可預測到學術界未來研究的動向;從統計索引中出現的各種產品名稱的頻率,便能預測到市場將出現的熱門商品。這些都是索引的預測功能。

MYSQL數據庫四種索引類型的簡單使用


MYSQL數據庫索引類型包括普通索引,唯一索引,主鍵索引與組合索引,這里對這些索引的做一些簡單描述:


(1)普通索引

這是最基本的MySQL數據庫索引,它沒有任何限制。它有以下幾種創建方式:

創建索引

CREATE INDEX indexName ON mytable(username(length));

如果是CHAR,VARCHAR類型,length可以小于字段實際長度;如果是BLOB和TEXT類型,必須指定 length,下同。

修改表結構

ALTER mytable ADD INDEX [indexName] ON (username(length)) 創建表的時候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );

刪除索引的語法:

DROP INDEX [indexName] ON mytable;

(2)唯一索引

它與前面的普通索引類似,不同的就是:MySQL數據庫索引列的值必須唯一,但允許有空值。如果是組合索引,則列值的組合必須唯一。它有以下幾種創建方式:

創建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length)) 修改表結構

ALTER mytable ADD UNIQUE [indexName] ON (username(length)) 創建表的時候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );

(3)主鍵索引

它是一種特殊的唯一索引,不允許有空值。一般是在建表的時候同時創建主鍵索引:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );

當然也可以用 ALTER 命令。記?。阂粋€表只能有一個主鍵。

(4)組合索引

為了形象地對比單列索引和組合索引,為表添加多個字段:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );

為了進一步榨取MySQL的效率,就要考慮建立組合索引。就是將 name, city, age建到一個索引里:

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age); 建表時,usernname長度為 16,這里用 10。這是因為一般情況下名字的長度不會超過10,這樣會加速索引查詢速度,還會減少索引文件的大小,提高INSERT的更新速度。

如果分別在 usernname,city,age上建立單列索引,讓該表有3個單列索引,查詢時和上述的組合索引效率也會大不一樣,遠遠低于我們的組合索引。雖然此時有了三個索引,但MySQL只能用到其中的那個它認為似乎是最有效率的單列索引。

建立這樣的組合索引,其實是相當于分別建立了下面三組組合MySQL數據庫索引:

usernname,city,age usernname,city usernname 為什么沒有 city,age這樣的組合索引呢?這是因為MySQL組合索引“最左前綴”的結果。簡單的理解就是只從最左面的開始組合。并不是只要包含這三列的查詢都會用到該組合索引,下面的幾個SQL就會用到這個組合MySQL數據庫索引:

SELECT * FROM mytable WHREE username="admin" AND city="鄭州" SELECT * FROM mytable WHREE username="admin" 而下面幾個則不會用到:

SELECT * FROM mytable WHREE age=20 AND city="鄭州" SELECT * FROM mytable WHREE city="鄭州"



如對本文有疑問,請提交到交流論壇,廣大熱心網友會為你解答??! 點擊進入論壇


發表評論 (1659人查看,0條評論)
請自覺遵守互聯網相關的政策法規,嚴禁發布色情、暴力、反動的言論。
用戶名: 驗證碼: 點擊我更換圖片
最新評論
------分隔線----------------------------
自拍偷拍福力视频,偷拍国际精品,麻豆一区福利电影,国产网红视频午夜福利,se视频大全,久久国产AV影院 久久影音先锋最新资源网| 玉女阁福利精品导航| 无遮挡18禁羞羞漫画免费| videos xvideos2中国| 亚洲欧美日韩v在线播放| 亚洲综合小说另类图片动图| 国产av欧美在线观看| 久久免费看少妇高潮a片| 四虎国产精品永久入口| 国产av在线看的| 国产乱了真实在线观看| 中文字幕亚洲综合小综合| 亚洲综合久久一本伊一区| 亚欧激情无码视频在线播放| 最近最新中文字幕大全直播下载| plustoken| 男女性动态激烈动态图| 池中烟雨(h)| 新婚之夜新娘的哀羞| 拍摄av现场失控高潮数次| 小的学生videosex| 日本xxxx色视频在线观看免费| 国产成_人_综合_亚洲_国产| 不用付费的看黄神器| 亲子乱子伦视频| 大香蕉伊网在线| 厕所露脸高清近距离偷拍| 久久综合中文字幕无码| 亚洲人成在线播放网站| 国产美女视频在线播放| 伊人久久大香线蕉综合bd高清| http://www.tweetfeedr.com