A method and apparatus for managing a composite database table

Abstract

The application relates to the technical field of databases, and provides a composite database table management method and device. The application classifies database tables into one of dense wide tables, dense narrow tables or sparse tables; according to the primary key values of multiple continuous data rows in the dense wide tables, the corresponding multiple continuous data rows are dispersedly stored into different physical pages, and the local serial numbers used for constituting the primary key values are in a skip type increasing distribution; according to the primary key values of multiple continuous data rows in the dense narrow tables, the corresponding multiple continuous data rows are continuously stored into physical pages, and the local serial numbers used for constituting the primary key values are in a compact increasing distribution; and according to the primary key values of multiple continuous data rows in the sparse tables, the multiple continuous data rows are stored into physical pages in a default mode, and the local serial numbers used for constituting the primary key values are in a default distribution, so that the problem that the wide tables cause frequent page splitting of B+ trees and lead to collapse of write-in performance when data is continuously written in the primary key order in the prior art is solved.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a method and apparatus for managing composite database tables. Background Technology

[0002] In new power systems, with the exponential growth of business scale and the extreme pursuit of high availability, system architecture has shifted from the early single-machine, primary-backup model to a multi-active architecture within the same city and a disaster recovery architecture in different locations. In this architecture, multiple data centers simultaneously handle production traffic, with each center deploying a complete application stack and database cluster. To ensure high data consistency between centers, structured data (table records in relational databases) needs to be synchronized in near real-time bidirectional or multidirectional directions. A multi-active center, short for multi-active data center, refers to multiple physical data centers (or availability zones) deployed in the same city or different locations, where each center is simultaneously online and handling production traffic, rather than the traditional primary-backup model (i.e., one center handles traffic while another is in cold or hot standby but not processing business). In this city-wide active-active architecture, each active-active center has a complete application stack and database cluster deployed inside, and multiple centers synchronize data bidirectionally or multidirectionally through a high-speed network. The core advantage of the city-wide active-active architecture is that it uses low-latency links within the same city to achieve load balancing and rapid failover of traffic. That is, when a center fails, other centers can take over its traffic in seconds or milliseconds to achieve business continuity. Among them, the low-latency links typically have a round-trip time (RTT) of less than 2 milliseconds.

[0003] However, when business systems enter a large-scale, high-concurrency phase, the synchronization of underlying structured data faces severe challenges. Especially in new power systems with large table data volumes, numerous table objects, and wide table structures, the generation and management of primary keys becomes a major problem. To solve the problem of primary key uniqueness in distributed environments, the industry has widely adopted partition-based algorithms, such as the classic Snowflake algorithm and its various variants.

[0004] For database tables, wide tables have large rows, each occupying thousands of kilobytes (KB). A 16KB physical storage page (or physical page) can only store 2 to 4 rows of data from a wide table. A physical page in the database storage engine is a fixed-size input / output (I / O) unit on the disk. In a B+ tree index, each leaf node corresponds to one such physical page, used to actually store the data rows in the table. However, in a multi-active data synchronization scenario within the same city, multiple active-active centers (i.e., nodes) may concurrently modify the same row of a database table. When multiple nodes concurrently write to the same database table, even if the primary keys of each data row increase in a trend, due to minor differences in network transmission and the microscopically disordered interleaving of primary keys generated by multiple instances, new data rows are forced to be inserted into the middle of already full or half-full physical pages, frequently triggering B+ tree page splits. Each split of the B+ tree requires moving a large amount of data, leading to decreased disk space utilization, increased index height, increased random I / O, and ultimately a precipitous drop in system throughput.

[0005] To ensure compatibility with wide tables and prevent write performance degradation, narrow tables with small row widths suffer from sparse primary key values ​​due to the large number of data rows stored per physical page. This results in low page space utilization and requires skipping multiple non-contiguous pages during sequential scans, reducing cache hit rate and sequential I / O efficiency. Furthermore, sparse tables have extremely low write frequencies, making page splits and write hotspots virtually nonexistent. Enabling complex step-size calculations and state maintenance for them would incur unnecessary CPU and memory overhead. Narrow tables strictly adhere to database design paradigms (e.g., third normal form), have fewer columns (typically 10 or less), each representing a single attribute, a clear primary key, and highly structured data. Narrow tables exhibit less data redundancy, compact storage, high query efficiency, and fast insert / update operations. Sparse tables, on the other hand, are designed for data sparsity, with many fields containing null or zero values. They use a "non-zero element + index" storage approach to avoid storing invalid null values, resulting in low space consumption. However, queries require locating non-zero data through the index, potentially sacrificing some real-time update flexibility.

[0006] Existing primary keys cannot accommodate various database tables and cannot meet the write performance requirements of multi-active architectures.

[0007] Therefore, overcoming the shortcomings of the existing technology is an urgent problem to be solved in this technical field. Summary of the Invention

[0008] The technical problem this invention aims to solve is that, in existing databases, when data is written continuously in primary key order, wide tables cause frequent page splits in the B+ tree, leading to a collapse in write performance.

[0009] Firstly, a composite database table management method is provided, including: Database tables can be classified into one of the following: dense wide tables, dense narrow tables, or sparse tables. Based on the primary key values ​​of multiple consecutive data rows in the dense wide table, the corresponding multiple consecutive data rows are distributed and stored in different physical pages, wherein the local sequence numbers used to constitute the primary key values ​​are distributed in a jump-incremental manner; Based on the primary key values ​​of multiple consecutive data rows in the dense narrow table, the corresponding multiple consecutive data rows are continuously stored in physical pages, wherein the local sequence numbers used to constitute the primary key values ​​are in a compact auto-incrementing distribution; Based on the primary key values ​​of multiple consecutive data rows in the sparse table, the multiple consecutive data rows are stored in physical pages in a default manner, wherein the local sequence numbers used to constitute the primary key values ​​are distributed by default.

[0010] Furthermore, classifying database tables into one of the following categories—dense wide tables, dense narrow tables, or sparse tables—includes: If the number of transactions per second for the database table is less than the processing threshold, then the database table is classified as a sparse table; otherwise, If the average physical row width of the database table is greater than the row width threshold, then the database table is classified as a dense wide table; otherwise, The database tables are classified as dense narrow tables.

[0011] Furthermore, the local sequence numbers of consecutive data rows within the same time window are sequentially increased according to the logical fill step size; The logical fill step size is determined based on the number of data rows that a single physical page can carry and the number of active data centers.

[0012] Furthermore, the number of data rows is the absolute value of the ratio of effective page capacity to row storage cost; the effective page capacity is the product of the database storage page size and the target page fill rate; the row storage cost is the sum of the average physical row width and auxiliary space overhead. The logical fill step size is the maximum of one and the jump step size; the jump step size is the absolute value of the ratio of the sequence number space size to the page capacity after correction; the sequence number space size is a power of a preset number of bits as the base and the number of bits occupied by the local sequence number as the exponent; the page capacity after correction is the product of the number of data rows and the site correction factor; the site correction factor is determined based on the number of active centers.

[0013] Furthermore, after classifying the database tables into one of dense wide tables, dense narrow tables, or sparse tables, the method further includes: Based on the perturbation value in the primary key of the dense wide table, perform query optimization and / or operation and maintenance monitoring.

[0014] Furthermore, the disturbance value is non-zero; The disturbance value is the result of taking the modulo of the mixed offset base value with a preset factor; the mixed offset base value is the sum of the table group identifier and the time offset of the business logic table group to which the dense wide table belongs.

[0015] Furthermore, the method also includes: The primary key index is used to perform a time range scan directly, wherein the highest bit of the primary key is a dynamic timestamp bit; Spatial positioning is performed based on the geographic node identifier corresponding to the data row in the primary key; Obtain the table group identifier of the business logic table group to which the database table belongs from the primary key, and perform business positioning based on the table group identifier.

[0016] Furthermore, the method also includes: Divide the database tables into multiple logical table groups; The lease container is generated and distributed through the primary key coordination server. The lease container includes the authorization information of all logical table groups. Before generating a primary key for any database table within the logical table group, the authorization information is used for validity verification so that the primary key can be generated if the validity verification is passed.

[0017] Furthermore, generating the primary key of the data row using the dynamic timestamp, the table group identifier of the logical table group to which the database table belongs, and the local sequence number further includes: Determine the geographic node identifier of the multi-active center that generated the data row; The disturbance value is determined based on the metadata of the business table corresponding to the database table; The dynamic timestamp, the geographic node identifier, the table group identifier, the perturbation value, and the local sequence number are sequentially determined as the various bit segments of the primary key in descending order of high digit to generate the primary key.

[0018] Secondly, a composite database table management device is provided, the composite database table management device comprising: a processor and a memory for storing processor-executable instructions; The processor is configured to execute the composite database table management method.

[0019] Thirdly, a non-volatile computer storage medium is provided, the computer storage medium storing computer-executable instructions, which are executed by one or more processors to perform the composite database table management method described in the first aspect.

[0020] Fourthly, a computer program product containing instructions is provided, which, when executed on a computer or processor, cause the computer or processor to perform the composite database table management method as described in the first aspect.

[0021] Compared with the prior art, the beneficial effects of the present invention are as follows: This invention divides database tables into dense wide tables, dense narrow tables, or sparse tables. For dense wide tables, due to their large row widths and frequent writes, a distributed local sequence number is used to avoid page splits. For dense narrow tables, due to their small row widths and frequent writes, compact auto-incrementing sequence numbers are used to maintain contiguous storage and improve space utilization. For sparse tables, due to their infrequent writes, writes are performed according to the database's default processing method to save resources. The jump sequence number method prevents multiple consecutive data rows from occupying the same physical page from the source, significantly reducing the frequency and overhead of physical page splits. This avoids B+ tree page splits and performance collapses caused by dense primary key storage during high-concurrency writes in wide tables, while maintaining efficient and compact storage in narrow tables and minimizing the impact of other processing methods applied to sparse tables on write performance. Attached Figure Description

[0022] To more clearly illustrate the technical solutions in the embodiments of the present invention 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 only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0023] Figure 1 This is a flowchart illustrating a composite database table management method provided in an embodiment of the present invention; Figure 2 This is a schematic diagram illustrating a specific example of a composite database table management system provided in an embodiment of the present invention; Figure 3 This is a schematic diagram of the overall structure of a wide-table-aware distributed primary key generation system according to an embodiment of the present invention. Figure 4 This is a schematic diagram illustrating a specific example of a primary key structure provided in an embodiment of the present invention; Figure 5 This is a flowchart illustrating step 10 provided in an embodiment of the present invention; Figure 6 This is a schematic diagram illustrating a specific example of a local sequence number for three consecutive data rows provided in an embodiment of the present invention; Figure 7 This is a schematic diagram illustrating a specific example of a B+ tree provided in an embodiment of the present invention; Figure 8This is a schematic diagram illustrating a specific example of a composite database table management method according to an embodiment of the present invention. Figure 9 This is a schematic diagram of a composite database table management device provided in an embodiment of the present invention. Detailed Implementation

[0024] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the invention.

[0025] Unless the context otherwise requires, throughout the specification and claims, the term "comprising" is interpreted as openly inclusive, meaning "including, but not limited to." In the description of the specification, terms such as "one embodiment," "some embodiments," "exemplary embodiment," "example," "specific example," or "some examples" are intended to indicate that a particular feature, structure, material, or characteristic associated with that embodiment or example is included in at least one embodiment or example of this disclosure. The illustrative representations of the above terms do not necessarily refer to the same embodiment or example. Furthermore, the specific features, structures, materials, or characteristics mentioned may be included in any suitable manner in any one or more embodiments or examples; that is, although they may be incorporated into embodiments or examples using the above terms for reasons such as order and position, it does not limit them to be incorporated in combination by a single embodiment or example.

[0026] In the description of this invention, the terms "first" and "second" are used for descriptive purposes only and should not be construed as indicating or implying relative importance or implicitly specifying the number of indicated technical features. Thus, a feature defined with "first" or "second" may explicitly or implicitly include one or more of that feature. In the description of embodiments of this disclosure, unless otherwise stated, "a plurality of" means two or more. Furthermore, for example, the description may use the prefix "A" or "B" to describe the same type of nouns as two independent entities. In this case, the corresponding features defined with "A" and "B" are used only to distinguish between similar entities and should not be construed as indicating or implying relative importance or implicitly specifying the number of indicated technical features.

[0027] In describing some embodiments, the terms "coupled," "coupled," and "connected," and their derivative expressions, may be used. For example, the term "connected" may be used in describing some embodiments to indicate that two or more components have direct physical or electrical contact with each other. Similarly, the term "coupled" may be used in describing some embodiments to indicate that two or more components have direct physical or electrical contact. However, the terms "connected" or "coupled" may also refer to two or more components that do not have direct contact with each other but still cooperate or interact with each other, such as "optical coupling," "wireless connection," etc. The embodiments disclosed herein are not necessarily limited to the scope of this invention.

[0028] Furthermore, the technical features involved in the various embodiments of the present invention described below can be combined with each other as long as they do not conflict with each other.

[0029] Example 1 In existing technologies, to solve the problem of primary key uniqueness in distributed environments, the industry generally adopts partition-based algorithms, such as the classic Snowflake algorithm and its various variants. These algorithms typically divide a 64-bit integer into three parts: timestamp, machine identifier (i.e., WorkerID), and sequence number.

[0030] Snowflake algorithm is an open-source distributed primary key generation algorithm designed to solve the problem of generating globally unique, trend-increasing, and high-performance 64-bit integer primary keys in distributed systems. By dividing the 64-bit integer into multiple semantic segments (including a sign bit, timestamp, machine ID, and sequence number), it allows each node to generate the primary key independently and locally, avoiding centralized dependencies and thus supporting high-concurrency scenarios. The final generated primary key is a 64-bit long integer, ordered by time (i.e., the timestamp is in the most significant bit), thus naturally exhibiting an increasing trend, which is beneficial for database indexing (e.g., facilitating sequential writing to B+ trees).

[0031] However, in the specific business context of this invention, these prior art technologies still have the problem of not being directly applicable. A detailed analysis follows: 1. Signaling storms and delayed fault recovery caused by massive table objects.

[0032] Existing technologies (such as the classic Snowflake algorithm) typically maintain leases and heartbeats at the service instance or database table partition level. For example, each node independently maintains a heartbeat or authorization in a coordination service (such as Redis or etcd) for each business table or data partition it is responsible for. Here, a business table is a logical data organization unit at the application layer, representing a complete business entity (e.g., an electricity meter data table). However, at the physical storage level, due to the intervention of database sharding middleware, a business table is split into multiple database tables (also known as single-table objects). Each database table is an independently existing table file in the database, used to carry a portion of the business table's data shards.

[0033] This will lead to the following two problems: (1) Signaling storm: In large systems involving massive business tables, if each local node needs to maintain a heartbeat independently for each database table, the signaling scale will reach "number of nodes × number of database tables", resulting in a scale explosion. The resulting massive requests will instantly fill the network bandwidth and cause the backend coordination service (such as Redis or etcd) to enter a denial-of-service state due to the overload of the query rate per second. For example, when there are 100 service nodes, each node is responsible for 10,000 database tables, then the heartbeat scale is "100 × 10,000", which is as high as 1 million. Each database table needs to be renewed periodically (e.g., the period is at the second level), resulting in the coordination service query rate at the million level per second. (2) Long recovery time: When the system undergoes multi-active switching or node failure recovery, massive database table objects need to reapply for partition authorization from the coordinator one by one; for example, a new node needs to reapply for the partition authorization corresponding to worker_id from the coordinator for each database table. This linear application logic significantly lengthens the system recovery time object (RTO), failing to meet the second-level recovery requirements of new power system applications. For example, each database table requires a remote procedure call (RPC), with the time accumulated linearly, which cannot meet the second-level RTO requirement.

[0034] 2. There is a lack of effective adjudication criteria when cross-center data synchronization conflicts occur.

[0035] In the asynchronous synchronization process of multi-active systems in the same city, multiple data centers may concurrently modify the same row of records, thus causing data conflicts. Specifically: (1) Lack of adjudication basis: In this case of data conflict, the primary key generated by the snowflake algorithm and its variants can only guarantee global uniqueness, but does not have business semantics. For example, the primary key only contains: timestamp, machine ID and sequence number, without any business semantics (e.g., data domain, priority, business type). When the synchronization gateway intercepts conflict records, it cannot know which business domain or priority level the data belongs to (e.g., scheduling instructions take precedence over data collection) based solely on the primary key. For example, in the specific scenario of bidirectional synchronization of multi-active systems in the same city in this invention, multiple data centers can concurrently modify the same row of records (e.g., the same meter reading); when the synchronization gateway receives two versions of data, i.e., when asynchronous synchronization conflict occurs, it can only determine the uniqueness through the primary key, and cannot identify which record has a higher priority (e.g., which has a higher priority between scheduling instructions and data collection). Moreover, in order to deal with the corresponding situation, different business domains need to adopt different conflict strategies, but the primary key generated by the existing snowflake algorithm and its variants cannot determine which business domain it belongs to. (2) High processing overhead: To resolve conflicts, the synchronization layer often has to look back at the database to obtain schema metadata. The schema is a logical container in the database, used to organize database objects such as tables, views, and functions, serving as a namespace and permission isolation mechanism. Metadata records the definition information of database objects, such as field names, data types, nullability, and default values. Schema metadata describes the structure and attributes of objects (such as tables, views, columns, and indexes) in the database or data structure, defining the organization, types, constraints, and relationships of data. It is the foundation for database system introspection and external tools to understand the data. Looking back at the database to obtain schema metadata greatly increases the back pressure of the synchronization link, resulting in excessive processing overhead and poor practicality.

[0036] 3. Hotspot physical region trap in distributed databases.

[0037] Existing multi-active architectures often employ distributed databases such as TiDB and CockroachDB. These databases use range-based sharding technology, storing data with similar primary keys in the same physical region. For example, data is divided into physical regions based on primary key ranges, with adjacent primary keys falling within the same physical region. While the existing snowflake algorithm places the 41-bit timestamp at the highest bit of the primary key, ensuring global ordering of the primary key, it also means that all business data generated within the same millisecond will be routed to the same physical region in the storage cluster. For instance, because primary keys are consecutive and incrementing, all newly written data with primary keys greater than the historical maximum will consistently fall within the last physical region.

[0038] In business environments where large amounts of data are continuously generated, this primary key layout can lead to severe write hotspots. Write pressure is concentrated on a single storage node, rapidly exhausting its Central Processing Unit (CPU) and disk I / O, while other nodes in the cluster remain idle. Although some databases offer primary key sharding techniques like AUTO_RANDOM, AUTO_RANDOM completely disrupts the logical order of the data. This forces time-range queries (e.g., querying the past 24 hours of data for a meter) to require scanning the entire table, impairing the performance of time-dependent range queries and failing to meet the efficiency requirements for historical data backtracking.

[0039] 4. Failure mechanisms of wide table index fragmentation and B+ tree splitting.

[0040] The primary keys generated by the existing snowflake algorithm and its variants are macroscopically incremental, but microscopically they may be out of order under multi-node concurrency and network latency jitter. For example, node A generates a primary key with a value of 100 at time T, and node B submits it slightly later due to network latency in the same millisecond, but its generated primary key may have a value less than 100 because of the different machine ID bits.

[0041] When processing structured data with wide columns, database storage engines typically use B+ trees as the physical carrier of clustered indexes. Further down the line, a wide table is a data table structure characterized by integrating the dimensions, metrics, and attributes of multiple related business entities into a single table with a large number of fields to improve query efficiency and ease of analysis; it usually contains dozens to hundreds of fields. In contrast, narrow tables follow database normalization design (e.g., third normal form), with each table storing only the core data of a single entity, having fewer fields (usually a few to dozens of columns), and linking other tables through foreign keys.

[0042] For a wide table with a row width of several kilobytes (KB), a standard 16KB storage page may only be able to store 2 to 4 rows of data. Therefore, in a multi-active architecture within the same city, node A and node B simultaneously write to the same table. Even with a trend-incrementing Snowflake primary key, due to minor differences in network transmission and the microscopically disordered interleaving of primary keys generated by multiple instances, the database must frequently insert records into the middle of full or nearly full physical pages when merging these wide table records from different nodes. That is, data from different nodes, due to the temporal interleaving of their corresponding primary keys, results in newly written rows not being simply appended to the last page, but rather falling in the middle of full or half-full pages—a microscopically out-of-order write. This behavior triggers physical page splits in the B+ tree; to insert a new row, the storage engine must move half of the data from the original page to the new page; and to maintain order, the B+ tree must move half of the data from the original page to the new page and update the index. For narrow tables, the impact of page splits can be mitigated by memory buffers; however, for wide tables, each split involves moving a large amount of data. Page splits can cause a sharp drop in disk space utilization, a rapid increase in index height, and ultimately a significant increase in random I / O during queries, resulting in a precipitous drop in system throughput and performance.

[0043] The existing Snowflake algorithm only guarantees the global uniqueness and macroscopic order of the generated primary keys, but cannot guarantee the microscopic order of the generated primary keys, and cannot achieve a balance between microscopic order and physical storage alignment. In scenarios with wide tables and high concurrency multi-node writes, the microscopic disorder and the structural characteristics of B+ trees conflict drastically, causing the storage engine to frequently perform costly page splitting operations.

[0044] To address the problems of existing technologies, this embodiment proposes a composite database table management method. In one embodiment, such as... Figure 1 As shown, it includes: Step 10: Classify the database tables into one of the following: dense wide tables, dense narrow tables, or sparse tables.

[0045] This invention provides a composite database table management method to balance the write performance of various database tables in a multi-active architecture scenario. First, the database tables of each multi-active center need to be classified: dense wide tables have large row widths and are frequently written; dense narrow tables have small row widths and are frequently written; and sparse tables have very few writes. A specific example of a classification method will be provided below.

[0046] Step 20: Based on the primary key values ​​of multiple consecutive data rows in the dense wide table, the corresponding multiple consecutive data rows are distributed and stored in different physical pages, wherein the local sequence numbers used to constitute the primary key values ​​are distributed in a jump-incremental manner.

[0047] The primary key is the final distributed unique primary key. The local sequence number is used to distinguish multiple data entities generated within the same time window, that is, to distinguish the primary keys of different data rows generated by the same multi-active node within the same millisecond. The specific method for generating the local sequence number shall be selected by those skilled in the art based on existing technologies and the specific use case.

[0048] When responding to primary key generation requests received within the same time window, the database generates a primary key for each of the multiple consecutive data rows corresponding to these requests. In this embodiment of the invention, the primary key consists of multiple parts, including a local sequence number position. For data rows in a dense wide table, the local sequence numbers in these primary keys are distributed in a jump-incrementing pattern. The specific method of this jump-incrementing pattern is determined by those skilled in the art based on the specific application scenario. For example, the local sequence numbers of consecutive data rows generated by jumping in increments of 171 can be 0, 171, 342, and 513, and so on.

[0049] The specific granularity of the time window is determined by those skilled in the art based on the specific application scenario. In one embodiment, the time window can be 1 millisecond. In this case, for primary key generation requests received within the same millisecond: the local sequence number increases by a logical padding step each time until it reaches its maximum value, then wraps around and waits for the next millisecond. A specific example of a local sequence number is: when the initial value of the local sequence number is 0, the local sequence number of the first primary key generation request received within the same millisecond can be 0, the local sequence number of the second primary key generation request received can be 171, and the local sequence number of the third primary key generation request received can be 342. Furthermore, for primary key generation requests received outside of this millisecond (e.g., the next millisecond): the sequence number is reset to the initial value (i.e., 0), and then increments again by the logical padding step starting from this initial value. The purpose of resetting is to avoid excessive accumulation of sequence numbers between different milliseconds, while maintaining the independence of the hash pattern within each millisecond.

[0050] This invention, through controlling the increment of local sequence numbers, ensures that the temporally consecutive primary keys generated by dense wide tables fall within the address range of the same storage page in physical storage. Temporally consecutive means that the generation times of the primary keys are closely linked on the timeline without intervals; that is, the same node receives primary key generation requests continuously within a very short time window (e.g., within the same millisecond or several consecutive milliseconds), and subsequently needs to respond to each primary key generation request sequentially to generate the corresponding primary key. For dense wide tables, the Physical Page Alignment (PPA) optimization mode of this invention is enabled, which uses locally incrementing sequence numbers to generate primary key values. In one embodiment, by setting the increment step of the local sequence number to a logical fill step greater than 1, the local sequence numbers of multiple primary key values ​​generated by the same node within a continuous time window exhibit a jump-like change. In one embodiment, for consecutive primary key generation requests, a jump in the local sequence number ensures a larger interval between the primary key values ​​generated for these data rows, allowing these primary keys to naturally fall within different leaf nodes (i.e., physical pages) in the B+ tree clustered index. Since the B+ tree allocates data rows to physical pages in order of primary key value size, and each physical page has a limited capacity, when the local sequence number increases consecutively (e.g., step size equals 1), the primary key values ​​of consecutive data rows are closely adjacent and are likely to be sequentially inserted into the same physical page, quickly filling the page. When new data rows are subsequently inserted, the page is already full, necessitating a page split (e.g., moving half the data to a new page and updating the index). However, a jump-incrementing sequence (e.g., step size much greater than 1) results in primary key values ​​that differ significantly between consecutive data rows, such as 1000, 1171, and 1342. These fall into different primary key ranges and are thus allocated to different physical pages by the database storage engine. Each page only writes a small number of rows, far from being full, and subsequent writes do not require frequent splits.

[0051] Step 30: Based on the primary key values ​​of multiple consecutive data rows in the dense narrow table, store the corresponding multiple consecutive data rows consecutively into physical pages, wherein the local sequence numbers used to constitute the primary key values ​​are in a compact auto-incrementing distribution.

[0052] This invention employs a compact auto-incrementing local sequence numbering method to tightly arrange primary key values, thereby storing consecutive data rows contiguously in the same physical page or adjacent pages, maximizing space utilization and sequential read performance. The specific compact auto-incrementing method is determined by those skilled in the art based on the specific application scenario. For example, the local sequence numbers of consecutive data rows generated by compact auto-incrementing with a step size of 1 can be 1, 2, 3, and 4, and so on. In one embodiment, when storing multiple consecutive data rows contiguously in a physical page according to the primary key value, the data of these consecutive data rows is first stored sequentially in the same physical page. When that physical page is full, the data continues to be stored sequentially in the next physical page.

[0053] Step 40: Based on the primary key values ​​of multiple consecutive data rows in the sparse table, store the multiple consecutive data rows to physical pages in a default manner, wherein the local sequence numbers used to constitute the primary key values ​​are distributed by default.

[0054] Sparse tables are database tables with a small number of transactions per second (TPS) that is less than a processing threshold, which is determined by those skilled in the art based on the specific use case. The default method and default distribution are determined by those skilled in the art based on the specific use case; the default distribution means storing data as if it were a regular database table, without any special optimization, in order to save system resources.

[0055] It should be noted that when storing data from the data rows to physical pages in steps 20 to 40 above, the operation is performed in the B+ tree based on the primary key value. The distribution of the corresponding local sequence number is used to control the primary key value, resulting in different control effects for dense wide tables, dense narrow tables, and sparse tables.

[0056] In one embodiment, a PPA optimization strategy is shown in Table 1 below: Table 1 Comparison of PPA Optimization Strategies

[0057] in, This represents the average physical row width of the database table, and its calculation method is determined by those skilled in the art based on the specific use case.

[0058] This invention divides database tables into dense wide tables, dense narrow tables, or sparse tables. For dense wide tables, due to their large row widths and frequent writes, a distributed local sequence number is used to avoid page splits. For dense narrow tables, due to their small row widths and frequent writes, compact auto-incrementing sequence numbers are used to maintain contiguous storage and improve space utilization. For sparse tables, due to their infrequent writes, writes are performed according to the database's default processing method to save resources. The jump sequence number method prevents multiple consecutive data rows from occupying the same physical page from the source, significantly reducing the frequency and overhead of physical page splits. This avoids B+ tree page splits and performance collapses caused by dense primary key storage during high-concurrency writes in wide tables, while maintaining efficient and compact storage in narrow tables and minimizing the impact of other processing methods applied to sparse tables on write performance.

[0059] To address the four problems mentioned above in the existing technology, this invention proposes a wide-table-aware distributed primary key generation method for multi-active architecture within the same city. The corresponding system, through the multi-dimensional bit space dynamic reconstruction, table group aggregation lease protocol, and PPA algorithm of the embodiments of this invention, successfully transforms the existing partitioned primary key technology and applies it to complex multi-active data synchronization scenarios.

[0060] The following is a further description of the composite database table management method according to an embodiment of the present invention: An embodiment of the present invention applies to a system architecture such as Figure 2 As shown, the primary key coordinating server is deployed independently outside of multiple active-active data centers and is responsible for managing N active-active data centers (e.g., active-active data center A, active-active data center B); each active-active data center has multiple local primary key servers deployed within it.

[0061] To address the lack of effective adjudication criteria in existing technologies when facing cross-center data synchronization conflicts, in one embodiment, the method further includes: dividing the database table into multiple logical table groups.

[0062] In existing technologies, business tables are logical units for data organization, while database tables (i.e., physical tables) are the actual storage units for data. A business table consists of at least one database table. This invention provides a design for logical table groups. A logical table group can contain one or more business tables (and all their subordinate database tables). When necessary, database tables belonging to the same business table may not belong to the same logical table group. Database tables are divided into logical table groups according to business domains, and each logical table group has a unique identifier.

[0063] like Figure 3 As shown, the primary key coordinating server can be a distributed primary key coordinating server, which can include a table group aggregation coordinator. The table group aggregation coordinator is used to divide a large number of single-table objects (i.e., database tables) into logical table groups based on the classification results.

[0064] This invention is a composite database table management method for a new type of power dispatching business platform. In one embodiment, in order to solve the problem that when the synchronization gateway intercepts conflict records, it cannot know which business domain or priority level the data belongs to based solely on the primary key, business aggregation and grouping can be implemented first. That is, by classifying the database tables into multiple logical table groups according to the classification results of dividing the database tables into multiple logical table groups, a large number of database tables can be divided into logical table groups. In an optional embodiment, the division criteria can be the following three: (1) Business domain affinity: Grouping tables belonging to the same service domain (e.g., order domain, payment domain) to ensure that a single application can meet all business requirements. (2) Physical layout affinity: Aggregating tables that have consistent primary key structure requirements (e.g., all require 3 perturbation bits) to simplify the logical complexity of the allocation engine; the specific meaning and example of a perturbation bit are given below and will not be repeated here. (3) Synchronization strategy affinity: Grouping according to synchronization delay requirements to facilitate batch lease priority scheduling in case of failure.

[0065] In one embodiment, the table group identifier of the business logic table group to which the database table belongs is obtained from the primary key, and business location is performed based on the table group identifier. The synchronization gateway can directly extract the table group identifier by parsing the primary key of the conflict record, immediately knowing the business domain to which the data belongs and its priority, thereby enabling stateless adjudication based on the corresponding strategy without relying on information other than the primary key.

[0066] Addressing the characteristics of multiple active nodes within the same city, this invention abandons the traditional fixed-bit partitioning method for primary keys and introduces a three-level topology partitioning layout. To further optimize the lack of effective adjudication criteria when facing cross-center data synchronization conflicts, embodiments of this invention also introduce geographical node identifiers and perturbation values ​​into the generated primary key. To facilitate the arrangement and effective functioning of geographical node identifiers and perturbation values, in one embodiment, the primary key follows a sequence from high to low bits, including dynamic timestamp bits, table group identifier bits, and local sequence number bits. Furthermore, as... Figure 4 As shown, the primary key structure of this embodiment is divided into the following parts from high to low: dynamic timestamp bit (i.e., ), geographic node identifier (i.e., ), table group identifier bit (i.e., ), disturbance position (i.e., ) and local sequence number position (i.e., ).

[0067] The dynamic timestamp is the time interval elapsed since the time the data row was generated in the corresponding database table, starting from a preset start time. The preset start time is determined by those skilled in the art based on the specific use case. In one embodiment, it can represent the number of milliseconds elapsed since the time the data row was generated, starting from the system-set start time.

[0068] In one embodiment, its mathematical expression is as follows: ; in, This represents the final 64-bit distributed unique primary key. This indicates a dynamic timestamp bit. This indicates the geographic node identifier. This indicates the table group identifier, used to represent a set of logical tables with the same business attributes or synchronization strategies. This represents the perturbation bit (i.e., the entropy bit). Indicates the local sequence number position. , , and These represent the displacement step size for each component, used to control the physical position of each part in 64-bit space. "This indicates the bitwise left shift operator." " indicates the bitwise OR operator.

[0069] Based on this, in one embodiment, the method further includes: performing a time range scan directly using a primary key index, wherein the highest bit of the primary key is a dynamic timestamp bit.

[0070] Spatial location is performed based on the geographic node identifier corresponding to the data row in the primary key. The geographic node identifier is used to identify which active-active data center each data row originated from. In one embodiment, when it is necessary to determine the active-active data center to which a data row belongs, the geographic node identifier in the geographic node identifier bit is extracted from the primary key value of the data row to obtain the node ID of the active-active data center.

[0071] The dynamic timestamp is located in the highest bit, ensuring the primary key is macroscopically ordered by time. This guarantees primary key continuity and efficient B+ tree indexing during time-range queries, without impacting query performance. The table group identifier follows immediately, enabling rapid differentiation of data from different business domains within the same time window. The synchronization gateway can determine the business domain and priority of a row simply by parsing the primary key, without needing to look up database metadata. The local sequence number is placed in the lowest bit, serving as the last distinguishing dimension within the same millisecond on the same active-active node, ensuring the uniqueness of data within the same domain. This arrangement, along with the timestamp and table group identifier, guarantees uniqueness. This arrangement allows the primary key to simultaneously carry temporal order and business adjudicability, completely resolving the issues of missing conflict resolution criteria and synchronization link backpressure caused by the lack of business semantics in existing snowflake algorithms.

[0072] When the local sequence number is in the lowest segment of the primary key value, and the high bits such as the timestamp are the same, the numerical difference of the primary key is mainly determined by the jump of the local sequence number, which enables these primary keys to be distributed and stored in different physical pages in the B+ tree clustered index.

[0073] This invention breaks the write hotspot of wide tables in distributed databases by setting a perturbation bit in the primary key and dynamically injecting random or hash factors through the perturbation value. A specific example will be given below, and will not be elaborated further.

[0074] In this embodiment of the invention, the table group identifier in the primary key generated needs to be located between the dynamic timestamp and the local sequence number. This ensures that the primary key itself can carry the business or priority semantics represented by the logical table group without disrupting the global temporal order of the primary key. This provides a stateless local basis for resolving conflicts in multi-active asynchronous synchronization. Since business information and / or priority information are built into the primary key, the gateway no longer needs to look up the database to obtain schema metadata to determine the business type or priority, completely avoiding the network overhead and backpressure on the synchronization link caused by lookups. By changing conflict resolution from an external dependency to local resolution, while maintaining the temporal order of the primary key representation and without compromising range query performance, the problem of conflict resolution in multi-active asynchronous synchronization within the same city is solved.

[0075] In one embodiment, such as Figure 3 As shown, the distributed primary key coordination server may include a wide table metadata detector and a load collector. Each local primary key server in a multi-active data center may include a multi-dimensional bit allocation engine and a PPA optimizer. When an upper-layer application requests a primary key, the local primary key server, based on the classification results provided by the wide table metadata detector and the load collector, generates a primary key carrying a table group identifier through the multi-dimensional bit allocation engine and the PPA optimizer, writes the data to its local database, and grants the primary key to the upper-layer application. Within each multi-active data center, the corresponding upper-layer application (i.e., the application program) writes the required data to the database, and the databases in different multi-active data centers are synchronized through a synchronization engine.

[0076] Here, the primary key generation request is a request received by the local primary key server to request the generation of a primary key for a specific data row. In one embodiment, such as... Figure 3As shown, primary key generation requests can be initiated by upper-layer applications located in the same active-active data center as the local primary key server. The same local primary key server will continuously receive primary key generation requests within a very short time window (such as within one millisecond or several consecutive milliseconds). Driven by needs such as creating new data rows, upper-layer applications send primary key generation requests to the local primary key server to generate primary keys for the corresponding data rows. When responding to a received primary key generation request, the local primary key server enters the primary key generation process, which first generates a local sequence number and then generates the primary key based on that local sequence number.

[0077] To enable different primary key value schemes for dense wide tables, dense narrow tables, and sparse tables, in one embodiment, a scan is first performed: the wide table metadata detector performs metadata retrieval, which in one embodiment periodically searches the database data dictionary to obtain the number of fields and data type definitions for each table. Then, physical column width is calculated: the wide table metadata detector calculates the expected average physical length of a single row record based on the data type. The wide table metadata detector evaluates the column width of each table in real time. Finally, load intensity is graded: the load collector collects the frequency of record generation (i.e., Figure 3 The data generated (based on the sampling frequency) is combined with the expected average physical length to label table objects as: dense wide table, dense narrow table, or sparse table. Specifically, in one embodiment, such as... Figure 5 As shown, step 10 includes: Step 101: If the number of transactions per second of the database table is less than the processing threshold, then the database table is classified as a sparse table.

[0078] The processing volume threshold is determined by those skilled in the art based on the specific use case. As shown in Table 1 above, the processing volume threshold is lower than the average transaction processing volume per second of ordinary database tables, in order to filter out database tables with sparse data writing frequency.

[0079] Step 102: Otherwise, if the average physical row width of the database table is greater than the row width threshold, the database table is classified as a dense wide table.

[0080] The row width threshold is determined by those skilled in the art based on the specific use case. In one embodiment, non-sparse database tables are identified as dense tables, and further divided into dense wide tables and dense narrow tables using a row width threshold. The row width threshold is much higher than the row width of ordinary database tables to filter out wide tables.

[0081] Step 103: Otherwise, classify the database table as a dense narrow table.

[0082] In one embodiment, a database table that is neither a sparse table nor a dense wide table is divided into a dense narrow table.

[0083] In one embodiment, two processing thresholds can be set: a very low threshold for filtering out sparse tables and a higher threshold for dividing into non-dense wide or narrow tables. For these database tables, to address write performance issues, the processing method used in step 30 for dense narrow tables can be adopted, or the processing method used in step 40 for sparse tables can be adopted. Since these database tables have a relatively small impact on write performance, the specific processing method can be determined by those skilled in the art based on the application scenario, and is not limited here.

[0084] Building upon this architecture, and addressing the issue of B+ tree page splitting easily caused by wide table records, this invention further overcomes the shortcomings of existing wide table index fragmentation and B+ tree splitting failure mechanisms. In the local sequence number generation stage, a physical page filling alignment optimization algorithm is introduced, no longer treating the local sequence number as a simple auto-increment, but rather as a physical space reservation function. Specifically, in one embodiment, as... Figure 6 As shown, the local sequence numbers of consecutive data rows within the same time window are sequentially increased according to the logical padding step. In one embodiment, for multiple consecutive data rows, when the time window changes (e.g., entering the next millisecond), the local sequence number is reset (e.g., reset to 0), and then incremented again according to the logical padding step. This sequential increment only applies within the same time window (e.g., milliseconds); the sequence number is reset when crossing milliseconds, and it does not increment globally indefinitely. For example, when the logical padding step is 171, the local sequence numbers corresponding to three consecutive data rows within the same time window can be 0, 171, and 342, respectively.

[0085] In one embodiment, the logical fill step size is determined based on the number of data rows that a single physical page can carry and the number of active data centers.

[0086] Specifically, in one embodiment, the number of data rows is the absolute value of the ratio of effective page capacity to row storage cost; the effective page capacity is the database storage page size. With target page fill rate The product; the row storage cost is the average physical row width. With auxiliary space overhead The sum of these values. In this embodiment of the invention, the logical fill step (LFS) is calculated based on the average physical row width obtained from the wide table metadata detector and the standard storage page size of the database (e.g., 16KB).

[0087] In one embodiment, the number of data rows can be expressed as follows: ; in, This indicates the number of records of the dense wide table that a single physical page can hold, i.e., the number of data rows mentioned above. This indicates the size of the database storage page, which is determined by those skilled in the art based on the specific use case. The target page fill rate is determined by those skilled in the art based on the specific use case; in one embodiment, the target page fill rate can be 0.7 to 0.85 to reserve synchronization buffer space. This represents the average physical row width. The auxiliary space overhead is determined by those skilled in the art based on the specific use case.

[0088] The logical fill step size is the maximum of one and the jump step size; the jump step size is the absolute value of the ratio of the serial number space size to the page capacity after correction; the serial number space size is: based on a preset number of bits and the number of bits occupied by the local serial number. The value is a power of the exponent; the corrected page capacity is the number of data rows. Site Correction Factor The product of these factors; the site correction factor is determined based on the number of active centers.

[0089] The preset bit length can be 2. Both the preset bit length and the site correction factor are determined by those skilled in the art based on the specific application scenario. In one embodiment, an expression for the logical fill step size is: ; in, This indicates the number of bits occupied by the local sequence number. This represents the site correction factor; in one embodiment, the site correction factor is dynamically adjusted according to the number of active-active centers to isolate the landing areas of primary keys on different active-active nodes.

[0090] The local sequence number for the primary key used in dense wide tables does not simply increment from 0, but rather dynamically calculates a logical fill step based on the average physical width of the table records. If the monitoring module detects that the current table is a wide table with extremely high write frequency, the generation engine ensures that the local sequence numbers corresponding to the dense wide tables generated by the same node fall within the address range of the same disk page (i.e., Page) in terms of physical storage distribution. By reserving tiny gap bits in the primary key, the physical layout of the primary key resonates with the fill rate of the B+ leaf nodes, thereby improving the write performance stability of dense wide tables in scenarios with continuously high-frequency data generation. PPA step control is a continuously effective incrementing strategy. As long as it is enabled for dense wide tables, the increment of the local sequence number strictly follows the rule of "current value + logical fill step," and is reset only during millisecond switching. This ensures that regardless of the timing of data row write requests, the primary key can be distributed across physical pages, thereby steadily reducing the frequency of page splits.

[0091] In a practical application scenario with 16KB physical pages, for a dense wide table with approximately 8KB of data per row, the primary key values ​​generated for five consecutive data rows are 1000, 1171, 1342, 1513, and 1684, respectively, with a logical padding step size of 171. For ease of explanation of the effects of this embodiment, only the local sequence number is shown here as the primary key value. In actual use, placing the local sequence number in the least significant bit achieves the same technical effect as in this specific example.

[0092] like Figure 7 The diagram shows a B+ tree, including a root node and multiple internal nodes belonging to the root node, as well as physical pages belonging to the corresponding internal nodes (including pages A, B, C, D, E, and F). When storing consecutive data rows into this B+ tree using the method of this embodiment, the insertion steps can be as follows: Insert 1000 → locate page A (range [0, 1100)), write; Insert 1171 → locate page B (range [1100, 1250)), write; Insert 1342 → locate page C (range [1250, 1400)), write; Insert 1513 → locate page D (range [1400, 1550)), write; Insert 1684 → locate page E (range [1550, 1700)), write. Each physical page stores only one row, and none are full (each page can store two rows), so no page splits are triggered. Subsequent insertions can continue to use the free space of these pages.

[0093] To address the signaling storms and delayed fault recovery issues caused by massive table objects, one embodiment generates and distributes lease containers through a primary key coordination server. These lease containers include authorization information for all logical table groups. Logical table groups serve as the aggregation unit for lease management. In another embodiment, the local primary key server within a multi-active data center requests lease containers at the logical table group level, allowing nodes in other data centers to take over authorization in batches during a fault.

[0094] This invention provides an aggregated lease scheme that addresses the overload problem of coordination services under massive table objects through containerized lease management. Specifically, when the primary key server instance starts, it no longer requests the partition primary key of database table A, but instead applies for a lease container containing all logical table groups hosted by that node through the table group aggregation coordinator. Here, the partition primary key refers to a unique authorization identifier (e.g., WorkerID or lease authorization credential in the Snowflake algorithm) assigned to each physical shard (i.e., partition) in a sharded database environment. This identifier is used to distinguish the write permissions of different nodes to different partitions, ensuring the uniqueness of the primary key within the same partition.

[0095] In one embodiment, when the local primary key server starts up, it initiates a containerized lease request to the table group aggregation coordinator. The containerized lease request carries a list of all logical table groups that the local primary key server is responsible for. In response to the containerized lease request, the table group aggregation coordinator issues a lease container to the local primary key server, wherein the lease container contains the authorization information of all logical table groups in the list.

[0096] Before generating a primary key for any database table within the logical table group, the authorization information is used for validity verification, so that the primary key can be generated if the validity verification is passed. The specific method for performing the validity verification can be determined by those skilled in the art based on the specific use case, and is not limited here.

[0097] In one embodiment, the local primary key server caches the lease container locally and performs a validity check based on the authorization information in the lease container when generating a primary key for any database table within the logical table group.

[0098] This invention employs a local link latency compensation method, leveraging the high bandwidth and low latency (i.e., less than 2 milliseconds) characteristics between multiple active-active centers within the same city. The coordinator sets the hard expiration time of the lease to 500 milliseconds to 1 second. Through high-frequency, lightweight state synchronization, when a node fails, other active-active centers can detect and take over the partition primary key at millisecond speeds, achieving rapid fault detection and takeover, as well as near-zero service switching awareness.

[0099] In one embodiment, the lease status of each logical table group can also be compressed into a bitmap-formatted heartbeat packet, which is then sent to the primary key coordination server to synchronize the lease status. This compresses the heartbeat status of massive database tables into a single protocol packet; the specific method of compressing the heartbeat packet using bitmap technology is determined by those skilled in the art based on the specific use case. This protocol packet uses bitmap technology to identify the health status of each table group, significantly reducing metropolitan area network bandwidth consumption and the query rate pressure on the coordination service.

[0100] According to the above scheme, in one embodiment, the local primary key server requests a lease container from the coordination server at startup and sends compressed bitmap heartbeats at a preset period to maintain authorization; wherein, the preset period is determined by those skilled in the art according to the specific use case.

[0101] The synchronization engine between each center is responsible for bidirectional data synchronization, while the coordination server realizes cross-center fault detection and rapid lease takeover through heartbeat detection, thus forming a collaborative architecture with unified trust of the primary key coordination server, local generation of multiple active centers, and cross-center data synchronization.

[0102] A logical table group can contain one or more business tables (and all its subordinate database tables), enabling the system to maintain authorization and heartbeats uniformly at the table group level. This reduces the signaling scale from "number of nodes × number of physical sub-tables" to "number of nodes × number of logical table groups", achieving high-frequency state synchronization and suppressing heartbeat storms.

[0103] In one embodiment, upon receiving the first primary key generation request for an uncached business table, the complete authorization information of the logical table group to which the business table belongs is obtained predictively and cached locally; when the business table is a sparse table, authorization information containing a silent flag is received, which is used to indicate that no heartbeat needs to be sent within a preset time period.

[0104] This invention enables the system to seamlessly switch between different modes based on business load by refactoring the primary key space. To achieve this effect, in one embodiment, an extremely ordered layout is used for narrow tables; for wide tables, a hotspot-balanced layout is used to address the distributed hotspot problem in existing technologies. Table 2 below shows the allocation schemes for the two modes within the 64-bit space: Table 2 Comparison of Application Schemes for Extreme Order Mode and Hotspot Equilibrium Mode

[0105] In one embodiment, in narrow table mode, the perturbation bit is set to 0, and the primary key values ​​maintain a strict trend order, maximizing the sequential read performance of the index. In wide table mode, the engine automatically "borrows" 3 bits from the redundant low bits of the timestamp or the high bits of the sequence number as perturbation bits.

[0106] Specifically, in one embodiment, after step 10, the method further includes: performing query optimization and / or operational monitoring based on the perturbation value in the primary key of the dense wide table. The perturbation value is the numerical value of the perturbation bit. For dense wide tables, the perturbation bit embedded in the primary key primarily plays a role in query optimization (Region pruning) and operational monitoring (load balancing, hotspot location), and is important metadata for improving performance and maintainability in a distributed database environment.

[0107] In one embodiment, the perturbation value is a non-zero value. In another embodiment, the perturbation value is the result of taking the modulo of the mixed offset base value with a preset factor; the mixed offset base value is the sum of the table group identifier of the business logic table group to which the dense wide table belongs and the time offset. In one embodiment, the offset between the dynamic timestamp and the preset benchmark is determined as the time offset. The values ​​of the preset benchmark and the preset factor are determined by those skilled in the art based on the specific use case. In one embodiment, the time offset can be the millisecond offset of the current timestamp (e.g., the time when the current data row was generated) relative to a certain benchmark (e.g., the system startup time or a custom time). The perturbation value is dynamically generated based on "(table group identifier + time offset)% preset factor".

[0108] The perturbation value changes dynamically over time to distribute distributed primary keys generated within the same time window across multiple storage areas of the distributed database. This introduces a moderate degree of hashing at the micro-timescale of milliseconds, allowing wide table data to be evenly distributed across multiple physical regions of the distributed database, avoiding single-point I / O bottlenecks. On the macro-timeline, because the perturbation bit is located in the lower-middle range of the primary key, the primary key maintains global temporal order, ensuring that the scanning efficiency of range queries does not suffer from the collapse seen with Universally Unique Identifiers (UUIDs).

[0109] Specifically, in the B+ tree clustered index of a database, data rows are physically stored in numerical order according to the primary key, and comparisons between integers follow a lexicographical order rule with the most significant bit first. This embodiment of the invention places the dynamic timestamp in the highest segment of the 64-bit primary key, ensuring that the size relationship between any two primary keys is primarily determined by their dynamic timestamps. That is, as long as the dynamic timestamp is larger, regardless of the values ​​of subsequent segments (including geographic node identifier, table group identifier, perturbation bit, and local sequence number bit), the value of that primary key will inevitably be greater than the primary key with the smaller timestamp. Thus, on the macro timeline, all primary keys naturally maintain an increasing order completely consistent with the data generation time, and newly written data logically always appears at the end of the primary key sequence. When the business needs to retrieve data by time range (e.g., querying the records of a certain electricity meter for the past 24 hours), the database can directly utilize the sequential nature of the primary keys to perform an efficient range scan along the rightmost path of the B+ tree. It only needs to locate the starting position and then sequentially read consecutive data pages, with I / O overhead proportional to the amount of data hit.

[0110] In contrast, UUIDs and their similar random primary key schemes "collapse" under high-concurrency write scenarios because their high-order bytes completely lack time information. UUID generation mechanisms are typically based on random numbers or hash values, resulting in the high-order bytes being evenly distributed across the entire value space. Since B+ trees require strictly ordered primary keys, when using UUIDs as clustered indexes, the primary key of each new row of data will randomly fall within any position of the existing primary key range. This position could be the leftmost, middle, or rightmost side of the tree, meaning each insertion might hit a full or nearly full B+ tree data page. This forces the storage engine to frequently perform costly page splitting operations, such as migrating about half the data from the original page to a new page and updating the parent node's index pointer. For wide tables, where each page can only store a few rows of data, the additional overhead of page splitting is further amplified. Meanwhile, because the data is completely distributed in physical storage, the database buffer pool cannot form effective hotspot areas. In one embodiment, the cache hit rate plummeted from over 90% in sequential write scenarios to below 50%, with a large amount of random I / O replacing the original sequential I / O. Furthermore, when executing range queries based on time conditions, even if the query conditions contain a clear time range, the database cannot use the primary key for range pruning because the numerical order of the primary key is unrelated to the time order. The system has to scan a large number of data pages that do not actually meet the time conditions, resulting in an exponential increase in query latency and a precipitous drop in throughput, i.e., the "collapse" mentioned above.

[0111] It is worth noting that, without disrupting the temporal order, this embodiment of the invention introduces a controllable micro-hash only in the lower-middle segments of the primary key (i.e., the perturbation bit). The presence of the perturbation bit causes primary keys generated consecutively within the same millisecond and the same table group to no longer be strictly consecutive, but rather exhibit a "jumping" distribution pattern within a limited range. Since the perturbation bit is after the dynamic timestamp, it can only affect the micro-arrangement of primary keys within the same time window, and cannot reverse the high-low size relationship determined by the timestamp. Therefore, on a macro-timescale, the primary key still maintains a strictly time-consistent total order relationship, and the new data in the B+ tree is still mainly append-only, with page splitting frequency remaining at an extremely low level; while at the micro-level, the dynamic change of the perturbation bit is sufficient to evenly distribute the data generated within the same time window across multiple physical regions of the distributed database, thereby eliminating the single-node write hotspot caused by the monotonically increasing primary key in the range sharding architecture. This achieves balanced write operations and ordered queries, resolving the contradiction between the two in existing technologies. It avoids the performance crashes caused by index fragmentation, random I / O, and range queries due to complete disorder, as seen in UUID schemes, and also solves the problem of physical region hotspots and B+ tree page splitting caused by macroscopic monotonically increasing behavior in the classic Snowflake algorithm in wide-table multi-active scenarios.

[0112] This invention addresses B+ tree index fragmentation and page splitting in wide tables through dynamic bit layout and physical page fill alignment: a multi-dimensional bit allocation engine determines the bit layout; dense wide tables inject perturbation bits, while sparse and dense narrow tables employ a zero-perturbation layout. Dense wide tables enable physical page fill alignment with a jump step size, reserving a buffer within the B+ tree page for multi-active data on the other end through step size control, preventing immediate page splitting. Dense narrow tables enable physical page fill alignment with an auto-incrementing sequence to prevent primary key conflicts on the same node at the same time.

[0113] Example 2 To better illustrate the composite database table management method of this invention, a specific example of using the composite database table management method of Embodiment 1 of this invention is provided below, such as... Figure 8 As shown, it is divided into three stages, as detailed below: Phase 1: Automatic perception of business characteristics and sampling of metadata.

[0114] (1) Pattern scanning: The wide table metadata detector performs metadata retrieval. In one embodiment, it can periodically retrieve the database data dictionary to obtain the number of fields and data type definitions for each table.

[0115] (2) Physical column width calculation: The wide table metadata detector calculates the expected average physical length of a single row record based on the data type.

[0116] (3) Load intensity classification: The frequency of data collection and recording by the load collector, combined with the expected average physical length, marks the table objects as: dense wide table, dense narrow table or sparse table.

[0117] Phase Two: Table Group Aggregation and Distributed Lease Distribution.

[0118] (1) Business aggregation grouping: The table group aggregation coordinator divides a large number of single table objects into logical table groups according to the classification results. The operation process is as described in step 10 of Embodiment 1 of this invention.

[0119] (2) Batch Lease Request: The local node initiates a containerized lease request for a specific table group list to the coordinator. The request is based on the following logic: 1. Scope of responsibility: Only the table group list corresponding to the application business currently under the responsibility of the node is requested. 2. Dynamic triggering: When the first primary key request for an uncached table is received from the application layer, all leases of the table group to which the table belongs are preemptively retrieved at once. 3. For sparse table groups, the coordinator issues long-term leases with a silent flag. The operation process is as described in steps 301a to 302a in Embodiment 1 of this invention.

[0120] (3) Bitmap heartbeat maintenance: The heartbeat packet is compressed using bitmap technology to achieve high-frequency state synchronization and suppress heartbeat storms. The operation process is as described in Embodiment 1 of this invention.

[0121] Phase 3: Alignment of dynamic bit layout with physical page fill.

[0122] (1) Layout application: The multi-dimensional bit allocation engine determines the bit layout. Dense wide tables are injected with perturbation bits, while sparse tables and dense narrow tables adopt a zero-perturbation layout. The operation process is as described in step 302b of Embodiment 1 of this invention.

[0123] (2) Step size control: Dense wide tables enable physical page fill alignment jump step size to reserve B+ tree page buffer for multi-active data on the other end and prevent immediate page splitting. Dense narrow tables enable physical page fill alignment auto-increment sequence to prevent primary key conflicts on the same node at the same time. The operation process is as described in steps 201 to 202 of Embodiment 1 of this invention.

[0124] Example 3 The foregoing embodiments provided a composite database table management method. In this embodiment, another composite database table management device is proposed. The composite database table management device includes: a processor and a memory for storing processor-executable instructions; wherein the processor is configured to execute the composite database table management method described in the foregoing embodiments.

[0125] like Figure 9 As shown, the composite database table management device includes a processor 21 and a memory 22, wherein the processor 21 and the memory 22 can be connected by a bus or other means.

[0126] Processor 21 can be a CPU. Processor 21 can also be other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, or combinations of the above types of chips.

[0127] The memory 22, as a non-transitory computer-readable storage medium, can be used to store non-transitory software programs, non-transitory computer-executable programs, and modules, such as the program instructions / modules corresponding to the composite database table management method in the aforementioned embodiments. The processor executes various functional applications and training processes by running the non-transitory software programs, instructions, and modules stored in the memory.

[0128] The memory 22 may include a program storage area and a training storage area. The program storage area may store the operating system and applications required for at least one function; the training storage area may store training data created by the processor. Furthermore, the memory may include high-speed random access memory and non-transitory memory, such as at least one disk storage device, flash memory, or other non-transitory solid-state storage device. In some embodiments, the memory 22 may optionally include memory remotely located relative to the processor, which can be connected to the processor via a network. Examples of such networks include, but are not limited to, the Internet, corporate intranets, local area networks, mobile communication networks, and combinations thereof. The one or more modules stored in the memory 22, when executed by the processor 21, perform the composite database table management method as shown in the embodiments of the present invention. Specific details of the composite database table management method can be understood by referring to the corresponding descriptions and effects in the embodiments of the present invention, and will not be repeated here.

[0129] This embodiment also provides a computer storage medium storing a computer program that can be executed by a processor to perform the composite database table management method described in the foregoing embodiments.

[0130] The computer storage medium stores computer-executable instructions, which can execute the composite database table management method in any of the above method embodiments. The storage medium can be a magnetic disk, optical disk, read-only memory (ROM), random access memory (RAM), flash memory, hard disk drive (HDD), or solid-state drive (SSD), etc.; the storage medium may also include combinations of the above types of memory.

[0131] The specific steps of the composite database table management method are described in the foregoing embodiments and will not be repeated in this embodiment.

[0132] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of the present invention should be included within the protection scope of the present invention.

Claims

1. A composite database table management method, characterized in that, include: Database tables can be classified into one of the following: dense wide tables, dense narrow tables, or sparse tables. Based on the primary key values ​​of multiple consecutive data rows in the dense wide table, the corresponding multiple consecutive data rows are distributed and stored in different physical pages, wherein the local sequence numbers used to constitute the primary key values ​​are distributed in a jump-incremental manner; Based on the primary key values ​​of multiple consecutive data rows in the dense narrow table, the corresponding multiple consecutive data rows are continuously stored in physical pages, wherein the local sequence numbers used to constitute the primary key values ​​are in a compact auto-incrementing distribution; Based on the primary key values ​​of multiple consecutive data rows in the sparse table, the multiple consecutive data rows are stored in physical pages in a default manner, wherein the local sequence numbers used to constitute the primary key values ​​are distributed by default.

2. The composite database table management method according to claim 1, characterized in that, The classification of database tables into one of the following categories—dense wide tables, dense narrow tables, or sparse tables—includes: If the number of transactions per second for the database table is less than the processing threshold, then the database table is classified as a sparse table; otherwise, If the average physical row width of the database table is greater than the row width threshold, then the database table is classified as a dense wide table; otherwise, The database tables are classified as dense narrow tables.

3. The composite database table management method according to claim 2, characterized in that, The local sequence numbers of consecutive data rows within the same time window are sequentially increased according to the logical fill step size; The logical fill step size is determined based on the number of data rows that a single physical page can carry and the number of active data centers.

4. The composite database table management method according to claim 3, characterized in that, The number of data rows is the absolute value of the ratio of effective page capacity to row storage cost; the effective page capacity is the database storage page size. With target page fill rate The product; the row storage cost is the average physical row width. With auxiliary space overhead sum; The logical fill step size is the maximum of one and the jump step size; the jump step size is the absolute value of the ratio of the serial number space size to the page capacity after correction; the serial number space size is: based on a preset number of bits and the number of bits occupied by the local serial number. The value is a power of the exponent; the corrected page capacity is the number of data rows. Site Correction Factor The product of these factors; the site correction factor is determined based on the number of active centers.

5. The composite database table management method according to claim 1, characterized in that, After classifying the database tables into one of dense wide tables, dense narrow tables, or sparse tables, the method further includes: Based on the perturbation value in the primary key of the dense wide table, perform query optimization and / or operation and maintenance monitoring.

6. The composite database table management method according to claim 5, characterized in that, The disturbance value is a non-zero value; The disturbance value is the result of taking the modulo of the mixed offset base value with a preset factor; the mixed offset base value is the sum of the table group identifier and the time offset of the business logic table group to which the dense wide table belongs.

7. The composite database table management method according to any one of claims 1-6, characterized in that, The method further includes: The primary key index is used to perform a time range scan directly, wherein the highest bit of the primary key is a dynamic timestamp bit; Spatial positioning is performed based on the geographic node identifier corresponding to the data row in the primary key; Obtain the table group identifier of the business logic table group to which the database table belongs from the primary key, and perform business positioning based on the table group identifier.

8. The composite database table management method according to any one of claims 1-6, characterized in that, The method further includes: Divide the database tables into multiple logical table groups; The lease container is generated and distributed through the primary key coordination server. The lease container includes the authorization information of all logical table groups. Before generating a primary key for any database table within the logical table group, the authorization information is used for validity verification so that the primary key can be generated if the validity verification is passed.

9. A composite database table management device, characterized in that, The composite database table management device includes: a processor and a memory for storing processor-executable instructions; The processor is configured to execute the composite database table management method according to any one of claims 1 to 8.

10. A non-volatile computer storage medium, characterized in that, The computer storage medium stores computer-executable instructions, which are executed by one or more processors to perform the composite database table management method according to any one of claims 1 to 8.

Images