Database index updating method and device, server and storage medium

Abstract

The invention relates to the technical field of data processing, and provides a database index updating method, which comprises the following steps of: acquiring an SQL (Structured Query Language) statement set of a database, extracting a database table field of each SQL statement in the SQL statement set according to an SQL statement type, extracting a database table name of each SQL statement in the SQL statement set according to a grammar structure of the SQL statement, and updating the database index of the database. Counting all the extracted database table fields and database table names to obtain field use information and table use information; aggregating the field use information and the table use information to obtain a summary information table, sorting the summary information table according to the use times from high to low, and extracting database table names and database table fields which are sorted in the top according to a preset proportion to obtain to-be-updated table names and to-be-updated fields; and updating the database index according to the to-be-updated table name and the to-be-updated field. According to the database index updating method, the database index can be updated according to common table names and fields in the database, and the field operation fluency of the database table is improved.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and in particular to a database index updating method, device, server and storage medium. Background Art

[0002] As an important component for storing and processing business data, the amount of data in the database will increase as the business continues to develop. If the database index is not established when the initial business data volume is small, the corresponding field operations will slow down after the business data volume increases in the later stage, affecting the use of business functions. After the database index is established, as the business developers and business needs change over time, the field operations of the database will also change, causing some database indexes established based on historical field operations to become invalid indexes, and after some business functions are offline, the corresponding database indexes may no longer be used and become invalid indexes. Invalid indexes will lead to index waste and affect the operational performance of the database table. Summary of the invention

[0003] The present invention provides a database index updating method, device, server and storage medium, the main purpose of which is to provide a method for updating database index according to database field operation to solve the technical problem that invalid index affects the database table operation performance.

[0004] To achieve the above object, the present invention provides a database index updating method, the method comprising:

[0005] Get the SQL statement set of the database;

[0006] Analyze each SQL statement in the SQL statement set according to the corresponding SQL statement type, extract the database table field of each SQL statement, count all the extracted database table fields to obtain field usage information, extract the database table name from each SQL statement in the SQL statement set according to the grammatical structure of the SQL statement, and count all the extracted database table names to obtain table usage information;

[0007] Aggregating the field usage information and the table usage information to obtain a summary information table, sorting the database table names and database table fields in the summary information table from high to low according to the number of times of use, and extracting the top-ranked database table names and database table fields in the summary information table according to a preset ratio to obtain the table names and fields to be updated;

[0008] The database index is updated according to the table name to be updated and the field to be updated.

[0009] Optionally, the acquiring of a set of SQL statements of a database includes:

[0010] Obtaining the SQL statement record table corresponding to the database;

[0011] A plurality of SQL statements are extracted from the SQL statement record table and the SQL statement set is generated.

[0012] Optionally, analyzing each SQL statement in the SQL statement set according to a corresponding SQL statement type and extracting a database table field of each SQL statement includes:

[0013] Classifying the SQL statements in the SQL statement set according to SQL statement types;

[0014] The positions of the database table fields in each classified SQL statement are analyzed according to the statement composition structure corresponding to the SQL statement type, and the database table fields in each SQL statement are extracted according to the positions of the database table fields.

[0015] Optionally, analyzing the location of the database table fields in each classified SQL statement according to the statement composition structure corresponding to the SQL statement type, and extracting the database table fields in each SQL statement according to the location of the database table fields, includes:

[0016] When the SQL statement is classified as a query statement, determining whether the query statement is a select * statement;

[0017] If not, extract the fields between select and from in the query statement according to the query statement composition structure to obtain the database table fields in the query statement;

[0018] If yes, obtain the query table name according to the composition structure of the select * query statement, obtain the corresponding data table from the database based on the query table name, and extract the fields in the data table as the database table fields in the query statement;

[0019] When the SQL statement is classified as a non-query statement, the database table fields in the SQL statement are extracted according to the statement composition structure corresponding to the SQL statement type to which the SQL statement belongs.

[0020] Optionally, the counting of all extracted database table fields to obtain field usage information includes:

[0021] Dividing a plurality of historical time periods according to the usage time of each SQL statement in the SQL statement set;

[0022] Count the usage times of database table fields extracted by SQL statements in each historical period;

[0023] The usage times of the database table fields in multiple historical periods are summed up to obtain the total usage times of the database table fields;

[0024] The field usage information is generated based on the total usage count of the database table field and the usage count of the database table field in each historical period.

[0025] Optionally, the counting of all extracted database table names to obtain table usage information includes:

[0026] Dividing a plurality of historical time periods according to the usage time of each SQL statement in the SQL statement set;

[0027] Count the number of times the database table name extracted by the SQL statement in each historical period is used;

[0028] The number of times the database table name is used in multiple historical periods is summed up to obtain the total number of times the database table name is used;

[0029] Generate table usage information based on the total number of times a database table name is used and the number of times a database table name is used in each historical period.

[0030] Optionally, updating the database index according to the to-be-updated table name and the to-be-updated field includes:

[0031] Matching the to-be-updated table name and the to-be-updated field with the index field in the database index;

[0032] Deleting the index fields in the database index that do not match the table name to be updated and the field to be updated;

[0033] Generate a new index field according to the table name to be updated and the field to be updated that is not matched to the index field and insert it into the database index.

[0034] In order to solve the above problem, the present invention further provides a database index updating device, the database index updating device comprising:

[0035] The statement acquisition module is used to obtain the SQL statement set of the database;

[0036] An analysis and statistics module is used to analyze each SQL statement in the SQL statement set according to the corresponding SQL statement type, extract the database table fields of each SQL statement, count all the extracted database table fields to obtain field usage information, extract the database table name from each SQL statement in the SQL statement set according to the grammatical structure of the SQL statement, and count all the extracted database table names to obtain table usage information;

[0037] A summary extraction module is used to aggregate the field usage information and the table usage information to obtain a summary information table, sort the database table names and database table fields in the summary information table from high to low according to the number of times of use, and extract the top-ranked database table names and database table fields in the summary information table according to a preset ratio to obtain the table names and fields to be updated;

[0038] The index updating module is used to update the database index according to the table name to be updated and the field to be updated.

[0039] In order to solve the above problem, the present invention further provides a server, the server comprising:

[0040] at least one processor; and,

[0041] a memory communicatively connected to the at least one processor; wherein,

[0042] The memory stores instructions that can be executed by the at least one processor. The instructions are executed by the at least one processor so that the at least one processor can perform the database index updating method as described above.

[0043] In order to solve the above problem, the present invention further provides a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the above-mentioned database index updating method is implemented.

[0044] The present invention analyzes the usage of tables and fields in a database based on the SQL statement set of the database, counts the usage times of tables and fields in the database according to the analysis results, extracts commonly used table names and fields in the database according to the usage times of tables and fields in the database, and updates the database index according to the commonly used table names and fields in the database, thereby realizing update optimization of the database index and improving the field operation fluency of the database table. BRIEF DESCRIPTION OF THE DRAWINGS

[0045] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the accompanying drawings required for use in the description of the embodiments of the present invention will be briefly introduced below. Obviously, the accompanying drawings in the following description are only some embodiments of the present invention. For ordinary technicians in this field, other accompanying drawings can be obtained based on these accompanying drawings without paying creative labor.

[0046] Figure 1 is a schematic diagram of an application environment of a database index updating method in one embodiment of the present invention;

[0047] Figure 2 It is a flowchart of a method for updating a database index in one embodiment of the present invention;

[0048] Figure 3 is a structural diagram of a server in one embodiment of the present invention;

[0049] Figure 4 It is a structural diagram of a database index updating device in one embodiment of the present invention.

[0050] The realization of the purpose, functional features and advantages of this application will be further explained in conjunction with embodiments and with reference to the accompanying drawings. DETAILED DESCRIPTION

[0051] In order to make the purpose, technical solution and advantages of the present invention more clear, the principle and spirit of the present invention will be described below with reference to several specific embodiments. It should be understood that the specific embodiments described here are only used to explain the present invention and are not used to limit the present invention.

[0052] It should be understood that the specific embodiments described herein are only used to explain the present invention and are not used to limit the present invention. Based on the embodiments in the present invention, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of the present invention. In addition, the technical solutions between each embodiment can be combined with each other, but it must be based on the ability of ordinary technicians in this field to implement. When the combination of technical solutions is contradictory or cannot be implemented, it should be considered that such combination of technical solutions does not exist and is not within the scope of protection required by the present invention.

[0053] It should be understood that the "multiple" mentioned in this application refers to two or more. In the description of this application, unless otherwise specified, " / " means or, for example, A / B can mean A or B; "and / or" in this article is only a description of the association relationship of associated objects, indicating that there can be three relationships, for example, A and / or B can mean: A exists alone, A and B exist at the same time, and B exists alone. In addition, in order to facilitate the clear description of the technical solution of this application, the words "first" and "second" are used to distinguish between the same items or similar items with basically the same functions and effects. Those skilled in the art can understand that the words "first" and "second" do not limit the quantity and execution order, and the words "first" and "second" do not limit them to be different.

[0054] The phrases such as "one embodiment" or "some embodiments" described in the present application mean that the specific features, structures or characteristics described in the embodiment are included in one or more embodiments of the present application. Therefore, the phrases such as "in one embodiment", "in some embodiments", "in some other embodiments", "in some other embodiments" that appear in the differences in the present application do not necessarily refer to the same embodiment, but mean "one or more but not all embodiments", unless otherwise specifically emphasized in other ways. In addition, the terms "including", "comprising", "having" and their variations all mean "including but not limited to", unless otherwise specifically emphasized in other ways.

[0055] The database index updating method provided by the embodiment of the present invention can be applied in Figure 1 In an application environment, the client communicates with the server through a network. The server can obtain the SQL statement set of the database; analyze each SQL statement in the SQL statement set according to the corresponding SQL statement type, extract the database table field of each SQL statement, count all the extracted database table fields to obtain field usage information, extract the database table name from each SQL statement in the SQL statement set according to the grammatical structure of the SQL statement, count all the extracted database table names to obtain table usage information; aggregate the field usage information and table usage information to obtain a summary information table, sort the database table names and database table fields in the summary information table from high to low according to the number of uses, extract the database table names and database table fields ranked top in the summary information table according to a preset ratio to obtain the table name and field to be updated; update the database index according to the table name and field to be updated, so that the client can perform field operations on the server's database according to the database index. In the present invention, the usage of tables and fields in the database is analyzed based on the SQL statement set of the database, the usage times of the tables and fields in the database are counted according to the analysis results, the commonly used table names and fields in the database are extracted according to the usage times of the tables and fields in the database, and the database index is updated according to the commonly used table names and fields in the database, thereby realizing the update optimization of the database index and improving the smoothness of the field operation of the database table. Among them, the client can be but not limited to various personal computers, laptops, smart phones, tablet computers and portable wearable devices. The server can be implemented with an independent server or a server cluster composed of multiple servers. The present invention is described in detail below through specific embodiments.

[0056] like Figure 2 FIG. 1 is a flow chart of an embodiment of a method for updating a database index according to the present invention. The method for updating a database index is applied to a server and includes steps S1-S4.

[0057] S1. Get the SQL statement set of the database.

[0058] In one embodiment, the obtaining of the SQL statement set of the database includes: obtaining a SQL statement record table corresponding to the database; extracting a plurality of SQL statements from the SQL statement record table and generating the SQL statement set.

[0059] In one embodiment, extracting multiple SQL statements from the SQL statement record table and generating the SQL statement set includes: obtaining multiple SQL statements and the usage time corresponding to each SQL statement from the SQL statement record table, generating a SQL statement set according to the multiple SQL statements, and marking the corresponding usage time for each SQL statement in the SQL statement set.

[0060] For example: The SQL statement record table corresponding to the database contains the following SQL statement records:

[0061] SQL Statements Time of use SELECT column FROM table_name; 20230512 INSERT INTO table_name(column)VALUES(value); 20230524 DELETE FROM table_name WHERE condition; 20230617 UPDATE table_name SET column=value WHERE condition; 20230725

[0062] The SQL statements obtained from the above SQL statement record table are:

[0063] 1. SELECT column FROM table_name;

[0064] Statement 1 is a query statement, where column indicates the name of the field to be selected. It can be multiple fields. If the field name is not specified, all fields will be selected; table_name indicates the name of the table to be queried.

[0065] 2. INSERT INTO table_name(column)VALUES(value);

[0066] Statement 2 is an insert statement, in which table_name indicates the name of the table into which the new field value needs to be inserted; column indicates the name of the column into which the new field value needs to be inserted; and value indicates the new field value to be inserted.

[0067] 3. DELETE FROM table_name WHERE condition;

[0068] Statement 3 is a delete statement, in which table_name indicates the name of the table to be deleted; condition indicates the deletion condition, which is used to specify which data to delete.

[0069] 4. UPDATE table_name SET column=value WHERE condition;

[0070] Statement 4 is a modification statement, in which table_name indicates the name of the table to be modified; column indicates the name of the column to be modified, which can be multiple columns; value indicates the field value to be modified, which can be multiple field values; condition indicates the modification condition, which is used to specify which data to be modified.

[0071] The above four SQL statements are extracted to form a SQL statement set, and the usage time is marked for each SQL statement. The usage time is used for subsequent classification and statistics of SQL statements in different historical time periods, which are usually set to be divided into time periods by month. Therefore, the usage time corresponding to the above SQL statements is marked as:

[0072] 1-2023 / 05; 2-2023 / 05; 3-2023 / 06; 4-2023 / 07.

[0073] S2. Analyze each SQL statement in the SQL statement set according to the corresponding SQL statement type, extract the database table fields of each SQL statement, count all the extracted database table fields to obtain field usage information, extract the database table name from each SQL statement in the SQL statement set according to the grammatical structure of the SQL statement, and count all the extracted database table names to obtain table usage information.

[0074] Field is a common term in computer science, referring to a column in a database table. In a database table, each field defines the type of data in that column, such as text, number, date, etc., and each field has a unique name so that programmers can easily reference them. Fields are one of the most basic elements in a database table. By combining multiple different fields together, you can create a complete and meaningful data table.

[0075] In one embodiment, analyzing each SQL statement in the SQL statement set according to the corresponding SQL statement type and extracting the database table fields of each SQL statement includes: classifying the SQL statements in the SQL statement set according to the SQL statement type; analyzing the locations of the database table fields in each classified SQL statement according to the statement composition structure corresponding to the SQL statement type, and extracting the database table fields in each SQL statement according to the locations of the database table fields.

[0076] In one embodiment, classifying the SQL statements in the SQL statement set according to SQL statement types includes: obtaining a statement type field corresponding to each SQL statement type, traversing and matching each SQL statement in the SQL statement set with the statement type field, and classifying the SQL statement type corresponding to the statement type field matched by each SQL statement as the SQL statement type corresponding to the SQL statement.

[0077] For example, the SQL statement types for database field operations include query statements, insert statements, delete statements, and update statements. The statement type field for query statements is SELECT, the statement type field for insert statements is INSERT, the statement type field for delete statements is DELETE, and the statement type field for update statements is UPDATE. The SQL statement set includes the following SQL statements:

[0078] 1. SELECT column FROM table_name;

[0079] 2. INSERT INTO table_name(column)VALUES(value);

[0080] 3. DELETE FROM table_name WHERE condition;

[0081] 4. UPDATE table_name SET column=value WHERE condition;

[0082] The above SQL statements are traversed and matched with the four statement type fields of SELECT, INSERT, DELETE and UPDATE respectively; the statement type field matched by statement 1 is SELECT, and the corresponding SQL statement type is a query statement; the statement type field matched by statement 2 is INSERT, and the corresponding SQL statement type is an insert statement; the statement type field matched by statement 3 is DELETE, and the corresponding SQL statement type is a delete statement; the statement type field matched by statement 4 is UPDATE, and the corresponding SQL statement type is an update statement.

[0083] In one embodiment, the position of the database table fields in each classified SQL statement is analyzed according to the statement composition structure corresponding to the SQL statement type, and the database table fields in each SQL statement are extracted according to the position of the database table fields, including: when the SQL statement is classified as a query statement, determining whether the query statement is a select* statement; if not, extracting the fields between select and from in the query statement according to the query statement composition structure to obtain the database table fields in the query statement; if so, obtaining the query table name according to the composition structure of the select* query statement, obtaining the corresponding data table from the database based on the query table name, and extracting the fields in the data table as the database table fields in the query statement; when the SQL statement is classified as a non-query statement, extracting the database table fields in the SQL statement according to the statement composition structure corresponding to the SQL statement type to which the SQL statement belongs.

[0084] For example, the structure of the select* query statement is:

[0085] SELECT * FROM table_name;

[0086] Among them, table_name indicates the name of the table to be queried, and * is a wildcard, indicating that all columns in the table are selected. According to the above composition structure of the select* query statement, the field after from is obtained to obtain the query table name in the select* query statement. Since the select* query statement does not specify the field name, but selects all fields through the wildcard *, the corresponding data table is obtained from the database based on the above query table name and all fields in the data table are extracted as the database table fields in the select* query statement.

[0087] Another query structure is:

[0088] SELECT column FROM table_name;

[0089] Among them, column represents the name of the field to be selected, which can be multiple fields; table_name represents the name of the table to be queried; according to the above query statement composition structure, the field column between select and from in the query statement is extracted to obtain the database table field in the query statement.

[0090] The classification of non-query statements in the above SQL statement classification includes but is not limited to insert statements, delete statements and update statements.

[0091] The above insert statement has the following structure:

[0092] INSERT INTO table_name(column)VALUES(value);

[0093] Among them, table_name represents the name of the table into which the new field value needs to be inserted; column represents the name of the column into which the new field value needs to be inserted; value represents the new field value to be inserted; according to the above insert statement composition structure, the field column in the insert statement is extracted to obtain the database table field in the insert statement.

[0094] The above delete statement has the following structure:

[0095] DELETE FROM table_name WHERE condition;

[0096] Among them, table_name indicates the name of the table to be deleted; condition indicates the deletion condition, which is used to specify which data to be deleted; according to the above deletion statement composition structure, the database table fields contained in the condition in the deletion statement are extracted to obtain the database table fields in the deletion statement.

[0097] The above update statement is structured as follows:

[0098] UPDATE table_name SET column=value WHERE condition;

[0099] Among them, table_name indicates the name of the table to be modified; column indicates the name of the column to be modified, which can be multiple columns; value indicates the field value to be modified, which can be multiple field values; condition indicates the modification condition, which is used to specify which data to be modified; according to the above update statement composition structure, the database table fields contained in column and condition in the update statement are extracted to obtain the database table fields in the update statement.

[0100] In one embodiment, counting all extracted database table fields to obtain field usage information includes: dividing a plurality of historical time periods according to the usage time of each SQL statement in the SQL statement set; counting the usage times of the database table fields extracted by the SQL statements in each historical time period; summing up the usage times of the database table fields in a plurality of historical time periods to obtain a total usage times of the database table fields; and generating field usage information based on the total usage times of the database table fields and the usage times of the database table fields in each historical time period.

[0101] For example, the SQL statement set contains the following SQL statements:

[0102] 1. SELECT column FROM table_name;

[0103] The usage time corresponding to the above SQL statement 1 is marked as: 2023 / 05, and the database table field extracted in SQL statement 1 is the field column between select and from.

[0104] 2. INSERT INTO table_name(column)VALUES(value);

[0105] The usage time corresponding to the above SQL statement 2 is marked as: 2023 / 05, and the database table field extracted in SQL statement 2 is the field column.

[0106] 3. DELETE FROM table_name WHERE condition;

[0107] The usage time corresponding to the above SQL statement 3 is marked as: 2023 / 06, and the database table field extracted in SQL statement 3 is the database table field included in condition.

[0108] 4. UPDATE table_name SET column=value WHERE condition;

[0109] The usage time corresponding to the above SQL statement 4 is marked as: 2023 / 07, and the database table fields extracted in SQL statement 4 are the database table fields included in column and condition.

[0110] Based on the usage time, the SQL statements in the above SQL statement set are divided into historical periods according to months, and the following are obtained:

[0111] The SQL statements for the historical period 2023 / 05 include: SQL statements 1 and 2;

[0112] The SQL statements for the historical period 2023 / 06 include: SQL statement 3;

[0113] The SQL statements for the historical period 2023 / 07 include: SQL statement 4.

[0114] Statistics are performed on all database table fields extracted in SQL statements 1 and 2 to obtain one or more database table fields corresponding to the historical period 2023 / 05 and their corresponding usage counts; statistics are performed on all database table fields extracted in SQL statement 3 to obtain one or more database table fields corresponding to the historical period 2023 / 06 and their corresponding usage counts; statistics are performed on all database table fields extracted in SQL statement 4 to obtain one or more database table fields corresponding to the historical period 2023 / 07 and their corresponding usage counts. All database table fields in the historical periods 2023 / 05, 2023 / 06 and 2023 / 07 are summarized, and the usage counts of the same database table field in each historical period are added up to obtain the total usage counts of each database table field.

[0115] In one embodiment, extracting the database table name from each SQL statement in the SQL statement set according to the grammatical structure of the SQL statement includes: classifying the SQL statements in the SQL statement set according to the SQL statement type; analyzing the position of the database table name in each classified SQL statement according to the grammatical structure corresponding to the SQL statement type, and extracting the database table name in each SQL statement according to the position of the database table name.

[0116] For example, the SQL statements in the SQL statement set are classified according to the SQL statement type, including query statements, insert statements, delete statements, and update statements.

[0117] The two grammatical structures of query statements are:

[0118] 1. SELECT column FROM table_name;

[0119] 2. SELECT * FROM table_name;

[0120] Among them, table_name indicates the name of the table to be queried; column indicates the name of the field to be selected, which can be multiple fields; * is a wildcard, indicating that all columns in the table are selected; according to the above query statement grammatical structure, the field table_name after from in the query statement is extracted to obtain the database table name in the query statement.

[0121] The syntax of the insert statement is:

[0122] INSERT INTO table_name(column)VALUES(value);

[0123] Among them, table_name indicates the name of the table into which the new field value needs to be inserted; column indicates the name of the column into which the new field value needs to be inserted; value indicates the new field value to be inserted; according to the above insert statement grammatical structure, the field table_name after insert into in the insert statement is extracted to obtain the database table name in the insert statement.

[0124] The syntax of the delete statement is:

[0125] DELETE FROM table_name WHERE condition;

[0126] Among them, table_name indicates the name of the table to be deleted; condition indicates the deletion condition, which is used to specify which data to be deleted; according to the above deletion statement grammatical structure, the field table_name between from and where in the deletion statement is extracted to obtain the database table name in the deletion statement.

[0127] The grammatical structure of the update statement is:

[0128] UPDATE table_name SET column=value WHERE condition;

[0129] Among them, table_name indicates the name of the table to be modified; column indicates the name of the column to be modified, which can be multiple columns; value indicates the field value to be modified, which can be multiple field values; condition indicates the modification condition, which is used to specify which data to be modified; according to the above update statement grammatical structure, the field table_name between update and set in the update statement is extracted to obtain the database table name in the update statement.

[0130] In one embodiment, the counting of all extracted database table names to obtain table usage information includes: dividing a plurality of historical time periods according to the usage time of each SQL statement in the SQL statement set; counting the number of times the database table name extracted by the SQL statement in each historical time period is used; summing the number of times the database table name is used in a plurality of historical time periods to obtain a total number of times the database table name is used; and generating table usage information based on the total number of times the database table name is used and the number of times the database table name is used in each historical time period.

[0131] For example, the SQL statement set contains the following SQL statements:

[0132] 1. SELECT column FROM table_name;

[0133] The usage time corresponding to the above SQL statement 1 is marked as: 2023 / 05, and the database table name extracted in SQL statement 1 is the field table_name after from.

[0134] 2. INSERT INTO table_name(column)VALUES(value);

[0135] The usage time corresponding to the above SQL statement 2 is marked as: 2023 / 05, and the database table name extracted in SQL statement 2 is the field table_name after insert into.

[0136] 3. DELETE FROM table_name WHERE condition;

[0137] The usage time corresponding to the above SQL statement 3 is marked as: 2023 / 06, and the database table name extracted in SQL statement 3 is the field table_name between from and where.

[0138] 4. UPDATE table_name SET column=value WHERE condition;

[0139] The usage time corresponding to the above SQL statement 4 is marked as: 2023 / 07, and the database table name extracted in SQL statement 4 is the field table_name between update and set.

[0140] Based on the usage time, the SQL statements in the above SQL statement set are divided into historical periods according to months, and the following are obtained:

[0141] The SQL statements for the historical period 2023 / 05 include: SQL statements 1 and 2;

[0142] The SQL statements for the historical period 2023 / 06 include: SQL statement 3;

[0143] The SQL statements for the historical period 2023 / 07 include: SQL statement 4.

[0144] All database table names extracted in SQL statements 1 and 2 are counted to obtain the database table names corresponding to the historical period 2023 / 05 and their corresponding usage times; all database table names extracted in SQL statement 3 are counted to obtain the database table names corresponding to the historical period 2023 / 06 and their corresponding usage times; all database table names extracted in SQL statement 4 are counted to obtain the database table names corresponding to the historical period 2023 / 07 and their corresponding usage times. All database table names in the historical periods 2023 / 05, 2023 / 06 and 2023 / 07 are summarized, and the usage times of the same database table name in each historical period are added up to obtain the total usage times of each database table name.

[0145] In this embodiment, by analyzing the SQL statements used by the database, the usage frequency information corresponding to the table names and table fields is extracted, thereby obtaining the database tables and database table fields that are frequently searched and used in the database.

[0146] For example, SQL statements are obtained in the trading application system of a financial company. According to "select amount from the first quarter fund trading table", the field "amount" and the table name "first quarter fund trading table" can be obtained. By counting all SQL statements, it is found that the field "amount" is used 10 times in April and the total number of times is 102 times. The table name "first quarter fund trading table" is used 95 times in May and the total number of times is 212 times.

[0147] S3. Aggregate the field usage information and the table usage information to obtain a summary information table, sort the database table names and database table fields in the summary information table from high to low according to the number of times used, and extract the top-ranked database table names and database table fields in the summary information table according to a preset ratio to obtain the table names and fields to be updated.

[0148] In data processing, aggregation is the merging of data from different data sources, emphasizing the merging of different statements about the same object scattered in different places to obtain more complete information about the object. The types of aggregation methods include group aggregation, compression aggregation, approximate aggregation and distributed aggregation.

[0149] In one embodiment, the method used to aggregate the field usage information and the table usage information to obtain the summary information table is group aggregation, the field usage information and the table usage information are grouped according to the data tables they belong to, and then the table usage count and the usage count of each field in each table are calculated to obtain the summary information table.

[0150] In one embodiment, the calculation of the number of times a table is used and the number of times each field in each table is used includes: obtaining the table creation time of each data table in the table usage information, setting the table weight of each data table according to the table creation time, and calculating the table usage times of each data table based on the table weight and the table usage time; obtaining the field creation time of each field in the field usage information, setting the field weight of each field according to the field creation time, and calculating the number of times each field in each table is used based on the field weight, the field usage time and the data table to which each field belongs.

[0151] In this embodiment, the table weight and field weight are set based on the creation time of the data table and the data table field, and the table usage times and the usage times of each field in each table are calculated according to the weight, which can reduce the situation that the identification of common tables and common fields is wrong and missed due to the inconsistent usage time of the new and old data tables. For example: the creation time of data table A is 1 year, the total usage times are 212 times, and the usage times in the last 2 months are 24 times; the creation time of data table B is 2 months, and the total usage times are 102 times. In this case, although the total usage times of data table B are less than the total usage times of data table A, the creation time of data table B is shorter than the creation time of data table A. If the data table is sorted only according to the total usage times, the more recently used data table B will be placed after data table A, resulting in errors in the identification of common tables. Therefore, setting weights according to the table creation time, setting a higher weight for the data table with a short creation time than the data table with a long creation time can effectively reduce the situation that the identification of common tables and common fields is wrong and missed due to the inconsistent usage time of the above-mentioned new and old data tables.

[0152] In one embodiment, the database index updating method further includes:

[0153] Detecting the execution time of each SQL statement in the database through a database monitoring program, and when the execution time of the SQL statement is greater than a preset time threshold, extracting the table name and table field in the SQL statement based on the structure of the SQL statement, determining whether the extracted table name and table field are database indexes of the database, and setting the preset ratio according to the number of table names and table fields extracted in a recent historical time period that are not database indexes;

[0154] The usage rate of each database index in the recent historical time period is detected by a database monitoring program to determine whether the usage rate of each database index is lower than a preset usage threshold, and the preset ratio is adjusted according to the number of database indexes whose usage rate is lower than the preset usage threshold in the recent historical time period.

[0155] In one embodiment, the preset ratio is set according to the number of table names and table fields extracted in the most recent historical time period and not being database indexes, including: obtaining a preset first quantity ratio mapping table, the first quantity ratio mapping table being used to represent the mapping relationship between the number of commonly used table names and table fields that are not database indexes and the extraction ratio of table names and fields to be updated; matching the corresponding extraction ratio of table names and fields to be updated from the first quantity ratio mapping table according to the number of table names and table fields extracted in the most recent historical time period and not being database indexes, and setting it to the preset ratio. Specifically, the more commonly used table names and table fields that are not database indexes in the first quantity ratio mapping table, the higher the corresponding extraction ratio of table names and fields to be updated. In this embodiment, for the case where the usage rate of table names and fields is high but no index is established, the scope of index establishment is expanded by increasing the preset ratio, thereby improving the coverage rate of database index usage.

[0156] In one embodiment, the preset ratio is adjusted according to the number of database indexes with a usage rate lower than a preset usage threshold in a recent historical time period, including: obtaining a preset second quantity ratio mapping table, the second quantity ratio mapping table is used to represent the mapping relationship between the number of database indexes with a usage rate lower than the preset usage threshold and the table names to be updated and the extraction ratios of the fields to be updated; according to the number of database indexes with a usage rate lower than the preset usage threshold in a recent historical time period, the corresponding table names to be updated and the extraction ratios of the fields to be updated are matched from the second quantity ratio mapping table, and set to the preset ratio. Specifically, the more database indexes with a usage rate lower than the preset usage threshold in the second quantity ratio mapping table, the lower the corresponding table names to be updated and the extraction ratios of the fields to be updated. In the case of low database index usage and invalid indexes, this embodiment reduces the extraction of invalid indexes by lowering the preset ratio, thereby improving the database index usage coverage.

[0157] S4. Update the database index according to the table name to be updated and the field to be updated.

[0158] In one embodiment, updating the database index according to the table name to be updated and the field to be updated includes: matching the table name to be updated and the field to be updated with the index field in the database index; deleting the index field in the database index that does not match the table name to be updated and the field to be updated; generating a new index field according to the table name to be updated and the field to be updated that does not match the index field and inserting it into the database index.

[0159] It can be seen from the above embodiments that the embodiments of the present invention analyze the usage of tables and fields in the database based on the SQL statement set of the database, count the number of times the tables and fields in the database are used according to the analysis results, extract the commonly used table names and fields in the database according to the number of times the tables and fields in the database are used, and update the database index according to the commonly used table names and fields in the database, thereby realizing the update optimization of the database index and improving the smoothness of the field operation of the database table.

[0160] like Figure 3 FIG. 1 is a schematic diagram of an embodiment of a server of the present invention. Server 1 is a device that can automatically perform numerical calculations and / or information processing according to pre-set or stored instructions. The server 1 can be a single network server, a server group consisting of multiple network servers, or a cloud based on cloud computing consisting of a large number of hosts or network servers, wherein cloud computing is a type of distributed computing, a super virtual computer composed of a group of loosely coupled computer sets.

[0161] In this embodiment, the server 1 includes, but is not limited to, a memory 11, a processor 12, and a network interface 13 that can be interconnected via a system bus. The memory 11 stores a database index update program 10 that can be executed by the processor 12. Figure 3 Only the server 1 having components 11-13 and the database index update program 10 is shown. It can be understood by those skilled in the art that Figure 3 The structure shown does not constitute a limitation on the server 1, and may include fewer or more components than shown in the figure, or combine certain components, or arrange the components differently.

[0162] The memory 11 includes a memory and at least one type of readable storage medium. The memory provides a cache for the operation of the server 1; the readable storage medium may be volatile or non-volatile. Specifically, the readable storage medium may be a storage medium such as a flash memory, a hard disk, a multimedia card, a card-type memory (e.g., SD or DX memory, etc.), a random access memory (RAM), a static random access memory (SRAM), a read-only memory (ROM), an electrically erasable programmable read-only memory (EEPROM), a programmable read-only memory (PROM), a magnetic memory, a disk, an optical disk, etc. In some embodiments, the readable storage medium may be an internal storage unit of the server 1, such as a hard disk of the server 1; in other embodiments, the storage medium may also be an external storage device of the server 1, such as a plug-in hard disk equipped on the server 1, a smart memory card (Smart Media Card, SMC), a secure digital (Secure Digital, SD) card, a flash card (Flash Card), etc. In this embodiment, the readable storage medium of the memory 11 mainly includes a program storage area and a data storage area, wherein the program storage area is generally used to store an operating system and various application software installed on the server 1, such as storing the code of the database index update program 10 in one embodiment of the present invention; the data storage area can store data created according to the use of the blockchain node, such as various types of data that have been output or are to be output.

[0163] The processor 12 may be composed of an integrated circuit in some embodiments, for example, a single packaged integrated circuit, or a plurality of integrated circuits with the same or different functions, including one or more central processing units (CPUs), microprocessors, digital processing chips, graphics processors, and combinations of various control chips. The processor 12 is the control core (Control Unit) of the electronic device, and uses various interfaces and lines to connect each component of the entire electronic device, and executes or executes programs or modules stored in the memory 11 (for example, executing resource dynamic scheduling programs, etc.), and calls data stored in the memory 11 to execute various functions of the electronic device and process data. The processor 12 is generally used to control the overall operation of the server 1, such as executing control and processing related to data interaction or communication with other devices. In this embodiment, the processor 12 is used to run the program code stored in the memory 11 or process data, such as running the database index update program 10, etc.

[0164] The network interface 13 may include a wireless network interface or a wired network interface.

[0165] Optionally, the server 1 may further include a user interface, which may include a display, an input unit such as a keyboard, and an optional user interface may further include a standard wired interface and a wireless interface. Optionally, in some embodiments, the display may be an LED display, a liquid crystal display, a touch-sensitive liquid crystal display, and an organic light-emitting diode (OLED) touch device. The display may also be appropriately referred to as a display screen or a display unit, which is used to display information processed in the server 1 and to display a visual user interface.

[0166] In one embodiment of the present invention, when the database index updating program 10 is executed by the processor 12, the following database index updating method is implemented, including steps S1-S4:

[0167] S1. Obtain the SQL statement set of the database;

[0168] S2. Analyze each SQL statement in the SQL statement set according to the corresponding SQL statement type, extract the database table field of each SQL statement, count all the extracted database table fields to obtain field usage information, extract the database table name from each SQL statement in the SQL statement set according to the grammatical structure of the SQL statement, and count all the extracted database table names to obtain table usage information;

[0169] S3, aggregating the field usage information and the table usage information to obtain a summary information table, sorting the database table names and database table fields in the summary information table from high to low according to the number of times of use, and extracting the top-ranked database table names and database table fields in the summary information table according to a preset ratio to obtain the table names and fields to be updated;

[0170] S4. Update the database index according to the table name to be updated and the field to be updated.

[0171] The specific operation steps implemented by the above steps S1-S4 are substantially the same as the steps S1-S4 of the first embodiment of the database index updating method of the present invention, and will not be repeated here.

[0172] In other embodiments, the database index update program 10 can also be divided into one or more modules, one or more modules are stored in the memory 11, and are executed by one or more processors (processor 12 in this embodiment) to complete the present invention. The module referred to in the present invention refers to a series of computer program instruction segments that can perform specific functions, and is used to describe the execution process of the database index update program 10 in the server 1.

[0173] like Figure 4FIG. 1 is a schematic diagram of the structure of a database index updating device provided in one embodiment of the present invention.

[0174] In the first embodiment of the present invention, the database index updating device 100 includes a statement acquisition module 110, an analysis and statistics module 120, a summary and extraction module 130 and an index updating module 140. Exemplarily:

[0175] The statement acquisition module 110 is used to acquire a set of SQL statements from a database;

[0176] The analysis and statistics module 120 is used to analyze each SQL statement in the SQL statement set according to the corresponding SQL statement type, extract the database table fields of each SQL statement, count all the extracted database table fields to obtain field usage information, extract the database table name from each SQL statement in the SQL statement set according to the grammatical structure of the SQL statement, and count all the extracted database table names to obtain table usage information;

[0177] The summary extraction module 130 is used to aggregate the field usage information and the table usage information to obtain a summary information table, sort the database table names and database table fields in the summary information table from high to low according to the number of times of use, and extract the top-ranked database table names and database table fields in the summary information table according to a preset ratio to obtain the table names and fields to be updated;

[0178] The index updating module 140 is used to update the database index according to the table name to be updated and the field to be updated.

[0179] The specific operation steps implemented by the above-mentioned statement acquisition module 110, analysis and statistics module 120, summary extraction module 130 and index update module 140 when being executed are generally the same as those in the above-mentioned embodiment, and will not be repeated here.

[0180] In addition, the embodiment of the present invention further proposes a computer-readable storage medium, which can be volatile or non-volatile. Specifically, the computer-readable storage medium can be any one or any combination of a hard disk, a multimedia card, an SD card, a flash memory card, an SMC, a read-only memory (ROM), an erasable programmable read-only memory (EPROM), a portable compact disk read-only memory (CD-ROM), a USB memory, etc. The computer-readable storage medium stores a database index update program.

[0181] The specific software function implementation of the computer-readable storage medium of the present invention is substantially the same as the specific software implementation in the above-mentioned server 1, and will not be described in detail here.

[0182] The serial numbers of the above embodiments of the present invention are only for description and do not represent the advantages or disadvantages of the embodiments.

[0183] It should be noted that, in this article, the terms "include", "comprises" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, device, article or method including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, device, article or method. In the absence of further restrictions, an element defined by the sentence "includes a ..." does not exclude the existence of other identical elements in the process, device, article or method including the element.

[0184] Through the description of the above implementation methods, those skilled in the art can clearly understand that the above-mentioned embodiment methods can be implemented by means of software plus a necessary general hardware platform, and of course by hardware, but in many cases the former is a better implementation method. Based on such an understanding, the technical solution of the present invention, or the part that contributes to the prior art, can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk), and includes a number of instructions for a terminal device (which can be a mobile phone, computer, server, air conditioner, or network device, etc.) to execute the method described in each embodiment of the present invention.

[0185] The above are only preferred embodiments of the present invention, and do not limit the patent scope of the present invention. Any equivalent structure or equivalent process transformation made by using the contents of the present invention and the drawings, or directly or indirectly applied in other related technical fields, are also included in the patent protection scope of the present invention. It should be noted that if software tools or components other than our company appear in the embodiments of this application, they are only used for example introduction and do not represent actual use.

Claims

1. A database index updating method, characterized in that: The method comprises: Get the SQL statement set of the database; Analyze each SQL statement in the SQL statement set according to the corresponding SQL statement type, extract the database table field of each SQL statement, count all the extracted database table fields to obtain field usage information, extract the database table name from each SQL statement in the SQL statement set according to the grammatical structure of the SQL statement, and count all the extracted database table names to obtain table usage information; Aggregating the field usage information and the table usage information to obtain a summary information table, sorting the database table names and database table fields in the summary information table from high to low according to the number of times of use, and extracting the top-ranked database table names and database table fields in the summary information table according to a preset ratio to obtain the table names and fields to be updated; The database index is updated according to the table name to be updated and the field to be updated.

2. The database index updating method according to claim 1, characterized in that: The SQL statement set for obtaining the database includes: Obtaining the SQL statement record table corresponding to the database; A plurality of SQL statements are extracted from the SQL statement record table and the SQL statement set is generated.

3. The database index updating method according to claim 1, characterized in that: The step of analyzing each SQL statement in the SQL statement set according to the corresponding SQL statement type and extracting the database table field of each SQL statement includes: Classifying the SQL statements in the SQL statement set according to SQL statement types; The positions of the database table fields in each classified SQL statement are analyzed according to the statement composition structure corresponding to the SQL statement type, and the database table fields in each SQL statement are extracted according to the positions of the database table fields.

4. The database index updating method according to claim 3, characterized in that: The analyzing the location of the database table fields in each classified SQL statement according to the statement composition structure corresponding to the SQL statement type, and extracting the database table fields in each SQL statement according to the location of the database table fields, includes: When the SQL statement is classified as a query statement, determining whether the query statement is a select * statement; If not, extract the fields between select and from in the query statement according to the query statement composition structure to obtain the database table fields in the query statement; If yes, obtain the query table name according to the composition structure of the select * query statement, obtain the corresponding data table from the database based on the query table name, and extract the fields in the data table as the database table fields in the query statement; When the SQL statement is classified as a non-query statement, the database table fields in the SQL statement are extracted according to the statement composition structure corresponding to the SQL statement type to which the SQL statement belongs.

5. The database index updating method according to claim 1, characterized in that: The method of obtaining field usage information by counting all extracted database table fields includes: Dividing a plurality of historical time periods according to the usage time of each SQL statement in the SQL statement set; Count the usage times of database table fields extracted by SQL statements in each historical period; The usage times of the database table fields in multiple historical periods are summed up to obtain the total usage times of the database table fields; The field usage information is generated based on the total usage count of the database table field and the usage count of the database table field in each historical period.

6. The database index updating method according to claim 1, characterized in that: The table usage information obtained by counting all extracted database table names includes: Dividing a plurality of historical time periods according to the usage time of each SQL statement in the SQL statement set; Count the number of times the database table name extracted by the SQL statement in each historical period is used; The number of times the database table name is used in multiple historical periods is summed up to obtain the total number of times the database table name is used; Generate table usage information based on the total number of times a database table name is used and the number of times a database table name is used in each historical period.

7. The database index updating method according to claim 1, characterized in that: The updating of the database index according to the to-be-updated table name and the to-be-updated field includes: Matching the to-be-updated table name and the to-be-updated field with the index field in the database index; Deleting the index fields in the database index that do not match the table name to be updated and the field to be updated; Generate a new index field according to the table name to be updated and the field to be updated that is not matched to the index field and insert it into the database index.

8. A database index updating device, characterized in that: The database index updating device comprises: The statement acquisition module is used to obtain the SQL statement set of the database; An analysis and statistics module is used to analyze each SQL statement in the SQL statement set according to the corresponding SQL statement type, extract the database table fields of each SQL statement, count all the extracted database table fields to obtain field usage information, extract the database table name from each SQL statement in the SQL statement set according to the grammatical structure of the SQL statement, and count all the extracted database table names to obtain table usage information; A summary extraction module is used to aggregate the field usage information and the table usage information to obtain a summary information table, sort the database table names and database table fields in the summary information table from high to low according to the number of times of use, and extract the top-ranked database table names and database table fields in the summary information table according to a preset ratio to obtain the table names and fields to be updated; The index updating module is used to update the database index according to the table name to be updated and the field to be updated.

9. A server, characterized in that: The server comprises: at least one processor; and, a memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can execute the database index updating method according to any one of claims 1 to 7.

10. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the database index updating method according to any one of claims 1 to 7 is implemented.

Images