Data processing method and database system

Abstract

A data processing method, comprising: acquiring a first query and a second query, wherein the second query is a subquery of the first query, the first query comprises a first predicate condition, the second query comprises a second predicate condition, distribution conditions of predicates in the parent query and the subquery are consistent, and the predicate condition of the parent query can be pushed down to the subquery to obtain a third query; and sending the third query to a data node. The query performance can be improved.

Description

A data processing method and database system

[0001] This application claims priority to the Chinese patent application filed with the State Intellectual Property Office on January 2, 2024, with application number 202410008097.4 and application name “A Data Processing Method and Database System”, the entire contents of which are incorporated by reference into this application. Technical Field

[0002] The present application relates to the field of database technology, and in particular to a data processing method, a database system, and a device. Background Art

[0003] A distributed database is a logically unified database formed by connecting multiple physically dispersed database units using a computer network. Each connected database unit is called a site or node.

[0004] In a distributed database system, the base tables of a subquery and the base tables of a parent query are distributed across multiple data nodes (DNs). In existing technologies, when executing a subquery, all base table data is scanned, passed to the upper-level operator, and filtered using predicate conditions. Finally, the results of the subquery and parent query are returned to the CN, which then returns the aggregated results to the user.

[0005] However, in existing technologies, for subqueries, it is necessary to scan all the data in the base table of the subquery and pass it to the upper-level operator. Only after passing through multiple layers of operators can the predicate condition filtering operation be performed. When the amount of data is large, on the one hand, the overhead of broadcasting data is very high, and the operator needs to process a large amount of data, consuming a lot of resources, and affecting query performance.

[0006] Summary of the Invention

[0007] In a first aspect, the present application provides a data processing method, the method comprising: obtaining a first query and a second query, the second query being a subquery of the first query, the first query including a first predicate condition, the second query including a second predicate condition, the first predicate condition indicating data in a first data range of a first table that satisfies a first constraint, the second predicate condition indicating data in a second data range of a second table whose relationship with data in the first data range satisfies a second constraint, and the first data range and the second data range both correspond to distribution keys in a table; obtaining a third query based on the first query and the second query; the third query including a third predicate condition indicating data in a second data range of the second table whose relationship with data in a third data range of the first table satisfies the second constraint, and the data in the third data range is data in the first data range that satisfies the first constraint; and sending the third query to a data node where the second table is deployed.

[0008] In an embodiment of the present application, in a distributed database, for a query, if the query contains a subquery and the distribution conditions of the predicates in the parent query and the subquery are consistent, then the predicate conditions of the parent query can be pushed down to the subquery to reduce the amount of data processed by the subquery, thereby improving the performance of the query.

[0009] The predicate condition of the first query as the parent query is a constraint on the data area (first data range) where the distribution key of the first table is located (that is, the first constraint in the embodiment of the present application). This constraint can indicate which data that needs to be queried in the parent query is in the data area where the distribution key of the first table is located.

[0010] The predicate condition of the second query as a subquery is also a constraint on the data area (second data range) where the distribution key of the second table is located (that is, the second constraint in the embodiment of the present application). The second constraint can indicate which data that needs to be queried in the subquery is in the data area where the distribution key of the second table is located, and the second constraint is related to the distribution key indicated by the predicate condition of the first query, that is, the second predicate condition indicates the data in the second data range of the second table whose relationship with the data in the first data range satisfies the second constraint.

[0011] There is a first constraint in the parent query for the data area where the distribution key of the first table is located, and the second constraint in the subquery is related to the data area where the distribution key of the first table is located. Since the predicate conditions in the parent query must be met in the final query result, the first constraint can be directly placed in the subquery, that is, the first constraint is directly added to the content in the subquery related to the data area where the distribution key of the first table is located (that is, the third query in the embodiment of the present application), thereby greatly reducing the number of data node scans.

[0012] In one possible implementation, the first constraint indicates data in the first data range of the first table that is equal to a preset constant. For example, data in column a of the first table that is equal to 1. In this case, "the third predicate condition indicates data such that the relationship between data in the second data range of the second table and the third data range of the first table satisfies the second constraint" can be understood as "the third predicate condition indicates data in the second data range of the second table that satisfies the first constraint, that is, data equal to the preset constant."

[0013] In one possible implementation, the third predicate condition indicates data in the second data range of the second table that is equal to a preset constant, and the condition of "equal to the preset constant" is specified from a direct or indirect parent query of the second query. "An indirect parent query refers to the relationship between a deep subquery and a parent query when the parent query contains multiple layers of nested subqueries."

[0014] In a possible implementation, the third query does not include the first query.

[0015] That is, the third query is obtained by delegating the first constraint in the first query to the second query, rather than completely fusing the first and second queries.

[0016] In a possible implementation, the first query is a subquery of a fourth query, and the fourth query includes a fourth predicate condition, where the fourth predicate condition indicates data in a fourth data range of the third table that satisfies a third constraint.

[0017] In a possible implementation, obtaining a third query according to the first query and the second query includes:

[0018] Based on the acquired indication information, a third query is obtained according to the first query and the second query; the indication information is used to instruct to push the first predicate condition into the second predicate condition included in the second query.

[0019] In one possible implementation, the method further includes:

[0020] Receive the data in the second table transmitted by the data node according to the third query.

[0021] In a possible implementation, the first data range is a column in the first table, and the second data range is a column in the second table.

[0022] In a second aspect, the present application provides a database system, including a coordination node and a data node:

[0023] The coordination node is used to execute the method in any possible implementation manner of the first aspect;

[0024] The data node is used to scan the second table according to the third query to obtain the data in the second table;

[0025] The data in the second table is delivered to the coordinating node.

[0026] In a third aspect, the present application provides a data processing device, comprising:

[0027] a transceiver module configured to obtain a first query and a second query, wherein the second query is a subquery of the first query, the first query including a first predicate condition, the second query including a second predicate condition, the first predicate condition indicating data in a first data range of the first table that satisfies a first constraint, the second predicate condition indicating data in a second data range of the second table whose relationship with data in the first data range satisfies a second constraint, and the first data range and the second data range both correspond to a distribution key in the table;

[0028] a processing module configured to obtain a third query based on the first query and the second query; the third query including a third predicate condition, the third predicate condition indicating that a relationship between data in the second data range of the second table and data in the third data range of the first table satisfies the second constraint, and the data in the third data range is data in the first data range that satisfies the first constraint;

[0029] The transceiver module is further configured to send the third query to the data node where the second table is deployed.

[0030] In a possible implementation, the first constraint indicates data in a first data range of the first table that is equal to a preset constant.

[0031] In a possible implementation, the third query does not include the first query.

[0032] In a possible implementation, the first query is a subquery of a fourth query, and the fourth query includes a fourth predicate condition, where the fourth predicate condition indicates data in a fourth data range of the third table that satisfies a third constraint.

[0033] In a possible implementation, the processing module is specifically configured to:

[0034] Based on the acquired indication information, a third query is obtained according to the first query and the second query; the indication information is used to instruct to push the first predicate condition into the second predicate condition included in the second query.

[0035] In a possible implementation, the transceiver module is further configured to:

[0036] Receive the data in the second table transmitted by the data node according to the third query.

[0037] In a possible implementation, the first data range is a column in the first table, and the second data range is a column in the second table.

[0038] In a fourth aspect, the present application provides a data processing device. The device may include at least one processor, a memory, and a communication interface. The processor is coupled to the memory and the communication interface. The memory is configured to store instructions, the processor is configured to execute the instructions, and the communication interface is configured to communicate with other network elements under the control of the processor. When executed by the processor, the instructions cause the processor to perform the method of any possible implementation of the first aspect.

[0039] In a fifth aspect of the present application, a computer-readable storage medium is provided, which stores a program, and the program enables a processor to execute the data processing method of the first aspect and any one of its various implementation methods.

[0040] In a sixth aspect of the present application, a computer program product is provided, which includes computer-executable instructions, which are stored in a computer-readable storage medium; at least one processor of a device can read the computer-executable instructions from the computer-readable storage medium, and at least one processor executes the computer-executable instructions so that the device implements the method provided by the above-mentioned first aspect or any possible implementation of the first aspect.

[0041] In a seventh aspect, the present application provides a chip system, which includes a processor for supporting a data processing device to implement the functions involved in the first aspect or any possible implementation of the first aspect. In one possible design, the chip system may also include a memory for storing program instructions and data necessary for the device to manage transactions. The chip system may be composed of a chip or may include a chip and other discrete devices. BRIEF DESCRIPTION OF THE DRAWINGS

[0042] Figures 1A to 1D are schematic diagrams of the application framework of this application;

[0043] Figures 2A and 2B are schematic diagrams of the application framework of this application;

[0044] FIG3 is a flowchart of a data processing method according to an embodiment of the present application;

[0045] Figures 4, 6, 8, and 10 are schematic diagrams of the basic embodiments of the present application;

[0046] Figures 5, 7, 9, and 11 are schematic diagrams of the execution plan of an embodiment of the present application;

[0047] FIG12 is a flowchart of a data processing method according to an embodiment of the present application;

[0048] 13 to 15 are schematic diagrams of the structures of the data processing devices according to the embodiments of the present application. DETAILED DESCRIPTION

[0049] The following describes the embodiments of the present application in conjunction with the accompanying drawings. Obviously, the embodiments described are only part of the embodiments of the present application, rather than all the embodiments. Those skilled in the art will appreciate that with the development of technology and the emergence of new scenarios, the technical solutions provided in the embodiments of the present application are also applicable to similar technical problems.

[0050] The terms "first," "second," and the like in the specification and claims of this application and in the accompanying drawings are used to distinguish similar objects and are not necessarily used to describe a particular order or precedence. It should be understood that the terms used in this manner are interchangeable where appropriate so that the embodiments described herein can be implemented in an order other than that illustrated or described herein. In addition, the terms "including" and "having," as well as any variations thereof, are intended to cover non-exclusive inclusions, e.g., a process, method, system, product, or apparatus comprising a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units that are not explicitly listed or that are inherent to these processes, methods, products, or apparatus.

[0051] To facilitate understanding, the following first introduces the relevant terms involved in the embodiments of the present application.

[0052] Distributed database: A distributed database is a logically unified database formed by connecting multiple physically dispersed database units using a computer network. Each connected database unit is called a site or node.

[0053] A key-value database, or key-value store, is a data storage paradigm designed for storing, retrieving, and managing associative arrays, a data structure more commonly known today as a "dictionary" or hash table. A dictionary contains a collection of objects or records, each of which has multiple "fields," or fields, each of which contains data. These records are stored and retrieved using a "key" that uniquely identifies the record and is used to quickly find data within the database.

[0054] Base table: A table is an object used to store data in a database. It is a structured collection of data and the foundation of the entire database system. A table is a database object that contains all the data in the database. A table is defined as a collection of columns.

[0055] Index: It is an ordered data structure in a database management system to assist in quickly querying and updating data in database tables.

[0056] NULL value: A null value. It is a special marker used in Structured Query Language (SQL). It is used to indicate an unknown or missing attribute in a database, indicating an uncertain value. Introduced by E.F. Codd, the creator of the relational database model, the SQL NULL value is used to meet the requirement of real relational database management systems (RDBMS) to support missing and inapplicable information.

[0057] Primary Key: A table often has one or more columns whose values ​​uniquely identify each row in the table. This column or columns is called the table's primary key and is used to enforce the entity integrity of the table. A primary key is created by defining a PRIMARY KEY constraint when creating or altering a table. A table can have only one PRIMARY KEY constraint, and the columns in a PRIMARY KEY constraint cannot accept NULL values. Because PRIMARY KEY constraints ensure unique data, they are often used to define identity columns.

[0058] Distribution key: In a distributed database, a column (or combination of columns) that determines the database partition where a specific row of data is stored.

[0059] Subquery: A subquery is a SELECT query that returns a single value and is nested within a SELECT, INSERT, UPDATE, or DELETE statement or another subquery. The parent query is the parent query. Subqueries can be nested multiple times.

[0060] The method provided in the embodiment of the present application can be applied to a database system. FIG1A shows a typical logical architecture of a database system. According to FIG1A , a database system 100 includes a database 110 and a database management system (DBMS) 130 .

[0061] Among them, the database 110 is an organized data set stored in the data storage 120, that is, a related data set organized, stored and used according to a specific data model. According to the different data models used to organize data, data can be divided into multiple types, such as relational data, graph data, time series data, etc. Relational data is data modeled using a relational model, usually represented as a table, and the rows in the table represent a set of related values ​​of an object or entity. Graph data, referred to as "graph", is used to represent the relationship between objects or entities, such as social relationships. Time series data, referred to as time series data, is a data column recorded and indexed in chronological order, used to describe the state change information of an object in the time dimension.

[0062] The database management system 130 is the core of the database system and is the system software used to organize, store, and maintain data. Clients 200 can access the database 110 through the database management system 130, and database administrators also use the database management system to perform database maintenance. The database management system 130 provides various functions for clients 200, which can be applications or user devices, to create, modify, and query databases. The functions provided by the database management system 130 may include but are not limited to the following: (1) Data definition function. The database management system 130 provides a data definition language (DDL) to define the structure of the database 110. DDL is used to describe the database framework and can be saved in the data dictionary; (2) Data access function. The database management system 130 provides a data manipulation language (DML) to implement basic access operations on the database 110, such as retrieval, insertion, modification and deletion; (3) Database operation management function. The database management system 130 provides a data control function to effectively control and manage the operation of the database 110 to ensure that the data is correct and valid; (4) Database establishment and maintenance function, including loading the initial data of the database, dumping, restoring, and reorganizing the database, system performance monitoring, analysis and other functions; (5) Database transmission. The database management system provides transmission of processed data to realize communication between the client and the database management system, which is usually coordinated with the operating system.

[0063] The data storage 120 includes, but is not limited to, solid-state drives (SSDs), disk arrays, cloud storage, or other types of non-transitory computer-readable storage media. Those skilled in the art will appreciate that a database system may include fewer or more components than those shown in FIG1A , or may include components different from those shown in FIG1A . FIG1A merely illustrates components that are more relevant to the implementation disclosed in the embodiments of the present invention.

[0064] The database system provided in the embodiments of the present application can be a distributed database system (DDBS). During transaction processing, a DDBS typically employs a global transaction manager (GTM) to manage transactions in order to achieve concurrency control between transactions. The following describes a DDBS with reference to Figures 1B and 1C.

[0065] Figure 1B is a schematic diagram of a distributed database system using a shared-storage architecture, including one or more coordinator nodes (CN), multiple data nodes (DN), and one or more GTMs (such as the first and second GTMs in Figure 1B). The first GTM serves as the master GTM, and the second GTM is used to back up the data of the first GTM and take over the work of the first GTM when the first GTM fails, thus ensuring the high reliability of the DDBS. The CN and DN communicate through a network channel. In one embodiment, the network channel can be composed of network devices such as switches, routers, and gateways. The CN, DN, and GTM jointly implement the functions of the database management system, providing clients with database retrieval, insertion, modification, and deletion services. In one embodiment, a database management system is deployed on each CN, DN, and GTM. The shared data storage stores data that can be shared by multiple DNs, and the DNs can perform read and write operations on the data in the data storage through the network channel. The shared data storage can be a shared disk array. The CN, DN, first GTM or second GTM in the distributed database system can be a physical machine, such as a database server, or a virtual machine (VM) or container running on abstract hardware resources. In one embodiment, the CN, DN, first GTM or second GTM is a virtual machine or container, and the network channel is a virtual switching network, which includes a virtual switch. The database management system deployed in the CN, DN, first GTM or second GTM is a DBMS instance, which can be a process or a thread. These DBMSs work together to complete the functions of the database relational system. In another embodiment, the CN, DN, first GTM or second GTM is a physical machine, and the network channel includes one or more switches, which are storage area network (SAN) switches, Ethernet switches, fiber switches or other physical switching devices.

[0066] Figure 1C is a schematic diagram of a distributed database system using a shared-nothing architecture. Each DN has its own dedicated hardware resources (such as data storage), operating system, and database. The CN, DN, and first or second GTM communicate via a network channel, which can be understood by referring to the corresponding description of Figure 1B above. In this system, data is distributed to each DN based on the database model and application characteristics. Query tasks are divided into several parts by the CN and executed in parallel on all DNs, collaborating with each other to provide database services as a whole. All communication functions are implemented on a high-bandwidth network interconnection system. Similar to the distributed database system with a shared-storage architecture described in Figure 1B, the CN, DN, first or second GTM can be either physical machines or virtual machines.

[0067] In all embodiments of the present application, the data storage of the database system includes but is not limited to solid state drives (SSDs), disk arrays, or other types of non-transient computer-readable media. Although the database is not shown in Figures 1B-1C, it should be understood that the database is stored in the data storage. Those skilled in the art will understand that a database system may include fewer or more components than those shown in Figures 1A-1C, or include components different from those shown in Figures 1A-1C, and Figures 1A-1C only illustrate components that are more relevant to the implementation methods disclosed in the embodiments of the present application. However, those skilled in the art will understand that a distributed database system may include any number of CNs and DNs. The database management system functions of each CN and DN may be implemented by an appropriate combination of software, hardware, and / or firmware running on each CN and DN, respectively.

[0068] The distributed database system described in FIG. 1B and FIG. 1C includes multiple DNs and multiple CNs, wherein the function of each DN is substantially the same, and the function of each CN is also substantially the same.

[0069] FIG1D shows an application data management system provided by an embodiment of the present application. Specifically, as shown, a data node cluster may include multiple data node DNs. Application developers may deploy the relevant data of the developed application in the corresponding data node DNs. The application may send a service request to the application server, and the application server may convert the service request into a data operation request; the application server may send the data operation request to the distributed database system. Then, one or more data node DNs in the distributed database system may perform the operation corresponding to the operation request on the data in the database memory. Specifically, the application server may send the data operation request to the coordination node in the coordination node cluster, and the coordination node may forward the operation request to the relevant DN, and the relevant DN may perform the data operation corresponding to the operation request.

[0070] Referring to Figure 2A, Figure 2A is a system architecture diagram of an embodiment of the present application: it includes 1 to multiple coordination nodes (CN) and multiple data nodes (DN), wherein CN receives SQL statements with subqueries sent by users, and generates an execution plan for predicate pushdown based on the distribution information of the parent query and subquery in the query statement. And the plan is sent to the data node DN. On DN, for subqueries, when scanning the base table data, the predicate condition filtering operation will be performed synchronously, and only data that meets the predicate condition will be passed to the upper-level operator. The same applies to the parent query. Finally, DN returns the data to the Gather operator of CN, and CN returns the result to the user after receiving the data.

[0071] Referring to Figure 2B, which is a system architecture diagram of an embodiment of the present application, the present application may be program code contained within a distributed database and deployed on a server. Taking the application scenario shown in Figure 2B as an example, the program code of this application resides within the database parser, optimizer, and executor of the coordinating node CN and data nodes DN of the distributed database. Base table data is stored on different DNs using a distribution algorithm based on the base table's distribution key. As shown in Figure 2B, a user sends an SQL statement with a subquery to the CN. The CN is responsible for parsing and checking the user's input statement, generating an execution plan, and connecting to the DN via the network, sending data and instructions to the DN, and finally aggregating statistical information from the DN. The DN is responsible for the specific execution of the execution plan. Aside from the data they store, the architectures of the DNs are similar and support various data distribution methods. The CN can be selected through an election algorithm or adopt a different architecture from the DN. Depending on the specific deployment of the distributed database, there can be multiple CNs. The various database modules are deployed equally on the CN and each DN, and the roles of the nodes in the distributed database are configured using configuration files.

[0072] A distributed database is a logically unified database formed by connecting multiple physically dispersed database units using a computer network. Each connected database unit is called a site or node.

[0073] In a distributed database system, the base tables of a subquery and the base tables of a parent query are distributed across multiple data nodes (DNs). In existing technologies, when executing a subquery, all base table data is scanned, passed to the upper-level operator, and filtered using predicate conditions. Finally, the results of the subquery and parent query are returned to the CN, which then returns the aggregated results to the user.

[0074] In existing technologies, the BROADCAST operator is used. This operator broadcasts the data of the current data node to all data nodes for subsequent operations. This operator is required for scenarios where the predicate condition and the base table distribution key are inconsistent. For example, table t1 contains two columns a and b, and the data is distributed according to column a. Table t2 contains two columns, and the data is distributed according to column b. Both tables contain two rows of data [1,3] and [2,4]. All the data in table t1 is on DN1, and all the data in table t2 is on DN2. In this case, for the following query statement: select t1.b, (select t2.b from t2 where t2.a = t1.a) from t1, this statement contains a subquery that returns the value of column b in the data where the value of column a in table t2 is the same as the value of column a in table t1 in the parent query. If this query is executed on a data node without the BROADCAST operator, since the data in tables t1 and t2 are distributed across different data nodes, no matching data can be found within the data node, resulting in an empty result, which is an incorrect result. However, with the BROADCAST operator, the data on each data node is broadcast to all other data nodes. Then, when the predicate is filtered, matching data can be found and the correct result can be returned.

[0075] However, in existing technologies, for subqueries, it is necessary to scan all the data in the base table of the subquery and pass it to the upper-level operator. Only after passing through multiple layers of operators can the predicate condition filtering operation be performed. When the amount of data is large, on the one hand, the overhead of broadcasting data is very high, and the operator needs to process a large amount of data, consuming a lot of resources, and affecting query performance.

[0076] In order to solve the above problems, an embodiment of the present application provides a data processing method. Referring to FIG3 , FIG3 is a flow chart of a data processing method provided in an embodiment of the present application, including:

[0077] 301. Obtain a first query and a second query, where the second query is a subquery of the first query. The first query includes a first predicate condition, and the second query includes a second predicate condition. The first predicate condition indicates data in a first data range of a first table that satisfies a first constraint, and the second predicate condition indicates data in a second data range of a second table whose relationship with the data in the first data range satisfies a second constraint. Both the first data range and the second data range correspond to a distribution key in a table.

[0078] 302. Obtain a third query based on the first query and the second query; the third query includes a third predicate condition, the third predicate condition indicating data that satisfies the second constraint between data in the second data range of the second table and data in the third data range of the first table; the data in the third data range is data in the first data range that satisfies the first constraint;

[0079] In a possible implementation, a user may send a query statement with a sub-query (eg, an SQL statement) to the coordination node CN. The query statement may include a first query and a second query, wherein the second query is a sub-query of the first query.

[0080] For example, the query statement contains a single-layer subquery, that is, the parent query (query A) contains a subquery (query B), wherein the second query can be query B and the first query can be query A.

[0081] For example, a query statement contains a multi-layer subquery, that is, a subquery (query B) of a parent query (query A) contains a subquery (query C), wherein the second query can be query B and the first query can be query A, or the second query can be query C, the first query can be query B, and the fourth query can be query A.

[0082] For example, the query statement contains two single-layer sub-queries, that is, the parent query (query A) contains sub-query (query B) and sub-query (query C), where the second query can be query B and the first query can be query A, or the second query can be query C and the first query can be query A.

[0083] For example, a query statement contains two multi-level subqueries, that is, a parent query (query A) contains a subquery (query B) and a subquery (query C), the subquery (query B) may further contain a subquery, and the subquery (query C) may further contain a subquery.

[0084] In an embodiment of the present application, if the parent query contains a subquery and the distribution conditions of the predicates in the parent query and the subquery are consistent, then the predicate conditions of the parent query can be pushed down to the subquery to reduce the amount of data processed by the subquery, thereby improving the query performance.

[0085] The predicate condition of the first query as the parent query is a constraint on the data area (first data range) where the distribution key of the first table is located (that is, the first constraint in the embodiment of the present application). This constraint can indicate which data that needs to be queried in the parent query is in the data area where the distribution key of the first table is located.

[0086] The predicate condition of the second query as a subquery is also a constraint on the data area (second data range) where the distribution key of the second table is located (that is, the second constraint in the embodiment of the present application). The second constraint can indicate which data that needs to be queried in the subquery is in the data area where the distribution key of the second table is located, and the second constraint is related to the distribution key indicated by the predicate condition of the first query, that is, the second predicate condition indicates the data in the second data range of the second table whose relationship with the data in the first data range satisfies the second constraint.

[0087] There is a first constraint in the parent query for the data area where the distribution key of the first table is located, and the second constraint in the subquery is related to the data area where the distribution key of the first table is located. Since the predicate conditions in the parent query must be met in the final query result, the first constraint can be directly placed in the subquery, that is, the first constraint is directly added to the content in the subquery related to the data area where the distribution key of the first table is located (that is, the third query in the embodiment of the present application), thereby greatly reducing the number of data nodes scanned.

[0088] For example, base table t1 contains two columns, a and b, with column a being the distribution key for table t1. Base table t2 contains two columns, a and b, with column a being the distribution key for table t2. The parent query semantics is to retrieve the value of column b from data in table t1 where the value of column a is 1. The subquery semantics is to retrieve the value of column b from data in table t2 where the value of column a matches the value of column a in the parent query. Since the parent query already has the constraint "column a value must be 1" for column a in table t1, the subquery semantics can be directly modified to "retrieve the value of column b from data in table t2 where the value of column a is 1."

[0089] Here are a few application scenarios:

[0090] 1. Single single-level subquery.

[0091] The base table schema definition used in Example 1 can be shown in Figure 4, which includes two base tables t1 and t2. Base table t1 contains two columns, a and b, where column a is the distribution key of table t1. Base table t2 contains two columns, a and b, where column a is the distribution key of table t2. Both base table t1 and base table t2 contain 1 million rows of data, distributed on two data nodes DN1 and DN2, with 500,000 rows of data on each data node. The query statement used contains a single-layer subquery. The semantics of the parent query statement is to obtain the value of column b in the data where the value of column a is 1 from table t1, and the semantics of the subquery is to obtain the value of column b in the data where the value of column a is the same as the value of column a in table t1 in the parent query from table t2.

[0092] Before subquery parameter passing was used, the database optimizer generated an execution plan for the subquery. The Seq Scan operator was first used to perform a full table scan on table t2. The scanned data was then sent to all data nodes (DNs) using the BROADCAST operator. After the DNs received the data, the Materialize operator saved the data to memory. Finally, the Result operator performed predicate filtering. Because all data in table t2 needed to be passed to the upper-level operator, this consumed a significant amount of resources and resulted in a very inefficient execution plan.

[0093] Because the predicate conditions of both the subquery and the parent query are based on column a, which is consistent with the distribution key of the base table, the parent query's predicate conditions can be pushed down to the subquery. Figure 5 shows the execution plan generated by the database optimizer after using the subquery to pass parameters. For the subquery, when the Seq Scan operator is used to scan table t2, predicate filtering is performed simultaneously. Only data that meets the predicate conditions is passed to the upper-level operator. This method only processes one piece of data, significantly reducing the resources used compared to the one million pieces of data mentioned above, resulting in significantly improved execution performance.

[0094] 2. Multi-layer subquery.

[0095] The definition of the base table schema can be shown in Figure 6. This example includes three base tables: t1, t2, and t3. Base table t1 contains two columns, a and b, with column a serving as the distribution key for t1. Base table t2 contains two columns, a and b, with column a serving as the distribution key for t2. Base table t3 contains two columns, a and b, with column a serving as the distribution key for t3. Each of these three base tables contains 1 million rows of data, distributed across two data nodes, DN1 and DN2, with 500,000 rows per node. The query statement used contains a multi-level subquery—a subquery within a subquery. The parent query statement retrieves the value of column b from data in table t1 where the value of column a is 1. The first-level subquery retrieves data from table t2 where the value of column a matches the value of column a in t1 in the parent query. The second-level subquery retrieves the value of column b from data in table t3 where the value of column a matches the value of column a in t2 in the first-level subquery.

[0096] Before subquery parameter passing was used, the database optimizer generated an execution plan for the second-level subquery. The Seq Scan operator was first used to perform a full table scan on table t3. The scanned data was then sent to all data nodes (DNs) using the BROADCAST operator. After the DNs received the data, the Materialize operator saved it to memory. Finally, the Result operator performed predicate filtering. The same process was applied to table t2 in the first-level subquery. Because all data from tables t2 and t3 must be passed to the upper-level operator, this consumes significant resources and results in a very inefficient execution plan.

[0097] Note that since the predicate conditions of the second-level and first-level subqueries, as well as the predicate conditions of the parent query, are all based on column a, which is consistent with the distribution key of the base table, the predicate conditions of the parent query can be pushed down to the subquery. The execution plan generated by the database optimizer after using subquery parameter passing is shown in Figure 7. For the second-level subquery, when scanning table t3 using the Seq Scan operator, predicate filtering is performed simultaneously. Only data that meets the predicate conditions is passed to the upper-level operator. The same applies to table t2 in the first-level subquery. In this way, the upper-level operator processes only one piece of data, which greatly reduces the resources used compared to the one million pieces of data mentioned above, resulting in a significant improvement in execution performance.

[0098] 3. Two single-level subqueries.

[0099] The definition of the base table schema used can be shown in Figure 8. This example includes three base tables: t1, t2, and t3. Base table t1 contains two columns, a and b, with column a serving as the distribution key for t1. Base table t2 contains two columns, a and b, with column a serving as the distribution key for t2. Base table t3 contains two columns, a and b, with column a serving as the distribution key for t3. Each of these three base tables contains 1 million rows of data, distributed across two data nodes, DN1 and DN2, with 500,000 rows per data node. The query statement used consists of two single-level subqueries. The parent query statement retrieves the value of column b from data in table t1 where the value of column a is 1. The first subquery retrieves data from table t2 where the value of column a matches the value of column a in t1 in the parent query. The second subquery retrieves the value of column b from table t3 where the value of column a matches the value of column a in t1 in the parent query.

[0100] Before using subquery parameter passing, the database optimizer generates an execution plan for the first subquery. The Seq Scan operator is used to perform a full table scan on table t2. The scanned data is then sent to all data nodes (DNs) using the BROADCAST operator. After receiving the data, the Materialize operator saves the data to memory, and the Result operator performs predicate filtering. The same process applies to table t3 in the first subquery. Because all data from tables t2 and t3 must be passed to the upper-level operator, this consumes significant resources and results in a very inefficient execution plan.

[0101] Note that since the predicate conditions of both subqueries and the parent query are based on column a, which is consistent with the distribution key of the base table, the predicate conditions of the parent query can be pushed down to the two subqueries. The execution plan generated by the database optimizer after using subquery parameter passing is shown in Figure 9. For the first subquery, when scanning table t2 using the Seq Scan operator, predicate filtering is performed simultaneously. Only data that meets the predicate conditions is passed to the upper-level operator. The same applies to table t3 in the second subquery. In this way, the upper-level operator processes only one piece of data, which greatly reduces the resources used compared to the one million pieces of data mentioned above, resulting in a significant improvement in execution performance.

[0102] 4. Two multi-level subqueries.

[0103] The base table schema definition used can be shown in Figure 10. This example includes five base tables: t1, t2, t3, t4, and t5. Each base table contains two columns, a and b, with column a serving as the table's distribution key. Each of the five base tables contains 1 million rows of data, distributed across two data nodes, DN1 and DN2, with 500,000 rows per data node. The query statement used includes two multi-level subqueries. In the first subquery, the first-level subquery semantics retrieves data from table t2 where the value of column a matches the value of column a in table t1 in the parent query. The second-level subquery semantics retrieves the value of column b from table t3 where the value of column a matches the value of column a in table t2 in the first-level subquery. In the second subquery, the first-level subquery semantics retrieves data from table t4 where the value of column a matches the value of column a in table t1 in the parent query. The second-level subquery semantics retrieves the value of column b from table t5 where the value of column a matches the value of column a in table t4 in the first-level subquery.

[0104] Before using subquery parameter passing, when the database optimizer generates an execution plan, in the first subquery, for the second-level subquery, the Seq Scan operator is first used to perform a full table scan of the t3 table. The scanned data is then sent to all data nodes (DNs) using the BROADCAST operator. After the DNs receive the data, the Materialize operator saves the data to memory, and finally the Result operator performs predicate filtering. The same applies to the first-level subquery. In the second subquery, for the second-level subquery, the Seq Scan operator is first used to perform a full table scan of the t5 table. The scanned data is then sent to all data nodes (DNs) using the BROADCAST operator. After the DNs receive the data, the Materialize operator saves the data to memory, and finally the Result operator performs predicate filtering. The same applies to the first-level subquery.

[0105] Note that since the predicates of both multi-level subqueries and the parent query are based on column a, which is consistent with the distribution key of the base table, the parent query's predicates can be pushed down to the two multi-level subqueries. The execution plan generated by the database optimizer after using subquery parameter passing is shown in Figure 11. In the first subquery, the Seq Scan operator is used to scan table t3 at the first level, while simultaneously filtering the predicate. Only data that meets the predicate is passed to the upper-level operator. The same applies to the first-level subquery. In the second subquery, the Seq Scan operator is used to scan table t5 at the first level, while simultaneously filtering the predicate. Only data that meets the predicate is passed to the upper-level operator. The same applies to the first-level subquery. This approach allows the upper-level operator in each subquery to process only one row of data, significantly reducing resources compared to the one million rows mentioned above, resulting in significantly improved execution performance.

[0106] It should be understood that in one possible implementation, when confirming whether to delegate the parent query, CN checks whether the statement has parameter information; or whether the predicate conditions and distribution information of the parent query and the child query in the statement are consistent. The parameter information can be called indication information, and the indication information is used to indicate that the first predicate condition is pushed down to the second predicate condition included in the second query. If there is parameter promotion information or the predicate distribution is consistent, the parameter path is generated directly, otherwise the BROADCAST operator is added, and then the parameter path is generated; CN converts the path into an execution plan. The executor executes according to the generated execution plan and returns the execution result.

[0107] 303. Send the third query to the data node where the second table is deployed.

[0108] It should be understood that, as described above, the first query can be a subquery of another query (e.g., the fourth query in the embodiment of the present application), wherein the fourth query includes a fourth predicate condition that indicates data within a fourth data range of the third table that satisfies the third constraint. It should be understood that the third table can be the same table as the first table or the second table.

[0109] 12 , which is a flow chart of a data processing method according to an embodiment of the present application, including:

[0110] The user sends an SQL statement with a subquery to the coordinating node CN;

[0111] CN checks whether the statement has parameter information; or whether the predicate conditions and distribution information of the parent query and subquery in the statement are consistent;

[0112] If there is parameter promotion information or the predicate distribution is consistent, the parameter path is generated directly. Otherwise, the BROADCAST operator is added and then the parameter path is generated.

[0113] CN converts the path into an execution plan.

[0114] The executor executes according to the generated execution plan and returns the execution result.

[0115] 13 , which is a schematic diagram of the structure of a data processing device provided in an embodiment of the present application. As shown in FIG13 , a data processing device 1300 provided in an embodiment of the present application includes:

[0116] The transceiver module 1301 is configured to obtain a first query and a second query, where the second query is a subquery of the first query, the first query includes a first predicate condition, and the second query includes a second predicate condition, the first predicate condition indicating data in a first data range of a first table that satisfies a first constraint, and the second predicate condition indicates data in a second data range of a second table whose relationship with the data in the first data range satisfies a second constraint, and both the first data range and the second data range correspond to a distribution key in a table;

[0117] The transceiver module 1301 is further configured to send the third query to the data node where the second table is deployed.

[0118] The detailed description of the transceiver module 1301 may refer to the introduction of steps 301 and 303 in the above embodiment, which will not be repeated here.

[0119] Processing module 1302 is configured to obtain a third query based on the first query and the second query; the third query includes a third predicate condition, the third predicate condition indicating data whose relationship between data in the second data range of the second table and data in the third data range of the first table satisfies the second constraint, and the data in the third data range is data in the first data range that satisfies the first constraint;

[0120] The specific description of the processing module 1302 can refer to the introduction of step 302 in the above embodiment, which will not be repeated here.

[0121] In a possible implementation, the first query is a subquery of a fourth query, the fourth query includes a fourth predicate condition, and the fourth predicate condition indicates data in a fourth data range of the third table that satisfies a third constraint.

[0122] In a possible implementation, the processing module 1302 is specifically configured to:

[0123] Based on the acquired indication information, a third query is obtained according to the first query and the second query; the indication information is used to instruct to push the first predicate condition into the second predicate condition included in the second query.

[0124] In a possible implementation, the transceiver module 1301 is further configured to:

[0125] Receive data in the second table transmitted by the data node according to the third query.

[0126] In a possible implementation, the first data range is a column in the first table, and the second data range is a column in the second table.

[0127] Next, a data processing device provided in an embodiment of the present application is introduced. Please refer to Figure 14, which is a structural diagram of a data processing device provided in an embodiment of the present application. Specifically, the data processing device 1400 includes: a receiver 1401, a transmitter 1402, a processor 1403 and a memory 1404 (wherein the number of processors 1403 in the data processing device 1400 can be one or more, and Figure 14 takes one processor as an example), wherein the processor 1403 may include an application processor 14031 and a communication processor 14032. In some embodiments of the present application, the receiver 1401, the transmitter 1402, the processor 1403 and the memory 1404 may be connected via a bus or other means.

[0128] Memory 1404 may include read-only memory and random access memory, and provides instructions and data to processor 1403. A portion of memory 1404 may also include non-volatile random access memory (NVRAM). Memory 1404 stores processor and operation instructions, executable modules, or data structures, or subsets or extended sets thereof. The operation instructions may include various operation instructions for implementing various operations.

[0129] Processor 1403 controls the operation of the data processing device. In specific applications, the various components of the data processing device are coupled together via a bus system. In addition to a data bus, the bus system may also include a power bus, a control bus, and a status signal bus. However, for clarity, all bus types are referred to as a bus system in the figure.

[0130] The methods disclosed in the above embodiments of the present application can be applied to or implemented by processor 1403. Processor 1403 can be an integrated circuit chip with signal processing capabilities. During implementation, each step of the above method can be completed by hardware integrated logic circuits or software instructions in processor 1403. The above processor 1403 can be a general-purpose processor, a digital signal processor (DSP), a microprocessor, or a microcontroller, and can further include an application-specific integrated circuit (ASIC), a field-programmable gate array (FPGA), or other programmable logic devices, discrete gate or transistor logic devices, or discrete hardware components. The processor 1403 can implement or execute the various methods, steps, and logic block diagrams disclosed in the embodiments of the present application. The general-purpose processor can be a microprocessor or any conventional processor. The steps of the methods disclosed in conjunction with the embodiments of the present application can be directly implemented as being executed by a hardware decoding processor, or can be executed by a combination of hardware and software modules in the decoding processor. The software module can be located in a storage medium well-known in the art, such as random access memory, flash memory, read-only memory, programmable read-only memory, electrically erasable programmable memory, or registers. The storage medium is located in memory 1404. Processor 1403 reads information from memory 1404 and, in conjunction with its hardware, performs the execution of CN or DN in the above method.

[0131] Receiver 1401 can be used to receive input digital or character information and generate signal input related to the relevant settings and function control of the data processing device. Transmitter 1402 can be used to output digital or character information through the first interface; transmitter 1402 can also be used to send instructions to the disk pack through the first interface to modify the data in the disk pack.

[0132] The present application also provides a data processing device. Please refer to Figure 15, which is a structural diagram of a data processing device provided by an embodiment of the present application. The data processing device 1500 may vary greatly due to different configurations or performance, and may include one or more central processing units (CPUs) 1515 (for example, one or more processors) and a memory 1532, and one or more storage media 1530 (for example, one or more mass storage devices) storing application programs 1542 or data 1544. Among them, the memory 1532 and the storage medium 1530 can be temporary storage or permanent storage. The program stored in the storage medium 1530 may include one or more modules (not shown in the figure), each module may include a series of instruction operations in the data processing device. Furthermore, the central processing unit 1515 can be configured to communicate with the storage medium 1530 to execute a series of instruction operations in the storage medium 1530 on the data processing device 1500.

[0133] The data processing device 1500 may also include one or more power supplies 1526, one or more wired or wireless network interfaces 1550, one or more input and output interfaces 1558; or one or more operating systems 1541, such as Windows Server™, Mac OS X™, Unix™, Linux™, FreeBSD™, etc.

[0134] In the embodiment of the present application, the central processing unit 1515 is used to execute the execution actions of CN or DN in the above embodiments.

[0135] An embodiment of the present application also provides a computer program product, which, when executed on a computer, enables the computer to execute the steps executed by the aforementioned data processing device.

[0136] A computer-readable storage medium is also provided in an embodiment of the present application. The computer-readable storage medium stores a program for performing signal processing. When the program is run on a computer, the computer executes the steps executed by the aforementioned data processing device.

[0137] The data processing device, data processing device or terminal device provided in the embodiments of the present application can specifically be a chip, and the chip includes: a processing unit and a communication unit. The processing unit can be, for example, a processor, and the communication unit can be, for example, an input / output interface, a pin or a circuit. The processing unit can execute the computer-executable instructions stored in the storage unit to enable the chip in the data processing device to perform the data processing method described in the above embodiment, or to enable the chip in the data processing device to perform the data processing method described in the above embodiment. Optionally, the storage unit is a storage unit in the chip, such as a register, a cache, etc. The storage unit can also be a storage unit located outside the chip in the wireless access device, such as a read-only memory (ROM) or other types of static storage devices that can store static information and instructions, a random access memory (RAM), etc.

[0138] The processor mentioned in any of the above places can be a general-purpose central processing unit, a microprocessor, an ASIC, or one or more integrated circuits for controlling the execution of the above program.

[0139] It should also be noted that the device embodiments described above are merely illustrative, wherein the units described as separate components may or may not be physically separate, and the components displayed as units may or may not be physical units, that is, they may be located in one place, or they may be distributed across multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the present embodiment. In addition, in the drawings of the device embodiments provided in this application, the connection relationship between the modules indicates that there is a communication connection between them, which can be specifically implemented as one or more communication buses or signal lines.

[0140] Through the description of the above embodiments, it is clear to those skilled in the art that the present application can be implemented by means of software plus necessary general-purpose hardware, and of course it can also be implemented by means of dedicated hardware including application-specific integrated circuits, dedicated CPUs, dedicated memories, dedicated components, etc. In general, all functions performed by computer programs can be easily implemented with corresponding hardware, and the specific hardware structures used to implement the same function can also be various, such as analog circuits, digital circuits, or dedicated circuits, etc. However, for the present application, software program implementation is a better implementation method in most cases. Based on such an understanding, the technical solution of the present application is essentially or the part that contributes to the prior art can be embodied in the form of a software product, which is stored in a readable storage medium, such as a computer's floppy disk, USB flash drive, mobile hard disk, ROM, RAM, magnetic disk, or optical disk, etc., and includes a number of instructions to enable a computer device (which can be a personal computer, a data processing device, or a network device, etc.) to execute the methods described in each embodiment of the present application.

[0141] In the above embodiments, all or part of the embodiments may be implemented by software, hardware, firmware, or any combination thereof. When implemented by software, all or part of the embodiments may be implemented in the form of a computer program product.

[0142] The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, the process or function described in the embodiment of the present application is generated in whole or in part. The computer can be a general-purpose computer, a special-purpose computer, a computer network, or other programmable devices. The computer instructions can be stored in a computer-readable storage medium, or transmitted from one computer-readable storage medium to another computer-readable storage medium. For example, the computer instructions can be transmitted from a website, a computer, a data processing device or a data center by wired (such as coaxial cable, optical fiber, digital subscriber line (DSL)) or wireless (such as infrared, wireless, microwave, etc.) mode to another website, computer, data processing device or data center. The computer-readable storage medium can be any available medium that a computer can store or a data storage device such as a data processing device, a data center, etc. that includes one or more available media integrations. The available medium can be a magnetic medium, (such as a floppy disk, a hard disk, a magnetic tape), an optical medium (such as a DVD), or a semiconductor medium (such as a solid-state drive (SSD)).

Claims

1. A data processing method, characterized in that, The method includes: Obtaining a first query and a second query, where the second query is a sub-query of the first query, the first query includes a first predicate condition, the second query includes a second predicate condition, the first predicate condition indicates data in a first data range of a first table that satisfies a first constraint, the second predicate condition indicates data in a second data range of a second table whose relationship with the data in the first data range satisfies a second constraint, and both the first data range and the second data range correspond to distribution keys in the table; Obtaining a third query according to the first query and the second query; the third query includes a third predicate condition, and the third predicate condition indicates data in the second data range of the second table whose relationship with the data in a third data range of the first table satisfies the second constraint, and the data in the third data range is the data in the first data range that satisfies the first constraint; Sending the third query to a data node where the second table is deployed.

2. The method according to claim 1, wherein The first constraint indicates data in the first data range of the first table that is equal to a preset constant.

3. The method according to claim 1 or 2, characterized in that The third query does not include the first query.

4. The method according to any one of claims 1 to 3, characterized in that The first query is a sub-query of a fourth query, and the fourth query includes a fourth predicate condition, and the fourth predicate condition indicates data in a fourth data range of a third table that satisfies a third constraint.

5. The method according to any one of claims 1 to 4, characterized in that, The obtaining the third query according to the first query and the second query includes: Based on the obtained indication information, obtaining a third query according to the first query and the second query; the indication information is used to indicate pushing down the first predicate condition into the second predicate condition included in the second query.

6. The method according to any one of claims 1 to 5, characterized in that The method further includes: Receiving data in the second table transmitted by the data node according to the third query.

7. According to the method described in any one of claims 1 to 6, characterized in that, The first data range is a column in the first table, and the second data range is a column in the second table.

8. A database system, characterized in that, Including a coordination node and a data node: The coordination node is configured to execute the method according to any one of claims 1 to 7; The data node is configured to scan the second table according to the third query to obtain data in the second table; Transmitting the data in the second table to the coordination node.

9. A data processing device, characterized in that, The apparatus includes: A transceiver module, configured to obtain a first query and a second query, where the second query is a sub-query of the first query, the first query includes a first predicate condition, the second query includes a second predicate condition, the first predicate condition indicates data in a first data range of a first table that satisfies a first constraint, the second predicate condition indicates data in a second data range of a second table whose relationship with the data in the first data range satisfies a second constraint, and both the first data range and the second data range correspond to distribution keys in the table; A processing module, configured to obtain a third query according to the first query and the second query; the third query includes a third predicate condition, and the third predicate condition indicates data in a second data range of the second table and data in a third data range of the first table, where a relationship between the data satisfies the second constraint, and the data in the third data range is data in the first data range that satisfies the first constraint; The transceiver module is further configured to send the third query to a data node where the second table is deployed.

10. The device according to claim 9, characterized in that The first constraint indicates data in a first data range of the first table that is equal to a preset constant.

11. The device according to claim 9 or 10, characterized in that, The third query does not include the first query.

12. The device according to any one of claims 9 to 11, characterized in that The first query is a subquery of a fourth query, and the fourth query includes a fourth predicate condition, and the fourth predicate condition indicates data in a fourth data range of a third table that satisfies a third constraint.

13. The device according to any one of claims 9 to 12, characterized in that, Specifically, the processing module is configured to: Based on the obtained indication information, obtain a third query according to the first query and the second query; the indication information is used to indicate that the first predicate condition is pushed down to the second predicate condition included in the second query.

14. The device according to any one of claims 9 to 13, characterized in that The transceiver module is further configured to: Receive data in the second table transmitted by the data node according to the third query.

15. The device according to any one of claims 9 to 14, characterized in that, The first data range is a column in the first table, and the second data range is a column in the second table.

16. A computer storage medium, characterized in that, The computer storage medium stores one or more instructions, and when the instructions are executed by one or more computers, the one or more computers are caused to perform the operations of the method according to any one of claims 1 to 7.

17. A computer program product, characterized in that, Including computer-readable instructions, when the computer-readable instructions run on a computer device, the computer device is caused to execute the method according to any one of claims 1 to 7.

18. A system, including at least one processor and at least one memory; the processor and the memory are connected through a communication bus and communicate with each other; The at least one memory is used to store code; The at least one processor is used to execute the code to execute the method according to any one of claims 1 to 7.

19. A chip, characterized in that, Including at least one processing unit and an interface circuit, the interface circuit is used to provide program instructions or data for the at least one processing unit, and the at least one processing unit is used to execute the program instructions to implement the method according to any one of claims 1 to 7.

Images