지역 사회 배우다 도구 라이브러리 여가
찾다
한국어
집 > 데이터 베이스 > MySQL 튜토리얼 > 본문

越界值导致执行计划走错

WBOY
풀어 주다： 2016-06-07 15:05:27
원래의
1253명이 탐색했습니다.

最近客户生产上遇到一个统计信息陈旧涉及的 越界 值查询 导致 执行 计划 走错的案例： SQL查询 bankdate = '2013/03/19' 就走到了不合适的索引 IDX_DSF_BANKAPPLHISTORY_02. 下面的 执行 计划 可以忽略DSF_BANKCODE这块。 下面的 执行 计划 可以忽略DSF_BANK

最近客户生产上遇到一个统计信息陈旧涉及的越界值查询导致执行计划走错的案例：

SQL查询bankdate >= '2013/03/19' 就走到了不合适的索引IDX_DSF_BANKAPPLHISTORY_02.

下面的执行计划可以忽略DSF_BANKCODE这块。

下面的执行计划可以忽略DSF_BANKCODE这块。


SQL> select *
  2    from dsf_bankapplhistory t
  3   where (((((bankdate >= '2013/03/19' and
  4         recordnum = 'P00Y0ABFG1E220120204358') and monthnm = '16') and
  5         bankcode in
  6         (select bankcode
  7               from dsf_bankcode
  8              where (bankcode = '800000000000' or
  9                    get_bankcode = '800000000000'))) and flag = '2') and
 10         opertype = '1')
 11  /

Execution Plan
----------------------------------------------------------
Plan hash value: 3875365240

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |     1 |   631 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI           |                            |     1 |   631 |     6   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DSF_BANKAPPLHISTORY        |     1 |   605 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_DSF_BANKAPPLHISTORY_02 |     1 |       |     3   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| DSF_BANKCODE               |     2 |    52 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | IDX_BANKCODE_1             |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"='2' AND "RECORDNUM"='P00Y0ABFG1E220120204358' AND "MONTHNM"='16' AND
              "OPERTYPE"='1')
   3 - access("BANKDATE">='2013/03/19')
   4 - filter("GET_BANKCODE"='800000000000' OR "BANKCODE"='800000000000')
   5 - access("BANKCODE"="BANKCODE")



生产上的这条SQL语句走错了索引，本身应该走到索引IDX_DSF_BANKAPPLHISTORY_10的，缺走了IDX_DSF_BANKAPPLHISTORY_02。

?2个索引的情况如下：
?
? IDX_DSF_BANKAPPLHISTORY_10(RECORDNUM,MONTHNM??)?
? IDX_DSF_BANKAPPLHISTORY_02(BANKDATE)?


如果查询采用bankdate >= '2013/03/18' 就能走到正确的索引。

SQL> select *
  2    from dsf_bankapplhistory t
  3   where (((((bankdate >= '2013/03/18' and
  4         recordnum = 'P00Y0ABFG1E220120204358') and monthnm = '16') and
  5         bankcode in
  6         (select bankcode
  7               from dsf_bankcode
  8              where (bankcode = '800000000000' or
  9                    get_bankcode = '800000000000'))) and flag = '2') and
 10         opertype = '1')
 11  /

Execution Plan
----------------------------------------------------------
Plan hash value: 1807757810

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |     1 |   631 |     7   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI           |                            |     1 |   631 |     7   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DSF_BANKAPPLHISTORY        |     1 |   605 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_DSF_BANKAPPLHISTORY_10 |     1 |       |     4   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| DSF_BANKCODE               |     2 |    52 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | IDX_BANKCODE_1             |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"='2' AND "BANKDATE">='2013/03/18' AND "OPERTYPE"='1')
   3 - access("RECORDNUM"='P00Y0ABFG1E220120204358' AND "MONTHNM"='16')
   4 - filter("GET_BANKCODE"='800000000000' OR "BANKCODE"='800000000000')
   5 - access("BANKCODE"="BANKCODE")
   
采用3月18日之前的日期都能走到正确的索引，而采用3月19日及其之后日期都会走到错误的IDX_DSF_BANKAPPLHISTORY_02索引上。

其实3月18日和3月19日走索引idx_dsf_bankapplhistory_10的成本都没有变化。

SQL> select  /*+index(t,idx_dsf_bankapplhistory_10)*/*
  2    from dsf_bankapplhistory t
  3   where (((((bankdate >= '2013/03/18' and
  4         recordnum = 'P00Y0ABFG1E220120204358') and monthnm = '16') and
  5         bankcode in
  6         (select bankcode
  7               from dsf_bankcode
  8              where (bankcode = '800000000000' or
  9                    get_bankcode = '800000000000'))) and flag = '2') and
 10         opertype = '1');

Execution Plan
----------------------------------------------------------
Plan hash value: 1807757810

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |     1 |   631 |     7   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI           |                            |     1 |   631 |     7   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DSF_BANKAPPLHISTORY        |     1 |   605 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_DSF_BANKAPPLHISTORY_10 |     1 |       |     4   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| DSF_BANKCODE               |     2 |    52 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | IDX_BANKCODE_1             |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"='2' AND "BANKDATE">='2013/03/18' AND "OPERTYPE"='1')
   3 - access("RECORDNUM"='P00Y0ABFG1E220120204358' AND "MONTHNM"='16')
   4 - filter("GET_BANKCODE"='800000000000' OR "BANKCODE"='800000000000')
   5 - access("BANKCODE"="BANKCODE")





SQL> select  /*+index(t,idx_dsf_bankapplhistory_10)*/*
  2    from dsf_bankapplhistory t
  3   where (((((bankdate >= '2013/03/19' and
  4         recordnum = 'P00Y0ABFG1E220120204358') and monthnm = '16') and
  5         bankcode in
  6         (select bankcode
  7               from dsf_bankcode
  8              where (bankcode = '800000000000' or
  9                    get_bankcode = '800000000000'))) and flag = '2') and
 10         opertype = '1')
 11  /

Execution Plan
----------------------------------------------------------
Plan hash value: 1807757810

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |     1 |   631 |     7   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI           |                            |     1 |   631 |     7   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DSF_BANKAPPLHISTORY        |     1 |   605 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_DSF_BANKAPPLHISTORY_10 |     1 |       |     4   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| DSF_BANKCODE               |     2 |    52 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | IDX_BANKCODE_1             |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"='2' AND "BANKDATE">='2013/03/19' AND "OPERTYPE"='1')
   3 - access("RECORDNUM"='P00Y0ABFG1E220120204358' AND "MONTHNM"='16')
   4 - filter("GET_BANKCODE"='800000000000' OR "BANKCODE"='800000000000')
   5 - access("BANKCODE"="BANKCODE")

而3月18日和3月19日走索引idx_dsf_bankapplhistory_02的成本却降低了1，而估算出的基数也降为1，导致CBO选择了这个错误的索引。

SQL> select  /*+index(t,idx_dsf_bankapplhistory_02)*/*
  2    from dsf_bankapplhistory t
  3   where (((((bankdate >= '2013/03/18' and
  4         recordnum = 'P00Y0ABFG1E220120204358') and monthnm = '16') and
  5         bankcode in
  6         (select bankcode
  7               from dsf_bankcode
  8              where (bankcode = '800000000000' or
  9                    get_bankcode = '800000000000'))) and flag = '2') and
 10         opertype = '1')
 11  /

Execution Plan
----------------------------------------------------------
Plan hash value: 3875365240

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |     1 |   631 |     7   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI           |                            |     1 |   631 |     7   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DSF_BANKAPPLHISTORY        |     1 |   605 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_DSF_BANKAPPLHISTORY_02 |    21 |       |     3   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| DSF_BANKCODE               |     2 |    52 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | IDX_BANKCODE_1             |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"='2' AND "RECORDNUM"='P00Y0ABFG1E220120204358' AND "MONTHNM"='16' AND
              "OPERTYPE"='1')
   3 - access("BANKDATE">='2013/03/18')
   4 - filter("GET_BANKCODE"='800000000000' OR "BANKCODE"='800000000000')
   5 - access("BANKCODE"="BANKCODE")
   
SQL> select  /*+index(t,idx_dsf_bankapplhistory_02)*/*
  2    from dsf_bankapplhistory t
  3   where (((((bankdate >= '2013/03/19' and
  4         recordnum = 'P00Y0ABFG1E220120204358') and monthnm = '16') and
  5         bankcode in
  6         (select bankcode
  7               from dsf_bankcode
  8              where (bankcode = '800000000000' or
  9                    get_bankcode = '800000000000'))) and flag = '2') and
 10         opertype = '1')
 11  /

Execution Plan
----------------------------------------------------------
Plan hash value: 3875365240

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |     1 |   631 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI           |                            |     1 |   631 |     6   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DSF_BANKAPPLHISTORY        |     1 |   605 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_DSF_BANKAPPLHISTORY_02 |     1 |       |     3   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| DSF_BANKCODE               |     2 |    52 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | IDX_BANKCODE_1             |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"='2' AND "RECORDNUM"='P00Y0ABFG1E220120204358' AND "MONTHNM"='16' AND
              "OPERTYPE"='1')
   3 - access("BANKDATE">='2013/03/19')
   4 - filter("GET_BANKCODE"='800000000000' OR "BANKCODE"='800000000000')
   5 - access("BANKCODE"="BANKCODE")
   
3月18日估算出的基数为21，而3月19日估算出的基数为1(实际上这里估算出的基数应该为0,但一般CBO不允许CARDINALITY为0)。

经查询统计信息过于陈旧：

SQL> select table_name,last_analyzed from dba_tables where table_name='DSF_BANKAPPLHISTORY';

TABLE_NAME           LAST_ANALYZED
-------------------- -------------------
DSF_BANKAPPLHISTORY  2012-11-25 21:17:26

这很容易让人联想到越界值的查询，对于越界值的查询将会导致选择性的线性降低，如下图所示：





下面的查询可以查询到BANKDATE的最大和最小值。

SQL> declare
  2     v_high_date date;
  3     v_low_date  date;
  4     v_high_value dba_tab_col_statistics.high_value%type;
  5     v_low_value  dba_tab_col_statistics.low_value%type;
  6  begin
  7     select high_value,low_value into v_high_value,v_low_value
  8     from dba_tab_col_statistics 
  9     where table_name='DSF_BANKAPPLHISTORY' and column_name='BANKDATE';
 10     dbms_stats.convert_raw_value(v_high_value,v_high_date);
 11     dbms_stats.convert_raw_value(v_low_value,v_low_date);
 12     dbms_output.put_line('high date:'||to_char(v_high_date,'YYYY-MM-DD HH24:MI:SS'));
 13     dbms_output.put_line('low  date:'||to_char(v_low_date,'YYYY-MM-DD HH24:MI:SS'));
 14  end;
 15  /
high date:2012-11-25 00:00:00
low  date:2012-08-03 00:00:00

PL/SQL procedure successfully completed.



为什么3月19日是转折点，下面的查询可以解析这一点：

SQL> select date '2012-11-25'+(date '2012-11-25' - date '2012-08-03') from dual;

DATE'2012-11-25'+11
-------------------
2013-03-19 00:00:00

其实还有一个转折点就是2012-04-11

SQL> select date '2012-08-03'-(date '2012-11-25' - date '2012-08-03') from dual;

DATE'2012-08-03'-(D
-------------------
2012-04-11 00:00:00

下面我们来看看2012-04-11这个查询的执行计划。







SQL> select  *
  2    from dsf_bankapplhistory t
  3   where bankdate
  4         recordnum = 'P00Y0ABFG1E220120204358' and monthnm = '16'
  5  /

Execution Plan
----------------------------------------------------------
Plan hash value: 471247677

----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                            |     1 |   605 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| DSF_BANKAPPLHISTORY        |     1 |   605 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_DSF_BANKAPPLHISTORY_02 |     1 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RECORDNUM"='P00Y0ABFG1E220120204358' AND "MONTHNM"='16')
   2 - access("BANKDATE"

SQL> select  *
  2    from dsf_bankapplhistory t
  3   where bankdate
  4         recordnum = 'P00Y0ABFG1E220120204358' and monthnm = '16'
  5  /

Execution Plan
----------------------------------------------------------
Plan hash value: 477419360

----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                            |     1 |   605 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| DSF_BANKAPPLHISTORY        |     1 |   605 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_DSF_BANKAPPLHISTORY_10 |     1 |       |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("BANKDATE"
   2 - access("RECORDNUM"='P00Y0ABFG1E220120204358' AND "MONTHNM"='16')







관련 라벨：
고객 导致 执行 计划 만나다
원천：php.cn
이전 기사：配置apache日志过滤 다음 기사：rsync配置详解
본 웹사이트의 성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.
저자별 최신 기사
최신 이슈
반환 값의 변수 형식은 무엇입니까? 저는 PHP를 처음 배우는 사람입니다. 코드 조각을 찾았습니다: if($x<time()){return[false,'error'];} 논리나 변수는 중요하지 ...
에서 2024-04-06 21:55:20
0
1
778
결과 집합에서 가장 낮은 값만 표시하는 방법(MYSQL) 나는 다음과 같은 진술을 가지고 있습니다 : selectDATE(recieved_on)asDay,round(count(*)/24)AS'average'frommes...
에서 2024-04-06 21:44:19
0
1
603
쿼리와 수식은 새 열로 전체 테이블을 형성합니다. 여기서 새 열은 다른 조건부 열 값을 포함하는 수식입니다. 편집: 수식은 "값" 열(카테고리를 기준으로 user_id가 가장 낮은 "값" 열)이어야 합니다. 따라서 카테고리 "...
에서 2024-04-06 21:23:40
0
2
530
opentbs를 사용하여 odt 파일을 생성할 때 발생하는 문제: 동일한 키의 값이 별도의 열이 아닌 동일한 행에 표시됩니다. PHP를 사용하여 odt를 만들기 위해 OpenTbs라는 라이브러리를 사용하고 있는데, 열과 행이 동적으로 생성되기 때문에 사용하고 있습니다. 행과 열을 만드는 ...
에서 2024-04-06 20:18:18
0
1
483
여러 경로에 선언된 작업자 풀이 임계값을 고려하지 않고 여전히 CPU 사용량을 유지할 수 있습니까? CPU 집약적인 작업을 처리하기 위해 작업자 풀이 있는 node.js 시스템을 찾고 있지만 여러 경로에서의 CPU 사용량에 대해 약간 혼란스럽습니다. 시나리오는 ...
에서 2024-04-06 19:54:23
0
1
444
관련 주제
더>
인기 추천
인기 튜토리얼
더>
관련 튜토리얼
인기 추천
최신 강좌
최신 다운로드
더>
웹 효과
웹사이트 소스 코드
웹사이트 자료
프론트엔드 템플릿