An optimization method based on golang to solve the retrieval sorting and paging problems under sharding
The GoLang middleware-based optimization method addresses database sharding inefficiencies by analyzing query instructions and utilizing goroutines to reduce data query volume and CPU usage, ensuring accurate retrieval, sorting, and paging operations.
Patent Information
- Application Number
- CN202210698761.3
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2022-06-20
- Publication Date
- 2024-12-03
- Estimated Expiration
- 2042-06-20
AI Technical Summary
Existing database sharding technologies face challenges in efficiently performing retrieval, sorting, and paging operations due to increased query data volume, memory usage, and CPU computing power, leading to search bottlenecks and inefficiencies.
An optimization method using GoLang middleware that analyzes query instructions, applies data scattering rules, and utilizes goroutines for parallel processing to reduce the scope of queries, sorting, and paging operations, leveraging hash table partitioning algorithms and natural month partitioning, and applies data convergence to converge secondary queries, ensuring the accuracy of data query and paging operations, thereby effectively narrowing the scope of searches, and reducing the amount of data query, thereby reducing the occupancy of system memory and computing power.
The method achieves efficient retrieval, sorting, and paging by reducing data query volume, memory usage, and CPU computing power, while maintaining data accuracy and reducing algorithm complexity and time complexity.
Smart Images

Figure CN115062045B_ABST
Abstract
Description
Technical Field
[0001] The present invention belongs to the technical field of Internet data processing, and relates to an optimization method for solving retrieval sorting and paging problems under sub-library and sub-table conditions based on Golang. Background Art
[0002] In business scenarios where the amount of data is often millions, horizontal database and table sharding is a way to solve the problem of insufficient capacity of a single database. However, this also makes retrieval, sorting and paging difficult. Due to database and table sharding, it is impossible to have a global view to accurately and completely retrieve, sort and paginate the records that meet the conditions, which brings great trouble to applications that require retrieval, paging and sorting.
[0003] At present, the most similar solution to the present invention is to search all tables in all libraries in a sufficiently large range, merge all records, form a search result view, sort the view, and then complete the paging. Alternatively, when dividing libraries and tables, the business direction is converged, the table range is restricted, and the search conditions are matched through the range to narrow the search range, and then the records are merged to complete the final sorting and paging.
[0004] The current technical disadvantage is that the amount of query data will increase with the number of pages, and the memory usage and CPU computing power will increase at a quadratic level, which is not suitable for most technical support. In addition, if the business restrictions on the range partitioning strategy of the data in the database sub-library and sub-table are increased, some databases will be in a cold state, while other parts will be in hot spots and be searched for a long time, causing a search bottleneck.
[0005] The present invention analyzes data scattering rules and converges secondary queries to effectively narrow the scope to be searched, while ensuring the accuracy of data query and paging, thereby effectively reducing the amount of data query, thereby reducing the occupancy of system memory and computing power, thereby completing the optimization of the retrieval, sorting and paging operations of data in the database's sub-libraries and sub-tables, and also reducing the algorithm complexity and time complexity. Summary of the invention
[0006] The purpose of the present invention is to address the above-mentioned problems in the existing technology and propose an optimization method based on golang to solve the retrieval sorting and paging problems under sharding. In the case of sharding of data, the optimization method can realize accurate retrieval of any field in the entire database to achieve accurate sorting and paging.
[0007] The purpose of the present invention can be achieved by the following technical solutions:
[0008] An optimization method for solving the retrieval sorting and paging problems under sub-library and sub-table based on golang, characterized by comprising the following steps:
[0009] S1. Build a golang service as middleware, which can be started independently;
[0010] S2. Start the golang middleware, intercept and parse the query instructions sent by the application, and analyze the query instructions according to the library and table sharding rules of the table sharding algorithm;
[0011] S3, sending the analyzed query instruction to the backend database, which processes it and obtains the first query result;
[0012] S4. According to the analysis of the first query result, a second query condition is obtained;
[0013] S5. Obtaining a second query result by processing the second query condition;
[0014] S6. After accurately sorting and paging the second query results, send them to the corresponding users of the application.
[0015] The query instruction includes inputting the data requirements to be queried, the data requirements are constructed by SQL statements, analyzing the query scope of the data requirements and the data storage rules in the database, and using the golang language coroutine to query all database tables in parallel.
[0016] The algorithms include a hash table partitioning algorithm, a natural month table partitioning algorithm, a date table partitioning algorithm and a range table partitioning algorithm.
[0017] The library and table division rules include the mapping relationship between tables and libraries, the number of sub-tables, and table splitting rules.
[0018] The analysis method for analyzing the query instruction includes fragmentation analysis and routing analysis.
[0019] The first query result includes front data, target data and tail data, the target data is the data actually needed by the user, the front data and the target data are both valid data, and the tail data is invalid data.
[0020] The analysis of the first query result is as follows: it shows whether there are at least two valid data in each database table. If so, the second query condition is obtained; if not, the current query result is the final query result, and the only database table with valid data is accurately queried and statistically analyzed to obtain the determined target data, and the target data is accurately sorted and paginated before being sent to the user corresponding to the application.
[0021] The processing of the second query condition is as follows: statistics are performed on the amount of valid data in each database table, and database tables with valid data are valid sets, and database tables without valid data are empty sets. The database table with the least amount of valid data has its valid data amount as the header data amount, and the header data amount that the empty set should have is allocated to other valid sets. The search is then performed again to cover the target data and reduce the amount of data returned, thereby obtaining the second query result.
[0022] According to the present invention, a computer device is provided, comprising a processor, a memory, and a computer program stored in the memory and configured to be executed by the processor, wherein the processor implements the above method when executing the program.
[0023] According to the present invention, a computer-readable storage medium is provided, wherein the computer-readable storage medium stores a computer program, and wherein the computer program executes the above method when executed by a processor.
[0024] Compared with the existing technology, this golang-based optimization method for solving the retrieval sorting and paging problems under sharding has the following advantages:
[0025] 1. Use secondary query, the second query scope depends on the first query result, and the first query condition depends on data analysis.
[0026] 2. Use the golang language's coroutine feature to execute queries in parallel, increasing query efficiency and computing speed.
[0027] 3. Effectively analyze the characteristics of data stored on disk and reduce the scope of queries without data loss. BRIEF DESCRIPTION OF THE DRAWINGS
[0028] Figure 1 This is a diagram of the amount of data evenly distributed in each database table under an ideal state in an embodiment of the present invention.
[0029] Figure 2 This is a diagram of the amount of data that is different when data is written to disk under actual conditions in an embodiment of the present invention.
[0030] Figure 3 It is a data volume diagram for demarcating a data segmentation line in an embodiment of the present invention.
[0031] Figure 4 It is a data volume diagram that cannot cover the target data in the embodiment of the present invention.
[0032] Figure 5 It is a data volume diagram that cannot cover the target data in the embodiment of the present invention.
[0033] Figure 6It is a diagram showing the amount of data that cannot cover the target data in the embodiments of the present invention.
[0034] Figure 7 It is a diagram showing the amount of data that cannot cover the target data in the embodiments of the present invention.
[0035] Figure 8 It is a principle block diagram of the present invention.
[0036] Fig. 9 It is a schematic structural diagram of a computer device in the present invention. Detailed implementation manners
[0037] The following are specific embodiments of the present invention and in combination with the accompanying drawings, the technical solutions of the present invention are further described, but the present invention is not limited to these embodiments.
[0038] Scenario setting: The amount of news data of a certain application is in the millions. This data is distributed in 10 databases and has been randomly hashed. It can be regarded that about 100,000 pieces of data are stored in a single database table. There is a current requirement to find the 10th page of news in ascending order of the publication time of the articles by editor Zhang San from all the news. The amount of news per page is 100, that is, to find the 901st to 1000th pieces of data published by Zhang San.
[0039] Comparative example:
[0040] The current direct solution is to send a statement like "select * from news where username = 'Zhang San' order by time asc" to each of the 10 databases respectively, obtain all the news published by Zhang San in each database table, and then fuse and sort all the data to find the data of the 901st - 1000th pieces for return. However, this will cause a sharp decline in memory performance. To find 100 pieces of data of a certain person, all the data of this person needs to be retrieved. Here, an extreme assumption is made. If all the news is published by this editor, it is equivalent to reading in all the news. First of all, this reading method is not realistic and will cause the server to crash directly, and this is equivalent to not using database sharding and table partitioning. Further, assume that the amount of data is not that large and the server can read in these data. Next, the fusion, search, and sorting of the data will be a great test for the CPU computing power.
[0041] This embodiment:
[0042] This solution analyzes the query range and data disk writing rules, effectively reduces the amount of query data, and uses goroutines in the Go language to query all the database tables in parallel. While reducing the amount of data, it also shortens the query time and effectively reduces the time complexity.
[0043] The specific solution is as follows:
[0044] Analyzing the data requirements to be queried, we are looking for the 901st to 1000th data published by Zhang San. These 100 data are scattered in 10 database tables, and it is not known in which database table each data is located.
[0045] Without affecting the results, we simplify the scenario and assume that all the data in the database table is from Zhang San. We consider the first 1000 pieces of data and randomly put these 1000 pieces of data into 10 tables in order. That is to say, we only need to find 100 pieces of data in each table on average to basically cover the first 1000 pieces of data. Now we have found an approximate range, which is not very accurate, because there may be extreme cases where a certain library will miss the target data, that is to say, it is possible to obtain data after 1000 pieces. Is there any way to solve this problem?
[0046] Of course, our target data is 901-1000 data, and the first 900 data are scattered in 10 database tables, which means that theoretically the first data of each database table is in the first 900 data that we don't need. So we move the search range of these 100 data relatively backward from the first data until we can completely cover most of the target data. In this way, the target data in each database table will theoretically be included in our search range. So how far should we move it relatively backward?
[0047] Let's analyze it. We don't know in which table the 901st data is scattered. Since we know that the first 900 data are not what we want, and these 900 data are scattered in 10 database tables, on average there are 90 data in each database table that we don't want, and their total number is certain, that is, if there is more data in one database, there will inevitably be less data in another database, which is a process of increase and decrease.
[0048] So ideally, if Figure 1 , we can find the 90th data as the dividing line, so that the next 100 data in each library are likely to contain all the target data. The high probability is just more accurate than the previous range.
[0049] However, in reality, the data is not evenly distributed. Figure 2 , then does this 90 dividing line still have meaning?
[0050] Yes, of course! Let’s take a look. Figure 3 , some target data are above the horizontal line, and some are below the horizontal line.
[0051] The next idea is a bit confusing, so we can draw a picture to help us understand it. Each bar chart represents a database table. The orange part is the data that needs to be found, and the blue and gray parts are the unnecessary data. We also use the 90th data as the dividing line and check 100 data later. The reason for using 100 as the range is that these 100 data fall into a certain database table. Figure 4 . Figure 4 This gives us another reminder, including another extreme case, in which the front data and target data do not exist in a certain database table, and only the tail data exists. In this way, it is possible that 90-100 data in the 10th database cannot cover the target data at all, so we have to make compatibility processing for extreme cases. It is foreseeable that the query result of each database table is either data or no data, and these two results will bring two different results. If a database does not return any data, that is, the database table is empty, it can be understood that the 90+100 data originally belonging to the database table are dispersed into other database tables, and we do not know which database table they are in. Therefore, every time an empty data set is returned in a certain database table, we should increase the search range by 190. Generally speaking, if n (n<10) database tables return an empty set, then the right interval range of the range we search again in the database with data returned should be (100+190*n), and the left interval should be the minimum data item of the set returned by these 5 database tables, that is, min (set 1, 2, 3, 4, 5). In more extreme cases, only one database table returns a result set, which means that the other 9 tables have no data, and these 1000 data are all in one database table. Therefore, we can optimize and directly query 901-1000 data. Compared with querying 91-1810 data, it is more efficient, but it also proves that this interval range can accurately include all target data and avoid the problem of completely querying all data.
[0052] So considering the above, we need to find a suitable search range, which is determined by the first query result. By querying the data of 90-190 in each library table, we can determine whether there are at least two data sets returned. If there is only one, we will perform a precise query of 901-1000. If there are more than 1 sets, we can find a minimum data item by comparison. We use this as the head of the data range, and then the tail data is 91+190*n (where n is the number of data tables that return empty data result sets), thereby forming the P (head node, tail node) area. By querying the secondary area of 10 databases, we can complete the coverage of target data and reduce the amount of data returned. Combining the advantages of golang's coroutines, grouping calculations, the header increment of the data returned by each database table, and comparing the offset number 90 of the first query, we calculate the offset position of the current first data, and cumulatively calculate the offset of the minimum data in the global field of view, thereby combining 901-1000 data.
[0053] This solution is a retrieval query optimization solution, which is a part of the database middleware algorithm and can complete the data query retrieval paging function of sub-libraries and sub-tables.
[0054] The present invention gives full play to the role of data analysis, refers to the idea of dichotomy, uses logical thinking to reduce the query scope without data loss, compromises algorithm complexity, reduces time complexity, and effectively improves data response speed.
[0055] The specific embodiments described herein are merely examples of the spirit of the present invention. Those skilled in the art may make various modifications or additions to the specific embodiments described or replace them in similar ways, but they will not deviate from the spirit of the present invention or exceed the scope defined by the appended claims.
Claims
1. An optimization method based on golang to solve the retrieval sorting and paging problems under sub-library and sub-table, characterized in that: The following steps are involved: S1. Build a golang service as middleware, which can be started independently; S2. Start the golang middleware, intercept and parse the query instructions sent by the application, and analyze the query instructions according to the library and table sharding rules of the table sharding algorithm; S3, sending the analyzed query instruction to the backend database, which processes it and obtains the first query result; S4. According to the analysis of the first query result, a second query condition is obtained; S5. Obtaining a second query result by processing the second query condition; S6. After accurately sorting and paging the second query results, the results are sent to the corresponding user of the application program. The first query result includes front data, target data and tail data, the target data is the data actually needed by the user, the front data and the target data are both valid data, and the tail data is invalid data; The analysis of the first query result is as follows: whether there are at least two valid data in each database table, if so, the second query condition is obtained; If not, the query result is the final query result, and the only database table with valid data is accurately queried and statistically analyzed to obtain the determined target data. The target data is accurately sorted and paginated and sent to the corresponding user of the application; The processing of the second query condition is as follows: statistics are performed on the amount of valid data in each database table, and database tables with valid data are valid sets, and database tables without valid data are empty sets. The database table with the least amount of valid data has its valid data amount as the header data amount, and the header data amount that the empty set should have is allocated to other valid sets. The search is then performed again to cover the target data and reduce the amount of data returned, thereby obtaining the second query result.
2. The optimization method for solving the retrieval sorting and paging problems under sub-library and sub-table based on golang according to claim 1 is characterized in that: The query instruction includes inputting the data requirements to be queried, where the data requirements are constructed by SQL statements, analyzing the query scope of the data requirements and the data storage rules in the database, and using the golang language coroutine to query all database tables in parallel.
3. The optimization method for solving the retrieval sorting and paging problems under sub-library and sub-table based on golang according to claim 1 is characterized in that: The algorithms include a hash table partitioning algorithm, a natural month table partitioning algorithm, a date table partitioning algorithm and a range table partitioning algorithm.
4. The optimization method for solving the retrieval sorting and paging problems under sub-library and sub-table based on golang according to claim 1 is characterized in that: The library and table division rules include the mapping relationship between tables and libraries, the number of sub-tables, and table splitting rules.
5. The optimization method for solving the retrieval sorting and paging problems under sub-library and sub-table based on golang according to claim 1 is characterized in that: The analysis method for analyzing the query instruction includes fragmentation analysis and routing analysis.
6. A computer device comprising a processor, a memory, and a computer program stored in the memory and configured to be executed by the processor, characterized in that: When the processor executes the program, an optimization method for solving retrieval sorting and paging problems under divided libraries and tables based on golang is implemented as described in any one of claims 1-5.
7. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by the processor, an optimization method for solving retrieval sorting and paging problems under sharding of libraries and tables based on golang is performed as described in any one of claims 1-5.
