索引(index)

1.创建索引

 

使用索引是为了提高查询效率,主要用在select语句中.像我们的书本前面的目录一样.

创建索引的语法:

CREATE [UNIQUE] INDEX index_name ON table(column[,column..]);

其中index_name是索引名字,推荐的命名规范是idx_tableName_columnName

table是数据库表名.column是表中某一列的名字.

UNIQUE是指唯一索引,如果创建一个表时里面某一列设为主键或有唯一约束则Oracle会自动创建唯一索引.

我们手动创建索引一般是非唯一索引居多吧.

如果以表中某一列为基础创建索引则为单列索引,如果以多列为基础则为复合索引.

例如:

CREATE INDEX idx_emp_empno ON emp(empno);--单列索引

CREATE INDEX idx_emp_empno_ename(empno,ename);--复合索引

索引创建好后是独立存储,不与表放一起.

 

2.使用索引.

其实创建了索引之后我们自己没法去直接使用,只是系统去后台调用.

凡事都有两面性,并不是索引建的越多越好,索引会带能性能的损耗,如果它提高的查询效率要比性能损耗没多多少自然是不适合用.

是否使用索引主要考虑下面因素

1.为经常出现在WHERE,ORDER BY,DISTINCT子句中的列创建索引

2.不要在小表上,在经常做DML操作的表中建立索引.因为做一次DML后数据会发生改变,这样会导致更新索引,进而带来性能损耗.

另外就算建立了索引如果在查询语句写的不好不一定会用到索引,这也意味着可以优化查询语句.

例1:SELECT * FROM emp WHERE empno <> 1234.就不会用到索引idx_emp_empno,而是对对整个表全盘扫描.

如果改成SELECT * FROM emp WHERE empno <1234 OR empno>1234就会用到索引了.所以查询语句尽量少用不等于.

例2:SELECT * FROM emp WHERE  ename = 'scott' and empno <123.不会用到索引idx_emp_empno_ename.

where后面的列必须要和创建索引时列的顺序一样.创建索引时是(empno,ename).所以要想用到索引必须把语句改成

SELECT * FROM emp WHERE empno <123 and ename = 'scott';在ORDER BY后面顺序也一样不能变.

如果在建立了索引的表上做了DML操作系统可能不一定马上就更新,我们可以手动更新下:

例如:ALTER INDEX idx_emp_empno REBUILD;

最后删除索引就是:DROP INDEX idx_emp_empno.

 

3.索引是怎么被系统使用的.

要理解索引的原理不能不先说下rowid.

我们建立一个表后,在表中每插入一行,系统都会针对每一行生成对应的唯一的rowid.只是它保存在另外的地方我们看不到.在任意一个表

中我们可以通过SELECT rowid FROM tableName查看.比如SELECT rowid FROM emp;

rowid的样子可能有点怪,一般是这样的:AAAfIhAAEAAAAPDAAA

在有主键的表中我们可以通过主键确定唯一的一行.但在没有主键的表中我们也可以通过rowid确定唯一的每一行.

我们可以这样用rowid  :SELECT * FROM emp WHERE rowid = 'AAAfIhAAEAAAAPDAAA'.记得加引号

1.不用索引时怎么使用rowid

如果我们不使用索引的话做一个查询SELECT * FROM emp WHERE ename='scott'.后台是这样去操作的:

a.先扫描整个表,找到哪一行ename为scott后再找出对应的rowid.

b.然后再根据rowid去提取数据,再最终返回给用户.

所以如果我们在where后面直接用rowid就会省了一步,所以查询效率是最高的.只是rowid不容易记住,所以很少这样用.

2.用索引时怎么使用rowid

假如我们根据enamep这一列创建一个index的话,index里面包含的内容就是下面这样的：

ename                         rowid

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

scott                       AAAfIhAAEAAAAPDAA

当SELECT * FROM emp WHERE ename='scott'.后台是这样去操作的:

a.不去扫描整个表,而是直接查询索引.找到scott对应的rowid

b.然后根据rowid提取数据返回给用户.

当然我把index表示成上面像表一样的形式只是为了便于理解.实际上它是用树形结构保存的.所以对index查询时不会像表一样一行一行的去查的.