暂无图片
暂无图片
6
暂无图片
暂无图片
暂无图片

01-SQL优化学习-基本概念

原创 ziyoo0830 2019-10-24
1024

第0章 环境

备份scott用户: oracle*o11g-/home/oracle>$expdp \' / as sysdba \' dumpfile=expdp_scott.dump logfile=expdp_scott.log schemas=scott 初始化环境: oracle*o11g-/home/oracle>$sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 27 13:08:43 2019 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@o11g>grant dba to scott; Grant succeeded. SYS@o11g>alter user scott account unlock ; SYS@o11g>alter user scott identified by tiger; SYS@o11g>conn scott/tiger; SCOTT@o11g>create table test as select * from dba_objects ;

第1章 SQL优化必懂概念

1.1、基数(CARDINALITY)

某个列的唯一键(Distinct_Keys)的数量;

主键列的基数等于表的总行数;

一般情况下,当查询结果是返回表中5%以内的数据时,应该走索引;当查询结果返回的是超过表中5%的数据时,应该走全表扫描;

1.2、选择性(SELECTIVITY)

基数与总行数的比值再乘以100%就是某个列的选择性;

1.3、收集表统计信息

begin dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'TEST', estimate_percent => 100, method_opt => 'for all columns size ?1/auto', no_invalidate => FALSE, degree => 1, cascade => TRUE); end; /

1.4、获取表每个列的基数与选择性

SCOTT@o11g>select a.column_name, b.num_rows, a.num_distinct Cardinality, round(a.num_distinct / b.num_rows * 100, 2) selectivity from dba_tab_col_statistics a, dba_tables b where a.owner = b.owner and a.table_name = b.table_name and a.owner = 'SCOTT' and a.table_name = 'TEST'; COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY --------------- ---------- ----------- ----------- OWNER 86963 30 .03 OBJECT_NAME 86963 52421 60.28 SUBOBJECT_NAME 86963 142 .16 OBJECT_ID 86963 86963 100 DATA_OBJECT_ID 86963 9077 10.44 OBJECT_TYPE 86963 45 .05 CREATED 86963 925 1.06 LAST_DDL_TIME 86963 1026 1.18 TIMESTAMP 86963 1065 1.22 STATUS 86963 1 0 TEMPORARY 86963 2 0 GENERATED 86963 2 0 SECONDARY 86963 2 0 NAMESPACE 86963 21 .02 EDITION_NAME 86963 0 0 15 rows selected.

1.5、什么样的列必须创建索引呢?

当一个列出现在where条件中,该列没有创建索引并且选择性大于20%,那么该列就必须创建索引;

只有大表才会产生性能问题

1.6、抓出必须创建索引的列

1.6.1、刷新数据库监控信息

begin dbms_stats.flush_database_monitoring_info; end; /

1.6.2、获取需要创建索引的列

select r.name owner, o.name table_name, c.name column_name, equality_preds, --等值过滤 equality_preds, --等值join,比如where a.id=b.id nonequijoin_preds, -- 不等值join range_preds, --范围过滤次数 > >= < <= between and like_preds, --like过滤 null_preds, --null过滤 timestamp from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r where o.obj# = u.obj# and c.obj# = u.obj# and c.col# = u.intcol# and r.name = 'SCOTT' and o.name = 'TEST';

1.7、直方图

for all columns size 1 表示对所有列都不收集直方图;

通过指定 dbms_stats 的 method_opt 参数,来创建直方图。在 method_opt 子句中有三个相关选项,即 skewonly、repeat 和 auto。
“skewonly” 选项,它的时间性很强,因为它检查每个索引中每列值的分布。如果 dbms_stats 发现一个索引中具有不均匀分布的列,它将为该索引创建直方图,以帮助基于成本的 SQL 优化器决定是使用索引还是全表扫描访问。

1.8、回表(TABLE ACCESS BY INDEX ROWID)

通过索引中记录的rowid访问表中的数据就叫回表;

回表一般是单块读;

回表次数太多会严重影响SQL性能;

1.9、获取数据存放的块数量

SCOTT@o11g>select count(distinct dbms_rowid.rowid_block_number(rowid)) from test where owner='SYS';

1.10、集群因子

集群因子用于判断索引回表需要消耗的物理I/O次数;
集群因子介于表的块数和表行数之间;

如果集群因子与块数接近,说明表的数据基本上时有序的,而且其顺序基本与索引顺序一样。这样在进行索引范围或者索引全扫描的时候,回表只需要读取少量的数据块就能完成;

如果集群因子与表记录接近,说明表的数据和索引顺序差异较大,在进行索引范围扫描或索引全扫描的时候,回表会读取更多的数据块;

集群因子只会影响索引范围扫描(index range scan)以及索引全扫描(index full scan),只有这两种索引扫描方式会有大量数据回表;

集群因子影响索引回表的物理I/O次数;

1.11、表与表之间的关系

1:1

1:N

N:N

最后修改时间:2019-10-28 12:48:06
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
2人已赞赏
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

文章被以下合辑收录

评论