Distributed database row data storage and query method and distributed database system

Abstract

The invention discloses a distributed database row data storage and query method and a distributed database system, and the method comprises the steps: compressing and storing other data except recorded extra information and data corresponding to a first column primary key in target data in a compound row record format into a first target database table; according to the embodiment of the invention, when a client performs data query, page decompression operation does not need to be performed, meanwhile, when a data node returns a result set to a computing node, decompression does not need to be performed on later compressed information, and all compressed columns are directly transmitted to the computing node and the client through a protocol; the technical problems that a page compression technology needs to consume CPU resources in the compression and decompression processes, performance may be affected under some conditions, and consumed time is long are solved.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a distributed database row data storage and query method and a distributed database system. Background Art

[0002] With the rapid development of information technology and the national economy, the amount of data and user scale in the financial industry has exploded. With the continuous growth of data volume, enterprises and organizations are facing the challenges of rising storage costs and limited storage space. In this context, page compression technology came into being and became one of the key tools to solve these problems.

[0003] In early database systems, data compression usually means compressing the entire database or data files, which can lead to a significant drop in performance, especially when data needs to be frequently accessed and modified. To overcome these limitations, MySQL introduced page compression technology, which allows the database to be compressed at the page level instead of the entire table or database. The introduction of page compression technology enables MySQL to effectively reduce the use of disk space without sacrificing query performance. This technology is particularly suitable for columns with a large amount of duplicate data or compressible data, such as text and binary data. By compressing this data, MySQL can reduce I / O operations, improve data caching efficiency, and speed up data transmission.

[0004] However, page compression is not without its challenges. Page compression consumes CPU resources during the compression and decompression process, which may affect performance in some cases. Therefore, when implementing page compression, you need to carefully consider the characteristics of the data, the characteristics of the workload, and the specific needs of the system to ensure that the compression technology can bring the expected benefits rather than becoming a performance bottleneck. Summary of the invention

[0005] The present application provides a distributed database row data storage and query method and a distributed database system, which solves the technical problem that page compression technology consumes CPU resources during compression and decompression, may affect performance in some cases, and takes a long time.

[0006] In view of this, the first aspect of the present application provides a distributed database row data storage method, the method comprising:

[0007] Step S1: The client sends an insert statement to the data node through the computing node, wherein the insert statement carries a first target database table and first target data;

[0008] Step S2: The data node compresses and stores the first target data in a compact row record format according to the insert statement, except for the recorded additional information and the data corresponding to the first column primary key, in the first target database table.

[0009] Optionally, before step S1, the step further includes:

[0010] Step S0: The client sends a table creation statement to the data node, so that the data node determines the storage mode of the target database table.

[0011] A second aspect of the present application provides a distributed database row data query method, based on the distributed database row data storage method according to any one of the first aspects of the present application, the distributed database row data query method includes:

[0012] Step S3: The client sends an SQL query statement to the data node through the computing node, wherein the SQL query statement carries the second target database table and the second target data;

[0013] Step S4: After the data node screens and matches the second target database table according to the data corresponding to the first column primary key in the second target data, the data node returns a result set to the client via the computing node, and the result set ends with the compressed field length and compressed attribute information.

[0014] Optionally, the result set also includes an encryption flag.

[0015] Optionally, the step S4 further includes:

[0016] The computing node decrypts the result set according to the encryption flag, and then parses and concatenates the result set to obtain the second target data.

[0017] Optionally, the step S4 further includes:

[0018] After decrypting the result set according to the encryption flag, the client parses and concatenates the result set to obtain the second target data.

[0019] A third aspect of the present application provides a distributed database system, including: a client, a computing node, and a data node database;

[0020] The client sends an insert statement to the data node through the computing node, wherein the insert statement carries a first target database table and first target data;

[0021] The data node compresses and stores the first target data in a compact row record format according to the insert statement, and stores the other data in the first target data except the recorded additional information and the data corresponding to the first column primary key in the first target database table.

[0022] Optionally, it also includes:

[0023] Send a table creation statement to the data node, so that the data node determines the storage mode of the target database table.

[0024] Optionally, it also includes:

[0025] The client sends an SQL query statement to the data node through the computing node, wherein the SQL query statement carries a second target database table and second target data;

[0026] After the data node screens and matches the second target database table according to the data corresponding to the first column primary key in the second target data, the data node returns a result set to the client via the computing node, and the result set ends with the compressed field length and compressed attribute information.

[0027] Optionally, the result set also includes an encryption flag.

[0028] It can be seen from the above technical solutions that the embodiments of the present application have the following advantages:

[0029] In the present application, a distributed database row data storage and query method and a distributed database system are provided, which compress and store other data in the target data in the compact row record format except for the additional information recorded and the data corresponding to the primary key of the first column into the first target database table, so that when the client performs data query, there is no need to decompress the page. At the same time, when the data node returns the result set to the computing node, there is no need to decompress the compressed information later. All compressed columns are directly transmitted to the computing node and the client through the protocol, which solves the technical problem that the page compression technology consumes CPU resources during the compression and decompression process, which may affect the performance in some cases and is time-consuming. BRIEF DESCRIPTION OF THE DRAWINGS

[0030] Figure 1 This is a method flow chart of a distributed database row data storage method in an embodiment of the present application;

[0031] Figure 2 This is a method flow chart of a distributed database row data storage method in an embodiment of the present application;

[0032] Figure 3 This is a schematic diagram of the structure of a distributed database system in an embodiment of the present application. DETAILED DESCRIPTION

[0033] In order to enable those skilled in the art to better understand the solution of the present application, 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 only 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 creative work are within the scope of protection of this application.

[0034] The present application designs a distributed database row data storage and query method and a distributed database system, which solves the technical problem that page compression technology consumes CPU resources during compression and decompression, may affect performance in some cases, and takes a long time.

[0035] For easier understanding, see Figure 1 , Figure 1 This is a method flow chart of a distributed database row data storage method in an embodiment of the present application, such as Figure 1 As shown, specifically:

[0036] Step S1: The client sends an insert statement to the data node through the computing node, where the insert statement carries a first target database table and first target data;

[0037] It should be noted that the client prepares an INSERT statement, which includes the data to be inserted and the target database table.

[0038] For example: INSERT INTO my_table(id,name,age)VALUES(1,'Alice',30);

[0039] Step S2: The data node compresses and stores the first target data in a compact row record format according to the insert statement, except for the recorded additional information and the data corresponding to the first column primary key in the first target data in the first target database table.

[0040] It should be noted that after receiving the INSERT statement, the data node stores the data in the NEWCOMPACT row record format.

[0041] Except for additional information (such as variable-length field length list, NULL value list, record header information) and the first column primary key data, other data is compressed and stored.

[0042] For example, if the name and age fields of the my_table table are stored in a compressed format, the id field remains unchanged.

[0043] Furthermore, before step S1, the following steps are also included:

[0044] Step S0: The client sends a table creation statement to the data node, so that the data node determines the storage mode of the target database table.

[0045] It should be noted that the client sends a table creation statement to the data node, and specifically can notify the data node by adding a hint to specify that the table storage method adopts the row data compression storage method provided in the embodiment of the present application.

[0046] For example: / *storage='NEWCOMPACT'* / CREATE TABLE my_table(id INTPRIMARYKEY,name VARCHAR(255),age INT);

[0047] See also Figure 2 , Figure 2 This is a method flow chart of a distributed database row data storage method in an embodiment of the present application, such as Figure 2 As shown, specifically:

[0048] Step S3: The client sends an SQL query statement to the data node through the computing node, where the SQL query statement carries the second target database table and the second target data;

[0049] It should be noted that the client prepares a SELECT statement including query conditions and target database table.

[0050] For example: SELECT*FROM my_table WHERE id=1;

[0051] Step S4: After the data node screens and matches the second target database table according to the data corresponding to the first column primary key in the second target data, the data node returns a result set to the client via the computing node, and the result set includes the compressed field length and the compressed attribute information.

[0052] It should be noted that the data node performs screening and matching based on the primary key data to find matching rows.

[0053] When the result set is constructed, it contains the compressed field length and the end marker of the compressed attribute information.

[0054] If encryption is enabled, the result set also includes the encryption flag.

[0055] For example, a result set may contain: Result Set Header, Fields, Flag (Encrypted), Length (Compressed Length), EOF, Row Data (Compressed Data), EOF;

[0056] Furthermore, the result set also includes an encryption flag.

[0057] Furthermore, step S4 further includes:

[0058] After the computing node decrypts the result set according to the encryption flag, it parses and concatenates the result set to obtain the second target data.

[0059] It should be noted that after receiving the result set, the computing node decrypts it according to the encryption flag.

[0060] After decryption, the computing nodes parse and concatenate the result sets to form a complete data set.

[0061] For example, if the result set is encrypted, the compute node decrypts it using the corresponding key and then parses the compressed data to convert the row data into a readable format.

[0062] Furthermore, step S4 further includes:

[0063] After the client decrypts the result set according to the encryption flag, it parses and concatenates the result set to obtain the second target data.

[0064] It should be noted that after receiving the result set, the client can also decrypt it according to the encryption flag.

[0065] The client parses the compressed data and converts the row data into a readable format.

[0066] For example, after the client uses the key to decrypt, it uses a specific algorithm to decompress the data and then displays it to the user.

[0067] It is understandable that the database stage saves data page decompression time compared to before. At the same time, since the computing nodes transmit compressed data to the clients and data nodes, the overall data volume is also smaller than the previous uncompressed data volume.

[0068] See also Figure 3 , Figure 3 Schematic diagram of the structure of the distributed database system in the embodiment of the present application. Figure 3 As shown, specifically:

[0069] Client 301, computing node 302 and data node 303;

[0070] The client 301 sends an insert statement to the data node 303 through the computing node 302, where the insert statement carries the first target database table and the first target data;

[0071] It should be noted that the client 301 prepares an INSERT statement including the data to be inserted and the target database table.

[0072] For example: INSERT INTO my_table(id,name,age)VALUES(1,'Alice',30);

[0073] According to the insert statement, the data node 303 compresses and stores the first target data in a compact row record format, except for the recorded additional information and the data corresponding to the first column primary key, in the first target database table.

[0074] It should be noted that after receiving the INSERT statement, the data node 303 stores the data in the NEWCOMPACT row record format.

[0075] Except for additional information (such as variable-length field length list, NULL value list, record header information) and the first column primary key data, other data is compressed and stored.

[0076] For example, if the name and age fields of the my_table table are stored in a compressed format, the id field remains unchanged.

[0077] Furthermore, it also includes:

[0078] The client 301 sends a table creation statement to the data node 303, so that the data node 303 determines the storage mode of the target database table.

[0079] It should be noted that the client 301 sends a table creation statement to the data node 303, and specifically can notify the data node 303 by adding a hint to specify that the table storage method adopts the row data compression method provided in the embodiment of the present application.

[0080] For example: / *storage='NEWCOMPACT'* / CREATE TABLE my_table(id INTPRIMARYKEY,name VARCHAR(255),age INT);

[0081] Furthermore, it also includes:

[0082] The client 301 sends an SQL query statement to the data node 303 through the computing node 302, where the SQL query statement carries the second target database table and the second target data;

[0083] It should be noted that the client 301 prepares a SELECT statement including a query condition and a target database table.

[0084] For example: SELECT * FROM my_table WHERE id = 1;

[0085] After the data node 303 screens and matches the second target database table according to the data corresponding to the first column primary key in the second target data, the data node 303 returns a result set to the client 301 via the computing node 302 . The result set includes the compressed field length and the compressed attribute information.

[0086] It should be noted that the data node 303 performs screening and matching according to the primary key data to find matching rows.

[0087] When the result set is constructed, it contains the compressed field length and the end marker of the compressed attribute information.

[0088] If encryption is enabled, the result set also includes the encryption flag.

[0089] For example, a result set may contain: Result Set Header, Fields, Flag (Encrypted), Length (Compressed Length), EOF, Row Data (Compressed Data), EOF;

[0090] Furthermore, the result set also includes an encryption flag.

[0091] Furthermore, the computing node 302 decrypts the result set according to the encryption flag, parses and concatenates the result set, and obtains the second target data.

[0092] It should be noted that after receiving the result set, the computing node 302 performs decryption according to the encryption flag.

[0093] After decryption, the computing node 302 parses and concatenates the result set to form a complete data set.

[0094] For example, if the result set is encrypted, the computing node 302 uses the corresponding key to decrypt it, and then parses the compressed data to convert the row data into a readable format.

[0095] Furthermore, the client 301 decrypts the result set according to the encryption flag, parses and concatenates the result set, and obtains the second target data.

[0096] It should be noted that after receiving the result set, the client 301 may also perform decryption according to the encryption flag.

[0097] The client 301 parses the compressed data and converts the row data into a readable format.

[0098] For example, after the client 301 uses the key to decrypt, it uses a specific algorithm to decompress the data and then displays it to the user.

[0099] It can be understood that the database stage saves data page decompression time compared to before. At the same time, since the computing node 302 transmits compressed data to the client 301 and the data node 303, the overall data volume is also smaller than the previous uncompressed data volume.

[0100] In an embodiment of the present application, a distributed database row data storage and query method and a distributed database system are provided, which compress and store other data in the target data in a compact row record format except for the additional information recorded and the data corresponding to the primary key of the first column into the first target database table, so that when the client performs data query, there is no need to decompress the page. At the same time, when the data node returns a result set to the computing node, there is no need to decompress the compressed information later, and all compressed columns are directly transmitted to the computing node and the client through the protocol, which solves the technical problem that the page compression technology consumes CPU resources during the compression and decompression process, which may affect the performance in some cases and is time-consuming.

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

[0102] The terms "first", "second", "third", "fourth", etc. (if any) in the specification of the present application and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the data used in this way can be interchangeable where appropriate, so that the embodiments of the present application described herein can be implemented in an order other than those illustrated or described herein, for example. In addition, the terms "including" and "having" and any of their variations are intended to cover non-exclusive inclusions, for example, a process, method, system, product or device comprising a series of steps or units is not necessarily limited to those steps or units clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0103] It should be understood that in the present application, "at least one (item)" means one or more, and "plurality" means two or more. "And / or" is used to describe the association relationship of associated objects, indicating that three relationships may exist. For example, "A and / or B" can mean: only A exists, only B exists, and A and B exist at the same time, where A and B can be singular or plural. The character " / " generally indicates that the objects associated before and after are in an "or" relationship. "At least one of the following" or similar expressions refers to any combination of these items, including any combination of single or plural items. For example, at least one of a, b or c can mean: a, b, c, "a and b", "a and c", "b and c", or "a and b and c", where a, b, c can be single or multiple.

[0104] In the several embodiments provided in the present application, it should be understood that the disclosed systems, devices and methods can be implemented in other ways. For example, the device embodiments described above are only schematic. For example, the division of the units is only a logical function division. There may be other division methods in actual implementation, such as multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be an indirect coupling or communication connection through some interfaces, devices or units, which can be electrical, mechanical or other forms.

[0105] The units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed on multiple network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0106] In addition, each functional unit in each embodiment of the present application may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit. The above-mentioned integrated unit may be implemented in the form of hardware or in the form of software functional units.

[0107] If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present application is essentially or the part that contributes to the prior art or all or part of the technical solution can be embodied in the form of a software product, and the computer software product is stored in a storage medium, including a number of instructions to enable a computer device (which can be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the method described in each embodiment of the present application. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (full name in English: Read-Only Memory, English abbreviation: ROM), random access memory (full name in English: Random Access Memory, English abbreviation: RAM), disk or optical disk and other media that can store program codes.

[0108] As described above, the above embodiments are only used to illustrate the technical solutions of the present application, rather than to limit them. Although the present application has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or make equivalent replacements for some of the technical features therein. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present application.

Claims

1. A distributed database row data storage method, characterized in that: include: Step S1: The client sends an insert statement to the data node through the computing node, wherein the insert statement carries a first target database table and first target data; Step S2: The data node compresses and stores the first target data in a compact row record format according to the insert statement, except for the recorded additional information and the data corresponding to the first column primary key, in the first target database table.

2. The distributed database row data storage method according to claim 1, characterized in that: Before step S1, the following steps are also included: Step S0: The client sends a table creation statement to the data node, so that the data node determines the storage mode of the target database table.

3. A distributed database row data query method, characterized in that: Based on the distributed database row data storage method according to any one of claims 1 or 2, the distributed database row data query method comprises: Step S3: The client sends an SQL query statement to the data node through the computing node, wherein the SQL query statement carries the second target database table and the second target data; Step S4: After the data node screens and matches the second target database table according to the data corresponding to the first column primary key in the second target data, the data node returns a result set to the client via the computing node, and the result set ends with the compressed field length and compressed attribute information.

4. The distributed database row data query method according to claim 3, characterized in that: The result set also includes an encryption flag.

5. The distributed database row data query method according to claim 4, characterized in that: The step S4 further comprises: The computing node decrypts the result set according to the encryption flag, and then parses and concatenates the result set to obtain the second target data.

6. The distributed database row data query method according to claim 4, characterized in that: The step S4 further comprises: After decrypting the result set according to the encryption flag, the client parses and concatenates the result set to obtain the second target data.

7. A distributed database system, characterized in that: include: Clients, compute nodes, and data nodes; The client sends an insert statement to the data node through the computing node, wherein the insert statement carries a first target database table and first target data; The data node compresses and stores the first target data in a compact row record format according to the insert statement, and stores the other data in the first target data except the recorded additional information and the data corresponding to the first column primary key in the first target database table.

8. The distributed database system according to claim 7, characterized in that: Also includes: Send a table creation statement to the data node, so that the data node determines the storage mode of the target database table.

9. The distributed database system according to claim 7, characterized in that: Also includes: The client sends an SQL query statement to the data node through the computing node, wherein the SQL query statement carries a second target database table and second target data; After the data node screens and matches the second target database table according to the data corresponding to the first column primary key in the second target data, the data node returns a result set to the client via the computing node, and the result set ends with the compressed field length and compressed attribute information.

10. The distributed database system according to claim 9, characterized in that: The result set also includes an encryption flag.

Images