Shulou(Shulou.com)05/31 Report--

This article is about how mysql is related to update, delete and index optimization. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

About update in not walking the index:

First, the select subquery is in the form of a walking index, as shown below:

Select * from acct_trans_payment where autopayflag='N' and objectno in (

Select serialno from acct_loan where businessstatus='1' and accountingorgid='10080201')

The implementation plan is as follows:

Then the form of the select connection:

Select * from acct_trans_payment a department accredited loan b where a.objectno=b.serialno and a.autopayboat collection collection N'and b.businessstatusstrength collection 10080201' and b.traintingorgidstones

The implementation plan is as follows:

From this we can see that the form optimizer of select in subquery has been internally converted into the form of join links to improve performance!

However, update's is not automatically converted to join links, as shown below:

Update acct_trans_payment set autopayflag='Y' where autopayflag='N' and objectno in (

Select serialno from acct_loan where businessstatus='1' and accountingorgid='10080201')

The retrieval process of the dependent subquery in the select_type in the execution plan is explained below.

So manually rewrite it into join:

Update acct_trans_payment a recalcitrance loan b set a.autopayboat payment 10080201'. Where a.objectno=b.serialno and a.autopayboat gift collection N'and b.businessstatusgift gift 1' and b.autopayboat gift 10080201'

The efficiency has been improved.

About the optimization process of delete:

Delete from cfs.acct_trans_payment where serialno in (

Select serialno from jd.jd_flow where repaymentstype='05'

);

First, let's explain what the dependent subquery in the figure means: the manual explanation is that the select of the jd.jd_flow table in the subquery depends on the external query. In such a sentence, in fact, it means that the query mode in the subquery depends on the external (cfs.acct_payment_log) query. In other words, the retrieval method of the jd.jd_ flow table depends on the data of the cfs.acct_payment_log table. For example, the record serialno (where serialno in) obtained by the cfs.acct_payment_log table can be used by the jd.jd_flow table as a unique_subquery to obtain its corresponding records. To put it another way, if the serialno of the first record scanned by the cfs.acct_payment_ log table is 10001, then the statement of the subsequent subquery is similar to this statement:

Select serialno from jd.jd_flow where repaymentstype='05' and serialno='10001' . At this point, this statement will be optimized to become the execution plan of the above subquery, and since the primary key of jd.jd_flow is serialno, it will take the primary key index.

Through this explanation, we can know that the full table scans the cfs.acct_payment_ log table, passes each record of cfs.acct_payment_log to the jd.jd_ flow table, and the jd.jd_flow table obtains the condition that the record judges itself through the primary key index, then finds a statement that satisfies this query.

Summary: when you see select_type as dependent subquery, it shows that the appearance of the full table, and then give each value value in the appearance of the where value in to the child query table, and then traverse the results!

When the result of the subquery is relatively small, you can first look up the subquery, and then write it in the following form:

Select * from cfs.acct_trans_payment where serialno in (

'101071256426871193705'

'101184648601257984005'

'101366238550600089605'

'101506423110987776005'

'101699991116782796905'

'101872867624796569705'

'99235027109713920005')

Corresponding execution plan:

So when the subquery result set is relatively large, how to optimize it?

In the same form of connection.

Delete a from cfs.acct_trans_payment a join jd.jd_flow b where a.serialno = b.serialno and b.repaymentstypewriter 05'

Equivalent to

Delete from cfs.acct_trans_payment where serialno in (

Select serialno from jd.jd_flow where repaymentstype='05'

);

The following are two implementation plans, obviously a lot of performance improvement!

Another example is:

Delete a, b from cfs.acct_trans_payment a join jd.jd_flow b where a.serialno = b.serialno and b.repaymentstypewriter 05'

Equivalent to

Delete from cfs.acct_trans_payment where serialno in (

Select serialno from jd.jd_flow where repaymentstype='05'

);

meanwhile

Delete from jd.jd_flow where repaymentstype='05' and serialno in (select serialno from

Cfs.acct_trans_payment)

In other words, the eligible ones of both tables will be deleted.

Digression: about the join form of delete:

Delete from left join

DELETE A FROM YSHA A LEFT JOIN YSHB B ON A.code=b.code WHERE b.code is NULL

Equivalent to

DELETE FROM YSHA WHERE NOT EXISTS (SELECT 1 FROM YSHB B WHERE YSHA.code=b.code)

Note that aliases are not allowed when delete, the following will make an error!

Delete from cfs.acct_trans_payment a where EXISTS (select serialno from jd.jd_flow b where b.repaymentstypedia 05 'and a.serialno=b.serialno)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near'a where EXISTS (select serialno from jd.jd_flow b where b.repaymentstypedia 05''at line 1

You can need to do this:

Delete from cfs.acct_trans_payment where EXISTS (select serialno from jd.jd_flow b where b.repaymentstypedia 05 'and cfs.acct_trans_payment.serialno=b.serialno)

Thank you for reading! This is the end of this article on "how to update, delete and optimize the index of mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.