A method for nearby access to cross-regional database data based on geographical partitioning

Abstract

The present invention discloses a method for nearby access to cross-regional data in a database based on geographic partitioning, and the present invention comprises the following steps: S1, creating a cross-domain index; S2, storing the created cross-domain index nearby; S3, improving the use priority of the cross-domain index by executing the optimizer CBO; S4, using the cross-domain index to read the requested data. In the distributed storage scenario of geographic partitioning, the present invention solves the problems of long cross-regional data reading and cross-domain calculation time, and reduces the network pressure caused by cross-regional data reading, reduces the cross-regional network overhead, and reduces the IO pressure of the database system by reducing the reading of the entire row of data. Since the data can be accessed nearby, the execution performance of the database is improved.

Description

Technical Field

[0001] The invention relates to the technical field of database storage, in particular to a method for nearby access to cross-regional database data based on geographical partitioning. Background Art

[0002] In a distributed database management system, when cluster nodes are distributed across multiple physically isolated computer rooms across regions, in order to ensure that data can be accessed locally, user data will be distributed to computer rooms in corresponding geographical locations using a geographic partition table. The partitioning rule is often based on geographical location, i.e., the data in city A is concentrated in the computer room in city A. However, in order to ensure high availability of the database, there will be a multi-location and multi-center data distribution method, which is generally not easy to change, including the storage location and the number of backup copies. This data geographic partitioning brings efficient access to local data, but also increases the overhead of cross-regional partition access, resulting in low performance of the database system involving cross-regional access, and a large amount of cross-domain network overhead. Summary of the invention

[0003] The purpose of the present invention is to address the above shortcomings and provide a method for local access to cross-regional data in a database based on geographical partitioning, which is used to solve the problem of converting cross-regional access into local access when user table data is geographically partitioned in existing distributed databases.

[0004] The technical solution adopted by the present invention is:

[0005] A method for accessing cross-regional data in a database based on geographic partitioning comprises the following steps:

[0006] S1. Create a cross-domain index;

[0007] S2. Store the created cross-domain index nearby;

[0008] S3. Improve the use priority of cross-domain indexes by executing the optimizer CBO;

[0009] S4. Use cross-domain indexing to read the requested data.

[0010] As a further optimization, the process of creating a cross-domain index in step S1 of the present invention includes the following steps:

[0011] S101, selecting index columns and storage columns required for establishing a cross-domain index according to access requirements, and collecting the cross-domain partition range and the storage partition location of the index data;

[0012] S102, reading required data from the corresponding storage partition;

[0013] S103, converting the read data into kv storage mode.

[0014] As a further optimization, the cross-domain index data of the present invention is created starting from a storage partition containing the most required data.

[0015] As a further optimization, the process of storing the created cross-domain index nearby in step S2 of the present invention includes the following steps:

[0016] S201, parsing the geographical location information of the cross-domain index, and automatically setting it to the geographical location of the created node if the user has not set it;

[0017] S202: Migrate the cross-domain index data to a nearby storage node for storage according to the set geographical location information.

[0018] As a further optimization, in step 202 of the present invention, after storing the storage node corresponding to the cross-domain index data migration value, the index key with the number of index columns greater than 2 is parsed to generate corresponding derived multi-level keys. The derived multi-level keys correspond to the same value, and each level of derived sub-key sequentially removes a key value at the head for range queries of multiple related columns.

[0019] As a further optimization, in step S1 of the present invention, in order to reduce the amount of data transmission, no multi-level index key is generated during the cross-domain index data creation process.

[0020] As a further optimization, in step S3 of the present invention, the process of improving the use priority of the cross-domain index by executing the optimizer CBO includes the following steps:

[0021] S301, receiving a cross-domain query SQL request sent by a user;

[0022] S302, parsing the SQL request content, and extracting the geographical partition information involved in the SQL request to determine whether cross-region data reading occurs;

[0023] S303, the optimizer CBO is executed to match whether the data involved in the SQL request is consistent with the cross-domain index;

[0024] S304: When the cross-domain index contains all SQL request data, the priority of the cross-domain index is increased to reduce the cost of using the cross-domain index.

[0025] As a further optimization, when the SQL request has a multi-column range query, the present invention automatically decomposes it into multi-level key matching and reads the required data at one time.

[0026] As a further optimization, the present invention performs customized updates on SQL requests not included in the cross-domain index when executing the optimizer CBO to compare and match SQL requests across domain indexes. When the cross-domain index is updated, only the changed data is read, the new index data is incrementally stored, data is sorted regularly, and invalid data is eliminated.

[0027] The present invention has the following advantages:

[0028] 1. In the distributed storage scenario of geographical partitions, the present invention solves the problem of long cross-regional data reading and cross-domain computing time, reduces the network pressure caused by cross-regional data reading, reduces cross-regional network overhead, and reduces the IO pressure of the database system by reducing the reading of the entire row of data. Since data can be accessed nearby, the execution performance of the database is improved;

[0029] 2. After the cross-domain index of the present invention is locally stored, by deriving a multi-level key structure corresponding to the same value, when performing a range query on multiple related columns, the results can be obtained at one time through the multi-level sub-keys, thereby reducing the read amplification problem of the entire index scan. Moreover, the introduction of the multi-level key makes the range of the cross-domain index larger, thereby further improving the probability of local access. BRIEF DESCRIPTION OF THE DRAWINGS

[0030] In order to more clearly illustrate the technical solutions in the embodiments of the present invention, the drawings required for use in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work.

[0031] The present invention is further described below in conjunction with the accompanying drawings:

[0032] Figure 1 is an exemplary flow chart of the present invention;

[0033] Figure 2 This is a cross-domain kv structure diagram. DETAILED DESCRIPTION

[0034] The present invention is further described below in conjunction with the accompanying drawings and specific embodiments so that those skilled in the art can better understand the present invention and implement it. However, the embodiments are not intended to limit the present invention. In the absence of conflict, the embodiments of the present invention and the technical features in the embodiments may be combined with each other.

[0035] It should be understood that in the description of the embodiments of the present invention, the words "first", "second", etc. are only used for the purpose of distinguishing the description, and cannot be understood as indicating or implying relative importance, nor can they be understood as indicating or implying an order. The "plurality" in the embodiments of the present invention refers to two or more.

[0036] The "and / or" in the embodiments of the present invention is only a description of the association relationship of the associated objects, indicating that there may be three relationships. For example, A and / or B can represent: A exists alone, B exists alone, and A and B exist at the same time. In addition, the character " / " in this document generally indicates that the associated objects before and after are in an "or" relationship.

[0037] The embodiment of the present invention provides a method for accessing cross-regional data in a database based on geographical partitions. Figure 1 As shown, the following steps are included:

[0038] S1. Create a cross-domain index; the steps are as follows:

[0039] S101, selecting index columns and storage columns required for establishing a cross-domain index according to access requirements, and collecting the cross-domain partition range and the storage partition location of the index data to ensure that the index data is stored in the storage partition in use;

[0040] S102, read the required data from the corresponding storage partition; in order to reduce the amount of data transmission, the cross-domain index data is created starting from the storage partition involved and with the largest data volume;

[0041] S103, converting the read data into kv storage mode.

[0042] S2. Store the created cross-domain index nearby;

[0043] S201, parsing the geographical location information of the cross-domain index, and automatically setting it to the geographical location of the created node if the user has not set it;

[0044] S202: Migrate the cross-domain index data to a corresponding storage node for storage according to the set geographic location information.

[0045] S3. Improve the use priority of cross-domain indexes by executing the optimizer CBO.

[0046] S301, receiving a cross-domain query SQL request sent by a user;

[0047] S302, parsing the SQL request content, and extracting the geographical partition information involved in the SQL request to determine whether cross-region data reading occurs;

[0048] S303, the optimizer CBO is executed to match whether the data involved in the SQL request is consistent with the cross-domain index; and in the process of the CBO optimizer matching the SQL request with the cross-domain index, the first cross-domain index is continuously updated;

[0049] S304: When the cross-domain index contains all SQL request data, the priority of the cross-domain index is increased to reduce the cost of using the cross-domain index.

[0050] S4. The SQL executor optimizer uses the cross-domain index to read the requested data. After the cross-domain index is optimized and updated by the execution optimizer, the priority of the cross-domain epitome is increased, and the cost of use is discussed. Therefore, when the execution plan is actually generated, the cross-domain index with the lowest cost is used to read the requested data.

[0051] The distributed NEWSQL database management system adopts KV storage for cross-region indexes. The key records the index columns that users need to access, and the value stores the data of related columns. Since KV storage can well meet the exact matching requirements, but KV storage is linear storage, when the index column has multiple columns, it can only meet the range matching of one field in the index column order in the key. Therefore, in order to improve the utilization rate of cross-domain indexes, this embodiment derives a storage method of corresponding multiple-level keys to one value, such as Figure 2 As shown, in the original key, the kv structure diagram includes the table ID number, index number, level number, field 1 value, field 2 value, field 3 value and the corresponding value. For example, table 50 creates a cross-domain index 1, where the Key value is: a=1, b=2, c=3, the value is: d=99, e='a', and the original key is as follows Figure 2 As shown in the first row: table ID number 50, index number 1, level number 1, field 1 value a=1, field 2 value b=2, field 3 value c=3, the first-level derived subkey is as follows Figure 2 As shown in the second row, the table ID number is 50, the index number is 1, the level number is 2, the field 2 value b=2, the field 3 value c=3, and the secondary derived subkey is as follows Figure 2 As shown in the third row, the table ID number is 50, the index number is 1, the level number is 3, and the field 3 value c=3. Based on the above kv storage method, when a range query of multiple related columns is required, the result can be obtained at one time through the multi-level derived sub-key, reducing the read amplification problem of the entire index scan.

[0052] In the process of creating the above cross-domain index, in order to reduce the amount of transmission, multi-level derivative keys are not generated during creation. After the cross-domain index data is transferred to the nearest node for storage, data checksum verification will be performed, and multi-level keys will be generated for the main index key with more than 2 index columns, and finally multi-level derivative keys will be generated. Based on the above kv structure, the cross-domain index data is stored in blocks and hierarchically in the created storage partition. The block is a single index data, and the level is the above-mentioned level. The blocks are sorted according to the key value, and the indexes in the same level are stored continuously to facilitate hierarchical search.

[0053] Based on the kv structure of the cross-domain index of this embodiment, when the SQL request has a multi-column range query, it is automatically decomposed into multi-level key matching, and the required data is read at one time to avoid full index scanning. The introduction of multi-level keys makes the scope of the cross-domain index larger, thereby further improving the probability of local access.

[0054] In this embodiment, when the CBO optimizer compares and matches the SQL request for the cross-domain index, a customized update is performed on the SQL request not included in the cross-domain index. When the cross-domain index is updated, only the changed data is read, the new index data is incrementally stored, and data is sorted regularly to eliminate invalid data.

[0055] Combine the following Figure 1 The method for nearby access to cross-regional data of the present invention is further described. Figure 1 In the distributed NEWSQL database management system, there are multiple storage partitions. When users access data, sql calculates the best data reading path by executing the optimizer to save access overhead. Based on this distributed storage method, when there is cross-region access, even if the best data reading path is calculated by executing the optimizer, due to its cross-region access characteristics, the overhead of cross-region access will still be greatly increased, resulting in a certain impact on the overall access performance of the database. Therefore, the cross-domain data nearby access method based on the present invention includes the following steps: the user's nearby access is storage partition 1. First, based on the user's cross-domain access requirements, a cross-region index is created. The index columns and related columns of the table required for cross-region access and the partition flag are selected to create a cross-region index. Different from ordinary indexes, cross-region indexes only pull part of the data of the table that the user cares about to create an index. The user's cross-region access requirements may be distributed in multiple storage partitions. Select a partition that stores the most user access requirements to start creation, such as Figure 1As shown, storage partition 3 contains the most user cross-domain access requirements. Index data is created in storage partition 3, and the required data is extracted from other partitions for storage, and the extracted data is converted into kv storage mode; after the index data is created, it will be migrated to storage partition 1 according to the geographic location information carried by the cross-domain index. After the data is migrated to the destination node, the data checksum will be verified, and the primary index key will be multi-level key generated at the same time, and finally a multi-level index is generated. In the process of creating the aforementioned index data, in order to reduce data transmission, the multi-level index key will not be generated. Based on the above process, the cross-domain index is created, but after the cross-domain index is completed, it cannot be used. The effectiveness of the cross-domain index depends on the optimization of the sql execution optimizer CBO. After receiving the user query request, the execution optimizer first extracts the user data range involved in the sql and calculates whether cross-domain access is generated. After that, the relevant columns and projection columns of the sql are extracted, and the columns are matched with the cross-domain index. When the cross-domain index can contain all the request data of the sql request, the priority of the cross-domain index is increased and the cost of use is reduced. In this way, when the execution plan is actually generated, the cross-domain index with the lowest cost will be used to read the requested data. Based on the optimization of the SQL execution optimizer CBO, when connecting to user data access needs, cross-domain indexes will be automatically scheduled for customized updates and will take effect automatically.

[0056] Since the cross-domain index is stored in KV, when matching index data, you can directly follow Figure 1 The structure directly concatenates the query results into keys for matching hits in the index data. For range queries, special values ​​of min and max are introduced. When you want to identify a range query, use min or max to indicate the minimum or maximum upper limit. For example, the search field a>1 can be translated into two key ranges [1] to [max]. The range query is described by two keys.

[0057] The above-described embodiments are only preferred embodiments for fully illustrating the present invention, and the protection scope of the present invention is not limited thereto. Equivalent substitutions or changes made by those skilled in the art based on the present invention are within the protection scope of the present invention. The protection scope of the present invention shall be subject to the claims.

Claims

1. A method for nearby access to cross-regional data in a database based on geographical partitioning, characterized in that: The steps include: S1. Create a cross-domain index; S101, selecting index columns and storage columns required for establishing a cross-domain index according to access requirements, and collecting the cross-domain partition range and the storage partition location of the index data; S102, reading required data from the corresponding storage partition; S103, converting the read data into a kv storage mode; S2. Store the created cross-domain index nearby; S201, parsing the geographical location information of the cross-domain index, and automatically setting it to the geographical location of the created node if the user has not set it; S202: Migrate the cross-domain index data to a nearby storage node for storage according to the set geographical location information; S3. Improve the use priority of cross-domain indexes by executing the optimizer CBO; S301, receiving a cross-domain query SQL request sent by a user; S302, parsing the SQL request content, and extracting the geographical partition information involved in the SQL request to determine whether cross-region data reading occurs; S303, the optimizer CBO is executed to match whether the data involved in the SQL request is consistent with the cross-domain index; S304, when the cross-domain index contains all SQL request data, the priority of the cross-domain index is increased to reduce the cost of using the cross-domain index; S4. Use cross-domain indexing to read the requested data.

2. The method for nearby access to cross-regional database data based on geographical partitioning according to claim 1 is characterized in that: Cross-domain index data is created starting from the storage partition that contains the most required data.

3. The method for nearby access to cross-regional database data based on geographical partitioning according to claim 1 is characterized in that: In step 202, after storing the storage node corresponding to the cross-domain index data migration value, the index key with more than 2 index columns is parsed to generate corresponding derived multi-level keys. The derived multi-level keys correspond to the same value, and each level of derived sub-key sequentially removes a key value at the head for range query of multiple related columns.

4. The method for nearby access to cross-regional database data based on geographical partitioning according to claim 3 is characterized in that: In step S1, in order to reduce the amount of data transmission, no multi-level index key is generated during the cross-domain index data creation process.

5. The method for nearby access to cross-regional database data based on geographical partitioning according to claim 1 is characterized in that: When a SQL request contains a multi-column range query, it is automatically decomposed into multi-level key matches to read the required data at one time.

6. The method for nearby access to cross-regional database data based on geographical partitioning according to claim 1 is characterized in that: When the optimizer CBO compares and matches SQL requests for cross-domain indexes, customized updates are performed on SQL requests that are not included in the cross-domain indexes. When the cross-domain index is updated, only the changed data is read, the new index data is incrementally stored, and data is sorted regularly to eliminate invalid data.

Images