CN113779064B - Intelligent optimization method and system for online sql - Google Patents
Intelligent optimization method and system for online sql Download PDFInfo
- Publication number
- CN113779064B CN113779064B CN202110935717.5A CN202110935717A CN113779064B CN 113779064 B CN113779064 B CN 113779064B CN 202110935717 A CN202110935717 A CN 202110935717A CN 113779064 B CN113779064 B CN 113779064B
- Authority
- CN
- China
- Prior art keywords
- sql
- management system
- database management
- system platform
- mysql database
- 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.)
- Active
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/23—Updating
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/25—Integrating or interfacing systems involving database management systems
- G06F16/252—Integrating or interfacing systems involving database management systems between a Database Management System and a front-end application
Abstract
The embodiment of the application provides an online sql intelligent optimization method and system, comprising the following steps: acquiring sql sentences and related information of the sql sentences which meet preset conditions and are used on a MySQL database management system platform; analyzing sql sentences and related information of the sql sentences meeting preset conditions to form sql optimization suggestions so as to optimize the sql functions; and optimizing the sql function of the MySQL database management system platform according to the sql optimization suggestion, and performing corresponding operation on data in the MySQL database management system platform by adopting an sql statement formed after the sql function optimization at the front end. And checking all sql statements on the line in time, automatically optimizing once the sql statement which can be optimized is found, and solving or exposing the sql statement before the problem occurs.
Description
Technical Field
The application relates to the field of sql optimization diagnosis, in particular to an online sql intelligent optimization method and system.
Background
MySQL is an open source relational database management system (RDBMS) that uses the most commonly used database management language, the Structured Query Language (SQL).
By using the planned sql statement, the data in MySQL can be added, deleted, modified and queried. In the practical application process, mySQL can store a large amount of data, in order to operate the data more efficiently, indexes are required to be added in a MySQL data table, the reasonable use of the indexes can improve the operation efficiency of adding, deleting and checking, and the improved efficiency can reach thousands of times. In a production environment, each piece of executed sql should use the index correctly, and the lack of the necessary index or the incorrect use of the index can significantly slow the efficiency of the entire MySQL service, even cause failures.
In carrying out the present application, the applicant has found that at least the following problems exist in the prior art:
1. in the existing production environment, a maintenance party of MySQL is a database administrator (DBA) team, a use party of MySQL is a business development team, the development team uses sql in a manner, the index is used without comprehensive knowledge, and a problem of knowing one-sided operation mechanism in the database exists, so that performance bottlenecks can be encountered in the process of using MySQL, and the service stability of MySQL can be seriously affected. On the other hand, as the service time increases, the data volume in MySQL increases continuously, and the sql command which runs well before also reaches the bottleneck due to the increase of the data volume.
2. Most of the sql which is not used correctly does not generate obvious problems, more resources are occupied, the execution speed is low, a large number of invisible problems are difficult to find in time, but a large number of resource wastes are actually caused, and the influence caused by the small problems is rapidly enlarged in certain scenes to cause faults so as to influence the service stability.
3. For the DBA team, since a large amount of data of the business team is accepted, the amount of sql used is very huge, no effort and time are needed to check whether the use of the sql is reasonable manually, when some sql is found to be problematic, the sql has serious influence, and the problem is handled in a very passive state.
Disclosure of Invention
The embodiment of the application provides an intelligent optimization method and system for on-line sql, which can timely check all the sql sentences on a line, automatically optimize the sql sentences once the sql sentences which can be optimized are found, and solve or expose the problems before the problems occur.
To achieve the above objective, in one aspect, an embodiment of the present application provides an online sql intelligent optimization method, including:
acquiring sql sentences and related information of the sql sentences which meet preset conditions and are used on a MySQL database management system platform;
analyzing sql sentences and related information of the sql sentences meeting preset conditions to form sql optimization suggestions so as to optimize the sql functions; the sql statement is used for operating data in the MySQL database management system platform by adopting an index, and the operations comprise: adding, deleting, modifying or querying;
optimizing the sql function of the MySQL database management system platform according to the sql optimization suggestion, and performing corresponding operation on data in the MySQL database management system platform by adopting an sql statement formed after the sql function optimization at the front end; and/or automatically optimizing the sql function in the background of the MySQL database management system platform according to the sql optimization suggestion, and carrying out data analysis on the MySQL database management system platform by adopting the sql statement formed after the sql function optimization and the index of the sql statement, wherein the data analysis result is used for guiding updating the sql use specification.
In another aspect, an embodiment of the present application provides an online sql intelligent optimization system, including:
the data acquisition unit is used for acquiring sql sentences and related information of the sql sentences which meet preset conditions and are used on the MySQL database management system platform;
the optimizing unit is used for analyzing the sql statement meeting the preset conditions and the related information of the sql statement to form an sql optimizing suggestion so as to optimize the sql function; the sql statement is used for operating data in the MySQL database management system platform by adopting an index, and the operations comprise: adding, deleting, modifying or querying;
the application unit is used for optimizing the sql function of the MySQL database management system platform according to the sql optimization suggestion, and the front end adopts an sql statement formed after the sql function optimization to perform corresponding operation on the data in the MySQL database management system platform; and/or automatically optimizing the sql function in the background of the MySQL database management system platform according to the sql optimization suggestion, and carrying out data analysis on the MySQL database management system platform by adopting the sql statement formed after the sql function optimization and the index of the sql statement, wherein the data analysis result is used for guiding updating the sql use specification.
The technical scheme has the following beneficial effects: and checking all sql statements on the line in time, automatically optimizing once the sql statements which can be optimized are found, and solving or exposing the sql statements before the problem occurs.
Drawings
In order to more clearly illustrate the embodiments of the application or the technical solutions in the prior art, the drawings that are required in the embodiments or the description of the prior art will be briefly described, it being obvious that the drawings in the following description are only some embodiments of the application, and that other drawings may be obtained according to these drawings without inventive effort for a person skilled in the art.
FIG. 1 is a flow chart of an on-line sql intelligent optimization method of an embodiment of the present application;
FIG. 2 is a block diagram of an on-line sql intelligent optimization system of an embodiment of the present application;
FIG. 3 is a schematic diagram of an on-line sql intelligent optimization platform in accordance with an embodiment of the present application;
FIG. 4 is a presentation page of the sql structure optimization suggestion and the index optimization suggestion obtained by the front end page of an embodiment of the present application;
FIG. 5 is an optimization record presentation page after execution of optimization suggestions in accordance with an embodiment of the present application.
Detailed Description
The following description of the embodiments of the present application will be made clearly and completely with reference to the accompanying drawings, in which it is apparent that the embodiments described are only some embodiments of the present application, but not all embodiments. All other embodiments, which can be made by those skilled in the art based on the embodiments of the application without making any inventive effort, are intended to be within the scope of the application.
As shown in fig. 1, in combination with an embodiment of the present application, an online sql intelligent optimization method is provided, which includes:
s101: acquiring sql sentences and related information of the sql sentences which meet preset conditions and are used on a MySQL database management system platform;
s102: analyzing sql sentences and related information of the sql sentences meeting preset conditions to form sql optimization suggestions so as to optimize the sql functions; the sql statement is used for operating data in the MySQL database management system platform by adopting an index, and the operations comprise: adding, deleting, modifying or querying;
s103: optimizing the sql function of the MySQL database management system platform according to the sql optimization suggestion, and performing corresponding operation on data in the MySQL database management system platform by adopting an sql statement formed after the sql function optimization at the front end; and/or automatically optimizing the sql function in the background of the MySQL database management system platform according to the sql optimization suggestion, and carrying out data analysis on the MySQL database management system platform by adopting the sql statement formed after the sql function optimization and the index of the sql statement, wherein the data analysis result is used for guiding updating the sql use specification.
Preferably, in step 101, the obtaining, on the MySQL database management system platform, an sql statement that meets a preset condition specifically includes:
collecting slow query slolog running in a MySQL database management system platform in real time by using a packet grabbing tool, and obtaining recorded sql sentences from the slolog; the slow query slolog is used for recording sql sentences with the running time exceeding a preset time threshold in the MySQL database management system platform.
Preferably, after the acquiring, the sql statement meeting the preset condition, which is used on the MySQL database management system platform, further includes:
s104: extracting the sql fingerprint of the sql statement, identifying the sql statement with the same filling content according to the sql fingerprint, de-duplicating the sql statement with the same filling content and the same sql fingerprint to obtain a de-duplicated sql statement, and using the de-duplicated sql statement and related information thereof to optimize the sql function; wherein, the sql fingerprint refers to a preset structure for forming some sort of sql statement.
Preferably, the relevant information of the sql statement includes: the source ip of the sql statement, the database, the table where the index is located and the business to which the index belongs;
step 102 specifically includes:
analyzing the de-duplicated sql fingerprint, the filling content of the de-duplicated sql statement and the related information of the de-duplicated sql statement to form an sql optimization suggestion; the sql optimization suggestion includes one of: how to modify the sql fingerprint structure, how to build the proper index of the sql statement, and how to modify the table structure where the index is located.
Preferably, S105 is further included: verifying the sql optimization suggestion before optimizing the sql function of the MySQL database management system platform according to the sql optimization suggestion;
the step 105 specifically includes:
s1051: modifying an sql fingerprint structure, modifying an index and/or modifying a table structure of the index in the MySQL database management system platform according to the sql optimization suggestion through the background of the MySQL database management system platform, wherein the table structure of the modified index is used for adding a new index in a table of the index, generating an sql statement according to the modified sql fingerprint structure, and indexing the generated sql statement through a corresponding index to verify the sql optimization suggestion; then, an execution plan about the sql statement is obtained through a MySQL command, wherein the execution plan about the sql statement comprises specific conditions of execution of the sql statement, and the execution efficiency of the sql statement is judged through the specific conditions;
s1052: when the sql optimization suggestion cannot be verified through the background of the MySQL database management system platform, adding an index and/or modifying the index in the MySQL database management system platform according to the optimization suggestion in the background of the MySQL database management system platform, modifying the table structure of the index before adding the index, and modifying the sql fingerprint structure according to the sql optimization suggestion by using the front end of the MySQL database management system platform; the front end generates an sql statement according to the modified sql fingerprint structure, indexes the generated sql statement through corresponding indexes, and achieves corresponding operation on data in the MySQL database management system platform; the execution plan for the sql statement is then obtained by MySQL command.
As shown in fig. 2, in connection with an embodiment of the present application, there is provided an online sql intelligent optimization system comprising:
the data acquisition unit 21 is used for acquiring sql sentences and related information of the sql sentences which meet preset conditions and are used on the MySQL database management system platform;
an optimizing unit 22, configured to analyze sql statements meeting preset conditions and related information of the sql statements, and form sql optimization suggestions to optimize the sql functions; the sql statement is used for operating data in the MySQL database management system platform by adopting an index, and the operations comprise: adding, deleting, modifying or querying;
the application unit 23 is configured to optimize an sql function of the MySQL database management system platform according to the sql optimization suggestion, and the front end performs corresponding operations on data in the MySQL database management system platform by using an sql statement formed after the sql function optimization; and/or automatically optimizing the sql function in the background of the MySQL database management system platform according to the sql optimization suggestion, and carrying out data analysis on the MySQL database management system platform by adopting the sql statement formed after the sql function optimization and the index of the sql statement, wherein the data analysis result is used for guiding updating the sql use specification.
Preferably, the data acquisition unit 21 is specifically configured to:
collecting slow query slolog running in a MySQL database management system platform in real time by using a packet grabbing tool, and obtaining recorded sql sentences from the slolog; the slow query slolog is used for recording sql sentences with the running time exceeding a preset time threshold in the MySQL database management system platform.
Preferably, the method further comprises:
the data deduplication unit 24 is configured to extract, after obtaining an sql statement that satisfies a preset condition and is used on the MySQL database management system platform, an sql fingerprint of the sql statement, identify, according to the sql fingerprint, an sql statement with the same filling content, deduplicate the sql statement with the same filling content and the same sql fingerprint, obtain a deduplicated sql statement, and use the deduplicated sql statement and related information thereof to optimize an sql function; wherein, the sql fingerprint refers to a preset structure for forming some sort of sql statement.
Preferably, the relevant information of the sql statement includes: the source ip of the sql statement, the database, the table where the index is located and the business to which the index belongs;
the optimizing unit 22 is specifically configured to:
analyzing the de-duplicated sql fingerprint, the filling content of the de-duplicated sql statement and the related information of the de-duplicated sql statement to form an sql optimization suggestion; the sql optimization suggestion includes one of: how to modify the sql fingerprint structure, how to build the proper index of the sql statement, and how to modify the table structure where the index is located.
Preferably, the method further comprises:
a verification unit 25, configured to verify the sql optimization suggestion before optimizing the sql function of the MySQL database management system platform according to the sql optimization suggestion;
the verification unit 25 includes:
the first verification subunit 251 is configured to modify, by using a background of the MySQL database management system platform, an sql fingerprint structure, modify an index, and/or modify a table structure where the index is located, where the table structure where the index is located is used to add a new index into a table where the index is located, generate an sql statement according to the modified sql fingerprint structure, and index the generated sql statement by using a corresponding index to verify the sql optimization suggestion; then, an execution plan about the sql statement is obtained through a MySQL command, wherein the execution plan about the sql statement comprises specific conditions of execution of the sql statement, and the execution efficiency of the sql statement is judged through the specific conditions;
the second verification subunit 252 is configured to, when the sql optimization suggestion cannot be verified by the background of the MySQL database management system platform, add an index to the MySQL database management system platform according to the optimization suggestion in the background of the MySQL database management system platform, and/or modify the index, and modify the table structure where the index is located before adding the index, and modify the sql fingerprint structure according to the sql optimization suggestion using the front end of the MySQL database management system platform; the front end generates an sql statement according to the modified sql fingerprint structure, indexes the generated sql statement through corresponding indexes, and achieves corresponding operation on data in the MySQL database management system platform; the execution plan for the sql statement is then obtained by MySQL command.
Aiming at the traditional sql optimization flow, the application has the following beneficial effects:
1. the coverage area is wide. All sql on the line will be examined.
2. Takes effect timely. The examination is carried out continuously for 24 hours all the day, and the sql which can be optimized is automatically optimized once found, and the problem is solved or exposed before the problem occurs.
3. The optimization level is high. The source characteristics of the sql are arranged, and the main sql source is found out from the source characteristics, so that the DBA team can be trained in a targeted manner, and the sql optimization problem is solved from the source.
The foregoing technical solutions of the embodiments of the present application will be described in detail with reference to specific application examples, and reference may be made to the foregoing related description for details of the implementation process that are not described.
The application aims to establish an intelligent sql diagnosis platform with wide coverage range, friendly operation and automatic processing, so as to assist a DBA team to diagnose a large amount of sql in a complex online environment quickly and timely, screen out the sql needing to be modified, automatically give processing advice and automatically execute the processing advice. Furthermore, features are extracted from massive sql, such as information of source ip, database, index table, service and the like of the sql, so that the features are processed, problems of the sql are reduced from the source, and the stability of the online environment is maintained.
The application discloses an online sql intelligent optimization platform based on sql diagnosis, which relates to the field of sql optimization diagnosis, and comprises the following implementation processes: firstly, acquiring a slow query sql used on a line, extracting a sql fingerprint for performing a duplication removal operation, putting the sql after duplication removal into a spar for performing sql optimization, storing optimization suggestions in a warehouse, and providing the data (referred to as optimization suggestions) for users to perform sql optimization, namely: the mailable advice is provided to the user who decides whether to adopt and execute according to the actual situation. The table-changing sentence optimizing table structure can be automatically formed, data analysis and mining can be carried out on the platform according to the generated data, and more reasonable sql use specifications can be generated from higher-level guidance; specifically, through data analysis, the logic and habit of using sql by the service are collected, different service scenes are adopted, the common sql types are different, for example, statistics of sql are more, inquiry of sql is more, and the like, the information is known, a targeted specification is generated, the operation industry has special attack, and the service cannot master all specifications.
As shown in fig. 3, the technical scheme of the present application is as follows:
1. slalow log using a shelf gather line running MySQL program
a) The sharlock is a self-grinding packet-grabbing tool and can be used for collecting real-time sql sentences.
b) The slownlog is used to record sql statements that run longer than 200ms in an online environment.
2. Collecting sql fingerprint to complete duplication eliminating operation
c) The sql fingerprint is data obtained by processing the sql statement to a certain extent, belongs to the structural framework of the sql statement, and is consistent with the sql fingerprint of the sql statement with the same structure and different specific contents.
Deduplication refers to removing duplicate sql content. Because slologlog is full-volume collected sql statement data, where many data is repeatedly used by the same sql statement, deduplication is required. So the same sql fingerprint and the same content only holds one sql statement.
3. Optimizing the sql statement after the duplication removal
d) The method can be used for optimizing SQL sentences by using a sar, SOAR (SQL Optimizer And Rewriter) is an automatic tool for optimizing and rewriting SQL, and is developed and maintained by a database team of the millet artificial intelligence and cloud platform, and the tool is already in the open. Specifically, the de-duplicated sql statement and some basic information are input into the SOAR, the SOAR optimizes the de-duplicated sql statement, and optimization is obtained to obtain optimization suggestions, including how to modify the sql structure and what proper index should be added, and the optimization suggestions are all for enabling the execution speed of the sql to be faster. The sql optimization suggestion and the index optimization suggestion are both results derived from the software, the sql optimization suggestion only modifies the structure, the usage and the like of the sql statement, and the sql itself is modified to accelerate the running speed; index optimization suggestions refer to adding the appropriate index to the data table, thereby speeding up the sql statement execution without involving changes to the sql itself. The two modes complement each other. Wherein, the basic information refers to: the source ip of the sql statement, the database where the sql statement is located, the data table where the sql statement belongs, the table where the index is located, and the service where the sql statement belongs. The sources of these basic information are: 1. and 2, acquiring from an online database in real time through online scripts, and 3, storing in an internal meta-information database.
4. Storing the optimized result in an open source distributed relational database TiDB, and providing a front-end page (shown in fig. 4 and 5) for a developer to inquire or execute the optimized result through an autopan system, wherein the autopan provides optimization suggestions.
e) TiDB is an open source distributed relational database that is designed and developed autonomously by the PingCAP company.
f) The optimization suggestions are typically verified in the background, and if verification of the optimization suggestions cannot be performed in the background, the front-end verification is adopted.
Before a business is ready to bring a new sql on line, a developer may manually input an sql statement alone on the front-end page to analyze the sql to be able to verify it before it comes on line.
When the optimization proposal is obtained, the optimization proposal is applied to the actual production environment to verify through the proposal form, wherein the proposal is similar to a work order system, since the data of the business are put in other databases, if the proposal is to be indexed (the indexing nature is a modification of a table structure), the proposal needs to be proposed to a department managing the other databases, and the proposal can be actually executed after the proposal is approved by the department managing the other databases, and the proposal comprises the port number of the database, how to modify the table structure and the like. The method comprises the steps of,
after verification, an optimization proposal and an execution plan of the sql can be obtained through the MySQL command, the execution plan contains the execution condition of the sql, and whether the sql statement can be executed efficiently or not, whether the index is efficient or not and the execution efficiency is estimated roughly can be seen from the plan. If the verification is not passed, manual intervention is performed, and the optimization operation is performed by the DBA.
g) The autopan system is a self-grinding sql automatic execution system, and can execute the sql more safely and stably.
h) The front page will count all sql optimization results and analyze their characteristics, such as traffic sources, etc. In order to make targeted measures in advance, such as training the business, in order to provide customized optimization suggestions and formulated specifications for the business, so as to avoid the problem of slow execution of sql after a new sql is added later.
i) After determining that the optimization suggestions can be efficiently performed, the mailable suggestions are provided to the user who decides whether to adopt and perform according to the actual situation. Specifically, the sql optimization suggestion is provided to the front end using the MySQL platform, and is used for modifying the sql statement according to the sql optimization suggestion when the front end inputs the sql statement into the MySQL platform, and carrying out corresponding operation on the data in the platform by adopting the modified sql statement.
The table-changing sentence optimizing table structure can be automatically formed, data analysis and mining can be carried out on the platform according to the generated data, and more reasonable sql use specifications can be generated from higher-level guidance; specifically, through data analysis, the logic and habit of using sql by the service are collected, different service scenes are adopted, the common sql types are different, for example, statistics of sql are more, inquiry of sql is more, and the like, the information is known, a targeted specification is generated, the operation industry has special attack, and the service cannot master all specifications. Aiming at the traditional sql optimization flow, the application has the following beneficial effects:
1. the coverage area is wide. Traditional sql optimizations only find sql statements that have enough impact to be found by the DBA team, so only a very small fraction of sql will be involved. The intelligent sql diagnostic platform of the present application examines all sql statements on the line.
2. Takes effect timely. The traditional sql optimization only can be found by the DBA team, and the DBA team intervenes in time at the moment, so that on-line service is influenced, and the situation that the sheep is complement is suspected. The intelligent sql diagnosis platform can be continuously checked for 24 hours all the day, and the sql can be automatically optimized once being found, and the problem is solved or exposed before the problem occurs.
3. The optimization level is high. Traditional sql optimizations are optimized only for the sql hierarchy and still belong to passive processing. By utilizing the intelligent sql diagnosis platform, the source characteristics of the sql can be sorted, and the main sql source can be found out from the source characteristics, so that the DBA team can be trained in a targeted manner, and the sql optimization problem can be solved from the source.
It should be understood that the specific order or hierarchy of steps in the processes disclosed are examples of exemplary approaches. Based on design preferences, it is understood that the specific order or hierarchy of steps in the processes may be rearranged without departing from the scope of the present disclosure. The accompanying method claims present elements of the various steps in a sample order, and are not meant to be limited to the specific order or hierarchy presented.
In the foregoing detailed description, various features are grouped together in a single embodiment for the purpose of streamlining the disclosure. This method of disclosure is not to be interpreted as reflecting an intention that the claimed embodiments of the subject matter require more features than are expressly recited in each claim. Rather, as the following claims reflect, application lies in less than all features of a single disclosed embodiment. Thus the following claims are hereby expressly incorporated into this detailed description, with each claim standing on its own as a separate preferred embodiment of this application.
The previous description of the disclosed embodiments is provided to enable any person skilled in the art to make or use the present application. As will be apparent to those skilled in the art; various modifications to these embodiments will be readily apparent, and the generic principles defined herein may be applied to other embodiments without departing from the spirit or scope of the disclosure. Thus, the present disclosure is not intended to be limited to the embodiments shown herein but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.
The foregoing description includes examples of one or more embodiments. It is, of course, not possible to describe every conceivable combination of components or methodologies for purposes of describing the aforementioned embodiments, but one of ordinary skill in the art may recognize that many further combinations and permutations of various embodiments are possible. Accordingly, the embodiments described herein are intended to embrace all such alterations, modifications and variations that fall within the scope of the appended claims. Furthermore, as used in the specification or claims, the term "comprising" is intended to be inclusive in a manner similar to the term "comprising," as interpreted when employed as a transitional word in a claim. Furthermore, any use of the term "or" in the specification of the claims is intended to mean "non-exclusive or".
Those of skill in the art will further appreciate that the various illustrative logical blocks (illustrative logical block), units, and steps described in connection with the embodiments of the application may be implemented by electronic hardware, computer software, or combinations of both. To clearly illustrate this interchangeability of hardware and software, various illustrative components (illustrative components), elements, and steps have been described above generally in terms of their functionality. Whether such functionality is implemented as hardware or software depends upon the particular application and design requirements of the overall system. Those skilled in the art may implement the described functionality in varying ways for each particular application, but such implementation is not to be understood as beyond the scope of the embodiments of the present application.
The various illustrative logical blocks or units described in the embodiments of the application may be implemented or performed with a general purpose processor, a digital signal processor, an Application Specific Integrated Circuit (ASIC), a field programmable gate array or other programmable logic device, discrete gate or transistor logic, discrete hardware components, or any combination thereof designed to perform the functions described. A general purpose processor may be a microprocessor, but in the alternative, the general purpose processor may be any conventional processor, controller, microcontroller, or state machine. A processor may also be implemented as a combination of computing devices, e.g., a digital signal processor and a microprocessor, a plurality of microprocessors, one or more microprocessors in conjunction with a digital signal processor core, or any other similar configuration.
The steps of a method or algorithm described in connection 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. A software module may be stored in RAM memory, flash memory, ROM memory, EPROM memory, EEPROM memory, registers, hard disk, a removable disk, a CD-ROM, or any other form of storage medium known in the art. In an example, a storage medium may be coupled to the processor such that the processor can read information from, and write information to, the storage medium. In the alternative, the storage medium may be integral to the processor. The processor and the storage medium may reside in an ASIC, which may reside in a user terminal. In the alternative, the processor and the storage medium may reside as distinct components in a user terminal.
In one or more exemplary designs, the above-described functions of embodiments of the present application may be implemented in hardware, software, firmware, or any combination of the three. If implemented in software, the functions may be stored on a computer-readable medium or transmitted as one or more instructions or code on the computer-readable medium. Computer readable media includes both computer storage media and communication media that facilitate transfer of computer programs from one place to another. A storage media may be any available media that can be accessed by a general purpose or special purpose computer. For example, such computer-readable media may include, but is not limited to, RAM, ROM, EEPROM, CD-ROM or other optical disk storage, magnetic disk storage or other magnetic storage devices, or any other medium that may be used to carry or store program code in the form of instructions or data structures and other data structures that may be read by a general or special purpose computer, or a general or special purpose processor. Further, any connection is properly termed a computer-readable medium, e.g., if the software is transmitted from a website, server, or other remote source via a coaxial cable, fiber optic cable, twisted pair, digital Subscriber Line (DSL), or wireless such as infrared, radio, and microwave, and is also included in the definition of computer-readable medium. The disks (disks) and disks (disks) include compact disks, laser disks, optical disks, DVDs, floppy disks, and blu-ray discs where disks usually reproduce data magnetically, while disks usually reproduce data optically with lasers. Combinations of the above may also be included within the computer-readable media.
The foregoing description of the embodiments has been provided for the purpose of illustrating the general principles of the application, and is not meant to limit the scope of the application, but to limit the application to the particular embodiments, and any modifications, equivalents, improvements, etc. that fall within the spirit and principles of the application are intended to be included within the scope of the application.
Claims (6)
1. An intelligent optimization method for online sql, which is characterized by comprising the following steps:
acquiring sql sentences and related information of the sql sentences which meet preset conditions and are used on a MySQL database management system platform;
analyzing sql sentences and related information of the sql sentences meeting preset conditions to form sql optimization suggestions so as to optimize the sql functions; the sql statement is used for operating data in the MySQL database management system platform by adopting an index, and the operations comprise: adding, deleting, modifying or querying;
optimizing the sql function of the MySQL database management system platform according to the sql optimization suggestion, and performing corresponding operation on data in the MySQL database management system platform by adopting an sql statement formed after the sql function optimization at the front end; and/or automatically optimizing the sql function in the background of the MySQL database management system platform according to the sql optimization suggestion, and carrying out data analysis on the MySQL database management system platform by adopting an sql statement formed after the sql function optimization and an index of the sql statement, wherein the data analysis result is used for guiding updating the sql use specification;
the obtaining the sql statement meeting the preset condition, which is used on the MySQL database management system platform, specifically comprises the following steps:
collecting slow query slolog running in a MySQL database management system platform in real time by using a packet grabbing tool, and obtaining recorded sql sentences from the slolog; the slow query slolog is used for recording sql sentences with the running time exceeding a preset time threshold in the MySQL database management system platform;
the intelligent optimization method for the online sql further comprises the following steps: verifying the sql optimization suggestion before optimizing the sql function of the MySQL database management system platform according to the sql optimization suggestion;
the verification of the sql optimization suggestion specifically comprises the following steps:
modifying an sql fingerprint structure, modifying an index and/or modifying a table structure of the index in the MySQL database management system platform according to the sql optimization suggestion through the background of the MySQL database management system platform, wherein the table structure of the modified index is used for adding a new index in a table of the index, generating an sql statement according to the modified sql fingerprint structure, and indexing the generated sql statement through a corresponding index to verify the sql optimization suggestion; then obtaining an execution plan about the sql statement through a MySQL command, wherein the execution plan about the sql statement comprises specific situations of the execution of the sql statement, and judging the execution efficiency of the sql statement through the specific situations;
when the sql optimization suggestion cannot be verified through the background of the MySQL database management system platform, adding an index and/or modifying the index in the MySQL database management system platform according to the optimization suggestion in the background of the MySQL database management system platform, modifying the table structure of the index before adding the index, and modifying the sql fingerprint structure according to the sql optimization suggestion by using the front end of the MySQL database management system platform; the front end generates an sql statement according to the modified sql fingerprint structure, indexes the generated sql statement through corresponding indexes, and achieves corresponding operation on data in the MySQL database management system platform; the execution plan for the sql statement is then obtained by MySQL command.
2. The intelligent optimization method of on-line sql according to claim 1, further comprising, after the obtaining of the sql statement satisfying the preset condition used on the MySQL database management system platform:
extracting the sql fingerprint of the sql statement, identifying the sql statement with the same filling content according to the sql fingerprint, de-duplicating the sql statement with the same filling content and the same sql fingerprint to obtain a de-duplicated sql statement, and using the de-duplicated sql statement and related information thereof to optimize the sql function; wherein, the sql fingerprint refers to a preset structure for forming some sort of sql statement.
3. The intelligent optimization method of on-line sql according to claim 2, wherein the relevant information of the sql statement includes: the source ip of the sql statement, the database, the table where the index is located and the business to which the index belongs;
the analyzing the sql statement and the related information of the sql statement which meet the preset conditions specifically comprises:
analyzing the de-duplicated sql fingerprint, the filling content of the de-duplicated sql statement and the related information of the de-duplicated sql statement to form an sql optimization suggestion; the sql optimization suggestion includes one of: how to modify the sql fingerprint structure, how to build the proper index of the sql statement, and how to modify the table structure where the index is located.
4. An online sql intelligent optimization system, comprising:
the data acquisition unit is used for acquiring sql sentences and related information of the sql sentences which meet preset conditions and are used on the MySQL database management system platform;
the optimizing unit is used for analyzing the sql statement meeting the preset conditions and the related information of the sql statement to form an sql optimizing suggestion so as to optimize the sql function; the sql statement is used for operating data in the MySQL database management system platform by adopting an index, and the operations comprise: adding, deleting, modifying or querying;
the application unit is used for optimizing the sql function of the MySQL database management system platform according to the sql optimization suggestion, and the front end adopts an sql statement formed after the sql function optimization to perform corresponding operation on the data in the MySQL database management system platform; and/or automatically optimizing the sql function in the background of the MySQL database management system platform according to the sql optimization suggestion, and carrying out data analysis on the MySQL database management system platform by adopting an sql statement formed after the sql function optimization and an index of the sql statement, wherein the data analysis result is used for guiding updating the sql use specification;
the data acquisition unit is specifically configured to:
collecting slow query slolog running in a MySQL database management system platform in real time by using a packet grabbing tool, and obtaining recorded sql sentences from the slolog; the slow query slolog is used for recording sql sentences with the running time exceeding a preset time threshold in the MySQL database management system platform;
the intelligent optimization system for the online sql further comprises:
the verification unit is used for verifying the sql optimization suggestion before optimizing the sql function of the MySQL database management system platform according to the sql optimization suggestion;
the authentication unit includes:
the first verification subunit is used for modifying the sql fingerprint structure, modifying the index and/or modifying the table structure of the index in the MySQL database management system platform according to the sql optimization suggestion through the background of the MySQL database management system platform, wherein the table structure of the modified index is used for adding a new index in the table of the index, generating an sql statement according to the modified sql fingerprint structure, and indexing the generated sql statement through the corresponding index to verify the sql optimization suggestion; then obtaining an execution plan about the sql statement through a MySQL command, wherein the execution plan about the sql statement comprises specific situations of the execution of the sql statement, and judging the execution efficiency of the sql statement through the specific situations;
the second verification subunit is used for adding indexes and/or modifying indexes in the MySQL database management system platform according to the optimization suggestions in the background of the MySQL database management system platform when the sql optimization suggestions cannot be verified through the background of the MySQL database management system platform, modifying the table structure of the indexes before adding the indexes, and modifying the sql fingerprint structure according to the sql optimization suggestions by using the front end of the MySQL database management system platform; the front end generates an sql statement according to the modified sql fingerprint structure, indexes the generated sql statement through corresponding indexes, and achieves corresponding operation on data in the MySQL database management system platform; the execution plan for the sql statement is then obtained by MySQL command.
5. The intelligent optimization system of on-line sql of claim 4, further comprising:
the data deduplication unit is used for extracting sql fingerprints of the sql sentences after obtaining the sql sentences meeting the preset conditions used on the MySQL database management system platform, identifying the sql sentences with the same filling content according to the sql fingerprints, deduplicating the sql sentences with the same filling content and the same sql fingerprints to obtain the deduplicated sql sentences, and using the deduplicated sql sentences and related information thereof to optimize the sql functions; wherein, the sql fingerprint refers to a preset structure for forming some sort of sql statement.
6. The intelligent optimization system of on-line sql according to claim 5, wherein the relevant information of the sql statement comprises: the source ip of the sql statement, the database, the table where the index is located and the business to which the index belongs;
the optimizing unit is specifically configured to:
analyzing the de-duplicated sql fingerprint, the filling content of the de-duplicated sql statement and the related information of the de-duplicated sql statement to form an sql optimization suggestion; the sql optimization suggestion includes one of: how to modify the sql fingerprint structure, how to build the proper index of the sql statement, and how to modify the table structure where the index is located.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110935717.5A CN113779064B (en) | 2021-08-16 | 2021-08-16 | Intelligent optimization method and system for online sql |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110935717.5A CN113779064B (en) | 2021-08-16 | 2021-08-16 | Intelligent optimization method and system for online sql |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN113779064A CN113779064A (en) | 2021-12-10 |
| CN113779064B true CN113779064B (en) | 2023-09-12 |
Family
ID=78837863
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202110935717.5A Active CN113779064B (en) | 2021-08-16 | 2021-08-16 | Intelligent optimization method and system for online sql |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN113779064B (en) |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US9501618B1 (en) * | 2009-02-03 | 2016-11-22 | Brooke Erin Wurst | Systems, methods and devices for anonymously collecting personal data using a mobile device |
| CN110515799A (en) * | 2019-08-15 | 2019-11-29 | 浙江万朋教育科技股份有限公司 | MySQL monitoring system and implementation method based on python language |
| CN111414382A (en) * | 2019-01-07 | 2020-07-14 | 北京智融网络科技有限公司 | Slow SQ L polymerization display method and system based on MongoDB |
| CN112506951A (en) * | 2020-12-07 | 2021-03-16 | 海南车智易通信息技术有限公司 | Processing method, server, computing device and system for database slow query log |
| CN112559554A (en) * | 2020-12-24 | 2021-03-26 | 北京百家科技集团有限公司 | Query statement optimization method and device |
-
2021
- 2021-08-16 CN CN202110935717.5A patent/CN113779064B/en active Active
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US9501618B1 (en) * | 2009-02-03 | 2016-11-22 | Brooke Erin Wurst | Systems, methods and devices for anonymously collecting personal data using a mobile device |
| CN111414382A (en) * | 2019-01-07 | 2020-07-14 | 北京智融网络科技有限公司 | Slow SQ L polymerization display method and system based on MongoDB |
| CN110515799A (en) * | 2019-08-15 | 2019-11-29 | 浙江万朋教育科技股份有限公司 | MySQL monitoring system and implementation method based on python language |
| CN112506951A (en) * | 2020-12-07 | 2021-03-16 | 海南车智易通信息技术有限公司 | Processing method, server, computing device and system for database slow query log |
| CN112559554A (en) * | 2020-12-24 | 2021-03-26 | 北京百家科技集团有限公司 | Query statement optimization method and device |
Non-Patent Citations (1)
| Title |
|---|
| 上海市垃圾物流管理平台数据库优化策略的设计与实现;岳松波;中国优秀硕士论文全文数据库 信息科技辑;I138-809 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN113779064A (en) | 2021-12-10 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US11204898B1 (en) | Reconstructing database sessions from a query log | |
| US11294869B1 (en) | Expressing complexity of migration to a database candidate | |
| CN111522816B (en) | Data processing method, device, terminal and medium based on database engine | |
| US11762882B2 (en) | System and method for analysis and management of data distribution in a distributed database environment | |
| US9996558B2 (en) | Method and system for accessing a set of data tables in a source database | |
| US8239369B2 (en) | Method and apparatus for enhancing performance of database and environment thereof | |
| CN103186639B (en) | Data creation method and system | |
| CN107247811B (en) | SQL statement performance optimization method and device based on Oracle database | |
| US10474675B2 (en) | Explain tool for optimizing SPARQL queries | |
| CN111400297A (en) | Mass data quality verification method based on Hadoop | |
| CN111459698A (en) | Database cluster fault self-healing method and device | |
| Al Mahruqi et al. | A semi-automated framework for migrating web applications from SQL to document oriented NoSQL database. | |
| CN113779064B (en) | Intelligent optimization method and system for online sql | |
| KR102125010B1 (en) | System and method for analyzing database migration | |
| CN113704246A (en) | Database index optimization method and system based on virtual index | |
| CN114372083A (en) | Metadata analysis method and device | |
| CN111984625A (en) | Database load characteristic processing method, device, medium and electronic equipment | |
| US20220377088A1 (en) | Data management computer and data management method | |
| US11657032B2 (en) | Compacted table data files validation | |
| CN117573456A (en) | Compatibility checking method and device for host performance acquisition portal | |
| CN116048927A (en) | Database query data processing method and device | |
| CN118051539A (en) | Page query method and device | |
| CN111324726A (en) | Method and device for automatically drying patent database | |
| CN114372044A (en) | Data processing method and device | |
| CN117555969A (en) | Hive-based blood-edge information management method and device and computer equipment |
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 | ||
| TA01 | Transfer of patent application right | ||
| TA01 | Transfer of patent application right |
Effective date of registration: 20230427 Address after: Room 501-502, 5/F, Sina Headquarters Scientific Research Building, Block N-1 and N-2, Zhongguancun Software Park, Dongbei Wangxi Road, Haidian District, Beijing, 100193 Applicant after: Sina Technology (China) Co.,Ltd. Address before: 100193 7th floor, scientific research building, Sina headquarters, plot n-1, n-2, Zhongguancun Software Park, Dongbei Wangxi Road, Haidian District, Beijing, 100193 Applicant before: Sina.com Technology (China) Co.,Ltd. |
|
| GR01 | Patent grant | ||
| GR01 | Patent grant |