oracle SQL语句优化

骑猪看日落 2021-09-23 05:56 511阅读 0赞

*  一般的sql优化
 *  （1）select语句中避免使用\*
 *  （2）使用where子句替代having子句
 *  （3）使用truncate 代替delete
 *  （4）在确保完整性的情况下多用commit语句
 *  （5）使用表连接而不是多个查询
 *  （6）使用exists替代in
 *  （7）使用exists替代distinct
 *  （8）使用"<="替代<(同理">="替代>)
 *  （9）使用完全限定的列引用
 *   
 *  表的连接方法
 *  （1）from子句表的顺序
 *  （2）where子句的连接顺序
 *   
 *  有效使用索引
 *  （1）创建索引的基本原则
 *  （2）索引列上所使用的的操作符
 *  （3）避免对唯一索引列使用null值
 *  （4）选择复合索引主列
 *  （5）监视索引是否被使用，删除索引

//查时间
    set timing on;

**一：一般的sql优化**

（1）select语句中避免使用\*

//可能是列比较少，前者速度比后者快这么一点
    //前者是通过数据字典将*转换为a表的所有列名，再执行如后者那样的查询操作
    select *from a;
    select id,name,age,sex,grade from a;

（2）使用where子句替代having子句

//不是绝对的，数据少的话前者也可能比后者快
    //where子句过滤行，having子句过滤组(分组后才过滤，分组要时间)
    //使用前者过滤行，减少分组的行数，也就减少了分组的时间
    //having一般用于对一些集合函数执行结果的过滤，如count(),avg()等，一般的检索条件写在where子句中
    select id,avg(age) from a group by id having id>1;
    select id,avg(age) from a where id>1 group by id;

（3）使用truncate 代替delete

//delete:逐行删除，并且使用回滚段记录删除操作，用户在没有使用commit提交前还可以使用rollback命令进行回滚操作
    //truncate:不会再撤销表空间记录删除操作，所有数据行同一时间被删除
    //注意:truncate只能实现删除全部数据，删除单行只能通过delete操作
    
    //如果是使用delete删除表记录，oracle会自动为该操作分配回滚段，删除操作需要较长时间
    delete from a;
    //为了加快删除操作，可以使用截断表去删除表记录
    truncate table a;
    //区别就是前者删除可以进行数据撤销，后者不可以

（4）在确保完整性的情况下多用commit语句

//用户实现dml操作后，如果不使用commit命令提交，oracle会在回滚段记录dml操作,可以通过rollback对数据进行恢复
    //不过要花费相应的时间和空间资源
    //使用commit后，系统释放回滚段的操作信息，被程序语句获得的锁，Redo Log Buffer中的空间，oracle系统管理前面三种资源所要的其他开销

（5）使用表连接而不是多个查询

//尽量减少表的查询次数，能一次查询就不要通过两次查询
    //因为每次执行查询语句，oracle内部执行很多工作-解析sql语句，估算索引的利用率，绑定变量，读取数据块等
    //使用后者这种形式
    select id,name from a where id in(select id from a_sex);
    select a_bm.id,a_bm.name from a a_bm inner join a_sex a_sex_bm on a_bm.id=a_sex_bm.id;

（6）使用exists替代in

//in操作符用于检查一个值是否包含在列表中
    //exists只检查行的存在性，而in检查实际的值，使用exists,not exists替代in,not in
    select id,name from a where id in(select id from a_sex);
    
    select a_bm.id,a_bm.name from a a_bm where exists (select 1 from a_sex a_sex_bm where a_bm.id=a_sex_bm.id);

（7）使用exists替代distinct

//distinct在禁止重复行显示之前要排序检索到的行
    //exists用于检查子查询返回的行的存在性
    //使用exists操作符实现子查询时，推荐使用表的连接查询来替代exists的使用方式(这样可读性更强)
    select distinct a_bm.id,a_bm.name from a a_bm,a_sex a_sex_bm where a_bm.id=a_sex_bm.id;
    
    select a_bm.id,a_bm.name from a a_bm where exists (select 1 from a_sex a_sex_bm where a_bm.id=a_sex_bm.id);
    
    select a_bm.id,a_bm.name from a a_bm inner join a_sex a_sex_bm on a_bm.id=a_sex_bm.id;

（8）使用"<="替代<

//如<=100和<101是一样的，但前者效率更高
    //原因是一个定位在100，一个定位在101，在查询的数据量较大，尤其是循环语句使用者两个操作符时，区别还是明显的
    //同理：">="比>效率高

（9）使用完全限定的列引用

//就是给表使用别名
    //这样数据库不用查询所操作表中包含了哪些的列，也减少了解析列的时间
    //同时也减少了由列引起的歧义（如两个表都有name列，select name，sql解析器无法判断这个列属于哪个表）
    
    //无法执行
    select name from a,a_sex;
    //可以执行，笛卡尔积
    select a_sex_bm.name from a a_bm,a_sex a_sex_bm;

**二：表的连接方法**

（1）from子句表的顺序

//Oracle解析器在处理from子句中的表时，是从右到左的，from子句最后的表为驱动表
    //先检索驱动表，对该表排序，然后再扫描倒数第二个表，将倒数第二个表的记录与第一个表中的合适记录合并，以此类推
    //一般将行数最少的表作为驱动表，也就是from子句的最后一个表
    
    select a_bm.id,a_bm.name from a a_bm,a_sex a_sex_bm;

（2）where子句的连接顺序

//在执行查询的where子句中，可以指定多个条件
    //oracle采用自右向左(自下向上)的顺序解析where子句
    //表之间的连接应该写在其他where条件之前
    //将可以过滤最大数量记录的条件写在where子句的末尾
    
    //10<(select count(*) from a)，我这里有14条记录，也就是10<14，就true的意思，没什么过滤的作用
    //name为漫步云端的只有一条记录
    select *from a 
    where name='漫步云端'
    and id<10
    and 10<(select count(*) from a);
    
    
    //理论上这条更快
    select *from a 
    where 10<(select count(*) from a)
    and id<10
    and name='漫步云端';

**三：有效使用索引**

（1）创建索引的基本原则

//索引要空间来存储，要定期维护
    //每当有记录增删或索引列被修改时，索引本身也会被修改
    //对每条记录的修改增加删除，都需要更多的磁盘I/O,因为索引要额外的存储空间和处理操作

创建索引时，对表分析，从以下几个基本原则进行考虑：
    1.对于经常以查询关键字为基础的表，并且该表中的数据是均匀分布的
    2.以查询关键字为基础，表中的数据行随机排序
    3.表中包含的列数相对比较少
    4.表中的大多数查询都包含相对简单的where子句
    
    
    在创建索引时，需要选择哪些列可以作为索引，有如下几个原则：
    1.经常在where子句中使用的列
    2.经常在表连接查询中用于表之间连接的列
    3.不宜将经常修改的列作为索引列
    4.不宜将经常在where子句中使用，但与函数或操作符相结合的列作为索引列
    5.对于取值较少的列，应考虑建立位图索引，而不应该采用B树索引
    
    注意：
    1.除了所查询的表没有索引，或者需要返回表中的所有行外时，oracle会进行全表扫描
    2.如果对索引列使用了函数或操作符（如like）,oracle也会对表进行全表扫描

（2）索引列上所使用的的操作符

//索引列的操作语句尽量避免使用"非"操作符的使用
    //如：not,!=,<>,!<,!>,not exists,not in,not like等
    //"非"操作符的使用会使oracle对表执行全表扫描
    
    //使用like操作符可以应用通配符查询，但是使用不好会产生性能问题
    //如：like '%540%',不会使用到索引
    //如：like '540%',会引用范围索引
    //当第一个字符为通配符时，索引不起作用，oracle执行全表扫描

（3）避免对唯一索引列使用null值

//使用unique关键字可以为列添加唯一索引，就是列值不能重复，但是多个null值却可以存在，因为oracle认为两个空值是不相等的
    //因此，在where子句使用is null或is not null，对唯一索引列进行空值比较时，oracle停止使用该列上的唯一索引，导致oracle进行全表查询
    //列中包含有null值的列都不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于复合索引就是无效的

（4）选择复合索引主列

//在多个列上创建的索引叫复合索引
    //列的顺序会影响效率
    
    创建复合索引原则：
    //1.选择经常在where子句中使用，并且由and操作符连接的列作为复合索引列
    //2.选择where子句中使用频率相对较高的列排在最前面，或者根据需要为其他列建立单独索引

//如创建一个名为a_name_index的索引
    create index a_name_index on a(id,name); 
    
    //下面两条语句结果一样，但效率前者比后者高
    //保持where子句中的列的顺序与复合索引中列的顺序一样
    select *from a where id>2 and name='漫步云端';
    select *from a where name='漫步云端' and id>2;
    
    注意：
    1.只有当复合索引中的第一列被where子句使用，这个复合索引才会使用
    2.如果只是使用了name，则不会使用该复合索引

（5）监视索引是否被使用，删除索引

//不必要的索引对表的查询效率起负作用
    //监视索引后，可以通过数据字典视图来了解索引的使用状态，如果该索引不必要，可以删除该索引
    
    //监视索引
    alter index a_name_index monitoring usage;
    
    //我执行一下
    select *from a where id<2 and name='漫步云端';
    
    //通过v$object_usage查看索引的使用状态
    select table_name,index_name,monitoring from v$object_usage;

![20190904170530703.png][]

//处于激活的表的索引会影响对表的检索，如果确定索引不再使用，可以删除索引
    drop index a_name_index;

[20190904170530703.png]: /images/20210923/4aecf7a0358a459c8bebfcb15f85a8d8.png