常用Sql优化

*1.应尽量避免在WHERE子句中使用!=或 <>操作符,否则将导致引擎放弃使用索引而进行全表扫描.,应使用<,<=,>,>=,BETWEEN以及LIKE('a%')

① : 示例sql

反例 : 
EXPLAIN SELECT *
      FROM HIS_BOOK_REG
      WHERE ID != '2';
      
 EXPLAIN SELECT *
     FROM HIS_BOOK_REG
     WHERE ID <> '2';

应改为 : 
EXPLAIN SELECT *
      FROM HIS_BOOK_REG
      WHERE ID = '1'
      UNION ALL
      SELECT *
      FROM HIS_BOOK_REG
      WHERE ID = '2';

② : sql执行计划

反例 : 
 Seq Scan on his_book_reg  (cost=0.00..2231.96 rows=56236 width=4498)
  Filter: (id <> '2'::bigint)
  正例 : 
  Append  (cost=0.41..16.89 rows=2 width=4498)
 ->  Index Scan using book_reg_pkey on his_book_reg  (cost=0.41..8.43 rows=1 width=4498)
         Index Cond: (id = '1'::bigint)
   ->  Index Scan using book_reg_pkey on his_book_reg his_book_reg_1  (cost=0.41..8.43 rows=1 width=4498)
           Index Cond: (id = '2'::bigint)

** WHERE子句中使用 LIKE进行模糊查询时，在关键词前加%或者前后都加%号都无法使用索引，而进行全表扫描;

① : 示例sql

反例 : 
EXPLAIN SELECT *
       FROM HIS_BOOK_REG
       WHERE PAT_NAME LIKE '%张'
       
EXPLAIN SELECT *
       FROM HIS_BOOK_REG
       WHERE PAT_NAME LIKE '%张%'        

② : sql执行计划

Seq Scan on his_book_reg  (cost=0.00..2231.96 rows=5 width=4498)
  Filter: ((pat_name)::text ~~ '%张'::text)

** 应尽量避免在 WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描，创建表时NULL是默认值，但大多数时候应该使用NOT NULL，或者使用一个默认值，如0作为默认值;

经验证后,不同的数据库对null的优化策略略有不同;

Postgre : is null 走索引,is not null 全表扫描;
 EXPLAIN SELECT * FROM HIS_BOOK_REG WHERE ID IS NULL;
 EXPLAIN SELECT * FROM HIS_BOOK_REG WHERE ID  IS NOT NULL;
 Postgre执行计划如下 : 
 Index Scan using book_reg_pkey on his_book_reg  (cost=0.41..6.23 rows=1 width=4498)
   Index Cond: (id IS NULL)
Seq Scan on his_book_reg  (cost=0.00..2091.37 rows=56237 width=4498)
 Filter: (id IS NOT NULL)

Oracle :  无论是null还是not null,都会导致全表扫描;
EXPLAIN PLAN FOR SELECT *
            FROM FIN_COM_UNDRUGINFO
            WHERE ITEM_CODE IS NOT NULL;
            SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY)

Oracle执行计划如下图 :

**应尽量避免在 WHERE子句中使用OR来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。使用OR的字句可以分解成多个查询，并且通过UNION连接多个查询。他们的速度只同是否使用索引有关，如果查询需要用到联合索引，用UNION ALL执行的效率更高;

① : 示例sql

反例 : 
EXPLAIN SELECT *
       FROM HIS_BOOK_REG
       WHERE ID = '1'
          OR PAT_NAME = 'A'
       
应改为 : 
EXPLAIN SELECT *
       FROM HIS_BOOK_REG
       WHERE ID = '1'
       UNION ALL
       SELECT *
       FROM HIS_BOOK_REG
       WHERE PAT_NAME = 'A';          

② : sql执行计划

反例  : 
Seq Scan on his_book_reg  (cost=0.00..2372.55 rows=4 width=4498)
  Filter: ((id = '1'::bigint) OR ((pat_name)::text = 'a'::text))

**应尽量避免在WHERE子句中使用IN和NOT IN，否则将导致全表扫描，对于连续的数值，能用 BETWEEN AND尽量避免使用 IN。一般，用EXISTS代替 IN。若需要使用 IN，在IN后面值的列表中，按照值的分布数量降序排列，减少判断的次数;

 **不同数据库优化不一样,Postgre中in是走索引的,not不走索引;Oracle中均不走索引;**

① : 示例sql

反例 : 
EXPLAIN SELECT *
       FROM HIS_BOOK_REG
       WHERE ID IN ('50', '51', '52');
应改为 : 
EXPLAIN SELECT *
       FROM HIS_BOOK_REG
       WHERE ID BETWEEN '50' AND '52';
     

**应尽量避免在WHERE子句中对 “=”左边的字段进行函数、算术运算及其他表达式运算，可以将表达式运算移至“=”右边，否则将导致引擎放弃使用索引而进行全表扫描;

① : 示例sql

反例 : 
EXPLAIN SELECT *
       FROM HIS_BOOK_REG
       WHERE TRIM(DEPT_CODE) = '1';

**如果在 WHERE子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时。它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项，可以改为强制查询使用索引;

① : 示例sql

反例 : 
SELECT *
FROM HIS_BOOK_REG
WHERE ID =@ ID

**在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引(遵循最左前缀原则)，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致.

参考 : sql优化