CN111159228A

CN111159228A - Data query method and device

Info

Publication number: CN111159228A
Application number: CN201911369823.0A
Authority: CN
Inventors: 范宏伟
Original assignee: NSFOCUS Information Technology Co Ltd; Nsfocus Technologies Inc
Current assignee: NSFOCUS Information Technology Co Ltd; Nsfocus Technologies Inc
Priority date: 2019-12-26
Filing date: 2019-12-26
Publication date: 2020-05-15

Abstract

The invention discloses a data query method and a device, which are used for solving the problems of large operation amount and long query time consumption caused by using an inappropriate SQL query mode, the embodiment of the application presets a query optimization scheme set corresponding to a query condition set consisting of different query conditions, when query contents input by a user are obtained, the query optimization scheme set can be matched according to the query conditions contained in the query contents, the query optimization scheme corresponding to the query condition set matched with the query contents input by the user is determined according to the corresponding relation between the preset query condition set and the query optimization scheme, query statements are generated according to the determined query optimization scheme, the query statements are executed to obtain query results from data to be queried, the application can flexibly adapt to the optimal query optimization scheme aiming at different query contents, and the query optimization mode of the application is more flexible, the applicability is strong.

Description

Data query method and device

Technical Field

The invention relates to the technical field of computers, in particular to a data query method and a data query device.

Background

SQL (Structured Query Language) is a database Query and programming Language for accessing data and querying, updating, and managing relational database systems.

In a relational database system, a two-dimensional table composed of rows and columns is used as the most basic container for data storage, and there often exists an association between multiple two-dimensional tables, so each two-dimensional table is also called a "relationship".

The vast majority of operations on data in relational databases are implemented by writing in SQL. The SQL language has a considerable degree of flexibility, and for a given query content (or query requirement), there may be a plurality of SQL writing methods that can obtain the target result, but different writing methods often have a great difference in performance time consumption.

In summary, the conventional method of using an inappropriate SQL query method may result in a large amount of operations and a long query time.

Disclosure of Invention

The invention provides a data query method and a data query device, which are used for solving the problems that the traditional method for using an improper SQL query method can cause large operation amount and long query time.

In a first aspect, a data query method provided in an embodiment of the present invention includes:

determining a query condition contained in query content input by a user;

determining a query optimization scheme set corresponding to a query condition set matched with a query condition contained in the query content according to a corresponding relation between a preset query condition set and the query optimization scheme set, wherein the query condition set comprises at least one query condition, and the query optimization scheme set comprises at least one query optimization scheme;

generating a query statement according to the determined query optimization scheme set;

and executing the generated query statement to acquire a query result corresponding to the query content from the data to be queried.

In the method, the query optimization scheme set corresponding to the query condition set consisting of different query conditions is preset, and when the query content input by the user is acquired, the query condition set can be matched according to the query conditions contained in the query content, the query optimization scheme corresponding to the query condition set matched with the query content input by the user is determined according to the corresponding relation between the preset query condition set and the query optimization scheme, and generating a query statement according to the determined query optimization scheme, and executing the query statement to acquire a query result from the data to be queried.

In an optional embodiment, the generating a query statement according to the determined query optimization scheme set includes:

if the query optimization scheme set comprises at least two query optimization schemes, determining the execution sequence of each query optimization scheme when the query content is queried according to the priority of the query optimization schemes;

and generating the query statements according to the execution sequence of the query optimization scheme.

In an alternative embodiment, the query condition set matching the query condition contained in the query content is determined by:

if the historical query data does not contain any query condition contained in the query content, or the historical query data contains a part of query conditions contained in the query content and the contained part of query conditions and the non-contained part of query conditions do not intersect with each other, taking a query condition set only containing target query conditions as a query condition set matched with the query conditions contained in the query content;

the target query condition is a query condition which is contained in the query content and is not contained in the historical query data.

In an optional embodiment, the method further comprises:

if the historical query data contains partial query conditions contained in the query content and no intersection exists between the contained partial query conditions and the non-contained partial query conditions, acquiring query results corresponding to the partial query conditions contained in the historical query data from the historical query data aiming at the partial query conditions contained in the historical query data;

after the query result corresponding to the query content is obtained from the data to be queried, the method comprises the following steps:

and determining a final query result according to the query result obtained from the data to be queried and the query result obtained from the historical query data.

In an optional embodiment, the method further comprises:

and if the historical query conditions comprise all query conditions contained in the query content, acquiring a query result corresponding to the query content in the historical query data.

In an optional embodiment, the method further comprises:

if the historical query data contains partial query conditions contained in the query content and intersection exists between the contained partial query conditions and the uncontained partial query conditions, obtaining query results corresponding to the partial query conditions contained in the query content of the historical query data packet;

generating a corresponding multiplexing query statement aiming at the partial query condition which is not contained;

and obtaining the query result corresponding to the part of the query conditions which are not contained from the obtained query result by executing the multiplexing query statement as a final query result.

In an optional implementation manner, after the query statement generated by the executing obtains a query result corresponding to the query content from the data to be queried, the executing further includes:

and adding the target query condition and the corresponding query result to the historical query data.

In an optional implementation manner, the query content includes a query condition within a range of data to be queried.

In an alternative embodiment, the query condition includes some or all of the following:

column information of the output list, list filtering, or grouping the list.

In an alternative embodiment, the query optimization scheme includes some or all of the following:

in optimization, exists optimization or non-null-unique optimization.

In a second aspect, an embodiment of the present invention further provides an apparatus for querying data, where the apparatus includes: a processor and a memory, wherein the memory stores program code that, when executed by the processor, causes the terminal to perform the following:

determining a query condition contained in query content input by a user;

In one possible implementation, the processor is specifically configured to:

In one possible implementation manner, the processor determines the query condition set matched with the query condition contained in the query content by the following means:

In one possible implementation, the processor is further configured to:

and after the query result corresponding to the query content is obtained from the data to be queried, determining a final query result according to the query result obtained from the data to be queried and the query result obtained from the historical query data.

In one possible implementation, the processor is further configured to:

In a possible implementation manner, after the query statement generated by the executing obtains a query result corresponding to the query content from the data to be queried, the processor is further configured to:

In a possible implementation manner, the query content includes a query condition within a range of data to be queried.

In one possible implementation, the query condition includes some or all of the following:

column information of the output list, list filtering, or grouping the list.

In one possible implementation, the query optimization scheme includes some or all of the following:

in optimization, exists optimization or non-null-unique optimization.

In a third aspect, an embodiment of the present invention further provides a device for querying data, where the device includes:

a first determination module: the query condition is used for determining that the query content input by the user contains the query condition;

a second determination module: the query optimization method comprises the steps of determining a query optimization scheme set corresponding to a query condition set matched with query conditions contained in query contents according to a corresponding relation between a preset query condition set and the query optimization scheme set, wherein the query condition set comprises at least one query condition, and the query optimization scheme set comprises at least one query optimization scheme;

a processing module: generating a query statement according to the determined query optimization scheme set;

an execution module: and the query statement is used for executing the generated query statement to acquire a query result corresponding to the query content from the data to be queried.

In a fourth aspect, the present application also provides a computer storage medium having a computer program stored thereon, which when executed by a processor, performs the steps of the method 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 technical solutions in the embodiments of the present invention, the drawings needed to be used in the description of the embodiments will be briefly introduced below, and it is obvious that the drawings in the following description are only some embodiments of the present invention, and it is obvious for those skilled in the art to obtain other drawings based on these drawings without inventive exercise.

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

FIG. 2 is a schematic flow chart of a first method of the combination method according to the embodiment of the present invention;

FIG. 3 is a flowchart of a second method of combining modes according to an embodiment of the present invention;

FIG. 4 is a schematic structural diagram of a first data query apparatus according to an embodiment of the present invention;

fig. 5 is a schematic structural diagram of a second data query apparatus according to an embodiment of the present invention.

Detailed Description

In order to make the objects, technical solutions and advantages of the present invention clearer, the present invention will be described in further detail with reference to the accompanying drawings, and it is apparent that the described embodiments are only a part of the embodiments of the present invention, not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.

Some of the words that appear in the text are explained below:

1. the term "and/or" in the embodiments of the present invention describes an association relationship of associated objects, and indicates that three relationships may exist, for example, a and/or B may indicate: a exists alone, A and B exist simultaneously, and B exists alone. The character "/" generally indicates that the former and latter associated objects are in an "or" relationship.

2. In the embodiments of the present application, the term "plurality" means two or more, and other terms are similar thereto.

The application scenario described in the embodiment of the present invention is for more clearly illustrating the technical solution of the embodiment of the present invention, and does not form a limitation on the technical solution provided in the embodiment of the present invention, and it can be known by a person skilled in the art that with the occurrence of a new application scenario, the technical solution provided in the embodiment of the present invention is also applicable to similar technical problems. In the description of the present invention, the term "plurality" means two or more unless otherwise specified.

The SQL system database is a data system dedicated to storing a large amount of data, and for the same query content, the query result can be obtained through different SQL query modes, but different SQL query modes consume different "computation amounts" in the query process. Currently, the query type SQL can be divided into the following three types according to the involved operators (query modes), see table 1:

TABLE 1

Therefore, after a developer builds a query type of SQL according to the query content, the SQL can be optimized to reduce the query time. For example, in the association query, the query performance can be optimized by first filtering and then associating, and it can be understood that if the query is first associated and then filtered, the association performed on the data to be filtered is equivalent to an invalid operation, which increases the query time, and thus, the optimization of the query SQL is essentially a time-consuming optimization for the core operator.

At present, there are many common ways to optimize query performance, and the following list is several:

1) the optimization method comprises the following steps: optimizing an SQL query mode;

as shown in table 1 above, the simple query and the associated query themselves are very simple query ways, and are used to query some basic query contents, for example, a certain column in the Excel table is screened, and the numerical values of the same row in the certain two columns in the Excel table are summed, so that the optimization space for the simple query and the associated query itself is limited. The association query and the association aggregation query are relatively complex query modes, and the optimization modes of the two query modes are as follows:

in the correlation query, one achievable optimization mode is to optimize the query SQL by filtering first and then correlating; another achievable optimization mode is that in or exists is selected to optimize the query SQL according to the query content; the third achievable optimization mode is that the query SQL is optimized through a CTE expression;

in the association aggregation query, if a group column in a certain table is a non-empty unique column, the association aggregation is performed first, and then the association is performed again to obtain other columns, so that the query performance may be greatly improved;

as mentioned above, in (in the range of …), exists, CTE (Common expression) expressions, etc. are syntax elements that can be used to implement the query in the SQL language.

2) And the second optimization mode is as follows: establishing an index;

the data in the SQL database is stored in a list form, one list may have millions of rows of data, but most of the rows often have the same value, and if an index is created for a certain row, as if a dictionary is created, each specific value of the row corresponds to which rows in the millions of rows, and can be directly searched through the index, so that the performance of "filtering" is greatly improved, and meanwhile, the index on the associated row also has a large optimization effect on "association", and the index on the grouped row also has a large optimization effect on "grouping".

For example, a list as shown in table 2 below:

TABLE 2

Line number	Name (I)	Age (age)
			1	First of all	18
2	Second step	19
			3	C3	30
4	T-shirt	18
			5	Wu Ying (five-element)	18
6	Has already been used for	26

Table 3 below is a schematic diagram of a list obtained by indexing age:

TABLE 3

Age (age)	Index (line number)
		18	1、4、5
19	2
		26	3
30	4

As shown in table 3 above, when the query is a person aged 18 years in table 2, the lines 1, 4 and 5 in table 2 may be hidden directly. When the data volume is large and the repeated numerical values on the columns are large, the query performance can be effectively improved by indexing the lists.

3) The optimization mode is four: optimizing the configuration of the server;

at present, there are a lot of server configuration parameters that directly affect query performance, for example, postgresql is taken as an example, where one of the configuration parameters is work _ mem and is used to indicate the number of memories used by the internal sorting operation and the Hash (Hash) table before the temporary disk file starts to be used, for example, if the default value is 1, it indicates that the number of memories used by the internal sorting operation and the Hash (Hash) table before the temporary disk file starts to be used is 1 megabyte (1MB), and it should be understood that the larger the default value is, the better the internal sorting performance is. Therefore, when the query relates to the sequencing, the query itself can directly relate to the sequencing, or other operators contain the sequencing, the server configuration parameters influencing the query performance can be adjusted temporarily, so that the query performance is improved. Among these, server configuration parameters for performance impact include, but are not limited to: the default values, enable _ nestloop, enable _ mergejoin.

In the above, there are various ways to optimize query performance, such as creating partitions for some large data lists, using temporary lists, etc.

At present, when the query performance is optimized, only some SQL constructed by simpler query contents can be optimized, the optimization mode cannot be flexibly adapted according to the query contents, and sometimes, when the query contents are more complicated, the SQL construction is difficult for developers, and the optimization mode can only ensure the correctness of the query result and cannot achieve effective optimization.

In view of this, an embodiment of the present application provides a data query method, in which a query condition set composed of query conditions included in different query contents input by a user is matched with a preset query condition set, a query optimization scheme set corresponding to the matched query condition set is determined according to a preset query condition combination and a corresponding relationship between the query optimization scheme set, a query statement is generated according to the determined query optimization scheme set, and the query statement is executed to obtain a query result from data to be queried. According to the embodiment of the application, the corresponding query optimization scheme can be adapted according to a large number of different query condition sets, and the query performance is optimized to the greatest extent.

The embodiments of the present invention will be described in further detail with reference to the drawings attached hereto.

As shown in fig. 1, an embodiment of the present invention provides a method for querying data, which specifically includes the following steps:

step S100: determining a query condition contained in query content input by a user;

step S101: determining a query optimization scheme set corresponding to a query condition set matched with a query condition contained in the query content according to a corresponding relation between a preset query condition set and the query optimization scheme set, wherein the query condition set comprises at least one query condition, and the query optimization scheme set comprises at least one query optimization scheme;

step S102: generating a query statement according to the determined query optimization scheme set;

step S103: and executing the generated query statement to acquire a query result corresponding to the query content from the data to be queried.

By the scheme, the query optimization scheme which can be flexibly adapted to the best query content can be achieved, unified optimization of query statements constructed by different query contents by using a single optimization tool and a single optimization mode is avoided, the query optimization scheme is flexible in query optimization mode, and the application is strong.

When the query content input by the user is obtained, the query content is firstly analyzed, and the query condition contained in the query content is obtained. For example, the data to be queried is table 4, and the table 4 is a student information statistics table, which contains information such as the grade, class, name, age, and area of the student.

	Grade of year	Class of class	Name (I)	Sex	Age (age)	Height (cm)	Body weight (kilogram)	Region of land
									1	1	2	Liuyang	Woman	17	165	44	Hebei river
2	1	3	Lilei	For male	18	175	60	Shandong (mountain east)
									3	1	1	Zhao Fan	Woman	20	172	55	Beijing
4	1	4	Schchekia chekiana	For male	24	178	75	Mansion door
									5	1	6	Haoyu	For male	22	180	78	Large connecting piece
……	……	……	……	……	……	……	……	……

Assuming that the query content input by the user is a person whose area is beijing and whose age is 20 years in table 4 is screened out, the query content is analyzed to obtain that the query condition included in the query content is two filtering sub-conditions, the filtering sub-conditions have a positive relationship, the filtering sub-condition 1 includes a specific object whose area is beijing, and the filtering sub-condition 2 includes a specific object whose age is equal to 20.

The above is merely an example, and the following describes query conditions in the embodiment of the present application, where the query conditions include, but are not limited to, the following conditions:

1, outputting column information;

the query condition of the output column information is specifically classified into the following three types:

(1) direct output column: directly outputting a column of a column, e.g., tb4 (Table 4). col2 (second column), i.e., outputting the second column of Table 4

(2) The aggregate operation is as follows: the result of the aggregation operation for a column in a table, for example, count (tb2.col2), i.e., the sum of the second column in table 2, is output.

(3) And others: like operations between columns of one list or different lists, for example: tb4.col2+ tb1.col2, i.e. adding the values of the same row in the second column of table 4 and the second column of table 4, or tb4.col2+ tb2.col2, i.e. adding the values of the same row in the second column of table 4 and the second column of table 2.

2, filtering conditions;

the filtering condition may be understood as a filtering list, and specifically, the type of the filtering condition of the SQL standard includes, but is not limited to, the following conditions:

in, not In, like, not like, > < operation, is null or is not null.

The types of filtration conditions described above are presented below in table 4:

TABLE 4

For the filtering conditions, the tables are independent of each other, each table may have multiple filtering conditions, and the filtering conditions may be different types, and may be a combination of conditions of one or more filtering types to constitute different query condition combinations. For example, sub-condition 1and sub-condition 2, or sub-condition 1or sub-condition 2, may be more complex when there are more than one, such as:

select XXX

from table1 t1

where(t1.col1 in(1,2,3)or t1.col1>100)

and t1.col2 like‘ABC％

and t1.col3 is null

3, grouping according to columns;

in a statistical analysis scenario, there are often packets, and packet-based aggregation operations.

Illustratively, in connection with table 4, table 4 is grouped by class, and table 4 is grouped by gender, respectively.

For example, in combination with the above table 4 as an example, if the query content input by the user is a girl with a query class number of 1, a height of over 160cm, or a weight of no more than 60 kg. The query content includes query conditions including: grouping, filtering and outputting column information; wherein, dividing the table 4 into grouping conditions according to classes, and screening out girls with height not more than 160cm or girls with weight not more than 60 kg as filtering conditions based on grouping results, wherein the filtering conditions comprise two sub-conditions, namely sub-condition 1, girls with height not more than 160cm or weight not more than 60 kg, respectively.

The query conditions are only examples, and the query conditions in the embodiment of the present application are not limited thereto, and the query conditions where the query content input by any user can be analyzed are all suitable for the embodiment of the present application, and the query conditions are descriptions of several categories of the query conditions.

Embodiments of the present application combine one or more query terms into a set of query terms, which, in embodiments of the present application, determining various query condition sets according to the combination of the query conditions, determining query optimization scheme sets suitable for different query condition sets, the query optimization scheme set comprises one or more query optimization schemes, and the corresponding relation between each query condition set and the query optimization scheme set is preset, determining a query optimization scheme corresponding to the query condition set matched with the query content input by the user according to the query condition set matched with the query condition contained in the query content input by the user and the corresponding relation between the preset query condition set and the query optimization scheme set, and generating a query statement by using the determined query optimization scheme so as to flexibly adapt to the corresponding query optimization scheme according to different query contents.

The query optimization schemes in the embodiments of the present application are various, and are introduced below by way of specific descriptions:

1, in optimization;

when the query relates to a plurality of tables, the query condition is that another table is screened through a certain table, and the corresponding query optimization scheme can be in query or exists query.

Here, in () is suitable for a case where the B table is smaller than the a table data, and exists () is suitable for a case where the B table is larger than the a table data. The following is illustrated by way of example:

select*from A

where id in(select id from B)

in the query statement using in, in () is executed only once, meaning that all id fields in B table are looked up and cached. After that, it is checked whether the id of the A table is equal to the id in the B table. If the A table records are equal, the A table records are added into the result set until all the A table records are traversed.

It can be seen that in () is not suitable for use when the B table data is large because it will traverse the B table data all once.

Therefore, when the query condition is that tbA is screened by tbB, if the data volume of tbB is smaller than that of tbA, the query condition is adapted to the in query mode, and the in query mode can play a role in query optimization.

2, exists optimization;

similarly, the following illustrates the query pattern of exists:

select a.*from A a

where exists(select 1from B b where a.id＝b.id)

the above query uses the exists statement, the exists () is executed a number (length) of times, and it does not cache the exists () result set. Because the contents of the exists () result set are not important, it is important whether there is a record in the result set, if so, a true is returned, and if not, a false is returned.

Therefore, exists () is suitable for use when the B table is larger than the A table data because it has not as many traversal operations and only needs to perform one more query.

Therefore, when the query condition is that tbA is screened by tbB, if the data size of tbB is larger than that of tbA, the query method is adapted to exists, and the query method by exists can play a role in query optimization.

3, non-null unique optimization;

non-empty unique column: when a column exists in a table, both a non-null constraint (null) and a unique constraint (unique), then the column may be referred to as a non-null only column.

When grouping is carried out according to a non-empty unique column of a certain table, and information of other columns of the table is simultaneously output, on the premise that query results are consistent, SQL has two construction modes, wherein the following two lists are A and B, the A table is a student information table, the B table is a course information table, the A table and the B table are different information for the same student statistics, id, name, class and address in the A table respectively represent the student number, name, class and address, aid in the B table is the student number, id is the course number as an example, and the SQL construction modes when the list A and the list B are grouped according to the non-empty unique column are described:

first, a non-empty unique column is illustrated, where a non-empty unique column means that the column in the list has a value, and any value in the column is unique, i.e., not repeated. For example, the identity card is provided for each user, and the number of the identity card of each user is different. As another example, the id column in the A table is a non-empty unique column, i.e., if there is one student, his school number must not be unknown, nor is it possible that the school numbers of two students are the same.

Illustratively, based on the table a and the table B, if the query requirement is to group the serial numbers of the respective student cases, the class number selected by each student is output, and simultaneously, the name of the student corresponding to the student number and the class of the student are output. Table 5 below shows the expected query results based on tables A and B.

Student number (id)	Student name (name)	Class (class)	Selected course number (address)
				100	Zhang three	801	3
101	Li four	806	10
				102	Wangwu tea	802	6

There are many implementations of obtaining the expected query result based on the above example query scenario, two of which are listed below:

mode one, inefficient mode: an example of the SQL query statement constructed based on this method is as follows:

select a.id,a.name,a.class,a.address,count(b.id)as cn

from A a join B b on a.id＝b.aid

group by a.id,a.name,a.class,a.address

it should be noted that, the meaning of a.id is the id column of table a, the meaning of a.class is the class column of table a, the meaning of a.address is the address column of table a, and the meaning of b.aid is the aid column of table B, and so on, and repeated parts are not described again.

Mode two, the high-efficient mode: firstly, grouping is carried out according to the non-empty unique columns, aggregation operation is carried out, after an intermediate result set is obtained, other columns are obtained by re-associating the table through the non-empty unique columns, and an example of the SQL query statement constructed based on the method is as follows:

select t.id,a.name,a.class,a.address,t.cn

from

(select a.id,count(b.id)as cn

from A a join B b on a.id＝b.aid

group by a.id)t

left join A a on t.id＝a.id

the two construction modes have larger influence on the query performance, and the embodiment of the application can adapt the corresponding non-empty unique optimized construction mode according to the query content so as to optimize the query performance.

The above-mentioned examples of the query optimization schemes provided for the embodiments of the present application, any scheme capable of optimizing the query performance is applicable to the embodiments of the present application, and the query optimization scheme set in the embodiments of the present application includes, but is not limited to, a combination of one or more of the above-mentioned query optimization schemes. For example, the query condition set is condition 1: grouping, and condition 2: screening tbA with small data volume through tbB with large data volume, wherein the query optimization schemes contained in the query optimization scheme set corresponding to the query condition set are non-null unique optimization and exists optimization.

And generating a query statement according to the determined query optimization scheme set, and executing the generated query statement to acquire a query result corresponding to the query content from the data to be queried.

When generating the query statement, an optional manner is to determine a query statement template (e.g., an SQL statement template) corresponding to each query optimization scheme set, and fill a specific object included in the query content into the SQL statement template corresponding to the query optimization scheme set corresponding to the query content, so as to generate an SQL statement corresponding to the query content;

another optional implementation manner is to determine a query statement template corresponding to each query optimization scheme, and fill a specific object included in the query content into the query statement template corresponding to the query optimization scheme corresponding to the query content, so as to generate a query statement corresponding to any query optimization scheme corresponding to the query content.

And executing the generated query statements corresponding to the query optimization schemes according to the priority order of the query optimization schemes in the production mode. For example: and respectively generating query statements corresponding to the non-empty-unique optimization and query statements corresponding to the exists optimization according to specific query conditions input by the user. Determining the execution sequence of exists optimization and non-null unique optimization according to the priority of the exists optimization and the non-null unique optimization, and executing the query statement corresponding to the non-null unique optimization first and then executing the query statement corresponding to the exists optimization based on the query received by the query statement corresponding to the non-null unique optimization if the priority of the non-null unique optimization is higher than that of the exists optimization.

Preferably, when the query condition included in the query content input by the user is obtained through analysis, before executing the query sentence generation, the validity of the query condition may be determined, and if the query condition itself is an invalid condition, the error information or the alarm information is directly input to notify the user that the query condition input by the user is incorrect, and the user is asked to confirm or please re-input the content.

Specifically, the validity of the query condition may be determined by determining whether the query condition is within the range of the data to be queried.

Illustratively, in combination with the above table 4, if the query content input by the user is a blood type of a student in the query table 4 (for example, when the user performs screening by inputting the keyword "blood type"), the query content is analyzed, it is determined that the query condition contained in the query content is filtering, the object of the filtering condition is the blood type, specifically, when the validity of the query condition is judged, whether each column of information contained in the table 4 contains the column of information "blood type" is judged, if not, the query condition is determined to be invalid, and optionally, an error message (or a warning message) is output to prompt the user that the query content input by the user is incorrect.

As another example, and again taking Table 4 as an example, assuming that each student in Table 4 has an age range of 18 to 30, it is understood that the above age range is determined by the minimum and maximum values in the column "age" in Table 4. If the query content input by the user is a student with the age of 35 in the query table 4, the query condition included in the query content is a filter condition, and the object of the filter condition is that the age is equal to 35, it is determined that the query content input by the user includes an error through the age range of each student indicated in the table 4, so as to prompt that the query content input by the user has an error.

In an optional scenario, if the query content input by the user includes multiple query conditions, if 1 of the query conditions is not valid, the rest of the query conditions may not be executed, and error information (or warning information) is directly output.

When the query condition contained in the query content input by the user is determined to be effective, the corresponding query optimization scheme set can be determined in the manner of matching the query condition set, the query statement corresponding to the query content is generated according to the determined query optimization scheme set, and the query statement is executed to obtain the query result.

As another optimization mode, after the query result is obtained, the query condition and the corresponding query result included in the query content are saved in the historical query record. When the user inputs the query content again, whether the same query condition is queried is judged according to the historical query records, for example: the query content is Chinese characters with radicals arranged as wood character radicals, and corresponding query results (Chinese characters with radicals arranged as wood), such as 'root, model and forest'. When the user inquires again, the input inquiry content is Chinese characters with radicals arranged as wood radicals and strokes of 10, and the inquiry time can be effectively shortened by inquiring through the historical inquiry records aiming at the inquiry content.

The embodiment of the present application may perform query optimization by combining two ways, i.e., matching query condition sets and historical query records, as illustrated below:

the combination method I comprises the following steps: a serial manner;

the serial mode is that whether the historical query records contain the query conditions contained in the query contents is judged firstly, if yes, the query results are obtained based on the historical query records, and if not, the query results are obtained in a mode of matching the query condition set.

As shown in fig. 2, a schematic flow chart of a method for performing query in a serial manner includes the following steps:

step S201, obtaining the query content input by the user

Step S202, analyzing the query content input by the user to obtain the query condition contained in the query content

Step S203, judging whether any inquiry condition has validity, if so, executing step S204, otherwise, executing step 206;

step S204, judging whether the historical query record contains any query condition, if so, executing step 205, otherwise, executing step 207;

step S205, obtaining query result based on historical query data

Step S206, outputting the prompt message (error message or alarm message)

Step S207, determining a query optimization scheme set corresponding to a query condition set matched with the query conditions contained in the query content according to the corresponding relation between a preset query condition set and the query optimization scheme set;

step S208, generating a query statement according to the determined query optimization scheme set;

step S209, executing the generated query statement to obtain a query result corresponding to the query content from the data to be queried.

And a second combination mode: a parallel mode;

the parallel mode is that the process 1 is used for judging whether the historical query records contain query conditions or not, the process 2 is used for executing and obtaining query results in a mode of matching query condition sets, the process 1and the process 2 can be executed simultaneously, when the process 1 determines that the historical query records contain the query conditions, the process 2 is informed to finish processing, and when the process 1 determines that the historical query records do not contain the query conditions, the process 2 can be continuously executed to obtain the query results. In a possible scenario, a time delay caused by the step of judging whether the historical query record contains the query strip can be avoided in a parallel mode.

As shown in FIG. 3, the flow diagram of the method for performing query in parallel mode includes the following steps

Step S301, acquiring query content input by a user;

step S302, analyzing the query content input by the user to obtain the query condition contained in the query content

Step S303, judging whether any inquiry condition has validity, if yes, executing step S304 and step S307 at the same time; otherwise, go to step S310;

step S304, judging whether the historical inquiry record contains inquiry conditions, if so, executing step S305, otherwise, executing step S306;

step S305, obtaining query result based on historical query data

Step S306, the process of steps S304-S306 is ended

Step S307, according to the corresponding relation between the preset query condition set and the query optimization scheme set, determining the query optimization scheme set corresponding to the query condition set matched with the query conditions contained in the query content

Step S308, generating a query statement according to the determined query optimization scheme set

Step S309, executing the generated query statement to obtain a query result corresponding to the query content from the data to be queried.

It should be noted that, when it is determined in step 304 that the historical query record includes the query condition included in the query content input by the user, step 305 is executed and the process corresponding to steps S307 to S309 is notified to end.

In the embodiment of the application, the query is preferably performed in a combined manner, when the historical query records do not have the query conditions contained in the query content input by the user, the process can be directly ended, the query is continuously performed in a manner of matching the query condition set, the time delay caused by the step of waiting for judgment is avoided, and the query performance is improved.

When judging whether the historical query data contains query conditions contained in query contents input by a user, there are various possible situations, and when obtaining query results according to the historical data, the query modes corresponding to different situations are different, and the following specific explanations are made one by one:

in order to simplify the distinction, the query optimization manner corresponding to the query optimization scheme corresponding to the query condition set that determines the query condition included in the query content to match according to the preset corresponding relationship between the query condition set and the query optimization scheme set is referred to as a first query optimization manner, and the manner of obtaining the query result through the historical query data is referred to as a second query optimization manner.

In case 1, the historical query data does not contain any query condition contained in the query content;

for example, the query content input by the user is to search all the Chinese characters with the components arranged as soil and the strokes of 8, and the historical query data has no query record about the Chinese characters with the components arranged as soil and no query record about the Chinese characters with the strokes of 8. Thus, it is determined that the historical query data does not contain any query condition contained by the query content.

And executing the method flow corresponding to the first query optimization mode for any query condition which is not contained in the historical query data and is contained in the query content.

In case 2, the historical query data contains the partial query conditions contained in the query content, and there is no intersection between the contained partial query conditions and the non-contained partial query conditions;

for example, the query content input by the user is a Chinese character with a query initial consonant of b or a Chinese character with an initial consonant of c, and the historical query data includes a query result corresponding to the query condition with the initial consonant of b, that is, the historical query data includes a part of query conditions included in the query content. Further, if there is no intersection between the condition 1 that the initial consonant is a chinese character of b and the condition 2 that the initial consonant is a chinese character of c, that is, there is no intersection between the query condition (condition 1) included in the query content included in the historical query data and the query condition (condition 2) not included in the query content, the following operations are performed:

for the condition 1, for the partial query conditions contained in the query content contained in the historical query data, the query result corresponding to the partial query conditions contained in the historical query data is obtained from the historical query data. For example, the query result of Chinese character with initial consonant b is obtained from historical query data.

Regarding to the condition 2, taking a query condition set only containing target query conditions as a query condition set matched with the query conditions contained in the query content; the target query condition is a query condition which is contained in the query content and is not contained in the historical query data. For example, a set composed of query conditions of condition 2 (a Chinese character with a query initial consonant of c) is used as a matched query condition set, a query statement is generated for a query optimization scheme set corresponding to the matched query condition set, and a query result is obtained from data to be queried.

And determining a final query result according to the query result obtained from the data to be queried and the query result obtained from the historical query data. For example, a Chinese character with initial consonant c obtained from the data to be queried in the first query mode and a Chinese character with initial consonant b obtained from the historical query data are merged and output.

In case 3, the historical query data contains partial query conditions contained in the query content, and the contained partial query conditions and the non-contained partial query conditions have intersection;

for example, the query content input by the user is a query radical, which is deployed with Chinese characters with radicals of gold and initial consonants of b, and the historical query data only contains the Chinese characters with the query condition of b, that is, the historical query data contains partial query conditions of the query content. Further, the content to be queried is a chinese character whose radical is a radical of a gold character in a chinese character whose initial consonant is b, that is, there is an intersection between a partial query condition (the chinese character whose initial consonant is b) included in the historical query data and a partial query condition (the radical is disposed as a radical of a gold character) not included in the historical query data, for example: palladium, barium, and the like.

Then, for the situation, obtaining the query result (Chinese character with initial consonant b) corresponding to the partial query condition contained in the query content of the historical query data packet; generating a corresponding multiplexing query statement aiming at the partial query conditions (radicals are deployed as the radicals) which are not contained;

illustratively, the above flow is specifically that a chinese character with initial consonant b is obtained from the historical query data, and a multiplexed query statement is generated based on the query condition that the radical is deployed as a radical, and assuming that a data list composed of chinese characters with initial consonant b obtained from the historical query data is tb5(tb5 may include a line number and a chinese character with initial consonant b), the multiplexed query statement generated by a straight line obtains chinese characters with radical deployment as a radical from tb 5. The multiplex query statement may also be generated by presetting a query condition and a corresponding multiplex statement model, for example, for a simple query, as the above-mentioned screening condition, the multiplex query statement may be: select From.

And obtaining the query result corresponding to the part of query conditions which are not contained in the historical query data from the obtained query result by the execution multiplexing query statement as a final query result.

Case 4, all the query conditions contained in the query content are contained in the historical query conditions

For example, if the query content input by the user is a Chinese character with a consonant of b, and the historical query data includes a Chinese character with a query condition of b, that is, the historical query data includes a query condition included in the query content, the query result corresponding to the query content is obtained from the historical query data.

As shown in fig. 4, a file access control device based on a user mode according to an embodiment of the present invention includes: at least one processing unit 400 and at least one memory unit 401, wherein the memory unit 401 stores program code which, when executed by the processing unit 400, causes the processing unit 400 to perform the following processes:

determining a query condition contained in query content input by a user;

Optionally, the processing unit 400 is specifically configured to:

Optionally, the processing unit 400 is specifically configured to determine a query condition set matching the query condition included in the query content by:

Optionally, the processing unit 400 is further configured to:

Optionally, after the query statement generated by the executing obtains the query result corresponding to the query content from the data to be queried, the processing unit 400 is further configured to:

Optionally, the query content includes a query condition in the data range to be queried.

Optionally, the query condition includes some or all of the following:

column information of the output list, list filtering, or grouping the list.

Optionally, the query optimization scheme includes some or all of the following:

in optimization, exists optimization or non-null-unique optimization.

Based on the same inventive concept, the embodiment of the present invention further provides a data query apparatus, and as the principle of the apparatus for solving the problem is similar to the data query method in the embodiment of the present invention, the implementation of the apparatus may refer to the implementation of the method, and repeated details are not repeated.

As shown in fig. 5, an apparatus for querying data according to an embodiment of the present invention includes:

the first determination module 500: the query condition is used for determining that the query content input by the user contains the query condition;

the second determination module 501: the query optimization method comprises the steps of determining a query optimization scheme set corresponding to a query condition set matched with query conditions contained in query contents according to a corresponding relation between a preset query condition set and the query optimization scheme set, wherein the query condition set comprises at least one query condition, and the query optimization scheme set comprises at least one query optimization scheme;

the processing module 502: generating a query statement according to the determined query optimization scheme set;

an execution module 503: and the query statement is used for executing the generated query statement to acquire a query result corresponding to the query content from the data to be queried.

Optionally, the processing module 502 is specifically configured to:

In a possible implementation manner, the second determining module 501 is configured to determine the set of query conditions matching the query conditions included in the query content by:

In a possible implementation manner, the second determining module 501 is further configured to:

In a possible implementation manner, after the query statement generated by the executing obtains the query result corresponding to the query content from the data to be queried, the executing module 503 is further configured to:

column information of the output list, list filtering, or grouping the list.

in optimization, exists optimization or non-null-unique optimization.

Further, an embodiment of the present invention also provides a computer-readable non-volatile storage medium, which includes program code for causing a computing apparatus to execute the steps of the data query method according to the embodiment of the present invention when the program code runs on the computing apparatus.

The present application is described above with reference to block diagrams and/or flowchart illustrations of methods, apparatus (systems) and/or computer program products according to embodiments of the application. It will be understood that one block of the block diagrams and/or flowchart illustrations, and combinations of blocks in the block diagrams and/or flowchart illustrations, 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, and/or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer and/or other programmable data processing apparatus, create means for implementing the functions/acts specified in the block diagrams and/or flowchart block or blocks.

Accordingly, the subject application may also be embodied in hardware and/or in software (including firmware, resident software, micro-code, etc.). Furthermore, the present application may take the form of a computer program product on a computer-usable or computer-readable storage medium having computer-usable or computer-readable program code embodied in the medium for use by or in connection with an instruction execution system. In the context of this application, a computer-usable or computer-readable medium may be any medium that can contain, store, communicate, propagate, or transport the program for use by or in connection with the instruction execution system, apparatus, or device.

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

Claims

1. A method for querying data, the method comprising:

determining a query condition contained in query content input by a user;

2. The method of claim 1, wherein the generating a query statement from the determined set of query optimization schemes comprises:

3. The method of claim 1, wherein the set of query conditions that match the query conditions contained in the query content is determined by:

4. The method of claim 3, wherein the method further comprises:

according to the query result obtained from the data to be queried and the query result obtained from the historical query data

And determining a final query result.

5. The method of claim 3, wherein the method further comprises:

6. The method of claim 3, wherein the method further comprises:

7. The method of claim 4, wherein after the executing the generated query statement obtains the query result corresponding to the query content from the data to be queried, the method further comprises:

8. The method according to any one of claims 1 to 7, wherein the query content includes query conditions within a range of data to be queried.

9. A method according to any one of claims 1 to 7, wherein the query conditions include some or all of:

column information of the output list, list filtering, or grouping the list.

10. The method of any one of claims 1 to 7, wherein the query optimization scheme comprises some or all of:

in optimization, exists optimization or non-null-unique optimization.

11. An apparatus for querying data, the apparatus comprising: a processor and a memory, wherein the memory stores program code that, when executed by the processor, causes the terminal to perform the following:

determining a query condition contained in query content input by a user;

12. The apparatus of claim 11, wherein the processor is specifically configured to:

13. The apparatus of claim 11, wherein the processor determines the set of query conditions that match the query conditions contained in the query content by:

14. The apparatus of claim 13, wherein the processor is further configured to:

15. The apparatus of claim 13, wherein the processor is further configured to:

16. The apparatus of claim 13, wherein the processor is further configured to:

17. The apparatus of claim 14, wherein after the query statement generated by the executing obtains a query result corresponding to the query content from data to be queried, the processor is further configured to:

18. The apparatus according to any one of claims 11 to 17, wherein the query content includes query conditions within a range of data to be queried.

19. An apparatus as claimed in any one of claims 11 to 17, wherein the query conditions include some or all of:

column information of the output list, list filtering, or grouping the list.

20. The apparatus of any of claims 11 to 17, wherein the query optimization scheme comprises some or all of:

in optimization, exists optimization or non-null-unique optimization.

21. An apparatus for querying data, the apparatus comprising:

22. A computer storage medium having a computer program stored thereon, the program, when executed by a processor, implementing the steps of a method according to any one of claims 1 to 10.