sql效率

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进行查询，则该索引不能起作用。