優化select count(*) from t1
分享下如何優化一條簡單的SQL語句，資料庫優化無止境，學習資料庫優化，我們先從一條最簡單的SQL語句開始。
select count(*) from t1;
這條語句雖然比較簡單，但很有玄機！對這句話執行的理解，反映了你對資料庫的理解深度！
好！我們開始我們的實驗。
建立實驗的使用的大表他t1
SQL> conn scott/tiger
已連線。
SQL> drop table t1 purge;
表已刪除。
SQL> create table t1 as select * from emp where 0=9;
表已建立。
SQL> insert into t1 select * from emp;
已建立14行。
SQL> insert into t1 select * from t1;
已建立14行。
SQL> /
已建立28行。
SQL> /
已建立56行。
SQL> /
已建立112行。
SQL> /
已建立224行。
SQL> /
已建立448行。
SQL> /
已建立896行。
SQL> /
已建立1792行。
SQL> /
已建立3584行。
SQL> /
已建立7168行。
SQL> /
已建立14336行。
SQL> /
已建立28672行。
SQL> /
已建立57344行。
SQL> commit;
提交完成。
收集統計資訊
SQL> execute dbms_stats.gather_table_stats('SCOTT','T1');
PL/SQL 過程已成功完成。
SQL> SET AUTOT TRACE EXP
SQL> SELECT COUNT(*) FROM T1;
執行計劃
--------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 124 (4)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 116K| 124 (4)| 00:00:02 |
-----------------------------------------------------
代價為124，執行的計劃為全表掃描。
那麼我們刪除表中所有的資料，是不是代價就能少了那？按照我們的正常思維是這樣的，但是oracle資料庫
沒有這麼做，沒有那麼智慧，畢竟它只是一個程式。
我們刪除T1的所有資料，我們來看看。
SQL> DELETE T1 WHERE DEPTNO=10;
已刪除24576行。
SQL> COMMIT;
提交完成。
SQL> execute dbms_stats.gather_table_stats('SCOTT','T1');
PL/SQL 過程已成功完成。
SQL> SELECT COUNT(*) FROM T1;
執行計劃
-----------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 123 (3)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 90286 | 123 (3)| 00:00:02 |
-----------------------------------------------------
我們看到了，代價為123幾乎沒有變化。
第一種優化的手段：
--1.降低高水位
SQL> alter table t1 move tablespace users;
表已更改。
SQL> execute dbms_stats.gather_table_stats('SCOTT','T1');
PL/SQL 過程已成功完成。
SQL> SELECT COUNT(*) FROM T1;
執行計劃
-----------------------------------------------------
| Id | Operation | Name | Rows |Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 102 (3)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 90667 | 102 (3)| 00:00:02 |
-----------------------------------------------------
代價為102，降低了，但是不多。
第二個手段是：
2.修改pctfree
SQL> alter table t1 pctfree 0;
表已更改。
SQL> alter table t1 move tablespace users;
表已更改。
SQL> execute dbms_stats.gather_table_stats('SCOTT','T1');
PL/SQL 過程已成功完成。
SQL> SELECT COUNT(*) FROM T1;
執行計劃
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 92 (4)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 91791 | 92 (4)| 00:00:02 |
-------------------------------------------------------------------
代價為92，降低了10%
第三種手段是：
--3.引數db_file_multiblock_read_count=64
該引數是一次性讀的資料庫塊數。一次性讀的塊數越多。資料庫的效能就越高。
第四種手段是：
--4.建立b*tree型別的索引
SQL> create index i1 on t1(empno);
索引已建立。
SQL> execute dbms_stats.gather_index_stats('SCOTT','I1');
PL/SQL 過程已成功完成。
SQL> SELECT COUNT(*) FROM T1;
執行計劃
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 92 (4)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 91791 | 92 (4)| 00:00:02 |
-------------------------------------------------------------------
為什麼沒有使用我們建立的索引，因為null不進入普通的索引！
SQL> alter table t1 modify(empno not null);
表已更改。
SQL> SELECT COUNT(*) FROM T1;
執行計劃
----------------------------------------------------------
Plan hash value: 129980005
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 (6)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| I1 | 91791 | 36 (6)| 00:00:01 |
----------------------------------------------------------------------
代價為36，我們的索引起到了很大的作用！
第五種手段是：
SQL> --5.使用並行查詢的特性
強制全表掃描，遮蔽索引
SQL> select /*+ full(t1) parallel(t1 2) */ COUNT(*) FROM T1;
執行計劃
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 51 (4)| 00:00:01 | | | |
| 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 | | 91791 | 51 (4)| 00:00:01 | Q1,00 | PCWC| |
| 6 | TABLE ACCESS FULL| T1 | 91791 | 51 (4)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------
並行度越高，代價越低
SQL> alter table t1 parallel 4;
表已更改。
也可以通過使用表的屬性來定義並行度，但是影響比較大，不如語句級別限制並行！
SQL> select count(*) from t1;
執行計劃
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQDistrib |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 (0)| 00:00:01 | | | |
| 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 | | 91791 | 25 (0)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| T1 | 91791 | 25 (0)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------
代價為25，代價比兩個的又少一半！
第六種手段是：
SQL> --6.建立點陣圖索引來避免全表掃描
SQL> create bitmap index i2 on t1(deptno);
索引已建立。
SQL> execute dbms_stats.gather_index_stats('SCOTT','I2');
PL/SQL 過程已成功完成。
SQL> select count(*) from t1;
執行計劃
----------------------------------------------------------
Plan hash value: 3738977131
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | BITMAP CONVERSION COUNT | | 91791 | 4 (0)| 00:00:01 |
| 3 | BITMAP INDEX FAST FULL SCAN| I2 | | | |
------------------------------------------------------------------------------
SQL> alter index i2 parallel 4;
索引已更改。
SQL> select count(*) from t1;
執行計劃
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | | |
| 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 | | 91791 | 2 (0)| 00:00:01 | Q1,00 |PCWC | |
| 6 | BITMAP CONVERSION COUNT | | 91791 | 2 (0)| 00:00:01 | Q1,00 |PCWP | |
| 7 | BITMAP INDEX FAST FULL SCAN| I2 | | | | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------
代價為2，原來為124，優化無止境呀！
只有你把握原理，一切盡在掌握！
要去開早會了，今天就到這裡吧，希望大家能學到一點有用的知識。在工作中能用到。
pxboracle@live.com
2014.08.12 08:24
share you knowledge with the world.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12798004/viewspace-1249555/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- count(*) 優化優化
- MySQL：SELECT COUNT 小結MySql
- MySQL優化COUNT()查詢MySql優化
- node express 在使用mysql執行SELECT count(*) from xx獲取總數取值格式問題ExpressMySql
- insert into select語句與select into from語句
- 百萬資料 mysql count（*）優化MySql優化
- select into from 和 insert into select 的用法和區別
- SQL-Hive中的Select From解析SQLHive
- 分散式查詢優化SERIAL_FROM_REMOTE分散式優化REM
- mysql count函式與分頁功能極限優化MySql函式優化
- mysql 大表中count() 使用方法以及效能優化.MySql優化
- SELECT COUNT(*) 會造成全表掃描？回去等通知吧
- 16、MySQL Case-索引key對select count(*)的影響MySql索引
- 還在用SELECT COUNT統計資料庫表的行數？Out了資料庫
- MySQL的COUNT語句--count(*)、 count(常量)、 count(列名)MySql
- Hibernate中不支援複雜子查詢from (select ……)解決方案
- springboot jap自定義原生sql 接收SELECT count(*) 的返回long型別結果Spring BootSQL型別
- MySQL的count(*)的優化，獲取千萬級資料表的總行數MySql優化
- 關於 mysql 中的 select * from table_a,table_b 的問題MySql
- count（*）、count（1）和count（列名）的區別
- count (*) 和 count (1) 和 count (列名) 區別
- count(*) 和 count(1)和count(列名)區別
- Oracle優化案例-select中to_clob對效能的影響（二十一）Oracle優化
- 不就是SELECT COUNT語句嗎，竟然能被面試官虐的體無完膚面試
- 圖解MySQL：count(*) 、count(1) 、count(主鍵欄位)、count(欄位）哪個效能最好？圖解MySql
- Django ORM效能優化之count和len方法的選擇（非常詳細推薦乾貨）DjangoORM優化
- [Oracle] “表中有資料，但select count(*)的結果為0”問題的解決辦法Oracle
- T1破大防
- T1掛分記
- 2024/7/2 T1
- [20180727]再論count(*)和count(1).txt
- 【Mysql原理與實踐】2020-08-03-景羅-MySQL中select count(col) 底層實現探索MySql
- 7.65 COUNT
- 佔用資源狂高的select min(bitmapped) from ts$ where dflmaxext =:1 and bitand(flags, 1024) = 1024APP
- CSP-J T1 poker
- select 下拉框用 Select select = new Select (element) 方法失敗
- MySQL：count(*) count(欄位) 實現上區別MySql
- SQL Server中count(*)和Count(1)的區別SQLServer
- select2初始化預設值