Big data-based database defragmentation method, apparatus, device, and medium

Abstract

The application provides a big data-based database fragmentation arrangement method and device, an electronic device and a storage medium. The big data-based database fragmentation arrangement method comprises the following steps: calculating a first logical space value of a data table in a database; optimizing and adjusting the first logical space value to obtain a second logical space value of the data table; querying a physical space value of the data table, and calculating a fragmentation rate of the data table based on the physical space value and the second logical space value; screening the data table in the database based on the fragmentation rate to obtain a data table to be arranged; and arranging the fragmentation in the data table to be arranged to release the storage space of the database. The application can obtain a more accurate fragmentation rate by optimizing and adjusting the logical space value of the data table in the database, thereby effectively releasing the storage space in the database and improving the utilization rate of the storage space in the database.

Description

Technical Field

[0001] This application relates to the field of big data technology, and in particular to a method, apparatus, electronic device and storage medium for database fragmentation based on big data. Background Technology

[0002] A database is a repository for organizing, storing, and managing data according to a data structure. Currently, databases can provide various data management methods that users need, ranging from the simplest tables that store various types of data to large database systems that can store massive amounts of data, and have been widely used in various fields.

[0003] Common databases like MySQL and Oracle often generate very large physical tables during routine operation and maintenance, even though these tables typically store very little content. This is because daily use of these tables involves deletion operations. Each time a row is deleted, that space becomes empty. Especially with a large number of deletions over a period of time, this empty space can become larger than the space used to store the table content. If these empty spaces are not occupied by appropriately sized data, they form data fragmentation, thus affecting the utilization of storage space in the database. Summary of the Invention

[0004] In view of the above, it is necessary to propose a database fragmentation method, device, electronic device and storage medium based on big data to solve the technical problem of how to improve the utilization rate of storage space in databases.

[0005] This application provides a database fragmentation defragmentation method based on big data, the method comprising:

[0006] Calculate the first logical space value of the data table in the database;

[0007] The first logical space value is optimized and adjusted to obtain the second logical space value of the data table;

[0008] Query the physical space value of the data table, and calculate the fragmentation rate of the data table based on the physical space value and the second logical space value;

[0009] Based on the fragmentation rate, the data tables in the database are filtered to obtain the data tables to be processed;

[0010] The fragments in the data table to be organized are organized to free up storage space in the database.

[0011] In some embodiments, the first logical space value of the data table in the computational database includes:

[0012] Based on database query data table data space values ​​and index space values;

[0013] The data space value and the index space value are summed, and the summation result is used as the first logical space value of the data table.

[0014] In some embodiments, optimizing and adjusting the first logical space value to obtain the second logical space value of the data table includes:

[0015] Query the metadata space value of the data table and the actual number of rows in the data table;

[0016] Calculate the theoretical number of rows in the data table corresponding to the metadata space value;

[0017] Calculate the ratio of the theoretical number of rows to the actual number of rows;

[0018] The confidence weight of the data space value is calculated based on the ratio, and the product of the data space value and the confidence weight is used as the optimization adjustment value of the data space value.

[0019] The optimized adjustment value of the data space value and the index space value are summed, and the summation result is used as the second logical space value of the data table.

[0020] In some embodiments, querying the physical space value of the data table and calculating the fragmentation rate of the data table based on the physical space value and the second logical space value includes:

[0021] Based on the database, query the physical space value of the data table;

[0022] Calculate the difference between the physical space value and the second logical space value;

[0023] The percentage of the difference to the physical space value is used as the fragmentation rate of the data table.

[0024] In some embodiments, the fragmentation rate of the data table satisfies the following relationship:

[0025]

[0026] Among them, C i Let P be the fragmentation rate of the i-th data table. i Let A be the physical space value of the i-th data table. i and B i Let m be the data space value and index space value of the i-th data table, where α is a hyperparameter, α>0, and m i Let m be the ratio of the theoretical number of rows to the actual number of rows in the i-th data table. i ≥1.

[0027] In some embodiments, the step of filtering the data tables in the database based on the fragmentation rate to obtain the data table to be processed includes:

[0028] Compare the fragmentation rate with a preset fragmentation rate threshold;

[0029] Compare the physical space values ​​in the data table with the preset physical space threshold;

[0030] Data tables that simultaneously satisfy the condition that the fragmentation rate is greater than the fragmentation rate threshold and the physical space value is greater than the physical space threshold are designated as data tables to be processed.

[0031] In some embodiments, defragmenting the data table to be defragmented to free up storage space in the database includes:

[0032] Create a new data table with the same table structure as the data table to be processed;

[0033] Synchronize the data in the data table to be processed to the new data table to defragment the data;

[0034] Delete the data table to be cleaned up, and replace it with a new data table that has been cleaned up to free up storage space in the database.

[0035] This application also provides a database fragmentation defragmentation device based on big data, the device including a calculation module, an optimization module, a query module, a filtering module, and a defragmentation module:

[0036] The calculation module is used to calculate the first logical space value of the data table in the database;

[0037] The optimization module is used to optimize and adjust the first logical space value to obtain the second logical space value of the data table.

[0038] The query module is used to query the physical space value of the data table, and calculate the fragmentation rate of the data table based on the physical space value and the second logical space value;

[0039] The filtering module is used to filter the data tables in the database based on the fragmentation rate to obtain the data tables to be sorted.

[0040] The sorting module is used to sort out the fragments in the data table to be sorted in order to free up the storage space of the database.

[0041] This application embodiment also provides an electronic device, the electronic device comprising:

[0042] Memory, storing at least one instruction;

[0043] The processor executes the instructions stored in the memory to implement the database defragmentation method based on big data.

[0044] This application also provides a computer-readable storage medium storing at least one instruction, which is executed by a processor in an electronic device to implement the database defragmentation method based on big data.

[0045] This application optimizes and adjusts the calculated logical space values ​​of data tables in the database to obtain a more accurate fragmentation rate, thereby effectively releasing storage space in the database and improving the utilization rate of storage space in the database. Attached Figure Description

[0046] Figure 1 This is a flowchart of a preferred embodiment of the database fragmentation method based on big data involved in this application.

[0047] Figure 2 This is a functional block diagram of a preferred embodiment of the database fragmentation device based on big data involved in this application.

[0048] Figure 3 This is a schematic diagram of the structure of an electronic device that is a preferred embodiment of the database fragmentation method based on big data involved in this application. Detailed Implementation

[0049] To better understand the purpose, features, and advantages of this application, a detailed description of the application is provided below with reference to the accompanying drawings and specific embodiments. It should be noted that, unless otherwise specified, the embodiments and features described in the embodiments of this application can be combined with each other. Numerous specific details are set forth in the following description to provide a thorough understanding of this application; the described embodiments are only a part of the embodiments of this application, and not all of them.

[0050] Furthermore, the terms "first" and "second" are used for descriptive purposes only and should not be construed as indicating or implying relative importance or implicitly specifying the number of technical features indicated. Thus, a feature defined as "first" or "second" may explicitly or implicitly include one or more of the stated features. In the description of this application, "a plurality of" means two or more, unless otherwise explicitly specified.

[0051] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this application belongs. The terminology used herein is for the purpose of describing particular embodiments only and is not intended to be limiting of the application. The term "and / or" as used herein includes any and all combinations of one or more of the associated listed items.

[0052] This application provides a database fragmentation defragmentation method based on big data, which can be applied to one or more electronic devices. An electronic device is a device that can automatically perform numerical calculations and / or information processing according to pre-set or stored instructions. Its hardware includes, but is not limited to, microprocessors, application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), digital signal processors (DSPs), embedded devices, etc.

[0053] Electronic devices can be any electronic product that allows human-computer interaction with a customer, such as personal computers, tablets, smartphones, personal digital assistants (PDAs), game consoles, interactive network television (IPTV), smart wearable devices, etc.

[0054] Electronic devices may also include network devices and / or client devices. The network devices include, but are not limited to, a single network server, a server group consisting of multiple network servers, or a cloud based on cloud computing consisting of a large number of hosts or network servers.

[0055] The networks in which electronic devices are located include, but are not limited to, the Internet, wide area networks, metropolitan area networks, local area networks, and virtual private networks (VPNs).

[0056] like Figure 1 The diagram shown is a flowchart of a preferred embodiment of the database fragmentation method based on big data according to this application. The order of the steps in this flowchart can be changed, and some steps can be omitted, depending on different requirements.

[0057] S10, calculate the first logical space value of the data table in the database.

[0058] In an optional embodiment, the first logical space value of the data table in the computational database includes:

[0059] Based on database query data table data space values ​​and index space values;

[0060] The data space value and the index space value are summed, and the summation result is used as the first logical space value of the data table.

[0061] In this optional embodiment, the data table in the database is stored as two files with physical file formats of .ibd and .frm. The .ibd file is used to store metadata information in the data table, while the .frm file is used to store the data in the data table. The metadata information is data that describes data attributes and is often used to support functions such as indicating the storage location of each data in the data table, historical data, resource lookup, and file records.

[0062] For example, a table named t1 consists of t1.idb and t1.frm, where the t1.idb file stores the metadata information of the data table t1, and the t1.frm file stores the data in the data table t1.

[0063] In this optional embodiment, metadata information corresponding to each data table in the database can be queried using SQL statements to obtain the data space value and index space value corresponding to the data table. The database can be a database that supports SQL statements, such as MySQL or Oracle. The data space value represents the size of the data in the data table, and the index space value represents the size of the index in the data table.

[0064] In this optional embodiment, the logical size of the corresponding data table can be obtained by summing the data space value and the index space value. In this scheme, the logical size of the data table is used as the first logical space value of the data table. The space actually occupied in the data table is called the logical space, which represents the logical size of the table. The allocated space is called the physical space. The logical space is often smaller than the physical space because after deleting some data from the data table, although the data no longer exists in the data table, the space occupied in the data table is not released. When new data is inserted into the data table, this space will still be used. Therefore, if the allocated space cannot be fully occupied by the data, the logical space of the data table will be smaller than the physical space.

[0065] In this way, the first logical space value of the data table can be quickly obtained through the database, providing data support for subsequent calculations of the data table's fragmentation rate.

[0066] S11, optimize and adjust the first logical space value to obtain the second logical space value of the data table.

[0067] In an optional embodiment, optimizing and adjusting the first logical space value to obtain the second logical space value of the data table includes:

[0068] Query the metadata space value of the data table and the actual number of rows in the data table;

[0069] Calculate the theoretical number of rows in the data table corresponding to the metadata space value;

[0070] Calculate the ratio of the theoretical number of rows to the actual number of rows;

[0071] The confidence weight of the data space value is calculated based on the ratio, and the product of the data space value and the confidence weight is used as the optimization adjustment value of the data space value.

[0072] The optimized adjustment value of the data space value and the index space value are summed, and the summation result is used as the second logical space value of the data table.

[0073] In this optional embodiment, the size of the metadata corresponding to the data table is used as the metadata space value of the data table. Since the metadata corresponding to the data table is not deleted after the data in the data table is deleted, the actual number of rows in the data table will change with the addition and deletion of data when the metadata space value of the data table remains unchanged. In this solution, the metadata space value of the data table and the actual number of rows in the data table can be queried in the database using SQL statements.

[0074] In this optional embodiment, during the daily operation and maintenance of the database, it often happens that the physical file of the data table in .ibd format is very large, while the actual number of rows stored in the corresponding .frm format file is insufficient. This is because the .frm format file contains a large number of data fragments. For example, if the physical file size of the .ibd format is 1TB, then under normal circumstances, the theoretical number of rows stored in the corresponding .frm format file should be billions of rows, while the actual number of rows is only a few million.

[0075] In this optional embodiment, the theoretical number of rows corresponding to the metadata space value in the data table can be calculated by acquiring massive amounts of big data. In this solution, 10,000 data tables can be selected based on the big data, and the ratio between the metadata space value of each data table and the corresponding actual number of data rows can be queried. Thus, the theoretical number of rows corresponding to the metadata space value of the current data table can be calculated based on the average of all ratios.

[0076] For example, there are 10,000 data tables. The ratio between the metadata space value of all data tables and the number of data rows in the corresponding data table is 1:4, that is, 1 byte of metadata corresponds to 4 rows of data. The metadata space value of the current data table is 100 bytes, so the theoretical number of rows corresponding to the current data table is 400 rows.

[0077] In this optional embodiment, the ratio of the theoretical number of rows to the actual number of rows in the data table can be calculated, and the data space value of the data table can be adjusted based on the ratio, thereby optimizing the first logical space value of the data table. The reason is that the more fragments in the data table, the more likely the data fragments will be counted as real data during the query process, resulting in a less accurate data space value obtained from the query. Therefore, a smaller confidence weight can be assigned to the data space value.

[0078] In this optional embodiment, the product of the data space value and the confidence weight is used as the optimized adjustment value of the data space value. Then, the optimized adjustment value of the data space value and the index space value are summed, and the summation result is used as the second logical space value of the data table. The second logical space value satisfies the following relation:

[0079]

[0080] Among them, V i A represents the second logical space value of the i-th data table. i and B i Let m be the data space value and index space value of the i-th data table, where α is a hyperparameter, α>0, and m i Let be the ratio of the theoretical number of rows to the actual number of rows in the i-th data table. The confidence weights are assigned to the data space values ​​of the i-th data table.

[0081] For example, table a has a metadata space value of 100 bytes, corresponding to a theoretical number of 400 rows, but the actual number of rows is 100. The ratio of the theoretical number of rows to the actual number of rows in table a is 4:1. Table b also has a metadata space value of 100 bytes, but the actual number of rows is 50. The ratio of the theoretical number of rows to the actual number of rows in table b is 8:1, indicating that there are more data fragments in table b. The first logical space value of table a is more reliable than that of table b because the more fragmented the table, the more likely it is to include fragmented data in the first logical space value, thus causing more errors. Therefore, the confidence weight assigned to table a is greater than that assigned to table b.

[0082] Thus, by optimizing and adjusting the first logical space value of the data table, a second logical space value that can more accurately reflect the true logical size of the data table can be obtained.

[0083] S12, query the physical space value of the data table, and calculate the fragmentation rate of the data table based on the physical space value and the second logical space value.

[0084] In an optional embodiment, querying the physical space value of the data table and calculating the fragmentation rate of the data table based on the physical space value and the second logical space value includes:

[0085] Based on the database, query the physical space value of the data table;

[0086] Calculate the difference between the physical space value and the second logical space value;

[0087] The percentage of the difference to the physical space value is used as the fragmentation rate of the data table.

[0088] In this optional embodiment, the physical space value of the data table in the database can also be queried using SQL statements, and the difference between the physical space value and the second logical space value can be calculated, wherein the physical space value is used to reflect the physical space size of the file with the format .ibd corresponding to the data table.

[0089] In this optional embodiment, the percentage of the difference to the physical space value can be used as the fragmentation rate of the data table, and the fragmentation rate of the data table satisfies the following relationship:

[0090]

[0091] Among them, C i Let P be the fragmentation rate of the i-th data table. i Let A be the physical space value of the i-th data table. i and B i Let m be the data space value and index space value of the i-th data table, where α is a hyperparameter, α>0, and m i Let m be the ratio of the theoretical number of rows to the actual number of rows in the i-th data table. i ≥1.

[0092] For example, if the second logical space value of data table c is 4489216kb and the corresponding physical space value is 11534336, then the percentage of the difference between the physical space value and the second logical space value to the physical space value is 61.08%.

[0093] In this way, the fragmentation rate of the data table can be quickly calculated using the physical space value and the corresponding second logical space value of the data table.

[0094] S13, Based on the fragmentation rate, the data tables in the database are filtered to obtain the data tables to be organized.

[0095] In an optional embodiment, the step of filtering the data tables in the database based on the fragmentation rate to obtain the data table to be processed includes:

[0096] Compare the fragmentation rate with a preset fragmentation rate threshold;

[0097] Compare the physical space values ​​in the data table with the preset physical space threshold;

[0098] Data tables that simultaneously satisfy the condition that the fragmentation rate is greater than the fragmentation rate threshold and the physical space value is greater than the physical space threshold are designated as data tables to be processed.

[0099] In this optional embodiment, after calculating the fragmentation rate of the data table, the fragmentation rate can be compared with a preset fragmentation rate threshold. In this scheme, the fragmentation rate threshold is 90%. At the same time, the physical space value of the physical file corresponding to the data table in .ibd format can be compared with the preset physical space threshold. In this scheme, the physical space threshold is 10G. The reason is that if the physical file of the data table is too small, there is no need to defragment it. Even if defragmentation is performed, it is difficult to release too much disk space.

[0100] In this optional embodiment, a data table that simultaneously satisfies both the fragmentation rate and the physical space value after comparison can be used as a data table to be processed, while a data table that does not simultaneously satisfy both the fragmentation rate and the physical space value does not need to be processed.

[0101] In this way, data tables that need to be defragmented can be selected in a timely manner by using preset fragmentation rate thresholds and physical space thresholds.

[0102] S14, organize the fragments in the data table to be organized to free up the storage space of the database.

[0103] In an optional embodiment, defragmenting the data table to be defragmented to free up storage space in the database includes:

[0104] Create a new data table with the same table structure as the data table to be processed;

[0105] Synchronize the data in the data table to be processed to the new data table to defragment the data;

[0106] Delete the data table to be cleaned up, and replace it with a new data table that has been cleaned up to free up storage space in the database.

[0107] In this optional embodiment, the data table to be defragmented can be defragmented by calling the pt-online-schema-change tool for online DDL execution. DDL stands for Data Definition Language, which is mainly used to perform some management operations on data tables in the database, such as creating tables, modifying tables, deleting tables, and adding, deleting, and modifying columns.

[0108] In this optional embodiment, a new data table with the same structure as the data table to be organized can be created using the pt-online-schema-change tool. The new data table can be named _t1_new. Then, a defragmentation operation is performed on the new data table, thereby creating three triggers (insert, update, and delete) in the data table to be organized. These triggers are used to synchronize the data in the data table to be organized to the new data table through write operations. Data can be copied from the data table to the new data table at a rate of 1000 rows at a time.

[0109] In this optional embodiment, after copying all the data from the data table to be organized to the new data table, the name of the data table to be organized can be changed to _t1_old, and the name of the new data table _t1_new can be changed to t1 to complete the replacement of the data table to be organized. Finally, the data table with the name _t1_old and the three triggers that have been created are deleted, thereby releasing the space occupied by the fragments of the data table and improving the utilization rate of the database storage space.

[0110] In this way, the data table to be processed can be quickly defragmented using the online data table editing tool, thereby freeing up database storage space.

[0111] Please see Figure 2 , Figure 2 This is a functional block diagram of a preferred embodiment of the database defragmentation device based on big data according to this application. The database defragmentation device 11 based on big data includes a calculation module 110, an optimization module 111, a query module 112, a filtering module 113, and a defragmentation module 114. The unit / module referred to in this application refers to a series of computer-readable instruction segments that can be executed by the processor 13 and perform a fixed function, and are stored in the memory 12. In this embodiment, the functions of each unit / module will be described in detail in subsequent embodiments.

[0112] In an optional embodiment, the calculation module 110 is used to calculate the first logical space value of the data table in the database.

[0113] In an optional embodiment, the first logical space value of the data table in the computational database includes:

[0114] Based on database query data table data space values ​​and index space values;

[0115] The data space value and the index space value are summed, and the summation result is used as the first logical space value of the data table.

[0116] In an optional embodiment, the optimization module 111 is used to optimize and adjust the first logical space value to obtain the second logical space value of the data table.

[0117] In an optional embodiment, optimizing and adjusting the first logical space value to obtain the second logical space value of the data table includes:

[0118] Query the metadata space value of the data table and the actual number of rows in the data table;

[0119] Calculate the theoretical number of rows in the data table corresponding to the metadata space value;

[0120] Calculate the ratio of the theoretical number of rows to the actual number of rows;

[0121] The confidence weight of the data space value is calculated based on the ratio, and the product of the data space value and the confidence weight is used as the optimization adjustment value of the data space value.

[0122] The optimized adjustment value of the data space value and the index space value are summed, and the summation result is used as the second logical space value of the data table.

[0123] In an optional embodiment, the query module 112 is used to query the physical space value of the data table and calculate the fragmentation rate of the data table based on the physical space value and the second logical space value.

[0124] In an optional embodiment, querying the physical space value of the data table and calculating the fragmentation rate of the data table based on the physical space value and the second logical space value includes:

[0125] Based on the database, query the physical space value of the data table;

[0126] Calculate the difference between the physical space value and the second logical space value;

[0127] The percentage of the difference to the physical space value is used as the fragmentation rate of the data table.

[0128] In this optional embodiment, the fragmentation rate of the data table satisfies the following relationship:

[0129]

[0130] Among them, C i Let P be the fragmentation rate of the i-th data table. i Let A be the physical space value of the i-th data table. i and B i Let m be the data space value and index space value of the i-th data table, where α is a hyperparameter, α>0, and m i Let m be the ratio of the theoretical number of rows to the actual number of rows in the i-th data table. i ≥1.

[0131] In an optional embodiment, the filtering module 113 is used to filter the data tables in the database based on the fragmentation rate to obtain a data table to be sorted.

[0132] In an optional embodiment, the step of filtering the data tables in the database based on the fragmentation rate to obtain the data table to be processed includes:

[0133] Compare the fragmentation rate with a preset fragmentation rate threshold;

[0134] Compare the physical space values ​​in the data table with the preset physical space threshold;

[0135] Data tables that simultaneously satisfy the condition that the fragmentation rate is greater than the fragmentation rate threshold and the physical space value is greater than the physical space threshold are designated as data tables to be processed.

[0136] In an optional embodiment, the sorting module 114 is used to sort out fragments in the data table to be sorted in order to free up storage space in the database.

[0137] In an optional embodiment, defragmenting the data table to be defragmented to free up storage space in the database includes:

[0138] Create a new data table with the same table structure as the data table to be processed;

[0139] Synchronize the data in the data table to be processed to the new data table to defragment the data;

[0140] Delete the data table to be cleaned up, and replace it with a new data table that has been cleaned up to free up storage space in the database.

[0141] As can be seen from the above technical solutions, this application can obtain a more accurate fragmentation rate by optimizing and adjusting the logical space value of the data table in the database, thereby effectively releasing the storage space in the database and improving the utilization rate of the storage space in the database.

[0142] Please see Figure 3 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. The electronic device 1 includes a memory 12 and a processor 13. The memory 12 is used to store computer-readable instructions, and the processor 13 executes the computer-readable instructions stored in the memory to implement the database fragmentation method based on big data as described in any of the above embodiments.

[0143] In an alternative embodiment, the electronic device 1 further includes a bus and a computer program stored in the memory 12 and executable on the processor 13, such as a database defragmentation program based on big data.

[0144] Figure 3 Only electronic device 1 with memory 12 and processor 13 is shown. It will be understood by those skilled in the art that... Figure 3 The structure shown does not constitute a limitation on the electronic device 1, and may include fewer or more components than shown, or combine certain components, or have different component arrangements.

[0145] Combination Figure 1 The memory 12 in the electronic device 1 stores multiple computer-readable instructions to implement a database defragmentation method based on big data, and the processor 13 can execute the multiple instructions to achieve:

[0146] Calculate the first logical space value of the data table in the database;

[0147] The first logical space value is optimized and adjusted to obtain the second logical space value of the data table;

[0148] Query the physical space value of the data table, and calculate the fragmentation rate of the data table based on the physical space value and the second logical space value;

[0149] Based on the fragmentation rate, the data tables in the database are filtered to obtain the data tables to be processed;

[0150] The fragments in the data table to be organized are organized to free up storage space in the database.

[0151] Specifically, the processor 13's implementation method for the above instructions can be found in [reference needed]. Figure 1 The descriptions of the relevant steps in the corresponding embodiments are not repeated here.

[0152] Those skilled in the art will understand that the schematic diagram is merely an example of electronic device 1 and does not constitute a limitation on electronic device 1. Electronic device 1 can be a bus-type structure or a star-type structure. Electronic device 1 may also include more or fewer other hardware or software than shown in the diagram, or different component arrangements. For example, electronic device 1 may also include input / output devices, network access devices, etc.

[0153] It should be noted that electronic device 1 is only an example. Other existing or future electronic products that are suitable for this application should also be included within the scope of protection of this application and are incorporated herein by reference.

[0154] The memory 12 includes at least one type of readable storage medium, which can be non-volatile or volatile. The readable storage medium includes flash memory, portable hard drives, multimedia cards, card-type memory (e.g., SD or DX memory), magnetic storage, magnetic disks, optical disks, etc. In some embodiments, the memory 12 can be an internal storage unit of the electronic device 1, such as a portable hard drive of the electronic device 1. In other embodiments, the memory 12 can also be an external storage device of the electronic device 1, such as a plug-in portable hard drive, smart media card (SMC), secure digital (SD) card, flash card, etc., equipped on the electronic device 1. The memory 12 can be used not only to store application software and various types of data installed on the electronic device 1, such as the code of a database defragmentation program based on big data, but also to temporarily store data that has been output or will be output.

[0155] In some embodiments, the processor 13 may be composed of integrated circuits, such as a single packaged integrated circuit or multiple integrated circuits packaged with the same or different functions, including combinations of one or more central processing units (CPUs), microprocessors, digital processing chips, graphics processors, and various control chips. The processor 13 is the control unit of the electronic device 1, connecting various components of the electronic device 1 via various interfaces and lines. It executes programs or modules stored in the memory 12 (e.g., executing a database defragmentation program based on large data) and calls data stored in the memory 12 to perform various functions and process data in the electronic device 1.

[0156] The processor 13 executes the operating system of the electronic device 1 and various installed applications. The processor 13 executes these applications to implement the steps in the various embodiments of the big data-based database fragmentation defragmentation method described above, for example... Figure 1 The steps are shown.

[0157] For example, the computer program may be divided into one or more units / modules, which are stored in the memory 12 and executed by the processor 13 to complete this application. The one or more units / modules may be a series of computer-readable instruction segments capable of performing a specific function, which describe the execution process of the computer program in the electronic device 1. For example, the computer program may be divided into a calculation module 110, an optimization module 111, a query module 112, a filtering module 113, and a sorting module 114.

[0158] The integrated unit implemented as a software functional module described above can be stored in a computer-readable storage medium. This software functional module, stored in a storage medium, includes several instructions to cause a computer device (which may be a personal computer, computer equipment, or network device, etc.) or processor to execute portions of the database defragmentation method based on big data described in the various embodiments of this application.

[0159] If the unit / module integrated in electronic device 1 is implemented as a software functional module and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, all or part of the processes in the methods of the above embodiments can also be implemented by a computer program instructing related hardware devices. The computer program can be stored in a computer-readable storage medium, and when executed by a processor, it can implement the steps of the various method embodiments described above.

[0160] The computer program includes computer program code, which may be in the form of source code, object code, executable file, or some intermediate form. The computer-readable medium may include: any entity or device capable of carrying the computer program code, recording media, USB flash drive, portable hard drive, magnetic disk, optical disk, computer memory, read-only memory (ROM), random access memory, and other memory.

[0161] Furthermore, the computer-readable storage medium may primarily include a stored program area and a stored data area, wherein the stored program area may store the operating system, an application program required for at least one function, etc.; and the stored data area may store data created based on the use of blockchain nodes, etc.

[0162] The blockchain referred to in this application is a novel application model of computer technologies such as distributed data storage, peer-to-peer transmission, consensus mechanisms, and encryption algorithms. Essentially, a blockchain is a decentralized database, a chain of data blocks linked together using cryptographic methods. Each data block contains information about a batch of network transactions, used to verify the validity of the information (anti-counterfeiting) and generate the next block. A blockchain can include an underlying blockchain platform, a platform product service layer, and an application service layer.

[0163] The bus can be a Peripheral Component Interconnect (PCI) bus or an Extended Industry Standard Architecture (EISA) bus, etc. This bus can be divided into address bus, data bus, control bus, etc. For ease of representation, in... Figure 3 The symbol is represented by only one arrow, but this does not indicate that there is only one bus or one type of bus. The bus is configured to enable communication between the memory 12 and at least one processor 13, etc.

[0164] This application also provides a computer-readable storage medium (not shown), which stores computer-readable instructions. These computer-readable instructions are executed by a processor in an electronic device to implement the database fragmentation method based on big data described in any of the above embodiments.

[0165] In the several embodiments provided in this application, it should be understood that the disclosed systems, apparatuses, and methods can be implemented in other ways. For example, the apparatus embodiments described above are merely illustrative; for instance, the division of modules is only a logical functional division, and other division methods may be used in actual implementation.

[0166] The modules described as separate components may or may not be physically separate. The components shown as modules may or may not be physical modules; that is, they may be located in one place or distributed across multiple network modules. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs.

[0167] Furthermore, the functional modules in the various embodiments of this application can be integrated into one processing unit, or each module can exist physically separately, or two or more modules can be integrated into one unit. The integrated unit can be implemented in hardware or in the form of hardware plus software functional modules.

[0168] Furthermore, the word "comprising" clearly does not exclude other modules or steps, and the singular does not exclude the plural. Multiple modules or devices described in the specification can also be implemented by a single module or device through software or hardware. Terms such as "first," "second," etc., are used to indicate names and do not indicate any specific order.

[0169] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of this application and are not intended to limit it. Although this application has been described in detail with reference to preferred embodiments, those skilled in the art should understand that modifications or equivalent substitutions can be made to the technical solutions of this application without departing from the spirit and scope of the technical solutions of this application.

Claims

1. A database fragmentation defragmentation method based on big data, characterized in that, The method includes: Calculate the first logical space value of the data table in the database; Optimizing the first logical space value to obtain the second logical space value of the data table includes: querying the metadata space value of the data table and the actual number of rows in the data table; calculating the theoretical number of rows in the data table corresponding to the metadata space value; calculating the ratio of the theoretical number of rows to the actual number of rows; calculating the confidence weight of the data space value based on the ratio, and using the product of the data space value and the confidence weight as the optimization adjustment value of the data space value; summing the optimization adjustment value of the data space value and the index space value, and using the summation result as the second logical space value of the data table. Querying the physical space value of the data table and calculating the fragmentation rate of the data table based on the physical space value and the second logical space value includes: querying the physical space value of the data table in the database using an SQL statement, where the physical space value reflects the physical space size of the file with the format .ibd corresponding to the data table; calculating the difference between the physical space value and the second logical space value; and using the percentage of the difference to the physical space value as the fragmentation rate of the data table, where the fragmentation rate of the data table satisfies the following relationship: ,in, Let i be the fragmentation rate of the i-th data table. The physical space value for the i-th data table. and For the data space values ​​and index space values ​​of the i-th data table, For hyperparameters, , Let be the ratio of the theoretical number of rows to the actual number of rows in the i-th data table. ; Based on the fragmentation rate, the data tables in the database are filtered to obtain the data tables to be processed; The fragments in the data table to be organized are organized to free up storage space in the database.

2. The database fragmentation defragmentation method based on big data as described in claim 1, characterized in that, The first logical space values ​​of the data tables in the computational database include: Based on database query data table data space values ​​and index space values; The data space value and the index space value are summed, and the summation result is used as the first logical space value of the data table.

3. The database fragmentation defragmentation method based on big data as described in claim 1, characterized in that, The step of filtering the data tables in the database based on the fragmentation rate to obtain the data table to be processed includes: Compare the fragmentation rate with a preset fragmentation rate threshold; Compare the physical space values ​​in the data table with the preset physical space threshold; Data tables that simultaneously satisfy the condition that the fragmentation rate is greater than the fragmentation rate threshold and the physical space value is greater than the physical space threshold are designated as data tables to be processed.

4. The database fragmentation defragmentation method based on big data as described in claim 1, characterized in that, The step of organizing the fragments in the data table to be organized in order to free up the storage space of the database includes: Create a new data table with the same table structure as the data table to be processed; Synchronize the data in the data table to be processed to the new data table to defragment the data; Delete the data table to be cleaned up, and replace it with a new data table that has been cleaned up to free up storage space in the database.

5. A database fragmentation defragmentation device based on big data, characterized in that, The apparatus is used to implement the database fragmentation method based on big data as described in any one of claims 1 to 4, and the apparatus includes a calculation module, an optimization module, a query module, a filtering module, and a fragmentation module: The calculation module is used to calculate the first logical space value of the data table in the database; The optimization module is used to optimize and adjust the first logical space value to obtain the second logical space value of the data table. The query module is used to query the physical space value of the data table, and calculate the fragmentation rate of the data table based on the physical space value and the second logical space value; The filtering module is used to filter the data tables in the database based on the fragmentation rate to obtain the data tables to be sorted. The sorting module is used to sort out the fragments in the data table to be sorted in order to free up the storage space of the database.

6. An electronic device, characterized in that, The electronic device includes: Memory, which stores computer-readable instructions; and The processor executes computer-readable instructions stored in the memory to implement the database defragmentation method based on big data as described in any one of claims 1 to 4.

7. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer-readable instructions, which, when executed by a processor, implement the database defragmentation method based on big data as described in any one of claims 1 to 4.

Images