CN111046059A

CN111046059A - Low-efficiency SQL statement analysis method and system based on distributed database cluster

Info

Publication number: CN111046059A
Application number: CN201911248586.2A
Authority: CN
Inventors: 徐国柱; 欧万翔; 邓智鸿; 张东凯
Original assignee: China Construction Bank Corp; CCB Finetech Co Ltd
Current assignee: China Construction Bank Corp
Priority date: 2019-12-09
Filing date: 2019-12-09
Publication date: 2020-04-21
Anticipated expiration: 2039-12-09
Also published as: CN111046059B

Abstract

The application provides an inefficient SQL statement analysis method and system based on a distributed database cluster, and the function of automatically analyzing an input SQL statement can be realized by setting an SQL performance analysis model, the SQL performance analysis model is formed by training according to historical data, both application data and system data are known data, the known data are used as the basis of a performance result, and the analysis result tends to be reasonable day by day through training. Manpower can be liberated, efficiency is improved, and analysis and diagnosis can be rapidly completed on low-efficiency SQL.

Description

Low-efficiency SQL statement analysis method and system based on distributed database cluster

Technical Field

The invention relates to the technical field of database query, in particular to an inefficient SQL statement analysis method and system based on a distributed database cluster.

Background

Distributed databases are in most cases better suited for large data storage engines, computation engines and analysis engines. For example, greenplus is used as an enterprise-level database product, and has the characteristics of supporting mass data storage and processing, high cost performance, supporting BI real-time analysis, realizing a dynamic data warehouse, system usability, supporting thread expansion by adopting an MPP parallel processing structure, better concurrency support and high availability support, supporting MapReduce, compressing inside a database and the like, so that the greenplus is one of the most advanced OLAP open source databases in the world.

A distributed database is a logical database composed of up to tens, hundreds of individual database services. The application of the method has the following characteristics: the SQL access amount is large; SQL access content is unpredictable; the operation efficiency is closely related to system resources; the operation efficiency is closely related to the design of the database. Further, for a distributed database, there may be millions of query statements each day, and if these statements are written properly, the SQL execution efficiency will be affected if the database table design is reasonable, and thus the user experience will be affected.

Disclosure of Invention

In order to solve at least one of the above problems, an embodiment of the first aspect of the present application provides an inefficient SQL statement analysis method based on a distributed database cluster, including:

obtaining analysis basis information and inefficient SQL statements of a distributed database cluster, wherein the analysis basis information comprises: application information and system information;

inputting the inefficient SQL statements and the analysis basis information into a preset SQL performance analysis model to obtain inefficient reason analysis results of the inefficient SQL statements;

wherein the SQL performance analysis model is obtained by training a plurality of known inefficient SQL statements, corresponding historical application information and historical system information in the distributed database cluster.

In certain embodiments, further comprising:

establishing the SQL performance analysis model;

training the SQL performance analysis model through a plurality of known inefficient SQL statements, corresponding historical application information and historical system information in the distributed database cluster.

In some embodiments, the building the SQL performance analysis model includes:

establishing an input layer, a data source sorting layer, an SQL (structured query language) analysis layer, a performance analysis layer and an output layer;

the input layer inputs the analysis basis information and the low-efficiency SQL statement;

the data source sorting layer extracts the analysis basis information and outputs each structured information formed by classification according to a preset rule;

the SQL analysis layer extracts an inefficient SQL statement and outputs characteristic information of structural characteristics of the inefficient SQL statement;

the analysis result layer inputs the characteristic information of the structural characteristics of the low-efficiency SQL statement and all structural information, and carries out SQL performance analysis to obtain the analysis result of the low-efficiency reasons;

and the output layer outputs the analysis result of the inefficiency reason.

In some embodiments, said training said SQL performance analysis model with a plurality of known inefficient SQL statements, corresponding historical application information, and historical system information in said distributed database cluster comprises:

inputting a known inefficient SQL statement, corresponding historical application information and historical system information into an input layer;

generating efficiency factors influencing SQL statements and corresponding weights according to the historical application information and the historical system information;

setting the structural features output by an SQL analysis layer, analyzing the known low-efficiency SQL statements to obtain the structural feature information of the known low-efficiency SQL statements, and further associating the efficiency factors, the corresponding weights and the structural feature information of the known low-efficiency SQL statements;

inputting a plurality of known inefficient SQL sentences and corresponding historical application information and historical system information, and training the SQL analysis layer.

An embodiment of a second aspect of the present application provides an inefficient SQL statement analysis system based on a distributed database cluster, including:

the acquisition module acquires analysis basis information and inefficient SQL statements of the distributed database cluster, wherein the analysis basis information comprises: application information and system information;

the performance analysis module inputs the low-efficiency SQL statement and the analysis basis information into a preset SQL performance analysis model to obtain a low-efficiency reason analysis result of the low-efficiency SQL statement;

In certain embodiments, further comprising:

the model establishing module is used for establishing the SQL performance analysis model;

and the model training module is used for training the SQL performance analysis model through a plurality of known low-efficiency SQL statements, corresponding historical application information and historical system information in the distributed database cluster.

In some embodiments, the SQL performance analysis model comprises: the system comprises an input layer, a data source sorting layer, an SQL analysis layer, a performance analysis layer and an output layer;

and the output layer outputs the analysis result of the inefficiency reason.

In certain embodiments, the model training module comprises:

the sample input unit is used for inputting a known low-efficiency SQL statement, corresponding historical application information and historical system information into the input layer;

the efficiency factor generating unit is used for generating efficiency factors influencing SQL sentences and corresponding weights according to the historical application information and the historical system information;

the correlation unit is used for setting the structural characteristics output by the SQL analysis layer and analyzing the known low-efficiency SQL statements to obtain the structural characteristic information of the known low-efficiency SQL statements so as to correlate the efficiency factors, the corresponding weights and the structural characteristic information of the known low-efficiency SQL statements;

and the training unit is used for inputting a plurality of known low-efficiency SQL sentences and corresponding historical application information and historical system information and training the SQL analysis layer.

A further embodiment of the present application provides a computer device, which includes a memory, a processor and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the steps of the method described above.

A further embodiment of the present application provides a computer-readable storage medium, on which a computer program is stored, which, when being executed by a processor, carries out the steps of the method described above.

The beneficial effect of this application is as follows:

Drawings

In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings used in the description of the embodiments or the prior art will be briefly described below, it is obvious that the drawings in the following description are only some embodiments of the present invention, and for those skilled in the art, other drawings can be obtained according to the drawings without creative efforts.

Fig. 1 is a schematic flow chart illustrating an inefficient SQL statement analysis method based on a distributed database cluster according to an embodiment of the present invention.

FIG. 2 is a schematic flow chart illustrating the automatic analysis of the inefficient SQL statement based on the distributed database cluster according to the embodiment of the present invention.

Fig. 3 shows a data acquisition process input-output diagram of process 1 in fig. 2.

Fig. 4 is a functional introduction diagram implemented by the data source arrangement process of the process 2 in fig. 2.

FIG. 5 shows a functional introduction diagram implemented by the SQL parsing process of Process 3 in FIG. 2.

Fig. 6 shows a functional introduction diagram implemented by the report preparation process of process 4 in fig. 2.

Fig. 7 shows a functional introduction diagram implemented by the report generation of the process 5 in fig. 2.

FIG. 8 shows a functional introduction diagram implemented by the process 6 index resolution of FIG. 2.

Fig. 9 shows a prior art SQL statement analysis diagram.

FIG. 10 is a structural diagram of an inefficient SQL statement analysis system based on a distributed database cluster in an embodiment of the present invention.

FIG. 11 shows a schematic block diagram of a computer device suitable for use in implementing embodiments of the present application.

Detailed Description

The technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are only a part of the embodiments of the present invention, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.

Fig. 1 shows an inefficient SQL statement analysis method based on a distributed database cluster in an embodiment of the present application, which specifically includes:

s1, acquiring analysis basis information and inefficient SQL statements of the distributed database cluster, wherein the analysis basis information comprises: application information and system information;

s2, inputting the low-efficiency SQL statement and the analysis basis information into a preset SQL performance analysis model to obtain a low-efficiency reason analysis result of the low-efficiency SQL statement;

The SQL performance analysis model is formed by training according to historical data, and both application data and system data are known data, the known data are used as the basis of performance results, and the analysis results tend to be reasonable day by day through training. Manpower can be liberated, efficiency is improved, and analysis and diagnosis can be rapidly completed on low-efficiency SQL.

The SQL performance analysis model may be built online or offline, for example, in some embodiments, the SQL performance analysis model is built online and trained, that is, the method further includes:

s01: establishing the SQL performance analysis model;

s02: training the SQL performance analysis model through a plurality of known inefficient SQL statements, corresponding historical application information and historical system information in the distributed database cluster.

The building and training of the SQL performance model is described in detail below.

The establishing of the SQL performance analysis model comprises the following steps:

and the output layer outputs the analysis result of the inefficiency reason.

The following describes the above input layer, data source arrangement layer, SQL parsing layer, performance analysis layer, and output layer in detail with reference to the embodiments.

Fig. 2 shows the specific flow analysis steps of the entire model.

In some embodiments not shown in the figures, the input layer further performs data classification, i.e., the input data is structured to obtain system resource information, inefficient SQL statements, metadata information, and run information.

In other embodiments, as shown in fig. 2, the SQL performance analysis model further includes a data acquisition layer, and the data acquisition layer normalizes input data to obtain system resource information, inefficient SQL statements, metadata information, and operation information.

In the embodiment of fig. 3, the data input by the input layer includes:

a1 application information, including: a1.1 single SQL needing to be optimized, an A1.2 database operation log and A1.3 index information;

a2 system information, including: a2.1 database system table, A2.2 node operation load information;

a3 empirical information, comprising: a3.1 cluster data, a3.2 inefficiency function.

In the embodiment in fig. 3, the data input by the data acquisition layer is the data output by the input layer, and the output B1 system resource information includes: b1.1 resource queue, B1.2CPU information, B1.3 memory information, and B1.4 disk information;

the B2 inefficient SQL includes: B2.1SQL, the content;

the B3 metadata information includes: b3.1 table information, B3.2 view information, B3.3 field information;

the B4 operation information includes: b4.1 execution information, B4.2 user information, B4.3 cluster information.

The data acquisition layer in the specific embodiment is described below in terms of its functions.

1. The information source is the basis of subsequent analysis, mainly from 3 aspects:

① report and index information of user, SQL for supporting the report and index application, and the operation time, environment and user of SQL;

② basic information of database environment where SQL operates, parts that can be taken from system log, system table, configuration information;

③ do not rely on empirical information of existing applications or systems, such as inefficiency functions already in hand, number of nodes in a database cluster, hardware configuration, etc.

2. "Process 1: data acquisition Process completion

And summarizing and carding the information types to be collected according to the application optimization requirements of the Greenplus database cluster, wherein the information types comprise system resource information, metadata information, an analysis object SQL (structured query language) and related operation information when the SQL is operated. The information may be distributed in a plurality of places in a large environment of the user, most of the information can be obtained automatically, but some information may need manual maintenance, and some information needs processing and extraction. Process 1: the input before the data acquisition process is uncertain and varies according to the situation difference of the user environment. "Process 1: data acquisition ' data of various sources and different properties are collected to the ' low-efficiency SQL statement analysis method and system ' to form a ' B data acquisition layer '.

3. "Process 1: data acquisition "meaning of the process:

the data acquisition is before the "inefficient SQL statement analysis method and system" described in the present application, and may be a database system itself, an external system, or an external function. Is necessary prerequisite preparation for subsequent work.

In some embodiments, the data source arrangement layer inputs data output by the data acquisition layer, as shown in fig. 4, and the output data includes:

c1 metadata info _ table, comprising: c1.1 table identification + operation identification, C1.2 table definition, and C1.3 table information;

c2 metadata info _ field, including: c2.1 table id + field id + job id, C2.2 field info _ field type sense;

c3 operational information, including: c3.1 job identification, C3.2 execution information, C3.3 user information, C3.4 resource queue, C3.5 application information, C3.6 machine information, C3.7 memory information, C3.8CPU information, C3.9 network information.

The function of the data source arrangement layer in the embodiment is described below.

1. Processing the collected and sorted B data acquisition layer, such as integration, standardization and the like;

2. "Process 2: data source arrangement reorganizes data of different sources and different properties into reasonably classified structured information meeting design specifications to form a C data source arrangement layer serving as a real data source of subsequent analysis work.

3. "Process 2: data source collation "meaning of the process:

"Process 2: the data source arrangement layer forms data of a layer C data source arrangement layer, and the layer can ensure that the functional logic and the information source in the subsequent analysis process are not tightly coupled and are relatively independent, so that the data source arrangement layer can be used as an independent and universal solution.

In the embodiment in fig. 5, the input of the SQL parsing layer is the output data of the data acquisition layer, and the output data of the SQL parsing layer includes:

d1 table and field list, including: d1.1 operation identification, D1.2 table identification, D1.3 field identification and D1.4 statistical information;

d2, table associations, including: d2.1 operation identification, D2.2 association identification, D2.3 left side information, D2.4 right side information, D2.5 association condition information, D2.6 statistical information and D2.7 characteristic information;

d3 table filter conditions, including: d3.1 operation identification, D3.2 condition identification, D3.3 left side information, D3.4 right side information, D3.5 filtering condition information, D3.6 statistical information and D3.7 characteristic information;

a D4 sub-query comprising: d4.1 operation identification, D4.2 sub-query identification, D4.3 statistical information and D4.4 design information;

d5group _ by, comprising: d5.1 job identification, D5.2group _ by identification, D5.3 statistical information and D5.4 characteristic information;

d6order _ by, comprising: d6.1 job identification, D6.2order _ by identification, D6.3 statistical information and D6.4 characteristic information.

And analyzing all tables, fields, associated information, condition information and other key factors used by the SQL from the low-efficiency SQL statement.

The analysis result is not suitable for collective processing because of its different dimensions, and is thus divided into 6 parts according to characteristics. The method is convenient for expressing the analysis result and optimizing and analyzing the bonding performance. These 6 dimensions are:

list of table fields

Inter-table association information

Table filter information

Sub-queries

Group_by

Order_by

The analysis method is based on a Python library sqlparse, and the tree-shaped result analyzed by the sqlparse is further analyzed and processed to obtain a clear structured result.

"Process 3: SQL parsing "meaning:

through the process, the inefficient SQL statements can be finely decomposed, and preparation is made for the next step of association with information in the C data source arrangement layer to obtain direct basis required by optimization analysis.

In some embodiments, the result of the inefficiency analysis is an analysis report, and in this embodiment, the performance analysis layer includes: a report preparation layer and a report layer.

The input of the report preparation layer is the output data of the data source arrangement layer and the SQL parsing layer, as shown in fig. 6, the output data includes:

e1 table design, comprising: e1.1 operation identification, E1.10 statistical information collection information, E1.2 table identification, E1.3 statistical information, E1.4 data volume, E1.5 distribution information, E1.6 partition information, E1.7 compression information, E1.8 column storage information and E1.9 expansion information;

e2SQL associations, including: e2.1 job identification, E2.10 left and right information comparison, E2.2 association identification, E2.3 left information, E2.4 right information, E2.5 association condition information, E2.6 statistical information, E2.7 characteristic information, design information of E2.8 related table, and design information of E2.9 related field;

e3SQL _ WHERE, comprising: e3.1 job identification, E3.10 left and right information comparison, E3.2 condition identification, E3.3 left side information, E3.4 right side information, E3.5 filter condition information, E3.6 statistical information, E3.7 characteristic information, E3.8 related table design information, E3.9 related field design information;

e4 system resources, including: e4.1 job identification, E4.2 execution plan overhead information, E4.3 run time period, E4.4 resource queue combination, E4.5CPU usage, E4.6 memory usage, E4.7 disk usage, E4.8 network usage, E4.9 system run space usage.

The function of the report preparation layer is explained below.

After completing a large amount of optimization analysis work performed manually, pen workers accumulate abundant optimization work experience, and summarize and sort out the steps of optimization analysis and concerned influence factors. Solidifying the contents to form an E report preparation layer; contains 4 parts:

e1 watch design

E2SQL correlation

E3SQL_WHERE

E4 System resources

The contents designed in the E report preparation layer can be obtained by correlating, counting and extracting the DSQL analysis layer and the C data source sorting layer, and a final basis is provided for the analysis result;

"Process 4: meaning of report preparation ":

the design of the E report preparation layer is actually the summary of the SQL performance analysis method based on the distributed database cluster;

the step covers efficiency factors needing to be concerned in each step of performance analysis, and data of the factors are subjected to evidence collection, so that preparation is made for generating a final user report in the next step;

the input of the step also comprises the acquisition of application information corresponding to SQL, and comprises the efficiency factor weight and the application efficiency relation machine learning result, so that the final report can not only identify the points influencing the efficiency, but also quantify the proportion of each point influencing the final efficiency.

The input of the report layer is the output data of the report preparation layer, as shown in fig. 7, the output data of the report layer includes:

f1 table design correlations, including: f1.1 distribution key, F1.2 partition, F1.3 compression, F1.4 column storage, F1.5 statistical information collection and F1.6 table expansion;

the SQL statement applied by F2 includes: f2.1 complexity, F2.2 association, F2.3 filter condition, F2.4 inefficiency function, F2.5 ordering, F2.6 union;

f3 system resources, including: f3.1 system resources;

the F4 consolidated report, comprising: is formed by combining F1, F2 and F3.

The function of the report layer is specifically described below.

And generating a report as a text by using the structured E report preparation layer stored in the database. Providing a reference for subsequent optimization processing for a demander of performance optimization;

because of many factors affecting the final execution performance of SQL, it needs to be analyzed and described from different dimensions. The preamble steps are also analyzed by classification. The embodiment is that the final report is also 3 relatively independent parts, which are respectively:

relevant part of a watch design

SQL problems themselves

Problem of system resources

In the process, a secondary processing process is also carried out on the E report preparation layer.

The final submission is the result of combining the three parts;

"Process 5: meaning of report generation ":

and presenting the analysis result in a text form.

In the embodiment, the SQL performance analysis model can be more perfect and intelligent through training, and the adaptability is better.

In some embodiments, the training step specifically comprises:

s10: inputting a known inefficient SQL statement, corresponding historical application information and historical system information into an input layer;

s20: generating efficiency factors influencing SQL statements and corresponding weights according to the historical application information and the historical system information;

s30: setting the structural features output by an SQL analysis layer, analyzing the known low-efficiency SQL statements to obtain the structural feature information of the known low-efficiency SQL statements, and further associating the efficiency factors, the corresponding weights and the structural feature information of the known low-efficiency SQL statements;

s40: inputting a plurality of known inefficient SQL sentences and corresponding historical application information and historical system information, and training the SQL analysis layer.

In an embodiment, as shown in fig. 8, the training step is the process 6 index analysis in fig. 2.

Wherein the G application information includes:

g1 relationship information: g1.1 application identification, G1.2 index identification, G1.3 operation identification, G1.4 matching degree, G1.5 efficiency factor and G1.6 efficiency information;

g2 weight information: g2.1 efficiency factor, G2.2 weight value and G2.3 regulating coefficient.

1. In combination with "Process 6: index analysis ' generated ' G application information ', and through a machine learning mode, the performance of SQL used by a large number of applications is analyzed and fed back, so that the weight of each efficiency influence factor can be obtained, and an accurate basis is provided for predicting the efficiency of new applications;

2. "Process 6: meaning of index analysis

3. And obtaining the relation between the application such as indexes and reports and the bottom SQL of the application. Therefore, the SQL analysis can be combined with the index report, and the weight of the SQL performance influence factor can be measured and calculated by obtaining the access efficiency feedback of the index and the report.

It can be understood that the application has the following beneficial effects:

1. manpower can be liberated, and efficiency is improved. Rapidly completing analysis and diagnosis on an inefficient SQL, wherein the analysis is based on the database system and the principle of a distributed database, the history access condition of a report and index application system, SQL writing experience and other aspects, and is not only directed at writing SQL statements;

2. the threshold can be lowered. The distributed database operation performance optimization analysis needs to deeply understand the principle of the distributed database, analysis and implementation personnel need to have rich database management and application development experience, and the threshold is high. By using the method introduced by the application, under the condition of collecting enough multi-source information, a detailed and accurate diagnosis report is obtained by the system, and the diagnosis report is almost zero threshold;

3. batch analysis can be performed based on the collection result of the low-efficiency SQL, database table design, SQL writing defects and the like can be rapidly and actively discovered, and the findings can further guide the improvement of design specifications and development specifications. The method has practical value for the conditions of more development and design personnel and uneven levels;

4. by tracking and analyzing a large number of reports, indexes and the operation effect of SQL application, a manager of the database can be guided, system parameter configuration, system resources and the like can be optimized and allocated, and operation, maintenance and management work can be guided. The larger the cluster, the more users and the more applications, the more practical significance is achieved;

5. not only is the application managed later after it comes online, as described above, but also it can be pre-checked before it comes online, finding possible problems in advance.

Based on the same inventive concept, another embodiment of the present application further provides an inefficient SQL statement analysis system based on a distributed database cluster, as shown in fig. 10, including:

the acquisition module 1 acquires analysis basis information and inefficient SQL statements of a distributed database cluster, wherein the analysis basis information comprises: application information and system information;

the performance analysis module 2 is used for inputting the low-efficiency SQL statement and the analysis basis information into a preset SQL performance analysis model to obtain a low-efficiency reason analysis result of the low-efficiency SQL statement;

For the same reason, in some embodiments, the system further comprises:

In a specific embodiment, the SQL performance analysis model includes: the system comprises an input layer, a data source sorting layer, an SQL analysis layer, a performance analysis layer and an output layer;

and the output layer outputs the analysis result of the inefficiency reason.

Based on the same inventive concept, in some embodiments, the model training module includes:

The SQL performance analysis model is formed by training according to historical data, and both application data and system data are known data, the known data are used as the basis of performance results, and the analysis results tend to be reasonable gradually through training. Manpower can be liberated, efficiency is improved, and analysis and diagnosis can be rapidly completed on low-efficiency SQL.

The prior art is illustrated by comparison.

Fig. 9 shows a method and an apparatus for structured query language SQL performance statistics in the prior art, where the method includes: acquiring SQL type log information, wherein the log information comprises SQL statements and execution performance information of the inefficient SQL statements; analyzing the low-efficiency SQL statement aiming at each piece of log information, and segmenting the low-efficiency SQL statement according to preset segmentation characters to obtain a segmented SQL statement; taking the divided SQL sentences as first index information of a performance statistics list, and storing the execution performance information of the SQL sentences in the current log information to the performance statistics list according to the first index information; and counting the performance data of the SQL sentences in the current log information according to the execution performance information of the SQL sentences in the current log information stored in the performance counting list. By the method and the system, the performance statistics of the SQL sentences can be quickly and efficiently carried out, and the SQL sentences with abnormal problems can be accurately found out.

In the prior art, the SQL statement is divided, and the divided SQL statement is used as the first index information of the performance statistics list. The partitioning strategy is to continuously iterate and traverse the SQL statement according to a preset separator, namely: according to a preset search rule, searching each preset segmentation character in an SQL sentence to be segmented; if the preset segmentation character is found, the SQL sentence to be segmented is segmented by taking the found preset segmentation character as a segmentation boundary line, the SQL sentence content before the preset segmentation character found in the SQL sentence to be segmented is taken as the SQL sentence to be segmented which is segmented next time, and the process is continuously repeated. This of course does not count as a disadvantage, but rather is a different processing focus than the present invention. The invention adopts a Python library sqlparse as a tool, analyzes a tree-shaped result by the sqlparse, further performs analysis processing, and classifies the result to obtain a clear structured result. Emphasis is placed on the classification and structuring process that follows. The analysis result is completed by the open source sqlparse, and the tree expression mode is more detailed and complete for describing one SQL. From tree-like to structured expression modes which can be stored, the conversion process embodies the characteristic of the 'low-efficiency SQL statement analysis method based on distributed database cluster' of the invention, and has irreplaceability.

2. The principle of judging unknown by knowing is different

One important function of the prior art is to "store the execution performance information of the SQL statement in the current log information to the performance statistics list according to the first index information; and counting the performance data of the SQL sentences in the current log information according to the execution performance information of the SQL sentences in the current log information stored in the performance counting list. The method aims to realize the performance statistics of the SQL sentences quickly and efficiently and accurately find out the SQL sentences with abnormal problems. The process is that a single historical SQL segmentation result is matched with an existing SQL segmentation result to obtain a historical result which is possibly matched with the existing SQL and is used as a prediction.

This direction of application of the prior art coincides with one of the directions of application of the present invention, but the principle is completely different.

According to the invention, according to the final application such as the report form, the index and the like, the information of the user (application user rather than the database user) of the application access, the IP, the access amount, the start-stop time (note that the start-stop time is not the start-stop time of SQL operation but the start-stop time of application execution submitted by the user), the success-failure ratio and the like covered in the application are obtained and used as the basis of the performance result. The execution and access information of the applications is fed back to the system of the invention to be used as the adjusting basis of the weight of the efficiency factor to the efficiency influence, and the application participates in the analysis process of the low-efficiency SQL. And automatically completing the process of adjusting the weight of the factor and tracking and feeding back the adjusted result in a machine learning mode. So that the adjustment result tends to be more and more reasonable. Based on a set of reasonable efficiency factors and weight information obtained by historical access, the system can perform operation prediction on SQL which is not on-line, and can also perform efficiency prediction on application corresponding to the SQL.

3. Feature fusion closeness to specific database types is not the same

This prior art can ignore any type of database, only regarding the relationship between SQL and SQL. The invention closely combines the characteristics of the distributed database, and the whole architecture and scheme are fused with the experience of the performance optimization work of the distributed database for many years, so that the experiences are fused in the system in an automatic mode through design.

The systems, devices, modules or units illustrated in the above embodiments may be implemented by a computer chip or an entity, or by a product with certain functions. A typical implementation device is a computer device, which may be, for example, a personal computer, a laptop computer, a cellular telephone, a camera phone, a smart phone, a personal digital assistant, a media player, a navigation device, an email device, a game console, a tablet computer, a wearable device, or a combination of any of these devices.

In a typical example, the computer device specifically comprises a memory, a processor and a computer program stored on the memory and executable on the processor, the processor implementing the method performed by the client as described above when executing the program, or the processor implementing the method performed by the server as described above when executing the program.

Referring now to FIG. 11, shown is a schematic diagram of a computer device 600 suitable for use in implementing embodiments of the present application.

As shown in fig. 11, the computer apparatus 600 includes a Central Processing Unit (CPU)601 which can perform various appropriate works and processes according to a program stored in a Read Only Memory (ROM)602 or a program loaded from a storage section 608 into a Random Access Memory (RAM)) 603. In the RAM603, various programs and data necessary for the operation of the system 600 are also stored. The CPU601, ROM602, and RAM603 are connected to each other via a bus 604. An input/output (I/O) interface 605 is also connected to bus 604.

The following components are connected to the I/O interface 605: an input portion 606 including a keyboard, a mouse, and the like; an output portion 607 including a display such as a Cathode Ray Tube (CRT), a Liquid Crystal Display (LCD), and the like, and a speaker; a storage section 608 including a hard disk and the like; and a communication section 609 including a network interface card such as a LAN card, a modem, or the like. The communication section 609 performs communication processing via a network such as the internet. The driver 610 is also connected to the I/O interface 606 as needed. A removable medium 611 such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, or the like is mounted on the drive 610 as necessary, so that a computer program read out therefrom is mounted as necessary on the storage section 608.

In particular, according to an embodiment of the present invention, the processes described above with reference to the flowcharts may be implemented as computer software programs. For example, embodiments of the invention include a computer program product comprising a computer program tangibly embodied on a machine-readable medium, the computer program comprising program code for performing the method illustrated in the flow chart. In such an embodiment, the computer program may be downloaded and installed from a network through the communication section 609, and/or installed from the removable medium 611.

Computer-readable media, including both non-transitory and non-transitory, removable and non-removable media, may implement information storage by any method or technology. The information may be computer readable instructions, data structures, modules of a program, or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), Static Random Access Memory (SRAM), Dynamic Random Access Memory (DRAM), other types of Random Access Memory (RAM), Read Only Memory (ROM), Electrically Erasable Programmable Read Only Memory (EEPROM), flash memory or other memory technology, compact disc read only memory (CD-ROM), Digital Versatile Discs (DVD) or other optical storage, magnetic cassettes, magnetic tape magnetic disk storage or other magnetic storage devices, or any other non-transmission medium that can be used to store information that can be accessed by a computing device. As defined herein, computer readable media does not include transitory computer readable media (transmyedia) such as modulated data signals and carrier waves.

For convenience of description, the above devices are described as being divided into various units by function, and are described separately. Of course, the functionality of the units may be implemented in one or more software and/or hardware when implementing the present application.

The present invention is described with reference to flowchart illustrations and/or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each flow and/or block of the flow diagrams and/or block diagrams, and combinations of flows and/or blocks in the flow diagrams and/or block diagrams, can be implemented by computer program instructions. These computer program instructions may be provided to a processor of a general purpose computer, special purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.

These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer-readable memory produce an article of manufacture including instruction means which implement the function specified in the flowchart flow or flows and/or block diagram block or blocks.

These computer program instructions may also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.

It should also be noted that the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising an … …" does not exclude the presence of other like elements in a process, method, article, or apparatus that comprises the element.

As will be appreciated by one skilled in the art, embodiments of the present application may be provided as a method, system, or computer program product. Accordingly, the present application may take the form of an entirely hardware embodiment, an entirely software embodiment or an embodiment combining software and hardware aspects. Furthermore, the present application may take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, and the like) having computer-usable program code embodied therein.

The application may be described in the general context of computer-executable instructions, such as program modules, being executed by a computer. Generally, program modules include routines, programs, objects, components, data structures, etc. that perform particular tasks or implement particular abstract data types. The application may also be practiced in distributed computing environments where tasks are performed by remote processing devices that are linked through a communications network. In a distributed computing environment, program modules may be located in both local and remote computer storage media including memory storage devices.

The embodiments in the present specification are described in a progressive manner, and the same and similar parts among the embodiments are referred to each other, and each embodiment focuses on the differences from the other embodiments. In particular, for the system embodiment, since it is substantially similar to the method embodiment, the description is simple, and for the relevant points, reference may be made to the partial description of the method embodiment.

The above description is only an example of the present application and is not intended to limit the present application. Various modifications and changes may occur to those skilled in the art. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present application should be included in the scope of the claims of the present application.

Claims

1. An inefficient SQL statement analysis method based on a distributed database cluster is characterized by comprising the following steps:

2. The inefficient SQL statement analysis method of claim 1, further comprising:

establishing the SQL performance analysis model;

3. The inefficient SQL statement analysis method of claim 2, wherein the building the SQL performance analysis model comprises:

and the output layer outputs the analysis result of the inefficiency reason.

4. The inefficient SQL statement analysis method of claim 2, wherein training the SQL performance analysis model with a plurality of known inefficient SQL statements, corresponding historical application information, and historical system information in the distributed database cluster comprises:

5. An inefficient SQL statement analysis system based on a distributed database cluster, comprising:

6. The inefficient SQL statement analysis system of claim 5, further comprising:

7. The inefficient SQL statement analysis system of claim 6, wherein the SQL performance analysis model comprises: the system comprises an input layer, a data source sorting layer, an SQL analysis layer, a performance analysis layer and an output layer;

and the output layer outputs the analysis result of the inefficiency reason.

8. The inefficient SQL statement analysis system of claim 6, wherein the model training module comprises:

9. A computer device comprising a memory, a processor and a computer program stored on the memory and executable on the processor, characterized in that the steps of the method of any of claims 1 to 4 are implemented when the program is executed by the processor.

10. A computer-readable storage medium, on which a computer program is stored which, when being executed by a processor, carries out the steps of the method of any one of claims 1 to 4.