CN113515521A

CN113515521A - Index optimization method and device based on checking SQL operation

Info

Publication number: CN113515521A
Application number: CN202110453195.5A
Authority: CN
Inventors: 陈志红; 朱跃林; 邹逸新; 梁升荣
Original assignee: Industrial and Commercial Bank of China Ltd ICBC
Current assignee: Industrial and Commercial Bank of China Ltd ICBC
Priority date: 2021-04-26
Filing date: 2021-04-26
Publication date: 2021-10-19

Abstract

The invention can be used in the technical field of big data, and provides an index optimization method and device based on checking SQL operation, wherein the index optimization method based on checking SQL operation comprises the following steps: dynamically scanning the SQL sentences which are already run in the research and development test environment to determine the SQL sentences the running time of which exceeds the preset time; statically scanning SQL sentences in the development environment to determine SQL sentences with risks; optimizing the SQL sentences the running time of which exceeds the preset time and the SQL sentences with risks. The index optimization method and device based on SQL checking operation provided by the invention can simply scan whether the SQL script in the code has a missed index without large data test quantity.

Description

Index optimization method and device based on SQL (structured query language) inspection operation

Technical Field

The invention belongs to the technical field of big data, and particularly relates to an index optimization method and device based on SQL checking operation.

Background

As is well known, a medium for storing data, a database, is one of the core components of IT technology systems, and MySQL is one of them. MySQL is a relatively versatile relational database management system consisting of one or more tables. Whereas in MySQL, data indexing is important. The MySQL index is established for the index which is important for the efficient operation of MySQL, and the retrieval speed of MySQL can be greatly improved. If the data volume is too large and the index is not matched, slow query is caused, the database connection pool is full, and connection cannot be released. The entire application service times out. The related business may need to be adjusted subsequently, such as account hanging, the amount of the client and the like. Indexing is data that is ordered to make mySQL query data faster. There are many reasons for slow database query, such as fig. 1, no index or no hit index, insufficient memory, lock or deadlock, too large query data amount, etc., which are all reasons for slow query, where the index is not used is the most common reason.

The existing slow SQL tool scans objects mainly for content which is already operated in a research and development test environment, and when the operated SQL is detected not to hit an index or the used time exceeds a set threshold value, relevant information is recorded and sent to a corresponding responsible person. However, the statement does not necessarily run to the development environment, or the SQL is run to the test environment temporarily without branches, or the SQL is not exposed due to a small amount of test environment data, and a crucial factor is slow to locate. There may have been a range of impacts when the statement caused the problem to be exposed due to a miss index or other problem. The slow SQL is positioned mainly in two modes, namely, the slow SQL is acquired through a slow log, historical information is acquired, and query execution information of slow SQL statements is executed in real time.

Disclosure of Invention

The invention belongs to the technical field of big data, and provides an index optimization method and device based on SQL checking operation, which can simply scan whether an SQL script in a code has a missed index without big data test quantity. And scanning SQL sentences in the codes, automatically translating the SQL sentences into standard SQL sentences, matching fields by the tool according to table information, scanning miss indexes OR using specific keywords such as OR, union and the like, finally summarizing and outputting the suspicious sentences and the positions of the suspicious sentences.

In order to solve the technical problems, the invention provides the following technical scheme:

in a first aspect, the present invention provides an index optimization method based on checking SQL operations, including:

dynamically scanning the SQL sentences which are already run in the research and development test environment to determine the SQL sentences the running time of which exceeds the preset time;

statically scanning SQL sentences in the development environment to determine SQL sentences with risks;

optimizing the SQL sentences the running time of which exceeds the preset time and the SQL sentences with risks.

In an embodiment, the dynamically scanning the executed SQL statements in the development test environment to determine the SQL statements whose execution time exceeds the preset time includes:

and monitoring the log file to determine the SQL statements with the running time exceeding the preset time.

In an embodiment, the dynamically scanning the executed SQL statements in the development test environment to determine the SQL statements whose execution time exceeds the preset time further includes:

judging whether a full table scanning operation exists or not and whether a temporary table creating operation exists or not in the research and development test environment;

in a production environment, monitoring SQL statements in real time to record the operation times of the SQL statements, the operation time of the SQL statements and the proportion of CPU in the operation of the SQL statements.

In one embodiment, the statically scanning SQL statements in the development environment to determine SQL statements at risk includes:

scanning the SQL statement for the presence of an operation on an index that may result in turning to a full-table scan, the operation that may result in turning to a full-table scan comprising: computation, function, and automatic or manual type conversion.

In a second aspect, the present invention provides an index optimization apparatus based on checking SQL operations, including:

the dynamic scanning module is used for dynamically scanning the SQL sentences which are already run in the research and development test environment so as to determine the SQL sentences the running time of which exceeds the preset time;

the static scanning module is used for statically scanning the SQL sentences in the development environment to determine the SQL sentences with risks;

and the statement optimization module is used for optimizing the SQL statements with the running time exceeding the preset time and the SQL statements with the risks.

In one embodiment, the dynamic scanning module comprises:

and the log monitoring unit is used for monitoring the log file so as to determine the SQL statement with the running time exceeding the preset time.

In one embodiment, the dynamic scanning module further comprises:

the operation judging unit is used for judging whether the full-table scanning operation exists or not and whether the temporary table creating operation exists or not in the research and development test environment;

and the statement monitoring unit is used for monitoring the SQL statement in real time in a production environment so as to record the operation times of the SQL statement, the operation time consumption of the SQL statement and the CPU ratio of the SQL statement in operation.

In one embodiment, the static scan module is specifically configured to scan whether an operation that can cause a transition to a full-table scan exists on an index in the SQL statement, and the operation that can cause a transition to a full-table scan includes: computation, function, and automatic or manual type conversion.

In a third aspect, the present invention provides an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the steps of the index optimization method based on checking SQL operations when executing the program.

In a fourth aspect, the present invention provides a computer readable storage medium having stored thereon a computer program which, when executed by a processor, performs the steps of a check SQL operation-based index optimization method.

As can be seen from the above description, the index optimization method and apparatus based on checking SQL operations provided in the embodiments of the present invention first perform dynamic scanning on the executed SQL statements in the development test environment to determine the SQL statements whose execution time exceeds the preset time; then, static scanning is carried out on the SQL sentences in the development environment to determine the SQL sentences with risks; and finally, optimizing the SQL sentences with the running time exceeding the preset time and the SQL sentences with risks. The invention optimizes the method for scanning slow SQL from two aspects of static scanning and dynamic scanning. The SQL statement with risk is obtained by comparing the threshold value with the execution time and by self-defining rules and static and dynamic scanning.

Drawings

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

FIG. 1 is a diagram illustrating factors influencing a database query in an embodiment of the present invention;

FIG. 2 is a flowchart illustrating an index optimization method based on checking SQL operations according to an embodiment of the present invention;

FIG. 3 is a first flowchart illustrating a step 100 in an index optimization method based on checking SQL operations according to an embodiment of the present invention;

FIG. 4 is a second flowchart illustrating step 100 of the index optimization method based on checking SQL operations according to the embodiment of the present invention;

FIG. 5 is a flowchart illustrating a step 200 of an index optimization method based on checking SQL operations according to an embodiment of the present invention;

FIG. 6 is a flowchart illustrating a method for optimizing an index based on checking SQL operations in an embodiment of the present invention;

FIG. 7 is a diagram illustrating employee record intent in an exemplary embodiment of the present invention;

FIG. 8 is a diagram illustrating a combined index full table scan of test case tables in an exemplary embodiment of the present invention;

FIG. 9 is a diagram illustrating the failure of a test case table string without a single quotation mark in the embodiment of the present invention;

FIG. 10 is a first block diagram of the static check code SQL in accordance with the embodiment of the invention;

FIG. 11 is a block diagram of the static check code SQL in accordance with the embodiment of the present invention;

FIG. 12 is a schematic structural diagram of an index optimization apparatus based on checking SQL operations in an embodiment of the present invention;

FIG. 13 is a first block diagram illustrating the structure of the dynamic scan module 10 according to an embodiment of the present invention;

FIG. 14 is a block diagram of the dynamic scan module 10 according to an embodiment of the present invention;

fig. 15 is a schematic structural diagram of an electronic device in an embodiment of the invention.

Detailed Description

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

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

It should be noted that the terms "comprises" and "comprising," and any variations thereof, in the description and claims of this application and the above-described drawings, are intended to cover a non-exclusive inclusion, such that a process, method, system, article, or apparatus that comprises a list of steps or elements is not necessarily limited to those steps or elements expressly listed, but may include other steps or elements not expressly listed or inherent to such process, method, article, or apparatus.

It should be noted that the embodiments and features of the embodiments in the present application may be combined with each other without conflict. The present application will be described in detail below with reference to the embodiments with reference to the attached drawings.

The embodiment of the present invention provides a specific implementation of an index optimization method based on checking SQL operations, and referring to fig. 2, the method specifically includes the following contents:

step 100: and dynamically scanning the SQL sentences which are already run in the research and development test environment to determine the SQL sentences the running time of which exceeds the preset time.

Many performance problems arise, mostly with respect to indexing. Whether adding an index or adjusting an SQL construct, the purpose is to make it hit in the index. In this case, problems are often exposed to a series of problems.

It can be understood that slow SQL detection is mainly performed in two ways, static scanning and dynamic scanning: dynamic scanning: the scan object is content that has been run in the development test environment. Static scanning: the incremental code is scanned, problems found by scanning need to be developed and confirmed, and the edition lifting can be prohibited seriously.

Step 200: static scanning is performed on the SQL statements in the development environment to determine the SQL statements with the risks.

Specifically, in a development environment, static scanning is realized, a code package is scanned by using a tool, static performance analysis is performed on SQL scanning based on a user-defined rule, and risk prompt is performed on risky sentences or writing methods, so that the effect of finding hidden dangers in time is achieved.

Step 300: optimizing the SQL sentences the running time of which exceeds the preset time and the SQL sentences with risks.

It will be appreciated that slow queries are generally due to either overly complex SQL logic or index misses. The strategy of searching slow SQL is started from the two directions, the SQL structure is added and adjusted to enable the index to take effect, and the core of the strategy is to hit the index so as to improve the query efficiency.

As can be seen from the above description, in the index optimization method based on the SQL operation checking provided in the embodiment of the present invention, at first, the executed SQL statements in the research and development test environment are dynamically scanned to determine the SQL statements whose execution time exceeds the preset time; then, static scanning is carried out on the SQL sentences in the development environment to determine the SQL sentences with risks; and finally, optimizing the SQL sentences with the running time exceeding the preset time and the SQL sentences with risks. The invention combines the test, the data simulation and the like, and can simply scan whether the SQL script in the code has the miss index without large data test quantity. And scanning SQL sentences in the codes, automatically translating the SQL sentences into standard SQL sentences, matching fields by the tool according to table information, scanning miss indexes OR using specific keywords such as OR, union and the like, finally summarizing and outputting the suspicious sentences and the positions of the suspicious sentences.

In one embodiment, referring to fig. 3, step 100 comprises:

step 101: and monitoring the log file to determine the SQL statements with the running time exceeding the preset time.

Specifically, detecting a MySQL log file, monitoring the log file, fishing out slow SQL statements exceeding a set threshold, checking how the MySQL uses an index to process a select statement and a connection table by combining methods such as Show processlist, explain # { SQL statement }, Show Profile and the like, judging whether full table scanning exists, time consumption of SQL exists, whether a temporary table is created or not, and summarizing and uniformly displaying the information and returning the information. And before returning, executing the slow SQL sentences scanned out by operation again, and displaying the execution information for comparison. And meanwhile, dynamic scanning is carried out according to the self-defined rule, and risky SQL sentences are scanned. Meanwhile, SQL injection detection is used for judging whether SQL injection risks exist or not, and a risk evaluation module is added to achieve detection and prevention.

In one embodiment, referring to fig. 4, step 100 further comprises:

step 102: judging whether a full table scanning operation exists or not and whether a temporary table creating operation exists or not in the research and development test environment;

step 103: in a production environment, monitoring SQL statements in real time to record the operation times of the SQL statements, the operation time of the SQL statements and the proportion of CPU in the operation of the SQL statements.

When the step 103 is implemented, the method specifically comprises the following steps: in a production environment, the system dynamically monitors in real time, records relevant information, running times, time consumption, CPU occupation and the like.

In one embodiment, referring to fig. 5, step 200 comprises:

step 201: scanning the SQL statement for the presence of an operation on an index that may result in turning to a full-table scan, the operation that may result in turning to a full-table scan comprising: computation, function, and automatic or manual type conversion.

Specifically, scanning whether an operation (calculation, function, automatic or manual type conversion) is performed on the index in the SQL statement may cause the index to fail and turn to a method of full-table scanning, and the like.

Specifically, scanning whether an operation exists on an index in the SQL statement may result in turning to a full-table scan, the operation that may result in turning to a full-table scan including: computation, function, and automatic or manual type conversion.

To further illustrate the present solution, the present invention further provides a specific application example of an index optimization method based on checking SQL operations, referring to fig. 6, where the method specifically includes the following steps:

and for slow SQL statements discovered in production, the SQL management platform sends a notice according to a pre-set responsible person for subsequent optimization processing. But also results in the extension of the modification process, so that the problems are scanned out as much as possible during static detection and dynamic detection, and the optimal effect can be achieved.

Referring to fig. 7, taking "employee records table" as an example, the table has only 8 fields, a unique index, a common index, and a joint index, and 6 pieces of data are added.

After the joint index is created, the joint index must be used strictly according to the principle of the leftmost prefix, otherwise, the index cannot be used, as shown in fig. 8, an SQL statement [ extract SELECT FROM work word _ age ═ 2AND position ═ dev'; ALL is performed, type is ALL, not conforming to the leftmost prefix rule, no hit.

In addition to the left-most prefix rule, there are many factors that can cause a miss index, as shown in fig. 9, the same statement [ EXPLAIN SELECT FROM test. And [ EXPLAIN SELECT FROM term FROM name 1'; string type without a single quotation mark can cause index invalidation.

There are many reasons for the low efficiency of SQL operation, but most of them are because there is no hit index, and optimizing and detecting the hit index of SQL statement can avoid many problems.

S1: and monitoring the log file to determine the SQL statements with the running time exceeding the preset time.

S2: judging whether a full table scanning operation exists or not and whether a temporary table creating operation exists or not in a research and development test environment;

the method comprises the following steps of dynamically scanning a development environment, scanning SQL statements which run in a research test environment based on a custom rule, and formulating the scanning rule, wherein the scanning rule mainly comprises the following steps:

the method is a method for formulating a detection scanning method, scanning whether operations (calculation, function, automatic or manual type conversion) are performed on the indexes in the SQL statement or not, and the like, which may cause index failure and turn to full-table scanning.

And secondly, making and detecting related contents of the SQL index, such as full-table scanning governance and index matching governance. Whether a matching index exists or not, whether special characters are translated and filtered or not, whether words such as is null or is not null or not, whether a best left prefix rule is met or not (the query starts from the leftmost front row of the index and does not skip columns in the index, the middle skipped value is used, the later index fails), whether a query statement using a multi-column index or a query statement using a LIKE keyword for query is available, the first character of a matching character string is "%", the situations of bilateral format inconsistency in WHERE the characters are compared, the full-table scan caused by the fact that statements such as SELECT/UPDATE do not have WHERE conditions, and the LIKE are scanned.

And thirdly, setting the execution time threshold of the SQL statement, if the threshold is set to be 3 seconds, if the average execution time exceeds the threshold, the execution times is more than 10, and the conditions of full-table scanning and the like are met, recording the average execution time of the SQL statement and returning the statement.

S3: in a production environment, monitoring the SQL sentences in real time to record the operation times of the SQL sentences, the time consumed by the operation of the SQL sentences and the proportion of the SQL sentences in a CPU during the operation.

S4: scanning for the presence of an operation on an index in an SQL statement that may result in a branch to full-table scan includes: computation, function, and automatic or manual type conversion.

As shown in FIG. 10, for a project with SQL statements, such as Hql file written by a large data platform, the code can be directly scanned, and risky data can be scanned through set rules. But for java projects such as distributed projects, there is no explicit SQL statement in the program, for which most tools tend to skip the static scanning step. But static scanning can be achieved by incorporating test engineering, as in fig. 11. And constructing corresponding test data by combining mock according to the test cases for calling the SQL codes, and acquiring corresponding SQL statements by running the test cases. And storing the data into a data set, statically scanning the data set to scan out SQL statements with certain risks, and detecting whether the statements need to be optimized.

As can be seen from the above description, in the index optimization method based on the SQL operation checking provided in the embodiment of the present invention, at first, the executed SQL statements in the research and development test environment are dynamically scanned to determine the SQL statements whose execution time exceeds the preset time; then, static scanning is carried out on the SQL sentences in the development environment to determine the SQL sentences with risks; and finally, optimizing the SQL sentences with the running time exceeding the preset time and the SQL sentences with risks. The statement may miss an index, may not be in a standard field format, and may need to be optimized because the query time exceeds a threshold, which are all contents to be concerned, and slow SQL is caused.

Based on the same inventive concept, the embodiment of the present application further provides an index optimization apparatus based on checking SQL operations, which can be used to implement the methods described in the above embodiments, such as the following embodiments. Because the principle of solving the problems of the index optimization device based on the check SQL operation is similar to that of the index optimization method based on the check SQL operation, the implementation of the index optimization device based on the check SQL operation can refer to the implementation of the index optimization method based on the check SQL operation, and repeated details are omitted. As used hereinafter, the term "unit" or "module" may be a combination of software and/or hardware that implements a predetermined function. While the system described in the embodiments below is preferably implemented in software, implementations in hardware, or a combination of software and hardware are also possible and contemplated.

The embodiment of the present invention provides a specific implementation manner of an index optimization device based on check SQL operations, which is capable of implementing an index optimization method based on check SQL operations, and referring to fig. 12, the index optimization device based on check SQL operations specifically includes the following contents:

the dynamic scanning module 10 is configured to dynamically scan an executed SQL statement in the research and test environment to determine an SQL statement whose execution time exceeds a preset time;

the static scanning module 20 is used for statically scanning the SQL sentences in the development environment to determine the SQL sentences with risks;

and the statement optimization module 30 is configured to optimize the SQL statements with the running time exceeding the preset time and the SQL statements with risks.

In one embodiment, referring to fig. 13, the dynamic scan module 10 includes:

and the log monitoring unit 101 is configured to monitor a log file to determine the SQL statement with the running time exceeding the preset time.

In one embodiment, referring to fig. 14, the dynamic scan module 10 further includes:

an operation judging unit 102, configured to judge whether a full table scan operation exists or a temporary table creation operation exists in the research and development test environment;

the statement monitoring unit 103 is configured to monitor the SQL statement in real time in a production environment, so as to record the number of times the SQL statement runs, the time consumed by the SQL statement when running, and the CPU ratio of the SQL statement when running.

In one embodiment, the static scan module 20 is specifically configured to scan whether an operation that can cause a transition to a full-table scan exists on an index in the SQL statement, where the operation that can cause a transition to a full-table scan includes: computation, function, and automatic or manual type conversion.

As can be seen from the above description, the index optimization apparatus based on the SQL operation according to the embodiment of the present invention first responds to the manufacturer request of the user, and parses the manufacturer request to determine the field name, the field type, the field comment, and the field tag; then, triggering a corresponding python function according to the field name, the field type, the field annotation and the field label to generate data to be manufactured; and finally, inserting a corresponding SQL statement and a value into the data to be manufactured according to the manufacture number request. The method solves the problem that the number making method in the prior art is difficult to meet the requirements in special scenes by a mode of self-defining Python function and template configuration. Specifically, the invention has the following beneficial effects:

referring now to FIG. 15, shown is a schematic diagram of an electronic device 600 suitable for use in implementing embodiments of the present application.

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

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

In particular, according to an embodiment of the present invention, the processes described above with reference to the flowcharts may be implemented as computer software programs. For example, an embodiment of the present invention includes a computer-readable storage medium, on which a computer program is stored, the computer program, when being executed by a processor, implementing the steps of the above-mentioned method for determining a distance to a person in a data-based room scenario, the steps including:

step 100: dynamically scanning the SQL sentences which are already run in the research and development test environment to determine the SQL sentences the running time of which exceeds the preset time;

step 200: statically scanning SQL sentences in the development environment to determine SQL sentences with risks;

In such an embodiment, the computer program may be downloaded and installed from a network through the communication section 609, and/or installed from the removable medium 611.

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

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

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

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

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

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

Claims

1. an index optimization method based on checking SQL operation, is characterized in that, comprises:

Dynamically scan the running SQL statements in the R&D test environment to determine the SQL statements whose running time exceeds the preset time;

Perform a static scan of SQL statements in the development environment to identify risky SQL statements;

The SQL statements whose running time exceeds the preset time and the SQL statements with risks are optimized.

2. the index optimization method based on checking SQL operation according to claim 1, is characterized in that, described to the SQL statement that has run in the research and development test environment is carried out dynamic scanning, to determine the SQL statement that running time exceeds preset time ,include:

The log files are monitored to determine the SQL statements whose execution time exceeds a preset time.

3. the index optimization method based on checking SQL operation according to claim 1, is characterized in that, described to the SQL statement that has run in the research and development test environment is carried out dynamic scanning, to determine the SQL statement that running time exceeds preset time ,Also includes:

Determine whether there is a full table scan operation or a temporary table creation operation in the R&D test environment;

In the production environment, the SQL statement is monitored in real time to record the number of times the SQL statement is run, the time spent running the SQL statement, and the CPU ratio when the SQL statement is running.

4. the index optimization method based on checking SQL operation according to claim 1, is characterized in that, the described SQL statement in the development environment is statically scanned to determine the SQL statement with risk, comprising:

Scanning whether there is an operation on an index column in the SQL statement that can lead to a full table scan, the operations that can lead to a full table scan include: calculation, function, and automatic or manual type conversion.

5. an index optimization device based on checking SQL operation, is characterized in that, comprises:

The dynamic scanning module is used to dynamically scan the already running SQL statements in the R&D test environment to determine the SQL statements whose running time exceeds the preset time;

A static scanning module, which is used to perform static scanning on SQL statements in the development environment to determine risky SQL statements;

A statement optimization module, configured to optimize the SQL statement whose running time exceeds the preset time and the SQL statement with risk.

6. The index optimization device based on checking SQL operations according to claim 5, wherein the dynamic scanning module comprises:

The log monitoring unit is configured to monitor the log file to determine the SQL statement whose running time exceeds the preset time.

7. The index optimization device based on checking SQL operations according to claim 5, wherein the dynamic scanning module further comprises:

an operation judging unit for judging whether there is a full table scan operation and whether there is an operation of creating a temporary table in the R&D test environment;

The statement monitoring unit is used to monitor the SQL statement in real time in the production environment, so as to record the number of times the SQL statement is run, the running time of the SQL statement, and the CPU ratio when the SQL statement is running.

8. The index optimization device based on checking SQL operations according to claim 5, wherein the static scanning module is specifically used to scan whether there is an operation that can lead to a full table scan on an index column in the SQL statement, The operations that can result in a transition to a full table scan include calculations, functions, and automatic or manual type conversions.

9. An electronic device, comprising a memory, a processor and a computer program stored on the memory and running on the processor, wherein the processor implements any one of claims 1 to 4 when the processor executes the program The steps of the index optimization method based on checking SQL operations.

10. A computer-readable storage medium on which a computer program is stored, characterized in that, when the computer program is executed by a processor, the steps of the index optimization method based on checking SQL operations described in any one of claims 1 to 4 are realized .