Multi-dimensional query method and device for sub-library and sub-table data, electronic equipment and medium

Abstract

The invention discloses a sub-library and sub-table data multi-dimensional query method and device, electronic equipment and a medium, and the method comprises the steps: obtaining first order data, and adding the first order data to a preset first message queue; determining a first fragmentation key according to the user id of the first order data, and storing the first order data in a user order table of a corresponding order library according to the first fragmentation key; a second fragmentation key is determined according to the merchant id of the first order data, and the first order data is redundantly stored in a merchant order table of a corresponding order library according to the second fragmentation key; obtaining a to-be-queried user id, and querying a user order table of a corresponding order library according to the to-be-queried user id to obtain user order query data; and obtaining a to-be-queried merchant id, and querying the merchant order table of the corresponding order library according to the to-be-queried merchant id to obtain merchant order query data. The method improves the efficiency of data multi-dimensional query under the scene of database division and table division, and can be widely applied to the technical field of data processing.

Description

Technical Field

[0001] The present invention relates to the field of data processing technology, and in particular to a method, device, electronic equipment and medium for multi-dimensional query of sub-library and sub-table data. Background Art

[0002] In business scenarios such as Internet e-commerce and SMS link marketing, there will be high concurrent requests and massive data generation. For this scenario, sharding is an unavoidable technical solution. According to the characteristics of the business system, the sharding key is reasonably selected to shard the table, so that the data can be routed to the corresponding library table as evenly as possible, which can support the rapid location and retrieval of data.

[0003] Although the technical solution of sharding databases and tables under massive data can deal with the bottleneck of database connections under high concurrency and solve the performance problem of large-scale data query, it also brings many problems such as distributed transactions and queries of different dimensions. For example, in the scenario of sharding databases and tables for e-commerce orders, if the sharding key of the user order table is the user ID, when the user queries the order data, it can be directly parsed and routed to the corresponding table according to the sharding key algorithm of the user ID, but it is very troublesome for the platform merchants to view all the orders of their own stores, because the user order data of the merchant is distributed in different tables, and the query needs to traverse the entire table, which affects the query performance. Not only e-commerce business, but also recruitment websites have similar scenarios. If the interview record table is sharded according to the hash rule of the user ID, job seekers can accurately route to the corresponding table through the user ID when viewing all their interview records, but when the company wants to view the interview record of a certain position, it cannot parse which table the data is in, and also faces the problem of traversing the entire table.

[0004] From the above, we can see that in the existing sharding scenario, there is a problem of low efficiency when querying data from multiple dimensions.

[0005] Terminology explanation:

[0006] Sub-library and table sharding: a strategy for relational database management and design to deal with the access pressure and query performance issues caused by high concurrent access volume and data volume.

[0007] Shard key: For commonly used relational databases, a database table field used to split the database (table) horizontally, which determines whether the data will be routed to a specific shard.

[0008] Sharding algorithm: Based on the value of the sharding key, the algorithm rules are used to calculate which physical shard the data should be routed to. Common algorithms include range sharding, hash sharding, and rule mapping sharding. Summary of the invention

[0009] The purpose of the present invention is to solve one of the technical problems existing in the prior art to at least a certain extent.

[0010] To this end, an object of an embodiment of the present invention is to provide a method for multi-dimensional query of data in sharded libraries and tables, which improves the efficiency of multi-dimensional query of data in sharded libraries and tables scenarios.

[0011] Another object of an embodiment of the present invention is to provide a multi-dimensional query device for sub-library and sub-table data.

[0012] In order to achieve the above technical objectives, the technical solutions adopted by the embodiments of the present invention include:

[0013] On the one hand, an embodiment of the present invention provides a multi-dimensional query method for sub-library and sub-table data, comprising the following steps:

[0014] Acquire first order data, and add the first order data to a preset first message queue;

[0015] Determine a first sharding key according to the user ID of the first order data, and store the first order data in a user order table of a corresponding order library according to the first sharding key;

[0016] Determine a second sharding key according to the merchant ID of the first order data, and redundantly store the first order data in a merchant order table of a corresponding order library according to the second sharding key;

[0017] In response to a user order query request from a user terminal, obtaining a user ID to be queried, querying the user order table of the corresponding order library according to the user ID to be queried, and obtaining user order query data;

[0018] In response to a merchant order query request from the merchant end, the merchant ID to be queried is obtained, and the merchant order table of the corresponding order library is queried according to the merchant ID to be queried to obtain the merchant order query data.

[0019] Further, in one embodiment of the present invention, determining a first sharding key according to the user ID of the first order data, and storing the first order data in a user order table of a corresponding order library according to the first sharding key specifically includes:

[0020] Monitoring the first message queue through a first consumer service;

[0021] When it is monitored that the first order data is added to the first message queue, the first sharding key is determined according to the user ID of the first order data;

[0022] A shard key value operation is performed according to the first shard key and a preset shard algorithm to determine a first physical shard corresponding to the first order data, and then the first order data is stored in the user order table of the corresponding order library according to the position of the first physical shard.

[0023] Further, in one embodiment of the present invention, determining a second sharding key according to the merchant ID of the first order data, and redundantly storing the first order data in a merchant order table of a corresponding order library according to the second sharding key, specifically includes:

[0024] Monitor the first message queue through a second consumer service;

[0025] When it is monitored that the first order data is added to the first message queue, the second sharding key is determined according to the merchant ID of the first order data;

[0026] A shard key value operation is performed according to the second shard key and a preset shard algorithm to determine the second physical shard corresponding to the first order data, and then the first order data is redundantly stored in the merchant order table of the corresponding order library according to the position of the second physical shard.

[0027] Further, in one embodiment of the present invention, in response to a user order query request from a user terminal, obtaining a user ID to be queried, querying the user order table of the corresponding order library according to the user ID to be queried, and obtaining user order query data, specifically includes:

[0028] When the first consumer service receives the user order query request from the user end, the first consumer service determines the user ID to be queried according to the user order query request, and determines the third sharding key according to the user ID to be queried;

[0029] A shard key value operation is performed according to the third shard key and the sharding algorithm to determine the third physical shard corresponding to the user ID to be queried, and then a query is performed in the user order table of the corresponding order library according to the position of the third physical shard to obtain the user order query data.

[0030] Further, in one embodiment of the present invention, in response to the merchant order query request of the merchant end, obtaining the merchant ID to be queried, querying the merchant order table of the corresponding order library according to the merchant ID to be queried, and obtaining the merchant order query data, which specifically includes:

[0031] When the second consumer service receives the merchant order query request from the merchant end, the merchant ID to be queried is determined according to the merchant order query request, and the fourth sharding key is determined according to the merchant ID to be queried;

[0032] A shard key value operation is performed according to the fourth shard key and the shard algorithm to determine the fourth physical shard corresponding to the merchant ID to be queried, and then a query is performed in the merchant order table of the corresponding order library according to the position of the fourth physical shard to obtain the merchant order query data.

[0033] Furthermore, in one embodiment of the present invention, the first order data in the first message queue is stored in the user order table and the merchant order table through a confirmation response mechanism and a timeout retransmission mechanism.

[0034] Furthermore, in one embodiment of the present invention, the multi-dimensional query method for sub-library and sub-table data further includes the following steps:

[0035] Monitoring the backlogged messages in the first message queue through the alarm platform;

[0036] When it is monitored that there is a backlog of messages in the first message queue and that they cannot be consumed normally, the backlogged second order data is obtained through the alarm platform, and the second order data is synchronized to the user order table and the merchant order table of the corresponding order library.

[0037] On the other hand, an embodiment of the present invention provides a multi-dimensional query device for sub-library and sub-table data, including:

[0038] A data acquisition module, used to acquire first order data and add the first order data to a preset first message queue;

[0039] A first shard storage module, configured to determine a first shard key according to a user ID of the first order data, and store the first order data in a user order table of a corresponding order library according to the first shard key;

[0040] A second sharding storage module, configured to determine a second sharding key according to the merchant ID of the first order data, and redundantly store the first order data in a merchant order table of a corresponding order library according to the second sharding key;

[0041] The first order query module is used to respond to the user order query request of the user terminal, obtain the user ID to be queried, query the user order table of the corresponding order library according to the user ID to be queried, and obtain the user order query data;

[0042] The second order query module is used to respond to the merchant order query request of the merchant end, obtain the merchant ID to be queried, query the merchant order table of the corresponding order library according to the merchant ID to be queried, and obtain the merchant order query data.

[0043] On the other hand, an embodiment of the present invention provides an electronic device, comprising a memory, a processor, a program stored in the memory and executable on the processor, and a data bus for realizing connection and communication between the processor and the memory, wherein when the program is executed by the processor, the multi-dimensional query method for sub-library and sub-table data as described above is realized.

[0044] On the other hand, an embodiment of the present invention further provides a storage medium, which is a computer-readable storage medium used for computer-readable storage, and the storage medium stores one or more programs, and the one or more programs can be executed by one or more processors to implement the multi-dimensional query method for sub-library and sub-table data as described above.

[0045] The advantages and beneficial effects of the present invention will be partly given in the following description, partly become apparent from the following description, or be understood through the practice of the present invention:

[0046] An embodiment of the present invention obtains first order data and adds the first order data to a preset first message queue, determines a first sharding key according to a user id of the first order data, and stores the first order data in a user order table of a corresponding order library according to the first sharding key, determines a second sharding key according to a merchant id of the first order data, and redundantly stores the first order data in a merchant order table of a corresponding order library according to the second sharding key, responds to a user order query request from a user end, obtains a user id to be queried, queries the user order table of the corresponding order library according to the user id to be queried, and obtains user order query data, responds to a merchant order query request from a merchant end, obtains a merchant id to be queried, queries the merchant order table of the corresponding order library according to the merchant id to be queried, and obtains merchant order query data. The embodiment of the present invention uses a message queue to buffer data flow in a high-concurrency scenario, thereby ensuring the real-time response of the interface in a high-concurrency business scenario and reducing the instantaneous write pressure of the database; utilizing the confirmation response and timeout retry mechanism of the message queue, it is possible to ensure that the order data is reliably delivered to the database to a certain extent; the backlog of messages in the message queue can be monitored through a third-party alarm platform and data compensation processing can be performed to ensure the final consistency of the data; the first sharding key and the second sharding key of the order data are respectively determined by the user id and the merchant id, and the same order data is redundantly stored in the user order table and the merchant order table of the corresponding order library according to the first sharding key and the second sharding key, so that the user end and the merchant end can quickly match the corresponding user order table and the merchant order table according to the user id and the merchant id when performing order inquiries, without traversing the entire library table, thereby improving the efficiency of multi-dimensional data query in the scenario of sub-library and sub-table. BRIEF DESCRIPTION OF THE DRAWINGS

[0047] In order to more clearly illustrate the technical solution in the embodiments of the present invention, the following introduction is made to the drawings required for use in the embodiments of the present invention. It should be understood that the drawings introduced below are only for the convenience of clearly describing some embodiments of the technical solution of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative work.

[0048] Figure 1 A flowchart of the steps of the multi-dimensional query method for sub-library and sub-table data provided by an embodiment of the present invention;

[0049] Figure 2 It is a flowchart of a NOSQL redundant storage solution in the prior art;

[0050] Figure 3 A schematic diagram of the process of a multi-dimensional shard key data redundancy double-write solution provided by an embodiment of the present invention;

[0051] Figure 4 A step flow chart of step S102 provided in an embodiment of the present invention;

[0052] Figure 5 A step flow chart of step S103 provided in an embodiment of the present invention;

[0053] Figure 6 A step flow chart of step S104 provided in an embodiment of the present invention;

[0054] Figure 7 A step flow chart of step S105 provided in an embodiment of the present invention;

[0055] Figure 8 Another step flow chart of the multi-dimensional query method for sub-database and sub-table data provided by the embodiment of the present invention;

[0056] Fig. 9 A schematic diagram of the structure of a multi-dimensional query device for sub-library and sub-table data provided by an embodiment of the present invention;

[0057] Fig.10 A schematic diagram of the hardware structure of an electronic device provided by an embodiment of the present invention;

[0058] Fig.11 A schematic diagram of the structure of a storage medium provided in an embodiment of the present invention. DETAILED DESCRIPTION

[0059] Embodiments of the present invention are described in detail below, and examples of the embodiments are shown in the accompanying drawings, wherein the same or similar reference numerals throughout represent the same or similar elements or elements having the same or similar functions. The embodiments described below with reference to the accompanying drawings are exemplary and are only used to explain the present application, and cannot be understood as limitations on the present application. It should be noted that, although the functional module division is performed in the system schematic diagram and the logical order is shown in the flow chart, in some cases, the steps shown or described may be performed in a different order from the module division in the system schematic diagram or the flow chart. For the step numbers in the following embodiments, they are only set for the convenience of explanation, and the order between the steps is not limited in any way. The execution order of each step in the embodiment can be adaptively adjusted according to the understanding of those skilled in the art.

[0060] In the description of the present invention, the meaning of "a plurality" is two or more. If there is a description of the first or the second, it is only for the purpose of distinguishing the technical features, and it cannot be understood as indicating or implying the relative importance or implicitly indicating the number of the indicated technical features or implicitly indicating the order of the indicated technical features. In addition, unless otherwise defined, all technical and scientific terms used in this document have the same meaning as those commonly understood by technicians in the technical field of this application. The terms used in this document are only for the purpose of describing the embodiments of the present application and are not intended to limit the present application.

[0061] The multi-dimensional query method for sub-library and sub-table data provided in the embodiment of the present application can be applied to the terminal, can also be applied to the server side, and can also be software running in the terminal or the server side. In some embodiments, the terminal can be a smart phone, a tablet computer, a laptop computer, a desktop computer, a set-top box, etc.; the server side can be configured as an independent physical server, or a server cluster or distributed system composed of multiple physical servers, and can also be configured as a cloud server that provides basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communications, middleware services, domain name services, security services, CDN, and big data and artificial intelligence platforms; the software can be an application that implements the multi-dimensional query method for sub-library and sub-table data, etc., but is not limited to the above forms.

[0062] The present application can be used in many general or special computer system environments or configurations. For example: personal computers, server computers, handheld or portable devices, tablet devices, multiprocessor systems, microprocessor-based systems, set-top boxes, programmable consumer electronic devices, network PCs, minicomputers, mainframe computers, distributed computing environments including any of the above systems or devices, etc. The present application can be described in the general context of computer-executable instructions executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, etc. that perform specific tasks or implement specific abstract data types. The present application can also be practiced in distributed computing environments, in which tasks are performed by remote processing devices connected through a communication network. In a distributed computing environment, program modules can be located in local and remote computer storage media including storage devices.

[0063] It should be noted that in each specific implementation of the present application, when it comes to the need to perform relevant processing based on data related to user identity or characteristics such as user information, user behavior data, user historical data, and user location information, the user's permission or consent will be obtained first, and the collection, use, and processing of these data will comply with the relevant laws, regulations, and standards of the relevant countries and regions. In addition, when the embodiment of the present application needs to obtain the user's sensitive personal information, the user's separate permission or consent will be obtained through a pop-up window or by jumping to a confirmation page. After clearly obtaining the user's separate permission or consent, the necessary user-related data for the normal operation of the embodiment of the present application will be obtained.

[0064] In the existing technology, NOSQL redundant storage solutions are mostly used to solve the problem of multi-dimensional data retrieval, such as Figure 2 The figure shows a flow chart of a NOSQL redundant storage solution in the prior art, and the specific process is as follows:

[0065] 1) The order data generated when the user places an order uses user_id as the shard key to be routed to different order databases.

[0066] 2) Use MySQL's binlog log incremental consumption and subscription component canal to monitor data changes.

[0067] 3) To prevent synchronization problems caused by excessive data volume, the idea of ​​asynchronous peak shaving can be adopted to push the monitored data changes to the message queue for temporary storage.

[0068] 4) Write a message push delivery program to write data from the message queue to elasticsearch.

[0069] 5) Ordinary users can subsequently query their orders from the relational database through the shard key routing rules, and platform merchants can query all their orders by accessing elasticsearch.

[0070] The solution of this solution is to process data heterogeneously, organize the data in the relational database according to new dimensions, and synchronize the data in full to external storage in real time or near real time. This can effectively solve the performance query bottleneck caused by sharding. However, the disadvantage is that it requires the introduction of multiple external components, which increases the complexity and maintenance cost of the system.

[0071] like Figure 1 FIG. 1 is a flowchart of a multi-dimensional query method for sub-database and sub-table data provided by an embodiment of the present invention. Figure 1 The embodiment of the present invention provides a multi-dimensional query method for sub-library and sub-table data, which specifically includes the following steps:

[0072] S101, obtaining first order data, and adding the first order data to a preset first message queue;

[0073] S102: Determine a first sharding key according to the user ID of the first order data, and store the first order data in a user order table of a corresponding order library according to the first sharding key;

[0074] S103, determining a second sharding key according to the merchant ID of the first order data, and redundantly storing the first order data in a merchant order table of a corresponding order library according to the second sharding key;

[0075] S104, in response to the user order query request of the user terminal, obtaining the user ID to be queried, querying the user order table of the corresponding order library according to the user ID to be queried, and obtaining the user order query data;

[0076] S105 . In response to the merchant order query request from the merchant end, the merchant ID to be queried is obtained, and the merchant order table of the corresponding order library is queried according to the merchant ID to be queried to obtain the merchant order query data.

[0077] Specifically, the embodiment of the present invention adopts a multi-dimensional shard key data redundancy double-write solution to solve the multi-dimensional data retrieval problem in the sharding scenario, such as Figure 3 The figure is a flow chart of a multi-dimensional shard key data redundancy double-write solution provided by an embodiment of the present invention. The specific process is as follows:

[0078] 1) The order data generated when the user places an order is delivered to the MQ message queue. The purpose of this step is to solve the distributed transaction problem caused by the redundant double-write solution, because the same order data needs to be written to two copies of storage, and these two copies of data may be routed to different database tables. The message queue's confirmation response and timeout retry mechanism can ensure the reliable delivery of messages. If the message backlog cannot be consumed normally due to extreme circumstances, the alarm platform can monitor the backlog of messages and synchronize the data to the database for compensation processing.

[0079] 2) The C-end consumer service of ordinary users monitors order messages and routes them to the order library table of the corresponding C-end user based on the user ID as the shard key. At this time, it is necessary to ensure that the C-end consumer service handles the idempotence of the messages well.

[0080] 3) The platform user B-side consumer service monitors order messages and routes them to the corresponding B-side merchant's order library table based on the merchant ID as the shard key. At this time, it is necessary to ensure that the B-side consumer service handles the message idempotence well.

[0081] 4) When C-side users query, the user ID is used as the shard key to route the query. When B-side platform users query, the merchant ID is used as the shard key to route the query. Both can avoid the problem of full database table routing.

[0082] It can be recognized that the multi-dimensional shard key data redundant double-write solution of the present invention adopts the idea of ​​trading space for time. Although it requires more storage space, it solves the problems of high traffic concurrency and distributed transactions by only introducing message queue components, and data queries are all implemented by accessing relational databases, reducing the operation and maintenance costs of the system. Developers do not need to understand the lexical rules of elasticsearch, etc., thereby improving the efficiency of multi-dimensional data retrieval in the scenario of sharding libraries and tables.

[0083] like Figure 4 FIG. 1 is a flow chart of step S102 provided in an embodiment of the present invention, referring to FIG. Figure 4 As an optional implementation, a first sharding key is determined according to the user ID of the first order data, and the first order data is stored in the user order table of the corresponding order library according to the first sharding key, which specifically includes:

[0084] S1021. Monitor the first message queue through the first consumer service;

[0085] S1022: when it is monitored that the first order data is added to the first message queue, determine the first sharding key according to the user ID of the first order data;

[0086] S1023. Perform shard key value operation according to the first shard key and a preset shard algorithm to determine the first physical shard corresponding to the first order data, and then store the first order data in the user order table of the corresponding order library according to the position of the first physical shard.

[0087] Specifically, the MQ message queue is monitored through the order C-end consumer service. When the order data newly added to the MQ message queue is monitored, the first sharding key is determined according to its user id, and then the sharding key value operation is performed according to the preset sharding algorithm (such as range sharding, hash sharding, and rule mapping sharding, etc.) to determine the first physical shard to which the order data is routed. It should be noted that each order library in the embodiment of the present invention is provided with a user order table and a merchant order table. The order data is stored in the user order table of the corresponding order library according to the location of the first physical shard, so that the sharded storage of the order data in the user dimension can be completed.

[0088] like Figure 5 FIG. 1 is a flow chart of step S103 provided in an embodiment of the present invention, referring to FIG. Figure 5 As an optional implementation, a second sharding key is determined according to the merchant ID of the first order data, and the first order data is redundantly stored in the merchant order table of the corresponding order library according to the second sharding key, which specifically includes:

[0089] S1031. Monitor the first message queue through the second consumer service;

[0090] S1032: when it is monitored that the first order data is added to the first message queue, determine the second sharding key according to the merchant ID of the first order data;

[0091] S1033. Perform shard key value operation according to the second shard key and the preset shard algorithm to determine the second physical shard corresponding to the first order data, and then redundantly store the first order data in the merchant order table of the corresponding order library according to the position of the second physical shard.

[0092] Specifically, the MQ message queue is monitored through the B-end consumer service of the order. When the order data newly added to the MQ message queue is monitored, the second sharding key is determined according to its merchant ID, and then the sharding key value operation is performed according to the preset sharding algorithm (such as range sharding, hash sharding, and rule mapping sharding, etc.) to determine the second physical shard to which the order data is routed. The order data is stored in the merchant order table of the corresponding order library according to the location of the second physical shard, thereby completing the sharded storage of the order data in the merchant dimension.

[0093] like Figure 6 FIG. 1 is a flow chart of step S104 provided in an embodiment of the present invention, referring to FIG. Figure 6As an optional implementation, in response to a user order query request from the user end, the user ID to be queried is obtained, and the user order table of the corresponding order library is queried according to the user ID to be queried to obtain the user order query data, which specifically includes:

[0094] S1041. When the first consumer service receives a user order query request from the user terminal, the first consumer service determines a user ID to be queried according to the user order query request, and determines a third sharding key according to the user ID to be queried;

[0095] S1042. Perform shard key value calculation according to the third shard key and shard algorithm to determine the third physical shard corresponding to the user ID to be queried, and then perform query and search in the user order table of the corresponding order library according to the position of the third physical shard to obtain user order query data.

[0096] Specifically, when the C-end consumer service of the order receives a user order query request from the user end, it determines the user id to be queried according to the user order query request, and then determines the third sharding key, and then performs sharding key value operation according to the preset sharding algorithm (such as range sharding, hash sharding, and rule mapping sharding, etc.) to determine the third physical shard corresponding to the third sharding key. According to the position of the third physical shard, the user order table of the corresponding order library can be quickly located, so that the user order query data can be quickly retrieved according to the user id to be queried.

[0097] like Figure 7 FIG. 1 is a flow chart of step S105 provided in an embodiment of the present invention, referring to FIG. Figure 7 As an optional implementation, in response to a merchant order query request from the merchant end, the merchant ID to be queried is obtained, and the merchant order table of the order library corresponding to the merchant ID to be queried is queried to obtain the merchant order query data, which specifically includes:

[0098] S1051. When the second consumer service receives a merchant order query request from the merchant end, the merchant ID to be queried is determined according to the merchant order query request, and the fourth sharding key is determined according to the merchant ID to be queried;

[0099] S1052. Perform shard key value calculation according to the fourth shard key and shard algorithm to determine the fourth physical shard corresponding to the merchant ID to be queried, and then perform query and search in the merchant order table of the corresponding order library according to the position of the fourth physical shard to obtain merchant order query data.

[0100] Specifically, when the consumer service on the B side of the order receives a merchant order query request from the merchant side, it determines the merchant ID to be queried according to the merchant order query request, and then determines the fourth shard key, and then performs shard key value operations according to a preset sharding algorithm (such as range sharding, hash sharding, and rule mapping sharding, etc.) to determine the fourth physical shard corresponding to the fourth shard key. According to the position of the fourth physical shard, the merchant order table of the corresponding order library can be quickly located, so that the merchant order query data can be quickly retrieved according to the merchant ID to be queried.

[0101] As a further optional implementation, the first order data in the first message queue is stored in the user order table and the merchant order table through a confirmation response mechanism and a timeout retransmission mechanism.

[0102] Specifically, the embodiment of the present invention can ensure the reliable delivery of messages through the confirmation response mechanism and timeout retry mechanism of the message queue.

[0103] like Figure 8 FIG. 1 is another flowchart of a multi-dimensional query method for sub-database and sub-table data provided by an embodiment of the present invention. Figure 8 As an optional implementation, the multi-dimensional query method for sub-library and sub-table data further includes the following steps:

[0104] S106, monitoring the backlogged messages in the first message queue through the alarm platform;

[0105] S107: When it is monitored that there is a backlog of messages in the first message queue and that the messages cannot be consumed normally, the backlog of the second order data is obtained through the alarm platform, and the second order data is synchronized to the user order table and the merchant order table of the corresponding order library.

[0106] Specifically, when the alarm platform detects that there is a backlog of second order data in the MQ message queue, the second order data can be synchronized to the user order table and the merchant order table through the alarm platform. When synchronizing data, the shard key can also be calculated based on the user id and the merchant id, so as to determine the corresponding user order table and merchant order table based on the shard key for data synchronization.

[0107] The above is a description of the method steps of the embodiment of the present invention. It can be recognized that the embodiment of the present invention uses the message queue to perform data flow buffering in high-concurrency scenarios, ensuring the real-time response of the interface in the business high-concurrency scenario and reducing the instantaneous write pressure of the database; the confirmation response and timeout retry mechanism of the message queue can ensure that the order data is reliably delivered to the database to a certain extent; the backlog messages of the message queue can be monitored through the third-party alarm platform and data compensation processing can be performed to ensure the final consistency of the data; the first sharding key and the second sharding key of the order data are determined respectively by the user id and the merchant id, and the same order data is redundantly stored in the user order table and the merchant order table of the corresponding order library according to the first sharding key and the second sharding key, so that the user end and the merchant end can quickly match the corresponding user order table and the merchant order table according to the user id and the merchant id when performing order queries, without traversing the entire library table, thereby improving the efficiency of multi-dimensional data query in the sub-library and sub-table scenario.

[0108] Compared with the common NoSQL redundant storage solution in the industry, the embodiment of the present invention reduces the introduction of canal components and elasticsearch external components, reduces the complexity of the system, and optimizes the data synchronization link from canal monitoring binlog flow to message queue, and finally to elasticsearch to directly flow from message queue to relational database, reducing the delay of data reaching final consistency. In addition, the embodiment of the present invention increases the data storage space at the cost of data storage space, so that data can be stored in a structural form (relational database), rather than storing one copy in a relational database and another in a non-relational database, which greatly reduces the data maintenance cost, especially in the face of data migration in the future.

[0109] like Fig. 9 FIG. 1 is a schematic diagram of a multi-dimensional query device for sub-database and sub-table data provided by an embodiment of the present invention. Fig. 9 The embodiment of the present invention provides a multi-dimensional query device for sub-library and sub-table data, including:

[0110] A data acquisition module, used for acquiring first order data and adding the first order data to a preset first message queue;

[0111] A first shard storage module, used to determine a first shard key according to a user ID of the first order data, and store the first order data in a user order table of a corresponding order library according to the first shard key;

[0112] A second shard storage module, used to determine a second shard key according to the merchant ID of the first order data, and redundantly store the first order data in a merchant order table of a corresponding order library according to the second shard key;

[0113] The first order query module is used to respond to the user order query request of the user terminal, obtain the user ID to be queried, query the user order table of the corresponding order library according to the user ID to be queried, and obtain the user order query data;

[0114] The second order query module is used to respond to the merchant order query request from the merchant end, obtain the merchant ID to be queried, query the merchant order table of the corresponding order library according to the merchant ID to be queried, and obtain the merchant order query data.

[0115] The contents of the above method embodiments are all applicable to the present device embodiments. The functions specifically implemented by the present device embodiments are the same as those of the above method embodiments, and the beneficial effects achieved are also the same as those achieved by the above method embodiments.

[0116] The embodiment of the present invention also provides an electronic device, the electronic device comprising: a memory, a processor, a program stored in the memory and executable on the processor, and a data bus for realizing connection and communication between the processor and the memory, and the program is executed by the processor to realize the above-mentioned multi-dimensional query method for sub-library and sub-table data. The electronic device can be any intelligent terminal including a tablet computer, a vehicle-mounted computer, etc.

[0117] like Fig.10 FIG. 1 is a schematic diagram of the hardware structure of an electronic device provided in an embodiment of the present invention, referring to FIG. Fig.10 , an embodiment of the present invention provides an electronic device, including:

[0118] The processor 1001 may be implemented by a general-purpose CPU (Central Processing Unit), a microprocessor, an application-specific integrated circuit (ASIC), or one or more integrated circuits, and is used to execute relevant programs to implement the technical solutions provided in the embodiments of the present invention.

[0119] The memory 1002 can be implemented in the form of a read-only memory (ROM), a static storage device, a dynamic storage device, or a random access memory (RAM). The memory 1002 can store an operating system and other application programs. When the technical solution provided in the embodiments of this specification is implemented by software or firmware, the relevant program code is stored in the memory 1002, and the processor 1001 calls and executes the multi-dimensional query method for sub-library and sub-table data in the embodiments of the present invention;

[0120] Input / output interface 1003, used to implement information input and output;

[0121] The communication interface 1004 is used to realize the communication interaction between the device and other devices. The communication can be realized through a wired manner (such as USB, network cable, etc.) or a wireless manner (such as mobile network, WIFI, Bluetooth, etc.);

[0122] A bus 1005 , which transmits information between various components of the device (e.g., the processor 1001 , the memory 1002 , the input / output interface 1003 , and the communication interface 1004 );

[0123] The processor 1001 , the memory 1002 , the input / output interface 1003 and the communication interface 1004 are connected to each other in communication within the device via the bus 1005 .

[0124] like Fig.11 FIG. 1 is a schematic diagram of a storage medium according to an embodiment of the present invention. Fig.11 The embodiment of the present invention further provides a storage medium, which is a computer-readable storage medium used for computer-readable storage. The storage medium stores one or more programs 1101, and the one or more programs 1101 can be executed by one or more processors to implement the above-mentioned multi-dimensional query method for sub-library and sub-table data.

[0125] The memory, as a non-transient computer-readable storage medium, can be used to store non-transient software programs and non-transient computer executable programs. In addition, the memory may include a high-speed random access memory, and may also include a non-transient memory, such as at least one disk storage device, a flash memory device, or other non-transient solid-state storage device. In some embodiments, the memory may optionally include a memory remotely disposed relative to the processor, and these remote memories may be connected to the processor via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0126] The embodiment of the present invention also discloses a computer program product or a computer program, wherein the computer program product or the computer program includes computer instructions, and the computer instructions are stored in a computer-readable storage medium. A processor of a computer device can read the computer instructions from the computer-readable storage medium, and the processor executes the computer instructions, so that the computer device executes Figure 1 The method shown.

[0127] In some selectable embodiments, the function / operation mentioned in the block diagram may not occur in the order mentioned in the operation diagram. For example, depending on the function / operation involved, the two boxes shown in succession can actually be executed substantially simultaneously or the above-mentioned boxes can sometimes be executed in reverse order. In addition, the embodiment presented and described in the flow chart of the present invention is provided by way of example, for the purpose of providing a more comprehensive understanding of technology. The disclosed method is not limited to the operation and logic flow presented herein. Selectable embodiments are expected, wherein the order of various operations is changed and the sub-operation of a part for which is described as a larger operation is performed independently.

[0128] In addition, although the present invention is described in the context of functional modules, it should be understood that, unless otherwise specified to the contrary, one or more of the above-mentioned functions and / or features can be integrated into a single physical device and / or software module, or one or more functions and / or features can be implemented in a separate physical device or software module. It is also understood that a detailed discussion of the actual implementation of each module is unnecessary for understanding the present invention. More specifically, in view of the properties, functions and internal relationships of the various functional modules in the device disclosed herein, the actual implementation of the module will be understood within the conventional skills of the engineer. Therefore, those skilled in the art can implement the present invention set forth in the claims without excessive experimentation using ordinary techniques. It is also understood that the specific concepts disclosed are merely illustrative and are not intended to limit the scope of the present invention, which is determined by the full scope of the appended claims and their equivalents.

[0129] If the above functions are implemented in the form of software functional units and sold or used as independent products, they can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, or the part that contributes to the prior art or the part of the technical solution, can be embodied in the form of a software product. The computer software product is stored in a storage medium, including several instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the above methods of each embodiment of the present invention. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), disk or optical disk and other media that can store program codes.

[0130] The logic and / or steps represented in the flowchart or otherwise described herein, for example, can be considered as an ordered list of executable instructions for implementing logical functions, and can be embodied in any computer-readable medium for use by an instruction execution system, device or apparatus (such as a computer-based system, a system including a processor, or other system that can fetch instructions from an instruction execution system, device or apparatus and execute instructions), or in conjunction with such instruction execution systems, devices or apparatuses. For the purposes of this specification, "computer-readable medium" can be any device that can contain, store, communicate, propagate or transmit a program for use by an instruction execution system, device or apparatus, or in conjunction with such instruction execution systems, devices or apparatuses.

[0131] More specific examples of computer-readable media (a non-exhaustive list) include the following: an electrical connection with one or more wires (electronic device), a portable computer disk case (magnetic device), a random access memory (RAM), a read-only memory (ROM), an erasable and editable read-only memory (EPROM or flash memory), an optical fiber device, and a portable compact disk read-only memory (CDROM). In addition, the computer-readable medium may even be a paper or other suitable medium on which the above-mentioned program is printed, since the above-mentioned program can be obtained electronically, for example, by optically scanning the paper or other medium, followed by editing, deciphering or processing in other suitable ways as necessary, and then stored in a computer memory.

[0132] It should be understood that the various parts of the present invention can be implemented by hardware, software, firmware or a combination thereof. In the above-mentioned embodiments, a plurality of steps or methods can be implemented by software or firmware stored in a memory and executed by a suitable instruction execution system. For example, if implemented by hardware, as in another embodiment, it can be implemented by any one of the following technologies known in the art or their combination: a discrete logic circuit having a logic gate circuit for implementing a logic function for a data signal, a dedicated integrated circuit having a suitable combination of logic gate circuits, a programmable gate array (PGA), a field programmable gate array (FPGA), etc.

[0133] In the above description of this specification, the description with reference to the terms "one embodiment / example", "another embodiment / example" or "certain embodiments / examples" etc. means that the specific features, structures, materials or characteristics described in conjunction with the embodiment or example are included in at least one embodiment or example of the present invention. In this specification, the schematic representation of the above terms does not necessarily refer to the same embodiment or example. Moreover, the specific features, structures, materials or characteristics described may be combined in any one or more embodiments or examples in a suitable manner.

[0134] Although embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that various changes, modifications, substitutions and variations may be made to the embodiments without departing from the principles and spirit of the present invention, and that the scope of the present invention is defined by the claims and their equivalents.

[0135] The above is a specific description of the preferred implementation of the present invention, but the present invention is not limited to the above embodiments. Those skilled in the art may make various equivalent modifications or substitutions without violating the spirit of the present invention. These equivalent modifications or substitutions are all included in the scope defined by the claims of this application.

Claims

1. A multi-dimensional query method for sub-library and sub-table data, characterized in that: The following steps are involved: Acquire first order data, and add the first order data to a preset first message queue; Determine a first sharding key according to the user ID of the first order data, and store the first order data in a user order table of a corresponding order library according to the first sharding key; Determine a second sharding key according to the merchant ID of the first order data, and redundantly store the first order data in a merchant order table of a corresponding order library according to the second sharding key; In response to a user order query request from a user terminal, obtaining a user ID to be queried, querying the user order table of the corresponding order library according to the user ID to be queried, and obtaining user order query data; In response to a merchant order query request from the merchant end, the merchant ID to be queried is obtained, and the merchant order table of the corresponding order library is queried according to the merchant ID to be queried to obtain the merchant order query data.

2. A multi-dimensional query method for sub-library and sub-table data according to claim 1, characterized in that: The determining of the first sharding key according to the user ID of the first order data, and storing the first order data in the user order table of the corresponding order library according to the first sharding key specifically includes: Monitoring the first message queue through a first consumer service; When it is monitored that the first order data is added to the first message queue, the first sharding key is determined according to the user ID of the first order data; A shard key value operation is performed according to the first shard key and a preset shard algorithm to determine a first physical shard corresponding to the first order data, and then the first order data is stored in the user order table of the corresponding order library according to the position of the first physical shard.

3. A multi-dimensional query method for sub-library and sub-table data according to claim 1, characterized in that: The determining a second sharding key according to the merchant ID of the first order data, and redundantly storing the first order data in a merchant order table of a corresponding order library according to the second sharding key, specifically includes: Monitor the first message queue through a second consumer service; When it is monitored that the first order data is added to the first message queue, the second sharding key is determined according to the merchant ID of the first order data; A shard key value operation is performed according to the second shard key and a preset shard algorithm to determine the second physical shard corresponding to the first order data, and then the first order data is redundantly stored in the merchant order table of the corresponding order library according to the position of the second physical shard.

4. A multi-dimensional query method for sub-library and sub-table data according to claim 2, characterized in that: The method of responding to a user order query request from the user terminal, obtaining a user ID to be queried, and querying the user order table of the corresponding order library according to the user ID to be queried to obtain user order query data specifically includes: When the first consumer service receives the user order query request from the user end, the first consumer service determines the user ID to be queried according to the user order query request, and determines the third sharding key according to the user ID to be queried; A shard key value operation is performed according to the third shard key and the sharding algorithm to determine the third physical shard corresponding to the user ID to be queried, and then a query is performed in the user order table of the corresponding order library according to the position of the third physical shard to obtain the user order query data.

5. A multi-dimensional query method for sub-library and sub-table data according to claim 3, characterized in that: The method of responding to the merchant order query request of the merchant end, obtaining the merchant ID to be queried, querying the merchant order table of the corresponding order library according to the merchant ID to be queried, and obtaining the merchant order query data specifically includes: When the second consumer service receives the merchant order query request from the merchant end, the merchant ID to be queried is determined according to the merchant order query request, and the fourth sharding key is determined according to the merchant ID to be queried; A shard key value operation is performed according to the fourth shard key and the shard algorithm to determine the fourth physical shard corresponding to the merchant ID to be queried, and then a query is performed in the merchant order table of the corresponding order library according to the position of the fourth physical shard to obtain the merchant order query data.

6. A multi-dimensional query method for sub-library and sub-table data according to claim 1, characterized in that: The first order data in the first message queue is stored in the user order table and the merchant order table through a confirmation response mechanism and a timeout retransmission mechanism.

7. A multi-dimensional query method for sub-library and sub-table data according to any one of claims 1 to 6, characterized in that: The multi-dimensional query method for sub-library and sub-table data also includes the following steps: Monitoring the backlogged messages in the first message queue through the alarm platform; When it is monitored that there is a backlog of messages in the first message queue and that they cannot be consumed normally, the backlogged second order data is obtained through the alarm platform, and the second order data is synchronized to the user order table and the merchant order table of the corresponding order library.

8. A multi-dimensional query device for sub-library and sub-table data, characterized in that: include: A data acquisition module, used to acquire first order data and add the first order data to a preset first message queue; A first shard storage module, configured to determine a first shard key according to a user ID of the first order data, and store the first order data in a user order table of a corresponding order library according to the first shard key; A second sharding storage module, configured to determine a second sharding key according to the merchant ID of the first order data, and redundantly store the first order data in a merchant order table of a corresponding order library according to the second sharding key; The first order query module is used to respond to the user order query request of the user terminal, obtain the user ID to be queried, query the user order table of the corresponding order library according to the user ID to be queried, and obtain the user order query data; The second order query module is used to respond to the merchant order query request of the merchant end, obtain the merchant ID to be queried, query the merchant order table of the corresponding order library according to the merchant ID to be queried, and obtain the merchant order query data.

9. An electronic device, characterized in that: The electronic device includes a memory, a processor, a program stored in the memory and executable on the processor, and a data bus for realizing connection and communication between the processor and the memory. When the program is executed by the processor, the steps of the multi-dimensional query method for sub-library and sub-table data are implemented as described in any one of claims 1 to 7.

10. A storage medium, the storage medium being a computer-readable storage medium, used for computer-readable storage, characterized in that: The storage medium stores one or more programs, and the one or more programs can be executed by one or more processors to implement the steps of the multi-dimensional query method for sub-library and sub-table data as described in any one of claims 1 to 7.

Images