Oracle优化之SQL

SQL(StructURED Query Language)：结构化查询语言

在进行Oracle优化之前，先让我们花时间来聊聊SQL，并了解下SQL的前世今生。
在此先通过3W原则来讲下SQL：

• what :
  SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). It is particularly useful in handling structured data。
  wiki里的解释翻译过来就是：SQL是用来设计，编程和处理关系型数据库的一门专用语言。
• why:
  SQL有什么优点，为啥要使用它呢？事实上SQL最大的优点是基本符合英语句子结构，简单易学(对于英语好的人来说)，其次它在操纵关系型数据库上很好用。但也仅限于关系型数据库。
• how：
  SQL语法众多，而且很多方言(只使用本数据库的语言)，所以较杂，所以说一下子想学会是很难的，更何况还有调优的大山。

SQL的执行

sql的执行其实是很复杂的，不同的数据库方案，不同的版本之间的差异都不小。但大抵是如下顺序：

1. 输入：SQL语句通过I/O进入数据库管理系统(DBMS)
2. 检查：DBMS检查SQL语句，包括语法检查，权限检查，语义检查等
3. 解析：DBMS将合格的SQL语句拆分成关系代数语法树
4. 优化：优化器将语法树进行分析，收集统计信息，分析执行计划，选出最优的执行计划(这过程可能会打乱语法树的结构，而且只是在优化器不抽筋的情况下，换言之，优化器是有可能抽筋的，这时候的执行计划就可能很烂，需要人工干预，也就是人工SQL优化)，选择出实际执行计划。
5. 执行计划：按上面的执行计划执行，并获取组织结果
6. 返回：将结果返回

Hint

如果用过ORACLE的同学，多多少少都用过或者听说过Hint，但好像很少人会去了解Hint是什么，为啥要有Hint，Hint的工作原理又是什么？

Hint工作原理

想要明白Hint为啥起作用，先要明白SQL执行的第四步-优化是怎么实现的。目前ORACLE有两种优化计算方式，老版本用的是RBO，现在是CBO。

1. 基于规则的优化方式(Rule-Based Optimization，简称为RBO**)
2. 基于代价的优化方式(Cost-Based Optimization，简称为CBO**)

RBO就像是赛车，来了一辆交通工具，我不知道它能跑多快，但我知道是普通汽车还是赛车，如果是五菱神车我就让它在低速段赛车，赛车就在高速端赛车。

CBO就像是玩LOL，我看着对面选整阵容，先一顿分析，然后选择克制英雄，这样就能很大程度保证胜率。

这两种方式各有优略。
对于RBO，因为它一股脑的分类，所以上来分类完了就开始执行，所以解析就快，如果说没啥花里胡哨的东西，大家都差不多，效率是很高的。但这种分类对于藤原豆腐店这种改装车是毫无辨别能力的，明明赛得过GTR，让人家和五菱神车打就很过分。
而对于CBO，每次跑之前，先一顿分析，选好阵容，能很大程度保证战绩。但问题是一来分析阵容是需要时间的，其二最怕一顿操作猛如虎，一看战绩0-5。大概率赢不代表一定赢。

而Hint就是两种方法的折中，正常情况下，ORACLE通过CBO分析代价，然后选择其认为代数组合来获得其认为的最快速度。但这就出现两个问题

1. CBO需要先分析各种情况，然后再选，这也耗费时间和精力
2. CBO选的不一定就是最好的

基于这两种问题，Hint诞生了，Hint是基于CBO的情况下，写死某些代数组合，这样CBO的选择面就窄很多，犯错的机率也小很多，甚至就不会犯错。这样就在一定程度上解决了上面两个问题。
但同样的其也带来了两个问题

1. Hint因为写死了部分规则，所以一旦数据组织方式发生变化，规则无法变通导致可能瞬间整个SQL执行时间完全不同。
2. 因为Hint是人为制定了SQL执行的路牌，所以需要写Hint的人考虑得极其周全，既要详细了解此SQL涉及的各个数据的组织方式，还需要预想未来可能发生的状况。还需要明白加什么Hint，在那加Hint

因为这两点，很多人要么视Hint如洪水猛兽，要么当成万能灵药，这两种情况都是万万要不得的。
我们主管有句话说的很好：写一句SQL，你要明白它是怎么执行的，执行需要多少秒，在写完之后对其就已经有了心里预期。当然很多时候我们还达不到精准的水平，明确知道其应该跑多少秒，像、我也达不到，但我们要做的是对其有心里预期，达到定性，争取定量。优化完之后最起码要保证其执行计划的代价能够减少。

索引

索引可能是除了SQL以外接触最多的关系型数据库的名词了，但索引很还是有很多东西值得说道说道的。

1. 什么是索引

首先，索引是一张表，本质上和其它表并没有什么区别，明白了这一点就已经能理解很多东西了。
其二，索引其实并非数据库的词汇，其是书籍管理词汇，索是绳索的意思，引是牵引的意思，这玩意说白了就是个竹简编的，用来查找其它书在哪的东西。其实关系型数据库本身就是用来管理书籍之类的东西，所以很多时候拿个图书馆来做类比，能很快理解很多东西。

2. 为什么要用索引

举个图书馆的例子，我想要到牛津图书馆借本《XXX》，但图书馆这么大，我咋知道它在哪呢？有下面几种方式：

1. 我到前台区问XX类的书籍在哪，工作人员会告诉你说在XX楼xx房间，XX书柜到XX书柜。
2. 我在前台的自助机器查，我知道了这书在XX楼XX房间XX书柜。
3. 问钱钟书，他知道牛津图书馆的XXX书在那个楼那个房间，那个书柜，第几本，甚至你问他内容他可以告诉你说在XX页的XX行。
   上面这3个例子其实就已经把索引说的很明白了，首先索引是用来找东西的，有了索引就不至于瞎摸(全表扫描)，但索引也有区别，有些索引能告诉你在XX房间(缩小查找范围)，但有些索引能告诉你精准位置，比如像钱老这样的变态级人物。然后就是索引不是说突然就知道了，它是需要事先收集信息，需要维护，你放了一本书进去，但上面的人都不知道，那这索引是完全无效的(索引失效)。其次就是索引的维护代价，如果我只知道这书在那个房间，那我的信息量就很小，维护起来也就简单，
   而如果是需要达到钱老的程度，那就需要把这本书精读一边，代价是不可同日而语的。

3. 索引的结构

在索引结构设计的，前人是踩过很多坑的，现在的关系型数据库的索引结构基本都是B+树结构，一句话来概况就是索引的索引，基本3层就能达到覆盖2KW数据吗，4层就能覆盖2E数据。其原理就是一个倒过来的树，我先进根节点索引，然后从根节点索引进入下一级索引，然后下一级索引再进入下一级索引。最底层的索引之间还通过链表关联，所以速度是极快的。3层的基本就3次就获取到了，每次万分之1秒。

4. 索引有啥需要注意的地方

有了上面的铺垫，相信你对索引的应用场景和使用原理都差不多懂了，而在此基础上，注意点也应该差不多发现了。

1. 索引本质是表，索引是需要维护的，而维度是有代价的，所以大表，特别是频繁UPDATE,INSERT，DELETE的大表强烈不建议加索引，其代价是非常高昂的。
2. 索引并不是说一定有效的，所以需要注意其使用方式，特别是最左前缀原则和DML的破坏。
3. 索引也不一定要是精准的，其本质是缩小查找范围，从这个定义来说分区其实也算索引，所以有分区索引嘛。而且越精准的索引，其维护起来代价越高，其失效的可能性也就越高。所以不要过分追求索引的精准性。万事平衡为佳。
4. 明确为什么要用索引，希望达到什么效果。