索引设计

最新推荐文章于 2024-06-20 23:27:17 发布

congyaocha1717

最新推荐文章于 2024-06-20 23:27:17 发布

阅读量144

点赞数

文章标签：数据库

收集表和主键索引统计信息和直方图

exec SYS.DBMS_STATS.GATHER_TABLE_STATS('username','table_name');

exec SYS.DBMS_STATS.GATHER_index_stats('username','index_name');

SQL> exec dbms_stats.gather_table_stats('alex','alex_t01')

SQL> exec dbms_stats.gather_index_stats('alex','pk_alex_t01')

wcYvzaCO+SxsAAAAABJRU5ErkJggg==

B树索引

由根节点，分支节点，叶节点三部分组成

索引条目分布包含索引条目头

键值长度

键值列

ROWID

全表扫描：全表扫描（FULL TABLE SCAN）就是在数据查询过程中，对整张表的全部低于高水位标记（High Water Mark，HWM）的数据块（Data Block）进行读取

强制全表扫描：select /*+full(alex_t00)*/ count(*) from alex_t00;

rowid扫描：oracle提供的伪列；

select rowid from alex_t00 where id=1;

AAAVAe AAB AAAWFp AAA

6位 3位 6位 3位

数据库对象编号文件编号块编号行编号

通过 dbms_rowid包分解rowid；

select dbms_rowid.rowid_object(rowid) object_id,

dbms_rowid.rowid_relative_fno(rowid) file_id,

dbms_rowid.rowid_block_number(rowid) block_id,

dbms_rowid.rowid_row_number(rowid) num

from alex_t00

where id = 1;

索引唯一扫描：

有一个特点，就是当唯一索引由多列组成，则至少要有组合索引的前导列参与查询；

索引范围扫描：根据索引列范围扫描表，结构有排序

索引全扫描：与全表扫描相比，其扫描过程进行排序，避免空块查询，单块读取；

索引快速全扫描：与索引全表扫描相比，其不同在于结过不排序，可以并行多块读取；

强制直接快速扫描

SQL> select /*+ index_ffs(alex_t01 idx_alex_t01_id_ab) */ a, b from alex_t01 where b>600;

索引跳跃扫描：在复合索引发生，where条件中没有对目标索引的前导列指定查询条件但同时又对该索引的非前导列指定了查询条件的目标SQL依然可以用上该索引

强制执行跳跃扫描

select /*+ index_ss(alex_t01 idx_alex_t01_id_ab) */ a, b, name from alex_t01 where b=600;

索引组合扫描：这个特性由_b_tree_bitmap_plans来控制；发生在where后面的两个条件，两个条件都是独立索引(其他索引扫描的补充)

强制走一个索引

select /*+index(alex_t01,idx_alex_t01_id_b)*/ * from alex_t01 where b=600 and c=600;

索引联立扫描：和索引组合扫描相似，只是当查询返回的列包含该两个索引列，就不用回表取数，可以index_jion(其他索引扫描的补充)

SQL> select /*+ index_join(alex_t01 idx_alex_t01_id_b idx_alex_t01_id_c) */ b, c from alex_t01 where b=600 and c=600;

2Q==

索引与排序：

索引扫描是可以排序的，（快速扫描除外）；

B树索引内部结构

降序索引：个人觉得没必要，因为优化器现在可以自动进行降序查询优化，不过在复合索引的降序是可以表现的如（order by a ase ,bdesc）

聚合查询min()和max()：同时查询最大最小值最优语句

Select (select max(id) from dual) as max_values, (select min(id) from dual) as min_values from dual

索引设计优化：

1. 索引选择度 ----越低越好

索引理想选择度=1/索引列的distinct数；

索引实际选择度=返回结果集行数/全表行数；

我们知道当一个表的NUM_DISTINCT 值越接近NUM_ROWS 值，则优化器越倾向于走

索引扫描。那么最优情况是NUM_DISTINCT=NUM_ROWS（典型代表为唯一索引，此类索引

为理想状态索引，扫描效率最高），最差情况则是NUM_DISTINCT=1（不考虑空表和Null

值）。引进一个比例NUM_ROWS/NUM_DISTINCT 来表示其关联性，该比例的值越小则越优。

第2 章　高效B 树索引41

通过一般情况与最差情况的对比，可以得到一般情况下的选择度，这个选择度就是一般情况

的理想选择度。

理想选择度＝ (NUM_ROWS/NUM_DISTINCT)/(NUM_ROWS/1) ＝ 1/NUM_DISTINCT

可以说理想选择度反映了优化器选择的倾向性。换而言之，如果希望优化器尽可能地选

择索引扫描，则索引设计上尽可能地参考理想选择度的原则，它给索引设计提供一定的指导

意义。

至于NUM_ROWS 和NUM_DISTINCT 的值可以通过扫描表获取，也可以通过如下方式

查询数据字典获取（必须收集统计信息）：

SQL> select num_rows from dba_tables;

SQL> select num_distinct from dba_tab_cols;

2Q==

1. 数据分布影响

均匀分布情况

这种情况的理想选择度和实际选择度是相等的，直方图信息是存放列的数据分布情况，当缺少索引列直方图信息，CBO会认为其是均匀分布，从而影响执行计划；

OEEpiABEoAcg8D+Lx0+LRMnbFgAAAABJRU5ErkJggg==

非均匀分布情况

如果按照实际选择度来走的话，COL=2 走全表扫描，其他情况走索引扫描是一个比较理想的选择。但是，优化器的实际处理情况未必如此。这个时候，如果我们没有COL 列上的直方图，优化器会认为数据是均匀分布的，则理想选择度 = 1/NUM_DISTINCT = 20%，很有可能都走了全表扫描。这对于COL 不为2 的情况几乎是不能接受的。

收集索引列直方图对于大表来说，开销很大，在这情况下不建议建立索引，否则直方图信息收集时必要的；

Oracle 有一个绑定变量窥探的机制，SQL 语句第一次执行决定了以后同样的SQL 语句的执行计划。这对于绑定变量也不合适

1. 索引聚族因子
  
  这情况，当where col=1时候，右图性能比左图好；
  
  可以通过数据字典查看聚族因子信息：CLUSTERING_FACTOR越小越好
  
  select index_name,CLUSTERING_FACTOR from dba_indexes where table_name='　'
  
  综合以上内容，我们可以来总结一下了，一个设计优秀的索引应该具有以下特点：
  
  ??具有较好的选择度，能参考理想选择度来界定；
  
  ??索引列的数据分布足够趋于均匀化；
  
  ??具有较小的聚簇因子。
2. 复合索引
  
  复合索引的设计