WO2019153550A1

WO2019153550A1 - Automatic sql optimization method and apparatus, and computer device and storage medium

Info

Publication number: WO2019153550A1
Application number: PCT/CN2018/085331
Authority: WO
Inventors: 徐钊
Original assignee: 平安科技（深圳）有限公司
Priority date: 2018-02-12
Filing date: 2018-05-02
Publication date: 2019-08-15
Also published as: CN108388626A

Abstract

An automatic SQL optimization method and apparatus, and a computer device and a storage medium. The method comprises: acquiring, when a current script is in an executing state, a current system performance index parameter according to a system performance index monitoring item set in the current script (S101); if the current system performance index parameter exceeds a preset index early-warning value, recording the current script to a specified path (S102); obtaining an execution plan of the current script (S103); if an index is missing in the execution plan of the current script, automatically adding an index to the current script (S104); and if the index of the current script fails, performing repairing the script by means of a forced index to obtain a repaired script (S105). The method can monitor the system performance in time, and can automatically optimize an SQL script having great impact on the system performance, thus quickly finding and resolving a performance problem.

Description

SQL自动优化方法、装置、计算机设备及存储介质SQL automatic optimization method, device, computer equipment and storage medium

本申请要求于2018年2月12日提交中国专利局、申请号为201810146454.8、申请名称为“SQL自动优化方法、装置、计算机设备及存储介质”的中国专利申请的优先权，其全部内容通过引用结合在本申请中。This application claims priority to Chinese Patent Application No. 201101146454.8, filed on Feb. 12, 2018, and entitled "SQL Automatic Optimization Method, Apparatus, Computer Equipment, and Storage Media", the entire contents of which are incorporated by reference. Combined in this application.

技术领域Technical field

本申请涉及数据库脚本技术领域，尤其涉及一种SQL自动优化方法、装置、计算机设备及存储介质。The present application relates to the field of database script technology, and in particular, to a SQL automatic optimization method, device, computer device, and storage medium.

背景技术Background technique

脚本，是使用一种特定的描述性语言，依据一定的格式编写的可执行文件，又称作宏或批处理文件。在软件开发的过程中，开发人员所编写的脚本质量因人而异，低质量的脚本在执行时给***带来了极大的性能隐患。一般是***在脚本执行出现性能问题后，才会去排查和修复脚本所导致的问题。由于脚本众多，无法及时定位具体导致问题出现的脚本，导致了对低质量的脚本进行修改和优化的效率低下。A script is an executable file, also called a macro or batch file, written in a specific descriptive language according to a certain format. In the process of software development, the quality of scripts written by developers varies from person to person. Low-quality scripts bring great performance problems to the system during execution. Generally, the system will not troubleshoot and fix problems caused by scripts after performance problems occur in script execution. Due to the large number of scripts, it is not possible to locate the scripts that caused the problem in time, which makes the modification and optimization of low-quality scripts inefficient.

发明内容Summary of the invention

本申请提供了一种SQL自动优化方法、装置、计算机设备及存储介质，旨在解决现有技术中众多脚本在执行时，无法及时定位具体导致问题出现的脚本，导致了对低质量的脚本进行修改和优化的效率低下的问题。The present application provides a method, an apparatus, a computer device, and a storage medium for automatically optimizing a SQL. The purpose of the present invention is to solve the problem that a plurality of scripts in the prior art cannot be located in time to cause a problem, which leads to a low-quality script. Inefficiencies in modification and optimization.

第一方面，本申请提供了一种SQL自动优化方法，其包括：当前脚本为执行状态时，根据当前脚本中所设置的***性能指标监控项采集当前***性能指标参数；若当前***性能指标参数超出预设的指标预警值，将当前脚本记录至指定路径；获取当前脚本的执行计划；若当前脚本的执行计划中缺少索引，自动添加索引至当前脚本；若当前脚本的索引失效，通过强制索引进行脚本修复，得到修复后脚本。In a first aspect, the present application provides an automatic SQL optimization method, which includes: when a current script is in an execution state, collecting current system performance indicator parameters according to a system performance indicator monitoring item set in a current script; if current system performance indicator parameters Exceeding the preset indicator warning value, the current script is recorded to the specified path; the execution plan of the current script is obtained; if the index of the current script is missing from the execution plan, the index is automatically added to the current script; if the index of the current script is invalid, the index is forced. Make a script fix and get the post-repair script.

第二方面，本申请提供了一种SQL自动优化装置，其包括：In a second aspect, the present application provides an SQL automatic optimization apparatus, including:

指标参数获取单元，用于当前脚本为执行状态时，根据当前脚本中所设置的***性能指标监控项采集当前***性能指标参数；The indicator parameter obtaining unit is configured to collect current system performance indicator parameters according to the system performance indicator monitoring item set in the current script when the current script is in an execution state;

超标脚本记录单元，用于若当前***性能指标参数超出预设的指标预警值，将当前脚本记录至指定路径；The over-script script recording unit is configured to record the current script to the specified path if the current system performance indicator parameter exceeds the preset indicator warning value;

执行计划获取单元，用于获取当前脚本的执行计划；An execution plan acquisition unit for obtaining an execution plan of the current script;

索引自动添加单元，用于若当前脚本的执行计划中缺少索引，自动添加索引至当前脚本；The index automatically adds a unit for automatically adding an index to the current script if an index is missing from the execution plan of the current script;

脚本修复单元，用于若当前脚本的索引失效，通过强制索引进行脚本修复，得到修复后脚本。The script repair unit is configured to fix the script by forcing the index if the index of the current script is invalid, and obtain the repaired script.

第三方面，本申请又提供了一种计算机设备，包括存储器、处理器及存储在所述存储器上并可在所述处理器上运行的计算机程序，所述处理器执行所述计算机程序时实现本申请提供的任一项所述的SQL自动优化方法。In a third aspect, the present application further provides a computer device comprising a memory, a processor, and a computer program stored on the memory and operable on the processor, the processor implementing the computer program The SQL automatic optimization method according to any one of the preceding claims.

第四方面，本申请还提供了一种存储介质，其中所述存储介质存储有计算机程序，所述计算机程序包括程序指令，所述程序指令当被处理器执行时使所述处理器执行本申请提供的任一项所述的SQL自动优化方法。In a fourth aspect, the present application also provides a storage medium, wherein the storage medium stores a computer program, the computer program comprising program instructions, the program instructions, when executed by a processor, causing the processor to execute the application Any of the SQL automatic optimization methods described in any one of the above.

本申请提供一种SQL自动优化方法、装置、计算机设备及存储介质。该方法能够及时监控***性能，并且能对影响***性能很大关系的SQL脚本进行自动优化，实现快速发现性能问题并解决。The application provides an SQL automatic optimization method, device, computer device and storage medium. The method can timely monitor system performance and automatically optimize SQL scripts that affect system performance, enabling rapid discovery of performance problems and resolution.

附图说明DRAWINGS

为了更清楚地说明本申请实施例技术方案，下面将对实施例描述中所需要使用的附图作简单地介绍，显而易见地，下面描述中的附图是本申请的一些实施例，对于本领域普通技术人员来讲，在不付出创造性劳动的前提下，还可以根据这些附图获得其他的附图。In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings used in the description of the embodiments will be briefly described below. Obviously, the drawings in the following description are some embodiments of the present application, For the ordinary technicians, other drawings can be obtained based on these drawings without any creative work.

图1为本申请实施例提供的一种SQL自动优化方法的示意流程图；FIG. 1 is a schematic flowchart of a method for automatically optimizing an SQL according to an embodiment of the present application;

图2是本申请实施例提供的一种SQL自动优化方法的子流程示意图；2 is a schematic diagram of a sub-flow of an SQL automatic optimization method according to an embodiment of the present application;

图3是本申请实施例提供的一种SQL自动优化方法的另一子流程示意图；FIG. 3 is a schematic diagram of another sub-flow of an automatic SQL optimization method according to an embodiment of the present application; FIG.

图4为本申请实施例提供的一种SQL自动优化装置的示意性框图；FIG. 4 is a schematic block diagram of an apparatus for automatically optimizing an SQL according to an embodiment of the present application;

图5为本申请实施例提供的一种SQL自动优化装置的子单元示意性框图；FIG. 5 is a schematic block diagram of a subunit of an automatic SQL optimization apparatus according to an embodiment of the present disclosure;

图6为本申请实施例提供的一种SQL自动优化装置的另一子单元示意性框图；FIG. 6 is a schematic block diagram of another subunit of the SQL automatic optimization apparatus according to an embodiment of the present application;

图7为本申请实施例提供的一种计算机设备的示意性框图。FIG. 7 is a schematic block diagram of a computer device according to an embodiment of the present application.

具体实施方式Detailed ways

下面将结合本申请实施例中的附图，对本申请实施例中的技术方案进行清楚、完整地描述，显然，所描述的实施例是本申请一部分实施例，而不是全部的实施例。基于本申请中的实施例，本领域普通技术人员在没有做出创造性劳动前提下所获得的所有其他实施例，都属于本申请保护的范围。The technical solutions in the embodiments of the present application are clearly and completely described in the following with reference to the drawings in the embodiments of the present application. It is obvious that the described embodiments are a part of the embodiments of the present application, and not all of the embodiments. All other embodiments obtained by a person of ordinary skill in the art based on the embodiments of the present application without departing from the inventive scope are the scope of the present application.

请参阅图1，图1是本申请实施例提供的一种SQL自动优化方法的示意流程图。该方法应用于台式电脑、手提电脑、平板电脑等终端中。如图1所示，该方法包括步骤S101～S105。Please refer to FIG. 1. FIG. 1 is a schematic flowchart of an automatic SQL optimization method according to an embodiment of the present application. The method is applied to terminals such as desktop computers, laptop computers, and tablet computers. As shown in FIG. 1, the method includes steps S101 to S105.

S101、当前脚本为执行状态时，根据当前脚本中所设置的***性能指标监控项采集当前***性能指标参数。S101: When the current script is in an execution state, collect current system performance indicator parameters according to the system performance indicator monitoring item set in the current script.

在本实施例中，为了能在脚本执行的过程中实时获取当前脚本对***性能的影响，则需要在当前脚本中增加***性能指标监控项(***性能指标监控项类似于参数获取语句，其作用是采集对应的指标参数，也即起到了实时监控获取***对应的指标参数的作用)，以监控***的如下性能：吞吐量、执行性能、连接情况、缓冲池使用情况。即当前脚本中包括至少一条语句，语句的作用是采集当前***性能指标参数(当前***性能指标参数包括上述列举的参数，即吞吐量、执行性能、连接情况、缓冲池使用情况)。***性能指标监控项起到了在脚本执行过程中实时监控***性能指标的作用，避免了用户在脚本执行工程中需手动开启任务查看器才能查看***性能指标的问题。In this embodiment, in order to obtain the impact of the current script on the performance of the system in real time during the execution of the script, it is necessary to add a system performance indicator monitoring item in the current script (the system performance indicator monitoring item is similar to the parameter acquisition statement, and its function It is to collect the corresponding index parameters, which is the function of real-time monitoring and acquisition of the corresponding index parameters of the system) to monitor the following performance of the system: throughput, execution performance, connection status, buffer pool usage. That is, the current script includes at least one statement, and the function of the statement is to collect current system performance indicator parameters (the current system performance indicator parameters include the above enumerated parameters, that is, throughput, execution performance, connection status, buffer pool usage). The system performance indicator monitoring item plays a role in monitoring the system performance indicators in real time during script execution, which avoids the problem that the user needs to manually open the task viewer in the script execution project to view the system performance indicators.

在一实施例中，所述当前***性能指标参数包括：吞吐量、SQL脚本执行性能参数、服务器连接情况参数、缓冲池使用情况参数。In an embodiment, the current system performance indicator parameters include: throughput, SQL script execution performance parameters, server connection status parameters, and buffer pool usage parameters.

如图2所示，该步骤S101包括以下子步骤：As shown in FIG. 2, the step S101 includes the following sub-steps:

S1011、通过内部计数器进行已执行语句的计数，得到吞吐量；S1011: Counting the executed statement by using an internal counter to obtain a throughput;

S1012、通过统计出现错误的SQL语句数量，或统计超过可配置的长查询限制的查询数量，得到SQL脚本执行性能参数；S1012: Obtaining a SQL script execution performance parameter by counting the number of SQL statements that have an error, or counting the number of queries exceeding a configurable long query limit;

S1013、通过统计当前开放的连接数量、当前运行的连接数量、由服务器错误导致的失败连接数、尝试与服务器进行连接结果失败的次数、或由最大连接数限制导致的失败连接数中任意一个，得到服务器连接情况参数；S1013, by counting the number of currently open connections, the number of currently running connections, the number of failed connections caused by server errors, the number of failed attempts to connect to the server, or the number of failed connections caused by the maximum number of connections, Get the server connection status parameter;

S1014、通过统计缓冲池中的总页数、缓冲池中已使用页数所占的比率、缓冲池发送的请求数、或缓冲池无法满足的请求数中任意一个，得到缓冲池使用情况参数。S1014: Obtain a buffer pool usage parameter by using the total number of pages in the statistics buffer pool, the ratio of the number of used pages in the buffer pool, the number of requests sent by the buffer pool, or the number of requests that the buffer pool cannot satisfy.

在本实施例中，监控吞吐量时，通过名称为Questions的内部计数器进行已执行语句(由客户端发出)计数；通过统计Com_select语句或Writes语句的总量来计算吞吐量；例如，通过以下指令，查询诸如Questions或Com_select服务器状态变量的值：SHOW GLOBAL STATUS LIKE″Questions″。In this embodiment, when the throughput is monitored, the executed statement (issued by the client) is counted by an internal counter named Questions; the throughput is calculated by counting the total amount of the Com_select statement or the Writes statement; for example, by the following instruction , query the value of the Question State or Com_select server state variable: SHOW GLOBAL STATUS LIKE "Questions".

监控SQL脚本执行性能参数时，主要是查询出现错误的SQL语句数量，及超过可配置的long_query_time限制的查询数量。上述两个指标都可从性能模式下的events_statements_summary_by_digest表格中得到。When monitoring SQL script execution performance parameters, it is mainly to query the number of SQL statements that have errors and the number of queries that exceed the configurable long_query_time limit. Both of the above metrics are available in the events_statements_summary_by_digest table in performance mode.

监控连接情况参数时，通过Threads_connected语句查询当前开放的连接，通过Threads_running语句查询当前运行的连接，通过Connection_errors_internal语句查询由服务器错误导致的失败连接数，通过Aborted_connects语句查询尝试与服务器进行连接结果失败的次数，通过Connection_errors_max_connections语句查询由max_connections限制导致的失败连接数。When monitoring the connection status parameter, query the currently open connection through the Threads_connected statement, query the currently running connection through the Threads_running statement, query the number of failed connections caused by the server error through the Connection_errors_internal statement, and query the number of failed attempts to connect with the server through the Aborted_connects statement. The number of failed connections caused by the max_connections limit is queried through the Connection_errors_max_connections statement.

监控缓冲池使用情况参数时，通过Innodb_buffer_pool_pages_total语句查询缓冲池中的总页数，通过缓冲池使用率语句查询缓冲池中已使用页数所占的比率，通过Innodb_buffer_pool_read_requests语句查询向缓冲池发送的请求数，通过Innodb_buffer_pool_reads语句查询缓冲池无法满足的请求数。When monitoring the buffer pool usage parameter, query the total number of pages in the buffer pool through the Innodb_buffer_pool_pages_total statement, query the ratio of the number of used pages in the buffer pool through the buffer pool usage statement, and query the number of requests sent to the buffer pool through the Innodb_buffer_pool_read_requests statement. The number of requests that the buffer pool cannot satisfy is queried through the Innodb_buffer_pool_reads statement.

通过执行当前脚本中预先写入的对应指令或对应语句时，就能对当前***性能指标参数进行精准监控，并能根据所采集的当前***性能指标参数判断***当前性能存在隐患时，及时对用户进行报警并自动进行修复。By executing the corresponding command or corresponding statement written in the current script, the current system performance indicator parameters can be accurately monitored, and the current system performance index parameters can be determined according to the collected current system performance index parameters, and the user is timely Alert and automatically repair.

S102、若当前***性能指标参数超出预设的指标预警值，将当前脚本记录至指定路径。S102: If the current system performance indicator parameter exceeds the preset indicator warning value, the current script is recorded to the specified path.

在一实施例中，若在吞吐量、SQL脚本执行性能参数、服务器连接情况参数、或缓冲池使用情况参数中有至少一个超出对应预设的指标预警值，判定当前***性能指标参数超出预设的指标预警值。In an embodiment, if at least one of the throughput, the SQL script execution performance parameter, the server connection status parameter, or the buffer pool usage parameter exceeds the preset preset indicator value, determining that the current system performance indicator parameter exceeds the preset. Indicator warning value.

在本实施例中，指定路径为预先设置的用于存储数据的一块或多块区域所对应路径(脚本也可以视为数据)，例如在脚本所执行的执行机器中存储介质选定一定存储空间，并用作存储脚本，也即只要满足当前***性能指标参数超出预设的指标预警值的条件，就将当前脚本记录至指定路径。由于在脚本运行过程中会影响***的多项***性能指标，上述列举的多个性能指标中，可对每一个性能指标都对应设置一个指标预警值，还可对其中比较重要的几个性能指标设置重要指标标识(如吞吐量、当前运行的连接、服务器错误导致的失败连接数、缓冲池中的总页数)，上述具有重要指标标识的指标中有一个或多个一旦超出对应指标预警值，则视为当前脚本的质量低下，需要对其进行优化或修复。如果除了上述设置重要指标标识的***性能指标以外的其他指标超出指标预警值，则只进行***提示(如通过桌面弹出框进行弹窗报警提示，在弹窗内显示具体是哪几个指标超出指标预警值，而且显示这几个指标的当前值，该弹窗在显示了指定时间后不再显示)，不进行优化脚本的提示。也就是至少要有一个被设置重要指标标识的***性能指标超出指标预警值，才视为真正的超出指标预警值，并需要将当前脚本记录至指定路径。In this embodiment, the specified path is a path corresponding to one or more blocks for storing data (the script may also be regarded as data), for example, the storage medium selected in the execution machine executed by the script selects a certain storage space. And used as a storage script, that is, as long as the current system performance indicator parameter exceeds the condition of the preset indicator warning value, the current script is recorded to the specified path. Because many system performance indicators of the system are affected during the running of the script, among the multiple performance indicators listed above, one indicator early warning value can be set for each performance indicator, and several important performance indicators are also important. Set important indicator IDs (such as throughput, current running connections, number of failed connections caused by server errors, total number of pages in the buffer pool), and one or more of the above indicators with important indicator identifiers exceed the corresponding indicator warning value. , the current script is considered to be of low quality and needs to be optimized or fixed. If the other indicators except the system performance indicators of the above-mentioned important indicator identifiers exceed the indicator warning value, only the system prompts (for example, pop-up alarm prompt through the desktop pop-up box, which specific indicators are exceeded in the pop-up window) The warning value, and the current value of these indicators are displayed. The popup window is no longer displayed after the specified time is displayed. The prompt of the optimization script is not performed. That is, at least one system performance indicator that is set with an important indicator identifier exceeds the indicator warning value, and is regarded as a true out-of-arrival warning value, and the current script needs to be recorded to the specified path.

S103、获取当前脚本的执行计划。S103. Obtain an execution plan of the current script.

在本实施例中，执行计划是数据库内部对SQL语句分析后决定的执行路径和执行步骤。一个执行计划由若干基本操作组成，例如遍历整张表、利用索引、执行一个嵌套循环或Hash连接等等，上述所有的基本操作都有一个输出即结果集。In this embodiment, the execution plan is an execution path and an execution step determined after analyzing the SQL statement inside the database. An execution plan consists of several basic operations, such as traversing the entire table, using an index, executing a nested loop, or a hash join, etc. All of the basic operations described above have an output, the result set.

若有当前***性能指标参数超出预设的指标预警值，为了具体分析当前脚本的具体执行情况，需要查看当前脚本的执行计划，从执行计划中看出当前脚本执行过程中各当前***性能指标参数的具体取值。本申请中执行计划采用利用索引的方式进行基本操作。If the current system performance indicator parameter exceeds the preset indicator warning value, in order to analyze the specific execution of the current script, you need to view the execution plan of the current script. From the execution plan, you can see the current system performance indicator parameters during the current script execution. The specific value. The execution plan in this application uses the index to perform basic operations.

其中，获取当前脚本的执行计划通过以下三条语句：Among them, the execution plan of the current script is obtained through the following three statements:

explain plan for待查看的SQL语句；Explain plan for the SQL statement to be viewed;

commit；Commit;

select*from table(dbms_xplan.display)；Select*from table(dbms_xplan.display);

explain plan for是用于查看待查看的SQL语句的执行计划的语句，commit为提交语句，select*from table(dbms_xplan.display)是查看dbms_xplan.display表中数据的语句。通过查看执行计划可以获取当前脚本的索引情况。The explain plan for is a statement for viewing the execution plan of the SQL statement to be viewed, commit is a commit statement, and select*from table(dbms_xplan.display) is a statement for viewing data in the dbms_xplan.display table. You can get the index of the current script by looking at the execution plan.

S104、若当前脚本的执行计划中缺少索引，自动添加索引至当前脚本。S104. If an index is missing from the execution plan of the current script, the index is automatically added to the current script.

在一实施例中，通过动态管理视图来判断当前脚本的执行计划是否缺乏索引。在本实施例中，判断当前脚本的执行计划是否缺乏索引时，通过DMV(Dynamic Management Views，动态管理视图)来判断当前脚本的执行计划是否缺乏索引，更具体的是通过如下的动态管理视图来实现：In an embodiment, the dynamic management view is used to determine whether the execution plan of the current script lacks an index. In this embodiment, when it is determined whether the execution plan of the current script lacks an index, the DMV (Dynamic Management Views) is used to determine whether the execution plan of the current script lacks an index, and more specifically, through the following dynamic management view. achieve:

sys.dm_db_missing_index_detailsSys.dm_db_missing_index_details

sys.dm_db_missing_index_groupsSys.dm_db_missing_index_groups

sys.dm_db_missing_index_group_statsSys.dm_db_missing_index_group_stats

sys.dm_db_missing_index_columns(index_handle)Sys.dm_db_missing_index_columns(index_handle)

sys.dm_db_missing_index_detailsSys.dm_db_missing_index_details

上述DMV记录了当前数据库下所有的missing index的信息，其针对的是数据库服务器从启动以来所有运行的语句，而不是针对某一个查询。The above DMV records information about all missing indexes in the current database, which is for all running statements of the database server since startup, rather than for a certain query.

其中，由sys.dm_db_missing_index_details返回的信息会在查询优化器优化查询时更新，因而不是持久化的。缺失索引信息只保留到重新启动数据库服务器前。如果数据库管理员要在服务器回收后保留缺失索引信息，则应定期制作缺失索引信息的备份副本，即在指定路径存储缺失索引信息的备份副本确保数据库服务器重启后也能使用；sys.dm_db_missing_index_columns(index_handle)则返回与缺少索引(不包括空间索引)的数据库表列有关的信息，sys.dm_db_missing_index_columns是一个动态管理函数，index_handle是唯一地标识缺失索引的整数。sys.dm_db_missing_index_groups返回有关特定缺失索引组中包含的缺失索引(不包括空间索引)的信息。sys.dm_db_missing_index_group_stats则返回缺失索引组的摘要信息，不包括空间索引。The information returned by sys.dm_db_missing_index_details is updated when the query optimizer optimizes the query and is therefore not persistent. Missing index information is only kept until the database server is restarted. If the database administrator wants to keep the missing index information after the server is reclaimed, it should periodically make a backup copy of the missing index information, that is, store the backup copy of the missing index information in the specified path to ensure that the database server can be used after restarting; sys.dm_db_missing_index_columns(index_handle Return information about database table columns that lack indexes (excluding spatial indexes), sys.dm_db_missing_index_columns is a dynamic management function, and index_handle is an integer that uniquely identifies missing indexes. Sys.dm_db_missing_index_groups returns information about missing indexes (excluding spatial indexes) contained in a particular missing index group. Sys.dm_db_missing_index_group_stats returns summary information for missing index groups, excluding spatial indexes.

在一实施例中，通过fn_Index_CreatelndexName函数对当前脚本自动添加索引。其中，fn_Index_CreatelndexName函数包括有三个输入参数：In an embodiment, the current script is automatically indexed by the fn_Index_CreatelndexName function. Among them, the fn_Index_CreatelndexName function includes three input parameters:

@equality_columns@equality_columns

@index_handlE@index_handlE

该函数的目的是为每个期望创建的索引都创建一个唯一名称。其中，拼接@equality_columns和@equality_columns两个输入变量，如果拼接后所得结果超过120个字符，那就截取至第120个字符。The purpose of this function is to create a unique name for each index that you expect to create. Among them, splicing @equality_columns and @equality_columns two input variables, if the result of splicing exceeds 120 characters, it is intercepted to the 120th character.

在对当前脚本自动添加索引，判断索引是否生效时，通过EXPLAIN解释命令查看索引是否生效。其中，具体使用时在select语句前加上explain即可判断索引是否生效，如：explain select surname，first_name form a，b where a.id＝b.id。When an index is automatically added to the current script to determine whether the index is valid, the EXPLAIN interpretation command is used to check whether the index takes effect. Among them, in the specific use, add the explain before the select statement to determine whether the index is valid, such as: explain select surname, first_name form a, b where a. id = b.id.

在脚本中添加了索引后，且在索引生效后就能完成对当前脚本的自动优化，且这一优化过程是在判断当前***性能指标参数超出预设的指标预警值而进行***报警时立即进行的修复，起到了实时修复的功能，避免了在故障后才从多条SQL语句定位故障产生原因而导致的修复效率实时性低的问题。After the index is added to the script, and the index is valid, the automatic optimization of the current script can be completed, and the optimization process is performed immediately when the current system performance indicator parameter exceeds the preset indicator warning value and the system alarm is performed. The repair function of real-time repair avoids the problem of low real-time repair efficiency caused by multiple SQL statement positioning faults after the fault.

S105、若当前脚本的索引失效，通过强制索引进行脚本修复，得到修复后脚本。S105. If the index of the current script is invalid, the script is repaired by forcing the index, and the repaired script is obtained.

如图3所示，该步骤S105包括以下子步骤：As shown in FIG. 3, the step S105 includes the following sub-steps:

S1051、获取所选定的数据库优化器；其中，所述数据库优化器为基于规则的优化器、基于成本的优化器、或选择性的优化器中的一种；S1051: Acquire a selected database optimizer; wherein the database optimizer is one of a rule-based optimizer, a cost-based optimizer, or a selective optimizer;

S1052、通过rebuild指令重建当前脚本的索引；S1052: Rebuild the index of the current script by using a rebuild command;

S1053、通过hint指令强制使用索引，得到修复后脚本。S1053. Force the index to be used by the hint instruction to obtain the repaired script.

在本实施例中，据库优化器是预先选定的，当需要采用全表扫描模式时则选用选择性的优化器，当无需采用全表扫描模式时则选用基于成本的优化器或基于规则的优化器。通过alter index索引名rebuild(例如alter index RECORD_ENTITYID rebuild，其中RECORD_ENTITYID为索引名)语句重建当前脚本的索引；通过给该语句加上hint后，强制其使用′RECORD_ENTITYID′这个索引。更具体的是在SELECT关键字后面，加上“/*+INDEX(表名称，索引名称)*/”的方式，强制数据库优化器用上该索引，以对脚本进行修复，实现了及时发现SQL脚本的问题并快速自动修复。In this embodiment, the database optimizer is pre-selected, and a selective optimizer is used when the full table scan mode is required, and a cost-based optimizer or rule-based is used when the full table scan mode is not required. Optimizer. Rebuild the index of the current script by using the alter index index name rebuild (for example, alter index RECORD_ENTITYID rebuild, where RECORD_ENTITYID is the index name); by adding a hint to the statement, it is forced to use the index "RECORD_ENTITYID". More specifically, after the SELECT keyword, add "/ * + INDEX (table name, index name) * /" way, force the database optimizer to use the index to repair the script, to achieve timely discovery of SQL scripts The problem is fixed quickly and automatically.

可见，该方法能够及时监控***性能，并且能对影响***性能很大关系的SQL脚本进行自动优化，实现快速发现性能问题并解决。It can be seen that the method can monitor system performance in time, and can automatically optimize SQL scripts that affect system performance, so as to quickly find and solve performance problems.

本申请实施例还提供一种SQL自动优化装置，该SQL自动优化装置用于执行前述任一项SQL自动优化方法。具体地，请参阅图4，图4是本申请实施例提供的一种SQL自动优化装置的示意性框图。SQL自动优化装置100可以安装于台式电脑、平板电脑、手提电脑、等终端中。The embodiment of the present application further provides an SQL automatic optimization apparatus, which is used to execute any of the foregoing SQL automatic optimization methods. Specifically, please refer to FIG. 4. FIG. 4 is a schematic block diagram of an apparatus for automatically optimizing SQL according to an embodiment of the present application. The SQL automatic optimization device 100 can be installed in a desktop computer, a tablet computer, a laptop computer, or the like.

如图4所示，SQL自动优化装置100包括指标参数获取单元101、超标脚本记录单元102、执行计划获取单元103、索引自动添加单元104、脚本修复单元 105。As shown in FIG. 4, the SQL automatic optimization apparatus 100 includes an index parameter acquisition unit 101, a superscript script recording unit 102, an execution plan acquisition unit 103, an index automatic addition unit 104, and a script repair unit 105.

指标参数获取单元101，用于当前脚本为执行状态时，根据当前脚本中所设置的***性能指标监控项采集当前***性能指标参数。The indicator parameter obtaining unit 101 is configured to collect current system performance indicator parameters according to the system performance indicator monitoring item set in the current script when the current script is in an execution state.

如图5所示，所述指标参数获取单元101包括以下子单元：As shown in FIG. 5, the indicator parameter obtaining unit 101 includes the following subunits:

第一参数获取单元1011，用于通过内部计数器进行已执行语句的计数，得到吞吐量；The first parameter obtaining unit 1011 is configured to perform counting of executed statements by using an internal counter to obtain throughput;

第二参数获取单元1012，用于通过统计出现错误的SQL语句数量，或统计超过可配置的长查询限制的查询数量，得到SQL脚本执行性能参数；The second parameter obtaining unit 1012 is configured to obtain a SQL script execution performance parameter by counting the number of SQL statements that have errors, or counting the number of queries exceeding the configurable long query limit.

第三参数获取单元1013，用于通过统计当前开放的连接数量、当前运行的连接数量、由服务器错误导致的失败连接数、尝试与服务器进行连接结果失败的次数、或由最大连接数限制导致的失败连接数中任意一个，得到服务器连接情况参数；The third parameter obtaining unit 1013 is configured to collect statistics on the number of currently open connections, the number of currently running connections, the number of failed connections caused by server errors, the number of failed attempts to connect to the server, or the maximum number of connections. Any one of the number of failed connections, get the server connection status parameter;

第四参数获取单元1014，用于通过统计缓冲池中的总页数、缓冲池中已使用页数所占的比率、缓冲池发送的请求数、或缓冲池无法满足的请求数中任意一个，得到缓冲池使用情况参数。The fourth parameter obtaining unit 1014 is configured to collect, by using, a total number of pages in the buffer pool, a ratio of the number of used pages in the buffer pool, a number of requests sent by the buffer pool, or a number of requests that cannot be satisfied by the buffer pool. Get the buffer pool usage parameters.

超标脚本记录单元102，用于若当前***性能指标参数超出预设的指标预警值，将当前脚本记录至指定路径。The over-script script recording unit 102 is configured to record the current script to the specified path if the current system performance indicator parameter exceeds the preset indicator warning value.

执行计划获取单元103，用于获取当前脚本的执行计划。The execution plan acquisition unit 103 is configured to acquire an execution plan of the current script.

commit；Commit;

select*from table(dbms_xplan.display)；Select*from table(dbms_xplan.display);

索引自动添加单元104，用于若当前脚本的执行计划中缺少索引，自动添加索引至当前脚本。The index automatic adding unit 104 is configured to automatically add an index to the current script if an index is missing from the execution plan of the current script.

sys.dm_db_missing_index_detailsSys.dm_db_missing_index_details

sys.dm_db_missing_index_groupsSys.dm_db_missing_index_groups

sys.dm_db_missing_index_group_statsSys.dm_db_missing_index_group_stats

sys.dm_db_missing_index_detailsSys.dm_db_missing_index_details

在一实施例中，通过fn_Index_CreateIndexName函数对当前脚本自动添加索引。其中，fn_Index_CreateIndexName函数包括有三个输入参数：In an embodiment, the current script is automatically indexed by the fn_Index_CreateIndexName function. Among them, the fn_Index_CreateIndexName function includes three input parameters:

@equality_columns@equality_columns

@index_handlE@index_handlE

脚本修复单元105，用于若当前脚本的索引失效，通过强制索引进行脚本修复，得到修复后脚本。The script repairing unit 105 is configured to: if the index of the current script is invalid, perform script repair by forcing the index, and obtain the repaired script.

如图6所示，所述脚本修复单元105包括以下子单元：As shown in FIG. 6, the script repair unit 105 includes the following subunits:

优化器选定单元1051，用于获取所选定的数据库优化器；其中，所述数据库优化器为基于规则的优化器、基于成本的优化器、或选择性的优化器中的一种；An optimizer selecting unit 1051, configured to acquire the selected database optimizer; wherein the database optimizer is one of a rule based optimizer, a cost based optimizer, or a selective optimizer;

重建索引单元1052，用于通过rebuild指令重建当前脚本的索引；Rebuilding an index unit 1052, configured to rebuild an index of the current script by using a rebuild instruction;

强制索引单元1053，用于通过hint指令强制使用索引，得到修复后脚本。The index unit 1053 is configured to force the use of the index by the hint instruction to obtain the repaired script.

可见，该装置能够及时监控***性能，并且能对影响***性能很大关系的SQL脚本进行自动优化，实现快速发现性能问题并解决。It can be seen that the device can monitor system performance in time, and can automatically optimize SQL scripts that affect system performance, so as to quickly find and solve performance problems.

上述SQL自动优化装置可以实现为一种计算机程序的形式，该计算机程序可以在如图7所示的计算机设备上运行。The above SQL automatic optimization apparatus can be implemented in the form of a computer program that can be run on a computer device as shown in FIG.

请参阅图7，图7是本申请实施例提供的一种计算机设备的示意性框图。该计算机设备500设备可以是终端。该终端可以是平板电脑、笔记本电脑、台式电脑、个人数字助理等电子设备。Please refer to FIG. 7. FIG. 7 is a schematic block diagram of a computer device according to an embodiment of the present application. The computer device 500 device can be a terminal. The terminal can be an electronic device such as a tablet computer, a notebook computer, a desktop computer, or a personal digital assistant.

参阅图7，该计算机设备500包括通过***总线501连接的处理器502、存储器和网络接口505，其中，存储器可以包括非易失性存储介质503和内存储器504。该非易失性存储介质503可存储操作***5031和计算机程序5032。该计算机程序5032包括程序指令，该程序指令被执行时，可使得处理器502执行一种SQL自动优化方法。该处理器502用于提供计算和控制能力，支撑整个计算机设备500的运行。该内存储器504为非易失性存储介质503中的计算机程序5032的运行提供环境，该计算机程序5032被处理器502执行时，可使得处理器 502执行一种SQL自动优化方法。该网络接口505用于进行网络通信，如发送分配的任务等。本领域技术人员可以理解，图7中示出的结构，仅仅是与本申请方案相关的部分结构的框图，并不构成对本申请方案所应用于其上的计算机设备500的限定，具体的计算机设备500可以包括比图中所示更多或更少的部件，或者组合某些部件，或者具有不同的部件布置。Referring to FIG. 7, the computer device 500 includes a processor 502, a memory, and a network interface 505 connected by a system bus 501, wherein the memory can include a non-volatile storage medium 503 and an internal memory 504. The non-volatile storage medium 503 can store an operating system 5031 and a computer program 5032. The computer program 5032 includes program instructions that, when executed, cause the processor 502 to perform an SQL auto-optimization method. The processor 502 is used to provide computing and control capabilities to support the operation of the entire computer device 500. The internal memory 504 provides an environment for the operation of the computer program 5032 in the non-volatile storage medium 503, which when executed by the processor 502, causes the processor 502 to perform an SQL automated optimization method. The network interface 505 is used for network communication, such as sending assigned tasks and the like. It will be understood by those skilled in the art that the structure shown in FIG. 7 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 of the computer device 500 to which the solution of the present application is applied, and a specific computer device. 500 may include more or fewer components than shown, or some components may be combined, or have different component arrangements.

其中，所述处理器502用于运行存储在存储器中的计算机程序5032，以实现如下功能：当前脚本为执行状态时，根据当前脚本中所设置的***性能指标监控项采集当前***性能指标参数；若当前***性能指标参数超出预设的指标预警值，将当前脚本记录至指定路径；获取当前脚本的执行计划；若当前脚本的执行计划中缺少索引，自动添加索引至当前脚本；若当前脚本的索引失效，通过强制索引进行脚本修复，得到修复后脚本。The processor 502 is configured to run the computer program 5032 stored in the memory to implement the following functions: when the current script is in an execution state, collect current system performance indicator parameters according to the system performance indicator monitoring item set in the current script; If the current system performance indicator parameter exceeds the preset indicator warning value, the current script is recorded to the specified path; the execution plan of the current script is obtained; if the index of the current script is missing from the execution plan, the index is automatically added to the current script; if the current script is The index is invalid, and the script is fixed by forcing the index to get the post-repair script.

在一实施例中，处理器502还执行如下操作：所述当前***性能指标参数包括吞吐量、SQL脚本执行性能参数、服务器连接情况参数、缓冲池使用情况参数；通过内部计数器进行已执行语句的计数，得到吞吐量；通过统计出现错误的SQL语句数量，或统计超过可配置的长查询限制的查询数量，得到SQL脚本执行性能参数；通过统计当前开放的连接数量、当前运行的连接数量、由服务器错误导致的失败连接数、尝试与服务器进行连接结果失败的次数、或由最大连接数限制导致的失败连接数中任意一个，得到服务器连接情况参数；通过统计缓冲池中的总页数、缓冲池中已使用页数所占的比率、缓冲池发送的请求数、或缓冲池无法满足的请求数中任意一个，得到缓冲池使用情况参数。In an embodiment, the processor 502 further performs the following operations: the current system performance indicator parameters include a throughput, a SQL script execution performance parameter, a server connection status parameter, a buffer pool usage parameter, and an executed statement by an internal counter. Count, get throughput; by counting the number of SQL statements that have errors, or counting the number of queries that exceed the configurable long query limit, get the SQL script execution performance parameters; by counting the number of currently open connections, the number of connections currently running, by The server connection status parameter is obtained by the number of failed connections caused by the server error, the number of failed attempts to connect to the server, or the number of failed connections caused by the maximum number of connections; by counting the total number of pages in the buffer pool, buffering The buffer pool usage parameter is obtained by the ratio of the number of used pages in the pool, the number of requests sent by the buffer pool, or the number of requests that the buffer pool cannot satisfy.

在一实施例中，处理器502还执行如下操作：若吞吐量、SQL脚本执行性能参数、服务器连接情况参数、及缓冲池使用情况参数中至少一个超出对应预设的指标预警值，判定当前***性能指标参数超出预设的指标预警值。In an embodiment, the processor 502 further performs the following operations: if at least one of the throughput, the SQL script execution performance parameter, the server connection status parameter, and the buffer pool usage parameter exceeds a preset preset indicator warning value, determining the current system. The performance indicator parameter exceeds the preset indicator warning value.

在一实施例中，处理器502还执行如下操作：通过动态管理视图来判断当前脚本的执行计划是否缺乏索引。In an embodiment, the processor 502 also performs an operation of determining whether the execution plan of the current script lacks an index by dynamically managing the view.

在一实施例中，处理器502还执行如下操作：通过fn_Index_CreateIndexName函数对当前脚本自动添加索引。In an embodiment, the processor 502 also performs the operation of automatically adding an index to the current script by the fn_Index_CreateIndexName function.

在一实施例中，处理器502还执行如下操作：获取所选定的数据库优化器；其中，所述数据库优化器为基于规则的优化器、基于成本的优化器、或选择性的优化器中的一种；通过rebuild指令重建当前脚本的索引；通过hint指令强制使用索引，得到修复后脚本。In an embodiment, the processor 502 also performs the operations of: acquiring the selected database optimizer; wherein the database optimizer is a rule-based optimizer, a cost-based optimizer, or a selective optimizer One; rebuild the index of the current script through the rebuild command; force the index through the hint instruction to get the repaired script.

本领域技术人员可以理解，图7中示出的计算机设备的实施例并不构成对计算机设备具体构成的限定，在其他实施例中，计算机设备可以包括比图示更多或更少的部件，或者组合某些部件，或者不同的部件布置。例如，在一些实施例中，计算机设备可以仅包括存储器及处理器，在这样的实施例中，存储器及处理器的结构及功能与图7所示实施例一致，在此不再赘述。It will be understood by those skilled in the art that the embodiment of the computer device shown in FIG. 7 does not constitute a limitation on the specific configuration of the computer device. In other embodiments, the computer device may include more or fewer components than illustrated. Or combine some parts, or different parts. For example, in some embodiments, the computer device may include only a memory and a processor. In such an embodiment, the structure and function of the memory and the processor are the same as those of the embodiment shown in FIG. 7, and details are not described herein again.

应当理解，在本申请实施例中，处理器502可以是中央处理单元(Central Processing Unit，CPU)，该处理器502还可以是其他通用处理器、数字信号处理器(Digital Signal Processor，DSP)、专用集成电路(Application Specific Integrated Circuit，ASIC)、现成可编程门阵列(Field-Programmable Gate Array，FPGA)或者其他可编程逻辑器件、分立门或者晶体管逻辑器件、分立硬件组件等。其中，通用处理器可以是微处理器或者该处理器也可以是任何常规的处理器等。It should be understood that, in the embodiment of the present application, the processor 502 may be a central processing unit (CPU), and the processor 502 may also be another general-purpose processor, a digital signal processor (DSP), Application Specific Integrated Circuit (ASIC), Field-Programmable Gate Array (FPGA) or other programmable logic device, discrete gate or transistor logic device, discrete hardware component, etc. The general purpose processor may be a microprocessor or the processor may be any conventional processor or the like.

在本申请的另一实施例中提供一种存储介质。该存储介质可以为非易失性的计算机可读存储介质。该存储介质存储有计算机程序，其中计算机程序包括程序指令。该程序指令被处理器执行时实现本申请实施例的SQL自动优化方法。In another embodiment of the present application, a storage medium is provided. The storage medium can be a non-transitory computer readable storage medium. The storage medium stores a computer program, wherein the computer program includes program instructions. When the program instruction is executed by the processor, the SQL automatic optimization method of the embodiment of the present application is implemented.

所述存储介质可以是前述设备的内部存储单元，例如设备的硬盘或内存。所述存储介质也可以是所述设备的外部存储设备，例如所述设备上配备的插接式硬盘，智能存储卡(Smart Media Card，SMC)，安全数字(Secure Digital，SD)卡，闪存卡(Flash Card)等。进一步地，所述存储介质还可以既包括所述设备的内部存储单元也包括外部存储设备。The storage medium may be an internal storage unit of the aforementioned device, such as a hard disk or a memory of the device. The storage medium may also be an external storage device of the device, such as a plug-in hard disk equipped on the device, a smart memory card (SMC), a secure digital (SD) card, and a flash memory card. (Flash Card), etc. Further, the storage medium may also include both an internal storage unit of the device and an external storage device.

所属领域的技术人员可以清楚地了解到，为了描述的方便和简洁，上述描述的设备、装置和单元的具体工作过程，可以参考前述方法实施例中的对应过程，在此不再赘述。A person skilled in the art can clearly understand that for the convenience and brevity of the description, the specific working process of the device, the device and the unit described above can refer to the corresponding process in the foregoing method embodiment, and details are not described herein again.

以上所述，仅为本申请的具体实施方式，但本申请的保护范围并不局限于此，任何熟悉本技术领域的技术人员在本申请揭露的技术范围内，可轻易想到各种等效的修改或替换，这些修改或替换都应涵盖在本申请的保护范围之内。因此，本申请的保护范围应以权利要求的保护范围为准。The foregoing is only a specific embodiment of the present application, but the scope of protection of the present application is not limited thereto, and any equivalents can be easily conceived by those skilled in the art within the technical scope disclosed in the present application. Modifications or substitutions are intended to be included within the scope of the present application. Therefore, the scope of protection of this application should be determined by the scope of protection of the claims.

Claims

一种SQL自动优化方法，其特征在于，包括：An automatic SQL optimization method, which comprises:

当前脚本为执行状态时，根据当前脚本中所设置的***性能指标监控项采集当前***性能指标参数；When the current script is in the execution state, the current system performance indicator parameters are collected according to the system performance indicator monitoring items set in the current script;

若当前***性能指标参数超出预设的指标预警值，将当前脚本记录至指定路径；If the current system performance indicator parameter exceeds the preset indicator warning value, the current script is recorded to the specified path;

获取当前脚本的执行计划；Get the execution plan of the current script;

若当前脚本的执行计划中缺少索引，自动添加索引至当前脚本；If the index is missing from the execution plan of the current script, the index is automatically added to the current script;

若当前脚本的索引失效，通过强制索引进行脚本修复，得到修复后脚本。If the index of the current script is invalid, the script is repaired by forcing the index, and the repaired script is obtained.
根据权利要求1所述的SQL自动优化方法，其特征在于，所述当前***性能指标参数包括：吞吐量、SQL脚本执行性能参数、服务器连接情况参数、缓冲池使用情况参数；The SQL automatic optimization method according to claim 1, wherein the current system performance indicator parameters include: throughput, SQL script execution performance parameters, server connection status parameters, and buffer pool usage parameters;

所述根据当前脚本中所设置的***性能指标监控项采集当前***性能指标参数，包括：Collecting current system performance indicator parameters according to the system performance indicator monitoring item set in the current script, including:

通过内部计数器进行已执行语句的计数，得到吞吐量；The execution of the executed statement is counted by the internal counter to obtain the throughput;

通过统计出现错误的SQL语句数量，或统计超过可配置的长查询限制的查询数量，得到SQL脚本执行性能参数；Get SQL script execution performance parameters by counting the number of SQL statements that have errors, or counting the number of queries that exceed the configurable long query limit.

通过统计当前开放的连接数量、当前运行的连接数量、由服务器错误导致的失败连接数、尝试与服务器进行连接结果失败的次数、或由最大连接数限制导致的失败连接数中任意一个，得到服务器连接情况参数；Get the server by counting the number of currently open connections, the number of currently running connections, the number of failed connections caused by server errors, the number of failed attempts to connect to the server, or the number of failed connections caused by the maximum number of connections. Connection condition parameter;

通过统计缓冲池中的总页数、缓冲池中已使用页数所占的比率、缓冲池发送的请求数、或缓冲池无法满足的请求数中任意一个，得到缓冲池使用情况参数。The buffer pool usage parameter is obtained by counting the total number of pages in the buffer pool, the ratio of the number of used pages in the buffer pool, the number of requests sent by the buffer pool, or the number of requests that the buffer pool cannot satisfy.
根据权利要求2所述的SQL自动优化方法，其特征在于，所述若当前***性能指标参数超出预设的指标预警值中，若在吞吐量、SQL脚本执行性能参数、服务器连接情况参数、或缓冲池使用情况参数中有至少一个超出对应预设的指标预警值，判定当前***性能指标参数超出预设的指标预警值。The SQL automatic optimization method according to claim 2, wherein if the current system performance indicator parameter exceeds a preset indicator warning value, if the throughput, the SQL script execution performance parameter, the server connection status parameter, or At least one of the buffer pool usage parameters exceeds the preset preset indicator value, and determines that the current system performance indicator parameter exceeds the preset indicator warning value.
根据权利要求1所述的SQL自动优化方法，其特征在于，所述若当前脚本的执行计划中缺少索引之前，包括：The SQL automatic optimization method according to claim 1, wherein if the index is missing from the execution plan of the current script, the method includes:

通过动态管理视图来判断当前脚本的执行计划是否缺乏索引。Dynamically manage the view to determine if the execution plan of the current script lacks an index.
根据权利要求1所述的SQL自动优化方法，其特征在于，所述自动添加索引至当前脚本包括，通过fn_Index_CreateIndexName函数对当前脚本自动添加索引。The SQL automatic optimization method according to claim 1, wherein the automatically adding an index to the current script comprises automatically adding an index to the current script by using the fn_Index_CreateIndexName function.
根据权利要求1所述的SQL自动优化方法，其特征在于，所述通过强制索引进行脚本修复，得到修复后脚本，包括：The SQL automatic optimization method according to claim 1, wherein the script is repaired by forcing an index, and the repaired script is obtained, including:

获取所选定的数据库优化器；其中，所述数据库优化器为基于规则的优化器、基于成本的优化器、或选择性的优化器中的一种；Obtaining the selected database optimizer; wherein the database optimizer is one of a rule based optimizer, a cost based optimizer, or a selective optimizer;

通过rebuild指令重建当前脚本的索引；Rebuild the index of the current script through the rebuild command;

通过hint指令强制使用索引，得到修复后脚本。The index is used by the hint instruction to get the post-repair script.
一种SQL自动优化装置，其特征在于，包括：An automatic SQL optimization device, comprising:

指标参数获取单元，用于当前脚本为执行状态时，根据当前脚本中所设置的***性能指标监控项采集当前***性能指标参数；The indicator parameter obtaining unit is configured to collect current system performance indicator parameters according to the system performance indicator monitoring item set in the current script when the current script is in an execution state;

超标脚本记录单元，用于若当前***性能指标参数超出预设的指标预警值，将当前脚本记录至指定路径；The over-script script recording unit is configured to record the current script to the specified path if the current system performance indicator parameter exceeds the preset indicator warning value;

执行计划获取单元，用于获取当前脚本的执行计划；An execution plan acquisition unit for obtaining an execution plan of the current script;

索引自动添加单元，用于若当前脚本的执行计划中缺少索引，自动添加索引至当前脚本；The index automatically adds a unit for automatically adding an index to the current script if an index is missing from the execution plan of the current script;

脚本修复单元，用于若当前脚本的索引失效，通过强制索引进行脚本修复，得到修复后脚本。The script repair unit is configured to fix the script by forcing the index if the index of the current script is invalid, and obtain the repaired script.
根据权利要求7所述的SQL自动优化装置，其特征在于，所述当前***性能指标参数包括：吞吐量、SQL脚本执行性能参数、服务器连接情况参数、缓冲池使用情况参数；The SQL automatic optimization device according to claim 7, wherein the current system performance indicator parameters include: throughput, SQL script execution performance parameters, server connection status parameters, and buffer pool usage parameters;

所述指标参数获取单元，包括：The indicator parameter obtaining unit includes:

第一参数获取单元，用于通过内部计数器进行已执行语句的计数，得到吞吐量；a first parameter obtaining unit, configured to perform counting of the executed statement by using an internal counter, to obtain a throughput;

第二参数获取单元，用于通过统计出现错误的SQL语句数量，或统计超过可配置的长查询限制的查询数量，得到SQL脚本执行性能参数；The second parameter obtaining unit is configured to obtain a SQL script execution performance parameter by counting the number of SQL statements that have errors, or counting the number of queries exceeding the configurable long query limit;

第三参数获取单元，用于通过统计当前开放的连接数量、当前运行的连接数量、由服务器错误导致的失败连接数、尝试与服务器进行连接结果失败的次数、或由最大连接数限制导致的失败连接数中任意一个，得到服务器连接情况参数；The third parameter obtaining unit is configured to count the number of currently open connections, the number of currently running connections, the number of failed connections caused by server errors, the number of failed attempts to connect to the server, or the failure caused by the maximum number of connections. Any one of the number of connections, get the server connection status parameters;

第四参数获取单元，用于通过统计缓冲池中的总页数、缓冲池中已使用页数所占的比率、缓冲池发送的请求数、或缓冲池无法满足的请求数中任意一个，得到缓冲池使用情况参数。The fourth parameter obtaining unit is configured to obtain, by using, the statistics, the total number of pages in the buffer pool, the ratio of the number of used pages in the buffer pool, the number of requests sent by the buffer pool, or the number of requests that cannot be satisfied by the buffer pool. Buffer pool usage parameters.
根据权利要求8所述的SQL自动优化装置，其特征在于，所述若当前***性能指标参数超出预设的指标预警值中，若在吞吐量、SQL脚本执行性能参数、服务器连接情况参数、或缓冲池使用情况参数中有至少一个超出对应预设的指标预警值，判定当前***性能指标参数超出预设的指标预警值。The SQL automatic optimization apparatus according to claim 8, wherein if the current system performance indicator parameter exceeds a preset indicator warning value, if the throughput, the SQL script execution performance parameter, the server connection status parameter, or At least one of the buffer pool usage parameters exceeds the preset preset indicator value, and determines that the current system performance indicator parameter exceeds the preset indicator warning value.
根据权利要求7所述的SQL自动优化装置，其特征在于，所述脚本修复单元，包括：The SQL automatic optimization device according to claim 7, wherein the script repairing unit comprises:

优化器选定单元，用于获取所选定的数据库优化器；其中，所述数据库优化器为基于规则的优化器、基于成本的优化器、或选择性的优化器中的一种；An optimizer selection unit for acquiring the selected database optimizer; wherein the database optimizer is one of a rule based optimizer, a cost based optimizer, or a selective optimizer;

重建索引单元，用于通过rebuild指令重建当前脚本的索引；Rebuilding the index unit for rebuilding the index of the current script by the rebuild instruction;

强制索引单元，用于通过hint指令强制使用索引，得到修复后脚本。Force the index unit to force the index to be used by the hint instruction to get the repaired script.
一种计算机设备，包括存储器、处理器及存储在所述存储器上并可在所述处理器上运行的计算机程序，其特征在于，所述处理器执行所述计算机程序时实现以下步骤：A computer apparatus comprising a memory, a processor, and a computer program stored on the memory and operative on the processor, wherein the processor, when executing the computer program, implements the following steps:

当前脚本为执行状态时，根据当前脚本中所设置的***性能指标监控项采集当前***性能指标参数；When the current script is in the execution state, the current system performance indicator parameters are collected according to the system performance indicator monitoring items set in the current script;

若当前***性能指标参数超出预设的指标预警值，将当前脚本记录至指定路径；If the current system performance indicator parameter exceeds the preset indicator warning value, the current script is recorded to the specified path;

获取当前脚本的执行计划；Get the execution plan of the current script;

若当前脚本的执行计划中缺少索引，自动添加索引至当前脚本；If the index is missing from the execution plan of the current script, the index is automatically added to the current script;

若当前脚本的索引失效，通过强制索引进行脚本修复，得到修复后脚本。If the index of the current script is invalid, the script is repaired by forcing the index, and the repaired script is obtained.
根据权利要求11所述的计算机设备，其特征在于，所述当前***性能指标参数包括：吞吐量、SQL脚本执行性能参数、服务器连接情况参数、缓冲池使用情况参数；The computer device according to claim 11, wherein the current system performance indicator parameters include: throughput, SQL script execution performance parameters, server connection status parameters, and buffer pool usage parameters;

所述根据当前脚本中所设置的***性能指标监控项采集当前***性能指标参数，包括：Collecting current system performance indicator parameters according to the system performance indicator monitoring item set in the current script, including:

通过内部计数器进行已执行语句的计数，得到吞吐量；The execution of the executed statement is counted by the internal counter to obtain the throughput;

通过统计出现错误的SQL语句数量，或统计超过可配置的长查询限制的查询数量，得到SQL脚本执行性能参数；Get SQL script execution performance parameters by counting the number of SQL statements that have errors, or counting the number of queries that exceed the configurable long query limit.

通过统计当前开放的连接数量、当前运行的连接数量、由服务器错误导致的失败连接数、尝试与服务器进行连接结果失败的次数、或由最大连接数限制导致的失败连接数中任意一个，得到服务器连接情况参数；Get the server by counting the number of currently open connections, the number of currently running connections, the number of failed connections caused by server errors, the number of failed attempts to connect to the server, or the number of failed connections caused by the maximum number of connections. Connection condition parameter;

通过统计缓冲池中的总页数、缓冲池中已使用页数所占的比率、缓冲池发送的请求数、或缓冲池无法满足的请求数中任意一个，得到缓冲池使用情况参数。The buffer pool usage parameter is obtained by counting the total number of pages in the buffer pool, the ratio of the number of used pages in the buffer pool, the number of requests sent by the buffer pool, or the number of requests that the buffer pool cannot satisfy.
根据权利要求12所述的计算机设备，其特征在于，所述若当前***性能指标参数超出预设的指标预警值中，若在吞吐量、SQL脚本执行性能参数、服务器连接情况参数、或缓冲池使用情况参数中有至少一个超出对应预设的指标预警值，判定当前***性能指标参数超出预设的指标预警值。The computer device according to claim 12, wherein if the current system performance indicator parameter exceeds a preset indicator warning value, if the throughput, the SQL script execution performance parameter, the server connection status parameter, or the buffer pool At least one of the usage parameters exceeds the preset preset warning value, and determines that the current system performance indicator parameter exceeds the preset indicator warning value.
根据权利要求11所述的计算机设备，其特征在于，所述自动添加索引至当前脚本包括，通过fn_Index_CreateIndexName函数对当前脚本自动添加索引。The computer device according to claim 11, wherein said automatically adding an index to the current script comprises automatically adding an index to the current script by the fn_Index_CreateIndexName function.
根据权利要求11所述的计算机设备，其特征在于，所述通过强制索引进行脚本修复，得到修复后脚本，包括：The computer device according to claim 11, wherein the script is repaired by a forced index, and the repaired script is obtained, including:

获取所选定的数据库优化器；其中，所述数据库优化器为基于规则的优化器、基于成本的优化器、或选择性的优化器中的一种；Obtaining the selected database optimizer; wherein the database optimizer is one of a rule based optimizer, a cost based optimizer, or a selective optimizer;

通过rebuild指令重建当前脚本的索引；Rebuild the index of the current script through the rebuild command;

通过hint指令强制使用索引，得到修复后脚本。The index is used by the hint instruction to get the post-repair script.
一种存储介质，其特征在于，所述存储介质存储有计算机程序，所述计算机程序包括程序指令，所述程序指令当被处理器执行时使所述处理器执行以下操作：A storage medium, characterized in that the storage medium stores a computer program, the computer program comprising program instructions that, when executed by a processor, cause the processor to perform the following operations:

当前脚本为执行状态时，根据当前脚本中所设置的***性能指标监控项采集当前***性能指标参数；When the current script is in the execution state, the current system performance indicator parameters are collected according to the system performance indicator monitoring items set in the current script;

若当前***性能指标参数超出预设的指标预警值，将当前脚本记录至指定路径；If the current system performance indicator parameter exceeds the preset indicator warning value, the current script is recorded to the specified path;

获取当前脚本的执行计划；Get the execution plan of the current script;

若当前脚本的执行计划中缺少索引，自动添加索引至当前脚本；If the index is missing from the execution plan of the current script, the index is automatically added to the current script;

若当前脚本的索引失效，通过强制索引进行脚本修复，得到修复后脚本。If the index of the current script is invalid, the script is repaired by forcing the index, and the repaired script is obtained.
根据权利要求11所述的存储介质，其特征在于，所述当前***性能指标参数包括：吞吐量、SQL脚本执行性能参数、服务器连接情况参数、缓冲池使用情况参数；The storage medium according to claim 11, wherein the current system performance indicator parameters include: throughput, SQL script execution performance parameters, server connection status parameters, and buffer pool usage parameters;

所述根据当前脚本中所设置的***性能指标监控项采集当前***性能指标参数，包括：Collecting current system performance indicator parameters according to the system performance indicator monitoring item set in the current script, including:

通过内部计数器进行已执行语句的计数，得到吞吐量；The execution of the executed statement is counted by the internal counter to obtain the throughput;

通过统计出现错误的SQL语句数量，或统计超过可配置的长查询限制的查询数量，得到SQL脚本执行性能参数；Get SQL script execution performance parameters by counting the number of SQL statements that have errors, or counting the number of queries that exceed the configurable long query limit.

通过统计当前开放的连接数量、当前运行的连接数量、由服务器错误导致的失败连接数、尝试与服务器进行连接结果失败的次数、或由最大连接数限制导致的失败连接数中任意一个，得到服务器连接情况参数；Get the server by counting the number of currently open connections, the number of currently running connections, the number of failed connections caused by server errors, the number of failed attempts to connect to the server, or the number of failed connections caused by the maximum number of connections. Connection condition parameter;

通过统计缓冲池中的总页数、缓冲池中已使用页数所占的比率、缓冲池发送的请求数、或缓冲池无法满足的请求数中任意一个，得到缓冲池使用情况参数。The buffer pool usage parameter is obtained by counting the total number of pages in the buffer pool, the ratio of the number of used pages in the buffer pool, the number of requests sent by the buffer pool, or the number of requests that the buffer pool cannot satisfy.
根据权利要求17所述的存储介质，其特征在于，所述若当前***性能指标参数超出预设的指标预警值中，若在吞吐量、SQL脚本执行性能参数、服务器连接情况参数、或缓冲池使用情况参数中有至少一个超出对应预设的指标预警值，判定当前***性能指标参数超出预设的指标预警值。The storage medium according to claim 17, wherein if the current system performance indicator parameter exceeds a preset indicator warning value, if the throughput, the SQL script execution performance parameter, the server connection status parameter, or the buffer pool At least one of the usage parameters exceeds the preset preset warning value, and determines that the current system performance indicator parameter exceeds the preset indicator warning value.
根据权利要求16所述的存储介质，其特征在于，所述自动添加索引至当前脚本包括，通过fn_Index_CreateIndexName函数对当前脚本自动添加索引。The storage medium according to claim 16, wherein said automatically adding an index to the current script comprises automatically adding an index to the current script by the fn_Index_CreateIndexName function.
根据权利要求16所述的存储介质，其特征在于，所述通过强制索引进行脚本修复，得到修复后脚本，包括：The storage medium according to claim 16, wherein the script is repaired by a forced index, and the repaired script is obtained, including:

获取所选定的数据库优化器；其中，所述数据库优化器为基于规则的优化器、基于成本的优化器、或选择性的优化器中的一种；Obtaining the selected database optimizer; wherein the database optimizer is one of a rule based optimizer, a cost based optimizer, or a selective optimizer;

通过rebuild指令重建当前脚本的索引；Rebuild the index of the current script through the rebuild command;

通过hint指令强制使用索引，得到修复后脚本。The index is used by the hint instruction to get the post-repair script.