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

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

影響數據庫性能的因素主要有以下幾個：

• DB call
• Hard Parse+Soft Parse
• Wait Event
• I/O
• 不合理的設計與開發

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

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

首先創建測試表：

生成測試數據：

對上述的Tip進行說明：

Tip1：生成1年的日期數據, 格式為 YYYYMMDD

Tip2：銷售類型別生成數據,2個B2C,1個B2B

Tip3：使用笛卡爾積生成大量數據

接下來我們進行測試：

不使用索引的情況

說明：

Tip.4 清除BUFFER與SHARED POOL里的內容(禁止在生產庫執行)

Tip.5 為抓取實際執行計劃

Tip.6 查看實際執行計劃內容

我們來看執行計劃：

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

然后創建索引,再次執行以上SQL.

此時查看執行計劃：

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

比之前提升了30倍左右.

接下來我們繼續測試：

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

查看執行計劃：

此時物理讀為3994.

創建復合索引,并再次執行相同操作：

再次查看執行計劃：

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

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

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

查看執行計劃：

SQL走全表掃,物理讀36111.

創建索引,并執行相同語句：

查看執行計劃：

WTH!

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

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

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

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

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

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

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

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

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

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

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

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

經驗證,此時性能達到最佳.

文章來自微信公眾號：數據和云

轉載請注明本頁網址：
http://www.fzlkiss.com/jiaocheng/3751.html