首席DBA用SQL洪荒之力，造一把通向数据库的钥匙

《首席DBA用SQL洪荒之力，造一把通向数据库的钥匙》要点：
本文介绍了首席DBA用SQL洪荒之力，造一把通向数据库的钥匙，希望对您有用。如果有疑问，可以联系我们。

本文根据DBAplus社群第70期线上分享整理而成

讲师介绍

韩锋宜信技术研发中心数据库架构师

• 精通多种关系型数据库,曾任职于当当网、TOM在线等公司,曾任多家公司首席DBA、数据库架构师等职,多年一线数据库架构、设计、开发经验.著有《SQL优化最佳实践》一书.

大家好,很高心今天有机会跟大家做这个分享.其次,感谢社群工作人员,组织保证这个活动得以开展.本次分享的题目是《SQL-通向数据库的一把钥匙》.希望通过此次的分享,让大家对SQL(这一古老的语言),有一种全新的认识.本人技术能力有限,只是把一些心得跟大家分享,希望能够与大家共同学习、共同提高.本次分享主要包括以下部分：

1、SQL语言的历史发展及重要性2、在新环境下SQL面临的各种疑问3、SQL优化法则、执行过程4、SQL优化器5、解读执行计划6、优化案例

一、SQL ：一种熟悉又陌生的编程语言

这里有几个关键词；“熟悉”、“陌生”、“编程语言”.

说它“熟悉”,是因为它是DBA和广大开发人员,操作数据库的主要手段,几乎每天都在使用.说它“陌生”,是很多人只是简单的使用它,至于它是怎么工作的?如何才能让它更高效的工作?却从来没有考虑过.

这里把SQL归结为一种“编程语言”,可能跟很多人对它的认知不同.让我们看看它的简单定义(以下内容摘自百度百科)

结构化查询语言(Structured Query Language),简称SQL,是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统.结构化查询语言是高级的非过程化编程语言,允许用户在高层数据结构上工作.它不要求用户指定对数据的存放方法,也不需要用户了解具体的数据存放方式,所以具有完全不同底层结构的不同数据库系统, 可以使用相同的结构化查询语言作为数据输入与管理的接口.结构化查询语言语句可以嵌套,这使它具有极大的灵活性和强大的功能.

总结一句话,SQL是一种非过程化的的编程语言,可通过它去访问关系型数据库系统.

从上面两张图可以发现,在近二十年来,SQL语言一直稳定出现在10～20名左右.它不同于一般的通用编程语言,作为一种功能较为“单一”的语言,能长期保持这样的排名,实属不易.这也印证了SQL语言的广泛流行性.

二、你真的了解“SQL”吗?

下面我会通过一个小例子,看看大家是否真正了解SQL.

这是一个很简单的示例,是关于SQL语句执行顺序的.这里将一个普通的SELECT语句,拆分为三个子句.那么在实际的执行过程中,是按照什么顺序处理的呢?这里有A-F六个选项,大家可以思考选择一下…

最终的答案是D,即按照先执行FROM子句,然后WHERE子句,最后是SELECT部分.

针对上面的示例,让我们真实构造一个场景,通过查看执行计划看看是否按照我们选择的顺序执行的.关于执行计划的判读,我后面会专门谈到.这里我先解释一下整个执行过程.

• 第一步,是按照全表扫描的方式访问了对象表(EMP).对应于语句中的FROM部分.
• 第二步,是对提取出的结果集进行了过滤(filter部分),即将满足条件的记录筛选出来.对应于语句中的WHERE部分.
• 第三步,是对满足条件的记录进行字段投射,即将需要显示的字段提取出来.对应于语句中的SELECT部分.

这是一个详细的SQL各部分执行顺序的说明.

通过对执行顺序的理解,可以为我们未来的优化工作带来很大帮助.一个很浅显的认识就是,优化动作越靠前越好.

三、SQL现在是否仍然重要?

这里引入了一个新的问题,在现有阶段SQL语言是否还重要?

之所以引入这一话题,是因为随着NOSQL、NEWSQL、BIGDATA等技术逐步成熟推广,“SQL语言在现阶段已经变得不那么重要”成为一些人的观点.那实际情况又是如何呢?

让我们先来看一张经典的图.图中描述了传统SMP架构的关系型数据库、MPP架构的NEWSQL、MPP架构的NoSQL不同方案的适用场景对比.

从上面的“数据价值密度、实时性”来看,传统关系型数据库适合于价值密度更高、实时性要求更高的场景(这也就不难理解类似账户、金额类信息都是保存在传统关系型数据库中)；MPP架构的NewSQL次之,MPP架构的NoSQL更适合于低价值、实时性要求不高的场景.

从下面的“数据规模”来看,传统关系型数据库适合保存的大小限制在TB级别,而后两者可在更大尺度上(PB、EB)级保存数据.

从下面的“典型场景”来看,传统关系型数据库适合于OLTP在线交易系统；MPP架构的NewSQL适合于OLAP在线分析系统；而NoSQL的使用场景较多(利于KV型需求、数据挖掘等均可以考虑).

最后从“数据特征”来看,前两者适合于保存结构化数据,后者更适合于半结构化、乃至非结构化数据的保存.

归纳一下,不同技术有其各自特点,不存在谁代替谁的问题.传统关系型数据库有其自身鲜明特点,在某些场合依然是不二选择.而作为其主要交互语言,SQL必然长期存在发展下去.

我们再来对比一下传统数据库与大数据技术.从数据量、增长型、多样化、价值等维度对比两种技术,各自有其适用场景.

对于大数据领域而言,各种技术层出不穷.但对于广大使用者来说,往往会存在一定的使用门槛,因此现在的一种趋势就是在大数据领域也引入“类SQL”,以类似SQL的方式访问数据.这对于广大使用者来说,无疑大大降低了使用门槛.

解答一些疑问：

1. NoSQL、NewSQL已经超越了传统数据库,SQL没有了用武之地!各种技术有着各自适合的不同场景,不能一概而论.SQL语言作为关系型数据库的主要访问方式,依然有其用武之地.
2. 以后都是云时代了,谁还用关系型数据库!对于价值密度高,严格一致性的场景,仍然适合采用关系型数据库作为解决方案.
3. 我编程都是用OR Mapping工具,从不需要写SQL!的确,引入OR Mapping工具大大提高了生产效率,但是它的副作用也很明显,那就是对语句的运行效率失去了控制.很多低效的语句,往往是通过工具直接生成的.这也是为什么有的Mapping工具还提供了原始的SQL接口,用来保证关键语句的执行效率.
4. 大数据时代,我们都用Hadoop、Spark了,不用写SQL啦!无论是使用Hadoop、Spark都是可以通过编写程序完成数据分析的,但其生产效率往往很低.这也是为什么产生了Hive 、Spark SQL等“类SQL”的解决方案来提高生产效率.
5. 数据库处理能力很强,不用太在意SQL性能!的确,随着多核CPU、大内存、闪存等硬件技术的发展,数据库的处理能力较以前有了很大的增强.但是SQL的性能依然很重要.后面我们可以看到,一个简单SQL语句就可以轻易地搞垮一个数据库.
6. SQL优化,找DBA就行了,我就不用学了!SQL优化是DBA的职责范畴,但对于开发人员来讲,更应该对自己的代码负责.如果能在开发阶段就注重SQL质量,会避免很多低级问题.
7. 我只是个运维DBA,SQL优化我不行!DBA的发展可分为“运维DBA->开发DBA->数据架构师…”.如果只能完成数据库的运维类工作,无疑是技能的欠缺,也是对各人未来发展不利.况且,随着Paas云的逐步推广,对于数据库的运维需求越来越少,对于优化、设计、架构的要求越来越多.因此,SQL优化是每个DBA必须掌握的技能.
8. 现在优化有工具了,很简单的!的确现在有些工具可以为我们减少些优化分析工作,会自动给出一些优化建议.但是,作为DBA来讲,不仅要知其然,还要知其所以然.况且,数据库优化器本身就是一个非常复杂的组件,很难做到完全无误的优化,这就需要人工的介入,分析.
9. 优化不就是加索引嘛,这有啥!的确,加索引是一个非常常用的优化手段,但其不是唯一的.且很多情况下,加了索引可能导致性能更差.后面,会有一个案例说明.

四、SQL仍然很重要!

我们通过一个示例,说明一下理解SQL运行原理仍然很重要.

这是我在生产环境碰到的一个真实案例.Oracle数据库环境,两个表做关联.执行计划触目惊心,优化器评估返回的数据量为3505T条记录,计划返回量127P字节,总成本9890G,返回时间999:59:59.

从执行计划中可见,两表关联使用了笛卡尔积的关联方式.我们知道笛卡尔连接是指在两表连接没有任何连接条件的情况.一般情况下应尽量避免笛卡尔积,除非某些特殊场合.否则再强大的数据库,也无法处理.这是一个典型的多表关联缺乏连接条件,导致笛卡尔积,引发性能问题的案例.

从案例本身来讲,并没有什么特别之处,不过是开发人员疏忽,导致了一条质量很差的SQL.但从更深层次来讲,这个案例可以给我们带来如下启示：

1. 开发人员的一个疏忽,造成了严重的后果,原来数据库竟是如此的脆弱.需要对数据库保持一种”敬畏”之心.
2. 电脑不是人脑,它不知道你的需求是什么,只能用写好的逻辑进行处理.
3. 不要去责怪开发人员,谁都会犯错误,关键是如何从制度上保证不再发生类似的问题.

五、SQL优化法则

下面我们来看看常见的优化法则.这里所说的优化法则,其实是指可以从那些角度去考虑SQL优化的问题.可以有很多种方式去看待它.下面列举一二.

这里来自阿里-叶正盛的一篇博客里的一张图,相信很多人都看过.这里提出了经典的漏斗优化法则,高度是指我们投入的资源,宽度是指可能实现的收益.从图中可见,“减少数据访问”是投入资源最少,而收益较多的方式；“增加硬件资源”是相对投入资源最多,而收益较少的一种方式.受时间所限,这里不展开说明了.

这是我总结的一个优化法则,简称为“DoDo”法则.

• 第一条,“Do Less or not do!”翻译过来,就是尽量让数据库少做工作、甚至不做工作.

怎么样来理解少做工作呢?比如创建索引往往可以提高访问效率,其原理就是将原来的表扫描转换为索引扫描,通过一个有序的结构,只需要少量的IO访问就可以得到相应的数据,因此效率才比较高.这就可以归纳为少做工作.

怎么样来理解不做工作呢?比如在系统设计中常见的缓存设计,很多是将原来需要访问数据库的情况,改为访问缓存即可.这样既提高了访问效率,又减少了数据库的压力.从数据库角度来说,这就是典型的不做工作.

• 第二条,“If must do,do it fast!”翻译过来,如果数据库必须做这件事件,那么请尽快做完它.

怎么样来理解这句话呢?比如数据库里常见的并行操作,就是通过引入多进程来加速原来的执行过程.加速处理过程,可以少占用相关资源,提高系统整体吞吐量.

六、SQL 执行过程

SQL的执行过程比较复杂,不同数据库有一定差异.下面介绍以两种主流的数据库(Oracle、MySQL)介绍一下.

1. 用户提交了一条SQL语句
2. 数据库按照SQL语句的字面值计算出一个HASH值
3. 根据HASH值,判断一下在数据库缓冲区中是否存在此SQL的执行计划.
4. 如果不存在,则需要生成一个执行计划(硬解析过程),然后将结果存入缓冲区.
5. 如果存在的话,判断是否为相同SQL(同样HASH值的语句,可能字符不相同；即使完全相同,也可能代表不同的语句.这块不展开说了)
6. 确认是同一条SQL语句,则从缓冲区中取出执行计划.
7. 将执行计划,交给执行器执行.
8. 结果返回给客户端.

1. 客户提交一条语句
2. 现在查询缓存查看是否存在对应的缓存数据,如有则直接返回(一般有的可能性极小,因此一般建议关闭查询缓存).
3. 交给解析器处理,解析器会将提交的语句生成一个解析树.
4. 预处理器会处理解析树,形成新的解析树.这一阶段存在一些SQL改写的过程.
5. 改写后的解析树提交给查询优化器.查询优化器生成执行计划.
6. 执行计划交由执行引擎调用存储引擎接口,完成执行过程.这里要注意,MySQL的Server层和Engine层是分离的.
7. 最终的结果有执行引擎返回给客户端,如果开启查询缓存的话,则会缓存.

七、SQL优化器

在上面的执行过程描述中,多次提高了优化器.它也是数据库中最核心的组件.下面我们来介绍一下优化器.

上面是我对优化器的一些认识.优化器是数据库的精华所在,值得DBA去认真研究.但是遗憾的是,数据库对这方面的开放程度并不够.(相对来说,Oracle还是做的不错的)

这里我们看到的MySQL的优化器的工作过程,大致经历了如下处理：

1. 词法分析、语法分析、语义检查
2. 预处理阶段(查询改写等)
3. 查询优化阶段(可详细划分为逻辑优化、物理优化两部分)
4. 查询优化器优化依据,来自于代价估算器估算结果(它会调用统计信息作为计算依据)
5. 交由执行器执行

此图是DBAplus社群MySQL原创专家李海翔对比不同数据库优化器技术所总结的.从这里可以看出：

1. 不同数据库的实现层次不同,有些支持、有些不支持
2. 即使支持,其实现原理也差异很大
3. 这只是列出了一小部分优化技术
4. 以上对比,也可以解释不同数据库对同样语句的行为不同.下面会有一个示例说明

八、SQL 执行计划

看懂执行计划是DBA优化的前提之一,它为我们开启一扇通往数据库内部的窗口.但是很遗憾,从没有一本书叫做“如何看懂执行计划”,这里的情况非常复杂,很多是需要DBA常年积累而成.

这是Oracle执行计划简单的示例,说明了执行计划的大致内容.

九、几个案例分享

前面讲了很多理论内容,下面通过几个案例说明一下.方便大家对前面内容的理解.

• 案例1：数据库对比

第一个例子,是一个优化器行为的对比案例.示例对比了三种数据库(四种版本)对于同样语句的行为.通过这个例子,大家可以了解,不同数据库(乃至不同版本)优化器的行为不同.对于数据库选型、数据库升级等工作,要做到充分的评估测试,也正是出于此目的.

简单构造了两张测试表,主要注意的是前一个字段是包含空值的.

第一种情况,是对于IN子查询的处理.对于Oracle来说,10g、11g行为相同,这里就列了一个.

对于这样的一个例子,不同数据库已经表现出不同的差异.Oracle和PG的行为类似,MySQL由于不支持哈希连接,因此采用了其他处理方式.具体的技术细节,这里不展开说明了.

第二种情况,是对于NOT IN子查询的处理.这种情况下,Oracle的不同版本、PG和MySQL表现出不同的行为.从上面例子可以看出,11g的优化器在处理此种情况是更加智能一些.

• 案例2：解决“ERP汇单慢”问题

这里我构造了类似的结构,模拟了上线的情况.

示例是一个关联子查询,其核心部分是转化为一个表关联,并使用了嵌套循环的一个变体-Filter实现关联方式.显然,如果外层表过大或内层探查效率过低,其执行效率可想而知.通常来说,两表关联,嵌套循环是最后的一种选择,如果能使用其他方式(例如HASH JOIN、SORT MERGE)可能会带来更好的效果.

这里优化器没有选择更优的计划,是优化器的Bug?还是功能所限?可通过人工手段干预,看看是否能达到意向不到的效果.

引入了一个Hint-unnest,主动实现子查询的解嵌套.将子查询部分提前,让优化器有了更多的选择.从执行计划来看,优化器生成了一个内联视图,然后跟外部表实现了一个哈希连接,整体效率大大提高.

这个示例说明,优化器的功能还是有所局限.在某些场合,可以人工干预语句的执行,提升整体执行效率.

• 案例3：处理“ERP清理数据”问题

下面这个示例,是因为结构设计不良导致的问题.

在日常的优化中,我们往往遵循着“语句级、对象级、架构级、业务级”的顺序考虑优化策略.但在项目需求、设计阶段,是按照反向的顺序进行.后者的影响力要远远大于前者.一个糟糕的对象结构设计,可能会带来一系列SQL的问题.示例中,就是这样的一个问题.

这是某公司后台的ERP系统,系统已经上线运行了10多年.随着时间的推移,累积的数据量越来越大.公司计划针对部分大表进行数据清理.在DBA对某个大表进行清理中,出现了问题.这个表本身有数百G,按照指定的清理规则只需要根据主键字段范围(>=)选择出一定比例(不超过10%)的数据进行清理即可.但在实际使用中发现,该SQL的是全表扫描,执行时间大大超出预期时间.DBA尝试使用强制指定索引方式清理数据,依然无效.

这套ERP系统历史很久远,相关信息已经找不到了.只能从纯数据库的角度进行分析,这是一个普通表(非分区表)按照主键字段的范围查询一批记录进行清理.按照正常理解,执行索引范围扫描应该是效率较高的一种处理方式,但实际情况确实全表扫描.进一步分析发现,该表的主键是没有业务含义的,仅仅是自增长的数据,其来源是一个序列.但奇怪的是,这个主键字段的类型是变长文本类型,而不是通常的数字类型.现在已经无从考证,当初定义该字段类型的依据,但实验表明正是这个字段的类型“异常”,导致了错误的执行路径.

下面构造了一个测试环境.

可以很好的复现案例的问题.选择少范围数据,文本方式依然走的全表扫描,数字方式走的索引扫描.效率高低,显而易见.

大家头脑中可以构想出一棵索引树结构,对于字符串来说,这个有序的结构该如何存放?是与你预期一样的吗?

知道了问题所在,该如何处理呢?修改结构无疑成本太高,不具备可操作性.这里所采取的策略是“局部有序”.利用修改语句中条件的范围,由开放区间变为封闭区间,影响基数的选择.(关于这部分,大家有兴趣可多看看《基于成本的Oracle优化》一书)

如仍然不起作用,可考虑进一步细化分段或干脆采用“逐条提取+批绑定”的方式解决.

一个小小的数据类型设置不当,会为我们后面的工作带来的多大的麻烦.

• 案例4： “抽丝剥茧”找出问题所在

这里会描述一次完整的优化过程,看看DBA是如何“抽丝剥茧”,发现问题本质的.

这个案例本身不是为了说明某种技术,而是展现了DBA在分析处理问题时的一种处理方式.其采用的方法往往是根据自己掌握的知识,分析判断某种可能性,然后再验证确认是否是这个原因.在不断的抛出疑问,不断的验证纠错中,逐步接近问题的本质.

也想通过这个示例,告知广大开发人员,DBA优化语句的不容易.

这是某数据仓库系统,有一个作业在某天出现较大延迟.为了不影响明天的业务系统,必须在今天解决这个问题.经和开发人员的沟通,该业务的SQL语句没有修改,相关的数据结构也没有变更相类似的其他业务(SQL语句相似的)也都正常运行,数据库系统本身也没有异常.

修改后执行计划,跟其他类似SQL相同了.整个计划可概述为”HASH JOIN” + “FULL TABLE SCAN”.经测试,速度略有提升,但是整个运行时间仍然超过2个小时.

开始了第一次尝试,开始想到的方法很简单,既然类似的SQL执行效率没问题,而这个SQL由于其他SQL执行计划偏差较大,我可以手工采取固化执行计划的方法.这里使用了抽取OUTLINE的方式.经测试,对速度提升不大,不知问题主因.

第二次尝试,从等待事件角度入手.首先考虑的是和缓存有关的问题.

不要盲目相信别人的话,优化之前先按照故有流程检验一遍,做到心中有数.对于此例来说,可以大大加快问题的解决.

Q&A

Q1：ANSI 的SQL标准,会一直推出新版本吗? 后续版本是否会加入新的语法和特性呢?

A1：这个问题没有仔细考虑过,ANSI-SQL的标准一直在变化,不同的数据库根据自身情况实现了它的子集.从我个人角度来看,未来ANSI-SQL可能会对大数据、数据挖掘方向有所考虑,加入部分新语法或特性.毕竟SQL接口作为人们最为熟悉的数据访问接口,未来在大数据等方向大有可为.

 

Q2：优化SQL最终的目的是不是改变SQL执行计划?

A2：第一目的,是理解现有优化器选择的行为,并考虑是否是最佳选择.第二目的,是在优化器功能有所局限的情况下,通过人工介入的方式,让数据库以更优的方式执行SQL.毕竟人要比电脑更理解数据.

 

Q3：能不能介绍一下开发中,数据类型的选择对数据库的影响?

A3：数据类型在优化层面,主要可从以下角度考虑：

1. 选择“合适”的类型存储数据.注意,这里使用的词是“合适”,要确保精度、够用、不浪费的原则.
2. 数据类型在数据库自身存储、计算上的特性,不同类型的效率是不同的.
3. 类型间要做到兼容,保证关联字段的类型一致性.

 

Q4：能不能介绍下oracle数据迁移的常用方式和利弊?

A4：这个有很多,取决于迁移的需求,比如常用的：

1.备份、恢复；2.逻辑导入、导出(含传输表空间等)；3.DATAGUARD；4.LOG SYNC(例如OGG等)；5.程序同步……利弊,主要取决于成本、代价了,每种方案都有自身的适用场景.

 

Q5：请问必须全表扫描的语句有什么优化思路?

A5：必须用全表扫描的情况,就适用于分享中的“DoDo”原则第二条,尽量让其更快的完成.可考虑的策略有：

1. DIRECT PATH READ
2. 加大MULTI BLOCK READ COUNT
3. 启用PARALLEL
4. 更好的IO

Q6：对于group by语句如何优化?

A6：对于分组来说,Oracle 11g以后的版本提供了HASH GROUP BY的实现.HASH是个重内存消耗操作,可从内存使用角度基于优化考虑.

 

Q7：访问路径是会缓存起来的,怎么判断回收没用的缓存中的访问路径呢?

A7：一般不需要考虑回收问题,如果非要做可从内存信息中了解此执行计划是否最近被使用,使用DBMS包清除即可.

 

Q8：oracle发现在云机上安装之后,在并发性方面不行,这是为什么?

A8：不同云的实现策略不同.并发性方面,可考虑从vCPU使用、IO等方面着手.这方面经验不多,抱歉!

 

Q9：全表扫描想办法修改为索引全表扫描是否合适?使用with子句来优化sql,这个手段如何?

A9：将全表扫描修改为索引全扫描,根本原则是能够缩小访问量,即让数据库干更少的活.

WITH子句,定义查询块,一个目的是减少多次引用,但也有可能出现不允许执行查询语句变形的情况,要具体分情况分析.

文章出处：DBAplus社群

转载请注明本页网址：
http://www.vephp.com/jiaocheng/4452.html