Data query optimization method, system, database and electronic device based on machine learning

Abstract

The data query optimization method, system, database and electronic device based on machine learning provided by the present invention include: obtaining an index automatic recommendation model; wherein the index automatic recommendation model is obtained based on feature training of each historical query in the historical query log; obtaining a query statement, and when the query mode of the query statement is a full table scan, predicting the optimal index corresponding to the query statement through the index automatic recommendation model; creating an optimal index, and optimizing the optimal index through a preset enhanced learning mechanism combined with the query performance after using the optimal index. The present invention dynamically recommends the optimal index for the query statement through the index automatic recommendation model, thereby ensuring the accuracy and pertinence of the index. The present invention can also automatically optimize the index through the enhanced learning mechanism, greatly reducing the complexity and labor cost of index management.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a data query optimization method, system, database and electronic device based on machine learning. Background Art

[0002] With the continuous development of database technology, database systems can efficiently manage and store large-scale data sets and support complex query operations. Users or applications can use SQL query statements to obtain the required data from the database. In this process, the database's built-in query optimizer will automatically generate a query plan, display the data access path and optimization suggestions. In order to improve query efficiency, the query optimizer may recommend the use of indexes to speed up data retrieval.

[0003] In the existing technology, if there is no suitable index in the query, the query optimizer may choose to perform a full table scan, which can cause significant performance problems. Therefore, the query optimizer will recommend that users or DBA (Database Administrator) manually create indexes. Database administrators are responsible for creating and deleting indexes and making corresponding adjustments based on query performance feedback. However, this process is time-consuming and requires rich experience, especially as the amount of data grows, manual management of indexes becomes increasingly difficult and difficult to maintain in an optimal state.

[0004] In addition, when faced with certain complex operations, the query optimizer may only give index recommendations based on the execution plan of a single query. This approach is called static index recommendation. However, this approach ignores the overall query pattern and load changes of the database. When the amount of data increases or the query pattern changes, the recommended index may become invalid or suboptimal after the data or query pattern changes, and may even increase the query overhead. Therefore, the existing static index recommendation mechanism cannot adapt to the dynamic needs in complex query environments. Summary of the invention

[0005] In view of this, the purpose of the present invention is to provide a data query optimization method, system, database and electronic device based on machine learning to solve the above technical problems. In order to achieve the above purpose, the technical solution adopted in the embodiment of the present invention is as follows: In a first aspect, the present invention provides a data query optimization method based on machine learning, the method comprising: obtaining an index automatic recommendation model; wherein the index automatic recommendation model is obtained based on feature training of each historical query in a historical query log; obtaining a query statement, and when the query mode of the query statement is a full table scan, predicting the optimal index corresponding to the query statement through the index automatic recommendation model; creating the optimal index, and optimizing the optimal index through a preset reinforcement learning mechanism combined with the query performance after using the optimal index.

[0006] In a second aspect, the present invention provides a data query optimization system based on machine learning, comprising: a query optimizer, a query plan analysis module, a creation module, and an enhanced learning module; the query plan analysis module is used to obtain an index automatic recommendation model; wherein the index automatic recommendation model is obtained based on feature training of each historical query in a historical query log; the query optimizer is used to obtain a query statement; the query plan analysis module is also used to predict the optimal index corresponding to the query statement through the index automatic recommendation model when it is determined that the query mode of the query statement is a full table scan; the creation module is used to create the optimal index; the enhanced learning module is used to optimize the optimal index through a preset enhanced learning mechanism combined with the query performance after using the optimal index.

[0007] In a third aspect, the present invention provides a database, comprising a data query optimization system based on machine learning as described in the aforementioned embodiment, and / or a database for executing a data query optimization method based on machine learning as described in the aforementioned first aspect.

[0008] In a fourth aspect, the present invention provides an electronic device comprising a processor and a memory, wherein the memory stores machine executable instructions that can be executed by the processor, and the processor can execute the machine executable instructions to implement the data query optimization method based on machine learning as described in the first aspect above.

[0009] The data query optimization method, system, database and electronic device based on machine learning provided by the embodiments of the present invention first train an index automatic recommendation model based on the characteristics of historical queries in historical query logs, and use this model to recommend the optimal index for new queries, thereby ensuring the accuracy and pertinence of the index. Secondly, the embodiments of the present invention can accurately judge the query performance after using the index through an enhanced learning mechanism, and automatically optimize the index based on the query performance, which shows that the embodiments of the present invention can automatically implement optimization measures such as deletion, adjustment, and preservation of the index, greatly reducing the complexity and labor cost of index management.

[0010] In order to make the above-mentioned objects, features and advantages of the present invention more obvious and easy to understand, preferred embodiments are given below and described in detail with reference to the accompanying drawings. BRIEF DESCRIPTION OF THE DRAWINGS

[0011] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings required for use in the embodiments are briefly introduced below. It should be understood that the following drawings only show certain embodiments of the present invention and therefore should not be regarded as limiting the scope. For ordinary technicians in this field, other related drawings can be obtained based on these drawings without creative work.

[0012] Figure 1 It is an existing database query optimization method; Figure 2 A schematic flow chart of a data query optimization method based on machine learning provided in an embodiment of the present invention; Figure 3 A schematic diagram illustrating a database working mechanism provided by an embodiment of the present invention; Figure 4 A functional module diagram of a data query optimization system based on machine learning provided by an embodiment of the present invention; Figure 5 This is a structural block diagram of an electronic device provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0013] The following will be combined with the accompanying drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Generally, the components of the embodiments of the present invention described and shown in the drawings here can be arranged and designed in various different configurations.

[0014] Therefore, the following detailed description of the embodiments of the present invention provided in the accompanying drawings is not intended to limit the scope of the invention claimed for protection, but merely represents selected embodiments of the present invention. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without making creative work are within the scope of protection of the present invention.

[0015] It should be noted that relational terms such as "first" and "second" are used only to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Moreover, the terms "include", "comprise" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, the elements defined by the sentence "comprise a ..." do not exclude the existence of other identical elements in the process, method, article or device including the elements.

[0016] See also Figure 1 , Figure 1 This is an existing database query optimization method. The defects of this method can be summarized as follows: 1. Static index recommendation cannot meet dynamic needs: it can only provide index recommendations based on the execution plan of a single query, but cannot comprehensively consider historical query patterns and the dynamic load of the database to flexibly adjust the index. This single static analysis method makes it easy for the recommended index to become inapplicable or even suboptimal when the data volume changes or the query pattern changes.

[0017] For example, taking the MySQL query optimizer as an example, when faced with certain complex JOIN operations, it is easy to generate suboptimal query plans. For example, assuming that there is a complex JOIN relationship between Table A and Table B, if there is a lack of suitable indexes, the MySQL query optimizer will usually recommend creating an index for a specific column. This kind of advice often does not fully consider the overall query pattern and load changes of the database. As the amount of data continues to grow, the originally recommended index may not only fail to improve query performance, but may increase query overhead. This shows that the static index recommendation method is difficult to meet dynamic needs in a complex query environment.

[0018] 2. Index management relies on manual work: Index management mainly relies on manual work by database administrators (DBAs). This method is not only time-consuming and laborious, but also difficult to adapt to changes in query patterns or rapid data growth in a timely manner, resulting in a gradual decline in query performance. Especially in a high-load database environment, where query patterns and data structures change frequently, manual index management becomes extremely inefficient and difficult to meet the needs of dynamic optimization.

[0019] 3. Lack of query pattern learning ability: The existing query optimizer cannot perform effective pattern recognition and learning based on historical query records. For example, when there are a large number of similar queries in the database, the optimizer cannot recognize common query patterns, let alone create indexes for these common queries, making it impossible for the database to automatically optimize indexes based on query history and load conditions, greatly limiting the improvement of database performance.

[0020] 4. Insufficient automatic processing of full table scans: When processing full table scan query patterns, the current query optimizer will output a warning when a full table scan occurs and recommends creating indexes for certain columns. However, this recommendation is based only on a single query, and the query optimizer cannot track the historical performance and frequency of the query pattern, and will not automatically create or delete indexes. This passive processing method is difficult to meet the needs of dynamic database optimization.

[0021] Taking the above technical problems into consideration, an embodiment of the present invention provides a method for designing a dynamic SQL query optimization and index recommendation based on machine learning. Compared with the prior art, it can automatically perform index management and optimization according to the historical query patterns, real-time load and data structure changes of the database.

[0022] See also Figure 2 , Figure 2 A schematic flow chart of a data query optimization method based on machine learning provided in an embodiment of the present invention, the method includes steps S201 to S203, which are described as follows: S201: Obtain an index automatic recommendation model; In the embodiment of the present invention, the index automatic recommendation model is obtained by training based on the features of each historical query in the historical query log; wherein the features may include but are not limited to: grammatical features, query execution time, index usage, query complexity, etc. Syntactic features refer to columns in the query statement, which may be a single column or a combination of columns, such as columns involved in clauses such as WHERE, JOIN, and ORDER BY.

[0023] S202: Obtain a query statement. When the query mode of the query statement is a full table scan, predict the optimal index corresponding to the query statement through the index automatic recommendation model. In the embodiment of the present invention, full table scan is a query mode. For a certain query, if an index needs to be used, but the index does not exist in the database or the index in the database is not suitable, the query mode is full table scan.

[0024] S201: Create an optimal index, and optimize the optimal index by combining the query performance after using the optimal index through a preset reinforcement learning mechanism.

[0025] The above-mentioned solution of step S201 to step S203 provided by the embodiment of the present invention has the following advantages compared with the traditional technology: First, the embodiment of the present invention trains an index automatic recommendation model based on the characteristics of historical queries in the historical query log, and uses this model to recommend the best index for new queries, thereby ensuring the accuracy and pertinence of the index recommendation. Secondly, the embodiment of the present invention can accurately judge the query performance after using the index through the enhanced learning mechanism, and automatically optimize the index based on the query performance, which shows that the embodiment of the present invention can automatically implement optimization measures such as deletion, adjustment, and preservation of the index, greatly reducing the complexity and labor cost of index management.

[0026] Next, the embodiments of the present invention will clearly and in detail illustrate each of the above steps.

[0027] In step S201, the embodiment of the present invention can first extract the columns involved in the WHERE, JOIN and other clauses from the historical query log as grammatical features, and use the query execution time and index usage as labels to train the model. The trained model can automatically predict whether an index needs to be generated based on the grammatical features of the new query, and recommend the optimal index. Therefore, the implementation of step S201 in the embodiment of the present invention includes steps a1 to a2, which are described as follows: Step a1: Obtain the syntax features, query execution time, and index usage mark of each historical query; Step a2: Use grammatical features as model input, query execution time and index usage tags as labels, train the preset machine learning model, and obtain an index automatic recommendation model.

[0028] In one embodiment of the present invention, the database system may extract the columns involved in each historical query from the historical query log, which may be a single column or a combination of columns, and convert these columns into vector representations to obtain grammatical features.

[0029] Optionally, the query type (SELECT, INSERT, UPDATE, etc.) of each query, the names of the tables and fields involved, the expected number of rows of query return results, and the current status information of the database, such as changes in table size, updates to field data distribution, etc., can also be incorporated into the grammatical features as dynamic factors.

[0030] In an optional implementation, the database system may first use principal component analysis (PCA) and Laplace smoothing to eliminate noise features during the process of extracting grammatical features.

[0031] In an embodiment of the present invention, the query execution time of each historical query is used as a performance indicator, and the index usage mark is used as a label to train the preset machine learning model. As an example, when the index usage mark is 1, it means that the historical query needs an index; when the index usage mark is 0, it means that the historical query does not need an index.

[0032] In an embodiment of the present invention, the machine learning model may be, but is not limited to, a random forest model.

[0033] As an example, if it is a random forest model, then the embodiment of the present invention can build a model through ensemble learning. In which, each tree uses an adaptively adjusted depth, combined with a greedy algorithm and particle swarm optimization (PSO) to determine the optimal hyperparameters. Specifically: During the training process, consider the impact of changes in query patterns on index requirements. For example, if the query frequency of a field suddenly increases, you may need to create an index for that field. You can record the timestamp of the query and consider the timing of the query to capture the trend of query patterns changing over time. Calculate statistics such as the execution time of the query and the number of rows returned. You can also consider the table size and field type of the database and build feature engineering based on this information.

[0034] In the process of training with random forest as the basic model, the training set data is used to perform initial training on the random forest. The depth of each tree is randomly generated within the initial range, and then the tree structure is constructed layer by layer. During the training process, the performance of the model is monitored in real time. At the same time, the preset optimization algorithm is started to iteratively optimize the hyperparameters. Each iteration generates a new set of hyperparameter combinations, and then the random forest model is retrained. By comparing the model performance under different hyperparameter combinations, the best performing hyperparameter combination is selected.

[0035] After training, the model can predict whether each query requires a new index and recommend the optimal index. By dynamically generating indexes through machine learning, the database system can adaptively handle changes in query patterns and avoid the limitations of static indexes.

[0036] In one embodiment of the present invention, it is considered that the existing query optimizer cannot perform pattern recognition and learning based on historical queries. For example, when there are a large number of similar queries in the database, the query optimizer will not recognize this pattern, nor will it automatically create composite indexes for these common patterns. This design that lacks learning ability makes it impossible for existing databases to automatically optimize indexes based on query history and load. Therefore, the index automatic recommendation model provided by the embodiment of the present invention can not only predict whether a new query requires an index and generate the optimal index, but also can learn past query behaviors based on the analysis of historical query patterns, and automatically recommend the optimal index combination based on query frequency. The embodiment of the present invention may also include: Step b1: Analyze various historical query modes in the historical query log, and identify columns in each historical query mode whose usage frequency exceeds a preset second frequency threshold; Step b2: Generate indexes for columns whose usage frequency exceeds a preset second frequency threshold using an automatic index recommendation model.

[0037] The embodiment of the present invention realizes regular analysis of historical queries through the above implementation methods, ensuring the effect of continuous optimization of indexes.

[0038] In step S202, the query statement is issued by the user or application to the database, and the query optimizer analyzes whether it corresponds to the full table scan mode. Specifically, the query optimizer analyzes the execution plan of the query statement through the EXPLAIN instruction to obtain the execution path of the data. If it is detected that there is no index or the index is not suitable, the query mode is determined to be a full table scan.

[0039] In the embodiment of the present invention, when the query mode is a full table scan, the database system can generate the optimal index through the index automatic recommendation model, which can be implemented as follows: Step c1: input the grammatical features corresponding to the query sentence into the index automatic recommendation model; Step c2: The index automatic recommendation model determines the columns that need to be indexed in the query statement based on the grammatical features; Step c3: Recommend the optimal index based on the columns that need to be indexed.

[0040] Through the above implementation, the embodiment of the present invention can use the index automatic recommendation model to dynamically predict future query patterns by analyzing the features in the historical query logs. The model analyzes the column combination, query frequency, and query complexity in each query and recommends the optimal single column or composite index. The system can continuously optimize index management without manual intervention and realize intelligent recommendation and automatic generation of indexes.

[0041] In one embodiment of the present invention, for the query mode of full table scan, the existing technology cannot automatically recommend indexes or actively optimize query performance, and can only prompt the DBA to manually optimize. In other words, when a full table scan problem occurs, the query optimizer usually outputs a warning and recommends creating indexes for certain columns. However, this suggestion is based on only one query, and the optimizer cannot track the historical performance and frequency of the query, nor will it automatically create or delete indexes.

[0042] In view of this, an embodiment of the present invention provides an implementation method that can automatically generate indexes for columns that cause full table scans in combination with historical query logs to reduce the occurrence of full table scans. This process does not require manual intervention, ensuring that the system can dynamically handle performance bottlenecks in queries. As shown below: Step d1: Identify the columns that cause a full table scan from the query statement; Step d2: Obtain historical query patterns associated with the columns that cause full table scans from historical query logs; Step d3: Identify a target historical query in a historical query mode that includes both columns that cause a full table scan and other columns whose usage frequency exceeds a preset first frequency threshold; In the embodiment of the present invention, the first frequency threshold can be flexibly set according to actual needs.

[0043] As an optional implementation, the embodiment of the present invention can introduce a K-means clustering algorithm to perform query pattern recognition on historical query logs, find frequently used column combinations, and automatically generate indexes or composite indexes for these columns.

[0044] Optionally, an embodiment of the present invention may convert columns in clauses such as WHERE and JOIN in each historical query into vectors as query features of each historical query, cluster these query features through the K-means algorithm, and identify frequently occurring column combinations.

[0045] Step d4: Generate indexes for the target historical query on the columns that cause the full table scan and other columns whose usage frequencies exceed a preset first frequency threshold.

[0046] For ease of understanding, for example: when a user queries a large table products, suppose a query is SELECT*FROMproductsWHEREcategory ='electronics'. Due to the lack of a suitable index, this query will result in a full table scan. The system finds through the EXPLAIN statement that the execution plan of the query includes a full table scan (ALL). At this time, the system will analyze all query patterns involving the category column in combination with historical query logs. Through the K-means clustering algorithm, the system recognizes that category and other frequently used columns (such as brand) often appear together in the WHERE clause, so the automatically generated index is a composite index (category, brand) to avoid full table scans and improve query performance.

[0047] Through the above implementation, for commonly used column combinations, the embodiment of the present invention can automatically generate composite indexes to improve the performance of complex queries. Through this pattern recognition, the database system can optimize indexes without administrator intervention to improve the overall query performance of the system.

[0048] In one embodiment of the present invention, the database system may also periodically retrain the index automatic recommendation model to ensure that the model can adapt to new query patterns at any time.

[0049] Next, the embodiment of the present invention will introduce the process of managing and optimizing the index in step S203.

[0050] In step 203, the embodiment of the present invention may optimize the index through a preset reinforcement learning mechanism according to the query performance after using the index.

[0051] It is understandable that the database system can monitor the feedback data after executing the query, and the feedback data may include but is not limited to: query execution time, CPU usage, etc. Based on these feedback data, the change in query performance of each query using the index can be analyzed, and then the index can be optimized. Therefore, the implementation of step S203 includes: Step e1: Determine whether the query performance is improved based on the performance feedback data; Step e2: If yes, keep the index in the database and increase the index priority; if no, delete or adjust the index.

[0052] In an embodiment of the present invention, the reinforcement learning mechanism analyzes whether the query performance is improved or reduced after using the index based on the feedback data, and then optimizes the index based on a preset reward mechanism or penalty mechanism. For example, a shortened query execution time can be regarded as positive feedback, and vice versa as negative feedback. For positive feedback, the reinforcement learning mechanism can reward the index, that is, retain the index, and strengthen its application, such as increasing the priority of the index. For negative feedback, a penalty mechanism is implemented for the index, that is, the index is adjusted or deleted.

[0053] In an optional implementation, the reinforcement learning mechanism may be, but is not limited to, an adaptive incremental learning mechanism.

[0054] The embodiment of the present invention combines the feedback results and uses the reinforcement learning mechanism to continuously update the index, giving priority to retaining effective indexes and deleting infrequently used indexes. Through this mechanism, the database system can dynamically adjust the index when the query mode changes, ensuring that the system can adaptively optimize the index in the long term, especially when the data and query mode change, it can still maintain efficient performance.

[0055] To facilitate an overall understanding of the method provided by the embodiment of the present invention, see Figure 3 , Figure 3 A schematic diagram illustrating a database working mechanism provided in an embodiment of the present invention.

[0056] Users or applications submit SQL queries through the system. After the SQL query statement is entered into the system, the query optimizer analyzes and obtains the execution plan of the SQL query statement, that is, the query EXPLAIN tool is used to analyze the execution plan of the SQL query. If a suitable index exists, it is used for the SQL query statement. If the query has successfully used the index and executed well, the optimization is skipped directly. When it is determined that there is a full table scan mode in the query execution plan, the index automatic recommendation model recommends the optimal index for the query statement. The optimal index is created and used for the SQL query statement, and the query performance feedback data of the SQL query statement using the optimal index is monitored. If the performance improves, the index is retained and the optimal index is automatically optimized. If the performance deteriorates, the index is deleted or adjusted.

[0057] In summary, the data query optimization method based on machine learning provided by the embodiment of the present invention has the following technical effects: 1. Automated Recommendation Index The present invention uses a machine learning model (such as a random forest model) to analyze the query features of historical queries and intelligently recommend the best index for new queries. Compared with the traditional manual index management method, the embodiment of the present invention breaks through the limitations of traditional static index recommendation, realizes the automation and intelligence of database index management, solves the problem of manual intervention in database index management, and realizes dynamic index recommendation and adaptive optimization through machine learning.

[0058] 2. Dynamically optimize indexes This invention introduces a reinforcement learning mechanism, which enables the system to dynamically adjust the index according to the execution results of each query. The system automatically removes indexes that are no longer frequently used by continuously rewarding or punishing indexes, and generates new indexes for high-frequency queries. This mechanism ensures that the database can continue to run efficiently and flexibly adapt to different query patterns and load changes.

[0059] 3. Automatic index optimization in full table scan mode When the system finds a full table scan, the embodiment of the present invention combines the K-means clustering algorithm to analyze historical query patterns and generates targeted composite indexes or single column indexes based on column combinations. This technology effectively avoids performance issues caused by full table scans, improves database performance through automatic detection and index recommendation, and solves the bottleneck problem that traditional systems cannot automatically respond to full table scans.

[0060] 4. Dynamic index management driven by enhanced learning The present invention introduces a reinforcement learning mechanism, which enables the database system to evaluate the index according to the performance after each query execution, give priority to retaining effective indexes, and delete infrequently used indexes. It realizes the automated processing of index creation, deletion, and optimization, reducing the manual operation requirements of database administrators (DBAs). The system dynamically adjusts the index according to the query mode and load changes, reducing manual intervention.

[0061] 5. Reduce resource consumption and improve overall performance The present invention not only significantly improves query performance, but also effectively reduces the waste of resources caused by inapplicable indexes. By automatically deleting indexes that are no longer used, the system reduces the storage space occupied by redundant indexes, thereby reducing the maintenance cost of the database. The enhanced learning mechanism ensures that each index has practical utility, thereby optimizing the overall resource utilization of the system.

[0062] In general, the embodiments of the present invention are applicable to high-frequency queries, complex queries and dynamic query modes, and perform particularly well in large-scale data processing and complex application scenarios, solving the performance bottleneck problem of traditional systems in dynamic query environments.

[0063] Based on Figure 2 With the same inventive concept, the embodiment of the present invention also provides a data query optimization system 40 based on machine learning. Figure 4 , Figure 4 The functional module diagram of the data query optimization system based on machine learning provided by the embodiment of the present invention may include: a query optimizer 401, a query plan analysis module 402, a creation module 403 and an enhanced learning module 404; The query plan analysis module 402 is used to obtain an index automatic recommendation model; wherein the index automatic recommendation model is obtained based on feature training of each historical query in the historical query log; The query optimizer 401 is used to obtain a query statement; The query plan analysis module 402 is further configured to predict the optimal index corresponding to the query statement through the index automatic recommendation model when determining that the query mode of the query statement is a full table scan; The creation module 403 is used to create the optimal index; The enhanced learning module 404 is used to optimize the optimal index by combining the query performance after using the optimal index through a preset enhanced learning mechanism.

[0064] It is understandable that the query optimizer 401, the query plan analysis module 402, the creation module 403 and the enhanced learning module 404 can be executed in a coordinated manner. Figure 2 Each step in the process is performed to achieve the corresponding technical effect.

[0065] It should be noted that the division of modules in the above embodiments of the present application is schematic and is only a logical function division. There may be other division methods in actual implementation. In addition, each functional unit in each embodiment of the present application may be integrated into a processing unit, or may exist physically separately, or two or more units may be integrated into one unit. The above-mentioned integrated unit may be implemented in the form of hardware or in the form of a software functional unit.

[0066] If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present application is essentially or the part that contributes to the prior art or all or part of the technical solution can be embodied in the form of a software product, and the computer software product is stored in a storage medium, including a number of instructions to enable an electronic device (which can be a personal computer, a server, or a network device, etc.) or a processor to perform all or part of the steps of the various embodiments of the present application. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (Read-Only Memory, ROM), random access memory (Random Access Memory, RAM), disk or optical disk and other media that can store program executable code.

[0067] The embodiment of the present invention further provides a database, the database comprising Figure 4 The machine learning-based data query optimization system 40 shown, and / or is used to execute the machine learning-based data query optimization method provided in an embodiment of the present invention.

[0068] The present invention also provides an electronic device, see Figure 5 , Figure 5 The structural block diagram of the electronic device provided for the embodiment of the present invention includes: a memory 501, a processor 502, and a communication interface 503. The memory 501, the processor 502 and the communication interface 503 are electrically connected to each other directly or indirectly to realize data transmission or interaction. For example, these components can be electrically connected to each other through one or more communication buses or signal lines. Optionally, the bus can be a Peripheral Component Interconnect (PCI) bus or an Extended Industry Standard Architecture (EISA) bus, etc. The bus can be divided into an address bus, a data bus, a control bus, etc. For ease of representation, Figure 5 Only one thick line is used in the diagram, but this does not mean that there is only one bus or only one type of bus.

[0069] In the embodiment of the present invention, the processor 502 may be a general-purpose processor, a digital signal processor, an application-specific integrated circuit, a field programmable gate array or other programmable logic device, a discrete gate or transistor logic device, or a discrete hardware component, and may implement or execute the methods, steps, and logic block diagrams disclosed in the embodiment of the present invention. A general-purpose processor may be a microprocessor or any conventional processor, etc. The steps of the method disclosed in the embodiment of the present invention may be directly embodied as being executed by a hardware processor, or may be executed by a combination of hardware and software modules in the processor. The software module may be located in the memory 501, and the processor 502 reads the program instructions in the memory 501 and completes the steps of the above method in combination with its hardware.

[0070] In the embodiment of the present invention, the memory 501 may be a non-volatile memory, such as a hard disk (HDD) or a solid-state drive (SSD), etc., or a volatile memory (Volatile Memory), such as RAM. The memory may also be any other medium that can be used to carry or store the desired program executable code in the form of instructions or data structures and can be accessed by a computer, but is not limited thereto. The memory in the embodiment of the present invention may also be a circuit or any other device that can realize a storage function, for storing instructions and / or data.

[0071] The memory 501 can be used to store software programs and modules, such as the instructions / modules of the data query optimization system 40 based on machine learning provided in the embodiment of the present invention, which can be stored in the memory 501 in the form of software or firmware (Firmware) or in the operating system (OS) of the solidified electronic device 50. The processor 502 executes various functional applications and data processing by executing the software programs and modules stored in the memory 501. The communication interface 503 can be used to communicate signaling or data with other node devices.

[0072] Those skilled in the art can clearly understand that, for the convenience and brevity of description, the specific working processes of the above-described devices and units can refer to the corresponding processes in the aforementioned method embodiments and will not be repeated here.

[0073] Understandably, Figure 5 The structure shown is for illustration only. The electronic device 50 may also include Figure 5 More or fewer components as shown, or with Figure 5 Different configurations are shown. Figure 5 The components shown may be implemented in hardware, software or a combination thereof.

[0074] Based on the above embodiments, the present application also provides a storage medium, in which a computer program is stored. When the computer program is executed by a computer, the computer executes the data query optimization method based on machine learning provided in the above embodiments.

[0075] Based on the above embodiments, an embodiment of the present invention further provides a computer program. When the computer program runs on a computer, the computer executes the data query optimization method based on machine learning provided in the above embodiments.

[0076] Based on the above embodiments, an embodiment of the present invention further provides a chip, which is used to read a computer program stored in a memory and to execute the data query optimization method based on machine learning provided in the above embodiments.

[0077] A computer program product is also provided in an embodiment of the present invention, including instructions, which, when executed on a computer, enable the computer to execute the data query optimization method based on machine learning provided in the above embodiment.

[0078] The embodiments of the present invention are described with reference to the flowcharts and / or block diagrams of the methods, devices (systems), and computer program products according to the embodiments of the present invention. It should be understood that each process and / or block in the flowchart and / or block diagram, as well as the combination of the processes and / or blocks in the flowchart and / or block diagram, can be implemented by instructions. These instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to generate a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0079] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0080] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

[0081] The above are only specific embodiments of the present invention, but the protection scope of the present invention is not limited thereto. Any changes or substitutions that can be easily thought of by a person skilled in the art within the technical scope disclosed by the present invention should be included in the protection scope of the present invention. Therefore, the protection scope of the present invention should be based on the protection scope of the claims.

Claims

1. A data query optimization method based on machine learning, characterized in that: The method comprises: Obtaining an index automatic recommendation model; wherein the index automatic recommendation model is obtained based on feature training of each historical query in the historical query log; Obtain a query statement, and when the query mode of the query statement is a full table scan, predict the optimal index corresponding to the query statement through the index automatic recommendation model; The optimal index is created, and the optimal index is optimized by combining the query performance after using the optimal index through a preset reinforcement learning mechanism.

2. The data query optimization method based on machine learning according to claim 1 is characterized in that: When the query mode of the query statement is a full table scan, predicting the optimal index corresponding to the query statement through the index automatic recommendation model includes: Inputting the grammatical features corresponding to the query statement into the index automatic recommendation model; The automatic index recommendation model determines the columns in the query statement that need to use the index based on the grammatical features; The optimal index is recommended based on the column that needs to use the index.

3. The data query optimization method based on machine learning according to claim 1 or 2, characterized in that: Get the index automatic recommendation model, including: Obtaining grammatical features, query execution time, and index usage marks of each of the historical queries; The grammatical features are used as model inputs, and the query execution time and the index usage mark are used as labels to train a preset machine learning model to obtain the index automatic recommendation model.

4. The data query optimization method based on machine learning according to claim 1 is characterized in that: The method further comprises: Identifying, from the query statement, a column that causes the full table scan to occur; Obtaining, from a historical query log, a historical query pattern associated with the column causing the full table scan; Identify a target historical query in the historical query mode that includes both the column that causes the full table scan and other columns whose usage frequency exceeds a preset first frequency threshold; An index is generated for the target historical query on the column causing the full table scan and other columns whose usage frequency exceeds a preset first frequency threshold.

5. The data query optimization method based on machine learning according to claim 1 is characterized in that: The method further comprises: Analyze various historical query modes in the historical query log, and identify columns whose usage frequency exceeds a preset second frequency threshold in each historical query mode; The index automatic recommendation model is used to generate an index for the column whose usage frequency exceeds a preset second frequency threshold.

6. The data query optimization method based on machine learning according to claim 1 is characterized in that: Optimizing the optimal index by combining the query performance after using the optimal index through a preset reinforcement learning mechanism includes: Determining whether the query performance is improved according to the performance feedback data; If so, the index is retained in the database and the priority of the index is increased; if not, the index is deleted or adjusted.

7. The data query optimization method based on machine learning according to claim 1 is characterized in that: The method further comprises: The index automatic recommendation model is updated regularly.

8. A data query optimization system based on machine learning, characterized in that: include: Query optimizer, query plan analysis module, creation module, and enhanced learning module; The query plan analysis module is used to obtain an index automatic recommendation model; wherein the index automatic recommendation model is obtained based on feature training of each historical query in the historical query log; The query optimizer is used to obtain a query statement; The query plan analysis module is further configured to predict the optimal index corresponding to the query statement through the index automatic recommendation model when determining that the query mode of the query statement is a full table scan; The creation module is used to create the optimal index; The reinforcement learning module is used to optimize the optimal index by combining the query performance after using the optimal index through a preset reinforcement learning mechanism.

9. A database, characterized in that: The database includes the machine learning-based data query optimization system as described in claim 8, and / or is used to execute the machine learning-based data query optimization method as described in any one of claims 1-7.

10. An electronic device, characterized in that: It includes a processor and a memory, wherein the memory stores machine executable instructions that can be executed by the processor, and the processor can execute the machine executable instructions to implement the data query optimization method based on machine learning as described in any one of claims 1-7.

Images