Method, device and computer program product for reading database data

Abstract

The embodiment of the invention aims to provide a method and equipment for reading database data and a computer program product. The database comprises at least one table, and each table is stored in a partitioned mode based on a partition field; the computer equipment obtains a first SQL query statement input by a user; according to a partition field of a table corresponding to the first SQL query statement, the first SQL query statement is rewritten into a plurality of second SQL query statements, and each second SQL query statement is used for querying a table partition; concurrently executing the plurality of second SQL query statements; and merging the execution results of the plurality of second SQL query statements and returning the merged execution results to the user. According to the embodiments, the first SQL statement input by the user is rewritten into the second SQL statements and the second SQL statements are executed concurrently, so that the data reading speed can be remarkably increased, and the performance bottleneck of traditional JDBC / ODBC single-thread execution is relieved.

Description

Technical Field

[0001] Embodiments of the present disclosure relate to the technical field of data storage, and in particular, to a technology for high-speed reading of database data. Background Art

[0002] In the prior art, data reading usually adopts JDBC (Java Database Connectivity) or ODBC (Open Database Connectivity) technology, both of which are commonly used database access interfaces. The specific process is that the user submits SQL (Structured Query Language) statements to the database through the application, and the database system executes these SQL statements and returns the results. Although this method can meet certain data reading needs, it has the following obvious disadvantages: 1. No parallel data retrieval from multiple partitions leads to performance bottlenecks: Inefficient query processing: JDBC and ODBC technologies usually cannot automatically optimize query execution plans when processing SQL queries.

[0003] Single-threaded execution of a single data retrieval SQL: In the traditional JDBC / ODBC mode, a SQL query is usually executed in a single thread, which still results in poor performance when the queried table has multiple partitions.

[0004] 2. JDBC / database lacks some automatic optimization functions of SQL: No targeted optimization: When executing SQL statements input by users, existing technologies cannot automatically optimize SQL statements based on the actual situation of the database (such as data characteristics). This results in the SQL query not being able to fully utilize the optimization function of the database's structural characteristics.

[0005] 3. Multiple partitions do not parallelize statistical functions, resulting in low SQL execution performance: Limited support for aggregate functions: When using aggregate functions (such as COUNT, SUM, AVG, etc.), existing technologies cannot automatically optimize to improve computing performance. For example, when performing aggregate calculations on a large table with multiple partitions, neither existing databases nor JDBC / ODBC can effectively optimize this. Summary of the invention

[0006] The purpose of various embodiments of the present disclosure is to provide a method, an apparatus, a device, a computer program product, and a computer program storage medium for reading database data.

[0007] According to one aspect of the present disclosure, a method for reading database data is provided, wherein the database includes at least one table, wherein each table is partitioned and stored based on a partition field; The method comprises the following steps: Get the first SQL query statement input by the user; According to the partition field of the table corresponding to the first SQL query statement, rewrite the first SQL query statement into multiple second SQL query statements, wherein each of the second SQL query statements is used to query a table partition; Concurrently executing the multiple second SQL query statements; The execution results of the multiple second SQL query statements are merged and returned to the user.

[0008] According to some embodiments, the first SQL query statement is a SELECT statement and does not use any function.

[0009] According to some embodiments, wherein the first SQL query statement is a SELECT statement and uses a non-aggregate function; The method further comprises the following steps: - determining whether to split the first SQL query statement into the plurality of second SQL query statements according to the non-aggregate function used.

[0010] Further, the non-aggregation function includes a conversion type function; The method further comprises the following steps: - Skipping the rewriting step, executing the first SQL query statement and returning the execution result of the first SQL query statement to the user.

[0011] According to some embodiments, the first SQL query statement is a SELECT statement and uses an aggregate function; wherein, The aggregation function includes a Count function, and the merging step is to add the return values ​​of the multiple second SQL query statements executed concurrently; The aggregation function includes a Max function, and the merging step is to take the maximum value of the return values ​​of the multiple second SQL query statements executed concurrently; The aggregation function includes a Min function, and the merging step is to take the minimum value of the return values ​​of the multiple second SQL query statements executed concurrently; The aggregation function includes a Sum function, and the merging step is to add the return values ​​of the multiple second SQL query statements executed concurrently; The aggregation function includes an Avg function, and the merging step is to obtain an average value according to the Count values ​​and Sum values ​​returned by the multiple second SQL query statements executed concurrently.

[0012] According to some embodiments, the first SQL query statement further includes a query condition. When the query condition uses the function in(), the query field is an index and the type is a continuous value, The rewriting step further includes rewriting the function in() into a query range expressed by between... and...; each of the second SQL query statements includes the rewritten query range.

[0013] According to some embodiments, the storage structure of the database is a B+ tree, and the method of reading data is to retrieve data from a single table.

[0014] According to one aspect of the present disclosure, there is also provided a device for reading database data, wherein the database includes at least one table, wherein each table is partitioned and stored based on a partition field; The device comprises: Device for acquiring a first SQL query statement input by a user; A device for rewriting the first SQL query statement into a plurality of second SQL query statements according to a partition field of a table corresponding to the first SQL query statement, wherein each of the second SQL query statements is used to query a table partition; means for concurrently executing the plurality of second SQL query statements; A device for merging the execution results of the multiple second SQL query statements and returning the results to the user.

[0015] According to one aspect of the present disclosure, there is also provided a device for reading database data, wherein the database includes at least one table, wherein each table is partitioned and stored based on a partition field; The device includes a processor and a memory, the memory includes computer executable instructions, and when the computer executable instructions are executed by the processor, the device is configured to perform the following operations: Get the first SQL query statement input by the user; According to the partition field of the table corresponding to the first SQL query statement, rewrite the first SQL query statement into multiple second SQL query statements, wherein each of the second SQL query statements is used to query a table partition; Concurrently executing the multiple second SQL query statements; The execution results of the multiple second SQL query statements are merged and returned to the user.

[0016] According to one aspect of the present disclosure, there is also provided a computer program product for reading database data, comprising computer program instructions, wherein the database comprises at least one table, wherein each table is partitioned and stored based on a partition field; Wherein, when the computer program instructions are executed by a computer device, the computer device is configured to perform the following operations: Get the first SQL query statement input by the user; According to the partition field of the table corresponding to the first SQL query statement, rewrite the first SQL query statement into multiple second SQL query statements, wherein each of the second SQL query statements is used to query a table partition; Concurrently executing the multiple second SQL query statements; The execution results of the multiple second SQL query statements are merged and returned to the user.

[0017] According to one aspect of the present disclosure, there is also provided a computer program storage medium storing computer executable instructions for reading database data, wherein the database includes at least one table, wherein each table is partitioned and stored based on a partition field; When the computer executable instructions are executed by a computer device, the computer device is configured to perform the following operations: Get the first SQL query statement input by the user; According to the partition field of the table corresponding to the first SQL query statement, rewrite the first SQL query statement into multiple second SQL query statements, wherein each of the second SQL query statements is used to query a table partition; Concurrently executing the multiple second SQL query statements; The execution results of the multiple second SQL query statements are merged and returned to the user.

[0018] According to various embodiments of the present disclosure, the first SQL query statement (data retrieval SQL) input by the user does not need to be modified in any way. By automatically rewriting the first SQL query statement, the data retrieval performance and even the computing performance of the present disclosure can be greatly improved.

[0019] Specifically, the embodiments of the present disclosure have the following improvements over the prior art: 1. Solution to the performance bottleneck of multi-partition data retrieval: Concurrent execution: By rewriting the SQL statement input by the user into multiple SQL subqueries for concurrent data acquisition, the embodiments of the present disclosure can significantly improve the data reading speed and alleviate the performance bottleneck of traditional JDBC / ODBC for single-threaded execution of a SQL query.

[0020] Automatic optimization: The database can automatically split SQL statements according to the partitioning of the target query table, avoiding the problem in existing technologies that cannot automatically optimize according to data characteristics.

[0021] 2. Improvements to the automatic optimization function of SQL statements based on data features: For example, when the query condition of SQL uses the function in(), there are many values ​​in in, and the query field is an index, and the type is date or int, which are types that can be compared to see whether they are continuous values, neither JDBC nor existing databases will rewrite and optimize this SQL. When the above conditions are met, the present disclosure can automatically rewrite SQL and rewrite in into an expression of between... and... Here, the database disk is stored as a B+ tree structure, and between... and... is a sequential read. The sequential reading of index values ​​will correspond to the sequential reading of disks, thereby greatly improving performance.

[0022] 3. When multiple partitions have statistical functions, split the SQL and execute them in parallel and then merge them: Aggregate function optimization: Different optimization processing methods are provided for various aggregate functions (such as COUNT, SUM, MAX, MIN, AVG), so that when performing aggregate calculations, the embodiments of the present disclosure can process complex queries more efficiently and improve computing performance.

[0023] In summary, the embodiments of the present disclosure can significantly improve the data reading performance and computing efficiency of SQL reading databases through automatic rewriting of SQL statements, concurrent execution and result merging mechanisms. BRIEF DESCRIPTION OF THE DRAWINGS

[0024] Other features, objects and advantages of the present disclosure will become more apparent from the detailed description of non-limiting embodiments made with reference to the following drawings: Figure 1 A flow chart of a method for reading database data according to an embodiment of the present disclosure is shown; Figure 2 A schematic diagram showing a device for reading database data according to an embodiment of the present disclosure is shown.

[0025] The same or similar reference numerals in the drawings represent the same or similar components. DETAILED DESCRIPTION

[0026] The specific embodiments of the present disclosure will be further described below in conjunction with the accompanying drawings.

[0027] Before discussing the exemplary embodiments in more detail, it should be mentioned that some exemplary embodiments of the present disclosure are described as devices represented by block diagrams and processes or methods represented by flow charts. Although the flow charts describe the operating processes of the various embodiments of the present disclosure as sequential processing, many of the operations therein can be implemented in parallel, concurrently or simultaneously. In addition, the order of the operations can be rearranged. The processes of the various embodiments of the present disclosure can be terminated when their operations are completed, but can also include additional steps not shown in the flow charts. The processes of the various embodiments of the present disclosure can correspond to methods, functions, procedures, subroutines, subprograms, etc. The methods illustrated by the flow charts and the devices illustrated by the block diagrams discussed below may be implemented by hardware, software, firmware, middleware, microcode, hardware description language, or any combination thereof. When implemented in software, firmware, middleware, or microcode, the program code or code segments that perform the necessary tasks may be stored in a machine or computer readable medium such as a storage medium. (One or more) processors may perform the necessary tasks. Similarly, it will also be understood that any flow charts, flow diagrams, state transition diagrams, and the like represent various processes that can be fully described as program code stored in a computer readable medium and thereby executed by a computer device or processor, whether or not such computer device or processor is explicitly shown.

[0028] As used herein, the term "storage medium" may refer to one or more devices for storing data, including read-only memory (ROM), random access memory (RAM), magnetic RAM, kernel memory, magnetic disk storage media, optical storage media, flash memory devices and / or other machine-readable media for storing information. The term "computer-readable medium" may include, but is not limited to, portable or fixed storage devices, optical storage devices, and various other media capable of storing and / or containing instructions and / or data. A code segment may represent a procedure, function, subprogram, program, routine, subroutine, module, software package, class, or any combination of instructions, data structures, or program descriptions. A code segment may be coupled to another code segment or hardware circuit by passing and / or receiving information, data, arguments, parameters, or storage contents. Information, arguments, parameters, data, etc. may be passed, forwarded, or transmitted via any suitable means including storage sharing, information passing, token passing, network transmission, etc. The "computer device" referred to in this context refers to an electronic device that can perform predetermined processing procedures such as numerical calculations and / or logical calculations by running predetermined programs or instructions, and may include at least a processor and a memory, wherein the processor executes program instructions pre-stored in the memory to perform the predetermined processing process, or the predetermined processing process is performed by hardware such as ASIC, FPGA, DSP, or a combination of the above two.

[0029] The above-mentioned "computer device" is usually expressed in the form of a general-purpose computer device, and its components may include but are not limited to: one or more processors or processing units, system memory. The system memory may include a computer-readable medium in the form of a volatile memory, such as a random access memory (RAM) and / or a cache memory. The "computer device" may further include other removable / non-removable, volatile / non-volatile computer-readable storage media. The memory may include at least one computer program product having a set (e.g., at least one) of program modules that are configured to perform the functions and / or methods of the various embodiments of the present disclosure. The processor executes various functional applications and data processing by running the program stored in the memory.

[0030] For example, a computer program for executing various functions and processes of multiple embodiments of the present disclosure is stored in the memory, and when the processor executes the corresponding computer program, multiple embodiments of the present disclosure are implemented.

[0031] Typically, a computer device may be, for example, a user device or a network device, or even a combination of the two. The user device includes, but is not limited to, a personal computer (PC), a laptop, a mobile terminal, etc. The mobile terminal includes, but is not limited to, a smart phone, a tablet computer, etc. The network device includes, but is not limited to, a single network server, a server group consisting of multiple network servers, or a cloud consisting of a large number of computers or network servers based on cloud computing (Cloud Computing), wherein cloud computing is a type of distributed computing, a super virtual computer consisting of a group of loosely coupled computer sets. The computer device may be run alone to implement the various embodiments of the present disclosure, or may be connected to a network and implement the various embodiments of the present disclosure through interactive operations with other computer devices in the network. The network in which the computer device is located includes, but is not limited to, the Internet, a wide area network, a metropolitan area network, a local area network, a VPN network, etc.

[0032] It should be noted that the user equipment, network equipment and network are only examples. Other existing or future computing devices or networks that are applicable to the embodiments of the present disclosure should also be included in the scope of protection of the present disclosure and are included here by reference.

[0033] The specific structural and functional details disclosed herein are merely representative and are for the purpose of describing exemplary embodiments of the present disclosure. However, the various embodiments of the present disclosure may be implemented in many alternative forms and should not be interpreted as being limited to only the embodiments set forth herein.

[0034] It should be understood that, although the terms "first", "second", etc. may be used herein to describe various units, these units should not be limited by these terms. These terms are used only to distinguish one unit from another unit. For example, without departing from the scope of the exemplary embodiments, the first unit may be referred to as the second unit, and similarly the second unit may be referred to as the first unit. The term "and / or" used herein includes any and all combinations of one or more of the listed associated items.

[0035] The terms used herein are only for describing specific embodiments and are not intended to limit exemplary embodiments. Unless the context clearly indicates otherwise, the singular forms "one", "one" and "item" used herein are also intended to include plural numbers. It should also be understood that the terms "include" and / or "comprise" used herein specify the existence of stated features, integers, steps, operations, units and / or components, without excluding the existence or addition of one or more other features, integers, steps, operations, units, components and / or combinations thereof.

[0036] It should also be noted that, in some alternative implementations, the functions / actions mentioned may occur in a different order than that indicated in the accompanying drawings. For example, depending on the functions / actions involved, two figures shown in succession may actually be performed substantially simultaneously or may sometimes be performed in the reverse order.

[0037] See also Figure 1 , which shows a process for reading database data according to an embodiment of the present disclosure.

[0038] According to an example, the database used in the present disclosure is a relational database, in which a table is partitioned and stored based on a partition field. A partition field is a field used to group data in a table, usually a column in the table (such as date, region, etc.). Partitions are determined based on the value of this field, so that data is allocated to different partitions.

[0039] Each partition is usually stored physically independently, possibly on different disks or different database instances. This separate storage helps optimize data access and management.

[0040] In a relational database, a partition is a B+ tree. The B+ tree is a self-balancing tree data structure used to implement efficient indexing in a database. It has fast search, insert, and delete operations, and can efficiently handle large amounts of data. In a partitioned table, each partition is usually implemented as a B+ tree index. This means that each partition stores data independently and manages the data through its own B+ tree. Due to the physical isolation between partitions, queries can only be performed on related partitions, avoiding full table scans and further improving performance. In addition, data can be retrieved from different partitions in parallel, and the performance is often much better than the default data retrieval method.

[0041] Relational databases that use the B+ structure include Oracle database and MySQL. Oracle database uses B+ tree indexes to implement its B-tree indexes, and optimizes query performance through this index structure. Oracle also supports reverse key indexes and partition indexes to further improve the efficiency of large-scale data processing. MySQL's default storage engine InnoDB uses B+ tree as its main index structure, supporting clustered indexes (primary key indexes) and secondary indexes (non-primary key indexes). InnoDB provides efficient query and data retrieval through B+ tree indexes.

[0042] In some embodiments, the database can read data in a single table and the table is partitioned and stored based on partition fields. Specifically, this means that a data table is divided into multiple smaller parts (partitions) by one or more fields (partition fields), and each partition is physically stored independently. This partition storage method can improve the efficiency of data management and query.

[0043] By dividing the data table into multiple parts through the partition field, queries can be performed within a specific partition without scanning the entire table, which improves the query speed. Each partition can be managed independently, which facilitates the maintenance and optimization of data in different partitions. In addition, partitioned storage allows parallel processing of data in multiple partitions, further improving query and processing performance.

[0044] According to an example, suppose a table tableA is partitioned by columnC, the number of partitions is 2, and the values ​​are "2023" and "2024" respectively. When the user executes SELECT columnA, columnB FROM tableA (where columnA, columnB, columnC are the column names of the database), the query can be split into two parts and processed in parallel: SELECT columnA, columnB FROM tableA WHERE columnC=”2023”; SELECT columnA, columnB FROM tableA WHERE columnC=”2024”; The two query results will be merged and returned to the user, which not only improves query efficiency but also returns results faster.

[0045] like Figure 1 As shown, in step S1, the computer device obtains a first SQL query statement input by a user; in step S2, the computer device rewrites the first SQL query statement into multiple second SQL query statements according to the partition field of the table corresponding to the first SQL query statement, wherein each second SQL query statement is used to query a table partition; in step S3, the computer device concurrently executes the multiple second SQL query statements; in step S4, the computer device merges the execution results of the multiple second SQL query statements and returns them to the user.

[0046] In the following discussion of the present disclosure, "computer device" and "database" are used equivalently. The database is deployed in the computer device to provide SQL query functions to users.

[0047] Specifically, in step S1, the database obtains a first SQL query statement input by a user.

[0048] SQL statements entered by users are the main way for users to interact with databases. SQL is a standard language for managing and operating relational databases.

[0049] The following shows some basic types of SQL statements: SELECT statement: retrieves data from one or more tables.

[0050] INSERT statement: Inserts new records into a table.

[0051] UPDATE Statement: Updates existing records in a table.

[0052] DELETE statement: deletes records from a table.

[0053] The present disclosure is intended to improve the data retrieval performance of the database, so the first SQL query statement input by the user here is a SELECT statement to retrieve data from the database table.

[0054] The SELECT statement is the most commonly used query statement in SQL, which is used to obtain data from one or more tables. Its syntax structure is shown below: SELECT column1, column2, ... FROM table_name WHERE condition; in, SELECT: This is the beginning of the SQL query that specifies the columns to be retrieved.

[0055] You can select one or more columns, or use * to select all columns.

[0056] column1, column2, ...: Specify the column names to be retrieved. Separate the column names with commas.

[0057] For example, SELECT name, age means selecting data from the name and age columns from the table.

[0058] FROM table_name: Specifies the table from which to retrieve data.

[0059] table_name is the name of the table. For example, FROM employees means to retrieve data from the employees table.

[0060] WHERE condition: (Optional) Specify filter conditions to filter records that meet the conditions.

[0061] Only records that satisfy the condition will be returned.

[0062] For example, WHERE age>30 means that only records with age column values ​​greater than 30 are returned.

[0063] The SELECT statement is the basic tool for retrieving data from a database. By specifying column names and filtering conditions, you can flexibly obtain the required data and use it for further analysis or presentation. This is the core statement for database query and operation and is widely used in various database applications.

[0064] Next, in step S2, the database rewrites the first SQL query statement into a plurality of second SQL query statements according to the partition fields of the table corresponding to the first SQL query statement, wherein each second SQL query statement is used to query a table partition.

[0065] The database first parses the first SQL query statement, identifies the target query table and its partition fields and corresponding partition values; then, based on the partition fields and partition values, the database rewrites the first SQL query statement into multiple sub-queries for different partitions.

[0066] According to an example, the first SQL query statement input by the user is: SELECT column1, column2 FROM table_name1 WHERE partition_field = 'some_value' AND other_condition; In this first SQL query statement, partition_field is the partition field of the table, some_value is the value of the partition field, and other_condition is other query conditions.

[0067] The table_name1 table is partitioned by the partition_field field, and different partitions can be stored in different physical locations, as shown below: table_name_partition1 table_name_partition2 table_name_partition3 According to the partition field partition_field, the first SQL query statement can be rewritten into three second SQL query statements, each of which queries a table partition, as shown below: SELECT column1, column2 FROM table_name1_partition1 WHERE partition_field = 'some_value' AND other_condition; SELECT column1, column2 FROM table_name1_partition2 WHERE partition_field = 'some_value' AND other_condition; SELECT column1, column2 FROM table_name1_partition3 WHERE partition_field = 'some_value' AND other_condition; Here, according to the structure of the first SQL query statement, the database can rewrite and split it in the following ways: 1. The first SQL query statement is a SELECT statement and does not use any functions When the first SQL query statement entered by the user is a SELECT statement that does not use any function, the SQL statement can be rewritten into multiple subqueries for different partitions by identifying the partition fields of the table. This method is particularly suitable for reading data from partitioned tables and can significantly improve query performance.

[0068] According to an example, tableA is partitioned by field columnC, and there are 2 partitions with values ​​of "2023" and "2024". When the user's data retrieval SQL (first SQL query statement) is select columnA, columnB fromtableA, the first SQL query statement will be split into 2 second SQL query statements to execute subqueries for different partitions: Select columnA, columnB from tableA where columnC="2023"; Select columnA, columnB from tableA where columnC="2024"; 2. The first SQL query statement is a SELECT statement and uses a non-aggregate function When the first SQL query statement input by the user is a SELECT statement and uses non-aggregate functions, such as character functions, numeric functions, date and time functions, or conversion functions, query performance can still be improved by partitioning the first SQL query statement.

[0069] According to some embodiments of the present disclosure, it may be decided whether to split the first SQL query statement into multiple second SQL query statements based on the non-aggregation function used.

[0070] According to an example, the non-aggregate function used in the first SQL query statement is the sysdate function, that is, taking the current system time value, such as Select sysdate from tableA, and the first SQL query statement input by the user will not be modified.

[0071] At this time, the database can skip the rewriting step, execute the first SQL query statement input by the user and return the execution result of the first SQL query statement, that is, the current time value of the system, to the user.

[0072] According to another example, the non-aggregate function used in the first SQL query statement is an abs function, ie, an absolute value function.

[0073] tableA is partitioned by columnC. There are two partitions with values ​​of "2023" and "2024".

[0074] When the first SQL query statement entered by the user is select abs(columnA) from tableA, the data retrieval SQL will be split into two second SQL query statements: Select abs(columnA) from tableA where columnC=”2023”; Select abs(columnA) from tableA where columnC=”2024”; 3. The first SQL query statement is a SELECT statement and uses an aggregate function When the first SQL query statement entered by the user is a SELECT statement and uses aggregate functions, such as the COUNT function, MAX function, MIN function, SUM function, and AVG function), the splitting example is as follows: tableA is partitioned by columnC. There are two partitions with values ​​of "2023" and "2024" respectively.

[0075] When the user's data retrieval SQL is select count(*) from tableA, the first SQL query statement will be split into two second SQL query statements: select count(*) from tableA where columnC="2023"; select count(*) from tableA where columnC="2024"; In some embodiments of the present disclosure, the first SQL query statement also includes a query condition. When the query condition uses the in() function and contains a continuous value, the continuous value in the in() function is rewritten into a query range expressed by between... and...; each second SQL query statement includes the rewritten query range.

[0076] In one example, when the query condition uses the in() function, there are many values ​​in in, and the query field is an index, and the type is date or int, which can be compared to see whether the value is continuous, the database automatically rewrites the SQL and rewrites in to the expression of between... and... Here, the database disk is stored in a B+ tree structure, and between... and... is a sequential read. The sequential read of index values ​​corresponds to the sequential read of the disk, which greatly improves the performance.

[0077] For example, continuing with the previous example, tableA is partitioned by columnC, with two partitions, whose values ​​are "2023" and "2024". When the user's data retrieval SQL (first SQL query statement) is select columnA, columnB fromtableA where id In(1, 2, 3), the first SQL query statement will be split into two second SQL query statements to execute subqueries for different partitions. At the same time, the query range is also limited in the subquery of each partition: Select columnA, columnB from tableA where columnC="2023" and idbetween '1' and '3'; Select columnA, columnB from tableA where columnC="2024" and idbetween '1' and '3'; Then, in step S3, the database concurrently executes the split multiple second SQL query statements. In step S4, the database merges the execution results of the multiple second SQL query statements and returns them to the user.

[0078] Here, the database rewrites the first SQL query statement input by the user into multiple second SQL query statements (subqueries) for different partitions according to the partition fields of the target query table; concurrently executes the rewritten multiple SQL subqueries using the multi-threaded or distributed computing resources of the computer device; collects the query results of each partition and merges the results; and encapsulates the merged final result and returns it to the user.

[0079] According to an example, the user enters the following first SQL query statement: SELECT COUNT(*) FROM table_name WHERE partition_field = 'some_value' AND other_condition; After database rewriting and concurrent execution, the number of records in different partitions is queried separately, and finally the results are merged and returned.

[0080] The rewritten second SQL query statement is as follows: SELECT COUNT(*) AS count FROM table_name_partition1 WHERE partition_field = 'some_value' AND other_condition; SELECT COUNT(*) AS count FROM table_name_partition2 WHERE partition_field = 'some_value' AND other_condition; SELECT COUNT(*) AS count FROM table_name_partition3 WHERE partition_field = 'some_value' AND other_condition; The database uses a multi-threaded or distributed computing framework to concurrently execute the second SQL query statement, collect the count results of each partition (such as 100, 150, 200), add them up to get a total count of 450, and finally return 450 as the final query result to the user.

[0081] According to some examples of the present disclosure, different aggregation functions may have different result merging methods. For example, the following table 1 shows: Table 1 in, The aggregation function is a Count function, and the corresponding merging step is to add the count values ​​returned by multiple concurrently executed second SQL query statements; The aggregation function is the Max function, and the corresponding merging step is to take the maximum value of the return values ​​of multiple second SQL query statements executed concurrently; The aggregation function is the Min function, and the corresponding merging step is to take the minimum value of the return values ​​of multiple second SQL query statements executed concurrently; The aggregation function is a Sum function, and the corresponding merging step is to add the return values ​​of multiple second SQL query statements executed concurrently; The aggregation function is the Avg function, and the corresponding merging step is to obtain an average value according to the Count values ​​and Sum values ​​returned by multiple second SQL query statements executed concurrently.

[0082] In one example, for example, continuing with the previous example, when the user's data retrieval SQL is select count(*) from tableA, the first SQL query statement is split into two second SQL query statements: select count(*) from tableA where columnC=”2023”; For example, the corresponding return value is V1; select count(*) from tableA where columnC=”2024”; For example, the corresponding return value is V2; The corresponding merging is thus to add the counts of the return values ​​of the multiple second SQL query statements executed concurrently, that is, V1+V2, and the value of V1+V2 will be returned to the user as a result.

[0083] Since the database system automatically rewrites the SQL statements entered by the user and performs concurrent queries, the user does not need to understand or manually handle these complex background operations, and can obtain the query results returned by the database system more quickly.

[0084] See also Figure 2 , which shows a schematic diagram of an apparatus for reading database data according to an embodiment of the present disclosure.

[0085] Figure 2 The data acquisition device 20 shown can be arranged in a computer device. In some embodiments, the data acquisition device 20 can be used as an interface module for interacting with a user and integrated into an interface server of a database. Figure 2 As shown, the data acquisition device 20 includes an SQL sending and receiving device 21, an SQL rewriting device 22, an SQL execution device 23 and an SQL merging device 24.

[0086] like Figure 2 As shown, the SQL transceiver 21 obtains a first SQL query statement input by a user; the SQL rewriting device 22 rewrites the first SQL query statement into multiple second SQL query statements according to the partition field of the table corresponding to the first SQL query statement, wherein each of the second SQL query statements is used to query a table partition; the SQL execution device 23 concurrently executes the multiple second SQL query statements; the SQL merging device 24 merges the execution results of the multiple second SQL query statements and returns them to the user by the SQL transceiver 21.

[0087] It should be noted that the various embodiments of the present disclosure may be implemented in software and / or a combination of software and hardware, for example, may be implemented using an application specific integrated circuit (ASIC), a general purpose computer, or any other similar hardware device. In one embodiment, the software program of the various embodiments of the present disclosure may be executed by a processor to implement the steps or functions described above. Similarly, the software program of the various embodiments of the present disclosure (including related data structures) may be stored in a computer-readable recording medium, such as a RAM memory, a magnetic or optical drive, or a floppy disk and the like. In addition, some steps or functions of the various embodiments of the present disclosure may be implemented using hardware, for example, as a circuit that cooperates with a processor to perform various steps or functions.

[0088] In addition, at least a portion of the embodiments of the present disclosure may be applied as a computer program product, such as a computer program instruction, which, when executed by a computing device, can call or provide the method and / or technical solution according to the embodiments of the present disclosure through the operation of the computing device. The program instruction for calling / providing the method of the embodiments of the present disclosure may be stored in a fixed or removable recording medium, and / or transmitted through a data stream in a broadcast or other signal-bearing medium, and / or stored in a working memory of a computing device that runs according to the program instruction.

[0089] For those skilled in the art, it is obvious that the embodiments of the present disclosure are not limited to the details of the above exemplary embodiments, and the embodiments of the present disclosure can be implemented in other specific forms without departing from the spirit or basic features of the embodiments of the present disclosure. Therefore, no matter from which point of view, the embodiments should be regarded as exemplary and non-restrictive, and the scope of the embodiments of the present disclosure is limited by the attached claims rather than the above description, so it is intended to include all changes within the meaning and scope of the equivalent elements of the claims in the embodiments of the present disclosure. Any figure mark in the claims should not be regarded as limiting the claims involved. In addition, it is obvious that the word "including" does not exclude other units or steps, and the singular does not exclude the plural. Multiple units or devices stated in the system claim can also be implemented by one unit or device through software or hardware. The words first, second, etc. are used to indicate names, and do not indicate any specific order.

Claims

1. A method for reading database data, in, The database includes at least one table, wherein each table is partitioned and stored based on a partition field; The method comprises the following steps: Get the first SQL query statement input by the user; According to the partition field of the table corresponding to the first SQL query statement, rewrite the first SQL query statement into multiple second SQL query statements, wherein each of the second SQL query statements is used to query a table partition; Concurrently executing the multiple second SQL query statements; The execution results of the multiple second SQL query statements are merged and returned to the user.

2. The method according to claim 1, in, The first SQL query statement uses a non-aggregate function; The method further comprises the following steps: - determining whether to split the first SQL query statement into the plurality of second SQL query statements according to the non-aggregate function used.

3. The method according to claim 2, in, The non-aggregate function includes a conversion function; The method further comprises the following steps: - Skipping the rewriting step, executing the first SQL query statement and returning the execution result of the first SQL query statement to the user.

4. The method according to claim 1, in, The first SQL query statement uses an aggregate function; in, The aggregation function includes a Count function, and the merging step is to add the return values ​​of the multiple second SQL query statements executed concurrently; The aggregation function includes a Max function, and the merging step is to take the maximum value of the return values ​​of the multiple second SQL query statements executed concurrently; The aggregation function includes a Min function, and the merging step is to take the minimum value of the return values ​​of the multiple second SQL query statements executed concurrently; The aggregation function includes a Sum function, and the merging step is to add the return values ​​of the multiple second SQL query statements executed concurrently; The aggregation function includes an Avg function, and the merging step is to obtain an average value according to the Count values ​​and Sum values ​​returned by the multiple second SQL query statements executed concurrently.

5. The method according to claim 1, in, The first SQL query statement also includes a query condition. When the query condition uses the in() function and contains continuous values, The rewriting step also includes: - rewriting the continuous values ​​in the function in() into a query range expressed by between... and...; each of the second SQL query statements includes the rewritten query range.

6. The method according to claim 1, in, The storage structure of the database is a B+ tree, and the method of reading data is single table data acquisition.

7. A device for reading database data, in, The database includes at least one table, wherein each table is partitioned and stored based on a partition field; The device comprises: Device for acquiring a first SQL query statement input by a user; A device for rewriting the first SQL query statement into a plurality of second SQL query statements according to a partition field of a table corresponding to the first SQL query statement, wherein each of the second SQL query statements is used to query a table partition; means for concurrently executing the plurality of second SQL query statements; A device for merging the execution results of the multiple second SQL query statements and returning the results to the user.

8. A device for reading database data, in, The database includes at least one table, wherein each table is partitioned and stored based on a partition field; The device includes a processor and a memory, the memory includes computer executable instructions, and when the computer executable instructions are executed by the processor, the device is configured to perform the following operations: Get the first SQL query statement input by the user; According to the partition field of the table corresponding to the first SQL query statement, rewrite the first SQL query statement into multiple second SQL query statements, wherein each of the second SQL query statements is used to query a table partition; Concurrently executing the multiple second SQL query statements; The execution results of the multiple second SQL query statements are merged and returned to the user.

9. A computer program product for reading database data, comprising computer program instructions, in, The database includes at least one table, wherein each table is partitioned and stored based on a partition field; Wherein, when the computer program instructions are executed by a computer device, the computer device is configured to perform the following operations: Get the first SQL query statement input by the user; According to the partition field of the table corresponding to the first SQL query statement, rewrite the first SQL query statement into multiple second SQL query statements, wherein each of the second SQL query statements is used to query a table partition; Concurrently executing the multiple second SQL query statements; The execution results of the multiple second SQL query statements are merged and returned to the user.

10. A computer program storage medium storing computer executable instructions for reading database data, in, The database includes at least one table, wherein each table is partitioned and stored based on a partition field; When the computer executable instructions are executed by a computer device, the computer device is configured to perform the following operations: Get the first SQL query statement input by the user; According to the partition field of the table corresponding to the first SQL query statement, rewrite the first SQL query statement into multiple second SQL query statements, wherein each of the second SQL query statements is used to query a table partition; Concurrently executing the multiple second SQL query statements; The execution results of the multiple second SQL query statements are merged and returned to the user.

Images