Automatic segmentation method and device for database table

Abstract

The invention provides an automatic segmentation method for a database table. The automatic segmentation method comprises the following steps: judging whether a large table exists in a database table to be segmented; if the large table exists, when it is judged that the column number of the to-be-segmented database table is larger than a first set value, all common column data in the to-be-segmented database table are extracted, and the common column data are segmented through the vertical segmentation technology to obtain a plurality of sub-tables; when it is judged that the column number of the to-be-segmented database table is smaller than or equal to a first set value, extracting all common row data in the to-be-segmented database table, and segmenting the common row data by adopting a horizontal segmentation technology to obtain a plurality of sub-tables; naming the sub-table; and storing the named sub-table. According to the method, the size of the table can be automatically judged, the database table can be automatically segmented according to commonly used and not commonly used data in the large table, horizontal segmentation or vertical segmentation can be automatically selected according to the row and column conditions of the data table, and the addition, deletion, modification and check performance of the stored sub-table is improved on the basis.

Description

Technical Field

[0001] The present invention relates to the technical field of database table segmentation, and in particular to a method and device for automatically segmenting a database table. Background Art

[0002] Traditional Java backend development, Java Android development, Qt application development, etc. all involve the storage of database table data. With the increasing number of users, the amount of data in the database table is increasing. The increase in the table will lead to problems with the performance of adding, deleting, modifying and checking. The table must be stored on the same medium, resulting in insufficient space and other problems. Different systems have different spaces for storing remaining amounts. Relying on manpower to solve these problems will require a lot of time and manpower costs, and may even affect the normal use of users. Summary of the invention

[0003] In order to overcome the above technical defects, the present invention provides a method and device for automatically segmenting a database table, which can realize manual inspection during express delivery.

[0004] In order to solve the above problems, the present invention is implemented according to the following technical solutions:

[0005] A method for automatically segmenting a database table comprises the steps of:

[0006] Determine whether there is a large table in the database table to be split;

[0007] If there is a large table, when it is determined that the number of columns in the database table to be split is greater than the first set value, all common column data in the database table to be split are extracted, and the common column data are split using a vertical splitting technique to obtain a number of sub-tables;

[0008] When it is determined that the number of columns of the database table to be segmented is less than or equal to the first set value, all common row data in the database table to be segmented are extracted, and the common row data are segmented by a horizontal segmentation technique to obtain a plurality of sub-tables;

[0009] Name the subtable;

[0010] Store the named subtable.

[0011] As a further improvement of the present invention, the step of determining whether there is a large table in the database table to be split includes:

[0012] Get the total data volume of all tables in the database table to be split;

[0013] If the total amount of data is greater than or equal to 1 million, it is considered a large table.

[0014] As a further improvement of the present invention, when it is determined that the number of columns in the database table to be segmented is greater than the first set value, the step of extracting all common column data in the database table to be segmented and segmenting the common column data using a vertical segmentation technique to obtain a plurality of sub-tables includes:

[0015] Check and count the data of the second set value line that is most frequently used within the set time;

[0016] Extracting the second set value row data as a sub-table;

[0017] The remaining row data is stored in several sub-tables.

[0018] As a further improvement of the present invention, the step of storing the remaining row data in a plurality of sub-tables includes:

[0019] Determine whether the total data volume of the large table divided by the second set value is equal to zero. If it is equal to zero, store the remaining row data in the first calculated value sub-tables; if it is not equal to zero, store the remaining row data in the second calculated value sub-tables; store the remaining third calculated value rows of data separately in a sub-table; wherein, the first calculated value is equal to the total data volume of the large table divided by the second set value minus one and rounded to the integer, the second calculated value is equal to the total data volume of the large table divided by the second set value minus one and rounded to the integer plus one, and the third calculated value is equal to the total data volume of the large table divided by the second set value.

[0020] As a further improvement of the present invention, when it is determined that the number of columns of the database table to be segmented is less than or equal to the first set value, the step of extracting all common row data in the database table to be segmented and using the horizontal segmentation technology to segment the common row data to obtain a plurality of sub-tables includes:

[0021] Check and count the row data of the second set value and the column data of the third set value that are most frequently used within the set time;

[0022] Extracting the third set value column data as a sub-table;

[0023] The remaining column data is stored in several sub-tables.

[0024] As a further improvement of the present invention, the step of storing the remaining row data in a plurality of sub-tables includes:

[0025] Determine whether the total number of columns of the large table divided by the third set value is equal to zero. If it is equal to zero, use the fourth calculated value of sub-tables to store the remaining row data; if it is not equal to zero, use the fifth calculated value of sub-tables to store the remaining row data; store the remaining sixth calculated value of row data separately in a sub-table; wherein the fourth calculated value is equal to the total number of columns of the large table divided by the third set value minus one and then rounded to the integer, the fifth calculated value is equal to the total number of columns of the large table divided by the third set value minus one and then rounded to the integer plus one, and the sixth calculated value is equal to the total data volume divided by the third set value.

[0026] As a further improvement of the present invention, the step of naming the sub-table includes:

[0027] The subtables are named in order of natural numbers.

[0028] As a further improvement of the present invention, the step of storing the sub-table includes:

[0029] Select free space with large remaining storage space to store the subtable.

[0030] As a further improvement of the present invention, after the step of storing the named sub-table, the following steps are further included:

[0031] Adjust the add, delete, modify and check statements;

[0032] If horizontal partitioning technology is used, the union operation provided by the database system is used to merge the results;

[0033] If vertical partitioning technology is used, the database system has its own join operation to merge the results.

[0034] Compared with the prior art, the automatic segmentation method provided by the present invention has the following beneficial effects: it can automatically determine the size of the table, and automatically segment the database table according to the commonly used and infrequently used data in the large table, and can automatically select "horizontal segmentation" or "vertical segmentation" according to the row and column conditions of the data table. The sub-tables stored on this basis have improved addition, deletion, modification and query performance.

[0035] In addition, the present invention also provides an electronic device, characterized in that the electronic device includes a processor and a memory, the memory stores at least one instruction, and the at least one instruction is loaded and executed by the processor to implement the above-mentioned automatic segmentation method. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] Figure 1 A flowchart of the automatic segmentation method of the invention;

[0037] Figure 2 A schematic diagram of the results returned by the present invention;

[0038] Figure 3 It is a schematic diagram of another returned result of the present invention. DETAILED DESCRIPTION

[0039] The preferred embodiments of the present invention are described below in conjunction with the accompanying drawings. It should be understood that the preferred embodiments described herein are only used to illustrate and explain the present invention, and are not used to limit the present invention.

[0040] See also Figure 1 , the present invention provides a method for automatically segmenting a database table, such as Figure 1 As shown, the steps include:

[0041] S1. Determine whether there is a large table in the database table to be split. In this step, first, obtain the total data volume of all tables in the database table to be split. If the total data volume is greater than or equal to 1 million, it is determined to be a large table. Taking MySQL database as an example, you can execute "select count(*)from table name" to obtain the total data of the table, assuming it is a. The advantage of this method is that the time complexity is O(1). No matter how large the data volume is, it can always return the result within 0.01 seconds, and determine whether the returned data is greater than or equal to 1 million.

[0042] S2. If there is a large table, when it is determined that the number of columns of the database table to be split is greater than the first set value, all common column data in the database table to be split are extracted, and the common column data are split using a vertical splitting technique to obtain a plurality of sub-tables.

[0043] Specifically, step S2 includes the following steps:

[0044] S21, checking and counting the second set value row data most frequently used within the set time;

[0045] S22, extracting the second set value row data as a sub-table;

[0046] S23. Use several sub-tables to store the remaining row data: determine whether the total data volume of the large table divided by the second set value is equal to zero. If it is equal to zero, use the first calculated value of sub-tables to store the remaining row data; if it is not equal to zero, use the second calculated value of sub-tables to store the remaining row data; store the remaining third calculated value of row data separately in a sub-table; wherein, the first calculated value is equal to the total data volume of the large table divided by the second set value minus one and then rounded up, the second calculated value is equal to the total data volume of the large table divided by the second set value minus one and then rounded up plus one, and the third calculated value is equal to the total data volume of the large table divided by the second set value.

[0047] S3. When it is determined that the number of columns of the database table to be segmented is less than or equal to the first set value, all common row data in the database table to be segmented are extracted, and the common row data are segmented by a horizontal segmentation technique to obtain a plurality of sub-tables.

[0048] Specifically, step S3 includes the following steps:

[0049] S31, checking and counting the row data of the second set value and the column data of the third set value that are most frequently used within a set time;

[0050] S32, extracting the third set value column data as a sub-table;

[0051] S33. Use several sub-tables to store the remaining column data: determine whether the total number of columns of the large table divided by the third set value is equal to zero. If it is equal to zero, use the fourth calculated value of sub-tables to store the remaining row data; if it is not equal to zero, use the fifth calculated value of sub-tables to store the remaining row data; store the remaining sixth calculated value of row data separately in a sub-table; wherein the fourth calculated value is equal to the total number of columns of the large table divided by the third set value minus one and then rounded up, the fifth calculated value is equal to the total number of columns of the large table divided by the third set value minus one and then rounded up plus one, and the sixth calculated value is equal to the total data volume divided by the third set value.

[0052] S4. Name the sub-tables, for example, name the sub-tables according to the order of natural numbers, set the serial number of the commonly used table to 1, and the others to increase in sequence of "2, 3, 4..." Then, judge the system environment.

[0053] S5. Store the named sub-table. In some embodiments, a free space with a large remaining storage space is selected to store the sub-table.

[0054] S6. Adjust the add, delete, modify and query statements. If the horizontal partitioning technique is used, use the union operation provided by the database system to merge the results. If the vertical partitioning technique is used, use the join operation provided by the database system to merge the results.

[0055] Next, the present invention is further explained in conjunction with the specific implementation process, as follows:

[0056] Determine whether the database table to be split has a large table. If so, continue to determine whether the number of columns in the database table to be split is greater than 100. If so, extract all common column data and use vertical splitting technology for splitting. If not, extract all common row data and use horizontal splitting technology for splitting.

[0057] The specific implementation logic of the vertical segmentation technology is: automatically check and count the 100,000 most frequently used rows of data in the past three months, extract all commonly used rows, and simply segment them into a sub-table. The total data volume of the large table is a÷100,000-1) and rounded, and determine whether [total data of the database table a%100,000] is equal to 0. If so, [(total data of the database table to be segmented a÷100,000-1) and rounded] sub-tables are used to store the remaining rows of data, and the data volume of each sub-table is 100,000 rows of data. If not, [(total data of the database table to be segmented a÷100,000-1) and rounded and then added 1] sub-tables are used to store the remaining rows of data. The total data of the database table to be segmented is a÷100,000-1 and the data volume of the entire sub-table is 100,000 rows of data. The remaining (total data of the database table a%10) rows of data are stored separately in a sub-table.

[0058] The specific implementation logic of the horizontal segmentation technology is: automatically check and count the 100,000 most frequently used row data in the past three months, further automatically check and count the top 100 most frequently used column data in the past three months, extract all commonly used column numbers, and simply split them into a sub-table, (total number of columns ÷ 100-1) and round up, and determine whether the total number of columns % 100 is equal to 0. If so, use [(total number of columns ÷ 100-1) and round up] sub-tables to store the remaining column data, and the data volume of each sub-table is 100 columns of data. If not, use [(total number of columns ÷ 100-1) and round up and add 1] sub-tables to store the remaining column data, and the data volume of [(total number of columns ÷ 100-1) and round up] sub-tables is 100 columns of data, and the remaining (a% 10) columns of data are stored separately in a sub-table.

[0059] If the database table to be split does not have a large table, continue to execute this step, that is, continue to determine whether the database table to be split has a large table.

[0060] The logic for judging whether there is a large table in the database table to be split is: get the data volume of all tables. If the data volume is greater than or equal to 1 million, it is judged to be a large table. Taking the MySQL database as an example, you can execute "select count(*)from table name" to obtain the total data of the table, assuming it is a. The advantage of this method is that the time complexity is O(1). No matter how large the data volume is, the result can always be returned within 0.01 seconds, and the returned data can be judged whether it is greater than or equal to 1 million.

[0061] After the sub-tables are split, you need to name the sub-tables and determine the system environment. Set the serial number of the commonly used table to 1, and the others to increase in "2, 3, 4...". Take the user table as an example. Assuming it is divided into 4 sub-tables, it is named user1 (common row data / column data, depending on the specific splitting method), and the others are: user2, user3, user4. Automatic judgment of the system environment, taking Windows and Linux systems as examples, the getpid() system call in the Linux system and the GetCurrentProcessId() function in the Windows system have similar functions, but the calling methods are different. The software can determine the running operating system by detecting the return values ​​of these function calls. If the GetCurrentProcessId() system call is used in the Linux system and the getpid() function is used in the Windows system, it will cause the system function call name to be wrong, thereby distinguishing the system environment.

[0062] The sub-table is stored using automatic storage technology. According to the specific system environment, the free space with large remaining storage space is automatically selected for storage. Taking Windows and Linux systems as examples, the command for Windows to obtain space is: wmic logicaldisk get size,freespace,caption (the returned result example is as follows Figure 2 In this example, the total size of the C drive is 100GB, and the remaining space is 45GB; the total size of the D drive is 200GB, and the remaining space is 180GB. The D drive will be automatically selected for storage. The Linux command to obtain space is: df -h (the returned result example is as follows Figure 3 As shown), in this example, / dev / sda1 is the root file system, with a total size of 46G, 11G used, 33G remaining, and a usage rate of 25%. It will be automatically mounted in the / dev / sda5 directory.

[0063] The specific implementation logic of the automatic conversion of the original SQL statements for adding, deleting, modifying and checking is as follows: (1) Adjust the statements for adding, deleting, modifying and checking. For example, if the SQL user inputs "select a,b from user where c>5", taking the above user table as an example, it will be automatically converted to "select a,b from user1 where c>5", "select a,b from user2 where c>5", "select a,b from user3 where c>5", "select a,b from user4 where c>5", and start 4 parallel threads to perform the operation, greatly improving efficiency. (2) If it is a horizontal split, the result is automatically merged using the union operation provided by the database system. If it is a vertical split, the result is automatically merged using the join operation provided by the database system.

[0064] In summary, the automatic segmentation method of the present invention has the following beneficial effects: it can automatically determine the size of the table, and automatically segment the database table for commonly used and infrequently used data in the large table, and can automatically select "horizontal segmentation" or "vertical segmentation" according to the row and column conditions of the data table, so that the performance of adding, deleting, modifying and checking the sub-table after segmentation is improved, and the sub-table can be stored on different media to realize the storage of data on multiple media, thereby improving space utilization. At the same time, the bottom layer uses automatic conversion of the original SQL statements for adding, deleting, modifying and checking, so that SQL users do not need to know the details of the technology and do not need to make any SQL statement or code modifications.

[0065] The present invention provides a resistor device, wherein the computer device comprises a processor and a memory, wherein at least one instruction is stored in the memory, and the at least one instruction is loaded and executed by the processor to implement the above automatic segmentation method.

[0066] The processor may be a central processing unit (CPU), other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field-programmable gate arrays (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc.

[0067] The memory can be used to store the computer program or module, and the processor realizes various functions of the auxiliary terminal device based on mirror neuron therapy by running or executing the computer program or module stored in the memory, and calling the data stored in the memory. The memory can mainly include a program storage area and a data storage area, wherein the program storage area can store an operating system, an application required for at least one function (such as a sound playback function, an image playback function, etc.), etc.; the data storage area can store data created according to the use of the mobile phone (such as audio data, a phone book, etc.), etc. In addition, the memory can include a high-speed random access memory, and can also include a non-volatile memory, such as a hard disk, a memory, a plug-in hard disk, a smart memory card (Smart Media Card, SMC), a secure digital (Secure Digital, SD) card, a flash card (Flash Card), at least one disk storage device, a flash memory device, or other volatile solid-state storage devices.

[0068] The above are only preferred embodiments of the present application and are not intended to limit the present application. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present application should be included in the protection scope of the present application.

Claims

1. A method for automatically segmenting a database table, characterized in that: Includes steps: Determine whether there is a large table in the database table to be split; If there is a large table, when it is determined that the number of columns in the database table to be split is greater than the first set value, all common column data in the database table to be split are extracted, and the common column data are split using a vertical splitting technique to obtain a number of sub-tables; When it is determined that the number of columns of the database table to be segmented is less than or equal to the first set value, all common row data in the database table to be segmented are extracted, and the common row data are segmented by a horizontal segmentation technique to obtain a plurality of sub-tables; Name the subtable; Store the named subtable.

2. The automatic segmentation method according to claim 1, characterized in that: The step of determining whether there is a large table in the database table to be split includes: Get the total data volume of all tables in the database table to be split; If the total amount of data is greater than or equal to 1 million, it is considered a large table.

3. The automatic segmentation method according to claim 1, characterized in that: The step of extracting all common column data in the database table to be segmented and segmenting the common column data into a plurality of sub-tables by using a vertical segmentation technique when it is determined that the number of columns in the database table to be segmented is greater than a first set value comprises: Check and count the data of the second set value line that is most frequently used within the set time; Extracting the second set value row data as a sub-table; The remaining row data is stored in several sub-tables.

4. The automatic segmentation method according to claim 3, characterized in that: The step of storing the remaining row data in a plurality of sub-tables includes: Determine whether the total data volume of the large table divided by the second set value is equal to zero. If it is equal to zero, store the remaining row data in the first calculated value sub-tables; if it is not equal to zero, store the remaining row data in the second calculated value sub-tables; store the remaining third calculated value rows of data separately in a sub-table; wherein, the first calculated value is equal to the total data volume of the large table divided by the second set value minus one and rounded to the integer, the second calculated value is equal to the total data volume of the large table divided by the second set value minus one and rounded to the integer plus one, and the third calculated value is equal to the total data volume of the large table divided by the second set value.

5. The automatic segmentation method according to claim 1, characterized in that: The step of extracting all common row data in the database table to be segmented and segmenting the common row data into a plurality of sub-tables by using a horizontal segmentation technique when the number of columns of the database table to be segmented is determined to be less than or equal to a first set value comprises: Check and count the row data of the second set value and the column data of the third set value that are most frequently used within the set time; Extracting the third set value column data as a sub-table; The remaining column data is stored in several sub-tables.

6. The automatic segmentation method according to claim 5, characterized in that: The step of storing the remaining row data in a plurality of sub-tables includes: Determine whether the total number of columns of the large table divided by the third set value is equal to zero. If it is equal to zero, use the fourth calculated value of sub-tables to store the remaining row data; if it is not equal to zero, use the fifth calculated value of sub-tables to store the remaining row data; store the remaining sixth calculated value of row data separately in a sub-table; wherein the fourth calculated value is equal to the total number of columns of the large table divided by the third set value minus one and then rounded to the integer, the fifth calculated value is equal to the total number of columns of the large table divided by the third set value minus one and then rounded to the integer plus one, and the sixth calculated value is equal to the total data volume divided by the third set value.

7. The automatic segmentation method according to claim 1, characterized in that: The step of naming the sub-table includes: The subtables are named in order of natural numbers.

8. The automatic segmentation method according to claim 1, characterized in that: The step of storing the sub-table includes: Select free space with large remaining storage space to store the subtable.

9. The automatic segmentation method according to claim 1, characterized in that: After the step of storing the named sub-table, the following steps are also included: Adjust the add, delete, modify and check statements; If horizontal partitioning technology is used, the union operation provided by the database system is used to merge the results; If vertical partitioning technology is used, the database system has its own join operation to merge the results.

10. An electronic device, characterized in that: The electronic device includes a processor and a memory, wherein the memory stores at least one instruction, and the at least one instruction is loaded and executed by the processor to implement the automatic segmentation method according to any one of claims 1 to 9.

Images