Data processing method, device and storage medium

By identifying and compressing tables with low data access but large data volume in the database and storing them as target fields, the problem of tight storage space in the database is solved, and space saving and query speed are improved.

CN114942926BActive Publication Date: 2024-11-12GREE ELECTRIC APPLIANCE INC OF ZHUHAI +1
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202210640261.4
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Filing Date
2022-06-07
Publication Date
2024-11-12
Estimated Expiration
2042-06-07

AI Technical Summary

Technical Problem

In the prior art, as the amount of data increases, the storage space of the database is gradually tight, and there is a lack of effective solutions.

Method used

By determining that the table in the database has a lower data access volume and a larger data access volume than another threshold, compress it into a target field, and store it in another table, reducing the number of tables and saving storage space.

Benefits of technology

It effectively saves the storage space of the database, especially when there are large data volume and low usage frequency, it can save more storage space, speed up query speed, and reduce server pressure.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN114942926B_ABST
    Figure CN114942926B_ABST
Patent Text Reader

Abstract

The present application relates to a data processing method, device and storage medium, wherein the method comprises: determining a first table in a database that meets a preset condition, wherein the first table that meets the preset condition means that the data access volume in the table is lower than a first threshold value and the data volume in the table is greater than a second threshold value; compressing the first table that meets the preset condition into a target field, and storing the target field in a second table. Through the present application, the problem of tight storage space of the database due to large data volume in the prior art is solved.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present application relates to the field of computers, and in particular to a data processing method, device and storage medium. Background Art

[0002] At present, the Internet industry is developing faster and faster. Distributed file systems, Nosql databases and cloud databases are often used in existing technologies to store data. However, with the increase in business volume and user volume, the amount of data also increases. Faced with increasingly large and complex data, the pressure on the database is also increasing.

[0003] There is currently no effective solution to the above problems in the prior art. Summary of the invention

[0004] The present application provides a data processing method, device and storage medium to solve the problem in the prior art that the storage space of the database is relatively tight due to the large amount of data.

[0005] In a first aspect, the present application provides a method for processing data, comprising: determining a first table in a database that meets preset conditions, wherein the first table that meets the preset conditions refers to a table in which the data access volume is lower than a first threshold and the data volume in the table is greater than a second threshold; compressing the first table that meets the preset conditions into a target field, and storing the target field in a second table.

[0006] In a second aspect, the present application provides a data processing device, comprising: a determination module, used to determine a first table in a database that meets preset conditions, wherein the first table that meets the preset conditions refers to a table in which the data access volume is lower than a first threshold and the data volume in the table is greater than a second threshold; a first processing module, used to compress the first table that meets the preset conditions into a target field, and store the target field in a second table.

[0007] In a third aspect, an electronic device is provided, comprising a processor, a communication interface, a memory and a communication bus, wherein the processor, the communication interface and the memory communicate with each other via the communication bus;

[0008] Memory, used to store computer programs;

[0009] The processor is used to implement the method steps described in any embodiment of the first aspect when executing the program stored in the memory.

[0010] In a fourth aspect, a computer-readable storage medium is provided, on which a computer program is stored. When the computer program is executed by a processor, the method steps described in any embodiment of the first aspect are implemented.

[0011] The above technical solution provided by the embodiment of the present application has the following advantages compared with the prior art:

[0012] The method provided in the embodiment of the present application determines the first table in the database that meets the preset conditions, that is, determines the first table with a relatively large data volume but a relatively low usage frequency, compresses the first table into target fields and stores them in the second table. For the entire database, one table is missing, and only one field is added to the other table, thereby saving the storage space of the database. In the case where there are a large number of tables with a relatively large data volume and a relatively low usage frequency, a similar method can be adopted. In this case, more storage space can be saved, thereby solving the problem of tight storage space of the database due to the large data volume in the prior art. BRIEF DESCRIPTION OF THE DRAWINGS

[0013] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the invention and, together with the description, serve to explain the principles of the invention.

[0014] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, for ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative labor.

[0015] Figure 1 One of the flowcharts of a data processing method provided in an embodiment of the present application;

[0016] Figure 2 A second flow chart of a data processing method provided in an embodiment of the present application;

[0017] Figure 3 A schematic diagram of a flow chart of a method for dividing large amounts of data provided in an embodiment of the present application;

[0018] Figure 4 One of the structural schematic diagrams of a data processing device provided in an embodiment of the present application;

[0019] Figure 5 A second structural diagram of a data processing device provided in an embodiment of the present application;

[0020] Figure 6 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0021] In order to make the purpose, technical solution and advantages of the embodiments of the present application clearer, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of this application.

[0022] Figure 1 A flowchart of a data processing method provided in an embodiment of the present application is shown as follows: Figure 1 As shown, the steps of the method include:

[0023] Step 102, determining a first table in the database that meets a preset condition, wherein the first table that meets the preset condition means that the data access volume in the table is lower than a first threshold and the data volume in the table is greater than a second threshold;

[0024] It should be noted that the first threshold and the second threshold in the embodiment of the present application can be set accordingly according to the actual situation. The requirements for data access volume in different application scenarios are different, and the data volume is similar. For example, in a certain application scenario, if the data access volume is less than 500 times within 1 minute, it means that the access volume is relatively low, and the first threshold can be set to 500. In this scenario, if the data volume accumulates to 200M within one minute, it means that the current data volume is large, and the second threshold can be set to 200M. Of course, the above first threshold and second threshold are only examples, and can be set accordingly according to the actual situation.

[0025] Step 104: compress the first table that meets the preset conditions into target fields, and store the target fields in the second table.

[0026] It should be noted that the first table and the second table are two different tables in the database, and may also be any two different tables in the database.

[0027] Through the above steps 102 to 104, by determining the first table in the database that meets the preset conditions, that is, determining the first table with a relatively large data volume but a relatively low usage frequency, the first table is compressed into a target field and then stored in the second table. For the entire database, one table is missing, and only one field is added to another table, thereby saving the storage space of the database. In the case where there are a large number of tables with a relatively large data volume and a relatively low usage frequency, a similar method can be adopted. In this case, more storage space can be saved, thereby solving the problem of tight storage space of the database due to the large data volume in the prior art.

[0028] In an optional implementation of an embodiment of the present application, the method of compressing the first table that meets the preset conditions into a target field involved in step 104 can further be: compressing the first table in the database associated with MySql into a Json (JavaScript Object Notation, JS object notation) field, wherein the Json field includes the primary key corresponding to the first table.

[0029] In an optional implementation of the embodiment of the present application, the method of storing the target field in the second table involved in step 104 may further include: storing the Json field in the target column of the second table.

[0030] Since Json is a lightweight data exchange format, after compressing the first table into a Json field, it can be easily read and written by humans, and is also easy for machines to parse and generate, and can be conveniently and quickly stored in the target column of the second table. It should be noted that a table may include multiple columns, that is, in addition to the target column, other columns in the second table can also store other tables compressed into Json fields. In addition, the primary key of the first table is also retained during the compression process, so that data can be easily and quickly queried after the subsequent split.

[0031] In an optional implementation of the embodiment of the present application, after the target field is stored in the second table, Figure 1 On the basis of Figure 2 As shown, the method of the embodiment of the present application may also include:

[0032] Step 106: when receiving a request for accessing data in the first table, split the Json field in the second table to obtain one or more third tables, wherein the third tables store primary keys associated with other tables in the one or more third tables, and the one or more third tables include the first table;

[0033] Among them, in the implementation of this application, the Json field in the second table can be split based on the binary log (binlog).

[0034] Step 108, associating one or more third tables based on the primary key;

[0035] Step 110: In response to the access request, query data based on the primary key, and query the data in the first table from the one or more associated third tables.

[0036] Step 112, returning the query result to the server;

[0037] Step 114, when the server indicates that the query result is normal, indicate that the data query is successful and end the data query;

[0038] Step 116: When the server indicates that the query result is abnormal, the operation of querying data based on the primary key is executed again after processing the abnormal data.

[0039] By the above steps 106 to step 116, when it is necessary to query data, the Json field stored in the second table can be split first to obtain another or multiple tables, and the table obtained by the split also retains the primary key for characterizing the relationship between the table and the table, therefore, after splitting, the split table can be associated based on the primary key. When the query data is abnormal (abnormal format, data error, etc.), data query can be performed again after abnormal data is processed. That is to say, by the method of the embodiment of the present application, not only can the space of the database be saved, but also the query speed can be accelerated to reduce the pressure of the server.

[0040] The present application is described below with reference to a specific implementation of an embodiment of the present application. The specific implementation provides a method for splitting a large amount of data, such as Figure 3 As shown, the steps of the method include:

[0041] Step 301, locate a table with a small number of visits but a large amount of data by checking the access records of the database;

[0042] That is to say, in step 301, in order to find out the part of the table with less frequent access, and then observe the data volume of that part of the table, find out the part with relatively large data volume; how to determine the part with large data volume and low access frequency can be set accordingly according to the actual situation.

[0043] Step 302, process the located table into a JSON field and store it in a column of another table.

[0044] Step 303, waiting for an access request to the data;

[0045] Step 304: After receiving the access request, use binlog to split the previously processed huge Json fields;

[0046] Each layer of Json fields can be split into a new Table, and the new Table will retain the primary key of the previous layer of Json.

[0047] Tables are associated with each other through the reserved primary keys.

[0048] Step 305, after the tables are associated based on the primary key, data is queried, and the query result is returned to the server;

[0049] Step 306, if there is no problem, the data query is successful and the program ends;

[0050] Step 307: If the data is abnormal, the problem is located and the data is accessed after the problem is processed.

[0051] Through the above steps 301 to 307, some tables with less access and large data volume are compressed into Json fields and stored in a column of a table, and the primary key is retained; when querying data later, this column can be split out again, and the Json can be split into a table, and the data can be queried by the previously retained primary key. This method can not only save database space, but also speed up the query speed and reduce the pressure on the server.

[0052] Corresponding to the above Figure 1 The present application also provides a data processing device, such as Figure 4 As shown, the device comprises:

[0053] A determination module 42, configured to determine a first table in the database that meets a preset condition, wherein the first table that meets the preset condition refers to a table in which the amount of data access is lower than a first threshold and the amount of data in the table is greater than a second threshold;

[0054] The first processing module 44 is used to compress the first table that meets the preset conditions into target fields, and store the target fields in the second table.

[0055] Through the device of the embodiment of the present application, by determining the first table in the database that meets the preset conditions, that is, determining the first table with a relatively large data volume but a relatively low usage frequency, the first table is compressed into a target field and then stored in the second table. For the entire database, one table is missing, and only one field is added to the other table, thereby saving the storage space of the database. In the case where there are a large number of tables with a relatively large data volume and a relatively low usage frequency, a similar method can be adopted. In this case, more storage space can be saved, thereby solving the problem of tight storage space of the database due to the large data volume in the prior art.

[0056] Optionally, the first processing module 44 in the embodiment of the present application may further include: a compression unit, used to compress the first table in the database associated with MySql into a Json field, wherein the Json field includes a primary key corresponding to the first table; a processing unit, used to store the Json field in a target column in the second table.

[0057] like Figure 5 As shown, the device in the embodiment of the present application may also include:

[0058] A splitting module 52 is used for, after storing the target field in the second table, splitting the Json field in the second table to obtain one or more third tables, wherein the third table stores primary keys associated with other tables in the one or more third tables, and the one or more third tables include the first table;

[0059] An association module 54, configured to associate the one or more third tables based on the primary key;

[0060] The query module 56 is used to query the data in the first table from the one or more associated third tables in response to the access request.

[0061] A return module 58, configured to return a query result to the server after querying the data in the first table from the one or more associated third tables;

[0062] The second processing module 60 is used to indicate that the data query is successful and end the data query when the server indicates that the query result is normal;

[0063] The third processing module 62 is used to process the abnormal data and then perform the operation of querying data based on the primary key again when the server indicates that the query result is abnormal.

[0064] It can be seen that when it is necessary to query the data, the Json field stored in the second table can be split first to obtain another table, and the table also retains the primary key. Therefore, the subsequent data query is not affected after the split, and in the case of abnormal query data (format abnormality, data error, etc.), the abnormal data can be processed and the data query can be performed again. In other words, the method of the embodiment of the present application can not only save database space, but also speed up the query speed and reduce the pressure on the server.

[0065] Optionally, the splitting module 52 may further include: a splitting unit, configured to split the Json field in the second table based on the binary log.

[0066] like Figure 6 As shown, an embodiment of the present application provides an electronic device, including a processor 111, a communication interface 112, a memory 113 and a communication bus 114, wherein the processor 111, the communication interface 112, and the memory 113 communicate with each other through the communication bus 114.

[0067] Memory 113, used for storing computer programs;

[0068] In one embodiment of the present application, the processor 111 is used to execute the program stored in the memory 113 to implement the data processing method provided by any of the above method embodiments, and the role played is similar to Figure 1 The method is similar to that in , so I will not repeat it here.

[0069] An embodiment of the present application further provides a computer-readable storage medium having a computer program stored thereon. When the computer program is executed by a processor, the steps of the data processing method provided in any one of the aforementioned method embodiments are implemented.

[0070] It should be noted that, in this article, relational terms such as "first" and "second" are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Moreover, the terms "include", "comprise" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, the elements defined by the sentence "comprise a ..." do not exclude the existence of other identical elements in the process, method, article or device including the elements.

[0071] The foregoing is merely a specific embodiment of the present invention, which enables those skilled in the art to understand or implement the present invention. Various modifications to these embodiments will be apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the present invention. Therefore, the present invention will not be limited to the embodiments shown herein, but rather to the widest scope consistent with the principles and novel features claimed herein.

Claims

1. A data processing method, characterized in that: include: Determine a first table in the database that meets a preset condition, wherein the first table that meets the preset condition means that the data access volume in the table is lower than a first threshold and the data volume in the table is greater than a second threshold; Compressing the first table that meets the preset condition into a target field, and storing the target field in the second table; The compressing the first table that meets the preset condition into a target field includes: compressing the first table in a database associated with MySql into a Json field, wherein the Json field includes a primary key corresponding to the first table; The storing the target field into the second table includes: storing the Json field into a target column in the second table; After storing the target field in the second table, the method further includes: In the case of receiving an access request for data in the first table, splitting the Json field in the second table to obtain one or more third tables, wherein the third tables store primary keys associated with other tables in the one or more third tables, and the one or more third tables include the first table; Associating the one or more third tables based on the primary key; In response to the access request, the data in the first table is queried from the one or more associated third tables.

2. The method according to claim 1, characterized in that After querying the data in the first table from the associated one or more third tables, the method further includes: Return query results to the server; When the server indicates that the query result is normal, indicating that the data query is successful and ending the data query; When the server indicates that the query result is abnormal, the operation of querying data based on the primary key is performed again after processing the abnormal data.

3. The method according to claim 1, characterized in that The splitting of the Json field in the second table includes: The Json field in the second table is split based on the binary log.

4. A data processing device, characterized in that: include: A determination module, configured to determine a first table in a database that meets a preset condition, wherein the first table that meets the preset condition refers to a table in which the amount of data access is lower than a first threshold and the amount of data in the table is greater than a second threshold; A first processing module, used for compressing the first table satisfying the preset condition into a target field, and storing the target field in a second table; The first processing module includes: a compression unit, used to compress the first table in the database associated with MySql into a Json field, wherein the Json field includes a primary key corresponding to the first table; a processing unit, used to store the Json field into a target column in the second table; The device also includes: A splitting module, configured to split the Json field in the second table after storing the target field in the second table, to obtain one or more third tables, wherein the third table stores primary keys associated with other tables in the one or more third tables, and the one or more third tables include the first table; An association module, configured to associate the one or more third tables based on the primary key; A query module is used to query the data in the first table from the one or more associated third tables in response to the access request.

5. An electronic device, characterized in that: It includes a processor, a communication interface, a memory and a communication bus, wherein the processor, the communication interface and the memory communicate with each other through the communication bus; Memory, used to store computer programs; A processor, for implementing the method according to any one of claims 1 to 3 when executing a program stored in a memory.

6. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the method according to any one of claims 1 to 3 is implemented.