count(*)優化
SQL> create table t1 as select * from dba_objects;
表已建立。
SQL> create index idx1_object_id on t1(object_id);
索引已建立。
SQL> select count(*) from t1;
COUNT(*)
----------
49821
思考一個問題,count(*)是否走索引?驗證一下
SQL> select count(*) from t1;
COUNT(*)
----------
49821
已用時間: 00: 00: 00.01
執行計劃
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 158 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 47795 | 158 (2)| 00:00:02 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
690 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
為什麼不走索引?索引列是不儲存空值的,索引並不知道表是否有空值,如果表有空值,走索引,count(*)將不準確.
如果想走索引,可以有以下兩種解決方法:
1)明確索引列非空
SQL> select count(*)from t1 where object_id is not null;
COUNT(*)
----------
49821
已用時間: 00: 00: 00.01
執行計劃
----------------------------------------------------------
Plan hash value: 1296839119
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 29 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX FAST FULL SCAN| IDX1_OBJECT_ID | 47795 | 606K| 29 (4)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
117 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
2)修改索引列為非空
SQL> desc t1;
名稱 是否為空? 型別
----------------------------------------- -------- -------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> alter table t1 modify object_id not null;
表已更改。
SQL> desc t1;
名稱 是否為空? 型別
----------------------------------------- -------- -------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> select count(*)from t1;
COUNT(*)
----------
49821
已用時間: 00: 00: 00.01
執行計劃
----------------------------------------------------------
Plan hash value: 1296839119
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX1_OBJECT_ID | 47795 | 29 (4)| 00:00:01 |
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
117 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
表已建立。
SQL> create index idx1_object_id on t1(object_id);
索引已建立。
SQL> select count(*) from t1;
COUNT(*)
----------
49821
思考一個問題,count(*)是否走索引?驗證一下
SQL> select count(*) from t1;
COUNT(*)
----------
49821
已用時間: 00: 00: 00.01
執行計劃
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 158 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 47795 | 158 (2)| 00:00:02 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
690 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
為什麼不走索引?索引列是不儲存空值的,索引並不知道表是否有空值,如果表有空值,走索引,count(*)將不準確.
如果想走索引,可以有以下兩種解決方法:
1)明確索引列非空
SQL> select count(*)from t1 where object_id is not null;
COUNT(*)
----------
49821
已用時間: 00: 00: 00.01
執行計劃
----------------------------------------------------------
Plan hash value: 1296839119
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 29 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX FAST FULL SCAN| IDX1_OBJECT_ID | 47795 | 606K| 29 (4)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
117 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
2)修改索引列為非空
SQL> desc t1;
名稱 是否為空? 型別
----------------------------------------- -------- -------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> alter table t1 modify object_id not null;
表已更改。
SQL> desc t1;
名稱 是否為空? 型別
----------------------------------------- -------- -------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> select count(*)from t1;
COUNT(*)
----------
49821
已用時間: 00: 00: 00.01
執行計劃
----------------------------------------------------------
Plan hash value: 1296839119
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX1_OBJECT_ID | 47795 | 29 (4)| 00:00:01 |
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
117 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26937943/viewspace-1264223/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- count(*) 優化優化
- count(*)小優化優化
- 【優化】COUNT(1)、COUNT(*)、COUNT(常量)、COUNT(主鍵)、COUNT(ROWID)等優化
- 【MySQL】效能優化之 count(*) VS count(col)MySql優化
- MySQL優化COUNT()查詢MySql優化
- 百萬資料 mysql count(*)優化MySql優化
- Sql優化(二) 快速計算Distinct CountSQL優化
- 【案例】MySQL count操作優化案例一則MySql優化
- 優化select count(*) from t1優化
- COUNT(*)計算行數有哪些優化手段優化
- mysql 大表中count() 使用方法以及效能優化.MySql優化
- mysql count函式與分頁功能極限優化MySql函式優化
- MySQL 5.6,5.7的優化器對於count(*)的處理方式MySql優化
- MySQL的COUNT語句--count(*)、 count(常量)、 count(列名)MySql
- count(0),count(1),count(*)總結與count(column)
- MySQL的count(*)的優化,獲取千萬級資料表的總行數MySql優化
- count(1),count(*),count(列)的區別
- count(*)、count(1)和count(列名)的區別
- count (*) 和 count (1) 和 count (列名) 區別
- count(*) 和 count(1)和count(列名)區別
- SQL最佳化-COUNT_ INDEX的巧用SQLIndex
- 圖解MySQL:count(*) 、count(1) 、count(主鍵欄位)、count(欄位)哪個效能最好?圖解MySql
- Django ORM效能優化之count和len方法的選擇(非常詳細推薦乾貨)DjangoORM優化
- count(*) 和count(column)之區別
- mysql中count(1)與count(*)比較MySql
- 前端效能優化(JS/CSS優化,SEO優化)前端優化JSCSS
- Ask Hoegh(4)——select count(*)和select count(1)、count(column)有區別嗎?
- MySQL:count(*) count(欄位) 實現上區別MySql
- SQL Server中count(*)和Count(1)的區別SQLServer
- 理解exists count
- 效能優化案例-SQL優化優化SQL
- MSSQL優化之索引優化SQL優化索引
- CUDA優化之指令優化優化
- select count(*)和select count(1)的區別
- mysql count函式與分頁功能極限最佳化MySql函式
- Android效能優化----卡頓優化Android優化
- 資料庫優化 - SQL優化資料庫優化SQL
- 【前端效能優化】vue效能優化前端優化Vue