建立聯合函式索引解決top sql效能問題
今天捕獲到一條top sql,其大致內容如下
select count(*) from archive partition (1105) where done = 2 and name like '%@qq.com';
其中archive為分割槽表，按月分割槽，分割槽鍵為create_time列；其總資料為6千萬，分割槽1105資料有600多萬，且每日遞增；分割槽1104資料有1千萬，預計到月底1105資料也要有千萬
再來看兩個查詢列，done的選擇性極差，其distinct值只有3個；而name的distinct值有20多萬，其實就是使用者的郵箱地址；兩個列上目前都沒有索引；
目前該sql的執行計劃如下，邏輯讀有60多萬
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 164K (1)| 00:32:59 | | |
| 1 | SORT AGGREGATE | | 1 | 23 | | | | |
| 2 | PARTITION RANGE SINGLE| | 243K| 5460K| 164K (1)| 00:32:59 | 6 | 6 |
|* 3 | TABLE ACCESS FULL | archive | 243K| 5460K| 164K (1)| 00:32:59 | 6 | 6 |
---------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
605310 consistent gets
530101 physical reads
0 redo size
517 bytes sent via SQL*Net to client
487 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
sql結構極為簡單，在無法修改應用邏輯的前提下，為了降低cost，只能考慮建立索引，而done列肯定是不合適的；
name列的選擇性倒是還可以，可是使用的是模糊查詢，而且%位於前面，直接建立索引顯然是不合適的，前面提過name是使用者的郵箱地址，而郵箱地址的格式是有規律的，
為*****@.**;我們可以透過instr獲取@的位置，聯合substr可以得到@.**部分的內容，即substr(name,instr(name,'@')，於是name like '%@qq.com'可以改寫為
substr(name,instr(name,'@')='@qq.com',現在則可以基於substr(name,instr(name,'@')建立函式索引
SQL> Create index ind_func_name on archive(substr(name,instr(name,'@'))) online;
Index created
建立後的執行計劃
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 112K (1)| 00:22:34 | | |
| 1 | SORT AGGREGATE | | 1 | 23 | | | | |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| archive | 48626 | 1092K| 112K (1)| 00:22:34 | 6 | 6 |
|* 3 | INDEX RANGE SCAN | ind_func_name | 247K| | 8 (25)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
192078 consistent gets
0 physical reads
0 redo size
517 bytes sent via SQL*Net to client
487 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
執行代價有所降低，但是此時索引不是local的，執行計劃中也沒有出現partition字樣，重新建立一個local索引
SQL> Create index ind_func_name on archive(substr(name,instr(name,'@'))) online local;
再看執行計劃
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 12490 (1)| 00:02:30 | | |
| 1 | SORT AGGREGATE | | 1 | 23 | | | | |
| 2 | PARTITION RANGE SINGLE | | 48626 | 1092K| 12490 (1)| 00:02:30 | 6 | 6 |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID| archive | 48626 | 1092K| 12490 (1)| 00:02:30 | 6 | 6 |
|* 4 | INDEX RANGE SCAN | ind_func_name | 25856 | | 3 (0)| 00:00:01 | 6 | 6 |
--------------------------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
106 recursive calls
0 db block gets
171296 consistent gets
1485 physical reads
0 redo size
517 bytes sent via SQL*Net to client
487 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到consistent gets建立索引前為605310，建立函式索引後下降為192078，函式索引修改成分割槽索引後變為171296；
雖然效能得到了提升，但是執行代價仍然有點大，此時可以考慮組合索引了
SQL> create index ind_func_name_done on archive(substr(name,instr(name,'@')),done) online local;
Index created.
再看一把執行計劃
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 3 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 23 | | | | |
| 2 | PARTITION RANGE SINGLE| | 48626 | 1092K| 3 (0)| 00:00:01 | 6 | 6 |
|* 3 | INDEX RANGE SCAN | ind_func_name_done | 48626 | 1092K| 3 (0)| 00:00:01 | 6 | 6 |
-------------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
1186 consistent gets
1181 physical reads
0 redo size
517 bytes sent via SQL*Net to client
487 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
這樣一來，sql的consistent gets驟降到1186，而它也從top sql的佇列中消失了
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15480802/viewspace-695166/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 不能建立降序索引的問題的解決索引
- [20180509]函式索引問題.txt函式索引
- 瞭解GaussDB效能調優之隱式轉換，解決慢SQL問題SQL
- SQL SERVER資料庫datediff函式引發的效能問題SQLServer資料庫函式
- [20190918]關於函式索引問題.txt函式索引
- mysql 建立和刪除聯合索引MySql索引
- MySQL的GROUP_CONCAT函式輕鬆解決多表聯查的聚合問題MySql函式
- sql多參問題解決SQL
- 深入sql多表差異化聯合查詢的問題詳解SQL
- MySQL建立表的時候建立聯合索引的方法MySql索引
- SQL優化案例-自定義函式索引（五）SQL優化函式索引
- SQL Server 2016升級遷移過程中效能問題解決案例SQLServer
- MySQL點陣圖索引解決使用者畫像問題(簡化建立流程)MySql索引
- 關於聯合索引，範圍查詢，時間列索引的幾個問題索引
- 解決WPF中過載Window.OnRender函式失效問題函式
- 解決vscode c語言中scanf函式的輸入問題VSCodeC語言函式
- es6學習_箭頭函式解決this指向問題函式
- 函式計算｜如何使用層解決依賴包問題？函式
- 遺傳演算法解決函式最佳化問題演算法函式
- 【ASK_ORACLE】由於索引分裂而產生的效能問題的解決方案Oracle索引
- 【TUNE_ORACLE】列出可以建立組合索引的SQL(回表訪問少數字段)的SQL參考Oracle索引SQL
- SQL最佳化案例-自定義函式索引（五）SQL函式索引
- 解決吞吐效能問題時的思路
- 將函式作為引數傳遞解決非同步問題函式非同步
- 用Promise建構函式來解決地獄回撥問題Promise函式
- 題解 P10249【【模板】多項式複合函式】函式
- 用SQL解決有向圖問題(轉)SQL
- 【OracleEBS】 訂單暫掛問題sql解決OracleSQL
- Spark SQL中出現 CROSS JOIN 問題解決SparkSQLROS
- 聯合索引和多個單列索引使用中的索引命中情況及索引建立原則索引
- 解決winform窗體重複建立問題ORM
- 優化-mysql子查詢索引失效問題解決優化MySql索引
- vue專案使用lodash節流防抖函式問題與解決Vue函式
- Unity容器建構函式引數迴圈引用問題及解決Unity函式
- 如何捕獲問題SQL解決過度CPU消耗的問題SQL
- MySQL的聯合索引MySql索引
- Oracle優化案例-join列索引缺失導致的sql效能問題（二十六）Oracle優化索引SQL
- 記錄一次SQL函式和優化的問題SQL函式優化
- 如何解決TOP-K問題