CN114020790A

CN114020790A - Data query method and device

Info

Publication number: CN114020790A
Application number: CN202111314574.2A
Authority: CN
Inventors: 王永栎
Original assignee: China Construction Bank Corp
Current assignee: China Construction Bank Corp
Priority date: 2021-11-08
Filing date: 2021-11-08
Publication date: 2022-02-08

Abstract

The application relates to the technical field of databases, and discloses a data query method and device, which are used for solving the problem of slow query response speed caused by high concurrent access during data query. The method provided in the present application comprises: receiving a first query request sent by a client, and acquiring first partition information matched with a first query condition from a cache according to the first query condition carried in the first query request, wherein the cache comprises the first partition information corresponding to the first query condition, and the first partition information comprises an identifier of at least one partition table which is accessed and hit from a service data table by the first query condition before the first query request is received; and inquiring the service data meeting the first inquiry condition in the partition tables respectively indicated by the identification of the at least one partition table, and sending the service data to the client to finish data inquiry.

Description

Data query method and device

Technical Field

The present application relates to the field of database technologies, and in particular, to a method and an apparatus for querying data.

Background

With the development of internet technology, the data volume in the data table also grows exponentially, so that the efficiency of data table query is lower and lower. One approach is to improve query efficiency by supporting high concurrent access. However, the requirement for database thread resources is high for high concurrent access, and the database thread resources are limited, so that the query efficiency is improved to a limited extent.

Disclosure of Invention

The embodiment of the application provides a data query method and device, which are used for solving the problem of low response speed in data query.

In a first aspect, an embodiment of the present application provides a method for querying data, where the method may be applied to a database server, and the method may include: receiving a first query request sent by a client, wherein the first query request carries a first query condition; acquiring first partition information matched with the first query condition from a cache according to the first query condition; the cache comprises first partition information corresponding to the first query condition, the first partition information comprises identifiers of N partition tables which are accessed and hit from a business data table by the first query condition before the first query request is received, the business data table is divided into a plurality of partition tables, and N is a positive integer; inquiring the service data meeting the first inquiry condition from the partition tables respectively indicated by the identifiers of the N partition tables; and sending the service data to the client.

Based on the scheme, when the large data table is queried, the partition information meeting the same query condition is queried in the cache, the service data meeting the query condition is directly obtained according to the partition information, the whole service data table does not need to be traversed, and the query speed is greatly improved.

In some embodiments, the method further comprises: receiving a second query request, wherein the second query request carries a second query condition; and when the partition information matched with the first query condition is not acquired from the cache according to the second query condition, querying the service data meeting the second query condition from the service data table, and storing the second query condition and the second partition information in the cache in a correlated manner, wherein the second partition information comprises a partition identifier of the partition table where the service data meeting the second query condition is located.

Based on the above scheme, when the cache does not have partition information matching the second query condition, after the service data meeting the second query condition is queried from the multiple partition tables included in the service data table, the partition information where the queried service data is located and the second query condition are stored in the cache in a related manner, if a request including the second query condition is subsequently received, the partition information can be obtained from the cache without traversing all the partition tables, and the query efficiency can be improved.

In some embodiments, the first partition information further includes the number of data in the N partition tables that satisfy the first query condition; the first query request also carries paging information of a page to be skipped; inquiring the service data meeting the first inquiry condition from the partition tables respectively indicated by the identifiers of the at least one partition table, including: determining at least one partition table of the N partition tables, which comprises target data of the to-be-skipped pages indicated by the page information, according to the data quantity meeting the first query condition in the N partition tables; and acquiring the target data from the at least one partition table.

Based on the scheme, the partition where the target data of the current display page is located and the initial position in the partition are determined through the paging information, and the initial position in the partition are determined according to the partition and the initial position in the partition to directly acquire the target data in the data table, so that full-table scanning is avoided, and the query speed is increased.

In some embodiments, the paging information includes the number of pieces of service data displayed per page and the page number of the page to be skipped; determining at least one partition table of the target data of the to-be-skipped pages indicated by the page information in the N partition tables according to the data quantity meeting the first query condition in the N partition tables, respectively, including: determining the index range of target data to be displayed in the service data meeting the first query condition according to the page number of the to-be-skipped pages and the number of the service data displayed on each page;

and determining at least one partition table including target data to be displayed in the current to-be-jumped pages in the N partition tables according to the data quantity and the index range respectively corresponding to the N partition tables.

Based on the scheme, when a user turns pages or other users send query requests with the same query conditions through the client, the user only needs to recalculate the position of target data to be queried in the partition table, and then accurately finds out corresponding business data from the business data table according to the partition position, so that the response speed of query is improved.

In some embodiments, the starting index position of the index range satisfies a condition shown by the following formula:

start＝(Pagenum-1)×Pagesize+1；

the end index position of the index range satisfies a condition shown by the following formula:

end＝Pagenum×Pagesize；

the Pagesize represents the number of pieces of service data displayed on each page, the Pagenum represents the page number of the to-be-skipped paginated, the start represents the start index position of the index range, and the end represents the end index position of the index range.

In some embodiments, the determining, according to the data quantity and the index range respectively corresponding to the N partition tables, at least one partition table including target data to be displayed for a current page to be skipped in the N partition tables includes: and determining the data quantity included in the N1 partition tables before the query sequence of the first partition table and the starting index position smaller than the index range according to the query sequence of the N partition tables, wherein the sum of the data quantity included in the N1 partition tables and the data quantity included in the first partition table is larger than the starting index position.

In some embodiments, obtaining the target data from the at least one partition table comprises: acquiring the service data meeting the first query condition from the at least one partition table; when the sum of the data quantity included in the N1 partition tables and the data quantity included in the first partition table is greater than or equal to the end index position, determining a start position and an end position of the target data in the service data, which meets the first query condition, included in the first partition table according to the start index position and the end index position; alternatively, the first and second electrodes may be,

when the sum of the data quantity included in the N1 partition tables and the data quantity included in the first partition table is smaller than the ending index position, determining the starting position of the target data in the business data which meets the first query condition and is included in the first partition table according to the starting index position; the at least one partition table further comprises a second partition table, and the query sequence of the second partition table in the N partition tables is positioned behind the first partition table; and determining the end position of the target data in the service data which meets the first query condition and is included in the second partition table according to the end index position.

Based on the scheme, when data query is carried out, only the partition related to the target data displayed in the page to be skipped and the starting position and the ending position in the business data meeting the first query condition in the partition need to be calculated, the business data are directly obtained from the partition, and the response speed of the data query is improved.

In a second aspect, an embodiment of the present application provides an apparatus for querying data, including:

the receiving module is used for receiving a first query request sent by a client, wherein the first query request carries a first query condition;

the processing module is used for acquiring first partition information matched with the first query condition from a cache according to the first query condition;

the cache comprises first partition information corresponding to the first query condition, the first partition information comprises identifiers of N partition tables which are accessed and hit from a business data table by the first query condition before the first query request is received, the business data table is divided into a plurality of partition tables, and N is a positive integer;

the processing module is further configured to query the service data meeting the first query condition from the partition tables respectively indicated by the identifiers of the N partition tables;

and the sending module is used for sending the service data to the client.

In some embodiments, the receiving module is further configured to receive a second query request, where the second query request carries a second query condition; the processing module is further configured to, when partition information matching the first query condition is not obtained from a cache according to the second query condition, query the service data meeting the second query condition from the partition table included in the service data table, and store the second query condition and the second partition information in association with each other in the cache, where the second partition information includes a partition identifier of the partition table in which the service data meeting the second query condition is located.

In some embodiments, the first partition information further includes the number of data in the N partition tables that satisfy the first query condition; the first query request also carries paging information of a page to be skipped;

when querying the service data meeting the first query condition from the partition tables respectively indicated by the identifier of the at least one partition table, the processing module is specifically configured to: determining at least one partition table of the N partition tables, which comprises target data of the to-be-skipped pages indicated by the page information, according to the data quantity meeting the first query condition in the N partition tables; and acquiring the target data from the at least one partition table.

In some embodiments, the paging information includes the number of pieces of service data displayed per page and the page number of the page to be skipped;

when determining at least one partition table including target data of a to-be-skipped partition indicated by the partition information in the N partition tables according to the data quantity meeting the first query condition in the N partition tables, the processing module is specifically configured to: determining the index range of target data to be displayed in the service data meeting the first query condition according to the page number of the to-be-skipped pages and the number of the service data displayed on each page;

In some embodiments, the starting index position of the index range satisfies a condition shown by the following formula;

start＝(Pagenum-1)×Pagesize+1；

end＝Pagenum×Pagesize；

In some embodiments, the at least one partition table includes a first partition table, and the processing module is specifically configured to: and determining the data quantity included in the N1 partition tables before the query sequence of the first partition table and the starting index position smaller than the index range according to the query sequence of the N partition tables, wherein the sum of the data quantity included in the N1 partition tables and the data quantity included in the first partition table is larger than the starting index position.

In some embodiments, the processing module, when obtaining the target data from the at least one partition table, is specifically configured to: acquiring the service data meeting the first query condition from the at least one partition table; when the sum of the data quantity included in the N1 partition tables and the data quantity included in the first partition table is greater than or equal to the end index position, determining a start position and an end position of the target data in the service data, which meets the first query condition, included in the first partition table according to the start index position and the end index position; alternatively, the first and second electrodes may be,

In a third aspect, an embodiment of the present application provides a data query apparatus, including:

a memory and a processor;

a memory for storing program instructions;

a processor, configured to call a program instruction stored in the memory, and execute the method according to the first aspect or any embodiment of the first aspect according to an obtained program.

In a fourth aspect, the present application provides a computer-readable storage medium storing computer instructions that, when executed on a computer, cause the computer to perform the method of the first aspect or any one of the embodiments of the first aspect.

In addition, for technical effects brought by any one implementation manner of the second aspect to the fourth aspect, reference may be made to technical effects brought by different implementation manners of the first aspect, and details are not described here.

Drawings

In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings needed to be used in the description of the embodiments or the prior art will be briefly introduced below, and it is obvious that the drawings in the following description are some embodiments of the present application, and for those skilled in the art, other drawings can be obtained according to these drawings without inventive exercise.

Fig. 1 is a schematic flowchart of a data query method according to an embodiment of the present application;

fig. 2A is a schematic diagram of service data of which a page to be skipped includes only one partition table according to an embodiment of the present application;

fig. 2B is a schematic diagram of a service data of a page to be skipped, which includes two partition tables according to an embodiment of the present application;

fig. 2C is a schematic diagram of service data of a page to be skipped, which includes three partition tables according to an embodiment of the present application;

fig. 3 is a schematic diagram of an apparatus for querying data according to an embodiment of the present disclosure;

fig. 4 is a schematic structural diagram of a database server according to an embodiment of the present application;

fig. 5 is a schematic architecture diagram of a database system according to an embodiment of the present application.

Detailed Description

To make the objects, embodiments and advantages of the present application clearer, the following description of exemplary embodiments of the present application will clearly and completely describe the exemplary embodiments of the present application with reference to the accompanying drawings in the exemplary embodiments of the present application, and it is to be understood that the described exemplary embodiments are only a part of the embodiments of the present application, and not all of the embodiments.

All other embodiments, which can be derived by a person skilled in the art from the exemplary embodiments described herein without inventive step, are intended to be within the scope of the claims appended hereto. In addition, while the disclosure herein has been presented in terms of one or more exemplary examples, it should be appreciated that aspects of the disclosure may be implemented solely as a complete embodiment.

It should be noted that the brief descriptions of the terms in the present application are only for the convenience of understanding the embodiments described below, and are not intended to limit the embodiments of the present application. These terms should be understood in their ordinary and customary meaning unless otherwise indicated.

The Redis cache scheme is to cache the hot spot data, and when the inquired data is the hot spot data, the hot spot data can be directly obtained from the cache without accessing the whole data table. When there are access requests to access the data table, each access request occupies one I/O (Input/Output) thread. When data inquired by some access requests in a plurality of access requests belong to hot data, the data can be directly acquired from the cache, and an I/O thread is not occupied to access the data table. The scheme can solve the problem that the inquiry is slow due to insufficient I/O caused by high concurrent access. However, the Redis cache cannot store a large amount of data, and when a large data table is accessed, the use of the Redis cache is limited. For the Query of the Redis database, a Structured Query Language (SQL) is generally adopted. And the SQL statement is optimized by associating a plurality of tables so as to achieve the purpose of improving the query speed. However, when a data table with a large data volume is queried, the problem that data cannot be normally returned due to connection timeout caused by low SQL execution efficiency occurs, and the query speed is still greatly limited.

The application provides a data query method and a data query device, wherein the query conditions and the partition table where the business data meeting the query conditions are located are associated to serve as partition information to be stored in a cache, when the query requests with the same query conditions exist, the partition information can be directly obtained from the cache of a database, then the business data can be obtained according to the partition information, the whole business data table can be prevented from being traversed, and the query speed can be improved. Compared with a Redis cache scheme, the data is not stored, and the problem that cache is insufficient when a large data scale is inquired in Redis cache is solved. Compared with query optimized by SQL sentences, the method and the device can quickly locate the partition table where the query data is located, and improve the query speed.

The following is a brief description of technical terms related to the present application. Redis is an open-source, high-performance Key (Key) -Value (Value) database, and is a memory type non-relational (NoSQL) database with a high access speed. The storage format used by keys may be a String (String). The saving format adopted by Value may adopt a string, a List (List), a Set (Set), a Hash table (Hash), and an ordered Set (Sorted Set). Redis supports many functions, such as persisting data in memory to a hard disk, using replication to extend read performance, using fragmentation to extend write performance, and so on.

In Redis, List types are linked lists of strings sorted in insertion order. Redis supports adding new elements at the head (left) and tail (right) of the string linked list corresponding to Key as a Value matching the Key. Redis establishes different linked lists of strings for different keys. If all elements included in the string linked list corresponding to a Key are deleted, the Key corresponding to the string linked list is also deleted from the database.

The database table referred to in the present application, which may be referred to as a data table for short, or a table for short, is one of the most important components of a database. The database is a framework, and the data table is the substance content in the database. As in a "teaching management system," the teaching management database contains 4 tables, each surrounding a particular topic: the teaching aid table, the course table, the score table and the student table are used for managing relevant information of students, teachers, courses and the like in the teaching process. The independent data tables are connected through establishing relations, and the data tables become a database which can be cross consulted and is clear at a glance. Each data table in the database may correspond to a physically real table file. The contents in the data table are stored on the physical disk in the form of a table file.

The partition table related to the application is obtained by partitioning a service data table. In some embodiments, one service data table may be divided into a plurality of partition tables according to dates. A partition table corresponds to a date. Of course, other partition table partitioning modes may also be adopted, and the present application is not limited to this specifically.

The embodiment of the application provides a method for data query, and fig. 1 exemplarily shows a flow of the method for data query, which may be performed by a database server. The database server may deploy the database using redis technology. The method flow of the data query comprises the following steps:

101, a database server receives a query request.

In some embodiments, the query request may be from a client. Such as a user querying the database server for business data via a browser. The browser may adopt a paging query mode, in which a query request may be a request for querying a certain page of service data.

Illustratively, the query request may include a query condition. The query condition may include an identification of the service data table to be queried and a condition that needs to be satisfied by querying the data. Such as a vehicle management database, and the query conditions may include a license plate, a logo, and the like. For convenience of description, the query request is referred to as a first query request. The query condition carried by the query request is referred to as a first query condition.

And 102, according to the first query condition, whether the partition information meeting the first query condition is included in the cache is matched.

In some embodiments, when the cache does not include partition information satisfying the first query condition, the service data satisfying the first query condition is queried from each partition included in the service data table according to the first query condition.

In one possible example, when the service data meeting the first query condition is queried, the identifier of the partition table to which each queried service data belongs and the association of the first query condition may be saved in the cache. The partition information may be understood as an identification of the partition table stored in association with the cache and the first query condition.

Illustratively, when the identification of the partition table and the first query condition are stored in an associated manner, a key-value storage manner may be adopted. For example, the first query condition is used as a key, and the identifier of each partition table satisfying the first query condition is used as a value. Alternatively, value may be in the format of a list.

In another possible example, the identifier of the partition table to which each queried service data belongs, the number of service data included in the partition table and meeting the first query condition, and the first query condition may be stored in association. The partition information may be understood as an identifier of a partition table stored in association with the cache, the number of service data included in the partition table and meeting the first query condition, and the first query condition.

Illustratively, the identification of the partition table, the number of the service data included in the partition table and meeting the first query condition, and the first query condition may be stored in a key-value storage manner. For example, the first query condition is used as a key, and the identifier of each partition table satisfying the first query condition and the number of the service data satisfying the first query condition in the partition table are used as values. Alternatively, value may be in the format of a list. In this embodiment of the present application, a certain row of the service data table may be understood as a piece of data, and the number of the service data meeting the first query condition may be understood as the number of the service data meeting the first query condition in the partition table.

In other embodiments, when the database server determines that the cache includes partition information that satisfies the first query condition, 103 is executed. For convenience of description, the partition information satisfying the first query condition in the cache is referred to as first partition information. In some embodiments, the first partition information includes an identification of N partition tables that were hit by accessing from a business data table, which is partitioned into a plurality of partition tables, with the first query criteria prior to receiving the first query request. It is to be understood that, before receiving the first query request, the database server receives another request for querying the business data with the first query condition, and after querying the business data meeting the first query condition from the business data table based on the request, the identifier of the N partition tables where the business data meeting the first query condition is located and the query condition may be stored in the cache as the first partition information association. Therefore, when the first query request is received, the N partition tables where the first query condition is satisfied can be directly obtained from the cache.

Illustratively, the first partition information may further include the number of data included in each of the N partition tables that satisfy the first query condition.

103, querying the service data meeting the first query condition from the partition tables respectively indicated by the identifiers of the N partition tables.

By the scheme, the partition table which meets a certain query condition is preferentially queried from the cache, so that the service data which meets the query condition can be obtained from the queried partition table, traversing query on the whole service table is not needed, and query efficiency can be improved.

Optionally, the data query method may further include step 104:

and 104, the database server sends the inquired business data to the client.

In a possible implementation manner, when the business data table is partitioned according to a date, one date corresponds to one partition table. In this case, if the query condition includes a certain date or several dates, when the database server receives the query condition, the corresponding partition table may be determined according to the date or the several dates, and then the service data meeting other query parameters in the query condition may be acquired from the corresponding partition table. If the query condition only comprises one date, the date corresponds to one partition table, and only the partition table needs to be queried, so that the query rate is greatly improved.

In one possible scenario, a user may query the database server for business data via a browser. The browser may adopt a paging query mode, in which a query request may be a request for querying a certain page of service data. For example, business data satisfying a query condition may need to display multiple pages. When a user submits a page turning request through a browser, the paging query request can be triggered. Illustratively, the first query request may also carry page information of a page to be skipped. The first partition information includes an identifier of the partition table, the number of the service data satisfying the first query condition included in the partition table, and the first query condition.

In some embodiments, when querying the service data satisfying the first query condition from the N partition tables, the following steps may be performed:

determining at least one partition table of the N partition tables, which comprises target data of the to-be-skipped pages indicated by the page information, according to the data quantity meeting the first query condition in the N partition tables; the target data is then obtained from the at least one partition table.

In some embodiments, after the target data is obtained, the target data may be returned to the client, and the received target data is displayed on the page to be skipped of the browser from the client.

In some embodiments, the administrator may configure a cleaning cycle for the partition information in the cache in the database server, so that the data server periodically cleans the partition information in the cache according to the cleaning cycle. In a possible manner, when a cleaning time point of the cleaning cycle is reached and a certain query condition in the cache is not hit by the query request, the partition information corresponding to the query condition may be deleted from the cache. For example, when a certain query request in the cache is hit, a hit indication may be added to the query condition correspondence. So that when the cleaning time point of the cleaning cycle is reached, it is determined whether the partition information needs to be deleted from the cache according to the indication. At the point in time, the cleaning cycle ends and the next cleaning cycle is started. When the next cleaning cycle starts, the indication may be updated for the query condition corresponding to the partition information that is not deleted, and the updated indication is used to indicate that the query condition corresponding to the partition information is not hit. Before the cleaning cycle is finished, if the query condition corresponding to the partition information is hit, updating the indication corresponding to the query condition, so that the indication is used for indicating that the query condition is hit. For example, the cleaning cycle may be one hour, one week, one day, and so forth.

In other embodiments, the administrator may set a time threshold for the partition information in the cache, and when the database server determines that the cleaning time point of a certain cleaning cycle is reached, if the number of times that the query condition corresponding to a certain partition information in the cache is hit is less than the time threshold, the partition information is deleted from the cache. The database server may update the number of hits of the partition information corresponding to the query condition each time the query condition is hit. At the beginning of each cleaning cycle, the number of hits in the partition information may be cleared to be recounted in that cleaning cycle.

In some embodiments, the first query request may further carry paging information, where the paging information includes the number of pieces of service data displayed in each page and a page number of a page to be skipped. When at least one partition table including target data of the pages to be jumped is determined from the N partition tables, the method can be realized by the following steps:

and determining the index range of target data to be displayed in the service data meeting the first query condition according to the page number of the to-be-skipped pages and the number of the service data displayed on each page. And then, determining at least one partition table including target data to be displayed in the current to-be-jumped pages in the N partition tables according to the data quantity and the index range respectively corresponding to the N partition tables.

For example, the number of pieces of business data displayed on each page may be set to Pagesize, and the page number of the page to be skipped may be set to Pagenum. The index range may be represented by a start index position and an end index position. For example, the start index position is represented by start, the start index position of the index range satisfies the condition shown in the following formula: start ═ (Pagenum-1) × Pagesize + 1.

For another example, if the ending index position is represented by end, the ending index position of the index range satisfies the condition shown in the following formula: end ═ Pagenum × Pagesize.

In some embodiments, when the target data is obtained from the at least one partition table, a start position and an end position of the target data in the at least one partition table may be determined according to the number of the service data corresponding to each of the N partition tables and an index range of the target data of the page to be skipped in the service data meeting the first query condition, so that the target data may be obtained from the service data meeting the query included in the at least one partition table according to the start position and the end position.

According to the scheme provided by the embodiment of the application, when the query requests with the same query conditions exist, namely when a user uses a browser to page or other users send the query requests with the same query conditions through a client, the partition table where the business data meeting the query conditions are located does not need to be determined again, the position of the target data needing to be queried in the partition table only needs to be calculated again, then the corresponding business data are accurately found out from the business data table according to the located partition position, the whole data table does not need to be traversed, and the query speed is improved.

In the following, it is exemplified that the at least one partition table including the target data of the page to be skipped includes the first partition table. It is understood that the partition table including the data of the target of the page to be jumped may be 1 or more. This is not a particular limitation of the present application. And N partition tables which indicate the business data meeting the first query condition are cached. The N partition tables each include service data satisfying a first query condition. When the browser is used for inquiring, the quantity of the business data displayed on each page is fixed, so that one page may display the business data in one or more partition tables in the N partition tables. In some embodiments, the N partition tables are configured with a query order. The order may be determined based on the date or other rules may be used.

For example, when a first partition table is determined from N partition tables including traffic data satisfying a first query condition, the number of data included in the N1 partition tables before the query order of the first partition table and a start index position smaller than the index range may be determined according to the query order of the N partition tables, and the sum of the number of data included in the N1 partition tables and the number of data included in the first partition table is larger than the start index position.

For example, the number of the service data meeting the first query condition in the ith partition table of the N partition tables may be represented by a (i), and the total number s (i) of the service data meeting the first query condition in the first i partition tables may be represented as: s (i) ═ a (1) + a (2) + a (3) … … + a (i).

In a possible example, the total number of the business data meeting the first query condition in the current i-1 partition tables is less than the start index position of the target data displayed by the page number of the page to be jumped, i.e. s (i-1) < start, then the target data displayed by the page number of the page to be jumped does not exist in the ith partition table. The total number of the service data satisfying the first query condition included in the first i partition tables may be continuously calculated if s (i) > start. The ith partition table is a partition table including target data of page number display of the page to be jumped. For example, the ith partition table may be understood as the first partition table.

In another possible example, the total number of the service data satisfying the first query condition in the current i partition tables is equal to the starting index position of the target data displayed by the page number of the page to be skipped, i.e. s (i) ═ start, then the last service data in the service data satisfying the first query condition in the i-th partition table is the target data displayed by the page number of the page to be skipped. For example, the ith partition table may be understood as the first partition table.

Further, after the at least one partition table is determined, the target data is obtained from the at least one partition table, for example, the business data meeting the first query condition may be obtained from the at least one partition table first. And then determining the starting position and the ending position of the target data included in the partition table according to the data quantity included in the first partition table.

In one example, when the sum of the data amounts included in the N1 partition tables and the data amount included in the first partition table is greater than or equal to the end index position, the start position and the end position of the target data in the service data included in the first partition table that satisfies the first query condition are determined according to the start index position and the end index position.

In another example, when the sum of the data quantity included in the N1 partition tables and the data quantity included in the first partition table is less than the ending index position, determining the starting position of the target data in the service data, which meets the first query condition, included in the first partition table according to the starting index position; the at least one partition table further comprises a second partition table, and the query sequence of the second partition table in the N partition tables is positioned behind the first partition table; and determining the end position of the target data in the service data meeting the first query condition, which is included in the second partition table, according to the end index position.

For example, following the above example, s (i-1) < start, if the total number of the service data satisfying the first query condition in the first i partition tables is greater than the start index position of the target data displayed by the page number of the page to be skipped, i.e. s (i) > start, and the total number of the service data satisfying the first query condition in the first i partition tables is greater than or equal to the end index position of the target data displayed by the page number of the page to be skipped, i.e. s (i) > end, the target data displayed by the page number of the page to be skipped only includes the service data in the ith partition, and the start position and the end position of the target data displayed by the page number of the page to be skipped in the ith partition table satisfy the service data between the first queries are:

the starting positions are: and start- (s (i-1) -used (0)), wherein used (0) is the number of the service data meeting the first query condition in the first i-1 partition tables as the target data displayed in the page number displayed in the page to be jumped, that is, the target data displayed in the page number of the page to be jumped comprises the service data after the start- (s (i-1) -used (0)) service data in the service data meeting the first query condition in the ith partition table.

The end position is: end-s (i-1), that is, the target data displayed in the page number of the page to be skipped includes service data before the end-s (i-1) service data in the service data meeting the first query condition in the ith partition table.

Since s (i-1) < start, the number of service data satisfying the first query condition in the i-1 th partition as target data displayed in the page number of the page to be skipped is zero, i.e., used (0) ═ 0.

In addition, s (i) > end, that is, the end index position of the target data displayed in the page number of the page to be skipped is in the service data meeting the first query condition in the ith partition table, at this time, the target data displayed in the page number of the page to be skipped is already taken out, and it is not necessary to calculate the number of the target data displayed in the page number meeting the first query condition in the first i partition tables as the service data meeting the first query condition.

For example, referring to fig. 2A, the number of pieces of service data displayed per page may be set to 7, and taking the page number of the page to be jumped as 4 as an example, the start index position of the index range is represented by start, where the start is 22, the end index position of the index range is represented by end, where the end is 28. When i is 4, a (4) is 10, s (3) is 20, s (4) is 30, s (3) < start, and s (4) > end. At this time, the number of the service data meeting the first query condition in the 3 rd partition table as the target data displayed in the page number of the page to be skipped is: when used (0) is 0, the starting position of the target data displayed by the page number of the page to be skipped in the service data meeting the first query in the 4 th partition is as follows: start- (s (i-1) -used (0)) -22- (20-0) -2, end position: and end-s (i-1) ═ 28-20-8, namely the target data displayed by the page number of the page to be jumped comprises 2 nd to 8 th service data in the service data meeting the first query condition in the 4 th partition table.

For another example, s (i-1) < start, if the total number of the service data satisfying the first query condition in the first i partition tables is greater than the start index position of the target data displayed by the page number of the page to be skipped, i.e. s (i) > start, and the total number of the service data satisfying the first query condition in the first i partition tables is less than the end index position of the target data displayed by the page number of the page to be skipped, i.e. s (i) < end, the target data displayed by the page number of the page to be skipped includes the service data in the ith partition table and also includes the service data in the (i +1) th partition table. And if the total number of the service data meeting the first query condition in the first i +1 partition tables is judged to be greater than or equal to the end index position of the target data displayed by the page number of the page to be jumped, the target data displayed by the page number of the page to be jumped is part of data in the service data meeting the first query condition in the i +1 th partition.

The starting position and the ending position of target data displayed by the page number of the page to be jumped in the service data meeting the first query condition in the ith partition table are as follows:

The end position is: and t is the value of the number of the business data meeting the first query condition in the ith partition table, and t is a (i), and the target data displayed in the page number of the page to be skipped comprises the business data before the t-th business data in the business data meeting the first query condition in the ith partition table.

The starting position and the ending position of target data displayed by the page number of the page to be jumped in business data meeting the first query condition in the (i +1) th partition table are as follows:

the starting positions are: start- (s (i) -used (1)), wherein used (1) is the number of the service data satisfying the first query condition in the first i partition tables as the target data displayed in the page number displayed in the page to be skipped, that is, the target data displayed in the page number of the page to be skipped includes the service data after the first start- (s (i) -used (1)) service data in the service data satisfying the first query condition in the ith partition table.

The end position is: end-s (i), that is, the target data displayed in the page number of the page to be skipped includes the service data before the end-s (i) service data in the service data meeting the first query condition in the (i +1) th partition table.

At this time, the number of the service data meeting the first query condition in the first i-1 partition tables as the target data displayed in the page number displayed by the page to be skipped meets the following formula:

used(0)＝0。

the number of the service data meeting the first query condition in the first i partition tables as the target data displayed in the page number displayed by the page to be skipped meets the following formula:

used(1)＝finish(1)-begin(1)+1；

the begin position and the end position of target data displayed by the page number of the page to be jumped in the ith partition table and meeting the first query condition are both begin data and finish data in the ith partition table.

For example, as shown in fig. 2B, the number of pieces of service data displayed per page is set to 7, and the page number of the page to be jumped is set to 4, then the start index position of the index range is represented by start, where the start is 22, the end index position of the index range is represented by end, where the end is 28. When i is 4, a (4) is 7, s (4) is 25, s (3) is 18, s (3) < start, s (4) > start, and s (4) < end, at this time, the target data displayed in the page number of the page to be skipped includes the service data satisfying the first query condition in the 4 th and 5 th partition tables. The number of the service data meeting the first query condition in the first 3 partition tables as the target data displayed in the page number of the page to be skipped is as follows: when used (0) is 0, the starting position of the target data displayed by the page number of the page to be skipped in the service data meeting the first query in the 4 th partition is as follows: start- (s (i-1) -used (0)) -22- (18-0) -4, end position: and t (a), (i) a (4) and 7, namely, the target data displayed by the page number of the page to be jumped comprises the 4 th to 7 th business data in the business data meeting the first query condition in the 4 th partition table. And then, calculating the position of the business data which meets the first query condition in the 5 th partition table of the target data displayed in the page number of the page to be jumped. When a (5) is 6, that is, s (5) is 31, the number of the service data satisfying the first query condition in the first 4 partition tables as the target data displayed in the page number of the page to be skipped is: the used (1) -finish (1) -begin (1) + 1-7-4 + 1-4, and the starting position of the target data displayed by the page number of the page to be skipped in the service data satisfying the first query in the 5 th partition is: start- (s (i) -used (1)) -22- (25-4) ═ 1, and the end position is: end-s (i) 28-25 (3), that is, the target data displayed by the page number of the page to be skipped further includes the 1 st to 3 rd service data in the service data satisfying the first query condition in the 5 th partition table.

For another example, s (i-1) < start, if the total number of the service data satisfying the first query condition in the first i partition tables is greater than the start index position of the target data displayed by the page number of the page to be skipped, i.e. s (i) > start, and the total number of the service data satisfying the first query condition in the first i partition tables is less than the end index position of the target data displayed by the page number of the page to be skipped, i.e. s (i) < end, the target data displayed by the page number of the page to be skipped includes the service data in the ith partition table. The method also comprises business data in the (i +1) th partition table. And then, calculating the quantity of target data which are displayed by taking the service data meeting the first query condition in the first i partition tables as the page number of the page to be jumped. And if the total number of the business data meeting the first query condition in the first i +1 partition tables is judged to be less than the end index position of the target data displayed by the page number of the page to be jumped, the target data displayed by the page number of the page to be jumped also comprises the business data in the i +2 th partition table. And then, calculating the quantity of target data which are displayed by using the business data meeting the first query condition in the first i +1 partition tables as the page number of the page to be jumped. And by analogy, if the total number of the service data meeting the first query condition in the first i + r partition tables is judged to be greater than or equal to the end index position of the target data displayed by the page number of the page to be skipped, the target data displayed by the page number of the page to be skipped is the data in the service data meeting the first query condition in the i + r partition tables. At this time, the target data displayed by the page number to be jumped is in the ith to (i + r) th partition tables.

the starting positions are: and start- (s (i) -used (1)), wherein used (1) is the number of the service data meeting the first query condition in the first i partition tables as the target data displayed in the page number of the page to be skipped, that is, the target data displayed in the page number of the page to be skipped includes the service data after the first start- (s (i) -used (1)) service data in the service data meeting the first query condition in the (i +1) th partition table.

The end position is: and t is the value of the number of the business data meeting the first query condition in the (i +1) th partition table, t is a (i +1), and the target data displayed in the page number of the page to be skipped comprises the business data before the t-th business data in the business data meeting the first query condition in the (i +1) th partition table.

By analogy, the starting position and the ending position of the target data displayed by the page number of the page to be skipped in the business data meeting the first query condition in the i + r-th partition table are as follows:

the starting positions are: the method comprises the steps of starting- (s (i + r-1) -used (r)), wherein the used (r) is the number of the service data meeting the first query condition in the first i + r-1 partition tables as target data displayed in the page number displayed by the page to be jumped, namely the target data displayed in the page number of the page to be jumped comprises the service data behind the first starting- (s (i + r-1) -used (r)) service data meeting the first query condition in the i + r partition tables.

The end position is: end-s (i + r-1), namely, the target data displayed in the page number of the page to be jumped comprises service data before the end-s (i + r-1) service data in the service data meeting the first query condition in the (i + r) th partition table.

used(0)＝0；

used(1)＝finish(1)-begin(1)+1；

The number of the service data meeting the first query condition in the first i + r-1 partition tables as the target data displayed in the page number displayed by the page to be jumped meets the following formula:

used (r) finish (r) -begin (r) +1+ used (r-1);

wherein begin (r-1) and finish (r-1) are both the starting position and the ending position of target data displayed by the page number of the page to be jumped in the business data meeting the first query condition in the (i + r-1) th partition table, and used (r-1) is the quantity of the target data displayed by the page number of the page to be jumped, wherein the business data meeting the first query condition in the first i + r-2 partition tables.

For example, as shown in fig. 2C, the number of pieces of service data displayed per page may be set to 7, and the page number of the page to be jumped is set to 4, then the start index position of the index range is represented by start, where the start is 22, the end index position of the index range is represented by end, where the end is 28. When i is 3, s (2) is 19, a (3) is 4, a (4) is 3, and a (5) is 4, s (3) is 23, s (4) is 26, and s (5) is 30. At this time, s (2) < start, s (3) > start, s (3) < end, s (4) < end, and s (5) > end. At this time, the target data displayed in the page number of the page to be skipped includes the service data meeting the first query condition in the partition tables of 3 rd, 4 th and 5 th. The number of the service data meeting the first query condition in the 2 nd partition table as the target data displayed in the page number of the page to be jumped is as follows: when used (0) is 0, the starting position of the target data displayed by the page number of the page to be skipped in the service data meeting the first query in the 3 rd partition is as follows: start- (s (i-1) -used (0)) -22- (19-0) -3, and end position: and t (a), (i) a (3) and 4, namely the target data displayed by the page number of the page to be jumped comprises the 3 rd and 4 th service data in the service data meeting the first query condition in the 3 rd partition table. And then calculating the position of the business data meeting the first query condition in the 4 th partition table of the target data displayed in the page number of the page to be jumped. The number of the service data meeting the first query condition in the first 3 partition tables as the target data displayed in the page number of the page to be skipped is as follows: the used (1) -finish (1) -begin (1) + 1-4-3 + 1-2, and the starting position of the target data displayed by the page number of the page to be skipped in the service data satisfying the first query in the 4 th partition table is: start- (s (i) -used (1)) -22- (23-2) ═ 1, and the end position is: and t (i +1) a (4) 3, that is, the target data displayed by the page number of the page to be jumped also includes the 1 st to 3 rd business data in the business data meeting the first query condition in the 4 th partition table. And then calculating the position of the service data meeting the first query condition in the 5 th partition table of the target data displayed in the page number of the page to be jumped. The number of the service data meeting the first query condition before the first 4 partition tables as the target data displayed in the page number of the page to be jumped is as follows: if used (2) -finish (2) -begin (2) +1+ used (1) -3-1 +1+ 2-5, the start position of the target data displayed by the page number of the page to be skipped in the 5 th partition, which satisfies the service data between the first queries, is: start- (s (i +1) -used (2)) -22- (26-5) -1, and the end position is: and end-s (i +1) ═ 28-26 ═ 2, namely the target data displayed by the page number of the page to be jumped comprises 1 st to 2 nd business data in the business data meeting the first query condition in the 5 th partition table.

The application embodiment provides a data query device 300, fig. 3 schematically shows a schematic diagram of the data query device, and the device 300 includes a receiving module 301, a processing module 302 and a sending module 303.

A receiving module 301, configured to receive a first query request sent by a client, where the first query request carries a first query condition;

a processing module 302, configured to obtain, from a cache, first partition information that matches the first query condition according to the first query condition;

the processing module 302 is further configured to query the service data meeting the first query condition from the partition tables respectively indicated by the identifiers of the N partition tables;

a sending module 303, configured to send the service data to the client.

In some embodiments, the receiving module 301 is further configured to receive a second query request, where the second query request carries a second query condition;

the processing module 302 is further configured to, when partition information that matches the first query condition is not obtained from a cache according to the second query condition, query the service data that meets the second query condition from the partition table included in the service data table, and store the second query condition and the second partition information in association with each other in the cache, where the second partition information includes a partition identifier of the partition table in which the service data that meets the second query condition is located.

when querying the service data meeting the first query condition from the partition tables respectively indicated by the identifier of the at least one partition table, the processing module 302 is specifically configured to:

determining at least one partition table of the N partition tables, which comprises target data of the to-be-skipped pages indicated by the page information, according to the data quantity meeting the first query condition in the N partition tables;

and acquiring the target data from the at least one partition table.

when determining, according to the data quantity meeting the first query condition in the N partition tables, at least one partition table including target data of a to-be-skipped page indicated by the page information in the N partition tables, the processing module 302 is specifically configured to:

determining the index range of target data to be displayed in the service data meeting the first query condition according to the page number of the to-be-skipped pages and the number of the service data displayed on each page;

start＝(Pagenum-1)×Pagesize+1；

end＝Pagenum×Pagesize；

In some embodiments, the at least one partition table includes a first partition table, and the processing module 302 is specifically configured to:

and determining the data quantity included in the N1 partition tables before the query sequence of the first partition table and the starting index position smaller than the index range according to the query sequence of the N partition tables, wherein the sum of the data quantity included in the N1 partition tables and the data quantity included in the first partition table is larger than the starting index position.

In some embodiments, the processing module 302, when obtaining the target data from the at least one partition table, is specifically configured to:

acquiring the service data meeting the first query condition from the at least one partition table;

when the sum of the data quantity included in the N1 partition tables and the data quantity included in the first partition table is greater than or equal to the end index position, determining a start position and an end position of the target data in the service data, which meets the first query condition, included in the first partition table according to the start index position and the end index position; alternatively, the first and second electrodes may be,

Referring to fig. 4, a schematic structural diagram of a database server 400 according to an embodiment of the present application is provided. Database server 400 includes memory 410 and processor 420, and database server 400 may be deployed in the form of a server cluster.

The memory 410 is used for storing program instructions.

The processor 420 is configured to call the program instructions stored in the memory 410, and execute the data query method according to the obtained program.

In some embodiments, the database server 400 further comprises a communication interface, which is not shown in fig. 4. The communication interface is used for receiving the query request sent by the customer service end and sending the queried service data to the client. In the embodiment of the present invention, the specific connection medium between the memory 410, the processor 420, and the communication interface is not limited, for example, a bus may be divided into an address bus, a data bus, a control bus, and the like.

In the embodiments of the present application, the processor may be a general-purpose processor, a digital signal processor, an application specific integrated circuit, a field programmable gate array or other programmable logic device, a discrete gate or transistor logic device, or a discrete hardware component, and may implement or execute the methods, steps, and logic blocks disclosed in the embodiments of the present application. A general purpose processor may be a microprocessor or any conventional processor or the like. The steps of a method disclosed in connection with the embodiments of the present application may be directly implemented by a hardware processor, or may be implemented by a combination of hardware and software modules in a processor.

In the embodiment of the present application, the memory may be a nonvolatile memory, such as a Hard Disk Drive (HDD) or a solID-state drive (SSD), and may also be a volatile memory (RAM), for example, a random-access memory (RAM). The memory can also be, but is not limited to, any other medium that can be used to carry or store desired program code in the form of instructions or data structures and that can be accessed by a computer. The memory in the embodiments of the present application may also be circuitry or any other device capable of performing a storage function for storing program instructions and/or data.

In some embodiments, a cache, not shown in FIG. 4, may be included in processor 420. Of course, the cache memory may also be disposed outside of the processor 420 and coupled to the processor 420.

Database server 400 may be deployed in a database system. The database system may include a storage unit 500, where the storage unit 500 includes a plurality of memories for storing the service data table, referring to fig. 5, where in fig. 5, an example includes M memories, where M is a positive integer.

Based on the same technical concept, embodiments of the present application also provide a computer-readable non-volatile storage medium, which includes computer-readable instructions, and when the computer-readable instructions are read and executed by a computer, the computer-readable instructions cause the computer to perform the above-mentioned data query method.

According to the data query method provided by the embodiment of the application, the query conditions and the partition information where the data meeting the query conditions are located are stored in the database cache in an associated manner, rather than the data itself, so that the problem of insufficient cache when a large data scale is queried is solved. During query, the position of the target data of the page to be skipped in the partition table and the position in the partition table are determined through the paging information, so that the target data are directly obtained from the partition table, full-table scanning is avoided, and the query speed is increased.

As will be appreciated by one skilled in the art, embodiments of the present application may be provided as a method, system, or computer program product. Accordingly, the present application may take the form of an entirely hardware embodiment, an entirely software embodiment or an embodiment combining software and hardware aspects. Furthermore, the present application may take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, and the like) having computer-usable program code embodied therein.

The present application is described with reference to flowchart illustrations and/or block diagrams of methods, apparatus (systems), and computer program products according to the application. It will be understood that each flow and/or block of the flow diagrams and/or block diagrams, and combinations of flows and/or blocks in the flow diagrams and/or block diagrams, can be implemented by computer program instructions. These computer program instructions may be provided to a processor of a general purpose computer, special purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.

These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer-readable memory produce an article of manufacture including instruction means which implement the function specified in the flowchart flow or flows and/or block diagram block or blocks.

These computer program instructions may also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.

It will be apparent to those skilled in the art that various changes and modifications may be made in the present application without departing from the spirit and scope of the application. Thus, if such modifications and variations of the present application fall within the scope of the claims of the present application and their equivalents, the present application is intended to include such modifications and variations as well.

Claims

1. A method of data querying, comprising:

receiving a first query request sent by a client, wherein the first query request carries a first query condition;

acquiring first partition information matched with the first query condition from a cache according to the first query condition;

inquiring the service data meeting the first inquiry condition from the partition tables respectively indicated by the identifiers of the N partition tables;

and sending the service data to the client.

2. The method of claim 1, wherein the method further comprises:

receiving a second query request, wherein the second query request carries a second query condition;

and when the partition information matched with the first query condition is not acquired from the cache according to the second query condition, querying the service data meeting the second query condition from the service data table, and storing the second query condition and the second partition information in the cache in a correlated manner, wherein the second partition information comprises a partition identifier of the partition table where the service data meeting the second query condition is located.

3. The method according to claim 1 or 2, wherein the first partition information further includes the amount of data satisfying the first query condition in the N partition tables; the first query request also carries paging information of a page to be skipped;

inquiring the service data meeting the first inquiry condition from the partition tables respectively indicated by the identifiers of the N partition tables, which comprises the following steps:

and acquiring the target data from the at least one partition table.

4. The method as claimed in claim 3, wherein the paging information includes the number of pieces of service data displayed per page and a page number of a page to be skipped;

determining at least one partition table of the target data of the to-be-skipped pages indicated by the page information in the N partition tables according to the data quantity meeting the first query condition in the N partition tables, respectively, including:

5. The method of claim 4, wherein a starting index position of the index range satisfies a condition shown by the following formula:

start＝(Pagenum-1)×Pagesize+1；

end＝Pagenum×Pagesize；

6. The method according to claim 4 or 5, wherein the at least one partition table includes a first partition table, and determining at least one partition table including target data to be displayed for the current page to be skipped in the N partition tables according to the data amount and the index range respectively corresponding to the N partition tables includes:

7. The method of claim 6, wherein obtaining the target data from the at least one partition table comprises:

8. An apparatus for querying data, comprising:

and the sending module is used for sending the service data to the client.

9. The apparatus of claim 8, wherein the receiving module is further configured to receive a second query request, where the second query request carries a second query condition;

the processing module is further configured to, when partition information matching the first query condition is not obtained from a cache according to the second query condition, query the service data meeting the second query condition from the partition table included in the service data table, and store the second query condition and the second partition information in association with each other in the cache, where the second partition information includes a partition identifier of the partition table in which the service data meeting the second query condition is located.

10. The apparatus according to claim 8 or 9, wherein the first partition information further includes the amount of data satisfying the first query condition in the N partition tables; the first query request also carries paging information of a page to be skipped;

when querying the service data meeting the first query condition from the partition tables respectively indicated by the identifier of the at least one partition table, the processing module is specifically configured to:

and acquiring the target data from the at least one partition table.

11. The apparatus of claim 10, wherein the paging information includes a number of pieces of service data displayed per page and a page number of a page to be skipped;

when determining at least one partition table including target data of a to-be-skipped partition indicated by the partition information in the N partition tables according to the data quantity meeting the first query condition in the N partition tables, the processing module is specifically configured to:

12. The apparatus of claim 11, wherein a starting index position of the index range satisfies a condition shown by the following formula;

start＝(Pagenum-1)×Pagesize+1；

end＝Pagenum×Pagesize；

13. The apparatus according to claim 11 or 12, wherein the at least one partition table comprises a first partition table, and the processing module is specifically configured to:

14. The apparatus of claim 13, wherein the processing module, when retrieving the target data from the at least one partition table, is specifically configured to:

15. A data query apparatus, comprising:

a memory and a processor;

a memory for storing program instructions;

a processor for calling the program instructions stored in the memory and executing the method of any one of claims 1 to 7 according to the obtained program.

16. A computer-readable storage medium having stored thereon computer instructions which, when executed on a computer, cause the computer to perform the method of any one of claims 1 to 7.