越界值导致执行计划走错-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

越界值导致执行计划走错

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 03:05 PM

clientlead toimplementplanmeet

最近客户生产上遇到一个统计信息陈旧涉及的越界值查询导致执行计划走错的案例： 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')

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Adding Users to MySQL: The Complete TutorialMay 12, 2025 am 12:14 AM

Mastering the method of adding MySQL users is crucial for database administrators and developers because it ensures the security and access control of the database. 1) Create a new user using the CREATEUSER command, 2) Assign permissions through the GRANT command, 3) Use FLUSHPRIVILEGES to ensure permissions take effect, 4) Regularly audit and clean user accounts to maintain performance and security.

Mastering MySQL String Data Types: VARCHAR vs. TEXT vs. CHARMay 12, 2025 am 12:12 AM

ChooseCHARforfixed-lengthdata,VARCHARforvariable-lengthdata,andTEXTforlargetextfields.1)CHARisefficientforconsistent-lengthdatalikecodes.2)VARCHARsuitsvariable-lengthdatalikenames,balancingflexibilityandperformance.3)TEXTisidealforlargetextslikeartic

MySQL: String Data Types and Indexing: Best PracticesMay 12, 2025 am 12:11 AM

Best practices for handling string data types and indexes in MySQL include: 1) Selecting the appropriate string type, such as CHAR for fixed length, VARCHAR for variable length, and TEXT for large text; 2) Be cautious in indexing, avoid over-indexing, and create indexes for common queries; 3) Use prefix indexes and full-text indexes to optimize long string searches; 4) Regularly monitor and optimize indexes to keep indexes small and efficient. Through these methods, we can balance read and write performance and improve database efficiency.

MySQL: How to Add a User RemotelyMay 12, 2025 am 12:10 AM

ToaddauserremotelytoMySQL,followthesesteps:1)ConnecttoMySQLasroot,2)Createanewuserwithremoteaccess,3)Grantnecessaryprivileges,and4)Flushprivileges.BecautiousofsecurityrisksbylimitingprivilegesandaccesstospecificIPs,ensuringstrongpasswords,andmonitori

The Ultimate Guide to MySQL String Data Types: Efficient Data StorageMay 12, 2025 am 12:05 AM

TostorestringsefficientlyinMySQL,choosetherightdatatypebasedonyourneeds:1)UseCHARforfixed-lengthstringslikecountrycodes.2)UseVARCHARforvariable-lengthstringslikenames.3)UseTEXTforlong-formtextcontent.4)UseBLOBforbinarydatalikeimages.Considerstorageov

MySQL BLOB vs. TEXT: Choosing the Right Data Type for Large ObjectsMay 11, 2025 am 12:13 AM

When selecting MySQL's BLOB and TEXT data types, BLOB is suitable for storing binary data, and TEXT is suitable for storing text data. 1) BLOB is suitable for binary data such as pictures and audio, 2) TEXT is suitable for text data such as articles and comments. When choosing, data properties and performance optimization must be considered.

MySQL: Should I use root user for my product?May 11, 2025 am 12:11 AM

No,youshouldnotusetherootuserinMySQLforyourproduct.Instead,createspecificuserswithlimitedprivilegestoenhancesecurityandperformance:1)Createanewuserwithastrongpassword,2)Grantonlynecessarypermissionstothisuser,3)Regularlyreviewandupdateuserpermissions

MySQL String Data Types Explained: Choosing the Right Type for Your DataMay 11, 2025 am 12:10 AM

MySQLstringdatatypesshouldbechosenbasedondatacharacteristicsandusecases:1)UseCHARforfixed-lengthstringslikecountrycodes.2)UseVARCHARforvariable-lengthstringslikenames.3)UseBINARYorVARBINARYforbinarydatalikecryptographickeys.4)UseBLOBorTEXTforlargeuns

See all articles

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Roblox: Grow A Garden - Complete Mutation Guide

3 weeks agoByDDD

Roblox: Bubble Gum Simulator Infinity - How To Get And Use Royal Keys

3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

How to fix KB5055612 fails to install in Windows 10?

3 weeks agoByDDD

Nordhold: Fusion System, Explained

3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Mandragora: Whispers Of The Witch Tree - How To Unlock The Grappling Hook

3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

Dreamweaver Mac version

Visual web development tools

MinGW - Minimalist GNU for Windows

This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.