基于oracle的sql优化如何_oracle数据库物理结构

Oracle结构;

         主要包括两部分:1.Oracle实例 2.数据库文件

Orac le 实例: 
1．是访问 Oracle database 的途径 
2．只能打开一个数据库 
3．由 SGA 内存区和一组后台进程组成

Oracle数据库文件:

Orac le 数据库的物理结构是硬盘上的一组文件。主要有三类文件：即数据文件,日志文件和控制文件。 
? 数据文件包含数据库的实际数据，数据存于用户定义的表中，此外数据字典数据、回滚数据、索引等 均存于数据文件中。 
? 日志文件记录对数据库的所有修改，用于数据库的恢复。 
? 控制文件记录日志文件和数据文件的信息，用于保证数据库文件的一致性和完整性。 
归档日志文件是在线日志的拷贝，在归档模式下在线日志在重复使用之前必须归档。

当用户写入sql语句开始查询时，首先进入到PGA(PGA作用是保存连接信息和权限信息进行预处理)，sql语句在PGA会生成一个唯一的HASH值（类似于身份证）。然后进入到SGA的共享池里，在共享池里如果存在一样的HASH值，则直接进行sql语句解析。如果没有相同的HASH值那就要检查 sql语法是否正确和语义是否正确是否有权限，没问题后生成一个HASH值保存在共享池里，再进行sql语句解析。sql解析的时候优化器会分析诸如走索引还是不走索引等，最终选择一条效率最高的路线，将执行计划保存下来。下一步就是到数据缓冲区 去取数据了。如果恰好此时缓冲区有相应的数据，则取到该数据，查询结束。如果数据缓冲区没有数据，就麻烦了,得到磁盘中的数据文件去读取数据。经过磁盘IO查询时间会大大增加。查到了就返回查询结果，如果没有查到则返回空。

oracle 共享原理:

ORACLE将执行过的SQL语句存放在内存的共享池(shared buffer pool)中，可以被所有的数据库用户共享。

当你执行一个SQL语句(有时被称为一个游标)时，如果它和之前的执行过的语句完全相同，ORACLE就能很快获得已经被解析的语句以及最好的执行路径.。这个功能大大地提高了SQL的执行性能并节省了内存的使用。

Oracle的内部结构:
共享区:
共享SQL区:用户执行sql时，Oracle会将最近执行的sql语句的文本，编译后的语法分析树和执行计划存入共享区。而将sql语句中的变量值存入私有Sql区。当服务器再次执行相同的sql语句时，服务器进程将不再进行语句分析，而是直接执行共享sql区内已经存在的内容；
共享pl/sql区:Oracel执行一个PL/SQL语句前，会先将程序单元放入共享PL/SQL区，而程序单元内的SQL语句将被放到SQL区中，当需要再次执行相同的程序单元时，直接从内存中调用，不用再次访问磁盘；
结构控制区:
数据缓冲区: 该区是SGA中的一个高速缓冲区域，用来存储最近从数据文件中读取的数据块，表，索引数据库。当用户处理查询时，服务器进程会先从数据库缓冲区查找所需要的数据库，缓冲区中没有时才会访问磁盘数据；
重做日志缓冲区:
当用户通过sql语句更改数据后，服务器进程会在重做日志缓冲区中记录这些修改。数据库发生意外后，可通过此记录恢复数据
数据字典缓冲区:
它包含了数据库的结构，用户信息和数据库的表，视图等信息；还有所有表和视图的名词，以及权限等信息；

1.in 是把外表和那表作hash join，而exists是对外表作loop，每次loop再对那表进行查询,in适合内外表都很大的情况，exists适合外表结果集很小的情况
2:当用户写入sql语句开始查询时，首先进入到PGA(PGA作用是保存连接信息和权限信息进行预处理)，sql语句在PGA会生成一个唯一的HASH值（类似于身份证）。然后进入到SGA的共享池里，在共享池里如果存在一样的HASH值，则直接进行sql语句解析。如果没有相同的HASH值那就要检查 sql语法是否正确和语义是否正确是否有权限，没问题后生成一个HASH值保存在共享池里，再进行sql语句解析。sql解析的时候会优化器会分析诸如走索引还是不走索引等，最终选择一条效率最高的路线，将改执行计划保存下来。下一步就是到数据缓冲区 去取数据了。如果恰好此时缓冲区有相应的数据，则取到该数据，查询结束。如果数据缓冲区没有数据，就麻烦了得到磁盘中的数据文件去读取数据。经过磁盘IO查询时间会大大增加。查到了就返回查询结果，如果没有查到则返回空。

在Oracle中可以几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。
使用EXIST，Oracle系统会首先检查主查询，然后运行子查询直到它找到第一个匹配项，
这就节省了时间Oracle系统在执行IN子查询时，首先执行子查询，并将获得的结果列表存放在在一个加了索引的临时表中。

3.SQL Select语句完整的执行顺序：

1、from子句组装来自不同数据源的数据；

2、where子句基于指定的条件对记录行进行筛选；

3、group by子句将数据划分为多个分组；

4、使用聚集函数进行计算；

5、使用having子句筛选分组；

6、计算所有的表达式；

7、select 的字段；

8、使用order by对结果集进行排序。

(8)SELECT (9) DISTINCT (11) <TOP_specification> <select_list>

(1) FROM <left_table>

(3) <join_type> JOIN <right_table>

(2) ON <join_condition>

(4) WHERE <where_condition>

(5) GROUP BY <group_by_list>

(6) WITH {CUBE | ROLLUP}

(7) HAVING <having_condition>

(10) ORDER BY <order_by_list>

逻辑查询处理阶段简介：

1、 FROM：对FROM子句中的前两个表执行笛卡尔积(交叉联接)，生成虚拟表VT1。
2、 ON：对VT1应用ON筛选器，只有那些使为真才被插入到TV2。
3、 OUTER (JOIN):如果指定了OUTER JOIN(相对于CROSS JOIN或INNER JOIN)，保留表中未找到匹配的行将作为外部行添加到VT2，生成TV3。如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表位置。
4、 WHERE：对TV3应用WHERE筛选器，只有使为true的行才插入TV4。
5、 GROUP BY：按GROUP BY子句中的列列表对TV4中的行进行分组，生成TV5。
6、 CUTE|ROLLUP：把超组插入VT5，生成VT6。
7、 HAVING：对VT6应用HAVING筛选器，只有使为true的组插入到VT7。
8、 SELECT：处理SELECT列表，产生VT8。
9、 DISTINCT：将重复的行从VT8中删除，产品VT9。
10、ORDER BY：将VT9中的行按ORDER BY子句中的列列表顺序，生成一个游标(VC10)。
11、TOP：从VC10的开始处选择指定数量或比例的行，生成表TV11，并返回给调用者。

不走索引的几种情况：

1.where子句中使用 is null 和 is not null

2.where子句中使用函数

3.使用like ‘%T’ 进行模糊查询

4.where子句中使用不等于操作(包括：<>, !=, not colum >= ?, not colum <= ? ,可以使用or代替)

5.比较不匹配数据类型，例如：select * from tablewhere jlbh = 1；jlbh为varchar2类型字段

为啥使用 EXISTS 的 SQL 运行更快呢，有两个原因

可以`用到索引，如果连接列 (id) 上建立了索引，那么查询 Class_B 时不用查实际的表，只需查索引就可以了。
如果使用 EXISTS，那么只要查到一行数据满足条件就会终止查询， 不用像使用 IN 时一样扫描全表。在这一点上 NOT EXISTS 也一样
另外如果 IN 后面如果跟着的是子查询，由于 SQL 会先执行 IN 后面的子查询，会将子查询的结果保存在一张临时的工作表里（内联视图），然后扫描整个视图，显然扫描整个视图这个工作很多时候是非常耗时的，而用 EXISTS 不会生成临时表。

避免排序:
SQL 是声明式语言，即对用户来说，只关心它能做什么，不用关心它怎么做。这样可能会产生潜在的性能问题：排序，会产生排序的代表性运算有下面这些

GROUP BY 子句
ORDER BY 子句
聚合函数(SUM、COUNT、AVG、MAX、MIN)
DISTINCT
集合运算符(UNION、INTERSECT、EXCEPT)
窗口函数(RANK、ROW_NUMBER 等)
如果在内存中排序还好，但如果内存不够导致需要在硬盘上排序上的话，性能就会急剧下降，所以我们需要减少不必要的排序。怎样做可以减少排序呢。

ORACLE sql 的处理过程大致如下：

1.运用HASH算法，得到一个HASH值，这个值可以通过V$SQLAREA.HASH_VALUE 查看

 2.到shared pool 中的 library cache 中查找是否有相同的HASH值，如果存在，则无需硬解析，进行软解析

 3.如果shared pool不存在此HASH值，则进行语法检查，查看是否有语法错误

 4.如果没有语法错误，就进行语义检查，检查该SQL引用的对象是否存在，该用户是否具有访问该对象的权限

 5.如果没有语义错误，对该SQL进行解析，生成解析树，执行计划

 6.生成ORACLE能运行的二进制代码，运行该代码并且返回结果给用户

硬解析和软解析都在第5步进行

硬解析通常是昂贵的操作，大约占整个SQL执行的70%左右的时间，硬解析会生成执行树，执行计划，等等。

当再次执行同一条SQL语句的时候，由于发现library cache中有相同的HASH值，这个时候不会硬解析，而会软解析，

那么软解析究竟是干了什么呢?其实软解析就是跳过了生成解析树，生成执行计划这个耗时又耗CPU的操作，直接利用生成的执行计划运行

Oracle的索引原理:

建设原则:

　1、索引应该经常建在Where 子句经常用到的列上。如果某个大表经常使用某个字段进行查询，并且检索行数小于总表行数的5%。则应该考虑。

　2、对于两表连接的字段，应该建立索引。如果经常在某表的一个字段进行Order By 则也经过进行索引。

　3、不应该在小表上建设索引。

创建索引:
　单一索引:Create Index <Index-Name> On <Table_Name>(Column_Name);

　复合索引: Create Index i_deptno_job on emp(deptno,job); —>在emp表的deptno、job列建立索引。

　　select * from emp where deptno=66 and job=‘sals‘ ->走索引。

　　select * from emp where deptno=66 OR job=‘sals‘ ->将进行全表扫描。不走索引

　　select * from emp where deptno=66 ->走索引。

　　select * from emp where job=‘sals‘ ->进行全表扫描、不走索引。

　　如果在where 子句中有OR 操作符或单独引用Job 列(索引列的后面列) 则将不会走索引，将会进行全表扫描。

抑制索引使用的情况:

1. 使用不等于运算符(<> , !=);
2. 使用IS NULL或IS NOT NULL;
3. 条件中有LIKE关键字会使用索引。
    通常有两种写法 LIKE ‘%some%‘  或LIKE ‘some%‘
    当%在前面的时候，索引不会被使用，但是当值在%前面时候，oracle可以使用索引
4. 使用函数:

• 除非使用基于函数的索引，否则在SQL语句的where子句中对存在的索引的列使用函数时，优化器会忽略索引.

1. 比较不匹配的数据类型

• 一个B树索引只有一个根节点，它实际就是位于树的最顶端的分支节点。可以用下图一来描述B树索引的结构。其中，B表示分支节点，而L表示叶子节点。

•  对于分支节点块（包括根节点块）来说，其所包含的索引条目都是按照顺序排列的（缺省是升序排列，也可以在创建索引时指定为降序排列）。每 个索引条目（也可以叫做每条记录）都具有两个字段。第一个字段表示当前该分支节点块下面所链接的索引块中所包含的最小键值；第二个字段为四个字节，表示所 链接的索引块的地址，该地址指向下面一个索引块。在一个分支节点块中所能容纳的记录行数由数据块大小以及索引键值的长度决定。比如从上图一可以看到，对于 根节点块来说，包含三条记录，分别为（0 B1）、（500 B2）、（1000 B3），它们指向三个分支节点块。其中的0、500和1000分别表示这三个分支节点块所链接的键值的最小值。而B1、B2和B3则表示所指向的三个分支节点块的地址。
•     对 于叶子节点块来说，其所包含的索引条目与分支节点一样，都是按照顺序排列的（缺省是升序排列，也可以在创建索引时指定为降序排列）。每个索引条目（也可以 叫做每条记录）也具有两个字段。第一个字段表示索引的键值，对于单列索引来说是一个值；而对于多列索引来说则是多个值组合在一起的。第二个字段表示键值所 对应的记录行的ROWID，该ROWID是记录行在表里的物理地址。如果索引是创建在非分区表上或者索引是分区表上的本地索引的话，则该ROWID占用6个字节；如果索引是创建在分区表上的全局索引的话，则该ROWID占用10个字节。

Oracle 12C引入了CDB与PDB的新特性，在ORACLE 12C数据库引入的多租用户环境（Multitenant Environment）中，允许一个数据库容器（CDB）承载多个可插拔数据库（PDB）。CDB全称为Container Database，中文翻译为数据库容器，PDB全称为Pluggable Database，即可插拔数据库。在ORACLE 12C之前，实例与数据库是一对一或多对一关系（RAC）：即一个实例只能与一个数据库相关联，数据库可以被多个实例所加载。而实例与数据库不可能是一对多的关系。当进入ORACLE 12C后，实例与数据库可以是一对多的关系。下面是官方文档关于CDB与PDB的关系图。

Sql优化方案:

1):对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 
涉及的列上建立索 .

         2): .应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描.

                   可以在num列上设置默认值0，确保表中num列没有null值.

      3): 应尽量避免在 where子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描.

      4): 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描;例如:

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10

union all

select id from t where num=20

      5): in 和 not in 也要慎用，否则会导致全表扫描;例如:

           select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

      6): 条件中有LIKE关键字会使用索引:

通常有两种写法 LIKE ‘%some%‘  或LIKE ‘some%‘
当%在前面的时候，索引不会被使用，但是当值在%前面时候，oracle可以使用索引

      7): 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致;

      8): 很多时候用 exists 代替 in 是一个好的选择：

Oracle执行顺序:

1、先执行From ->Where ->Group By->Order By

2、执行From 字句是从右往左进行执行。因此必须选择记录条数最少的表放在右边。这是为什么呢？　　

3、对于Where字句其执行顺序是从后向前执行、因此可以过滤最大数量记录的条件必须写在Where子句的末尾，而对于多表之间的连接，则写在之前。
因为这样进行连接时，可以去掉大多不重复的项。　　

4. SELECT子句中避免使用(*)ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间

Oracle的内部结构及SQL优化原理

原文：https://www.cnblogs.com/wk-missQ1/p/13273977.html