优化全表扫描

最新推荐文章于 2024-04-13 21:05:42 发布
最新推荐文章于 2024-04-13 21:05:42 发布
阅读量255 收藏
点赞数

全表扫描的工作是扫描高水位一下所有的数据块。高水位的标志存在表头。
该数据块以后都是崭新未格式化的数据块，
高水位有两个目的。一是它是全表扫描的终点，二是并行插入的起点！
优化全表扫描的办法有四种，归根到底，最终核心目的就是降低高水位！
一、降低高水位；二、紧密码放数据（压缩数据）；三、并行查询；四、修改初始化参数
降低高水位的办法有三：
 一、在线回收空间；二、挪动表空间；三、导出和导入。
紧密码放数据办法有二：
 一、调整pctfree;二、使用压缩特性。
我们采用具体的实验来介绍如何优化全表扫描。

实验如下：
建立大表，50万左右，分析表，列select * from t1;的计划，看代价！
SQL> conn scott/tiger
Connected.
SQL> drop table t1 purge;

Table dropped.

SQL> create table t1 as select * from emp where 0=9;

Table created.

SQL> insert into t1 select * from emp;

已创建14行。

SQL> insert into t1 select * from t1;

已创建14行。

SQL> /

--一直斜杠，直到

已创建229376行。

SQL> commit;
现在我们就有了45万行左右的大表！
分析表，获得统计信息！
SQL>analyze table T1 compute statistics;

Table analyzed.

SQL> set autot trace expl
SQL> select * from t1;

SQL>select NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE from tabs where table_name='T1';
执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   458K|    14M|   544  (10)| 00:00:07 |
|   1 |  TABLE ACCESS FULL| T1   |   458K|    14M|   544  (10)| 00:00:07 |
--------------------------------------------------------------------------
我们看到代价为544，我们围绕544进行优化，降低代价！

SQL>set autot off
SQL>delete t1 where deptno=30;--我们删除了数据，验证高水位有没有回收。
SQL>commit;
SQL>analyze table T1 compute statistics;
SQL>set autot trace exp;
SQL>select NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE from tabs where table_name='T1';
SQL>select * from t1;
执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   262K|  8192K|   526   (7)| 00:00:07 |
|   1 |  TABLE ACCESS FULL| T1   |   262K|  8192K|   526   (7)| 00:00:07 |
--------------------------------------------------------------------------
我们看到代价为526,比原来小一点，因为cost是根据块，内存，cpu,网络综合计算的。
行少了一半，但代价没有少多少！因为这里高水位没有变化！

一、在线回收空间；
SQL>alter table t1 enable row  movement;
SQL>alter table t1 shrink space;
SQL>analyze table T1 compute statistics;
SQL> select NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE from tabs where table_name='T1';
NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE
-------- ---------- ------------ ----------
  262144       1376           32         21
占用了1376个数据块。

SQL>select * from t1;

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   262K|  8192K|   275  (11)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| T1   |   262K|  8192K|   275  (11)| 00:00:04 |
--------------------------------------------------------------------------
我们看到代价为275,比原来小了接近一半。

二、挪动表空间；
SQL> alter table t1 move tablespace users;
这句话也可以重新码放数据。
SQL> analyze table T1 compute statistics;

表已分析。

SQL> select NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE from tabs where table_name='T1';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE
---------- ---------- ------------ ----------
    262144       1568           96        826
占用了1568个数据块，比原来多了192个数据块，这是因为高水位不是一个一个块的挪动，
而是一组一组的挪动。

SQL>select * from t1;
执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   262K|  8192K|   310  (10)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| T1   |   262K|  8192K|   310  (10)| 00:00:04 |
--------------------------------------------------------------------------
代价为310，比原来的275大，因为浪费了一些块，这些块存在于高水位下，但没有数据。
但数据库全表扫描的时候还是查看了空块，浪费了！

三、调整pctfree
SQL> alter table t1 pctfree 0;
Table altered.
这句话的目的是使每个数据块更加紧密的码放数据，没有update,或者update行长不变的表，
pctfree应该设置为0.

SQL> alter table t1 move tablespace users;


SQL>analyze table T1 compute statistics;
SQL>select * from t1;

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   262K|  8192K|   281  (10)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| T1   |   262K|  8192K|   281  (10)| 00:00:04 |
--------------------------------------------------------------------------
代价为281，比310笑了10%，因为pctfree默认为10。

四、使用压缩存储的新特性
SQL>alter table t1 compress;
SQL>alter table t1 move tablespace users;
SQL>analyze table T1 compute statistics;
SQL>select * from t1;
执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   262K|  8192K|    97  (27)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| T1   |   262K|  8192K|    97  (27)| 00:00:02 |
--------------------------------------------------------------------------
代价为97，因为压缩了，数据在同一个数据块内复用了，减少了存储空间。
但带来的负面影响是当我们update的时候，表会暴涨，比不压缩还大，而且普通的
插入不能压缩，只有在直接加载的时候，才会有压缩的特性，参考网站内的压缩表文章。

五、使用并行查询来提高全表扫描的性能。
SQL> select /*+ full(t1) parallel(t1 16) */ * from t1;

执行计划
----------------------------------------------------------
Plan hash value: 2494645258

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


| Id  | Operation      | Name     | Rows  | Bytes | Cost (%CPU)| Time  |  TQ  |IN-OUT| PQ Distrib |

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

|   0 | SELECT STATEMENT     |   |   262K| 8192K  |  7  (29)| 00:00:01 |        |      |         |
|   1 |  PX COORDINATOR      |       |    |       |       |     |      |      |         |
|   2 |   PX SEND QC (RANDOM)|:TQ10000 |262K|8192K| 7  (29)| 00:00:01 |  Q1,00 | P->S | QC (RAND) |
|   3 |    PX BLOCK ITERATOR |   |   262K|  8192K |   7  (29)| 00:00:01 |  Q1,00 | PCWC |         |
|   4 |     TABLE ACCESS FULL| T1  |  262K|  8192K|  7  (29)| 00:00:01 |  Q1,00 | PCWP |        |

-----------------------------------------------------------------------------------------------
代价为7，比原来的544小了近百倍。效果明显。

六、修改db_file_multiblock_read_count参数，使每次的i/o尽量多读数据块，也会提高全表扫描性能。

SQL> conn / as sysdba
已连接。
SQL> alter system set db_file_multiblock_read_count=1;

系统已更改。

SQL> startup force
重新启动数据库

SQL> conn scott/tiger
已连接。
SQL> set autot trace expl
SQL> select * from t1;

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   262K|  8192K|   419   (7)| 00:00:06 |
|   1 |  TABLE ACCESS FULL| T1   |   262K|  8192K|   419   (7)| 00:00:06 |
--------------------------------------------------------------------------
原理97的代价，上升为419，为什么？因为每次读的数据块少了！


-----------------------------------------------------------------------------------------------------------------------
pxboracle@live.com
2014.5.22 14:35
share you knowledge with the world.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12798004/viewspace-1167512/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12798004/viewspace-1167512/

cuchou5321
关注
SQL全表扫描优化基础知识
xiaochengxuyuan1的博客
01-28 896
1、模糊查询效率很低： 原因：like本身效率就比较低，应该尽量避免查询条件使用like；对于like ‘%…%’（全模糊）这样的条件，是无法使用索引的，全表扫描自然效率很低；另外，由于匹配算法的关系，模糊查询的字段长度越大，模糊查询效率越低。 解决办法：首先尽量避免模糊查询，如果因为业务需要一定要使用模糊查询，则至少保证不要使用全模糊查询，对于右模糊查询，即like ‘…%’，是会使用索引的；左模糊like ‘%…’无法直接使用索引，但可以利用reverse + function index 的形式，变化
MySQL千万级数据的全表扫描优化
奔跑的五花肉
10-11 2074
数据场景 一个很简单 的业务数据表，2300W规模的数据，没有复杂数据类型，不想分表，还需要全表遍历 Select全表性能 查询接口 @Mapper public interface UserMapper { @Select("SELECT * FROM loadtimeslot") public List<Pojo> select(...
oracle全表扫描的3种优化手段
11-14
oracle全表扫描的3种优化手段及详细命令
oracle全表扫描如何优化,优化Oracle with全表扫描的问题
weixin_39527292的博客
04-03 763
用户表比较大，是一个有900w记录的分区表。查看执行计划，都符合预期，只是最后关联用户表的时候，使用了全表扫描，直接导致这个今天开发接了一个很BT的需求。找一个人的所有好友，查询所有好友的所有作品，，然后按照时间倒序排列,取若干记录，然后关联作品评论表。作品包括原唱表，翻唱表，伴奏表，视频表，博客表和照片表,不同的作品类型还要关联不同的专辑表，最后还要关联用户表..----------------...
Oracle中全表扫描优化方法
专注于数据库技术分享，包含但不限于Oracle，MySQL，PostgreSQL，ElasticSearch及国产数据库等
03-17 510
虽然在某些场景下全表扫描可能是最优选择（例如：当需要访问大部分或全部数据、表很小或者索引访问成本高于全表扫描时），但在很多情况下，特别是对于大型表和有特定查询条件的SQL语句，优化全表扫描是提高查询性能的关键步骤之一。通过以上方法的综合运用，可以显著降低不必要的全表扫描次数，提升数据库的整体性能和响应速度。同时，对于必须进行全表扫描的操作，也可以通过上述手段减轻其对系统资源的压力，使其更加高效地执行。
Oracle SQL优化必要的全表扫描思路分析
梅森上校的博客 业精于勤荒于嬉，形成于思毁于随。
06-22 1707
大多数情况下，我们需要避免SQL在查询时进行全表扫描（FTS），但是对于必须需要进行全表扫描的情况，也可以进行一些优化处理。 即使全表扫描是检索所需数据的唯一可行方法，仍然有多种方法来提升查询性能。优化扫描的技术包括： 1、  通过让表变小来减少扫描所需的逻辑块读取的数量 2、  通过提高数据库IO的效率降低扫描所需要的物理块读取的数量 3、  通过缓存或抽样降低扫描开销 4、  通过使
MySQL查询优化:LIMIT 1避免全表扫描提高查询效率
01-21
例如下面的用户表(主键id,邮箱,密码)： 代码如下: create table t_user( id int primary key auto_increment, email varchar(255), password varchar(255) ); 每个用户的email是唯一的，如果用户使用email作为...
Oracle全表扫描及其执行计划
12-14
在优化SQL性能时，不应盲目避免全表扫描，而应根据实际情况权衡。有时候，优化数据库结构、更新统计信息或调整查询语句，可能会提高查询效率。了解何时应该使用全表扫描，以及何时应该避免它，对于优化Oracle数据库...
Mysql如何避免全表扫描的方法
09-11
全表扫描在数据量较大时，会显著降低查询性能，因此避免全表扫描对于数据库性能优化至关重要。 以下是一些避免MySQL全表扫描的方法： 1. **合理设计索引**：在经常用于WHERE子句或JOIN条件的列上创建索引，这样...
Oracle优化-全表扫描与高水位线
last_kunshen的博客
08-30 248
全表扫描与高水位线 决定是否使用全表扫描的要素： 访问的数据块个数 最终结果集行数 舍弃的行数 全表扫描通常要读出直到HWM标记的所有的属于该表数据块，即使该表中没有任何数据。 HWM(高水位线)：用来界定一个段中使用的块和未使用的块。在我们插入数据时，HWM逐渐上移，在HWM之下的，表示使用过的块，HWM之上的就表示未使用过的块。 delete操作不会降低HWM，关于insert语句，如果使用了append选项，HWM会直接上移，数据不会插入到表的空闲块中。 所以，一张原本有大量数据的表，用delet
oracle全表扫描的优化
weixin_33804990的博客
01-06 207
全表扫描的优化analyze table t1 estimate statistics；收集统计信息select * from t1;执行计划为全表扫描,假设代价为68select /*+ full(t1) parallel(t1,4) */ * from t1;使用并行查询的强制,代价为17alter table t1 pctfree 0;alter table t1 mo...
Oracle 数据库全表扫描的4种优化方法（DB）
最新发布
2301_79009758的博客
04-13 1326
我们看到代价为526,比原来小一点，因为cost是根据块，内存，cpu,网络综合计算的。占用了1568个数据块，比原来多了192个数据块，这是因为高水位不是一个一个块的挪动，代价为310，比原来的275大，因为浪费了一些块，这些块存在于高水位下，但没有数据。但带来的负面影响是当我们update的时候，表会暴涨，比不压缩还大，而且普通的。插入不能压缩，只有在直接加载的时候，才会有压缩的特性，参考网站内的压缩表文章。代价为97，因为压缩了，数据在同一个数据块内复用了，减少了存储空间。高水位的标志存在表头。
oracle优化：避免全表扫描
xjg396的专栏
11-28 215
1． 对返回的行无任何限定条件，即没有where 子句 2． 未对数据表与任何索引主列相对应的行限定条件 例如：在City-State-Zip列创建了三列复合索引，那么仅对State列限定条件不能使用这个索引，因为State不是索引的主列。 3． 对索引的主列有限定条件，但是在条件表达式里使用以下表达式则会使索引失效，造成全表扫描： （1）where子句中对字段进行函数、...
oracle全表扫描如何优化,Oracle的大表,小表与全表扫描
weixin_31419153的博客
04-03 761
Oracle的大表,小表与全表扫描通常对于小表，Oracle建议通过全表扫描进行数据访问，对于大表则应该通过索引以加快数据查询，当然如果查询要求返回表中大部分或者全部数据，那么全表扫描可能仍然是最好的选择。从V$SYSSTAT视图中，我们可以查询得到关于全表扫描的系统统计信息：SQL> col name for a30SQL> select name,value from v$syss...
oracle 优化，全表扫描
极e象
03-05 1809
1． 对返回的行无任何限定条件，即没有where 子句，就是一个全表扫描 2． 未对数据表与任何索引主列相对应的行限定条件 例如：在City-State-Zip列创建了三列复合索引，那么仅对State列限定条件不能使用这个索引，因为State不是索引的主列。   3． 对索引的主列有限定条件，但是在条件表达式里使用以下表达式则会使索引失效，造成全表扫描：(在条件表达式里面使用非索引字段进行
oracle全表扫描优化,优化Oracle with全表扫描的问题
weixin_35531735的博客
04-03 388
今天开发接了一个很BT的需求。找一个人的所有好友，查询所有好友的所有作品，然后按照时间倒序排列,取若干记录，然后关联作品评论表。作品包括原唱表，翻唱表，伴奏表，视频表，博客表和照片表,不同的作品类型还要关联不同的专辑表，最后还要关联用户表..结果就是这个SQL很长...witht1 as (select to_userid from friend_list f where f.userid=411...
深入解析Oracle数据库全表扫描原理，优缺点，优化方法
专注于数据库技术分享，包含但不限于Oracle，MySQL，PostgreSQL，ElasticSearch及国产数据库等
03-29 644
在Oracle数据库中，全表扫描（Full Table Scan，FTS）是一种数据访问方法，它指的是数据库在执行SQL查询时，如果优化器判断通过索引访问不如直接扫描整张表来得高效，或者表上没有合适的索引可供使用时，会选择对表的所有数据块进行顺序扫描。数据库从表的第一个数据块开始，按物理存储顺序逐个读取数据块，直至达到表的高水位线（High Water Mark，HWM）。在扫描过程中，Oracle会检查每一行数据是否满足SQL查询的条件，如果是，则将符合条件的行放入结果集中。
MySQL全表扫描：性能杀手的隐患与优化策略
爱编程的鱼的博客
02-03 1226
MySQL全表扫描是一种性能较低的查询方式，应尽量避免在大型表或复杂查询条件下使用。通过合理使用索引、优化查询语句和数据库配置，可以减少全表扫描的需要，提高查询性能。了解全表扫描的原理和影响，并采取相应的优化策略，可以帮助开发者更好地处理全表扫描的问题，提升数据库的性能和响应能力。
优化全表扫描：Oracle多块读与FTS策略
全表扫描是Oracle数据库中的一个优化技术，它涉及在查询时读取整个表的数据，而不受WHERE子句的限制条件筛选。当没有特定的条件来过滤数据时，Oracle会顺序读取表中的所有数据块，直到达到高水位（HWM），这个值标识...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值