提高count查詢速度
顯然昨天做的分割槽並未提升count(1)的查詢速度，怎樣都避免不了全表掃描，後來開始在網上搜尋，發現的確有很多方法，所以準備挨個嘗試
首先正常查詢一次
3分多鐘，6382 cost
1.嘗試bitmap索引，還是以id列做索引
首先刪掉之前的btree索引
drop index trans_tmp_id;
唉，這就執行了好久，然後建立bitmap索引
create bitmap index bitmap_idx_id on trans_tmp(id) online;
因為應用還在跑著，所以採用online的方式，漫長等待後，進行查詢
近4分鐘，而且cost 29563，怎麼更高了呢？繼續在這上嘗試
2.更改bitmap索引並行
首先正常查詢一次
10:19:30 SQL> select count(1) from trans_tmp;
COUNT(1)
----------
8997943
已用時間: 00: 03: 23.76
執行計劃
----------------------------------------------------------
Plan hash value: 55114451
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6382 (5)| 00:01:17 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| TRANS_TMP_ID | 11M| 6382 (5)| 00:01:17 |
-------------------------------------------------------------------------------------
統計資訊
----------------------------------------------------------
231 recursive calls
0 db block gets
28019 consistent gets
22734 physical reads
0 redo size
411 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
3分多鐘，6382 cost
1.嘗試bitmap索引，還是以id列做索引
首先刪掉之前的btree索引
drop index trans_tmp_id;
唉，這就執行了好久，然後建立bitmap索引
create bitmap index bitmap_idx_id on trans_tmp(id) online;
因為應用還在跑著，所以採用online的方式，漫長等待後，進行查詢
10:43:52 SQL> select count(1) from trans_tmp;
COUNT(1)
----------
8997943
已用時間: 00: 03: 57.18
執行計劃
----------------------------------------------------------
Plan hash value: 3875526975
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29563 (1)| 00:05:55 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | BITMAP CONVERSION COUNT | | 11M| 29563 (1)| 00:05:55 |
| 3 | BITMAP INDEX FAST FULL SCAN| BITMAP_IDX_ID | | | |
---------------------------------------------------------------------------------------
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
40454 consistent gets
32655 physical reads
0 redo size
411 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
2.更改bitmap索引並行
10:52:37 SQL> alter index bitmap_idx_id parallel 4;
索引已更改。
10:54:40 SQL> select count(1) from trans_tmp;
已用時間: 00: 03: 40.23
執行計劃
----------------------------------------------------------
Plan hash value: 408296253
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29563 (1)| 00:05:55 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 11M| 29563 (1)| 00:05:55 | Q1,00 | PCWC | |
| 6 | BITMAP CONVERSION COUNT | | 11M| 29563 (1)| 00:05:55 | Q1,00 | PCWP | |
| 7 | BITMAP INDEX FAST FULL SCAN| BITMAP_IDX_ID | | | | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------
統計資訊
----------------------------------------------------------
939 recursive calls
3 db block gets
33540 consistent gets
32656 physical reads
628 redo size
411 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
14 sorts (memory)
0 sorts (disk)
1 rows processed
時間只減少了10幾秒，cost 29563 一點沒變；既然並行了索引，那麼也並行表吧
時間只減少了10幾秒，cost 29563 一點沒變；既然並行了索引，那麼也並行表吧
10:58:32 SQL> alter table trans_tmp parallel 4;
表已更改。
去掉了查詢部分，只看執行計劃，這回cost 8212，下來不少
3.禁用索引，並行掃描全表
11:01:08 SQL> select count(1) from trans_tmp;
已用時間: 00: 00: 01.03
執行計劃
----------------------------------------------------------
Plan hash value: 408296253
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8212 (1)| 00:01:39 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 11M| 8212 (1)| 00:01:39 | Q1,00 | PCWC | |
| 6 | BITMAP CONVERSION COUNT | | 11M| 8212 (1)| 00:01:39 | Q1,00 | PCWP | |
| 7 | BITMAP INDEX FAST FULL SCAN| BITMAP_IDX_ID | | | | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------
去掉了查詢部分，只看執行計劃，這回cost 8212，下來不少
3.禁用索引，並行掃描全表
11:03:15 SQL> select /*+full(trans_tmp)parallel(trans_tmp 2)*/ count(1) from trans_tmp;
已用時間: 00: 00: 00.17
執行計劃
----------------------------------------------------------
Plan hash value: 1765477136
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 93757 (1)| 00:18:46 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 11M| 93757 (1)| 00:18:46 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| TRANS_TMP | 11M| 93757 (1)| 00:18:46 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------
cost 93757 看來很高，不過之前已經把並行度改成了4，那麼直接查詢試試看
11:06:45 SQL> select /*+full(trans_tmp)*/ count(1) from trans_tmp;
已用時間: 00: 00: 00.96
執行計劃
----------------------------------------------------------
Plan hash value: 1765477136
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46879 (1)| 00:09:23 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 11M| 46879 (1)| 00:09:23 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| TRANS_TMP | 11M| 46879 (1)| 00:09:23 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------
cost 46897 降了一半左右,既然bitmap索引效果不大，刪掉，然後重建btree索引繼續嘗試並行
4.btree索引並行
cost 46897 降了一半左右,既然bitmap索引效果不大，刪掉，然後重建btree索引繼續嘗試並行
4.btree索引並行
11:03:34 SQL> drop index bitmap_idx_id;
索引已刪除。
11:07:11 SQL> create index trans_tmp_id on trans_tmp(id) online;
索引已建立。
11:13:51 SQL> alter index trans_tmp_id parallel 4;
索引已更改。
已用時間: 00: 00: 00.32
11:27:44 SQL> select count(1) from trans_tmp;
已用時間: 00: 00: 00.42
執行計劃
----------------------------------------------------------
Plan hash value: 1765477136
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46879 (1)| 00:09:23 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 11M| 46879 (1)| 00:09:23 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| TRANS_TMP | 11M| 46879 (1)| 00:09:23 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------
全表掃描了？gather stat了一下還是全表，那麼就強制走索引吧
11:32:03 SQL> select /*+rule*/ count(1) from trans_tmp;
已用時間: 00: 00: 00.18
執行計劃
----------------------------------------------------------
Plan hash value: 1765477136
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46879 (1)| 00:09:23 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 11M| 46879 (1)| 00:09:23 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| TRANS_TMP | 11M| 46879 (1)| 00:09:23 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------
呃，竟然一樣，好吧，我準備繼續折騰
呃，竟然一樣，好吧，我準備繼續折騰
11:35:43 SQL> set autotrace on
11:35:58 SQL> select count(1) from trans_tmp;
COUNT(1)
----------
8997943
已用時間: 00: 02: 35.10
執行計劃
----------------------------------------------------------
Plan hash value: 1765477136
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46879 (1)| 00:09:23 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 11M| 46879 (1)| 00:09:23 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| TRANS_TMP | 11M| 46879 (1)| 00:09:23 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------
統計資訊
----------------------------------------------------------
273 recursive calls
0 db block gets
769352 consistent gets
768853 physical reads
0 redo size
411 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
並行掃描全表用時2分35，至少是比最開始快了一些
最後再次嘗試一下bitmap
並行掃描全表用時2分35，至少是比最開始快了一些
最後再次嘗試一下bitmap
11:38:44 SQL> drop index trans_id;
索引已刪除。
已用時間: 00: 00: 01.31
11:41:31 SQL> create bitmap index bitmap_id_idx on trans_tmp(id) online;
11:59:20 SQL> set autotrace trace exp
11:59:36 SQL> select count(1) from trans_tmp;
已用時間: 00: 00: 00.15
執行計劃
----------------------------------------------------------
Plan hash value: 4075861796
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29563 (1)| 00:05:55 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | BITMAP CONVERSION COUNT | | 11M| 29563 (1)| 00:05:55 |
| 3 | BITMAP INDEX FAST FULL SCAN| BITMAP_ID_IDX | | | |
---------------------------------------------------------------------------------------
11:59:41 SQL> alter index bitmap_id_idx parallel 4;
索引已更改。
已用時間: 00: 00: 00.23
11:59:55 SQL> select count(1) from trans_tmp;
已用時間: 00: 00: 00.23
執行計劃
----------------------------------------------------------
Plan hash value: 3303238536
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8212 (1)| 00:01:39 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 11M| 8212 (1)| 00:01:39 | Q1,00 | PCWC | |
| 6 | BITMAP CONVERSION COUNT | | 11M| 8212 (1)| 00:01:39 | Q1,00 | PCWP | |
| 7 | BITMAP INDEX FAST FULL SCAN| BITMAP_ID_IDX | | | | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------
11:59:58 SQL> set autot on
12:00:12 SQL> select count(1) from trans_tmp;
COUNT(1)
----------
8997943
已用時間: 00: 03: 39.95
執行計劃
----------------------------------------------------------
Plan hash value: 3303238536
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8212 (1)| 00:01:39 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 11M| 8212 (1)| 00:01:39 | Q1,00 | PCWC | |
| 6 | BITMAP CONVERSION COUNT | | 11M| 8212 (1)| 00:01:39 | Q1,00 | PCWP | |
| 7 | BITMAP INDEX FAST FULL SCAN| BITMAP_ID_IDX | | | | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------
統計資訊
----------------------------------------------------------
229 recursive calls
0 db block gets
34045 consistent gets
32656 physical reads
0 redo size
411 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
雖然cost很低，但是執行時間卻很長，看來還是隻能並行全表掃描了
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25680865/viewspace-714224/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 提高sql查詢速度SQL
- 提高查詢速度使用materizlizedZed
- 提高查詢速度方法總結
- 優化sql提高查詢速度優化SQL
- 使用子查詢可提升 COUNT DISTINCT 速度 50 倍
- 提高跨庫查詢速度，你只需一個Smartbi
- QL Server 百萬級資料提高查詢速度的方法Server
- 提高ORACLE資料庫的查詢統計速度（轉）Oracle資料庫
- MySQL優化COUNT()查詢MySql優化
- 提高SQL查詢效能SQL
- 【索引】Oracle查詢指定索引提高查詢效率索引Oracle
- 優化sql查詢速度優化SQL
- db_file_multiblock 設定多塊讀取來提高查詢速度BloC
- MYSQL count標量子查詢改left joinMySql
- hibernate異常之--count查詢異常
- Oracle提高查詢效率的方法Oracle
- 檔案上傳速度查詢方法
- 最佳化拼多多關鍵詞搜尋介面：提高查詢響應速度的技巧
- 使用Bulk Collect提高Oracle查詢效率Oracle
- 面試官這樣問你：為什麼MySQL新增索引後就可以提高查詢速度面試MySql索引
- ClickHouse為什麼查詢速度快？
- Oracle臨時表 優化查詢速度Oracle優化
- 提高MSSQL資料庫效能(1)對比count(*) 和 替代count(*)SQL資料庫
- 用hash cluster表提高查詢效能 (一)
- 提高mysql查詢效率的六種方法MySql
- 如何將 MySQL 查詢速度提升 300 倍MySql
- Oracle臨時表最佳化查詢速度Oracle
- hibernate跟jdbc的查詢速度相差10？？？JDBC
- Oracle臨時表 最佳化查詢速度Oracle
- MySQL加速查詢速度的獨門武器：查詢快取(QueryCache)MySql快取
- 提高 Laravel Eloquent 查詢的5個小技巧Laravel
- Gbase8d如何提高查詢效能？
- 請教如何提高查詢系統的效能？
- 加快dba_hist_active_sess_history的查詢速度
- 利用Redis cache優化app查詢速度實踐Redis優化APP
- 找出Mysql查詢速度慢的SQL語句MySql
- 處理上百萬條的資料庫如何提高處理查詢速度資料庫
- mysql group by 執行原理及千萬級別count 查詢最佳化MySql