Disclosure of Invention
The invention aims to provide a multidimensional comprehensive database SQL (structured query language) auditing and optimizing method and system, which are used for solving the problems that in the prior art, SQL database statements are difficult to audit and bring great influence on the environment on lines.
In order to achieve the purpose, the invention provides the following technical scheme: a multidimensional comprehensive database SQL checking system comprises a WEB front end, a service background and a database,
the WEB front end comprises a work order submitting module, a work order auditing module, an optimized recording module, a standard configuration module and a quality visualization module,
a submit work order module used for creating an SQL check work order;
the work order auditing module is used for auditing the created SQL auditing work order;
the optimization recording module is used for storing audit records and optimization records in work order audit;
the standard configuration module is used for carrying out standard standardized setting on the SQL auditing standard;
the quality visualization module is used for visualizing the SQL examination quality;
the service background comprises an API service module, a data acquisition module, an SQL management module, an auditing engine module, an optimizing engine module and a task scheduling module,
API service module, which is used to manage the data transmission interface between modules;
the data acquisition module is used for capturing basic data required by examination from a data source;
the SQL management module is used for completing the full life cycle management of the checked object;
the auditing engine module is used for finishing the definition and configuration work of the rule specification;
the optimization engine module is used for completing analysis of SQL execution characteristics, execution plans and operation efficiency and providing optimization suggestions;
the task scheduling module is used for scheduling the auditing tasks of a plurality of work orders;
the database comprises an auditing intermediate base module, a testing database module and an on-line database module,
the auditing intermediate library module is used for storing the acquired data and intermediate data and result data in the processing process;
the test database module is used for storing the test data of the SQL;
and the online database module is used for storing data in the SQL formal operation process.
Preferably, the core data stored in the database building module in the audit is an object class and an SQL class.
Preferably, the quality visualization module is used for digitizing the quality of the SQL examination by the scoring system and visualizing the examination quality by a billboard mode.
Preferably, the audit engine module is an audit execution part for completing the specific rule.
In order to achieve the above purpose, the invention also provides the following technical scheme: a multidimensional SQL (structured query language) auditing and optimizing method for an integrated database is based on an SQL auditing system and comprises the following specific steps:
(1) filling SQL check work orders through a work order submitting module of the WEB front end;
(2) sending the filled SQL audit work order to a construction site audit module to form an audit request;
(3) sending the request to a service background through an API service module to carry out SQL examination;
(4) scheduling the requested SQL by a task scheduling module, and then auditing and outputting optimization suggestions to the audited object by an SQL management module, an auditing engine module and an optimization engine module;
(5) the auditing and optimizing suggestions are fed back to the work order auditing module through the API service module;
(6) simultaneously storing the optimization suggestion into an optimization recording module;
(7) performing data display on the checked SQL correspondingly through a quality visualization module;
(8) in the SQL operation process needing to be checked, SQL basic data acquisition is carried out through a data acquisition module;
(9) matching the current CMDB information to the slave library according to the metadata mapping relation, and initiating an audit requirement to the slave library;
(10) after the audit of the audit service, pushing an audit result to an API service module;
(11) the auditing and optimizing suggestions are fed back to the work order auditing module through the API service module;
(12) and then storing the optimization suggestion into an optimization recording module.
Preferably, grammar meaning audit rules are set from the library according to step (9).
Preferably, according to steps (9) and (10), the audit service includes inclusion audit tool audits and targeted audit logic customization.
Preferably, the targeted audit logic customization includes the customization of audit information and the customization of audit logic.
Preferably, the audit information includes three types,
the first type of information is suggested information that is either significantly wrong or itself violates the basic specification,
the second category of information is a potential problem,
the third type of information is improvement suggestion type information.
Compared with the prior art, the invention has the beneficial effects that: the method and the system can greatly accelerate the speed of SQL examination, database structure optimization and SQL optimization, reduce the daily working pressure of the database DBA and effectively prevent the influence of SQL performance hidden danger on the online environment.
For research personnel, the problem can be conveniently positioned and timely modified; in addition, by mastering the rule specification, the SQL development experience can be improved by guiding the rule specification to design and develop work.
For the DBA, auditing can be performed visually and quickly, inefficient SQL can be screened in batches, and general problems can be diagnosed quickly through information provided by the system.
For research and development management personnel, the SQL quality of each project and each individual can be tracked, quality problems can be found in time, and supervision and improvement are facilitated.
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.
Referring to fig. 1, the present invention provides a technical solution: a multidimensional comprehensive database SQL checking system comprises a WEB front end, a service background and a database,
the WEB front end comprises a work order submitting module, a work order auditing module, an optimized recording module, a standard configuration module and a quality visualization module,
a submit work order module used for creating an SQL check work order;
the work order auditing module is used for auditing the created SQL auditing work order;
the optimization recording module is used for storing audit records and optimization records in work order audit;
the standard configuration module is used for carrying out standard standardized setting on the SQL auditing standard;
the quality visualization module is used for visualizing the SQL examination quality;
the service background comprises an API service module, a data acquisition module, an SQL management module, an auditing engine module, an optimizing engine module and a task scheduling module,
API service module, which is used to manage the data transmission interface between modules;
the data acquisition module is used for capturing basic data required by examination from a data source;
the SQL management module is used for completing the full life cycle management of the checked object;
the auditing engine module is used for finishing the definition and configuration work of the rule specification;
the optimization engine module is used for completing analysis of SQL execution characteristics, execution plans and operation efficiency and providing optimization suggestions;
the task scheduling module is used for scheduling the auditing tasks of a plurality of work orders;
the database comprises an auditing intermediate base module, a testing database module and an on-line database module,
the auditing intermediate library module is used for storing the acquired data and intermediate data and result data in the processing process;
the test database module is used for storing the test data of the SQL;
and the online database module is used for storing data in the SQL formal operation process.
And the core data stored in the database building module in the auditing process are an object class and an SQL class.
The quality visualization module is used for digitizing the quality of the SQL examination through the scoring system and visualizing the examination quality through a board watching mode.
The auditing engine module is an auditing execution part for completing specific rules.
A multidimensional SQL (structured query language) auditing and optimizing method for an integrated database is based on an SQL auditing system and comprises the following specific steps:
(1) filling SQL check work orders through a work order submitting module of the WEB front end;
(2) sending the filled SQL audit work order to a construction site audit module to form an audit request;
(3) sending the request to a service background through an API service module to carry out SQL examination;
(4) scheduling the requested SQL by a task scheduling module, and then auditing and outputting optimization suggestions to the audited object by an SQL management module, an auditing engine module and an optimization engine module;
(5) the auditing and optimizing suggestions are fed back to the work order auditing module through the API service module;
(6) simultaneously storing the optimization suggestion into an optimization recording module;
(7) performing data display on the checked SQL correspondingly through a quality visualization module;
(8) in the SQL operation process needing to be checked, SQL basic data acquisition is carried out through a data acquisition module;
(9) matching the current CMDB information to the slave library according to the metadata mapping relation, and initiating an audit requirement to the slave library;
(10) after the audit of the audit service, pushing an audit result to an API service module;
(11) the auditing and optimizing suggestions are fed back to the work order auditing module through the API service module;
(12) and then storing the optimization suggestion into an optimization recording module.
And (9) setting a grammar meaning auditing rule from the library.
According to steps (9) and (10), the audit service includes inclusion audit tool audits and targeted audit logic customization.
Targeted audit logic customization includes customization of audit information and customization of audit logic.
The audit information includes three types of information,
the first type of information is suggested information that is either significantly wrong or itself violates the basic specification,
the second category of information is a potential problem,
the third type of information is improvement suggestion type information.
By the technical scheme, aiming at the problems in the background technology, the multi-dimensional SQL examination and optimization method and the system for the comprehensive database are provided, and the method comprises 3 statement dimensions: DDL, DML, DQL, 2 time dimensions: and (5) auditing in advance and optimizing after the fact.
For the examination of the DDL, the basic requirements of the business are that the object change requirements are generated only when the business changes, such examination is mainly performed in advance, and the important attention is paid to the control of the DBA.
The DML auditing is based on SQL performance or influence range, and also includes DML flashback (i.e. backup is first obtained), and is also pre-auditing.
The DQL query statement is the requirement of another dimension, more is considered in the aspects of performance and safety, and the result is given through automatic analysis of SQL execution characteristics and SQL execution planning, mainly the operation optimization after the fact;
as shown in fig. 2, firstly, the front end fills in an SQL audit work order, or shows SQL to be optimized from data acquisition, and after submitting a request, the API service is used to interface with the service and result callback of the back end.
And 3-5, matching the existing CMDB information to the slave library according to the metadata mapping relation, initiating a checking requirement to the slave library, and checking syntax semantics at the slave library end.
After the audit of the audit service, the audit result is pushed to the API server, which is the core of the audit service and is to be completely logically controllable, and this can be divided into two levels of work:
one is to fully absorb the advantages of existing auditing tools, such as integration;
and the other is to carry out targeted customization on the auditing logic, wherein the customization is divided into two parts, namely the customization of the auditing information and the customization of the auditing logic.
Then, the audit information is classified into three types:
the first category of information is suggestions that are clearly erroneous or that themselves violate the basic specification, indicating that these suggestions are, for example, that the character set of the table does not meet the criteria, that the table name is a mixture of cases, etc., that the field name is a keyword, etc. This category must be modified to screen out the emphasis cues.
The second kind of information is potential problems, such as the use of an unresuggested data type (lob), the range of a timestamp type is limited, and the like, and the significance of the information is greater, so that the potential problems can be avoided as much as possible.
The third type of information is improvement suggestion type information, such as comments on table fields, and we may have no way to require comments on all fields submitted by development, or to set default values, but we can come up as improvements and suggestions.
The three types of information are configured and made into a scoring system, so that the whole process is more transparent;
the use of the scoring system is more friendly to the business, and the following points are designed for the scoring part:
the auditing suggestions are divided into three levels of 'must improve', 'potential problem' and 'suggested improve', the number of the deduction points appearing in one level is respectively 5,2 and 1, and the fraction ratio of each level is 50,30 and 20;
the total score was 100 points, and the lowest score was 10 points, i.e., <10 points was set to 10 points.
And finally, according to the recommendation information of the audit, making the calling information persistent, including SQL and the audit recommendation, and then comparing and following within a certain time range to see which recommendations are not good enough and which can be continuously improved.
The method and the system can greatly accelerate the speed of SQL examination, database structure optimization and SQL optimization, reduce the daily working pressure of the database DBA and effectively prevent the influence of SQL performance hidden danger on the online environment.
For research personnel, the problem can be conveniently positioned and timely modified; in addition, by mastering the rule specification, the SQL development experience can be improved by guiding the rule specification to design and develop work.
For the DBA, auditing can be performed visually and quickly, inefficient SQL can be screened in batches, and general problems can be diagnosed quickly through information provided by the system.
For research and development management personnel, the SQL quality of each project and each individual can be tracked, quality problems can be found in time, and supervision and improvement are facilitated.
Although embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that changes, modifications, substitutions and alterations can be made in these embodiments without departing from the principles and spirit of the invention, the scope of which is defined in the appended claims and their equivalents.