共计 8280 个字符，预计需要花费 21 分钟才能阅读完成。
一、SQL：一种熟悉又陌生的编程语言
这里有几个关键词；“熟悉”、“陌生”、“编程语言”。
说它“熟悉”，是因为它是 DBA 和广大开发人员，操作数据库的主要手段，几乎每天都在使用。说它“陌生”，是很多人只是简单的使用它，至于它是怎么工作的？如何才能让它更高效的工作？却从来没有考虑过。
这里把 SQL 归结为一种“编程语言”，可能跟很多人对它的认知不同。让我们看看它的简单定义（以下内容摘自百度百科）
结构化查询语言(Structured Query Language)，简称 SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统, 可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套，这使它具有极大的灵活性和强大的功能。
总结一句话，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 的方式访问数据。这对于广大使用者来说，无疑大大降低了使用门槛。
解答一些疑问：
NoSQL、NewSQL 已经超越了传统数据库，SQL 没有了用武之地！
各种技术有着各自适合的不同场景，不能一概而论。SQL 语言作为关系型数据库的主要访问方式，依然有其用武之地。
以后都是云时代了，谁还用关系型数据库！
对于价值密度高，严格一致性的场景，仍然适合采用关系型数据库作为解决方案。
我编程都是用 OR Mapping 工具，从不需要写 SQL！
的确，引入 OR Mapping 工具大大提高了生产效率，但是它的副作用也很明显，那就是对语句的运行效率失去了控制。很多低效的语句，往往是通过工具直接生成的。这也是为什么有的 Mapping 工具还提供了原始的 SQL 接口，用来保证关键语句的执行效率。
大数据时代，我们都用 Hadoop、Spark 了，不用写 SQL 啦！
无论是使用 Hadoop、Spark 都是可以通过编写程序完成数据分析的，但其生产效率往往很低。这也是为什么产生了 Hive、Spark SQL 等“类 SQL”的解决方案来提高生产效率。
数据库处理能力很强，不用太在意 SQL 性能！
的确，随着多核 CPU、大内存、闪存等硬件技术的发展，数据库的处理能力较以前有了很大的增强。但是 SQL 的性能依然很重要。后面我们可以看到，一个简单 SQL 语句就可以轻易地搞垮一个数据库。
SQL 优化，找 DBA 就行了，我就不用学了!
SQL 优化是 DBA 的职责范畴，但对于开发人员来讲，更应该对自己的代码负责。如果能在开发阶段就注重 SQL 质量，会避免很多低级问题。
我只是个运维 DBA，SQL 优化我不行！
DBA 的发展可分为“运维 DBA-> 开发 DBA-> 数据架构师…”。如果只能完成数据库的运维类工作，无疑是技能的欠缺，也是对各人未来发展不利。况且，随着 Paas 云的逐步推广，对于数据库的运维需求越来越少，对于优化、设计、架构的要求越来越多。因此，SQL 优化是每个 DBA 必须掌握的技能。
现在优化有工具了，很简单的！
的确现在有些工具可以为我们减少些优化分析工作，会自动给出一些优化建议。但是，作为 DBA 来讲，不仅要知其然，还要知其所以然。况且，数据库优化器本身就是一个非常复杂的组件，很难做到完全无误的优化，这就需要人工的介入，分析。
优化不就是加索引嘛，这有啥！
的确，加索引是一个非常常用的优化手段，但其不是唯一的。且很多情况下，加了索引可能导致性能更差。后面，会有一个案例说明。
四、SQL 仍然很重要！
我们通过一个示例，说明一下理解 SQL 运行原理仍然很重要。
这是我在生产环境碰到的一个真实案例。Oracle 数据库环境，两个表做关联。执行计划触目惊心，优化器评估返回的数据量为 3505T 条记录，计划返回量 127P 字节，总成本 9890G，返回时间 999:59:59。
从执行计划中可见，两表关联使用了笛卡尔积的关联方式。我们知道笛卡尔连接是指在两表连接没有任何连接条件的情况。一般情况下应尽量避免笛卡尔积，除非某些特殊场合。否则再强大的数据库，也无法处理。这是一个典型的多表关联缺乏连接条件，导致笛卡尔积，引发性能问题的案例。
从案例本身来讲，并没有什么特别之处，不过是开发人员疏忽，导致了一条质量很差的 SQL。但从更深层次来讲，这个案例可以给我们带来如下启示：
- 开发人员的一个疏忽，造成了严重的后果，原来数据库竟是如此的脆弱。需要对数据库保持一种 ” 敬畏 ” 之心。
- 电脑不是人脑，它不知道你的需求是什么，只能用写好的逻辑进行处理。
- 不要去责怪开发人员，谁都会犯错误，关键是如何从制度上保证不再发生类似的问题。
五、SQL 优化法则
下面我们来看看常见的优化法则。这里所说的优化法则，其实是指可以从那些角度去考虑 SQL 优化的问题。可以有很多种方式去看待它。下面列举一二。
这里来自阿里 - 叶正盛的一篇博客里的一张图，相信很多人都看过。这里提出了经典的漏斗优化法则，高度是指我们投入的资源，宽度是指可能实现的收益。从图中可见，“减少数据访问”是投入资源最少，而收益较多的方式；“增加硬件资源”是相对投入资源最多，而收益较少的一种方式。受时间所限，这里不展开说明了。
这是我总结的一个优化法则，简称为“DoDo”法则。
第一条，“Do Less or not do!”翻译过来，就是尽量让数据库少做工作、甚至不做工作。
怎么样来理解少做工作呢？比如创建索引往往可以提高访问效率，其原理就是将原来的表扫描转换为索引扫描，通过一个有序的结构，只需要少量的 IO 访问就可以得到相应的数据，因此效率才比较高。这就可以归纳为少做工作。
怎么样来理解不做工作呢？比如在系统设计中常见的缓存设计，很多是将原来需要访问数据库的情况，改为访问缓存即可。这样既提高了访问效率，又减少了数据库的压力。从数据库角度来说，这就是典型的不做工作。
第二条，“If must do,do it fast!”翻译过来，如果数据库必须做这件事件，那么请尽快做完它。
怎么样来理解这句话呢？比如数据库里常见的并行操作，就是通过引入多进程来加速原来的执行过程。加速处理过程，可以少占用相关资源，提高系统整体吞吐量。
六、SQL 执行过程
SQL 的执行过程比较复杂，不同数据库有一定差异。下面介绍以两种主流的数据库（Oracle、MySQL）介绍一下。
- 用户提交了一条 SQL 语句
- 数据库按照 SQL 语句的字面值计算出一个 HASH 值
- 根据 HASH 值，判断一下在数据库缓冲区中是否存在此 SQL 的执行计划。
- 如果不存在，则需要生成一个执行计划(硬解析过程)，然后将结果存入缓冲区。
- 如果存在的话，判断是否为相同 SQL（同样 HASH 值的语句，可能字符不相同；即使完全相同，也可能代表不同的语句。这块不展开说了）
- 确认是同一条 SQL 语句，则从缓冲区中取出执行计划。
- 将执行计划，交给执行器执行。
- 结果返回给客户端。
- 客户提交一条语句
- 现在查询缓存查看是否存在对应的缓存数据，如有则直接返回(一般有的可能性极小，因此一般建议关闭查询缓存)。
- 交给解析器处理，解析器会将提交的语句生成一个解析树。
- 预处理器会处理解析树，形成新的解析树。这一阶段存在一些 SQL 改写的过程。
- 改写后的解析树提交给查询优化器。查询优化器生成执行计划。
- 执行计划交由执行引擎调用存储引擎接口，完成执行过程。这里要注意，MySQL 的 Server 层和 Engine 层是分离的。
- 最终的结果有执行引擎返回给客户端，如果开启查询缓存的话，则会缓存。
七、SQL 优化器
在上面的执行过程描述中，多次提高了优化器。它也是数据库中最核心的组件。下面我们来介绍一下优化器。
上面是我对优化器的一些认识。优化器是数据库的精华所在，值得 DBA 去认真研究。但是遗憾的是，数据库对这方面的开放程度并不够。（相对来说，Oracle 还是做的不错的）
这里我们看到的 MySQL 的优化器的工作过程，大致经历了如下处理：
- 词法分析、语法分析、语义检查
- 预处理阶段(查询改写等)
- 查询优化阶段(可详细划分为逻辑优化、物理优化两部分)
- 查询优化器优化依据，来自于代价估算器估算结果(它会调用统计信息作为计算依据)
- 交由执行器执行
此图是 DBAplus 社群 MySQL 原创专家李海翔对比不同数据库优化器技术所总结的。从这里可以看出：
- 不同数据库的实现层次不同，有些支持、有些不支持
- 即使支持，其实现原理也差异很大
- 这只是列出了一小部分优化技术
- 以上对比，也可以解释不同数据库对同样语句的行为不同。下面会有一个示例说明
八、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：数据类型在优化层面，主要可从以下角度考虑：
选择“合适”的类型存储数据。注意，这里使用的词是“合适”，要确保精度、够用、不浪费的原则。
数据类型在数据库自身存储、计算上的特性，不同类型的效率是不同的。
类型间要做到兼容，保证关联字段的类型一致性。
Q4：能不能介绍下 oracle 数据迁移的常用方式和利弊？
A4：这个有很多，取决于迁移的需求，比如常用的：
1. 备份、恢复；2. 逻辑导入、导出(含传输表空间等)；3.DATAGUARD；4.LOG SYNC（例如 OGG 等）；5. 程序同步……利弊，主要取决于成本、代价了，每种方案都有自身的适用场景。
Q5：请问必须全表扫描的语句有什么优化思路？
A5：必须用全表扫描的情况，就适用于分享中的“DoDo”原则第二条，尽量让其更快的完成。可考虑的策略有：
- DIRECT PATH READ
- 加大 MULTI BLOCK READ COUNT
- 启用 PARALLEL
- 更好的 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 社群分享
内容来源：宜信技术学院
