SQL优化：很简单的一篇进步SQL机能的文章！【MySQL教程】,SQL性能优化实现

在sql查询中为了进步查询效力,我们常常会采用一些步伐对查询语句举行sql优化,下面总结的一些要领,有须要的能够参考参考。在某运营商的优化阅历中曾遇到了一条比较有意思的 SQL，细致以下：

1 该最最先的 sql 实行状况以下

SQL> SELECT
  2    NVL(T.RELA_OFFER_SPEC_ID, SUBOS.SUB_OFFER_SPEC_ID) "offerSpecId"
  3    FROM OFFER_SPEC_RELA T
  4    LEFT JOIN OFFER_SPEC_GRP_RELA SUBOS
  5    ON T.RELA_GRP_ID     = SUBOS.OFFER_SPEC_GRP_ID
  6    AND subos.start_dt  <= SYSDATE
  7    AND subos.end_dt    >= SYSDATE
  8    WHERE T.RELA_TYPE_CD = 2
  9    AND t.start_dt      <= SYSDATE
 10    AND t.end_dt        >= SYSDATE
 11    AND (T.OFFER_SPEC_ID = 109910000618
 12    OR EXISTS
 13      (SELECT A.OFFER_SPEC_GRP_ID
 14      FROM OFFER_SPEC_GRP_RELA A
 15      WHERE A.SUB_OFFER_SPEC_ID = 109910000618
 16      AND T.OFFER_SPEC_GRP_ID   = A.OFFER_SPEC_GRP_ID
 17      ))
 18    AND rownum<500;

no rows selected
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1350156609

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<500)
   2 - filter("T"."OFFER_SPEC_ID"=109910000618 OR  EXISTS (SELECT 0 FROM
              "SPEC"."OFFER_SPEC_GRP_RELA" "A" WHERE "A"."OFFER_SPEC_GRP_ID"=:B1 AND
              "A"."SUB_OFFER_SPEC_ID"=109910000618))
   3 - access("T"."RELA_GRP_ID"="SUBOS"."OFFER_SPEC_GRP_ID"(+))
   4 - filter("T"."RELA_TYPE_CD"=2 AND "T"."END_DT">=SYSDATE@! AND
              "T"."START_DT"<=SYSDATE@!)
   5 - filter("SUBOS"."END_DT"(+)>=SYSDATE@! AND "SUBOS"."START_DT"(+)<=SYSDATE@!)
   6 - access("A"."SUB_OFFER_SPEC_ID"=109910000618 AND "A"."OFFER_SPEC_GRP_ID"=:B1)
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      12444  consistent gets
          0  physical reads
          0  redo size
        339  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
 
                  PLAN                     GET     DISK    WRITE              ROWS      ROWS USER_IO(MS)  ELA(MS)  CPU(MS) CLUSTER(MS)    PLSQL
END_TI I    HASH VALUE EXEC           PRE EXEC PRE EXEC PER EXEC ROW_P    PRE EXEC PRE FETCH    PER EXEC PRE EXEC PRE EXEC    PER EXEC PER EXEC

2 第一次剖析
此时应该有以下个处所值得注意
1) 该 sql 天天实行上千次，均匀每次实行返回不到 10 行数据，然则均匀逻辑读到达1.2W，能够存在机能题目。
2）ID 为 4,5 的实行计划途径中涌现了两个全表扫描，看到这儿我们能够想到多是没有适宜的索引致使走了全表扫描从而实行效力低下。
3）ID 为 2 的实行计划途径涌现了 FILTER，且 3，和 6 为其子途径，假如FILTER有两个及两个以上的子途径，那末他的实行道理将类似于嵌套轮回，id 号最小的子途径假如返回行数较多，能够会致使屡次实行id号更小的子途径，致使机能低下。平常存在 “OR EXISTS” 的时刻会涌现此状况，能够依据状况防止。

相干链接：

PHP-FPM完成机能优化，php-fpm机能优化

【SQL】MySQL机能优化_MySQL

MySQL优化视频教程

以上就是SQL优化：很简单的一篇进步SQL机能的文章！的细致内容，更多请关注ki4网别的相干文章！