首頁  >  文章  >  資料庫  >  mysql的索引優化如何使用

mysql的索引優化如何使用

ringa_lee
ringa_lee原創
2017-08-19 10:28:161537瀏覽

通常在每一本書的前幾頁都是目錄,而最後幾頁會有一個關鍵字索引。

對於資料庫來講系統表(如:sysobjects等)就是目錄,而標字段上的索引就如同書本後面的關鍵字索引。

資料庫中,目錄(資料字典)與索引的差異:目錄縱向、索引橫向。

一、影響索引作用的因素

區分度(檢索比率)

#優化器根據統計資訊來產生執行計劃,如果資料庫沒有收集索引的統計信息,優化器就無從下手,只能按部就班,透過全表掃描來執行查詢。所以,新建立的索引需要重新運行統計,否則索引無效。

舉一個例子,有個表TABLE1,其中有一個字段COL1取值是「1」、「2」、「3」三種,運行統計的結果是告訴資料庫TABLE1中的資料其中一個字段COL1的各種取值所佔的比重。示意如下:

「1」 - 12%;

「2」 - 66%;

「3」 - 22%。

假設還有個欄位COL2取值和資料所佔的百分比如下:

「A」- 50%;

「B」- 50%。

則查詢語句1:

select * from TABLE1 where COL1 = “1”and COL2 = “A”,

資料庫最佳化器會優先選擇欄位COL1上的索引來定位表中的數據,因為透過COL1上的索引就可以將結果集迅速定位在一個小範圍內12%。而相反的,對於查詢語句2:

select * from TABLE1 where COL1 = “2”and COL2 = “A”,

資料庫會優先選擇COL2上的索引,因為對於語句2的查詢條件COL2上的索引有較好的區分度。

從上面可以看出,資料庫的最佳化器通常會優先選擇區分度較高的索引(針對於查詢條件,條件不同選擇的索引可能不同)。

資料庫裡的數據是變化的,所以某個時候採集的統計信息,過一段時間後可能會過時,甚至誤導數據庫優化器,這樣同樣會造成運行性能的低下。所以除了,最初建立索引時需要執行統計,在表中的資料發生變化時也需要執行統計。經驗:當表中資料量變化達到10%時,需要重新執行統計。

二、聚集度

範圍掃描

#表格大小:

#小型資料表

#超大型表

業務類型

OLTP和OLAP

函數與索引

函數,like語句。 。 。

Substring(col_name,1, 3)vs. Substring(col_name, 3, 3)

like 'QQQ% vs. like '%QQQ'

#索引開銷

#性能利器

雙刃劍

#索引對插入操作的影響(Oracle)

#索引對插入操作的影響(MySQL)

比較索引與促發器對效能的影響

索引總結

使用索引實現關鍵資料的高效存取。但是需要知道每個索引都會為資料庫更新帶來額外的開銷。這就意味著,低效率的索引會為資料庫帶來災難。 ######對於資料庫,我們必須專注於關鍵資料的讀取,為他們提供最高效的存取路徑。對此,基本策略就是建立索引。在索引提供高效率存取的同時,也帶來了額外的系統開銷。開銷分為磁碟空間的開銷和處理器開銷。下面我們討論一下處理器開銷。每當在表中插入或刪除記錄時,該表的所有索引必須進行相應調整。每當已建立索引的欄位進行更新時,這種調整也會發生。舉例子說,如果在未建立索引的表中插入資料需要100個單位時間,那麼每增加一個索引就會增加100到250個單位時間。有趣的是,維護索引的開銷與簡單觸發器帶來的開銷大致相當。 ######在建立索引前線介紹一些最通俗的信息,這些信息來自developWorks,列出這些信息是因為我覺得這些信息通常情況下是值得參考的:######1.當要在一個合理的時間內結束查詢時,應避免添加索引,因為索引會降慢更新操作的速度並消耗額外的空間。有時候也可能存在覆蓋好幾個查詢的大型索引。 ######1.基數較大的欄位很適合用來做索引。 ######3.考慮到管理上的開銷,應避免在索引中使用多於5個的欄位。 ######4.對於多列索引,將查詢中引用最多的列放在定義的前面。 ######5.避免加入與現有的索引相似的索引。因為這樣會為優化器帶來更多的工作,並且會降慢更新操作的速度。相反,我們應該修改已有的索引,使其包含附加的欄位。例如,假設在一個表的 (c1,c2)上有一個索引i1。您注意到查詢中使用了"wherec2=?",於是又建立一個(c2)上的索引i2。但是這個相似的索引沒有添加任何東西,它只是i1的冗餘,而現在反而成了額外的開銷。 ###

6.如果表是唯讀的,並且包含很多的行,那麼可以嘗試定義一個索引,透過CREATE INDEX中的INCLUDE子句使該索引包含查詢中引用的所有列(被INCLUDE子句包含的列並不是索引的一部分,而只是作為索引頁的一部分來存儲,以避免附加的資料FETCHES)。

對於資料倉儲(查詢系統資料庫)可以建立較多的索引(索引和資料的比例可以是1:1)。

決定是否使用索引,可以專注於檢索比率。即,判斷索引有效性的依據,就使用鍵值作唯一性條件檢索出的資料的百分比。百分比越低,索引越有效。做出這個論點的前提是一些假設,如磁碟存取的相關效能。

索引鍵值相關記錄的物理位置是否相鄰也很重要,因為是透過區塊來操作資料的。建立了索引之後,如果索引鍵所指向的記錄散佈於整個表中,即使這些記錄在表中佔的比率很小,但因為它們分散在整個磁碟上,所以索引的效能就會大打折扣。

另外值得注意的是,函數和型別轉換可能導致索引失效。

以上是mysql的索引優化如何使用的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述:
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn