CN113590657A

CN113590657A - Data query method and device based on database shards and server

Info

Publication number: CN113590657A
Application number: CN202110909319.6A
Authority: CN
Inventors: 李翰
Original assignee: China Mobile Communications Group Co Ltd; MIGU Culture Technology Co Ltd
Current assignee: China Mobile Communications Group Co Ltd; MIGU Culture Technology Co Ltd
Priority date: 2021-08-09
Filing date: 2021-08-09
Publication date: 2021-11-02

Abstract

The embodiment of the invention relates to the technical field of databases, and discloses a data query method, a device and a server based on database fragments.

Description

Data query method and device based on database shards and server

Technical Field

The embodiment of the invention relates to the technical field of databases, in particular to a data query method, a data query device and a server based on database shards.

Background

As the database stores more and more data, the time delay of the single table of the database responding to the query is larger and larger. At present, the database fragmentation technology is more and more widely applied, an original single table is horizontally split into a plurality of fragments with the same structure, and data of the original database-in single table is changed into data to be stored in the plurality of fragments. Thus, the data amount of the single data table is reduced, and the load of the single table is reduced.

However, the inventor of the present application finds in research that even after data fragmentation, the current database also has the problems of large query workload and slow query speed.

Disclosure of Invention

In view of the above problems, embodiments of the present invention provide a data query method, apparatus, device and system based on database shards, which are used to solve the problems in the prior art.

According to an aspect of the embodiments of the present invention, a data query method based on database shards is provided, including:

acquiring a data query request, wherein the data query request is used for querying data in a first page number, and the first page number is a page number obtained by sequencing all data in a database;

acquiring a first intermediate data set according to the fragment size, the page size and the total data amount of the database; the first intermediate data set comprises data in the first page number;

determining a maximum value and a minimum value of data in the first page number from the first intermediate data set according to the ordering of the data in the first intermediate data set in all data and the range of the data in the first page number;

acquiring data with values within a preset range from each fragment of the database, wherein the preset range is greater than or equal to the minimum value and less than or equal to the maximum value;

and determining the data in the preset range as query data corresponding to the data query request.

Preferably, the obtaining a first intermediate data set according to the fragmentation size, the paging size, and the total data amount of the database includes:

acquiring a starting subscript and a terminating subscript of the data in the first page number in each database fragment according to the fragment size, the page size and the total data amount of the database;

according to the starting subscript and the ending subscript, respectively acquiring corresponding data values from the sorted database fragments;

each of the data values constitutes a first intermediate data set.

Preferably, the obtaining a start subscript and a stop subscript of data in the first page number in each database fragment according to the fragment size, the page size, and the total data amount of the database includes:

determining a starting subscript and a terminating subscript corresponding to a first page in the database according to the first page and the page size;

determining the initial subscript of the first page in each database fragment according to the initial subscript, the page size and the total data amount corresponding to the first page;

and determining the termination index of the first page in each database fragment according to the termination index, the paging size and the total data amount corresponding to the first page.

Preferably, the determining the maximum value and the minimum value of the data in the first page number from the first intermediate data set according to the sorting of the data in the first intermediate data set in all data and the range of the data in the first page number includes:

sorting the first set of intermediate data;

performing a binary search in the sorted first intermediate data set to obtain an intermediate value;

acquiring the quantity of data not greater than the intermediate value from each database fragment;

determining the subscript value corresponding to the intermediate value in the database according to the number;

if the subscript value is within the range of the data of the first page number, the intermediate value is put into a second intermediate data set until the halving search of the sorted first intermediate data set is completed;

and taking the minimum value in the second intermediate data set as the minimum value of the data in the first page number, and taking the maximum value in the second intermediate data set as the maximum value of the data in the first page number.

Preferably, the obtaining the quantity of data not greater than the intermediate value from each database shard includes:

traversing each database fragment to acquire the quantity of data which is not greater than the intermediate value in each database fragment;

and acquiring the sum of the corresponding quantity of each database fragment.

Preferably, if the subscript value is within the range of data in the first page number, placing the intermediate value into a second intermediate data set, including:

and if the subscript value is greater than or equal to the starting subscript corresponding to the first page code, and the subscript value is less than or equal to the ending subscript corresponding to the first page code, placing the intermediate value into a second intermediate data set.

Preferably, if the subscript value is within the range of data in the first page number, placing the intermediate value into a second intermediate data set, further comprising:

if the subscript value is greater than the termination subscript corresponding to the first page, judging whether a plurality of same maximum values exist in the numerical values which are not greater than the intermediate values in each data fragment;

if so, subtracting the number of the remaining same maximum values from the subscript value;

and if the subscript value after the value reduction is greater than or equal to the starting subscript corresponding to the first page code and less than or equal to the ending subscript corresponding to the first page code, putting the intermediate value into a second intermediate data set.

An embodiment of the present application further provides a server, including: the system comprises a processor, a memory, a communication interface and a communication bus, wherein the processor, the memory and the communication interface complete mutual communication through the communication bus;

the memory is used for storing at least one executable instruction, and the executable instruction causes the processor to execute the data query method based on the database shards.

The embodiment of the present application further provides a computer-readable storage medium, where at least one executable instruction is stored in the storage medium, and when the executable instruction runs on a server, the server executes the database fragment-based data query method.

According to the method and the device, the first intermediate data set is obtained, the maximum value and the minimum value of the data in the first page number are determined according to the sequence of the data in all the data in the first intermediate data set and the range of the data in the first page number, the query result is determined according to the maximum value and the minimum value, the query result can be accurately determined, and compared with the prior art, the method and the device do not need to perform integral sequence on all fragments in a database, reduce the complexity of data query, and improve the speed of data query.

The foregoing description is only an overview of the technical solutions of the embodiments of the present invention, and the embodiments of the present invention can be implemented according to the content of the description in order to make the technical means of the embodiments of the present invention more clearly understood, and the detailed description of the present invention is provided below in order to make the foregoing and other objects, features, and advantages of the embodiments of the present invention more clearly understandable.

Drawings

The drawings are only for purposes of illustrating embodiments and are not to be construed as limiting the invention. Also, like reference numerals are used to refer to like parts throughout the drawings. In the drawings:

FIG. 1 is a schematic diagram illustrating a database shard-based data query flow provided by an embodiment of the present invention;

FIG. 2 is a schematic diagram illustrating a binary search process provided by an embodiment of the present invention;

FIG. 3 is a schematic diagram illustrating a binary search provided by an embodiment of the present invention;

FIG. 4 is another schematic diagram of a binary search provided by an embodiment of the present invention;

FIG. 5 is another schematic diagram of a binary search provided by an embodiment of the present invention;

FIG. 6 is a block diagram of a database shard-based data query device according to an embodiment of the present invention;

fig. 7 is a block diagram of a server according to an embodiment of the present invention.

Detailed Description

Exemplary embodiments of the present invention will be described in more detail below with reference to the accompanying drawings. While exemplary embodiments of the invention are shown in the drawings, it should be understood that the invention can be embodied in various forms and should not be limited to the embodiments set forth herein.

With the increasing development of internet technology, more and more data need to be stored in a database, and in order to realize rapid search of stored data, a fragmentation technology is generally adopted in the currently common database technology to manage and store data so as to improve the query efficiency of data. As shown in table 1, the table structure of the foo table in the currently common MySQL database is:

TABLE 1

Field(s)	Type (B)	Description of field
			id	int(11)auto_increment	Self-increment key
name	varchar(10)	Name (R)
			score	int(11)	Integration

In the database, data is stored through the foo table, assuming that the foo is divided into 4 slices (Slice 0-Slice 3), and assuming that 100 pieces of data are stored in 4 slices on average, each Slice stores 25 pieces of data, and the score fields of each Slice are collected as follows:

TABLE 2

Slicing	Score with unordered slices
		Slice0



		81,67,70,46,17,96,5,48,61,68,96,98,23,49,63,93,85,43,72,16,99,23,86,49,58
Slice1			49,4,98,43,31,65,79,36,28,86,54,22,51,84,86,94,6,45,89,71,86,9,54,84,46
	Slice2	91,12,40,90,49,16,90,1,32,4,42,48,39,18,14,73,89,64,34,8,17,70,56,96,41
Slice3			69,54,20,88,1,72,76,97,63,69,28,5,86,61,59,43,56,56,1,77,96,13,2,8,85

Assuming that, in the using process, a user needs to query all data stored in the database, page the data stored in the database by 4 data per page, and query the data of page 3, the following operations need to be performed:

first sorting:

firstly, the data of each segment is put into the first-level cache in sequence, and as shown in table 3, the table is a table obtained by sorting the data of each segment:

TABLE 3

And (3) second sequencing:

applying for a large enough buffer, and then inserting the data of each fragment into the buffer in sequence to form an overall pull-through data sequence, as shown in table 4:

TABLE 4

Paging:

after all the data are sorted, paging and fetching are performed on all the data, and when paging is performed according to 4 data per page, the 3 rd page contains the following data: {5,6,8,8}.

It can be seen from the above that, the data query method needs a huge cache space, and as the data volume increases, the number of fragments increases, the data volume of each fragment also increases, and the space complexity becomes higher and higher. Moreover, the data query method needs to sequence the data twice, which is not efficient and results in higher time complexity.

In order to solve the technical problem in the foregoing solution, an embodiment of the present application provides a data query method based on database shards, which can well solve the problem in the foregoing solution, and as shown in fig. 1, is a flow chart of the data query method based on database shards provided in the embodiment of the present application, and meanwhile, in order to better compare the scheme provided in the present application with the foregoing solution, the embodiment of the present application still takes the foregoing data as an example for description, specifically, the method includes:

step 101: acquiring a data query request;

the data query request is used for querying data in a first page number, and the first page number is a page number obtained by sequencing all data in the database. In the embodiment of the present application, still taking the database in the above scheme as an example for description, the data stored in the database is paged according to 4 data per page. Assuming that the data query request requests to query the data of the 3 rd page, the data of the 3 rd page refers to the data located on the 3 rd page after all the data in the database are sorted and paged, and therefore, the first page number is 3.

Step 102: acquiring a first intermediate data set according to the fragment size, the page size and the total data amount of the database, wherein the first intermediate data set comprises data in the first page number;

the fragment size, the page size and the total data amount of the database are preset data in the system, and the fragment size, the page size and the total data amount stored in the database can be preset in the database.

Specifically, the starting subscript and the ending subscript corresponding to a first page in the database are determined according to the first page and the page size, the starting subscript of the first page in each database fragment is determined according to the starting subscript, the page size and the total data amount corresponding to the first page, and the ending subscript of the first page in each database fragment is determined according to the ending subscript, the page size and the total data amount corresponding to the first page. And then, according to the starting subscript and the ending subscript in each database fragment, respectively acquiring corresponding data values from each sorted database fragment, and forming each data value into a first intermediate data set.

In this embodiment of the application, the first page number is defined as pageIndex, the tile size of the database is sliceSize, the page size is pageSize, the total amount of data is the amount of all data stored in the database, and the total amount of data is defined as total, as can be seen from the database configuration in the above scheme, the first page number pageIndex is 3, the tile size of the database is sliceSize is 25, the page size pageSize is 4, and the total amount of data is 100.

Firstly, according to the first pageIndex and the paging size pageSize, a starting index startIndex and an ending index endIndex of the first pageIndex corresponding to all data in the database are calculated, where:

startIndex＝pageSize*(pageIndex-1)；

endIndex＝pageSize*pageIndex-1；

since the first page code pageIndex is 3 and pageSize is 4, therefore:

startIndex＝4*(3-1)＝8；

endIndex＝4*3-1＝11；

therefore, the index of the first page in the database for page 3 is 8 at the beginning and 11 at the end.

Then, calculating a start subscript startInSlice and a stop subscript endInSlice of the first page in each database fragment according to the start subscript startIndex, the stop subscript endIndex, the fragment size sliceSize and the total data amount total corresponding to the first page, wherein:

startIndexInSlice＝sliceSize*startIndex/total；

endIndexInSlice＝sliceSize*endIndex/total；

wherein the startIndexInSlice is a value rounded downwards, and the endInSlice is a value rounded upwards.

In the above example, sliceSize is 25, total is 100, startIndex is 8, endIndex is 11, so:

startIndexInSlice＝25*8/100＝2；

endIndexInSlice＝25*11/100＝2.75；

rounding up endIndexInSlice, endIndexInSlice is 3.

Finally, acquiring corresponding data values from each database fragment according to the starting subscript and the ending subscript in the database fragments, wherein each data value forms a first intermediate data set;

specifically, the data in each database fragment needs to be sorted, and the sorted data is shown in table 5:

TABLE 5

Slicing	Score after ordering of the slices
		Slice0



		5,16,17,23,23,43,46,48,49,49,58,61,63,67,68,70,72,81,85,86,93,96,96,98,99
Slice1			4,6,9,22,28,31,36,43,45,46,49,51,54,54,65,71,79,84,84,86,86,86,89,94,98
	Slice2	1,1,4,8,14,16,17,18,32,34,39,40,41,42,48,49,56,64,70,73,89,90,90,91,96
Slice3			1,1,2,5,8,13,20,28,43,54,56,56,59,61,63,69,69,72,76,77,85,86,88,96,97

According to the starting subscript startInExInSlice and the ending subscript endInInSlice in the database fragments, respectively acquiring corresponding data values from the sorted database fragments, wherein the value taking result is as follows:

for Slice0, the data with subscripts 2 and 3 are: {17,23 };

for Slice1, the data with subscripts 2 and 3 are: {9,22 };

for Slice2, the data with subscripts 2 and 3 are: {4,8 };

for Slice3, the data with subscripts 2 and 3 are: {2,5}.

Assembling the data taken out from the fragments into a first intermediate data set meta-set:

meta-set＝{17,23,9,22,4,8,2,5}。

step 103: determining a maximum value and a minimum value of data in the first page number from the first intermediate data set according to the ordering of the data in the first intermediate data set in all data and the range of the data in the first page number;

after the data in the first intermediate data set is sorted, any data in the sorted first intermediate data set can be selected for comparison, the number of data meeting the condition is screened out, and then whether the data meeting the condition falls in the range of the data in the first page is judged according to the range of the data determined by the start subscript and the end subscript of the data in the first page until all the data in the first intermediate data set are compared, so that the maximum value and the minimum value of the data in the first page are determined from the first intermediate data set. The maximum value and the minimum value in the first page number are screened out by comparing all the data in the sorted first intermediate data set, so that the secondary sorting of all the data in the database is avoided, and the efficiency of data screening is improved.

Further, in order to improve the comparison efficiency, in the embodiment of the present application, another method is further provided, specifically, as shown in fig. 2, the method includes the following steps:

step 1031: sorting the data in the first intermediate data set;

the data in the first intermediate data set meta-set are sorted from small to large, and the sorted first intermediate data set is:

meta-set＝{2,4,5,8,9,17,22,23}。

step 1032: performing a binary search in the sorted first intermediate data set to obtain an intermediate value;

in order to improve the efficiency of data comparison, in the embodiment of the present application, it is proposed to perform data acquisition by a binary search.

First-time halving search:

performing a binary search in the sorted first intermediate data set meta-set, where the length len (meta-set) of the first intermediate data set is 8, and the intermediate value of the mean-set is meta-set [ len (meta-set)/2] ═ meta-set [4] ═ 9, as shown in fig. 3, illustrating the binary search process, where len (meta-set)/2 is a half of the length of the array.

And (5) second-time halving search:

as shown in fig. 4, the second binary search needs to be performed in the meta-set {2,4,5,8,9}, and len (meta-set) is 5, so that the median meta-set value is meta-set [ len (meta-set)/2] ═ meta-set [2] ═ 5.

And (3) third-time halving search:

as shown in fig. 5, the third binary search needs to be performed in the set meta-set {5,8,9}, len (meta-set) is 3, and the median value of meta-set is meta-set [ len (meta-set)/2] ═ meta-set [1] ═ 8.

And repeating the steps until all data are searched.

It should be noted that the next step is performed after each binary search, and for convenience of description, the results of each binary search are listed, and should not be construed as performing three binary searches in one step.

Step 1033: acquiring the quantity of data not greater than the intermediate value from each database fragment;

traversing each database fragment to acquire the quantity of data which is not greater than the intermediate value in each database fragment; and acquiring the sum of the corresponding quantity of the data in each database fragment.

According to the result of the first binary search in the above step, the intermediate value 9 of the first intermediate data set obtains the amount of data not greater than the intermediate value from each of the database shards, as shown in table 6:

TABLE 6

As can be seen from the above table, in the Slice0, only {5} is not greater than 9, so the number of data not greater than 9 is 1; in Slice1, {4,6,9} is not greater than 9, so the amount of data not greater than 9 is 3; in Slice2, there are {1,1,4,8} not more than 9, so the number of data not more than 9 is 4; in Slice3, there are {1,1,2,5,8} not more than 9, so the number of data not more than 9 is 5; therefore, the number i of data not greater than the intermediate value 9 is 13 in all database shards.

Go to step 1034.

According to the result of the second binary search in the above step, the intermediate value 5 of the first intermediate data set obtains the amount of data not greater than the intermediate value from each of the database shards, as shown in table 7:

TABLE 7

As can be seen from the above table, in the Slice0, only {5} is not greater than 5, so the number of data not greater than 5 is 1; in Slice1, there are {4} no more than 5, so the number of data no more than 5 is 1; in Slice2, there are {1,1,4} not more than 5, so the number of data not more than 5 is 3; in Slice3, there are {1,1,2,5} not more than 5, so the number of data not more than 5 is 4; therefore, the number i of data not greater than the intermediate value 5 is 9 in all database shards.

Go to step 1034.

According to the result of the third binary search in the above step, the intermediate value 8 of the first intermediate data set obtains the amount of data not greater than the intermediate value from each of the database shards, as shown in table 8:

TABLE 8

As can be seen from the above table, in the Slice0, {5} is not greater than 8, so the number of data not greater than 8 is 1; in Slice1, there are {4,6} not greater than 8, so the number of data not greater than 8 is 2; in Slice2, there are {1,1,4,8} not greater than 8, so the number of data not greater than 8 is 4; in Slice3, there are {1,1,2,5,8} no more than 8, so the number of data no more than 8 is 5; therefore, the number i of data not greater than the intermediate value of 8 is 12 in all database shards.

Go to step 1034.

It should be noted that, after determining the number of data not greater than the intermediate value each time, the next steps are performed, and for convenience of description, the number of data not greater than the intermediate value determined each time is listed, and should not be construed as performing three determination steps in one step.

Step 1034: determining the subscript value corresponding to the intermediate value in the database according to the number, judging whether the subscript value is in the range of the data corresponding to the first page code, if not, turning to step 1035, and if so, turning to step 1036;

and acquiring the subscript value corresponding to the intermediate value in the database according to the number i, wherein the subscript of the data in the database is started from 0, so that the subscript value corresponding to the number i is i-1.

If the subscript value is greater than or equal to the start subscript corresponding to the first page code, and the subscript value is less than or equal to the end subscript corresponding to the first page code, go to step 1036.

Specifically, the process of determining whether the subscript value corresponding to the intermediate value in the database is within the range of the data corresponding to the first page code is as follows:

as can be seen from the above steps, the range of the data corresponding to the first page is the starting index startIndex and the ending index endIndex of the first page corresponding to the first page in the database, as described in step 102:

startIndex＝pageSize*(pageIndex-1)；

endIndex＝pageSize*pageIndex-1；

since the first page code pageIndex is 3 and pageSize is 4, therefore:

startIndex＝4*(3-1)＝8；

endIndex＝4*3-1＝11；

therefore, the index of the first page corresponds to the data of 8 at the beginning of the page in the database, and the index of the page at the end is 11.

The quantity of the data which is determined according to the first halving search and is not larger than the intermediate value is as follows:

as shown in step 1032, after the first binary search, the determined number i of data not greater than the intermediate value 9 is 13, that is, the index value corresponding to the data is 12, which exceeds the ending index 11 of the paging, so that the index value corresponding to the intermediate value 9 in the database is not within the range of the data corresponding to the first page, and step 1035 is performed, and the intermediate value 9 needs to be discarded.

The quantity of the data which is determined according to the second halving search and is not larger than the intermediate value is as follows:

as shown in step 1032, after the second binary search, the number i of the determined data that is not greater than the intermediate value 5 is 9, that is, the index value corresponding to the data is 8, and falls within the range of the data corresponding to the first page code, so that the index value corresponding to the intermediate value 5 in the database falls within the range of the data corresponding to the first page code, and step 1036 is performed, and the intermediate value 5 needs to be placed in a second intermediate data set.

The quantity of data which is determined according to the third halving search and is not larger than the intermediate value is as follows:

as shown in step 32, after the third binary search, the number i of the determined data that is not greater than the intermediate value 8 is 12, that is, the index value corresponding to the data is 11, and falls within the range of the data corresponding to the first page code, so that the index value corresponding to the intermediate value 8 in the database falls within the range of the data corresponding to the first page code, and step 36 is performed, where the intermediate value 8 needs to be placed in the second intermediate data set, and step 1036 is performed.

It should be noted that, after the number of data not greater than the intermediate value is determined, the following steps are performed, and for convenience of description, the result of each determination is listed, and should not be understood as performing three determination processes in one step.

Step 1035: the intermediate value is discarded and step 1037 is performed.

Step 1036: and putting the intermediate value into a second intermediate data set.

As can be seen from the above steps, for the intermediate value 9 of the first binary search, the number of data not greater than the intermediate value 9 obtained in all the database fragments is 13, and the subscript value corresponding to the intermediate value 9 is not within the range of the data corresponding to the first page code, so 9 is discarded;

for the intermediate value 5 of the second halving search, acquiring the number of data not greater than the intermediate value 5 in all the database fragments as 9, wherein the subscript value corresponding to the intermediate value 5 falls within the range of the data corresponding to the first page code, and therefore 5 is put into a second intermediate number set;

for the intermediate value 8 of the third binary search, the number of data not greater than the intermediate value 8 obtained from all the database shards is 12, and the subscript value corresponding to the intermediate value 8 falls within the range of the data corresponding to the first page code, so that 8 is put into a second intermediate number set;

when the binary search is completed, the finally formed second intermediate data set is {5,8}

Step 1037: judging whether the halving searching of all the data is finished or not, if not, turning to step 1032; otherwise go to step 1038;

and judging whether the halving searching of all the data in the first intermediate data set is finished or not, and if not, turning to the step 1032 to continue the operation.

Step 1038: taking the minimum value in the second intermediate data set as the minimum value of data in the first page code, and taking the maximum value in the second intermediate data set as the maximum value of data in the first page code;

the data in the second intermediate data set is sorted, as shown in step 1036, the second intermediate data set is {5,8}, and in the second intermediate data set, the minimum value is 5 and the maximum value is 8, so that 5 is the minimum value of the data in the first page and 8 is the maximum value of the data in the first page.

Therefore, in the embodiment of the application, the maximum value and the minimum value of the data in the first page are quickly found out in the first intermediate data set through binary search, and the efficiency of data matching is improved.

Step 104: acquiring data with values within a preset range from each fragment of the database, wherein the preset range is greater than or equal to the minimum value and less than or equal to the maximum value;

and acquiring data which is greater than or equal to the minimum value and less than or equal to the maximum value from each fragment of the database according to the minimum value and the maximum value.

According to the maximum value and the minimum value of the data in the first page number determined in step 103, querying all the database shards, and determining all the data that is greater than or equal to the minimum value and less than or equal to the maximum value, as shown in table 9 below:

TABLE 9

As can be seen from the above, in each piece of sliced data, the data set located in the preset range is {5,6,8,8 }.

Step 105: determining the data in the preset range as query data corresponding to the data query request;

thus, the data set {5,6,8,8} is determined to be the final query result determined from the first page code. The query result is consistent with the prior art query result described above.

Therefore, in summary, in the embodiment of the present application, a first intermediate data set is obtained, the maximum value and the minimum value of data in a first page are determined according to the sorting of the data in all data in the first intermediate data set and the range of the data in the first page, and a query result is determined according to the maximum value and the minimum value, so that a query result can be accurately determined.

Further, in the present embodiment, some scenarios in the actual data query process are optimized, and in step 1034, if it is determined that the subscript value corresponding to the intermediate value does not fall within the range of the data of the first page, the present embodiment further provides a further determination by the following method, which specifically includes:

if the subscript value corresponding to the intermediate value is greater than the end subscript endIndex corresponding to the first page code, further determining whether a plurality of same maximum values exist in the data which are not greater than the intermediate value in each data fragment; if so, subtracting the subscript value by the number of remaining identical maximum values; and if the reduced subscript value is greater than or equal to the starting subscript startIndex corresponding to the first page code and is less than or equal to the ending subscript endIndex corresponding to the first page code, placing the intermediate value into a second intermediate data set.

In the embodiment of the present application, table 10 is described as an example, and for convenience of description, table 10 is different from the data in table 3 in that the value 9 of Slice1 is changed to 8, so as to facilitate description of the above situation. Score value after ordering of each slice:

watch 10

Slicing	Score after ordering of the slices
		Slice0



		5,16,17,23,23,43,46,48,49,49,58,61,63,67,68,70,72,81,85,86,93,96,96,98,99
Slice1			4,6,8,22,28,31,36,43,45,46,49,51,54,54,65,71,79,84,84,86,86,86,89,94,98
	Slice2	1,1,4,8,14,16,17,18,32,34,39,40,41,42,48,49,56,64,70,73,89,90,90,91,96
Slice3			1,1,2,5,8,13,20,28,43,54,56,56,59,61,63,69,69,72,76,77,85,86,88,96,97

From the above embodiment, it can be seen that:

the first page code pageIndex is 3 and pageSize is 4, so:

startIndex＝4*(3-1)＝8；

endIndex＝4*3-1＝11；

startIndexInSlice＝2；

endIndexInSlice＝3；

the calculation method of the above values is the same as the above embodiment, and is not described herein again.

The first determined intermediate data set meta-set is {17,23,8,22,4,8,2,5}, and the first sorted intermediate data set meta-set is {2,4,5,8,8,17,22,23 }.

The mean value of meat-set determined by the first binary search is meta-set [ len (meta-set)/2] ═ meta-set [4] ═ 8.

According to the result of the first binary search in the above step, the intermediate value 8 of the first intermediate data set obtains the amount of data not greater than the intermediate value from each of the database shards, as shown in table 11:

TABLE 11

As can be seen from the above table, in the Slice0, only {5} is not greater than 8, so the number of data not greater than 8 is 1; in Slice1, there are {4,6,8} no more than 8, so the number of data no more than 8 is 3; in Slice2, there are {1,1,4,8} not greater than 8, so the number of data not greater than 8 is 4; in Slice3, there are {1,1,2,5,8} no more than 8, so the number of data no more than 8 is 5; therefore, in all database shards, the number i of data not greater than the intermediate value 8 is 13, and its corresponding index value is 12.

Since startIndex is 8, endIndex is 11, and index value 12 corresponding to number 13 corresponding to intermediate value 8 is greater than endIndex, a set of data not greater than intermediate value 8 in each of the slices is further obtained, where the set is {5,4,6,8,1,1,4,8,1,1,2,5,8 }. The maximum value in the set is 8, and there are 3 maximum values 8, at this time, it is necessary to subtract the remaining number of the same maximum values from the subscript value 12, in this example, the number of the remaining same maximum values is 2, so that after subtracting 2 from the subscript value 12, the value is 10, and its corresponding subscript value is 10, so that the subscript value corresponding to the subtracted number i is greater than or equal to the starting subscript startIndex corresponding to the first page code, and at the same time, is less than or equal to the ending subscript endIndex corresponding to the first page code, so that the intermediate value 8 needs to be put into the second intermediate data set.

The following data query process is the same as the steps in the above embodiments, and is not described herein again.

Therefore, in the above embodiment of the present application, if the subscript value corresponding to the intermediate value is greater than the end subscript endIndex corresponding to the first page, it is further determined whether there are multiple same maximum values in the data that is not greater than the intermediate value in each data slice; if the subscript value corresponding to the intermediate value exists, subtracting the number of the remaining same maximum values from the subscript value corresponding to the intermediate value; and if the reduced subscript value is greater than or equal to the starting subscript startIndex corresponding to the first page and is less than or equal to the ending subscript endIndex corresponding to the first page, placing the intermediate value into a second intermediate data set, thereby further improving the accuracy of data query.

Further, an embodiment of the present application further provides a data query apparatus 200 based on a database shard, as shown in fig. 6, including: a data query request acquisition module 201, a first intermediate data set acquisition module 202, a data range determination module 203, and a data query result determination module 204.

The data query request obtaining module 201 is configured to obtain a data query request, where the data query request is used to query data in a first page, and the first page is a page in which all data in a database are sorted.

The first intermediate data set obtaining module 202 is configured to obtain a first intermediate data set according to the size of the fragments, the size of the pages, and the total amount of data of the database; the first set of intermediate data includes data in the first page number.

The data range determining module 203 is configured to determine a maximum value and a minimum value of data in the first page number from the first intermediate data set according to the sorting of the data in the first intermediate data set among all data and the range of the data in the first page number.

The data query result determining module 204 is configured to obtain data whose value is within a preset range from each segment of the database, where the preset range is greater than or equal to the minimum value and less than or equal to the maximum value; and determining the data in the preset range as query data corresponding to the data query request.

Optionally, the first intermediate data set obtaining module 202 is further configured to obtain a start subscript and an end subscript of data in the first page number in each database fragment according to the fragment size, the page size, and the total data amount of the database; according to the starting subscript and the ending subscript, respectively acquiring corresponding data values from the sorted database fragments; each of the data values constitutes a first intermediate data set

Optionally, the first intermediate data set obtaining module 202 is further configured to determine a start subscript and an end subscript corresponding to a first page in the database according to the first page and a page size;

The data range determination module 203 is further configured to sort the first intermediate data set; performing a binary search in the sorted first intermediate data set to obtain an intermediate value; acquiring the quantity of data not greater than the intermediate value from each database fragment; determining the subscript value corresponding to the intermediate value in the database according to the number; if the subscript value is within the range of the data of the first page number, the intermediate value is put into a second intermediate data set until the halving search of the sorted first intermediate data set is completed; and taking the minimum value in the second intermediate data set as the minimum value of the data in the first page number, and taking the maximum value in the second intermediate data set as the maximum value of the data in the first page number.

The data range determining module 203 is further configured to traverse each database fragment to obtain the amount of data that is not greater than the intermediate value in each database fragment; and acquiring the sum of the corresponding quantity of each database fragment.

The data range determining module 203 is further configured to place the intermediate value into a second intermediate data set when the subscript value is greater than or equal to the start subscript corresponding to the first page code and the subscript value is less than or equal to the end subscript corresponding to the first page code.

The data range determining module 203 is further configured to determine whether multiple identical maximum values exist in the numerical values, which are not greater than the intermediate value, in each data fragment when the subscript value is greater than the termination subscript corresponding to the first page; if so, subtracting the subscript value by the number of remaining identical maximum values; and if the subscript value after the value reduction is greater than or equal to the starting subscript corresponding to the first page code and less than or equal to the ending subscript corresponding to the first page code, putting the intermediate value into a second intermediate data set.

Further, an embodiment of the present application further provides a server, as shown in fig. 7. The server may include: a processor (processor)702, a Communications Interface 704, a memory 706, and a communication bus 708.

Wherein: the processor 702, communication interface 704, and memory 706 communicate with each other via a communication bus 708. A communication interface 704 for communicating with network elements of other devices, such as clients or other servers. The processor 702 is configured to execute the program 710, and may specifically execute relevant steps in the above-described database partition-based data query method embodiment.

In particular, the program 710 may include program code comprising computer-executable instructions.

The processor 702 may be a central processing unit CPU, or an Application Specific Integrated Circuit (ASIC), or one or more Integrated circuits configured to implement an embodiment of the present invention. The visualization network terminal comprises one or more processors, which can be the same type of processor, such as one or more CPUs; or may be different types of processors such as one or more CPUs and one or more ASICs.

The memory 706 stores a program 710. The memory 706 may comprise high-speed RAM memory, and may also include non-volatile memory (non-volatile memory), such as at least one disk memory.

The program 710 may be specifically invoked by the processor 702 to cause the server to perform the following operations:

Further, the obtaining a first intermediate data set according to the fragmentation size, the paging size, and the total data amount of the database includes:

each of the data values constitutes a first intermediate data set.

Further, the obtaining a start subscript and a stop subscript of data in the first page number in each database fragment according to the fragment size, the page size, and the total data amount of the database includes:

Further, the determining the maximum value and the minimum value of the data in the first page number from the first intermediate data set according to the sorting of the data in the first intermediate data set among all the data and the range of the data in the first page number comprises:

sorting the first set of intermediate data;

Further, the obtaining the quantity of the data not greater than the intermediate value from each database shard includes:

and acquiring the sum of the corresponding quantity of each database fragment.

Further, if the subscript value is within the range of data in the first page number, placing the intermediate value into a second intermediate data set, including:

Further, if the subscript value is within the range of data in the first page number, placing the intermediate value into a second intermediate data set, further comprising:

if so, subtracting the subscript value by the number of remaining identical maximum values;

An embodiment of the present invention provides a computer-readable storage medium, where the storage medium stores at least one executable instruction, and when the executable instruction is executed on a server, the server executes a data query method based on a database fragment in any method embodiment described above.

The algorithms or displays presented herein are not inherently related to any particular computer, virtual system, or other apparatus. Various general purpose systems may also be used with the teachings herein. The required structure for constructing such a system will be apparent from the description above. In addition, embodiments of the present invention are not directed to any particular programming language. It is appreciated that a variety of programming languages may be used to implement the teachings of the present invention as described herein, and any descriptions of specific languages are provided above to disclose the best mode of the invention.

In the description provided herein, numerous specific details are set forth. It is understood, however, that embodiments of the invention may be practiced without these specific details. In some instances, well-known methods, structures and techniques have not been shown in detail in order not to obscure an understanding of this description.

Similarly, it should be appreciated that in the foregoing description of exemplary embodiments of the invention, various features of the embodiments of the invention are sometimes grouped together in a single embodiment, figure, or description thereof for the purpose of streamlining the invention and aiding in the understanding of one or more of the various inventive aspects. However, the disclosed method should not be interpreted as reflecting an intention that: that the invention as claimed requires more features than are expressly recited in each claim.

Those skilled in the art will appreciate that the modules in the device in an embodiment may be adaptively changed and disposed in one or more devices different from the embodiment. The modules or units or components of the embodiments may be combined into one module or unit or component, and may be divided into a plurality of sub-modules or sub-units or sub-components. All of the features disclosed in this specification (including any accompanying claims, abstract and drawings), and all of the processes or elements of any method or apparatus so disclosed, may be combined in any combination, except combinations where at least some of such features and/or processes or elements are mutually exclusive. Each feature disclosed in this specification (including any accompanying claims, abstract and drawings) may be replaced by alternative features serving the same, equivalent or similar purpose, unless expressly stated otherwise.

It should be noted that the above-mentioned embodiments illustrate rather than limit the invention, and that those skilled in the art will be able to design alternative embodiments without departing from the scope of the appended claims. In the claims, any reference signs placed between parentheses shall not be construed as limiting the claim. The word "comprising" does not exclude the presence of elements or steps not listed in a claim. The word "a" or "an" preceding an element does not exclude the presence of a plurality of such elements. The invention may be implemented by means of hardware comprising several distinct elements, and by means of a suitably programmed computer. In the unit claims enumerating several means, several of these means may be embodied by one and the same item of hardware. The usage of the words first, second and third, etcetera do not indicate any ordering. These words may be interpreted as names. The steps in the above embodiments should not be construed as limiting the order of execution unless specified otherwise.

Claims

1. A data query method based on database shards is characterized by comprising the following steps:

2. The method of claim 1, wherein obtaining the first intermediate data set based on a shard size, a pagination size, and a total amount of data of the database comprises:

each of the data values constitutes a first intermediate data set.

3. The method of claim 2, wherein the obtaining the start index and the end index of the data in the first page number in each database fragment according to the fragment size, the page size, and the total amount of data of the database comprises:

4. The method of claim 3, wherein determining the maximum and minimum values of data in the first page number from the first intermediate data set according to an ordering of data in the first intermediate data set among all data and a range of data in the first page number comprises:

sorting the first set of intermediate data;

5. The method of claim 4, wherein said obtaining from each of said database shards an amount of data that is not greater than said intermediate value comprises:

and acquiring the sum of the corresponding quantity of each database fragment.

6. The method of claim 5, wherein placing the intermediate value into a second set of intermediate data if the subscript value is within the range of data in the first page number comprises:

7. The method of claim 6, wherein the placing the intermediate value into a second set of intermediate data if the subscript value is within the range of data in the first page number, further comprising:

8. A data query device based on database shards is characterized by comprising:

a data query request acquisition module: the data query method comprises the steps of obtaining a data query request, wherein the data query request is used for querying data in a first page number, and the first page number is a page number obtained by sequencing all data in a database;

a first intermediate data set acquisition module: the data processing device is used for acquiring a first intermediate data set according to the fragment size, the page size and the total data amount of the database; the first intermediate data set comprises data in the first page number;

a data range determination module: determining a maximum value and a minimum value of data in the first page number from the first intermediate data set according to the ordering of the data in the first intermediate data set among all data and the range of the data in the first page number;

a data query result determination module: the data acquisition module is used for acquiring data with values within a preset range from each fragment of the database, wherein the preset range is greater than or equal to the minimum value and less than or equal to the maximum value; and determining the data in the preset range as query data corresponding to the data query request.

9. A server, comprising: the system comprises a processor, a memory, a communication interface and a communication bus, wherein the processor, the memory and the communication interface complete mutual communication through the communication bus;

the memory is used for storing at least one executable instruction, which causes the processor to execute the database shard based data query method according to any one of claims 1 to 7.

10. A computer-readable storage medium, wherein at least one executable instruction is stored in the storage medium, and when executed on a server, causes the server to execute the database shard-based data query method according to any one of claims 1 to 7.