CN116719843A - Query method, storage medium and device for database system - Google Patents

Query method, storage medium and device for database system Download PDF

Info

Publication number
CN116719843A
CN116719843A CN202310639992.1A CN202310639992A CN116719843A CN 116719843 A CN116719843 A CN 116719843A CN 202310639992 A CN202310639992 A CN 202310639992A CN 116719843 A CN116719843 A CN 116719843A
Authority
CN
China
Prior art keywords
column
query
index
detection
indexes
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Pending
Application number
CN202310639992.1A
Other languages
Chinese (zh)
Inventor
徐洲
尹强
徐登峰
冷建全
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Beijing Kingbase Information Technologies Co Ltd
Original Assignee
Beijing Kingbase Information Technologies Co Ltd
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by Beijing Kingbase Information Technologies Co Ltd filed Critical Beijing Kingbase Information Technologies Co Ltd
Priority to CN202310639992.1A priority Critical patent/CN116719843A/en
Publication of CN116719843A publication Critical patent/CN116719843A/en
Pending legal-status Critical Current

Links

Classifications

    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/245—Query processing
    • G06F16/2453—Query optimisation
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/22—Indexing; Data structures therefor; Storage structures
    • G06F16/2228—Indexing structures
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/22—Indexing; Data structures therefor; Storage structures
    • G06F16/2228—Indexing structures
    • G06F16/2255—Hash tables
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/242—Query formulation
    • G06F16/2433—Query languages
    • Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
    • Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
    • Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management

Landscapes

  • Engineering & Computer Science (AREA)
  • Theoretical Computer Science (AREA)
  • Physics & Mathematics (AREA)
  • Data Mining & Analysis (AREA)
  • Databases & Information Systems (AREA)
  • General Engineering & Computer Science (AREA)
  • General Physics & Mathematics (AREA)
  • Computational Linguistics (AREA)
  • Software Systems (AREA)
  • Mathematical Physics (AREA)
  • Information Retrieval, Db Structures And Fs Structures Therefor (AREA)

Abstract

The application provides a query method, a storage medium and equipment of a database system. The query method of the database system comprises the steps of obtaining query sentences and extracting all query columns in the query sentences; acquiring column combinations existing in a plurality of column indexes in a query column; acquiring column combinations with the largest number of query columns, and marking the column combinations as detection column combinations; detecting whether query conditions corresponding to the detection column combinations accord with preset conditions or not; if the index configuration of the database system meets the preset condition, acquiring all indexes of which the index key values start by taking the detection column combination as a start, and marking the indexes as a detection index set, wherein the index configuration stores index types and corresponding index key values of the indexes; detecting and acquiring hash multi-column indexes, the number of index key values in a detection index set is the same as the number of query columns in a detection column combination; a query path is created using the hash multi-column index. The hash multi-column index can only be obtained through accurate matching, and the query efficiency is improved.

Description

Query method, storage medium and device for database system
Technical Field
The present application relates to the field of database technologies, and in particular, to a method, a storage medium, and an apparatus for querying a database system.
Background
In the use of databases, querying the database data is a common database operation. In the process of inquiring the data of the database, a user needs to send an inquiry statement to the database, and after receiving the inquiry statement, the database analyzes the inquiry statement and then optimizes the inquiry statement, so that an inquiry path which is considered to be optimal by the system is generated.
For the optimization process of the query statement, a query path generation mode can be provided for the database execution query statement by means of configuration index. The index can be compared with the catalog of the drawing, and after the table is indexed, the content of the query can be quickly positioned through the index when the query is carried out, so that the query speed is improved. And the way the optimizer calls the index at present has room for further optimization.
Disclosure of Invention
An object of the present application is to provide a query method, a storage medium, and a device for a database system that optimizes an index calling method.
A further object of the present application is to improve the efficiency of index selection.
In particular, the present application provides a method for querying a database system, including:
acquiring a query sentence, and extracting all query columns in the query sentence;
acquiring column combinations existing in a plurality of column indexes in all the query columns;
acquiring the column combination with the largest number of query columns in all column combinations, and marking the column combination as a detection column combination;
detecting whether the query conditions corresponding to the detection column combinations accord with preset conditions or not;
if the preset condition is met, acquiring all indexes of index key values starting from the detection column combination according to index configuration of the database system, and marking the indexes as a detection index set, wherein index types of indexes and corresponding index key values are stored in the index configuration;
detecting and acquiring hash multi-column indexes, the number of index key values in the detection index set is the same as the number of query columns in the detection column combination;
if a Ha Xiduo column index is obtained, a query path is created using the Ha Xiduo column index.
Optionally, the step of detecting whether the query condition corresponding to the detection column combination meets the preset condition includes:
if the number of the query columns is less than the number of the query columns, judging whether other column combinations exist, if so, selecting the next group of column combinations according to the sequence from more than less than the number of the query columns, and marking the next group of column combinations as the detection column combinations;
and executing the step of detecting whether the query conditions corresponding to the detection column combinations accord with the preset conditions.
Optionally, the step of detecting and obtaining hash multi-column indexes, the number of index key values in the detection index set is the same as the number of query columns in the detection column combination, includes:
if the Ha Xiduo column index is not obtained, detecting whether other multi-column indexes except the hash multi-column index exist in the detection index set, if so, creating a query path by using the other multi-column indexes, and if not, executing the step of judging whether other column combinations exist.
Optionally, the step of determining whether other column combinations exist includes:
if not, detecting whether a single-column index applicable to the query statement exists, and if so, creating a query path by using the single-column index.
Optionally, the step of detecting whether there is a single column index applicable to the query statement includes:
if the single-column index is not detected, a query path is created according to a preset processing mode.
Optionally, the step of creating the query path according to the preset processing mode includes:
the query path is created in a full table scan.
Optionally, the step of creating a query path using the Ha Xiduo column index includes:
if multiple hash multi-column indexes are acquired, multiple query paths are created by utilizing multiple Ha Xiduo column indexes.
Optionally, the step of detecting whether the query condition corresponding to the column combination meets the preset condition includes:
and detecting whether query conditions corresponding to the detection column combinations are all equations, and if so, conforming the query conditions corresponding to the column combinations to preset conditions.
According to another aspect of the present application, there is also provided a machine-readable storage medium having stored thereon a machine-executable program which, when executed by a processor, implements a method of querying a database system according to any of the above.
According to yet another aspect of the present application, there is also provided a computer device including a memory, a processor, and a machine executable program stored on the memory and running on the processor, and the processor implementing a method of querying a database system according to any of the above when executing the machine executable program.
The query method of the database system acquires all query columns in the query statement, acquires column combinations existing in the multi-column indexes in all query columns, and marks the column combination with the largest number of query columns as a detection column combination. Detecting whether the query conditions corresponding to the detection column combinations accord with preset conditions, if so, acquiring index key values according to index configuration of a database system, marking all indexes starting from the detection column combinations as detection index sets, and storing index types and corresponding index key values of the indexes in the index configuration. And detecting and acquiring hash multi-column indexes, the number of index key values in the detection index set is the same as the number of query columns in the detection column combination. If Ha Xiduo column index is obtained, creating a query path by using the hash multi-column index. That is, it is possible to preliminarily acquire a multi-column index from a column combination in a query sentence, and then determine whether there is an applicable hash multi-column index according to the type of the multi-column index and the number of index key values. Therefore, the hash index can be obtained only through accurate matching, the hash index can be accurately applied, the situation that the hash index cannot find data is effectively avoided, and the query efficiency is improved. Moreover, because the multi-column index is preliminarily obtained according to the column combination in the query statement, the selection of the multi-column index applicable to the leftmost matching is still maintained on the basis of realizing the accurate matching of the hash multi-column index. Therefore, under the condition that no hash multi-column index is available, other multi-column indexes can be used for creating the query path, and the index calling mode in the query process is optimized, so that the query efficiency is guaranteed.
The above, as well as additional objectives, advantages, and features of the present application will become apparent to those skilled in the art from the following detailed description of a specific embodiment of the present application when read in conjunction with the accompanying drawings.
Drawings
Some specific embodiments of the application will be described in detail hereinafter by way of example and not by way of limitation with reference to the accompanying drawings. The same reference numbers will be used throughout the drawings to refer to the same or like parts or portions. It will be appreciated by those skilled in the art that the drawings are not necessarily drawn to scale. In the accompanying drawings:
FIG. 1 is a schematic flow chart diagram of a method of querying a database system in accordance with one embodiment of the application;
FIG. 2 is a schematic flow chart diagram of a method of querying a database system in accordance with another embodiment of the application;
FIG. 3 is a schematic flow chart diagram of a method of querying a database system in accordance with yet another embodiment of the application;
FIG. 4 is a schematic flow chart diagram of a method of querying a database system in accordance with yet another embodiment of the application;
FIG. 5 is a schematic diagram of a machine-readable storage medium according to one embodiment of the application;
FIG. 6 is a schematic diagram of a computer device according to one embodiment of the application.
Detailed Description
It should be understood by those skilled in the art that the embodiments described below are only some embodiments of the present application, but not all embodiments of the present application, and the some embodiments are intended to explain the technical principles of the present application and are not intended to limit the scope of the present application. All other embodiments, which can be obtained by a person skilled in the art without any inventive effort, based on the embodiments provided by the present application, shall still fall within the scope of protection of the present application.
It should be noted that the logic and/or steps represented in the flowcharts or otherwise described herein, for example, may be considered as a ordered listing of executable instructions for implementing logical functions, and may be embodied in any computer-readable medium for use by or in connection with an instruction execution system, apparatus, or device, such as a computer-based system, processor-containing system, or other system that can fetch the instructions from the instruction execution system, apparatus, or device and execute the instructions.
As shown in fig. 1, in one embodiment, a method of querying a database system generally includes:
step S101, acquiring a query sentence, and extracting all query columns in the query sentence. Specifically, for a query statement, a query condition field may be included, and the query condition may include a column name and a value of a column to be queried. Extracting all query columns in the query statement extracts all the column names involved.
For example, the query statement is "select id, age from tb where id =10 and age=12", indicating that the data of id=10 and age=12 is looked up from the table of table name tb. Then, for this query statement, the columns to be queried, i.e., id and age, are assigned "=10" and "=12", respectively. So, query columns, id and age.
Step S102, column combinations existing in the multi-column index in all query columns are acquired. Specifically, a multi-column index is an index that is commonly created for two or more columns of data. The query columns contained in the column combinations of the multi-column index, i.e., the column combinations, are all included in the data columns for which the multi-column index is intended.
Illustratively, the query statement is "select id, age from tb where id =10 and age=12", and if there are multiple column indexes for the id and age columns, then the column combinations id and age can be obtained in the query statement described above because both id and age are present in the multiple column indexes.
Alternatively, if there are multiple column indexes for the id, age, and name columns, then the column combinations id and age can be obtained in the query statement described above, since both id and age are present in the multiple column indexes. If the query statement is "select id, age, name from tb where id =10 and age=12 and name=a," then the column combination exists with id and age, age and name, and id, age and name.
Step S103, obtaining the column combination with the largest number of query columns in all column combinations, and marking the column combination as a detection column combination. Specifically, the column combination having the largest number of query columns is found from all column combinations and is designated as the detection column combination.
Illustratively, the query statement is "select id, age from tb where id =10 and age=12", and if there are multiple column indexes for the id and age columns, then the column combinations id and age can be obtained in the query statement, and the column combinations, i.e., id and age, are detected because there are only two query columns at most.
Alternatively, if there are multiple column indexes for the id, age, and name columns, then the column combinations id and age can be obtained in the query statement described above, and the column combinations id and age are detected because there are only two query columns at most. If the query statement is "select id, age, name from tb where id =10 and age=12 and name=a," then the column combination exists and the column combination is detected as id, age and name.
Step S104, detecting whether the query condition corresponding to the detection column combination meets the preset condition. Specifically, whether the query conditions corresponding to the detection column combinations are all equations is detected, and if so, the query conditions corresponding to the detection column combinations accord with preset conditions. That is, it is detected whether the assignment of the query column in the column combination is an equation, and if so, the query condition of the detection column combination meets the preset condition.
For example, the query sentence is "select id, age from tb where id =10 and age=12", and if the detection column combinations id and age are acquired, the query conditions corresponding to the detection column combinations id and age are equations ("=10" and "=12"), so the detection column combinations satisfy the preset conditions.
Step S105, if the preset condition is met, acquiring all indexes of the index key value starting with the detection column combination according to the index configuration of the database system, and marking the indexes as detection index sets. The index configuration stores index types and corresponding index key values of the indexes. Specifically, for an index, which includes an index type and an index key value, when the index is created, setting may be made in a creation statement. The index created by the database system is recorded in the index configuration of the database system. Among these index types may be hash index, b+ tree index, etc. The index key is a column to which the index is directed, for example, a multi-column index for an id column and an age column, and the index key is id and age.
Because the order of index key values is also one of the attributes of the index at the time of index creation. Therefore, the index key value is the index whose index key value starts with the detection column combination, that is, the index whose index key value starts with the detection column combination is the same as the index of the detection column combination.
For example, the query term is "select id, age from tb where id =10 and age=12", and if the detection column combination id and age are acquired, the detection column combination meets the preset condition. So the indexes of the first index key value id and the second index key value age are obtained, and whether other index key values exist at the back side is not considered. And marking all the acquired indexes as a detection index set.
Step S106, hash multi-column indexes with the same number of index key values in the detection index set as the number of query columns in the detection column combination are detected and acquired. Specifically, whether hash index exists in the detection index set is detected, if hash index exists, whether the number of index key values of the detection hash index is the same as the number of detection column combinations is detected, and if the number of index key values is the same, the hash index is obtained.
In step S107, if the Ha Xiduo column index is obtained, a query path is created using the hash multiple column index. If the corresponding hash index is available, a query path is created using the hash index.
Although hash indexes are faster to query than other indexes, the use of hash indexes is difficult because Ha Xiduo column indexes only support exact matches, while existing database systems all use leftmost matches.
Specifically, for the leftmost matching principle, if the index key values of the multi-column index are id and age, even if the query condition of the query sentence is only "id=10", it is still determined that the multi-column index can be used. That is, the leftmost match, i.e., starting from the foremost key of the index, any number of consecutive indexes may be matched. This is different from the exact matching principle of hash multi-column indexes, because Ha Xiduo column indexes only have index key values that appear and are equations. Therefore, there are difficulties in using hash multi-column indexes. For example, if the index key value of the Ha Xiduo column index is id and age, even if the query condition of the query statement is only "id=10", the hash index is still determined to be usable because of the existing leftmost matching principle, but the actual hash index is not usable, which may cause a situation that the query cannot find data.
In the scheme of the embodiment, by acquiring all query columns in the query statement, and then acquiring column combinations existing in the multi-column index in all query columns, the column combination with the largest number of query columns is recorded as a detection column combination. Detecting whether the query conditions corresponding to the detection column combinations accord with preset conditions, if so, acquiring index key values according to index configuration of a database system, marking all indexes starting from the detection column combinations as detection index sets, and storing index types and corresponding index key values of the indexes in the index configuration. And detecting and acquiring hash multi-column indexes, the number of index key values in the detection index set is the same as the number of query columns in the detection column combination. If Ha Xiduo column index is obtained, creating a query path by using the hash multi-column index.
That is, it is possible to preliminarily acquire a multi-column index from a column combination in a query sentence, and then determine whether there is an applicable hash multi-column index according to the type of the multi-column index and the number of index key values. Therefore, the hash index can be obtained only through accurate matching, the hash index can be accurately applied, and the situation that the hash index cannot find data is effectively avoided. Moreover, because the multi-column index is preliminarily obtained according to the column combination in the query statement, the selection of the multi-column index applicable to the leftmost matching is still maintained on the basis of realizing the accurate matching of the hash multi-column index. Therefore, under the condition that no hash multi-column index is available, other multi-column indexes can be used for creating the query path, and the index calling mode in the query process is optimized, so that the query efficiency is guaranteed.
For example, if there are hash multi-column indexes for id and age and b+ tree multi-column indexes for id and age in the index configuration. When the query statement is "select id, age from tb where id =10 and age=12", i.e., the data of id=10 and age=12 is searched for from the table of table name tb. Then, for this query statement. First, the detection column combination is an id column and an age, and the detection column combination meets a preset condition. The hash multi-column index for id and age and the b+ tree multi-column index for id and age are all started with the detection column combination, so the two indexes are acquired and marked as a detection index set. The hash index with the same index key value number as the query column number of the detection column combination exists in the detection index set, namely the hash index for id and age, so that the query path is created by using the hash index.
For another example, if there is a hash multi-column index for id, age, and name in the index configuration. When the query statement is "select id, age from tb where id =10 and age=12", i.e., the data of id=10 and age=12 is searched for from the table of table name tb. Then, for this query statement. First, the detection column combination is an id column and an age, and the detection column combination meets a preset condition. The hash multi-column index for id, age and name starts with the detection column combination, so this index is obtained and recorded as the detection index set. Since the hash multi-column index for id, age, and name does not agree with the number of detection column combination query columns, it is determined that it cannot be used.
As shown in fig. 2, in one embodiment, a method of querying a database system generally includes:
step S201, acquiring a query sentence, and extracting all query columns in the query sentence.
Step S202, obtaining column combinations existing in the multi-column index in all query columns.
Step S203, the column combination with the largest number of query columns in all column combinations is acquired and is marked as a detection column combination.
Step S204, detecting whether the query condition corresponding to the detection column combination meets the preset condition, if so, executing step S205, otherwise, executing step S208. Specifically, whether the query conditions corresponding to the detection column combinations are all equations is detected, and if so, the query conditions corresponding to the detection column combinations accord with preset conditions.
In step S205, all indexes starting with the detection column combination are marked as a detection index set according to the index configuration of the database system.
Step S206, hash multi-column indexes with the same number of index key values in the detection index set as the number of query columns in the detection column combination are detected and acquired.
In step S207, if the Ha Xiduo column index is obtained, a query path is created using the hash multiple column index.
Step S208, it is determined whether other column combinations exist, if so, step S209 is executed, and if not, step S210 is executed. Specifically, it is determined whether there are any undetected column combinations. If the query columns of the query statement are above three columns, there may be multiple column combinations. For example, if the query statement is "select id, age, name from tb where id =10 and age=12 and name=a," then the column combination may have ids and ages, ages and names, and ids, ages and names.
Step S209, selecting the next group of column combinations according to the order of the number of the query columns from more to less, and marking the next group of column combinations as detection column combinations. That is, the next group of column combinations is selected according to the order of the number of query columns contained in the column combinations from more to less, and the column combinations are marked as detection column combinations. Step S204 is performed.
That is, when the current detection column combination does not satisfy the preset condition, the detection is continued by selecting a column combination from among the column combinations as the detection column combination in the order in which the number of query columns is greater than or equal to the predetermined number.
Step S210, detecting whether there is a single-column index applicable to the query sentence, if yes, executing step S211, otherwise, executing step S212. Specifically, under the condition that all column combinations of the query statement do not meet the preset conditions, it is explained that the query statement has no available multi-column index, then whether the query statement has an available single-column index is detected, a single query column of the query statement can be regarded as a column combination by the detection mode, and the detection mode which is the same as that of the column combination is adopted.
Step S211, creating a query path using the single column index. If the appropriate single-column index can be obtained, then the query path is created with the single-column index.
Step S212, a query path is created according to a preset processing mode. If the appropriate single column index is not obtained, then the query path may be created in a manner other than the index. For example, the query path is created in a full table scan, or the query path is created in a view query, or the like.
In the scheme of the present embodiment, the detection is performed by selecting the column combinations of the query sentences in order of the number of query columns included from a larger number to a smaller number. That is, in the case where there is a hash index that can be applied, the hash index of the query column containing the most query sentences can be preferentially selected and called, thereby ensuring that an optimal query path is created.
For example, if there are hash-multi-column indexes for id, age, and score in the index configuration, and hash-multi-column indexes for id and age. When the query statement is "select id, age, score from tb where id =m and age=n and score=x". Then, for this query statement, the column combination includes id, age, and score, and id and age.
First, the detection column combination is id, age and score, and the detection column combination query conditions are equations, which meet the preset conditions. The hash multi-column index for id, age and score starts with the detection column combination, so this index is obtained and recorded as the detection index set. Since the hash multi-column index for id, age, and score is consistent with the number of detection column combined query columns, this hash multi-column index is applicable. The column combinations of id and age are no longer detected.
When the query statement is "select id, age, score from tb where id =m and age=n and score > x". Then, for this query statement, the column combination includes id, age, and score, and id and age. First, the detection column combination is id, age and score, and the detection column combination query condition is uneven as an equation, and does not conform to the preset condition. Therefore, a column combination of id and age is selected as a detection column combination, and the column combination of id and age is an equation and meets preset conditions. The hash indexes for id, age and score are all starting from the detection column combination, so the acquisition index is recorded as the detection index set. The hash multi-column index for id, age and score is not chosen because it is inconsistent with the number of detection column combined query columns. Since the hash multiple-column index for id and age is consistent with the number of detection column combined query columns, this hash multiple-column index is applicable.
As shown in fig. 3, in one embodiment, a method of querying a database system generally includes:
step S301, a query sentence is acquired, and all query columns in the query sentence are extracted.
In step S302, column combinations existing in the multi-column index in all query columns are acquired.
Step S303, the column combination with the largest number of query columns in all column combinations is acquired and is marked as a detection column combination.
Step S304, detecting whether the query condition corresponding to the detection column combination meets the preset condition, if so, executing step S305, and if not, executing step S310. Specifically, whether the query conditions corresponding to the detection column combinations are all equations is detected, and if so, the query conditions corresponding to the detection column combinations accord with preset conditions.
In step S305, the index key value is obtained according to the index configuration of the database system to detect all indexes starting from the column combination, and the index is recorded as a detection index set.
Step S306, hash multi-column indexes with the same number of index key values in the detection index set as the number of query columns in the detection column combination are detected and acquired.
In step S307, if the Ha Xiduo column index is obtained, a query path is created using the hash multiple column index.
Step S308, if the Ha Xiduo column index is not obtained, detecting whether there are other multi-column indexes except the hash multi-column index in the detection index set, if yes, executing step S309, creating a query path by using the other multi-column indexes, and if no, executing step 310. Specifically, if there are no hash multi-column indexes available, a check is made to detect whether there are other multi-column indexes in the index set, such as b+ tree indexes, etc.
Step S309, create query paths using other multi-column indexes. Because the multi-column index except the Ha Xiduo column index does not need to ensure that the number of index key values is the same as the number of query columns, only the query columns and the index key values can be matched from front to back. Therefore, in the case where the detected column combination meets the preset condition, it is necessary to use only if there are other multi-column indexes.
Step S310, determining whether there are other column combinations, if so, executing step S311, and if not, executing step S312. Specifically, it is determined whether there are any undetected column combinations.
Step S311, selecting the next group of column combinations according to the order of the number of the query columns from more to less, and recording the selected group of column combinations as detection column combinations. Step S304 is performed.
Step S312, detecting whether there is a single-column index applicable to the query sentence, if yes, executing step S313, otherwise, executing step S314.
In step S313, a query path is created using the single column index. If the appropriate single-column index can be obtained, then the query path is created with the single-column index.
Step S314, a query path is created according to a preset processing mode. If the appropriate single column index is not obtained, then the query path may be created in a manner other than the index. For example, the query path is created in a full table scan, or the query path is created in a view query, or the like.
As shown in fig. 4, in one embodiment, a method of querying a database system generally includes: the execution flow in the case where there is a hash multi-column index available is as follows:
step S401, acquiring a query sentence, and extracting all query columns in the query sentence.
Step S402, a detection column combination is acquired. The column combinations are detected, i.e., the column combinations that exist in the multi-column index among all query columns.
Step S403, detecting whether the query condition corresponding to the detection column combination meets the preset condition, if so, executing step S404, and if not, executing step S405. Specifically, whether the query conditions corresponding to the detection column combinations are all equations is detected, and if so, the query conditions corresponding to the detection column combinations accord with preset conditions.
Step S404, obtaining index key values according to index configuration of the database system, taking the detection column combination as all indexes starting, and marking the indexes as a detection index set.
Step S405, detecting whether the index in the index set is a hash index, if so, executing step S406. I.e. sequentially detecting if the index in the detection index set is a hash index.
In step S406, if the number of index key values of the hash multi-column index is identical to the number of query columns of the detection column combination, step S407 is executed.
Step S407, creating a query path by using the hash multi-column index.
Specifically, in one embodiment, the step of creating a query path using a hash index includes: if a plurality of hash indexes are acquired, a plurality of query paths are created by using the hash indexes. And then the optimal query path is selected by the optimizer according to the cost rule.
The present embodiment also provides a machine-readable storage medium and a computer device. Fig. 5 is a schematic diagram of a machine-readable storage medium 10 according to one embodiment of the application. Fig. 6 is a schematic diagram of a computer device 20 according to one embodiment of the application.
The machine-readable storage medium 10 has stored thereon a machine-executable program 11, which when executed by a processor, implements the database system querying method of any of the embodiments described above.
The computer device 20 may include a memory 210, a processor 220, and a machine executable program 11 stored on the memory 210 and running on the processor 220, and the processor 220 implements the database system querying method of any of the embodiments described above when executing the machine executable program 11.
It should be noted that the logic and/or steps represented in the flow diagrams or otherwise described herein, e.g., a ordered listing of executable instructions for implementing logical functions, can be embodied in any machine-readable storage medium for use by or in connection with an instruction execution system, apparatus, or device, such as a computer-based system, processor-containing system, or other system that can fetch the instructions from the instruction execution system, apparatus, or device and execute the instructions.
For the purposes of this description of embodiments, a machine-readable storage medium 10 can be any apparatus that can contain, store, communicate, propagate, or transport the program for use by or in connection with the instruction execution system, apparatus, or device. More specific examples (a non-exhaustive list) of the computer-readable medium would include the following: an electrical connection (electronic device) having one or more wires, a portable computer diskette (magnetic device), a Random Access Memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber device, and a portable compact disc read-only memory (CDROM). Additionally, the machine-readable storage medium 10 may even be paper or other suitable medium upon which the program is printed, as the program may be electronically captured, via, for instance, optical scanning of the paper or other medium, then compiled, interpreted, or otherwise processed in a suitable manner, if necessary, and then stored in a computer memory.
It is to be understood that portions of the present application may be implemented in hardware, software, firmware, or a combination thereof. In the above-described embodiments, the various steps or methods may be implemented in software or firmware stored in a memory and executed by a suitable instruction execution system.
The computer device 20 may be, for example, a server, a desktop computer, a notebook computer, a tablet computer, or a smartphone. In some examples, computer device 20 may be a cloud computing node. The computer device 20 may be described in the general context of computer system-executable instructions, such as program modules, being executed by a computer system. Generally, program modules may include routines, programs, objects, components, logic, data structures, etc. that perform particular tasks or implement particular abstract data types. Computer device 20 may be practiced in distributed cloud computing environments where tasks are performed by remote processing devices that are linked through a communications network. In a distributed cloud computing environment, program modules may be located in both local and remote computing system storage media including memory storage devices.
Computer device 20 may include a processor 220 adapted to execute stored instructions, a memory 210 providing temporary storage for the operation of the instructions during operation. Processor 220 may be a single core processor, a multi-core processor, a computing cluster, or any number of other configurations. Memory 210 may include Random Access Memory (RAM), read only memory, flash memory, or any other suitable storage system.
Processor 220 may be connected via a system interconnect (e.g., PCI-Express, etc.) to an I/O interface (input/output interface) adapted to connect computer device 20 to one or more I/O devices (input/output devices). The I/O devices may include, for example, a keyboard and a pointing device, which may include a touch pad or touch screen, among others. The I/O device may be a built-in component of the computer device 20 or may be a device externally connected to the computing device.
The processor 220 may also be linked through a system interconnect to a display interface suitable for connecting the computer device 20 to a display device. The display device may include a display screen as a built-in component of the computer device 20. The display device may also include a computer monitor, television, projector, or the like, that is externally connected to the computer device 20. Further, a network interface controller (network interface controller, NIC) may be adapted to connect the computer device 20 to a network through a system interconnect. In some embodiments, the NIC may use any suitable interface or protocol (such as an internet small computer system interface, etc.) to transfer data. The network may be a cellular network, a radio network, a Wide Area Network (WAN), a Local Area Network (LAN), or the internet, among others. The remote device may be connected to the computing device through a network.
The flowcharts provided by the present application are not intended to indicate that the operations of the methods are to be performed in any particular order, or that all of the operations of the methods are included in all of each case. Furthermore, the method may include additional operations. Additional variations may be made to the above-described methods within the scope of the technical ideas provided by the methods of the present embodiments.
By now it should be appreciated by those skilled in the art that while a number of exemplary embodiments of the application have been shown and described herein in detail, many other variations or modifications of the application consistent with the principles of the application may be directly ascertained or inferred from the present disclosure without departing from the spirit and scope of the application. Accordingly, the scope of the present application should be understood and deemed to cover all such other variations or modifications.

Claims (10)

1. A method of querying a database system, comprising:
acquiring a query sentence, and extracting all query columns in the query sentence;
acquiring column combinations existing in a plurality of column indexes in all the query columns;
acquiring the column combination with the largest number of query columns in all column combinations, and marking the column combination as a detection column combination;
detecting whether the query conditions corresponding to the detection column combinations accord with preset conditions or not;
if the preset condition is met, acquiring all indexes of index key values starting from the detection column combination according to index configuration of the database system, and marking the indexes as a detection index set, wherein index types of indexes and corresponding index key values are stored in the index configuration;
detecting and acquiring hash multi-column indexes, the number of index key values in the detection index set is the same as the number of query columns in the detection column combination;
if a Ha Xiduo column index is obtained, a query path is created using the Ha Xiduo column index.
2. The query method of the database system according to claim 1, wherein the step of detecting whether the query condition corresponding to the detection column combination meets a preset condition comprises:
if the number of the query columns is less than the number of the query columns, judging whether other column combinations exist, if so, selecting the next group of column combinations according to the sequence from more than less than the number of the query columns, and marking the next group of column combinations as the detection column combinations;
and executing the step of detecting whether the query conditions corresponding to the detection column combinations accord with the preset conditions.
3. The query method of the database system according to claim 2, wherein the step of detecting and acquiring hash multi-column indexes having the same number of index key values in the detection index set as the number of query columns in the detection column combination includes:
if the Ha Xiduo column index is not obtained, detecting whether other multi-column indexes except the hash multi-column index exist in the detection index set, if so, creating a query path by using the other multi-column indexes, and if not, executing the step of judging whether other column combinations exist.
4. The query method of a database system according to claim 2, wherein the step of determining whether other column combinations exist comprises, after:
if not, detecting whether a single-column index applicable to the query statement exists, and if so, creating a query path by using the single-column index.
5. The query method of a database system according to claim 4, wherein said step of detecting whether there is a single column index applicable to said query statement comprises, after:
if the single-column index is not detected, a query path is created according to a preset processing mode.
6. The query method of a database system according to claim 5, wherein the step of creating the query path according to a preset processing manner comprises:
the query path is created in a full table scan.
7. The query method of a database system according to claim 1, wherein the creating a query path using the Ha Xiduo column index comprises:
if multiple hash multi-column indexes are acquired, multiple query paths are created by utilizing multiple Ha Xiduo column indexes.
8. The query method of a database system according to claim 1, wherein the step of detecting whether the query condition corresponding to the column combination meets a preset condition comprises:
and detecting whether query conditions corresponding to the detection column combinations are all equations, and if so, conforming the query conditions corresponding to the column combinations to preset conditions.
9. A machine-readable storage medium having stored thereon a machine-executable program which when executed by a processor implements a method of querying a database system according to any of claims 1 to 8.
10. A computer device comprising a memory, a processor and a machine executable program stored on the memory and running on the processor, and the processor implementing a method of querying a database system according to any of claims 1 to 8 when executing the machine executable program.
CN202310639992.1A 2023-05-31 2023-05-31 Query method, storage medium and device for database system Pending CN116719843A (en)

Priority Applications (1)

Application Number Priority Date Filing Date Title
CN202310639992.1A CN116719843A (en) 2023-05-31 2023-05-31 Query method, storage medium and device for database system

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
CN202310639992.1A CN116719843A (en) 2023-05-31 2023-05-31 Query method, storage medium and device for database system

Publications (1)

Publication Number Publication Date
CN116719843A true CN116719843A (en) 2023-09-08

Family

ID=87867159

Family Applications (1)

Application Number Title Priority Date Filing Date
CN202310639992.1A Pending CN116719843A (en) 2023-05-31 2023-05-31 Query method, storage medium and device for database system

Country Status (1)

Country Link
CN (1) CN116719843A (en)

Cited By (1)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN117093611A (en) * 2023-10-16 2023-11-21 北京人大金仓信息技术股份有限公司 Database combined index suggestion processing method, storage medium and computer device

Cited By (2)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN117093611A (en) * 2023-10-16 2023-11-21 北京人大金仓信息技术股份有限公司 Database combined index suggestion processing method, storage medium and computer device
CN117093611B (en) * 2023-10-16 2024-03-19 北京人大金仓信息技术股份有限公司 Database combined index suggestion processing method, storage medium and computer device

Similar Documents

Publication Publication Date Title
CN105653537A (en) Paging query method and device for database application system
CN104468777A (en) Data operating method and device
CN111913954B (en) Intelligent data standard catalog generation method and device
CN116719843A (en) Query method, storage medium and device for database system
CN116257552A (en) Database query statement optimization method, storage medium and device
CN116595044A (en) Optimization method, storage medium and equipment for database selectivity calculation
CN109543027A (en) The acquisition methods and device of paged data, equipment and storage medium
CN111651397B (en) Method and equipment for accessing peripheral module of PXIe
CN110874365B (en) Information query method and related equipment thereof
CN112256999A (en) Page display method, device and equipment
CN111724143A (en) RPA-based flow element positioning method and device, computing equipment and storage medium
CN111078671A (en) Method, device, equipment and medium for modifying data table field
CN106202423A (en) A kind of file ordering method and apparatus
CN107273293B (en) Big data system performance test method and device and electronic equipment
CN114020769A (en) Data blood margin analysis method and device and storage medium
CN110297825B (en) Data processing method, device, computer equipment and storage medium
US10866993B2 (en) Managing online help information in a data center
CN112836472A (en) Address annotation method, device, equipment and storage medium
CN110704523A (en) Data export method, device, equipment and computer readable storage medium
CN109815426A (en) Page info detection method and device, terminal and computer readable storage medium
CN117149821B (en) Query optimization method, storage medium and computer equipment
CN107958074B (en) Engineering information display method and device
CN117743382A (en) Method, medium and computer equipment for using result set of database
JP2019109554A (en) Information processing program, information processing apparatus and information processing method
CN116257551A (en) Processing method, storage medium and device for database query statement

Legal Events

Date Code Title Description
PB01 Publication
PB01 Publication
SE01 Entry into force of request for substantive examination
SE01 Entry into force of request for substantive examination