建议使用以下浏览器,以获得最佳体验。 IE 9.0+以上版本 Chrome 31+ 谷歌浏览器 Firefox 30+ 火狐浏览器
请选择 进入手机版 | 继续访问电脑版
设置昵称

在此一键设置昵称,即可参与社区互动!

确定
我再想想
选择版块
093454tjmehwgqrskef4if.jpg 云上开发精选优惠 云上优选 特惠来袭

云集而动

发帖: 203粉丝: 96

级别 : 版主

发消息 + 关注

发表于2020-3-31 10:24:39 7780 114
直达本楼层的链接
楼主
显示全部楼层
[云享读书会] 【第六期】SQL优化核心思想——读书笔记征集

918_348读书笔记征集.png


本期为系列活动的第六期,领读书籍为《SQL优化核心思想》

邀请华为高斯数据主任工程师徐铭老师视频领读

助您掌握SQL优化技能 提升数据库性能


查看活动详情,请戳【活动链接】

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

01.【征集时间】

2020年4月20日~2020年4月26日


02.【什么是云享读书会】

每期云享读书会活动,会选取一本技术相关的畅销书籍,由领读专家提炼书籍精华,在读书会的专属微信社群中,每日输出精华知识的领读视频,帮助大家快速积累专业知识。活动期间会设置每日自测题、结业实践任务、提交读书笔记三种积分获取任务,并根据活动结束后的积分排行发放活动奖励。


03.【读书笔记要求】

1. 每篇读书笔记字数要求≥300字;

2. 内容要求与每日领读视频、《SQL优化核心思想》书籍或是华为云产品优化意见相关;

3. 内容原创不可抄袭;

4. 回帖时请务必留下你的微信昵称和华为云账号


04.【最佳读书笔记奖励】

领读专家将在活动时间内提交的有效读书笔记中,评选出3篇最佳读书笔记。

奖品为HUAWEI FreeBuds 悦享版

prize_1.png

05.【活动排行奖励】

活动排行1-10名

奖品为华为云定制双肩包


活动排行11-50名

奖品为本期领读书籍《SQL优化核心思想》


20200115-093721(eSpace).jpgprize_3.png



06.【注意事项】

1. 读书笔记提交后,小助手会在3个工作日内按续完成审核,并增加活动积分100分/篇

2. 本次活动通过提交读书笔记,可获得的积分上限为500分

3. 请务必按照上述要求提交内容,以免影响积分增加;

4. 若积分值相同则以完成学习任务的时间先后排序,其中任务完成时间的判定优先级为:读书笔记>结业实践>自测题>其他;

5. 其他积分获取方式请查看活动社群公告;

6. 其他未说明事项请参照 云享读书会《SQL优化核心思想》


举报
分享

分享文章到朋友圈

分享文章到微博

啊酒哥哥

发帖: 0粉丝: 0

级别 : 新手上路

发消息 + 关注

发表于2020-4-8 15:09:06
直达本楼层的链接
沙发
显示全部楼层

沙发

点赞 评论 引用 举报

一永人

发帖: 0粉丝: 0

级别 : 新手上路

发消息 + 关注

发表于2020-4-9 11:02:41
直达本楼层的链接
板凳
显示全部楼层

沙发

点赞 评论 引用 举报

andyleung

发帖: 289粉丝: 18

级别 : 外部版主

发消息 + 关注

发表于2020-4-10 12:40:40
直达本楼层的链接
地板
显示全部楼层

沙发。

点赞 评论 引用 举报

www2046

发帖: 5粉丝: 0

级别 : 中级会员

发消息 + 关注

发表于2020-4-13 08:49:52
直达本楼层的链接
5#
显示全部楼层

mark

点赞 评论 引用 举报

cityhunter2046

发帖: 19粉丝: 2

级别 : 中级会员

发消息 + 关注

发表于2020-4-13 08:50:17
直达本楼层的链接
6#
显示全部楼层

加油!

点赞 评论 引用 举报

wei

发帖: 8粉丝: 0

级别 : 注册会员

发消息 + 关注

发表于2020-4-20 10:53:13
直达本楼层的链接
7#
显示全部楼层

SQL优化核心思想学习笔记

华为云id:huawei018

微信昵称:潜伏

第一天

  • 统计信息

收集统计信息是为了让优化器选择最佳执行计划,以最少的代价(成本)查询出表中的数据。统计信息主要分为表的统计信息、列的统计信息、索引的统计信息、系统的统计信息、数据字典的统计信息以及动态性能视图基表的统计信息。

表的统计信息主要包含表的总行数(num_rows)、表的块数(blocks)以及行平均长度(avg_row_len)。 列的统计信息主要包含列的NDV、列中的空值数量以及列的数据分布情况(直方图)。 索引的统计信息主要包含索引blevel(索引高度-1)、叶子块的个数(leaf_blocks)以及聚集因子(clustering_factor)。

  • 执行计划

执行计划是sql语句在 Oracle 中的执行过程或访问路径的描述。

  • 查看执行计划的方法

set autot on:该命令会运行SQL并且显示运行结果,执行计划和统计信息。

set autot trace:该命令会运行SQL,但不显示运行结果,会显示执行计划和统计信息。 

set autot trace exp:运行该命令查询语句不执行,DML语句会执行,只显示执行计划。

set autot trace stat:该命令会运行SQL,只显示统计信息。

set autot off:关闭AUTOTRACE。

explain plan for SQL语句

查看带有A-TIME的执行计划 alter session set statistics_level=all;或者在SQL语句中添加hint:/*+ gather_plan_statistics */ select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));


点赞 评论 引用 举报

tomandy2046

发帖: 22粉丝: 3

级别 : 中级会员

发消息 + 关注

发表于2020-4-20 13:13:52
直达本楼层的链接
8#
显示全部楼层

SQL读书笔记第一课:



点赞 评论 引用 举报

andyleung

发帖: 289粉丝: 18

级别 : 外部版主

发消息 + 关注

发表于2020-4-20 13:39:17
直达本楼层的链接
9#
显示全部楼层

华为云ID: andyleung  微信昵称:city hunter


 

 

DAY01. SQL优化基础

一:SQL优化必懂概念

Cardinality :表示一个结果集的数据量大小,可以对应执行计划中某一步返回的数据量。

NDV-V(唯一值数)是指列的唯一值数量。

SelectivitySelectivity指一组谓词(条件)在一个数据集中命中的比例。

CostCost表示优化器计算出来的SQL执行代价,优化器通过Cost的大小选择最优的执行计划,Cost的绝对值没有太大的意义。

二:直方图(HISTOGRAM

存在的意义:直方图是用来帮助CBO在对NDV很低、数据分布不均衡的列进行Rows估算的时候,可以得到更精确的Rows

直方图记录什么数据:

1)  NDV比较小,重复值比较多,查询的值实际不存在。

2)  如果使用绑定参数-如果支持变量窥视,可以根据实际参数值生成执行计划,否则一般按照1/NDV估计。Oracle对应特性为自适应游标共享。

3)  NDV很高时:如果支持变量窥视,可以根据实际参数值生成执行计划,否则一般按照1/NDV估计。Oracle对应特性为自适应游标共享。

回表:

  定义:当对一个列创建索引之后,索引会包含该列的键值以及键值对应行所在的rowid。通过索引中记录的rowid访问表中的数据就叫回表。

 其中回表的重要性:回表一般是单块读,回表次数太多会严重影响SQL性能,如果回表次数太多,就不应该走索引扫描了,应该直接走全表扫描。 在进行SQL优化的时候,一定要注意回表次数!特别是要注意回表的物理I/O次数!

 

聚集因子:聚集因子表示一个索引对应数据在表中的顺序性,它的大小介于表的块数和表行数之间。

聚集因子如何计算:首先我们比较23 对应的ROWID是否在同一 个数据块,如果在同一个数据块, Clustering Factor +0;如果不在同一个数据块,那么Clustering Factor 值加1 然后我们比较34对应的ROWID是否在同一 个数据块,如果在同一个数据块, Clustering Factor 值不变;如果不在同一个数据块,那么Clustering Factor值加1 像上面步骤一样,一直这样有序地比较下去, 直到比较完索引中最后一个键值。 根据算法我们知道集群因子介于表的块数和表 行数之间

聚集因子的影响

聚集因子只会影响索引范围扫描(INDEX RANGE SCAN)以及索引全扫描(INDEX FULL SCAN),因为只有这两种索引扫描方式会有大量数据回表。 扫描同样的数据量,通过不同的索引性能差距可能很大,聚集因子是一个重要因素。 CBO在计算cost时,也会考虑聚集因子的大小

如果降低聚集因子的影响

1)重建表,按照某个索引列的顺序

2)通过组合索引避免回表

3)使用Oraclecluster table

4)增大buffer cache将大部分数据缓存到内存或者使用内存表

:统计信息

定义:统计信息主要分为表的统计信息、列的统计信息、索引的统计信息、系统的统计信息、数据字典的统计信息以及动态性能视图基表的统计信息。

统计信息的内容

表的统计信息主要包含表的总行数(num_rows)、表的块数(blocks)以及行平均长度(avg_row_len)。 列的统计信息主要包含列的NDV、列中的空值数量以及列的数据分布情况(直方图)。 索引的统计信息主要包含索引blevel(索引高度-1)、叶子块的个数(leaf_blocks)以及聚集因子(clustering_factor)。

收集统计信息重要参数设置

比较复杂参数比较多,需要实战中才能学习好这些详细的了。

六:统计信息的过期

如何查询统计信息是否陈旧?

stale_stats=YES,表示统计信息已经陈旧,需要重新收集。

什么情况下统计信息状态变为陈旧?

如果调用dbms_stats.flush_database_monitoring_info;会把变化的数据刷新到视图user_tab_modifications 如果累计增删改的数量超过一定的比例(一般为10%),统计信息状态变为陈旧。

七:动态采样

定义上就是跟流式处理有点像吧?

什么时候需要启用动态采样呢?这个用得比较少,不好理解呢。

八:Oracle获取执行计划

Autotrace

explain plan

查看带有A-TIME的执行计划

计划是挺多的,接触不多,所以理解不够深刻,保持学习吧。

执行计划

谓词 “*”

访问路径

TABLE ACCESS FULL

全表扫描是怎么扫描数据的?

等等还是挺多路径的,还是得实战才能学习好的呢。

十一:PG执行计划-案例解读

用一个案例去让我们理解PG这个数据库还是非常生动的。

后面还有一个PG的实践,这个就真的是非常,通过实践更能理解老师的课程。

谢谢老师了。


DAY01. SQL优化基础-andyleung(微信昵称:city hunter).docx 15.92 KB,下载次数:3

点赞 评论 引用 举报

oldlei

发帖: 3粉丝: 2

级别 : 注册会员

发消息 + 关注

发表于2020-4-20 18:51:39
直达本楼层的链接
10#
显示全部楼层
华为云账号:oldlei 微信:成雷 ## SQL优化中的名词概念 `Cardinality`:表示一个结果集的数据量大小,可以对应执行计划中某一步返回的数据量。 `NDV` :列的唯一值数量(基数)。 > 性别列的基数是2,主键列的基数是表的总行数 `Selectivity`:指一组谓词(条件)在一个数据集中命中的比例。 `谓词`:取值为 TRUE、FALSE 或 UNKNOWN 的表达式。 > h.SalesOrderID > 43669 这个范围查询就是一个过滤谓词。 > 位于LEFT/INNER/RIGHT JOIN的ON后面的为连接谓词。 `Cost`:优化器计算出来的SQL执行代价,优化器通过Cost的大小选择最优的执行计划,Cost的绝对值没有太大的意义。 ## 直方图 `直方图(HISTOGRAM)`:是用来帮助CBO在对NDV很低、数据分布不均衡的列进行Rows估算的时候,可以得到更精确的Rows 。 `CBO`:基于成本的优化器(Cost-Based Optimization) `RBO`:基于规则的优化器(Rule-Based Optimization) 直方图分两种:频率直方图、高度直方图。 > 频率直方图:NDV小于桶数(桶数默认256) > 高度直方图:NDV大于桶数 1. 当ndv比较小,(即重复值比较多)查询的值实际不存在时,优化器估算的数据量不是0,而是1/ndv。 2. 如果使用绑定参数,必须支持变量窥视,才能根据实际参数生成执行计划,否则还是按照1/ndv估算。 >`变量窥视`:当一条SQL语句在硬解析时如果有绑定变量,会查看这个变量值,有了变量值便于得出正确的执行计划,在后续软解析过程中将不再查看这个变量值了。 3. 当ndv很高,数据有不均衡时,可能估算的值与实际情况的值差距很大。 ​ #### 列应不应该索引创建? 1. 列必须出现在where中。 2. 选择性必须大于20%。选择性(ndv除以总数) 3. 总数有一定规模,大于几百几千条。 #### 什么样的列需要收集直方图。 1. 列必须出现在where中。 2. 选择性比较小,并且分部不均衡。 ### 回表 `回表`:当对一个列创建索引之后,索引会包含该列的键值以及键值对应行所在的rowid。通过索引中记录的rowid访问表中的数据就叫回表。 > 回表一般是单块读,回表次数太多导致I/O比较多会严重影响SQL性能 。 ### 聚集因子 `聚集因子`:一个索引对应数据在表中的顺序性,它的大小介于表的块数和表行数之间。 > 聚集因子只会影响索引范围扫描(INDEX RANGE SCAN)以及索引全扫描(INDEX FULL SCAN) ,这两种索引扫描方式会有大量数据回表。 #### 如何降低聚集因子的影响 。 1.重建表,按照某个索引列的顺序 2.通过组合索引避免回表 3.使用Oracle的cluster table 4.增大buffer cache将大部分数据缓存到内存或者使用内存表 #### 什么是统计信息 收集统计信息是为了让优化器选择最佳执行计划,以最少的代价(成本)查询出表中的数据。 > 统计信息的过期 > 当stale_stats=YES,表示统计信息已经陈旧,需要重新收集。 ### 动态采样 如果一个表从来没收集过统计信息,默认情况下Oracle会对表进行动态采样(Level=2)以便优化器估算出较为准确的Rows,动态采样的最终目的就是为了让优 化器能够评估出较为准确的Rows。 当系统中有全局临时表,就需要使用动态采样,因为全局临时表无法收集统计信息,建议对全局临时表至少启用level 4进行采样。 Oracle获取执行计划 Autotrace :性能分析工具。 ### 执行计划 过滤谓词(filter):过滤条件,不影响访问路径,只起过滤作用 访问谓词(access):索引扫描条件,影响访问路径 ### 访问路径 访问路径指的就是通过哪种扫描方式获取数据,比如全表扫描、索引扫描或者直接通过ROWID获取数据。 Oracle最小的存储单位是块(`block`),物理上连续的块组成了区(`extent`),区又组成了段(`segment`)。 区里面的数据块在物理上是连续的,所以全表扫描可以多块读 如果表中有部分块已经缓存在buffer cache中,在进行全表扫描的时候,扫描到已经被缓存的块所在区时,就会引起I/O中断。 从磁盘1次读取1个块到buffer cache就叫`单块读`。 从磁盘1次读取多个块到buffer cache就叫`多块读`。 如果数据块都已经缓存在buffer cache中,那就 不需要物理I/O了,没有物理I/O也就不存在单块读与多块读。 返回表中总行数5%以内的数据,走`索引扫描`,超过5%走`全表扫描`。
点赞 评论 引用 举报

Alex_Cool

发帖: 0粉丝: 0

级别 : 新手上路

发消息 + 关注

发表于2020-4-20 21:43:02
直达本楼层的链接
11#
显示全部楼层

华为云账号:alex_cool  微信昵称:海之梦

聚集因子 是索引数据顺序和表顺序的一致性程度,是可以影响索引回表。

执行计划是可以被优化的,方法一般有:收集统计信息,动态采样,变量窥视。

数据库逻辑结构:extent是连续的磁盘空间,分区表的一个分区是一个segment,segment可以跨越数据文件。

全表扫描可能比索引扫描快,index fast full scan是全表扫描的一种替代方案,执行计划中的access谓词表示该条件通过索引扫描。

支持多块读的扫描方式有:全表扫描,索引快速全扫描。

索引范围扫描,索引全扫描,这俩个方法扫描方式的结果是有序的。

回表:数据库根据索引找到了指定的记录所在行后,还需要根据rowid再次到数据块里取数据的操作。


点赞 评论 引用 举报

游客

富文本
Markdown
您需要登录后才可以回帖 登录 | 立即注册