mysql count 全表扫描_oracle优化之count的优化-避免全表扫描

最新推荐文章于 2023-04-19 09:33:04 发布
最新推荐文章于 2023-04-19 09:33:04 发布
阅读量294 收藏
点赞数
文章标签： mysql count 全表扫描
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42149153/article/details/114327471
版权

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，运行的计划为全表扫描。

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 |

-----------------------------------------------------

SQL> --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，降低了

SQL> --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%

SQL> --3.参数db_file_multiblock_read_count=64

SQL> --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 |

----------------------------------------------------------------------

我们的索引起到了很大的作用！

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，优化无止境呀！

只有你把握原理，一切尽在掌握！

FeMnO
关注
select count(*)使用全表扫描？闲聊下其中的成本计算...
loongshawn的博客
06-01 3545
SELECT count(*) FROM table; SELECT count(1) FROM table;
mysql 全表扫描优化_MySQL性能优化之使用Limit关键字来避免全表扫描
weixin_34171095的博客
02-17 603
在MySQL数据库操作中，我们在做一些查询的时候总希望能避免数据库引擎做全表扫描，因为全表扫描时间长，而且其中大部分扫描对客户端而言是没有意义的。其实我们可以使用Limit关键字来避免全表扫描的情况，从而提高效率。假设我们需要一个分页的查询，如果是Oracle 程序员会有点痛苦，因为Oracle 中没有Limit 这个关键字，一般来说都是用以下SQL句子实现：class="dp-xml">S...
mysql count 全表扫描_我说 SELECT COUNT(*) 会造成全表扫描，面试官让我回去等通知...
weixin_34551614的博客
01-19 744
SELECTCOUNT(*)FROMSomeTableSELECTCOUNT(1)FROMSomeTable原因是会造成全表扫描，有位读者说这种说法是有问题的，实际上针对无 where_clause 的 COUNT(*)，MySQL 是有优化的，优化器会选择成本最小的辅助索引查询计数，其实反而性能最高，这位读者的说法对不对呢针对这个疑问，我首先去生产上找了一个千万级别的表使用 EXP...
MySql查询优化:避免全表扫描
wang_magento的博客
05-06 592
对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引: 尝试下面的技巧以避免优化器错选了表扫描： 使用ANALYZE TABLE tbl_name为扫描的表更新关键字分布。 对扫描的表使用FORCE INDEX告知MySQL，相对于使用给定的索引表扫描将非常耗时。 S...
MySQL_15_MySQL底层SQL查询成本计算
毛毛的专栏
09-06 833
文章目录一、前言二、select count(*) 和 select count(1)针对无 where_clause子句 的 COUNT(*)，MySQL 是有优化的，优化器会选择成本最小的辅助索引查询计数，达到性能最高理论：SQL 选用索引的执行成本如何计算(全文重点，面试考点)实践：SQL 选用索引的执行成本如何计算(全文重点，面试考点)继续深入，MySQL 做全表扫描的成本有多少，SHOW TABLE STATUS LIKE 'person' 构造全表扫描optimizer_trace 工具，计算索
我说 SELECT COUNT(*) 会造成全表扫描，面试官说拜拜！
YJ语
06-02 1263
SELECT COUNT(*) 通过最小索引树遍历后不需要返回行数据直接+1 SELECT COUNT(1) count（1）是需要返回数据的但是不做为空判断+1 SELECT COUNT(字段) count（字段）是需要返回数据的但是不做为空判断+1，相比SELECT COUNT(1)占用的内存更多 按照效率排序的话： count(字段)<count(主键id)<count(1)≈count() 所以我建议你，尽量使用count()。 由上面比较可以看出，由于Mysql优化器的原因
oracle优化之count的优化-避免全表扫描
AinUser的博客
03-13 6082
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; 表已创建。
mysql的优化-避免全表扫描
ideaaaa的博客
12-14 232
对查询进行优化，应尽量避免全表扫描，首先应考虑在where及orderby涉及的列上建立索引: 尝试下面的技巧以避免优化器错选了表扫描： 使用ANALYZETABLEtbl_name为扫描的表更新关键字分布。 对扫描的表使用FORCEINDEX告知MySQL，相对于使用给定的索引表扫描将非常耗时。 SELECT*FROMt1,t2FORCEINDEX(...
mysql-优化-避免全表扫描
核桃的博客——JavaWeb技术分享
05-29 2151
对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引: 尝试下面的技巧以避免优化器错选了表扫描： 使用ANALYZE TABLE tbl_name为扫描的表更新关键字分布。对扫描的表使用FORCE INDEX告知MySQL，相对于使用给定的索引表扫描将非常耗时。SELECT * FROM t1, t2 FORCE INDEX (index_for_...
SELECT COUNT(*) 会造成全表扫描？
一路向北
03-19 496
本文通过一个例子深入剖析了 MySQL 的执行计划是如何选择的，以及为什么它的选择未必是我们认为的最优的，这也提醒我们，在生产中如果有多个索引的情况，使用 WHERE 进行过滤未必会选中你认为的索引，我们可以提前使用EXPLAIN来优化我们的查询语句。
灵魂一问：SELECT COUNT(*) 会造成全表扫描吗？
最新发布
m0_71777195的博客
04-19 204
IO 成本: 即从磁盘把数据加载到内存的成本，默认情况下，读取数据页的 IO 成本是 1，MySQL 是以页的形式读取数据的，即当用到某个数据时，并不会只读取这个数据，而会把这个数据相邻的数据也一起读到内存中，这就是有名的程序局部性原理，所以 MySQL 每次会读取一整页，一页的成本就是 1。这就比较有意思了，理论上采用了覆盖索引的方式进行查找性能肯定是比全表扫描更好的，为啥 MySQL 选择了全表扫描呢，既然它认为全表扫描比使用覆盖索引的形式性能更好，那我们分别用这两者执行来比较下查询时间吧。
优化select count(*) (转oracle培训老师的文章)
08-02 136
select count(*) from t1; 这句话比较简单，但很有玄机！对这句话运行的理解，反映了你对数据库的理解深度！ 建立实验的大表他t1 SQL&gt; conn scott/tiger 已连接。 SQL&gt; drop table t1 purge; 表已删除。 SQL&gt; create table t1 as select * from emp where 0=...
mysql count效率差，怎么解决
weixin_35756690的博客
01-03 4826
MySQL 中的 COUNT 函数通常比较慢，因为它会扫描整个表并计算表中的行数。要提高 COUNT 的效率，你可以尝试以下方法： 对于大表，使用带有 WHERE 子句的 COUNT 语句可以提高效率，因为它只会计算满足条件的行数。 在 SELECT 语句中使用 SQL_CALC_FOUND_ROWS 和 FOUND_ROWS() 函数可以避免 COUNT 的性能问题。 对于频繁使用的 CO...
SQL优化
Poor - Because you have no ambition
07-11 168
对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引 - 尝试下面的技巧以避免优化器错选了表扫描： - 使用ANALYZE TABLEtbl_name为扫描的表更新关键字分布。 - 对扫描的表使用FORCEINDEX告知MySQL，相对于使用给定的索引表扫描将非常耗时。 ...
避免全表扫描的sql优化
u012925131的博客
12-26 312
对查询进行优化，应尽量避免全表扫描，首先应考虑在where 及order by 涉及的列上建立索引: .尝试下面的技巧以避免优化器错选了表扫描： · 使用ANALYZE TABLE tbl_name为扫描的表更新关键字分布。 · 对扫描的表使用FORCE INDEX告知MySQL，相对于使用给定的索引表扫描将非常耗时。 SELECT * FROM t1, t2 FORCE INDEX (index...
SQL全表扫描优化基础知识
xiaochengxuyuan1的博客
01-28 963
1、模糊查询效率很低： 原因：like本身效率就比较低，应该尽量避免查询条件使用like；对于like ‘%…%’（全模糊）这样的条件，是无法使用索引的，全表扫描自然效率很低；另外，由于匹配算法的关系，模糊查询的字段长度越大，模糊查询效率越低。 解决办法：首先尽量避免模糊查询，如果因为业务需要一定要使用模糊查询，则至少保证不要使用全模糊查询，对于右模糊查询，即like ‘…%’，是会使用索引的；左模糊like ‘%…’无法直接使用索引，但可以利用reverse + function index 的形式，变化
MySQL优化之COUNT(*)效率
秋韩
07-21 652
说到MySQL的COUNT(*)的效率，发现越说越说不清楚，干脆写下来，分享给大家。COUNT(*)与COUNT(COL)网上搜索了下，发现各种说法都有：比如认为COUNT(COL)比COUNT(*)快的；认为COUNT(*)比COUNT(COL)快的；还有朋友很搞笑的说到这个其
MySQL数据库优化————COUNT优化
weixin_38910645的博客
02-18 5789
直接进入主题。
我说 SELECT COUNT(*) 会造成全表扫描，面试官让我回去等通知
python爬虫人工智能大数据
05-26 1121
前言上篇 SQL 进阶技巧（下) 中提到使用以下 sql 会导致慢查询SELECTCOUNT(*)FROMSomeTable SELECTCOUNT(1)FROMSomeTa...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值
>