CN112988786B

CN112988786B - Database maintenance method and device

Info

Publication number: CN112988786B
Application number: CN202110514701.7A
Authority: CN
Inventors: 周征; 王若楠; 段坤
Original assignee: Beijing Uxsino Software Co ltd
Current assignee: Beijing Uxsino Software Co ltd
Priority date: 2021-05-12
Filing date: 2021-05-12
Publication date: 2022-04-05
Anticipated expiration: 2041-05-12
Also published as: CN112988786A

Abstract

The present disclosure relates to the field of database technologies, and in particular, to a database maintenance method and apparatus. The execution time of the SQL sentences after the normalization processing is obtained by normalizing the SQL sentences used by the user in the database, the low-efficiency SQL sentences in the SQL sentences after the normalization processing are obtained according to the execution time, and then the rewriting prompt information corresponding to the low-efficiency SQL sentences is pushed to the user, so that the low-efficiency sentences can be found and processed in time, the operation and maintenance efficiency of the database can be improved, and the difficulty of the operation and maintenance of the database can be greatly reduced because technicians do not need to learn the services borne by the database.

Description

Database maintenance method and device

Technical Field

The present disclosure relates to the field of database technologies, and in particular, to a database maintenance method and apparatus.

Background

Structured Query Language (SQL), a special purpose programming Language, is a database Query and programming Language for accessing data and querying, updating, and managing database systems. The existing database usually needs special operation and maintenance personnel to maintain, a user usually needs to spend a lot of time to monitor the database when positioning the inefficient SQL statement, and the diagnosis and the optimization of the SQL statement need the operation and maintenance personnel with rich experience to spend a lot of time to analyze, which easily causes the waste of labor cost and time cost.

Disclosure of Invention

To solve at least one of the above technical problems, the present disclosure provides a database maintenance method and apparatus.

In one aspect, an embodiment of the present disclosure provides a database maintenance method, where for any database, the method includes:

normalizing SQL sentences used by users in the database;

acquiring the execution time of the SQL statement after the normalization processing;

acquiring an inefficient SQL statement in the normalized SQL statement, wherein the inefficient SQL statement is the SQL statement with the longest execution time or the SQL statement with the execution time being greater than, equal to or not less than a first reference threshold;

and pushing rewriting prompt information corresponding to the low-efficiency SQL statement to a user.

Optionally, the obtaining of the execution time of the normalized SQL statement includes:

acquiring the execution time of the SQL statement after the normalization processing according to the following formula;

T=T _{general assembly} /M

In the formula (I), the compound is shown in the specification,Tto normalize the execution time of the processed SQL statement,T _{general assembly}To normalize the accumulated execution time of the processed SQL statement,Mthe number of times of call is the accumulated number of times of the SQL statement after normalization processing.

Optionally, a rewriting knowledge base is arranged in the database or in a device connected to the database, and a rewriting rule for rewriting the inefficient SQL statement is stored in the rewriting knowledge base;

the pushing of rewriting prompt information corresponding to the inefficient SQL statement to the user includes:

acquiring an inefficient type of the inefficient SQL statement;

and pushing the rewriting prompt information to a user according to the low-efficiency type of the low-efficiency SQL statement, wherein the rewriting prompt information comprises a rewriting rule corresponding to the low-efficiency type of the low-efficiency SQL statement.

Optionally, the method further comprises:

after receiving a rewriting instruction of a user, judging whether the rewriting instruction is matched with the rewriting prompt information;

when the rewriting instruction is matched with the rewriting prompt information, rewriting the low-efficiency SQL statement according to the rewriting instruction or the rewriting rule included in the rewriting prompt information;

and when the rewriting instruction is not matched with the rewriting prompt information, rewriting the low-efficiency SQL statement according to the rewriting instruction, and storing the rewriting instruction as a new rewriting rule to the rewriting knowledge base.

Optionally, the method further comprises:

judging whether the normalized SQL statement inquired column has an index or not;

and when the column has no index, establishing an index for the column, or pushing index establishment prompt information to a user.

Optionally, the method comprises:

when the column has indexes, calculating the use frequency of each index;

and when the use frequency of the index is less than, equal to or not greater than a second reference threshold value, deleting the index or pushing index deletion prompt information to a user.

Optionally, the method comprises:

when the column has indexes, calculating the use frequency of each index;

when the use frequency of the index is less than, equal to or not greater than a second reference threshold, judging whether the index is a necessary index, wherein the necessary index refers to an index established by a user recently or an index used by the user recently;

and when the index is not a necessary index, deleting the index or pushing index deletion prompt information to a user.

Optionally, the determining whether the index is a necessary index includes:

acquiring the establishment time and/or the latest use time of the index;

when the difference value between the establishment time of the index and the current time is less than, equal to or not greater than a third reference threshold value, determining the index as the index established by the user most recently;

and when the difference value between the latest use time of the index and the current time is less than, equal to or not greater than a fourth reference threshold value, determining the index as the index which is most recently used by the user.

Optionally, the method further comprises:

acquiring query records and modified data of a table corresponding to the normalized SQL statement;

and if the table has the query record and no modified data, creating a materialized view for the table, or pushing prompt information for creating the materialized view for the table to a user.

Optionally, the method further comprises:

if the table has the query record and no modified data, judging whether the table is a new table or a abandoned table;

and when the table is not a new table or a abandoned table, creating a materialized view for the table, or pushing prompt information for creating the materialized view for the table to a user.

Optionally, the determining whether the table is a new table includes:

acquiring the creation time of the table;

determining that the table is a new table when a difference between the creation time of the table and the current time is less than, equal to, or not greater than a fifth reference threshold.

Optionally, the determining whether the table is a discard table includes:

obtaining the latest access time of the table;

determining that the table is a discard table when a difference value of the latest access time of the table from the current time is greater than, equal to, or not less than a sixth reference threshold.

In another aspect, an embodiment of the present disclosure provides a database maintenance apparatus, where the apparatus is applied to any database, and the apparatus includes:

the normalization processing module is configured to normalize SQL sentences used by users in the database;

the first acquisition module is configured to acquire the execution time of the SQL statement after the normalization processing;

the second acquisition module is configured to acquire an inefficient SQL statement in the normalized SQL statement, wherein the inefficient SQL statement is the SQL statement with the longest execution time or the SQL statement with the execution time greater than, equal to or not less than a first reference threshold;

and the first information pushing module is configured to push rewriting prompt information corresponding to the low-efficiency SQL statement to a user.

In a further aspect, the present fair-opening embodiment provides a database optimization apparatus, which is applied to any database, and includes a processor and a memory, where the memory stores computer program instructions adapted to be executed by the processor, and the computer program instructions are executed by the processor to perform the database maintenance method as described above.

Yet another aspect the present fair-opening embodiment provides a computer storage medium for application to any database, the computer storage medium storing computer-executable instructions that, when executed by a processor, implement a database maintenance method as described above.

The present fair open embodiment also provides a database comprising any of the apparatus as described above, and/or a computer storage medium as described above.

The present fair-open embodiment also provides an electronic device comprising any of the apparatus as described above, a computer storage medium as described above and/or a database as described above.

The beneficial effects brought by the technical scheme provided by the embodiment of the disclosure at least can include:

the execution time of the SQL sentences after the normalization processing is obtained by normalizing the SQL sentences used by the user in the database, the low-efficiency SQL sentences in the SQL sentences after the normalization processing are obtained according to the execution time, and then the rewriting prompt information corresponding to the low-efficiency SQL sentences is pushed to the user, so that the low-efficiency sentences can be found and processed in time, the operation and maintenance efficiency of the database can be improved, and the difficulty of the operation and maintenance of the database can be greatly reduced because technicians do not need to learn the services borne by the database.

Drawings

In order to more clearly illustrate the embodiments or prior art solutions of the present disclosure, the drawings used in the description of the embodiments or prior art will be briefly described below, and it is obvious that the drawings in the following description are included in and constitute a part of this specification, and other drawings can be obtained by those skilled in the art without inventive effort from these drawings. For convenience of description, only portions relevant to the present disclosure are shown in the drawings.

Fig. 1 shows a flowchart of a database maintenance method provided by a first embodiment of the present disclosure;

FIG. 2 is a flow chart of a database maintenance method provided by a second embodiment of the present disclosure;

fig. 3 shows a flowchart of a database maintenance method provided by a third embodiment of the present disclosure;

fig. 4 shows a flowchart of a database maintenance method provided by a fourth embodiment of the present disclosure;

fig. 5 is a schematic diagram of a database maintenance apparatus provided in a fifth embodiment of the present disclosure;

fig. 6 shows a schematic diagram of a database maintenance apparatus provided in a sixth embodiment of the present disclosure;

fig. 7 shows a schematic diagram of a database maintenance apparatus according to a seventh embodiment of the disclosure.

Detailed Description

To make the objects, technical solutions and advantages of the embodiments of the present disclosure more clear, the technical solutions in the embodiments of the present disclosure will be described clearly and completely with reference to the drawings in the embodiments of the present disclosure, and it is obvious that the described embodiments are some embodiments of the present disclosure, not all embodiments, and features in the embodiments and implementations in the present disclosure may be combined with each other without conflict. All other embodiments, which can be derived by a person skilled in the art from the embodiments disclosed herein without making any creative effort, shall fall within the protection scope of the present disclosure.

Before discussing exemplary embodiments in more detail, it should be noted that some exemplary embodiments are described as processes or methods depicted as flowcharts. Although a flowchart may describe the operations or steps as a sequential process, many of the operations can be performed in parallel, concurrently, or simultaneously. In addition, the order of various operations or steps may be rearranged. The process may be terminated when its operations are completed, but may have additional steps not included in the figure. The processes may correspond to methods, functions, procedures, subroutines, and the like. The methods provided by some embodiments of the present disclosure may be executed by a processor, and are all described below by taking the processor as an example of an execution subject. The execution subject may be adjusted according to the actual application, for example, the execution subject may be a server, an electronic device, a computer, or the like. More specifically, one or more steps of the methods provided by the embodiments of the present disclosure may be performed by computer program instructions adapted to be executed by a processor.

In the prior art, a data center or a database usually stores thousands of SQL statements, and those skilled in the art will spend a lot of time in diagnosing the SQL statements. In addition, the database often bears a plurality of complex services, and technicians need to spend a lot of time to be familiar with the services borne by the database before SQL statements used in the database can be optimized, which brings difficulty to the technicians in terms of work. Therefore, a more efficient database maintenance method is urgently needed.

A first embodiment of the present disclosure provides a database maintenance method, where for any database, as shown in fig. 1, the method includes:

s101, normalizing SQL sentences used by users in a database;

s102, acquiring the execution time of the SQL statement after normalization processing;

s103, acquiring an inefficient SQL statement in the normalized SQL statement, wherein the inefficient SQL statement is the SQL statement with the longest execution time or the SQL statement with the execution time being greater than, equal to or not less than a first reference threshold;

and S104, pushing rewriting prompt information corresponding to the low-efficiency SQL statement to the user.

Optionally, obtaining the execution time of the normalized SQL statement includes:

T=T _{general assembly} /M

Optionally, a rewriting knowledge base is arranged in the database or in equipment connected with the database, and rewriting rules for rewriting the inefficient SQL statements are stored in the rewriting knowledge base;

pushing rewriting prompt information corresponding to the inefficient SQL statement to a user, comprising:

acquiring an inefficient type of an inefficient SQL statement;

and pushing rewriting prompt information to the user according to the inefficient type of the inefficient SQL statement, wherein the rewriting prompt information comprises a rewriting rule corresponding to the inefficient type of the inefficient SQL statement.

Optionally, the method further comprises:

after receiving a rewriting instruction of a user, judging whether the rewriting instruction is matched with rewriting prompt information;

and when the rewriting instruction is not matched with the rewriting prompt information, rewriting the low-efficiency SQL statement according to the rewriting instruction, and storing the rewriting instruction as a new rewriting rule in a rewriting knowledge base.

Optionally, the method further comprises:

and when the column does not have the index, establishing the index for the column or pushing index establishment prompt information to a user.

Optionally, the method comprises:

when the column has indexes, calculating the use frequency of each index;

and when the use frequency of the index is less than, equal to or not greater than the second reference threshold, deleting the index or pushing index deletion prompt information to the user.

Optionally, the method comprises:

when the column has indexes, calculating the use frequency of each index;

when the index is not the necessary index, deleting the index or pushing index deletion prompt information to the user.

Optionally, the determining whether the index is a necessary index comprises:

acquiring the establishment time and/or the latest use time of the index;

Optionally, the method further comprises:

when the table is not a new table or a discarded table, a materialized view is created for the table, or prompt information for creating the materialized view for the table is pushed to a user.

Optionally, determining whether the table is a new table includes:

acquiring the creation time of a table;

when the difference between the creation time of the table and the current time is less than, equal to, or not greater than the fifth reference threshold, it is determined that the table is a new table.

Optionally, determining whether the table is a discard table includes:

obtaining the latest access time of the table;

and when the difference value of the latest access time of the table from the current time is greater than, equal to or not less than a sixth reference threshold value, determining that the table is a discard table.

According to the database maintenance method provided by the embodiment, the execution time of the SQL sentences after the normalization processing is obtained through the SQL sentences used by the user in the normalization processing database, the low-efficiency SQL sentences in the SQL sentences after the normalization processing are obtained according to the execution time, and then the rewriting prompt information corresponding to the low-efficiency SQL sentences is pushed to the user, so that the low-efficiency sentences can be found and processed in time, the operation and maintenance efficiency of the database can be improved, and the difficulty of the operation and maintenance of the database can be greatly reduced because technicians do not need to learn the service carried by the database.

The second embodiment of the disclosure provides a database maintenance method, which is applicable to any database. For example, the method is applicable to each database node in any stand-alone database or distributed database cluster system. As shown in fig. 2, the method includes:

s201, for any database, obtaining SQL sentences used by users in the database.

A database is a repository that stores data, which is essentially a file system. To facilitate management of the data stored in the database, the data is typically stored in the database in a particular format. The user can add, modify, delete and query the data in the database, and when the user performs various operations on the database through the SQL statements, the database can usually record the SQL statements used by the user in real time. In step S201, for any database, the SQL statements used by the user in the database, or in other words, the SQL statements used by the user in the database, may be obtained or collected according to the history of the database. The SQL statements used by the user in the database include SQL statements used by the user to perform query operations on data in the database, and may also include SQL statements used by the user to perform addition, modification, and/or deletion operations on data in the database.

S202, normalizing the obtained SQL statement.

In this embodiment, the normalization processing of the SQL statements may be to convert user-defined variables in the same type of SQL statements into specific values, so that the same type of SQL statements have the same syntax tree. For example, for SQL statements A and B:

A：select* from table1 where id =10；

B：select* from table1 where id =9；

after the SQL sentences A and B are normalized, an SQL sentence C with a uniform or same format can be obtained:

C：select* from table1 where id =$1 。

similarly, for SQL statements D and E:

D：select* from table1 where num =10；

E：select* from table1 where num =9；

after the SQL sentences D and E are subjected to normalization processing, an SQL sentence F with a uniform or same format can be obtained:

F：select* from table1 where num =$2。

s203, acquiring the execution time of the SQL statement after the normalization processing.

The history of the database often records the number of calls of the SQL statement and the execution time of each call or execution of the SQL statement. Therefore, before, at the same time of or after the SQL statement is normalized, the number of calls of each SQL statement and the execution time of each call or execution of the SQL statement may be obtained according to the history of the database. Because the normalization processing only modifies the format of the SQL statement, the number of calls and the execution time of the SQL statement before and after the normalization processing are the same for the same SQL statement, respectively.

After obtaining the number of calls and the execution time of the SQL statement, the execution time of the normalized SQL statement may be obtained according to the following formula:

T=T _{general assembly} /M

In the formula (I), the compound is shown in the specification,Tto normalize the execution time of the processed SQL statement,T _{general assembly}To normalize the accumulated execution time of the processed SQL statement,Mthe number of times of call is the accumulated number of times of the SQL statement after normalization processing. It should be noted that, in this embodiment, the accumulated execution time of the normalized SQL statement may be the accumulated execution time that can be converted into the SQL statement with the same format (or the same) after the normalization, and the accumulated number of calls of the normalized SQL statement may be the accumulated number of calls that can be converted into the SQL statement with the same format (or the same) after the normalization. Therefore, obtaining the execution time of the SQL statement after the normalization processing may also be regarded as obtaining the average execution time of the SQL statement with the same format after the normalization processing.

For example, for SQL statements A, B and C, SQL statements A and B can be converted to the same format SQL statement C after normalization. Suppose the number of calls of A is 2 and the execution times of two calls A are 0.1 and 0.2 seconds, respectively, the number of calls of B is 1 and the execution time of call B is 0.3 seconds. At this time, the process of the present invention,T _{general assembly}0.1+0.2+0.3=0.6 seconds,M2+1=3 times, and the execution time of the normalized SQL statement C is 0.2 seconds. And similarly, the execution time of the SQL statement after other normalization processing can be calculated.

And S204, acquiring the inefficient SQL sentences in the SQL sentences after the normalization processing according to the execution time of the SQL sentences after the normalization processing.

In this embodiment, the inefficient SQL statement is the SQL statement with the longest execution time, or the SQL statement with the execution time greater than, equal to, or not less than the first reference threshold. In a possible implementation manner, after the execution time of the normalized SQL statements is calculated, the SQL statements may be arranged in ascending order or descending order of the execution time, so as to screen out the inefficient SQL statements. For example, after the execution times of the normalized SQL statements C and F are obtained, C and F may be sorted by the execution times. It should be noted that the specific value of the first reference threshold may be set by the user before the method provided by the embodiment is executed. For example, the value of the first reference threshold may be a positive number greater than 0, and the unit of the first reference threshold may be a time unit such as seconds or milliseconds.

S205, pushing rewriting prompt information corresponding to the low-efficiency SQL statement to the user.

In one possible implementation, for any database, a rewriting knowledge base may be provided in the database or in an entity or non-entity device connected to the database in a wired or wireless manner, and the rewriting knowledge base may store rewriting rules for rewriting inefficient SQL statements. After obtaining the inefficient SQL statement, the inefficient type of the inefficient SQL statement may be obtained or determined, that is, what reason the execution time of the SQL statement is lengthened is obtained or determined. After the inefficient type of the inefficient SQL statement is obtained, corresponding rewriting prompt information may be pushed to the user according to the inefficient type of the inefficient SQL statement, and the rewriting prompt information may include a rewriting rule corresponding to the inefficient type of the inefficient SQL statement. For the sake of distinction, the rewriting notice information may be referred to as first notice information.

For example, when SELECT is used in an SQL statement, unnecessary results are often returned, which results in a longer execution time of the SQL statement, and the network is easily burdened, thereby reducing system performance. Therefore, after the inefficient SQL statement is obtained, it may be determined whether SELECT is included in the inefficient SQL statement. When the inefficient SQL statement includes SELECT, rewrite prompt information may be pushed to the user, and the rewrite prompt information may include a rewrite suggestion that suggests the user to give a field value that needs to be returned specifically. The rewrite suggestion is a rewrite rule for the inefficient SQL statement when the inefficient SQL statement includes SELECT.

For another example, when the filter condition key of the SQL statement is preceded by "%", the SQL statement will go through the full table query, and the execution time of the SQL statement will be longer. Therefore, after the inefficient SQL statement is obtained, it may be determined whether the filter condition keyword of the inefficient SQL statement is preceded by "%". When the filter condition key word of the inefficient SQL statement is preceded by "%", in order to avoid full-table query and judgment on a NULL value, rewriting prompt information can be pushed to a user, and the rewriting prompt information can comprise rewriting suggestions for suggesting the user to write the filter condition. The rewriting suggestion is the rewriting rule of the low-efficiency SQL statement when the filtering condition key word of the low-efficiency SQL statement is preceded by "%".

It should be noted that the inefficient SQL statement acquired in step S204 is the normalized SQL statement. After the SQL sentences are normalized, the SQL sentences with the same format correspond to the same type of SQL sentences, so the rewriting rules corresponding to the inefficient SQL sentences are applicable to the type of SQL sentences corresponding to the inefficient SQL sentences. For example, if the SQL statement C is an inefficient SQL statement, the rewrite rule corresponding to the SQL statement C applies to the SQL statements a and B. In addition, after the rewriting prompt information corresponding to the inefficient SQL statement is pushed to the user, the database maintenance method provided by the embodiment may be terminated or terminated, and perform the relevant operation according to the instruction of the user. For example, the user may adopt the rewriting suggestion in the rewriting prompt information and issue a rewriting instruction for rewriting the inefficient SQL statement, or the user may adopt no rewriting suggestion in the rewriting prompt information but use another rewriting method to rewrite the inefficient SQL statement, or the user may not modify the inefficient SQL statement. In one possible implementation, the rewriting prompt information may include options of confirming the modification and confirming not to modify. When the user selects the option of confirming the modification, the inefficient SQL statement can be automatically modified in the background directly according to the rewriting prompt information or the rewriting rule in the rewriting prompt information. Of course, the user may also modify the inefficient SQL statement manually.

In a possible implementation manner, after receiving a rewriting instruction of a user for a low-efficiency SQL statement, whether the rewriting instruction matches with the rewriting prompt information pushed to the user, that is, whether the rewriting instruction matches with the rewriting rule in the rewriting prompt information pushed to the user may be determined. If the rewriting instruction is matched with the rewriting prompt information pushed to the user, the low-efficiency SQL statement can be rewritten according to the rewriting instruction of the user or the rewriting rule included in the rewriting prompt information; if the rewriting instruction is not matched with the rewriting prompt information pushed to the user, the low-efficiency SQL statement can be rewritten only according to the rewriting instruction, and the rewriting instruction of the user can be stored in a rewriting knowledge base as a new rewriting rule at the same time or later. For example, when the user selects the option of confirming the modification, it can be confirmed that the rewriting instruction of the user matches with the rewriting prompt information; or, when the user manually inputs a specific rewriting instruction for the inefficient SQL statement, it may be determined whether the rewriting instruction input by the user matches the rewriting rule corresponding to the inefficient type of the inefficient SQL statement. When the rewrite instruction of the user is stored as a new rewrite rule in the rewrite knowledge base, the old rewrite rule corresponding to the inefficient type of the inefficient SQL statement may be deleted, or the new and old rewrite rules may be stored together. When new rewriting rules and old rewriting rules are stored in the rewriting knowledge base at the same time, when the rewriting prompt information of the low-efficiency SQL statement is pushed to the user, the rewriting prompt information comprising the new rewriting rules and the old rewriting rules can be pushed to the user, and the rewriting prompt information comprising the new rewriting rules, the old rewriting rules or a plurality of rewriting rules can be pushed to the user in an optional mode.

In practice, when SQL statements are used in a database, each SQL statement usually has a corresponding table to be queried, and each SQL statement usually has a corresponding column to be queried. Therefore, in order to improve the system operation efficiency, after the obtained SQL statements are normalized, the columns queried by the SQL statements can be optimized. In one possible implementation manner, the query may be performed according to a predicate in the normalized SQL statement, where the SQL statement is the column or columns of the table, and then whether the column has an index may be determined. When the column does not have the index, it indicates that the user does not build the index for the column, at this time, the index can be directly built for the column, or prompt information for building the index is pushed to the user, and the prompt information may include an SQL statement for building the index. For the sake of distinction, the index creation hint may be referred to as a second hint. After the index creation hint is pushed to the user, the index may be created for the column according to the user's instructions. For example, the index creation prompt may include an SQL statement for creating an index, and the index creation prompt may also include an option to confirm whether to create an index or not. After the user selects the option of confirming the index establishment, the index can be directly established for the column; or, the user may also manually build the index for the column according to the index build prompt information or according to the SQL statement for building the index included in the index build prompt information. By indexing the columns without indexes, the data query efficiency can be improved.

When there are indexes in a column, the frequency of use or the number of uses of each index may be calculated. When the use frequency of the index is less than, equal to or not greater than the second reference threshold, the index can be directly deleted, or index deletion prompt information can be pushed to the user. For the sake of distinction, this prompt message may be referred to as a third prompt message. After the index deletion prompting information is pushed to the user, the index can be deleted according to the deletion instruction of the user. It should be noted that the specific value of the second reference threshold may be set by the user before the method provided in the embodiment is executed. For example, the value of the second reference threshold may be an integer equal to or greater than 0.

In one possible implementation, the index may have been recently created or recently used by the user. In order to avoid deleting the index that the user has just established or used recently, after determining that the frequency of use of the index is less than, equal to, or not greater than the second reference threshold, it may be determined whether the index is a necessary index, which refers to an index that the user has established recently or an index that the user has used recently. For example, when it is determined that the frequency of use of the index is less than, equal to, or not greater than the second reference threshold, the establishment time and/or the most recent use time of the index is obtained or queried. When the difference between the establishment time of the index and the current time is less than, equal to or not greater than a third reference threshold, the index can be determined to be the index established by the user most recently; when the difference between the latest usage time of the index and the current time is less than, equal to, or not greater than the fourth reference threshold, the index may be determined to be the index most recently used by the user. When the index is determined to be the index which is created or used by the user recently, namely the index is determined to be the necessary index, even if the use frequency of the index is less than, equal to or not more than the second reference threshold, the index is not deleted or the index deletion prompt information is not pushed to the user. And when the index is not the necessary index, the index can be deleted, or index deletion prompt information can be pushed to the user. It should be noted that specific values of the third reference threshold and the fourth reference threshold may be set by a user before the method provided in this embodiment is executed. For example, the third reference threshold and the fourth reference threshold may each have a positive value greater than 0, and may be in units of time such as seconds, minutes, hours, days, weeks, months, and years. The third and fourth reference thresholds may be equal or unequal in value and unit.

In a possible implementation manner, after the obtained SQL statement is normalized, the query record and the modification data of the table corresponding to the normalized SQL statement may be obtained according to the log file or the history record. The query record for a table may reflect the time and frequency of the table being queried, and when a query record exists for a table, it may indicate that the table was queried. The modified data of the table may include a history of additions, deletions, and/or modifications to the data in the table, etc. When the table corresponding to the SQL statement has the query record and no modified data, it can be determined that the table has high stability and certain use frequency, so that a materialized view can be created for the table, or prompt information for creating the materialized view for the table is pushed to a user, and then the materialized view can be created for the table according to the instruction of the user. For the sake of distinction, this prompt message may be referred to as a fourth prompt message. By creating a materialized view for a table with higher stability, the query efficiency of data can be improved.

In one possible implementation manner, after the query record of the table is obtained, it may be determined whether the table is a new table recently established by the user or a obsolete table that has not been queried or modified for a long time. For example, the creation time and/or access time of the table may be obtained from a log file or a history, and when the difference between the creation time of the table and the current time is less than, equal to, or not greater than a fifth reference threshold, it may be determined that the table is a new table that was newly created by the user; when the difference between the latest access time of the table and the current time is greater than, equal to, or not less than the sixth reference threshold, it may be determined that the table is a discard table that has not been used for a long time. Since the creation of a materialized view may increase the storage space of a table, a materialized view may not be created for a new table with lower stability and a discarded table that is not used for a long time. It should be noted that specific values of the fifth reference threshold and the sixth reference threshold may be set by the user before the method provided in the present embodiment is executed. For example, the numerical values of the fifth reference threshold and the sixth reference threshold may each be a positive number greater than 0, and the units may be time units such as seconds, minutes, hours, days, weeks, months, and years. The values and units of the fifth reference threshold and the sixth reference threshold may be equal or different.

It should be noted that, the value ranges of the various reference thresholds described in the present embodiment are theoretically possible, but in specific practice, a person skilled in the art should reasonably set the values of the various reference thresholds according to actual requirements.

The database maintenance method provided by the embodiment may be automatically executed at irregular or periodic intervals, or may also be started to be executed according to an instruction of a user. For example, when the user considers that maintenance is required for the database, an instruction for executing the database maintenance method may be sent to the database, so as to start diagnosing and optimizing the SQL statements and data columns or data tables in the database.

According to the database maintenance method provided by the embodiment, the execution time of the SQL sentences after the normalization processing is obtained through the SQL sentences used by the user in the normalization processing database, the low-efficiency SQL sentences in the SQL sentences after the normalization processing are obtained according to the execution time, and then the rewriting prompt information corresponding to the low-efficiency SQL sentences is pushed to the user, so that the low-efficiency sentences can be found and processed in time, the operation and maintenance efficiency of the database can be improved, and the difficulty of the operation and maintenance of the database can be greatly reduced because technicians do not need to learn the service carried by the database. By establishing indexes for columns without indexes or prompting a user to establish indexes for the columns without indexes and establishing materialized views for tables with higher stability, the query efficiency of data in the database can be improved.

The third embodiment of the present disclosure provides a database maintenance method, which is applicable to any database. For example, the method is applicable to each database node in any stand-alone database or distributed database cluster system. As shown in fig. 3, the method includes:

s301, normalizing SQL sentences used by users in the database.

S302, judging whether the column queried by the SQL statement after normalization processing has an index.

S303, when the column has no index, establishing an index for the column, or pushing index establishment prompt information to a user.

Alternatively, when there are indexes in a column, the frequency of use of each index is calculated. And when the use frequency of the index is less than, equal to or not greater than the second reference threshold, deleting the index or pushing index deletion prompt information to the user. Or, when the use frequency of the index is less than, equal to or not greater than the second reference threshold, judging whether the index is a necessary index, wherein the necessary index refers to an index which is established by the user recently or an index which is used by the user recently, and when the index is not the necessary index, deleting the index or pushing index deletion prompt information to the user.

For example, the build time and/or the most recent usage time of the index may be obtained;

The database maintenance method provided in this embodiment is the same as or similar to the optimization method for the data sequence in the database maintenance method provided in the above embodiment, and details are not repeated here.

By establishing indexes for columns without indexes or pushing index establishment prompt information to a user and establishing indexes for the columns according to instructions of the user, the data query efficiency can be improved. When the indexes exist in the columns, the index which is not commonly used can be deleted according to the use frequency of the indexes, and the storage space can be saved.

The fourth embodiment of the present disclosure provides a database maintenance method, which is applicable to any database. For example, the method is applicable to each database node in any stand-alone database or distributed database cluster system. As shown in fig. 4, the method includes:

s401, normalizing SQL sentences used by users in the database.

S402, acquiring query records and modified data of the table corresponding to the SQL statement after normalization processing.

S403, if the table has the query record and no modified data, creating a materialized view for the table, or pushing prompt information for creating the materialized view for the table to a user; or judging whether the table is a new table or a abandoned table, and creating a materialized view for the table or pushing prompt information for creating the materialized view for the table to a user when the table is not the new table or the abandoned table.

Alternatively, the creation time and the latest access time of the table may be acquired, and when the difference between the creation time of the table and the current time is less than, equal to, or not greater than the fifth reference threshold, it may be determined that the table is a new table; and when the difference value of the latest access time of the table from the current time is greater than, equal to or not less than a sixth reference threshold value, determining that the table is a discard table.

The database maintenance method provided in this embodiment is the same as or similar to the optimization method for the data table in the database maintenance method provided in the above embodiment, and details are not repeated here.

The operation and maintenance efficiency of the database can be improved by creating the materialized view for the table with good stability or pushing prompt information for creating the materialized view for the table to a user and creating the materialized view for the table with good stability according to a user instruction. By judging whether the table is a new table or a abandoned table and giving up creating the materialized view for the table when the table is the new table or the abandoned table, the materialized view can be prevented from being created for the table with low stability or the table which is not used for a long time, and the waste of storage space is avoided.

A fifth embodiment of the present disclosure provides a database maintenance apparatus, which may be applied to any database, for example, may be applied to any stand-alone database or each database node in a distributed database cluster system. As shown in fig. 5, the apparatus includes:

the second acquisition module is configured to acquire an inefficient SQL statement in the normalized SQL statement, wherein the inefficient SQL statement is the SQL statement with the longest execution time or the SQL statement with the execution time greater than, equal to or not less than the first reference threshold;

the first information pushing module is configured to push rewriting prompt information corresponding to the inefficient SQL statement to a user.

Optionally, the first obtaining module may obtain the execution time of the normalized SQL statement according to the following formula;

T=T _{general assembly} /M

Optionally, a rewriting knowledge base is provided in the database applied by the apparatus or in a device connected to the database applied by the apparatus, and the rewriting knowledge base stores rewriting rules for rewriting the inefficient SQL statements.

Optionally, the apparatus includes a third obtaining module configured to obtain an inefficient type of an inefficient SQL statement; the first information pushing module may be configured to push rewriting prompt information to the user according to the inefficient type of the inefficient SQL statement, the rewriting prompt information including a rewriting rule corresponding to the inefficient type of the inefficient SQL statement.

Optionally, the apparatus further comprises a first determining module, a rewriting module and a storing module.

The first judging module can be configured to judge whether the rewriting instruction is matched with the rewriting prompt information after receiving the rewriting instruction of the user;

the rewriting module may be configured to rewrite the inefficient SQL statement according to a rewriting instruction or a rewriting rule included in the rewriting hint information when the rewriting instruction matches the rewriting hint information;

the rewriting module may be further configured to rewrite the inefficient SQL statement according to the rewriting instruction when the rewriting instruction does not match the rewriting prompt information;

the storage module may be configured to store the rewrite instruction as a new rewrite rule to a rewrite repository when the rewrite instruction does not match the rewrite hint information.

Optionally, the apparatus may further include a second determining module, an index establishing module, a second information pushing module, a first calculating module, an index deleting module, and a third information pushing module.

The second judging module can be configured to judge whether the normalized SQL statement query column has an index;

the index building module may be configured to build an index for a column when the column does not have an index;

the second information pushing module can be configured to push index establishment prompt information to the user when the column has no index;

the first calculation module may be configured to calculate a frequency of use of each index when the index exists in the column;

the index deletion module may be configured to delete the index when the frequency of use of the index is less than, equal to, or not greater than a second reference threshold;

the third information pushing module can be configured to push the index deletion prompting information to the user when the use frequency of the index is less than, equal to or not greater than the second reference threshold.

Optionally, the apparatus further includes a third determining module.

The third judging module may be configured to judge whether the index is a necessary index when the use frequency of the index is less than, equal to, or not greater than a second reference threshold, the necessary index being an index that is newly established by the user or an index that is recently used by the user;

the index deletion module may be further configured to delete the index when the index is not a necessary index;

the third information pushing module can be further configured to push index deletion prompting information to the user when the index is not the necessary index.

Optionally, the third determining module includes a first obtaining sub-module, a first determining sub-module, and a second determining sub-module.

The first obtaining submodule can be configured to obtain the establishment time and/or the latest use time of the index;

the first determining sub-module may be configured to determine that the index is an index most recently set up by the user when a difference between the set-up time of the index and the current time is less than, equal to, or not greater than a third reference threshold;

the second determination submodule may be configured to determine the index as an index most recently used by the user when a difference between a most recently used time of the index and the current time is less than, equal to, or not greater than a fourth reference threshold.

Optionally, the apparatus may further include a fourth obtaining module, a materialized view creating module, and a fourth information pushing module.

The fourth obtaining module may be configured to obtain query records and modification data of a table corresponding to the normalized SQL statement;

the materialized view creation module may be configured to create a materialized view for the table when the table has a query record and no modified data;

the fourth information pushing module may be configured to push a hint to a user to create a materialized view for the table when the table has the query record and no modified data.

Optionally, the apparatus may further include a fourth determining module.

The fourth determining module may be configured to determine whether the table is a new table or a discard table when the table has the query record and no modified data.

The materialized view creation module may be further configured to create a materialized view for the table when the table is not a new table or a discarded table;

the fourth information pushing module may be further configured to push a hint to a user to create a materialized view for the table when the table is not a new table or a discarded table.

Optionally, the fourth determining module may include a second obtaining sub-module, a third determining sub-module, and a fourth determining sub-module.

The second obtaining sub-module may be configured to obtain a creation time of the table;

the third determination submodule may be configured to determine that the table is a new table when a difference between the creation time of the table and the current time is less than, equal to, or not greater than a fifth reference threshold.

The third obtaining submodule may be configured to obtain a latest access time of the table;

the fourth determination submodule may be configured to determine that the table is a discard table when a difference between the latest access time of the table and the current time is greater than, equal to, or not less than a sixth reference threshold.

The database maintenance device provided by this embodiment obtains the execution time of the SQL statements after the normalization processing by normalizing the SQL statements used by the user in the database, obtains the inefficient SQL statements in the SQL statements after the normalization processing according to the execution time, and then pushes the rewriting prompt information corresponding to the inefficient SQL statements to the user, so that the inefficient statements can be found and processed in time, the operation and maintenance efficiency of the database can be improved, and the difficulty of the operation and maintenance of the database can be greatly reduced because technicians are not required to learn the services carried by the database. By establishing indexes for columns without indexes or prompting a user to establish indexes for the columns without indexes and establishing materialized views for tables with higher stability, the query efficiency of data in the database can be improved.

A sixth embodiment of the present disclosure provides a database maintenance apparatus, which may be applied to any database, for example, may be applied to any stand-alone database or each database node in a distributed database cluster system. As shown in fig. 6, the apparatus includes:

the second judgment module is configured to judge whether the normalized SQL statement inquired column has an index or not;

an index building module configured to build an index for a column when the column does not have an index; or the second information pushing module is configured to push index establishment prompt information to the user when the column does not have the index.

Optionally, the apparatus further comprises:

a first calculation module configured to calculate a frequency of use of each index when the index exists in the column.

An index deletion module configured to delete the index when a frequency of use of the index is less than, equal to, or not greater than a second reference threshold;

and the third information pushing module is configured to push the index deletion prompting information to the user when the use frequency of the index is less than, equal to or not greater than the second reference threshold.

Optionally, the apparatus further includes a third determining module.

A seventh embodiment of the present disclosure provides a database maintenance apparatus, which may be applied to any database, for example, may be applied to any stand-alone database or each database node in a distributed database cluster system. As shown in fig. 7, the apparatus includes:

the fourth acquisition module is configured to acquire the query records and the modified data of the table corresponding to the normalized SQL statement;

the materialized view creating module is configured to create a materialized view for the table when the table has the query record and no modification data; or the fourth information pushing module is configured to push prompt information for creating a materialized view for the table to the user when the table has the query record and no modification data.

Optionally, the apparatus may further include a fourth determining module.

An eighth embodiment of the present disclosure provides a database optimization apparatus, which includes a processor and a memory. Wherein the memory has stored therein computer program instructions adapted to be executed by the processor. When the computer program instructions are executed by a processor, the processor performs the database maintenance method provided by any of the above embodiments.

It should be noted that, when the database maintenance apparatus provided in the foregoing embodiment is used to maintain a database, the division of each functional module is merely used as an example, and in practical applications, the above function distribution may be completed by different functional modules according to needs, that is, the internal structure or program of the apparatus is divided into different functional modules to complete all or part of the above described functions. In addition, the database maintenance device and the database maintenance method provided by the above embodiments belong to the same concept, and specific implementation processes thereof are detailed in the method embodiments and are not described herein again.

A ninth embodiment of the present disclosure provides a computer storage medium having computer-executable instructions stored thereon. The computer executable instructions, when executed by a processor, implement the database maintenance method provided by any of the above embodiments. The database maintenance method provided by the first embodiment is implemented, for example, when the computer program instructions are executed by a processor.

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

A tenth embodiment of the present disclosure provides a database comprising the database maintenance apparatus and/or the computer storage medium of any of the above embodiments. The database maintenance apparatus and the computer storage medium provided in the above embodiments may be embedded in the database system in the form of computer code, computer program, and/or computer function.

An eleventh embodiment of the present disclosure provides an electronic device, which includes the database maintenance apparatus according to any of the foregoing embodiments, a computer storage medium, and/or the database provided by the foregoing embodiments. For example, the electronic device may be a mobile phone, a computer, a tablet computer, a server, a network device, or the like, or may also be a usb disk, a removable hard disk, a Read Only Memory (ROM), a magnetic disk, or an optical disk, and the electronic device may be loaded with the database provided in the above embodiments.

For example, the electronic device may include: a processor, a memory, an input/output interface, a communication interface, and a bus. Wherein the processor, the memory, the input/output interface and the communication interface are communicatively connected to each other within the device by a bus.

The processor may be implemented by a general-purpose CPU (Central Processing Unit), a microprocessor, an Application Specific Integrated Circuit (ASIC), or one or more Integrated circuits, and is configured to execute a relevant program to implement the technical solutions provided in the embodiments of the present specification.

The Memory may be implemented in the form of a ROM (Read Only Memory), a RAM (Random access Memory), a static storage device, a dynamic storage device, or the like. The memory may store an operating system and other application programs, and when the technical solution provided by the embodiments of the present specification is implemented by software or firmware, the relevant program codes are stored in the memory and called by the processor to be executed.

The input/output interface is used for connecting the input/output module to realize information input and output. The input/output/modules may be configured in the device as components or may be external to the device to provide corresponding functionality. The input devices may include a keyboard, a mouse, a touch screen, a microphone, various sensors, etc., and the output devices may include a display, a speaker, a vibrator, an indicator light, etc.

The communication interface is used for connecting the communication module so as to realize the communication interaction between the equipment and other equipment. The communication module can realize communication in a wired mode (such as USB, network cable and the like) and also can realize communication in a wireless mode (such as mobile network, WIFI, Bluetooth and the like).

A bus includes a path that transfers information between the various components of the device, such as the processor, memory, input/output interfaces, and communication interfaces.

It should be noted that although the above-described device shows only a processor, a memory, an input/output interface, a communication interface and a bus, in a specific implementation, the device may also include other components necessary for normal operation. In addition, those skilled in the art will appreciate that the above-described apparatus may also include only the components necessary to implement the embodiments of the present description, and not necessarily all of the described components.

From the above description of the embodiments, it is clear to those skilled in the art that the embodiments of the present disclosure can be implemented by software plus necessary general hardware platform. Based on such understanding, the technical solutions of the embodiments of the present specification may be essentially or partially implemented in the form of a software product, which may be stored in a storage medium, such as a ROM/RAM, a magnetic disk, an optical disk, etc., and includes several instructions for enabling a computer device (which may be a personal computer, a server, or a network device, etc.) to execute the methods described in the embodiments or some parts of the embodiments of the present specification.

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

The embodiments in the present specification are described in a progressive manner, and the same and similar parts among the embodiments are referred to each other, and each embodiment focuses on the differences from the other embodiments. The above-described method embodiments are merely illustrative, wherein the modules described as separate components may or may not be physically separate, and the functions of the modules may be implemented in one or more software and/or hardware when implementing the embodiments of the present specification. And part or all of the modules can be selected according to actual needs to achieve the purpose of the scheme of the embodiment. One of ordinary skill in the art can understand and implement it without inventive effort.

In the description herein, reference to the description of the terms "one embodiment/mode," "some embodiments/modes," "example," "specific example," or "some examples," etc., means that a particular feature, structure, material, or characteristic described in connection with the embodiment/mode or example is included in at least one embodiment/mode or example of the application. In this specification, the schematic representations of the terms used above are not necessarily intended to be the same embodiment/mode or example. Furthermore, the particular features, structures, materials, or characteristics described may be combined in any suitable manner in any one or more embodiments/modes or examples. Furthermore, the various embodiments/aspects or examples and features of the various embodiments/aspects or examples described in this specification can be combined and combined by one skilled in the art without conflicting therewith.

Furthermore, the terms "first", "second" and "first" are used for descriptive purposes only and are not to be construed as indicating or implying relative importance or implicitly indicating the number of technical features indicated. Thus, a feature defined as "first" or "second" may explicitly or implicitly include at least one such feature. In the description of the present application, "plurality" means at least two, e.g., two, three, etc., unless specifically limited otherwise.

It will be understood by those skilled in the art that the foregoing embodiments are merely for clarity of illustration of the disclosure and are not intended to limit the scope of the disclosure. Other variations or modifications may occur to those skilled in the art, based on the foregoing disclosure, and are still within the scope of the present disclosure.

Claims

1. A method of maintaining a database, the method comprising, for any one database:

normalizing SQL sentences used by users in the database to convert user-defined variables in the same type of SQL sentences into specific values, so that the same type of SQL sentences have the same syntax tree;

pushing rewriting prompt information corresponding to the low-efficiency SQL statement to a user;

a rewriting knowledge base is arranged in the database or in equipment connected with the database, and rewriting rules for rewriting the low-efficiency SQL statements are stored in the rewriting knowledge base;

the pushing of rewriting prompt information corresponding to the inefficient SQL statement to the user includes: acquiring an inefficient type of the inefficient SQL statement; pushing the rewriting prompt information to a user according to the low-efficiency type of the low-efficiency SQL statement, wherein the rewriting prompt information comprises a rewriting rule corresponding to the low-efficiency type of the low-efficiency SQL statement;

the method further comprises the following steps:

after receiving a rewriting instruction of a user, judging whether the rewriting instruction is matched with the rewriting prompt information; when the rewriting instruction is matched with the rewriting prompt information, rewriting the low-efficiency SQL statement according to the rewriting instruction or the rewriting rule included in the rewriting prompt information; and when the rewriting instruction is not matched with the rewriting prompt information, rewriting the low-efficiency SQL statement according to the rewriting instruction, and storing the rewriting instruction as a new rewriting rule in the rewriting knowledge base, wherein the rewriting rule corresponding to the low-efficiency SQL statement is applicable to the same type of SQL statement corresponding to the low-efficiency SQL statement.

2. The database maintenance method according to claim 1, wherein the obtaining the execution time of the normalized SQL statement comprises:

T=T _{general assembly} /M

3. The database maintenance method according to any of claims 1-2, characterized in that the method further comprises:

4. The database maintenance method according to claim 3, characterized in that the method comprises:

when the column has indexes, calculating the use frequency of each index;

5. The database maintenance method according to claim 3, characterized in that the method comprises:

when the column has indexes, calculating the use frequency of each index;

6. The database maintenance method according to claim 5, wherein said determining whether the index is a necessary index comprises:

acquiring the establishment time and/or the latest use time of the index;

7. The database maintenance method according to any of claims 1-2, characterized in that the method further comprises:

8. The database maintenance method according to any of claims 1-2, characterized in that the method further comprises:

9. The method of claim 8, wherein said determining whether the table is a new table comprises:

acquiring the creation time of the table;

10. The method of claim 8, wherein the determining whether the table is a discard table comprises:

obtaining the latest access time of the table;

11. A database maintenance apparatus, wherein the apparatus is applied to any database, the apparatus comprising:

the normalization processing module is configured to normalize SQL sentences used by users in the database, so that user-defined variables in the same type of SQL sentences are converted into specific values, and the same type of SQL sentences have the same syntax tree;

the first information pushing module is configured to push rewriting prompt information corresponding to the low-efficiency SQL statement to a user;

a rewriting knowledge base is arranged in a database applied by the device or in equipment connected with the database applied by the device, and rewriting rules for rewriting the low-efficiency SQL statements are stored in the rewriting knowledge base;

the apparatus further comprises a third obtaining module configured to obtain an inefficiency type of the inefficiency SQL statement; the first information pushing module is configured to push rewriting prompt information to a user according to the inefficient type of the inefficient SQL statement, wherein the rewriting prompt information comprises a rewriting rule corresponding to the inefficient type of the inefficient SQL statement;

the device also comprises a first judgment module, a rewriting module and a storage module;

the first judging module is configured to judge whether the rewriting instruction is matched with the rewriting prompt information after receiving the rewriting instruction of a user;

the rewriting module is configured to rewrite the inefficient SQL statement according to the rewriting instruction or a rewriting rule included in the rewriting prompt information when the rewriting instruction is matched with the rewriting prompt information;

the rewriting module is further configured to rewrite the inefficient SQL statement according to the rewriting instruction when the rewriting instruction does not match the rewriting prompt information;

the storage module is configured to store the rewrite instruction as a new rewrite rule in the rewrite knowledge base when the rewrite instruction does not match the rewrite prompt information, and the rewrite rule corresponding to the inefficient SQL statement is applicable to the same SQL statement type corresponding to the inefficient SQL statement.

12. An electronic device, applied to any database, comprising a processor and a memory, wherein computer program instructions are stored in the memory and executed by the processor, and the computer program instructions are executed by the processor to perform the database maintenance method according to any one of claims 1 to 10.

13. A computer storage medium applied to any database, the computer storage medium storing computer-executable instructions which, when executed by a processor, implement the database maintenance method according to any one of claims 1 to 10.