《巧用復合索引，有效降低系統(tǒng)IO》要點：
本文介紹了巧用復合索引，有效降低系統(tǒng)IO，希望對您有用。如果有疑問，可以聯(lián)系我們。

我們知道索引至關(guān)重要,合理的索引使用能夠在很大程度上改善數(shù)據(jù)庫的性能.然而很多人都會走入這樣一個誤區(qū)：走索引的SQL語句的性能一定比全表掃描好.真的是這樣嗎?今天我們將圍繞B*Tree索引的使用,解讀如何合理地使用索引,以及如何通過正確的索引來提高性能.

影響數(shù)據(jù)庫性能的因素主要有以下幾個：

• DB call
• Hard Parse+Soft Parse
• Wait Event
• I/O
• 不合理的設(shè)計與開發(fā)

在以上幾個因素中,我認為I/O的問題是最重要的,也是很多數(shù)據(jù)庫最普遍的性能問題.因此SQL優(yōu)化的核心就是用最少的I/O處理想要的數(shù)據(jù),提高核心SQL的處理速度,會帶來整個系統(tǒng)性能的提升.而跟I/O最相關(guān)的因素就是索引.

接下來我們通過真實案例來分析索引的使用.

首先創(chuàng)建測試表：

生成測試數(shù)據(jù)：

對上述的Tip進行說明：

Tip1：生成1年的日期數(shù)據(jù), 格式為 YYYYMMDD

Tip2：銷售類型別生成數(shù)據(jù),2個B2C,1個B2B

Tip3：使用笛卡爾積生成大量數(shù)據(jù)

接下來我們進行測試：

不使用索引的情況

說明：

Tip.4 清除BUFFER與SHARED POOL里的內(nèi)容(禁止在生產(chǎn)庫執(zhí)行)

Tip.5 為抓取實際執(zhí)行計劃

Tip.6 查看實際執(zhí)行計劃內(nèi)容

我們來看執(zhí)行計劃：

我們看到此時SQL走全表掃描,物理讀為36111.

然后創(chuàng)建索引,再次執(zhí)行以上SQL.

此時查看執(zhí)行計劃：

我們看到,此時走索引范圍掃描,物理讀為1322.

比之前提升了30倍左右.

接下來我們繼續(xù)測試：

以下是單列索引,對之前的查詢條件做了修改：

查看執(zhí)行計劃：

此時物理讀為3994.

創(chuàng)建復合索引,并再次執(zhí)行相同操作：

再次查看執(zhí)行計劃：

相同的操作邏輯讀降為原來的十分之一.說明復合索引的效率在合理的場景下效率更高.

但是索引真的是萬能的嗎?我們繼續(xù)測試

在沒有索引的情況下修改查詢條件執(zhí)行以下語句：

查看執(zhí)行計劃：

SQL走全表掃,物理讀36111.

創(chuàng)建索引,并執(zhí)行相同語句：

查看執(zhí)行計劃：

WTH!

物理讀竟然達到了40921?!比全表掃還多?!

這是什么原因呢?我們看上面的查詢條件就能知道,當要訪問的數(shù)據(jù)量占所有數(shù)據(jù)的比例較高的時候,此時全表掃描可以通過多塊讀加快速度,而索引則需要一條一條地進行檢索,因此性能反而變差.

所以,并不是所有使用索引的SQL性能都比全表掃描好.

前面分析到,在某些場景下,如何使用適當?shù)膹秃纤饕?能夠很大程度提高性能.那么接下來我們將通過真實案例來說明,如何創(chuàng)建高性能的復合索引.

假如對于測試表,經(jīng)常要進行操作的SQL語句包括以下幾個：

收集表使用的所有SQL,制作成表格用于分析：

如果為每一條SQL語句創(chuàng)建最佳索引,則列舉如下：

接下來我們使用排除法,來選擇最佳索引.

1、SQL-4可以被 X_2代替使用, 這時X_4去掉.或者,反過來X_4 代替 X_2使用也可以.但是,SQL-2 為點與線段的條件組合,如使用 X_4 效率不高.

2、對于剩下的三組,對比發(fā)現(xiàn),索引2和3相似,只是3包含更多的列.因此考慮索引多的話會對DML操作有負擔,所以最終合并為2個索引.

但是,其中 SQL-2 的 SQL寫法變換為以下寫法.

這樣處理后,創(chuàng)建兩個索引,一個是以SALE_YMD的單列索引,一個是SHOP_ID,SALE_TP,SALE_YHD的組合索引.

經(jīng)驗證,此時性能達到最佳.

文章來自微信公眾號：數(shù)據(jù)和云

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/3751.html