1、优化器
为SQL选择一个最优的执行计划的工具分为RBO和CBO。两种优化器的区别在于:RBO不会计算cost,但是CBO会计算cost。
2、统计信息
表中的数据量与数据分布等信息。查看统计信息,是在sys下的视图中查看。
|-- num_rows
table -> user_tables --|-- blocks
|-- last_analyzed
|-- num_rows
index -> user_indexs --|-- leaf_blocks
|-- distinct_keys
|-- last_analyzed
|-- num_rows
row -> user_columns ---|-- low_values
|-- hight_values
|-- last_analyzed
如果没有统计信息,CBO会动态采样,取一定比例的数据分析得到一个统计信息。
3、表连接
实际上应该用row source来代替表。表连接并不是整张表连接。典型的表连接包括:嵌套循环(nested loop)、哈希连接(hash join)、排序合并连接(sort merge join)。驱动表(在表连接中先存取的表)。表连接一次只能连接两个表,即使有多个表的连接,也只能两个两个地连接,所以,两个表的连接很重要,表的连接顺序也很重要。
4、nested loops(嵌套循环)
eg:
select /*+ use_nl(ab) full(a) full(b) leading(b)*/ *
from t_userinfo a,t_userserviceinfo b
where a.phonenumber = b.phonenumber
leading(b)以b为驱动表
(1)分析该查询语句的索引:
这种查询就会采用nested loops(嵌套循环),就像java中的a.forEach(ele -> b.forEach())
执行计划中的第一个表为驱动表,既:外层循环。
/*+ use_nl(ab) full(a) full(b) leading(b)*/ 是查询语句中的提示。
注意:在选择驱动表的时候,以小表为驱动表,小表不是真的小表,而是row source小的表。并且,大表上要有索引。
(2)提示:
“提示”是用来提示优化器采用什么样的执行计划。
当出现以下情况的时候,就需要使用提示了:
a、没有统计信息
b、临时表上不能收集统计信息
c、CBO选择的执行计划不好
(3)提示的分类:
a、指定连接顺序:
leading -> 指定驱动表:no_unnest(以主查询为驱动)、unnest(以子查询为驱动)
ordered -> 指定顺序
b、指定连接方式:
use_nl -> 嵌套循环
use_hash -> 哈希连接
c、指定访问路径:
full -> 全表扫描
index -> 索引扫描
d、nl_sj,nl_aj,hash_sj,hash_aj
用于子查询,sj->in/exists
aj->not in/not exists
(4)如何选择驱动表:
a、当两个表都是全表扫描时,以小表为驱动表
b、内表的查询列一定要能命中索引
c、当b表上有固定查询条件,可以缩小b表与a表的连接的row source时,以b表为驱动表。
所以,应该以参与连接的row source比较小的表为驱动表
(5)带子查询的nested loops:
nl-sj -> 以主查询为驱动
in/exist -> 以主查询为驱动
no_unnest -> 与nl_sj一样,但是当10G以后,需要使用no_unnest
(6)多表 nested loops:
a、在多表连接的时候,实际上是两个表两个表地连接。当使用多表连接的时候,可以使用order(a b c)来指定连接顺序,
两个表连接以后的row source越少,就应该越靠前。
b、带外连接的nested loop都是以左表为驱动表。
c、多表连接默认情况下,是根据from后面的表的顺序从右向左开始连接,但是,如果RBO认为有更好的
执行计划,也会改变。
5、hash join
(1)nested loop 与 hash join的比较:
a、nested loop之适合小数据量
b、hash join适合大数据量
(2)hash join的连接过程:
a、生产hash表,以驱动表数据生成。为了使hash表小一点,要以小表为驱动表。
b、生成hash表以后,扫描探测表(探测表是另外一张表),与hash表匹配,匹配上就将数据存入结果集。
c、hash连接结束,就可以得到结果
(3)默认优化器会使用 index fast full scan 而不是table full scan
(4)update技巧
sql语句:
update t_useinfo a
set a.username = (select b.oprinitinfo from t_userservice_info b where a.phone=b.phone);
分析sql语句:
a、这里会使用nested loop(嵌套循环)
b、视图update
update(
select a.username,b.opinfo from a,b where a.phone=b.phone
)
set username=opinfo;
c、PL/SQL更新
begin
for item in(
select a.rowid,b.opinfo from a,b where a.phone=b.phone
)
loop
update a set username=item.opinfo where rowid=item.rowid;
end loop;
end;
(5)hash join总结
a、适用于大数据,连接条件必须是等值连接
b、选择小表为驱动表
c、设置较大的GPA,可以加快hash join
d、注意顺序,中间结果集尽量小
e、hash join与nested loop比较,实际上是用空间换时间,当内存不足的时候,就会报错
(6)分区表
physical上分离,logical上一体。这样可以加速查询
6、oracle 索引
(1)B+树索引
B+树索引的数据结果是一个树,树的叶子节点是表中的数据行,叶子节点的父节点叫“索引叶子节点块”,在索引叶子节点块中会保留相邻的索引叶子节点的地址。
在B+树中,索引节点是有序的,而这种有序是针对每一层而言的。B+树中,每一层可以有200个节点块,每一个节点块中,可以对应400个下一级节点块。但是,叶子节点块是无序的(叶子节点块中存的是数据行)。
(2)查看表的统计信息
可以分析表的一些基本数据。注意:数据被delete,数据占用的块不会被释放,但是,新insert的数据会优先占用这些空间。
(3)查看表索引的统计信息
可以分析表的索引的一些信息。
(4)关于索引的一些注意点
a、空字段值是不会被加入索引的,因此,查某个字段为空是不能在索引中查到的
b、不要在长字段上创建索引,这会导致B+树层高太大,影响索引扫描,使索引扫描时间增加。
分析:
1)为什么在长字段上创建索引会导致B+树层高太大:因为每一个索引叶子节点块能够存的数
据量是有限的。索引字段越长,索引叶子节点块中的索引数量越少,叶子节点块的数量就
越多,B+树层高就越大。
2)为什么B+树层高越大,索引扫描越慢:因为B+树层高越大,需要扫描的叶子节点块的数量
就越多,所以,扫描时间越大。
c、分配索引空间和表空间时,两者因该是差不多的。
d、索引键是可以重复的,也是可以不重复的(设置索引为unique),但是不能为空。
e、索引的重复度越高,查询越慢:
B+树的高度决定了从根节点到叶子节点查询的节点数量。重复度决定了在叶子节点中查询的节点数
量。所以,B+树高度越高,查询的节点树越多。重复度越高,查询的节点数越多。
(5)索引的查询
索引扫描的数度是由以下两个参数决定的:层高、索引重复度
层高:层高由“索引块数”、“索引字段长度”这两个参数决定。因为块的大小是固定的,所以,索引字段
的长度可以决定索引的块数。因为每一层索引块数固定,所以,索引块数可以决定层高。
索引重复度:索引重复度描述了同一个索引占用的索引块的数量。索引重复度越高,在同一层扫描的索
引块树越多,索引扫描的效率越低。
7、数据库性能调优
(1)数据库性能的影响因子:
IO、CPU、网络。如果数据库做的计算越多,则,CPU使用率越高,网络使用率越低。如果数据库做的计
算越少,则,CPU使用率越低,网络使用率越高。
所以,要合理地安排数据库的计算,尽量使IO、CPU、网络的使用率相当。
(2)减少数据块的读取
(3)数据块是数据行存储的位置,一个数据块可以存多少行的数据,由行大小来决定。数据块大小由磁盘文
件系统决定,也可以设置。磁盘格式化时,会将磁盘划分为指定大小的块。
8、数据的访问路径
(1)执行计划的三个方面:
a、访问路径:全表扫描、各种索引扫描
b、连接方式:单表查询不考虑
c、连接顺序:单表查询不考虑
(2)autotrace工具
查看执行计划、访问量多少数据块
(3)delete不会降低表的高水位
即使数据块是空块,全表扫描也会扫描
(4)执行计划是从下向上看的
(5)select /*+full(a)*/ count(*) from user a;
/*+full(a)*/ : 是告诉优化器对a表执行全表扫描,a为表user的别名
(6)index unique scan
因为是唯一索引,所以,查询的索引块数量为层高。
table access by index rowid,通过行号(rowid)访问表
主要分析:consistent gets 和 physical reads的数量
select /*+index(a indexName)*/ field1 from table a where field2='a' and field3='b';
/*+index(a indexName)*/:指定命中索引a为table别名,index为field2与field3的复合索引
9、index range scan
索引范围扫描:对于组合索引,key为多个字段的拼接。索引重复度高或范围查询
注意:不能命中 index range scan
(1)全模糊查询
username like '%abc%' / '%abc%'不能比对,因为不知道'abc'是第几位
(2)没有使用索引中的第一个字段(前导列)
(3)使用“不等号”或“not in”
(4)查询列上有表达式或函数
(5)index range scan的性能分析:
通过分析该查询访问的块来衡量性能,块越少,性能越好。查询的块 = 索引块 + 数据块
注意:查询条件可以分为:定位条件、过滤条件
如果定位条件先起作用,则,访问的块数就会很少,然后用过滤条件过滤
10、如何确定哪个索引好
标准:访问的数据块越少越好,访问的数据块 = 索引块 + 表块
(1)叶子节点的访问量对性能影响巨大。查询条件和索引的重复率对该因素影响巨大
(2)因为表数据是不连续的,所以,通过叶子节点来访问表是需要来回地从叶子节点获得rowid。这也是为
什么,当命中不了索引时,全表扫描比强制使用index快。自增字段在入表时是相对接近的,因为数据
入表时间是相近的。
(3)索引“聚集因子”:它描述了数据在表中的聚集情况。但是,聚集因子的计算因为出现物理读的可能性很
大,所以,开销也很大。
(4)一般,如果索引命中超过5%,就不用index range scan
11、index full scan
索引全扫描:扫描所有的叶子节点
特点:
(1)有序,叶子节点是有序的
(2)只能单块读(全表扫描可以多块读),叶子节点之间是指针相连,只能沿指针遍历
(3)效率比全表扫描低
使用场景:
(1)排序取Top N
全表扫描排序因为需要将数据读入内存,所以,非常消耗性能。但是,index full scan(索引全
扫描)只能扫描Top N就返回
12、index fast full scan
索引快速全扫描:根据索引在磁盘的物理序扫描,不会顺着叶子节点指针扫描
特点:
并行、无序、可多块读、只适用于CBO(CBO是一种优化器)
13、index skip scan
定义:跳过索引前导列的索引扫描
14、索引设计原则
(1)分区表不创建全局索引,否则,当delete分区时,索引会失效
(2)不要创建无用的索引,否则,会降低DML的性能
(3)t_1_r_1 与 t_1 是一样功能的索引
(4)key不要太长,否则,B+树会很高
(5)重复度低的应该靠前
(6)索引和表要建在不同的表空间中,可以提高IO性能