索引

最新推荐文章于 2024-10-14 22:42:36 发布

cuiym1949

最新推荐文章于 2024-10-14 22:42:36 发布

阅读量100

点赞数

文章标签：数据库

一．索引的使用场景

在对大表进行少量数据的查询时可以大幅度提高性能。

二．走全表扫描还是索引扫描

Oracle在sql语句进行查询时，会使用CBO优化器对查询语句进行优化处理，在进行基于成本的优化之后选择查询方式，所以有时即使创建索引不见得就会索引扫描，这取决于全表扫描和索引扫描哪个成本更低。

一般来说，访问的数据小于总数据的5%~10%，会选择索引扫描，否则全表扫描。

三．影响索引扫描成本的指标

集群因子：比较某行的ROWID和前一行的ROWID是否在同一数据块中，如果不在，则clustermg Factor加1，它体现了数据在基于索引分布的有序性度量。

二元高度：二元高度其实就是B树的节点层数，因为索引扫描是进行单数据块的访问，所以节点的层数直接影响扫描速度。

直方图：直方图体现的是一个表中数据的分布情况，原理就是将数据行平均分布在一定数量的存贮桶中如果有条数据在多个桶中多次出现，使用索引反而会降低性能。

四．索引扫描工作机制

在索引扫描时，oracle会根据索引得到对应数据的ROWID，再通过ROWID得到该数据。

五．限制索引使用的情况

在索引列上使用1.<> 2.is (not) null 3.函数 4.比较不匹配的数据类型时，即使创建了索引，oracle也无法使用，最终会选择全表扫描。

所以，当语句中在索引列上使用函数时，应将语句改写，将索引列分离出来，如：

Where empno+1<2 改为 Where empno<2-1 这样才会使用empno列上的索引。

如果有的函数无法改写，可以考虑建立函数索引。

六．查询索引信息命令

1. 使用数据字典user_ind_columns查询详细的索引信息

Select column_name, index_name, table_name

from user_ind_columns

where index_name like’emp%’;

2.也可以使用数据字典user_indexes，但无法显示列名。

Select column_name, index_name, table_name, tablespace_name

from user_indexes

where index_name like’emp%’;

七．确定该索引在查询时是否被使用：使用monitoring usage监控索引。

实例：SQL> select * from user_ind_columns where index_name like '%EMP%';

INDEX_NAME

------------------------------------------------------------

TABLE_NAME

------------------------------------------------------------

COLUMN_NAME

----------------------------------------------------------------------------

COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND

--------------- ------------- ----------- --------

PK_EMP

EMP

EMPNO

1 22 0 ASC

SQL> ALTER INDEX PK_EMP MONITORING USAGE;

索引已更改。

SQL> SELECT EMPNO from emp where empno='7396';

未选定行

SQL> select empno from emp;

EMPNO

----------

7369

7499

7521

7566

7654

7698

7782

7839

7844

7900

7902

7934

已选择12行。

SQL> alter index PK_EMP NOMONITORING USAGE;

索引已更改。

SQL> SELECT INDEX_NAME,TABLE_NAME,MONITORING,USED FROM V$OBJECT_USAGE;

INDEX_NAME

------------------------------------------------------------

TABLE_NAME MONITO USED

------------------------------------------------------------ ------ ------

PK_EMP

EMP

NO YES

八．复合索引

创建：create index SB on emp(ename, job, mgr);

在对ename job mgr 排序时讲究可选性强的排在前面，这样从一开始就过滤掉了大部分无用数据，另外需注意，在oracle9i之前的版本不支持skip scan index功能，即：select * from emp where job=‘a’;是不会使用这个SB复合索引的，只有过滤条件含有ename时才可以。

九．重建索引：alter index PK_EMP rebuild;

压缩索引：alter index PK_EMP coalesce;

查看索引状态视图：index_stats

十．Bitmap索引：索引条目包括：索引键值、该键值对应的记录对应的起始位置和终止位置、bit序列三部分组成。位映射索引可以索引null值的列，缺点是在对记录进行DML记录时，位映射索引要对记录进行锁定，所以在高并发的环境中产生严重的锁等待。

SQL> select /*+index(tt bit_idx)*/ * from tt;

ID NAME

---------- ----------

1 a

执行计划

----------------------------------------------------------

Plan hash value: 1467841563

----------------------------------------------------------------------------------------