CN114168634A

CN114168634A - Paging parallel query method and system under heterogeneous platform

Info

Publication number: CN114168634A
Application number: CN202111501754.1A
Authority: CN
Inventors: 李屹秦; 李承文
Original assignee: China Construction Bank Corp
Current assignee: China Construction Bank Corp
Priority date: 2021-12-09
Filing date: 2021-12-09
Publication date: 2022-03-11

Abstract

The invention relates to the technical field of cross-library query, in particular to a paging parallel query method and system under a heterogeneous platform. The method comprises the following steps: performing lexical analysis on the SQL query statement to generate a sequencing object on a programming language level; analyzing the parameter object required by SQL query binding; traversing the task list, modifying paging information and establishing a query task, wherein the query task comprises judging the relation between a database platform to which an application AP server currently receiving a request belongs and a database platform corresponding to the task; and establishing a globally uniform thread pool at an application level, importing the query task into the thread pool to complete scheduling, and merging the sequencing results. The technical scheme builds a complete parallel query solution of supporting paging of the heterogeneous platform, and is convenient for high-performance completion of cross-platform parallel query on the premise that computer resources are reasonably controlled.

Description

Paging parallel query method and system under heterogeneous platform

Technical Field

The invention relates to the technical field of cross-library query, in particular to a paging parallel query method and system under a heterogeneous platform.

Background

Currently, the ARM architecture with proprietary intellectual property continues to heat up. In consideration of the stability of the ARM architecture, in order to reduce the risk of the system, methods such as gray scale release, white list, hybrid deployment and the like are widely applied. In addition to the problems of self query performance and high system availability caused by data scale, the hybrid heterogeneous system also has an irrevocable problem of complex query performance of data dispersed under different system architectures.

In the prior art, for query of a big data magnitude of a relational database, a method of database and table division is generally adopted, and according to a sharing key, a database and table number where a record is located is firstly confirmed, and then query is performed in a designated table. For the case that a sharing key cannot be confirmed or multiple sharing keys are involved in the query, there are two main common parallel query solutions:

one is to entrust the inquiry to the database middleware, analyze SQL with the help of the middleware intelligence, analyze that those SQL can be issued directly, those SQL need optimize and transform, optimize into what kind, and route to what instance node to carry out, give full play to the whole ability of the database instance, reduce the data transmission quantity between the networks, carry on the aggregate calculation to a small amount of results after different instances are processed finally and return to the application caller.

The other is that the application directly analyzes the databases, tables involved in the query, and establishes multiple connections to different databases in parallel, multiple data tables on each connection, or multiple queries or connection queries. And adopting a paging method to asynchronously obtain all results and then return the results after processing.

The above two parallel query solutions have their own limitations. On one hand, whether the database middleware or the direct query access is adopted, the system structure of a plurality of database tables needs to be completely consistent for isomorphic databases; on the other hand, the two parallel query modes concentrate query pressure on single point completion, which easily causes load balancing problem and is not beneficial to high availability of the system.

Disclosure of Invention

In order to overcome the defects of the prior art, the invention provides a paging parallel query method and system under a heterogeneous platform, which can efficiently solve the problem of complex query of data dispersed under different platform systems.

In order to achieve the above purpose, the technical scheme adopted by the invention comprises the following steps:

a paging parallel query method under a heterogeneous platform is characterized by comprising the following steps:

step S1, carrying out lexical analysis on the SQL query statement to generate a sequencing object on the programming language level;

step S2, analyzing the parameter object needed by SQL query binding;

step S3, traversing the task list, modifying the paging information and establishing a query task, including judging the relation between the database platform to which the application AP server currently receiving the request belongs and the database platform corresponding to the task;

and step S4, establishing a globally uniform thread pool at the application level, importing the query task into the thread pool to complete scheduling, and merging the sequencing results.

Further, the step S2 includes parsing the parameter object, and determining the database platform, the database number, and the database table number.

Further, the step S2 further includes:

when the SQL query relates to a plurality of segmentation keys, traversing all the segmentation keys, calculating a database platform corresponding to each segmentation key, a database number and a database table number under the database platform, removing the duplication of the database platform, the database number and the database table number, storing the database platform, the database number and the database table number as a task list, and generating a sub-query task;

and when the segmentation key related to the SQL query cannot be confirmed, integrating the data of the full database platform, the database number and storing the data as a task list to generate a sub-query task.

Further, in step S3, the modifying paging information creating query task includes modifying the paging information of n rows starting from the first start row to start + n rows starting from the 0 th row.

Further, in step S3, the determining the relationship between the platform to which the application AP server currently receiving the request belongs and the platform to which the task belongs includes:

if the database platform to which the application AP server currently receiving the request belongs is consistent with the database platform corresponding to the task, and the application AP server number is consistent with the task database number, generating a local query task, and accessing a query result through SQL;

if the database platform to which the application AP server currently receiving the request belongs is consistent with the database platform corresponding to the task, but the application AP server number is inconsistent with the task database number, generating an RPC internal call query task of the database platform, and calling other AP servers in the database platform to obtain a query result;

and if the database platform to which the application AP server currently receiving the request belongs is not consistent with the database platform corresponding to the task, generating a cross-platform RPC query task, and acquiring a query result in the heterogeneous database platform.

Further, the generating of the cross-platform RPC query task and the obtaining of the query result in the heterogeneous database platform include connecting an application AP server under the heterogeneous database platform and performing cross-database platform conversion on the SQL access query.

Further, the step S4 further includes:

putting the sub-query results of any thread into a queue;

when the length of the queue is more than or equal to 2, randomly taking 2 sub-query results from the queue to perform sorting merging through the sorting object, and re-pressing the intermediate result into the merging result queue;

and sequentially and circularly taking any 2 sub-query results from the queue to perform sequencing merging through the sequencing object, and pressing the merging result queue until only one result is left in the queue, namely the paging query result.

The invention also relates to a paging parallel query system under the heterogeneous platform, which is characterized by comprising the following steps:

the sequencing module is used for carrying out lexical analysis on the SQL query statement and generating a sequencing object on the programming language level;

the analysis module is used for analyzing the parameter object required by SQL query binding;

the modification paging module is used for traversing the task list, modifying paging information and establishing a query task, and judging the relation between a database platform to which the application AP server currently receiving the request belongs and a database platform corresponding to the task;

and the result generation module is used for establishing a globally uniform thread pool at the application level, importing the query task into the thread pool to complete scheduling, and combining the sequencing results.

The invention also relates to a computer-readable storage medium, characterized in that the storage medium has stored thereon a computer program which, when being executed by a processor, carries out the above-mentioned method.

The invention also relates to an electronic device, characterized in that it comprises a processor and a memory;

the memory is used for storing operation instructions;

the processor is used for executing the method by calling the operation instruction.

The invention also relates to a computer program product comprising a computer program and/or instructions, characterized in that the computer program and/or instructions, when executed by a processor, implement the steps of the method as described above.

The invention has the beneficial effects that:

by adopting the paging parallel query method and the paging parallel query system under the heterogeneous platform, the technical scheme integrates multiple technical means such as local query, RPC internal call, SQL grammar mapping conversion, paging information segmentation processing, intermediate result discarding and merging and the like, a complete parallel query solution of the heterogeneous platform supporting paging is built, and cross-platform parallel query can be conveniently finished at high performance on the premise that computer resources are reasonably controlled.

Drawings

FIG. 1 is a flowchart illustrating a paging parallel query method under a heterogeneous platform according to the present invention.

FIG. 2 is a schematic structural diagram of a paging parallel query system under a heterogeneous platform according to the present invention.

Detailed Description

In the invention, the heterogeneous platform refers to a computing unit with different instruction sets and architectures, and the heterogeneous platform mainly refers to a software and hardware platform mixed deployment and cooperative work of different instruction set architectures, such as ARM and X86, which together form an application system for providing services to the outside.

Database and table division: the relational database itself is easy to become a system bottleneck, and the single machine has limited storage capacity, connection number and processing capacity. When the data size of the single table reaches 1000W or 100G, due to the fact that the query dimension is large, even if a slave library and an optimized index are added, the performance is still seriously reduced when a lot of operations are performed. The core content of the database and the table is data segmentation. The data segmentation is to dispersedly store data in a plurality of data tables of a plurality of databases, so that the data volume in a single database is reduced, and the performance problem of the single database is relieved by expanding the number of hosts, thereby achieving the purpose of improving the operation performance of the database.

Key segmentation: when the database is divided into the base and the table, one or more fields in the database table record are selected, a specific value is obtained through Hash operation, and the base number and the table number of the record are determined based on the value calculation.

And (3) parallel query: the method is to forcibly extract the idle resources of the database server and perform paging processing on some high-load large-data-volume data. Allowing one SQL query statement to be divided into multiple smaller queries, with each partial query running concurrently, and then combining the results of the various parts to provide the final result, mostly for full table scanning, large table scanning and joining, creating large indexes, partition index scanning, large batch insert updates and deletions.

RPC: the RPC, Remote Procedure Call Protocol, is a Protocol that requests services from Remote computer programs over a network without knowledge of the underlying network technology. Belongs to the application layer protocol, but compared with the HTTP/HTTPS protocol, the RPC protocol has more bias and performance between the function and the performance, namely, the RPC framework is a high-performance network communication framework.

For a clearer understanding of the contents of the present invention, reference will be made to the accompanying drawings and examples.

The first aspect of the present invention relates to a paging parallel query method under a heterogeneous platform, which has a step flow shown in fig. 1, and includes:

and step S1, performing lexical analysis on the SQL query statement to generate a sequencing object at the programming language level.

Illustratively, the query statement is select from A where xx order by field1 desc, field2 asc; when the sorting object is realized, the sorting object is obtained by analyzing the sorting part

field1 desc

field2 asc；

Illustratively, for the o1, o2 objects in the ordering interface, for example, java

public int compare(Object o1,Object o2)

Traversing the analyzed content, and executing the following operations for each row:

a. calculating attribute values v1 and v2 of the current field of o1 and o2 objects through reflection;

b. if any one of v1 and v2 is empty or the type does not match, continuing to traverse the next attribute;

c. if v1 is equal to v2, continuing to traverse the next attribute;

d. when the mark is asc, returning to v1-v 2;

e. when the flag is desc, v2-v1 is returned.

And step S2, analyzing the parameter object required by the SQL query binding.

Optionally, the step S2 includes parsing the parameter object, and determining the database platform, the database number, and the database table number.

Optionally, when the SQL query relates to a plurality of segmentation keys, traversing all the segmentation keys, calculating a database platform corresponding to each segmentation key, a database number and a database table number under the database platform, removing duplicates of the database platform, the database number and the database table number, storing the duplicates as a task list, and generating a sub-query task;

Illustratively, if the query includes the client list, all the client lists are traversed, the request is first routed to the application AP server corresponding to any client in the client list (hereinafter, referred to as "a server" in this embodiment), and if the query does not include the client list, the request is first routed to any application AP server at random.

After receiving the request, the server A analyzes a database platform, a database number and a database table number corresponding to each client according to the client list, and as a plurality of clients may be in the same database platform, database or database table, the database platform, the database number and the database table number are subjected to duplicate removal and stored as a task list to generate a sub-query task; and if the request does not contain the customer list, integrating the data of the full database platform, the database number and storing the data as a task list to generate a sub-query task.

The application AP server is preferably an x86 or arm server.

Step S3, traversing the task list, modifying the paging information, and establishing a query task, including determining a relationship between a database platform to which the currently-received application AP server belongs and a database platform corresponding to the task.

Optionally, modifying the paging information to create the query task includes modifying the paging information of n rows starting from the first start row to start + n rows starting from the 0 th row.

Optionally, the determining the relationship between the platform to which the application AP server currently receiving the request belongs and the platform to which the task belongs includes:

The cross-platform RPC query task is generated, the query result is obtained from the heterogeneous database platform, and the cross-database platform conversion is carried out on the SQL access query by connecting an application AP server under the heterogeneous database platform.

Exemplarily, if the database platform to which the server a currently receives the request belongs is consistent with the database platform corresponding to the task, and the number of the server a is consistent with the number of the task database, a local query task is generated, and a query result is accessed through SQL;

if the database platform to which the server A currently receives the request belongs is consistent with the database platform corresponding to the task, but the number of the server A is inconsistent with the number of the task database, generating an RPC internal call query task of the database platform, and calling other AP servers in the database platform to obtain a query result;

and if the database platform to which the server A currently receives the request belongs is not consistent with the database platform corresponding to the task, generating a cross-platform RPC query task, and acquiring a query result in the heterogeneous database platform.

And summarizing all results to obtain a final result.

Illustratively, a query task is divided into a set of sub-query tasks, resulting in a set of sub-query results such as rs1, rs2 … rsN.

If the query range of the original page is from line a to line b (a < ═ b), after the original page is divided into sub queries, line 1 of the sub query result may be the line a of the whole query result, but line b +1 of the sub query result cannot be the line b of the whole query result, so line 1 to line b are taken for each sub query result.

If all query results are collected and then processed, Nb lines are needed in the memory, and the memory pressure is high when the query concurrency pressure of the type of the system is high. Therefore, this embodiment is optional, and the following method is adopted for processing:

when any thread finishes the query task, immediately putting the sub-query result into a queue;

when the length of the queue is more than or equal to 2, randomly taking 2 sub-query results from the queue to perform sorting and merging through the sorting objects, and because the b +1 row after sorting cannot be the b-th row of the whole query result, taking the 1 st row to the b-th row of each sub-query result, and re-pressing the intermediate result into the merging result queue;

and sequentially and circularly taking any 2 sub-query results from the queue to perform sorting and merging through the sorting object, pressing the results into the merging result queue until only one result remains in the queue, and taking the final result from the row a to the row b to obtain the paging query result.

Another aspect of the present invention further relates to a paging parallel query system under a heterogeneous platform, which has a structure shown in fig. 2, and includes:

By using this system, the above-described arithmetic processing method can be executed and a corresponding technical effect can be achieved.

Embodiments of the present invention also provide a computer-readable storage medium capable of implementing all the steps of the method in the above embodiments, the computer-readable storage medium having stored thereon a computer program which, when executed by a processor, implements all the steps of the method in the above embodiments.

Embodiments of the present invention also provide an electronic device for executing the method, as an implementation apparatus of the method, the electronic device at least has a processor and a memory, in particular, the memory stores data required for executing the method and related computer programs, such as the like, and all steps of the implementation method are executed by calling the data and programs in the memory by the processor, and corresponding technical effects are obtained.

Preferably, the electronic device may comprise a bus architecture, which may include any number of interconnected buses and bridges linking together various circuits including one or more processors and memory. The bus may also link various other circuits such as peripherals, voltage regulators, power management circuits, and the like, which are well known in the art, and therefore, will not be described any further herein. A bus interface provides an interface between the bus and the receiver and transmitter. The receiver and transmitter may be the same element, i.e., a transceiver, providing a means for communicating with various other systems over a transmission medium. The processor is responsible for managing the bus and general processing, while the memory may be used for storing data used by the processor in performing operations.

Additionally, the electronic device may further include a communication module, an input unit, an audio processor, a display, a power source, and the like. The processor (or controller, operational controls) employed may include a microprocessor or other processor device and/or logic device that receives input and controls the operation of various components of the electronic device; the memory may be one or more of a buffer, a flash memory, a hard drive, a removable medium, a volatile memory, a non-volatile memory or other suitable devices, and may store the above-mentioned related data information, and may also store a program for executing the related information, and the processor may execute the program stored in the memory to realize information storage or processing, etc.; the input unit is used for providing input to the processor, and can be a key or a touch input device; the power supply is used for supplying power to the electronic equipment; the display is used for displaying display objects such as images and characters, and may be an LCD display, for example. The communication module is a transmitter/receiver that transmits and receives signals via an antenna. The communication module (transmitter/receiver) is coupled to the processor to provide an input signal and receive an output signal, which may be the same as in the case of a conventional mobile communication terminal. Based on different communication technologies, a plurality of communication modules, such as a cellular network module, a bluetooth module, and/or a wireless local area network module, may be disposed in the same electronic device. The communication module (transmitter/receiver) is also coupled to a speaker and a microphone via an audio processor to provide audio output via the speaker and receive audio input from the microphone to implement the usual telecommunication functions. The audio processor may include any suitable buffers, decoders, amplifiers and so forth. In addition, the audio processor is also coupled to the central processor, so that recording on the local machine can be realized through the microphone, and sound stored on the local machine can be played through the loudspeaker.

As will be appreciated by one skilled in the art, embodiments of the present invention may be provided as a method, system, or computer program product. Accordingly, the present invention may take the form of an entirely hardware embodiment, an entirely software embodiment or an embodiment combining software and hardware aspects. Furthermore, the present invention may take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, and the like) having computer-usable program code embodied therein.

The present invention is described with reference to flowchart illustrations and/or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each flow and/or block of the flow diagrams and/or block diagrams, and combinations of flows and/or blocks in the flow diagrams and/or block diagrams, can be implemented by computer program instructions. These computer program instructions may be provided to a processor of a general purpose computer, special purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create a system for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.

These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer-readable memory produce an article of manufacture including an instruction system which implement the function specified in the flowchart flow or flows and/or block diagram block or blocks.

These computer program instructions may also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks. While preferred embodiments of the present invention have been described, additional variations and modifications in those embodiments may occur to those skilled in the art once they learn of the basic inventive concepts. Therefore, it is intended that the appended claims be interpreted as including preferred embodiments and all such alterations and modifications as fall within the scope of the invention.

The above description is only for the preferred embodiment of the present invention, but the scope of the present invention is not limited thereto, and any changes or substitutions that can be easily conceived by those skilled in the art within the technical scope of the present invention are included in the scope of the present invention. Therefore, the protection scope of the present invention shall be subject to the protection scope of the claims.

Claims

1. A paging parallel query method under a heterogeneous platform is characterized by comprising the following steps:

step S2, analyzing the parameter object needed by SQL query binding;

2. The method of claim 1, wherein step S2 includes parsing the parameter object to determine the database platform, the database number, and the database table number.

3. The method of claim 2, wherein the step S2 further comprises:

4. The method of claim 2, wherein in step S3, modifying paging information creating inquiry task includes modifying n rows of paging information starting from the first row to start + n rows starting from the 0 th row.

5. The method according to claim 4, wherein in step S3, the determining the relationship between the platform to which the application AP server currently receiving the request belongs and the platform to which the task belongs includes:

6. The method of claim 5, wherein generating a cross-platform RPC query task and obtaining query results in a heterogeneous database platform comprises connecting an application AP server under the heterogeneous database platform and performing cross-database platform translation on SQL access queries.

7. The method according to any one of claims 1 to 6, wherein the step S4 further comprises:

putting the sub-query results of any thread into a queue;

8. A paging parallel query system under a heterogeneous platform is characterized by comprising:

9. A computer-readable storage medium, characterized in that the storage medium has stored thereon a computer program which, when being executed by a processor, carries out the method of any one of claims 1 to 7.

10. An electronic device comprising a processor and a memory;

the memory is used for storing operation instructions;

the processor is used for executing the method of any one of claims 1 to 7 by calling the operation instruction.

11. A computer program product comprising a computer program and/or instructions, characterized in that the computer program and/or instructions, when executed by a processor, implement the steps of the method of any one of claims 1 to 7.