索引

索引组织表

​ **回表：**select * from t where id=1之类的查询，id列有索引，如果是普通的表，需要先从索引中获取rowid，然后定位到表中，获取id以外的其他列的动作，这就是回表。

索引组织表最大的特点：表就是索引，索引就是表，这是一种很特别的设计，所以无须访问表。

但是这种设计的表的更新比普通表开销更大。因为表要和索引一样有序的排序，更新负担将会非常严重，因此这种设计一般适用于在很少更新、频繁读的应用场合，比如地区配置表，这种表数据一般很少变动，却大量读取。

关键字：organization index

簇表的介绍及应用

普通表还有一个缺陷，就是ORDER BY 语句中的排序不可避免，实际上有序簇表可以避免排序。

关于避免排序，还有另外一种方法，也是更常见的方法；排列序正好是索引列时，可以避免排序。

由于结构的特殊导致更新操作开销非常大，所以也需要谨慎使用。

索引

结构

索引建在表的列上，其存在目的就是让查询更快，建一个索引，在逻辑结构上就是一个segment

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SRg8TbU0-1583652013496)(file:///C:\Users\hjj\AppData\Local\Temp\ksohtml\wps8C5F.tmp.jpg)]

主要由Root、Branch、Leaf组成，其中Leaf主要存储了Key column value （索引列的具体值）以及能具体定位到数据块所在位置的rowid。

select * from T where id=12;

select id from T where id=12;

– 统计索引详情,其中blevel表示高度，0位1层，1位2层

select index_name,blevel,leaf_blocks,num_rows,

distinct_keys,clustering_factor

from user_ind_statistics;

段的最小分配空间是64KB

blevel为0表示只有叶子块

什么时候COUNT(*)查询语句用索引扫描比全表扫描高效很多呢？

表的字段很多，并且字段长度大多都很长，其中有一个非空且长度很短的列建了一个索引，这时索引的体积相对表来说特别小，那索引读效率就高多了

 --查看数据库当前用户下的索引

SELECT

  user_ind_columns.index_name 索引名称,

  user_ind_columns.table_name 表名,

  user_ind_columns.column_name 字段名,

  user_indexes.uniqueness 约束 

FROM

  user_ind_columns,

  user_indexes 

WHERE

  user_ind_columns.index_name = user_indexes.index_name;
  
 --查看数据库中段的大小（M） 

   Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name;

三大重要特点：索引不能存储空记录

1. 索引高度较低

   梁老师见过的表最大的有500G一张，记录有几百亿条，但是该表上某列索引的高度才不过6层而已

   因为表索引高度较低，所以产生的IO比较少，所以在记录差异显著的表索引扫描性能可以相同。

   索引高度不高的特性给查询带来了巨大的便捷，但是请注意我们的查询只返回1条记录，如果返回绝大部分的数据，那用索引反而要慢得多；这种时候不如全表扫描，全表扫描还有一个优势，就是一次可以读取多个块，不仅是一次读取一个块，这样IO的次数还可以大大降下来的。

   表字段越少查询性能越高

2. 索引存储列值

   索引存储了表的索引所在列的具体信息，还包含了标记定位行数据在数据库中位置的rowid

   select count(*) from t;//表字段相对较多，可通过索引来统计表的数目，索引列不能为空，列入主键设为索引列

3. 索引本身有序

   select index_name blevel,leaf_blocks,num_rows,distinct_keys,clustering_factor

   from user_ind_statistics where table in(‘T1’,‘T2’);

4. 索引回表与优化

索引回表读 TABLE ACCESS BY INDEX ROWID
聚合因子决定了回表查询的速度：
如果在 TABLE ACCESS BY INDEX ROWID 不可避免的情况下，必须执行回表动作，是否回表查询方式也有效率高低之分呢？

-- 可以通过数据字典来判断索引的聚合因子情况：
select index_name,blevel,leaf_blocks,num_rows,distinct_keys,clustering_factor
from user_ind_statistics
where table_name in('对应的表名');
-- clustering_factor的官方解释：表明有多少临近的索引条目指到不同的数据块。

同样大小的表和同样大小的索引，且记录数也相同，执行的是同样的语句。仅是聚合因子的差异，或者说是表的排列顺序的差异，就可以导致性能差异非常的悬殊。
怎样让表里的所有索引列的聚合因子都比较低，从而提升回表的性能呢？
这是不可能的，列的索引的排序是按列的内容来排序的，各列的内容各不相同，表只有一种插入顺序，如何去匹配全部这些呢？但是我们可以重点选择，某列的读取频率远高于其他列，那就保证表的排列顺序和这列一致，按照这列的顺序，重组一下表记录来优化即可了。

5. ORDER BY 优化：

select * from t where object_id>2;
select * from where object_is>2 order by object_id;
排序由是影响性能的。

在order by列的语句中，我们就可以考虑在该列建一个索引来消除排序，尤其是当系统面临排序的严重瓶颈时。
6. DISTINCT排重优化：
select distinct object_id from t where object_id=2;
DISTINCT 这个常见的排除重复记录的也会用到排序。采用的是HASH UNIQUE的算法。
不过现实中，DISTINCT 语句靠索引来优化往往收效是不明显的，因为大多数情况用到DISTINCT 都是因为记录有重复，因此我们首要的是要考虑为什么会重复。

索引扫描类型：

INDEX RANGE SCAN;针对索引高度较低这个特性实现的一种范围扫描方式，在返回记录很少时相当高效

INDEX FAST FULL SCAN;一次读取多个块

INDEX FULL SCAN;一次读取一个块，适用于有排序的场合

INDEX FULL SCAN(MIN/MAX);MAX取值只需要往最右边的叶子快去瞧瞧就行了，块里的最后一行就是。

INDEX ROWID INDEX RANGE SCAN;

真正决定性能的是COST的高低和真实完成的时间，一般COST越小性能越高。
Oracle执行计划的选择就是由COST来决定的，时间也是非常简单的衡量方式，完成时间越短性能越高

7.UNION合并的优化
复杂的语句就是由简单的语句组合而成的。

UNION和UNION ALL的差别：
UNION会有去除重复记录的动作，和DISTINCT很相似。
UNION ALL 就是简单的排序
最常见的UNION的优化居然是把UNION
改为UNION ALL。在某些业务场景下，两个表根本就不可能重复，却用UNION而不用UNION ALL。

组合索引高效设计要领

组合索引的用途：

• 避免回表
• 很多时候，在a字段上查询返回记录比较多，在b字段上查询返回的字段也比较多，如果a和b字段同时查询，返回的记录比较少，那就适合建联合索引了
  强调：过多的字段建联合索引往往是不可取的，因为这样索引也必然过大，不仅影响了定位数据，更严重影响了更新性能，一般不宜超过3个字段组合

回表的动作：
TABLE ACCESS BY INDEX ROWID
组合列返回越少越高效

组合两列谁在前更合适？

1. 组合索引的两列，当一列是范围查询，一列是等值查询的情况下，等值查询列在前，范围查询列在后，这样的索引才有效。
2. 原理：为什么在组合索引两列都是等值查询时，无论那一列前置，性能都一样。
   而组合索引两列有一列是范围查询时，必须要等值查询列在前才更高效，和列的重复度没有关系，这是为什么？

组合索引设计中需要考虑单列的查询情况