Method and electronic device for rebuilding database index

Abstract

The present application provides a method and electronic device for rebuilding a database index, the method comprising: obtaining the predicted time consumption of the server for rebuilding the database index predicted by the reconstruction time prediction model when the reconstruction judgment model determines that the database index needs to be rebuilt, and obtaining the prediction result of the subsequent change of the number of front-end business interface connections of the database output by the traffic low time period prediction model; predicting multiple operational time periods of the server based on the prediction result, selecting a rebuildable operational time period that meets the predicted time consumption from the predicted multiple operational time periods; and instructing the server to reorganize the index or regenerate the index in the rebuildable operational time period. According to the method of the present application, it is possible to quickly determine whether the database index needs to be rebuilt and to select the operational time period for rebuilding the database index, thereby improving the timeliness of database index reconstruction and effectively avoiding affecting other database operations due to rebuilding the database index.

Description

Technical Field

[0001] The present application relates to the field of computer technology, and in particular to a method and electronic device for rebuilding a database index. Background Art

[0002] Rebuilding database indexes is a common maintenance operation. After a database has been running for a long time, the database index may become corrupted. Or, after a large amount of data has been updated, the database index may not match the database content. In this case, rebuilding the database index is necessary to improve database retrieval efficiency.

[0003] Generally, whether or not a database index needs to be rebuilt depends largely on the database administrator's experience. To avoid reduced database search efficiency, the database administrator must promptly rebuild the index when it becomes corrupted or when the index mismatches the database content. This requires the database administrator to constantly monitor the database status and possesses accurate judgment.

[0004] However, manual operation often lacks the ability to monitor database status in a timely manner, nor can it guarantee the accuracy of judgment based on the database administrator's experience. This often leads to failure to immediately rebuild database indexes after they become corrupted. Therefore, a method for rebuilding database indexes is needed to accurately determine when to initiate a rebuild. Summary of the Invention

[0005] To address the issue of determining when to initiate database index reconstruction, the present application provides a method and electronic device for database index reconstruction. This method can determine when to initiate database index reconstruction, preventing the database index reconstruction from interfering with other database operations and improving the timeliness of database index reconstruction.

[0006] This application adopts the following technical solutions:

[0007] In a first aspect, the present application provides a method for rebuilding a database index, the method being applied to an electronic device, the method comprising:

[0008] Collecting relevant information about the database, including: the size of the database index, the number of index scans of the database, the index fragmentation rate of the database, the index density of the database, the processor configuration of the server implementing the database, the number of read and write operations that can be performed by the server per second, the memory size of the server, the memory parameter settings of the server, and monitoring information on the number of front-end business interface connections of the database;

[0009] Calling a reconstruction judgment model, wherein the reconstruction judgment model is used to judge whether database index reconstruction is required according to the index fragmentation rate and the index scan times;

[0010] Inputting the index scan times and the index fragmentation rate into the reconstruction judgment model, and obtaining a judgment result output by the reconstruction judgment model, wherein the judgment result includes whether database index reconstruction is required or not;

[0011] When the determination result is that the database index reconstruction is not required, the process of the method for reconstructing the database index is terminated;

[0012] When the determination result is that the database index needs to be rebuilt, calling a reconstruction time prediction model, the reconstruction time prediction model is used to predict the time taken by the server to rebuild the database index based on the index size, the index density, the processor configuration of the server, the number of read and write operations that can be performed by the server per second, the memory size of the server, and the memory parameter settings of the server;

[0013] Inputting the index size, the index density, the processor configuration, the number of read and write operations that can be performed by the server per second, the memory size of the server, and the memory parameter settings of the server in the relevant information of the database into the reconstruction time prediction model, and obtaining the predicted time output from the reconstruction time prediction model;

[0014] When the determination result is that the database index needs to be rebuilt, calling the traffic valley time period prediction model, the traffic valley time period prediction model is used to generate a prediction result of the subsequent change of the number of front-end service interface connections of the database based on the monitoring information of the number of front-end service interface connections of the database;

[0015] Inputting the monitoring information of the number of front-end service interface connections of the database into the traffic low-time period prediction model, and obtaining the prediction result outputted from the traffic low-time period prediction model;

[0016] Predicting multiple operational time periods of the server based on the prediction results, wherein a time period during which the number of connections of the front-end service interface of the database is less than a preset connection number threshold is the operational time period of the server;

[0017] Selecting a reconstructable operation time period from the predicted multiple operable operation time periods, wherein the reconstructable operation time period is the first operable operation time period after the current moment and having a duration greater than or equal to the predicted time consumption;

[0018] The server is instructed to rebuild the database index during the rebuildable operation time period.

[0019] According to the method of the embodiment of the present application, the reconstruction judgment model determines whether the database index needs to be rebuilt, and the operation time period for rebuilding the database index is preferably selected based on the reconstruction duration prediction model and the traffic low time period prediction model. This can effectively avoid the reconstruction of the database index affecting the execution of other database services and improve the timeliness of database index reconstruction.

[0020] In an implementation of the first aspect, rebuilding the database index includes:

[0021] Reorganizing the index, wherein the reorganizing the index includes reordering the data in the outermost data pages of the database and compressing the index pages;

[0022] or,

[0023] Regenerating the index, wherein the regenerating the index includes deleting the original index of the database and generating a new index.

[0024] In an implementation of the first aspect, the reconstruction judgment model is a decision tree algorithm model;

[0025] The method further comprises:

[0026] Acquire historical data of the database, the historical data of the database including historical records of index scan times and index fragmentation rates of the database and / or other databases, the historical records of index scan times including recorded values of index scan times and corresponding time periods, and the historical records of index scan times including recorded values of index fragmentation rates and corresponding time periods;

[0027] Generating a plurality of characteristic values according to the historical data of the database, wherein each characteristic value includes, for the same database, a record value of the number of index scans and a record value of the index fragmentation rate corresponding to the same time period;

[0028] Adding a mark indicating that the database index needs to be rebuilt to the feature value, or adding a mark indicating that the database index does not need to be rebuilt to the feature value, to generate a plurality of training samples;

[0029] A decision tree algorithm is used and a machine learning platform is utilized to perform model training using the multiple training samples to obtain the reconstruction judgment model.

[0030] In an implementation of the first aspect, the method further includes:

[0031] Acquire the database index reconstruction history record, where the database index reconstruction history record includes the time when the database index reconstruction was executed;

[0032] The adding a mark indicating that the database index reconstruction needs to be performed to the feature value, or adding a mark indicating that the database index reconstruction does not need to be performed to the feature value, to generate a plurality of training samples, includes:

[0033] Based on the database index reconstruction history, confirming whether the database index reconstruction was performed within the time period corresponding to the characteristic value;

[0034] When the database index reconstruction is executed within the time period corresponding to the characteristic value, adding a mark indicating that the database index reconstruction needs to be executed to the characteristic value;

[0035] When the database index reconstruction is not performed within the time period corresponding to the feature value, a mark indicating that the database index reconstruction is not required is added to the feature value.

[0036] In an implementation of the first aspect, adding a flag indicating that the database index needs to be rebuilt to the feature value, or adding a flag indicating that the database index does not need to be rebuilt to the feature value, to generate multiple training samples, includes:

[0037] When the recorded value of the index fragmentation rate is greater than a preset fragmentation rate threshold, and the recorded value of the index scan times is greater than a preset scan times threshold, adding a mark indicating that the database index reconstruction needs to be performed to the characteristic value;

[0038] When the recorded value of the index fragmentation rate is less than or equal to a preset fragmentation rate threshold, and / or the recorded value of the index scan times is less than or equal to a preset scan times threshold, a mark indicating that the database index reconstruction does not need to be performed is added to the feature value.

[0039] In an implementation of the first aspect, the reconstruction time prediction model is a multiple linear regression algorithm model;

[0040] The method further comprises:

[0041] Obtaining a database index reconstruction history of the database and / or other databases, wherein the database index reconstruction history includes a database index reconstruction duration record value and a time when the database index reconstruction is executed;

[0042] Obtaining database historical data, the database historical data including index size history records, index density history records, server processor configuration records, records of the number of read and write operations that can be performed by the server per second, server memory size records, and server memory parameter setting records of the database and / or other databases, the index size history records including index size record values and corresponding time periods, the index density history records including index density record values and corresponding time periods, the server processor configuration records including server processor configuration record values and corresponding time periods, the server read and write operation count records including server read and write operation count records including server memory size record values and corresponding time periods, the server memory size records including server memory size record values and corresponding time periods, and the server memory parameter setting records including server memory parameter setting record values and corresponding time periods;

[0043] Generate a plurality of training samples according to the database index reconstruction history records, each training sample including a database index reconstruction duration record value;

[0044] Adding feature values to the multiple training samples based on the database historical data, wherein: the feature values include the index density record value, the index size record value, the server processor configuration record value, the server read and write operation number per second record value, the server memory size record value, and the server memory parameter setting record value; in the same training sample, the database index reconstruction time record value, the index density record value, the index size record value, the server processor configuration record value, the server read and write operation number per second record value, the server memory size record value, and the server memory parameter setting record value correspond to the same database and the same time period;

[0045] A multivariate linear regression algorithm is used, utilizing a machine learning platform, and the plurality of training samples are used to perform model training to obtain the reconstruction time prediction model.

[0046] In an implementation of the first aspect, the low traffic period prediction model is a K-nearest neighbor regression algorithm model;

[0047] The method further comprises:

[0048] Obtaining a monitoring history record of the number of front-end service interface connections of the database and / or other databases, wherein the monitoring history record of the number of front-end service interface connections includes a record value of the number of front-end service interface connections and a corresponding time period;

[0049] Acquire host access history data of the database and / or other databases, wherein the host access history data includes the address of the host accessing the database and / or other databases and the time when the access occurred;

[0050] Generating a training sample set including a plurality of training samples according to the monitoring history record of the number of front-end service interface connections, each of the training samples including a record value of the number of front-end service interface connections;

[0051] Adding feature values to the training samples based on the host access history data, wherein: the feature values include the host address and the time when the access occurred; the host address and the front-end service interface connection number record value in the same training sample correspond to the same database, and the time when the access occurred is within the time period corresponding to the front-end service interface connection number record value;

[0052] A K-nearest neighbor regression algorithm is used, and a machine learning platform is utilized to perform model training using the training sample set to obtain the low traffic period prediction model.

[0053] In an implementation of the first aspect, performing model training using the training sample set to obtain the low traffic period prediction model includes:

[0054] Splitting the training sample set into a plurality of sub-training sample sets based on the difference of services of the database, each sub-training sample set corresponding to a service of the database;

[0055] The sub-training sample set is used to perform model training to obtain the traffic low time period prediction model corresponding to the business of the database.

[0056] In an implementation of the first aspect, after instructing the server to rebuild the database index in the rebuildable operation time period, the method further includes:

[0057] At the start of the reconfigurable operation time period, obtaining the current load status of the server;

[0058] When the current load of the server meets the execution requirement of rebuilding the database index, the database index is rebuilt in the rebuildable operation time period.

[0059] In a second aspect, the present application provides an electronic device, comprising a memory for storing computer program instructions and a processor for executing computer program instructions, wherein when the computer program instructions are executed by the processor, the electronic device is triggered to execute the method steps described in the first aspect. BRIEF DESCRIPTION OF THE DRAWINGS

[0060] Figure 1 FIG2 is a schematic diagram of the structure of an electronic device according to an embodiment of the present application;

[0061] Figure 2 Shown Figure 1 A schematic diagram of the logical structure of the model center of the electronic device shown;

[0062] Figure 3 Shown is a flowchart of a method for rebuilding a database index according to an embodiment of the present application;

[0063] Figure 4 Shown is based on Figure 2 A flowchart of a method for training and rebuilding a judgment model for a method for rebuilding a database index;

[0064] Figure 5 Shown Figure 2 A flowchart of a method for training a reconstruction time prediction model for a method for rebuilding a database index;

[0065] Figure 6 Shown Figure 2 The training and prediction trend diagram of the reconstruction time prediction model of the method for rebuilding the database index;

[0066] Figure 7 Shown Figure 2 A flowchart of a method for rebuilding a database index and a method for training a traffic low-time period prediction model. DETAILED DESCRIPTION

[0067] To make the purpose, technical solutions, and advantages of this application more clear, the technical solutions of this application will be clearly and completely described below in conjunction with the specific embodiments of this application and the corresponding drawings. Obviously, the embodiments described are only part of the embodiments of this application, not all of them. Based on the embodiments in this application, all other embodiments obtained by ordinary technicians in this field without making creative efforts are within the scope of protection of this application.

[0068] The terms used in the implementation section of this application are only used to explain the specific embodiments of this application and are not intended to limit this application.

[0069] In order to solve the problem in the prior art of being unable to rebuild a database index in a timely manner, the present application provides a method and an electronic device for rebuilding a database index.

[0070] Figure 1 FIG2 is a schematic structural diagram of an electronic device according to an embodiment of the present application.

[0071] like Figure 1As shown, the electronic device includes a memory 1310 for storing computer program instructions and a processor 1320 for executing the program instructions, wherein when the computer program instructions stored in the memory 1310 are executed by the processor 1320, the electronic device is triggered to execute the method flow corresponding to the computer program instructions.

[0072] Specifically, the processor 1320 is constructed with a data acquisition module 110 , an index reconstruction determination module 120 and a model center 140 .

[0073] The data collection module 110 is used to collect relevant information from the database.

[0074] The model center 140 is used to load the calculation model.

[0075] Figure 2 for Figure 1 A schematic diagram of the logical structure of the model center 140 is shown.

[0076] like Figure 2 As shown, the model center 140 is used to save and load the reconstruction judgment model, the reconstruction time prediction model and the traffic low time period prediction model.

[0077] The index reconstruction judgment module 120 is used to obtain relevant information of the database collected by the data acquisition module 110; call the reconstruction judgment model, reconstruction duration prediction model and traffic low time period prediction model loaded by the model center 140; according to the relevant information of the database, use the reconstruction judgment model, reconstruction duration prediction model and traffic low time period prediction model to perform calculations, and select the first time period for database index reconstruction based on the calculation results.

[0078] Specifically, Figure 3 Shown is a flowchart of a method for rebuilding a database index according to an embodiment of the present application.

[0079] Figure 1 The electronic device shown performs the following steps: Figure 3 The method for rebuilding a database index is shown to instruct the server to rebuild the database index.

[0080] In step S200 , the data collection module 110 collects relevant information from the database.

[0081] Specifically, in step S200, the relevant information of the database includes: the index size of the database

[0082] Monitoring information includes: (indexsize), the number of database index scans (idx_scan), the database index fragmentation rate (leaf_fragmentation), the database index density (leaf_density), the processor configuration of the server implementing the database (for example, the number of processor cores), the number of read and write operations that the server can perform per second (the hard disk's Input / Output Operations Per Second, IOPS)), the server's memory size (work_mem), the server's memory parameter settings (maintenance_work_mem), and the number of connections to the database's front-end business interfaces.

[0083] The server's memory parameter setting refers to the server's available memory for database maintenance. For example, in an application scenario, the server's memory size is 8GB, and 4GB of it is set as the server's available memory for database maintenance.

[0084] Furthermore, in step S200, the data sources of the relevant information of the database include but are not limited to one or any combination of the following: database index and table information, CMBD server configuration information, and platform business flow monitoring data.

[0085] Specifically, in step S200, the relevant information of the database is obtained through a Structured Query Language (SQL) query. The specific acquisition steps include:

[0086] Create a database table (index_info). The database table (index_info) includes the following columns: id (number), creator (creator), create_date (creation time), update_date (update time), hostinfo (host address), dbname (database name), CPU, IOPS, work_mem, maintenance_work_mem, tablename (table name), indexname (index name), indexsize (index size), leaf_density (index density), leaf_fragmentation (index fragmentation rate), idx_scan (index scan times), is_redo (whether to rebuild), status, esti_duration (estimated duration), esti_starttime, esti_endtime, starttime, endtime;

[0087] Enter the database address and database name of the database;

[0088] Based on the entered database address and database name, call the SQL script to query index information. The queried index information includes index name (corresponding to indexname of the database table), index size (corresponding to indexsize of the database table), index scan times (corresponding to idx_scan of the database table), index fragmentation rate (corresponding to leaf_fragmentation of the database table), and index density (corresponding to leaf_density of the database table).

[0089] Write the queried index information into the database table (index_info);

[0090] Call the Configuration Management Database (CMDB) platform to query the database server information, including the server's CPU core count (corresponding to the CPU in the database table), the server's hard disk IOPS (corresponding to the IOPS in the database table), the server's memory size (corresponding to the work_mem in the database table), and the server's memory parameter settings (corresponding to the maintenance_work_mem in the database table).

[0091] In step S200, database-related information may be collected for one index of one database (the database table includes only one row of content); or, database-related information may be collected for multiple indexes of multiple databases, or, for multiple indexes of one database (the database table includes only multiple rows of content).

[0092] Table 1 shows part of the data in the database table (index_info) according to an embodiment of the present application.

[0093] Table 1

[0094]

[0095] As shown in Table 1, the columns of the database table (index_info) include table name, index name, index size, index density, index fragmentation rate, and index scan times.

[0096] Furthermore, in order to improve data accuracy, in step S200, data cleaning and data updating are performed on the collected relevant information of the database.

[0097] Specifically, in one embodiment, index information or server information containing null values is deleted. For example, if one or more of the index name, index size, index scan times, index fragmentation rate, and index density in the obtained index information is null, the index information needs to be deleted and the index information needs to be re-acquired. For another example, if one or more of the CPU core count, hard disk IOPS, memory size, and memory parameter settings in the obtained server information is null, the corresponding null value information needs to be deleted and the server information needs to be re-acquired.

[0098] Specifically, in one embodiment, index information or server information containing erroneous data is deleted. For example, assuming that the index fragmentation rate of the obtained index information is 100% (theoretically, this will not occur in actual application scenarios), the index information needs to be deleted and the index information needs to be re-acquired. For another example, assuming that the memory amount corresponding to the memory parameter setting in the obtained server information is greater than the memory size, the corresponding server information needs to be deleted and the server information needs to be re-acquired.

[0099] In step S210 , the index reconstruction determination module 120 calls the reconstruction determination model loaded by the model center 140 . The reconstruction determination model is used to determine whether database index reconstruction is required.

[0100] In application scenarios, there are many factors that determine whether database index reconstruction is necessary. In one embodiment of the present application, based on database maintenance experience analysis, model training and scoring comparison are performed to screen out significant features such as index fragmentation rate and index usage times that determine whether database index reconstruction is necessary, and non-significant features such as index density and index size are discarded.

[0101] Specifically, in one embodiment of the present application, the reconstruction judgment model determines whether database index reconstruction is required based on the index fragmentation rate and the number of index references.

[0102] Specifically, in one embodiment, the reconstruction judgment model is a decision tree algorithm model. Preferably, the reconstruction judgment model is a decision tree algorithm model in a supervised learning-classification algorithm.

[0103] Figure 4 Shown is a flowchart of a method for training a reconstruction judgment model according to an embodiment of the present application.

[0104] Before step S210, the electronic device is based on Figure 4 The following process shown trains the reconstruction judgment model.

[0105] Step S510, obtain database historical data, the database historical data includes the index scan number historical records and index fragmentation rate historical records of the database and / or other databases, the index scan number historical records include the index scan number record value and the corresponding time period, the index scan number historical records include the index fragmentation rate record value and the corresponding time period.

[0106] Step S520: Generate multiple characteristic values based on the database history data, where each characteristic value includes, for a database, a record value of the number of index scans and a record value of the index fragmentation rate corresponding to the same time period;

[0107] For example, in the history record of the number of index scans for database A1, the number of index scans recorded during the time period from 13:30:15 on October 9, 2022 to 13:31:25 on October 9, 2022 is 9.

[0108] For another example, in the index fragmentation rate history record for database A1, it is recorded that during the time period from 13:30:25 on October 9, 2022 to 13:33:15 on October 9, 2022, the index fragmentation rate record value is 13%.

[0109] A characteristic value T1 can be generated. The characteristic value T1 includes an index fragmentation rate record value of 13% and an index scan count record value of 9. The time period corresponding to the characteristic value T1 is from 13:30:25 on October 9, 2022 to 13:31:25 on October 9, 2022.

[0110] Step S530 : adding a flag (True, T) indicating that database index reconstruction is required to the feature value, or adding a flag (False, F) indicating that database index reconstruction is not required to the feature value, to generate multiple training samples.

[0111] Specifically, in step S530, based on the index scan count record value and the index fragmentation rate record value included in the characteristic value, it is determined whether to add a mark indicating that database index reconstruction is required for the characteristic value, or to add a mark indicating that database index reconstruction is not required for the characteristic value.

[0112] For example, when the recorded value of the index fragmentation rate is greater than a preset fragmentation rate threshold (e.g., 20%), and the recorded value of the index scan count is greater than a preset scan count threshold (e.g., 10), a flag indicating that database index reconstruction is required is added to the feature value;

[0113] When the recorded value of the index fragmentation rate is less than or equal to a preset fragmentation rate threshold, and / or the recorded value of the index scan times is less than or equal to a preset scan times threshold, a mark indicating that no database index reconstruction is required is added to the feature value.

[0114] Furthermore, in another embodiment, a database index reconstruction history record is obtained, and it is determined based on the database index reconstruction history record whether to add a mark indicating that database index reconstruction is required for the feature value, or to add a mark indicating that database index reconstruction is not required for the feature value.

[0115] Specifically, the database index reconstruction history includes the time at which the database index reconstruction was performed. Based on the database index reconstruction history, it is determined whether the database index reconstruction was performed within the time period corresponding to the characteristic value. If the database index reconstruction was performed at the time at which the characteristic value occurred, a flag indicating that the database index reconstruction needs to be performed is added to the characteristic value. If the database index reconstruction was not performed at the time at which the characteristic value occurred, a flag indicating that the database index reconstruction does not need to be performed is added to the characteristic value.

[0116] For example, in the database index reconstruction history record for database A1, it is recorded that the database index reconstruction was performed at 13:30:55 on October 9, 2022. The time when the database index reconstruction occurred is within the time period corresponding to the eigenvalue T1. Therefore, a mark indicating that the database index reconstruction needs to be performed is added to the eigenvalue T1 to generate the training sample X1.

[0117] Step S540: Use a decision tree algorithm, utilize a machine learning platform, and use multiple training samples to perform model training to obtain a reconstruction judgment model.

[0118] Furthermore, after step S540, the reconstruction judgment model is tested using the training samples. Specifically, the characteristic values of the training samples are input into the reconstruction judgment model, and the output of the reconstruction judgment model is compared with the characteristic value labels of the training samples to see if they are consistent. If they are consistent, it means that the output of the reconstruction judgment model is correct, and if they are inconsistent, it means that the output of the reconstruction judgment model is wrong. Use multiple training samples to test the reconstruction judgment model, obtain the output accuracy of the reconstruction judgment model, and when the output accuracy of the reconstruction judgment model reaches a preset standard (for example, 90%), the training of the reconstruction judgment model is completed. If the output accuracy of the reconstruction judgment model does not reach the preset standard, continue to train the reconstruction judgment model.

[0119] Table 2 shows some sample data and test results of the reconstruction judgment model training according to an embodiment of the present application.

[0120] Table 2

[0121] ID Index fragmentation rate Index scan count Label Test results 1 9.98 1304 F F 2 1.8 34673 F F 3 34.16 525881304 T T 4 30.77 1735836929 T T 5 35.37 15116277 T T 6 32.78 29405877 T T 7 33.88 0 F F 8 16.73 52764284 F F 9 30.98 1065862400 T T 19 31.64 85170 T T

[0122] In one embodiment, as shown in Table 2, the training samples include the feature values, the index fragmentation rate, and the number of times used (index scans) in the same row. The labels in the same row are the tags added to the feature values. The test results are the judgment results output by the reconstruction judgment model after the feature values are input into the reconstruction judgment model. When the tags added to the feature values are consistent with the judgment results output by the reconstruction judgment model, it indicates that the reconstruction judgment model output is correct.

[0123] After step S210, step S211 is executed.

[0124] Step S211 : input the index scanning times and index fragmentation rate in the relevant information of the database into the reconstruction judgment model to obtain the judgment result output by the reconstruction judgment model.

[0125] Specifically, in step S211, the index scan times (idx_scan) and the index fragmentation rate (leaf_fragmentation) of the database table (index_info) are input into the reconstruction judgment model to obtain the judgment result output by the reconstruction judgment model. The judgment result includes whether the database index reconstruction needs to be performed (T) or whether the database index reconstruction does not need to be performed (F).

[0126] The judgment result output by the reconstruction judgment model is recorded in the database table (index_info) (corresponding to the is_redo of the database table). Specifically, when the reconstruction judgment model outputs T, T is recorded in the is_redo of the database table; when the reconstruction judgment model outputs F, F is recorded in the is_redo of the database table.

[0127] Furthermore, when the database table (index_info) includes multiple rows of content, the database table

[0128] The index scan times (idx_scan) and index fragmentation rate (leaf_fragmentation) of each row of (index_info) are input into the reconstruction judgment model, and the judgment results corresponding to each row are obtained and recorded.

[0129] Table 3 shows part of the data after the database table (index_info) shown in Table 1 is filled with the determination results according to one embodiment of the present application.

[0130] Table 3

[0131]

[0132] In one embodiment, as shown in Table 3, the columns of the database table (index_info) include table name, index name, index size, index density, index fragmentation rate, index scan times, and whether the database index needs to be rebuilt.

[0133] According to the method of the embodiment of the present application, whether the database index needs to be rebuilt is determined based on the reconstruction judgment model, thereby improving the timeliness of database index reconstruction.

[0134] Furthermore, after step S211, the rows in the database table (index_info) where is_redo is F are deleted to reduce the amount of data in the database table (index_info).

[0135] After step S211, when the reconstruction judgment model outputs a determination result that database index reconstruction is not necessary (is_redo for each row in the database table (index_info) is F (or the database table (index_info) is empty)), step S212 is executed. Step S212 ends the process of the method for reconstructing the database index.

[0136] After step S211, if the reconstruction judgment model outputs a determination that database index reconstruction is required (is_redo for one or more rows in the database table (index_info) is T), step S220 is executed. In step S220, the index reconstruction judgment module 120 calls the reconstruction duration prediction model loaded by the model center 140. The reconstruction duration prediction model is used to predict the estimated time required for the server to perform database index reconstruction.

[0137] In application scenarios, there are many factors that affect the time it takes for a server to rebuild a database index. In one embodiment of the present application, based on database maintenance experience, factors affecting the time it takes to rebuild an index are analyzed, and the index size, index density, number of CPU cores, server IOPS, database work_mem parameter, and maintenance_work_mem parameter are selected as significant feature values that affect the time it takes for a server to rebuild an index. Non-significant features such as table size and number of table rows are discarded.

[0138] Specifically, in one embodiment, the reconstruction time prediction model predicts the time it takes for the server to rebuild the database index based on the index size, index density, server processor configuration, the number of read and write operations that the server can perform per second, the server's memory size, and the server's memory parameter settings.

[0139] Specifically, in one embodiment, the reconstruction time prediction model is a multiple linear regression algorithm model. Specifically, the reconstruction time prediction model is a supervised learning-regression algorithm-multiple linear regression algorithm model.

[0140] Figure 5 Shown is a flowchart of a method for training a reconstruction duration prediction model according to an embodiment of the present application.

[0141] Before S220, electronic devices were based on Figure 5 The following process is shown to train the reconstruction duration prediction model.

[0142] Step S710: Obtain database index reconstruction history records of the database and / or other databases, where the database index reconstruction history records include a database index reconstruction duration record value and a time when the database index reconstruction is executed.

[0143] Step S720, obtain database historical data, which includes index size history records of the database and / or other databases, index density history records, server processor configuration records, number of read and write operations that the server can perform per second, server memory size records, and server memory parameter setting records.

[0144] Index size history records include index size records and corresponding time periods. Specifically, when database data changes during database operation, resulting in changes in the database index size, the server records the latest index size in the index size history records and also records the time when the index size record value changed. The time period between the two times the index size changes occurred before and after the index size record value is the time period corresponding to the index size record value.

[0145] Index density history records include index density values and corresponding time periods. Specifically, when database data changes during database operation, resulting in changes in the database's index density, the server records the latest index density in the index size history and also records the time when the index density value change occurred. The time period between the two index density changes before and after the index density value change is the time period corresponding to the index density value.

[0146] The server's processor configuration record includes the server's processor configuration record value and the corresponding time period. Specifically, when the server's processor configuration changes (for example, upgrading a dual-core processor to a triple-core processor), the server records the latest processor configuration in the processor configuration record and also records the time when the processor configuration record value changes. The time period between the two times the processor configuration record value changes is the time period corresponding to the processor configuration record value.

[0147] The record of the number of read and write operations that the server can perform per second includes the record value of the number of read and write operations that the server can perform per second and the corresponding time period. Specifically, when the number of read and write operations that the server can perform per second changes (for example, upgrading the hard disk and configuring a hard disk with a higher read and write speed), the server records the latest number of read and write operations that can be performed per second in the processor configuration record and simultaneously records the time when the change in the record value of the number of read and write operations per second occurs. The time period between the two times when the change in the record value of the number of read and write operations per second occurs is the time period corresponding to the record value of the number of read and write operations per second.

[0148] A server memory size record includes the server memory size record value and the corresponding time period. Specifically, when the server memory size changes (for example, by upgrading the memory or configuring a larger memory capacity), the server records the latest memory size in the processor configuration record and also records the time when the memory size record value changed. The time period between the times when the two memory size record values changed is the time period corresponding to the memory size record value.

[0149] A server memory parameter setting record includes the server memory parameter setting record value and the corresponding time period. Specifically, when a server memory parameter setting changes (for example, configuring more memory for database maintenance), the server records the latest memory parameter setting in the processor configuration record and also records the time when the memory parameter setting record value changed. The time period between the time when the two memory parameter setting record values changed is the time period corresponding to the memory parameter setting record value.

[0150] Furthermore, in some application scenarios, one or more of the server's processor configuration, the number of read and write operations per second, the memory size, and the memory parameter settings are inherent properties of the server, and the scenarios in which they change are not considered. In this application scenario, the items that do not change in the processor configuration, the number of read and write operations per second, the memory size, and the memory parameter settings do not have a corresponding time period (or, the corresponding time period is valid for a long time).

[0151] Furthermore, since the index size, index density, server processor configuration, the number of read and write operations that the server can perform per second, the server's memory size, and the server's memory parameter settings predict that the data volume levels between servers vary greatly (for example, the index size is at the MB level, and the memory size and memory parameter settings are at the GB level), in step S720, the record data in the database historical data is standardized, for example, the values of the index size, memory size, and memory parameter settings are set to fall within the range of -1 to 0.

[0152] For example, in a real-world scenario, assuming the physical upper limit of server memory size is 32GB, set the corresponding normalized range of server memory size to -1 to 0 (-1 corresponds to 0GB, and 0 corresponds to 32GB). If the server memory size is 16GB, the normalized value is -0.5.

[0153] For example, in a real-world scenario, assuming the upper limit of the index size is 500MB, the normalized range for the index size is set to -1 to 0 (-1 corresponds to 0MB, and 0 corresponds to 500MB). If the index size is 20MB, the normalized value is -0.96.

[0154] Step S730 : generating a plurality of training samples according to the database index reconstruction history records, each training sample including a database index reconstruction duration record value.

[0155] Step S740, adding feature values for multiple training samples based on database historical data, wherein: the feature values include index density record values, index size record values, server processor configuration record values, server read and write operation times per second record values, server memory size record values, and server memory parameter setting record values.

[0156] In the same training sample, the recorded values of database index rebuilding time, index density, index size, server processor configuration, number of read and write operations per second, server memory size, and server memory parameter setting correspond to the same database and the same time period.

[0157] For example, in the database index reconstruction history record for database A1, it is recorded that the database index reconstruction was performed at 13:30:55 on October 9, 2022, and the execution duration of the database index reconstruction was 3 seconds.

[0158] In the index size history record for database A1, the index size record value for the period from 13:30:14 on October 9, 2022 to 13:31:26 on October 9, 2022 is 50 MB.

[0159] In the index density history record for database A1, it is recorded that during the time period from 13:30:15 on October 9, 2022 to 13:31:27 on October 9, 2022, the index density record value is 65.15.

[0160] In the processor configuration record for the server of database A1, the processor configuration record value is 4 cores, 1.33 GHz. In addition, the processor configuration is valid for a long time and does not have a corresponding time period.

[0161] In the record of the number of read and write operations that can be performed per second on the server of database A1, the record value of the number of read and write operations that can be performed per second is 1000. Moreover, the number of read and write operations that can be performed per second is valid for a long time and does not have a corresponding time period.

[0162] In the memory size record for the server of database A1, the memory size record value is 16GB. Moreover, the memory size is valid for a long time and does not have a corresponding time period.

[0163] In the memory parameter setting record for the server of database A1, the memory parameter setting record value is 8GB. In addition, the memory parameter setting is valid for a long time and does not have a corresponding time period.

[0164] Create training sample X2. This training sample includes a database index rebuild duration of 3 seconds. Add feature value T2 to training sample X2. Feature value T2 includes an index size record value of 50MB, an index density record value of 65.15, a processor configuration record value of 4 cores / 1.33GHz, a read and write operations per second record value of 1000, a memory size record value of 16GB, and a memory parameter setting record value of 8GB.

[0165] Step S750: Use a multiple linear regression algorithm, utilize a machine learning platform, and use multiple training samples to perform model training to obtain a reconstruction time prediction model.

[0166] Furthermore, after step S750, the reconstruction time prediction model is tested using the training sample. Specifically, the characteristic value of the training sample is input into the reconstruction time prediction model, and the difference between the predicted time output by the reconstruction judgment model and the database index reconstruction time record value of the training sample is compared to see whether it is within a preset range. If the difference is within the preset range, it means that the reconstruction judgment model output is correct. If the difference is outside the preset range, it means that the reconstruction judgment model output is incorrect. Specifically, when the difference between the predicted time and the database index reconstruction time record value is less than 10% of the database index reconstruction time record value, the difference between the predicted time and the database index reconstruction time record value is within the preset range.

[0167] Figure 6 Shown is a training and prediction trend diagram of a reconstruction duration prediction model according to an embodiment of the present application.

[0168] like Figure 6 As shown in Figure 3, the prediction results of the reconstruction time prediction model are consistent with the database index reconstruction time record values of the training samples.

[0169] Table 4 shows part of the training data of the reconstruction duration prediction model according to an embodiment of the present application.

[0170] Table 4

[0171]

[0172]

[0173] As shown in Table 4, index size, index density, CPU, IOPS, memory size (mem1), and memory parameter settings (mem2) are standardized data. "time" represents the recorded database index rebuild duration for the training sample. The index size, index density, CPU, IOPS, memory size (mem1), and memory parameter settings (mem2) are input into the rebuild duration prediction model, which then outputs the test results.

[0174] As shown in Table 4, the deviation between the test results and time is large, and the reconstruction time prediction model needs to be further trained.

[0175] The reconstruction duration prediction model is tested using multiple training samples to obtain the output accuracy of the reconstruction duration prediction model. When the output accuracy of the reconstruction duration prediction model reaches a preset standard (e.g., 90%), the training of the reconstruction duration prediction model is completed. If the output accuracy of the reconstruction duration prediction model does not reach the preset standard, the training of the reconstruction duration prediction model continues.

[0176] After step S220, step S221 is executed.

[0177] Step S221, input the index size, index density, processor configuration, the number of read and write operations that the server can perform per second, the memory size of the server, and the memory parameter settings of the server in the relevant information of the database into the reconstruction time prediction model to obtain the predicted time output by the reconstruction time prediction model (in one embodiment, the predicted time is in seconds).

[0178] Specifically, in step S221, the indexsize, leaf_density, CPU, IOPS, work_mem and maintenance_work_mem of the row where is_redo is recorded as T in the database table (index_info) are input into the reconstruction duration prediction model, the predicted time output by the reconstruction duration prediction model is obtained, and the predicted time is recorded in esti_duration.

[0179] Furthermore, when the database table (index_info) includes multiple rows where is_redo is T, the indexsize, leaf_density, CPU, IOPS, work_mem, and maintenance_work_mem of each row where is_redo is T in the database table (index_info) are input into the reconstruction duration prediction model, and the predicted duration corresponding to each row is obtained and recorded in the esti_duration of the corresponding row.

[0180] After step S211, when the determination result output by the reconstruction judgment model indicates that database index reconstruction is required, in addition to executing steps S220 and S221 to obtain the predicted time output by the reconstruction duration prediction model, step S230 is also executed.

[0181] In step S230, the index reconstruction determination module 120 calls the traffic valley time period prediction model loaded by the model center 140. The traffic valley time period prediction model is used to predict subsequent changes in the number of front-end business interface connections of the database based on the monitoring information of the number of front-end business interface connections of the database.

[0182] Specifically, in one embodiment, the traffic low-time period prediction model is a K-Nearest Neighbor (KNN) model.

[0183] Figure 7 Shown is a flowchart of a method for training a traffic low-time period prediction model according to an embodiment of the present application.

[0184] Before step S230, the electronic device is based on Figure 7 The following process is shown to train a traffic low-time period prediction model.

[0185] Step S810: Acquire monitoring history records of the number of front-end service interface connections of the database and / or other databases. The monitoring history records of the number of front-end service interface connections include record values of the number of front-end service interface connections and corresponding time periods.

[0186] Specifically, in step S810, the monitoring data on the number of front-end business interface connections in the monitoring platform (for example, zabbix) is queried to obtain the monitoring data for the past year.

[0187] Step S820: Acquire host access history data of the database and / or other databases, where the host access history data includes the address of the host accessing the database and / or other databases and the time when the access occurred.

[0188] Step S830 : generating a training sample set including a plurality of training samples according to the monitoring history records of the number of front-end service interface connections, wherein each training sample in the training sample set includes a record value of the number of front-end service interface connections.

[0189] Step S840: Add feature values to the training samples in the training sample set according to the host access history data, wherein:

[0190] The characteristic value includes the host address of the host making the access and the time when the access occurs;

[0191] The host address and front-end business interface connection number records in the same training sample correspond to the same database;

[0192] The access occurrence time in the same training sample is within the time period corresponding to the record value of the front-end business interface connection number.

[0193] For example, in the monitoring history record of the number of front-end business interface connections of database A1, it is recorded that the number of front-end business interface connections was 5 during the time period from 12:30:55 on October 9, 2022 to 12:35:27 on October 9, 2022.

[0194] In the host access history data for database A1, it is recorded that at 12:32:14 on October 9, 2022, the host with host address Z1 accessed the database.

[0195] Create training sample X3, which includes a record value of 5 for the number of front-end service interface connections. Add feature value T3 to training sample X3, which includes host address Z1 and access time 12:32:14, October 9, 2022.

[0196] Furthermore, in one embodiment, the time period corresponding to the access occurrence time in the host access history data and the front-end service interface connection number record value in the monitoring history record of the front-end service interface connection number is converted into a periodic description method with a year as the cycle.

[0197] Specifically, convert the time to week (week is the week number of the year (a year has 52 weeks), weekday (week has 7 days), hour (hour is the hour number of the day (a day has 24 hours), and minutes (minutes is the minute number of the hour (an hour has 60 minutes). For example, January 6, 2022, 5:10 am, is converted to week(1)weekday(6)hour(5)minutes(10).

[0198] Step S850: Use the K-nearest neighbor regression algorithm, utilize the machine learning platform, and use the training sample set to perform model training to obtain a traffic low-valley time period prediction model.

[0199] Furthermore, after step S850, the traffic trough period prediction model is tested using the training sample. Specifically, the characteristic value of the training sample is input into the traffic trough period prediction model, and the difference between the predicted value of the number of front-end business interface connections output by the traffic trough period prediction model and the recorded value of the number of front-end business interface connections of the training sample is compared to see whether it is within a preset range. If the difference is within the preset range, it means that the output of the traffic trough period prediction model is correct. If the difference is outside the preset range, it means that the output of the traffic trough period prediction model is incorrect. Specifically, when the difference between the predicted value and the recorded value of the number of front-end business interface connections is less than 10% of the recorded value of the number of front-end business interface connections, the difference between the predicted value and the recorded value of the number of front-end business interface connections is within the preset range.

[0200] Table 5 shows part of the training data of the low traffic period prediction model according to an embodiment of the present application.

[0201] host week weekday hour minute value Test results 0 13 6 23 5 152.0367 159.20088 0 13 6 23 5 155.8981 159.20088 0 13 6 23 5 162.8581 159.20088 0 13 6 23 5 168.9614 159.20088 0 13 6 23 4 233.772 293.86074 0 13 6 23 4 220.3243 293.86074 0 13 6 23 4 241.0623 293.86074 0 13 6 23 4 215.5407 293.86074 0 13 6 23 3 261.03 296.36378 0 13 6 23 3 255.8694 296.36378 0 13 6 23 3 271.6318 296.36378 0 13 6 23 3 260.4238 296.36378 0 13 6 23 2 262.7971 283.79818 0 13 6 23 2 271.9616 283.79818

[0202] As shown in Table 5, the feature values of the training samples (access occurrence time) are converted into

[0203] The format is week / weekday / hour / minutes, which means minutes 2 to 5 of the 23rd hour of the 6th day of the 13th week. If the recorded value of the front-end service interface connection count is consistent with the output value (test result) of the traffic low-time prediction model, the traffic low-time prediction model can be used.

[0204] Furthermore, in actual application scenarios, different database services have different characteristics, resulting in different periodic variations in the number of front-end service interface connections. To address these characteristics, in one embodiment, the training sample set is split into multiple sub-training sample sets based on the differences in database services, with each sub-training sample set corresponding to a database service. Model training is performed using the sub-training sample sets to obtain a prediction model for the low-traffic time periods of the corresponding database services.

[0205] After step S230, step S231 is executed.

[0206] Step S231: input the monitoring information of the number of front-end service interface connections of the database in the relevant information of the database into the traffic valley time period prediction model, and obtain the prediction result of the subsequent change of the number of front-end service interface connections output by the traffic valley time period prediction model.

[0207] Specifically, in one embodiment, the host address of the host currently accessing the database is obtained based on monitoring information about the number of front-end service interface connections of the database and recorded in the hostinfo of the database table (index_info). The host address recorded in the hostinfo of the database table (index_info) is input into a low-traffic time period prediction model to obtain a predicted value of the number of front-end service interface connections output by the low-traffic time period prediction model and the time period corresponding to the predicted value.

[0208] Step S240 , predicting multiple operational time periods of the server based on the prediction results, wherein the time period when the number of connections of the front-end business interface of the database is less than a preset connection number threshold is the operational time period of the server.

[0209] Specifically, the front-end service interface connection number prediction value is compared with a preset connection number threshold, and the front-end service interface connection number prediction value that is less than the preset connection number threshold is screened out from the front-end service interface connection number prediction values.

[0210] The preset connection threshold is set based on server processing resources. When the number of connections to the front-end service interface is greater than or equal to the connection threshold, the server's processing resources are insufficient to rebuild the database index. Only when the number of connections to the front-end service interface is less than or equal to the connection threshold, the server's processing resources are sufficient to rebuild the database index. Specifically, the connection threshold is between 50 and 300.

[0211] The server's operational time period is predicted based on the time period corresponding to the predicted value of the front-end business interface connection number that is less than the preset connection number threshold. When multiple time periods corresponding to the predicted value of the front-end business interface connection number that is less than the preset connection number threshold are adjacent time periods, the adjacent time periods are spliced together to form an operational time period; when the time period corresponding to the predicted value of the front-end business interface connection number that is less than the preset connection number threshold is an independent time period, the independent time period is used as the operational time period.

[0212] After steps S220 to S221 and steps S230 to S240 are all executed, the predicted time consumption output by the reconstruction duration prediction model is obtained, and multiple operational time periods of the server are predicted, step S250 is executed.

[0213] Step S250: Select a reconstructable operation time period from the predicted multiple operable time periods according to the predicted time consumption output by the reconstruction time length prediction model, wherein the reconstructable operation time period is the first operable operation time period after the current moment whose duration is greater than or equal to the predicted time consumption.

[0214] For example, in one embodiment, the predicted duration required to rebuild the database index is TS, and the predicted operable time periods for rebuilding the database index include time periods T1, T2, T3, and T4 starting from the current moment. Time periods T1 and T4 are less than time period TS. Time periods T2 and T3 are greater than time period TS. To avoid database index rebuild failure, database index rebuilding must be performed continuously. Therefore, time period T2 or T3 is selected as the operable time period for rebuilding the database index. Furthermore, to avoid database service errors, the database index must be rebuilt as quickly as possible. Therefore, time period T2 is selected as the operable time period for rebuilding the database index.

[0215] Furthermore, in another embodiment, when there is no operational time period that is greater than or equal to the predicted time consumption in the predicted operational time period for database index reconstruction, the index reconstruction determination module 120 outputs a reminder message to the administrator, and the administrator specifies the time period for performing index reconstruction.

[0216] According to the method of the embodiment of the present application, the operation time period for rebuilding the database index can be optimized, effectively preventing the rebuilding of the database index from affecting the execution of other database services.

[0217] Step S260: instruct the server to rebuild the database index during the rebuildable operation time period.

[0218] Specifically, in step S260, the index rebuild determination module 120 creates a (time-task) scheduled job based on the selected available time period. For example, an executable script is created by combining the data source information, the rebuild statement, and the rebuild start time and added to the job schedule. The Python code is then connected to the server's scheduled task system, which then executes the scheduled job created by the index rebuild determination module 120.

[0219] Specifically, the server reorganizes the index or regenerates the index during the database index rebuilding process.

[0220] Specifically, reorganizing the index includes reordering the data in the outermost data pages of the database and compressing the index pages, thereby reducing the amount of indexed data.

[0221] Reindexing involves deleting the existing database index and generating a new one. During the reindexing process, all index fragments in the original index are deleted. The logical order of the newly generated index is consistent with the physical order of the data stored in the database, greatly improving index retrieval speed.

[0222] When the server rebuilds the database index, it determines whether to use either reorganizing the index or regenerating the index based on the current database status. For example, if the amount of updated data in the database exceeds 10% of the total data volume compared to the last database index rebuild, the database index rebuild method is used; if the amount of updated data in the database is less than or equal to 10% of the total data volume compared to the last database index rebuild, the database index rebuild method is used.

[0223] Specifically, in steps S231 to S260, a session table (session_info) is created. This table includes the following columns: hostinfo (host address), dbname (database name), week (week), weekday (day), hour (hour), minute (minute), sessions, and esti_load. The week, weekday, hour, and minute columns represent the time by converting a year into 52 weeks, represented by week, day, hour, and minute.

[0224] Get the predicted value of the number of front-end business interface connections over time periods in the next month output by the traffic low time period prediction model, and include it in the sessions of the time period table (session_info).

[0225] Determine whether the session value exceeds the connection threshold. If so, record "high" in the esti_load column of the session table (session_info). If not, record "low" in the esti_load column of the session table (session_info).

[0226] Match each value of eti_duration in the database table (index_info) with a period of time when eti_load was low. Specifically, convert eti_duration in the database table (index_info) to minutes and record the consecutive periods of low eti_load as a time interval in the database table (index_info) as esti_starttime and esti_endtime. If eti_duration is less than esti_endtime minus esti_starttime, use eti_starttime as the start time for index reconstruction.

[0227] Create an index rebuild script and add the index's hostinfo, rebuild_sql (fixed syntax + index name, for example: rebuild index concurrently indexname), and esti_starttime to the job schedule (crontab).

[0228] Furthermore, it is considered that the index reconstruction determination module 120 is based on the predicted operable time period for database index reconstruction (predicted traffic low period), and further selects the operable time period for database index reconstruction.

[0229] Therefore, in order to avoid errors in the prediction of the operational time period that can be used for database index reconstruction, which causes the reconstruction of the database index to affect the normal operation of other database services, after S260, before the server rebuilds the database index during the rebuildable operational time period, it is necessary to confirm whether the load status of the server that implements the database service meets the requirements for rebuilding the database index.

[0230] Specifically, after step S260, the server performs the following steps:

[0231] At the start of the rebuildable job time period, the current load status of the server is obtained.

[0232] Determine whether the current load of the server meets the execution requirements for rebuilding the database index.

[0233] When the current load of the server meets the execution requirements of rebuilding the database index, the database index is rebuilt during the rebuildable operation time period.

[0234] When the current load of the server does not meet the execution requirement of rebuilding the database index, the database index rebuilding operation is canceled.

[0235] Specifically, in one embodiment, it is determined whether the current server load value (e.g., the percentage of system resources occupied by the current load) exceeds a preset value X (the value X is defined based on the level of service busyness, e.g., 60%). When the current server load value exceeds the preset value X, it is determined that the current server load does not meet the execution requirements for rebuilding the database index.

[0236] Specifically, the server calls the job scheduler (crontab) to execute the index rebuild script. The index rebuild script queries the current server load (load average). If the load average exceeds X, the index rebuild script is canceled. Otherwise, the index rebuild script (rebuild_sql) is executed. The actual rebuild start and end times are written to the starttime and endtime columns of the index_info table.

[0237] Furthermore, in another embodiment, the current database server load is detected. If the server load within 5m is less than 1 and the number of server connections is less than 100, it is determined that the current server load meets the execution requirements for rebuilding the database index.

[0238] According to the method of the embodiment of the present application, the reconstruction judgment model can automatically determine whether the database index needs to be rebuilt, and the operation time period for rebuilding the database index is preferably selected based on the reconstruction duration prediction model and the traffic low time period prediction model, thereby improving the timeliness of database index reconstruction and effectively avoiding the reconstruction of the database index affecting the execution of other database services.

[0239] In the description of the embodiments of the present application, for the convenience of description, the device is described as being divided into various modules according to their functions. The division of each module is merely a division of logical functions. When implementing the embodiments of the present application, the functions of each module can be implemented in the same or multiple software and / or hardware.

[0240] Specifically, the device proposed in the embodiment of the present application can be fully or partially integrated into a physical entity during actual implementation, or it can be physically separated. And these modules can all be implemented in the form of software calling through processing elements; or they can all be implemented in the form of hardware; or some modules can be implemented in the form of software calling through processing elements, and some modules can be implemented in the form of hardware. For example, the detection module can be a separately established processing element, or it can be integrated in a chip of an electronic device. The implementation of other modules is similar. In addition, these modules can be fully or partially integrated together, or they can be implemented independently. During the implementation process, each step of the above method or each of the above modules can be completed by the hardware integrated logic circuit in the processor element or the instructions in the form of software.

[0241] Those skilled in the art will appreciate that the various units and algorithm steps described in the embodiments of the present application can be implemented using a combination of electronic hardware, computer software, and electronic hardware. Whether these functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. Professionals and technicians can use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of this application.

[0242] Those skilled in the art will clearly understand that, for the convenience and brevity of description, the specific working processes of the above-described devices, apparatuses and units can refer to the corresponding processes in the aforementioned method embodiments and will not be repeated here.

[0243] Those skilled in the art will appreciate that the embodiments of the present application may be provided as methods, apparatus, or computer program products. Thus, the present invention may take the form of an entirely hardware embodiment, an entirely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention may take the form of a computer program product implemented on one or more computer-usable storage media containing computer-usable program code.

[0244] In the several embodiments provided in this application, if any function is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this application, or the part that contributes to the prior art, or part of the technical solution, can be embodied in the form of a software product, which is stored in a storage medium and includes a number of instructions for enabling a computer device (which can be a personal computer, server, or network device, etc.) to perform all or part of the steps of the method described in each embodiment of this application.

[0245] Specifically, an embodiment of the present application further provides a computer-readable storage medium, which stores a computer program. When the computer-readable storage medium is run on a computer, the computer executes the method provided in the embodiment of the present application.

[0246] An embodiment of the present application further provides a computer program product, which includes a computer program. When the computer program product is run on a computer, it enables the computer to execute the method provided in the embodiment of the present application.

[0247] The description of the embodiments in this application is described with reference to the flowcharts and / or block diagrams of the methods, devices (apparatus), and computer program products according to the embodiments of the application. It should be understood that each process and / or box in the flowchart and / or block diagram, as well as the combination of the processes and / or boxes in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the steps in the process. Figure 1 a process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0248] These computer program instructions may also be stored in a computer readable memory that can direct a computer or other programmable data processing device to work in a specific manner, so that the instructions stored in the computer readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 a process or multiple processes and / or boxes Figure 1 The function specified in one or more boxes.

[0249] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operational steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing the instructions executed on the computer or other programmable device for implementing the process. Figure 1 a process or multiple processes and / or boxes Figure 1 The steps for the function specified in one or more boxes.

[0250] It should also be noted that, in the embodiments of the present application, "at least one" refers to one or more, and "more than one" refers to two or more. "And / or" describes the association relationship of associated objects, indicating that three relationships may exist. For example, A and / or B can represent the existence of A alone, the existence of A and B at the same time, and the existence of B alone. Among them, A and B can be singular or plural. The character " / " generally indicates that the previous and subsequent associated objects are in an "or" relationship. "At least one of the following" and similar expressions refer to any combination of these items, including any combination of single or plural items. For example, at least one of a, b and c can represent: a, b, c, a and b, a and c, b and c or a and b and c, where a, b, c can be single or multiple.

[0251] In the embodiments of the present application, the terms "comprises," "includes," or any other variations thereof are intended to encompass non-exclusive inclusion, such that a process, method, commodity, or apparatus comprising a series of elements includes not only those elements but also other elements not explicitly listed, or elements inherent to such process, method, commodity, or apparatus. In the absence of further limitations, an element defined by the phrase "comprising a ..." does not preclude the presence of additional identical elements in the process, method, commodity, or apparatus comprising the element.

[0252] The present application may be described in the general context of computer-executable instructions executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, etc. that perform specific tasks or implement specific abstract data types. The present application may also be practiced in distributed computing environments where tasks are performed by remote processing devices connected through a communications network. In a distributed computing environment, program modules may be located in local and remote computer storage media, including storage devices.

[0253] The various embodiments in this application are described in a progressive manner. Similar parts between the various embodiments can be referred to in conjunction with each other. Each embodiment focuses on the differences from other embodiments. In particular, the device embodiments are generally similar to the method embodiments, so the description is relatively simple. For relevant parts, refer to the partial description of the method embodiments.

[0254] The above description is merely a specific embodiment of the present application. Any person skilled in the art may easily conceive of variations or substitutions within the technical scope disclosed in this application, and such variations or substitutions shall be within the scope of protection of this application. The scope of protection of this application shall be subject to the scope of protection of the claims.

Claims

1. A method for rebuilding a database index, characterized in that: The method is applied to an electronic device, and includes: Collecting relevant information about the database, including: the size of the database index, the number of index scans of the database, the index fragmentation rate of the database, the index density of the database, the processor configuration of the server implementing the database, the number of read and write operations that can be performed by the server per second, the memory size of the server, the memory parameter settings of the server, and monitoring information on the number of front-end business interface connections of the database; Calling a reconstruction judgment model, wherein the reconstruction judgment model is used to judge whether database index reconstruction is required according to the index fragmentation rate and the index scan times; Inputting the index scan times and the index fragmentation rate into the reconstruction judgment model, and obtaining a judgment result output by the reconstruction judgment model, wherein the judgment result includes whether database index reconstruction is required or not; When the determination result is that the database index reconstruction is not required, the process of the method for reconstructing the database index is terminated; When the determination result is that the database index needs to be rebuilt, calling a reconstruction time prediction model, the reconstruction time prediction model is used to predict the time taken by the server to rebuild the database index based on the index size, the index density, the processor configuration of the server, the number of read and write operations that can be performed by the server per second, the memory size of the server, and the memory parameter settings of the server; Inputting the index size, the index density, the processor configuration, the number of read and write operations that can be performed by the server per second, the memory size of the server, and the memory parameter settings of the server in the relevant information of the database into the reconstruction time prediction model, and obtaining the predicted time output from the reconstruction time prediction model; When the determination result is that the database index needs to be rebuilt, calling the traffic valley time period prediction model, the traffic valley time period prediction model is used to generate a prediction result of the subsequent change of the number of front-end service interface connections of the database based on the monitoring information of the number of front-end service interface connections of the database; Inputting the monitoring information of the number of front-end service interface connections of the database into the traffic low-time period prediction model, and obtaining the prediction result outputted from the traffic low-time period prediction model; Predicting multiple operational time periods of the server based on the prediction results, wherein a time period during which the number of connections of the front-end service interface of the database is less than a preset connection number threshold is the operational time period of the server; Selecting a reconstructable operation time period from the predicted multiple operable operation time periods, wherein the reconstructable operation time period is the first operable operation time period after the current moment and having a duration greater than or equal to the predicted time consumption; The server is instructed to rebuild the database index during the rebuildable operation time period.

2. The method according to claim 1, characterized in that The database index reconstruction includes: Reorganizing the index, wherein the reorganizing the index includes reordering the data in the outermost data pages of the database and compressing the index pages; or, Regenerating the index, wherein the regenerating the index includes deleting the original index of the database and generating a new index.

3. The method according to claim 1, characterized in that The reconstruction judgment model is a decision tree algorithm model; The method further comprises: Acquire historical data of the database, the historical data of the database including historical records of index scan times and index fragmentation rates of the database and / or other databases, the historical records of index scan times including recorded values of index scan times and corresponding time periods, and the historical records of index scan times including recorded values of index fragmentation rates and corresponding time periods; Generating a plurality of characteristic values according to the historical data of the database, wherein each characteristic value includes, for the same database, a record value of the number of index scans and a record value of the index fragmentation rate corresponding to the same time period; Adding a mark indicating that the database index needs to be rebuilt to the feature value, or adding a mark indicating that the database index does not need to be rebuilt to the feature value, to generate a plurality of training samples; A decision tree algorithm is used and a machine learning platform is utilized to perform model training using the multiple training samples to obtain the reconstruction judgment model.

4. The method according to claim 3, characterized in that The method further comprises: Acquire the database index reconstruction history record, where the database index reconstruction history record includes the time when the database index reconstruction was executed; The adding a mark indicating that the database index reconstruction needs to be performed to the feature value, or adding a mark indicating that the database index reconstruction does not need to be performed to the feature value, to generate a plurality of training samples, includes: Based on the database index reconstruction history, confirming whether the database index reconstruction was performed within the time period corresponding to the characteristic value; When the database index reconstruction is executed within the time period corresponding to the characteristic value, adding a mark indicating that the database index reconstruction needs to be executed to the characteristic value; When the database index reconstruction is not performed within the time period corresponding to the feature value, a mark indicating that the database index reconstruction is not required is added to the feature value.

5. The method according to claim 3, characterized in that The adding a mark indicating that the database index reconstruction needs to be performed to the feature value, or adding a mark indicating that the database index reconstruction does not need to be performed to the feature value, to generate a plurality of training samples, includes: When the recorded value of the index fragmentation rate is greater than a preset fragmentation rate threshold, and the recorded value of the index scan times is greater than a preset scan times threshold, adding a mark indicating that the database index reconstruction needs to be performed to the characteristic value; When the recorded value of the index fragmentation rate is less than or equal to a preset fragmentation rate threshold, and / or the recorded value of the index scan times is less than or equal to a preset scan times threshold, a mark indicating that the database index reconstruction does not need to be performed is added to the feature value.

6. The method according to claim 1, characterized in that The reconstruction time prediction model is a multiple linear regression algorithm model; The method further comprises: Obtaining a database index reconstruction history of the database and / or other databases, wherein the database index reconstruction history includes a database index reconstruction duration record value and a time when the database index reconstruction is executed; Obtaining database historical data, the database historical data including index size history records, index density history records, server processor configuration records, records of the number of read and write operations that can be performed by the server per second, server memory size records, and server memory parameter setting records of the database and / or other databases, the index size history records including index size record values and corresponding time periods, the index density history records including index density record values and corresponding time periods, the server processor configuration records including server processor configuration record values and corresponding time periods, the server read and write operation count records including server read and write operation count records including server memory size record values and corresponding time periods, the server memory size records including server memory size record values and corresponding time periods, and the server memory parameter setting records including server memory parameter setting record values and corresponding time periods; Generate a plurality of training samples according to the database index reconstruction history records, each training sample including a database index reconstruction duration record value; Adding feature values to the multiple training samples based on the database historical data, wherein: the feature values include the index density record value, the index size record value, the server processor configuration record value, the server read and write operation number per second record value, the server memory size record value, and the server memory parameter setting record value; in the same training sample, the database index reconstruction time record value, the index density record value, the index size record value, the server processor configuration record value, the server read and write operation number per second record value, the server memory size record value, and the server memory parameter setting record value correspond to the same database and the same time period; A multivariate linear regression algorithm is used, utilizing a machine learning platform, and the plurality of training samples are used to perform model training to obtain the reconstruction time prediction model.

7. The method according to claim 1, characterized in that The traffic low period prediction model is a K-nearest neighbor regression algorithm model; The method further comprises: Obtaining a monitoring history record of the number of front-end service interface connections of the database and / or other databases, wherein the monitoring history record of the number of front-end service interface connections includes a record value of the number of front-end service interface connections and a corresponding time period; Acquire host access history data of the database and / or other databases, wherein the host access history data includes the address of the host accessing the database and / or other databases and the time when the access occurred; Generating a training sample set including a plurality of training samples according to the monitoring history record of the number of front-end service interface connections, each of the training samples including a record value of the number of front-end service interface connections; Adding feature values to the training samples based on the host access history data, wherein: the feature values include the host address and the time when the access occurred; the host address and the front-end service interface connection number record value in the same training sample correspond to the same database, and the time when the access occurred is within the time period corresponding to the front-end service interface connection number record value; A K-nearest neighbor regression algorithm is used, and a machine learning platform is utilized to perform model training using the training sample set to obtain the low traffic period prediction model.

8. The method according to claim 7, characterized in that The performing model training using the training sample set to obtain the low traffic period prediction model includes: Splitting the training sample set into a plurality of sub-training sample sets based on the difference of the services of the database, each sub-training sample set corresponding to a service of the database; The sub-training sample set is used to perform model training to obtain the traffic low time period prediction model corresponding to the business of the database.

9. The method according to any one of claims 1 to 8, characterized in that After instructing the server to rebuild the database index during the rebuildable operation time period, the method further includes: At the start of the reconfigurable operation time period, obtaining the current load status of the server; When the current load of the server meets the execution requirement of rebuilding the database index, the database index is rebuilt in the rebuildable operation time period.

10. An electronic device, characterized in that: The electronic device comprises a memory for storing computer program instructions and a processor for executing the computer program instructions, wherein when the computer program instructions are executed by the processor, the electronic device is triggered to perform the method steps according to any one of claims 1 to 9.

Images