WO2018058959A1 - Sql审核方法、装置、服务器及存储设备 - Google Patents

Sql审核方法、装置、服务器及存储设备 Download PDF

Info

Publication number
WO2018058959A1
WO2018058959A1 PCT/CN2017/083645 CN2017083645W WO2018058959A1 WO 2018058959 A1 WO2018058959 A1 WO 2018058959A1 CN 2017083645 W CN2017083645 W CN 2017083645W WO 2018058959 A1 WO2018058959 A1 WO 2018058959A1
Authority
WO
WIPO (PCT)
Prior art keywords
audit
sql
result
sql statement
template
Prior art date
Application number
PCT/CN2017/083645
Other languages
English (en)
French (fr)
Inventor
侯松
汪洋
Original Assignee
平安科技（深圳）有限公司
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by 平安科技（深圳）有限公司 filed Critical 平安科技（深圳）有限公司
Priority to US15/579,376 priority Critical patent/US10402402B2/en
Priority to EP17800997.3A priority patent/EP3343411A4/en
Priority to JP2017565717A priority patent/JP2018532171A/ja
Priority to AU2017268630A priority patent/AU2017268630A1/en
Priority to KR1020187015349A priority patent/KR102230661B1/ko
Publication of WO2018058959A1 publication Critical patent/WO2018058959A1/zh

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/245—Query processing
    • G06F16/2453—Query optimisation
    • G06F16/24534—Query rewriting; Transformation
    • G06F16/2454—Optimisation of common expressions
    • 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/2455—Query execution
    • G06F16/24553—Query execution of query operations
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F11/00—Error detection; Error correction; Monitoring
    • G06F11/30—Monitoring
    • G06F11/34—Recording or statistical evaluation of computer activity, e.g. of down time, of input/output operation ; Recording or statistical evaluation of user activity, e.g. usability assessment
    • G06F11/3409—Recording or statistical evaluation of computer activity, e.g. of down time, of input/output operation ; Recording or statistical evaluation of user activity, e.g. usability assessment for performance assessment
    • G06F11/3433—Recording or statistical evaluation of computer activity, e.g. of down time, of input/output operation ; Recording or statistical evaluation of user activity, e.g. usability assessment for performance assessment for load management
    • 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/21—Design, administration or maintenance of databases
    • G06F16/217—Database tuning
    • 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/24—Querying
    • G06F16/245—Query processing
    • G06F16/2453—Query optimisation

Definitions

  • the present application relates to the field of database technologies, and in particular, to a SQL audit method, device, server, and storage device.
  • SQL audit In the work of the database, often need to execute SQL on the database (Structured Query Language, structured query language) statements are audited, so that SQL statements with poor performance are prevented from entering the production system, resulting in a decrease in the overall performance of the database.
  • SQL audit in the traditional way, only SQL statements can be parsed by SQL scripts, and some simple audit work can be performed, and SQL can not be further examined. For example, SQL execution plan review, etc., resulting in inaccurate SQL audit.
  • a SQL audit method, apparatus, server, and storage device are provided.
  • a SQL audit method that includes:
  • An audit result set is generated based on the audit result.
  • a SQL auditing device including:
  • An extracting module configured to extract, from the slow log data, an SQL statement to be audited and basic information matching the SQL statement;
  • a parsing module configured to identify and parse the SQL statement, and obtain an analysis result
  • An auditing module configured to review the parsing result and/or the basic information according to an audit item in a preset auditing template, and obtain an audit result
  • a generating module is configured to generate an audit result set according to the audit result.
  • a server comprising a memory and a processor, the memory storing computer executable instructions, the computer executable instructions being executed by the processor, such that the processor performs the following steps:
  • An audit result set is generated based on the audit result.
  • One or more storage devices storing computer executable instructions that, when executed by one or more processors, cause the one or more processors to perform the following steps:
  • An audit result set is generated based on the audit result.
  • Figure 1 is a block diagram of a server in one embodiment
  • FIG. 2 is a flow chart of a SQL audit method in an embodiment
  • FIG. 3 is a flow chart of an audit in accordance with an audit item in a preset audit template in one embodiment
  • FIG. 4 is a flow chart of establishing a SQL baseline and performing analysis in one embodiment
  • Figure 5 is a flow chart showing the establishment of a SQL baseline in one embodiment
  • Figure 6 is a block diagram of an SQL auditing device in one embodiment
  • FIG. 7 is a block diagram of an extraction module in one embodiment
  • FIG. 8 is a block diagram of an SQL auditing apparatus in another embodiment
  • Figure 9 is a block diagram of a building block in one embodiment.
  • FIG. 1 is a block diagram of a server in one embodiment.
  • the server includes a processor, memory, and network interface connected by a system bus.
  • the server's processor is used to provide computing and control capabilities to support the operation of the entire server.
  • the server's memory is used to store data, code instructions, etc., and in one embodiment, the memory can include non-volatile storage media and internal memory.
  • the non-volatile storage medium stores an operating system, a database, and computer executable instructions, and the database stores slow log data, and the computer executable instructions are used to implement a SQL auditing method applicable to the server provided in the embodiment of the present application. .
  • the internal memory provides a cached operating environment for operating systems and computer executable instructions in a non-volatile storage medium.
  • the network interface of the server is used to communicate with an external terminal through a network connection, such as receiving a modified SQL statement sent by the terminal and returning SQL audit data to the terminal.
  • the server can be implemented with a stand-alone server or a server cluster consisting of multiple servers. It will be understood by those skilled in the art that the structure shown in FIG. 1 is only a block diagram of a part of the structure related to the solution of the present application, and does not constitute a limitation on the server to which the solution of the present application is applied.
  • the specific server may include More or fewer components are shown in the figures, or some components are combined, or have different component arrangements.
  • a SQL auditing method is provided, which is applicable to the server shown in FIG. 1, and the method includes the following steps:
  • step S210 slow log data is obtained.
  • the server can obtain slow log data from a target database with imperfect data dictionary information by means of an external link table, for example, a target database such as MySQL or Postgres, wherein the slow log data refers to slow query log data, which is exceeded in the database.
  • the SQL statement query at the specified time is called "slow query”.
  • the slow log data record has SQL statements that exceed the specified time, and related data generated when SQL statements are executed such as log time, storage location, execution time, and performer number.
  • Step S220 extracting the SQL statement to be audited and the basic information matching the SQL statement from the slow log data.
  • the server may extract the SQL statement to be audited from the obtained slow log data, and the basic information matching the SQL statement to be audited for reviewing the SQL statement, and the basic information may be extracted according to the audit item in the preset audit template.
  • the basic information may include the number of executions, total execution time, execution operations, execution overhead, and the like. Extracting the SQL statement to be audited through the slow log data is equivalent to filtering the SQL statement, filtering the SQL statement with good performance according to the execution time of the SQL statement, and performing the SQL statement with poor performance exceeding the specified time.
  • step S220 extracts the SQL statement to be audited and the basic information matching the SQL statement from the slow log data, including (a) and (b):
  • the server can analyze the slow log data and extract the data in the slow log data according to a preset format.
  • the preset format can be set according to actual requirements.
  • the preset format may include a field name, a field type, and a field content, and the slow log is extracted. The name of each field in the data, and the field type and field content corresponding to the field name.
  • the slow log data list may be generated according to the data extracted from the slow log data according to the preset format.
  • the format of the slow log data list corresponds to the preset format and the extracted data.
  • the slow log data list may be as shown in Table 1:
  • the log_time is the log time
  • the user_name is the user name
  • the database_name is the database name
  • the userid is the user number
  • the calls are the execution times
  • the total_time is the total execution time. It can be understood that the content and format of the slow log data list are not limited to the table. The content and format shown in 1.
  • the server may extract the field name named query as the field name of the execution statement in the slow log data list as the SQL statement to be audited, and extract the slow log data list according to the audit item in the preset audit template.
  • the corresponding basic information matching the SQL statement for example, the audit item in the preset audit template includes the execution time, and the field contents of the fields named calls and total_time are extracted from the slow log data list as basic information matching the SQL statement. .
  • Step S230 identifying and parsing the SQL statement to obtain an analysis result.
  • the server can identify and parse the SQL statement extracted from the slow log data, and obtain the parsing result that can be recognized by the audit template.
  • the SQL tree parsing result can be obtained after parsing the SQL statement, which is convenient according to the audit template.
  • the audit project reviews the results of the analysis.
  • step S240 the analysis result and/or the basic information are reviewed according to the audit items in the preset audit template, and the audit result is obtained.
  • the default review template can include an execution plan review template and a SQL syntax review template.
  • the execution plan audit template is to audit the execution plan of the SQL statement.
  • a SQL statement indicates the data that you want to get, but it does not indicate how to obtain the data. For example, through a SQL statement, you can take out all the residences in Shenzhen. client.
  • the database in the server receives the SQL, the calculation will choose to read the entire customer table or use the index to obtain data.
  • the method in which the final SQL statement is physically executed is called the execution plan.
  • Audit items that execute a plan review template can include slow queries, high cost operations, and more.
  • the SQL syntax review template is to review the syntax semantics of the SQL statement itself.
  • the audit item of the SQL syntax review template may include prohibiting duplicate query clauses and prohibiting select selection. *Wait.
  • the server may review the analysis results and/or basic information one by one according to each audit item in the preset audit template. When any of the audit items in the audit template is not met, the review fails, and all of them meet the review template. When each item is reviewed, it is approved.
  • Step S250 generating an audit result set according to the audit result.
  • the server may audit multiple SQL statements extracted from the slow log data and basic information matching each SQL statement, and generate an audit result set according to the audit result.
  • the audit result set may include the SQL number of the audited SQL statement and the database name. , execution time, command type, audit status, violation items, etc., and store the audit result set in the audit database, where the audit status indicates the audit result, and the results can be represented by numbers such as 0, 1, or other letters or symbols. To pass or pass.
  • the above SQL audit method extracts the SQL statement to be audited and the basic information matching the SQL statement from the slow log data, and according to the audit item in the preset audit template, the parsing result obtained by parsing the SQL statement and matching with the SQL statement
  • the basic information is audited.
  • step S240 reviews the analysis result and/or basic information one by one according to the audit item in the preset audit template, and obtains the audit result, which may include step S302 and step S304:
  • Step S302 according to the audit item in the execution plan review template, extracting the data matching the audit item in the basic information for review, and obtaining the audit result.
  • the preset audit template may include an execution plan review template and a SQL syntax review template, and the execution plan review template is to review the execution plan of the SQL statement.
  • the auditing item in the execution plan auditing template may include a slow query, a high-cost operation, and the like.
  • the auditing content of the slow query is to determine whether the execution time of the SQL statement is greater than a preset execution time, for example, whether the execution time is greater than 1 s (seconds).
  • the audit content of high-cost operations is to determine whether there are high-cost operations in the SQL statement, for example, whether there is Seq Scan (sequence scan), full table scan and other operations.
  • the content reviewed by the audit item of the execution plan review template generally needs to be obtained from the basic information matching the SQL statement.
  • the execution number and the total execution time need to be obtained from the basic information. And to obtain the average, that is, the execution time, and review the high-cost operation, you need to obtain the execution operation in the basic information.
  • Each audit item has a unique corresponding audit item number. When the audit fails, the audit item number that fails will be recorded.
  • Step S304 according to the audit item in the SQL grammar review template, parsing the parsing result, and performing audit according to the analysis result, and obtaining the audit result.
  • the SQL syntax review template is to review the syntax semantics of the SQL statement itself.
  • the audit item of the SQL syntax review template may include prohibiting the use of select. *, prohibit duplicate query clauses, update prohibits order By clause, update must appear where clause, prohibit nested select clause, delete must appear where clause, delete prohibits order The by clause, delete prohibits the occurrence of the limit clause. Exempting audit items with duplicate query clauses can improve the execution efficiency of SQL statements. Update prohibits the order. By clause, delete prohibits the appearance of an order by clause and delete the exception clause to prevent the occurrence of the limit clause to prevent DML (Data Manipulation Language, data manipulation language) process unnecessary ordering, update must appear where clause and delete must have the where clause of the audit item to prevent full table locks in the DML process.
  • the SQL tree parsing results obtained by the parsing may be analyzed and audited according to each audit item of the SQL syntax review template, and the audit result is obtained.
  • Each audit item in the preset audit template can be used to score the audited SQL statement corresponding to different audit values.
  • the total audit value of the SQL statement is deducted from the audit value corresponding to the audit item that fails.
  • the final audit value can be obtained.
  • the SQL statement can be clearly obtained by scoring the SQL statement. Performance is good or bad.
  • the audit data corresponding to the slow query and the high cost operation in the execution plan audit template are 1 point and 2 points respectively, and the total number of audits of the SQL statement is 100 points. If the SQL statement does not meet the audit item of the slow query, then one is deducted.
  • the final audit value is 99 points.
  • the parsing result obtained by parsing the SQL statement and/or the basic information matching the SQL statement may be audited one by one by executing the plan auditing template and the SQL grammar review template, and may be in the database with imperfect data dictionary information. SQL conducts deeper auditing, making SQL auditing more targeted and effectively improving the accuracy of SQL auditing.
  • step S250 after the audit result set is generated according to the audit result in step S250, the following steps are further included:
  • Step S402 extracting an SQL result baseline in which the audit result in the audit result set is a failed SQL statement.
  • the SQL baseline is used to record the change process of the related data and status of the SQL statement whose audit result is not passed.
  • the SQL baseline may include the contents of the SQL statement, the SQL number, the database name, the user name, the audit item number that failed the audit, the audit status, etc.
  • the audit status is not passed. After the database administrator rectifies the SQL statement, the audit status can be modified to pass.
  • step S402 extracts an audit result result centralized audit result as a SQL statement that does not pass to establish a SQL baseline, and includes the following steps:
  • Step S502 extracting the SQL statement that the audit result in the audit result set is not passed.
  • step S504 the SQL number of the SQL statement whose audit result is not passed is obtained, and the audit item number that does not pass is obtained.
  • the server can extract the audit result in the audit result set to the SQL statement that does not pass to establish the SQL baseline, and can obtain the SQL statement content, SQL number, database name, user name, and audit failure of the SQL statement whose audit result is not passed from the audit result set. Review information such as project numbers.
  • step S506 it is determined whether there is a matching SQL baseline according to the SQL number and the audit item number that does not pass. If yes, step S508 is performed, and if no, step S510 is performed.
  • Each audit item that does not pass the SQL statement can correspond to a unique SQL baseline, that is, each SQL baseline has a unique corresponding SQL number and a failed audit item number. If there are multiple audit items that the SQL statement does not pass, Create multiple corresponding SQL baselines. After the server extracts the audit result, the result of the audit is a failed SQL statement, and after obtaining the SQL number and the failed audit item number, it can first find out whether there is a corresponding SQL baseline in the audit database according to the SQL number, and if so, further In the SQL baseline corresponding to the SQL number, look for the SQL baseline that matches the audit item number. If there is, you do not need to re-establish the SQL baseline, just modify the audit status of the matching SQL baseline to fail.
  • step S508 the basic state of the SQL is marked as failing.
  • step S510 an SQL baseline is established according to the SQL number and the audit item number that does not pass.
  • the SQL baseline is established in the audit database according to the SQL number and the failed audit item number, and the audit status in the SQL baseline is marked as failing.
  • Step S404 analyzing the SQL baseline to obtain SQL audit data, and displaying the SQL audit data.
  • the server may extract the newly established one from the audit database every preset time or the status is re-marked to not pass the SQL baseline, and obtain the audit data of the SQL, for example, every hour, three hours, etc.
  • SQL audit data can include the contents of the SQL statement, SQL number, database name, user name, audit item number that fails to pass the review, etc., and display through the terminal.
  • the database administrator can rectify the SQL statement according to the displayed SQL audit data to make it conform to the audit project.
  • the audit status of the SQL baseline can be modified according to the rectification result. If the audit item that fails the SQL statement review has been rectified, The audit status of the SQL baseline that matches the rectified audit item number can be marked as pass.
  • a baseline report may be generated at intervals, such as generating a baseline daily report and a baseline monthly report.
  • the baseline report records all newly generated SQL baselines for that period of time, and a SQL baseline for changing the audit status. The database administrator understands the changes in the SQL baseline in the audit database.
  • the result of the centralized auditing of the audit result is a SQL statement that does not pass the SQL statement, which can facilitate the database administrator to rectify the SQL statement that the database fails to pass, effectively improve the efficiency of the SQL audit work, and help improve the database. Data processing performance.
  • an SQL auditing apparatus including an obtaining module 610, an extracting module 620, a parsing module 630, an auditing module 640, and a generating module 650.
  • the obtaining module 610 is configured to obtain slow log data.
  • the server can obtain slow log data from a target database with imperfect data dictionary information by means of an external link table, for example, a target database such as MySQL or Postgres, wherein the slow log data refers to slow query log data, which is exceeded in the database.
  • the SQL statement query at the specified time is called "slow query”.
  • the slow log data record has SQL statements that exceed the specified time, and related data generated when SQL statements are executed such as log time, storage location, execution time, and performer number.
  • the extracting module 620 is configured to extract, from the slow log data, the SQL statement to be audited and the basic information that matches the SQL statement.
  • the server may extract the SQL statement to be audited from the obtained slow log data, and the basic information matching the SQL statement to be audited for reviewing the SQL statement, and the basic information may be extracted according to the audit item in the preset audit template.
  • the basic information may include the number of executions, total execution time, execution operations, execution overhead, and the like. Extracting the SQL statement to be audited through the slow log data is equivalent to filtering the SQL statement, filtering the SQL statement with good performance according to the execution time of the SQL statement, and performing the SQL statement with poor performance exceeding the specified time.
  • the extraction module 620 includes a list generation unit 622 and an information extraction unit 624.
  • the list generating unit 622 is configured to analyze the slow log data according to the preset format, and generate a slow log data list.
  • the server can analyze the slow log data and extract the data in the slow log data according to a preset format.
  • the preset format can be set according to actual requirements.
  • the preset format may include a field name, a field type, and a field content, and the slow log is extracted. The name of each field in the data, and the field type and field content corresponding to the field name.
  • the slow log data list may be generated according to the data extracted from the slow log data according to the preset format.
  • the format of the slow log data list corresponds to the preset format and the extracted data.
  • the slow log data list may be as shown in Table 1.
  • Log_time is the log time
  • user_name is the user name
  • database_name is the database name
  • userid is the user number
  • calls is the number of executions
  • total_time is the total execution time. It can be understood that the content and format of the slow log data list are not limited to Table 1. The content and format shown.
  • the information extracting unit 624 is configured to extract, from the slow log data list, the SQL statement to be audited and the basic information matching the SQL statement according to the field name in the slow log data list.
  • the server may extract the field name named query as the field name of the execution statement in the slow log data list as the SQL statement to be audited, and extract the slow log data list according to the audit item in the preset audit template.
  • the corresponding basic information matching the SQL statement for example, the audit item in the preset audit template includes the execution time, and the field contents of the fields named calls and total_time are extracted from the slow log data list as basic information matching the SQL statement. .
  • the parsing module 630 is configured to identify and parse the SQL statement to obtain an analysis result.
  • the server can identify and parse the SQL statement extracted from the slow log data, and obtain the parsing result that can be recognized by the audit template.
  • the SQL tree parsing result can be obtained after parsing the SQL statement, which is convenient according to the audit template.
  • the audit project reviews the results of the analysis.
  • the auditing module 640 is configured to review the analysis result and/or the basic information according to the audit item in the preset audit template one by one, and obtain the audit result.
  • the default review template can include an execution plan review template and a SQL syntax review template.
  • the execution plan audit template is to audit the execution plan of the SQL statement.
  • a SQL statement indicates the data that you want to get, but it does not indicate how to obtain the data. For example, through a SQL statement, you can take out all the residences in Shenzhen. client.
  • the database in the server receives the SQL, the calculation will choose to read the entire customer table or use the index to obtain data.
  • the method in which the final SQL statement is physically executed is called the execution plan.
  • Audit items that execute a plan review template can include slow queries, high cost operations, and more.
  • the SQL syntax review template is to review the syntax semantics of the SQL statement itself.
  • the audit item of the SQL syntax review template may include prohibiting duplicate query clauses and prohibiting select selection. *Wait.
  • the server may review the analysis results and/or basic information one by one according to each audit item in the preset audit template. When any of the audit items in the audit template is not met, the review fails, and all of them meet the review template. When each item is reviewed, it is approved.
  • the generating module 650 is configured to generate an audit result set according to the audit result.
  • the server may audit multiple SQL statements extracted from the slow log data and basic information matching each SQL statement, and generate an audit result set according to the audit result.
  • the audit result set may include the SQL number of the audited SQL statement and the database name. , execution time, command type, audit status, violation items, etc., and store the audit result set in the audit database, where the audit status indicates the audit result, and the results can be represented by numbers such as 0, 1, or other letters or symbols. To pass or pass.
  • the above SQL auditing device extracts the SQL statement to be audited and the basic information matching the SQL statement from the slow log data, and matches the parsed result obtained by parsing the SQL statement and matches the SQL statement according to the audit item in the preset audit template.
  • the basic information is audited.
  • the auditing module 640 is further configured to: according to the auditing item in the execution plan auditing template, extract the data matching the auditing item in the basic information for review, and obtain the audit result.
  • the preset audit template may include an execution plan review template and a SQL syntax review template, and the execution plan review template is to review the execution plan of the SQL statement.
  • the auditing item in the execution plan auditing template may include a slow query, a high-cost operation, and the like, wherein the auditing content of the slow query is to determine whether the execution time of the SQL statement is greater than a preset execution time, for example, whether the execution time is greater than 1 s, high.
  • the audit of the cost operation is to determine whether there are high-cost operations in the SQL statement, for example, whether there is Seq Scan, full table scan, etc.
  • the content reviewed by the audit item of the execution plan review template generally needs to be obtained from the basic information matching the SQL statement.
  • the execution number and the total execution time need to be obtained from the basic information. And to obtain the average, that is, the execution time, and review the high-cost operation, you need to obtain the execution operation in the basic information.
  • Each audit item has a unique corresponding audit item number. When the audit fails, the audit item number that fails will be recorded.
  • the auditing module 640 is further configured to perform the syntax analysis on the analysis result according to the audit item in the SQL syntax review template, and perform the audit according to the analysis result, and obtain the audit result.
  • the SQL syntax review template is to review the syntax semantics of the SQL statement itself.
  • the audit item of the SQL syntax review template may include prohibiting the use of select. *, prohibit duplicate query clauses, update prohibits order By clause, update must appear where clause, prohibit nested select clause, delete must appear where clause, delete prohibits order The by clause, delete prohibits the occurrence of the limit clause. Exempting audit items with duplicate query clauses can improve the execution efficiency of SQL statements. Update prohibits the order. By clause, delete prohibits order The by clause and delete prohibit the occurrence of the limit clause of the audit item to prevent unnecessary sorting of the DML process, update must appear where clause and delete must have the where clause of the audit item to prevent full table locks in the DML process.
  • the SQL tree parsing results obtained by the parsing may be analyzed and audited according to each audit item of the SQL syntax review template, and the audit result is obtained.
  • Each audit item in the preset audit template can be used to score the audited SQL statement corresponding to different audit values.
  • the total audit value of the SQL statement is deducted from the audit value corresponding to the audit item that fails.
  • the final audit value can be obtained.
  • the SQL statement can be clearly obtained by scoring the SQL statement. Performance is good or bad.
  • the audit data corresponding to the slow query and the high cost operation in the execution plan audit template are 1 point and 2 points respectively, and the total number of audits of the SQL statement is 100 points. If the SQL statement does not meet the audit item of the slow query, then one is deducted.
  • the final audit value is 99 points.
  • the parsing result obtained by parsing the SQL statement and/or the basic information matching the SQL statement may be audited one by one by executing the plan auditing template and the SQL grammar review template, and may be in the database with imperfect data dictionary information. SQL conducts deeper auditing, making SQL auditing more targeted and effectively improving the accuracy of SQL auditing.
  • the SQL auditing apparatus includes an obtaining module 610, an extracting module 620, a parsing module 630, an auditing module 640, and a generating module 650, and an establishing module 660 and a displaying module 670.
  • the establishing module 660 is configured to extract a SQL baseline for the SQL statement that fails to pass the audit result in the audit result set.
  • the SQL baseline is used to record the change process of the related data and status of the SQL statement whose audit result is not passed.
  • the SQL baseline may include the contents of the SQL statement, the SQL number, the database name, the user name, the audit item number that failed the audit, the audit status, etc.
  • the audit status is not passed. After the database administrator rectifies the SQL statement, the audit status can be modified to pass.
  • the setup module 660 includes a statement extraction unit 662, an acquisition unit 664, and a determination unit 666.
  • the statement extracting unit 662 is configured to extract an SQL statement whose audit result in the audit result set is not passed.
  • the obtaining unit 664 is configured to obtain an SQL number of the SQL statement whose audit result is not passed, and an audit item number that does not pass.
  • the server can extract the audit result in the audit result set to the SQL statement that does not pass to establish the SQL baseline, and can obtain the SQL statement content, SQL number, database name, user name, and audit failure of the SQL statement whose audit result is not passed from the audit result set. Review information such as project numbers.
  • the determining unit 666 is configured to determine, according to the SQL number and the audit item number that does not pass, whether there is a matching SQL baseline, and if yes, mark the status of the SQL baseline as failing, and if not, according to the SQL number and the failed audit item The number establishes the SQL baseline.
  • Each audit item that does not pass the SQL statement can correspond to a unique SQL baseline, that is, each SQL baseline has a unique corresponding SQL number and a failed audit item number. If there are multiple audit items that the SQL statement does not pass, Create multiple corresponding SQL baselines. After extracting the audit result, the result of the audit is a failed SQL statement, and after obtaining the SQL number and the failed audit item number, you can first find out whether there is a corresponding SQL baseline in the audit database according to the SQL number, and if so, further In the SQL baseline corresponding to the SQL number, look for the SQL baseline that matches the audit item number. If there is no need to re-establish the SQL baseline, simply change the audit status of the matching SQL baseline to fail. If there is no SQL baseline matching the SQL number and the failed audit item number, the SQL baseline is established in the audit database according to the SQL number and the failed audit item number, and the audit status in the SQL baseline is marked as failing.
  • the display module 670 is configured to analyze the SQL baseline to obtain SQL audit data and display the SQL audit data.
  • the server may extract the newly established ones from the audit database at preset time or the status is re-marked to not pass the SQL baseline, and obtain the audit data of the SQL, for example, every hour, three hours, etc., SQL
  • the audit data may include the contents of the SQL statement, the SQL number, the database name, the user name, the audit item number that the audit fails, and the like, and is displayed through the terminal.
  • the database administrator can rectify the SQL statement according to the displayed SQL audit data to make it conform to the audit project. After the rectification is completed, the audit status of the SQL baseline can be modified according to the rectification result. If the audit item that fails the SQL statement review has been rectified, The audit status of the SQL baseline that matches the rectified audit item number can be marked as pass.
  • a baseline report may be generated at intervals, such as generating a baseline daily report and a baseline monthly report.
  • the baseline report records all newly generated SQL baselines for that period of time, and a SQL baseline for changing the audit status. The database administrator understands the changes in the SQL baseline in the audit database.
  • the result of the centralized auditing of the audit result is a SQL statement that does not pass the SQL statement, which can facilitate the database administrator to rectify the SQL statement that the database fails to pass, effectively improve the efficiency of the SQL audit work, and help improve the database. Data processing performance.
  • the various modules in the above SQL auditing device may be implemented in whole or in part by software, hardware or a combination thereof.
  • the display module 670 can send SQL audit data to the terminal through the network interface on the server, so that the terminal displays the SQL audit data;
  • the audit module 640 can follow the preset audit by the processor of the server.
  • the audit item in the template, the analysis result and/or the basic information is reviewed, and the audit result is obtained, wherein the processor can be a central processing unit, a microprocessor, or the like.
  • the above modules may be embedded in the hardware of the server or may be stored in the memory of the server in a software form, so that the processor can call the corresponding operations of the above modules.
  • the storage device may be a magnetic disk, an optical disk, or a read-only storage memory (Read-Only) Memory, ROM), etc.

Landscapes

  • Engineering & Computer Science (AREA)
  • Theoretical Computer Science (AREA)
  • Databases & Information Systems (AREA)
  • General Engineering & Computer Science (AREA)
  • Physics & Mathematics (AREA)
  • General Physics & Mathematics (AREA)
  • Data Mining & Analysis (AREA)
  • Computational Linguistics (AREA)
  • Computer Hardware Design (AREA)
  • Quality & Reliability (AREA)
  • Mathematical Physics (AREA)
  • Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
  • Debugging And Monitoring (AREA)
  • Management, Administration, Business Operations System, And Electronic Commerce (AREA)
  • Stored Programmes (AREA)

Abstract

一种SQL审核方法，包括：获取慢日志数据；从所述慢日志数据中提取待审核的SQL语句及与所述SQL语句匹配的基本信息；识别并解析所述SQL语句，得到解析结果；逐一按照预设的审核模板中的审核项目，对所述解析结果和/或所述基本信息进行审核，得到审核结果；及根据所述审核结果生成审核结果集。

Description

SQL审核方法、装置、服务器及存储设备
本申请要求于2016年9月28日提交中国专利局、申请号为201610860550X、发明名称为“SQL审核方法和装置”的中国专利申请的优先权，其全部内容通过引用结合在本申请中。
【技术领域】
本申请涉及数据库技术领域，特别是涉及一种SQL审核方法、装置、服务器及存储设备。
【背景技术】
在数据库的工作中，经常需要对数据库执行的SQL（Structured Query Language，结构化查询语言）语句进行审核，从而避免性能太差的SQL语句进入生产系统，导致数据库的整体性能降低。对于一些数据字典信息不完善的数据库，例如MySQL、Postgres等数据库的SQL审核，在传统方式中仅能通过SQL脚本解析SQL语句，进行一些简单的审核工作，而无法对SQL进行更深度的审核，例如SQL的执行计划审核等，导致SQL审核不准确。
【发明内容】
根据本申请的各种实施例，提供一种SQL审核方法、装置、服务器及存储设备。
一种SQL审核方法，包括：
获取慢日志数据；
从所述慢日志数据中提取待审核的SQL语句及与所述SQL语句匹配的基本信息；
识别并解析所述SQL语句，得到解析结果；
逐一按照预设的审核模板中的审核项目，对所述解析结果和/或所述基本信息进行审核，得到审核结果；及
根据所述审核结果生成审核结果集。
一种SQL审核装置，包括：
获取模块，用于获取慢日志数据；
提取模块，用于从所述慢日志数据中提取待审核的SQL语句及与所述SQL语句匹配的基本信息；
解析模块，用于识别并解析所述SQL语句，得到解析结果；
审核模块，用于逐一按照预设的审核模板中的审核项目，对所述解析结果和/或所述基本信息进行审核，得到审核结果；及
生成模块，用于根据所述审核结果生成审核结果集。
一种服务器，包括存储器和处理器，所述存储器中储存有计算机可执行指令，所述计算机可执行指令被所述处理器执行时，使得所述处理器执行以下步骤：
获取慢日志数据；
从所述慢日志数据中提取待审核的SQL语句及与所述SQL语句匹配的基本信息；
识别并解析所述SQL语句，得到解析结果；
逐一按照预设的审核模板中的审核项目，对所述解析结果和/或所述基本信息进行审核，得到审核结果；及
根据所述审核结果生成审核结果集。
一个或多个存储有计算机可执行指令的存储设备，所述计算机可执行指令被一个或多个处理器执行时，使得所述一个或多个处理器执行以下步骤：
获取慢日志数据；
从所述慢日志数据中提取待审核的SQL语句及与所述SQL语句匹配的基本信息；
识别并解析所述SQL语句，得到解析结果；
逐一按照预设的审核模板中的审核项目，对所述解析结果和/或所述基本信息进行审核，得到审核结果；及
根据所述审核结果生成审核结果集。
本申请的一个或多个实施例的细节在下面的附图和描述中提出。本申请的其它特征、目的和优点将从说明书、附图以及权利要求书变得明显。
【附图说明】
为了更清楚地说明本申请实施例或现有技术中的技术方案，下面将对实施例或现有技术描述中所需要使用的附图作简单地介绍，显而易见地，下面描述中的附图仅仅是本申请的一些实施例，对于本领域普通技术人员来讲，在不付出创造性劳动的前提下，还可以根据这些附图获得其他的附图。
图1为一个实施例中服务器的框图；
图2为一个实施例中SQL审核方法的流程图；
图3为一个实施例中按照预设的审核模板中的审核项目进行审核的流程图；
图4为一个实施例中建立SQL基线并进行分析的流程图；
图5为一个实施例中建立SQL基线的流程图；
图6为一个实施例中SQL审核装置的框图；
图7为一个实施例中提取模块的框图；
图8为另一个实施例中SQL审核装置的框图；
图9为一个实施例中建立模块的框图。
【具体实施方式】
为了使本申请的目的、技术方案及优点更加清楚明白，以下结合附图及实施例，对本申请进行进一步详细说明。应当理解，此处所描述的具体实施例仅用以解释本申请，并不用于限定本申请。
图1为一个实施例中服务器的框图。如图1所示，该服务器包括通过系统总线连接的处理器、存储器和网络接口。其中，该服务器的处理器用于提供计算和控制能力，支撑整个服务器的运行。该服务器的存储器用于存储数据、代码指令等，在一个实施例中，存储器可包括非易失性存储介质及内存储器。非易失性存储介质存储有操作系统、数据库和计算机可执行指令，数据库中存储有慢日志数据，该计算机可执行指令用于实现本申请实施例中提供的适用于服务器的一种SQL审核方法。内存储器为非易失性存储介质中的操作系统及计算机可执行指令提供高速缓存的运行环境。该服务器的网络接口用于据以与外部的终端通过网络连接通信，比如接收终端发送的修改后的SQL语句以及向终端返回SQL审核数据等。服务器可以用独立的服务器或者是多个服务器组成的服务器集群来实现。本领域技术人员可以理解，图1中示出的结构，仅仅是与本申请方案相关的部分结构的框图，并不构成对本申请方案所应用于其上的服务器的限定，具体的服务器可以包括比图中所示更多或更少的部件，或者组合某些部件，或者具有不同的部件布置。
如图2所示，在一个实施例中，提供了一种SQL审核方法，适用于如图1所示的服务器，该方法包括以下步骤：
步骤S210，获取慢日志数据。
服务器可通过外部链接表的方式，从数据字典信息不完善的目标数据库中获取慢日志数据，例如、MySQL、Postgres等目标数据库，其中，慢日志数据指的是慢查询日志数据，在数据库中超过指定时间的SQL语句查询称为“慢查询”，慢日志数据记录有超过指定时间的SQL语句，以及日志时间、存储位置、执行时间、执行者编号等SQL语句执行时产生的相关数据。
步骤S220，从慢日志数据中提取待审核的SQL语句及与SQL语句匹配的基本信息。
服务器可从获取的慢日志数据中提取待审核的SQL语句，以及审核SQL语句所需的与待审核的SQL语句匹配的基本信息，基本信息可根据预设的审核模板中的审核项目进行提取，基本信息可包括执行次数、执行总时间、执行操作、执行开销等。通过慢日志数据中提取待审核的SQL语句，相当于对SQL语句进行筛选，根据SQL语句的执行时间过滤性能好的SQL语句，超过指定时间的性能较差的SQL语句则进行审核。
在一个实施例中，步骤S220从慢日志数据中提取待审核的SQL语句及与SQL语句匹配的基本信息，包括（a）和（b）：
（a）根据预设格式分析慢日志数据，并生成慢日志数据列表。
服务器可分析慢日志数据并按照预设格式提取慢日志数据中的数据，预设格式可根据实际需求进行设定，例如，预设格式可包括字段名、字段类型、字段内容，则提取慢日志数据中各个字段名，以及与字段名对应的字段类型及字段内容。可根据按照预设格式从慢日志数据提取的数据生成慢日志数据列表，慢日志数据列表的格式内容与预设格式及提取的数据对应，例如，慢日志数据列表可如表1所示：
表 1
字段名 字段类型 字段内容
log_time Timestamp(3)with time zone 20160523
user_name text ZhangSan
database_name text DB1
userid oid 267975431
calls bigint 5
total_time Double precision 35
其中，log_time为日志时间，user_name为用户名，database_name为数据库名，userid为用户编号，calls为执行次数，total_time为执行总时间，可以理解地，慢日志数据列表中的内容及格式并不限于表1所示的内容及格式。
（b）根据慢日志数据列表中的字段名从慢日志数据列表中提取待审核的SQL语句及与SQL语句匹配的基本信息。
服务器可在慢日志数据列表中，提取字段名为query，即字段名为执行语句的字段内容作为待审核的SQL语句，并根据预设的审核模板中的审核项目，从慢日志数据列表中提取对应的与SQL语句匹配的基本信息，例如，预设的审核模板中的审核项目包括执行时间，则从慢日志数据列表中提取字段名为calls及total_time的字段内容作为与SQL语句匹配的基本信息。
步骤S230，识别并解析SQL语句，得到解析结果。
服务器可对从慢日志数据中提取的SQL语句进行识别解析，得到可被审核模板识别的解析结果，在本实施例中，解析SQL语句后可得到SQL树形解析结果，方便按照审核模板中的审核项目对解析结果进行审核。
步骤S240，逐一按照预设的审核模板中的审核项目，对解析结果和/或基本信息进行审核，得到审核结果。
预设的审核模板可包括执行计划审核模板及SQL语法审核模板。执行计划审核模板是对SQL语句的执行计划进行审核，在数据库中，一个SQL语句表示想要得到的数据，但是并没有表示如何获取数据，例如，通过一个SQL语句，可以取出所有住在深圳的客户. 当服务器中的数据库接收到的这条SQL的时候，通过计算会选择读取整个客户表还是利用索引进行获取数据，最终SQL语句被物理性执行的方法被称为执行计划。执行计划审核模板的审核项目可包括慢查询、高成本操作等。SQL语法审核模板是对SQL语句本身的语法语义进行审核，SQL语法审核模板的审核项目可包括禁止出现重复查询子句、禁止出现select *等。服务器可逐一按照预设的审核模板中的各个审核项目，对解析结果和/或基本信息进行审核，当不满足审核模板中的任一审核项目时，则审核不通过，当全部符合审核模板中的各个审核项目时，则审核通过。
步骤S250，根据审核结果生成审核结果集。
服务器可对从慢日志数据中提取的多个SQL语句及与各个SQL语句匹配的基本信息进行审核，并根据审核结果生成审核结果集，审核结果集可包括审核的SQL语句的SQL编号、数据库名称、执行时间、命令类型、审核状态、违规项目等内容，并将审核结果集存储在审计数据库中，其中，审核状态表示审核结果，可用0、1等数字或其它的字母、符号等表示审核结果为不通过或是通过。
上述SQL审核方法，从慢日志数据中提取待审核的SQL语句及与SQL语句匹配的基本信息，并按照预设的审核模板中的审核项目，对解析SQL语句得到的解析结果及与SQL语句匹配的基本信息进行审核，通过挖掘分析慢日志数据，能够为数据字典信息不完善的数据库扩展出可进行SQL深度审核SQL语句及与SQL语句匹配的基本信息，使SQL审核更具有针对性，并有效提高了SQL审核的准确性。
如图3所示，在一个实施例中，步骤S240逐一按照预设的审核模板中的审核项目，对解析结果和/或基本信息进行审核，得到审核结果，可包括步骤S302和步骤S304：
步骤S302，逐一按照执行计划审核模板中的审核项目，提取基本信息中与审核项目匹配的数据进行审核，得到审核结果。
预设的审核模板可包括执行计划审核模板及SQL语法审核模板，执行计划审核模板是对SQL语句的执行计划进行审核。执行计划审核模板中的审核项目可包括慢查询、高成本操作等项目，其中，慢查询的审核内容为判断SQL语句的执行时间是否大于预设的执行时间，例如，执行时间是否大于1s（秒），高成本操作的审核内容为判断SQL语句中是否有高成本操作，例如，是否有Seq Scan（序列扫描）、全表扫描等操作。执行计划审核模板的审核项目所审核的内容一般需要从与SQL语句匹配的基本信息中获取，例如，审核执行时间是否大于预设的执行时间，则需要从基本信息中获取执行次数及执行总时间，并求取平均值，即执行时间，审核高成本操作则需要获取基本信息中的执行操作。每个审核项目有唯一对应的审核项目编号，当审核不通过时，可记录不通过的审核项目编号。
步骤S304，逐一按照SQL语法审核模板中的审核项目，对解析结果进行语法分析，并根据分析结果进行审核，得到审核结果。
SQL语法审核模板是对SQL语句本身的语法语义进行审核，SQL语法审核模板的审核项目可包括禁止使用select *、禁止出现重复查询子句、update禁止出现order by子句、update必须出现where子句、禁止嵌套select子句、delete必须出现where子句、delete禁止出现order by子句、delete禁止出现limit子句等。禁止出现重复查询子句的审核项目可提高SQL语句的执行效率，update禁止出现order by子句、delete禁止出现order by子句及delete禁止出现limit子句的审核项目可防止DML（Data Manipulation Language，数据操纵语言）过程出现不必要的排序，update必须出现where子句及delete必须出现where子句的审核项目可防止DML过程出现全表锁。可逐一根据SQL语法审核模板的各个审核项目对解析得到的SQL树形解析结果进行分析审核，并得到审核结果。
预设的审核模板中的各个审核项目可对应不同的审核数值，对审核的SQL语句进行评分。当审核项目不通过时，则将SQL语句的审核总数值扣除不通过的审核项目对应的审核数值，审核结束后，可得到最后的审核数值，通过对SQL语句进行评分可清晰地得到SQL语句的性能好坏程度。例如，执行计划审核模板中的慢查询及高成本操作分别对应的审核数据为1分、2分，SQL语句的审核总数值为100分，若SQL语句不符合慢查询的审核项目，则扣除一分，最后的审核数值为99分。
在本实施例中，可通过执行计划审核模板及SQL语法审核模板对解析SQL语句得到的解析结果和/或与SQL语句匹配的基本信息逐一进行审核，可对数据字典信息不完善的数据库中的SQL进行更深度的审核，使SQL审核更具有针对性，并有效提高了SQL审核的准确性。
如图4所示，在一个实施例中，在步骤S250根据审核结果生成审核结果集之后，还包括以下步骤：
步骤S402，提取审核结果集中审核结果为不通过的SQL语句建立SQL基线。
SQL基线用于记录审核结果为不通过的SQL语句的相关数据及状态的变化过程，SQL基线可包括SQL语句内容、SQL编号、数据库名称、用户名、审核不通过的审核项目编号、审核状态等，刚建立SQL基线时，审核状态为不通过，当数据库管理员对SQL语句进行整改后，审核状态可修改为通过。
如图5所示，在一个实施例中，步骤S402提取审核结果集中审核结果为不通过的SQL语句建立SQL基线，包括以下步骤：
步骤S502，提取审核结果集中审核结果为不通过的SQL语句。
步骤S504，获取审核结果为不通过的SQL语句的SQL编号及不通过的审核项目编号。
服务器可提取审核结果集中审核结果为不通过的SQL语句建立SQL基线，可从审核结果集中获取审核结果为不通过的SQL语句的SQL语句内容、SQL编号、数据库名称、用户名、审核不通过的审核项目编号等信息。
步骤S506，根据SQL编号及不通过的审核项目编号判断是否存在匹配的SQL基线，若是，则执行步骤S508，若否，则执行步骤S510。
SQL语句每个不通过的审核项目可对应于唯一的SQL基线，即每个SQL基线有唯一对应的SQL编号及不通过的审核项目编号，若SQL语句不通过的审核项目有多个，则需建立多个对应的SQL基线。服务器提取审核结果集中审核结果为不通过的SQL语句，并获取SQL编号及不通过的审核项目编号后，可先根据SQL编号查找审计数据库中是否有对应的SQL基线，若有，则再进一步从与SQL编号对应的SQL基线中，查找是否有与审核项目编号匹配的SQL基线，若有，则无需重新建立SQL基线，只需将匹配的SQL基线的审核状态修改为不通过。
步骤S508，将SQL基本的状态标记为不通过。
步骤S510，根据SQL编号及不通过的审核项目编号建立SQL基线。
若没有与SQL编号及不通过的审核项目编号匹配的SQL基线，则根据SQL编号及不通过的审核项目编号在审计数据库中建立SQL基线，SQL基线中的审核状态标记为不通过。
步骤S404，分析SQL基线得到SQL审核数据，并展示SQL审核数据。
具体的，服务器可每隔预设时间从审计数据库中提取预设时间内新建立的或是状态被重新标记为不通过SQL基线，得到SQL的审核数据，例如，每隔1小时、3小时等时间，SQL的审核数据可包括SQL语句内容、SQL编号、数据库名称、用户名、审核不通过的审核项目编号等内容，并通过终端进行展示。数据库管理员可根据展示的SQL审核数据对SQL语句进行整改，使其符合审核项目，整改完成后，可根据整改结果修改SQL基线的审核状态，若SQL语句审核不通过的审核项目已进行整改，可将与该整改的审核项目编号匹配的SQL基线的审核状态标记为通过。在其它的实施例中，可每隔一段时间生成基线报告，例如生成基线日报及基线月报，基线报告中记录有该段时间所有新生成的SQL基线，以及变更过审核状态的SQL基线，方便数据库管理员了解审计数据库中SQL基线的变化。
在本实施例中，提取审核结果集中审核结果为不通过的SQL语句建立SQL基线，可方便数据库管理员对数据库中审核不通过的SQL语句进行整改，有效提高SQL审核工作的效率，帮助提升数据库的数据处理性能。
如图6所示，在一个实施例中，提供了一种SQL审核装置，包括获取模块610、提取模块620、解析模块630、审核模块640及生成模块650。
获取模块610，用于获取慢日志数据。
服务器可通过外部链接表的方式，从数据字典信息不完善的目标数据库中获取慢日志数据，例如、MySQL、Postgres等目标数据库，其中，慢日志数据指的是慢查询日志数据，在数据库中超过指定时间的SQL语句查询称为“慢查询”，慢日志数据记录有超过指定时间的SQL语句，以及日志时间、存储位置、执行时间、执行者编号等SQL语句执行时产生的相关数据。
提取模块620，用于从慢日志数据中提取待审核的SQL语句及与SQL语句匹配的基本信息。
服务器可从获取的慢日志数据中提取待审核的SQL语句，以及审核SQL语句所需的与待审核的SQL语句匹配的基本信息，基本信息可根据预设的审核模板中的审核项目进行提取，基本信息可包括执行次数、执行总时间、执行操作、执行开销等。通过慢日志数据中提取待审核的SQL语句，相当于对SQL语句进行筛选，根据SQL语句的执行时间过滤性能好的SQL语句，超过指定时间的性能较差的SQL语句则进行审核。
如图7所示，在一个实施例中，提取模块620包括列表生成单元622及信息提取单元624。
列表生成单元622，用于根据预设格式分析慢日志数据，并生成慢日志数据列表。
服务器可分析慢日志数据并按照预设格式提取慢日志数据中的数据，预设格式可根据实际需求进行设定，例如，预设格式可包括字段名、字段类型、字段内容，则提取慢日志数据中各个字段名，以及与字段名对应的字段类型及字段内容。可根据按照预设格式从慢日志数据提取的数据生成慢日志数据列表，慢日志数据列表的格式内容与预设格式及提取的数据对应，例如，慢日志数据列表可如表1所示，其中，log_time为日志时间，user_name为用户名，database_name为数据库名，userid为用户编号，calls为执行次数，total_time为执行总时间，可以理解地，慢日志数据列表中的内容及格式并不限于表1所示的内容及格式。
信息提取单元624，用于根据慢日志数据列表中的字段名从慢日志数据列表中提取待审核的SQL语句及与SQL语句匹配的基本信息。
服务器可在慢日志数据列表中，提取字段名为query，即字段名为执行语句的字段内容作为待审核的SQL语句，并根据预设的审核模板中的审核项目，从慢日志数据列表中提取对应的与SQL语句匹配的基本信息，例如，预设的审核模板中的审核项目包括执行时间，则从慢日志数据列表中提取字段名为calls及total_time的字段内容作为与SQL语句匹配的基本信息。
解析模块630，用于识别并解析SQL语句，得到解析结果。
服务器可对从慢日志数据中提取的SQL语句进行识别解析，得到可被审核模板识别的解析结果，在本实施例中，解析SQL语句后可得到SQL树形解析结果，方便按照审核模板中的审核项目对解析结果进行审核。
审核模块640，用于逐一按照预设的审核模板中的审核项目，对解析结果和/或基本信息进行审核，得到审核结果。
预设的审核模板可包括执行计划审核模板及SQL语法审核模板。执行计划审核模板是对SQL语句的执行计划进行审核，在数据库中，一个SQL语句表示想要得到的数据，但是并没有表示如何获取数据，例如，通过一个SQL语句，可以取出所有住在深圳的客户. 当服务器中的数据库接收到的这条SQL的时候，通过计算会选择读取整个客户表还是利用索引进行获取数据，最终SQL语句被物理性执行的方法被称为执行计划。执行计划审核模板的审核项目可包括慢查询、高成本操作等。SQL语法审核模板是对SQL语句本身的语法语义进行审核，SQL语法审核模板的审核项目可包括禁止出现重复查询子句、禁止出现select *等。服务器可逐一按照预设的审核模板中的各个审核项目，对解析结果和/或基本信息进行审核，当不满足审核模板中的任一审核项目时，则审核不通过，当全部符合审核模板中的各个审核项目时，则审核通过。
生成模块650，用于根据审核结果生成审核结果集。
服务器可对从慢日志数据中提取的多个SQL语句及与各个SQL语句匹配的基本信息进行审核，并根据审核结果生成审核结果集，审核结果集可包括审核的SQL语句的SQL编号、数据库名称、执行时间、命令类型、审核状态、违规项目等内容，并将审核结果集存储在审计数据库中，其中，审核状态表示审核结果，可用0、1等数字或其它的字母、符号等表示审核结果为不通过或是通过。
上述SQL审核装置，从慢日志数据中提取待审核的SQL语句及与SQL语句匹配的基本信息，并按照预设的审核模板中的审核项目，对解析SQL语句得到的解析结果及与SQL语句匹配的基本信息进行审核，通过挖掘分析慢日志数据，能够为数据字典信息不完善的数据库扩展出可进行SQL深度审核SQL语句及与SQL语句匹配的基本信息，使SQL审核更具有针对性，并有效提高了SQL审核的准确性。
在一个实施例中，审核模块640，还用于逐一按照执行计划审核模板中的审核项目，提取基本信息中与审核项目匹配的数据进行审核，得到审核结果。
预设的审核模板可包括执行计划审核模板及SQL语法审核模板，执行计划审核模板是对SQL语句的执行计划进行审核。执行计划审核模板中的审核项目可包括慢查询、高成本操作等项目，其中，慢查询的审核内容为判断SQL语句的执行时间是否大于预设的执行时间，例如，执行时间是否大于1s，高成本操作的审核内容为判断SQL语句中是否有高成本操作，例如，是否有Seq Scan、全表扫描等操作。执行计划审核模板的审核项目所审核的内容一般需要从与SQL语句匹配的基本信息中获取，例如，审核执行时间是否大于预设的执行时间，则需要从基本信息中获取执行次数及执行总时间，并求取平均值，即执行时间，审核高成本操作则需要获取基本信息中的执行操作。每个审核项目有唯一对应的审核项目编号，当审核不通过时，可记录不通过的审核项目编号。
审核模块640，还用于逐一按照SQL语法审核模板中的审核项目，对解析结果进行语法分析，并根据分析结果进行审核，得到审核结果。
SQL语法审核模板是对SQL语句本身的语法语义进行审核，SQL语法审核模板的审核项目可包括禁止使用select *、禁止出现重复查询子句、update禁止出现order by子句、update必须出现where子句、禁止嵌套select子句、delete必须出现where子句、delete禁止出现order by子句、delete禁止出现limit子句等。禁止出现重复查询子句的审核项目可提高SQL语句的执行效率，update禁止出现order by子句、delete禁止出现order by子句及delete禁止出现limit子句的审核项目可防止DML过程出现不必要的排序，update必须出现where子句及delete必须出现where子句的审核项目可防止DML过程出现全表锁。可逐一根据SQL语法审核模板的各个审核项目对解析得到的SQL树形解析结果进行分析审核，并得到审核结果。
预设的审核模板中的各个审核项目可对应不同的审核数值，对审核的SQL语句进行评分。当审核项目不通过时，则将SQL语句的审核总数值扣除不通过的审核项目对应的审核数值，审核结束后，可得到最后的审核数值，通过对SQL语句进行评分可清晰地得到SQL语句的性能好坏程度。例如，执行计划审核模板中的慢查询及高成本操作分别对应的审核数据为1分、2分，SQL语句的审核总数值为100分，若SQL语句不符合慢查询的审核项目，则扣除一分，最后的审核数值为99分。
在本实施例中，可通过执行计划审核模板及SQL语法审核模板对解析SQL语句得到的解析结果和/或与SQL语句匹配的基本信息逐一进行审核，可对数据字典信息不完善的数据库中的SQL进行更深度的审核，使SQL审核更具有针对性，并有效提高了SQL审核的准确性。
如图8所示，在一个实施例中，上述SQL审核装置，除了包括获取模块610、提取模块620、解析模块630、审核模块640及生成模块650，还包括建立模块660及展示模块670。
建立模块660，用于提取审核结果集中审核结果为不通过的SQL语句建立SQL基线。
SQL基线用于记录审核结果为不通过的SQL语句的相关数据及状态的变化过程，SQL基线可包括SQL语句内容、SQL编号、数据库名称、用户名、审核不通过的审核项目编号、审核状态等，刚建立SQL基线时，审核状态为不通过，当数据库管理员对SQL语句进行整改后，审核状态可修改为通过。
如图9所示，在一个实施例中，建立模块660包括语句提取单元662、获取单元664及判断单元666。
语句提取单元662，用于提取审核结果集中审核结果为不通过的SQL语句。
获取单元664，用于获取审核结果为不通过的SQL语句的SQL编号及不通过的审核项目编号。
服务器可提取审核结果集中审核结果为不通过的SQL语句建立SQL基线，可从审核结果集中获取审核结果为不通过的SQL语句的SQL语句内容、SQL编号、数据库名称、用户名、审核不通过的审核项目编号等信息。
判断单元666，用于根据SQL编号及不通过的审核项目编号判断是否存在匹配的SQL基线，若是，则将SQL基线的状态标记为不通过，若否，则根据SQL编号及不通过的审核项目编号建立SQL基线。
SQL语句每个不通过的审核项目可对应于唯一的SQL基线，即每个SQL基线有唯一对应的SQL编号及不通过的审核项目编号，若SQL语句不通过的审核项目有多个，则需建立多个对应的SQL基线。提取审核结果集中审核结果为不通过的SQL语句，并获取SQL编号及不通过的审核项目编号后，可先根据SQL编号查找审计数据库中是否有对应的SQL基线，若有，则再进一步从与SQL编号对应的SQL基线中，查找是否有与审核项目编号匹配的SQL基线，若有，则无需重新建立SQL基线，只需将匹配的SQL基线的审核状态修改为不通过。若没有与SQL编号及不通过的审核项目编号匹配的SQL基线，则根据SQL编号及不通过的审核项目编号在审计数据库中建立SQL基线，SQL基线中的审核状态标记为不通过。
展示模块670，用于分析SQL基线得到SQL审核数据，并展示SQL审核数据。
服务器可每隔预设时间从审计数据库中提取预设时间内新建立的或是状态被重新标记为不通过SQL基线，得到SQL的审核数据，例如，每隔1小时、3小时等时间，SQL的审核数据可包括SQL语句内容、SQL编号、数据库名称、用户名、审核不通过的审核项目编号等内容，并通过终端进行展示。数据库管理员可根据展示的SQL审核数据对SQL语句进行整改，使其符合审核项目，整改完成后，可根据整改结果修改SQL基线的审核状态，若SQL语句审核不通过的审核项目已进行整改，可将与该整改的审核项目编号匹配的SQL基线的审核状态标记为通过。在其它的实施例中，可每隔一段时间生成基线报告，例如生成基线日报及基线月报，基线报告中记录有该段时间所有新生成的SQL基线，以及变更过审核状态的SQL基线，方便数据库管理员了解审计数据库中SQL基线的变化。
在本实施例中，提取审核结果集中审核结果为不通过的SQL语句建立SQL基线，可方便数据库管理员对数据库中审核不通过的SQL语句进行整改，有效提高SQL审核工作的效率，帮助提升数据库的数据处理性能。
上述SQL审核装置中的各个模块可全部或部分通过软件、硬件或其组合来实现。例如，在硬件实现上，上述展示模块670可通过服务器上的网络接口向终端发送SQL审核数据，以使终端对SQL审核数据进行展示；审核模块640可通过服务器的处理器逐一按照预设的审核模板中的审核项目，对解析结果和/或基本信息进行审核，得到审核结果，其中，处理器可以为中央处理单元、微处理器等。上述各模块可以硬件形式内嵌于或独立于服务器的处理器中，也可以以软件形式存储于服务器的存储器中，以便于处理器调用执行以上各个模块对应的操作。
本领域普通技术人员可以理解实现上述实施例方法中的全部或部分流程，是可以通过计算机程序来指令相关的硬件来完成，所述的程序可存储于一计算机可读取存储设备中，该程序在执行时，可包括如上述各方法的实施例的流程。其中，所述的存储设备可为磁碟、光盘、只读存储记忆体（Read-Only Memory，ROM）等。
以上所述实施例的各技术特征可以进行任意的组合，为使描述简洁，未对上述实施例中的各个技术特征所有可能的组合都进行描述，然而，只要这些技术特征的组合不存在矛盾，都应当认为是本说明书记载的范围。
以上所述实施例仅表达了本申请的几种实施方式，其描述较为具体和详细，但并不能因此而理解为对发明专利范围的限制。应当指出的是，对于本领域的普通技术人员来说，在不脱离本申请构思的前提下，还可以做出若干变形和改进，这些都属于本申请的保护范围。因此，本申请专利的保护范围应以所附权利要求为准。

Claims (20)

  1. 一种SQL审核方法，包括：
    获取慢日志数据；
    从所述慢日志数据中提取待审核的SQL语句及与所述SQL语句匹配的基本信息；
    识别并解析所述SQL语句，得到解析结果；
    逐一按照预设的审核模板中的审核项目，对所述解析结果和/或所述基本信息进行审核，得到审核结果；及
    根据所述审核结果生成审核结果集。
  2. 根据权利要求1所述的方法，其特征在于，所述从所述慢日志数据中提取待审核的SQL语句及与所述SQL语句匹配的基本信息，包括：
    根据预设格式分析所述慢日志数据，并生成慢日志数据列表；及
    根据所述慢日志数据列表中的字段名从所述慢日志数据列表中提取待审核的SQL语句及与所述SQL语句匹配的基本信息。
  3. 根据权利要求1所述的方法，其特征在于，所述预设的审核模板包括执行计划审核模板；
    所述逐一按照预设的审核模板中的审核项目，对所述解析结果和/或所述基本信息进行审核，得到审核结果，包括：
    逐一按照所述执行计划审核模板中的审核项目，提取所述基本信息中与所述审核项目匹配的数据进行审核，得到审核结果。
  4. 根据权利要求1或3所述的方法，其特征在于，所述预设的审核模板包括SQL语法审核模板；
    所述逐一按照预设的审核模板中的审核项目，对所述解析结果和/或所述基本信息进行审核，得到审核结果，包括：
    逐一按照所述SQL语法审核模板中的审核项目，对所述解析结果进行语法分析，并根据分析结果进行审核，得到审核结果。
  5. 根据权利要求1所述的方法，其特征在于，在所述根据所述审核结果生成审核结果集之后，所述方法还包括：
    提取所述审核结果集中审核结果为不通过的SQL语句建立SQL基线；及
    分析所述SQL基线得到SQL审核数据，并展示所述SQL审核数据。
  6. 根据权利要求5所述的方法，其特征在于，所述提取所述审核结果集中审核结果为不通过的SQL语句建立SQL基线，包括：
    提取所述审核结果集中审核结果为不通过的SQL语句；
    获取所述审核结果为不通过的SQL语句的SQL编号及不通过的审核项目编号；及
    根据所述SQL编号及不通过的审核项目编号判断是否存在匹配的SQL基线，若是，则将所述SQL基线的状态标记为不通过，若否，则根据所述SQL编号及不通过的审核项目编号建立SQL基线。
  7. 一种SQL审核装置，包括：
    获取模块，用于获取慢日志数据；
    提取模块，用于从所述慢日志数据中提取待审核的SQL语句及与所述SQL语句匹配的基本信息；
    解析模块，用于识别并解析所述SQL语句，得到解析结果；
    审核模块，用于逐一按照预设的审核模板中的审核项目，对所述解析结果和/或所述基本信息进行审核，得到审核结果；及
    生成模块，用于根据所述审核结果生成审核结果集。
  8. 根据权利要求7所述的装置，其特征在于，所述提取模块，包括：
    列表生成单元，用于根据预设格式分析所述慢日志数据，并生成慢日志数据列表；及
    信息提取单元，用于根据所述慢日志数据列表中的字段名从所述慢日志数据列表中提取待审核的SQL语句及与所述SQL语句匹配的基本信息。
  9. 根据权利要求7所述的装置，其特征在于，所述预设的审核模板包括执行计划审核模板及SQL语法审核模板；
    所述审核模块，还用于逐一按照所述执行计划审核模板中的审核项目，提取所述基本信息中与所述审核项目匹配的数据进行审核，得到审核结果；
    所述审核模块，还用于逐一按照所述SQL语法审核模板中的审核项目，对所述解析结果进行语法分析，并根据分析结果进行审核，得到审核结果。
  10. 根据权利要求7至9任一所述的装置，其特征在于，所述装置还包括：
    建立模块，用于提取所述审核结果集中审核结果为不通过的SQL语句建立SQL基线；及
    展示模块，用于分析所述SQL基线得到SQL审核数据，并展示所述SQL审核数据。
  11. 根据权利要求10所述的装置，其特征在于，所述建立模块，包括：
    语句提取单元，用于提取所述审核结果集中审核结果为不通过的SQL语句；
    获取单元，用于获取所述审核结果为不通过的SQL语句的SQL编号及不通过的审核项目编号；及
    判断单元，用于根据所述SQL编号及不通过的审核项目编号判断是否存在匹配的SQL基线，若是，则将所述SQL基线的状态标记为不通过，若否，则根据所述SQL编号及不通过的审核项目编号建立SQL基线。
  12. 一种服务器，包括存储器和处理器，所述存储器中储存有计算机可执行指令，所述计算机可执行指令被所述处理器执行时，使得所述处理器执行以下步骤：
    获取慢日志数据；
    从所述慢日志数据中提取待审核的SQL语句及与所述SQL语句匹配的基本信息；
    识别并解析所述SQL语句，得到解析结果；
    逐一按照预设的审核模板中的审核项目，对所述解析结果和/或所述基本信息进行审核，得到审核结果；及
    根据所述审核结果生成审核结果集。
  13. 根据权利要求12所述的服务器，其特征在于，所述从所述慢日志数据中提取待审核的SQL语句及与所述SQL语句匹配的基本信息，包括：
    根据预设格式分析所述慢日志数据，并生成慢日志数据列表；及
    根据所述慢日志数据列表中的字段名从所述慢日志数据列表中提取待审核的SQL语句及与所述SQL语句匹配的基本信息。
  14. 根据权利要求12所述的服务器，其特征在于，所述预设的审核模板包括执行计划审核模板；
    所述逐一按照预设的审核模板中的审核项目，对所述解析结果和/或所述基本信息进行审核，得到审核结果，包括：
    逐一按照所述执行计划审核模板中的审核项目，提取所述基本信息中与所述审核项目匹配的数据进行审核，得到审核结果。
  15. 根据权利要求14所述的服务器，其特征在于，所述预设的审核模板包括SQL语法审核模板；
    逐一按照所述SQL语法审核模板中的审核项目，对所述解析结果进行语法分析，并根据分析结果进行审核，得到审核结果。
  16. 根据权利要求12所述的服务器，其特征在于，所述计算机可执行指令被所述处理器执行时，还使得所述处理器在执行所述根据所述审核结果生成审核结果集的步骤之后，还执行：
    提取所述审核结果集中审核结果为不通过的SQL语句建立SQL基线；及
    分析所述SQL基线得到SQL审核数据，并展示所述SQL审核数据的步骤。
  17. 根据权利要求16所述的服务器，其特征在于，所述提取所述审核结果集中审核结果为不通过的SQL语句建立SQL基线，包括：
    提取所述审核结果集中审核结果为不通过的SQL语句；
    获取所述审核结果为不通过的SQL语句的SQL编号及不通过的审核项目编号；及
    根据所述SQL编号及不通过的审核项目编号判断是否存在匹配的SQL基线，若是，则将所述SQL基线的状态标记为不通过，若否，则根据所述SQL编号及不通过的审核项目编号建立SQL基线。
  18. 一个或多个存储有计算机可执行指令的存储设备，所述计算机可执行指令被一个或多个处理器执行时，使得所述一个或多个处理器执行以下步骤：
    获取慢日志数据；
    从所述慢日志数据中提取待审核的SQL语句及与所述SQL语句匹配的基本信息；
    识别并解析所述SQL语句，得到解析结果；
    逐一按照预设的审核模板中的审核项目，对所述解析结果和/或所述基本信息进行审核，得到审核结果；及
    根据所述审核结果生成审核结果集。
  19. 根据权利要求18所述的存储设备，其特征在于，所述从所述慢日志数据中提取待审核的SQL语句及与所述SQL语句匹配的基本信息，包括：
    根据预设格式分析所述慢日志数据，并生成慢日志数据列表；及
    根据所述慢日志数据列表中的字段名从所述慢日志数据列表中提取待审核的SQL语句及与所述SQL语句匹配的基本信息。
  20. 根据权利要求18所述的存储设备，其特征在于，所述预设的审核模板包括执行计划审核模板及SQL语法审核模板；
    所述逐一按照预设的审核模板中的审核项目，对所述解析结果和/或所述基本信息进行审核，得到审核结果，包括：
    逐一按照所述执行计划审核模板中的审核项目，提取所述基本信息中与所述审核项目匹配的数据进行审核，得到审核结果；
    逐一按照所述SQL语法审核模板中的审核项目，对所述解析结果进行语法分析，并根据分析结果进行审核，得到审核结果。
PCT/CN2017/083645 2016-09-28 2017-05-09 Sql审核方法、装置、服务器及存储设备 WO2018058959A1 (zh)

Priority Applications (5)

Application Number Priority Date Filing Date Title
US15/579,376 US10402402B2 (en) 2016-09-28 2017-05-09 Method, device, server and storage apparatus of reviewing SQL
EP17800997.3A EP3343411A4 (en) 2016-09-28 2017-05-09 SQL TEST PROCESS AND DEVICE, SERVER AND STORAGE DEVICE
JP2017565717A JP2018532171A (ja) 2016-09-28 2017-05-09 Ｓｑｌ審査方法、サーバ及び記憶デバイス
AU2017268630A AU2017268630A1 (en) 2016-09-28 2017-05-09 Method, device, server and storage apparatus of reviewing SQL
KR1020187015349A KR102230661B1 (ko) 2016-09-28 2017-05-09 Sql 검토 방법, 장치, 서버 및 저장 매체

Applications Claiming Priority (2)

Application Number Priority Date Filing Date Title
CN201610860550.X 2016-09-28
CN201610860550.XA CN106980637B (zh) 2016-09-28 2016-09-28 Sql审核方法和装置

Publications (1)

Publication Number Publication Date
WO2018058959A1 true WO2018058959A1 (zh) 2018-04-05

Family

ID=59340265

Family Applications (1)

Application Number Title Priority Date Filing Date
PCT/CN2017/083645 WO2018058959A1 (zh) 2016-09-28 2017-05-09 Sql审核方法、装置、服务器及存储设备

Country Status (8)

Country Link
US (1) US10402402B2 (zh)
EP (1) EP3343411A4 (zh)
JP (1) JP2018532171A (zh)
KR (1) KR102230661B1 (zh)
CN (1) CN106980637B (zh)
AU (2) AU2017268630A1 (zh)
TW (1) TWI634449B (zh)
WO (1) WO2018058959A1 (zh)

Cited By (2)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN110413516A (zh) * 2019-07-15 2019-11-05 杭州数梦工场科技有限公司 识别慢sql代码的方法及装置和电子设备
CN113297245A (zh) * 2020-05-29 2021-08-24 阿里巴巴集团控股有限公司 获取执行信息的方法及装置

Families Citing this family (15)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN107688626B (zh) * 2017-08-18 2021-01-01 北京小度信息科技有限公司 慢查询日志处理方法、装置及电子设备
CN107766552A (zh) * 2017-11-01 2018-03-06 搜易贷（北京）金融信息服务有限公司 一种mysql自动上线方法
US11176169B2 (en) * 2018-01-09 2021-11-16 Cleartrail Technologies Private Limited Recommending visual and execution templates to enable automation of control and data exploration across systems
CN108415991A (zh) * 2018-02-12 2018-08-17 广州市贝聊信息科技有限公司 一种sql审核方法与系统
CN110716912B (zh) * 2018-07-12 2021-09-28 马上消费金融股份有限公司 一种sql性能检测方法及服务器
CN109408378B (zh) * 2018-09-30 2022-05-24 福建星瑞格软件有限公司 一种在大数据量下快速定位sql解析错误的测试方法及系统
CN109284282A (zh) * 2018-10-22 2019-01-29 北京极数云舟科技有限公司 一种基于MySQL数据库运维方法和系统
CN109710518A (zh) * 2018-12-13 2019-05-03 中国联合网络通信集团有限公司 脚本审核方法及装置
CN110163603A (zh) * 2019-04-18 2019-08-23 平安城市建设科技(深圳)有限公司 信息验证方法、装置、设备及可读存储介质
CN111259040B (zh) * 2020-02-19 2023-04-11 中国工商银行股份有限公司 Sql语句审核方法和系统
CN111913937B (zh) * 2020-07-31 2024-03-08 中国工商银行股份有限公司 数据库运维方法和装置
CN113760945A (zh) * 2020-08-11 2021-12-07 北京沃东天骏信息技术有限公司 一种审核sql语句的方法及装置
CN112181840B (zh) * 2020-09-30 2023-09-26 深圳前海微众银行股份有限公司 一种数据库状态的确定方法及装置、设备、存储介质
CN113590647B (zh) * 2021-07-29 2024-02-23 中国联合网络通信集团有限公司 Sql语句优化方法、装置、设备、存储介质及产品
CN114661751B (zh) * 2022-03-22 2023-01-31 医渡云（北京）技术有限公司 基于sql知识库的数据生产方法、装置、系统、设备及介质

Citations (4)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN101021874A (zh) * 2007-03-21 2007-08-22 金蝶软件（中国）有限公司 一种对查询sql请求进行优化的方法及装置
US20080140627A1 (en) * 2006-12-08 2008-06-12 International Business Machines Corporation Method and apparatus for aggregating database runtime information and analyzing application performance
CN105243147A (zh) * 2015-10-22 2016-01-13 浪潮(北京)电子信息产业有限公司 一种MySQL数据库慢查询日志的管理方法及系统
CN105653607A (zh) * 2015-12-23 2016-06-08 北京奇虎科技有限公司 Sql日志收集分析方法及装置

Family Cites Families (17)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
JP3779431B2 (ja) * 1997-06-13 2006-05-31 富士通株式会社 リレーショナルデータベース管理装置，中間リンクテーブル自動作成処理方法およびプログラム記憶媒体
JP2002342110A (ja) * 2001-05-17 2002-11-29 Fujitsu Ltd プログラム解析装置及びそのプログラム並びにプログラム記録媒体
KR20030013656A (ko) * 2001-08-08 2003-02-15 한국전자통신연구원 동적 질의어 튜닝 시스템 및 그 방법
JP2003263353A (ja) * 2002-03-08 2003-09-19 Hitachi Information Systems Ltd 障害発生原因分析装置，障害発生原因分析方法，及び障害発生原因分析プログラム
JP4295326B2 (ja) * 2007-01-10 2009-07-15 株式会社日立製作所 計算機システム
CN101075256A (zh) * 2007-06-08 2007-11-21 北京神舟航天软件技术有限公司 数据库实时审计分析系统及方法
US8903801B2 (en) * 2007-09-14 2014-12-02 Oracle International Corporation Fully automated SQL tuning
US20100153431A1 (en) 2008-12-11 2010-06-17 Louis Burger Alert triggered statistics collections
CN102411633A (zh) * 2011-12-27 2012-04-11 北京人大金仓信息技术股份有限公司 基于模板的数据库审计设置方法
US20140189526A1 (en) * 2013-01-02 2014-07-03 International Business Machines Corporation Changing log file content generation
WO2014174681A1 (ja) * 2013-04-26 2014-10-30 株式会社日立製作所 特定装置、特定方法、および特定プログラム
CN103488797A (zh) * 2013-10-14 2014-01-01 德讯科技股份有限公司 一种基于mysql数据库的sql信息采集审计系统
CN104679646B (zh) 2013-11-29 2018-02-06 阿里巴巴集团控股有限公司 一种用于检测sql代码缺陷的方法和装置
CN104391995A (zh) * 2014-12-15 2015-03-04 北京趣拿软件科技有限公司 一种sql语句审核的方法、数据库运维的方法及系统
CN104537015A (zh) * 2014-12-19 2015-04-22 电信科学技术第十研究所 一种日志分析的计算机实现的方法、计算机和系统
CN104965861B (zh) 2015-06-03 2018-06-22 上海新炬网络信息技术股份有限公司 一种数据访问监控装置
US9811444B2 (en) * 2015-08-04 2017-11-07 Salesforce.Com, Inc. Testing software enhancements in database applications

Patent Citations (4)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US20080140627A1 (en) * 2006-12-08 2008-06-12 International Business Machines Corporation Method and apparatus for aggregating database runtime information and analyzing application performance
CN101021874A (zh) * 2007-03-21 2007-08-22 金蝶软件（中国）有限公司 一种对查询sql请求进行优化的方法及装置
CN105243147A (zh) * 2015-10-22 2016-01-13 浪潮(北京)电子信息产业有限公司 一种MySQL数据库慢查询日志的管理方法及系统
CN105653607A (zh) * 2015-12-23 2016-06-08 北京奇虎科技有限公司 Sql日志收集分析方法及装置

Non-Patent Citations (1)

* Cited by examiner, † Cited by third party
Title
See also references of EP3343411A4 *

Cited By (3)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN110413516A (zh) * 2019-07-15 2019-11-05 杭州数梦工场科技有限公司 识别慢sql代码的方法及装置和电子设备
CN110413516B (zh) * 2019-07-15 2023-08-25 杭州数梦工场科技有限公司 识别慢sql代码的方法及装置和电子设备
CN113297245A (zh) * 2020-05-29 2021-08-24 阿里巴巴集团控股有限公司 获取执行信息的方法及装置

Also Published As

Publication number Publication date
JP2018532171A (ja) 2018-11-01
TW201814565A (zh) 2018-04-16
KR102230661B1 (ko) 2021-03-23
KR20190060725A (ko) 2019-06-03
US10402402B2 (en) 2019-09-03
EP3343411A4 (en) 2019-04-17
CN106980637A (zh) 2017-07-25
AU2017101864A4 (en) 2019-11-14
EP3343411A1 (en) 2018-07-04
AU2017268630A1 (en) 2018-04-12
US20190228008A1 (en) 2019-07-25
CN106980637B (zh) 2019-06-07
TWI634449B (zh) 2018-09-01

Similar Documents

Publication Publication Date Title
WO2018058959A1 (zh) Sql审核方法、装置、服务器及存储设备
WO2018014580A1 (zh) 数据接口测试方法、装置、服务器和存储介质
WO2021218021A1 (zh) 数据血缘分析方法、装置、设备及计算机可读存储介质
WO2018107811A1 (zh) 网络安全联合防御方法、装置、服务器和存储介质
WO2018113241A1 (zh) 页面展示方法、装置、服务器和存储介质
WO2020253112A1 (zh) 测试策略的获取方法、装置、终端及可读存储介质
WO2017107367A1 (zh) 用户标识处理的方法、终端和非易失性计算可读存储介质
WO2015144012A1 (zh) 一种软件开发事务的实现方法及电子设备
WO2013174172A1 (zh) 一种文件信息预览方法及系统
WO2016047866A1 (ko) 데이터베이스 마이그레이션 방법 및 그 장치
WO2014012498A1 (zh) 桌面控制方法和装置、计算机存储介质
KR101917807B1 (ko) 원본 데이터베이스의 부하를 최소화한 데이터 정합성 검증 방법 및 시스템
WO2020087981A1 (zh) 风控审核模型生成方法、装置、设备及可读存储介质
WO2020253135A1 (zh) 自动化分析方法、用户设备、存储介质及装置
WO2014189190A1 (ko) 데이터 부재 태깅 기반의 정보 검색 시스템 및 방법
WO2014044130A1 (zh) 业务巡检方法和系统、计算机存储介质
WO2021051558A1 (zh) 基于知识图谱的问答方法、装置和存储介质
WO2011162444A1 (ko) 온톨로지 스키마와 결합된 개체명 사전 및 마이닝 규칙을 이용한 개체명 사전 또는 마이닝 규칙 데이터베이스 갱신 장치 및 방법
WO2020233060A1 (zh) 事件通知方法、事件通知服务器、存储介质及装置
WO2020233089A1 (zh) 测试用例生成方法、装置、终端及计算机可读存储介质
WO2020177376A1 (zh) 数据的提取方法、装置、终端及计算机可读存储介质
WO2011065660A4 (ko) 계산 시뮬레이션 모사 시스템 및 그 방법
WO2019085400A1 (zh) 银行自助终端及其控制方法、计算机可读存储介质
WO2013008978A1 (ko) 개체 식별 결과 검색 시스템 및 방법
WO2018090535A1 (zh) 累加检索处理方法和装置、终端和存储介质

Legal Events

Date Code Title Description
WWE Wipo information: entry into national phase

Ref document number: 11201709929P

Country of ref document: SG

WWE Wipo information: entry into national phase

Ref document number: 2017565717

Country of ref document: JP

ENP Entry into the national phase

Ref document number: 2017268630

Country of ref document: AU

Date of ref document: 20170509

Kind code of ref document: A

ENP Entry into the national phase

Ref document number: 20187015349

Country of ref document: KR

Kind code of ref document: A

NENP Non-entry into the national phase

Ref country code: DE