CN116881230A - Automatic relational database optimization method based on cloud platform - Google Patents

Automatic relational database optimization method based on cloud platform Download PDF

Info

Publication number
CN116881230A
CN116881230A CN202311155171.7A CN202311155171A CN116881230A CN 116881230 A CN116881230 A CN 116881230A CN 202311155171 A CN202311155171 A CN 202311155171A CN 116881230 A CN116881230 A CN 116881230A
Authority
CN
China
Prior art keywords
query
strategy
user
cloud platform
index information
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Pending
Application number
CN202311155171.7A
Other languages
Chinese (zh)
Inventor
夏何均
石龙
于珍
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Beijing Guqi Data Technology Co ltd
Original Assignee
Beijing Guqi Data Technology Co ltd
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by Beijing Guqi Data Technology Co ltd filed Critical Beijing Guqi Data Technology Co ltd
Priority to CN202311155171.7A priority Critical patent/CN116881230A/en
Publication of CN116881230A publication Critical patent/CN116881230A/en
Pending legal-status Critical Current

Links

Classifications

    • GPHYSICS
    • G06COMPUTING; CALCULATING OR COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/21Design, administration or maintenance of databases
    • G06F16/217Database tuning
    • GPHYSICS
    • G06COMPUTING; CALCULATING OR COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/23Updating
    • GPHYSICS
    • G06COMPUTING; CALCULATING OR COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24Querying
    • G06F16/245Query processing
    • G06F16/2458Special types of queries, e.g. statistical queries, fuzzy queries or distributed queries
    • G06F16/2462Approximate or statistical queries
    • GPHYSICS
    • G06COMPUTING; CALCULATING OR COUNTING
    • G06NCOMPUTING ARRANGEMENTS BASED ON SPECIFIC COMPUTATIONAL MODELS
    • G06N20/00Machine learning
    • YGENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y02TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
    • Y02DCLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
    • Y02D10/00Energy efficient computing, e.g. low power processors, power management or thermal management

Landscapes

  • Engineering & Computer Science (AREA)
  • Theoretical Computer Science (AREA)
  • Physics & Mathematics (AREA)
  • Databases & Information Systems (AREA)
  • Data Mining & Analysis (AREA)
  • General Engineering & Computer Science (AREA)
  • General Physics & Mathematics (AREA)
  • Software Systems (AREA)
  • Probability & Statistics with Applications (AREA)
  • Mathematical Physics (AREA)
  • Fuzzy Systems (AREA)
  • Computational Linguistics (AREA)
  • Artificial Intelligence (AREA)
  • Computer Vision & Pattern Recognition (AREA)
  • Evolutionary Computation (AREA)
  • Medical Informatics (AREA)
  • Computing Systems (AREA)
  • Information Retrieval, Db Structures And Fs Structures Therefor (AREA)

Abstract

The invention relates to the field of databases, in particular to a cloud platform-based automatic relational database optimization method, which comprises the following steps: s1, a monitoring node for monitoring and collecting user query related index information is established on a cloud platform; s2, tracking, analyzing and recording user query requests and related index information in real time; s3, classifying according to the type and the frequency of the user request, and generating a preset query strategy matched with the type of query; s4, applying the generated query strategy to a target database, and recording an execution result and related index information; s5, automatically updating and optimizing preset query strategies of the corresponding types of queries, selecting the optimal strategy and applying the optimal strategy to the subsequent real-time query requests. The invention combines the machine learning technology to automatically update and optimize the preset query strategy, thereby achieving the purpose of automatically optimizing the query strategy.

Description

Automatic relational database optimization method based on cloud platform
Technical Field
The invention relates to the technical field of databases, in particular to an automatic relational database optimization method based on a cloud platform.
Background
Conventional relational databases present performance bottlenecks in the face of large-scale data and complex queries, resulting in excessive query response times and increased system load. In order to improve the performance of relational databases, many methods of manual tuning, index design, physical storage, etc. have been proposed. However, these methods all require manual intervention and do not meet the real-time automated optimization requirements.
The Chinese patent with the bulletin number of CN104503974A discloses an automatic optimizing method of a relational database based on a cloud platform, which can collect the running state information of the database and the hardware index information of a virtual machine where the database is located at regular time, and timely actively optimize suggestions, passively optimize suggestions or automatically passively optimize, thereby improving the running performance stability of the database in the cloud platform. The method can output active optimization suggestions, output passive optimization suggestions and output automatic passive optimization.
However, the above disclosed solution has the following disadvantages: although the utilization rate of the physical device can be improved, the query strategy cannot be automatically optimized to improve the query efficiency of the user.
Disclosure of Invention
The invention aims to provide a method for automatically optimizing a relational database based on a cloud platform, aiming at the problem that the cloud platform in the background technology cannot automatically optimize a query strategy.
On one hand, the invention provides a cloud platform-based automatic relational database optimization method, which comprises the following steps:
s1, a monitoring node for monitoring and collecting user query related index information is established on a cloud platform;
s2, tracking, analyzing and recording user query requests and related index information in real time;
s3, sorting according to the type and the frequency of the user request, generating a preset query strategy matched with the type of query, sorting from fast to slow according to the query time corresponding to the historical query strategy, and selecting the query strategy with the forefront sorting by default;
s4, applying the generated query strategy to a target database, and recording an execution result and related index information;
s5, automatically updating and optimizing preset query strategies of the corresponding type of query according to the historical query mode and the related index information based on a learning algorithm, selecting an optimal strategy and applying the optimal strategy to a subsequent real-time query request.
Preferably, in S1, the user query related index information includes a query type, a query preference, a query frequency, a query time period, and a system load.
Preferably, in S2, the type and the preference direction of the user query are obtained by analysis, and the content range of the query is approximately queried in a specific time period, and the query frequency in the specific time period is obtained.
Preferably, in S3, the query policy includes keyword matching based, semantic analysis based, user history based, and collaborative filtering based.
Preferably, based on the keyword matching one or more keywords entered by the user, the system matches documents in the relational database that contain similar content based on the keywords; by performing semantic analysis on the user input based on the semantic analysis, understanding the intention and the context behind the user input, so that related resources are recommended better; recommending relevant resources according to past search behaviors and preferences of a user based on historical behaviors of the user, establishing a personalized model by collecting and analyzing user data, and providing customized search results to the user; the relevant resources are recommended based on collaborative filtering using information shared among multiple users and other people who have similar characteristics and interests as the current target user.
Preferably, in S4, the relevant index information is the adoption condition of the query result, and the adoption condition is judged by the user looking up time, cursor position and copy condition, and the adoption condition includes the adoption and non-adoption of two conditions.
Preferably, in S5, the method for selecting the optimal strategy includes the steps of: s51, selecting an optimal query strategy according to query classification; s52, judging the current system load, if the system load is greater than 80%, only executing the query strategy with the fastest history query and outputting a query result; s53, if the system load is less than or equal to 80%, firstly executing the query strategy with the fastest historical query and outputting the query result, and then sequentially executing other query strategies; s54, comparing the query time of the multiple query strategies, selecting the query strategy with the fastest query time, re-determining the optimal query strategy of the type of query, and re-ordering the query strategies of the type of query.
On the other hand, the invention provides a cloud platform-based relational database automatic optimization system which is used for executing the cloud platform-based relational database automatic optimization method and comprises a cloud platform, a database, a monitoring module, an analysis module, a query classification module, a query strategy module and a query strategy updating module; the cloud platform is connected with the database data transmission; the monitoring module is arranged in the cloud platform and used for monitoring and collecting related index information; the analysis module is used for tracking, analyzing and recording user requests and related index information; the query classification module is used for classifying the type and the frequency of the user request; the query strategy module is used for generating a query strategy sequence, executing the current optimal query strategy and outputting a result; the query strategy updating module is used for updating the query strategy ordering condition in real time, selecting the optimal strategy and applying the optimal strategy to the subsequent real-time query request.
Compared with the prior art, the invention has the following beneficial technical effects: in order to adapt to the change of the user query requirement and the change of the database performance, a learning algorithm is adopted to learn and iterate the historical query mode and the related index information, the execution results of different query strategies and the related index information are analyzed, and the preset query strategy is automatically updated and optimized by combining a machine learning technology, so that the aim of automatically optimizing the query strategy is fulfilled.
Drawings
FIG. 1 is a workflow diagram of one embodiment of the present invention;
FIG. 2 is a flow chart of a method of selecting an optimal strategy;
fig. 3 is a schematic structural diagram of an automatic relational database optimization system based on a cloud platform.
Detailed Description
Examples
As shown in fig. 1, the method for automatically optimizing the relational database based on the cloud platform provided by the invention comprises the following steps:
s1, a monitoring node for monitoring and collecting user query related index information is established on a cloud platform; the user query related index information includes query type, query preference, query frequency, query time period, and system load.
S2, tracking, analyzing and recording user query requests and related index information in real time; the analysis obtains the type and the preference direction of the user query, the general query content range in a specific time period and the query frequency in the specific time period.
S3, sorting according to the type and the frequency of the user request, generating a preset query strategy matched with the type of query, sorting from fast to slow according to the query time corresponding to the historical query strategy, and selecting the query strategy with the forefront sorting by default; query policies include keyword matching based, semantic analysis based, user history based behavior, and collaborative filtering based.
S4, applying the generated query strategy to a target database, and recording an execution result and related index information; the related index information is the adoption condition of the query result, the adoption condition is judged by checking time, cursor position and copying condition of a user, and the adoption condition comprises the adoption condition and the non-adoption condition.
S5, automatically updating and optimizing preset query strategies of the corresponding type of query according to the historical query mode and the related index information based on a learning algorithm, selecting an optimal strategy and applying the optimal strategy to a subsequent real-time query request.
Working principle: and the cloud platform is adopted as a basic framework, and the powerful computing and storage resources and the load balancing capability of the cloud platform are utilized to realize the automatic optimization of the relational database. And the monitoring nodes are installed on the cloud platform kernel, and data analysis and processing are performed by collecting user requests and related index information. By tracking, analyzing and recording user requests and related index information in real time, the performance influence condition of different types, frequencies and other characteristics on operation in each specific scene can be known. And then generating a matched and highly targeted query strategy according to the characteristics, and applying the query strategy to a target database. Meanwhile, the system can continuously monitor various index information in the running process, record the execution result and automatically update the query strategy in real time.
In this embodiment, in order to adapt to the change of the user query requirement and the change of the database performance, a learning algorithm is adopted to learn and iterate the historical query mode and the related index information, and the execution result and the related index information of different query strategies are analyzed, and the preset query strategy is automatically updated and optimized by combining with the machine learning technology, so that the purpose of automatically optimizing the query strategy is achieved.
Examples
As shown in fig. 1, in the automatic optimization method for a relational database based on a cloud platform provided by the invention, compared with the first embodiment, the system matches one or more keywords input by a user based on keyword matching, and the system matches files containing similar contents in the relational database according to the keywords; by performing semantic analysis on the user input based on the semantic analysis, understanding the intention and the context behind the user input, related resources are recommended better, such as entities, trigger events and the like can be extracted by utilizing natural language processing technology; recommending relevant resources according to past search behaviors and preferences of a user based on historical behaviors of the user, establishing a personalized model by collecting and analyzing user data, and providing customized search results to the user; the relevant resources are recommended based on collaborative filtering using information shared among multiple users and other people who have similar characteristics and interests as the current target user.
In this embodiment, when using different types of query strategies, a trade-off between accuracy and recall may occur, such as increasing accuracy, which typically results in a decrease in recall and vice versa. Therefore, in practical application, policy selection and adjustment can be performed according to specific requirements and user feedback.
Examples
As shown in fig. 2, in the automatic relational database optimization method based on the cloud platform provided by the invention, compared with the first embodiment, in S5, the method for selecting the optimal strategy includes the following steps: s51, selecting an optimal query strategy according to query classification; s52, judging the current system load, if the system load is greater than 80%, only executing the query strategy with the fastest history query and outputting a query result; s53, if the system load is less than or equal to 80%, firstly executing the query strategy with the fastest historical query and outputting the query result, and then sequentially executing other query strategies; s54, comparing the query time of the multiple query strategies, selecting the query strategy with the fastest query time, re-determining the optimal query strategy of the type of query, and re-ordering the query strategies of the type of query.
In this embodiment, according to the system load 80% being the limit, if the system load is greater than 80%, it is indicated that the current system load is greater, at this time, it needs to be ensured that all users can obtain the query result faster, only the historical optimal query policy can be executed, and when the system load is less than or equal to 80%, it is indicated that there is a greater margin in the current system operation, multiple query policies corresponding to the same query are conditionally executed, and the query policies corresponding to different types of queries are adjusted in real time compared with the result, so as to provide a more efficient and stable automatic optimization method.
Examples
As shown in fig. 3, the automatic optimizing system of the relational database based on the cloud platform based on the embodiment of the automatic optimizing method of the relational database based on the cloud platform comprises a cloud platform, a database, a monitoring module, an analyzing module, a query classifying module, a query strategy module and a query strategy updating module; the cloud platform is connected with the database data transmission; the monitoring module is arranged in the cloud platform and used for monitoring and collecting related index information; the analysis module is used for tracking, analyzing and recording user requests and related index information; the query classification module is used for classifying the type and the frequency of the user request; the query strategy module is used for generating a query strategy sequence, executing the current optimal query strategy and outputting a result; the query strategy updating module is used for updating the query strategy ordering condition in real time, selecting the optimal strategy and applying the optimal strategy to the subsequent real-time query request.
In this embodiment, the aim of automatically optimizing the query strategy is achieved by analyzing the execution results and related index information of different query strategies and automatically updating and optimizing the preset query strategy by combining the machine learning technology.
The embodiments of the present invention have been described in detail with reference to the drawings, but the present invention is not limited thereto, and various changes can be made within the knowledge of those skilled in the art without departing from the spirit of the present invention.

Claims (8)

1. The automatic relational database optimizing method based on the cloud platform is characterized by comprising the following steps of:
s1, a monitoring node for monitoring and collecting user query related index information is established on a cloud platform;
s2, tracking, analyzing and recording user query requests and related index information in real time;
s3, sorting according to the type and the frequency of the user request, generating a preset query strategy matched with the type of query, sorting from fast to slow according to the query time corresponding to the historical query strategy, and selecting the query strategy with the forefront sorting by default;
s4, applying the generated query strategy to a target database, and recording an execution result and related index information;
s5, automatically updating and optimizing preset query strategies of the corresponding type of query according to the historical query mode and the related index information based on a learning algorithm, selecting an optimal strategy and applying the optimal strategy to a subsequent real-time query request.
2. The automatic optimization method of a relational database based on a cloud platform as recited in claim 1, wherein in S1, the user query related index information includes a query type, a query preference, a query frequency, a query time period, and a system load.
3. The automatic optimization method of a relational database based on a cloud platform according to claim 1, wherein in S2, the type and the preference direction of the user query are analyzed and obtained, and the content range of the query is approximately queried in a specific time period, and the query frequency in the specific time period is obtained.
4. The method for automatically optimizing a relational database based on a cloud platform as recited in claim 1, wherein in S3, the query strategy comprises keyword matching based, semantic analysis based, user history behavior based, and collaborative filtering based.
5. The automatic optimization method of a relational database based on a cloud platform as claimed in claim 4, wherein the system matches the documents containing similar contents in the relational database based on the keywords matching one or more keywords inputted by the user; by performing semantic analysis on the user input based on the semantic analysis, understanding the intention and the context behind the user input, so that related resources are recommended better; recommending relevant resources according to past search behaviors and preferences of a user based on historical behaviors of the user, establishing a personalized model by collecting and analyzing user data, and providing customized search results to the user; the relevant resources are recommended based on collaborative filtering using information shared among multiple users and other people who have similar characteristics and interests as the current target user.
6. The automatic optimization method of the relational database based on the cloud platform according to claim 1, wherein in S4, the relevant index information is the adoption of the query result, the adoption is judged by the user checking time, the cursor position and the duplication, and the adoption includes the adoption and the non-adoption.
7. The automatic optimization method of a relational database based on a cloud platform as claimed in claim 1, wherein in S5, the selecting the best strategy method comprises the steps of: s51, selecting an optimal query strategy according to query classification; s52, judging the current system load, if the system load is greater than 80%, only executing the query strategy with the fastest history query and outputting a query result; s53, if the system load is less than or equal to 80%, firstly executing the query strategy with the fastest historical query and outputting the query result, and then sequentially executing other query strategies; s54, comparing the query time of the multiple query strategies, selecting the query strategy with the fastest query time, re-determining the optimal query strategy of the type of query, and re-ordering the query strategies of the type of query.
8. The automatic optimizing system of the relational database based on the cloud platform is used for executing the automatic optimizing method of the relational database based on the cloud platform, and is characterized by comprising a cloud platform, a database, a monitoring module, an analyzing module, a query classifying module, a query strategy module and a query strategy updating module; the cloud platform is connected with the database data transmission; the monitoring module is arranged in the cloud platform and used for monitoring and collecting related index information; the analysis module is used for tracking, analyzing and recording user requests and related index information; the query classification module is used for classifying the type and the frequency of the user request; the query strategy module is used for generating a query strategy sequence, executing the current optimal query strategy and outputting a result; the query strategy updating module is used for updating the query strategy ordering condition in real time, selecting the optimal strategy and applying the optimal strategy to the subsequent real-time query request.
CN202311155171.7A 2023-09-08 2023-09-08 Automatic relational database optimization method based on cloud platform Pending CN116881230A (en)

Priority Applications (1)

Application Number Priority Date Filing Date Title
CN202311155171.7A CN116881230A (en) 2023-09-08 2023-09-08 Automatic relational database optimization method based on cloud platform

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
CN202311155171.7A CN116881230A (en) 2023-09-08 2023-09-08 Automatic relational database optimization method based on cloud platform

Publications (1)

Publication Number Publication Date
CN116881230A true CN116881230A (en) 2023-10-13

Family

ID=88255491

Family Applications (1)

Application Number Title Priority Date Filing Date
CN202311155171.7A Pending CN116881230A (en) 2023-09-08 2023-09-08 Automatic relational database optimization method based on cloud platform

Country Status (1)

Country Link
CN (1) CN116881230A (en)

Citations (9)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US9858355B1 (en) * 2016-10-28 2018-01-02 International Business Machines Corporation Search engine optimization based upon most popular search history
US20190179938A1 (en) * 2017-12-13 2019-06-13 Google Llc Reinforcement learning techniques to improve searching and/or to conserve computational and network resources
CN110688393A (en) * 2019-09-29 2020-01-14 星环信息科技(上海)有限公司 Query statement optimization method and device, computer equipment and storage medium
CN111159228A (en) * 2019-12-26 2020-05-15 北京神州绿盟信息安全科技股份有限公司 Data query method and device
CN111241407A (en) * 2020-01-21 2020-06-05 中国人民大学 Personalized search method based on reinforcement learning
CN113407807A (en) * 2020-12-15 2021-09-17 腾讯科技(深圳)有限公司 Query optimization method and device for search engine and electronic equipment
CN115309777A (en) * 2022-10-10 2022-11-08 北京奥星贝斯科技有限公司 Data query method and device
CN116521719A (en) * 2023-04-15 2023-08-01 复旦大学 Query optimization system based on cost estimation
CN116680290A (en) * 2023-06-05 2023-09-01 中国工商银行股份有限公司 Data query method and device based on data center

Patent Citations (9)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US9858355B1 (en) * 2016-10-28 2018-01-02 International Business Machines Corporation Search engine optimization based upon most popular search history
US20190179938A1 (en) * 2017-12-13 2019-06-13 Google Llc Reinforcement learning techniques to improve searching and/or to conserve computational and network resources
CN110688393A (en) * 2019-09-29 2020-01-14 星环信息科技(上海)有限公司 Query statement optimization method and device, computer equipment and storage medium
CN111159228A (en) * 2019-12-26 2020-05-15 北京神州绿盟信息安全科技股份有限公司 Data query method and device
CN111241407A (en) * 2020-01-21 2020-06-05 中国人民大学 Personalized search method based on reinforcement learning
CN113407807A (en) * 2020-12-15 2021-09-17 腾讯科技(深圳)有限公司 Query optimization method and device for search engine and electronic equipment
CN115309777A (en) * 2022-10-10 2022-11-08 北京奥星贝斯科技有限公司 Data query method and device
CN116521719A (en) * 2023-04-15 2023-08-01 复旦大学 Query optimization system based on cost estimation
CN116680290A (en) * 2023-06-05 2023-09-01 中国工商银行股份有限公司 Data query method and device based on data center

Similar Documents

Publication Publication Date Title
US7730060B2 (en) Efficient evaluation of object finder queries
US7428533B2 (en) Automatic generation of taxonomies for categorizing queries and search query processing using taxonomies
US10366093B2 (en) Query result bottom retrieval method and apparatus
US7529736B2 (en) Performant relevance improvements in search query results
US8051073B2 (en) System and method for measuring the quality of document sets
EP2369506B1 (en) System and method of optimizing performance of schema matching
US11562292B2 (en) Method of and system for generating training set for machine learning algorithm (MLA)
US20060248076A1 (en) Automatic expert identification, ranking and literature search based on authorship in large document collections
US20050240570A1 (en) Partial query caching
US9569495B2 (en) Feedback mechanism providing row-level filtering earlier in a plan
US9110949B2 (en) Generating estimates for query optimization
US20140006383A1 (en) System for evolutionary analytics
US20220391367A1 (en) Efficient Indexing for Querying Arrays in Databases
US20220222250A1 (en) Cost-based optimization for document-oriented database queries on arrays
Zou et al. Survey on learnable databases: A machine learning perspective
Mangaravite et al. Improving the efficiency of a genre-aware approach to focused crawling based on link context
NikRavesh Fuzzy conceptual-based search engine using conceptual semantic indexing
CN116881230A (en) Automatic relational database optimization method based on cloud platform
US20150154198A1 (en) Method for in-loop human validation of disambiguated features
US11947535B2 (en) Multicomputer system with machine learning engine for query optimization and dynamic data reorganization
Sharma et al. Indexer++ workload-aware online index tuning with transformers and reinforcement learning
Yu et al. Zebra: A novel method for optimizing text classification query in overload scenario
Ghansah et al. Survey On Score Normalization: A Case Of Result Merging In Distributed Information Retrieval
Qi et al. ANSWER: Automatic Index Selector for Knowledge Graphs
Li et al. DAF: An adaptive computing framework for multimedia data streams analysis

Legal Events

Date Code Title Description
PB01 Publication
PB01 Publication
SE01 Entry into force of request for substantive examination
SE01 Entry into force of request for substantive examination