Method and device for interacting data with database, electronic equipment and storage medium

By merging SQL statements that interact with the terminal and the database, DML operations are reduced, the interaction performance between the terminal and the database is improved, the problem of low performance in peak business scenarios is solved, and costs are reduced.

CN118964382APending Publication Date: 2024-11-15JINZHUAN INFORMATION TECHNOLOGY CO LTD
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202411023232.9
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2024-07-29
Publication Date
2024-11-15

AI Technical Summary

Technical Problem

In the business peak scenario, the performance of terminal interaction with the database is low, and existing optimization strategies such as reducing business volume or optimizing on the database side will increase costs.

Method used

By determining the SQL statement corresponding to the target row data in the table, if the preset merge condition is met, it will be merged into a new SQL statement, reducing DML operations, using concurrent thread batch consumption executable SQL in the cache, and batching through JDBC-API Deliver to the database to establish long connections to improve performance.

Benefits of technology

It reduces the frequency and traffic between the terminal and the database, improves the interaction performance, and reduces the computing pressure and load on the database server.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118964382A_ABST
    Figure CN118964382A_ABST
Patent Text Reader

Abstract

The invention relates to a method and device for interacting data with a database, electronic equipment and a storage medium, and the method comprises the steps that a first SQL statement and a second SQL statement corresponding to target row data in a table are determined, the first SQL statement and the second SQL statement are used for sequentially executing DML operation on the target row data according to a DML operation time sequence, and the first SQL statement and the second SQL statement are used for executing DML operation on the target row data according to the DML operation time sequence; the first SQL statement is an original statement or a combined statement; if the DML operation indicated by the first SQL statement and the DML operation indicated by the second SQL statement meet a preset merging condition, merging the first SQL statement and the second SQL statement into a new SQL statement; and when the new SQL statements cannot be merged, issuing the SQL statements corresponding to the target row data to a database. The interaction performance of the terminal and the database is improved.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present application relates to the field of data interaction technology, and in particular to a method, device, electronic device and storage medium for interacting with a database. Background Art

[0002] In the field of terminal-database interaction, especially in business peak scenarios, multi-session, high-concurrency DML (Data Manipulation Language) operations are required on tables. As the business volume increases, the frequency of terminal-database interaction also increases accordingly. The industry usually uses the following methods to improve the performance of database interaction: 1. Reduce the business volume per unit time; 2. Trade space for performance, such as using a distributed, multi-node deployment mode for the database; 3. Replace the storage system, such as CQRS, replacement storage, etc. In the above optimization strategies, method 1 is to optimize on the terminal, but for real production environments and business scenarios, it is difficult to reduce the business volume. Methods 2 and 3 are both optimized on the database side, which will increase various costs, such as hardware costs, deployment costs, and operation and maintenance costs. Summary of the invention

[0003] The present application provides a method, device, electronic device and storage medium for interacting with a database to solve the problem of low interaction performance between a terminal and a database.

[0004] In a first aspect, the present application provides a method for interacting with a database, the method comprising:

[0005] Determine a first SQL statement and a second SQL statement corresponding to the target row data in the table, wherein the first SQL statement and the second SQL statement are used to sequentially perform DML operations on the target row data according to a DML operation sequence, and the first SQL statement is an original statement or a merged statement;

[0006] If the DML operation indicated by the first SQL statement and the DML operation indicated by the second SQL statement meet a preset merging condition, merging the first SQL statement and the second SQL statement into a new SQL statement;

[0007] When the new SQL statement can no longer be merged, the SQL statement corresponding to the target row data is sent to the database.

[0008] Optionally, if the DML operation indicated by the first SQL statement and the DML operation indicated by the second SQL statement meet a preset merging condition, merging the first SQL statement and the second SQL statement into a new SQL statement includes:

[0009] If the first SQL statement indicates an insert operation and the second SQL statement indicates a delete operation, the first SQL statement and the second SQL statement are merged, and the generated new SQL statement indicates no operation on the target row data; or,

[0010] If the first SQL statement indicates an insert operation, and the second SQL statement indicates an update operation, the first SQL statement and the second SQL statement are merged, and the generated new SQL statement indicates inserting the updated data; or,

[0011] If the first SQL statement indicates an update operation, and the second SQL statement indicates an update operation, the first SQL statement and the second SQL statement are merged, and the generated new SQL statement indicates that the target row data is updated for the second time; or,

[0012] If the first SQL statement indicates an update operation, and the second SQL statement indicates a delete operation, the first SQL statement and the second SQL statement are merged, and the generated new SQL statement indicates deleting the target row data.

[0013] Optionally, after determining the first SQL statement and the second SQL statement corresponding to the target row data in the table, the method further includes:

[0014] If the DML operation indicated by the first SQL statement and the DML operation indicated by the second SQL statement do not satisfy the preset merging condition, the first SQL statement, the second SQL statement, and the SQL statement following the second SQL statement are respectively stored in the cache.

[0015] Optionally, sending the SQL statement corresponding to the target row data to the database includes:

[0016] After the SQL statements corresponding to each row of data in the table are merged, the executable SQL in the cache is consumed in batches using concurrent threads, wherein the executable SQL includes the merged SQL statements and the SQL statements that cannot be merged;

[0017] Load the database driver and create a long connection session;

[0018] Call multiple JDBC-APIs to send the executable SQL statements to the database in batches.

[0019] Optionally, determining the first SQL statement and the second SQL statement corresponding to the target row data in the table includes:

[0020] Determine whether there is a merged SQL statement corresponding to the target row data in the cache, wherein each SQL statement carries a unique identifier of the corresponding row data;

[0021] If it exists, use the merged SQL statement as the first SQL statement;

[0022] If it does not exist, the first SQL statement in the table data file is used as the first SQL statement;

[0023] According to the DML operation sequence, the next SQL statement after the first SQL statement in the table data file is used as the second SQL statement.

[0024] Optionally, before determining the first SQL statement and the second SQL statement corresponding to the target row data in the table, the method further includes:

[0025] Use the ETL module to filter and screen source data according to pre-configured black and white lists, row filtering, and field masking;

[0026] The ETL module is used to perform mapping and conversion processing on the filtered source data to obtain the syntax and field type that can be recognized by the database, and generate pre-compiled SQL with placeholders.

[0027] Optionally, before using the ETL module to filter and screen the source data, the method further includes:

[0028] The initial data is consumed by the consumer thread of Kafka, and the source data is generated after the message is parsed and formatted in the consumer thread;

[0029] The initial data is processed and formatted by the performance testing tool sysbench to generate source data;

[0030] The performance testing tool jmeter is used to process and format the initial data to generate source data.

[0031] In a second aspect, the present application provides a device for interacting with a database, the device comprising:

[0032] A determination module, configured to determine a first SQL statement and a second SQL statement corresponding to the target row data in the table, wherein the first SQL statement and the second SQL statement are used to sequentially perform DML operations on the target row data according to a DML operation sequence, and the first SQL statement is an original statement or a merged statement;

[0033] a merging module, configured to merge the first SQL statement and the second SQL statement into a new SQL statement if the DML operation indicated by the first SQL statement and the DML operation indicated by the second SQL statement meet a preset merging condition;

[0034] The sending module is used to send the SQL statement corresponding to the target row data to the database when the new SQL statement can no longer be merged.

[0035] In a third aspect, the present application provides an electronic device comprising: at least one communication interface; at least one bus connected to the at least one communication interface; at least one processor connected to the at least one bus; and at least one memory connected to the at least one bus.

[0036] In a fourth aspect, the present application further provides a computer storage medium storing computer executable instructions, wherein the computer executable instructions are used to execute the method for interacting with a database as described in any one of the above items of the present application.

[0037] The above technical solution provided by the embodiment of the present application has the following advantages compared with the prior art: if the two SQL statements corresponding to the target row data meet the preset merging condition, the two SQL statements are merged, which is essentially to merge the DML operations on the target row data, so that the originally large number of DML operations are reduced, the SQL statement business volume of the terminal is reduced, and the interaction frequency between the terminal and the database is correspondingly reduced, thereby improving the interaction performance between the terminal and the database. BRIEF DESCRIPTION OF THE DRAWINGS

[0038] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the invention and, together with the description, serve to explain the principles of the invention.

[0039] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, for ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative labor.

[0040] One or more embodiments are exemplarily described by pictures in the corresponding drawings, and these exemplified descriptions do not constitute limitations on the embodiments. Elements with the same reference numerals in the drawings represent similar elements, and unless otherwise stated, the figures in the drawings do not constitute proportional limitations.

[0041] Figure 1 A schematic diagram of the hardware environment of the method for interacting with a database according to an embodiment of the present application;

[0042] Figure 2 A flow chart of a method for interacting with a database according to an embodiment of the present application;

[0043] Figure 3 A schematic diagram showing the comparison of interactive performance before and after data merging provided in an embodiment of the present application;

[0044] Figure 4 A flowchart of interacting with a database according to an embodiment of the present application;

[0045] Figure 5 A schematic diagram of the structure of a device for interacting with a database provided in an embodiment of the present application;

[0046] Figure 6 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0047] In order to make the purpose, technical solution and advantages of the embodiments of the present application clearer, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of this application.

[0048] The disclosure below provides many different embodiments or examples to implement different structures of the present invention. In order to simplify the disclosure of the present invention, the parts and settings of specific examples are described below. Of course, they are only examples, and the purpose is not to limit the present invention. In addition, the present invention can repeat reference numbers and / or letters in different examples. This repetition is for the purpose of simplification and clarity, and does not itself indicate the relationship between the various embodiments and / or settings discussed.

[0049] Optionally, in the embodiment of the present application, the above method for interacting with a database can be applied to: Figure 1 In the hardware environment composed of the terminal 101 and the database 103 shown in FIG. Figure 1 As shown, the database 103 communicates with the terminal 101 through the JDBC API to provide data storage services for the terminal 101. The terminal 101 includes but is not limited to data operation equipment, computers, servers, etc.

[0050] This application is used in the field of optimizing the performance of interaction with the database, especially in batch running, peak business, and high-concurrency business scenarios.

[0051] A method for interacting with a database in an embodiment of the present application can be executed by the terminal 101 to improve the interaction performance with the database. The following will be combined with specific implementation methods to describe the method for interacting with a database in an embodiment of the present application in detail. Figure 2 As shown, the specific steps are as follows:

[0052] Step 201: Determine a first SQL statement and a second SQL statement corresponding to target row data in a table, wherein the first SQL statement and the second SQL statement are used to sequentially perform DML operations on the target row data according to a DML operation sequence, and the first SQL statement is an original statement or a merged statement.

[0053] Step 202: If the DML operation indicated by the first SQL statement and the DML operation indicated by the second SQL statement meet a preset merging condition, the first SQL statement and the second SQL statement are merged into a new SQL statement.

[0054] Step 203: When the new SQL statement can no longer be merged, the SQL statement corresponding to the target row data is sent to the database.

[0055] The present application involves the interaction between two network elements, a terminal and a database. The client relies on a database driver and calls JDBC API (Java DataBase Connectivity java, database connection) to communicate with the database over the network. The database driver is included in the terminal network element in the form of a component.

[0056] After receiving the table data processed by ETL (Extract-Transform-Load), the terminal selects a row of data from the table data as the target row data. The table file corresponding to the target row data records the SQL statement for the DML operation of the row data. The DML operations include INSERT, UPDATE, and DELETE. Each SQL statement is used to record a DML operation. The SQL statements corresponding to two DML operations under certain specific types can be merged.

[0057] Specifically, the terminal obtains the first SQL statement and the second SQL statement corresponding to the target row data respectively, and then determines whether the DML operation indicated by the first SQL statement and the DML operation indicated by the second SQL statement meet the preset merging condition. If the merging condition is met, the first SQL statement and the second SQL statement are merged. In essence, the two DML operations are merged into one DML operation. The interaction between the terminal and the database is the writing, updating or deleting of data. After the merger, the number of DML operations is reduced, and the frequency of interaction between the terminal and the database is also reduced accordingly.

[0058] The first SQL statement and the second SQL statement are merged into a new SQL statement. If the new SQL statement and the next SQL statement also meet the preset merging condition, the SQL statements are continued to be merged until the merged SQL statement and the next SQL statement cannot be merged, or there are no more SQL statements to be merged.

[0059] If the DML operation indicated by the first SQL statement and the DML operation indicated by the second SQL statement do not satisfy the preset merging condition, the first SQL statement, the second SQL statement, and the SQL statement following the second SQL statement are respectively stored in the cache.

[0060] Exemplarily, the target row data corresponds to SQL statements 1 to SQL statement 5. If SQL statement 1 and SQL statement 2 meet the merge condition, SQL statement 1 and SQL statement 2 are merged to obtain SQL statement 1*; if SQL statement 1* and SQL statement 3 meet the merge condition, SQL statement 1* and SQL statement 3 are merged to obtain SQL statement 2*. If SQL statement 2* and SQL statement 4 do not meet the merge condition, statement merging is no longer performed, and interaction with the database is performed based on SQL statement 2*, SQL statement 4, and SQL statement 5.

[0061] The data merging in this application is to perform multiple valid DML operations on the unique row data of a table. Consider merging multiple DML operations into one or several operations at the terminal, and then interact with the database to execute. For example, if the row with primaryKey=1 is updated and deleted at different time points, under normal circumstances, the client needs to interact with the database twice, respectively, to perform the update operation and the delete operation. This method can merge the update and delete operations into a delete operation, and then interact with the database once to execute the delete operation.

[0062] In this application, if the two SQL statements corresponding to the target row data meet the preset merging conditions, the two SQL statements are merged, which is essentially a dml operation merge of the target row data. In this way, the originally large number of dml operations is reduced, the SQL statement business volume of the terminal is reduced, and the frequency of interaction between the terminal and the database is correspondingly reduced, thereby improving the interaction performance between the terminal and the database.

[0063] In addition, SQL statement merging can greatly reduce the terminal memory space usage, reduce the frequency and amount of data interaction with the database, and reduce the computing pressure and load on the database server.

[0064] Figure 3The above curve is the interactive performance before and after data merging under the same business model and the same data volume. The lower curve is the interactive performance after data merging. It can be seen that after data merging, the interactive performance with the database is greatly improved.

[0065] In the embodiments of the present application, the SQL statements are merged into new SQL statements including the following four embodiments.

[0066] Embodiment 1: If the first SQL statement indicates an insert operation and the second SQL statement indicates a delete operation, the first SQL statement and the second SQL statement are merged, and the generated new SQL statement indicates no operation on the target row data.

[0067] Embodiment 2: If the first SQL statement indicates an insert operation and the second SQL statement indicates an update operation, the first SQL statement and the second SQL statement are merged, and the generated new SQL statement indicates the insertion of the updated data.

[0068] Embodiment 3: If the first SQL statement indicates an update operation, and the second SQL statement indicates an update operation, the first SQL statement and the second SQL statement are merged, and the generated new SQL statement indicates that the update target row data is the second update data.

[0069] Embodiment 4: If the first SQL statement indicates an update operation and the second SQL statement indicates a delete operation, the first SQL statement and the second SQL statement are merged, and the generated new SQL statement indicates deleting the target row data.

[0070] Specifically, the SQL statements are merged as shown in Table 1.

[0071]

[0072] Table 1

[0073] INSERT+DELETE: The merge result is no operation, which is equivalent to inserting a piece of data and then deleting it. From the final result, it is equivalent to not performing any operation.

[0074] INSERT+UPDATE: The combined result is INSERT, which is equivalent to inserting a piece of data and then updating it to another new data, which is equivalent to directly inserting another new data.

[0075] UPDATE+UPDATE: The merged result is UPDATE, which is equivalent to performing two update operations on a row of data, and is equivalent to directly updating the new data to the second UPDATE.

[0076] UPDATE+DELETE: The combined result is DELETE, which is equivalent to updating a row of data first and then deleting it, which is equivalent to directly deleting the row of data.

[0077] The types of SQL statements that cannot be merged include: DELETE+INSERT, DELETE+DELETE, DELETE+UPDATE, UPDATE+INSERT, and INSERT+INSERT.

[0078] As an optional implementation, determining the first SQL statement and the second SQL statement corresponding to the target row data in the table includes: judging whether there is a merged SQL statement corresponding to the target row data in the cache, wherein each SQL statement carries a unique identifier of the corresponding row data; if so, using the merged SQL statement as the first SQL statement; if not, using the first SQL statement in the table data file as the first SQL statement; and according to the DML operation sequence, using the next SQL statement after the first SQL statement in the table data file as the second SQL statement.

[0079] In the embodiment of the present application, the SQL statements that have been merged in real time will be saved in the cache. If you want to find the first SQL statement, first search it in the cache. If it cannot be found in the cache, it means that the SQL statement of the target row data has not been merged or the first SQL statement cannot be merged. Then, search for the first SQL statement of the target row data in the table data file where the target row data is located. Therefore, the first SQL statement can be the merged SQL statement or the first SQL statement that has not been merged. Among them, the search SQL statement is determined according to the unique identifier of the row data.

[0080] Each SQL statement corresponds to a DML operation in the DML operation sequence, and the SQL statement also carries the operation sequence number of the corresponding DML operation. After the terminal determines the first SQL statement, it determines the sequence number corresponding to the first SQL statement, and then searches for the second SQL statement carrying the sequence number according to the next sequence number in the DML operation sequence.

[0081] When determining the serial number corresponding to the first SQL statement, if the first SQL statement is a merged SQL, then of the two SQL statements constituting the merge, the second SQL statement must be a non-merged statement, and the serial number of the second SQL statement is used as the serial number corresponding to the merged first SQL statement.

[0082] When the first SQL statement and the second SQL statement are merged, the new SQL statement is also saved in the cache. If the first SQL statement and the second SQL statement cannot be merged, the first SQL statement, the second SQL statement, and the SQL statements after the second SQL statement are all saved in the cache for subsequent sending of the data in the cache to the database.

[0083] Before merging SQL statements, the terminal needs to obtain source data and perform ETL processing on the source data to obtain the SQL statements to be merged.

[0084] Methods for obtaining source data include: consuming the initial data through the consumer thread of Kafka, and generating source data after parsing and formatting the message in the consumer thread; generating source data after processing and formatting the initial data through the performance testing tool Sysbench; generating source data after processing and formatting the initial data through the performance testing tool JMeter.

[0085] The method of ETL processing of source data includes: the ETL module is responsible for data cleaning and conversion. In the cleaning stage, the ETL module generates psSQL to filter and screen the source data according to the pre-configured black and white lists, row or column filtering, library or table or column filtering. In the conversion stage, heterogeneous databases and non-equivalent databases require pre-configured mapping relationships. The ETL module performs mapping and conversion processing on the source data, converting it into the syntax and field type that the database can recognize, and then creates a prepareStatement object, generates pre-compiled SQL with placeholders through the prepareStatement object, and then replaces the placeholders later.

[0086] After the terminal merges the SQL statements, it needs to send the SQL statements corresponding to the target row data to the database. The specific process of sending is: after the SQL statements corresponding to each row of data in the table are merged, the executable SQL in the cache is consumed in batches by concurrent threads, where the executable SQL includes the merged SQL statements and the SQL statements that cannot be merged; load the database driver and create a long connection session; call multiple JDBC-APIs to send the executable SQL in batches to the database.

[0087] JDBC is a Java API for executing SQL statements. It can provide unified access to a variety of relational databases. It consists of a set of classes and interfaces written in Java.

[0088] The database receives batch executable SQL statements sent by the terminal, and completes data storage according to the processes of syntax analysis, semantic analysis, execution plan generation, and SQL execution.

[0089] In this application, the merge occurs before the concurrent execution threads, and the SQL statements involved in the merge are complete. Compared with distributing SQL statements for the same row of data to different threads in concurrent threads, SQL statements in different threads cannot be merged. Early merging can maximize the merging effect.

[0090] The terminal establishes a long connection with the database. Long connection is relative to the usual short connection, that is, to maintain the connection status between the client and the server for a long time. The usual short connection operation steps are: connection--data transmission--close connection; while the long connection is usually: connection--data transmission--maintain connection--data transmission--maintain connection--...--close connection. This requires that the long connection sends data packets regularly when there is no data communication to maintain the connection status, and the short connection is directly closed when there is no data transmission. Long connection can reduce the number of network requests and delays, reduce server load, and improve interactive performance.

[0091] This application provides a flow chart for interacting with a database, such as Figure 4 As shown, the following steps are included:

[0092] 1. Source data generation.

[0093] The client pulls Kafka data to generate source data, which is completed by the client's consumer thread. After the data is consumed, the message is parsed and formatted in the consumer thread and then handed over to the ETL module.

[0094] The source data is generated by the performance testing tool sysbench, and is handed over to the ETL module after processing and formatting.

[0095] The source data is generated by the testing tool jmeter and is handed over to the ETL module after processing and formatting.

[0096] 2.ETL processing.

[0097] Clean and transform the data, remove invalid data, and convert valid data into types and syntax that the database can recognize and execute.

[0098] 3.SQL statement merging.

[0099] SQL statement merging is the key to improving data interaction performance. It supports merging two SQL statements that identify unique rows of data when the merging conditions are met. When the merged SQL statement encounters the next SQL statement, if the merging conditions are still met, it will continue to be merged, and so on.

[0100] 4. Data cache.

[0101] The merged SQL statements and the unmerged SQL statements are saved in the cache, and multi-threaded subscription consumption is performed through the in-table concurrent module.

[0102] The data interaction in this application includes the terminal side and the database side, such as Figure 1 shown.

[0103] The terminal side includes ETL processing, SQL merging and intra-table concurrency modules.

[0104] ETL processing: used to clean and transform data, remove invalid data, and convert valid data into types and syntax that the database can recognize and execute.

[0105] SQL merge: It is used to merge SQL statements for uniquely determined row data, so that multiple operation behaviors are merged into one operation behavior, while ensuring the correct timing of effective DML.

[0106] Intra-table concurrent module: used to cache the merged SQL according to the DML type (INSERT, UPDATE, DELETE). The SQL that cannot be merged is also cached. After the SQL in the table is merged, the concurrent threads consume the cached data in batches and call the JDBC API (batch protocol) to issue and execute the SQL.

[0107] Conventional data consumption, processing, and execution are all completed in one process. In this application, source data consumption, ETL processing, and concurrent execution constitute a three-level client architecture and asynchronous processing model to maximize client performance. The terminal adopts a multi-level architecture + cache queue model to achieve asynchronous data processing and avoid saturation, starvation, and other blocking phenomena in the upstream and downstream.

[0108] Execute DML on the database side: Receive batch executable SQL sent by the client, and complete the data storage according to the defined process such as syntax analysis, semantic analysis, generation of execution plan, and execution of SQL.

[0109] The present application provides a device for interacting with a database, such as Figure 5 As shown, the device comprises:

[0110] A determination module 501 is used to determine a first SQL statement and a second SQL statement corresponding to the target row data in the table, wherein the first SQL statement and the second SQL statement are used to sequentially perform DML operations on the target row data according to the DML operation sequence, and the first SQL statement is an original statement or a merged statement;

[0111] A merging module 502, configured to merge the first SQL statement and the second SQL statement into a new SQL statement if the DML operation indicated by the first SQL statement and the DML operation indicated by the second SQL statement meet a preset merging condition;

[0112] The sending module 503 is used to send the SQL statement corresponding to the target row data to the database when the new SQL statement can no longer be merged.

[0113] Optionally, the merging module 502 is used to:

[0114] If the first SQL statement indicates an insert operation and the second SQL statement indicates a delete operation, the first SQL statement and the second SQL statement are merged, and the generated new SQL statement indicates no operation on the target row data; or,

[0115] If the first SQL statement indicates an insert operation, and the second SQL statement indicates an update operation, the first SQL statement and the second SQL statement are merged, and the generated new SQL statement indicates inserting the updated data; or,

[0116] If the first SQL statement indicates an update operation, and the second SQL statement indicates an update operation, the first SQL statement and the second SQL statement are merged, and the generated new SQL statement indicates that the update target row data is the second update data; or,

[0117] If the first SQL statement indicates an update operation, and the second SQL statement indicates a delete operation, the first SQL statement and the second SQL statement are merged, and the generated new SQL statement indicates deleting the target row data.

[0118] Optionally, the device is also used for:

[0119] If the DML operation indicated by the first SQL statement and the DML operation indicated by the second SQL statement do not satisfy the preset merging condition, the first SQL statement, the second SQL statement, and the SQL statement following the second SQL statement are respectively stored in the cache.

[0120] Optionally, the sending module 503 is used to:

[0121] After the SQL statements corresponding to each row of data in the table are merged, concurrent threads are used to batch consume the executable SQL statements in the cache, where the executable SQL statements include the merged SQL statements and the SQL statements that cannot be merged;

[0122] Load the database driver and create a long connection session;

[0123] Call multiple JDBC-APIs to send executable SQL statements to the database in batches.

[0124] Optionally, the determination module 501 is used to:

[0125] Determine whether there is a merged SQL statement corresponding to the target row data in the cache, where each SQL statement carries a unique identifier of the corresponding row data;

[0126] If it exists, the merged SQL statement is used as the first SQL statement;

[0127] If it does not exist, the first SQL statement in the table data file is used as the first SQL statement;

[0128] According to the DML operation sequence, the next SQL statement after the first SQL statement in the table data file is used as the second SQL statement.

[0129] Optionally, the device is also used for:

[0130] Use the ETL module to filter and screen source data according to pre-configured black and white lists, row filtering, and field masking;

[0131] The ETL module is used to map and transform the filtered source data to obtain the syntax and field types that the database can recognize, and generate precompiled SQL with placeholders.

[0132] Optionally, the device is also used for:

[0133] The initial data is consumed by Kafka's consumer thread, and the source data is generated after message parsing and formatting in the consumer thread;

[0134] The performance testing tool sysbench is used to process and format the initial data to generate source data;

[0135] The performance testing tool jmeter is used to process and format the initial data and generate source data.

[0136] like Figure 6 As shown, an embodiment of the present application provides an electronic device, including a processor 601, a communication interface 602, a memory 603 and a communication bus 604, wherein the processor 601, the communication interface 602, and the memory 603 communicate with each other through the communication bus 604.

[0137] The memory 603 is used to store computer programs.

[0138] In one embodiment of the present application, the processor 601 is used to execute the program stored in the memory 603 to implement the method for interacting with the database provided by any of the above method embodiments, including:

[0139] Determine a first SQL statement and a second SQL statement corresponding to the target row data in the table, wherein the first SQL statement and the second SQL statement are used to sequentially perform DML operations on the target row data according to the DML operation sequence, and the first SQL statement is an original statement or a merged statement;

[0140] If the DML operation indicated by the first SQL statement and the DML operation indicated by the second SQL statement meet the preset merging condition, then merge the first SQL statement and the second SQL statement into a new SQL statement;

[0141] When the new SQL statement can no longer be merged, the SQL statement corresponding to the target row data is sent to the database.

[0142] Optionally, if the DML operation indicated by the first SQL statement and the DML operation indicated by the second SQL statement meet a preset merging condition, merging the first SQL statement and the second SQL statement into a new SQL statement includes:

[0143] If the first SQL statement indicates an insert operation and the second SQL statement indicates a delete operation, the first SQL statement and the second SQL statement are merged, and the generated new SQL statement indicates no operation on the target row data; or,

[0144] If the first SQL statement indicates an insert operation, and the second SQL statement indicates an update operation, the first SQL statement and the second SQL statement are merged, and the generated new SQL statement indicates inserting the updated data; or,

[0145] If the first SQL statement indicates an update operation, and the second SQL statement indicates an update operation, the first SQL statement and the second SQL statement are merged, and the generated new SQL statement indicates that the update target row data is the second update data; or,

[0146] If the first SQL statement indicates an update operation, and the second SQL statement indicates a delete operation, the first SQL statement and the second SQL statement are merged, and the generated new SQL statement indicates deleting the target row data.

[0147] Optionally, after determining the first SQL statement and the second SQL statement corresponding to the target row data in the table, the method further includes:

[0148] If the DML operation indicated by the first SQL statement and the DML operation indicated by the second SQL statement do not satisfy the preset merging condition, the first SQL statement, the second SQL statement, and the SQL statement following the second SQL statement are respectively stored in the cache.

[0149] Optionally, sending the SQL statement corresponding to the target row data to the database includes:

[0150] After the SQL statements corresponding to each row of data in the table are merged, concurrent threads are used to batch consume the executable SQL statements in the cache, where the executable SQL statements include the merged SQL statements and the SQL statements that cannot be merged;

[0151] Load the database driver and create a long connection session;

[0152] Call multiple JDBC-APIs to send executable SQL statements to the database in batches.

[0153] Optionally, determining the first SQL statement and the second SQL statement corresponding to the target row data in the table includes:

[0154] Determine whether there is a merged SQL statement corresponding to the target row data in the cache, where each SQL statement carries a unique identifier of the corresponding row data;

[0155] If it exists, the merged SQL statement is used as the first SQL statement;

[0156] If it does not exist, the first SQL statement in the table data file is used as the first SQL statement;

[0157] According to the DML operation sequence, the next SQL statement after the first SQL statement in the table data file is used as the second SQL statement.

[0158] Optionally, before determining the first SQL statement and the second SQL statement corresponding to the target row data in the table, the method further includes:

[0159] Use the ETL module to filter and screen source data according to pre-configured black and white lists, row filtering, and field masking;

[0160] The ETL module is used to map and transform the filtered source data to obtain the syntax and field types that the database can recognize, and generate precompiled SQL with placeholders.

[0161] Optionally, before using the ETL module to filter and screen the source data, the method further includes:

[0162] The initial data is consumed by Kafka's consumer thread, and the source data is generated after message parsing and formatting in the consumer thread;

[0163] The performance testing tool sysbench is used to process and format the initial data to generate source data;

[0164] The performance testing tool jmeter is used to process and format the initial data and generate source data.

[0165] An embodiment of the present application also provides a computer-readable storage medium on which a computer program is stored. When the computer program is executed by a processor, the steps of the method for interacting with a database as provided in any of the aforementioned method embodiments are implemented.

[0166] The device embodiments described above are merely illustrative, wherein the units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed on multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0167] Through the description of the above implementation methods, those skilled in the art can clearly understand that each implementation method can be implemented by means of software plus a general hardware platform, and of course, by hardware. Based on this understanding, the above technical solution is essentially or the part that contributes to the relevant technology can be embodied in the form of a software product, and the computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, a disk, an optical disk, etc., including a number of instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to execute the methods described in each embodiment or some parts of the embodiments.

[0168] It should be understood that the terms used in the text are only for the purpose of describing specific example embodiments, and are not intended to be limiting. Unless the context clearly indicates otherwise, the singular forms "one", "an" and "said" as used in the text may also be meant to include plural forms. The terms "include", "comprise", "contain", and "have" are inclusive, and therefore specify the existence of stated features, steps, operations, elements and / or parts, but do not exclude the existence or addition of one or more other features, steps, operations, elements, parts, and / or combinations thereof. The method steps, processes, and operations described herein are not interpreted as necessarily requiring them to be performed in the specific order described or illustrated, unless the execution order is clearly indicated. It should also be understood that additional or alternative steps may be used.

[0169] The foregoing is merely a specific embodiment of the present invention, which enables those skilled in the art to understand or implement the present invention. Various modifications to these embodiments will be apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the present invention. Therefore, the present invention will not be limited to the embodiments shown herein, but rather to the widest scope consistent with the principles and novel features claimed herein.

Claims

1. A method for interacting with a database, characterized in that: The method comprises: Determine a first SQL statement and a second SQL statement corresponding to the target row data in the table, wherein the first SQL statement and the second SQL statement are used to sequentially perform DML operations on the target row data according to a DML operation sequence, and the first SQL statement is an original statement or a merged statement; If the DML operation indicated by the first SQL statement and the DML operation indicated by the second SQL statement meet a preset merging condition, merging the first SQL statement and the second SQL statement into a new SQL statement; When the new SQL statement can no longer be merged, the SQL statement corresponding to the target row data is sent to the database.

2. The method according to claim 1, characterized in that If the DML operation indicated by the first SQL statement and the DML operation indicated by the second SQL statement meet a preset merging condition, merging the first SQL statement and the second SQL statement into a new SQL statement includes: If the first SQL statement indicates an insert operation and the second SQL statement indicates a delete operation, the first SQL statement and the second SQL statement are merged, and the generated new SQL statement indicates no operation on the target row data; or, If the first SQL statement indicates an insert operation, and the second SQL statement indicates an update operation, the first SQL statement and the second SQL statement are merged, and the generated new SQL statement indicates inserting the updated data; or, If the first SQL statement indicates an update operation, and the second SQL statement indicates an update operation, the first SQL statement and the second SQL statement are merged, and the generated new SQL statement indicates that the target row data is updated for the second time; or, If the first SQL statement indicates an update operation, and the second SQL statement indicates a delete operation, the first SQL statement and the second SQL statement are merged, and the generated new SQL statement indicates deleting the target row data.

3. The method according to claim 1, characterized in that After determining the first SQL statement and the second SQL statement corresponding to the target row data in the table, the method further includes: If the DML operation indicated by the first SQL statement and the DML operation indicated by the second SQL statement do not satisfy the preset merging condition, the first SQL statement, the second SQL statement, and the SQL statement following the second SQL statement are respectively stored in the cache.

4. The method according to claim 3, characterized in that: Sending the SQL statement corresponding to the target row data to the database includes: After the SQL statements corresponding to each row of data in the table are merged, the executable SQL in the cache is consumed in batches using concurrent threads, wherein the executable SQL includes the merged SQL statements and the SQL statements that cannot be merged; Load the database driver and create a long connection session; Call multiple JDBC-APIs to send the executable SQL statements to the database in batches.

5. The method according to claim 1, characterized in that: Determining the first SQL statement and the second SQL statement corresponding to the target row data in the table includes: Determine whether there is a merged SQL statement corresponding to the target row data in the cache, wherein each SQL statement carries a unique identifier of the corresponding row data; If it exists, use the merged SQL statement as the first SQL statement; If it does not exist, the first SQL statement in the table data file is used as the first SQL statement; According to the DML operation sequence, the next SQL statement after the first SQL statement in the table data file is used as the second SQL statement.

6. The method according to claim 1, characterized in that Before determining the first SQL statement and the second SQL statement corresponding to the target row data in the table, the method further includes: Use the ETL module to filter and screen source data according to pre-configured black and white lists, row filtering, and field masking; The ETL module is used to perform mapping and conversion processing on the filtered source data to obtain the syntax and field type that can be recognized by the database, and generate pre-compiled SQL with placeholders.

7. The method according to claim 1, characterized in that Before using the ETL module to filter and screen the source data, the method further includes: The initial data is consumed by the consumer thread of Kafka, and the source data is generated after the message is parsed and formatted in the consumer thread; The initial data is processed and formatted by the performance testing tool sysbench to generate source data; The performance testing tool jmeter is used to process and format the initial data to generate source data.

8. A device for interacting with a database, characterized in that: The device comprises: A determination module, configured to determine a first SQL statement and a second SQL statement corresponding to the target row data in the table, wherein the first SQL statement and the second SQL statement are used to sequentially perform DML operations on the target row data according to a DML operation sequence, and the first SQL statement is an original statement or a merged statement; a merging module, configured to merge the first SQL statement and the second SQL statement into a new SQL statement if the DML operation indicated by the first SQL statement and the DML operation indicated by the second SQL statement meet a preset merging condition; The sending module is used to send the SQL statement corresponding to the target row data to the database when the new SQL statement can no longer be merged.

9. An electronic device, characterized in that: It includes a processor, a communication interface, a memory and a communication bus, wherein the processor, the communication interface and the memory communicate with each other through the communication bus; Memory, used to store computer programs; A processor, for implementing any of the methods described in claims 1-7 when executing a program stored in a memory.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the method according to any one of claims 1 to 7 is implemented.