資料庫索引的使用,資料庫索引使用

來源:互聯網
上載者:User

資料庫索引的使用,資料庫索引使用

        今天發現一個問題,問題大概是這樣的,查詢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資料類型的列不應該增加索引。這是因為,這些列的資料量要麼相當大,要麼取值很少。第四,當修改效能遠遠大於檢索效能時,不應該建立索引。這是因為,修改效能和檢索效能是互相矛盾的。當增加索引時,會提高檢索效能,但是會降低修改效能。當減少索引時,會提高修改效能,降低檢索效能。因此,當修改效能遠遠大於檢索效能時,不應該建立索引。

 

好了。問題也解決完了,也學到了不少的東西,這次的索引課認識深刻了。



相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.