Query optimization method and device based on OceanBase database data fragmentation and related products

Abstract

The invention provides a query optimization method and device based on OceanBase database data fragmentation and related products, and the method comprises the following steps: determining one or more columns as service keys based on a plurality of service tables, the service keys being used for connecting service data in the service tables; based on the service key, setting one or more columns as fragmentation keys, and combining the fragmentation keys with the service key so as to construct a fragmentation table in the OceanBase database; performing fragmentation processing on target data based on the fragmentation table to create a data fragmentation structure; based on the data fragmentation structure, constructing an extended SQL query statement carrying preset prompt optimization information SQL Hint; on the basis of the extended SQL query statement, a query optimizer of the OceanBase database selects a preset execution plan to query the service table so as to obtain a target result set, and the query performance is improved in combination with the parallel processing capacity of the database.

Description

Technical Field

[0001] The present invention belongs to the field of databases, and specifically relates to a query optimization method, device and related products based on OceanBase database data sharding. Background Art

[0002] With the continuous development of database application systems, the explosive growth of data volume has put forward higher and higher requirements on the storage, query and processing capabilities of database systems. Especially for large-scale distributed database systems such as OceanBase, it aims to meet the challenges of massive data through efficient distributed storage and parallel processing mechanisms. However, when faced with complex queries, multi-table associations and large data processing, OceanBase database query optimization methods often fail to achieve ideal performance.

[0003] Specifically, when facing multi-table queries, the OceanBase database system often needs to rely on the query optimizer to automatically select the optimal query execution plan. However, the query optimizer is not perfect enough, and its decisions are based on statistical information and heuristic rules, which are not accurate enough under large data volumes and complex business logic, resulting in the selected execution plan being suboptimal, which in turn affects query efficiency. In addition, for distributed databases, data distribution and sharding strategies are also key factors affecting query performance.

[0004] In the OceanBase distributed database system, although a basic data sharding mechanism is provided, how to determine the business key and sharding key according to business characteristics and query requirements, customize and optimize the sharding strategy, and how to use the sharded table to optimize the query execution plan are still urgent problems to be solved. Traditional SQL query statements often cannot directly guide the database system to select a specific execution path or index, resulting in the query optimizer being unable to fully utilize the advantages of the sharded table in some cases, resulting in low query efficiency. Summary of the invention

[0005] In view of this, embodiments of the present invention provide a query optimization method, device and related products based on OceanBase database data sharding to at least partially solve the above problems.

[0006] According to a first aspect of an embodiment of the present invention, a query optimization method based on OceanBase database data sharding is provided, the method comprising the following steps: Based on multiple business tables, determine one or more columns as business keys, where the business keys are used to connect business data in the business tables; Based on the business key, one or more columns are set as sharding keys, and the sharding key is combined with the business key to construct a sharding table in the OceanBase database; Performing sharding processing on the target data based on the sharding table to create a data sharding structure; Based on the data sharding structure, an extended SQL query statement carrying preset prompt optimization information SQL Hint is constructed; Based on the extended SQL query statement, the query optimizer of the OceanBase database selects a preset execution plan to query the business table to obtain a target result set.

[0007] Optionally, determining one or more columns as business keys based on multiple business tables includes: Analyze the business table to determine a core business table, wherein the core business table contains key data in the business logic; Analyze the column definition of the core business table to select a column that can uniquely identify each record in the table as a candidate column for the business key; The consistency of the candidate columns between different core business tables is analyzed to identify key candidate columns that can be associated with data in the core business table as business keys.

[0008] Optionally, the step of setting one or more columns as sharding keys based on the business key, and combining the sharding key with the business key to construct a sharding table in the OceanBase database includes: The sharding key is combined with the business key to construct a sharding table in the OceanBase database, so that the data in the sharding table can be sharded according to the sharding key and can be associated with the core business table through the business key; Create indexes for the shard key and business key fields respectively; A sharding rule of the sharding table is defined to shard the target data based on the sharding rule.

[0009] Optionally, the sharding rule defining the sharding table is specifically implemented as follows: Determine a multi-level sharding system, the multi-level sharding system includes one or a combination of a primary sharding type and a secondary sharding type, the primary sharding type includes list sharding and hash sharding, and the secondary sharding type includes range sharding; The list sharding includes using one or a combination of a region code, an organization, and an account as a sharding key, which is constructed in the sharding table to shard the target data by a business attribute dimension; The hash sharding adopts the hash sharding method to realize the hash distribution of data; The range sharding includes using time as a sharding key, which is constructed in a sharding table, so as to perform time dimension sharding on the target data.

[0010] The range sharding also includes using a self-increasing positive integer as a sharding key to shard the target data according to the value range.

[0011] Optionally, performing sharding processing on the target data based on the sharding table to create a data sharding structure includes: Slice the target data according to the first-level partition type to obtain a first-level data slicing structure; construct a first mapping relationship based on the first-level partition type, wherein the first mapping relationship is used to match and index the first-level partition type with the corresponding first-level slicing data; The first-level shard data is sharded according to the second-level shard type to obtain a second-level data shard structure; a second mapping relationship is constructed based on the second-level partition type, and the second mapping relationship is used to match and index the second-level partition type with the corresponding second-level shard data.

[0012] Optionally, the step of constructing an extended SQL query statement carrying preset hint optimization information SQL Hint based on the data sharding structure includes: Determine the query target and scope based on business needs, and build standard SQL query statements based on preset query condition parameters; The standard SQL query statement includes a plurality of business tables connected based on the shard table, and includes one or a combination of selection, sorting, and grouping operations; A grammatical structure of preset hint optimization information SQL Hint is embedded in the standard SQL query statement to obtain an extended SQL query statement; the hint optimization information SQL Hint is used to intervene in one or a combination of a query execution path, an index usage indication, and a table connection strategy of a query optimizer to select an optimal execution path for the SQL query statement.

[0013] Optionally, based on the extended SQL query statement, the query optimizer of the OceanBase database selects a preset execution plan to query the business table to obtain a target result set, including: The OceanBase database parses the extended SQL query statement to obtain the standard SQL statement and its corresponding prompt optimization information SQL Hint; The query optimizer receives the standard SQL statement and the hint optimization information SQL Hint; The query optimizer selects a preset execution plan according to the prompt optimization information SQL Hint; Based on the execution plan, execute the standard SQL statement to query the business table; Get the target result set and return it to the requester.

[0014] Optionally, the OceanBase database parses the extended SQL query statement to obtain the standard SQL statement and its corresponding hint optimization information SQL Hint, including: The OceanBase database parses the extended SQL query statement to obtain key information, index information, query clause information and SQL Hint of the standard SQL query statement; The key information includes a shard key; the query clause information includes SELECT, WHERE, and JOIN clauses to extract the name of the business table to be connected, the target column, and the connection condition of the business table to be connected; The executing the standard SQL statement based on the execution plan to query the business table includes: Use SQL Hint to intervene in the query optimizer to select the NESTED-LOOP JOIN connection algorithm; Specify the mandatory use of a specified index in the SQL statement to reduce the amount of data scanned and the memory usage through the index; Use the shard table as the external table to drive the search for the target business table; Based on the shard key, the position of each shard in the database is obtained, and the shard where the target data is located is located accordingly; The query optimizer retrieves the shards in parallel to execute the standard SQL query statement to query the business table.

[0015] In a second aspect of the implementation manner of the present application, a data sharding and query optimization device based on an OceanBase database is provided, which is applied to the data sharding and query optimization method based on an OceanBase database as described in any one of the items, and includes: A business key identification module, used to determine one or more columns as business keys based on multiple business tables, wherein the business keys are used to connect business data in the business tables; A sharding key setting module, used to set one or more columns as sharding keys based on the business key, and the sharding key is combined with the business key to construct a sharding table in the OceanBase database; A sharding processing module, used to perform sharding processing on the target data based on the sharding table to create a data sharding structure; A query statement building module, used to build an extended SQL query statement carrying preset prompt optimization information SQL Hint based on the data sharding structure; The query execution module is used to select a preset execution plan based on the extended SQL query statement and the query optimizer of the OceanBase database to query the business table to obtain a target result set.

[0016] In a third aspect of the implementation of the present application, an electronic device is provided, including: a memory and a processor, wherein the memory stores a computer executable program, and the processor is used to execute the computer executable program to implement any of the data sharding and query optimization methods based on the OceanBase database described in the present application.

[0017] In a fourth aspect of the implementation manner of the present application, a storage medium is provided, on which a computer executable program is stored. When the computer executable program is executed, it implements the data sharding and query optimization method based on the OceanBase database as described in any one of the present application.

[0018] The present application provides a query optimization method, device and related products based on OceanBase database data sharding, the method comprising the following steps: based on multiple business tables, determining one or more columns as business keys, the business keys being used to connect business data in the business tables; based on the business keys, setting one or more columns as sharding keys, the sharding keys being combined with the business keys to construct a sharding table in the OceanBase database; based on the sharding table, performing sharding processing on target data to create a data sharding structure; based on the data sharding structure, constructing an extended SQL query statement carrying preset hint optimization information SQL Hint; based on the extended SQL query statement, the query optimizer of the OceanBase database selects a preset execution plan to query the business table to obtain a target result set. By combining business logic and query requirements, reasonably setting sharding keys and business keys, building efficient sharding tables, and customizing and optimizing query statements, the query optimizer's decision-making process is intervened by embedding SQL Hints to guide the query optimizer to select the optimal execution plan. This query optimization method improves query performance. In addition, data sharding combined with the parallel processing capabilities of the OceanBase database can support higher concurrent query requests and meet performance requirements in large-scale business scenarios. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0020] Figure 1 This is a flow chart of a query optimization method based on OceanBase database data sharding in an embodiment of the present application; Figure 2 A schematic diagram of a query optimization device based on OceanBase database data sharding in an embodiment of the present application; Figure 3 This is a schematic diagram of the structure of an electronic device according to an embodiment of the present application; Figure 4 This is a schematic diagram of the hardware structure of the electronic device according to an embodiment of the present application. DETAILED DESCRIPTION

[0021] In order to enable those skilled in the art to better understand the solution of the present application, the following will be combined with the drawings in the embodiments of the present application to clearly and completely describe the technical solutions 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 should fall within the scope of protection of this application.

[0022] It should be noted that, in the absence of conflict, the embodiments and features in the embodiments of the present application can be combined with each other. The present application will be described in detail below with reference to the accompanying drawings and in combination with the embodiments.

[0023] Figure 1 Flowchart of a query optimization method based on OceanBase database data sharding in an embodiment of the present application; Figure 1 As shown, the present application provides a query optimization method based on data sharding of an OceanBase database, the method comprising the following steps: based on multiple business tables, determining one or more columns as business keys, the business keys being used to connect business data in the business tables; based on the business keys, setting one or more columns as sharding keys, the sharding keys being combined with the business keys to construct a sharding table in the OceanBase database; performing sharding processing on target data based on the sharding table to create a data sharding structure; based on the data sharding structure, constructing an extended SQL query statement carrying preset hint optimization information SQL Hint; based on the extended SQL query statement, the query optimizer of the OceanBase database selects a preset execution plan to query the business table to obtain a target result set.

[0024] In this embodiment, by sharding the data according to the sharding key, the related business data can be physically closer, reducing the need for cross-node queries, thereby significantly improving the query response speed. SQLHint is used to guide the query optimizer to select the execution plan that best suits the current data sharding structure, avoiding the optimizer's blind selection among multiple possible plans, and further improving query efficiency. In addition, for queries involving multiple business tables and complex connection conditions, reasonable data sharding design can significantly reduce the amount of data that needs to be processed during the query process and improve the execution efficiency of complex queries. In addition, data sharding combined with the parallel processing capabilities of the OceanBase database can support higher concurrent query requests and meet performance requirements in large-scale business scenarios.

[0025] Optionally, determining one or more columns as business keys based on multiple business tables includes: Analyze the business table to determine a core business table, wherein the core business table contains key data in the business logic; Analyze the column definition of the core business table to select a column that can uniquely identify each record in the table as a candidate column for the business key; The consistency of the candidate columns between different core business tables is analyzed to identify key candidate columns that can be associated with data in the core business table as business keys.

[0026] In this embodiment, by analyzing the core business table and selecting a column that can uniquely identify each record as a business key, the uniqueness and accuracy of the data can be ensured, which helps to avoid data duplication and redundancy and improve data quality. As a bridge between data, the business key can ensure the consistency and accuracy of data exchange between different data tables, systems or databases, and reduce errors and conflicts caused by inconsistent data. In addition, by analyzing the consistency of candidate columns between different core business tables, key columns that can associate data in different tables can be identified, which helps to achieve cross-table data integration and associated queries, and improve the efficiency of data analysis and business insights. Business keys enable big data platforms such as data warehouses and data lakes to more effectively integrate data from different sources and support complex data analysis and reporting requirements. Furthermore, using business keys as indexes or primary keys can significantly improve the performance of database query and update operations, because indexes can speed up data retrieval, while primary key constraints ensure the uniqueness and integrity of data. In environments such as data warehouses, sharding and indexing strategies based on business keys can further optimize query performance. At the same time, by clarifying the business key, the business logic and data model can be defined more clearly, which helps team members communicate and collaborate better and reduce misunderstandings and errors. The process of selecting business keys is also a process of in-depth understanding of data models and business requirements, which helps to discover and solve potential data problems. Finally, in complex business scenarios, such as multi-tenant systems, distributed databases, etc., the selection and definition of business keys is particularly important. Through reasonable business key design, more complex query, transaction processing and data isolation requirements can be supported. Business keys can also serve as the basis for advanced functions such as data change tracking and version control to support more complex business logic and data management requirements.

[0027] Optionally, the step of setting one or more columns as sharding keys based on the business key, and combining the sharding key with the business key to construct a sharding table in the OceanBase database includes: The sharding key is combined with the business key to construct a sharding table in the OceanBase database, so that the data in the sharding table can be sharded according to the sharding key and can be associated with the core business table through the business key; Create indexes for the shard key and business key fields respectively; A sharding rule of the sharding table is defined to shard the target data based on the sharding rule.

[0028] In this embodiment, by creating indexes for the sharding key and business key fields respectively, the query speed based on these keys can be greatly accelerated. The index enables the database to quickly locate the physical location of the data, reducing the need for full table scans. The design of the sharding table allows query operations to be limited to specific shards rather than the entire database, which significantly reduces the amount of data required for query processing, thereby improving query efficiency. In addition, sharding technology allows the database to expand the capacity and processing power of the entire database by adding more shards while keeping the size of a single shard controllable, which is crucial for applications that process large-scale data sets. Through reasonable sharding rules, it is possible to ensure that data is evenly distributed on multiple shards, thereby avoiding excessive load on a single node and improving the stability and reliability of the entire system. In a distributed database system, the failure of a single shard will not affect the data and services of other shards, thereby improving the fault tolerance and availability of the system. In addition, the design of the sharding table also simplifies the process of data recovery. When specific data needs to be recovered, operations can be performed only on the affected shards, rather than the entire database. Furthermore, the combination of business key and shard key allows flexible selection of shard key according to business needs to adapt to different data access modes and business scenarios. The retention of business key allows the shard table to be associated with the core business table, which is convenient for data integration and analysis. As the business develops and the amount of data grows, the sharding rules can be dynamically adjusted or new shards can be added according to actual needs to meet higher performance requirements and capacity requirements. Finally, through a reasonable sharding strategy, hardware resources such as CPU, memory and storage can be used more effectively to avoid waste of resources. Dynamically adjusting resource allocation according to actual needs can reduce the cost of data storage and processing.

[0029] Optionally, the sharding rule defining the sharding table is specifically implemented as follows: Determine a multi-level sharding system based on the sharding key, the multi-level sharding system includes one or a combination of a primary sharding type and a secondary sharding type, the primary sharding type includes a list sharding and a hash sharding, and the secondary sharding type includes a range sharding; The list sharding includes using one or a combination of a region code, an organization, and an account as a sharding key, which is constructed in the sharding table to shard the target data by a business attribute dimension; The hash sharding adopts the hash sharding method to realize the hash distribution of data; The range sharding includes using time as a sharding key, which is constructed in a sharding table, so as to perform time dimension sharding on the target data.

[0030] In this embodiment, through a multi-level sharding system, especially using range sharding (such as using time as the sharding key), data of a specific time period can be stored in a specific shard. For example, when querying the order data of a certain month, the database only needs to search within the corresponding time range shard, avoiding full table scanning, greatly improving the query efficiency, because it reduces the amount of unnecessary data retrieval and utilizes the local characteristics of the data. In addition, for list sharding, the business attribute dimension sharding is performed using the regional code, organization or account as the sharding key. When querying data related to a specific region, organization or account, the corresponding shard can be quickly located, which also reduces the data range that needs to be traversed during the query. Secondly, for hash hash distribution to balance the query load, hash sharding uses hash sharding to achieve hash hash distribution of data. This method can evenly distribute data among the shards and avoid the problem of data skew. When performing large-scale data queries, the query load of each shard is relatively balanced, and there will be no situation where the performance of a certain shard is seriously degraded due to excessive data volume, thereby improving the query performance as a whole. At the same time, different sharding types (list sharding, hash sharding, range sharding) shard the data from different dimensions (business attributes, hash hashing, time), so that the data is logically grouped clearly. For example, for business data, it can be reasonably divided according to business attributes (such as different regions, organizations), data generation time, etc., so that administrators can operate on specific shards when performing data backup, recovery, migration, etc., instead of facing the entire huge data set, which improves management efficiency. Especially in heterogeneous data migration, the overall data structure of the original table does not need to be changed, and the original value does not need to be modified and revised, which greatly reduces the difficulty of migration. Furthermore, the design of the multi-level sharding system makes it easy to increase the number of shards or adjust the sharding rules when the amount of data continues to grow. For example, as the business develops, new regions or organizations are added, it is easy to adapt to new business needs by modifying the rules of list sharding (adding new region codes or organizations as sharding keys) without causing a subversive impact on the entire data storage and query architecture, ensuring the scalability of the system. Finally, there may be multiple query requirements in business scenarios, such as querying order data by time range, querying customer information by region, querying business statistics by organization, etc. By providing multiple sharding types (list sharding and hash sharding for the first-level sharding type, and range sharding for the second-level sharding type), these different query scenarios can be flexibly met. For example, for time-sensitive queries (such as querying transaction records for a certain day), range sharding can quickly locate relevant data; for queries closely related to business attributes (such as querying customer activity in a certain area), list sharding can play a role.

[0031] To this end, in the process of determining a multi-level sharding system based on the sharding key, we must first select a suitable sharding key based on business requirements and data characteristics. For range sharding, time is selected as the sharding key because in many business scenarios, time is an important dimension, and data query, statistics and other operations are often performed according to the time range. For example, in the business, query the order volume within a certain time period, query the transaction flow within a certain time period, etc. For list sharding, area codes, organizations, and accounts are selected as sharding keys because they can well divide data from the business attribute dimension. For example, in an enterprise management system, employee data is divided by organization, and sales data is divided by region (area code), so as to facilitate independent management and query of business data with different attributes. Hash sharding uses hash functions to process data to avoid data skew and make the data volume of each shard relatively balanced. After determining the sharding key, decide whether to use a primary sharding type or a combination of a primary sharding type and a secondary sharding type based on business requirements. If the business data is relatively simple, only a primary sharding type (such as only using list sharding or hash sharding) may be required to meet query and management requirements. However, if the business data is more complex and needs to be divided from multiple dimensions, a combination of primary and secondary sharding types can be used.

[0032] When one or a combination of area code, organization, and account is used as the shard key, the corresponding area code, organization, or account information needs to be accurately extracted and set as the shard key value when entering or importing data. According to the set shard key value, records with the same shard key value are divided into the same shard. For example, all customer records with the area code "010" (Beijing area) will be divided into the same shard to facilitate subsequent query and management of customers in the Beijing area. In actual implementation, this data sharding operation can be completed through database query statements or specific sharding algorithms. For example, use the WHERE clause in the SQL statement to filter out qualified records based on the shard key value, and then store them in the corresponding shard table.

[0033] When using time as the sharding key, ensure that the time information is accurately recorded and set as the sharding key value during data entry, import, or data migration. For example, in an order management system, for each order record, the order time (such as "2020-11-27 10:00:00") is recorded and stored as the sharding key value in the sharding table. According to the set time range, the records falling within the corresponding time range are divided into the same shard. For example, if a month is set as a time range, all order records in November 2020 will be divided into the same shard. In actual implementation, this data sharding operation can be completed through database query statements or specific sharding algorithms. For example, use the WHERE clause in the SQL statement to filter out qualified records according to the time range, and then store them in the corresponding sharding table. At the same time, in order to facilitate the query of data in different time ranges, it may be necessary to create an index, such as creating an index for the time sharding key in the sharding table, so that the corresponding shard can be quickly located when querying.

[0034] Optionally, performing sharding processing on the target data based on the sharding table to create a data sharding structure includes: Slice the target data according to the first-level partition type to obtain a first-level data slicing structure; construct a first mapping relationship based on the first-level partition type, wherein the first mapping relationship is used to match and index the first-level partition type with the corresponding first-level slicing data; The first-level shard data is sharded according to the second-level shard type to obtain a second-level data shard structure; a second mapping relationship is constructed based on the second-level partition type, and the second mapping relationship is used to match and index the second-level partition type with the corresponding second-level shard data.

[0035] In this embodiment, by first sharding according to the first-level partition type to obtain a first-level data sharding structure and constructing a first mapping relationship, and then further sharding according to the second-level sharding type to obtain a second-level data sharding structure and construct a second mapping relationship, a hierarchical indexing mechanism is formed. When executing a query operation, this hierarchical index can quickly locate the specific sharding level where the target data is located, reducing the amount of data that needs to be traversed. For example, when querying data under specific conditions, the first-level sharding range that may contain the target data can be quickly screened out through the first mapping relationship, and then the second mapping relationship is used to accurately locate the second-level sharding where the target data is located within the first-level sharding range, greatly shortening the query time and improving the query efficiency. In addition, the sharding structures of different levels and the corresponding mapping relationships make the data more orderly and have clear logical relationships in storage. This makes it possible to accurately locate the sharding where the target data is located according to specific query conditions (which may involve factors related to the first-level sharding type and factors related to the second-level sharding type) during the query process, avoiding aimless searches of the entire data set, thereby improving the accuracy and speed of the query. Furthermore, the creation of the data sharding structure divides the target data in an orderly manner according to the primary partition type and the secondary partition type, forming a clear hierarchical data organization structure. This clear structure makes it easier to understand the distribution of data. When performing operations such as data backup, recovery, and migration, shards at different levels can be processed more targetedly, rather than facing chaotic and disordered overall data, which improves the convenience and operability of data management. At the same time, sharding processing divides the target data into different sharding structures, so that the data is no longer a large centralized block in storage, but is scattered in each suitable shard. In this way, when storing and retrieving data, only the relevant shards can be operated according to actual needs, avoiding unnecessary operations such as full table scans, thereby reducing the waste of system storage resources (such as disk space) and computing resources (such as CPU, memory, etc.), and improving the utilization efficiency of system resources. Finally, the hierarchical sharding structure makes parallel processing possible. In queries or other data processing operations, work can be carried out simultaneously on different primary shards or secondary shards to achieve parallel processing. For example, when querying data that meets specific conditions, if there are multiple first-level shards that may contain the target data, then these first-level shards can be retrieved at the same time, and then the second-level shards within the found first-level shards can be processed in parallel. This can make full use of the system's multi-core processor and other resources to further improve the system's processing speed and performance.

[0036] To this end, when sharding and constructing the first mapping relationship according to the first-level partition type, it is necessary to determine the parameters related to the first-level partition type. First, it is necessary to clarify the first-level partition type used, such as the list sharding or hash sharding mentioned above. For list sharding, it is necessary to determine the format, value range and other parameters of the information such as the region code, organization or account that is specifically used as the sharding key. According to the determined first-level partition type and related parameters, the target data is sharded. After completing the first-level sharding operation, the first mapping relationship is constructed. This mapping relationship mainly matches and indexes the first-level partition type (such as the region code type of the list sharding, the hash type of the hash sharding, etc.) with the corresponding first-level shard data. It can be constructed in a variety of ways, such as creating an index table, which records the identifier of the first-level partition type (such as "region code sharding", "hash sharding", etc.) and the identifier of each corresponding first-level shard (such as shard number, shard name, etc.), and connects it with the storage location of the actual first-level shard data through pointers or other association methods, so that the corresponding first-level shard data can be quickly found according to the first-level partition type when querying. When performing sharding and building the second mapping relationship based on the secondary partition type, it is necessary to determine the parameters related to the secondary partition type. It is also necessary to first clarify the secondary partition type used, such as range sharding. For range sharding, it is necessary to determine the format, value range, time interval and other parameters of the information such as time used as the sharding key. For example, if time is used as the sharding key, it is necessary to know how to express time (such as year-month-day hour: minute: second format) and how to define different time ranges (such as dividing the time range by day, month, year, etc.). When performing the secondary sharding operation, the first-level sharding data that has completed the first-level sharding is sharded according to the determined secondary partition type and related parameters. Taking range sharding as an example, if time is used as the sharding key, traverse each record in the first-level sharding data set, extract the time value in the record, and then divide the record into the corresponding shard according to the time value. For example, all records in November 2020 will be divided into the same shard (assuming that the sharding is based on the month as the time range). After completing the secondary sharding operation, build the second mapping relationship. This mapping relationship mainly matches and indexes the secondary partition type (such as the time type of the range shard, etc.) with the corresponding secondary shard data. Similar to the way of constructing the first mapping relationship, an index table can be created to record the identifier of the secondary partition type (such as "time shard", etc.) and the identifier of each corresponding secondary shard (such as shard number, shard name, etc.), and connect it to the storage location of the actual secondary shard data through pointers or other association methods, so that the corresponding secondary shard data can be quickly found according to the secondary partition type when querying.During the entire sharding process, attention should be paid to the consistency and integrity of the data to ensure that data will not be lost or errors will occur during operations such as sharding and building mapping relationships. At the same time, the sharding type and parameters should be reasonably selected and adjusted according to business needs and the actual situation of the database to achieve the best technical effect.

[0037] Optionally, the step of constructing an extended SQL query statement carrying preset hint optimization information SQL Hint based on the data sharding structure includes: Determine the query target and scope based on business needs, and build standard SQL query statements based on preset query condition parameters; The standard SQL query statement includes a plurality of business tables connected based on the shard table, and includes one or a combination of selection, sorting, and grouping operations; A grammatical structure of preset hint optimization information SQL Hint is embedded in the standard SQL query statement to obtain an extended SQL query statement; the hint optimization information SQL Hint is used to intervene in one or a combination of a query execution path, an index usage indication, and a table connection strategy of a query optimizer to select an optimal execution path for the SQL query statement.

[0038] In this embodiment, by analyzing the business needs and determining the target and scope of the query, it is ensured that the query statement can accurately meet the business needs, avoiding unnecessary data processing and resource waste. The constructed standard SQL query statement can include multiple business tables connected based on the sharding table, and can include one or a combination of selection, sorting, and grouping operations. The query statement can handle complex business logic, support multi-table connection and advanced data processing. The standard SQL query statement supports a variety of operation combinations, improves the flexibility and scalability of the query statement, and can adapt to various complex query needs. In addition, the preset prompt optimization information SQL Hint is embedded in the standard SQL query statement, which can intervene in the query execution path, index usage instructions, table connection strategy, etc. of the query optimizer to ensure the selection of the optimal execution path. Through SQL Hint, the query optimizer's selection can be directly intervened, which can avoid the suboptimal selection that the optimizer may produce, and ensure that the query performance reaches the best state. Furthermore, in the data sharding environment, the data is dispersed and stored on multiple physical nodes. Through SQL Hint, it can be optimized according to the characteristics of data distribution, such as selecting the most suitable node for scanning, or optimizing data transmission across nodes. This helps to better utilize the parallel processing capabilities brought by sharding while reducing network latency and data redundancy.

[0039] Optionally, based on the extended SQL query statement, the query optimizer of the OceanBase database selects a preset execution plan to query the business table to obtain a target result set, including: The OceanBase database parses the extended SQL query statement to obtain the standard SQL statement and its corresponding prompt optimization information SQL Hint; The query optimizer receives the standard SQL statement and the hint optimization information SQL Hint; The query optimizer selects a preset execution plan according to the prompt optimization information SQL Hint; Based on the execution plan, execute the standard SQL statement to query the business table; Get the target result set and return it to the requester.

[0040] In this embodiment, through SQL Hint, the developer can directly guide the query optimizer to select the optimal execution plan, avoiding the optimizer from making complex cost estimates and comparisons among multiple possible execution plans, thereby reducing the compilation time and execution time of the query. This performance improvement is particularly obvious when dealing with complex queries and large-scale data sets. Secondly, SQL Hint makes the execution path and result set of the query more predictable. Without SQL Hint, the decision of the query optimizer may be affected by various factors, such as the accuracy of database statistics, system load, etc., while SQL Hint can clearly specify the execution plan, reduce these uncertain factors, and make the query results more stable and reliable. In addition, in some specific business scenarios, there may be queries that are difficult for the query optimizer to automatically optimize. For example, when the data is unevenly distributed, the index selection is inappropriate, or the query conditions are complex, SQL Hint can optimize for these specific scenarios to ensure that the query performance is optimal. Furthermore, by precisely controlling the execution plan, the OceanBase database can use system resources more efficiently. For example, SQL Hint can specify the use of specific indexes to reduce the amount of data scanning and memory usage; or specify the use of parallel processing to speed up query execution. These optimization measures help reduce the load on the server, improve the response speed of other queries, and make full use of the computing power of multi-core processors. Then, for database administrators, SQL Hint provides a powerful tool to manage query performance. By presetting and executing SQL Hint, administrators can easily optimize queries without having to deeply understand the internal workings of the query optimizer or perform complex performance tuning. This reduces the complexity and cost of database management. At the same time, as the business develops, query requirements may change. SQL Hint allows developers to flexibly adjust the query execution plan according to changes in business requirements, which helps ensure that database queries can always meet business needs while remaining efficient and reliable. Finally, by improving query performance and predictability, the OceanBase database can return query results to the requester faster, which helps improve user experience and enables users to obtain the required information and make decisions faster.

[0041] Optionally, the OceanBase database parses the extended SQL query statement to obtain the standard SQL statement and its corresponding hint optimization information SQL Hint, including: The OceanBase database parses the extended SQL query statement to obtain key information, index information, query clause information and SQL Hint of the standard SQL query statement; The key information includes a shard key; the query clause information includes SELECT, WHERE, and JOIN clauses to extract the name of the business table to be connected, the target column, and the connection condition of the business table to be connected; The executing the standard SQL statement based on the execution plan to query the business table includes: Use SQL Hint to intervene in the query optimizer to select the NESTED-LOOP JOIN connection algorithm; Specify the mandatory use of a specified index in the SQL statement to reduce the amount of data scanned and the memory usage through the index; Use the shard table as the external table to drive the search for the target business table; Based on the shard key, the position of each shard in the database is obtained, and the shard where the target data is located is located accordingly; The query optimizer retrieves the shards in parallel to execute the standard SQL query statement to query the business table.

[0042] In this embodiment, by parsing the extended SQL query statement, the OceanBase database can accurately obtain the key information, index information, query clause information and SQL Hint of the standard SQL statement. This information provides rich context for the query optimizer, enabling it to accurately control the query execution path and strategy according to the developer's intention. SQLHint allows developers to specify the use of specific connection algorithms, such as NESTED-LOOP JOIN. This ability is particularly important for processing large-scale data and complex connection operations. By selecting the connection algorithm that best suits the current data and query characteristics, the query execution time and resource consumption can be significantly reduced. In addition, specifying the mandatory use of specified indexes in the SQL statement can ensure that the query optimizer gives priority to these indexes when generating an execution plan. This is crucial to reducing the amount of data scanned, reducing memory usage and improving query performance, especially when the business key has a unique index, using these indexes can greatly speed up the query speed. Furthermore, using the shard table as a foreign table to drive the search for the target business table is an efficient query strategy. This strategy allows the database to first determine the shard range to be queried, and then perform query operations only on these shards, which can greatly reduce the amount of data to be scanned and improve the efficiency and accuracy of the query. At the same time, OceanBase database supports parallel retrieval of shards, which means that multiple query operations can be executed on different shards at the same time. This parallel processing capability can significantly improve query throughput, especially when processing large-scale data sets. Through parallel retrieval, the database can return query results faster and improve user experience. Furthermore, by precisely controlling query execution paths and strategies, OceanBase database can use system resources more efficiently. For example, by reducing unnecessary data scanning and memory usage, the server load can be reduced and the response speed of other queries can be improved. In addition, the parallel retrieval function can also make full use of the computing power of multi-core processors to further improve resource utilization. Finally, using SQL Hint to optimize query execution not only improves query performance, but also enhances the maintainability and scalability of the database. Developers can easily adjust SQL Hint to adapt to different business needs and data changes without making large-scale modifications to the database architecture or query logic. In addition, as the amount of data grows and the complexity of queries increases, developers can further optimize query performance by adding new SQL Hints.

[0043] Optionally, the query optimizer searching the shards in parallel includes: Taking advantage of the distributed database features of the OceanBase database, multiple worker threads are started to scan different shards where the target data is located, thereby improving the query speed.

[0044] In this embodiment, the OceanBase database is built on a distributed system and can start multiple worker threads to process query tasks in parallel. When the data that the query needs to access is distributed on multiple shards, these worker threads can work separately and scan different shards where the target data is located at the same time. This parallel scanning method significantly reduces the total query time and improves the query speed. In a distributed system, parallel processing can more effectively utilize the CPU, memory and IO resources in the cluster. Through parallel retrieval, OceanBase can make full use of the hardware resources of the cluster and improve the overall performance of the system. In addition, for cross-shard query requests, the optimizer of the OceanBase database will automatically generate a distributed execution plan based on the physical distribution of the query and data. Through the shard pruning technology, unnecessary data access can be reduced, further improving query performance. Furthermore, the execution plan of the OceanBase database is divided into multiple DFOs (Data Flow Objects) in the vertical direction. Each DFO can be divided into tasks with a specified degree of parallelism. The optimizer will intelligently schedule these tasks to execute on appropriate nodes based on the complexity of the query and the distribution of the data to achieve optimal query performance. Furthermore, since the OceanBase database adopts a distributed architecture, its system performance can be linearly expanded as the number of nodes increases. When the amount of data increases or the query load increases, the system's processing power and query speed can be improved by adding nodes. Finally, the OceanBase database will balance the data shards to multiple nodes according to a certain balancing strategy to avoid single-point overload and performance bottlenecks. Through parallel retrieval, load balancing can be further achieved to improve the stability and reliability of the system. The OceanBase database uses a multi-copy mechanism to ensure high data availability. During the parallel retrieval process, even if a node fails, other nodes can still continue to execute query tasks, thereby ensuring the system's fault tolerance and data consistency.

[0045] Optionally, creating indexes for the sharding key and business key fields may include: creating a primary key index for the sharding key; creating a unique index for the business key field; or creating other appropriate indexes.

[0046] In this embodiment, creating a primary key index for the primary sharding key can ensure the uniqueness of the field, and the database system can use this index to quickly locate the physical location of the data. The primary key index is one of the most commonly used index types in the database because it provides a unique identifier for each row of data in the table, thereby accelerating the retrieval speed of the data. Creating a unique index for the business key field can ensure that the value of the field is unique in the entire table, which helps to prevent data duplication. At the same time, the unique index also supports fast query operations because it allows the database system to quickly find records with specific business key values ​​through the index. In addition, the existence of the primary key index enforces the uniqueness of the sharding key, which helps to maintain the integrity of the data. In a distributed database system, the uniqueness of the sharding key is crucial to ensuring data consistency and avoiding data conflicts. The unique index of the business key helps to maintain the integrity of the data, which ensures that the key data in the business logic will not cause errors or inconsistencies due to duplication. In addition, the creation of an index can optimize the storage and access methods of data. The database system can use the index to reduce the amount of data that needs to be scanned, thereby speeding up the query speed. The index can also help the database system manage the physical storage of data more effectively and improve the access efficiency of data. Furthermore, when processing queries involving multiple tables and complex join conditions, primary key indexes and unique indexes can significantly improve query performance. These indexes enable the database system to locate relevant data rows more quickly and reduce unnecessary table scans and join operations. Finally, the creation of indexes is also very important for supporting the scalability of the system. As the amount of data grows, indexes can help the database system manage data more efficiently and support more concurrent queries and update operations. In a distributed database system, indexes can also help achieve horizontal expansion of data by adding more shards to expand the capacity and processing power of the entire system.

[0047] Optionally, in another embodiment of the present application, the sharding system adopts a hash sharding method, and the sharding key is set as a hash sharding key to shard the target data based on a hash function sharding rule; to this end, the steps are specifically included: Set the total number of preset shards of the shard table; Dividing the target data into a corresponding number of shards based on a preset total number of shards; Based on the hash function, the target data shard key is calculated to obtain its hash value; Establishing a mapping relationship between the hash value and the shards; The target data is allocated to the slice to which it should belong based on the mapping relationship.

[0048] In this embodiment, using a hash function as a sharding rule can ensure that data is evenly distributed between shards. Hash functions usually have good randomness and uniformity, and can map the input data key to a relatively evenly distributed hash value, and then distribute the data to different shards according to the hash value, so as to avoid the situation where some shards are overloaded while other shards are idle, thereby improving the overall performance and stability of the system. In addition, uniform data distribution means that query operations can be performed more efficiently. When data is evenly distributed on each shard, query operations can be performed on multiple shards in parallel, thereby speeding up the query speed. Due to the fast calculation characteristics of hash functions, queries based on hash sharding usually have higher efficiency. Furthermore, the preset total number of shards allows the database system to dynamically expand the number of shards as needed. With the increase in the amount of data and the increase in query requirements, the capacity and processing power of the entire system can be expanded by adding new shards. This scalability enables the database system to flexibly respond to different business needs and data growth trends. At the same time, the use of hash sharding keys and the preset total number of shards simplifies the sharding management process. The database system can automatically distribute and manage data based on the hash function and the preset total number of shards, reducing the need for manual intervention, which allows database administrators to focus more on other important database management tasks. Uniform data distribution and hash sharding rules help optimize the storage and access methods of data. The database system can organize the physical storage of data based on the hash value of the sharding key so that related data can be stored in adjacent locations as much as possible, thereby reducing disk I / O operations and improving data access speed. Finally, the hash sharding rule helps support high-concurrency data access operations. Since the data is evenly distributed on each shard, concurrent queries can be executed on multiple shards in parallel, thereby improving the system's concurrent processing capabilities, which is particularly important for application scenarios that need to process a large number of concurrent requests. By dividing the target data into a corresponding number of shards, the data can be physically more ordered and easier to manage. Each shard contains part of the data, which helps to reduce the size of a single data shard, thereby improving the efficiency of data operations. Moreover, by using the hash function to calculate the value of the target data shard key, a relatively uniform hash value distribution can be obtained. This distribution ensures that the data is evenly distributed among the shards, avoiding the situation where some shards have too much data and other shards have too little data, thereby improving the overall performance and load balancing capabilities of the system. In addition, the established mapping relationship between hash values ​​and shards allows the database system to quickly locate the shard where the data should be stored or retrieved. This fast positioning capability can significantly improve the efficiency of queries and data retrieval.

[0049] Optionally, the column definition of the core business table is analyzed to select a column that can uniquely identify each record in the table as a candidate column for the business key, wherein the candidate column includes but is not limited to one or a combination of a primary key and a unique index column in the core business table.

[0050] Optionally, set the number of shards for the shard table including: The number of shards of the shard table is set based on the resource occupancy rate and performance indicators of the database system. The sharding strategy is set through CPU performance, query request number per second (QPS) and single table data volume rules to control the amount of data stored in each shard and the total number of shards. The resource occupancy rate of the database system includes CPU, memory, and disk I / O resource occupancy rates.

[0051] In this embodiment, by considering CPU performance, memory usage and disk I / O resource occupancy rate, it can be ensured that the sharding strategy can evenly distribute the load to each node, avoid single point overload, thereby improving the overall performance and response speed of the system. The number of shards is set according to the number of query requests per second QPS, which can ensure that the system can efficiently process requests and reduce query delays in high-concurrency query scenarios. Secondly, the number of shards is set based on performance indicators and resource occupancy rate, so that the system can flexibly adjust the sharding strategy as the business grows and the amount of data increases, without the need for large-scale system reconstruction. When the processing capacity of a single node reaches a bottleneck, horizontal expansion can be achieved by increasing the number of shards to improve the processing capacity and storage capacity of the system. Furthermore, by accurately controlling the amount of data stored in each shard, it is possible to avoid the situation where some shards have too much data while other shards are idle, thereby optimizing resource utilization and reducing resource waste. A reasonable sharding strategy can reduce hardware costs while ensuring system performance, because there is no need to over-configure resources to cope with occasional peak loads. In addition, sharding strategies are usually combined with data redundancy and backup strategies. By dispersing data storage, the fault tolerance of the system can be improved. Even if a node or shard fails, it will not affect the normal operation of the entire system. In a sharded system, fault recovery is usually faster and simpler because the affected shards can be handled separately without restoring the entire database. Finally, through a reasonable sharding strategy, data management and maintenance work can be simplified. For example, tasks such as data backup, recovery, and migration can be completed more efficiently. Sharding can also help achieve data isolation. Different shards can store data from different businesses or different users, thereby improving data security and privacy.

[0052] Optionally, the sharding strategy is set based on CPU performance, query request per second (QPS), and single table data volume rules to control the amount of data stored in each shard and the total number of shards, including actually testing the query time under different sharding scales and selecting a combination of the number of shards and the amount of data per shard that minimizes the query time, specifically including: Calculate the total amount of data in the table, obtained through COUNT(*) query; By presetting the data volume of each shard, test the query time of the database on the preset shard data volume, and select the data volume with the shortest query time as the actual shard data volume; Calculate the total number of shards. The specific formula is: .

[0053] By presetting the amount of data for each shard, the query time of the test database on the preset shard data volume includes: Set the data volume of each shard to 2 million and test the query time of the database; Set the data volume of each shard to 1.5 million and test the query time of the database; Set the data volume of each shard to 1 million and test the query time of the database; Set the data volume of each shard to 500,000 and test the query time of the database; Set the data volume of each shard to 200,000 and test the query time of the database.

[0054] In this embodiment, first, COUNT (*) is used to query the total amount of data in the table, which is the basis for the formulation of the sharding strategy. The table data is divided into 2 million records per shard, and the query time is tested under this sharding scale. Similarly, the table data is divided into 1.5 million records per shard, and the query time is tested. The query time of 1 million records per shard is continued to be tested, and the query time of 500,000 records per shard is tested. Finally, the query time of 200,000 records per shard is tested. For each sharding scale, the query time is recorded, and the impact of different sharding scales on query performance is analyzed. Based on the test results, the number of shards with the shortest query time and the amount of data per piece are selected as the final sharding strategy. This embodiment only uses specific numerical values ​​for example comparison. In the actual process, program automatic control can be used to select the sharding strategy, and shards of other data amounts can be used for testing. By actually testing the query time under different sharding data amounts, the sharding scale that makes the query performance optimal can be accurately found, which helps to ensure that the database can maintain high efficiency and speed when processing query requests. Different sharding sizes consume different resources such as CPU, memory, and disk I / O of the database server. Through testing, you can choose a sharding size that can meet performance requirements and maximize resource utilization.

[0055] Optionally, the hint optimization information SQL Hint includes one or a combination of parallel and index.

[0056] In one implementation, a shard table tab_key(NUMnumber(30),COL_KEY char(xx)) is constructed; num is used as the shard key, the value of the field is a natural number from 1 to infinity, COL_KEY is the business key, and a unique index idx_col_key is created for it; then SQL join is used to intervene in the query optimizer through hints to select the optimal query path to improve query efficiency; the details are as follows: Here are two types of tables as examples: Card account table: tbl_1(col_cardno char(19),COL_KEY char(xx),col_2 int,col_3datetime,col_4,…), Deposit transaction: tbl_2(col_cardno char(19) ,COL_KEY char(xx),col_2 int,col_3datetime,col_4,…), The modified sql: select / * parallel(9) index(a idx_a_col_key) * / a.col_cardno, a.COL_KEY,b.col_2,c.col_3,d.col_4, case when a.col_3 = 1 then 'C' when a.col_3 = 2 then 'D' else 'E' end as col_5, … from tbl_1 a inner join tbl_key i on a.COL_KEY = i.COL_KEY inner join tbl_3 b on a.col_cardno = b.col_cardno inner join tbl_4 c on a.COL_KEY = c.COL_KEY left join tbl_5 d on a.COL_KEY = d.COL_KEY … where i.num>=1 and i.num<= 100000 In this embodiment, hint is used to intervene in the execution plan, and the specified index is used. The query optimizer will select NESTED-LOOP JOIN, and the shard table tbl_key is used as the outer table to drive the search for tbl_1, and the index idx_col_key is used. The scanned data is greatly reduced, and the memory usage is also reduced. Secondly, the shard key of the shard table is num, and using it for range query is very efficient for the optimizer. The integer step size makes the step size planning simple, and there is no need to select the shard key for each table according to the data distribution. Planning different types of step sizes is more efficient than the previous time (day, week, month, year) step size and symbol step size. Furthermore, parallelism is increased, and the characteristics of the distributed database are fully utilized to start multiple working threads to scan different shards of the shard table separately, which greatly improves the query speed.

[0057] Optionally, the OceanBase database is a distributed database that adopts the Oracle mode.

[0058] Figure 2Schematic diagram of a query optimization device based on OceanBase database data sharding in an embodiment of the present application, such as Figure 2 As shown, in another embodiment of the present application, a data sharding and query optimization device based on OceanBase database is provided, which includes: A business key identification module, used to determine one or more columns as business keys based on multiple business tables, wherein the business keys are used to connect business data in the business tables; A sharding key setting module, used to set one or more columns as sharding keys based on the business key, and the sharding key is combined with the business key to construct a sharding table in the OceanBase database; A sharding processing module, used to perform sharding processing on the target data based on the sharding table to create a data sharding structure; A query statement building module, used to build an extended SQL query statement carrying preset prompt optimization information SQL Hint based on the data sharding structure; The query execution module is used to select a preset execution plan based on the extended SQL query statement and the query optimizer of the OceanBase database to query the business table to obtain a target result set.

[0059] Figure 3 Schematic diagram of the electronic device structure of the present application embodiment; Figure 3 The electronic device provided in the present application includes: a memory and a processor, wherein the memory stores a computer executable program, and the processor is used to execute the computer executable program to implement any method described in the present application.

[0060] Figure 4 Schematic diagram of the hardware structure of the electronic device of the present application embodiment; Figure 4 The present application provides a storage medium on which a computer executable program is stored. When the computer executable program is executed, the method described in any one of the present application is implemented.

[0061] The hardware structure of the electronic device may include: a database server, a processor, a communication interface, a computer-readable medium and a communication bus; Wherein, the database server, the processor, the communication interface, and the computer-readable medium communicate with each other via a communication bus; Optionally, the communication interface may be an interface of a communication module, such as an interface of a GSM module; The processor may be specifically configured to run an executable program stored in the memory, thereby executing all or part of the processing steps of any of the above method embodiments.

[0062] The processor may be a general-purpose processor, including a central processing unit (CPU), a network processor (NP), etc.; it may also be a digital signal processor (DSP), an application-specific integrated circuit (ASIC), a field-programmable gate array (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components. The methods, steps and logic block diagrams disclosed in the embodiments of the present application may be implemented or executed. The general-purpose processor may be a microprocessor or the processor may also be any conventional processor, etc.

[0063] It should be pointed out that, according to the needs of implementation, the various components / steps described in the embodiments of the present application can be split into more components / steps, or two or more components / steps or partial operations of components / steps can be combined into new components / steps to achieve the purpose of the embodiments of the present application.

[0064] The above-mentioned method according to the embodiment of the present application can be implemented in hardware, firmware, or implemented as software or computer code that can be stored in a recording medium (such as a CD ROM, RAM, floppy disk, hard disk or magneto-optical disk), or implemented as a computer code originally stored in a remote recording medium or a non-temporary machine-readable medium downloaded through a network and to be stored in a local recording medium, so that the method described herein can be stored in such software processing on a recording medium using a general-purpose computer, a dedicated processor or programmable or dedicated hardware (such as an ASIC or FPGA). It can be understood that a computer, a processor, a microprocessor controller or programmable hardware includes a storage component (e.g., RAM, ROM, flash memory, etc.) that can store or receive software or computer code, and when the software or computer code is accessed and executed by a computer, a processor or hardware, the verification code generation method described herein is implemented. In addition, when a general-purpose computer accesses the code for implementing the verification code generation method shown here, the execution of the code converts the general-purpose computer into a dedicated computer for executing the verification code generation method shown here.

[0065] It should be noted that the same and similar parts between the various embodiments in this specification can be referred to each other, and each embodiment focuses on the differences from other embodiments. In particular, for the device and system embodiments, since they are basically similar to the method embodiments, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiments. The device and system embodiments described above are merely schematic, wherein the modules described as separate components may or may not be physically separated, and the components indicated as modules may or may not be physical modules, that is, they may be located in one place, or they may be distributed on multiple network modules. Some or all of the modules may be selected according to actual needs to achieve the purpose of the scheme of this embodiment. A person of ordinary skill in the art can understand and implement it without paying creative labor.

[0066] The above is only a specific implementation of the present application, but the protection scope of the present application is not limited thereto. Any changes or substitutions that can be easily thought of by a person skilled in the art within the technical scope disclosed in the present application should be included in the protection scope of the present application. Therefore, the protection scope of the present application should be based on the protection scope of the claims.

Claims

1. A query optimization method based on OceanBase database data sharding, characterized in that: The method comprises the following steps: Based on multiple business tables, determine one or more columns as business keys, where the business keys are used to connect business data in the business tables; Based on the business key, one or more columns are set as sharding keys, and the sharding key is combined with the business key to construct a sharding table in the OceanBase database; Performing sharding processing on the target data based on the sharding table to create a data sharding structure; Based on the data sharding structure, an extended SQL query statement carrying preset prompt optimization information SQL Hint is constructed; Based on the extended SQL query statement, the query optimizer of the OceanBase database selects a preset execution plan to query the business table to obtain a target result set.

2. The query optimization method based on OceanBase database data sharding according to claim 1 is characterized in that: The determining one or more columns as business keys based on the multiple business tables includes: Analyze the business table to determine a core business table, wherein the core business table contains key data in the business logic; Analyze the column definition of the core business table to select a column that can uniquely identify each record in the table as a candidate column for the business key; The consistency of the candidate columns between different core business tables is analyzed to identify key candidate columns that can be associated with data in the core business table as business keys.

3. The query optimization method based on OceanBase database data sharding according to claim 1 is characterized in that: The step of setting one or more columns as sharding keys based on the business key, and combining the sharding key with the business key to construct a sharding table in the OceanBase database includes: The sharding key is combined with the business key to construct a sharding table in the OceanBase database, so that the data in the sharding table can be sharded according to the sharding key and can be associated with the core business table through the business key; Create indexes for the shard key and business key fields respectively; A sharding rule of the sharding table is defined to shard the target data based on the sharding rule.

4. The query optimization method based on OceanBase database data sharding according to claim 1 is characterized in that: The sharding rules defining the sharding table are specifically implemented as follows: Determine a multi-level sharding system, the multi-level sharding system includes one or a combination of a primary sharding type and a secondary sharding type, the primary sharding type includes list sharding and hash sharding, and the secondary sharding type includes range sharding; The list sharding includes using one or a combination of a region code, an organization, and an account as a sharding key, which is constructed in the sharding table to shard the target data by a business attribute dimension; The hash sharding adopts the hash sharding method to realize the hash distribution of data; The range sharding includes using time as a sharding key, which is constructed in a sharding table, so as to perform time dimension sharding on the target data.

5. The query optimization method based on OceanBase database data sharding according to claim 4 is characterized in that: The performing sharding processing on the target data based on the sharding table to create a data sharding structure includes: Slice the target data according to the first-level partition type to obtain a first-level data slicing structure; construct a first mapping relationship based on the first-level partition type, wherein the first mapping relationship is used to match and index the first-level partition type with the corresponding first-level slicing data; The first-level shard data is sharded according to the second-level shard type to obtain a second-level data shard structure; a second mapping relationship is constructed based on the second-level partition type, and the second mapping relationship is used to match and index the second-level partition type with the corresponding second-level shard data.

6. The query optimization method based on OceanBase database data sharding according to claim 1 is characterized in that: Based on the extended SQL query statement, the query optimizer of the OceanBase database selects a preset execution plan to query the business table to obtain a target result set, including: The OceanBase database parses the extended SQL query statement to obtain the standard SQL statement and its corresponding prompt optimization information SQL Hint; The query optimizer receives the standard SQL statement and the hint optimization information SQL Hint; The query optimizer selects a preset execution plan according to the prompt optimization information SQL Hint; Based on the execution plan, execute the standard SQL statement to query the business table; Get the target result set and return it to the requester.

7. The query optimization method based on OceanBase database data sharding according to claim 6 is characterized in that: The OceanBase database parses the extended SQL query statement to obtain the standard SQL statement and its corresponding hint optimization information SQL Hint, including: The OceanBase database parses the extended SQL query statement to obtain key information, index information, query clause information and SQL Hint of the standard SQL query statement; The key information includes a shard key; the query clause information includes SELECT, WHERE, and JOIN clauses to extract the name of the business table to be connected, the target column, and the connection condition of the business table to be connected; The executing the standard SQL statement based on the execution plan to query the business table includes: Use SQL Hint to intervene in the query optimizer to select the preset connection algorithm; Specify the mandatory use of a specified index in the SQL statement to reduce the amount of data scanned and the memory usage through the index; Use the shard table as the external table to drive the search for the target business table; Based on the shard key, the position of each shard in the database is obtained, and the shard where the target data is located is located accordingly; The query optimizer retrieves the shards in parallel to execute the standard SQL query statement to query the business table.

8. A data sharding and query optimization device based on OceanBase database, characterized in that: include: A business key identification module, used to determine one or more columns as business keys based on multiple business tables, wherein the business keys are used to connect business data in the business tables; A sharding key setting module, used to set one or more columns as sharding keys based on the business key, and the sharding key is combined with the business key to construct a sharding table in the OceanBase database; A sharding processing module, used to perform sharding processing on the target data based on the sharding table to create a data sharding structure; A query statement building module, used to build an extended SQL query statement carrying preset hint optimization information SQLHint based on the data sharding structure; The query execution module is used to select a preset execution plan based on the extended SQL query statement and the query optimizer of the OceanBase database to query the business table to obtain a target result set.

9. An electronic device, characterized in that: include: A memory and a processor, wherein the memory stores a computer executable program, and the processor is used to execute the computer executable program to implement the method according to any one of claims 1 to 7.

10. A storage medium, characterized in that: The storage medium stores a computer executable program, and the computer executable program implements the method according to any one of claims 1 to 7 when executed.

Images