Database index updating method and device, equipment and medium

Abstract

The embodiment of the invention discloses a database index updating method and device, equipment and a medium. The method comprises the steps of obtaining a current processing statement from a preset statement execution queue, and determining a current table-level characteristic of a current data table corresponding to the current processing statement based on a preset target table-level container; if the current table-level characteristic is a read-write cross characteristic, determining a historical processing statement associated with the current data table in a preset historical time period, and determining a time period table-level characteristic of the current data table based on the historical processing statement; if the period table-level characteristic is a write-read cross characteristic, updating a historical index corresponding to the current data table to obtain a reference index, and determining a reference execution duration and a reference execution plan of the current processing statement according to the reference index; and determining a target index corresponding to the current data table according to the reference execution duration, the reference execution plan and a preset index updating strategy. The invalid indexes in the database are reduced, and the performance of the database is improved.

Description

Technical Field

[0001] Embodiments of the present invention relate to the technical field of data processing, and in particular, to a method, device, equipment and medium for updating a database index. Background Art

[0002] In the development of business systems, the maintenance method of databases is usually based on the experience of developers to create indexes. There are cases of index abuse and unreasonable construction, resulting in some ineffective indexes that are not hit, wasting storage space, increasing database overhead, and affecting the performance of the database. Therefore, how to reduce ineffective indexes and improve the performance of the database is crucial. Summary of the Invention

[0003] The present invention provides a method, device, equipment and medium for updating a database index to reduce ineffective indexes and improve the performance of the database.

[0004] According to one aspect of the present invention, there is provided a method for updating a database index, including:

[0005] Obtaining a current processing statement from a preset statement execution queue, and determining a current table-level characteristic of a current data table corresponding to the current processing statement based on a preset target table-level container; wherein, the target table-level container is determined according to a preset initial monitoring container and candidate data tables in the database;

[0006] If the current table-level characteristic is a read-write cross characteristic, determining historical processing statements associated with the current data table within a preset historical period, and determining a period table-level characteristic of the current data table based on the historical processing statements;

[0007] If the period table-level characteristic is a write-read cross characteristic, updating a historical index corresponding to the current data table to obtain a reference index, and determining a reference execution duration and a reference execution plan of the current processing statement according to the reference index;

[0008] Determining a target index corresponding to the current data table according to the reference execution duration, the reference execution plan and a preset index update strategy.

[0009] According to another aspect of the present invention, there is provided a device for updating a database index, including:

[0010] A current table-level characteristic determination module, configured to obtain a current processing statement from a preset statement execution queue, and determine a current table-level characteristic of a current data table corresponding to the current processing statement based on a preset target table-level container; wherein, the target table-level container is determined according to a preset initial monitoring container and candidate data tables in the database;

[0011] A time period table-level feature determination module, configured to, if the current table-level feature is a read-write cross feature, determine the historical processing statements associated with the current data table within a preset historical time period, and based on the historical processing statements, determine the time period table-level feature of the current data table;

[0012] A reference index determination module, configured to, if the time period table-level feature is a write-read cross feature, update the historical index corresponding to the current data table to obtain a reference index, and based on the reference index, determine the reference execution duration and reference execution plan of the current processing statement;

[0013] A target index determination module, configured to determine the target index corresponding to the current data table according to the reference execution duration, the reference execution plan, and a preset index update strategy.

[0014] According to another aspect of the present invention, there is provided an electronic device, including:

[0015] One or more processors;

[0016] A memory, configured to store one or more programs;

[0017] When the one or more programs are executed by the one or more processors, the one or more processors are enabled to execute any one of the database index update methods provided by the embodiments of the present invention.

[0018] According to another aspect of the present invention, there is provided a computer-readable storage medium, storing computer instructions, where the computer instructions are used to cause a processor to implement any one of the database index update methods provided by the embodiments of the present invention when executed.

[0019] An embodiment of the present invention provides a database index update solution, which obtains the currently processed statement from a preset statement execution queue and determines the current table-level characteristics of the current data table corresponding to the currently processed statement based on a preset target table-level container; wherein, the target table-level container is determined according to a preset initial monitoring container and candidate data tables in the database; if the current table-level characteristics are read-write cross characteristics, determine the historical processed statements associated with the current data table within a preset historical period, and based on the historical processed statements, determine the period table-level characteristics of the current data table; if the period table-level characteristics are write-read cross characteristics, update the historical index corresponding to the current data table to obtain a reference index, and based on the reference index, determine the reference execution duration and reference execution plan of the currently processed statement; according to the reference execution duration, reference execution plan, and a preset index update policy, determine the target index corresponding to the current data table. In the above solution, by updating the historical index of the current data table according to the historical processed statements within a preset historical period to obtain a reference index, and then determining the target index based on the reference execution plan, reference execution duration determined by the reference index, and the preset index update policy, the index corresponding to the current data table is optimized, thereby reducing the invalid indexes in the database and improving the performance of the database.

[0020] It should be understood that the content described in this part is not intended to identify the key or important features of the embodiments of the present invention, nor is it used to limit the scope of the present invention. Other features of the present invention will become easily understood through the following description. BRIEF DESCRIPTION OF THE DRAWINGS

[0021] In order to more clearly illustrate the technical solutions in the embodiments of the present invention, the following will briefly introduce the drawings required for the description of the embodiments. Obviously, the drawings in the following description are only some embodiments of the present invention. For those of ordinary skill in the art, other drawings can be obtained based on these drawings without creative efforts.

[0022] Figure 1 is a flowchart of a method for updating a database index provided in Embodiment 1 of the present invention;

[0023] Figure 2 is a flowchart of a method for updating a database index provided in Embodiment 2 of the present invention;

[0024] Figure 3 is a schematic structural diagram of a device for updating a database index provided in Embodiment 4 of the present invention;

[0025] Figure 4 is a schematic structural diagram of an electronic device for implementing a method for updating a database index provided in Embodiment 5 of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0026] The present invention will be further described in detail below with reference to the accompanying drawings and embodiments. It can be understood that the specific embodiments described herein are only for explaining the present invention, rather than limiting the present invention. Additionally, it should be noted that for ease of description, only parts related to the present invention rather than all structures are shown in the drawings.

[0027] Embodiment 1

[0028] Figure 1 FIG. is a flowchart of a method for updating a database index provided in Embodiment 1 of the present invention. This embodiment is applicable to the situation of updating the indexes corresponding to each data table in the database. This method can be executed by a database index update device, which can be implemented in software and / or hardware and can be configured in an electronic device carrying the database index update function.

[0029] See Figure 1 The method for updating a database index shown in FIG. includes:

[0030] S110. Obtain the current processing statement from a preset statement execution queue, and determine the current table-level characteristics of the current data table corresponding to the current processing statement based on a preset target table-level container.

[0031] Among them, the statement execution queue can be used to store the initial processing statements executed during the current period, as well as the initial execution plans and initial execution durations corresponding to the initial processing statements. The initial processing statement refers to the processing statement executed during the current period. Exemplarily, the processing statement can be an SQL (Structured Query Language, a standard language for managing and operating relational databases) statement. The present invention does not limit the length of the current period in any way, and it can be set by those skilled in the art according to experience or needs.

[0032] Among them, the initial execution plan refers to the execution plan of the initial processing statement. The execution plan refers to a series of steps and operations generated for executing the processing statement. The initial execution duration refers to the time consumed for executing the initial processing statement.

[0033] Among them, the current processing statement refers to the initial processing statement currently used for index update. The target table-level container can be used to store candidate data table identifiers, candidate table-level characteristics, field types of candidate fields, and candidate indexes. The current data table refers to the candidate data table corresponding to the current processing statement. The current table-level characteristics refer to the processing characteristics of the current data table.

[0034] Exemplarily, the current data table identifier is extracted from the current processing statement, and the corresponding current data table is determined from each candidate data table in the database according to the current data table identifier, and the candidate table-level characteristics corresponding to the current data table identifier in the target table-level container are used as the current table-level characteristics. Among them, the candidate data tables refer to the data tables pre-stored in the database. The current data table identifier can be used to uniquely represent the identity of the current data table.

[0035] Exemplarily, the target table-level container is determined according to a preset initial monitoring container and candidate data tables in the database. Among them, the target monitoring container refers to the initial monitoring container used to store configuration file metadata and interface association data.

[0036] S120. If the current table-level characteristic is a read-write cross characteristic, determine the historical processing statements associated with the current data table within a preset historical period, and based on the historical processing statements, determine the period table-level characteristic of the current data table.

[0037] Among them, the read-write cross characteristic can be understood as a characteristic of more reads than writes, that is, among the candidate table processing statements corresponding to the current data table, the processing statements for reading data are more than the processing statements for writing data. The embodiments of the present invention do not make any limitation on the length of the preset historical period, which can be set by those skilled in the art according to experience or needs, or determined through a large number of experiments. The preset historical period can be understood as an adjacent historical period based on the time point of the current processing statement. Exemplarily, the preset historical period can be set according to the execution cycle of the processing statement. For example, if the time point of the current processing statement is 8 o'clock on the 12th, the preset historical period can be from 8 o'clock on the 11th to 8 o'clock on the 12th.

[0038] Among them, the historical processing statements refer to the previous candidate table processing statements associated with the current data table within the preset historical period. The period table-level characteristic refers to the processing characteristic of the current data table within the preset historical period. It should be noted that the current table-level characteristic and the period table-level characteristic of the current data table may be the same or different.

[0039] Exemplarily, based on the statement characteristics of the historical processing statements, the historical processing statements are divided to obtain historical read statements and historical write statements; determine the historical read quantity of the historical read statements and the historical write quantity of the historical write statements; and determine the period table-level characteristic according to the historical read quantity and the historical write quantity.

[0040] Further, if the historical read count is greater than the historical write count, the time period table-level characteristic is determined to be a read-write cross characteristic; if the historical read count is less than the historical write count, the time period table-level characteristic is determined to be a write-read cross characteristic. Herein, the historical read statement refers to the historical processing statement for reading data. The historical write statement refers to the historical processing statement for writing data. The historical read count refers to the number of historical read statements. The historical write count refers to the number of historical write statements.

[0041] S130. If the time period table-level characteristic is a write-read cross characteristic, update the historical index corresponding to the current data table to obtain a reference index, and determine the reference execution duration and reference execution plan of the current processing statement according to the reference index.

[0042] Herein, the write-read cross characteristic can be understood as a characteristic of more writes and fewer reads, that is, the processing statements for reading data in the historical processing statements corresponding to the current data table are fewer than the processing statements for writing data. The historical index refers to the index corresponding to each current field in the current data table within a preset historical time period.

[0043] Herein, the reference index refers to the index obtained after updating the historical index corresponding to the current data table. The reference execution duration refers to the duration consumed when the current processing statement is re-executed based on the reference index. The reference execution plan refers to the execution plan re-determined for the current processing statement based on the reference index.

[0044] In an alternative embodiment, updating the historical index corresponding to the current data table to obtain a reference index includes: deleting the historical index corresponding to each current field in the current data table, and respectively determining the access times of each current field within a preset historical time period; according to the access times, determining whether there is a current key field among the current fields; if so, performing index reconstruction based on the current key field to obtain a reference index.

[0045] Herein, the current field refers to the field in the current data table. The access times refer to the number of times the current field is accessed. For example, for any current field, the number of times the current field is queried as a condition and result within a preset historical time period is used as the access times of the current field.

[0046] Herein, the current key field refers to the current field with a relatively high access frequency within a preset historical time period. Exemplarily, for any current field, if the access times of the current field are greater than or equal to a preset access times threshold, the current field is determined to be a current key field; if the access times of the current field are less than the preset access times threshold, the current field is prohibited from being used as a current key field. The embodiments of the present invention do not make any limitation on the size of the preset access times threshold, which can be set by those skilled in the art according to experience or needs, or determined through a large number of experiments repeatedly.

[0047] Exemplarily, a combined index reconstruction is performed on the current keyword fields to obtain a reference index, which can be understood as that all the current keyword fields correspond to one reference index.

[0048] It can be understood that after deleting the historical index corresponding to the current data table, according to the access times of the current fields within a preset historical period, it is determined whether there are current keyword fields in the current fields. If so, the reference index corresponding to the current keyword fields can be determined, realizing the deletion of invalid indexes, reducing the number of invalid indexes. At the same time, by performing index reconstruction on the determined current key data, the accuracy and applicability of the reconstructed reference index are improved. Moreover, the embodiments of the present invention realize the dynamic adjustment of indexes, improving the performance of the database.

[0049] In another optional embodiment, updating the historical index corresponding to the current data table to obtain a reference index includes: deleting the historical indexes corresponding to the current fields in the current data table, and respectively determining the access times of the current fields within a preset historical period; according to the access times, determining whether there are current keyword fields in the current fields; if not, determining that the reference index is empty.

[0050] Exemplarily, if the time period table-level feature is a read-write cross feature, then the historical indexes corresponding to the current fields in the current data table are deleted, and at this time, the reference index corresponding to the current data table is empty.

[0051] S140. Determine the target index corresponding to the current data table according to the reference execution duration, the reference execution plan, and the preset index update policy.

[0052] Among them, the preset index update policy refers to a pre-set policy for controlling index iterative update. Exemplarily, the preset index update policy means that when the index coverage type in the execution plan of the current processing statement is not the full table scan type, the index update is stopped to obtain the target index corresponding to the current data table. The target index refers to the index obtained after optimizing the original index corresponding to the current data table.

[0053] In an optional embodiment, determining the target index corresponding to the current data table according to the reference execution duration, the reference execution plan, and the preset index update policy includes: according to the reference execution duration and the reference execution plan, determining whether the current processing statement is a processing statement to be updated; if the current processing statement is a processing statement to be updated, determining the update fields in the processing statement to be updated, and determining the index status of the update fields; if the index status is that there is no index, determining the current update index of the update fields, and performing iterative update on the current update index and the reference index according to the current update index, the reference index, and the preset index update policy to obtain the target index corresponding to the current data table.

[0054] Among them, the processing statement to be updated refers to the current processing statement that needs to update the index of the current data table again after determining the reference index of the current data table. The update field refers to the field in the processing statement to be updated that is used to update the index again. Exemplarily, the update field may include a conditional update field and a conditional association field. The conditional update field refers to the where conditional field, that is, the conditional field in the processing statement to be updated that is used to update the index. The conditional association field refers to other fields associated with the conditional update field.

[0055] Among them, the index status can be used to characterize whether there is a corresponding index for the update field. Exemplarily, the index status can be no index or there is an index. The current update index refers to the index corresponding to the update field. Exemplarily, a composite index reconstruction is performed on the update field to obtain the corresponding current update index.

[0056] In an alternative embodiment, determining that the current processing statement is a processing statement to be updated according to the reference execution duration and the reference execution plan includes: if the reference execution duration is greater than a preset duration threshold and the reference index coverage type in the reference execution plan is the full table scan type, then determine that the current processing statement is a processing statement to be updated.

[0057] The present invention does not limit the size of the preset duration threshold in any way. It can be set by those skilled in the art according to experience or needs, or determined through a large number of repeated experiments. Exemplarily, the preset duration threshold can be 500 ms.

[0058] Among them, the reference index coverage type refers to the type of scanning the current data table based on the current processing statement. The full table scan type refers to the type of comprehensively scanning the current data table.

[0059] It can be understood that by using the current processing statement with a reference execution duration greater than the preset duration threshold and being of the full table scan type as the processing statement to be updated, the accuracy of the determined processing statement to be updated is improved, and the accuracy of subsequent index update of the current data table based on the processing statement to be updated is improved.

[0060] In another alternative embodiment, if the reference execution duration is less than or equal to the preset duration threshold, then it is prohibited to use the current processing statement as a processing statement to be updated.

[0061] Exemplarily, if the index status is that there is an index, then use the reference index as the target index corresponding to the current data table.

[0062] It can be understood that by first determining whether the current processing statement is a processing statement to be updated, if so, determining the index status of the updated fields in the processing statement to be updated, when the index status is that there is no index, constructing the current update index corresponding to the updated fields, and iteratively updating the current update index and the reference index to obtain the target index corresponding to the current data table, the accuracy and effectiveness of the determined target index are improved.

[0063] It should be noted that if there is a reference index for the conditional update field d in the updated fields and there is no reference index for the conditional associated field, determine the execution position of the conditional update field d in the current processing statement. If the execution position is the foremost position, there is no need to construct the current update index corresponding to the conditional update field d; if the execution position is not the foremost position, construct the current update index corresponding to the conditional update field d according to the conditional update field and the conditional associated field. Here, the execution position refers to the position of the conditional update field in the current processing statement. The foremost position refers to the execution first position in the current processing statement.

[0064] The embodiment of the present invention provides a database index update scheme. By obtaining the current processing statement from a preset statement execution queue and determining the current table-level characteristics of the current data table corresponding to the current processing statement based on a preset target table-level container; where the target table-level container is determined according to a preset initial monitoring container and candidate data tables in the database; if the current table-level characteristics are read-write cross characteristics, determine the historical processing statements associated with the current data table within a preset historical period, and based on the historical processing statements, determine the period table-level characteristics of the current data table; if the period table-level characteristics are write-read cross characteristics, update the historical index corresponding to the current data table to obtain a reference index, and according to the reference index, determine the reference execution duration and reference execution plan of the current processing statement; according to the reference execution duration, reference execution plan and a preset index update strategy, determine the target index corresponding to the current data table. In the above scheme, by updating the historical index of the current data table according to the historical processing statements within a preset historical period to obtain a reference index, and then determining the target index according to the reference execution plan, reference execution duration determined by the reference index, and the preset index update strategy, the index corresponding to the current data table is optimized, thereby reducing the invalid indexes in the database and improving the performance of the database.

[0065] On the basis of the above technical solution, the method further includes: obtaining the current execution times of the current data table, and determining whether the current processing statement is a cached processing statement according to the reference execution duration and the current execution times; if so, determining the cached processing data of the cached processing statement, and adding the cached processing data to a pre-constructed data cache; where the cached processing data includes a cached processing template, cached processing parameters and a cached processing result.

[0066] Among them, the current execution times refer to the number of times the current data table is executed or accessed. The cache processing statement refers to the current processing statement that needs to be cached. The cache processing data refers to the data that needs to be cached corresponding to the cache processing statement. Exemplarily, the cache processing data may include a cache processing template, cache processing parameters, and a cache processing result.

[0067] Among them, the cache processing template refers to the processing statement template of the current processing statement. The cache processing parameters refer to the parameters that need to be added to the cache processing template in the current processing statement. The cache processing result refers to the processing result corresponding to the current processing statement.

[0068] Among them, the data buffer can be used to store cache processing data. Exemplarily, the data buffer may include a temporary data buffer and a long-term data buffer. The temporary data buffer can be used to store short-term or less frequently used cache processing data. The long-term data buffer can be used to store long-term or frequently used cache processing data.

[0069] Exemplarily, determining whether the current processing statement is a cache processing statement according to the reference execution duration and the current execution times includes: if the reference execution duration of the current processing statement is greater than a preset duration threshold and the current execution times are greater than a preset execution times threshold, then determine that the current processing statement is a cache processing statement; if the reference execution duration of the current processing statement is less than or equal to the preset duration threshold and the current execution times are less than or equal to the preset execution times threshold, then prohibit using the current processing statement as a cache processing statement.

[0070] Further, if the current processing statement is a cache processing statement, then sequentially query the temporary data buffer and the long-term data buffer according to the cache processing statement (that is, first query the temporary data buffer, and if it does not exist, then query the long-term data buffer), determine whether there is matching candidate cache data in the temporary data buffer and the long-term data buffer. If not, then store the cache processing statement in the temporary data buffer or the long-term data buffer; if it exists, then there is no need to store it in the data buffer. Among them, the candidate cache data refers to the cache data stored in the data buffer.

[0071] It can be understood that by determining whether the current processing statement is a cache processing statement according to the reference execution duration and the current execution times, if so, then determining the cache processing data of the cache processing statement and adding the cache processing data to a pre-constructed data buffer, the embodiment of the present invention realizes the caching of data by introducing the data buffer, improves the efficiency of determining the processing result of the processing statement based on the data buffer subsequently, that is, improves the efficiency of processing the processing statement.

[0072] Embodiment 2

[0073] Figure 2 It is a flowchart of a method for updating a database index provided in the second embodiment of the present invention. On the basis of the above embodiments, further, the operations of "obtaining the configuration file metadata of the database operation framework and the interface association data, and determining the target monitoring container according to the configuration file metadata, the interface association data of the candidate database interface, and the pre-constructed initial monitoring container are added; wherein, the interface association data includes the interface class name, the interface method, the interface processing statement, and the interface identifier; traversing the interface processing statements in the target monitoring container, and determining the data table identifier corresponding to the interface processing statement; constructing an initial table-level container including the data table identifier, and determining the candidate table processing statement corresponding to the data table identifier based on the data tables in the database; determining the candidate table-level characteristics corresponding to the corresponding data table identifier according to the function identifier data in the candidate table processing statement, and obtaining the candidate fields in the data table corresponding to the data table identifier from the database, and determining the field type of the candidate field and the candidate index of the candidate field; updating the initial table-level container based on the candidate table-level characteristics, the field type of the candidate field, and the candidate index to obtain the target table-level container" are performed to improve the determination mechanism of the target table-level container. It should be noted that for the parts not detailed in the embodiments of the present invention, reference may be made to the descriptions of other embodiments.

[0074] See Figure 2 The method for updating the database index shown in

[0075] S210. Obtain the configuration file metadata of the database operation framework and the interface association data of the candidate database interface, and determine the target monitoring container according to the configuration file metadata, the interface association data, and the pre-constructed initial monitoring container.

[0076] Wherein, the database operation framework refers to the framework for operating the database. Exemplarily, the database operation framework can be mybatis. The configuration file metadata refers to the data of the configuration file in the database operation framework. The candidate database interface refers to the interface for data interaction between the database and the database operation framework. Exemplarily, the candidate database interface can be mapper.

[0077] Wherein, the interface association data refers to the data associated with the candidate database interface. Exemplarily, the interface association data includes the interface class name, the interface method, the interface processing statement, and the interface identifier. The interface class name refers to the identity identifier of the class corresponding to the candidate database interface. The interface method refers to the method corresponding to the candidate database interface. The interface processing statement refers to the processing statement corresponding to the candidate database interface. The interface identifier can be used to uniquely represent the identity of the candidate database interface.

[0078] Among them, the initial monitoring container refers to a pre-constructed basic container for monitoring the processing statements between the database operation framework and the database. The target monitoring container refers to the initial monitoring container used to store the configuration file metadata and interface association data.

[0079] Specifically, obtain the configuration file metadata from the database operation framework and store the configuration file metadata in the initial monitoring container to obtain a candidate monitoring container; obtain the interface association data from the candidate list of candidate database interfaces and store the interface association data in the candidate monitoring container to obtain the target monitoring container.

[0080] Among them, the candidate monitoring container refers to the initial monitoring container that stores the configuration file metadata. The candidate list refers to the list used to provide the interface association data in the candidate database interfaces. Exemplarily, the candidate list may include the MapperRegistry list (Mapper registration list), the mappedStatements list (mapped statement list), and the MapperFactory list (Mapper factory list).

[0081] S220. Traverse the interface processing statements in the target monitoring container and determine the candidate data table identifier corresponding to the interface processing statement.

[0082] Among them, the candidate data table identifier refers to the data table identifier extracted from the interface processing statement. The candidate data table identifier can be used to uniquely represent the identity of the candidate data table.

[0083] S230. Construct an initial table-level container including the candidate data table identifier, and based on the candidate data tables in the database, determine the candidate table processing statement corresponding to the candidate data table identifier.

[0084] Among them, the initial table-level container refers to a basic container for storing the table-level data associated with the candidate data tables. The initial table-level container refers to the container that stores the candidate data table identifier. The candidate table processing statement refers to the processing statement associated with the candidate data table.

[0085] Specifically, for any candidate data table identifier, determine the candidate data table corresponding to the candidate data table identifier in the database, and use the processing statement associated with the candidate data table corresponding to the candidate data table identifier as the candidate table processing statement corresponding to the candidate data table identifier.

[0086] S240. According to the function identifier data in the candidate table processing statement, determine the candidate table-level characteristics corresponding to the corresponding candidate data table identifier, and obtain the candidate fields in the candidate data table corresponding to the candidate data table identifier from the database, and determine the field type of the candidate field and the candidate index of the candidate field.

[0087] Among them, the function identification data refers to the data that can be used to indicate the processing function of the candidate table processing statement. It should be noted that different processing functions correspond to different function identification data. The candidate table-level feature refers to the processing feature of the candidate data table corresponding to the candidate data table identifier. Exemplarily, the candidate table-level features may include a read-only feature, a write-only feature, a read-write cross feature, and a write-read cross feature.

[0088] Among them, the read-only feature can characterize that the candidate data table is only used for reading processing. The write-only feature can characterize that the candidate data table is only used for insertion processing. The read-write cross feature (i.e., the feature of more reads than writes) can characterize that the candidate data table is used for reading processing more times than for writing processing. The write-read cross feature (i.e., the feature of more writes than reads) can characterize that the candidate data table is used for writing processing more times than for reading processing.

[0089] Among them, the candidate field refers to the field in the candidate data table. The field type refers to the type of the candidate field. The candidate index refers to the index corresponding to the candidate field.

[0090] In an alternative embodiment, determining the candidate table-level feature corresponding to the corresponding candidate data table identifier according to the function identification data in the candidate table processing statement includes: for any candidate data table identifier, taking the candidate table processing statement associated with the candidate data table identifier as the target table processing statement, and determining the initial table-level feature corresponding to the candidate data table identifier based on the function identification data in the target table processing statement; if the initial table-level feature is a feature to be verified, determining the target interface identifier corresponding to the candidate data table identifier based on the target monitoring container, and obtaining the program call data of the corresponding target database interface according to the target interface identifier; verifying the initial table-level feature according to the program call data to determine the candidate table-level feature of the candidate data table identifier.

[0091] Among them, the target table processing statement refers to the candidate table processing statement corresponding to the candidate data table identifier. The initial table-level feature refers to the initial processing feature of the candidate data table corresponding to the candidate data table identifier. The feature to be verified refers to the initial table-level feature that needs to be verified. Exemplarily, the feature to be verified may be a write-read cross feature or a read-write cross feature.

[0092] Among them, the target interface identifier refers to the identifier of the target database interface corresponding to the candidate data table identifier. The target interface identifier can be used to uniquely represent the identity of the target database interface. The target database interface refers to the candidate database interface corresponding to the target interface identifier.

[0093] Among them, the program call data refers to the program data for the target database interface to interact. Exemplarily, the program call data may include the execution function data of the program for the target database interface to interact, such as the program call data may include program reading data or program writing data.

[0094] Exemplarily, for any candidate data table identifier A, at least one candidate table processing statement associated with the candidate data table identifier A is used as a target table processing statement; the function identifier data in each target table processing statement is determined respectively, and according to the function identifier data, the statement characteristics of the corresponding target table processing statement are determined; according to the statement characteristics of the target table processing statement, the initial table-level characteristics of the candidate data table identifier A are determined. Among them, the statement characteristics can be understood as the function types of the processing statements.

[0095] Specifically, if the statement characteristics of the target table processing statements associated with the candidate data table identifier A are all read characteristics, then it is determined that the initial table-level characteristics of the candidate data table identifier A are read-only characteristics; if the statement characteristics of the target table processing statements associated with the candidate data table identifier A are all write characteristics, then it is determined that the initial table-level characteristics of the candidate data table identifier A are write-only characteristics; if the statement characteristics of the target table processing statements associated with the candidate data table identifier A include read characteristics and write characteristics, then the number of target table processing statements with read characteristics and the number of target table processing statements with write characteristics are determined respectively; if the number of target table processing statements with read characteristics is greater than the number of target table processing statements with write characteristics, then it is determined that the initial table-level characteristics of the candidate data table identifier A are read-write cross characteristics; if the number of target table processing statements with read characteristics is less than the number of target table processing statements with write characteristics, then it is determined that the initial table-level characteristics of the candidate data table identifier A are write-read cross characteristics.

[0096] Illustrate with examples. For the characteristic of more reads than writes (i.e., read-write cross characteristics): there are more select statements and fewer insert, update, and delete statements; for the characteristic of more writes than reads (i.e., write-read cross characteristics): there are fewer select statements and more insert, update, and delete statements; for the write-only characteristic: there are only insert, update, and delete statements; for the read-only characteristic: there are only select statements.

[0097] Exemplarily, if the initial table-level feature is a write-read cross feature or a read-write cross feature, it is determined that the initial table-level feature is a feature to be verified; according to the target table processing statement and the target monitoring container, the target interface identifier corresponding to the candidate data table identifier A is determined; according to the target interface identifier, the target database interface is determined from the candidate database interfaces; the program call data of the target database interface is obtained; according to the program call data, the number of program reads and the number of program writes performed by the target database interface are determined; if the number of program reads is greater than the number of program writes, it is determined that the candidate table-level feature of the candidate data table identifier A is a read-write cross feature; if the number of program reads is less than the number of program writes, it is determined that the candidate table-level feature of the candidate data table identifier A is a write-read cross feature.

[0098] Illustratively, through the call code of the statement, reasoning is performed again, mainly for the cases of more reads than writes and more writes than reads; for the initial table-level feature of more reads than writes: if there are a large number of calls related to writes in the code, that is, fewer reads are associated and more writes are associated, then the target table-level feature is more writes than reads; for the initial table-level feature of more writes than reads: if there are a large number of reads associated and fewer writes are associated in the code, then the target table-level feature is more reads than writes.

[0099] It can be understood that by verifying the feature to be verified, the situation where the initial table-level feature determined directly according to the function identifier data is incorrect is avoided, and the accuracy of the determined target table-level feature is improved.

[0100] S250. Update the initial table-level container based on the candidate table-level feature, the field type of the candidate field, and the candidate index to obtain the target table-level container.

[0101] Among them, the target table-level container can be used to store the candidate data table identifier, the candidate table-level feature, the field type of the candidate field, and the candidate index.

[0102] Specifically, the candidate table-level feature, the field type, and the candidate index are stored at the corresponding positions of the candidate data table identifier in the initial table-level container to obtain the target table-level container.

[0103] It should be noted that the candidate index corresponding to a certain candidate field in the candidate data table may be empty, that is, there is no corresponding candidate index for the candidate field. Therefore, there may be empty candidate indexes in the target table-level container.

[0104] S260. Obtain the current processing statement from the preset statement execution queue, and determine the current table-level feature of the current data table corresponding to the current processing statement based on the preset target table-level container.

[0105] Among them, the target table-level container is determined according to a preset initial monitoring container and candidate data tables in the database.

[0106] S260. If the current table-level feature is a read-write cross feature, determine the historical processing statements associated with the current data table within a preset historical period, and based on the historical processing statements, determine the period table-level feature of the current data table.

[0107] S280. If the period table-level feature is a write-read cross feature, update the historical index corresponding to the current data table to obtain a reference index, and based on the reference index, determine the reference execution duration and reference execution plan of the current processing statement.

[0108] S290. Determine the target index corresponding to the current data table according to the reference execution duration, reference execution plan, and a preset index update strategy.

[0109] The embodiment of the present invention provides a database index update solution. By adding the configuration file metadata of the database operation framework and the interface association data of the candidate database interface, and determining the target monitoring container according to the configuration file metadata, interface association data, and a pre-constructed initial monitoring container; among them, the interface association data includes an interface class name, an interface method, an interface processing statement, and an interface identifier; traverse the interface processing statements in the target monitoring container, and determine the data table identifier corresponding to the interface processing statement; construct an initial table-level container including the data table identifier, and based on the data tables in the database, determine the candidate table processing statements corresponding to the data table identifier; according to the function identifier data in the candidate table processing statements, determine the candidate table-level features corresponding to the corresponding data table identifier, and obtain the candidate fields in the data table corresponding to the data table identifier from the database, and determine the field type of the candidate field and the candidate index of the candidate field; based on the candidate table-level features, the field type of the candidate field, and the candidate index, update the initial table-level container to obtain a target table-level container operation, improving the determination mechanism of the target table-level container. In the above solution, by obtaining the configuration file metadata of the database operation framework and the interface association data of the candidate database interface, the target monitoring container is determined, improving the comprehensiveness and accuracy of the data in the determined target monitoring container, and further improving the accuracy and comprehensiveness of the data in the target table-level container determined subsequently based on the interface processing statements and candidate data tables in the target monitoring container; at the same time, in the embodiment of the present invention, the candidate data table identifier determined by the interface processing statement is used to reverse deduce the candidate table processing statement according to the candidate data table corresponding to the candidate data table identifier, further improving the comprehensiveness of the data in the target table-level container.

[0110] Embodiment III

[0111] Based on the above embodiments, an alternative example is provided in the embodiments of the present invention. It should be noted that for the parts not detailed in the embodiments of the present invention, reference can be made to the descriptions of other embodiments.

[0112] In the development of business systems, the maintenance method of databases is usually based on the experience of developers to create indexes, resulting in the abuse and unreasonable construction of indexes. There are some invalid indexes that are not hit, wasting storage space and increasing database overhead. Moreover, there may be some SQL statements that do not hit indexes, resulting in slow query speed, increasing database pressure, easily causing database failures, and damaging business. In addition, the performance of the traditional database table maintenance method needs to be improved, especially for the maintenance of a large amount of data, there is a lack of efficient maintenance means and effects.

[0113] The method for updating database indexes provided by the embodiments of the present invention realizes the dynamic priority execution of SQL statements in data tables by dynamically managing SQL indexes.

[0114] Exemplarily, first, perform table metadata optimization, that is, construct a target table-level container. Specifically, construct an SQL (Structured Query Language) monitor context SQLMonitorContext (i.e., the initial monitor container), and add it to the Spring lifecycle process. Customize MybatisAutoConfiguration (i.e., the automatic registrar) to take over the automatic registration behavior of mybatis (i.e., the database operation framework). By extracting the metadata of the mybatis configuration file and putting it into SQLMonitorContext, a candidate monitor container is obtained. During the initialization completion of the Spring lifecycle, extract the MapperRegistry list and the mappedStatements list, and construct a composite object of the Mapper class name (i.e., the interface class name), method (i.e., the interface method), and SQL statement (i.e., the interface processing statement). At the same time, extract the Mapper object (i.e., the interface identifier) created by the corresponding MapperFactory from MapperRegistry and put it into the monitor context to obtain the target monitor container. Through the composite object in the monitor context, traverse all SQL statements (i.e., traverse the interface processing statements in the target monitor container), and extract the table names of the SQL statements (i.e., the candidate data table identifiers). Establish a table-level container TC (i.e., the initial table-level container) centered on the table name, and query all statements of the table in mappedStatements (i.e., determine the candidate table processing statements associated with the candidate data table). Infer the characteristics of the database table from the statements (i.e., determine the target table-level characteristics): marks of characteristics such as more reads than writes, more writes than reads, and write-only. At the same time, according to the table name, obtain the metadata information of the candidate data table identifier through database query, and determine the type of table fields (i.e., the field type) and whether there is an index (i.e., determine whether there is a candidate index). Among them, the automatic registrar is used to obtain the metadata of the configuration file from the database operation framework.

[0115] Exemplarily, perform SQL statement execution monitoring, that is, intercept by constructing an application-level Executor (executor). In the interceptor, extract the SQL statements executed by the application (i.e., the initial processing statements), and establish a listener for this SQL statement. In the listener, collect the data execution plan of the SQL statement (i.e., the initial execution plan) and the execution time of the SQL statement (i.e., the initial execution duration), and construct an SQL execution log queue (i.e., the statement execution queue), and put the execution information of the SQL statement into the queue.

[0116] Exemplarily, SQL-aware optimization means listening to the SQL statement execution queue, obtaining SQL execution log information, extracting the table name of the SQL statement (i.e., the current processing statement) (the current data table identifier), obtaining the metadata information of the table in the TC container (i.e., the target table-level container), and performing database optimization according to the characteristics of the table (i.e., the current table-level characteristics).

[0117] Optionally, if the current table-level characteristic is a write-only characteristic, then by obtaining all the current fields of the current data table and determining whether there is an index, if there is, delete the index to improve the data insertion efficiency.

[0118] Alternatively, optionally, if the current table-level characteristic is a read-write cross characteristic (i.e., a characteristic of more reads and less writes), then build a read and write counter and a time period in the TC container (i.e., the target table-level container), analyze the read and write time periodicity, build an execution scheduler according to the period, and manage the indexes of the candidate data tables. If it is analyzed that the current data table has more inserts and very few reads in a preset historical period, delete the historical indexes of the current data table in the preset historical period; if several fields are used as conditions and results for queries more frequently in the preset historical period, reconstruct the index according to these fields (i.e., the current key fields) to build a composite index (i.e., the reference index); through the reference index, determine the execution time of the SQL statement (the current processing statement) (i.e., the reference execution duration). If the SQL execution time (i.e., the reference execution duration) is greater than 500 milliseconds, determine whether there is a full table scan in the type of Type in the reference execution plan. If so, extract the where condition fields and associated fields in the SQL statement (i.e., extract the updated fields), check whether there is an index for the updated fields. If not, build a combined index (i.e., the current update index) according to the updated fields, conduct a probe, and obtain the execution plan again until there is no full table scan in the execution plan of the SQL statement, and obtain the target index of the current data table.

[0119] It should be noted that for the read-write cross characteristic, while determining the reference execution duration based on the reference index, obtain the current execution times of the current data table based on the counter. If the current execution times are relatively large, in the Executor interception, extract the hash value with the SQL template (i.e., the cached processing data) + parameters (i.e., the cached processing parameters), and build a timed data cache (i.e., the temporary data cache) and an LRU data cache (i.e., the long-term data cache). When querying the SQL statement, first hit the timed data cache and the LRU data cache, and subsequently, the processing of the SQL statement can be realized based on the timed data cache and the LRU data cache. If the corresponding processing result is matched, directly return; if not, continue to process the SQL statement by accessing the database. The advantage is that it improves the processing efficiency of SQL statements under the read-write cross characteristic.

[0120] Alternatively, for the case of more writes than reads, the difference from the case of more reads than writes is that no timed data cache and LRU data cache are built, and the indexes of the table are dynamically managed through the execution period of the SQL statement. Specifically, if the current table-level characteristic is the write-read cross characteristic (i.e., the characteristic of more writes than reads), then read and write counters and time periods are built in the TC container (i.e., the target table-level container), and the read and write time periodicity is analyzed. An execution scheduler is built according to the period to manage the indexes of the candidate data tables. If it is analyzed that the current data table has a large number of inserts and very few reads within a preset historical period, the historical indexes of the current data table within the preset historical period are deleted; if a certain number of fields are frequently queried as conditions and results within the preset historical period, the indexes are reconstructed according to these fields (i.e., the current key fields) to build a composite index (i.e., the reference index); through the reference index, the execution time of the SQL statement (the current processing statement) is determined (i.e., the reference execution duration). If the SQL execution time (i.e., the reference execution duration) is greater than 500 milliseconds, it is determined whether there is a full table scan in the type of Type in the reference execution plan. If so, the where condition fields and associated fields (i.e., the updated fields) of the SQL statement are extracted to check whether there is an index for the updated fields. If not, a combined index (i.e., the current update index) is built according to the updated fields for probing, and the execution plan is obtained again until there is no full table scan in the execution plan of the SQL statement, and the target index of the current data table is obtained.

[0121] Embodiment 4

[0122] Figure 3 FIG. 9 is a schematic structural diagram of an apparatus for updating a database index according to Embodiment 4 of the present invention. This embodiment is applicable to the case of updating the indexes corresponding to each data table in the database. This method can be executed by an apparatus for updating a database index, and the apparatus can be implemented in a software and / or hardware manner and can be configured in an electronic device carrying the function of updating a database index.

[0123] As Figure 3 shown, the apparatus includes: a current table-level characteristic determination module 310, a time-period table-level characteristic determination module 320, a reference index determination module 330, and a target index determination module 340. Among them,

[0124] The current table-level characteristic determination module 310 is configured to obtain a current processing statement from a preset statement execution queue and determine the current table-level characteristic of the current data table corresponding to the current processing statement based on a preset target table-level container; wherein, the target table-level container is determined according to a preset initial monitoring container and the data tables in the database;

[0125] The time period table-level feature determination module 320 is configured to, if the current table-level feature is a read-write cross feature, determine the historical processing statements associated with the current data table within a preset historical time period, and based on the historical processing statements, determine the time period table-level feature of the current data table;

[0126] The reference index determination module 330 is configured to, if the time period table-level feature is a write-read cross feature, update the historical index corresponding to the current data table to obtain a reference index, and based on the reference index, determine the reference execution duration and the reference execution plan of the current processing statement;

[0127] The target index determination module 340 is configured to determine the target index corresponding to the current data table according to the reference execution duration, the reference execution plan, and a preset index update policy.

[0128] An embodiment of the present invention provides a database index update solution. The current processing statement is obtained from a preset statement execution queue, and based on a preset target table-level container, the current table-level feature of the current data table corresponding to the current processing statement is determined; wherein, the target table-level container is determined according to a preset initial monitoring container and candidate data tables in the database; if the current table-level feature is a read-write cross feature, the historical processing statements associated with the current data table within a preset historical time period are determined, and based on the historical processing statements, the time period table-level feature of the current data table is determined; if the time period table-level feature is a write-read cross feature, the historical index corresponding to the current data table is updated to obtain a reference index, and based on the reference index, the reference execution duration and the reference execution plan of the current processing statement are determined; according to the reference execution duration, the reference execution plan, and a preset index update policy, the target index corresponding to the current data table is determined. In the above solution, by updating the historical index of the current data table according to the historical processing statements within a preset historical time period to obtain a reference index, and then determining the target index according to the reference execution plan, the reference execution duration determined by the reference index, and a preset index update policy, the index corresponding to the current data table is optimized, thereby reducing the invalid indexes in the database and improving the performance of the database.

[0129] Optionally, the target table-level container is determined based on the following device:

[0130] The target monitoring container determination module is configured to obtain the configuration file metadata of the database operation framework and the interface association data of the candidate database interface, and determine a target monitoring container according to the configuration file metadata, the interface association data, and a pre-constructed initial monitoring container; wherein, the interface association data includes an interface class name, an interface method, an interface processing statement, and an interface identifier;

[0131] A candidate data table identifier determination module, configured to traverse interface processing statements in the target monitoring container and determine candidate data table identifiers corresponding to the interface processing statements;

[0132] A candidate table processing statement determination module, configured to construct an initial table-level container including the candidate data table identifiers, and determine candidate table processing statements corresponding to the candidate data table identifiers based on candidate data tables in a database;

[0133] A candidate index determination module, configured to determine candidate table-level characteristics corresponding to corresponding candidate data table identifiers according to function identifier data in the candidate table processing statements, and obtain candidate fields in candidate data tables corresponding to the candidate data table identifiers from the database, determine field types of the candidate fields and candidate indexes of the candidate fields;

[0134] A target table-level container determination module, configured to update the initial table-level container based on the candidate table-level characteristics, field types of the candidate fields, and the candidate indexes to obtain a target table-level container.

[0135] Optionally, the candidate index determination module is specifically configured to:

[0136] For any candidate data table identifier, use the candidate table processing statement associated with the candidate data table identifier as a target table processing statement, and determine initial table-level characteristics corresponding to the candidate data table identifier based on the function identifier data in the target table processing statement;

[0137] If the initial table-level characteristics are characteristics to be verified, determine a target interface identifier corresponding to the candidate data table identifier based on the target monitoring container, and obtain program call data of a corresponding target database interface according to the target interface identifier;

[0138] Verify the initial table-level characteristics according to the program call data to determine candidate table-level characteristics of the candidate data table identifier.

[0139] Optionally, the target index determination module 340 includes:

[0140] A to-be-updated processing statement determination unit, configured to determine whether the current processing statement is a to-be-updated processing statement according to the reference execution duration and the reference execution plan;

[0141] An index status determination unit, configured to, if the current processing statement is the to-be-updated processing statement, determine updated fields in the to-be-updated processing statement and determine index statuses of the updated fields;

[0142] A target index determination unit, configured to determine a current update index of the update field if the index status is that there is no index, and iteratively update the current update index according to the current update index, the reference index, and the preset index update policy to obtain a target index corresponding to the current data table.

[0143] Optionally, the statement to be updated determination unit is specifically configured to:

[0144] If the reference execution duration is greater than a preset duration threshold and the reference index coverage type in the reference execution plan is a full table scan type, determine the current processing statement as the statement to be updated.

[0145] Optionally, the apparatus further includes:

[0146] A cache processing statement determination module, configured to obtain a current execution count of the current data table and determine whether the current processing statement is a cache processing statement according to the reference execution duration and the current execution count;

[0147] A data cache storage module, configured to, if so, determine cache processing data of the cache processing statement and add the cache processing data to a pre-constructed data cache; wherein, the cache processing data includes a cache processing template, cache processing parameters, and a cache processing result.

[0148] Optionally, the reference index determination module 330 includes:

[0149] An access count determination unit, configured to delete historical indexes corresponding to each current field in the current data table and respectively determine access counts of each current field within the preset historical period;

[0150] A current key field determination unit, configured to determine whether there is a current key field in the current fields according to the access counts;

[0151] A reference index determination unit, configured to, if any, perform index reconstruction based on the current key field to obtain a reference index.

[0152] The database index update apparatus provided by an embodiment of the present invention can execute the database index update method provided by any embodiment of the present invention, and has function modules and beneficial effects corresponding to executing each database index update method.

[0153] In the technical solution of the present invention, the collection, storage, use, processing, transmission, provision, and disclosure of the current processing statement, interface associated data, configuration file metadata, etc. comply with the provisions of relevant laws and regulations and do not violate public order and good customs.

[0154] Embodiment 5

[0155] Figure 4 It is a schematic structural diagram of an electronic device for implementing an update method of a database index provided in Embodiment 5 of the present invention. The electronic device 410 is intended to represent various forms of digital computers, such as, laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as, personal digital processors, cellular phones, smart phones, wearable devices (such as helmets, glasses, watches, etc.) and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely illustrative and are not intended to limit the implementation of the present invention described and / or claimed herein.

[0156] As Figure 4 shown, the electronic device 410 includes at least one processor 411, and a memory communicatively connected to the at least one processor 411, such as a read-only memory (ROM) 412, a random access memory (RAM) 413, etc. Among them, the memory stores a computer program executable by the at least one processor. The processor 411 can perform various appropriate actions and processes according to the computer program stored in the read-only memory (ROM) 412 or the computer program loaded from the storage unit 418 into the random access memory (RAM) 413. In the RAM 413, various programs and data required for the operation of the electronic device 410 can also be stored. The processor 411, the ROM 412, and the RAM 413 are connected to each other through a bus 414. The input / output (I / O) interface 415 is also connected to the bus 414.

[0157] Multiple components in the electronic device 410 are connected to the I / O interface 415, including: an input unit 416, such as a keyboard, a mouse, etc.; an output unit 417, such as various types of displays, speakers, etc.; a storage unit 418, such as a magnetic disk, an optical disk, etc.; and a communication unit 419, such as a network card, a modem, a wireless communication transceiver, etc. The communication unit 419 allows the electronic device 410 to exchange information / data with other devices through a computer network such as the Internet and / or various telecommunication networks.

[0158] The processor 411 may be various general-purpose and / or special-purpose processing components with processing and computing capabilities. Some examples of the processor 411 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various dedicated artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any suitable processor, controller, microcontroller, etc. The processor 411 executes the various methods and processes described above, such as the method for updating the database index.

[0159] In some embodiments, the method for updating the database index may be implemented as a computer program tangibly embodied in a computer-readable storage medium, such as the storage unit 418. In some embodiments, part or all of the computer program may be loaded and / or installed onto the electronic device 410 via the ROM 412 and / or the communication unit 419. When the computer program is loaded into the RAM 413 and executed by the processor 411, one or more steps of the method for updating the database index described above may be executed. Alternatively, in other embodiments, the processor 411 may be configured to execute the method for updating the database index by any other suitable means (e.g., by means of firmware).

[0160] Various embodiments of the systems and techniques described above in this document may be implemented in digital electronic circuitry, integrated circuit systems, field programmable gate arrays (FPGA), application specific integrated circuits (ASIC), application specific standard products (ASSP), systems on a chip (SOC), complex programmable logic devices (CPLD), computer hardware, firmware, software, and / or combinations thereof. These various embodiments may include: implemented in one or more computer programs that may be executed and / or interpreted on a programmable system including at least one programmable processor, which may be a special-purpose or general-purpose programmable processor that receives data and instructions from a storage system, at least one input device, and at least one output device, and transmits the data and instructions to the storage system, the at least one input device, and the at least one output device.

[0161] The computer program for implementing the method of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to the processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when the computer programs are executed by the processor, the functions / operations specified in the flowchart and / or block diagram are implemented. The computer programs may be executed entirely on the machine, partially on the machine, as a stand-alone software package partially on the machine and partially on a remote machine, or entirely on a remote machine or server.

[0162] In the context of the present invention, a computer-readable storage medium can be a tangible medium that can contain or store a computer program for use by or in connection with an instruction execution system, apparatus, or device. The computer-readable storage medium can include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination of the foregoing. Alternatively, the computer-readable storage medium can be a machine-readable signal medium. More specific examples of the machine-readable storage medium would include an electrical connection based on one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or Flash memory), an optical fiber, a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

[0163] To provide for interaction with a user, the systems and techniques described herein can be implemented on an electronic device having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and a pointing device (e.g., a mouse or a trackball) by which the user can provide input to the electronic device. Other kinds of devices can also be used to provide for interaction with the user; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including acoustic input, speech input, or tactile input).

[0164] The systems and techniques described herein can be implemented in a computing system that includes back-end components (e.g., as a data server), or a computing system that includes middleware components (e.g., an application server), or a computing system that includes front-end components (e.g., a user computer having a graphical user interface or a web browser through which the user can interact with an implementation of the systems and techniques described herein), or a computing system that includes any combination of such back-end components, middleware components, or front-end components. The components of the system can be interconnected by any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include: a local area network (LAN), a wide area network (WAN), a blockchain network, and the Internet.

[0165] A computing system may include a client and a server. The client and the server are generally far from each other and usually interact via a communication network. The client-server relationship is created by computer programs running on respective computers and having a client-server relationship with each other. The server may be a cloud server, also known as a cloud computing server or a cloud host, which is a host product in the cloud computing service system, solving the defects of difficult management and weak business scalability existing in traditional physical hosts and VPS services.

[0166] It should be understood that various forms of processes shown above can be used, with steps reordered, added or deleted. For example, the steps described in the present invention can be executed in parallel, sequentially or in a different order, as long as the desired results of the technical solution of the present invention can be achieved, and no limitation is made herein.

[0167] The above specific embodiments do not constitute a limitation on the protection scope of the present invention. Those skilled in the art should understand that various modifications, combinations, sub-combinations and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions and improvements made within the spirit and principles of the present invention shall be included within the protection scope of the present invention.

Claims

1. A method for updating a database index, characterized in that, Including: Obtain the currently processed statement from a preset statement execution queue, and determine the current table-level characteristics of the current data table corresponding to the currently processed statement based on a preset target table-level container; wherein, the target table-level container is determined according to a preset initial monitoring container and candidate data tables in the database; If the current table-level characteristic is a read-write cross characteristic, determine the historical processed statements associated with the current data table within a preset historical period, and determine the period table-level characteristics of the current data table based on the historical processed statements; If the period table-level characteristic is a write-read cross characteristic, update the historical index corresponding to the current data table to obtain a reference index, and determine the reference execution duration and reference execution plan of the current processed statement according to the reference index; Determine the target index corresponding to the current data table according to the reference execution duration, the reference execution plan, and a preset index update strategy.

2. The method for updating a database index according to claim 1, wherein The target table-level container is determined based on the following method: Obtain the configuration file metadata of the database operation framework and the interface association data of the candidate database interfaces, and determine the target monitoring container according to the configuration file metadata, the interface association data, and a pre-constructed initial monitoring container; wherein, the interface association data includes interface class names, interface methods, interface processed statements, and interface identifiers; Traverse the interface processed statements in the target monitoring container, and determine the candidate data table identifiers corresponding to the interface processed statements; Construct an initial table-level container including the candidate data table identifiers, and determine the candidate table processed statements corresponding to the candidate data table identifiers based on the candidate data tables in the database; Determine the candidate table-level characteristics corresponding to the corresponding candidate data table identifiers according to the function identifier data in the candidate table processed statements, and obtain the candidate fields in the candidate data tables corresponding to the candidate data table identifiers from the database, and determine the field types of the candidate fields and the candidate indexes of the candidate fields; Update the initial table-level container based on the candidate table-level characteristics, the field types of the candidate fields, and the candidate indexes to obtain the target table-level container.

3. The method for updating a database index according to claim 2, wherein The determining the candidate table-level characteristics corresponding to the corresponding candidate data table identifiers according to the function identifier data in the candidate table processed statements includes: For any candidate data table identifier, use the candidate table processed statement associated with the candidate data table identifier as the target table processed statement, and determine the initial table-level characteristics corresponding to the candidate data table identifier based on the function identifier data in the target table processed statement; If the initial table-level characteristic is a characteristic to be verified, determine the target interface identifier corresponding to the candidate data table identifier based on the target monitoring container, and obtain the program call data of the corresponding target database interface according to the target interface identifier; Verify the initial table-level characteristic according to the program call data to determine the candidate table-level characteristics of the candidate data table identifier.

4. The method for updating a database index according to claim 1, wherein The determining the target index corresponding to the current data table according to the reference execution duration, the reference execution plan, and a preset index update strategy includes: Determine whether the current processing statement is a processing statement to be updated according to the reference execution duration and the reference execution plan; If the current processing statement is the processing statement to be updated, determine the update fields in the processing statement to be updated, and determine the index status of the update fields; If the index status is that there is no index, determine the current update index of the update field, and iteratively update the current update index according to the current update index, the reference index, and the preset index update strategy to obtain the target index corresponding to the current data table.

5. The method for updating a database index according to claim 4, wherein Determining that the current processing statement is a processing statement to be updated according to the reference execution duration and the reference execution plan includes: If the reference execution duration is greater than the preset duration threshold and the reference index coverage type in the reference execution plan is the full table scan type, determine that the current processing statement is a processing statement to be updated.

6. The method for updating a database index according to claim 4, wherein The method further includes: Obtain the current execution times of the current data table, and determine whether the current processing statement is a cached processing statement according to the reference execution duration and the current execution times; If so, determine the cached processing data of the cached processing statement, and add the cached processing data to a pre-constructed data buffer; wherein, the cached processing data includes a cached processing template, cached processing parameters, and a cached processing result.

7. The method for updating a database index according to any one of claims 1-6, characterized in that, The updating the historical index corresponding to the current data table to obtain a reference index includes: Delete the historical indexes corresponding to each current field in the current data table, and respectively determine the access times of each current field within the preset historical period; Determine whether there is a current key field in the current fields according to the access times; If it exists, perform index reconstruction based on the current key field to obtain a reference index.

8. An update device for a database index, characterized in that, Including: A current table-level feature determination module, configured to obtain a current processing statement from a preset statement execution queue, and determine the current table-level feature of the current data table corresponding to the current processing statement based on a preset target table-level container; wherein, the target table-level container is determined according to a preset initial monitoring container and candidate data tables in the database; A period table-level feature determination module, configured to, if the current table-level feature is a read-write cross feature, determine the historical processing statements associated with the current data table within a preset historical period, and determine the period table-level feature of the current data table based on the historical processing statements; A reference index determination module, configured to, if the period table-level feature is a write-read cross feature, update the historical index corresponding to the current data table to obtain a reference index, and determine the reference execution duration and the reference execution plan of the current processing statement according to the reference index; A target index determination module, configured to determine the target index corresponding to the current data table according to the reference execution duration, the reference execution plan, and a preset index update strategy.

9. An electronic device, characterized in that, Including: One or more processors; A memory for storing one or more programs; When the one or more programs are executed by the one or more processors, the one or more processors implement a method for updating a database index as described in any one of claims 1-7.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements a method for updating a database index as described in any one of claims 1-7.

Images