sql效率
SQL效率一、关于索引的知识要写出运行效率高的sql,需要对索引的机制有一定了解,下面对索引的基本知识做一介绍。1、索引的优点和局限索引
SQL效率
一、
关于索引的知识
要写出运行效率高的sql,需要对索引的机制有一定了解,下面对索引的基本知识做一介绍。
1、索引的优点和局限
索引可以提高查询的效率,但会降低dml操作的效率。
所以建立索引时需要权衡。对于dml操作比较频繁的表,索引的个数不宜太多。
2、什么样的列需要建索引?
经常用于查询、排序和分组的列(即经常在where、order或group by子句中出现的列)。
3、主键索引和复合索引
对于一张表的主键,系统会自动为其建立索引。
如果一张表的几列经常同时作为查询条件,可为其建立复合索引。
4、建立索引的语句
create index i_staff on staff (empno);
create index i_agent on agent (empno, start_date);
5、删除索引的语句
drop index I_staff;
drop index I_agent;
6、查询索引的语句
法一:利用数据字典
表一:all_indexes 查看一张表有哪些索引以及索引状态是否有效
主要字段: index_name, table_name, status
例如:select index_name, status
from all_indexes
where table_name=’STAFF_INFO’;
INDEX_NAME STATUS
--------------------- -----------
I_STAFF VALID
表二:all_ind_columns 查看一张表在哪些字段上建了索引
主要字段: table_name, index_name, column_name, column_position
例如: select index_name, column_name, column_position
from all_ind_columns
where table_name=’AGENT’
INDEX_NAME COLUMN_NAME COLUMN_POSITON
--------------------- ----------------------- --------------------------
I_AGENT EMPNO 1
I_AGENT START_DATE 2
由此可见,agent表中有一个复合索引(empno, start_date )
法二:利用toad工具
toad用户界面比sql*plus友好,并且功能强大。你可以在toad编辑器中键入表名,按F4,便可见到这张表的表结构以及所有索引列等基本信息。
7、索引的一些特点
1): 不同值较多的列上可建立检索,不同值少的列上则不要建。比如在雇员表的“性别”列上只有“男”与“女”两个不同值,因此就没必要建立
索引。如果建立索引不但不会提高查询效率,反而会严重降低更新速度。
2): 如果在索引列上加表达式,则索引不能正常使用
例如:b1,c1分别是表b,c的索引列
select * from b where b1/30< 1000 ;
select * from c where to_char(c1,’YYYYMMDD HH24:MI:SS’) = ‘200203 14:01:01’;
以上都是不正确的写法
3): where子句中如果使用in、or、like、!=,均会导致索引不能正常使用
例如:select * from b where b1=30 or b1=40;
4): 使用复合索引进行查询时必须使用前置列
例如表a上有一个复合索引(c1,c2,c3),则c1为其前置列
如果用c1或c1+c2或c1+c2+c3为条件进行查询,则该复合索引可以发挥作用,反之,用c2或c3或c2+c3进行查询,则该索引不能起作用。