資料庫索引的使用,資料庫索引使用
今天發現一個問題,問題大概是這樣的,查詢interface的資訊,在本地使用本地的資料庫訪問沒有問題,但是發布到伺服器上以後訪問速度就特別的忙,需要5分鐘左右才能返回資料,這肯定是無法讓人接受的,剛開始以為是伺服器效能的問題,為了驗證就把伺服器上的Database Backup到本地,發現本地的速度也馬上慢了下來,到底是什麼問題的。看了一下查詢interface的sql語句不禁嚇了一跳:
select distinct a.id ,a.name ,a.interfacecode ,a.version ,a.synasyn ,a.frequence ,a.solutionmodelid ,a.owner ,a.createtime ,a.status ,a.description ,p.id as "project.id" ,p.name as "project.name" ,p.pcategory as "project.pcategory" ,r.name as "release.name" , r.id as "release.id" ,b.name as "middlewarename" ,l1.name as "sourcesystem" ,l2.name as "targetsystem" , f.name as "messageformat1" ,k.name as "messageformat2" ,g.name as "messagename1" ,l.name as "messagename2" ,m.id as "interfacemapid" ,m.category as category , c.bos as "bos1" ,h.bos as "bos2" ,sm.name as "solutionModelName" ,a.lastmodifytime as "lastModifiedAt" ,df.name as "scenario" ,a.iscurrent as "iscurrent" ,m.reviewstatus as "reviewStatus" ,m.reviewedby as "reviewedBy" ,m.reviewedat as "reviewedAt" ,m.trackleader FROM interfacemapping m LEFT JOIN project p ON m.projectid = p.id LEFT JOIN realse r ON p.realseid = r.id LEFT JOIN integrationinterface a ON m.interfaceid = a.id LEFT JOIN logicsystem b ON a.middleware = b.id LEFT JOIN interfacedetail c ON c.interfaceid = a.id AND UCASE(c.flowflag) = 'START' LEFT JOIN logicsystem d ON d.id = c.logicsystemid LEFT JOIN messageformat f ON f.id = c.messageformatid LEFT JOIN messagedic g ON g.id = c.messagename LEFT JOIN interfacedetail h ON h.interfaceid = a.id AND UCASE(h.flowflag) = 'END' LEFT JOIN logicsystem j ON j.id = h.middleware LEFT JOIN messageformat k ON k.id = h.messageformatid LEFT JOIN messagedic l ON l.id = h.messagename LEFT JOIN interfacedetail u ON u.interfaceid = a.id AND UCASE(u.flowflag) = 'MID' LEFT JOIN messageformat n ON n.id = u.messageformatid LEFT JOIN messagedic z on z.id = u.messagename LEFT JOIN logicsystem l1 ON l1.id = a.sourcesystemid LEFT JOIN logicsystem l2 ON l2.id = a.targetsystemid lEFT JOIN solutionmodel sm ON sm.id = a.solutionmodelid LEFT JOIN DATAFLOWINFO df ON df.ID = a.SCENARIOID
我想你也一定被嚇到了。但是這也只是其中的一部分,還有動態sql我沒有貼出來。是因為一堆表串連所以速度有影響嗎?決定下手調一下這個sql語句,使用的方法就是逐個的串連表。當我串連到LEFT JOINinterfacedetail c 時,查詢速度竟然是20秒。就是這一個表導致的表之間串連的速度慢的嗎?
然後我就開始分析為什麼原來本地的資料庫時資料快,我發現原來本地的庫中interfacedetail中是沒有資料的。而現在的庫中表中有8000條資料,這是導致查詢慢的原因嗎?8000條以上的資料就不適合做串連查詢了嗎?
但是我突然想到原來在db2中一樣的資料,為什麼查詢就挺快的。首先我是驗證了。當前mysql中每一個表中的資料和db2中的資料完全相同。結果發現完全相同,但是db2中執行的速度是0.4秒,完全是可以接受的。問題又來了。這就是企業級資料庫與普通資料庫的區別?
但這個結果還是不能讓我相信的。我就查了其他的資料,發現有人說索引可以提高表之間串連的速度,結果我還是真的發現db2中有interfacedetail.interfaceid的索引。我在mysql中加入同樣的索引:
CREATE INDEX T_PI_VLO_NAAE_IDX1 ONinterfacedetail (interfaceid);
然後再直接上面的語句,直接速度馬上到了0.5秒以內。。問題算是解決了。但是究竟索引為啥又這麼大的作用,索引是什麼呢?
為什麼要建立索引呢?這是因為,建立索引可以大大提高系統的效能。
第一,通過建立唯一性索引,可以保證資料庫表中每一行資料的唯一性。
第二,可以大大加快資料的檢索速度,這也是建立索引的最主要的原因。
第三,可以加速表和表之間的串連,特別是在實現資料的參考完整性方面特別有意義。
第四,在使用分組和排序子句進行資料檢索時,同樣可以顯著減少查詢中分組和排序的時間。
第五,通過使用索引,可以在查詢的過程中,使用最佳化隱藏器,提高系統的效能。
也許會有人要問:增加索引有如此多的優點,為什麼不對錶中的每一個列建立一個索引呢?這種想法固然有其合理性,然而也有其片面性。雖然,索引有許多優點,但是,為表中的每一個列都增加索引,是非常不明智的。這是因為,增加索引也有許多不利的一個方面。
第一,建立索引和維護索引要耗費時間,這種時間隨著資料量的增加而增加。
第二,除了資料表占資料空間之外,每一個索引還要佔一定的物理空間,如果要建立聚簇索引,那麼需要的空間就會更大。
第三,當對錶中的資料進行增加、刪除和修改的時候,索引也要動態維護,這樣就降低了資料的維護速度。
索引是建立在資料庫表中的某些列的上面。因此,在建立索引的時候,應該仔細考慮在哪些列上可以建立索引,在哪些列上不能建立索引。一般來說,應該在這些列上建立索引,例如:在經常需要搜尋的列上,可以加快搜尋的速度;在作為主鍵的列上,強制該列的唯一性和組織表中資料的排列結構;在經常用在串連的列上,這些列主要是一些外鍵,可以加快串連的速度;在經常需要根據範圍進行搜尋的列上建立索引,因為索引已經排序,其指定的範圍是連續的;在經常需要排序的列上建立索引,因為索引已經排序,這樣查詢可以利用索引的排序,加快排序查詢時間;在經常使用在WHERE子句中的列上面建立索引,加快條件的判斷速度。
同樣,對於有些列不應該建立索引。一般來說,不應該建立索引的的這些列具有下列特點:第一,對於那些在查詢中很少使用或者參考的列不應該建立索引。這是因為,既然這些列很少使用到,因此有索引或者無索引,並不能提高查詢速度。相反,由於增加了索引,反而降低了系統的維護速度和增大了空間需求。第二,對於那些只有很少資料值的列也不應該增加索引。這是因為,由於這些列的取值很少,例如人事表的性別列,在查詢的結果中,結果集的資料行佔了表中資料行的很大比例,即需要在表中搜尋的資料行的比例很大。增加索引,並不能明顯加快檢索速度。第三,對於那些定義為text,image和bit資料類型的列不應該增加索引。這是因為,這些列的資料量要麼相當大,要麼取值很少。第四,當修改效能遠遠大於檢索效能時,不應該建立索引。這是因為,修改效能和檢索效能是互相矛盾的。當增加索引時,會提高檢索效能,但是會降低修改效能。當減少索引時,會提高修改效能,降低檢索效能。因此,當修改效能遠遠大於檢索效能時,不應該建立索引。
好了。問題也解決完了,也學到了不少的東西,這次的索引課認識深刻了。