A query method and system based on single index and multiple conditions

Abstract

The present invention discloses a query method and system based on single index and multiple conditions. The row key rowkey is designed according to the priority of the conditions to be queried, the values ​​required as condition columns are stored in the rowkey in an organized manner to produce multiple SQLs, and the multiple SQLs are merged to improve the query efficiency. The purpose of multi-condition dynamic query can be achieved by arranging SQL and dynamically splicing algorithms, and multi-condition query is realized by complicating SQL and simplifying indexes. The method is simple to implement and easy to implement. The present invention greatly improves the speed of retrieving information in the information system, and reduces the creation and use of indexes to a certain extent, and provides more diverse optimization solutions to reduce data redundancy and improve data writing speed, and has good performance in stability and execution efficiency.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a query method and system based on single index and multiple conditions. Background Art

[0002] Database technology is an essential component in almost all information systems. Database optimization is a very important part of the information system construction process. The efficiency of database use determines the overall use effect of the information system and user satisfaction. Database is also the shortest board in the barrel effect of many Internet applications. Therefore, the efficiency of the database almost determines the efficiency of the information system. Nowadays, there are many different ways to optimize the database.

[0003] At present, the most direct way to improve query efficiency for a certain condition is to add an index. This has almost become a conditional reflex operation when using relational databases such as MySQL and Oracle. However, in some non-relational databases, adding an index is not a "simple thing", because one more index often means one more data write overhead, and also one more data redundancy. The specific reason is still due to the design principle of databases like Hbase. Taking Hbase as an example, its internal storage structure is in the form of Key-value. There is only one global index, namely the primary key index, which is both the primary key and a unique index, and all values ​​are stored in the value.

[0004] In order to meet business needs, non-relational databases such as Hbase are often relationalized, such as using technologies such as Phoenix. However, no matter which relationalization technology is used, the final storage principle is the same, which means that when facing multi-condition queries, you can only choose to add indexes one by one, sacrificing writing and storage overhead, or perform a full scan operation. When the amount of data is small, a full scan operation can be used, but the data scale is not as desired in all scenarios. Summary of the invention

[0005] To this end, the present invention provides a single-index multi-condition query method and system to solve the problem of completing multi-condition query tasks on a single-index architecture database without an additional index mechanism.

[0006] In order to achieve the above object, the present invention provides the following technical solutions:

[0007] According to a first aspect of an embodiment of the present invention, a query method based on a single index and multiple conditions is proposed, the method comprising:

[0008] Determine the priorities of multiple conditions based on business logic and design row key content based on the priorities;

[0009] Get all the values ​​of each condition within their respective range and store them;

[0010] According to a condition to be queried, the values ​​of other stored conditions are traversed, and the values ​​of each condition are spliced ​​in priority order according to the row key content, and multiple SQL statements are generated in sequence;

[0011] Merge the multiple generated SQL statements and use the merged SQL statements for query.

[0012] Furthermore, all values ​​of each condition within its respective range are obtained and stored, specifically including:

[0013] If the value is updated, the stored content will be updated in time.

[0014] Furthermore, the priorities of multiple conditions are determined according to the business logic, and the row key content is designed according to the priorities, including:

[0015] If the priority order of multiple conditions is determined to be: condition 1> condition 2>...> condition n, then the row key is designed to be condition 1-condition 2-...-condition n.

[0016] Furthermore, the generated multiple SQL statements are merged, specifically including:

[0017] Use the union keyword to combine multiple SQL statements into one SQL statement.

[0018] According to a second aspect of an embodiment of the present invention, a query system based on a single index and multiple conditions is proposed, the system comprising:

[0019] The priority determination module is used to determine the priorities of multiple conditions according to business logic and design row key content according to the priorities;

[0020] The condition value storage module is used to obtain and store all values ​​of each condition within its respective range;

[0021] The condition concatenation module is used to traverse the values ​​of other stored conditions according to a condition to be queried, and concatenate the values ​​of each condition in order of priority according to the row key content, and generate multiple SQL statements in sequence;

[0022] The SQL statement merging query module is used to merge multiple generated SQL statements and use the merged SQL statements for querying.

[0023] Furthermore, the conditional value storage module is specifically used to timely update the stored content if the value is updated.

[0024] Furthermore, the priority determination module is specifically used to design the row key as condition 1-condition 2-...-condition n if the priority order of multiple conditions is determined to be: condition 1>condition 2>...>condition n.

[0025] According to a third aspect of an embodiment of the present invention, a computer storage medium is proposed, wherein the computer storage medium contains one or more program instructions, and the one or more program instructions are used to be executed by a query system based on a single index and multiple conditions to perform any of the methods described above.

[0026] The present invention has the following advantages:

[0027] The present invention proposes a query method and system based on a single index and multiple conditions. The row key rowkey is designed according to the priority of the conditions to be queried, the values ​​required as condition columns are stored in the rowkey in an organized manner to produce multiple SQLs, and the multiple SQLs are merged to improve the query efficiency. The purpose of multi-condition dynamic query can be achieved by arranging SQL and dynamically splicing algorithms. Multi-condition query is realized by complicating SQL and simplifying indexes. The method is simple to implement and easy to implement. The present invention greatly improves the speed of retrieving information in the information system, and reduces the creation and use of indexes to a certain extent. It provides more diverse optimization solutions to reduce data redundancy and improve data writing speed, and has good performance in stability and execution efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0028] In order to more clearly illustrate the implementation methods of the present invention or the technical solutions in the prior art, the following briefly introduces the drawings required for the implementation methods or the description of the prior art. Obviously, the drawings in the following description are only exemplary, and for ordinary technicians in this field, other implementation drawings can be derived from the provided drawings without creative work.

[0029] The structures, proportions, sizes, etc. illustrated in this specification are only used to match the contents disclosed in the specification so as to facilitate understanding and reading by persons familiar with the technology. They are not used to limit the conditions under which the present invention can be implemented, and therefore have no substantial technical significance. Any structural modification, change in proportion or adjustment of size shall still fall within the scope of the technical contents disclosed in the present invention without affecting the effects and purposes that can be achieved by the present invention.

[0030] Figure 1 A flowchart of a query method based on a single index and multiple conditions provided in Example 1 of the present invention. DETAILED DESCRIPTION

[0031] The following is a description of the implementation of the present invention by specific embodiments. People familiar with the art can easily understand other advantages and effects of the present invention from the contents disclosed in this specification. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0032] Example 1

[0033] like Figure 1 As shown, this embodiment proposes a query method based on a single index and multiple conditions, the method comprising:

[0034] S100. Determine the priorities of multiple conditions according to business logic, and design row key content according to the priorities.

[0035] Take employee data as an example, where employees have length of service, department, rank and employee number. In the design of conventional row keys (unique indexes), the employee number, a unique identifier, is often directly selected as the primary key (row key) for storage. However, if other conditions need to be used for query, it is more troublesome, and the only way to increase the query speed is to add indexes.

[0036] In this embodiment, the conditions to be queried are sorted into priorities, and the higher the priority, the more advanced it is, and so on. For example, the department is the most commonly used query, followed by the rank, and then the length of service. Then the row key can be designed as department-rank-length of service-employee number. After this design, if you want to query employees in a certain department, you only need to specify rowkey>departmentx-&&rowkey<departmentx-|. However, it is still necessary to solve the query conditions such as rank.

[0037] S200, obtaining and storing all values ​​of each condition within its respective range.

[0038] First, the value range of department, job level, and length of service is fixed within a company. For example, there are not many departments, and a few dozen are usually enough. Job levels are also basically fixed values, and length of service is basically in the range of 0-50. Then you can store these fixed values, and update this stored collection if there is an update. Then, SQL will be generated through these stored collections every time you query.

[0039] S300. According to a condition to be queried, the values ​​of other stored conditions are traversed, and the values ​​of each condition are concatenated in order of priority according to the row key content, and multiple SQL statements are generated in sequence.

[0040] Since row keys are stored in lexicographic order from left to right, you need to fill in the leftmost column when querying conditions other than the leftmost column. For example, if you want to query employees with a length of service of 3, since there may be employees with a length of service of 3 in each department and each rank, in order to query this condition, you need to obtain all departments and ranks in the collection, and then create multiple SQL statements, which are then concatenated into one SQL statement using union.

[0041] like:

[0042] where rowkey>A department-intermediate-3

[0043] where rowkey>A-department-senior-3

[0044] where rowkey>B department-intermediate-3

[0045] And so on.

[0046] It is worth noting that the more conditions and condition values ​​there are, the more possible permutations and combinations there are between them. So you need to be careful when generating SQL.

[0047] S400: Merge the generated multiple SQL statements, and use the merged SQL statement to perform query.

[0048] After completing the above process, multiple SQL statements will be obtained. Use the union keyword (result set merging function) to merge multiple SQL results. Of course, if some class libraries do not support union, similar functions can also be implemented through programs. Finally, all the results found after executing all the SQL statements are merged. The merged set is the final result to be queried.

[0049] The correctness of the result set can be verified on a small amount of data, and the execution results of the common query statements can be compared with the execution results of the algorithm of the present invention to ensure the accuracy of the algorithm.

[0050] The query method based on single index and multiple conditions proposed in this embodiment is simple to implement and easy to implement. It greatly improves the speed of retrieving information in the information system and reduces the creation and use of indexes to a certain extent. It provides more diverse optimization schemes to reduce data redundancy and improve the writing speed of data. The operability of the present invention has been verified in the construction of a time information system. In the time test, the program designed using the algorithm of the present invention has good performance in stability and execution efficiency.

[0051] Example 2

[0052] Corresponding to the above-mentioned embodiment 1, this embodiment proposes a query system based on a single index and multiple conditions, and the system includes:

[0053] The priority determination module is used to determine the priorities of multiple conditions according to business logic and design row key content according to the priorities;

[0054] The condition value storage module is used to obtain and store all values ​​of each condition within its respective range;

[0055] The condition concatenation module is used to traverse the values ​​of other stored conditions according to a condition to be queried, and concatenate the values ​​of each condition in order of priority according to the row key content, and generate multiple SQL statements in sequence;

[0056] The SQL statement merging query module is used to merge multiple generated SQL statements and use the merged SQL statements for querying.

[0057] Furthermore, the conditional value storage module is specifically used to timely update the stored content if the value is updated.

[0058] Furthermore, the priority determination module is specifically used to design the row key as condition 1-condition 2-...-condition n if the priority order of multiple conditions is determined to be: condition 1>condition 2>...>condition n.

[0059] The functions performed by the various components in the query system based on a single index and multiple conditions provided by the embodiment of the present invention have been described in detail in the above embodiment 1, so they will not be described in detail here.

[0060] Example 3

[0061] Corresponding to the above-mentioned embodiment, this embodiment proposes a computer storage medium, which contains one or more program instructions, and the one or more program instructions are used to be executed by a query system based on a single index and multiple conditions such as the method in Example 1.

[0062] Although the present invention has been described in detail above by general description and specific embodiments, it is obvious to those skilled in the art that some modifications or improvements can be made to the present invention. Therefore, these modifications or improvements made without departing from the spirit of the present invention all belong to the scope of protection claimed by the present invention.

Claims

1. A query method based on single index and multiple conditions, characterized in that: The method comprises: Determine the priorities of multiple conditions based on business logic and design row key content based on the priorities; Get all the values ​​of each condition within their respective range and store them; According to a condition to be queried, the values ​​of other stored conditions are traversed, and the values ​​of each condition are spliced ​​in priority order according to the row key content, and multiple SQL statements are generated in sequence; Merge the generated multiple SQL statements and use the merged SQL statements for query; Determine the priorities of multiple conditions according to business logic, and design the row key content based on the priorities, including: if the priority order of multiple conditions is determined to be: condition 1> condition 2>...> condition n, then the row key is designed to be condition 1-condition 2-...-condition n.

2. A query method based on single index and multiple conditions according to claim 1, characterized in that: Get all the values ​​of each condition within its respective range and store them, including: If the value is updated, the stored content will be updated in time.

3. The query method based on single index and multiple conditions according to claim 1, characterized in that: Merge multiple generated SQL statements, including: Use the union keyword to combine multiple SQL statements into one SQL statement.

4. A query system based on single index and multiple conditions, characterized in that: The system comprises: The priority determination module is used to determine the priorities of multiple conditions according to business logic and design row key content according to the priorities; The condition value storage module is used to obtain and store all values ​​of each condition within its respective range; The condition concatenation module is used to traverse the values ​​of other stored conditions according to a condition to be queried, and concatenate the values ​​of each condition in order of priority according to the row key content, and generate multiple SQL statements in sequence; An SQL statement merging query module is used to merge multiple generated SQL statements and use the merged SQL statements for querying; The priority determination module is specifically used to design a row key as condition 1-condition 2-...-condition n if the priority order of multiple conditions is determined to be: condition 1>condition 2>...>condition n.

5. A query system based on single index and multiple conditions according to claim 4, characterized in that: The conditional value storage module is specifically used to update the stored content in a timely manner if the value is updated.

6. A computer storage medium, characterized in that: The computer storage medium includes one or more program instructions, and the one or more program instructions are used to be executed by a query system based on a single index and multiple conditions according to any one of claims 1 to 3.

Images