java面试之sql语句优化

1. 建立合适的索引，尽量利用索引，避免数据量大的表的全表扫描

   索引是表的一个概念部分,用来提高检索数据的效率，ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证。通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率. 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的。

2. 合理使用临时表

3**. 避免写复杂的SQL（会降低sql查询的速度），有时候可以分几个SQL去完成一个问题**

4. 在不影响业务的前提下减小事务的粒度（粒度解释：https://blog.csdn.net/weixin_40654252/article/details/85232914，https://blog.csdn.net/zdplife/article/details/48035837）

   Sql server2000中锁定的粒度包括 行、页、扩展盘区、表、库等资源。

5. IS NULL 与IS NOT NULL

   任何sql语句只要在where语句后面添加is null或者is not null，那么oracl优化器将不再使用索引。

6. 使用带通配符（%）的语句

   列举两个例子说明该问题：

   查询ur_user_info表中phone_no带10的服务号码

   例子1：Select *from ur_user_info where phone_no like ‘%10%’；

   例子2：Select *from ur_user_info where phone_no like ‘10%’；

   由于例1中通配符（%）在搜寻词首出现，所以oracle系统不使用phone_no的索引，通配符会降低查询的效率，但当通配符不再首出现，又能使用索引，如例2所示。

7. where字句中的连接顺序

   oracle的解析按照从上而下解析，因此表之间的连接必须写在where条件的最前面

8. 使用truncate代替delete

   当删除表时，使用delete执行操作，回滚端用来存放可恢复的信息，当没有提交事务的时候，执行回滚事务，数据会恢复到执行delete操作之前，而当用truncate是，回滚端则不会存放可恢复的信息，减少资源的调用。

9. 用where字句替换HAVING字句

   避免使用 HAVING 子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过 WHERE 子句限制记录的数目,那就能减少这方面的开销.

10. 减少对表的查询

    在进行where语句进行连表子查询时，尽量减少对表的查询次数

    低效：

    Select tab_name from tables where tab_name = ( select

    tab_name from tab_columns where version = 604) and db_ver=

    ( select db_ver from tab_columns where version = 604)

    高效：

    select tab_name from tables where (tab_name,db_ver) =

    ( select tab_name,db_ver from tab_columns where version =604)

11. 用in代替or

    低效

    Select… from location where loc_id = 10 or loc_id = 20 or loc_id = 30

    高效

    Select…from location where loc_in in (10,20,30);

12. 如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引. 当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引。

13. 避免在索引列上使用函数

14. 当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换.在进行转化的时候，oracle将不会使用索引

15. 用>= 替代 >

16. 通过使用>=、<=等，避免使用NOT命令

    例子：

    select * from employee where salary <> 3000;

    对这个查询，可以改写为不使用NOT：

    select * from employee where salary<3000 or salary>3000;

    ​ 虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引，而第一种查询则不能使用索引。