mysql的索引優(yōu)化怎么使用

對(duì)于數(shù)據(jù)庫(kù)來(lái)講系統(tǒng)表（如：sysobjects等）就是目錄，而標(biāo)字段上的索引就如同書本后面的關(guān)鍵字索引。

數(shù)據(jù)庫(kù)中，目錄（數(shù)據(jù)字典）和索引的區(qū)別：目錄縱向、索引橫向。

一、影響索引作用的因素

區(qū)分度（檢索比率）

優(yōu)化器根據(jù)統(tǒng)計(jì)信息來(lái)生成執(zhí)行計(jì)劃，如果數(shù)據(jù)庫(kù)沒(méi)有收集索引的統(tǒng)計(jì)信息，優(yōu)化器就無(wú)從下手，只能按部就班，通過(guò)全表掃描來(lái)執(zhí)行查詢。所以，新創(chuàng)建的索引需要重新運(yùn)行統(tǒng)計(jì)，否則索引無(wú)效。

舉一個(gè)例子，有個(gè)表TABLE1，其中有一個(gè)字段COL1取值是“1”、“2”、“3”三種，運(yùn)行統(tǒng)計(jì)的結(jié)果是告訴數(shù)據(jù)庫(kù)TABLE1中的數(shù)據(jù)其中字段COL1的各種取值所占的比重。示意如下：

“1” － 12%；

“2” － 66%；

“3” － 22%。

假設(shè)還有個(gè)字段COL2取值和數(shù)據(jù)所占的百分比如下：

“A” － 50%；

“B” － 50%。

則查詢語(yǔ)句1：

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

數(shù)據(jù)庫(kù)優(yōu)化器會(huì)優(yōu)先選擇字段COL1上的索引來(lái)定位表中的數(shù)據(jù)，因?yàn)橥ㄟ^(guò)COL1上的索引就可以將結(jié)果集迅速定位在一個(gè)小范圍內(nèi)12%。而相反的，對(duì)于查詢語(yǔ)句2：

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

數(shù)據(jù)庫(kù)會(huì)優(yōu)先選擇COL2上的索引，因?yàn)閷?duì)于語(yǔ)句2的查詢條件COL2上的索引具有更好的區(qū)分度。

從上面可以看出，數(shù)據(jù)庫(kù)的優(yōu)化器通常會(huì)優(yōu)先選擇區(qū)分度較高的索引（針對(duì)于查詢條件，條件不同選擇的索引可能不同）。

數(shù)據(jù)庫(kù)里的數(shù)據(jù)是變化的，所以某個(gè)時(shí)候采集的統(tǒng)計(jì)信息，過(guò)一段時(shí)間后可能會(huì)過(guò)時(shí)，甚至誤導(dǎo)數(shù)據(jù)庫(kù)優(yōu)化器，這樣同樣會(huì)造成運(yùn)行性能的低下。所以除了，最初建立索引時(shí)需要運(yùn)行統(tǒng)計(jì)，在表中的數(shù)據(jù)發(fā)生變化時(shí)也需要運(yùn)行統(tǒng)計(jì)。經(jīng)驗(yàn)：當(dāng)表中數(shù)據(jù)量變化達(dá)到10%時(shí)，需要重新運(yùn)行統(tǒng)計(jì)。

二、聚集度

范圍掃描

表大�。�

小型表

中大型表

超大型表

業(yè)務(wù)類型

OLTP和OLAP

函數(shù)與索引

函數(shù)，like語(yǔ)句。。。

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

like ‘QQQ% vs. like ‘%QQQ’

索引開銷

性能利器

雙刃劍

索引對(duì)插入操作的影響（Oracle）

索引對(duì)插入操作的影響（MySQL）

比較索引與促發(fā)器對(duì)性能的影響

索引總結(jié)

使用索引實(shí)現(xiàn)關(guān)鍵數(shù)據(jù)的高效訪問(wèn)。但是需要知道每個(gè)索引都會(huì)給數(shù)據(jù)庫(kù)更新帶來(lái)額外的開銷。這就意味著，低效的索引會(huì)給數(shù)據(jù)庫(kù)帶來(lái)災(zāi)難。

對(duì)于數(shù)據(jù)庫(kù)，我們必須關(guān)注關(guān)鍵數(shù)據(jù)的讀取，為他們提供最高效的訪問(wèn)路徑。對(duì)此，基本策略就是建立索引。在索引提供高效訪問(wèn)的同時(shí)，也帶來(lái)了額外的系統(tǒng)開銷。開銷分為磁盤空間的開銷和處理器開銷。下面我們討論一下處理器開銷。每當(dāng)在表中插入或刪除記錄時(shí)，該表的所有索引必須進(jìn)行相應(yīng)調(diào)整。每當(dāng)對(duì)已建立索引的字段進(jìn)行更新時(shí)，這種調(diào)整也會(huì)發(fā)生。舉例子說(shuō)，如果在未建立索引的表中插入數(shù)據(jù)需要100個(gè)單位時(shí)間，那么每增加一個(gè)索引就會(huì)增加100到250個(gè)單位時(shí)間。有趣的是，維護(hù)索引的開銷與簡(jiǎn)單觸發(fā)器帶來(lái)的開銷大致相當(dāng)。

在建立索引前線介紹一些最通俗的信息，這些信息來(lái)自developWorks，列出這些信息是因?yàn)槲矣X(jué)得這些信息通常情況下是值得參考的：

1.當(dāng)要在一個(gè)合理的時(shí)間內(nèi)結(jié)束查詢時(shí)，應(yīng)避免添加索引，因?yàn)樗饕龝?huì)降慢更新操作的速度并消耗額外的空間。有時(shí)候還可能存在覆蓋好幾個(gè)查詢的大型索引。

1.基數(shù)較大的列很適合用來(lái)做索引。

3.考慮到管理上的開銷，應(yīng)避免在索引中使用多于5個(gè)的列。

4.對(duì)于多列索引，將查詢中引用最多的列放在定義的前面。

5.避免添加與已有的索引相似的索引。因?yàn)檫@樣會(huì)給優(yōu)化器帶來(lái)更多的工作，并且會(huì)降慢更新操作的速度。相反，我們應(yīng)該修改已有的索引，使其包含附加的列。例如，假設(shè)在一個(gè)表的 (c1,c2)上有一個(gè)索引i1。您注意到查詢中使用了"wherec2=?"，于是又創(chuàng)建一個(gè)(c2)上的索引i2。但是這個(gè)相似的索引沒(méi)有添加任何東西，它只是i1的冗余，而現(xiàn)在反而成了額外的開銷。

6.如果表是只讀的，并且包含很多的行，那么可以嘗試定義一個(gè)索引，通過(guò)CREATE INDEX中的INCLUDE子句使該索引包含查詢中引用的所有列（被INCLUDE子句包含的列并不是索引的一部分，而只是作為索引頁(yè)的一部分來(lái)存儲(chǔ)，以避免附加的數(shù)據(jù)FETCHES）。

對(duì)于數(shù)據(jù)倉(cāng)庫(kù)（查詢系統(tǒng)數(shù)據(jù)庫(kù)）可以建立較多的索引（索引和數(shù)據(jù)的比例可以是1：1）。

決定是否使用索引，可以重點(diǎn)考慮檢索比率。即，判斷索引有效性的依據(jù)，就使用鍵值作唯一性條件檢索出的數(shù)據(jù)的百分比。百分比越低，索引越有效。做出這個(gè)論斷的前提是一些假設(shè)，如磁盤訪問(wèn)的相關(guān)性能。

索引鍵值相關(guān)記錄的物理位置是否相鄰也很重要，因?yàn)槭峭ㄟ^(guò)塊來(lái)操作數(shù)據(jù)的。建立了索引之后，如果索引鍵所指向的記錄散布于整個(gè)表中，即使這些記錄在表中占的比率很小，但因?yàn)樗鼈兎稚⒃谡麄�(gè)磁盤上，所以索引的性能就會(huì)大打折扣。

另外值得注意的是，函數(shù)和類型轉(zhuǎn)換可能導(dǎo)致索引失效。

以上就是mysql的索引優(yōu)化如何使用的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章！