A method and terminal for improving database deep paging query efficiency

Abstract

The present invention discloses a method and a terminal for improving the efficiency of deep paging query of a database; reading business data from an original data table according to preset business conditions; establishing sub-tables in sequence, and sequentially writing the business data into a plurality of sub-tables according to preset sub-table data thresholds to obtain all sub-tables corresponding to the business conditions; receiving a paging query request of a business program under the business conditions, and calculating a target sub-table number and a data offset of a sub-table where target data is stored according to a page number and a page capacity contained in the paging query request; querying and acquiring the target data from all sub-tables corresponding to the business conditions according to the target sub-table number, the offset and the page capacity, and returning the target data to the business program; directly locating the sub-table where the target data is located according to the sub-table information to query the target data, thereby greatly improving the query efficiency of the business data in the case of a large amount of data.

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and in particular to a method and a terminal for improving the efficiency of deep paging query of a database. Background Art

[0002] In a project, when a table with more than one million data is queried in pages, the query speed will become slower and slower as the number of pages increases, which is the deep paging problem of MySQL. In order to prevent the database from loading too much data at one time, resulting in excessive memory and disk IO overhead, paging display is often required. At this time, the LIMIT keyword of MySQL is needed. When querying 10 data, the query efficiency of LIMIT 0,10, LIMIT 10000,10, and LIMIT 1000000,10 is completely different. For example, LIMIT 1000000,10 queries the first 1000010 data, then discards the first 1000000 data, and only returns the last 10 data. Therefore, as the offset increases, the time spent on the query shows an exponential growth trend, and the query efficiency is low. Summary of the invention

[0003] The technical problem to be solved by the present invention is to provide a method and a terminal for improving the efficiency of deep paging query of a database, thereby improving the efficiency of deep paging query of a database.

[0004] In order to solve the above technical problems, the technical solution adopted by the present invention is:

[0005] A method for improving the efficiency of deep paging query of a database, comprising the steps of:

[0006] S1. Read business data from the original data table according to preset business conditions;

[0007] S2. Establish sub-tables in sequence, and write the business data into multiple sub-tables in sequence according to a preset sub-table data threshold, to obtain all sub-tables corresponding to the business conditions;

[0008] S3, receiving a paging query request from a business program under the business conditions, and calculating a target sub-table number and a data offset of a sub-table where target data is stored according to a page number and a page capacity included in the paging query request;

[0009] S4. According to the target sub-table number, the offset and the page capacity, query and obtain the target data from all sub-tables corresponding to the business condition, and return the target data to the business program.

[0010] In order to solve the above technical problems, another technical solution adopted by the present invention is:

[0011] A terminal for improving the efficiency of deep paging query of a database comprises a processor, a memory, and a computer program stored in the memory and executable on the processor, wherein the processor implements the following steps when executing the computer program:

[0012] S1. Read business data from the original data table according to preset business conditions;

[0013] S2. Establish sub-tables in sequence, and write the business data into multiple sub-tables in sequence according to a preset sub-table data threshold, to obtain all sub-tables corresponding to the business conditions;

[0014] S3, receiving a paging query request from a business program under the business conditions, and calculating a target sub-table number and a data offset of a sub-table where target data is stored according to a page number and a page capacity included in the paging query request;

[0015] S4. According to the target sub-table number, the offset and the page capacity, query and obtain the target data from all sub-tables corresponding to the business condition, and return the target data to the business program.

[0016] The beneficial effect of the present invention is that the present invention partitions the data in the original data table according to the business conditions of the business query in a specific business scenario, so that when the corresponding business query request is subsequently received, the sub-table where the target data is located can be directly located according to the sub-table information to query the target data, which greatly improves the query efficiency of the business data when there is a large amount of data. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] Figure 1 A flow chart of a method for improving the efficiency of deep paging query of a database according to the present invention;

[0018] Figure 2 A structural diagram of a terminal for improving the efficiency of deep paging query of a database according to the present invention;

[0019] Description of labels:

[0020] 1. A terminal for improving the efficiency of deep paging query of a database; 2. A processor; 3. A memory. DETAILED DESCRIPTION

[0021] In order to explain the technical content, achieved objectives and effects of the present invention in detail, the following is an explanation in combination with the implementation modes and the accompanying drawings.

[0022] Please refer to Figure 1 , a method for improving the efficiency of deep paging query of a database, comprising the steps of:

[0023] S1. Read business data from the original data table according to preset business conditions;

[0024] S2. Establish sub-tables in sequence, and write the business data into multiple sub-tables in sequence according to a preset sub-table data threshold, to obtain all sub-tables corresponding to the business conditions;

[0025] S3, receiving a paging query request from a business program under the business conditions, and calculating a target sub-table number and a data offset of a sub-table where target data is stored according to a page number and a page capacity included in the paging query request;

[0026] S4. According to the target sub-table number, the offset and the page capacity, query and obtain the target data from all sub-tables corresponding to the business condition, and return the target data to the business program.

[0027] From the above description, it can be seen that the beneficial effect of the present invention is that: the present invention divides the data in the original data table into sub-tables according to the business conditions of the business query in a specific business scenario, so that when the corresponding business query request is received subsequently, the sub-table where the target data is located can be directly located according to the sub-table information to query the target data, which greatly improves the query efficiency of the business data when there is a large amount of data.

[0028] Furthermore, the step S2 specifically includes the steps of:

[0029] S21, create a sub-table with sub-table number 1, and write the business data into the sub-table with sub-table number 1;

[0030] S22, continuously monitoring the data volume of the currently written sub-table, determining whether the data volume of the currently written sub-table reaches a preset sub-table data threshold, and if so, calculating the sub-table number and creating a new sub-table according to the sub-table number, and continuing to write data to the newly created sub-table until the writing of the business data is completed, thereby obtaining all sub-tables corresponding to the business conditions.

[0031] From the above description, it can be seen that the data volume of the sub-table is monitored, and when the data volume reaches the preset threshold, a new sub-table is created for data writing, ensuring that the data volume of each sub-table does not exceed the sub-table data threshold to ensure the normal execution of subsequent steps.

[0032] Furthermore, the sub-table data threshold is a multiple of the page capacity, and the sub-table data threshold is greater than the page capacity. The calculation formula of the target sub-table number in step S3 is:

[0033] n={[pagesize*(pageindex-1)] / tablesize}+1;

[0034] The calculation formula of the data offset is:

[0035] offset={(pageindex-1)-[tablesize / pagesize*(n-1)]}*pagesize

[0036] =(pageindex-1)*pagesize-tablesize*(n-1);

[0037] Among them, n represents the target sub-table number, pagesize represents the page capacity, pageindex represents the page number, tablesize represents the sub-table data threshold, and offset represents the offset.

[0038] From the above description, it can be known that according to the page number and the page capacity, the sub-table location where the target data is located can be accurately located and the offset can be calculated, so that the target data can be accurately obtained from the sub-table later, thereby improving the efficiency of data acquisition.

[0039] Furthermore, the step S4 is specifically as follows:

[0040] Determine the target sub-table where the target data is located according to the target sub-table number n, and create a query statement according to the name tablename of the target sub-table, the offset offset, and the page capacity pagesize:

[0041] select*from tablename limit offset,pagesize;

[0042] The target data is queried from the target sub-table through the query statement, and the target data is returned to the business program.

[0043] From the above description, it can be seen that when the sub-table number and data offset are known, the sub-table can be accurately located, and a query statement can be established according to the sub-table name and offset, so that the target data can be directly found in the corresponding sub-table, thereby improving the efficiency of data acquisition.

[0044] Furthermore, the page capacity is 10, and the sub-table data threshold is 100,000.

[0045] From the above description, it can be seen that the page capacity is 10 and the sub-table data threshold is 100000, which form a multiple relationship, and the page capacity is a common and regular capacity value, which is easy to use.

[0046] Please refer to Figure 2 , a terminal for improving the efficiency of deep paging query of a database, comprising a processor, a memory, and a computer program stored in the memory and executable on the processor, wherein the processor implements the following steps when executing the computer program:

[0047] S1. Read business data from the original data table according to preset business conditions;

[0048] S2. Establish sub-tables in sequence, and write the business data into multiple sub-tables in sequence according to a preset sub-table data threshold, to obtain all sub-tables corresponding to the business conditions;

[0049] S3, receiving a paging query request from a business program under the business conditions, and calculating a target sub-table number and a data offset of a sub-table where target data is stored according to a page number and a page capacity included in the paging query request;

[0050] S4. According to the target sub-table number, the offset and the page capacity, query and obtain the target data from all sub-tables corresponding to the business condition, and return the target data to the business program.

[0051] From the above description, it can be seen that the beneficial effect of the present invention is that: the present invention divides the data in the original data table into sub-tables according to the business conditions of the business query in a specific business scenario, so that when the corresponding business query request is received subsequently, the sub-table where the target data is located can be directly located according to the sub-table information to query the target data, which greatly improves the query efficiency of the business data when there is a large amount of data.

[0052] Furthermore, the step S2 specifically includes the steps of:

[0053] S21, create a sub-table with sub-table number 1, and write the business data into the sub-table with sub-table number 1;

[0054] S22, continuously monitoring the data volume of the currently written sub-table, determining whether the data volume of the currently written sub-table reaches a preset sub-table data threshold, and if so, calculating the sub-table number and creating a new sub-table according to the sub-table number, and continuing to write data to the newly created sub-table until the writing of the business data is completed, thereby obtaining all sub-tables corresponding to the business conditions.

[0055] From the above description, it can be seen that the data volume of the sub-table is monitored, and when the data volume reaches the preset threshold, a new sub-table is created for data writing, ensuring that the data volume of each sub-table does not exceed the sub-table data threshold to ensure the normal execution of subsequent steps.

[0056] Furthermore, the sub-table data threshold is a multiple of the page capacity, and the sub-table data threshold is greater than the page capacity. The calculation formula of the target sub-table number in step S3 is:

[0057] n={[pagesize*(pageindex-1)] / tablesize}+1;

[0058] The calculation formula of the data offset is:

[0059] offset={(pageindex-1)-[tablesize / pagesize*(n-1)]}*pagesize

[0060] =(pageindex-1)*pagesize-tablesize*(n-1);

[0061] Among them, n represents the target sub-table number, pagesize represents the page capacity, pageindex represents the page number, tablesize represents the sub-table data threshold, and offset represents the offset.

[0062] From the above description, it can be known that according to the page number and the page capacity, the sub-table location where the target data is located can be accurately located and the offset can be calculated, so that the target data can be accurately obtained from the sub-table later, thereby improving the efficiency of data acquisition.

[0063] Furthermore, the step S4 is specifically as follows:

[0064] Determine the target sub-table where the target data is located according to the target sub-table number n, and create a query statement according to the name tablename of the target sub-table, the offset offset, and the page capacity pagesize:

[0065] select*from tablename limit offset,pagesize;

[0066] The target data is queried from the target sub-table through the query statement, and the target data is returned to the business program.

[0067] From the above description, it can be seen that when the sub-table number and data offset are known, the sub-table can be accurately located, and a query statement can be established according to the sub-table name and offset, so that the target data can be directly found in the corresponding sub-table, thereby improving the efficiency of data acquisition.

[0068] Furthermore, the page capacity is 10, and the sub-table data threshold is 100,000.

[0069] From the above description, it can be seen that the page capacity is 10 and the sub-table data threshold is 100000, which form a multiple relationship, and the page capacity is a common and regular capacity value, which is easy to use.

[0070] A method and terminal for improving the efficiency of deep paging query of a database according to the present invention are used in a scenario where data query needs to be performed on a form with a huge amount of data under a specific business environment.

[0071] Please refer to Figure 1 , Embodiment 1 of the present invention is:

[0072] A method for improving the efficiency of deep paging query of a database, comprising the steps of:

[0073] S1. Read business data from the original data table according to preset business conditions;

[0074] S2. Establish sub-tables in sequence, and write the business data into multiple sub-tables in sequence according to a preset sub-table data threshold, to obtain all sub-tables corresponding to the business conditions;

[0075] The step S2 specifically includes the following steps:

[0076] S21, create a sub-table with sub-table number 1, and write the business data into the sub-table with sub-table number 1;

[0077] S22, continuously monitoring the data volume of the currently written sub-table, determining whether the data volume of the currently written sub-table reaches a preset sub-table data threshold, and if so, calculating the sub-table number and creating a new sub-table according to the sub-table number, and continuing to write data to the newly created sub-table until the writing of the business data is completed, thereby obtaining all sub-tables corresponding to the business conditions.

[0078] In this embodiment, the initial data table is base_tb. The total amount of data in the data table is read by select count(0)from base_tb, which contains 10 million data. Data is read according to the business paging conditions and migrated to the sub-table according to the set amount. The system is set to divide every 100,000 data into one table. According to the total amount of data obtained in step 1, 10 million, we need to divide it into 100 tables and create the first sub-table block1_tb. For example, the current client query condition is select*from base_tbwhere fieldint>1and fieldstr='abc', and the client displays 10 data items as a page. Then, to read the first page, select*from base_tb where fieldint>1and fieldstr='abc'limit 0,10 is used, and the read data is inserted into the first table block1_tb; to read the second page, select*from base_tb wherefieldint>1and fieldstr='abc'limit 10,10 is used, and the read data is also inserted into the first table block1_tb. And so on, 100,000 data items are inserted into the first sub-table block1_tb.

[0079] Until the 100001th data is read, select * from base_tb where fieldint>1andfieldstr='abc'limit 100000,10. The data in the first single table has reached the 100,000 threshold set by the system, so a new sub-table needs to be created for storage. You can create a new sub-table based on the current number of forms, or determine the sub-table number by calculation to accurately locate the data.

[0080] In this embodiment, the calculation of the sub-table number by using the page number, page capacity and sub-table data threshold is taken as an example, and the steps are as follows:

[0081] According to the table name number formula n = ((pagesize*pageindex) / 100000) + 1, we get n = 2, so we create a new block2_tb and insert the data into the second chapter sub-table block2_tb. By analogy, we can get the first table block1_tb, the second table block2_tb, and the nth table blockn_tb. According to this method, insert the 10 million data in base_tb into block1_tb to block100_tb, and each table stores 100,000 data.

[0082] S3, receiving a paging query request from a business program under the business conditions, and calculating a target sub-table number and a data offset of a sub-table where target data is stored according to a page number and a page capacity included in the paging query request;

[0083] The sub-table data threshold is a multiple of the page capacity, and the sub-table data threshold is greater than the page capacity. The calculation formula of the target sub-table number in step S3 is:

[0084] n={[pagesize*(pageindex-1)] / tablesize}+1;

[0085] The calculation formula of the data offset is:

[0086] offset={(pageindex-1)-[tablesize / pagesize*(n-1)]}*pagesize

[0087] =(pageindex-1)*pagesize-tablesize*(n-1);

[0088] Among them, n represents the target sub-table number, pagesize represents the page capacity, that is, the number of data items displayed on each page of the front-end page, pageindex represents the page number, tablesize represents the sub-table data threshold, that is, the upper limit of the number of data items stored in the sub-table, and offset represents the offset.

[0089] In this embodiment, the table data threshold tablesize=100000, the page capacity is 10, and assuming that pagesize=10 and pageindex=10005 are received, the target table number is calculated as follows:

[0090] n={[10*(10005-1)] / 1000000}+1;

[0091] The sub-table number is 2, that is, data is queried from the sub-table with sub-table number 2.

[0092] Because each table stores 100,000 data items, 10 items per page, that is, each table stores 10,000 pages of data, then the query offset offset = {(10005-1)-[100000 / 10*(2-1)]}*10, and the obtained offset offset = 40.

[0093] S4, querying and acquiring the target data from all sub-tables corresponding to the business condition according to the target sub-table number, the offset and the page capacity, and returning the target data to the business program;

[0094] The step S4 is specifically as follows:

[0095] Determine the target sub-table where the target data is located according to the target sub-table number n, and create a query statement according to the name tablename of the target sub-table, the offset offset, and the page capacity pagesize:

[0096] select*from tablename limit offset,pagesize;

[0097] The target data is queried from the target sub-table through the query statement, and the target data is returned to the business program.

[0098] In this embodiment, according to the sub-table number 2, it can be determined that the sub-table name is block2_tb, and the offset offset = 40, so the query statement is select * from block2_tb limit 40, 10. At this time, the original query condition wherefieldint>1and fieldstr='abc' is not needed, because in the process of migrating data to the sub-table, it has been read and inserted in sequence according to the set conditions, so the data sequence has been sorted according to the conditions, so in the sub-table query process, you only need to find out which record the data is in.

[0099] The data is obtained according to the query statement select * from block2_tb limit 40,10 and returned to the business program.

[0100] Please refer to Figure 2 , Embodiment 2 of the present invention is:

[0101] A terminal 1 for improving the efficiency of deep paging query of a database includes a processor 2, a memory 3, and a computer program stored in the memory 3 and executable on the processor 2. When the processor 2 executes the computer program, the steps in the above embodiment 1 are implemented.

[0102] To sum up, the present invention provides a method terminal for improving the efficiency of deep paging query of a database. Its main principle is to store the query data under specific business conditions in multiple sub-tables according to a sequential rule, so that the sub-table of the target data can be directly located according to the rule during subsequent queries, and data can be queried directly from the sub-table whose data volume is far smaller than the original data table, thereby improving the efficiency of data query.

[0103] The above descriptions are merely embodiments of the present invention and are not intended to limit the patent scope of the present invention. Any equivalent transformations made using the contents of the present invention's specification and drawings, or directly or indirectly applied in related technical fields, are also included in the patent protection scope of the present invention.

Claims

1. A method for improving the efficiency of deep paging query of a database, characterized in that: Includes steps: S1. Read business data from the original data table according to preset business conditions; S2. Establish sub-tables in sequence, and write the business data into multiple sub-tables in sequence according to a preset sub-table data threshold, to obtain all sub-tables corresponding to the business conditions; S3, receiving a paging query request from a business program under the business conditions, and calculating a target sub-table number and a data offset of a sub-table where target data is stored according to a page number and a page capacity included in the paging query request; S4, querying and acquiring the target data from all sub-tables corresponding to the business condition according to the target sub-table number, the offset and the page capacity, and returning the target data to the business program; The step S4 is specifically as follows: Determine the target sub-table where the target data is located according to the target sub-table number n, and create a query statement according to the name tablename of the target sub-table, the offset offset, and the page capacity pagesize: select from tablename limit offset,pagesize; and querying the target data from the target sub-table through the query statement, and returning the target data to the business program; The sub-table data threshold is a multiple of the page capacity, and the sub-table data threshold is greater than the page capacity. The calculation formula of the target sub-table number in step S3 is: n={[pagesize ( pageindex-1)] / tablesize}+1; The calculation formula of the data offset is: offset={(pageindex-1) -[tablesize / pagesize (n-1)]} pagesize =(pageindex-1) pagesize-tablesize (n-1); Among them, n represents the target sub-table number, pagesize represents the page capacity, pageindex represents the page number, tablesize represents the sub-table data threshold, and offset represents the offset.

2. A method for improving the efficiency of deep paging query of a database according to claim 1, characterized in that: The step S2 specifically includes the following steps: S21, create a sub-table with sub-table number 1, and write the business data into the sub-table with sub-table number 1; S22, continuously monitoring the data volume of the currently written sub-table, determining whether the data volume of the currently written sub-table reaches a preset sub-table data threshold, and if so, calculating the sub-table number and creating a new sub-table according to the sub-table number, and continuing to write data to the newly created sub-table until the writing of the business data is completed, thereby obtaining all sub-tables corresponding to the business conditions.

3. A method for improving the efficiency of deep paging query of a database according to claim 1, characterized in that: The page capacity is 10, and the sub-table data threshold is 100,000.

4. A terminal for improving the efficiency of deep paging query of a database, comprising a processor, a memory, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the computer program, the following steps are implemented: S1. Read business data from the original data table according to preset business conditions; S2. Establish sub-tables in sequence, and write the business data into multiple sub-tables in sequence according to a preset sub-table data threshold, to obtain all sub-tables corresponding to the business conditions; S3, receiving a paging query request from a business program under the business conditions, and calculating a target sub-table number and a data offset of a sub-table where target data is stored according to a page number and a page capacity included in the paging query request; S4, querying and acquiring the target data from all sub-tables corresponding to the business condition according to the target sub-table number, the offset and the page capacity, and returning the target data to the business program; The step S4 is specifically as follows: Determine the target sub-table where the target data is located according to the target sub-table number n, and create a query statement according to the name tablename of the target sub-table, the offset offset, and the page capacity pagesize: select from tablename limit offset,pagesize; and querying the target data from the target sub-table through the query statement, and returning the target data to the business program; The sub-table data threshold is a multiple of the page capacity, and the sub-table data threshold is greater than the page capacity. The calculation formula of the target sub-table number in step S3 is: n={[pagesize ( pageindex-1)] / tablesize}+1; The calculation formula of the data offset is: offset={(pageindex-1) -[tablesize / pagesize (n-1)]} pagesize =(pageindex-1) pagesize-tablesize (n-1); Among them, n represents the target sub-table number, pagesize represents the page capacity, pageindex represents the page number, tablesize represents the sub-table data threshold, and offset represents the offset.

5. A terminal for improving the efficiency of deep paging query of a database according to claim 4, characterized in that: The step S2 specifically includes the following steps: S21, create a sub-table with sub-table number 1, and write the business data into the sub-table with sub-table number 1; S22, continuously monitoring the data volume of the currently written sub-table, determining whether the data volume of the currently written sub-table reaches a preset sub-table data threshold, and if so, calculating the sub-table number and creating a new sub-table according to the sub-table number, and continuing to write data to the newly created sub-table until the writing of the business data is completed, thereby obtaining all sub-tables corresponding to the business conditions.

6. A terminal for improving the efficiency of deep paging query of a database according to claim 4, characterized in that: The page capacity is 10, and the sub-table data threshold is 100,000.

Images