Message data partition query method and device, computer device, and storage medium

Abstract

The application discloses a message data partition query method and device, computer equipment and a storage medium. The method comprises the following steps: performing a table splitting operation on a database table according to a message type, so that each message type corresponds to a message master table; partitioning each message master table to obtain a certain number of partition tables, and numbering the partition tables; when message data is inserted, determining the corresponding message master table according to the message type of the message data, obtaining the user code of each user logging into the database table, performing Hash remainder calculation on the user code of each user, determining the partition table number corresponding to each user in the message master table according to the Hash remainder value, and inserting the message data of each user into the corresponding partition table according to the partition table number; and when message data is queried, the message data is queried according to the user code. The application can reasonably split the large amount of database tables, and can improve the message data query efficiency.

Description

Technical Field

[0001] This application relates to the field of data query technology, and in particular to a method, apparatus, computer device, and storage medium for querying message data partitions. Background Technology

[0002] Whether targeting individual consumers (C-end) or business users (B-end), querying user message data is a common function. However, with the continuous growth of the user base, the amount of message data stored is also constantly increasing. Currently, message data for different types and groups of people is stored in the same large message table. This large message table means that each user may only generate a few dozen messages per day. However, when each user queries message data, they need to retrieve these few dozen messages from a message table containing tens of millions of data entries. This puts a heavy burden on database performance and SQL (Structured Query Language) queries, resulting in slow query efficiency. Summary of the Invention

[0003] This application provides a message data partition query method, apparatus, computer device, and storage medium, aiming to solve the technical problems of existing message data query methods, such as high database performance and SQL query consumption, and slow query efficiency.

[0004] To solve the above-mentioned technical problems, the technical solution adopted in this application is as follows:

[0005] A method for querying message data partitions, comprising:

[0006] The database tables are partitioned according to message type, so that each message type corresponds to a main message table;

[0007] Each message master table is partitioned according to a preset partitioning rule to obtain a certain number of partition tables, and the partition tables are numbered.

[0008] When inserting message data, the corresponding message master table is determined according to the message type of the message data, and the user code of each user who logs into the database table is obtained. The user code of each user is subjected to Hash remainder calculation, and the partition table number corresponding to each user in the message master table is determined according to the value of the Hash remainder. The message data of each user is inserted into the corresponding partition table according to the partition table number.

[0009] When querying the message data, the corresponding partition table is found based on the user code, and the message data is queried in the partition table.

[0010] The technical solution adopted in this application embodiment also includes: the message type includes task to be done, task reminder or system message.

[0011] The technical solution adopted in this application embodiment further includes: partitioning each message master table according to preset partitioning rules, which includes:

[0012] Obtain the user code (user_code) and the total number of logins to the database table for each user within a set time period;

[0013] Based on the total number of logins, all users are divided into activity levels according to the set login frequency range threshold, and the number of user activity levels is 9.

[0014] Each message main table is partitioned according to the number of user activity levels, and the partition tables in each message main table are numbered from 1 to 9.

[0015] The technical solution adopted in this application embodiment further includes: after partitioning each message master table according to the preset partitioning rules, it also includes:

[0016] Based on the user activity level, each partition table is divided into levels, resulting in a sub-partition table for each activity level in each partition table.

[0017] The technical solution adopted in this application embodiment further includes: the specific steps of inserting each user's message data into the corresponding partition table according to the partition table number are as follows:

[0018] The partition table corresponding to each user in the main message table is determined based on the remainder value of the hash, and the sub-partition table corresponding to each user in the main message table is found based on the user activity level of each user. The message data of each user is then inserted into the sub-partition table.

[0019] The hash modulo formula is: MD5(user_code) / mod number of partition tables, where MD5 is used to evenly distribute users of different activity levels across the partition tables.

[0020] The technical solution adopted in this application embodiment further includes: finding the corresponding partition table based on the user code and querying message data in the partition table specifically involves:

[0021] The user code is calculated using the hash modulo formula. The corresponding partition table is retrieved based on the hash modulo value. The corresponding sub-partition table is retrieved based on the user's activity level. Message data is then retrieved from the sub-partition table.

[0022] The technical solution adopted in this application embodiment further includes: after finding the corresponding partition table according to the user code and querying the message data in the partition table, it further includes:

[0023] The JOB (Job) scheduled task is used to periodically delete historical message data in the sub-partition table that has exceeded a set time, thereby cleaning up the data in the sub-partition table.

[0024] Another technical solution adopted in this application embodiment is: a message data partition query device, comprising:

[0025] The first partitioning module is used to partition the database tables according to message type, so that each message type corresponds to a main message table.

[0026] The second partitioning module is used to partition each message master table according to preset partitioning rules, obtain a certain number of partition tables, and number the partition tables.

[0027] Data insertion module: When inserting message data, it determines the corresponding message master table according to the message type of the message data, obtains the user code of each user who logs into the database table, performs a hash remainder calculation on the user code of each user, determines the partition table number corresponding to each user in the message master table according to the hash remainder value, and inserts the message data of each user into the corresponding partition table according to the partition table number.

[0028] Data query module: used to query the message data, find the corresponding partition table based on the user code, and perform message data query in the partition table.

[0029] Another technical solution adopted in this application embodiment is: a computer device, the computer device comprising:

[0030] A memory that stores executable program instructions;

[0031] The processor connected to the memory;

[0032] The processor calls the executable program instructions stored in the memory to execute the message data partition query method described above.

[0033] Another technical solution adopted in this application embodiment is: a storage medium storing processor-executable program instructions, the program instructions being used to execute the above-described message data partition query method.

[0034] This application's message data partition query method, apparatus, computer device, and storage medium propose an optimized scheme for partition querying large volumes of message data. It performs table partitioning and sharding operations on a database table based on message type and user code, thereby inserting user message data into the corresponding partition. When querying data, the user finds the corresponding partition and shard based on the user code, thus performing the message data query. This application's embodiments can reasonably split large volumes of database tables into partitions, distributing large volumes of message data across different partition tables. This improves message data query efficiency, enhances the overall performance of the database table, and reduces the overhead of database table queries. Attached Figure Description

[0035] Figure 1 This is a flowchart illustrating the message data partition query method according to the first embodiment of this application;

[0036] Figure 2 This is a flowchart illustrating the message data partition query method according to the second embodiment of this application;

[0037] Figure 3 This is a schematic diagram of the structure of the message data partition query device according to an embodiment of this application;

[0038] Figure 4 This is a schematic diagram of the computer device structure according to an embodiment of this application;

[0039] Figure 5 This is a schematic diagram of the storage medium structure according to an embodiment of this application. Detailed Implementation

[0040] The technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only a part of the embodiments of this application, and not all of the embodiments. Based on the embodiments of this application, all other embodiments obtained by those of ordinary skill in the art without creative effort are within the scope of protection of this application.

[0041] The terms "first," "second," and "third" in this application are for descriptive purposes only and should not be construed as indicating or implying relative importance or implicitly specifying the number of indicated technical features. Therefore, a feature defined as "first," "second," or "third" may explicitly or implicitly include at least one of that feature. In the description of this application, "multiple" means at least two, such as two, three, etc., unless otherwise explicitly specified. All directional indications (such as up, down, left, right, front, back, etc.) in the embodiments of this application are only used to explain the relative positional relationships and movements between components in a specific orientation (as shown in the figures). If the specific orientation changes, the directional indications also change accordingly. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion. For example, a process, method, system, product, or device that includes a series of steps or units is not limited to the listed steps or units, but may optionally include steps or units not listed, or may optionally include other steps or units inherent to these processes, methods, products, or devices.

[0042] In this document, the term "embodiment" means that a particular feature, structure, or characteristic described in connection with an embodiment may be included in at least one embodiment of this application. The appearance of this phrase in various places throughout the specification does not necessarily refer to the same embodiment, nor is it a separate or alternative embodiment mutually exclusive with other embodiments. It will be explicitly and implicitly understood by those skilled in the art that the embodiments described herein can be combined with other embodiments.

[0043] Please see Figure 1 This is a flowchart illustrating the message data partition query method of the first embodiment of this application. The message data partition query method of the first embodiment of this application includes the following steps:

[0044] S100: Perform table partitioning on the database table according to the message type, so that each message type corresponds to a separate message master table;

[0045] S110: Partition each message master table according to the preset partitioning rules to obtain a certain number of partition tables, and number the partition tables.

[0046] S120: When inserting message data, determine the corresponding message master table according to the message type of the message data, obtain the user code of each user who logs into the database table, perform a hash remainder calculation on the user code of each user, determine the partition table number corresponding to each user in the message master table according to the hash remainder value, and insert the message data of each user into the corresponding partition table according to the partition table number.

[0047] S130: When querying the message data, the corresponding partition table is found according to the user code, and the message data is queried in the partition table.

[0048] Based on the above, the message data partitioning query method of the first embodiment of this application performs table partitioning and sharding operations on the database table according to the message type and user code, thereby inserting the user's message data into the corresponding partition. When querying data, the user finds the corresponding partition and sharding based on the user code, thus performing the message data query. This embodiment of the application can reasonably split large-volume database tables into partitions, thereby distributing large-volume message data across different partition tables. This improves message data query efficiency, enhances the overall performance of the database table, and reduces the query overhead on the database table.

[0049] Please see Figure 2 This is a flowchart illustrating the message data partition query method according to the second embodiment of this application. The message data partition query method according to the second embodiment of this application includes the following steps:

[0050] S200: Obtain the database table used to store message data;

[0051] S210: Perform table partitioning on the database table according to the message type, so that each message type corresponds to a separate message master table;

[0052] In this step, message types include, but are not limited to, task to be done, task reminders, or system messages. Since different message types have different levels of importance, the number of times users access message data also varies. In this embodiment, the database table is partitioned according to the message type, so that each message type corresponds to a message master table. When querying message data, the corresponding message master table can be queried according to the message type, which helps to distribute the access traffic of the entire database table.

[0053] S220: Obtain the user code and the total number of logins to the database table for each user within a set time period, and classify all users into activity levels based on the total number of logins and the set login frequency range threshold.

[0054] In this step, the user code (user_code) refers to the unique code for each user logging into the database table, and the total login count refers to the cumulative number of times each user logs into the database table within a set time period. The higher the total login count, the higher the user's activity level. In this embodiment, the login count interval thresholds for dividing user activity levels and the number of activity levels can be set according to the actual application scenario. The number of login count interval thresholds is the same as the number of activity levels. For example, if the number of user activity levels (user_level) is set to 9 (that is, all users are divided into 9 activity levels), then the login count interval thresholds corresponding to the 9 activity levels need to be set separately. Assuming that the first activity level is the highest activity level and the ninth activity level is the lowest activity level, then among the corresponding 9 login count interval thresholds, the first login count interval threshold is the largest. When a user's total login count is within the first login count interval threshold, the user is classified as the first activity level. The ninth login count interval threshold is the smallest. When a user's total login count is within the ninth login count interval threshold, the user is classified as the ninth activity level, and so on, to complete the activity level division for all users.

[0055] S230: Partition each message master table according to the preset partitioning rules to obtain a certain number of partition tables, and divide each partition table into levels according to the user's activity level to obtain a sub-partition table for each activity level in each partition table.

[0056] In this step, the partitioning rule for each message master table is as follows: Each message master table is partitioned based on the number of user activity levels, with the number of partition tables corresponding to the number of user activity level divisions. For example, if there are 9 user activity levels, each message master table is divided into 9 partitions, resulting in 9 partition tables for each message master table. These 9 partition tables are numbered from 1 to 9. Then, each partition table is further divided according to user activity level, resulting in sub-partition tables corresponding to different activity levels within each of the 9 partition tables in the message master table. This facilitates inserting message data from users of the same message type and activity level into the same sub-partition table and prioritizing message data queries based on user activity level. It can be understood that the number of partitions in the message master table can also be set based on the total amount of message data within a specified time interval.

[0057] S240: When inserting message data, determine the corresponding message master table according to the message type of the message data, perform a hash remainder calculation on the user code according to the number of active level divisions, determine the partition table corresponding to the user in the message master table according to the hash remainder value, and insert the user's message data into the sub-partition table of the corresponding partition table according to the user's active level.

[0058] In this step, a hash modulo operation is performed on the user code. The remainder value after hashing determines the partition table number to which the message data needs to be inserted. Since the user code is unique, the hash modulo value is also unique; therefore, the partition table number corresponding to that user is also unique. For example, assuming the message type of user A with an activity level of 1 is "task assignment," and the remainder after hashing their user code is 1, the main task assignment message table is first located, and then the partition table with the number 1 is found within the main task assignment message table. Finally, user A's message data is inserted into the sub-partition table corresponding to the 1st activity level within the partition table with the number 1 in the main task assignment message table. Specifically, the hash modulo calculation formula is: MD5(user_code) / mod number of partition tables. MD5 is used to ensure a more even distribution of message data, allowing users with different activity levels to be evenly distributed across various partition tables. In this embodiment of the application, since the remainder value after performing a hash modulo operation on the user code based on the number of active level divisions includes 1 to 9, in order to ensure that each hash remainder value can find a corresponding partition table, it is preferable to set the number of partitions in the partition table to 9.

[0059] S250: When a message data query request initiated by a user is received, the corresponding message master table is queried according to the message type, the corresponding partition table is queried according to the user code, and the corresponding sub-partition table is queried according to the user's activity level, and the message data in the sub-partition table is returned to the user.

[0060] In this step, when querying message data, the user code is also calculated using the hash remainder formula. The corresponding partition table is retrieved based on the hash remainder value, and then the corresponding sub-partition table is retrieved based on the user's activity level to obtain the message data that the user needs to query. In this embodiment, when querying message data, a priority can also be set according to the user's activity level. That is, users with higher activity levels are given priority query permissions, and message data of users with lower activity levels (inactive users) is deleted, thereby reducing database memory usage.

[0061] S260: Use the scheduled task JOB to periodically delete historical message data in the sub-partition table that exceeds the set time, and clean up the data in the sub-partition table;

[0062] In this step, message data in the partition table can only be inserted and deleted, not updated. To avoid a large amount of expired data consuming memory, this embodiment of the application uses a scheduled task (JOB) to delete historical message data in the partition table that has exceeded a set time, thereby cleaning up the partition table. Specifically, message data between different partition tables flows through the scheduled task according to hash rules. As the number of user logins accumulates, the user's activity level changes accordingly, and message data also migrates between different sub-partition tables as the user's activity level changes. That is, the scheduled task (JOB) is triggered to transfer message data from low-activity to high-activity data tables during idle periods.

[0063] Based on the above, the message data partitioning query method of the second embodiment of this application proposes an optimized scheme for partitioning query of large-volume message data. It performs table partitioning operations on the database table according to message type, so that each message type corresponds to a main message table. Then, it partitions each main message table according to the number of user activity level divisions, obtaining partition tables corresponding to the number of activity level divisions. Furthermore, it divides each partition table according to user activity level, obtaining sub-partition tables for each activity level in each partition table. Then, it performs a hash modulo calculation on the user code and total login count based on the number of activity level divisions. The hash modulo value determines the corresponding partition table for each user in different main message tables. Finally, it inserts the user's message data into the corresponding sub-partition table according to the user's activity level. When querying data, the user finds the corresponding sub-partition table based on the user code and activity level, thereby querying the message data. This embodiment of the application can reasonably partition large-volume database tables, distributing large-volume message data across different sub-partition tables according to the hash of user code and user activity level. This improves message data query efficiency, enhances the overall performance of the database table, and reduces the query overhead on the database table.

[0064] In an optional implementation, the results of the message data partition query method can also be uploaded to the blockchain.

[0065] Specifically, the corresponding digest information is obtained based on the result of the aforementioned message data partition query method. More specifically, the digest information is obtained by hashing the result of the message data partition query method, for example, using the SHA256s algorithm. Uploading the digest information to the blockchain ensures its security and fairness and transparency to users. Users can download the digest information from the blockchain to verify whether the result of the message data partition query method has been tampered with. The blockchain referred to in this example is a new application model of computer technologies such as distributed data storage, peer-to-peer transmission, consensus mechanisms, and encryption algorithms. A blockchain is essentially a decentralized database, a chain of data blocks linked using cryptographic methods. Each data block contains information about a batch of network transactions, used to verify the validity of the information (anti-counterfeiting) and generate the next block. A blockchain can include a blockchain underlying platform, a platform product service layer, and an application service layer.

[0066] Please see Figure 3 This is a schematic diagram of the structure of the message data partition query device according to an embodiment of this application. The message data partition query device 40 according to an embodiment of this application includes:

[0067] The first partitioning module 41 is used to partition the database table according to the message type, so that each message type corresponds to a message master table. The message types include, but are not limited to, task to be done, task reminder, or system message. Since different message types have different levels of importance, the number of times users access message data is also different. In this embodiment of the application, the database table is partitioned according to the message type, so that each message type corresponds to a message master table. When querying message data, the corresponding message master table can be queried according to the message type, which helps to distribute the access traffic of the entire database table.

[0068] The second partitioning module 42 is used to partition each message master table according to preset partitioning rules, obtaining a certain number of partition tables, and numbering the partition tables. Specifically, the partitioning method of the second partitioning module is as follows: Each user's user code and the total number of logins to the database table within a set time period are obtained. Based on the total number of logins, all users are divided into activity levels according to a set login count interval threshold. Then, each message master table is partitioned according to the number of user activity levels, obtaining a certain number of partition tables. Each partition table is then divided into levels according to the user activity level, resulting in a sub-partition table for each activity level in each partition table. Here, the user code (user_code) refers to the unique code for each user logging into the database table, and the total number of logins refers to the cumulative number of logins by each user within a set time period. A higher total number of logins indicates a higher user activity level. In this embodiment, the login count interval threshold for dividing user activity levels and the number of activity levels can be set according to the actual application scenario. The number of login count interval thresholds is the same as the number of activity level divisions. For example, if the number of user activity levels (user_level) is set to 9 (i.e., all users are divided into 9 activity levels), then a login count interval threshold needs to be set for each of the 9 activity levels. Assuming the first activity level is the highest and the ninth activity level is the lowest, then among the 9 login count interval thresholds, the first login count interval threshold is the largest; when a user's total login count falls within the first login count interval threshold, the user is classified as the first activity level. The ninth login count interval threshold is the smallest; when a user's total login count falls within the ninth login count interval threshold, the user is classified as the ninth activity level, and so on, completing the activity level classification for all users. Assuming there are 9 user activity levels, each main message table is divided into 9 partitions, resulting in 9 partition tables for each main message table. These partition tables are numbered from 1 to 9. Then, each partition table is further divided into sub-partition tables based on user activity levels, resulting in sub-partition tables corresponding to different activity levels within each of the 9 partition tables. This allows for the insertion of message data from users of the same message type and activity level into the same sub-partition table, and prioritizes message data queries based on user activity levels. It can be understood that the number of partitions in the main message table can also be set based on the total amount of message data within a defined time interval.

[0069] Data insertion module 43: When inserting message data, it determines the corresponding message master table based on the message type of the message data, obtains the user code of each user logging into the database table, performs a hash modulo calculation on the user code of each user, determines the partition table number corresponding to each user in the message master table based on the hash modulo value, and inserts the message data of each user into the corresponding partition table based on the partition table number. Specifically, the data insertion method of the data insertion module includes: determining the corresponding message master table based on the message type of the message data, performing a hash modulo calculation on the user code based on the number of activity levels, determining the partition table corresponding to the user in the message master table based on the hash modulo value, and inserting the user's message data into the sub-partition table of the corresponding partition table based on the user's activity level. By performing a hash modulo calculation on the user code, the partition table number of the partition table into which the message data needs to be inserted is determined based on the hash modulo value. Since the user code is unique, and the hash modulo value is also unique, the partition table number corresponding to the user is also unique. For example, assuming the message type of user A's message data is "task assignment," and the remainder after hashing their user code is 1, the task assignment message master table is first located, and then the partition table numbered 1 is found within that table. User A's message data is then inserted into the sub-partition table corresponding to the first activity level within the partition table numbered 1 of the task assignment message master table. Specifically, the hash remainder calculation formula is: MD5(user_code) / mod number of partition tables. MD5 is used to ensure a more even distribution of message data, allowing users of different activity levels to be evenly distributed across the partition tables. In this embodiment, since the remainder value after hashing the user code based on the number of activity levels ranges from 1 to 9, to ensure that each hash remainder value can find a corresponding partition table, it is preferable to set the number of partitions in the partition table to 9.

[0070] Data query module 44: Used to query the message data, locate the corresponding partition table based on the user code, and perform message data query in the partition table. Specifically, when querying message data, the user code is also hashed using the hash remainder calculation formula. The corresponding partition table is retrieved based on the hash remainder value, and then the corresponding sub-partition table is retrieved based on the user's activity level to obtain the message data the user needs to query. In this embodiment, when querying message data, a priority can also be set according to the user's activity level. That is, users with higher activity levels are given priority query permissions, and message data of users with lower activity levels (inactive users) is deleted, thereby reducing database memory usage.

[0071] This application's embodiment of the message data partition query device proposes an optimized scheme for partition querying large-volume message data. It performs table partitioning and sharding operations on the database table based on message type, user code, and user activity level. Then, it inserts the user's message data into the corresponding partitions based on the user code and activity level. When querying data, the user finds the corresponding partition based on their user code and activity level, thereby performing the message data query. This embodiment of the application can reasonably split large-volume database tables into partitions, distributing large-volume message data across different sub-partition tables according to the hash of user code and user activity level. This improves message data query efficiency, enhances the overall performance of the database table, and reduces the query overhead on the database table.

[0072] Please see Figure 4 This is a schematic diagram of a computer device structure according to an embodiment of this application. The computer device 50 includes:

[0073] Memory 51 storing executable program instructions;

[0074] Processor 52 connected to memory 51;

[0075] The processor 52 is used to call the executable program instructions stored in the memory 51 and perform the following steps: partitioning the database table according to the message type, so that each message type corresponds to a main message table; partitioning each main message table according to a preset partitioning rule to obtain a certain number of partition tables, and numbering the partition tables; when inserting message data, determining the corresponding main message table according to the message type of the message data, obtaining the user code of each user logging into the database table, performing a hash modulo calculation on the user code of each user, determining the partition table number corresponding to each user in the main message table according to the hash modulo value, and inserting the message data of each user into the corresponding partition table according to the partition table number; when querying the message data, finding the corresponding partition table according to the user code, and querying the message data in the partition table.

[0076] The processor 52 can also be referred to as a CPU (Central Processing Unit). The processor 52 may be an integrated circuit chip with signal processing capabilities. The processor 52 can also be a general-purpose processor, a digital signal processor (DSP), an application-specific integrated circuit (ASIC), an off-the-shelf programmable gate array (FPGA), or other programmable logic devices, discrete gate or transistor logic devices, or discrete hardware components. A general-purpose processor can be a microprocessor or any conventional processor.

[0077] This application proposes a partitioned query optimization scheme for large-volume message data. The scheme involves partitioning a database table based on message type and user code, inserting the user's message data into the corresponding partition. When querying data, the user locates the corresponding partition based on their user code, thus retrieving the message data. This application can rationally partition a large-volume database table, distributing the large amount of message data across different partition tables. This improves message data query efficiency, enhances the overall performance of the database table, and reduces the overhead of database table queries.

[0078] Please see Figure 5 , Figure 5 This is a schematic diagram of the structure of the storage medium in an embodiment of this application. The storage medium in this embodiment stores program instructions 61 capable of implementing the following steps: performing table partitioning operations on the database table according to the message type, so that each message type corresponds to a message master table; partitioning each message master table according to a preset partitioning rule to obtain a certain number of partition tables, and numbering the partition tables; when inserting message data, determining the corresponding message master table according to the message type of the message data, obtaining the user code of each user logging into the database table, performing a hash remainder calculation on the user code of each user, determining the partition table number corresponding to each user in the message master table according to the hash remainder value, and inserting the message data of each user into the corresponding partition table according to the partition table number; when querying the message data, finding the corresponding partition table according to the user code, and performing message data query in the partition table. The program instructions 61 can be stored in the above-mentioned storage medium in the form of a software product, including several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) or processor to execute all or part of the steps of the methods of various embodiments of this application. The aforementioned storage media include: USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks or optical disks, and other media that can store program instructions, or terminal devices such as computers, servers, mobile phones, and tablets.

[0079] This application proposes a partitioned query optimization scheme for large-volume message data. The database table is partitioned based on message type and user code, allowing user message data to be inserted into the corresponding partition. When querying data, the user locates the corresponding partition based on their user code, thus retrieving the message data. This application can rationally partition large-volume database tables, distributing large amounts of message data across different partitioned tables. This improves message data query efficiency, enhances overall database table performance, and reduces query overhead.

[0080] In the several embodiments provided in this application, it should be understood that the disclosed systems, apparatuses, and methods can be implemented in other ways. For example, the system embodiments described above are merely illustrative; for instance, the division of units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be an indirect coupling or communication connection through some interfaces, apparatuses, or units, and may be electrical, mechanical, or other forms.

[0081] Furthermore, the functional units in the various embodiments of this application can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated units described above can be implemented in hardware or as software functional units. The above are merely embodiments of this application and do not limit the patent scope of this application. Any equivalent structural or procedural transformations made based on the description and drawings of this application, or direct or indirect applications in other related technical fields, are similarly included within the patent protection scope of this application.

Claims

1. A method for querying message data partitions, characterized in that, include: The database tables are partitioned according to message type, so that each message type corresponds to a main message table; Each message master table is partitioned according to a preset partitioning rule to obtain a certain number of partition tables, and the partition tables are numbered; wherein, the partitioning of each message master table according to the preset partitioning rule includes: Obtain the user code (user_code) and the total number of logins to the database table for each user within a set time period; Based on the total number of logins, all users are divided into activity levels according to the set login count interval threshold, and the number of user activity levels is 9. The message main table is partitioned according to the number of user activity levels, and the partition tables in each message main table are numbered from 1 to 9. Based on the user activity level, each partition table is divided into levels, resulting in a sub-partition table for each activity level in each partition table; When inserting message data, the corresponding main message table is determined according to the message type of the message data, and the user code of each user who logs into the database table is obtained. A hash modulo operation is performed on the user code of each user, and the partition table corresponding to each user in the main message table is determined according to the value of the hash modulo operation. Then, the sub-partition table corresponding to each user in the partition table is found according to the user activity level of each user, and the message data of each user is inserted into the sub-partition table. The hash modulo operation formula is: MD5(user_code) / mod number of partition tables. MD5 is used to ensure that users with different activity levels are evenly distributed among the partition tables. When querying the message data, the corresponding partition table is found based on the user code, and the corresponding sub-partition table is found in the partition table based on the user activity level. Message data is then queried in the sub-partition table.

2. The message data partition query method according to claim 1, characterized in that, The message types include tasks to be done, task reminders, or system messages.

3. The message data partition query method according to claim 1, characterized in that, The step of finding the corresponding partition table based on the user code and querying message data in the partition table further includes: The JOB (Job) scheduled task is used to periodically delete historical message data in the sub-partition table that has exceeded a set time, thereby cleaning up the data in the sub-partition table.

4. A message data partition query device, used to implement the message data partition query method as described in any one of claims 1-3, characterized in that, include: The first partitioning module is used to partition the database tables according to message type, so that each message type corresponds to a main message table. The second partitioning module is used to partition each message master table according to preset partitioning rules, obtain a certain number of partition tables, and number the partition tables. Data insertion module: When inserting message data, it determines the corresponding message master table according to the message type of the message data, obtains the user code of each user who logs into the database table, performs a hash remainder calculation on the user code of each user, determines the partition table number corresponding to each user in the message master table according to the hash remainder value, and inserts the message data of each user into the corresponding partition table according to the partition table number. Data query module: used to query the message data, find the corresponding partition table based on the user code, and perform message data query in the partition table.

5. A computer device, characterized in that, The computer device includes: A memory that stores executable program instructions; The processor connected to the memory; The processor calls the executable program instructions stored in the memory to execute the message data partition query method as described in any one of claims 1-3.

6. A storage medium storing processor-executable program instructions, characterized in that, The program instructions are used to execute the message data partition query method according to any one of claims 1 to 3.

Images