Method and system for optimizing SQL execution in HTAP database using deep learning

Abstract

The present invention discloses a method and system for deep learning to participate in HTAP database SQL optimization, which avoids manual optimization of search engines, improves query speed, and reduces memory usage, thereby improving machine performance. Its technical solution is: the SQL parser receives SQL execution statements and checks whether the SQL execution statements meet the specifications; the execution plan module generates an execution plan through a deep learning model based on SQL statements that meet the specifications after being checked by the SQL parser; the engine executes the generated execution plan, and sends the detailed information of each SQL execution result and SQL execution process to the training set of the deep learning model; the deep learning model learns and trains based on the training set, and then feeds back the results of model learning and training to the execution plan module in the form of the best optimization solution. After receiving the feedback from the deep learning model, the execution plan module automatically optimizes the execution plan and applies it to the next SQL query.

Description

Technical Field

[0001] The present invention relates to database technology, and in particular to a method and system for performing SQL optimization on a HTAP database using deep learning. Background Art

[0002] Before the Internet boom, the amount of data in enterprises was generally small, especially for core business data, which could usually be stored in a single database. At that time, storage did not require a complex architecture, and all online requests (OLTP, Online Transactional Processing) and background analysis (OLAP, Online Analytical Processing) ran on the same database instance.

[0003] With the development of the Internet, the amount of business data of enterprises continues to increase. The capacity limitation of single-machine databases restricts their use in massive data scenarios. Therefore, in actual applications, in order to meet various needs, OLTP and OLAP have parted ways in technology. In many enterprise architectures, these two types of tasks are processed by different teams. As the application of IoT big data continues to deepen, with massive sensor data requiring real-time updates and queries, the performance requirements for databases are also getting higher and higher. At this time, new problems arise.

[0004] There is usually a delay of several minutes or even hours between OLAP and OLTP systems. The consistency between OLAP and OLTP databases cannot be guaranteed, making it difficult to meet business scenarios that require high real-time analysis. In addition, enterprises need to maintain different databases to support two different tasks, which results in high management and maintenance costs.

[0005] Therefore, a database that can uniformly support transaction processing and workload analysis has become a need for many companies. In this context, HTAP (Hybrid Transactional / Analytical Processing) proposed by Gartner has become a hope. Based on an innovative computing and storage framework, HTAP databases can support business system operations and OLAP scenarios on the same data, avoiding a large amount of data interaction between online and offline databases in traditional architectures. In addition, HTAP is based on a distributed architecture and supports elastic expansion. It can expand throughput or storage on demand, and can easily cope with high concurrency and massive data scenarios.

[0006] Currently, there are not many databases that implement HTAP, mainly including PingCAP's TiDB, Alibaba Cloud's HybridDB for MySQL, Baidu's BaikalDB, etc.

[0007] In the big data scenario, in addition to HTAP and cloud native technology (to ensure dynamic scalability), search engines have higher and higher requirements for SQL optimization execution. Not only speed is required, but also machine performance load, such as memory, IO, etc. Especially for OLAP, the data to be pulled is often very large, at the TB level, and the memory is often not enough.

[0008] In summary, the existing solutions will have excessive memory usage in HTAP and large data volume scenarios, requiring manual optimization of the search engine. The time complexity and space complexity of each query are not much different, and automatic optimization is not possible. Summary of the invention

[0009] A brief summary of one or more aspects is given below to provide a basic understanding of these aspects. This summary is not an exhaustive overview of all conceived aspects, and is neither intended to identify the key or critical elements of all aspects nor to define the scope of any or all aspects. Its only purpose is to give some concepts of one or more aspects in a simplified form as a prelude to a more detailed description that will be given later.

[0010] The purpose of the present invention is to solve the above problems and provide a method and system for deep learning to participate in HTAP database SQL optimization execution, which uses deep learning models to implement automatic optimization technology for SQL queries, avoids manual optimization of search engines, improves query speed, and reduces memory usage, thereby improving machine performance.

[0011] The technical solution of the present invention is: the present invention discloses a method for deep learning to participate in HTAP database SQL optimization, the method comprising:

[0012] Step 1: The SQL parser receives the SQL execution statement and checks whether the SQL execution statement complies with the specification;

[0013] Step 2: The execution plan module generates an execution plan through a deep learning model based on the SQL statements that meet the specifications after being checked by the SQL parser;

[0014] Step 3: The engine executes the execution plan generated in step 2 and sends the results of each SQL execution and detailed information of the SQL execution process to the training set of the deep learning model;

[0015] Step 4: The deep learning model learns and trains based on the training set, and then feeds back the results of model learning and training to the execution plan module in the form of the best optimization solution. After receiving the feedback from the deep learning model, the execution plan module automatically optimizes the execution plan and applies it to the next SQL query.

[0016] According to an embodiment of the method for executing SQL optimization in a HTAP database using deep learning of the present invention, the execution plan generated in step 2 is divided into a row-based plan and a column-based plan, and the engine in step 3 is divided into a row-based execution engine to execute the row-based plan and a vectorized execution engine to execute the column-based plan.

[0017] According to an embodiment of the method for executing SQL optimization in HTAP database with deep learning of the present invention, the execution plan module in step 2 generates an execution plan based on the deep learning model further comprising:

[0018] The execution plan module uses the input layer, hidden layer, and output layer of the recurrent neural network structure. The input layer inputs the SQL execution results and detailed information of the SQL execution process, the hidden layer performs cost calculation, and the output layer outputs the SQL statement and the optimal execution plan.

[0019] According to an embodiment of the method for executing SQL optimization by using deep learning in a HTAP database of the present invention, in the cost calculation of the hidden layer, cost estimation is performed for the detailed information of the SQL execution result and the SQL execution process, and the loss function parameters are continuously adjusted during the loop iteration process to reduce the cost. When the reduced cost is better than the expected value of the cost estimate, the optimization result of the loop iteration is constructed into a data structure of a defined execution plan and fed back to the execution plan module.

[0020] According to an embodiment of the method for executing SQL optimization in a HTAP database using deep learning of the present invention, step 4 further includes:

[0021] The interaction protocol between the execution plan module and the deep learning model is predefined, and the data structure of the execution plan is defined in advance;

[0022] The deep learning model learns and trains the training set data sent by the engine in step 3, quantifies and designs the points that can be fed back, and finally feeds back the results of model learning and training to the execution plan module, so that after receiving the feedback from the deep learning model, the execution plan module directly modifies the corresponding execution plan data structure in the abstract syntax tree in the memory for optimization, ensuring that the next query can be applied to this optimization feedback.

[0023] The present invention also discloses a system for executing SQL optimization by participating in deep learning in HTAP database, the system comprising:

[0024] SQL parser, used to receive SQL execution statements and check whether the SQL execution statements comply with the specifications;

[0025] The execution plan module is used to generate an execution plan through a deep learning model based on SQL statements that meet the specifications after being checked by the SQL parser;

[0026] The engine module is used to execute the generated execution plan and send the detailed information of each SQL execution result and SQL execution process to the training set of the deep learning model;

[0027] The deep learning model learns and trains based on the training set, and then feeds back the results of model learning and training to the execution plan module in the form of the best optimization solution, so that after the execution plan module receives the feedback from the deep learning model, it automatically optimizes the execution plan and applies it to the next SQL query.

[0028] According to an embodiment of the system for executing SQL optimization in HTAP database using deep learning of the present invention, the execution plan generated by the execution plan module is divided into a row-based plan and a column-based plan, and the engine module is divided into a row-based execution engine for executing the row-based plan and a vectorized execution engine for executing the column-based plan.

[0029] According to an embodiment of the system for executing SQL optimization using deep learning in HTAP database of the present invention, the execution plan generated by the execution plan module based on the deep learning model further includes:

[0030] The execution plan module uses the input layer, hidden layer, and output layer of the recurrent neural network structure. The input layer inputs the SQL execution results and detailed information of the SQL execution process, the hidden layer performs cost calculation, and the output layer outputs the SQL statement and the optimal execution plan.

[0031] According to an embodiment of the system for executing SQL optimization by using deep learning in the HTAP database of the present invention, in the hidden layer cost calculation of the execution plan module, cost estimation is performed for the detailed information of the SQL execution result and the SQL execution process, and the loss function parameters are continuously adjusted during the loop iteration process to reduce the cost. When the reduced cost is better than the expected value of the cost estimate, the optimization result of the loop iteration is constructed into a data structure of a defined execution plan and fed back to the execution plan module.

[0032] According to an embodiment of the system for executing SQL optimization by using deep learning in HTAP database of the present invention, the deep learning model is further configured as follows:

[0033] The interaction protocol between the execution plan module and the deep learning model is predefined, and the data structure of the execution plan is defined in advance;

[0034] The deep learning model learns and trains the training set data sent by the engine module, quantifies and designs the points that can be fed back, and finally feeds back the results of model learning and training to the execution plan module, so that after receiving the feedback from the deep learning model, the execution plan module directly modifies the corresponding execution plan data structure in the abstract syntax tree in the memory for optimization, ensuring that the next query can be applied to this optimization feedback.

[0035] Compared with the prior art, the present invention has the following beneficial effects: In the solution of the present invention, for HTAP databases, after each SQL execution, the execution result and execution process will be sent to the deep learning model for training, and optimization scheme prediction will be made. The prediction result of the optimization scheme prediction will be fed back to the execution plan module, and the execution plan module will automatically optimize the generated plan according to the feedback of the prediction result, and apply the generated plan to the next query. After the feedback takes effect, the entire SQL execution process is more efficient and occupies less memory. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] The above features and advantages of the present invention can be better understood after reading the detailed description of the embodiments of the present disclosure in conjunction with the following drawings. In the drawings, the components are not necessarily drawn to scale, and components with similar related properties or features may have the same or similar reference numerals.

[0037] Figure 1 A flowchart of an embodiment of a method for executing SQL optimization in a HTAP database using deep learning is shown.

[0038] Figure 2 A schematic diagram of an embodiment of a system for executing SQL optimization in a HTAP database using deep learning of the present invention is shown. DETAILED DESCRIPTION

[0039] The present invention is described in detail below in conjunction with the accompanying drawings and specific embodiments. Note that the aspects described below in conjunction with the accompanying drawings and specific embodiments are only exemplary and should not be construed as limiting the scope of protection of the present invention in any way.

[0040] Figure 1 The flowchart of an embodiment of the method for performing SQL optimization by using deep learning in HTAP database of the present invention is shown. Figure 1 The following is a detailed description of the various implementation steps of the method for performing SQL optimization on a HTAP database using deep learning in this embodiment.

[0041] Step 1: The SQL parser receives the SQL execution statement to check whether the SQL execution statement complies with the specification, usually including checking whether the SQL format and syntax are correct.

[0042] Step 2: The execution plan module generates an execution plan through a deep learning model based on the SQL statements that meet the specifications after being checked by the SQL parser.

[0043] according to Figure 1 As shown, the execution plan module first generates a logical layer plan, and then generates a physical layer plan based on the logical layer plan. Since the HTAP database is a mixed row and column database, the physical layer plan is divided into a row-based plan and a column-based plan.

[0044] The specific process of the execution plan module generating an execution plan based on the deep learning model is as follows.

[0045] The execution plan module uses a recurrent neural network structure (the recurrent neural network includes an input layer, a hidden layer, and an output layer). The input layer inputs the SQL execution results and detailed information of the SQL execution process, the hidden layer performs cost calculation, and the output layer outputs the SQL statement and the optimal execution plan.

[0046] In the cost calculation of the hidden layer, cost estimation is performed based on the detailed information of the SQL execution results and the SQL execution process. During the loop iteration process (the number of iterations is preset in advance), the loss function parameters are continuously adjusted to reduce the cost. When the reduced cost is better than the expected value of the cost estimate, the optimization result of the loop iteration is constructed into a data structure of a defined execution plan and fed back to the execution plan module.

[0047] The above cost estimation is divided into two stages, one is the cost estimation analysis, and the other is the learning optimization stage.

[0048] The cost estimation analysis phase includes multiple execution scenarios, such as full table scan estimation, common index estimation, and multi-table join query. The deep learning model makes an overall estimation of each of the above different execution scenarios to obtain the expected value of the cost estimation, which includes IO cost (number of IO times), CPU computing cost, and memory usage cost.

[0049] In the subsequent learning optimization stage, the weight parameters of the deep learning model are continuously optimized using a recurrent neural network based on the training set data of the deep learning model, and compared with the expected values ​​of the cost estimation analysis stage. If the result of the learning optimization is better than the expected value of the cost estimation analysis, it will be fed back to the execution plan module after a certain number of learning times, and the optimization will be continuously iterated.

[0050] The learning optimization phase is decoupled from SQL execution. The detailed information of each SQL execution result and SQL execution process is sent to the training set of the deep learning model in an asynchronous manner. The recurrent neural network feeds back to the execution plan module after a certain number of optimizations.

[0051] The generated execution plans are generally divided into the following cases:

[0052] 1. Useless plan matching: Sometimes there may be only one way to execute a query. For example, a heap table can only obtain data through table scan. In order to avoid wasting time optimizing such queries, SQL Server maintains a list of useless plans for selection. If a plan matching the query is found in the useless list during the optimization phase, a similar plan is generated without any further optimization.

[0053] 2. Multi-stage optimization: For complex queries, the number of alternative processing strategies that need to be analyzed may be large, and evaluating each choice may take a long time. Therefore, the optimization phase does not analyze all possible processing strategies, but divides them into several configurations, each with different index and connection techniques.

[0054] Index variants consider different index characteristics, single column index, composite index, index column order, index density, etc. Similarly, join variants consider different join techniques available in the engine: nested loop join, merge join, and hash match.

[0055] Learning optimization considers the statistics of the columns involved in the WHERE clause to evaluate the effectiveness of the index and join strategy. The strategy is statistically evaluated in multiple optimization stages to determine the cost of the configuration. The cost includes many factors, such as the CPU, memory usage, and disk I / O required to execute the query. After each optimization stage, the cost of the processing strategy is evaluated. If the cost is economical enough, stop looping further in the optimization stage and exit the optimization process. Otherwise, continue to loop in the optimization stage to determine a high cost-effective processing strategy.

[0056] Step 3: The engine executes the execution plan generated in step 2. The engine includes Row Based Execution Engine (a row-based execution engine used to execute row-based plans) and Vectorized Execution Engine (a vectorized execution engine used to execute column-based plans). It sends the results of each SQL execution and detailed information of the SQL execution process to the training set of the deep learning model.

[0057] During the execution of the engine, the data (data after SQL execution, some intermediate data and data caches such as data page, index page, redo log, etc.) are placed in the row buffer pool and column index buffer pool in the storage engine. After each SQL execution, the SQL execution results and detailed information of the SQL execution process are sent to the training set of the deep learning model.

[0058] The row-based execution engine mainly processes transaction-related execution plans. It selects operators of different modules (such as full table scan, nested loop join, and other operators) based on the execution plans and determines how many tables need to be operated and the APIs that need to be called for this execution.

[0059] A full table scan in a row-based execution engine is as follows: a table specified by the execution plan is scanned, the expression framework generates corresponding instructions, and calls the corresponding processing API until the required data is found.

[0060] The nested loop join in the row-based execution engine is: a nested loop joins an external data set to an internal data set. For each row of data in the external data set, the database matches all rows in the internal data set that match the predicate condition. If an index is available for the internal data set or internal table at this time, the database will use it to locate the rowid to obtain the data.

[0061] The vectorized execution engine mainly processes execution plans related to data analysis. Most requests are mainly related to query and analysis. Since data storage is multi-dimensional, it is very convenient for data analysis. The commonly used transaction type is a two-dimensional data model. When multiple form sets are required for operation, it is inconvenient to operate and analyze. In addition, through the expression framework, a unique SIMD instruction (single instruction stream multiple data stream) is generated, and the corresponding column index access API is called to improve the overall analysis efficiency.

[0062] The full table scan of the vectorized execution engine is: Since it is a column-based database, even if a full table scan is performed, some required column data can be extracted for analysis, which greatly reduces the amount of data in the full table scan.

[0063] The nested loop join of the vectorized execution engine is as follows: according to the execution plan, the characteristics of the columnar database are fully utilized, and for each row of data in the external data set, only the required columns are associated and the corresponding column data are reassembled.

[0064] In the hash join of the vectorized execution engine, the hash join is mainly divided into two phases: the build phase and the probe phase. The build phase: select a table (generally the smaller table to reduce the time and space for building the hash table), and use the hash function to obtain the hash value of the join attribute on each tuple in it, thereby building a hash table. The probe phase: for the other table, scan each of its rows and calculate the hash value of the join attribute, and compare it with the hash table built in the build phase. If there are any that fall into the same bucket, if the join predicate is satisfied, they are connected to a new table. When the memory is large enough, the entire table is in the memory during the process of building the hash table, and it is not put on the disk until the join operation is completed. However, this process will also bring a lot of I / O operations.

[0065] The aggregation grouping of the vectorized execution engine is: Aggregation grouping (GROUP BY) is very common. GROUP BY combined with all, cube, and rowup is a common OLAP (online analysis) function, which is very convenient to use. The above OALP series functions can achieve many functions in OLAP scenarios such as database reports, statistical analysis, and warehouse processing. When combined with window functions, they will surely play a greater role.

[0066] Step 4: The deep learning model learns and trains based on the training set, and then feeds back the results of model learning and training (the best optimization solution) to the execution plan module. After receiving the feedback from the deep learning model, the execution plan module automatically optimizes the execution plan and applies it to the next SQL query.

[0067] The execution plan module and the deep learning model predefine the interaction protocol and the data structure of the execution plan in advance. These data structures include the abstract syntax tree, access type type, table connection matching condition ref, etc.

[0068] The deep learning model learns and trains the training set data sent by the engine in step 3, quantifies and designs the points that can be fed back (such as the height estimation of the abstract syntax tree, access type, etc.), and finally feeds back the results of model learning and training to the execution plan module. After receiving the feedback from the deep learning model, the execution plan module directly modifies the corresponding execution plan data structure in the abstract syntax tree in memory for optimization, ensuring that the next query can be applied to this optimization feedback, and promptly writes the optimization information to the disk for developers to analyze.

[0069] Figure 2 The principle of an embodiment of the system for deep learning to participate in HTAP database SQL optimization of the present invention is shown. Figure 2, the system of this embodiment includes: a SQL parser, an execution plan module, an engine module and a deep learning model.

[0070] The SQL parser is used to receive SQL execution statements and check whether the SQL execution statements comply with the specifications, usually including checking whether the SQL format and syntax are correct.

[0071] The execution plan module is used to generate an execution plan through a deep learning model based on SQL statements that meet the specifications after being checked by the SQL parser.

[0072] The execution plan module first generates a logical layer plan, and then generates a physical layer plan based on the logical layer plan. Since the HTAP database is a mixed row and column database, the physical layer plan is divided into a row-based plan and a column-based plan.

[0073] The specific process of the execution plan module generating an execution plan based on the deep learning model is as follows.

[0074] The execution plan module uses a recurrent neural network structure (the recurrent neural network includes an input layer, a hidden layer, and an output layer). The input layer inputs the SQL execution results and detailed information of the SQL execution process, the hidden layer performs cost calculation, and the output layer outputs the SQL statement and the optimal execution plan.

[0075] In the cost calculation of the hidden layer, cost estimation is performed based on the detailed information of the SQL execution results and the SQL execution process. During the loop iteration process (the number of iterations is preset in advance), the loss function parameters are continuously adjusted to reduce the cost. When the reduced cost is better than the expected value of the cost estimate, the optimization result of the loop iteration is constructed into a data structure of a defined execution plan and fed back to the execution plan module.

[0076] The above cost estimation is divided into two stages, one is the cost estimation analysis, and the other is the learning optimization stage.

[0077] The cost estimation analysis phase includes multiple execution scenarios, such as full table scan estimation, common index estimation, and multi-table join query. The deep learning model makes an overall estimation of each of the above different execution scenarios to obtain the expected value of the cost estimation, which includes IO cost (number of IO times), CPU computing cost, and memory usage cost.

[0078] In the subsequent learning optimization stage, the weight parameters of the deep learning model are continuously optimized using a recurrent neural network based on the training set data of the deep learning model, and compared with the expected values ​​of the cost estimation analysis stage. If the result of the learning optimization is better than the expected value of the cost estimation analysis, it will be fed back to the execution plan module after a certain number of learning times, and the optimization will be continuously iterated.

[0079] The learning optimization phase is decoupled from SQL execution. The detailed information of each SQL execution result and SQL execution process is sent to the training set of the deep learning model in an asynchronous manner. The recurrent neural network feeds back to the execution plan module after a certain number of optimizations.

[0080] The generated execution plans are generally divided into the following cases:

[0081] 3. Useless plan matching: Sometimes there may be only one way to execute a query. For example, a heap table can only obtain data through table scanning. In order to avoid wasting time optimizing such queries, SQL Server maintains a list of useless plans for selection. If a plan matching the query is found in the useless list during the optimization phase, a similar plan is generated without any further optimization.

[0082] 4. Multi-stage optimization: For complex queries, the number of alternative processing strategies that need to be analyzed may be large, and evaluating each choice may take a long time. Therefore, the optimization phase does not analyze all possible processing strategies, but divides them into several configurations, each with different index and connection techniques.

[0083] Index variants consider different index characteristics, single column index, composite index, index column order, index density, etc. Similarly, join variants consider different join techniques available in the engine: nested loop join, merge join, and hash match.

[0084] Learning optimization considers the statistics of the columns involved in the WHERE clause to evaluate the effectiveness of the index and join strategy. The strategy is statistically evaluated in multiple optimization stages to determine the cost of the configuration. The cost includes many factors, such as the CPU, memory usage, and disk I / O required to execute the query. After each optimization stage, the cost of the processing strategy is evaluated. If the cost is economical enough, stop looping further in the optimization stage and exit the optimization process. Otherwise, continue to loop in the optimization stage to determine a high cost-effective processing strategy.

[0085] The engine module is used to execute the generated execution plan and send the detailed information of each SQL execution result and SQL execution process to the training set of the deep learning model.

[0086] The engine includes the Row Based Execution Engine (used to execute row-based plans) and the Vectorized Execution Engine (used to execute column-based plans), which sends the results of each SQL execution and detailed information of the SQL execution process to the training set of the deep learning model.

[0087] During the execution of the engine, the data (data after SQL execution, some intermediate data and data caches such as data page, index page, redo log, etc.) are placed in the row buffer pool and column index buffer pool in the storage engine. After each SQL execution, the SQL execution results and detailed information of the SQL execution process are sent to the training set of the deep learning model.

[0088] The row-based execution engine mainly processes transaction-related execution plans. It selects operators of different modules (such as full table scan, nested loop join, and other operators) based on the execution plans and determines how many tables need to be operated and the APIs that need to be called for this execution.

[0089] A full table scan in a row-based execution engine is as follows: a table specified by the execution plan is scanned, the expression framework generates corresponding instructions, and calls the corresponding processing API until the required data is found.

[0090] The nested loop join in the row-based execution engine is: a nested loop joins an external data set to an internal data set. For each row of data in the external data set, the database matches all rows in the internal data set that match the predicate condition. If an index is available for the internal data set or internal table at this time, the database will use it to locate the rowid to obtain the data.

[0091] The vectorized execution engine mainly processes execution plans related to data analysis. Most requests are mainly related to query and analysis. Since data storage is multi-dimensional, it is very convenient for data analysis. The commonly used transaction type is a two-dimensional data model. When multiple form sets are required for operation, it is inconvenient to operate and analyze. In addition, through the expression framework, a unique SIMD instruction (single instruction stream multiple data stream) is generated, and the corresponding column index access API is called to improve the overall analysis efficiency.

[0092] The full table scan of the vectorized execution engine is: Since it is a column-based database, even if a full table scan is performed, some required column data can be extracted for analysis, which greatly reduces the amount of data in the full table scan.

[0093] The nested loop join of the vectorized execution engine is as follows: according to the execution plan, the characteristics of the columnar database are fully utilized, and for each row of data in the external data set, only the required columns are associated and the corresponding column data are reassembled.

[0094] In the hash join of the vectorized execution engine, the hash join is mainly divided into two phases: the build phase and the probe phase. The build phase: select a table (generally the smaller table to reduce the time and space for building the hash table), and use the hash function to obtain the hash value of the join attribute on each tuple in it, thereby building a hash table. The probe phase: for the other table, scan each of its rows and calculate the hash value of the join attribute, and compare it with the hash table built in the build phase. If there are any that fall into the same bucket, if the join predicate is satisfied, they are connected to a new table. When the memory is large enough, the entire table is in the memory during the process of building the hash table, and it is not put on the disk until the join operation is completed. However, this process will also bring a lot of I / O operations.

[0095] The aggregation grouping of the vectorized execution engine is: Aggregation grouping (GROUP BY) is very common. GROUP BY combined with all, cube, and rowup is a common OLAP (online analysis) function, which is very convenient to use. The above OALP series functions can achieve many functions in OLAP scenarios such as database reports, statistical analysis, and warehouse processing. When combined with window functions, they will surely play a greater role.

[0096] The deep learning model learns and trains based on the training set, and then feeds back the results of model learning and training to the execution plan module in the form of the best optimization solution, so that after receiving the feedback from the deep learning model, the execution plan module automatically optimizes the execution plan and applies it to the next SQL query.

[0097] The execution plan module and the deep learning model predefine the interaction protocol and the data structure of the execution plan in advance. These data structures include the abstract syntax tree, access type type, table connection matching condition ref, etc.

[0098] The deep learning model learns and trains the training set data sent by the engine module, quantifies and designs the points that can be fed back (such as the height estimation of the abstract syntax tree, access type, etc.), and finally feeds back the results of model learning and training to the execution plan module. After receiving the feedback from the deep learning model, the execution plan module directly modifies the corresponding execution plan data structure in the abstract syntax tree in the memory for optimization, ensuring that the next query can be applied to this optimization feedback, and promptly writes the optimization information to the disk for developers to analyze.

[0099] Although the above methods are illustrated and described as a series of actions for simplicity of explanation, it should be understood and appreciated that these methods are not limited by the order of the actions, because according to one or more embodiments, some actions may occur in a different order and / or concurrently with other actions from those illustrated and described herein or not illustrated and described herein but understandable to those skilled in the art.

[0100] Those skilled in the art will further appreciate that the various illustrative logic blocks, modules, circuits, and algorithm steps described in conjunction with the embodiments disclosed herein may be implemented as electronic hardware, computer software, or a combination of the two. To clearly illustrate this interchangeability of hardware and software, various illustrative components, blocks, modules, circuits, and steps are generally described above in terms of their functionality. Whether such functionality is implemented as hardware or software depends on the specific application and the design constraints imposed on the overall system. The technician may implement the described functionality in different ways for each specific application, but such implementation decisions should not be interpreted as resulting in a departure from the scope of the present invention.

[0101] The various illustrative logic blocks, modules, and circuits described in conjunction with the embodiments disclosed herein may be implemented or performed with a general purpose processor, a digital signal processor (DSP), an application specific integrated circuit (ASIC), a field programmable gate array (FPGA) or other programmable logic device, discrete gate or transistor logic, discrete hardware components, or any combination thereof designed to perform the functions described herein. A general purpose processor may be a microprocessor, but in the alternative, the processor may be any conventional processor, controller, microcontroller, or state machine. The processor may also be implemented as a combination of computing devices, such as a combination of a DSP and a microprocessor, a plurality of microprocessors, one or more microprocessors in cooperation with a DSP core, or any other such configuration.

[0102] The steps of the method or algorithm described in conjunction with the embodiments disclosed herein may be embodied directly in hardware, in a software module executed by a processor, or in a combination of the two. The software module may reside in a RAM memory, a flash memory, a ROM memory, an EPROM memory, an EEPROM memory, a register, a hard disk, a removable disk, a CD-ROM, or any other form of storage medium known in the art. An exemplary storage medium is coupled to a processor so that the processor can read and write information from / to the storage medium. In an alternative, a storage medium may be integrated into a processor. The processor and the storage medium may reside in an ASIC. The ASIC may reside in a user terminal. In an alternative, the processor and the storage medium may reside in a user terminal as discrete components.

[0103] In one or more exemplary embodiments, the functions described may be implemented in hardware, software, firmware, or any combination thereof. If implemented as a computer program product in software, each function may be stored on or transmitted by a computer-readable medium as one or more instructions or codes. Computer-readable media include both computer storage media and communication media, including any medium that facilitates the transfer of a computer program from one place to another. Storage media may be any available medium that can be accessed by a computer. As an example and not limitation, such a computer-readable medium may include RAM, ROM, EEPROM, CD-ROM or other optical disk storage, disk storage or other magnetic storage device, or any other medium that can be used to carry or store the desired program code in the form of an instruction or data structure and can be accessed by a computer. Any connection is also properly referred to as a computer-readable medium. For example, if the software is transmitted from a website, server, or other remote source using a coaxial cable, fiber optic cable, twisted pair, digital subscriber line (DSL), or wireless technologies such as infrared, radio, and microwaves, the coaxial cable, fiber optic cable, twisted pair, DSL, or wireless technologies such as infrared, radio, and microwaves are included in the definition of the medium. Disk and disc as used herein include compact disc (CD), laser disc, optical disc, digital versatile disc (DVD), floppy disk and Blu-ray disc, wherein disk often reproduces data magnetically, while disc reproduces data optically with lasers. Combinations of the above should also be included within the scope of computer-readable media.

[0104] The previous description of the disclosure is provided to enable any person skilled in the art to make or use the disclosure. Various modifications to the disclosure will be apparent to those skilled in the art, and the general principles defined herein may be applied to other variations without departing from the spirit or scope of the disclosure. Thus, the disclosure is not intended to be limited to the examples and designs described herein, but should be granted the widest scope consistent with the principles and novel features disclosed herein.

Claims

1. A method for optimizing SQL execution in HTAP database using deep learning, characterized in that: Methods include: Step 1: The SQL parser receives the SQL execution statement and checks whether the SQL execution statement complies with the specification; Step 2: The execution plan module generates an execution plan through a deep learning model based on the SQL statements that meet the specifications after being checked by the SQL parser; Step 3: The engine executes the execution plan generated in step 2 and sends the results of each SQL execution and detailed information of the SQL execution process to the training set of the deep learning model; Step 4: The deep learning model learns and trains based on the training set, and then feeds back the results of the model learning and training to the execution plan module in the form of the best optimization solution. After receiving the feedback from the deep learning model, the execution plan module automatically optimizes the execution plan and applies it to the next SQL query; Among them, the execution plan module in step 2 generates an execution plan based on the deep learning model further including: The execution plan module uses the input layer, hidden layer, and output layer of the recurrent neural network structure. The input layer inputs the SQL execution results and detailed information of the SQL execution process, the hidden layer performs cost calculation, and the output layer outputs the SQL statement and the best execution plan. In the cost calculation of the hidden layer, the cost is estimated for the detailed information of the SQL execution results and the SQL execution process. In the process of loop iteration, the loss function parameters are continuously adjusted to reduce the cost. When the reduced cost is better than the expected value of the cost estimate, the optimization result of the loop iteration is constructed into a defined execution plan data structure and fed back to the execution plan module. Wherein, step 4 further comprises: The interaction protocol between the execution plan module and the deep learning model is predefined, and the data structure of the execution plan is defined in advance; The deep learning model learns and trains the training set data sent by the engine in step 3, quantifies and designs the points that can be fed back, and finally feeds back the results of model learning and training to the execution plan module, so that after receiving the feedback from the deep learning model, the execution plan module directly modifies the corresponding execution plan data structure in the abstract syntax tree in the memory for optimization, ensuring that the next query can be applied to this optimization feedback.

2. The method for executing SQL optimization by using deep learning to participate in HTAP database according to claim 1, characterized in that: The execution plan generated in step 2 is divided into a row-based plan and a column-based plan, and the engine in step 3 is divided into a row-based execution engine to execute the row-based plan and a vectorized execution engine to execute the column-based plan.

3. A system for deep learning to participate in HTAP database SQL optimization, characterized in that: The system includes: SQL parser, used to receive SQL execution statements and check whether the SQL execution statements comply with the specifications; The execution plan module is used to generate an execution plan through a deep learning model based on SQL statements that meet the specifications after being checked by the SQL parser; The engine module is used to execute the generated execution plan and send the detailed information of each SQL execution result and SQL execution process to the training set of the deep learning model; The deep learning model learns and trains based on the training set, and then feeds back the results of the model learning and training to the execution plan module in the form of the best optimization solution, so that after receiving the feedback from the deep learning model, the execution plan module automatically optimizes the execution plan and applies it to the next SQL query; Among them, the execution plan generated by the deep learning model of the execution plan module further includes: The execution plan module uses the input layer, hidden layer, and output layer of the recurrent neural network structure. The input layer inputs the SQL execution results and detailed information of the SQL execution process, the hidden layer performs cost calculation, and the output layer outputs the SQL statement and the best execution plan. In the cost calculation of the hidden layer, the execution plan module estimates the cost for the detailed information of the SQL execution results and the SQL execution process, and continuously adjusts the loss function parameters in the process of loop iteration to reduce the cost. When the reduced cost is better than the expected value of the cost estimate, the optimization result of the loop iteration is constructed into a defined execution plan data structure and fed back to the execution plan module. Among them, the deep learning model is further configured as: The interaction protocol between the execution plan module and the deep learning model is predefined, and the data structure of the execution plan is defined in advance; The deep learning model learns and trains the training set data sent by the engine module, quantifies and designs the points that can be fed back, and finally feeds back the results of model learning and training to the execution plan module, so that after receiving the feedback from the deep learning model, the execution plan module directly modifies the corresponding execution plan data structure in the abstract syntax tree in the memory for optimization, ensuring that the next query can be applied to this optimization feedback.

4. The system for executing SQL optimization by participating in HTAP database using deep learning according to claim 3, characterized in that: The execution plan generated by the execution plan module is divided into a row-based plan and a column-based plan, and the engine module is divided into a row-based execution engine to execute the row-based plan and a vectorized execution engine to execute the column-based plan.

Images