aryson ms sql_Oracle数据库的SQL优化 - howard4

1.总则

没有最优的SQL，只有最合适的SQL。一条SQL的执行效率的高低与其操作的数据有关。

优化器

.优化器类别

• RBO——Rule based optimizer(8i之前)基于规则的优化器。在Oracle9i之后的版本，将不再支持。

  按照SQL的书写的顺序倒序执行。如:

  1.表关联条件要写在查询条件之前。防止先按照条件查询出结果，然后再按照视图关联。

  2.数据量小的表要写的后面，以便小表从大表中获取相对少的数据来进行处理。

  3.联合索引，不写最左边的索引则索引失效。

• CBO——Cost based optimizer(8i之后)基于成本的优化器

  依据一套数据模型，计算数据访问和处理成本，选择最优成本为执行方案。CBO在Oracle7引入，但在Oracle8i中才成熟。

  基于成本的优化器，主要是以成本优先在成本低的前提下，甚至可以选择不使用索引。

优化器是Oracle一个不完善的数学模型，每个版本的机制有较大差异。I/O成本最为关键(执行计划)，优化器本身无法对SQL进行智能优化。

我们需要理解优化器的优化思路。

的工作模式

• all_rows 以结果集的全部处理完毕为目的。
• first_rows(n) 以最快返回n行为目的

优化器模式的设置方式

1. 参数设置

   optimizer_mode                         string   all_rows
   

2. 会话设置

   alert session set optimizer_mode = all_rows;
   

3. SQL设置

   select /*＋ all_rows */ count(*) from t;
   

.几个非常重要的概念

.1.selectivity～选择性

表的选择性
表字段中的值重复率越低，表在该字段的选择性越好。这样的字段更适合B-tree索引。反之，字段中的值重复率越高，如性别字段，选择性就差，不适合B-tree索引索引的选择性
基本概念同上。

.2.cardinality～返回的记录数(Oracle根据表信息估算出的值)

在执行计划中表示每一步操作返回的记录数。
CBO通过对这个值的权重计算，决定使用哪一种方式访问数据。
10gR2以前执行计划中以card表示记录数。
10gR2以后执行计划中以Rows表示记录数。

.3.clustering factor～索引集簇

表数据在数据库中以多个数据块的形式存储。索引集簇值表示，通过索引扫描表数据时，跳跃数据块的次数。

的核心，成本的计算

• 数据访问的成本估算
  • I/O成本的估算
  • CPU成本的估算
• 数据处理成本的估算(如：联接，排序，分组等操作)

I/O成本易估算，CBO成本不易控制

优化的核心——执行计划

.数据的访问

• 直接表的访问

  • 并行 执行计划中以PX开头的就是并行数据处理。一般无索引的全表扫描Oracle会以并行方式进行数据处理。并行处理时，Oracle会动态调整处理并行的进程数。假如Oracle开启四个进程处理并行，那么Oracle会将表分为四块同时进行扫描。
  • 多数据块。

• 通过索引访问

  • index unique scan 访问唯一索引的数据。select * from t where ID = 10;

  • index range scan 索引数据的范围查询。select * from t where ID ＜ 10;

  • index full scan 扫描索引全部数据。select id from t order by id;

  • index fast full scan 类似于多块扫描。将索引数据分为多个块，同时从多个块开始扫描全部索引数据。select count(0) from t;

    与全表扫描相比index fast full scan 因为扫描全部索引，其效率要高于全表扫描。但二者的原理相似，都是可以多数据块的读取。

    index range scan 索引范围扫描，顺序读取数据块index fast scan 索引多数据块读取
    一般情况下后者因为是多数据块读取，效率高于前者。但在数据量非常小的时候，即前者扫描的数据块远小于后者时，前者效率高。

  • index skip scan 跳跃式扫描索引

    用于替代全表扫描的一种数据访问方法。
    对于前导重复率高的联合索引，有时候index skip scan性能要好一些。

    在使用联合索引时，如果最左侧的索引字段(前导字段)没有使用或者重复率高，会导致联合索引失效，Oracle9i之前会选择全表扫描。Oracle9i之后，会跳过前导字段，继续在索引上按使用的字段扫描。这就是index skip scan 。当然这样做的前提是Oracle优化器认为扫描索引的成本低于扫描全表的成本。

.数据处理

在找到满足条件的数据之后，对数据进行的操作，包括：
order by
group by
count
avg
sum
......

.数据关联处理

nested loop join 嵌套循环。从A表中拿出一条记录在B表中找匹配的数据。找到后再进入下一次循环。如果B表扫描的是索引字段，效率相对较高。

从一个表中循环取数据，去另一张表中逐一去比较。

应用场景：

• 关联中有一个表比较小
• 被关联表的字段上有索引
• 索引的键值不应该有很高的重复率

merge join 对排序后的两张表进行关联。通常情况下效率不高，因为先要得到两个排序后的表比较消耗资源。

原理：
将两个结果集进行排序，对排序后的两个结果集进行连接

应用场景：
只有当结果集排过序后关联

hash join 哈希关联。通常一个大表和一个小表做关联时，会对小表做哈希运算，然后再和大表做关联。

哈希关联的原理：

1. 将小表hash到内存里，形成一块块存储部分结果集的分区。
2. 内存装不下的分区，就把它刷到磁盘上(Oracle临时表空间)。
3. 对大表关联的字段也做hash处理，并形成分区。
4. 将1、3形成的分区，按地址相同对分区中的数据进行比对关联，得出关联结果集。

应用场景：

• 一个大表，一个小表的关联
• 表上没有索引
• 返回的结果集比较大

.查看执行计划

查看执行计划的方式有很多，这里只列举一个估算的执行计划查看方式(同PL/SQL的F5)。

--需要相关数据库权限才能使用下面语句
set autotrace trace exp;
set linesize 120;
select * from t;

Oracle执行计划的阅读方式：自上而下，由内而外。

COST——代价，CBO的核心
执行计划中显示的代价，自下而上是累加的关系。即上面一行显示的代价是当前行与上面一行的代价和。

执行顺序为：6-8-7-5-10-9-4-11-3-12-2-13-1-0

cost代价：5 = 6 + 7 + 8

4.Oracle优化器操作hints

4.1.什么是Hints

Hints是用来约束优化器行为的一种技术

• 优化器模式

  all_rows 快速返回全表数据，统计型数据库

  first_row(n) 快速返回指定行数的数据。

• 数据访问路径

  基于表的数据访问
  基于索引的数据访问

• 表关联的方式

  nested loop join
  merge join
  hash join

尽量避免在开发中使用，只是辅助DBA来做性能排查和优化。

我们通过对Hints的学习来了解执行计划的意思。

.访问路径相关的Hints

全部Hints信息可查询官方文档。

语法示例：

SELECT /*+ index(t idx_id) */ * from student t;

全表扫描

/*＋ full(t)*/  对t表进行全表扫描

使用指定索引扫描

/*＋ index(t idx_id)*/ 对t 表使用idx_id索引扫描

不使用指定索引扫描

/*＋ no_index(t idx_id)*/ 对t 表不使用idx_id索引扫描

快速索引扫描

/*+ index_ffs */ 使用 index fast full scan扫描表

跳跃索引扫描

/*＋ index_ss */ index skip scan

4.3.表关联的Hints

表关联即就是结果集的关联

嵌套循环

/*＋ use_nl */ Nested Loop Jions 嵌套循环

哈希关联

/*＋ use_hash */ hash join 哈希关联

合并连接

/*＋ use_merge */ merge join 

4.4.其他的Hints

/*＋ leading(t3,t1,t2) */ 规定表连接的顺序
--按照t3，t1，t2的顺序，读取表，然后做连接。

5.索引

.建立索引

B-tree索引，用于辨识度高(重复率低)的字段。

可以创建单列索引，还可以创建包含多个列的复合索引。

CREATE INDEX 索引名 ON 表名(列名1, 列名2, 列名3, ...);

bitmap位图索引，用于辨识度低的字段。其数据结构为一个二进制数。位数代表行数，0，1标识是否。如，档案状态为临时，归档，封存。就要为该字段创建三个位图索引。注意，位图索引会造成大范围行锁，因为数据的增删操作回改变相关索引二进制的位运算，那索引中的所有行都会受到影响。

CREATE BITMAP INDEX 索引名 ON 表名(列名);

Text Index全文索引，用于like '%student%'这样的模糊查询，其数据结构也是B-tree。缺点，占用磁盘空间大，维护成本高，bug多。

CREATE INDEX 索引名 ON 表名(列名) INDEXTYPE IS CTXSYS.CONTEXT ;
-- context类型的全文索引在基表数据更改的时候需要使用下面的命令同步全文索引
exec CTX_DDL.SYNC_INDEX('索引名');

索引相关操作

删除索引

DROP INDEX 索引名;

查看某个表中的所有索引

SELECT * FROM ALL_INDEXES WHERE TABLE_NAME = '表名'

查看某个表中建立了索引的所有列。

SELECT * FROM ALL_IND_COLUMNS WHERE TABLE_NAME = '表名'

.防止索引失效

1. CBO认为扫描表花费的成本比扫描索引要小，所以不走索引。

2. 使用<>，not in，not exist，如果条件是索引字段，必然会导致索引的全部扫描。如果不是，则会全表扫描。

3. 单独使用>，<，根据扫描索引与扫描表成本的比较确定是否使用索引。

4. 使用模糊查询，like "%_" 百分号在前。

5. 单独引用复合索引里非第一位置的索引列。使用非第一位置索引列两个及以上时，使用index skip scan扫描索引。

6. 隐式转换导致索引失效。由于表的字段定义为Varchar2但查询条件写为number，或者相反的情况。如： 字符型字段为数字时在where条件里不添加引号。

7. 对索引列进行运算。需要建立函数索引。(后半句不理解)

8. B-tree索引 is null不会走,is not null会走。

   位图索引 is null,is not null 都会走。

   联合索引 is not null 只要在建立的索引列(不分先后)都会走。(没有试成功)

6.表分区(不属于SQL性能提升的范畴)

表分区的作用主要不是为了SQL查询效率提升，而是为了表数据管理。

当一个表建立了分区，这个表将不再是存储数据的数据段，而是代表各个表分区的关联的逻辑关系。各个分区可以存储在不同的表空间中，这样更方便于数据管理。

1. 分区裁剪，Oracle以表分区字段扫描数据时只会扫描部分分区，而不会全表扫描。

   ListPartitioning 按类别分区，如，东部销售区，西部销售区。

   RangePartitioning 按时间分区，如，按月份分区。

   HashPartitioning 均匀分区，每个分区数据量基本相同。

2. 分区——组合分区(子分区)

   可以对分区再进行分区，如，先RangePartitioning分区，然后再HashPartitioning

6.1分区索引

LocalIndex 表的DML操作无需重建全部索引。

DML(Data Manipulation Language)数据操纵语言：
适用范围：对数据库中的数据进行一些简单操作，如insert,delete,update,select等.

创建一个分区索引会为表的各个分区创建索引，每一个索引对应一个分区键值。某个分区的数据变更不会引起其他分区索引的变动。

全局索引

表的DDL操作会导致全局索引无效。

DDL(Data Definition Language)数据定义语言：
适用范围：对数据库中的某些对象(例如，database,table)进行管理，如Create,Alter和Drop.

6.3全局索引与分区索引的性能

索引的数据是按照键值的大小进行排列的，相同的数据是排列在相邻的部分的，如果找到了数据，就不会继续扫描后面的索引数据了。

总结：

• 分区索引的目的在于数据的管理而非性能。
• 一个分区表上如果经常由DDL 操作，将会导致全局索引无效，需要对索引重建，此时创建分区索引更加合适。