索引的概念:
索引是提高查询速度的一种手段,索引有很多种,目前只需掌握索引的概念,创建索引,使用索引,删除索引即可
SELECT *
FROM SCOTT.emp
WHERE sal>1000;
以上查询只能看到数据,而不能看到整个查询中的sql语句执行计划过程,如果要观察过程则可以先切换到超级管理员下,打开跟踪器即可。
开启跟踪器:
切换用户:
CONN SYS/tiger AS SYSDBA;
打开跟踪器:
SET AUTOTRACE ON;
SELECT *
FROM SCOTT.emp
WHERE sal>1000;
TABLE ACCESS FULL 表示在查询数据的时候使用的是全表扫描的模式,
比如说有10000个雇员,要求查询出薪资大于5000的雇员信息,但是薪资大于5000的只有100个,此时查询的时候同样需要去判断另外9900个雇员的薪资,这9900的判断就是多余的,此时提出了索引的概念,就雇员的信息按照某个字段的值保存在索引树的结构中,
索引树有很多种,以二叉树为例
此时如果要查询薪资大于5000的雇员信息,只需要在二叉树种找到5000的节点,直接查询该节点右边的数据即可,提高了查询的效率
以上就是索引树的一种代表,也是一种思想,
将数据按照索引数据的方式保存需要先创建索引
基本语法:
CREATE INDEX 索引名 ON 数据表(字段名)
创建索引:
在emp表中的sal字段上创建索引:
CREATE INDEX emp_sal_index ON SCOTT.emp(sal);
现在创建了索引,查询的时候就在索引范围内扫描,就提高了查询的效率
删除索引:
DROP INDEX emp_sal_index;
以上是一个索引作用于一个字段,还可以一个索引作用于多个字段,这样的索引叫作复合索引
复合索引创建:
复合索引就是一个索引在多个字段上创建
创建复合索引:
CREATE INDEX emp_job_sal_index ON SCOTT.emp(job,sal);
查询数据(不使用第一个字段)
SELECT *
FROM SCOTT.emp
WHERE sal>1000;
此时用的是全表扫描模式
查询(使用第一个字段)
SELECT *
FROM SCOTT.emp
WHERE job='SALESMAN';
当使用复合索引的时候如果只要其中一个字段,那么必须使用第一个字段(在创建索引时候的字段顺序)索引才会生效,这叫作索引的最左原则
查询(使用两个字段)
SELECT *
FROM SCOTT.emp
WHERE job='SALESMAN' AND sal>1000;
SELECT *
FROM SCOTT.emp
WHERE sal>1000 AND job='SALESMAN';
在Oracle中如果使用了多个字段,并且是AND连接的条件,那么字段的顺序不影响扫描的方式(都使用索引扫描),如果是MySQL则需要和复合索引的字段的顺序一致
在OR逻辑中使用复合索引:
SELECT *
FROM SCOTT.emp
WHERE sal>1000 OR job='SALESMAN';
发现使用复合索引的时候如果放到了OR查询中,则会导致索引失效,可以用UNION ALL代替OR查询
SELECT *
FROM SCOTT.emp
WHERE sal>1000
UNION ALL
SELECT *
FROM SCOTT.emp
WHERE job='SALESMAN';
总结:
索引不能随便使用,如果一张数据表中的数据更新频率太高,因为更新数据之后需重新创索引,这个过程很耗费性能