Database query execution method and device, equipment and medium

Abstract

The invention relates to a database query execution method and device, equipment and a medium, and the method comprises the steps: obtaining a target query statement, and judging whether the total character number of the target query statement exceeds a preset total character number threshold value or not; when the preset character total number threshold value is not exceeded and the grammar of the target query statement is correct, judging whether a logic execution plan corresponding to the target query statement belongs to a simple query execution plan or not; when the logic execution plan belongs to the execution plan of the simple query, judging whether a physical execution plan corresponding to the logic execution plan belongs to the execution plan of the simple query; and when the physical execution plan belongs to the execution plan of the simple query, locally executing the target query statement according to the physical execution plan. By distinguishing the complexity of the query statements and dynamically selecting the execution environment, the processing efficiency of the simple query task is improved, and meanwhile, the resource waste of the distributed computing environment when the simple task is processed is reduced.

Description

Technical Field

[0001] The present application relates to the field of database query technology, and in particular to a database query execution method and its device, equipment, and medium. Background Art

[0002] In the current field of big data processing, with the surge in data volume, the traditional single-machine computing model can no longer meet the needs of efficient processing. Therefore, distributed computing frameworks have emerged and become the mainstream technology for processing massive data. These frameworks achieve parallel processing of data by distributing data and computing tasks to multiple computing nodes, thereby greatly improving the efficiency and speed of data processing. Specifically, the core advantage of the distributed computing framework is that it can make full use of the computing power of the cluster, and by processing data simultaneously on multiple nodes, it can significantly shorten the data processing time and meet the needs of real-time data analysis and processing.

[0003] However, although distributed computing frameworks perform well in processing large-scale data, they still have some efficiency issues when processing some simple tasks. Specifically, when executing tasks, existing distributed computing frameworks usually take a certain amount of time to load driver components and executors, which is essential even for simple tasks. This not only increases the response time of tasks, but also leads to unnecessary waste of computing resources. Summary of the invention

[0004] The primary purpose of the present application is to solve at least one of the above problems and provide a database query execution method and its device, equipment, and medium.

[0005] In order to meet the various objectives of this application, this application adopts the following technical solutions:

[0006] A database query execution method and its device, equipment, and medium provided to meet one of the purposes of the present application include the following steps:

[0007] Obtaining a target query statement, and determining whether the total number of characters in the target query statement exceeds a preset total number of characters threshold, and if the total number of characters exceeds the preset total number of characters threshold, submitting the target query statement to a distributed computing environment for execution;

[0008] When the total number of characters does not exceed a preset threshold and the syntax of the target query statement is correct, determining whether the logical execution plan corresponding to the target query statement belongs to an execution plan for a simple query; if it does not belong to an execution plan for a simple query, submitting the corresponding target query statement to a distributed computing environment for execution;

[0009] When the logical execution plan belongs to an execution plan of a simple query, determine whether the physical execution plan corresponding to the logical execution plan belongs to an execution plan of a simple query; if it does not belong to an execution plan of a simple query, submit the corresponding target query statement to the distributed computing environment for execution;

[0010] When the physical execution plan is an execution plan for a simple query, the target query statement is executed locally according to the physical execution plan.

[0011] A database query execution device is proposed to meet one of the purposes of the present application and is adapted to the database query execution method, and comprises:

[0012] A total character count judgment module is configured to obtain a target query statement and judge whether the total character count of the target query statement exceeds a preset total character count threshold. If the total character count exceeds the preset total character count threshold, the target query statement is submitted to the distributed computing environment for execution.

[0013] The logic execution plan judgment module is configured to judge whether the logic execution plan corresponding to the target query statement belongs to the execution plan of a simple query when the total number of characters does not exceed a preset threshold and the syntax of the target query statement is correct. If it does not belong to the execution plan of a simple query, the corresponding target query statement is submitted to the distributed computing environment for execution;

[0014] A physical execution plan judgment module is configured to judge whether a physical execution plan corresponding to the logical execution plan belongs to an execution plan of a simple query when the logical execution plan belongs to an execution plan of a simple query, and submit the corresponding target query statement to a distributed computing environment for execution when it does not belong to an execution plan of a simple query;

[0015] The local execution module is configured to execute the target query statement locally according to the physical execution plan when the physical execution plan belongs to an execution plan of a simple query.

[0016] On the other hand, a computer device provided to meet one of the purposes of the present application includes a central processing unit and a memory, wherein the central processing unit is used to call and run a computer program stored in the memory to execute the steps of the database query execution method described in the present application.

[0017] On the other hand, a computer-readable storage medium is provided to meet another purpose of the present application, which stores a computer program implemented according to the database query execution method in the form of computer-readable instructions, and when the computer program is called and executed by a computer, it executes the steps included in the method.

[0018] The technical solution of this application has many advantages, including but not limited to the following aspects:

[0019] This application uses a multi-level judgment mechanism, that is, a comprehensive evaluation of the total number of characters in the target query statement, the logical execution plan, and the physical execution plan to determine whether the corresponding target query statement is a simple query. The simple query is executed locally, avoiding unnecessary driver component and executor loading time in the distributed computing environment, thereby saving computing resources. By executing simple queries locally, this application reduces the response time of the task, especially when processing small-scale data or simple queries, and can quickly return results, improving the user experience. BRIEF DESCRIPTION OF THE DRAWINGS

[0020] The above and / or additional aspects and advantages of the present application will become apparent and easily understood from the following description of the embodiments in conjunction with the accompanying drawings, in which:

[0021] Figure 1 A flowchart of a typical embodiment of the database query execution method of the present application;

[0022] Figure 2 A schematic diagram of a flow chart for determining whether the total number of characters exceeds a preset threshold in an embodiment of the present application;

[0023] Figure 3 A schematic diagram of a flow chart for generating and determining whether a logical execution plan is an execution plan for a simple query in an embodiment of the present application;

[0024] Figure 4 A schematic diagram of a process for generating and determining whether a physical execution plan is an execution plan for a simple query in an embodiment of the present application;

[0025] Figure 5 A schematic diagram of a flow chart for specifically determining whether a physical execution plan is an execution plan for a simple query in an embodiment of the present application;

[0026] Figure 6 A schematic diagram of a process for identifying and handling abnormal situations in an embodiment of the present application;

[0027] Figure 7 A schematic diagram of a process for adding a timeout configuration in an embodiment of the present application;

[0028] Figure 8 This is a principle block diagram of the database query execution device of the present application;

[0029] Fig. 9 A schematic diagram of the structure of a computer device used in this application. DETAILED DESCRIPTION

[0030] The embodiments of the present application are described in detail below, and examples of the embodiments are shown in the accompanying drawings, wherein the same or similar reference numerals throughout represent the same or similar elements or elements having the same or similar functions. The embodiments described below with reference to the accompanying drawings are exemplary and are only used to explain the present application, and cannot be interpreted as limiting the present application.

[0031] It will be understood by those skilled in the art that, unless expressly stated, the singular forms "one", "said", and "the" used herein may also include plural forms. It should be further understood that the term "comprising" used in the specification of the present application refers to the presence of the features, integers, steps, operations, elements and / or components, but does not exclude the presence or addition of one or more other features, integers, steps, operations, elements, components and / or groups thereof. It should be understood that when we refer to an element as being "connected" or "coupled" to another element, it may be directly connected or coupled to the other element, or there may be an intermediate element. In addition, the "connection" or "coupling" used herein may include wireless connection or wireless coupling. The term "and / or" used herein includes all or any unit and all combinations of one or more associated listed items.

[0032] It will be understood by those skilled in the art that, unless otherwise defined, all terms (including technical and scientific terms) used herein have the same meaning as those generally understood by those skilled in the art to which this application belongs. It should also be understood that terms such as those defined in general dictionaries should be understood to have meanings consistent with those in the context of the prior art, and will not be interpreted with idealized or overly formal meanings unless specifically defined as here.

[0033] It will be understood by those skilled in the art that the terms "client", "terminal" and "terminal device" as used herein include both devices with wireless signal receivers, which are devices with only wireless signal receivers without transmission capability, and devices with receiving and transmitting hardware, which are devices with receiving and transmitting hardware capable of performing two-way communication over a two-way communication link. Such devices may include: cellular or other communication devices such as personal computers, tablet computers, which have single-line displays or multi-line displays or cellular or other communication devices without multi-line displays; PCS (Personal Communications Services) which may combine voice, data processing, fax and / or data communication capabilities; PDA (Personal Digital Assistant) which may include a radio frequency receiver, a pager, Internet / intranet access, a web browser, a notepad, a calendar and / or a GPS (Global Positioning System) receiver; conventional laptop and / or palmtop computers or other devices which have and / or include a radio frequency receiver. The "client", "terminal" and "terminal device" used herein may be portable, transportable, installed in a vehicle (aviation, shipping and / or land), or adapted and / or configured to run locally, and / or in a distributed form, running at any other location on the earth and / or in space. The "client", "terminal" and "terminal device" used herein may also be a communication terminal, an Internet terminal, a music / video playing terminal, such as a PDA, a MID (Mobile Internet Device) and / or a mobile phone with a music / video playing function, or may be a smart TV, a set-top box and other devices.

[0034] The hardware referred to by the names such as "server", "client", and "work node" in this application is essentially an electronic device with the equivalent capabilities of a personal computer. It is a hardware device with the necessary components revealed by the von Neumann principle, such as a central processing unit (including an arithmetic unit and a controller), a memory, an input device, and an output device. The computer program is stored in its memory, and the central processing unit calls the program stored in the external memory into the internal memory for execution, executes the instructions in the program, and interacts with the input and output devices to complete specific functions.

[0035] It should be pointed out that the concept of "server" referred to in this application can also be extended to the case of server clusters. According to the network deployment principle understood by those skilled in the art, the servers should be logically divided. In physical space, these servers can be independent of each other but can be called through interfaces, or integrated into a physical computer or a set of computer clusters. Those skilled in the art should understand this flexibility, and should not use it to restrict the implementation of the network deployment method of this application.

[0036] The present application discloses a database query execution method, which aims to optimize large-scale concurrent query processing, and is particularly suitable for high-concurrency application scenarios such as e-commerce platforms. The method can be programmed as a computer program product, deployed in a client or server, and implemented to achieve efficient query task management and execution.

[0037] In e-commerce platforms, users often initiate a large number of product query requests. For example, users want to find products of a specific brand or category. At this time, the business server, as a product search service engine, responds to these query requests centrally. The business server first stores the target query statement entered by the user in a database and marks its status as "pending submission". Subsequently, another process in the business server will schedule the target query statement in the database, and determine whether the target query statement should be executed locally or in a distributed computing environment according to the method of this application. The execution status will be updated to "local mode execution" or "ACK mode execution" accordingly.

[0038] During the implementation of this typical embodiment, a timer is set to start timing from the moment the target query statement is obtained, and continuously monitor whether the target query statement has been submitted to the distributed computing environment for execution within the preset time. If it is not submitted within the preset time, and the timer reading shows that the preset time has been reached, the target query statement is submitted to the distributed computing environment for execution, and the execution status is changed to "ACK mode execution". In addition, in one embodiment, query tasks in the database with an execution status of "pending submission" are polled regularly. If the number of query tasks submitted by the user is less than the preset threshold during the polling time period, it will be determined that this type of task has a high degree of customization. At this time, it will be directly submitted to the distributed computing environment for execution instead of being executed locally. Among them, the preset threshold is set by personnel in this field as needed.

[0039] See also Figure 1 A database query execution method of the present application, in its typical embodiment, comprises the following steps:

[0040] Step S5100, obtaining a target query statement, and determining whether the total number of characters in the target query statement exceeds a preset total number of characters threshold, and if it exceeds the preset total number of characters threshold, submitting the target query statement to a distributed computing environment for execution;

[0041] A target query statement with an execution status of "pending submission" is obtained from a database storing information related to user queries. The total number of characters of the target query statement can be calculated by a simple string length statistical function, and then the calculated total number of characters is compared with a preset total number of characters threshold that has been set in advance. The preset total number of characters threshold can be determined based on experience or multiple query tests, and is used to distinguish between simple queries and complex queries. If the total number of characters exceeds the preset total number of characters threshold, the target query statement is marked as a complex query. In one embodiment, after marking the target query statement as a complex query, the query task is submitted to a distributed computing environment, and the distributed computing environment starts the corresponding resources to execute the target query statement based on the resource usage requested by the query, so as to utilize the parallel processing capabilities and computing resources of the distributed computing environment, and at the same time, the execution status of the target query statement is changed to "ACK mode execution".

[0042] Assuming that the total number of characters of the target query statement submitted by the user is 120, 120 is compared with the preset threshold, assuming that the preset threshold is 50. Since 120 exceeds 50, the target query statement is marked as a complex query, and the query task is submitted to the distributed computing environment for execution, and the execution state is changed to "ACK mode execution".

[0043] Step S5200: When the total number of characters does not exceed a preset threshold and the syntax of the target query statement is correct, determine whether the logical execution plan corresponding to the target query statement belongs to an execution plan for a simple query; if it does not belong to an execution plan for a simple query, submit the corresponding target query statement to a distributed computing environment for execution;

[0044] When the total number of characters in the target query statement does not exceed the preset total number of characters threshold, the target query statement is first verified for grammatical correctness through the parser. Once the syntax is confirmed to be correct, the logical execution plan corresponding to the query statement is further analyzed. The logical execution plan shows in detail the execution steps of the query task, including the access order of the data table, the connection operation, the filtering condition, the sorting and aggregation operations and other logical processes. By analyzing the logical execution plan, it can be determined whether the target query statement involves complex operations such as multi-table query, subquery or aggregation operations. Even if the number of characters in the query statement does not exceed the preset total number of characters threshold, if the logical execution plan does not meet the characteristics of a simple query (such as a simple selection and projection operation of a single table), that is, the target query statement involves multi-table query, subquery or aggregation operation, then the target query statement is regarded as a complex query because its execution logic is relatively complex. Therefore, for complex queries that do not belong to the simple query execution plan, they will be submitted to the distributed computing environment for execution, and at the same time, the execution state of the target query statement will be changed to "ACK mode execution" to ensure that each node in the distributed environment can correctly respond to and execute the query task.

[0045] Step S5300: When the logical execution plan belongs to an execution plan of a simple query, determine whether the physical execution plan corresponding to the logical execution plan belongs to an execution plan of a simple query; if it does not belong to an execution plan of a simple query, submit the corresponding target query statement to the distributed computing environment for execution;

[0046] After determining that the logical execution plan belongs to the execution plan of a simple query through the previous step, further analyze whether the physical execution plan corresponding to the logical execution plan belongs to the execution plan of a simple query. Generate a corresponding physical execution plan based on the logical execution plan. The physical execution plan considers factors such as the actual physical storage structure, index usage, and data distribution, and converts the operators in the logical execution plan into specific physical operators.

[0047] In one embodiment, by counting the number of operators in the physical execution plan and comparing the number of operators with a preset threshold value of the number of operators, it is determined whether the target query statement corresponding to the physical execution plan is a simple query. Usually, the number of operators in the physical execution plan is counted and compared with a preset threshold value of the number of operators. According to the experience of those skilled in the art and the general characteristics of database queries, the threshold value of the number of operators is set to 5. That is, if the number of operators included in a physical execution plan does not exceed 5, then the corresponding target query statement will be defined as a simple query. Simple queries generally include operators of the types of selection, projection, single table scan, etc.

[0048] In another embodiment, the data access range in the physical execution plan is analyzed, and the data access range is compared with a preset range threshold to determine whether the target query statement corresponding to the physical execution plan is a simple query. The data access range in the physical execution plan is analyzed and compared with a preset data access range threshold. An upper limit on the number of data rows and an upper limit on the number of data pages are set by a technician in this field as needed. For example, if the query only accesses no more than 1,000 rows of data, and if the query only accesses no more than 100 data pages, the target query statement is also considered a simple query. It is also possible to check whether there is a full table scan. A full table scan usually indicates that the query involves a large amount of data and is not a simple query.

[0049] In another embodiment, the linearity of the physical execution plan is retrieved, that is, the branching of the physical execution plan. A threshold value of the number of branches or nested structures is set. If the number of branches or nested structures of the physical execution plan exceeds the set threshold value, the target query statement is determined to be a complex query, wherein the threshold value of the number of branches or nested structures is set by a person skilled in the art as needed. That is, the execution plan of a simple query is usually relatively linear, without complex branches and nesting.

[0050] In these embodiments, as long as it is determined that the physical execution plan corresponding to the logical execution plan does not belong to the execution plan of a simple query, the corresponding target query statement is submitted to the distributed computing environment for execution, and the execution state of the target query statement is changed to "ACK mode execution".

[0051] Step S5400: When the physical execution plan is an execution plan for a simple query, the target query statement is executed locally according to the physical execution plan.

[0052] When it is determined through the previous step that the physical execution plan belongs to the execution plan of a simple query, the query is executed in the local database environment according to the steps and operators in the physical execution plan to obtain the required data results, and the execution status is updated to "local mode execution". Since the physical execution plan belongs to the execution plan of a simple query, that is, the complexity of the query is low, it can be efficiently executed in the local environment, thereby reducing the dependence on the distributed computing environment and improving the efficiency and response speed of query processing. By following the steps in the physical execution plan, performing table scanning, filtering, joining, aggregation and other operations in sequence, the query results are finally obtained and returned to the user. In this way, it can be ensured that simple queries are efficiently processed in the local environment.

[0053] It can be known from the typical embodiments of the present application that the technical solution of the present application has many advantages, including but not limited to the following aspects:

[0054] This application uses a multi-level judgment mechanism, that is, a comprehensive evaluation of the total number of characters in the target query statement, the logical execution plan, and the physical execution plan to determine whether the corresponding target query statement is a simple query. The simple query is executed locally, avoiding unnecessary driver component and executor loading time in the distributed computing environment, thereby saving computing resources. By executing simple queries locally, this application reduces the response time of the task, especially when processing small-scale data or simple queries, and can quickly return results, improving the user experience.

[0055] Based on any embodiment of the method of this application, please refer to Figure 2 , obtaining a target query statement, and determining whether the total number of characters of the target query statement exceeds a preset total number of characters threshold, and when the total number of characters exceeds the preset total number of characters threshold, submitting the target query statement to a distributed computing environment for execution, including:

[0056] Step S6100: preprocessing the acquired target query statement, including removing all blank characters therein;

[0057] The preprocessing operation first identifies and removes all blank characters in the target query statement, such as spaces, tabs, newlines, etc., through string processing functions or regular expressions. The purpose of this step is to ensure that the total number of characters is not affected by formatting characters, so as to more accurately evaluate the complexity of the query statement. For example, in one embodiment, the `re` module in the Python programming language is used to implement this function, by writing a regular expression `\s+` to match all blank characters, and using the re.sub function to replace them with an empty string, thereby achieving the effect of removing blank characters.

[0058] Step S6200, counting the total number of characters in the target query statement with spaces removed;

[0059] After removing the blank characters, the string length statistics function is used to calculate the total number of characters in the target query statement. This statistical result will be used in the comparison step in the subsequent embodiments to determine whether to submit the query statement to the distributed computing environment for execution. In one embodiment, the length of the string, i.e., the total number of characters, can be directly obtained using the l en function in the Python programming language. For example, if the target query statement after removing the blank characters is "SELECT * FROM table WHERE id = 1", the total number of characters in the target query statement will return 25.

[0060] Step S6300: compare the total number of characters obtained by counting with a preset total number of characters threshold. When the total number of characters obtained by counting exceeds the preset total number of characters threshold, submit the target query statement to the distributed computing environment for execution.

[0061] The total number of characters counted in the previous step is compared with the preset total number of characters threshold. If the total number of characters exceeds the preset threshold, the query statement is considered to be a complex statement and needs to be submitted to the distributed computing environment for execution. Therefore, the query statement is submitted to the distributed computing environment, and its execution status is updated to "ACK mode execution" accordingly. In one embodiment, assuming that the preset total number of characters threshold is 50, if the total number of characters counted is 27, the query statement will not be submitted to the distributed computing environment for execution; if the total number of characters counted is 51, the query statement will be submitted to the distributed computing environment for execution.

[0062] In this embodiment, by removing all blank characters in the target query statement, it is ensured that the total number of characters is not affected by the formatting characters, so as to more accurately evaluate the complexity of the query statement. The total number of characters of the target query statement with blank characters removed is counted, and the total number of characters obtained by counting is compared with the preset total number of characters threshold. Through comparison, complex query statements can be quickly identified and submitted to the distributed computing environment for execution, thereby making full use of the resources of the distributed computing environment and improving the efficiency and response speed of query processing. At the same time, for simple query statements, unnecessary distributed environment loading can be avoided, saving computing resources.

[0063] Based on any embodiment of the method of this application, please refer to Figure 3 , determining whether the logical execution plan corresponding to the target query statement belongs to the execution plan of a simple query, and if it does not belong to the execution plan of a simple query, submitting the corresponding target query statement to the distributed computing environment for execution, including:

[0064] Step S5210: generate a corresponding logical execution plan by parsing the target query statement;

[0065] As disclosed above, the target query statement to be executed is obtained from the database. The target query statement will first be converted into a SQL syntax tree by the parser. Specifically, the parser first decomposes the target query statement into individual syntax units, such as keywords (SELECT, FROM, WHERE, etc.), identifiers (table names, column names), operators (>, =), etc. Then, the syntax analyzer combines these syntax units into an abstract syntax tree (AST) according to the syntax rules of SQL. Each node of the abstract syntax tree represents a syntax structure of the target query statement, such as SELECT clause, FROM clause, WHERE clause and GROUP BY clause, etc. Based on the abstract syntax tree, a logical execution plan is generated, which describes how to retrieve data from a specific database, including which tables need to be accessed, which filtering conditions are applied, how to sort and group data, etc.

[0066] For example, for the query statement: "SELECT customers.name,SUM(orders.amount)FROMcustomers JOI N orders ON customers.id=orders.customer_id WHERE orders.date>='2023-01-01'GROUP BY customers.name;" the lexical analyzer will decompose it into the following grammatical units "SELECT,customers.name,SUM,(,orders.amount,),FROM,customers,JOI N,orders,ON,customers.id,=,orders.customer_id,WHERE,orders.date,>=,'2023-01-01',GROUPBY,customers.name" and then combine these grammatical units into an abstract syntax tree according to the grammatical rules of SQL, where each node of the abstract syntax tree represents a grammatical structure in the target query statement. For example: SELECT node, containing child nodes to represent the selected fields (customers.name, SUM(orders.amount)); FROM node, containing child nodes to represent the tables involved (customers, orders); JOI N node, representing the join condition between tables (customers.id = orders.customer_id); WHERE node, representing the filtering condition (orders.date> = '2023-01-01'); GROUP BY node, representing the grouping condition (customers.name). Based on AST, generate a logical execution plan. The logical execution plan is a detailed sequence of execution steps that describes how to retrieve data from the database. For example: Step 1: Read data from the customers table; Step 2: Read data from the orders table; Step 3: Perform a JOI N operation based on the condition that customers.id = orders.customer_id; Step 4: Apply the WHERE condition (orders.date> = '2023-01-01') to filter; Step 5: Group the results (GROUP BY customers.name). Step 6: Calculate the aggregate function (SUM(orders.amount)); Step 7: Select the final field (customers.name, SUM(orders.amount)).

[0067] Step S5220: judging whether the target query statement contains multi-table query, subquery or aggregation operation according to the generated logical execution plan;

[0068] According to the logical execution plan generated in the previous step, it is possible to determine whether the target query statement contains multi-table queries, subqueries or aggregate operations by traversing the nodes in the logical execution plan and checking specific operation types. In one embodiment, in the previous step, the generated logical execution plan is assumed to be the first step: read data from the customers table; the second step: read data from the orders table; the third step: perform a JO IN operation based on the condition that customers.id = orders.customer_id; the fourth step: apply the WHERE condition (orders.date> = '2023-01-01') for filtering; the fifth step: group the results (GROUP BY customers.name); the sixth step: calculate the aggregate function (SUM (orders.amount)); the seventh step: select the final field (customers.name, SUM (orders.amount)). First, check whether there is a JO IN operation in the logical execution plan. In this embodiment, the logical execution plan includes a JO IN operation (customers JO IN orders ON customers.id = orders.customer_id), indicating that the query involves join operations on multiple tables and is therefore a multi-table query. Check whether there are nested SELECT statements in the logical execution plan. In this embodiment, the query statement does not have nested SELECT statements and therefore does not contain subqueries. Check whether there are aggregate functions in the logical execution plan, such as SUM, AVG, COUNT, etc. In the embodiment, the logical execution plan includes a SUM function (SUM (orders.amount)), indicating that an aggregate operation is used in the query.

[0069] Since the logical execution plan includes multi-table queries and aggregation operations, the query statement is determined to be a complex query.

[0070] Step S5230: When the target query statement contains multi-table query, subquery or aggregation operation, submit the target query statement to a distributed computing environment for execution.

[0071] When the generated logical execution plan determines that the target query statement contains multi-table queries, subqueries, or aggregation operations, the target query statement will be submitted to the distributed computing environment for execution, and the execution status will be changed to "ACK mode execution". Because multi-table queries, subqueries, and aggregation operations usually involve complex calculations and large amounts of data processing, executing these operations in a single-machine environment may cause performance bottlenecks or long response times. In a distributed computing environment, these complex queries can be broken down into multiple subtasks and processed in parallel on multiple computing nodes, significantly improving processing speed and efficiency.

[0072] In this embodiment, by parsing the target query statement and generating a logical execution plan, it is possible to convert a complex SQL statement into a sequence of steps that can be understood and executed within the database, and then by analyzing the logical execution plan to determine the complexity of the query, accurately identify whether the query involves multi-table query, subquery or aggregation operation, and thus determine whether the target query statement is executed locally or submitted to a distributed computing environment for execution. That is, when the target query statement contains multi-table query, subquery or aggregation operation, it is submitted to a distributed computing environment for execution, avoiding unnecessary loading time of driver components and executors in the distributed computing environment, thereby saving computing resources.

[0073] Based on any embodiment of the method of this application, please refer to Figure 4 , determining whether the physical execution plan corresponding to the logical execution plan belongs to the execution plan of a simple query, including:

[0074] Step S5310: Generate a corresponding physical execution plan according to the logical execution plan;

[0075] By analyzing each operator and step in the logical execution plan, and based on the actual physical storage structure, index usage, data distribution and other factors, a specific physical execution plan is generated. The physical execution plan converts the abstract operators in the logical execution plan into specific physical operators and optimizes the execution path to improve efficiency. For example, for the table scan operator, a full table scan or index scan is selected based on the physical storage structure of the table; for the join operator, a suitable join algorithm is selected based on the data distribution and join conditions, such as hash join, sort merge join, etc. Through this step, the abstract logical execution plan can be converted into a physical execution plan suitable for the actual execution environment, ensuring efficient execution of queries.

[0076] In one embodiment, it is assumed that the logical execution plan contains a table scan operator and a join operator. Select a suitable scanning method according to the physical storage structure of the table (such as whether it is sharded, whether column storage is used, etc.). If the table data is stored on multiple shards, select parallel scanning to improve efficiency. For the join operator, select a suitable join algorithm according to the data distribution and join conditions. If the data is evenly distributed and the join conditions are simple, select hash join; if the data is unevenly distributed or the join conditions are complex, select sort merge join. Through these specific physical operators and optimization strategies, an efficient physical execution plan is generated.

[0077] Step S5320: Determine the number and size of files used by the target query statement according to the generated physical execution plan to determine whether the physical execution plan is an execution plan for a simple query.

[0078] In one embodiment, the number and total size of all files involved in the corresponding target query statement are extracted from the generated physical execution plan, wherein the files include data files and index files. Then, the number and total size of the extracted files are compared with the preset file number and size thresholds of the simple query execution plan to determine whether the physical execution plan belongs to the execution plan of the simple query. If the number and total size of the files are both lower than these thresholds, the physical execution plan is determined to be an execution plan of the simple query; otherwise, it is determined to be an execution plan of the complex query.

[0079] In one embodiment, it is assumed that two tables are involved in the physical execution plan: customers and orders. Assume that there are 10 data files corresponding to the customers table, each with a size of 1GB; at the same time, there are 5 index files corresponding to the orders table, each with a size of 500MB. The total number of files extracted from the physical execution plan is 15, and the total size is 12.5GB. Then, the number and total size of the extracted files are compared with the preset thresholds. Assume that the preset number threshold is 20, and the size threshold is 15GB. Since the number of files and the total size are both lower than these thresholds, it is determined that the physical execution plan belongs to the execution plan of a simple query. Through this step, the complexity of the query can be accurately judged based on the actual usage of the files, so that corresponding optimization measures can be taken to improve the efficiency and performance of query execution.

[0080] In this embodiment, by analyzing the logical execution plan and generating a corresponding physical execution plan, by extracting the number and total size of all files involved in the corresponding target query statement in the generated physical execution plan and comparing them with a preset threshold, it is possible to accurately determine whether the physical execution plan is an execution plan for a simple query. This step helps to take corresponding optimization measures according to the actual usage of the files to improve the efficiency and performance of query execution.

[0081] Based on any embodiment of the method of this application, please refer to Figure 5 , according to the generated physical execution plan, determining the number and size of files used by the target query statement to determine whether the physical execution plan belongs to an execution plan for a simple query, including:

[0082] Step S5321: extract the number and total size of all files involved in the corresponding target query statement from the physical execution plan, where the files include data files and index files;

[0083] Based on the generated physical execution plan, the number and total size of all files involved in the corresponding target query statement are extracted, and the files include data files and index files. Through this step, the number and total size of all files that need to be accessed during the query execution process are determined, which is used to subsequently determine whether the corresponding physical execution plan belongs to the execution plan of a simple query. Among them, the size of the file is usually calculated in bytes.

[0084] Step S5322: Compare the number of extracted files and the total size of all files with the preset file number and size threshold of the simple query execution plan to determine whether the physical execution plan belongs to the execution plan of the simple query.

[0085] Compare the number of files and the total size obtained in the previous step with the preset thresholds. The number of files and the size thresholds can be determined based on experience or performance testing to distinguish between simple queries and complex queries. Specifically, check whether the number of files is lower than the preset number threshold and whether the total size is lower than the preset size threshold. If both the number of files and the total size are lower than the preset number and size thresholds, the physical execution plan is determined to be an execution plan for a simple query; otherwise, it is determined to be an execution plan for a complex query. The implementation of this step can accurately judge the complexity of the query based on the actual usage of the files, so as to take corresponding optimization measures. For example, if the number of files is 15 and the total size is 12.5GB, and the preset number threshold is 20 and the size threshold is 15GB, the physical execution plan will be determined to be an execution plan for a simple query.

[0086] In this embodiment, by extracting the number and total size of all files involved in the target query statement from the physical execution plan, the amount of data required to be accessed during the query execution process can be quantified, so that the execution difficulty of the query can be evaluated based on the number and total size of files, providing a basis for subsequent optimization decisions. Then, the extracted number and total size of files are compared with the preset threshold, so that it can be accurately determined whether the physical execution plan belongs to the execution plan of a simple query. This step ensures that the complexity of the query can be accurately determined based on the actual usage of the files, so that corresponding optimization measures can be taken to improve the efficiency and performance of query execution.

[0087] Based on any embodiment of the method of this application, please refer to Figure 6 ,include:

[0088] Step S7100: monitor the execution status corresponding to the target query statement in real time, and count the number of target query statements whose execution status is characterized as the target query statement being submitted to the execution environment;

[0089] In one embodiment, in a database storing target query statements, a new field is added to record the execution status of each target query statement. The value of this field can be "pending submission", "submitting", "local mode execution" or "ACK mode execution", which respectively represent different execution stages of the target query statement, wherein "pending submission" represents that the target query statement has not been submitted to the execution environment and is waiting for processing, "submitting" represents that the target query statement is being submitted to the execution environment and the submission operation is being performed, "local mode execution" represents that the target query statement is being executed in the local environment, and "ACK mode execution" represents that the target query statement is being executed in a distributed computing environment. In this database, the number of target query statements with an execution status of "submitting" can be obtained in real time, that is, the number of target query statements whose execution status is characterized as the target query statement being submitted to the execution environment is counted.

[0090] Step S7200: When the number of target query statements counted is greater than a preset target query statement number threshold or remains unchanged within a predetermined time, an alarm event is triggered.

[0091] The number of target query statements with an execution status of "submitting" is counted in real time through the previous step. If the counted number is greater than the preset target query statement number threshold, this may be due to a surge in query requests, insufficient resource allocation, etc., wherein the target query statement number threshold is set in advance by a person skilled in the art as needed. For example, if the preset target query statement number threshold is 1,000 query statements, and the actual counted number reaches 1,500, this indicates that a processing bottleneck or abnormal situation may be faced. On the other hand, if the counted number does not change within a predetermined time (such as 10 minutes), this indicates that there may be problems such as blocking, deadlock or resource exhaustion, resulting in the inability to continue to submit or process new target query statements. In these cases, an alarm event will be triggered. The alarm event can be issued by sending an email, SMS notification, system log record, etc., so as to promptly notify the administrator or relevant personnel to handle it. In this way, the submission of the target query statement can be monitored in real time, potential problems can be discovered and handled in a timely manner, and stable operation can be ensured.

[0092] In this embodiment, by monitoring the execution status of the target query statement in real time and counting the number of target query statements being submitted, when the number of target query statements exceeds the threshold or does not change within the set time, an alarm event will be automatically triggered. This can quickly identify and respond to abnormal situations such as blocking, deadlock or resource exhaustion, and promptly notify the administrator to take measures to ensure efficient and stable operation and reduce potential business interruption risks.

[0093] Based on any embodiment of the method of this application, please refer to Figure 7 ,include:

[0094] Step S8100: Start a timer, starting from when the target query statement is obtained;

[0095] When the target query statement is received, a timer is immediately started to record the time from obtaining the query statement to submitting it for execution. The timer can be a built-in time management component, such as a high-precision system clock or a dedicated timing module, which is used to accurately measure time intervals. For example, a nanosecond timestamp can be used to record the time when the query statement is received. In actual operation, the current precise timestamp will be obtained by calling the system API or using a dedicated timing tool at the moment the query statement is received. This timestamp will be used as the starting point of the timer. In this way, the processing time of the query statement can be accurately tracked to ensure that the submission is completed within the preset time, thereby improving the efficiency and response speed of query processing.

[0096] Step S8200: monitor the reading of the timer in real time to determine whether the target query statement has completed the total character threshold determination, the logical execution plan determination, the physical execution plan determination and the local execution of the target query statement within a preset time;

[0097] The total character threshold determination is to determine the execution environment (local execution or execution in a distributed computing environment) of the target query statement by determining whether the total character count of the target query statement exceeds the preset total character threshold in the above steps; the logical execution plan determination is to determine the execution environment of the target query statement by determining whether the logical execution plan corresponding to the target query statement belongs to the execution plan of a simple query; the physical execution plan determination is to determine the execution environment of the target query statement by determining whether the physical execution plan corresponding to the logical execution plan belongs to the execution plan of a simple query.

[0098] After the timer is started, the timer reading is continuously monitored to ensure that the target query statement is submitted and executed within a preset time (e.g., 10 seconds). Specifically, by checking the timer reading in real time and comparing it with the preset time, it is ensured that each determination stage (including the total character threshold determination, the logical execution plan determination, the physical execution plan determination) and the final local execution stage are completed within the preset time, wherein the preset time is set in advance by a person skilled in the art as needed.

[0099] Step S8300: If the above steps are not completed within the preset time, and the timer reading shows that the preset time has been reached, the target query statement is submitted to the distributed computing environment for execution.

[0100] When the monitoring loop detects that the timer reading has reached or exceeded the preset time (for example, 10 seconds), and the target query statement has not completed each determination stage in the above steps (including the total character threshold determination, the logical execution plan determination, the physical execution plan determination) and the final local execution stage within the preset time, the automatic submission mechanism is automatically triggered to submit the target query statement to the distributed computing environment for execution, and the execution state is changed to "ACK mode execution" to ensure that the query task will not be delayed due to excessive waiting time.

[0101] In this embodiment, by starting a timer and recording the time from obtaining the target query statement to submitting the execution, and by real-time monitoring the reading of the timer, it is possible to ensure that each judgment stage (including the total character threshold judgment, the logical execution plan judgment, the physical execution plan judgment) and the final local execution stage are completed within the preset time, thereby improving the efficiency and response speed of query processing. This real-time monitoring mechanism helps to timely discover and deal with possible delay problems, ensuring that the query task is executed in a timely manner. When the monitoring loop detects that the reading of the timer has reached or exceeded the preset time, and the target query statement has not completed each judgment stage in the above steps (including the total character threshold judgment, the logical execution plan judgment, the physical execution plan judgment) and the final local execution stage within the preset time, the automatic submission mechanism will be automatically triggered to submit the target query statement to the distributed computing environment for execution. This automatic submission mechanism ensures that the query task will not be delayed due to excessive waiting time. In this way, it can ensure that the query statement is processed within the specified time, thereby improving the efficiency and response speed of query processing.

[0102] See also Figure 8 , a database query execution device provided to meet one of the purposes of the present application is a functional embodiment of the database query execution method of the present application. On the other hand, the device is a database query execution device provided to meet one of the purposes of the present application, including a total character judgment module 5100, a logical execution plan judgment module 5200, a physical execution plan judgment module 5300 and a local execution module 5400, wherein the total character judgment module 5100 is configured to obtain a target query statement and determine whether the total number of characters of the target query statement exceeds a preset total character threshold. When the total number of characters exceeds the preset threshold, the target query statement is submitted to a distributed computing environment for execution; the logical execution plan judgment module 5200 is configured to determine whether the total number of characters of the target query statement exceeds a preset total character threshold. Threshold, and when the syntax of the target query statement is correct, determine whether the logical execution plan corresponding to the target query statement belongs to the execution plan of a simple query. When it does not belong to the execution plan of a simple query, submit the corresponding target query statement to the distributed computing environment for execution; a physical execution plan determination module 5300 is configured to determine whether the physical execution plan corresponding to the logical execution plan belongs to the execution plan of a simple query when the logical execution plan belongs to the execution plan of a simple query. When it does not belong to the execution plan of a simple query, submit the corresponding target query statement to the distributed computing environment for execution; a local execution module 5400 is configured to execute the target query statement locally according to the physical execution plan when the physical execution plan belongs to the execution plan of a simple query.

[0103] Based on any embodiment of the device of the present application, the total character determination module 5100 includes: a preprocessing submodule, configured to perform preprocessing operations on the acquired target query statement, including removing all blank characters therein; a total character counting submodule, configured to count the total number of characters of the target query statement with blank characters removed; a total character comparison submodule, configured to compare the total number of characters obtained by counting with a preset total number of characters threshold, and when the total number of characters obtained by counting exceeds the preset total number of characters threshold, submitting the target query statement to a distributed computing environment for execution.

[0104] Based on any embodiment of the device of the present application, the logic execution plan judgment module 5200 includes: a logic execution plan generation submodule, which is configured to generate a corresponding logic execution plan by parsing the target query statement; a query complexity analysis submodule, which is configured to determine whether the target query statement contains multi-table query, subquery or aggregation operation based on the generated logic execution plan; a second query execution decision submodule, which is configured to submit the target query statement to a distributed computing environment for execution when the target query statement contains multi-table query, subquery or aggregation operation.

[0105] Based on any embodiment of the device of the present application, the physical execution plan judgment module 5300 includes: a physical execution plan generation submodule, which is configured to generate a corresponding physical execution plan based on the logical execution plan; a third query execution decision submodule, which is configured to determine the number and size of files used by the target query statement based on the generated physical execution plan to determine whether the physical execution plan belongs to an execution plan for a simple query.

[0106] Based on any embodiment of the device of the present application, the second query execution decision submodule includes: a physical execution plan parsing submodule, which is configured to obtain the number and total size of all files involved in the corresponding target query statement by parsing the physical execution plan, and the files include data files and index files; a file data statistics submodule, which is configured to be a fourth query execution decision submodule, which is configured to compare the number of extracted files and the total size of all files with the file number and size threshold of the preset simple query execution plan to determine whether the physical execution plan belongs to the execution plan of the simple query.

[0107] Based on any embodiment of the device of the present application, it also includes: a query statement statistics submodule, which is configured to monitor the execution status corresponding to the target query statement in real time, and count the number of target query statements whose execution status is characterized as the target query statement being submitted to the execution environment; an alarm event triggering submodule, which is configured to trigger an alarm event when the number of target query statements counted is greater than a preset target query statement number threshold or remains unchanged within a predetermined time.

[0108] On the basis of any embodiment of the device of the present application, it also includes: a query timing start submodule, which is configured to start the timer and start timing from the acquisition of the target query statement; a real-time timing monitoring submodule, which is configured to monitor the reading of the real-time monitoring timer to determine whether the target query statement has completed the total character threshold determination, logical execution plan determination, physical execution plan determination and the local execution of the target query statement within a preset time; a timeout query execution submodule, which is configured to submit the target query statement to the distributed computing environment for execution if the above steps are not completed within the preset time and the timer reading shows that the preset time has been reached.

[0109] In order to solve the above technical problems, the present application also provides a computer device. Fig. 9 As shown, a schematic diagram of the internal structure of a computer device. The computer device includes a processor, a computer-readable storage medium, a memory, and a network interface connected via a system bus. Among them, the computer-readable storage medium of the computer device stores an operating system, a database, and computer-readable instructions. The database may store a control information sequence. When the computer-readable instructions are executed by the processor, the processor can implement a database query execution method. The processor of the computer device is used to provide computing and control capabilities to support the operation of the entire computer device. The memory of the computer device may store computer-readable instructions. When the computer-readable instructions are executed by the processor, the processor can execute the database query execution method of the present application. The network interface of the computer device is used to connect and communicate with a terminal. Those skilled in the art can understand that Fig. 9 The structure shown in the figure is only a block diagram of a part of the structure related to the solution of the present application, and does not constitute a limitation on the computer device to which the solution of the present application is applied. The specific computer device may include more or fewer components than those shown in the figure, or combine certain components, or have a different arrangement of components.

[0110] In this embodiment, the processor is used to execute Figure 8 The memory stores the program code and various data required to execute the above modules or submodules. The network interface is used to transmit data between user terminals or servers. The memory in this embodiment stores the program code and data required to execute all modules / submodules in the database query execution device of this application, and the server can call the program code and data of the server to execute the functions of all submodules.

[0111] The present application also provides a storage medium storing computer-readable instructions. When the computer-readable instructions are executed by one or more processors, the one or more processors execute the steps of the database query execution method of any embodiment of the present application.

[0112] A person skilled in the art can understand that all or part of the processes in the above-mentioned embodiments of the present application can be implemented by instructing the relevant hardware through a computer program, and the computer program can be stored in a computer-readable storage medium. When the program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, the aforementioned storage medium can be a computer-readable storage medium such as a disk, an optical disk, a read-only memory (ROM), or a random access memory (RAM).

[0113] It will be understood by those skilled in the art that the various operations, methods, steps, measures, and schemes in the processes discussed in this application may be alternated, changed, combined, or deleted. Furthermore, other steps, measures, and schemes in the various operations, methods, and processes discussed in this application may also be alternated, changed, rearranged, decomposed, combined, or deleted. Furthermore, the steps, measures, and schemes in the various operations, methods, and processes in the prior art that are open source and in this application may also be alternated, changed, rearranged, decomposed, combined, or deleted.

[0114] The above description is only a partial implementation method of the present application. It should be pointed out that for ordinary technicians in this technical field, several improvements and modifications can be made without departing from the principles of the present application. These improvements and modifications should also be regarded as the scope of protection of the present application.

Claims

1. A database query execution method, characterized in that: include: Obtaining a target query statement and determining whether the total number of characters in the target query statement exceeds a preset total number of characters threshold, and if the total number of characters exceeds the preset total number of characters threshold, submitting the target query statement to a distributed computing environment for execution; When the total number of characters does not exceed a preset threshold and the syntax of the target query statement is correct, determining whether the logical execution plan corresponding to the target query statement belongs to an execution plan for a simple query; if it does not belong to an execution plan for a simple query, submitting the corresponding target query statement to a distributed computing environment for execution; When the logical execution plan belongs to an execution plan for a simple query, determining whether the physical execution plan corresponding to the logical execution plan belongs to an execution plan for a simple query; if it does not belong to an execution plan for a simple query, submitting the corresponding target query statement to a distributed computing environment for execution; When the physical execution plan is an execution plan for a simple query, the target query statement is executed locally according to the physical execution plan.

2. The database query execution method according to claim 1, characterized in that: Obtaining a target query statement and determining whether the total number of characters in the target query statement exceeds a preset total number of characters threshold, and submitting the target query statement to a distributed computing environment for execution if the total number of characters exceeds the preset total number of characters threshold, including: Preprocess the obtained target query statement, including removing all blank characters; Count the total number of characters in the target query statement after removing whitespace characters; The total number of characters obtained by counting is compared with a preset total number of characters threshold. When the total number of characters obtained by counting exceeds the preset total number of characters threshold, the target query statement is submitted to the distributed computing environment for execution.

3. The database query execution method according to claim 1, characterized in that: Determining whether the logical execution plan corresponding to the target query statement belongs to an execution plan for a simple query; if it does not belong to an execution plan for a simple query, submitting the corresponding target query statement to a distributed computing environment for execution, including: By parsing the target query statement, a corresponding logical execution plan is generated; According to the generated logical execution plan, determine whether the target query statement contains multi-table query, subquery or aggregation operation; When the target query statement contains multi-table query, subquery or aggregation operation, the target query statement is submitted to a distributed computing environment for execution.

4. The database query execution method according to claim 1, characterized in that: Determining whether the physical execution plan corresponding to the logical execution plan is an execution plan for a simple query includes: Generate a corresponding physical execution plan based on the logical execution plan; According to the generated physical execution plan, the number and size of files used by the target query statement are determined to determine whether the physical execution plan is an execution plan for a simple query.

5. The database query execution method according to claim 4, characterized in that: Determining the number and size of files used by the target query statement based on the generated physical execution plan to determine whether the physical execution plan is an execution plan for a simple query includes: Extracting the number and total size of all files involved in the corresponding target query statement from the physical execution plan, wherein the files include data files and index files; The number of extracted files and the total size of all files are compared with preset file number and size thresholds of a simple query execution plan to determine whether the physical execution plan belongs to an execution plan of a simple query.

6. The database query execution method according to any one of claims 1 to 5, characterized in that: include: By monitoring the execution status corresponding to the target query statement in real time, and counting the number of target query statements whose execution status is represented by the target query statement being submitted to the execution environment; When the number of target query statements counted is greater than a preset target query statement number threshold or remains unchanged within a predetermined time, an alarm event is triggered.

7. The database query execution method according to any one of claims 1 to 5, characterized in that: include: Start the timer, starting from the time the target query statement is obtained; Real-time monitoring of a timer reading to determine whether a target query statement has completed character count threshold determination, logical execution plan determination, physical execution plan determination, and local execution of the target query statement within a preset time; If the above steps are not completed within the preset time, and the timer reading shows that the preset time has been reached, the target query statement is submitted to the distributed computing environment for execution.

8. A database query execution device, characterized in that: include: a total character count determination module configured to obtain a target query statement and determine whether the total character count of the target query statement exceeds a preset total character count threshold; if the total character count exceeds the preset total character count threshold, submit the target query statement to the distributed computing environment for execution; a logic execution plan determination module configured to determine whether the logic execution plan corresponding to the target query statement is an execution plan for a simple query when the total number of characters does not exceed a preset threshold and the syntax of the target query statement is correct; and if the logic execution plan does not belong to an execution plan for a simple query, submit the corresponding target query statement to the distributed computing environment for execution; a physical execution plan judgment module configured to, when the logical execution plan belongs to an execution plan for a simple query, judge whether the physical execution plan corresponding to the logical execution plan belongs to an execution plan for a simple query; and, if it does not belong to an execution plan for a simple query, submit the corresponding target query statement to the distributed computing environment for execution; The local execution module is configured to execute the target query statement locally according to the physical execution plan when the physical execution plan belongs to an execution plan of a simple query.

9. A computer device comprising a central processing unit and a memory, characterized in that: The central processing unit is configured to call and run a computer program stored in the memory to execute the steps of the method according to any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that It stores a computer program implemented according to the method described in any one of claims 1 to 7 in the form of computer-readable instructions, and when the computer program is called and executed by a computer, the steps included in the corresponding method are executed.

Images