CN111221840A

CN111221840A - Data processing method and device, data caching method, storage medium and system

Info

Publication number: CN111221840A
Application number: CN201811413291.1A
Authority: CN
Inventors: 王雄
Original assignee: Alibaba Group Holding Ltd
Current assignee: Alibaba Cloud Computing Ltd
Priority date: 2018-11-23
Filing date: 2018-11-23
Publication date: 2020-06-02
Anticipated expiration: 2038-11-23
Also published as: CN111221840B

Abstract

The application discloses a data processing method and device, a data caching method, a storage medium and a system. Wherein, the method comprises the following steps: receiving an SQL access statement; searching configuration information corresponding to the SQL access statement from a preset cache, wherein the configuration information is extracted from a historical execution plan, and the execution plan is used for indicating a set of logic operations executed in a database; and generating an execution plan corresponding to the SQL access statement according to the configuration information, and executing the execution plan corresponding to the SQL access statement. The method and the device solve the technical problem that the execution efficiency of the SQL statement is low due to the fact that the memory overhead consumed in the process of searching the execution plan corresponding to the SQL statement is large.

Description

Data processing method and device, data caching method, storage medium and system

Technical Field

The present application relates to the field of database caching, and in particular, to a data processing method and apparatus, a data caching method, a storage medium, and a system.

Background

In the prior art, after an SQL statement is input to a MySQL server, the following execution processes are generally performed: syntax parsing (parse), optimization (optimize), generation of execution plan (play) and execution (execute). The main output of this process is the execution plan (plan) of the statement, which consumes a long time when the statement is complex, and when the SQL statement to be executed appears repeatedly in a large amount, the process of generating the execution plan (plan) consumes more time, thereby resulting in inefficient execution of the SQL statement.

If when an SQL statement is executed, the execution plan of the statement is cached, and when a similar statement is executed again, the execution time of the statement can be greatly saved by using the cached execution plan, but when the cached execution plan is searched, the optimizer compares each path, so that an optimal execution plan with the minimum cost is selected, and the process brings great performance overhead and affects the execution performance of the SQL statement.

In view of the above problems, no effective solution has been proposed.

Disclosure of Invention

The embodiment of the application provides a data processing method and device, a data caching method, a storage medium and a system, and at least solves the technical problem that the execution efficiency of an SQL statement is low due to the fact that the memory overhead consumed in the process of searching an execution plan corresponding to the SQL statement is large.

According to an aspect of an embodiment of the present application, there is provided a data processing method, including: receiving an SQL access statement; searching configuration information corresponding to the SQL access statement from a preset cache, wherein the configuration information is extracted from a historical execution plan, and the execution plan is used for indicating a set of logic operations executed in a database; and generating an execution plan corresponding to the SQL access statement according to the configuration information, and executing the execution plan corresponding to the SQL access statement.

According to another aspect of the embodiments of the present application, there is also provided a data caching method, including: partitioning a preset cache to obtain a cache partition; formatting the SQL access statement to obtain a target SQL access statement; obtaining a hash value of a target SQL access statement, and determining an access identifier of a cache partition based on the hash value and the number of the cache partitions in a preset cache; and establishing a mapping relation between the access identifier and the cache partition.

According to another aspect of the embodiments of the present application, there is provided a storage medium, where the storage medium includes a stored program, and the program, when executed, controls an apparatus where the storage medium is located to perform the following steps: receiving an SQL access statement; searching configuration information corresponding to the SQL access statement from a preset cache, wherein the configuration information is extracted from a historical execution plan, and the execution plan is used for indicating a set of logic operations executed in a database; and generating an execution plan corresponding to the SQL access statement according to the configuration information, and executing the execution plan corresponding to the SQL access statement.

According to another aspect of the embodiments of the present application, there is also provided a data processing apparatus, including: the receiving module is used for receiving the SQL access statement; the system comprises a searching module, a database processing module and a processing module, wherein the searching module is used for searching configuration information corresponding to an SQL access statement from a preset cache, wherein the configuration information is extracted from an execution plan of a historical record, and the execution plan is used for indicating a set of logic operations executed in the database; and the execution module is used for generating an execution plan corresponding to the SQL access statement according to the configuration information and executing the execution plan corresponding to the SQL access statement.

According to still another aspect of the embodiments of the present application, there is also provided a system, including: a processor; and a memory coupled to the processor for providing instructions to the processor for processing the following processing steps: receiving an SQL access statement; searching configuration information corresponding to the SQL access statement from a preset cache, wherein the configuration information is extracted from a historical execution plan, and the execution plan is used for indicating a set of logic operations executed in a database; and generating an execution plan corresponding to the SQL access statement according to the configuration information, and executing the execution plan corresponding to the SQL access statement.

In the embodiment of the application, receiving SQL access statements is adopted; searching configuration information corresponding to the SQL access statement from a preset cache, wherein the configuration information is extracted from a historical execution plan which is used for indicating a set of logic operations executed in a database; generating an execution plan corresponding to the SQL access statement according to the configuration information, executing the execution plan corresponding to the SQL access statement, dividing a preset cache into partitions, calculating a hash value of the formatted SQL statement by using a hash algorithm, performing remainder calculation by using the obtained hash value and the number of the cache partitions, distributing the cache partitions for the execution plan of the SQL statement according to a calculation result, and storing the execution plan in the distributed cache partitions. The execution plan of the SQL statement is stored by adopting the method, when similar statements are executed again, the same algorithm is adopted to determine the cache partition where the execution plan is located when the execution plan is searched for the SQL statement, namely, the optimizer can select the final execution plan at one time and check the performance expense of the process of the execution plan corresponding to the SQL statement, so that the technical effect of improving the execution efficiency of the SQL statement is realized, and the technical problem of low execution efficiency of the SQL statement caused by large memory expense consumed by the process of searching the execution plan corresponding to the SQL statement is solved.

Drawings

The accompanying drawings, which are included to provide a further understanding of the application and are incorporated in and constitute a part of this application, illustrate embodiment(s) of the application and together with the description serve to explain the application and not to limit the application. In the drawings:

fig. 1 shows a hardware configuration block diagram of a computer terminal (or mobile device) for implementing a data processing method;

FIG. 2 is a flow chart of a method of data processing according to an embodiment of the present application;

FIG. 3 is a flowchart of a method for searching configuration information corresponding to an SQL access statement according to an embodiment of the present application;

FIG. 4 is a flowchart of a method for determining a cache partition corresponding to an SQL statement according to an embodiment of the present application;

FIG. 5 is a flow chart of a data caching method according to an embodiment of the present application;

fig. 6 is a block diagram of a data processing apparatus according to an embodiment of the present application;

FIG. 7 is a block diagram of a system according to an embodiment of the present application;

fig. 8 is a block diagram of a computer terminal according to an embodiment of the present application.

Detailed Description

In order to make the technical solutions better understood by those skilled in the art, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the drawings in the embodiments of the present application, and it is obvious that the described embodiments are only partial embodiments of the present application, but not all embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present application.

It should be noted that the terms "first," "second," and the like in the description and claims of this application and in the drawings described above are used for distinguishing between similar elements and not necessarily for describing a particular sequential or chronological order. It is to be understood that the data so used is interchangeable under appropriate circumstances such that the embodiments of the application described herein are capable of operation in sequences other than those illustrated or described herein. Furthermore, the terms "comprises," "comprising," and "having," and any variations thereof, are intended to cover a non-exclusive inclusion, such that a process, method, system, article, or apparatus that comprises a list of steps or elements is not necessarily limited to those steps or elements expressly listed, but may include other steps or elements not expressly listed or inherent to such process, method, article, or apparatus.

First, some terms or terms appearing in the description of the embodiments of the present application are applicable to the following explanations:

SQL statement: structured Query Language (SQL), a database Query and programming Language, is used to access data and to Query, update, and manage relational database systems.

MySQL: a relational database management system that maintains data in different tables, rather than placing all of the data in a large warehouse, increases speed and flexibility.

Plan: and executing the plan, wherein the database executor actually executes the collection of the logic operation of the SQL statement.

Example 1

There is also provided, in accordance with an embodiment of the present application, an embodiment of a data processing method, to note that the steps illustrated in the flowchart of the figure may be performed in a computer system, such as a set of computer-executable instructions, and that, although a logical order is illustrated in the flowchart, in some cases, the steps illustrated or described may be performed in an order different than here.

The method provided by the first embodiment of the present application may be executed in a mobile terminal, a computer terminal, or a similar computing device. Fig. 1 shows a hardware configuration block diagram of a computer terminal (or mobile device) for implementing a data processing method. As shown in fig. 1, the computer terminal 10 (or mobile device 10) may include one or more (shown as 102a, 102b, … …, 102 n) processors 102 (the processors 102 may include, but are not limited to, a processing device such as a microprocessor MCU or a programmable logic device FPGA, etc.), a memory 104 for storing data, and a transmission module 106 for communication functions. Besides, the method can also comprise the following steps: a display, an input/output interface (I/O interface), a Universal Serial Bus (USB) port (which may be included as one of the ports of the I/O interface), a network interface, a power source, and/or a camera. It will be understood by those skilled in the art that the structure shown in fig. 1 is only an illustration and is not intended to limit the structure of the electronic device. For example, the computer terminal 10 may also include more or fewer components than shown in FIG. 1, or have a different configuration than shown in FIG. 1.

It should be noted that the one or more processors 102 and/or other data processing circuitry described above may be referred to generally herein as "data processing circuitry". The data processing circuitry may be embodied in whole or in part in software, hardware, firmware, or any combination thereof. Further, the data processing circuit may be a single stand-alone processing module, or incorporated in whole or in part into any of the other elements in the computer terminal 10 (or mobile device). As referred to in the embodiments of the application, the data processing circuit acts as a processor control (e.g. selection of a variable resistance termination path connected to the interface).

The memory 104 may be used to store software programs and modules of application software, such as program instructions/data storage devices corresponding to the data processing method in the embodiment of the present application, and the processor 102 executes various functional applications and data processing by running the software programs and modules stored in the memory 104, that is, implementing the vulnerability detection method of the application program. The memory 104 may include high speed random access memory, and may also include non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some examples, the memory 104 may further include memory located remotely from the processor 102, which may be connected to the computer terminal 10 via a network. Examples of such networks include, but are not limited to, the internet, intranets, local area networks, mobile communication networks, and combinations thereof.

The transmission device 106 is used for receiving or transmitting data via a network. Specific examples of the network described above may include a wireless network provided by a communication provider of the computer terminal 10. In one example, the transmission device 106 includes a Network adapter (NIC) that can be connected to other Network devices through a base station to communicate with the internet. In one example, the transmission device 106 can be a Radio Frequency (RF) module, which is used to communicate with the internet in a wireless manner.

The display may be, for example, a touch screen type Liquid Crystal Display (LCD) that may enable a user to interact with a user interface of the computer terminal 10 (or mobile device).

Under the operating environment, the application provides a data processing method as shown in fig. 2. Fig. 2 is a flowchart of a data processing method according to an embodiment of the present application, as shown in fig. 2, the method includes the following steps:

step S202, SQL access statements are received.

In some optional embodiments of the present application, the at least one SQL access statement is received via a graphical user interface.

Step S204, searching configuration information corresponding to the SQL access statement from a preset cache, wherein the configuration information is extracted from a historical execution plan, and the execution plan is used for indicating a set of logic operations executed in the database.

In some optional embodiments of the present application, step S204 may be implemented by: determining a cache partition corresponding to the SQL statement, wherein the cache partition is obtained after partitioning a preset cache; and reading the configuration information of the execution plan from the cache partition. Fig. 3 is a flowchart of a method for searching configuration information corresponding to an SQL access statement according to an embodiment of the present application, and as shown in fig. 3, the method includes the following steps:

step S302, determining a cache partition corresponding to the SQL statement, where the cache partition is obtained after performing partition processing on a preset cache.

In some optional embodiments of the present application, after the SQL statement is input into the MySQL server, the following steps are generally performed: the method comprises the steps of parsing (parse), optimizing (optimize), generating an execution plan (plan) and executing (execute), wherein the main output of the process is the execution plan (plan) of the statement, when the statement is complex, the process consumes longer time, and when the SQL statement needing to be executed is repeated in a large quantity, the process for generating the execution plan (plan) consumes more time, so that the execution plan of the SQL statement is cached (plan cache) when the SQL statement is executed, when the similar statement is executed again, the cached execution plan can be used, the execution plan generating stage can be directly skipped, and the execution speed of the SQL statement can be improved. And storing the execution plan of the SQL statement on a preset cache.

According to an alternative embodiment of the present application, the following three data structures are provided to manage the default cache:

and the Excute _ place _ cache _ manager manages the whole preset cache and is responsible for providing an interface for the server to call. The Excute _ place _ cache _ manager is a component, and is responsible for initializing the whole preset cache and monitoring global information of the preset cache, for example, how many execution plans are cached in total, and the execution plans stored in the preset cache are input into information such as hit rate selected by SQL statements in the MySQL server.

The Excute _ play _ cache _ partition is used for partitioning a preset cache, in order to reduce the influence of a lock on the performance of increasing, deleting and modifying operations of the whole cache, the preset cache is divided into a plurality of cache partitions, execution plans of different SQL statements are respectively stored in the plurality of cache partitions, and the lock on the cache partition storing the execution plan of the SQL statement is a mechanism for controlling concurrent query and is usually realized by using a mutual exclusion lock of an operating system. Since the Relational Database Service (RDS) execution plan cache currently supports only SELECT query statements, all Data Manipulation statements (DML) may be supported later. That is, only the configuration information of the SQL statement that meets certain conditions can be stored in the preset cache.

According to an alternative embodiment of the present application, the configuration information of the SQL statement that satisfies the following conditions can be cached: selection query 2.Wi th only one table 3.No extension 4.No Union 5.No subquery6.No view or derivative table 7.No _ PLAN _ CACHE hit 8.Not a const place 9.query dose't leaving to function/process 10. plant CACHE art Not full.

Excute _ play _ cache: used to store all the information needed for the SQL statement execution plan.

Fig. 4 is a flowchart of a method for determining a cache partition corresponding to an SQL statement according to an embodiment of the present application, and as shown in fig. 4, the method includes the following steps:

step S402, formatting the SQL access statement to obtain a target SQL access statement.

Step S404, hash calculation is performed on the target SQL access statement to obtain a hash value.

According to an alternative embodiment of the present application, when step S404 is executed, hash calculation is performed on the target SQL statement to obtain a hash value. The hash algorithm is a hash function, which is a method of creating small numbers from an arbitrary file. An input of arbitrary length is converted by a hashing algorithm into an output of fixed length, which is a hash value, and this conversion is a compression mapping.

Step S406, determining a cache partition corresponding to the SQL statement according to the hash value and the number of cache partitions in the preset cache.

In some optional embodiments of the present application, step S406 is implemented by: performing remainder operation on the hash value and the number to obtain a remainder value; and determining a cache partition corresponding to the SQL statement according to the remainder value.

When caching an execution plan in an SQL statement, formatting the SQL statement by an SQL _ digest statement, and replacing a constant in the SQL statement with "? "then output, for example, SQL statement select from t1where a is 1, formatted by SQL _ digest statement, and remove all spaces, output new SQL statement: select from t1where a? And then, calculating a character string of a new SQL statement obtained by processing the SQL _ digest statement by using a hash algorithm to obtain a hash value of the character string. And then, performing remainder calculation on the number of the cache partitions in the preset cache according to the hash value, and determining the position of the cache partition stored in the execution plan of the SQL statement.

After a new SQL statement is input, step S402 is executed, and the SQL statement is formatted by SQL _ digest statement, and the constant in the SQL statement is replaced with "? "then output, for example, SQL statement select from t1where a is 1, formatted by SQL _ digest statement, and remove all spaces, output new SQL statement: select from 1where a? And obtaining a new SQL statement, namely a target SQL access statement. And then, calculating the character string of the target SQL statement by utilizing a Hash algorithm to obtain a Hash value of the target SQL statement. And positioning the position information of one cache partition by utilizing the Hash value of the target SQL statement and the number remainder calculation of the cache partitions in the preset cache, wherein the cache partition is the cache partition corresponding to the newly input SQL statement, and the execution plan stored in the cache partition is the execution plan of the query of the newly input SQL statement.

Step S304, reading the configuration information from the buffer partition.

In some optional embodiments of the present application, step S304 is implemented by: and searching the configuration information corresponding to the hash value from the cache partition.

According to an optional embodiment of the present application, the searching for configuration information corresponding to a hash value from a cache partition includes: when a plurality of identical hash values exist in the cache partition, comparing target statements of the query statements with the identical hash values, wherein the target statements are statements obtained after formatting the query statements; and taking the configuration information corresponding to the target statement identical to the target SQL statement as the configuration information corresponding to the SQL access statement.

After the cache partition corresponding to the SQL statement is determined, the configuration information corresponding to the hash value of the target statement corresponding to the SQL statement is searched from the cache partition. In the searching process, in order to prevent hash computation collision, in the case that multiple hash values are the same, the SQL _ digest statements corresponding to multiple SQL statements are compared (that is, whether target SQL statements corresponding to multiple SQL statements are the same or not is compared), whether multiple target SQL statements with the same hash values are the same or not is further confirmed, if multiple target SQL statements with the same hash values are the same, the multiple target SQL statements are determined to be the same query, and the configuration information corresponding to the multiple same hash values is determined to be the configuration information corresponding to the SQL statements.

According to an alternative embodiment of the present application, the preset cache satisfies the following condition: executing exclusive lock operation on the write operation of the preset cache; and executing the shared lock operation on the read operation of the preset cache.

Exclusive locks, also known as write locks, are those locks that one thread can only acquire at a time, and other threads must wait for the lock to be released before they can acquire the lock. If object T adds an exclusive lock to data object A, object T can read A and also modify A, and other objects can not add any lock to A until T releases the exclusive lock on A. This ensures that something else cannot read and modify a before T releases the exclusive lock on a.

Shared locks, also known as read locks, allow multiple threads to acquire a lock at the same time, and a lock may be owned by multiple threads at the same time. If object T adds a shared lock to data object A, object T can read A but cannot modify A, and other objects can only add any shared lock to A but cannot add an exclusive lock until T releases the shared lock on A. This ensures that something else can read A, but that T cannot make any modifications to A until it releases the shared lock on A.

In some optional embodiments of the present application, step S204 may also be implemented by: determining the access type of the SQL statement; searching configuration information corresponding to the access type from a preset cache, wherein when the access type is index scanning, the configuration information comprises: indexing information; when the access type is partition scanning, the configuration information includes: index information used by partition scanning and partition information; the configuration information when the access type is full data table scan includes: the type of access.

In some alternative embodiments of the present application, the Relational Database (RDS) execution plan cache may support only SELECT query statements at present, and may support all Data Manipulation statements (DML) at a later time. That is, only the execution plan of the SQL statement that meets certain conditions can be stored in the preset cache. The execution plan of the SQL statement that satisfies the following conditions can be cached: selection Query 2.With only one table 3. NoExplant 4.No Union 5.No subquery6.No view or derivative table 7.No _ PLAN _ CACHE hit 8.Not a const place 9.Query dose't leaving to function/process 10. plant CACHE Not full. Therefore, before searching the execution plan configuration information corresponding to the SQL access statement from the preset cache, the type of the SQL statement needs to be determined first to determine whether the SQL statement to be queried meets the condition that the execution condition configuration information can be cached, so as to determine whether the preset cache stores the configuration information that the SQL statement needs to be queried.

After determining the access type of the SQL statement, searching configuration information corresponding to the access type from a preset cache, wherein if the access type is index (index) scanning, the configuration information is index information, specifically information such as the index and the index length; if the access type is partition (range) scanning, the configuration information comprises index information used by the partition scanning and information of each partition; the access type if a full table scan, the configuration information only includes the access type.

According to an alternative embodiment of the application, the SQL access statement has a plurality of data tables; the configuration information includes: the access type of each data table; the connection sequence between the data tables; the type of connection between data tables; the data table links the algorithms used.

When the SQL access statement accesses a plurality of data tables, the configuration information comprises the access type of each data table, the connection sequence among the data tables and the connection type among the data tables, wherein the connection type comprises INNERJOIN, OUTER JOIN and SEMI JOIN; the data table connects the algorithms used, currently supporting NestLoop, Block Nest-Loop (BNL), patched key access (BKA).

Step S206, generating an execution plan corresponding to the SQL access statement according to the configuration information, and executing the execution plan corresponding to the SQL access statement. The SQL statement is executed according to the selected execution plan.

In the prior art, the execution plan of the SQL statement is cached, and when a similar statement is executed again, the cached execution plan can be used, so that the generation step of the execution plan of the SQL statement is skipped, and the execution speed of the statement is increased. However, when selecting the cached execution plan, for the case where the single table has multiple indexes, the optimizer compares each path when selecting the execution plan, so as to select a least expensive execution plan as the most efficient execution plan, which may bring a large performance overhead.

Through the steps, the preset cache partition is divided, the hash value of the formatted SQL statement is calculated by using a hash algorithm, the obtained hash value and the number of the cache partitions are used for carrying out remainder calculation, the cache partitions are distributed for the execution plan of the SQL statement according to the calculation result, and the execution plan is stored in the distributed cache partitions. By the method, the execution cache of the SQL statement is stored, when similar statements are executed again, and the execution plan is searched for the SQL statement, the same algorithm is adopted to determine the cache partition where the execution plan is located, so that the optimizer can select the final execution plan at one time, the performance expense is reduced, and the execution performance of the SQL statement is further improved.

Example 2

Fig. 5 is a flowchart of a data caching method according to an embodiment of the present application, and as shown in fig. 5, the method includes the following steps:

step S502, the preset cache is partitioned to obtain cache partitions.

According to an optional embodiment of the present application, partitioning the preset cache is performed by the component exit _ place _ cache _ partition, so that the partitioning of the preset cache is performed to reduce the influence of a lock on performance caused by add/delete modify operations on the whole preset cache, the preset cache is divided into a plurality of cache partitions, execution plans of different SQL statements are stored in the plurality of cache partitions, the lock is performed on the cache partition storing the execution plans of the SQL statements, and the lock is a mechanism for controlling concurrent queries, and is usually implemented by using a mutex lock of an operating system.

Step S504, the SQL access statement is formatted to obtain a target SQL access statement.

In some optional embodiments of the present application, when caching an execution plan in an SQL statement, the SQL statement is formatted by an SQL _ digest statement, and a constant in the SQL statement is replaced with "? "then output, for example, the SQL statement select from t1where a is 1 is formatted by SQL _ digest statement, and all spaces are removed, and a new SQL statement is output: select from t1where a? And outputting the new SQL statement as the target SQL statement.

Step S506, obtaining a hash value of the target SQL access statement, and determining an access identifier of a cache partition based on the hash value and the number of the cache partitions in a preset cache; and establishing a mapping relation between the access identifier and the cache partition.

According to an alternative embodiment of the present application, step S506 may be implemented by: and performing remainder operation on the hash value and the number to obtain a remainder value, and taking the remainder value as an access identifier.

And then, calculating a character string of a new SQL statement obtained by processing the SQL _ digest statement by using a hash algorithm to obtain a hash value of the character string. And then, performing remainder calculation on the number of the cache partitions in the preset cache according to the hash value obtained by calculation to obtain a remainder value as an access identifier, and establishing a mapping relation between the access identifier and the cache partitions, wherein the access identifier is used for representing the position information of the cache partitions.

The configuration information corresponding to the SQL statement is cached through the steps, compared with the prior art, the method includes the steps that a preset cache partition is adopted, the hash value of the formatted SQL statement is calculated through a hash algorithm, remainder calculation is conducted through the obtained hash value and the number of the cache partitions, the cache partition is distributed for the configuration information of the SQL statement according to the calculation result, and the configuration information is stored in the distributed cache partition. The method stores the configuration information corresponding to the SQL statement, and when similar statements are executed again and the configuration information is searched for the SQL statement, the same algorithm is adopted to determine the cache partition where the configuration information is located, so that the optimizer can select the final execution plan at one time, the performance cost is reduced on the basis of the prior art, and the execution performance of the SQL statement is further improved.

It should be noted that, for simplicity of description, the above-mentioned method embodiments are described as a series of acts or combination of acts, but those skilled in the art will recognize that the present application is not limited by the order of acts described, as some steps may occur in other orders or concurrently depending on the application. Further, those skilled in the art should also appreciate that the embodiments described in the specification are preferred embodiments and that the acts and modules referred to are not necessarily required in this application.

Through the above description of the embodiments, those skilled in the art can clearly understand that the method according to the above embodiments can be implemented by software plus a necessary general hardware platform, and certainly can also be implemented by hardware, but the former is a better implementation mode in many cases. Based on such understanding, the technical solutions of the present application may be embodied in the form of a software product, which is stored in a storage medium (e.g., ROM/RAM, magnetic disk, optical disk) and includes instructions for enabling a terminal device (e.g., a mobile phone, a computer, a server, or a network device) to execute the method of the embodiments of the present application.

Example 3

According to an embodiment of the present application, there is also provided a data processing apparatus for implementing the data processing method, and fig. 6 is a structural diagram of a data processing apparatus according to an embodiment of the present application, as shown in fig. 6, the apparatus includes:

and the receiving module 60 is configured to receive the SQL access statement.

And the searching module 62 is configured to search the configuration information corresponding to the SQL access statement from the preset cache, where the configuration information is extracted from a historical execution plan, and the execution plan is used to indicate a set of logical operations executed in the database.

And the execution module 64 is configured to generate an execution plan corresponding to the SQL access statement according to the configuration information, and execute the execution plan corresponding to the SQL access statement.

It should be noted here that the receiving module 60, the searching module 62 and the executing module 64 correspond to steps S202 to S206 in embodiment 1, and the three modules are the same as the corresponding steps in the implementation example and application scenario, but are not limited to the disclosure in embodiment 1. It should be noted that the above modules may be operated in the computer terminal 10 provided in embodiment 1 as a part of the apparatus.

In addition, it should be noted that, for alternative or preferred embodiments of the present embodiment, reference may be made to the relevant description in embodiment 1, and details are not described herein again.

Example 4

According to an embodiment of the present application, there is further provided an embodiment of a system, which may execute any one of the optional or preferred data processing methods in embodiment 1, and fig. 7 is a structural diagram of a system according to an embodiment of the present application, and as shown in fig. 7, the system includes:

a processor 70; and a memory 72, coupled to the processor 70, for providing instructions to the processor 70 to process the following process steps: receiving an SQL access statement; searching configuration information corresponding to the SQL access statement from a preset cache, wherein the configuration information is extracted from a historical execution plan, and the execution plan is used for indicating a set of logic operations executed in a database; and generating an execution plan corresponding to the SQL access statement according to the configuration information, and executing the execution plan corresponding to the SQL access statement.

Example 5

The embodiment of the application can provide a computer terminal, and the computer terminal can be any one computer terminal device in a computer terminal group. Optionally, in this embodiment, the computer terminal may also be replaced with a terminal device such as a mobile terminal.

Optionally, in this embodiment, the computer terminal may be located in at least one network device of a plurality of network devices of a computer network.

In this embodiment, the computer terminal may execute the program code of the following steps in the vulnerability detection method of the application program: receiving an SQL access statement; searching configuration information corresponding to the SQL access statement from a preset cache, wherein the configuration information is extracted from a historical execution plan, and the execution plan is used for indicating a set of logic operations executed in a database; and generating an execution plan corresponding to the SQL access statement according to the configuration information, and executing the execution plan corresponding to the SQL access statement.

Optionally, fig. 8 is a block diagram of a computer terminal according to an embodiment of the present application. As shown in fig. 8, the computer terminal a may include: one or more processors 80 (only one of which is shown), a memory 82, and a peripheral interface 84.

The memory 82 may be used to store software programs and modules, such as program instructions/modules corresponding to the security vulnerability detection method and apparatus in the embodiment of the present application, and the processor 80 executes various functional applications and data processing by running the software programs and modules stored in the memory 82, that is, implements the above-mentioned detection method for system vulnerability attacks. The memory 82 may include high speed random access memory and may also include non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some examples, the memory 82 may further include memory 82 located remotely from the processor 80, and these remote memories 82 may be connected to terminal a through a network. Examples of such networks include, but are not limited to, the internet, intranets, local area networks, mobile communication networks, and combinations thereof.

The processor 80 may invoke the memory-stored information and the application program through the transmission means to perform the following steps: receiving an SQL access statement; searching configuration information corresponding to the SQL access statement from a preset cache, wherein the configuration information is extracted from a historical execution plan, and the execution plan is used for indicating a set of logic operations executed in a database; and generating an execution plan corresponding to the SQL access statement according to the configuration information, and executing the execution plan corresponding to the SQL access statement.

Optionally, the processor may further execute the program code of the following steps: determining a cache partition corresponding to the SQL statement, wherein the cache partition is obtained after partitioning a preset cache; configuration information is read from the cache partition.

Optionally, the processor may further execute the program code of the following steps: formatting the SQL access statement to obtain a target SQL access statement; performing hash calculation on the target SQL access statement to obtain a hash value; and determining the cache partitions corresponding to the SQL statements according to the hash values and the number of the cache partitions in the preset cache.

Optionally, the processor may further execute the program code of the following steps: performing remainder operation on the hash value and the number to obtain a remainder value; and determining a cache partition corresponding to the SQL statement according to the remainder value.

Optionally, the processor may further execute the program code of the following steps: and searching the configuration information corresponding to the hash value from the cache partition.

Optionally, the processor may further execute the program code of the following steps: when a plurality of identical hash values exist in the cache partition, comparing target statements of the query statements with the identical hash values, wherein the target statements are statements obtained after formatting the query statements; and taking the configuration information corresponding to the target statement identical to the target SQL statement as the configuration information corresponding to the SQL access statement.

Optionally, the processor may further execute the program code of the following steps: determining the access type of the SQL statement; searching configuration information corresponding to the access type from a preset cache, wherein when the access type is index scanning, the configuration information comprises: indexing information; when the access type is partition scanning, the configuration information includes: index information used by partition scanning and partition information; when the access type is full data table scan, the configuration information includes: the type of access.

By adopting the embodiment of the application, a data processing scheme is provided. The method comprises the steps of partitioning a preset cache, calculating the hash value of a formatted SQL statement by using a hash algorithm, performing remainder calculation by using the obtained hash value and the number of the cache partitions, allocating the cache partitions for the execution plan of the SQL statement according to the calculation result, and storing the execution plan in the allocated cache partitions, so that the purpose that when similar SQL statements are executed again and the execution plan is searched for the SQL statement, the cache partitions where the execution plan is located are determined by using the same algorithm can be achieved, the final execution plan can be selected by an optimizer at one time, and the technical problem that the execution efficiency of the SQL statement is low due to the fact that the process of searching the execution plan corresponding to the SQL statement consumes large memory overhead is solved.

Those skilled in the art will appreciate that all or part of the steps in the methods of the above embodiments may be implemented by a program instructing hardware associated with the terminal device, where the program may be stored in a computer-readable storage medium, and the storage medium may include: flash disks, Read-Only memories (ROMs), Random Access Memories (RAMs), magnetic or optical disks, and the like.

Example 6

Embodiments of the present application also provide a storage medium. Alternatively, in this embodiment, the storage medium may be configured to store the program code executed by the data processing method provided in embodiment 1.

Optionally, in this embodiment, the storage medium may be located in any one of computer terminals in a computer terminal group in a computer network, or in any one of mobile terminals in a mobile terminal group.

Optionally, in this embodiment, the storage medium is configured to store program code for performing the following steps: receiving an SQL access statement; searching configuration information corresponding to the SQL access statement from a preset cache, wherein the configuration information is extracted from a historical execution plan, and the execution plan is used for indicating a set of logic operations executed in a database; and generating an execution plan corresponding to the SQL access statement according to the configuration information, and executing the execution plan corresponding to the SQL access statement.

Optionally, in this embodiment, the storage medium is configured to store program code for performing the following steps: determining a cache partition corresponding to the SQL statement, wherein the cache partition is obtained after partitioning a preset cache; configuration information is read from the cache partition.

Optionally, in this embodiment, the storage medium is configured to store program code for performing the following steps: formatting the SQL access statement to obtain a target SQL access statement; performing hash calculation on the target SQL access statement to obtain a hash value; and determining the cache partitions corresponding to the SQL statements according to the hash values and the number of the cache partitions in the preset cache.

Optionally, in this embodiment, the storage medium is configured to store program code for performing the following steps: performing remainder operation on the hash value and the number to obtain a remainder value; and determining a cache partition corresponding to the SQL statement according to the remainder value.

Optionally, in this embodiment, the storage medium is configured to store program code for performing the following steps: and searching the configuration information corresponding to the hash value from the cache partition.

Optionally, in this embodiment, the storage medium is configured to store program code for performing the following steps: when a plurality of identical hash values exist in the cache partition, comparing target statements of the query statements with the identical hash values, wherein the target statements are statements obtained after formatting the query statements; and taking the configuration information corresponding to the target statement identical to the target SQL statement as the configuration information corresponding to the SQL access statement.

Optionally, in this embodiment, the storage medium is configured to store program code for performing the following steps: determining the access type of the SQL statement; searching configuration information corresponding to the access type from a preset cache, wherein when the access type is index scanning, the configuration information comprises: indexing information; when the access type is partition scanning, the configuration information includes: index information used by partition scanning and partition information; when the access type is full data table scan, the configuration information includes: the type of access.

The above-mentioned serial numbers of the embodiments of the present application are merely for description and do not represent the merits of the embodiments.

In the above embodiments of the present application, the descriptions of the respective embodiments have respective emphasis, and for parts that are not described in detail in a certain embodiment, reference may be made to related descriptions of other embodiments.

In the embodiments provided in the present application, it should be understood that the disclosed technology can be implemented in other ways. The above-described embodiments of the apparatus are merely illustrative, and for example, the division of the units is only one type of division of logical functions, and there may be other divisions when actually implemented, for example, a plurality of units or components may be combined or may be integrated into another system, or some features may be omitted, or not executed. In addition, the shown or discussed mutual coupling or direct coupling or communication connection may be an indirect coupling or communication connection through some interfaces, units or modules, and may be in an electrical or other form.

The units described as separate parts may or may not be physically separate, and parts displayed as units may or may not be physical units, may be located in one place, or may be distributed on a plurality of network units. Some or all of the units can be selected according to actual needs to achieve the purpose of the solution of the embodiment.

In addition, functional units in the embodiments of the present application may be integrated into one processing unit, or each unit may exist alone physically, or two or more units are integrated into one unit. The integrated unit can be realized in a form of hardware, and can also be realized in a form of a software functional unit.

The integrated unit, if implemented in the form of a software functional unit and sold or used as a stand-alone product, may be stored in a computer readable storage medium. Based on such understanding, the technical solution of the present application may be substantially implemented or contributed to by the prior art, or all or part of the technical solution may be embodied in a software product, which is stored in a storage medium and includes instructions for causing a computer device (which may be a personal computer, a server, or a network device) to execute all or part of the steps of the method according to the embodiments of the present application. And the aforementioned storage medium includes: a U-disk, a Read-Only Memory (ROM), a Random Access Memory (RAM), a removable hard disk, a magnetic or optical disk, and other various media capable of storing program codes.

The foregoing is only a preferred embodiment of the present application and it should be noted that those skilled in the art can make several improvements and modifications without departing from the principle of the present application, and these improvements and modifications should also be considered as the protection scope of the present application.

Claims

1. A data processing method, comprising:

receiving an SQL access statement;

searching configuration information corresponding to the SQL access statement from a preset cache, wherein the configuration information is extracted from a historical execution plan which is used for indicating a set of logic operations executed in a database;

and generating an execution plan corresponding to the SQL access statement according to the configuration information, and executing the execution plan corresponding to the SQL access statement.

2. The method of claim 1, wherein searching for configuration information corresponding to the SQL access statement from a preset cache comprises:

determining a cache partition corresponding to the SQL statement, wherein the cache partition is obtained after partitioning the preset cache;

and reading the configuration information from the cache partition.

3. The method of claim 2, wherein determining the cache partition corresponding to the SQL statement comprises:

formatting the SQL access statement to obtain a target SQL access statement;

performing hash calculation on the target SQL access statement to obtain a hash value;

and determining the cache partitions corresponding to the SQL statements according to the hash values and the number of the cache partitions in the preset cache.

4. The method according to claim 3, wherein determining the cache partition corresponding to the SQL statement according to the hash value and the number of cache partitions in the preset cache comprises:

performing remainder operation on the hash value and the number to obtain a remainder value;

and determining a cache partition corresponding to the SQL statement according to the remainder value.

5. The method of claim 3, wherein reading the configuration information from the cache partition comprises:

and searching the configuration information corresponding to the hash value from the cache partition.

6. The method of claim 5, wherein looking up configuration information corresponding to the hash value from the cache partition comprises:

when a plurality of identical hash values exist in the cache partition, comparing target statements of query statements with the identical hash values, wherein the target statements are statements obtained after formatting the query statements; and taking the configuration information corresponding to the target statement which is the same as the target SQL statement as the configuration information corresponding to the SQL access statement.

7. The method of claim 2, wherein the predetermined buffer satisfies the following condition: executing exclusive lock operation on the write operation of the preset cache; and executing a shared lock operation on the read operation of the preset cache.

8. The method of claim 1, wherein searching for configuration information corresponding to the SQL access statement from a preset cache comprises:

determining the access type of the SQL statement;

searching the configuration information corresponding to the access type from the preset cache, wherein when the access type is index scanning, the configuration information comprises: indexing information; when the access type is partition scanning, the configuration information includes: index information used by partition scanning and partition information; when the access type is full data table scan, the configuration information includes: the type of access.

9. The method according to any one of claims 1 to 8, wherein the SQL access statement accesses a plurality of data tables; the configuration information includes: the access type of each data table; the connection sequence between the data tables; the type of connection between data tables; the data table links the algorithms used.

10. A method for caching data, comprising:

partitioning a preset cache to obtain a cache partition;

formatting the SQL access statement to obtain a target SQL access statement;

obtaining a hash value of the target SQL access statement, and determining an access identifier of the cache partition based on the hash value and the number of the cache partitions in the preset cache; and establishing a mapping relation between the access identifier and the cache partition.

11. The method of claim 10, wherein determining the access identifier of the cache partition based on the hash value and the number of cache partitions in the predetermined cache comprises:

and performing remainder operation on the hash value and the number to obtain a remainder value, and taking the remainder value as the access identifier.

12. A storage medium, characterized in that the storage medium comprises a stored program, wherein when the program runs, a device in which the storage medium is located is controlled to execute the data processing method according to any one of claims 1 to 9.

13. A data processing apparatus, comprising:

the receiving module is used for receiving the SQL access statement;

the searching module is used for searching configuration information corresponding to the SQL access statement from a preset cache, wherein the configuration information is extracted from a historical execution plan, and the execution plan is used for indicating a set of logic operations executed in a database;

and the execution module is used for generating an execution plan corresponding to the SQL access statement according to the configuration information and executing the execution plan corresponding to the SQL access statement.

14. A system, comprising:

a processor; and

a memory coupled to the processor for providing instructions to the processor for processing the following processing steps:

receiving an SQL access statement;