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查询时不会像表一样一行一行的去查的.