Database index updating method, device, equipment and medium

Abstract

The embodiment of the present invention discloses a method, device, equipment and medium for updating a database index. The method includes: obtaining a 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; if the current table-level characteristics are read-write cross characteristics, determining the historical processing statements associated with the current data table within a preset historical period, and determining the period table-level characteristics of the current data table based on the historical processing statements; if the period table-level characteristics are write-read cross characteristics, updating the historical index corresponding to the current data table to obtain a reference index, and determining the reference execution time and reference execution plan of the current processing statement based on the reference index; determining the target index corresponding to the current data table based on the reference execution time, reference execution plan and preset index update strategy. Invalid indexes in the database are reduced, and the performance of the database is improved.

Description

Technical Field

[0001] The embodiments of the present invention relate to the field of data processing technology, and in particular to a method, apparatus, device and medium for updating a database index. Background Art

[0002] During business system development, database maintenance is often based on developer experience, creating indexes. This can lead to misuse and irrational index construction, resulting in invalid indexes that are not hit, wasting storage space, increasing database overhead, and impacting database performance. Therefore, reducing invalid indexes and improving database performance is crucial. Summary of the Invention

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

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

[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 based on a preset initial monitoring container and candidate data tables in a database;

[0006] If the current table-level characteristic is a read-write cross characteristic, determining a historical processing statement 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 statement;

[0007] If the time period table-level characteristic is a write-read cross characteristic, the historical index corresponding to the current data table is updated to obtain a reference index, and a reference execution duration and a reference execution plan of the current processing statement are determined based on the reference index;

[0008] A target index corresponding to the current data table is determined 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, comprising:

[0010] A current table-level property determination module is configured to obtain a current processing statement from a preset statement execution queue and determine the current table-level property 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 based on a preset initial monitoring container and candidate data tables in the database;

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

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

[0013] The target index determination module is used to determine the target index corresponding to the current data table according to the reference execution time, the reference execution plan and the preset index update strategy.

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

[0015] one or more processors;

[0016] a memory for storing one or more programs;

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

[0018] According to another aspect of the present invention, a computer-readable storage medium is provided. The computer-readable storage medium stores computer instructions, which are used to enable a processor to implement any database index updating method provided by an embodiment of the present invention when executed.

[0019] An embodiment of the present invention provides a database index update solution, which obtains a current processing statement from a preset statement execution queue and determines the current table-level characteristics 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 based on a preset initial monitoring container and a candidate data table in a database; if the current table-level characteristic is a read-write cross characteristic, 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 characteristics of the current data table are determined; if the time period table-level characteristic is a write-read cross characteristic, the historical index corresponding to the current data table is updated to obtain a reference index, and based on the reference index, a reference execution time and a reference execution plan of the current processing statement are determined; based on the reference execution time, the reference execution plan and the preset index update strategy, the target index corresponding to the current data table is determined. The above solution updates the historical index of the current data table according to the historical processing statements within the preset historical period to obtain a reference index, and then determines the target index based on the reference execution plan and reference execution time determined by the reference index, as well as the preset index update strategy, thereby optimizing the index corresponding to the current data table, thereby reducing invalid indexes in the database and improving database performance.

[0020] It should be understood that the content described in this section is not intended to identify the key or important features of the embodiments of the present invention, nor is it intended to limit the scope of the present invention. Other features of the present invention will become readily 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 briefly introduces the drawings required for use in the description of the embodiments. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work.

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

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

[0024] Figure 3 This is a schematic diagram of the structure of a database index updating device provided by the fourth embodiment of the present invention;

[0025] Figure 4 3 is a structural diagram of an electronic device for implementing a method for updating a database index provided in a fifth embodiment of the present invention. DETAILED DESCRIPTION

[0026] The present invention will be further described in detail below with reference to the accompanying drawings and examples. It will be understood that the specific embodiments described herein are intended only to illustrate the present invention and are not intended to limit the present invention. It should also be noted that, for ease of description, the accompanying drawings only illustrate portions relevant to the present invention, not all structures.

[0027] Example 1

[0028] Figure 1 This is a flowchart of the database index updating method provided in the first embodiment of the present invention. This embodiment is applicable to the situation where the indexes corresponding to each data table in the database are updated. The method can be executed by a database index updating device, which can be implemented in software and / or hardware and can be configured in an electronic device that carries the database index updating function.

[0029] See also Figure 1 The database index update method shown includes:

[0030] S110 : 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.

[0031] The statement execution queue can be used to store initial processing statements executed within the current time period, as well as the initial execution plan and initial execution duration corresponding to the initial processing statements. Initial processing statements refer to processing statements executed within the current time period. For example, processing statements can be SQL (Structured Query Language, a standard language for managing and operating relational databases) statements. The embodiments of the present invention do not impose any restrictions on the length of the current time period; technicians can set the length based on experience or needs.

[0032] The initial execution plan refers to the execution plan for the initial processing statement. The execution plan is the series of steps and operations generated to execute the processing statement. The initial execution duration refers to the time it takes to execute the initial processing statement.

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

[0034] For example, a current data table identifier is extracted from the currently processed statement. Based on the current data table identifier, the corresponding current data table is determined from candidate data tables in the database. The candidate table-level attribute corresponding to the current data table identifier in the target table-level container is used as the current table-level attribute. The candidate data table refers to a data table pre-stored in the database. The current data table identifier can be used to uniquely identify the current data table.

[0035] Exemplarily, the target table-level container is determined based on a preset initial monitoring container and candidate data tables in the database, wherein the target monitoring container refers to an initial monitoring container for storing configuration file metadata and interface-related 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 determine the period table-level characteristic of the current data table based on the historical processing statements.

[0037] Among them, the read-write crossover feature can be understood as a read-more-write-less feature, that is, the candidate table processing statements corresponding to the current data table have more processing statements for reading data than for writing data. The embodiment of the present invention does not impose any restrictions on the length of the preset historical period, which can be set by technical personnel based on experience or needs, or repeatedly determined through a large number of experiments. The preset historical period can be understood as an adjacent period of historical time 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:00 on the 12th, the preset historical period can be 8:00 on the 11th to 8:00 on the 12th.

[0038] Here, historical processing statements refer to previous candidate table processing statements associated with the current data table within a preset historical period. Period-based table-level features refer to the processing features of the current data table within the preset historical period. It should be noted that the current table-level features and period-based table-level features of the current data table can be the same or different.

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

[0040] Furthermore, if the historical read count is greater than the historical write count, the period table-level characteristic is determined to be a read-write interleaved characteristic; if the historical read count is less than the historical write count, the period table-level characteristic is determined to be a write-read interleaved characteristic. Here, historical read statements refer to historical processing statements used to read data. Historical write statements refer to historical processing statements used to write 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, the historical index corresponding to the current data table is updated to obtain a reference index, and a reference execution duration and a reference execution plan of the current processing statement are determined based on the reference index.

[0042] The "write-read interleaving" feature can be understood as a "more writes than reads" feature, meaning that the historical processing statements corresponding to the current data table contain fewer processing statements for reading data than for writing data. A historical index refers to the index corresponding to each current field in the current data table during a preset historical period.

[0043] The reference index is the index obtained by updating the historical index corresponding to the current data table. The reference execution duration is the duration of re-executing the current statement based on the reference index. The reference execution plan is the execution plan re-determined based on the reference index for the current statement.

[0044] In an optional embodiment, the historical index corresponding to the current data table is updated to obtain a reference index, including: deleting the historical index corresponding to each current field in the current data table, and determining the number of times each current field is accessed within a preset historical period; based on the number of accesses, determining whether there is a current key field in the current field; if so, reconstructing the index based on the current key field to obtain a reference index.

[0045] The current field refers to a field in the current data table. The number of visits refers to the number of times the current field has been visited. For example, for any current field, the number of times the current field was used as a condition and result in a query within a preset historical period is considered the number of visits to the current field.

[0046] The current key field refers to a current field that has been accessed frequently within a preset historical period. For example, for any current field, if the number of accesses to the current field is greater than or equal to a preset access threshold, the current field is determined to be a current key field; if the number of accesses to the current field is less than the preset access threshold, the current field is prohibited from being used as a current key field. The embodiments of the present invention do not impose any restrictions on the size of the preset access threshold; it can be set by a technician based on experience or needs, or determined repeatedly through extensive testing.

[0047] Exemplarily, a joint index reconstruction is performed on the current key fields to obtain a reference index. It can be understood that all current key fields correspond to one reference index.

[0048] It can be understood that after deleting the historical index corresponding to the current data table, it is determined whether the current key field exists in the current field based on the number of times the current field is accessed within the preset historical period. If so, the reference index corresponding to the current key field can be determined, thereby realizing the deletion of invalid indexes and reducing the number of invalid indexes. At the same time, by reconstructing the index of the determined current key data, the accuracy and applicability of the reconstructed reference index are improved, and the embodiment of the present invention realizes dynamic adjustment of the index and improves the performance of the database.

[0049] In another optional embodiment, the historical index corresponding to the current data table is updated to obtain a reference index, including: deleting the historical index corresponding to each current field in the current data table, and determining the number of times each current field is accessed within a preset historical period; based on the number of accesses, determining whether there is a current key field in the current field; if not, determining that the reference index is empty.

[0050] For example, if the time period table-level characteristic is a read-write cross characteristic, the historical index corresponding to each current field in the current data table is deleted. At this time, the reference index corresponding to the current data table is empty.

[0051] S140: Determine a target index corresponding to the current data table according to the reference execution time, the reference execution plan, and the preset index update strategy.

[0052] The preset index update strategy refers to a pre-set strategy for controlling iterative index updates. For example, when the index coverage type in the execution plan of the currently processed statement is not a full table scan, the preset index update strategy stops index updates and obtains the target index corresponding to the current data table. The target index is the index obtained by optimizing the original index corresponding to the current data table.

[0053] In an optional embodiment, the target index corresponding to the current data table is determined based on the reference execution time, the reference execution plan and the preset index update strategy, including: determining whether the current processing statement is a processing statement to be updated based on the reference execution time and the reference execution plan; if the current processing statement is a processing statement to be updated, determining the update field in the processing statement to be updated, and determining the index status of the update field; if the index status is that the index does not exist, determining the current update index of the update field, and iteratively updating the current update index and the reference index based on the current update index, the reference index and the preset index update strategy to obtain the target index corresponding to the current data table.

[0054] The "to-be-updated processing statement" refers to the current processing statement that requires re-indexing the current data table after determining the reference index of the current data table. The "update field" refers to the field in the to-be-updated processing statement used for re-indexing. Exemplarily, the update field may include a conditional update field and a conditional association field. The conditional update field refers to the "where" condition field, i.e., the conditional field used for index updating in the to-be-updated processing statement. The conditional association field refers to other fields associated with the conditional update field.

[0055] The index status can be used to indicate whether a corresponding index exists for the updated field. For example, the index status can be either "no index exists" or "there is an index." The current updated index refers to the index corresponding to the updated field. For example, a joint index reconstruction is performed on the updated field to obtain the corresponding current updated index.

[0056] In an optional embodiment, the current processing statement is determined to be a processing statement to be updated based on the reference execution time and the reference execution plan, including: if the reference execution time is greater than a preset time threshold and the reference index coverage type in the reference execution plan is a full table scan type, then the current processing statement is determined to be a processing statement to be updated.

[0057] The embodiment of the present invention does not set the time threshold value, and does not impose any limitation on the value. It can be set by technicians based on experience or needs, or determined repeatedly through a large number of experiments. For example, the preset time threshold value can be 500ms.

[0058] The reference index covering type refers to a type that scans the current data table based on the current processing statement, and the full table scanning type refers to a type that scans the entire current data table.

[0059] It can be understood that by using the current processing statement whose reference execution time is greater than the preset time threshold and is 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 updates of the current data table based on the processing statement to be updated is improved.

[0060] In another optional embodiment, if the reference execution time is less than or equal to a preset time threshold, the current processing statement is prohibited from being used as a processing statement to be updated.

[0061] Exemplarily, if the index status is that the index exists, the reference index is used 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 update field in the processing statement to be updated, then when the index status is that there is no index, constructing the current update index corresponding to the update field, and iteratively updating the current update index and the reference index to obtain the target index corresponding to the current data table, thereby improving the accuracy and effectiveness of the determined target index.

[0063] It should be noted that if a reference index exists for the conditional update field d in the update field, and no reference index exists for the conditional association field, the execution position of the conditional update field d in the current processing statement is determined. 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, the current update index corresponding to the conditional update field d is constructed based on the conditional update field and the conditional association field. The execution position refers to the position of the conditional update field in the current processing statement. The foremost position refers to the first execution position in the current processing statement.

[0064] An embodiment of the present invention provides a database index update solution, which obtains a current processing statement from a preset statement execution queue and determines the current table-level characteristics 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 based on a preset initial monitoring container and a candidate data table in a database; if the current table-level characteristic is a read-write cross characteristic, 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 characteristics of the current data table are determined; if the time period table-level characteristic is a write-read cross characteristic, the historical index corresponding to the current data table is updated to obtain a reference index, and based on the reference index, a reference execution time and a reference execution plan of the current processing statement are determined; based on the reference execution time, the reference execution plan and the preset index update strategy, the target index corresponding to the current data table is determined. The above solution updates the historical index of the current data table according to the historical processing statements within the preset historical period to obtain a reference index, and then determines the target index based on the reference execution plan and reference execution time determined by the reference index, as well as the preset index update strategy, thereby optimizing the index corresponding to the current data table, thereby reducing invalid indexes in the database and improving database performance.

[0065] Based on the above technical solution, the method also includes: obtaining the current execution count of the current data table, and determining whether the current processing statement is a cache processing statement based on the reference execution time and the current execution count; if so, determining the cache processing data of the cache processing statement, and adding the cache processing data to a pre-built data cache; wherein the cache processing data includes a cache processing template, cache processing parameters and cache processing results.

[0066] The current execution count refers to the number of times the current data table has been 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 corresponding to the cache processing statement that needs to be cached. For example, the cache processing data may include a cache processing template, cache processing parameters, and cache processing results.

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

[0068] The data buffer can be used to store cached processing data. Exemplarily, the data buffer can 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 cached processing data. The long-term data buffer can be used to store long-term or more frequently used cached processing data.

[0069] Exemplarily, determining whether the current processing statement is a cached processing statement based on the reference execution time and the current execution count includes: if the reference execution time of the current processing statement is greater than a preset time threshold, and the current execution count is greater than the preset execution count threshold, determining that the current processing statement is a cached processing statement; if the reference execution time of the current processing statement is less than or equal to the preset time threshold, and the current execution count is less than or equal to the preset execution count threshold, prohibiting the current processing statement from being a cached processing statement.

[0070] Furthermore, if the current processing statement is a cache processing statement, the temporary data cache and the long-term data cache are sequentially queried based on the cache processing statement (i.e., the temporary data cache is queried first, and if not found, the long-term data cache is queried again) to determine whether matching candidate cache data exists in the temporary data cache and the long-term data cache. If not, the cache processing statement is stored in the temporary data cache or the long-term data cache; if so, it does not need to be stored in the data cache. The candidate cache data refers to the cache data stored in the data cache.

[0071] It can be understood that by determining whether the current processing statement is a cached processing statement based on the reference execution time and the current number of executions, if so, the cached processing data of the cached processing statement is determined, and the cached processing data is added to the pre-built data cache. The embodiment of the present invention realizes caching of data by introducing a data cache, thereby improving the efficiency of subsequently determining the processing results of the processing statement based on the data cache, that is, improving the efficiency of processing the processing statement.

[0072] Example 2

[0073] Figure 2 This is a flowchart of a database index update method provided by Example 2 of the present invention. Based on the above embodiments, this embodiment further adds the following operations: "obtaining configuration file metadata and interface-related data of the database operation framework, and determining a target monitoring container based on the configuration file metadata, the interface-related data of the candidate database interface, and a pre-constructed initial monitoring container; wherein the interface-related data includes the interface class name, interface method, interface processing statement, and 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 table in the database; determining the candidate table-level characteristics corresponding to the corresponding data table identifier based on the function identification 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, determining the field type of the candidate field and the candidate index of the candidate field; and 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" to improve the mechanism for determining the target table-level container. It should be noted that for portions not described in detail in this embodiment of the present invention, reference can be made to the descriptions of other embodiments.

[0074] See also Figure 2 The database index update method shown includes:

[0075] S210 , obtaining configuration file metadata of the database operation framework and interface association data of the candidate database interface, and determining a target monitoring container based on the configuration file metadata, the interface association data, and a pre-built initial monitoring container.

[0076] The database operation framework refers to a framework for operating a database. For example, the database operation framework can be MyBatis. Configuration file metadata refers to the data in the configuration file within the database operation framework. The candidate database interface refers to the interface for data exchange between the database and the database operation framework. For example, the candidate database interface can be a mapper.

[0077] Interface-related data refers to data associated with a candidate database interface. Exemplarily, the interface-related data includes the interface class name, interface method, interface processing statement, and interface identifier. The interface class name is the identifier of the class corresponding to the candidate database interface. The interface method is the method corresponding to the candidate database interface. The interface processing statement is the processing statement corresponding to the candidate database interface. The interface identifier can be used to uniquely identify the candidate database interface.

[0078] The initial monitoring container is a pre-built basic container used to monitor the processing statements between the database operation framework and the database. The target monitoring container is an initial monitoring container used to store configuration file metadata and interface-related data.

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

[0080] The candidate monitoring container refers to the initial monitoring container that stores configuration file metadata. The candidate list refers to a list of candidate database interfaces that provide interface-related data. For example, the candidate list may include a MapperRegistry list (Mapper registration list), a MappedStatements list (Mapped statement list), and a MapperFactory list (Mapper factory list).

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

[0082] The candidate data table identifier is a data table identifier extracted from the interface processing statement and can be used to uniquely represent the identity of the candidate data table.

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

[0084] The initial table-level container refers to the basic container used to store table-level data associated with the candidate data table. 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, the candidate data table corresponding to the candidate data table identifier is determined from the database, and the processing statement associated with the candidate data table corresponding to the candidate data table identifier is used as the candidate table processing statement corresponding to the candidate data table identifier.

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

[0087] Function identification data refers to data that can be used to indicate the processing function of a candidate table processing statement. It should be noted that different processing functions correspond to different function identification data. Candidate table-level characteristics refer to the processing characteristics of the candidate data table corresponding to the candidate data table identification. For example, candidate table-level characteristics may include read-only, write-only, read-write crosstalk, and read-write crosstalk.

[0088] The read-only characteristic can indicate that a candidate data table is used only for read processing. The write-only characteristic can indicate that a candidate data table is used only for insert processing. The read-write interleaving characteristic (i.e., more reads than writes) can indicate that a candidate data table is used for read processing more often than for write processing. The write-read interleaving characteristic (i.e., more writes than reads) can indicate that a candidate data table is used for write processing more often than for read processing.

[0089] Among them, candidate fields refer to fields in the candidate data table. Field type refers to the type of candidate field. Candidate index refers to the index corresponding to the candidate field.

[0090] In an optional embodiment, the candidate table-level characteristics corresponding to the corresponding candidate data table identifier are determined based on the function identification data in the candidate table processing statement, including: for any candidate data table identifier, the candidate table processing statement associated with the candidate data table identifier is used as the target table processing statement, and based on the function identification data in the target table processing statement, the initial table-level characteristics corresponding to the candidate data table identifier are determined; if the initial table-level characteristics are characteristics to be verified, the target interface identifier corresponding to the candidate data table identifier is determined based on the target monitoring container, and the program call data of the corresponding target database interface is obtained based on the target interface identifier; based on the program call data, the initial table-level characteristics are verified to determine the candidate table-level characteristics of the candidate data table identifier.

[0091] The target table processing statement refers to the candidate table processing statement corresponding to the candidate data table identifier. The initial table-level characteristic refers to the initial processing characteristic of the candidate data table corresponding to the candidate data table identifier. The to-be-verified characteristic refers to the initial table-level characteristic that needs to be verified. Exemplarily, the to-be-verified characteristic can be a write-read crossover characteristic or a read-write crossover characteristic.

[0092] The target interface identifier is the identifier of the target database interface corresponding to the candidate data table identifier. The target interface identifier can be used to uniquely identify the target database interface. The target database interface is the candidate database interface corresponding to the target interface identifier.

[0093] The program call data refers to program data for interacting with the target database interface. Exemplarily, the program call data may include execution function data of the program for interacting with the target database interface, such as program read data or program write 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 the target table processing statement; the function identification data in each target table processing statement is determined, and the statement characteristics of the corresponding target table processing statement are determined based on the function identification data; and based on the statement characteristics of the target table processing statement, the initial table-level characteristics of the candidate data table identifier A are determined. The statement characteristics can be understood as the functional type of the processing statement.

[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 the initial table-level characteristics of the candidate data table identifier A are determined to be 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 the initial table-level characteristics of the candidate data table identifier A are determined to be 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 the initial table-level characteristics of the candidate data table identifier A are determined to be 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 the initial table-level characteristics of the candidate data table identifier A are determined to be write-read cross characteristics.

[0096] For example, for a scenario with more reads than writes (i.e., read-write interleaving), there are more select statements and fewer insert, update, and delete statements. For a scenario with more writes than reads (i.e., write-read interleaving), there are fewer select statements and more insert, update, and delete statements. For a write-only scenario, there are only insert, update, and delete statements. For a read-only scenario, there is only select statements.

[0097] Exemplarily, if the initial table-level characteristic is a write-read cross characteristic or a read-write cross characteristic, then the initial table-level characteristic is determined to be a characteristic to be verified; based on the target table processing statement and the target monitoring container, the target interface identifier corresponding to the candidate data table identifier A is determined; based on 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; based on 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, then the candidate table-level characteristic of the candidate data table identifier A is determined to be a read-write cross characteristic; if the number of program reads is less than the number of program writes, then the candidate table-level characteristic of the candidate data table identifier A is determined to be a write-read cross characteristic.

[0098] For example, we can reason again through the statement calling code, mainly targeting the situations where there are more reads and fewer writes and more writes and fewer reads. If the initial table-level characteristic is more reads and fewer writes: if the code contains a large number of call association writes, that is, there are fewer read associations and more write associations, then the target table-level characteristic is more writes and fewer reads. If the initial table-level characteristic is more writes and fewer reads: if the code contains a large number of read associations and relatively few write associations, then the target table-level characteristic is more reads and fewer writes.

[0099] It can be understood that by verifying the characteristics to be verified, errors in the initial table-level characteristics determined directly based on the function identification data are avoided, and the accuracy of the determined target table-level characteristics is improved.

[0100] S250: Based on the candidate table-level characteristics, the field types of the candidate fields, and the candidate indexes, the initial table-level container is updated to obtain a target table-level container.

[0101] The target table-level container may be used to store candidate data table identifiers, candidate table-level properties, candidate field types, and candidate indexes.

[0102] Specifically, the candidate table-level characteristics, field types, and candidate indexes are stored in the initial table-level container at the location corresponding to the corresponding candidate data table identifier to obtain a target table-level container.

[0103] It should be noted that the candidate index corresponding to a candidate field in the candidate data table may be empty, that is, the candidate field does not have a corresponding candidate index. Therefore, an empty candidate index may exist 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 characteristics of the current data table corresponding to the current processing statement based on the preset target table-level container.

[0105] The target table-level container is determined based on a preset initial monitoring container and candidate data tables in the database.

[0106] S260: 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 determine the period table-level characteristic of the current data table based on the historical processing statements.

[0107] S280: If the time period table-level characteristic is a write-read cross characteristic, the historical index corresponding to the current data table is updated to obtain a reference index, and a reference execution duration and a reference execution plan of the current processing statement are determined based on the reference index.

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

[0109] An embodiment of the present invention provides a database index update solution, which obtains configuration file metadata of a database operation framework and interface-related data of a candidate database interface by adding, and determines a target monitoring container based on the configuration file metadata, the interface-related data and a pre-built initial monitoring container; wherein the interface-related data includes an interface class name, an interface method, an interface processing statement and an interface identifier; traverses the interface processing statements in the target monitoring container and determines a data table identifier corresponding to the interface processing statement; constructs an initial table-level container including a data table identifier, and determines a candidate table processing statement corresponding to the data table identifier based on a data table in a database; determines a candidate table-level feature corresponding to the corresponding data table identifier based on function identifier data in the candidate table processing statement, obtains candidate fields in the data table corresponding to the data table identifier from the database, determines the field type of the candidate field and the candidate index of the candidate field; updates the initial table-level container based on the candidate table-level feature, the field type of the candidate field and the candidate index, obtains a target table-level container operation, and improves the target table-level container determination mechanism. The above scheme determines the target monitoring container by obtaining the configuration file metadata of the database operation framework and the interface-related data of the candidate database interface, thereby 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 subsequently determined 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 reversely inferred from the candidate data table corresponding to the candidate data table identifier to obtain the candidate table processing statement, further improving the comprehensiveness of the data in the target table-level container.

[0110] Example 3

[0111] The embodiment of the present invention provides an optional example based on the above embodiment. It should be noted that for parts not described in detail in the embodiment of the present invention, reference can be made to the descriptions of other embodiments.

[0112] During business system development, database maintenance is often based on developer experience, creating indexes based on their own experience. This can lead to misuse and irrational index construction, resulting in invalid indexes that are not hit, wasting storage space and increasing database overhead. Furthermore, SQL statements that miss indexes can slow queries, increase database pressure, and easily cause database failures, leading to business losses. Furthermore, the performance of traditional database table maintenance methods needs improvement, especially for large data volumes, as they lack efficient and effective maintenance methods.

[0113] The database index updating method provided by the embodiment of the present invention realizes dynamic priority of SQL statement execution of a data table by dynamically managing SQL indexes.

[0114] For example, first optimize the table metadata, that is, build the target table-level container. Specifically, build a SQL (Structured Query Language) monitor context SQLMonitorContext (i.e., the initial monitoring container), add the Spring life cycle process, customize MybatisAutoConfiguration (i.e., automatic registrar), take over the automatic registration behavior of mybatis (i.e., database operation framework), extract the configuration file metadata of mybatis, put it into SQLMonitorContext, and obtain the candidate monitoring container; when the initialization of the Spring life cycle is completed, extract the MapperRegistry list and mappedStatements list, and build a composite object of Mapper class name (i.e., interface class name), method (i.e., interface method), and SQL statement (i.e., interface processing statement), and extract the corresponding MapperRegistry from the MapperRegistry. The Mapper object (i.e., interface identifier) ​​created by pperFactory is placed into the monitoring context to obtain the target monitoring container. Using the composite object in the monitoring context, all SQL statements are traversed (i.e., interface processing statements in the target monitoring container) and the table name (i.e., candidate data table identifier) ​​of the SQL statement is extracted. A table-level container (TC) (i.e., initial table-level container) is established with the table name as the center. All statements in the mappedStatements table are then queried (i.e., candidate table processing statements associated with the candidate data table are determined). The database table's characteristics are inferred from these statements (i.e., target table-level characteristics): flags for characteristics such as read-mostly-write-less, write-mostly-read-less, and write-only. Based on the table name, metadata information for the candidate data table identifier is retrieved from the database to determine the table field type (i.e., field type) and whether there is an index (i.e., whether there is a candidate index). The automatic registrar is used to obtain configuration file metadata from the database operation framework.

[0115] For example, SQL statement execution monitoring is achieved by building an application-level Executor interceptor. In the interceptor, the SQL statement executed by the application (i.e., the initial processing statement) is extracted, and a listener for the SQL statement is established. In the listener, the SQL statement execution plan data (i.e., the initial execution plan) and the SQL statement execution time (i.e., the initial execution duration) are collected, and an SQL execution log queue (i.e., the statement execution queue) is built to put the SQL statement execution information into the queue.

[0116] For example, SQL-aware optimization monitors the SQL statement execution queue, obtains SQL execution log information, extracts the table name (current data table identifier) ​​of the SQL statement (i.e., the currently processed statement), obtains the table metadata information in the TC container (i.e., the target table-level container), and optimizes the database based on the table characteristics (i.e., the current table-level characteristics).

[0117] Optionally, if the current table-level feature is a write-only feature, all current fields of the current data table are obtained and whether an index exists. If so, the index is deleted to improve data insertion efficiency.

[0118] Alternatively, if the current table-level feature is read-write interleaving (i.e., more reads than writes), read and write counters and time periods are built in the TC container (i.e., the target table-level container). The read and write time periodicity is analyzed, and an execution scheduler is built based on the periodicity to manage the indexes of the candidate data tables. If analysis shows that the current data table has a high number of inserts and very low number of reads within the preset historical period, the historical index of the current data table within the preset historical period is deleted. If certain fields are frequently used as conditions and result queries within the preset historical period, the index is reconstructed based on these fields (i.e., the current key fields) to build a composite index (i.e., the reference index). The execution time (i.e., the reference execution time) of the SQL statement (currently processed statement) is determined using the reference index. If the SQL execution time (i.e., the reference execution time) is greater than 500 milliseconds, determine whether the Type type in the reference execution plan involves a full table scan. If so, check whether an index exists for the update field by extracting the where condition field and associated fields in the SQL statement (i.e., extracting the update field). If not, build a joint index (i.e., the current update index) based on the update field, perform probing, and obtain the execution plan again until the execution plan of the SQL statement does not involve a full table scan, thus obtaining the target index of the current data table.

[0119] It should be noted that, in response to the read-write crossover feature, while determining the reference execution duration based on the reference index, the current execution count of the current data table is obtained based on a counter. If the current execution count is relatively high, in the Executor interception, a hash value extraction is performed using an SQL template (i.e., cached processing data) + parameters (i.e., cached processing parameters), and a timed data cache (i.e., a temporary data cache) and an LRU data cache (i.e., a long-term data cache) are constructed. When querying an SQL statement, the timed data cache and the LRU data cache are hit first. Subsequently, the SQL statement can be processed based on the timed data cache and the LRU data cache. If the corresponding processing result is matched, it is returned directly; if not, the SQL statement processing continues by accessing the database. The benefit is that the processing efficiency of SQL statements under the read-write crossover feature is improved.

[0120] Alternatively, for scenarios with more writes than reads, unlike scenarios with more reads than writes, the timed data buffer and LRU data buffer are no longer built. Instead, table indexes are dynamically managed based on the SQL statement execution cycle. Specifically, if the current table-level characteristic is read-write interleaving (i.e., more writes than reads), read and write counters and time periods are built in the TC container (i.e., the target table-level container). The read and write time periodicity is analyzed, and an execution scheduler is built based on the periodicity to manage the indexes of the candidate data tables. If analysis shows that the current data table has a high number of inserts and very low number of reads within the preset historical period, the historical index of the current data table within the preset historical period is deleted. If certain fields are frequently used as conditions and result queries within the preset historical period, the index is reconstructed based on these fields (i.e., the current key fields) to build a composite index (i.e., the reference index). The execution time (i.e., the reference execution time) of the SQL statement (currently processed statement) is determined using the reference index. If the SQL execution time (i.e., the reference execution time) is greater than 500 milliseconds, determine whether the Type type in the reference execution plan involves a full table scan. If so, check whether an index exists for the update field by extracting the SQL statement's where condition field and the associated field (i.e., the update field). If not, build a joint index (i.e., the current update index) based on the update field, perform probing, and obtain the execution plan again until the SQL statement's execution plan does not involve a full table scan, thus obtaining the target index for the current data table.

[0121] Example 4

[0122] Figure 3 This is a schematic diagram of the structure of a database index update device provided in Example 4 of the present invention. This embodiment is applicable to updating indexes corresponding to various data tables in a database. The method can be performed by the database index update device, which can be implemented using software and / or hardware and can be configured in an electronic device that carries the database index update function.

[0123] like Figure 3 As 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.

[0124] The current table-level property determination module 310 is configured to obtain a current processing statement from a preset statement execution queue and determine the current table-level property 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 based on a preset initial monitoring container and a data table in the database;

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

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

[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 strategy.

[0128] An embodiment of the present invention provides a database index update solution, which obtains a current processing statement from a preset statement execution queue and determines the current table-level characteristics 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 based on a preset initial monitoring container and a candidate data table in a database; if the current table-level characteristic is a read-write cross characteristic, 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 characteristics of the current data table are determined; if the time period table-level characteristic is a write-read cross characteristic, the historical index corresponding to the current data table is updated to obtain a reference index, and based on the reference index, a reference execution time and a reference execution plan of the current processing statement are determined; based on the reference execution time, the reference execution plan and the preset index update strategy, the target index corresponding to the current data table is determined. The above solution updates the historical index of the current data table according to the historical processing statements within the preset historical period to obtain a reference index, and then determines the target index based on the reference execution plan and reference execution time determined by the reference index, as well as the preset index update strategy, thereby optimizing the index corresponding to the current data table, thereby reducing invalid indexes in the database and improving database performance.

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

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

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

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

[0133] a candidate index determination module, configured to determine, based on the function identification data in the candidate table processing statement, a candidate table-level characteristic corresponding to the corresponding candidate data table identifier, obtain from the database a candidate field in the candidate data table corresponding to the candidate data table identifier, and determine a field type of the candidate field and a candidate index of the candidate field;

[0134] The target table-level container determination module is configured to update 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 a target table-level container.

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

[0136] For any candidate data table identifier, the candidate table processing statement associated with the candidate data table identifier is used as the target table processing statement, and based on the function identification data in the target table processing statement, the initial table-level characteristics corresponding to the candidate data table identifier are determined;

[0137] If the initial table-level feature is a feature to be verified, determining a target interface identifier corresponding to the candidate data table identifier based on the target monitoring container, and obtaining program call data of the corresponding target database interface according to the target interface identifier;

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

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

[0140] a processing statement to be updated determining unit, configured to determine whether the current processing statement is a processing statement to be updated based on the reference execution duration and the reference execution plan;

[0141] an index status determining unit, configured to determine an update field in the processing statement to be updated and determine an index status of the update field if the current processing statement is the processing statement to be updated;

[0142] The target index determination unit is used to determine the current update index of the update field if the index status is that the index does not exist, 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.

[0143] Optionally, the unit for determining the statement to be updated is specifically used to:

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

[0145] Optionally, the device 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 based on the reference execution duration and the current execution count;

[0147] The data cache storage module is used to determine the cache processing data of the cache processing statement and add the cache processing data to the pre-built data cache; wherein the cache processing data includes a cache processing template, cache processing parameters and cache processing results.

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

[0149] an access count determining unit, configured to delete the historical index corresponding to each current field in the current data table, and respectively determine the access count of each current field within the preset historical period;

[0150] a current key field determining unit, configured to determine whether a current key field exists in the current field according to the number of accesses;

[0151] The reference index determining unit is configured to reconstruct the index based on the current key field to obtain a reference index if the reference index exists.

[0152] The database index updating device provided in the embodiment of the present invention can execute the database index updating method provided in any embodiment of the present invention, and has the corresponding functional modules and beneficial effects for executing each database index updating method.

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

[0154] Example 5

[0155] Figure 4 4 is a schematic diagram of the structure of an electronic device for implementing a method for updating a database index provided in a fifth embodiment 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 assistants, 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 examples and are not intended to limit the implementation of the present invention described and / or claimed herein.

[0156] like Figure 4 As shown, electronic device 410 includes at least one processor 411 and memory, such as read-only memory (ROM) 412 and random access memory (RAM) 413, communicatively connected to at least one processor 411. The memory stores computer programs executable by the at least one processor. Processor 411 can perform various appropriate actions and processes based on the computer programs stored in ROM 412 or loaded from storage unit 418 into RAM 413. RAM 413 can also store various programs and data required for the operation of electronic device 410. Processor 411, ROM 412, and RAM 413 are interconnected via bus 414. An input / output (I / O) interface 415 is also connected to bus 414.

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

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

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

[0160] Various embodiments of the systems and techniques described above can be implemented in digital electronic circuit systems, integrated circuit systems, field programmable gate arrays (FPGAs), application specific integrated circuits (ASICs), application specific standard products (ASSPs), system-on-chip systems (SOCs), programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments can include being implemented in one or more computer programs that are executable and / or interpreted on a programmable system that includes at least one programmable processor, which can be a special purpose or general purpose programmable processor that can receive data and instructions from a storage system, at least one input device, and at least one output device, and transmit data and instructions to the storage system, the at least one input device, and the at least one output device.

[0161] Computer programs for implementing the methods of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when the computer program is executed by the processor, the functions / operations specified in the flowcharts and / or block diagrams are implemented. The computer program 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 may be a tangible medium that may contain or store a computer program for use by or in conjunction with an instruction execution system, device, or apparatus. A computer-readable storage medium may include, but is not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, device, or apparatus, or any suitable combination of the foregoing. Alternatively, a computer-readable storage medium may be a machine-readable signal medium. More specific examples of machine-readable storage media may 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 disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

[0163] To provide interaction with a user, the systems and techniques described herein can be implemented on an electronic device that has: 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 pointing device (e.g., a mouse or trackball) through which the user can provide input to the electronic device. Other types of devices can also be used to provide interaction with the user; for example, the 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, voice 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 with a graphical user interface or web browser through which a user can interact with implementations 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 clients and servers. The clients and servers are typically remote from each other and typically interact via a communication network. This client-server relationship arises through computer programs running on the respective computers, creating a client-server relationship. The server may be a cloud server, also known as a cloud computing server or cloud host. This server is a hosting product within the cloud computing service ecosystem that addresses the management difficulties and limited scalability of traditional physical hosting and VPS services.

[0166] It should be understood that the various forms of the processes shown above can be used to reorder, add, or delete steps. For example, the steps described in the present invention can be performed 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. This is not limited herein.

[0167] The above specific embodiments do not limit the scope of protection of the present invention. Those skilled in the art will appreciate that various modifications, combinations, sub-combinations, and substitutions may be made based on design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of the present invention are intended to be included within the scope of protection of the present invention.

Claims

1. A method for updating a database index, characterized in that: include: 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 based on a preset initial monitoring container and candidate data tables in a database; If the current table-level characteristic is a read-write crossover characteristic, historical processing statements associated with the current data table within a preset historical period are determined, and based on the historical processing statements, a period-based table-level characteristic of the current data table is determined; wherein the read-write crossover characteristic refers to a characteristic of more reads than writes, specifically, that among the candidate table processing statements corresponding to the current data table, there are more processing statements for reading data than for writing data; If the time period table-level characteristic is a write-read crossover characteristic, the historical index corresponding to the current data table is updated to obtain a reference index, and a reference execution duration and a reference execution plan of the current processing statement are determined based on the reference index; wherein the write-read crossover characteristic refers to a characteristic of more writes than reads, specifically, that among the historical processing statements corresponding to the current data table, there are fewer processing statements for reading data than for writing data; Determine a target index corresponding to the current data table according to the reference execution time, the reference execution plan, and a preset index update strategy; The target table-level container is determined based on the following method: Obtaining configuration file metadata of the database operation framework and interface-related data of the candidate database interface, and determining a target monitoring container based on the configuration file metadata, the interface-related data, and a pre-built initial monitoring container; wherein the interface-related data includes the interface class name, interface method, interface processing statement, and interface identifier; Traversing the interface processing statements in the target monitoring container and determining the candidate data table identifiers corresponding to the interface processing statements; Constructing an initial table-level container including the candidate data table identifier, and determining a candidate table processing statement corresponding to the candidate data table identifier based on the candidate data table in the database; Determining, based on the function identification data in the candidate table processing statement, a candidate table-level characteristic corresponding to the corresponding candidate data table identifier, obtaining from the database a candidate field in the candidate data table corresponding to the candidate data table identifier, and determining a field type of the candidate field and a candidate index of the candidate field; Based on the candidate table-level characteristics, the field types of the candidate fields, and the candidate indexes, the initial table-level container is updated to obtain a target table-level container.

2. The method for updating a database index according to claim 1, wherein: The step of determining the candidate table-level characteristics corresponding to the corresponding candidate data table identifier according to the function identifier data in the candidate table processing statement includes: For any candidate data table identifier, the candidate table processing statement associated with the candidate data table identifier is used as the target table processing statement, and based on the function identification data in the target table processing statement, the initial table-level characteristics corresponding to the candidate data table identifier are determined; If the initial table-level feature is a feature to be verified, determining a target interface identifier corresponding to the candidate data table identifier based on the target monitoring container, and obtaining program call data of the corresponding target database interface according to the target interface identifier; The initial table-level characteristics are verified according to the program call data to determine the candidate table-level characteristics of the candidate data table identifier.

3. The method for updating a database index according to claim 1, wherein: The determining, based on the reference execution duration, the reference execution plan, and a preset index update strategy, a target index corresponding to the current data table includes: determining, based on the reference execution duration and the reference execution plan, whether the current processing statement is a processing statement to be updated; If the current processing statement is the processing statement to be updated, determining an update field in the processing statement to be updated, and determining an index status of the update field; If the index status is that no index exists, the current update index of the update field is determined, and the current update index is iteratively updated 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.

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

5. The method for updating a database index according to claim 3, wherein: The method further comprises: Obtaining the current execution count of the current data table, and determining whether the current processing statement is a cached processing statement based on the reference execution duration and the current execution count; If so, the cache processing data of the cache processing statement is determined, and the cache processing data is added to the pre-built data cache; wherein the cache processing data includes a cache processing template, cache processing parameters and cache processing results.

6. The method for updating a database index according to any one of claims 1 to 5, characterized in that: The updating of 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 determining the number of accesses of each current field within the preset historical period; Determining whether a current key field exists in the current field according to the number of accesses; If it exists, the index is reconstructed based on the current key field to obtain a reference index.

7. A device for updating a database index, characterized in that: include: A current table-level property determination module is configured to obtain a current processing statement from a preset statement execution queue and determine the current table-level property 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 based on a preset initial monitoring container and candidate data tables in the database; a period table-level characteristic determination module configured to, if the current table-level characteristic is a read-write crossover characteristic, determine historical processing statements associated with the current data table within a preset historical period, and determine the period table-level characteristic of the current data table based on the historical processing statements; wherein the read-write crossover characteristic refers to a characteristic of more reads than writes, specifically, that among the candidate table processing statements corresponding to the current data table, there are more processing statements for reading data than for writing data; a reference index determination module configured to update the historical index corresponding to the current data table to obtain a reference index if the period table-level characteristic is a write-read crossover characteristic, and determine a reference execution duration and a reference execution plan for the current processing statement based on the reference index; wherein the write-read crossover characteristic refers to a characteristic of more writes than reads, specifically, that among the historical processing statements corresponding to the current data table, there are fewer processing statements for reading data than for writing data; A target index determination module is used to determine the target index corresponding to the current data table according to the reference execution time, the reference execution plan and a preset index update strategy; The target table-level container is determined based on the following means: A target monitoring container determination module is configured to obtain configuration file metadata of the database operation framework and interface association data of the candidate database interface, and determine the target monitoring container based on the configuration file metadata, the interface association data, and a pre-built initial monitoring container; wherein the interface association data includes the interface class name, interface method, interface processing statement, and interface identifier; a candidate data table identifier determination module, configured to traverse the interface processing statements in the target monitoring container and determine the candidate data table identifier corresponding to the interface processing statement; a candidate table processing statement determination module, configured to construct an initial table-level container including the candidate data table identifier, and determine a candidate table processing statement corresponding to the candidate data table identifier based on the candidate data tables in the database; a candidate index determination module, configured to determine, based on the function identification data in the candidate table processing statement, a candidate table-level characteristic corresponding to the corresponding candidate data table identifier, obtain from the database a candidate field in the candidate data table corresponding to the candidate data table identifier, and determine a field type of the candidate field and a candidate index of the candidate field; The target table-level container determination module is configured to update 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 a target table-level container.

8. An electronic device, characterized in that: include: 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 the database index updating method according to any one of claims 1 to 6.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the method for updating a database index as described in any one of claims 1 to 6 is implemented.

Images