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

最新推荐文章于 2023-02-18 22:46:33 发布
最新推荐文章于 2023-02-18 22:46:33 发布
阅读量254 收藏
点赞数
文章标签： 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
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL查询优化:LIMIT 1避免全表扫描提高查询效率
01-21
例如下面的用户表(主键id,邮箱,密码)： 代码如下: create table t_user( id int primary key auto_increment, email varchar(255), password varchar(255) ); 每个用户的email是唯一的，如果用户使用email作为...
SQL优化
Poor - Because you have no ambition
07-11 138
对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引 - 尝试下面的技巧以避免优化器错选了表扫描： - 使用ANALYZE TABLEtbl_name为扫描的表更新关键字分布。 - 对扫描的表使用FORCEINDEX告知MySQL，相对于使用给定的索引表扫描将非常耗时。 ...
MySQL数据库优化————COUNT优化
weixin_38910645的博客
02-18 5439
直接进入主题。
我说 SELECT COUNT(*) 会造成全表扫描，面试官说拜拜！
YJ语
06-02 1152
SELECT COUNT(*) 通过最小索引树遍历后不需要返回行数据直接+1 SELECT COUNT(1) count（1）是需要返回数据的但是不做为空判断+1 SELECT COUNT(字段) count（字段）是需要返回数据的但是不做为空判断+1，相比SELECT COUNT(1)占用的内存更多 按照效率排序的话： count(字段)<count(主键id)<count(1)≈count() 所以我建议你，尽量使用count()。 由上面比较可以看出，由于Mysql优化器的原因
SQL中哪些情况会引起全表扫描
damokelisijian866的博客
11-04 3130
1、模糊查询效率很低： 原因：like本身效率就比较低，应该尽量避免查询条件使用like;对于like’%…%’(全模糊）这样的条件，是无法使用索引的，全表扫描自然会效率很低；另外由于匹配算法的关系，模糊查询的字段长度越大，查询的效率就越低。 解决办法：首先尽量避免模糊查询，如果因为业务需要一定要使用模糊查询，则至少保证不要使用全模糊查询，对于右模糊查询，即like ‘…%’，是会使用索引的；左模糊like ‘%…’无法直接使用索引，但可以利用reverse + function index 的形式，变化成
mysql count 全表扫描_我说 SELECT COUNT(*) 会造成全表扫描，面试官让我回去等通知...
weixin_34551614的博客
01-19 682
SELECTCOUNT(*)FROMSomeTableSELECTCOUNT(1)FROMSomeTable原因是会造成全表扫描，有位读者说这种说法是有问题的，实际上针对无 where_clause 的 COUNT(*)，MySQL 是有优化的，优化器会选择成本最小的辅助索引查询计数，其实反而性能最高，这位读者的说法对不对呢针对这个疑问，我首先去生产上找了一个千万级别的表使用 EXP...
Mysql如何避免全表扫描的方法
12-15
在以下几种条件下，MySQL就会做全表扫描： 1>数据表是在太小了，做一次全表扫描比做索引键的查找来得快多了。当表的记录总数小于10且...但是对大表来说，尝试使用以下技术来避免让优化程序错误地选择全表扫描： 1>执行
大幅提升MySQL中InnoDB的全表扫描速度的方法
01-19
在 InnoDB中更加快速的全表扫描  一般来讲,大多数应用查询的时候都会用索引，查找很少的几行数据（主键查找或百行内的查询），但有时候我们需要全表查询。典型的全表扫描就是逻辑备份 (mysqldump) 和 online ...
最全的MySQL大表优化方案
02-24
当MySQL单表记录数过大时，增删改查性能都会急剧下降，可以参考以下步骤来优化：除非单表数据未来会一直不断上涨，否则不要一开始就考虑拆分，拆分会带来逻辑、部署、运维的各种复杂度，一般以整型值为主的表在千万...
oracle执行计划全表扫描,Oracle 全表扫描及其执行计划
weixin_39853863的博客
04-09 1468
全表扫描是Oracle访问数据库表是较为常见的访问方式之一。很多朋友一看到SQL语 句执行计划中的全表扫描，就要考虑对其进行修理一番。全表扫描的存在，的确存在可能优化的余地。但事实上很多时候全表扫描也并非是最低效的，完全要看不同 的情形与场合，任一方式都是有利有弊的，也就是具体情况要具体分析。本文描述了什么是全表扫描以及何时发生全表扫描，何时全表扫描才低效。本文涉及到的相关链接：1、什么是全表扫描...
优化select count(*) (转oracle培训老师的文章)
08-02 110
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=...
oracle优化之count的优化-避免全表扫描
热门推荐
robbie的专栏
12-06 1万+
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; 表已创建。
select count(*)使用全表扫描？闲聊下其中的成本计算...
loongshawn的博客
06-01 3478
SELECT count(*) FROM table; SELECT count(1) FROM table;
MySQL中的全表扫描和索引树扫描
Trouvailless的博客
05-17 6634
引言 在学习mysql时，我们经常会使用explain来查看sql查询的索引等优化手段的使用情况。在使用explain时，我们可以观察到，explain的输出有一个很关键的列，它就是type属性，type表示的是扫描方式，代表 MySQL 使用了哪种索引类型，不同的索引类型的查询效率是不一样的。 在type这一列，有如下一些可能的选项： system：系统表，少量数据，往往不需要进行磁盘IO const：常量连接 eq_ref：主键索引(primary key)或者非空唯一索引(unique ..
mysql count 全表扫描_mongodb与mysql全表扫描能力PK
weixin_34481252的博客
01-27 270
nosql的数据在内存里，而传统rdbms，某个select第一次执行的时候，如果发现内存里没有需要的数据(比如mysql的innodb buffer pool)，会去从磁盘读取，然后再开始计算，这样子从原理上就必然比nosql要慢一些，但是，会慢多少呢？可以用一个分组统计的全表扫描来PK下。测试环境如下：server：阿里云服务器(Ubuntu14.04+1核cpu和 1G内存)mysql:5....
造成mysql全表扫描的原因
Mr_毋
06-02 9985
全表扫描是数据库搜寻表的每一条记录的过程，直到所有符合给定条件的记录返回为止。通常在数据库中，对无索引的表进行查询一般称为全表扫描；然而有时候我们即便添加了索引，但当我们的SQL语句写的不合理的时候也会造成全表扫描。 以下是经常会造成全表扫描的SQL语句及应对措施： 1. 使用null做为判断条件 如：select account from member where nicknam
count 全表扫描 需要加索引
xiaowenli~~~的博客
09-10 374
下面语句执行select 查询属性的时候 不是全表扫描， 执行select count(0) 全表扫描 SELECT COUNT(0) FROM park_info t1 INNER JOIN park_info t2 ON t1.event_type = 0 AND t1.audit_status = 1 AND t2.only_code = t1.only_code AND t2.audit_status = 1 AND t2.event_type
页面分页的显示，在大数据量的情况下，怎么解决count(*)全表扫描的问题
weixin_43947156的博客
06-16 478
高性能mysql：覆盖索引或者创建汇总表或者调整业务架构 对于大表的后台，都有默认条件，默认7天之内。但如果查七天之前的就比较头痛
mysql强制全表扫描
最新发布
05-30
3. 查询结果太大：如果查询结果太大，MySQL 可能会选择执行全表扫描来避免使用大量的 I/O 操作。 4. 查询条件中使用了函数：如果查询条件中使用了函数，MySQL 可能会选择执行全表扫描来避免对每行数据进行函数计算...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值