Shulou(Shulou.com)06/01 Report--

Two database optimization analysis and solution is what, many novices are not very clear about this, in order to help you solve this problem, the following small series will explain in detail for everyone, there are people who need this to learn, I hope you can harvest.

No matter who or what, you will not destroy me. If you knock me down, I'll get back up. If you beat me, I will rise and try again.

I promised my consultant yesterday that there was a problem in the financial software, but I have been busy and have no time to support it. I found the consultant early this morning. The problem appeared in the ORACLE database. When executing a storage process, the first return speed is very fast, and the second follow-up will be slower and slower. Finally, I may not be able to bear it.

First of all, let's look at what kind of stored procedure it is. After checking, we found that it is two stored procedures, one of which is a cursor, and each time the obtained numerical variable is called to another stored procedure, and another stored procedure is called. There are two cursors in the other stored procedure, which belongs to nested type.

First of all, the most important SQL here is like this.

insert into cntvoucher_wqt

(vchdate, kmh, opkmh, dir, vchmemo, mny, mccode, vtid)

select cnt.vchdate,

cnt.kmh,

cnt.opkmh,

cnt.dir,

cnt.vchmemo,

sum(cnt.mny) mny,

cpid,

vtid_id

from cntvoucher cnt

left join cntbusssheet sheet

on cnt.transid = sheet.sheetid

where cnt.vchdate = f_actdate

--and sheet.extaddr2 in

and exists (select distinct b.extaddr2

from cntvoucher a

left join cntbusssheet b on a.transid = b.sheetid

where sheet.extaddr2 = b.extaddr2

and a.cpid = f_eventcode

and a.kmh = f_km

and a.dir = f_dir

and a.vchdate = f_actdate

-- and a.vchdate>=date'2018-01-01'

)

group by cnt.vchdate, cnt.kmh, cnt.opkmh, cnt.dir, cnt.vchmemo, cpid;

After running the stored procedure, it is found that the lock exists in

INSERT INTO CNTVOUCHER_WQT (VCHDATE, KMH, OPKMH, DIR, VCHMEMO, MNY, MCCODE, VTID)

SELECT CNT.VCHDATE, CNT.KMH, CNT.OPKMH, CNT.DIR, CNT.VCHMEMO, SUM(CNT.MNY) MNY, CPID, :B2

FROM CNTVOUCHER CNT

LEFT JOIN CNTBUSSSHEET SHEET ON CNT.TRANSID = SHEET.SHEETID WHERE CNT.VCHDATE = :B1 AND

EXISTS (SELECT DISTINCT B.EXTADDR2

FROM CNTVOUCHER A

LEFT JOIN CNTBUSSSHEET B ON A.TRANSID = B.SHEETID

WHERE SHEET.EXTADDR2 = B.EXTADDR2 AND A.CPID = :B5 AND A.KMH = :B4

AND A.DIR = :B3 AND A.VCHDATE = :B1 )

GROUP BY CNT.VCHDATE, CNT.KMH, CNT.OPKMH, CNT.DIR, CNT.VCHMEMO, CPID

The above statement.

By looking at EXPLAIN and verifying the execution time of this select statement, this statement takes about less than a minute to execute, due to the small memory, the amount of data is also relatively large tens of millions of data (actually OK).

In fact, I have said before, in the operation of data,(SQL SERVER , MYSQL , PG, Oracle), these types of RDS databases are best not to use (especially the query is very slow) insert into select.

We suggest that the method is, query and insert to be separated, and ORACLE SQL SERVER, PG have a good temporary table mechanism, especially SESSION base class. MYSQL also has temporary tables, but the high probability is not used, which is related to the way he uses it, of course, to see the specific situation.

And there are two reasons for the above problems.

1 Use cursors to trigger insert into select , which is equivalent to high-frequency SQL statements that trigger this query slowly, and INSERT INTO and SELECT are equivalent to a transaction, the inserted table will be locked, so it often causes intolerable slow problems, especially when the number of loops is large.

Database optimization, is to be able to batch one-time processing, do not divide multiple processing (such as cursor mode), and in MYSQL thinking, short and small transactions, in fact, into the use of other databases is also beneficial. After all, it is definitely not good to occupy the X lock of the table for a long time.

The solution given here

1 Using ORACLE temporary table SESSION level, then insert data into temporary table first each time, and then insert the data of temporary table into final table, so as to reduce the time of insert into select, which is helpful for database optimization.

2 straighten out the logic, can convert the cursor to a select can query the data, do not use the cursor way.

Of course, there are other ways to optimize, but in the current situation, the above two can solve the problem.

Just after sorting out the above problems, the developers came again in the afternoon.

The requirement is that MYSQL data should be inserted in batches. The inserted table has a unique index. When the inserted value conflicts with this unique index, it cannot be inserted. This is of course. It is the root of the original design of this unique index. It is not to insert it to prevent repeated deductions or loans. However, the problem is that if MYSQL data is inserted in batches, one item cannot be inserted, and the whole is ROLLBACK. This is not a bad thing. Later programmers changed to data insertion one by one, which was actually a worse thing, inefficient, and significantly increased pressure on the database.

The final solution:

Insert into on duplicate key update such statements, since the batch of inserts found duplicate, we can add a field to the original table, and found duplicate values, we will not insert, and update the newly added field behind, to UPDATE a value. This ensures that there are duplicate inserts and no batch rollback, and also knows which rows have duplicate values that you want to insert. It's a two-pronged idea.

Did reading the above help you? If you still want to have further understanding of related knowledge or read more related articles, please pay attention to the industry information channel, thank you for your support.

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.