Automatic optimization method for MySQL (My Structured Query Language) slow query statement, computer equipment and storage medium

An automatic optimization and slow query technology, applied in the field of database management, can solve problems such as low efficiency and achieve the effect of improving work efficiency

Inactive Publication Date: 2018-09-07
WUHAN DOUYU NETWORK TECH CO LTD
View PDF6 Cites 22 Cited by
  • Summary
  • Abstract
  • Description
  • Claims
  • Application Information

AI Technical Summary

Problems solved by technology

[0005] The embodiment of the present application solves the problem of low efficiency in optimizing the slow query statement of the OLTP system in the prior art by providing a method for automatically optimizing MySQL slow query statements, computer equipment and storage media

Method used

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
View more

Image

Smart Image Click on the blue labels to locate them in the text.
Viewing Examples
Smart Image
  • Automatic optimization method for MySQL (My Structured Query Language) slow query statement, computer equipment and storage medium
  • Automatic optimization method for MySQL (My Structured Query Language) slow query statement, computer equipment and storage medium
  • Automatic optimization method for MySQL (My Structured Query Language) slow query statement, computer equipment and storage medium

Examples

Experimental program
Comparison scheme
Effect test

Embodiment 1

[0071] see figure 1 , the present embodiment is applied in the OLTP system, and the MySQL slow query statement automatic optimization method provided by the present embodiment includes the following steps:

[0072] S101. Obtain the slow query statement through the slow query SQL acquisition function;

[0073] Specifically, acquiring slow query statements through the slow query SQL acquisition function may include but not limited to:

[0074] First obtain the slow query statements from the slow query file. In actual operation, you can obtain the slow query statements one by one and then optimize them one by one, or you can obtain all the slow query statements at once and then optimize them one by one. There are not too many restrictions here;

[0075] Then save each slow query statement line by line in the table of the database, wherein the information of each line of the table includes but not limited to SQL content, query time, running time, lock time, number of scanned rows...

Embodiment 2

[0119] see figure 2 , the present embodiment is applied in the OLTP system, and the MySQL slow query statement automatic optimization device provided by the present embodiment includes:

[0120] An acquisition module 201, configured to acquire a slow query statement through a slow query SQL acquisition function;

[0121] Specifically, the obtaining module 201 is used to obtain the slow query statement through the slow query SQL obtaining function, which specifically includes:

[0122] Used to obtain slow query statements from slow query files;

[0123] It is used to save the slow query statement in a table of the database, wherein the information of each row of the table includes SQL content, query time, running time, lock time, number of scanned rows and number of sent rows.

[0124] The checking module 202 is used to check the number of rows and statement content of the slow query statement through the slow query SQL preliminary analysis function, and store the check resu...

Embodiment 3

[0136] This embodiment provides a computer-readable storage medium 300, the computer-readable storage medium 300 stores a computer program 311, and the computer program 311 implements the following steps when executed by a processor:

[0137] Obtain the slow query statement through the slow query SQL acquisition function;

[0138] Check the row number and statement content of the slow query statement by the slow query SQL preliminary analysis function, and store the check result in the tuning result set;

[0139] Obtain the execution plan of the slow query statement through the execution plan acquisition function;

[0140] Check the index and table structure of the execution plan through the execution plan analysis function, and store the check result in the tuning result set;

[0141] If the tuning result set is empty, obtain a preset number of rewritten SQL statements through the SQL statement rewriting function, and store them in the tuning result set;

[0142] The tuning...

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
Login to view more

PUM

No PUM Login to view more

Abstract

The invention provides an automatic optimization method for a MySQL (My Structured Query Language) slow query statement, computer equipment and a storage medium. The automatic optimization method forthe MySQL slow query statement, the computer equipment and the storage medium can be applied to an OLTP (On-Line Transaction Processing) system. The method comprises the steps of acquiring a slow query statement through a slow query SQL acquisition function; checking a row number and a statement content of the slow query statement through a slow query SQL preliminary analysis function, and storinga checking result into an optimization result set; acquiring an execution plan of the slow query statement through an execution plan acquisition function; checking an index and a stable structure ofthe execution plan through an execution plan analysis function, and storing the checking result into the optimization result set; if the optimization result set is null, acquiring the preset number ofrewriting SQL statements through an SQL statement rewriting function, and storing the rewriting SQL statements into the optimization result set; and displaying the optimization result set in a form of web. Therefore, according to the automatic optimization method for the MySQL slow query statement, the computer equipment and the storage medium, the MySQL slow query statement can be automaticallyoptimized in the OLTP system and the working efficiency is greatly improved.

Description

technical field [0001] The invention belongs to the field of database management, and in particular relates to a method for automatically optimizing MySQL slow query statements, computer equipment and storage media. Background technique [0002] Slow queries are SQL statements that exceed the specified time. The system calculates the execution time of each command before and after the execution of the command. When the execution time exceeds the preset threshold, it records the relevant information of this command. [0003] Traditional optimization schemes for slow query statements are basically manual optimization. Manual optimization is performed based on experience and some optimization rules. When there are thousands or hundreds of databases under management, it takes a lot of manpower to optimize the performance of slow query statements. Tuning. [0004] In the OLTP (On-Line Transaction Processing, online transaction processing process) system, there are generally some...

Claims

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
Login to view more

Application Information

Patent Timeline
no application Login to view more
Patent Type & Authority Applications(China)
IPC IPC(8): G06F17/30
Inventor 陈阳陈少杰张文明
Owner WUHAN DOUYU NETWORK TECH CO LTD
Who we serve
  • R&D Engineer
  • R&D Manager
  • IP Professional
Why Eureka
  • Industry Leading Data Capabilities
  • Powerful AI technology
  • Patent DNA Extraction
Social media
Try Eureka
PatSnap group products

Browse by: Latest US Patents, China's latest patents, Technical Efficacy Thesaurus, Application Domain, Technology Topic.

© 2024 PatSnap. All rights reserved.Legal|Privacy policy|Modern Slavery Act Transparency Statement|Sitemap