コミュニティ 学ぶ ツールライブラリ レジャー
検索
日本語
ホームページ > データベース > mysql チュートリアル > 本文

越界值导致执行计划走错

WBOY
リリース： 2016-06-07 15:05:27
オリジナル
1252 人が閲覧しました

最近客户生产上遇到一个统计信息陈旧涉及的 越界 值查询 导致 执行 计划 走错的案例： 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'];} ロジックや変数は重要ではありませんが、[false...
から 2024-04-06 21:55:20
0
1
778
結果セットから最小値のみを表示する方法 (MYSQL) 次のステートメントがあります: selectDATE(recieved_on)asDay,round(count(*)/24)AS'average'frommessagewhere...
から 2024-04-06 21:44:19
0
1
603
クエリと式は、新しい列を含むテーブル全体を形成します。新しい列は、他の条件列の値を含む式です。 編集: 式は「値」列、つま​​りカテゴリに基づいて最も低い user_id を持つ「値」列である必要があります。カテゴリ「A」の最も低い「user_id」の「value」列が 0....
から 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
関連トピック
詳細>
人気のおすすめ
人気のチュートリアル
詳細>
関連するチュートリアル
人気のおすすめ
最新のコース
最新のダウンロード
詳細>
ウェブエフェクト
公式サイト
サイト素材
フロントエンドテンプレート