Data processing method, device and storage medium

Abstract

This application provides a data processing method, device, and storage medium. The method includes the following steps: determining a database table to be partitioned and its primary key; determining a first target column from the database table that is distinct from the primary key; if a mapping relationship exists between the primary key and a partitioning function using the first target column as the partitioning key, then partitioning the database table according to the partitioning function using the first target column as the partitioning key to obtain multiple partitions; wherein the primary key of each partition is consistent with the primary key of the database table. The technical solution provided by this application can eliminate the restriction in the prior art that the primary key must include a partitioning key while ensuring the global uniqueness of the primary key, providing users with greater convenience.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to a data processing method, device and storage medium. Background Technology

[0002] The rapid growth of internet applications has led to a significant increase in the amount of data in a single database table. Traditional RDS (Relational Database Service) tables, due to limitations in local disk space, cannot overcome the limitations of the upper-layer executor's computing power, even when using shared storage. To address these limitations, database sharding and table partitioning were prematurely adopted, along with the temporary deployment of a third-party distributed optimizer to handle execution plans and multi-machine concurrency scenarios.

[0003] The MySQL relational database management system within the next-generation relational cloud-native database product PolarDB, supporting ePQ (elastic parallel query) and single-machine parallelism and cross-machine parallelism, greatly alleviates users' needs for database sharding and table partitioning, adopting a centralized and simple solution. However, for single tables with excessively large datasets, performance bottlenecks and starts to decline once the data volume reaches a certain level. Managing single tables, single clustered indexes, and secondary indexes becomes difficult. How to efficiently divide and conquer data and elastically scale without sacrificing transaction and analytical performance is a pressing issue that needs to be addressed.

[0004] Partitioned tables are a common data sharding technique in databases. Compared to database sharding and table partitioning, they allow customers to simplify applications using standard database usage patterns, rather than relying on specific SQL (Structured Query Language) or driver-driven usage methods. Summary of the Invention

[0005] This application proposes to provide a data processing method, apparatus, and storage medium.

[0006] Therefore, in one embodiment of this application, a data processing method is provided. The method includes:

[0007] In another embodiment of this application, a data processing method is provided. The method includes:

[0008] Determine the database tables to be partitioned and their primary keys;

[0009] Determine a first target column from the database table that is distinct from the primary key;

[0010] If there is a mapping relationship between the primary key and the partitioning function with the first target column as the partitioning key, then the database table is partitioned according to the partitioning function with the first target column as the partitioning key to obtain multiple partitions; wherein the primary key of the partition is consistent with the primary key of the database table.

[0011] In another embodiment of this application, a data processing method is provided. The method includes:

[0012] A second target column, distinct from the partition key of the database table, is determined from the database table; the database table corresponds to multiple partitions; the multiple partitions are obtained by partitioning the database table according to a partition function based on the partition key;

[0013] If there is a mapping relationship between the second target column and the partition function with respect to the partition key, then determine the mapping relationship between the second target column and the partition function with respect to the partition key;

[0014] Based on the second database operation request related to the database table, determine the query conditions of the second database operation request for the second target column of the database table;

[0015] Based on the query conditions and the mapping relationship between the second target column and the partition function with respect to the partition key, the second target partition to be accessed by the second data operation request is determined from the plurality of partitions.

[0016] In another embodiment of this application, an electronic device is provided. The electronic device includes: a memory and a processor, wherein,

[0017] The memory is used to store programs;

[0018] The processor, coupled to the memory, is used to execute the program stored in the memory to implement the data processing method described above.

[0019] In another embodiment of this application, a computer-readable storage medium storing a computer program is provided, which, when executed by a computer, can implement the data processing method described in any of the above claims.

[0020] In the technical solution provided by this application, a first target column, distinct from the primary key, is searched in the database table. If a mapping relationship exists between the primary key and a partitioning function using the first target column as the partitioning key, the first target column is determined as the partitioning key. Subsequently, the database table is partitioned according to the partitioning function using the first target column as the partitioning key. Since a mapping relationship exists between the primary key and the partitioning function regarding the partitioning key, the same primary key value corresponds to the same partitioning function value; the same partitioning function value necessarily corresponds to the same partition. Therefore, database table records with the same primary key value will be assigned to the same partition. If two database table records with the same primary key value appear repeatedly, according to this solution, these two records with the same primary key value will be assigned to the same partition. Within the same partition, due to the uniqueness of the primary key, it is impossible to repeatedly insert database table records with the same primary key value. This ensures the global uniqueness of the primary key. By adopting the technical solution provided by this application, the global uniqueness of the primary key can be guaranteed while eliminating the restriction in the prior art that the primary key must include a partitioning key, providing users with greater convenience. Attached Figure Description

[0021] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0022] Figure 1 A schematic flowchart illustrating a data processing method provided in an embodiment of this application;

[0023] Figure 2 Example of a data processing method provided in an embodiment of this application Figure 1 ;

[0024] Figure 3 Example of a data processing method provided in an embodiment of this application Figure 2 ;

[0025] Figure 4 Example of a data processing method provided in an embodiment of this application Figure 3 ;

[0026] Figure 5 A schematic flowchart illustrating a data processing method provided in an embodiment of this application;

[0027] Figure 6 This is a structural block diagram of an electronic device provided in an embodiment of this application. Detailed Implementation

[0028] Currently, the biggest problems with using partitioned tables are the following two points:

[0029] First, the definition of a partitioned table differs from that of a single table, with the primary key being that it must include the partition key to ensure the global uniqueness of the partitioned data.

[0030] This restriction forces users to use only primary keys, such as auto-incrementing primary keys like ID (Identity Document), as partition keys. This minimizes changes to the primary key, thus avoiding impact on secondary indexes. Note: Each leaf node in a secondary index must contain the primary key.

[0031] This shows that if a user wants to use a column other than the primary key of the single table, such as a time column, as the partition key, they need to add a partition key to the primary key of the single table and redefine the primary key.

[0032] Second, due to the existence of partition keys, which are different from primary keys, users' structured query statements (SQL) must be modified to include the partition key in order to improve performance. This results in the inability to smoothly switch users' access objects from single tables to partitioned tables.

[0033] To address the first problem mentioned above, this application proposes the following: utilizing the association between partition keys and primary keys, or user applications, to ensure the uniqueness of primary key data entering a partition, decoupling the inclusion relationship between primary keys and partition keys, maintaining the primary key definition of a single table, and reducing the space occupied by primary keys and secondary indexes, as well as improving query efficiency.

[0034] To address the second issue mentioned above, the relationship between the partition key and the query column (which can be a primary key, a secondary index column, or a regular column) is used to decouple the inclusion relationship between the user's SQL and the partition key. This solves the problem that the user's SQL must include the partition key to optimize the efficiency of querying, inserting, updating, and deleting data. In this way, users do not need to change their applications.

[0035] To enable those skilled in the art to better understand the present application, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are merely some embodiments of the present application, and not all embodiments. All other embodiments obtained by those skilled in the art based on the embodiments of the present application without creative effort are within the scope of protection of the present application.

[0036] Furthermore, some processes described in the specification, claims, and accompanying drawings of this application include multiple operations that appear in a specific order. These operations may be performed out of order or in parallel. Operation numbers such as 101, 102, etc., are merely used to distinguish different operations and do not represent any execution order. Additionally, these processes may include more or fewer operations, and these operations may be performed sequentially or in parallel. It should be noted that the terms "first," "second," etc., used herein are used to distinguish different messages, devices, modules, etc., and do not represent a sequential order, nor do they limit "first" and "second" to different types.

[0037] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties. Furthermore, the collection, use and processing of the relevant data must comply with the relevant laws, regulations and standards of the relevant countries and regions, and corresponding operation portals are provided for users to choose to authorize or refuse.

[0038] Figure 1 A flowchart illustrating a data processing method according to an embodiment of this application is shown. The execution entity of this method can be a client or a server. The client can be hardware with embedded programs integrated on a terminal, application software installed on the terminal, or utility software embedded in the terminal's operating system, etc., and this embodiment does not limit its scope. The terminal can be any terminal device, including mobile phones and tablets. The server can be a conventional server, a cloud server, or a virtual server, etc., and this embodiment does not specifically limit its scope. Figure 1 As shown, the method includes:

[0039] 101. Determine the database tables to be partitioned and their primary keys.

[0040] 102. Determine a first target column from the database table that is distinct from the primary key.

[0041] 103. If there is a mapping relationship between the primary key and the partitioning function with the first target column as the partitioning key, then the database table is partitioned according to the partitioning function with the first target column as the partitioning key to obtain multiple partitions.

[0042] The primary key of the partition is consistent with the primary key of the database table.

[0043] In the above 101, the database table to be partitioned can be specified by the user according to actual needs. Alternatively, it can be automatically filtered from multiple database tables according to a certain filtering strategy. This application embodiment does not specifically limit this.

[0044] In practical applications, when the number of records in a database table exceeds a preset threshold, the database table can be designated as a database table to be partitioned.

[0045] A primary key generally refers to a primary key. A primary key is a combination of one or more fields (or columns) in a database table, and its value is used to uniquely identify a record in the database table.

[0046] In point 102 above, the first target column differs from the primary key in that it is not included in the primary key. In other words, the first target column is not any column within the primary key.

[0047] The first target column mentioned above can be specified by the user according to actual needs, or it can be automatically determined according to a certain strategy. This application embodiment does not specifically limit this.

[0048] In the above 103, the partitioning function can be specified by the user according to actual needs, and this application embodiment does not specifically limit it.

[0049] The mapping relationship between the primary key and the partition function with the first target column as the partition key can be a one-to-one or many-to-one mapping relationship. A many-to-one mapping relationship means that multiple different primary key values ​​correspond to the same partition function value. The mapping relationship between the primary key and the partition function with the first target column as the partition key can be expressed mathematically as: f(primary key) = g(partition key), where g(partition key) is the partition function, and f() is the mapping relationship between the primary key and the partition function with the first target column as the partition key.

[0050] The following example illustrates the mapping relationship between the primary key and the partitioning function with the first target column as the partitioning key:

[0051] Assume the database table includes an ID number column and a region column. Note that specific digits in the ID number generally indicate the user's region, such as a license plate number. The ID number column is the primary key. If the partitioning function is based on region, then there is a mapping relationship between the ID number column and the partitioning function that uses the region column as the partition key.

[0052] Assume the database table includes a student ID column and a class column. Note that a specific digit in the student ID usually indicates the student's class. The student ID column is the primary key. If the partitioning function is based on class, then there is a mapping relationship between the student ID column and the partitioning function that uses the class column as the partition key.

[0053] Suppose a database table includes a record number column and a record creation time column. The record number column can be an auto-incrementing ID (Identity Document) column. Both the record number and record creation time columns are monotonically increasing, meaning that a record created later will always have a greater record number than a record created earlier, and the creation time of a record created later will always be later than the creation time of a record created earlier. Note: The creation times of any two records are different. If the partitioning function is based on a time interval, then there is also a mapping relationship between the record number column and the partitioning function that uses the record creation time column as the partition key.

[0054] If there is a mapping relationship between the primary key and the partitioning function with the first target column as the partitioning key, then the first target column is used as the target partitioning key, and the database table is partitioned according to the partitioning function of the target partitioning key to obtain multiple partitions.

[0055] In practical applications, database table records with the same partitioning function value will be assigned to the same partition. It can be considered that one partitioning function value corresponds to one partition, and different partitioning function values ​​correspond to different partitions. That is, multiple partitioning function values ​​correspond one-to-one with multiple partitions. This correspondence can be established and saved in advance, and subsequently, database table records can be assigned to the corresponding partitions based on this correspondence. This correspondence can be stored in memory.

[0056] In this embodiment, the primary key of each partition in the multiple partitions remains consistent with the primary key of the database table and has not changed.

[0057] In the technical solution provided by this application, a first target column, distinct from the primary key, is searched in the database table. If a mapping relationship exists between the primary key and a partitioning function using the first target column as the partitioning key, the first target column is determined as the partitioning key. Subsequently, the database table is partitioned according to the partitioning function using the first target column as the partitioning key. Since a mapping relationship exists between the primary key and the partitioning function regarding the partitioning key, the same primary key value corresponds to the same partitioning function value; the same partitioning function value necessarily corresponds to the same partition. Therefore, database table records with the same primary key value will be assigned to the same partition. If two database table records with the same primary key value appear repeatedly, according to this solution, these two records with the same primary key value will be assigned to the same partition. Within the same partition, due to the uniqueness of the primary key, it is impossible to repeatedly insert database table records with the same primary key value. This ensures the global uniqueness of the primary key. By adopting the technical solution provided by this application, the global uniqueness of the primary key can be guaranteed while eliminating the restriction in the prior art that the primary key must include a partitioning key, providing users with greater convenience.

[0058] To avoid full table scans, existing technologies propose partition pruning schemes. Partition pruning refers to the optimizer's ability to automatically extract the partitions to be scanned based on the partition key query conditions carried in the FROM or WHERE clauses of the SQL statement for partitioned tables or partitioned indexes. This avoids full table scans, reduces the number of data blocks scanned, and improves performance. This partition pruning scheme requires that the SQL statement must carry a partition key query condition to achieve partition pruning. In existing technologies, in SQL statements based on primary keys, the partition key query condition can be determined because it carries a primary key query condition, and the primary key contains the partition key. However, when the primary key does not contain a partition key, an additional partition key query condition needs to be added to the SQL statement. In other words, users need to adaptively modify their application logic to include partition key query conditions in their SQL statements.

[0059] To implement partition pruning without altering the user's application logic, this application proposes the following method: based on the mapping relationship between the primary key and the partition function with the first target column as the partition key, perform partition pruning operations for database operation requests. Specifically, the above method may further include:

[0060] 104. Determine the mapping relationship between the primary key and the partition function with the first target column as the partition key.

[0061] 105. Based on the first database operation request related to the database table, determine the primary key query conditions of the database table for which the first database operation request is made.

[0062] 106. Based on the primary key query conditions and the mapping relationship between the primary key and the partition function with the first target column as the partition key, determine the first target partition that the first data operation request needs to access from the plurality of partitions.

[0063] In the above 104, in practical applications, if there is a mapping relationship between the primary key and the partition function with the first target column as the partition key, the mapping relationship between the primary key and the partition function with the first target column as the partition key can be determined and the mapping relationship can be saved.

[0064] In one instance of 105 above, the first database operation request for the database table carries a primary key query condition.

[0065] In another instance, the first database operation request is a join query request regarding the database table and other database tables. The phrase "determining the primary key query condition of the database table based on the first database operation request regarding the database table" in step 105 above may include:

[0066] 1051a. Based on the query conditions for the other database tables carried in the connection query request, query the other database tables to determine the primary key query conditions for the database tables in the first database operation request.

[0067] Based on the query conditions for the other database tables carried in the connection query request, the other database tables are queried to obtain a first query result; based on the first query result, the primary key query conditions for the database tables in the first database operation request are determined.

[0068] In another example, the first database operation request includes: a main query and a related subquery nested within the main query; the phrase "determining the primary key query condition of the first database operation request for the database table based on the first database operation request related to the database table" in step 105 above may include:

[0069] 1051b. Based on the main query, execute the corresponding query to determine the primary key query conditions of the subquery for the database table.

[0070] 1052b. The primary key query condition of the subquery for the database table is determined as the primary key query condition of the first database operation request for the database table.

[0071] Specifically, based on the main query conditions carried in the main query, the target database table is queried to obtain the second query result; based on the second query result, the primary key query condition of the subquery is determined. The target database table can be the aforementioned database table or other database tables.

[0072] In step 106 above, the first target partition to be accessed by the first data operation request is determined from the plurality of partitions based on the primary key query conditions and the mapping relationship between the primary key and the partition function with the first target column as the partition key.

[0073] The primary key query condition will carry a primary key value and / or a range of primary key values. According to the above mapping relationship, the primary key value carried in the primary key query condition is mapped to the first target partitioning function value; according to the above mapping relationship, the range of primary key values ​​carried in the primary key query condition is mapped to at least one second target partitioning function value.

[0074] Based on the first target partition function value and / or at least one second target partition function value, determine the first target partition that the first data operation request needs to access from a plurality of partitions. The number of first target partitions can be one or more.

[0075] If the request contains primary key query conditions, pruning can be performed before the query is executed; this is called static pruning.

[0076] If the request does not include a primary key query condition, the query must be executed first before pruning can be performed; this is called dynamic pruning.

[0077] In one instance, after determining the first target partition, the above method may further include:

[0078] 107a. Based on the first target partition, rewrite the first data operation request to add the query conditions corresponding to the partition key to the first data operation request, so as to obtain the rewritten first data operation request.

[0079] 108a. Perform the corresponding data operation according to the rewritten first data operation request.

[0080] In this embodiment, a rewritten first data operation request containing the query conditions corresponding to the partition key is obtained by rewriting the first data operation request. Data operations on the database table are then performed based on the rewritten first data operation request.

[0081] In another instance, after the first target partition has been determined, the above method may further include:

[0082] 107b. After determining the first target partition, modify the partition pruning read bitmap to set the position of the number corresponding to the first target partition in the partition pruning read bitmap to a first value and set the position of the number corresponding to other partitions in the partition pruning read bitmap to a second value.

[0083] 108b. Based on the first data operation request and the modified partition pruning bitmap, perform the corresponding data operation.

[0084] The bitmap read by the above-mentioned partition pruning has multiple bits, and each bit corresponds one-to-one with multiple partitions. The first value and the second value are different. For example, the first value can be 1, and the second value can be 0. The specific values ​​of the first value and the second value can be set according to actual needs, and this application embodiment does not impose any limitations on this.

[0085] Optionally, the first database operation request carries a partition key query condition. The above method may further include:

[0086] 109. Based on the partition key query conditions, determine the third target partition that the first data operation request needs to access from the plurality of partitions.

[0087] 110. If there is no intersection between the first target partition and the third target partition, then an empty result set is returned for the first data operation request.

[0088] In this embodiment, the first database operation request carries a partition key query condition.

[0089] Based on the partition key lookup criteria, the third target partition required by the first data operation request is determined from the plurality of partitions. If there is no intersection between the first target partition and the third target partition, then no QR code scanning is required, and an empty result set can be returned for the first data operation request.

[0090] In this embodiment, the final query results can be returned without scanning a specific partition.

[0091] In practical applications, it is necessary to determine whether a mapping relationship exists between the primary key and the partition function with the first target column as the partition key. In one feasible solution, the above method may further include:

[0092] 111. Determine whether the primary key and the first target column are increasing columns.

[0093] 112. If both the primary key and the first target column are incrementing columns, it is determined that there is a mapping relationship between the primary key and the partition function with the first target column as the partition key.

[0094] In practical applications, both the record number column and the record creation time column are incrementing columns. The record number column is typically an auto-incrementing column.

[0095] Once it is determined that both the primary key and the first target column are incrementing columns, then it can be determined that there is a mapping relationship between the primary key and the partition function with the first target column as the partition key.

[0096] In this embodiment, the determination process is simple and effective.

[0097] Furthermore, the partitioning function is a function that partitions according to the value range of the first target column; the input of the partitioning function is the column value of the first target column in the database table record; the output of the partitioning function is the partition identifier of the partition to which the database table record belongs. In this embodiment, the output of the partitioning function, i.e., the partitioning function value, is directly used as the partition identifier.

[0098] The step 104 above, "determining the mapping relationship from the primary key to the partition function with the first target column as the partition key," can be achieved using the following steps:

[0099] 1041. Determine the range of primary key values ​​for each of the multiple partitions.

[0100] 1042. Establish the correspondence between the primary key value range of each partition and its partition identifier in the plurality of partitions, so as to serve as the mapping relationship between the primary key and the partition function with the first target column as the partition key.

[0101] In this way, the partition identifier corresponding to each primary key value can be determined based on this mapping relationship.

[0102] like Figure 2 As shown, database table t1 corresponds to multiple partitions 201, and its corresponding mapping relationship is the correspondence between the value range of the primary key and the partition identifier in table 200. Figure 2 In the corresponding relation table 200, each element in the first row corresponds to the range of values ​​for the primary key, which is the range between the current element's value and the next element's value. The SQL statement corresponding to the first database operation request is: `Select * from t1 where id = 45000`. Here, `id = 45000` is the primary key query condition. Based on the primary key query condition in the SQL statement, the corresponding relation table 200 is queried first, and then the target partition p2112 is determined.

[0103] like Figure 3 As shown, the SQL statement corresponding to the join query request is: `select * FROM t1, t2 where t1.id = t2.t1_id and t2.name = "BOB"`. Here, `t1` is the aforementioned database table, and `t2` is another database table mentioned above. `t2.name = "BOB"` is the query condition for the other database table `t2`. Based on this query condition, `t1_id = 70000` is found; therefore, the primary key query condition for database table `t1` is also `t1_id = 70000`. Based on the primary key query condition, the corresponding relation table `200` is queried, thereby determining the target partition `p2201`.

[0104] like Figure 2 As shown, the SQL statement is: `Select * from t1 where id = 45000 and created_at >= '20220201' and created_at >= '20220228'`. Based on `id = 45000`, the corresponding relation table 200 is queried to determine the target partition p2112; based on `created_at >= '20220201' and created_at >= '20220228'`, the target partition p2202 is determined. These two target partitions have no intersection, therefore an empty result set can be returned directly, without needing to scan the partitions.

[0105] like Figure 4 As shown, the query statement in this solution can also be an aggregate query statement, such as: Select count(*) from t1 having id>43000 and id<55000. Based on the primary key query condition in this aggregate query statement, the corresponding relation table 200 is retrieved, thereby determining the target partition.

[0106] Optionally, the above method may further include:

[0107] 113. Determine the second target column from the database table.

[0108] The second target column is different from the first target column, which serves as the partition key.

[0109] 114. If there is a mapping relationship between the second target column and the partition function with the first target column as the partition key, then determine the mapping relationship between the second target column and the partition function with the first target column as the partition key.

[0110] 115. Based on the second database operation request related to the database table, determine the query conditions of the second database operation request for the second target column of the database table.

[0111] 116. Based on the query conditions and the mapping relationship between the second target column and the partition function with the first target column as the partition key, determine the second target partition that the second data operation request needs to access from the plurality of partitions.

[0112] In section 113 above, the second target column is not a partition key. The second target column can also be specified by the user according to their actual needs.

[0113] The specific implementation of each of the above steps will be described in detail in the following embodiments.

[0114] By leveraging the association between partition keys and primary keys mentioned in this solution, or by ensuring the uniqueness of application data entering the primary key data of a partition, the inclusion relationship between primary keys and partition keys can be decoupled. This maintains the primary key definition of a single table, reducing the space usage of primary keys and secondary indexes and improving query efficiency. By utilizing the association between partition keys and query columns (e.g., the primary key or secondary target column mentioned above), the inclusion relationship between user SQL and partition keys is decoupled, resolving the issue that user SQL must include the partition key to optimize the efficiency of queries, insertions, updates, and deletions. Users do not need to change their application logic.

[0115] This solution can be applied in scenarios where the relational database management system MySQL uses partitioned tables, allowing users to smoothly transition from a single table to a partitioned table and improve performance without modifying the application logic.

[0116] Figure 5 This illustration shows a flowchart of a data processing method according to another embodiment of this application. The executing entity of this method can be a client or a server. The client can be hardware with embedded programs integrated on a terminal, application software installed on the terminal, or utility software embedded in the terminal's operating system, etc., and this embodiment does not limit its scope. The terminal can be any terminal device, including mobile phones, tablets, etc. The server can be a conventional server, a cloud server, or a virtual server, etc., and this embodiment does not specifically limit its scope. Figure 5 As shown, the method includes:

[0117] 501. Determine a second target column from the database table that is distinct from the partition key of the database table; the database table corresponds to multiple partitions.

[0118] The multiple partitions are obtained by partitioning the database table according to a partitioning function based on the partition key.

[0119] 502. If there is a mapping relationship between the second target column and the partition function with respect to the partition key, then determine the mapping relationship between the second target column and the partition function with respect to the partition key.

[0120] 503. Based on the second database operation request related to the database table, determine the query conditions of the second database operation request for the second target column of the database table.

[0121] 504. Based on the query conditions and the mapping relationship between the second target column and the partition function related to the partition key, determine the second target partition that the second data operation request needs to access from the plurality of partitions.

[0122] In the 501 above, the second target column is different from the partition key of the database table.

[0123] The aforementioned second target column can be specified by the user according to actual needs, or it can be automatically determined according to a certain strategy. This application embodiment does not specifically limit this.

[0124] The above partitioning function can be specified by the user according to actual needs, and this application embodiment does not impose specific limitations on it.

[0125] The mapping relationship between the second target column and the partition function with the first target column as the partition key can be a one-to-one or many-to-one mapping relationship. A many-to-one mapping relationship means that multiple different second target column values ​​correspond to the same partition function value. The mapping relationship between the second target column and the partition function with the first target column as the partition key can be expressed mathematically as: f(second target column) = g(partition key), where g(partition key) is the partition function, and f() is the mapping relationship between the second target column and the partition function with the first target column as the partition key.

[0126] In step 502 above, if there is a mapping relationship between the second target column and the partition function with the first target column as the partition key, the mapping relationship between the second target column and the partition function with the first target column as the partition key can be determined and the mapping relationship can be saved.

[0127] In one instance of the above 503, the second database operation request for the database table carries query conditions for the second target column.

[0128] In another instance, the first database operation request is a join query request regarding the database table and other database tables. The phrase "determining the query conditions of the second database operation request for the second target column of the database table based on the second database operation request regarding the database table" in step 503 above may include:

[0129] 5031a. Based on the query conditions for the other database tables carried in the connection query request, query the other database tables to determine the query conditions for the second target column of the database tables in the second database operation request.

[0130] Based on the query conditions for the other database tables carried in the connection query request, the other database tables are queried to obtain a third query result; based on the third query result, the query conditions for the second database operation request for the relevant second target column of the database table are determined.

[0131] In another example, the second database operation request includes: a main query and a related subquery nested within the main query; the phrase "determining the query conditions of the second database operation request for the second target column of the database table based on the second database operation request for the database table" in step 503 above may include:

[0132] 5031b. Based on the main query, execute the corresponding query to determine the query conditions of the subquery for the relevant second target column of the database table.

[0133] 5032b. Determine the query conditions of the subquery for the relevant second target column of the database table as the query conditions of the second database operation request for the relevant second target column of the database table.

[0134] Based on the main query conditions carried in the main query, the target database table is queried to obtain the fourth query result; based on the fourth query result, the query conditions of the subquery for the relevant second target column of the database table are determined. The target database table can be the aforementioned database table, or it can be other database tables; this application embodiment does not impose specific limitations on this.

[0135] In step 504 above, based on the query conditions for the second target column and the mapping relationship between the second target column and the partition function with the first target column as the partition key, the second target partition to be accessed by the second data operation request is determined from the plurality of partitions.

[0136] The query conditions for the second target column will include the value of the second target column and / or the range of values ​​for the second target column. According to the above mapping relationship, the value of the second target column carried in the query conditions for the second target column is mapped to the value of the third target partitioning function; according to the above mapping relationship, the range of values ​​for the second target column carried in the query conditions for the second target column is mapped to at least one value of the fourth target partitioning function.

[0137] Based on the value of the third target partition function and / or at least one fourth target partition function, determine the second target partition that the second data operation request needs to access from a plurality of partitions. The number of second target partitions can be one or more.

[0138] In one instance, after the second target partition is determined, the above method may further include:

[0139] Based on the second target partition, the second data operation request is rewritten to add the query conditions corresponding to the partition key to the second data operation request, resulting in the rewritten second data operation request.

[0140] Based on the rewritten second data operation request, perform the corresponding data operation.

[0141] In this embodiment, by rewriting the second data operation request, a rewritten second data operation request containing the query conditions corresponding to the partition key is obtained. Based on the rewritten second data operation request, data operations on the database table are then performed.

[0142] In another instance, after the second target partition has been determined, the above method may further include:

[0143] After determining the second target partition, modify the partition pruning read bitmap to set the position of the number corresponding to the second target partition in the partition pruning read bitmap to a first value and set the position of the number corresponding to other partitions in the partition pruning read bitmap to a second value.

[0144] Based on the second data operation request and the modified partition pruning read bitmap, perform the corresponding data operation.

[0145] The bitmap read by the above-mentioned partition pruning has multiple bits, and each bit corresponds one-to-one with multiple partitions. The first value and the second value are different. For example, the first value can be 1, and the second value can be 0. The specific values ​​of the first value and the second value can be set according to actual needs, and this application embodiment does not impose any limitations on this.

[0146] Optionally, the second database operation request carries a partition key query condition. The above method may further include:

[0147] Based on the partition key query conditions, the fourth target partition that the second data operation request needs to access is determined from the plurality of partitions.

[0148] If there is no intersection between the second target partition and the fourth target partition, then an empty result set is returned for the second data operation request.

[0149] In this embodiment, the second database operation request carries a partition key query condition.

[0150] Based on the partition key lookup criteria, the third target partition required by the second data operation request is determined from the plurality of partitions. If there is no intersection between the second target partition and the third target partition, then no barcode scanning is required, and an empty result set can be returned for the second data operation request.

[0151] In this embodiment, the final query results can be returned without scanning a specific partition.

[0152] In practical applications, it is necessary to determine whether a mapping relationship exists between the second target column and the partition function that uses the first target column as the partition key. In one feasible solution, the above method may further include:

[0153] Determine whether the second target column and the first target column are increasing columns.

[0154] If both the second target column and the first target column are increasing columns, then it is determined that there is a mapping relationship between the second target column and the partition function with the first target column as the partition key.

[0155] Once it is determined that both the second target column and the first target column are increasing columns, then it can be determined that there is a mapping relationship between the second target column and the partition function with the first target column as the partition key.

[0156] In this embodiment, the determination process is simple and effective.

[0157] Furthermore, the partitioning function is a function that partitions according to the value range of the first target column; the input of the partitioning function is the column value of the first target column in the database table record; the output of the partitioning function is the partition identifier of the partition to which the database table record belongs. In this embodiment, the output of the partitioning function, i.e., the partitioning function value, is directly used as the partition identifier.

[0158] Optionally, the "determining the mapping relationship from the second target column to the partition function with the first target column as the partition key" in step 502 above can be achieved by the following steps:

[0159] 5021. Determine the numerical range of the second target column of each of the multiple partitions.

[0160] 5022. Establish the correspondence between the numerical range of the second target column of each of the multiple partitions and its partition identifier, so as to serve as the mapping relationship between the second target column and the partition function with the first target column as the partition key.

[0161] In this way, based on this mapping relationship, the partition identifier corresponding to the value of each second target column can be determined.

[0162] In one instance, the above method may further include:

[0163] In response to a database operation request for the database table, after performing the corresponding data operation on the database table, the correspondence between the value range of the primary key of each partition and its partition identifier is updated according to the current value range of the primary key of each partition in the multiple partitions, and / or, the correspondence between the value range of the second target column of each partition and its partition identifier is updated according to the current value range of the second target column of each partition in the multiple partitions.

[0164] The aforementioned database operation request may include both the first database operation request and the second database operation request. The first and second database operation requests can be any of the CRUD (Create, Read, Update, Delete) operations.

[0165] Updating the correspondence essentially means updating the numerical range within the correspondence, thus enabling adaptive modification of the correspondence.

[0166] It should be noted that any steps in the method provided in this application that are not described in detail can be found in the corresponding content of the above embodiments, and will not be repeated here. Furthermore, the method provided in this application may include other parts or all of the steps in the above embodiments in addition to the steps described above; for details, please refer to the corresponding content of the above embodiments, and will not be repeated here.

[0167] Figure 6 A schematic diagram of the structure of an electronic device according to an embodiment of this application is shown. Figure 6 As shown, the electronic device includes a memory 1101 and a processor 1102. The memory 1101 can be configured to store various other data to support operation on the electronic device. Examples of such data include instructions for any application or method used to operate on the electronic device. The memory 1101 can be implemented by any type of volatile or non-volatile storage device or a combination thereof, such as Static Random Access Memory (SRAM), Electrically Erasable Programmable Read-Only Memory (EEPROM), Electrically Programmable Read Only Memory (EPROM), Programmable Read Only Memory (PROM), Read Only Memory (ROM), magnetic storage, flash memory, magnetic disk, or optical disk.

[0168] The memory 1101 is used to store programs;

[0169] The processor 1102 is coupled to the memory 1101 and is used to execute the program stored in the memory 1101 to implement the methods provided in the above-described method embodiments.

[0170] Furthermore, such as Figure 6 As shown, the electronic device also includes: communication component 1103, display 1104, power supply component 1105, audio component 1106, and other components. Figure 6The diagram only shows some components and does not mean that the electronic device includes only these components. Figure 6 The components shown.

[0171] Accordingly, embodiments of this application also provide a computer-readable storage medium storing a computer program, which, when executed by a computer, can implement the steps or functions of the methods provided in the above-described method embodiments.

[0172] The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs. Those skilled in the art can understand and implement this without any creative effort.

[0173] Through the above description of the embodiments, those skilled in the art can clearly understand that each embodiment can be implemented by means of software plus necessary general-purpose hardware platforms, and of course, it can also be implemented by hardware. Based on this understanding, the above technical solutions, in essence or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as ROM (Read Only Memory) / RAM (Random Access Memory), magnetic disk, optical disk, etc., including several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute the methods described in the various embodiments or some parts of the embodiments.

[0174] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of this application, and are not intended to limit them. Although this application has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of this application.

Claims

1. A data processing method, characterized in that, include: Determine the database tables to be partitioned and their primary keys; Determine a first target column from the database table that is distinct from the primary key; If there is a mapping relationship between the primary key and the partition function with the first target column as the partition key, then the database table is partitioned according to the partition function with the first target column as the partition key to obtain multiple partitions; wherein, the mapping relationship between the primary key and the partition function with the first target column as the partition key includes a one-to-one or many-to-one mapping relationship, and the many-to-one mapping relationship means that multiple different primary key values ​​correspond to the same partition function value, and the primary key of the partition is different from the primary key of the first target column.

2. The method according to claim 1, characterized in that, Also includes: Determine the mapping relationship between the primary key and the partition function with the first target column as the partition key; Based on the first database operation request related to the database table, determine the primary key query conditions of the database table for the first database operation request; Based on the primary key query conditions and the mapping relationship between the primary key and the partition function with the first target column as the partition key, the first target partition to be accessed by the first data operation request is determined from the plurality of partitions.

3. The method according to claim 2, characterized in that, The first database operation request is a join query request for the database table and other database tables; Based on the first database operation request concerning the database table, the primary key query conditions of the first database operation request for the database table are determined, including: Based on the query conditions for the other database tables carried in the connection query request, query the other database tables to determine the primary key query conditions for the database tables in the first database operation request.

4. The method according to claim 2, characterized in that, The first database operation request includes: a main query and a related subquery nested within the main query; Based on the first database operation request concerning the database table, the primary key query conditions of the first database operation request for the database table are determined, including: Based on the main query, execute the corresponding query to determine the primary key query conditions of the subquery for the database table; The primary key query condition of the subquery for the database table is determined as the primary key query condition of the first database operation request for the database table.

5. The method according to any one of claims 2 to 4, characterized in that, Also includes: Determine whether the primary key and the first target column are incrementing columns; If both the primary key and the first target column are incrementing columns, it is determined that there is a mapping relationship between the primary key and the partitioning function with the first target column as the partitioning key.

6. The method according to claim 5, characterized in that, The partitioning function is a function that partitions according to the value range of the first target column; the input of the partitioning function is the column value of the first target column in the database table record of the database table; The output of the partitioning function is the partition identifier of the partition to which the database table record belongs; Determining the mapping relationship between the primary key and the partition function with the first target column as the partition key includes: Determine the range of primary key values ​​for each of the multiple partitions; Establish a correspondence between the primary key value range of each partition and its partition identifier in the plurality of partitions, so as to serve as a mapping relationship from the primary key to the partition function with the first target column as the partition key.

7. The method according to any one of claims 1 to 4, characterized in that, Also includes: Determine the second target column from the database table; The second target column is different from the first target column, which serves as the partition key; If there is a mapping relationship between the second target column and the partition function with the first target column as the partition key, then determine the mapping relationship between the second target column and the partition function with the first target column as the partition key; Based on the second database operation request related to the database table, determine the query conditions of the second database operation request for the second target column of the database table; Based on the query conditions and the mapping relationship between the second target column and the partitioning function with the first target column as the partitioning key, the second target partition to be accessed by the second data operation request is determined from the plurality of partitions.

8. The method according to any one of claims 2 to 4, characterized in that, The first database operation request carries a partition key query condition; the method further includes: Based on the partition key query conditions, determine the third target partition that the first data operation request needs to access from the plurality of partitions; If there is no intersection between the first target partition and the third target partition, then an empty result set is returned for the first data operation request.

9. A data processing method, characterized in that, include: Identify a second target column from the database table that is distinct from the partition key of the database table; the database table has multiple partitions. The multiple partitions are obtained by partitioning the database table according to a partitioning function based on the partition key; If there is a mapping relationship between the second target column and the partition function with respect to the partition key, then the mapping relationship between the second target column and the partition function with respect to the partition key is determined. The mapping relationship between the second target column and the partition function with respect to the partition key includes a one-to-one or many-to-one mapping relationship. The many-to-one mapping relationship means that multiple different second target column values ​​correspond to the same partition function value. Based on the second database operation request related to the database table, determine the query conditions of the second database operation request for the second target column of the database table; Based on the query conditions and the mapping relationship between the second target column and the partition function with respect to the partition key, the second target partition to be accessed by the second data operation request is determined from the plurality of partitions.

10. An electronic device, characterized in that, include: Memory and processor, among which, The memory is used to store programs; The processor, coupled to the memory, is used to execute the program stored in the memory to implement the data processing method according to any one of claims 1 to 9.

11. A computer-readable storage medium storing a computer program, characterized in that, When the computer program is executed by a computer, it can implement the data processing method according to any one of claims 1 to 9.

Images