CN117093611A - Database combined index suggestion processing method, storage medium and computer device - Google Patents
Database combined index suggestion processing method, storage medium and computer device Download PDFInfo
- Publication number
- CN117093611A CN117093611A CN202311329424.8A CN202311329424A CN117093611A CN 117093611 A CN117093611 A CN 117093611A CN 202311329424 A CN202311329424 A CN 202311329424A CN 117093611 A CN117093611 A CN 117093611A
- Authority
- CN
- China
- Prior art keywords
- query
- condition
- combined index
- preset
- obtaining
- 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.)
- Granted
Links
- 238000003672 processing method Methods 0.000 title claims abstract description 19
- 238000000034 method Methods 0.000 claims description 20
- 238000001914 filtration Methods 0.000 claims description 13
- 238000005457 optimization Methods 0.000 claims description 7
- 230000006870 function Effects 0.000 claims description 5
- 238000012545 processing Methods 0.000 claims description 3
- 230000001174 ascending effect Effects 0.000 claims description 2
- 238000010586 diagram Methods 0.000 description 7
- 238000004458 analytical method Methods 0.000 description 2
- 239000000463 material Substances 0.000 description 2
- 238000012986 modification Methods 0.000 description 2
- 230000004048 modification Effects 0.000 description 2
- 230000001413 cellular effect Effects 0.000 description 1
- 238000004891 communication Methods 0.000 description 1
- 238000012217 deletion Methods 0.000 description 1
- 230000037430 deletion Effects 0.000 description 1
- 238000005516 engineering process Methods 0.000 description 1
- 230000005055 memory storage Effects 0.000 description 1
- 230000003287 optical effect Effects 0.000 description 1
- 239000013307 optical fiber Substances 0.000 description 1
- 238000012546 transfer Methods 0.000 description 1
- 238000012795 verification Methods 0.000 description 1
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
- G06F16/24534—Query rewriting; Transformation
- G06F16/24542—Plan optimisation
- G06F16/24545—Selectivity estimation or determination
-
- 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/221—Column-oriented storage; Management thereof
-
- 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/2246—Trees, e.g. B+trees
-
- 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)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Software Systems (AREA)
- Operations Research (AREA)
- Computational Linguistics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention provides a database combined index suggestion processing method, a storage medium and computer equipment, wherein the processing method comprises the following steps: in a preset stage of data query, obtaining preset conditions of data columns participating in combined index suggestions in each base table; obtaining the selectivity of each preset condition; and obtaining the combined index suggestion with highest query efficiency according to the selection rate. The technical scheme provided by the invention can solve the problem of unreasonable combined index suggestion during data query in the prior art, thereby achieving the purpose of improving the data query efficiency.
Description
Technical Field
The present invention relates to the field of database query technologies, and in particular, to a database combined index suggestion processing method, a storage medium, and a computer device.
Background
In the database, each column of the combined index exists in a B-Tree index, and each column has a pointer pointing to the other B-Tree index, so that a multi-level B-Tree index structure is formed, and each level points to the next level and finally points to a data line. When processing the inquiry request, the first column in the combined index is checked first, if the value of the first column meets the inquiry condition, the second column is checked continuously, if the value of the second column also meets the inquiry condition, the third column is checked, and the like, and finally the data row meeting the inquiry condition is found.
When data query is performed on a plurality of data tables, a combined index suggestion is required to be acquired, wherein the combined index suggestion refers to that for a given database and SQL (Structured Query Language ) statement, an index suggestion DDL (Data Definition Language ) statement for creating an index is given for improving the execution speed of the original SQL statement.
When the database gives a combined index suggestion for a plurality of columns of a table, the database always gives the index suggestion according to the arrangement combination for the data columns participating in the combination, and as the number of the data columns increases, the number of the combinations increases exponentially, and huge expenditure is brought to the subsequent index verification. The combination mode commonly used in the prior art can sort the data columns according to the historical use frequency, and cannot obtain the combination index suggestion with highest execution efficiency.
Disclosure of Invention
In view of the above problems, the present invention provides a database combined index suggestion processing method, a storage medium and a computer device, which overcome the above problems or at least partially solve the above problems, and can solve the problem of unreasonable combined index suggestions during data query in the prior art, so as to achieve the purpose of improving the data query efficiency.
Specifically, the invention provides a database combined index suggestion processing method,
in a preset stage of data query, obtaining preset conditions of data columns participating in combined index suggestions in each base table;
obtaining the selectivity of each preset condition; and
and obtaining the combined index suggestion with highest query efficiency according to the selection rate and the data column.
According to one embodiment of the present invention, the preset stage is a query optimization stage.
According to an embodiment of the present invention, the obtaining the preset condition to which the data column participating in the combined index suggestion in each base table belongs includes:
and acquiring the query conditions related to each base table from the query statement, and obtaining the preset conditions according to the query conditions.
According to one embodiment of the present invention, the obtaining the preset condition according to the query condition includes:
deleting the query conditions which are not supported by the index, and taking the query conditions supported by the index as the preset conditions.
According to one embodiment of the present invention, the deleting the query condition that is not supported by the index includes:
if both ends of the query condition are related to the current table, contain unstable functions or operators and do not support indexes, the query condition is the condition that the indexes do not support.
According to one embodiment of the present invention, the obtaining the selectivity of each preset condition includes:
and obtaining the statistical information of each base table, and calculating the selectivity according to each statistical information.
According to one embodiment of the present invention, the obtaining the combined index suggestion with highest query efficiency according to the selection ratio and the data column includes:
and arranging corresponding data columns according to the sequence from the small selection rate to the large selection rate, and obtaining the combined index suggestion according to the ascending arrangement result.
According to one embodiment of the present invention, the preset condition is a filtering condition or a connection condition.
In another aspect, the present invention also provides a machine-readable storage medium, on which a machine-executable program is stored, which when executed by a processor, implements a database combined index recommendation processing method according to any one of the embodiments described above.
In yet another aspect, the present invention further provides a computer device, including a memory, a processor, and a machine executable program stored on the memory and running on the processor, where the processor executes the machine executable program to implement a database combined index suggestion processing method according to any of the above embodiments.
In the technical scheme provided by the invention, in the preset stage of data query, firstly, preset conditions of each base table to which a data column needing to participate in the combined index suggestion belongs are acquired, then, the selectivity of each preset condition is acquired, and the combined index suggestion with highest query efficiency of each base table is obtained according to the selectivity of each preset condition. The selection rate of the preset conditions can accurately reflect the times which need to be compared in the query process, so that the combined index suggestion with highest query efficiency is obtained according to the selection rate of each preset condition in the technical scheme of the invention, and the working efficiency of the database for data query can be improved.
The above, as well as additional objectives, advantages, and features of the present invention will become apparent to those skilled in the art from the following detailed description of a specific embodiment of the present invention when read in conjunction with the accompanying drawings.
Drawings
Some specific embodiments of the invention 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 diagram of a database combined index suggestion processing method in accordance with one embodiment of the invention;
FIG. 2 is a schematic flow chart of a method of obtaining preset conditions to which each base table needs to participate in a combined index suggestion data column according to one embodiment of the invention;
FIG. 3 is a schematic flow chart of a method of obtaining preset conditions to which data columns of a base table need to participate in a combined index belong in accordance with one embodiment of the invention;
FIG. 4 is a schematic flow chart diagram of a method of obtaining a most query-efficient combined index suggestion for base tables in accordance with one embodiment of the invention;
FIG. 5 is a schematic diagram of a machine-readable storage medium according to one embodiment of the invention;
FIG. 6 is a schematic diagram of a computer device according to one embodiment of the invention.
Detailed Description
A database combined index recommendation processing method, a storage medium, and a computer device according to an embodiment of the present invention are described below with reference to fig. 1 to 6. In the description of the present embodiment, it should be understood that the terms "first" and "second" are used for descriptive purposes only and are not to be construed as indicating or implying a relative importance or implicitly indicating the number of technical features indicated. Thus, a feature defining "a first" or "a second" may explicitly or implicitly include at least one such feature, i.e. one or more such features. In the description of the present invention, the meaning of "plurality" means at least two, for example, two, three, etc., unless specifically defined otherwise. When a feature "comprises or includes" a feature or some of its coverage, this indicates that other features are not excluded and may further include other features, unless expressly stated otherwise.
In the description of the present embodiment, a description referring to the terms "one embodiment," "some embodiments," "illustrative embodiments," "examples," "specific examples," or "some examples," etc., means that a particular feature, structure, material, or characteristic described in connection with the embodiment or example is included in at least one embodiment or example of the present invention. In this specification, schematic representations of the above terms do not necessarily refer to the same embodiments or examples. Furthermore, the particular features, structures, materials, or characteristics described may be combined in any suitable manner in any one or more embodiments or examples.
Referring to fig. 1, fig. 1 is a schematic flowchart of a database combined index suggestion processing method according to an embodiment of the present invention, where the method is used to obtain a combined index suggestion with highest query efficiency when a database performs a data query, so as to improve the working efficiency of the database data query. In this embodiment, a detailed description is made of a database combined index suggestion processing method in combination with the flow shown in fig. 1.
As shown in fig. 1, the database combined index suggestion processing method of the present embodiment includes the following steps:
step S1: in a preset stage of data query, obtaining preset conditions of data columns of each base table which need to participate in combined index suggestion;
step S2: acquiring the selectivity of each base table corresponding to a preset condition;
step S3: and obtaining the combined index suggestion with highest query efficiency according to the selectivity of each base table corresponding to the preset condition.
In the step S1, the preset stage of the data query refers to one of the stages when the data query is performed on the base table in the database according to the SQL statement during the execution of the SQL statement. The data columns in the base table which need to participate in the combined index suggestion are the data columns in each base table involved in executing the SQL statement, and the preset condition to which the data columns belong can be a filtering condition or a connection condition.
In the step S2, the selectivity of the preset condition refers to the ratio of the number of rows of the data column corresponding to the preset condition to the total number of rows of the data in the base table, and the selectivity may reflect the number of times that the data column corresponding to the preset condition needs to be compared when the data query is performed.
In the step S3, the combined index suggestion with the highest query efficiency of each base table may be generated according to the data sequence of each base table that needs to participate in the combined index suggestion and the selectivity of the corresponding preset condition, or all the combined index suggestions of each base table may be generated by using a method in the prior art, and then the query efficiency of each combined index suggestion is calculated according to the selectivity of each preset condition, and the combined index suggestion with the highest query efficiency is selected.
In summary, in the preset stage of data query in this embodiment, first, the preset conditions to which the data columns of each base table need to participate in the combined index suggestion belong are obtained, then the selectivity of each preset condition is obtained, and the combined index suggestion with the highest query efficiency of each base table is obtained according to the selectivity of each preset condition. The number of times of comparison in the query process can be accurately reflected by the selectivity of the preset conditions, so that the working efficiency of the database for data query can be improved according to the combined index suggestion with highest query efficiency obtained by the selectivity of each preset condition.
According to the technical scheme provided by the invention, the combined index suggestion with highest query efficiency is obtained according to the selectivity of each base table corresponding to the preset condition, and the selection rate according to the preset condition is the proportion of the number of lines of the data column corresponding to the preset condition to the total data line, instead of the historical use frequency of the preset condition adopted in the prior art, so that the technical scheme of the invention has outstanding substantive characteristics and remarkable progress, and can effectively improve the working efficiency of data query of a database.
In an embodiment of the present invention, the preset stage of the database query in the step S1 is a query optimization stage of the database query.
When executing the SQL statement, after the SQL statement enters the database, the SQL statement needs to be processed in the stages of lexical grammar analysis, semantic analysis, query rewriting, query optimization and query execution. In the query optimization stage of the SQL sentence, all logic optimization measures in the SQL sentence are basically completed, the filtering condition and the connection condition of the SQL sentence are not changed any more, and the accuracy of the obtained query condition is highest. Therefore, in the embodiment, the combined index suggestion of each base table is obtained in the query optimization stage, so that the accuracy and reliability of data query can be improved.
In one embodiment of the present invention, the method for obtaining the preset condition to which each base table needs to participate in the data column of the combined index suggestion in the step S1 is shown in fig. 2, and includes the following steps:
step S21: acquiring the query conditions related to each base table from the query statement;
step S22: and obtaining preset conditions of the data columns of each base table which need to participate in the combined index according to the query conditions related to each base table.
In this embodiment, the query conditions include a filter condition and a connection condition. When the step S21 is executed, the filtering condition and the connection condition in the query sentence are obtained by identifying and analyzing the query sentence. Because each filtering condition and each connection condition may correspond to a plurality of base tables, in order to accurately obtain the preset condition that each base table needs to participate in the data column of the combined index suggestion, in this embodiment, each base table is used as a reference to obtain the connection condition and the filtering condition related to each base table in the query statement, so as to obtain the preset condition that each base table needs to participate in the data column of the combined index suggestion.
In the step S22, the query condition related to each base table may be used as a corresponding preset condition, or the query condition related to each base table may be preprocessed, for example, the query condition related to each base table may be filtered, so as to obtain a corresponding preset condition.
Through the setting mode of the embodiment, the query conditions related to each base table in the data query process can be acquired first, and then the corresponding preset conditions are obtained according to the query conditions related to each base table, so that the accuracy and the integrity of the data columns of each obtained base table which need to participate in the combined index are improved, and the reliability of acquiring the combined index suggestion is improved.
In one embodiment of the present invention, the method for obtaining the preset condition of each base table to which the data column of the combined index needs to participate according to the query condition related to each base table in step S22 is shown in fig. 3, and includes the following steps:
step S221: judging the query conditions of each base table according to preset judgment conditions to obtain index supported query conditions and index unsupported query conditions; step S222: deleting the query conditions which are not supported by the index, and taking the query conditions supported by the index as corresponding preset conditions. In the step S221, a preset judgment condition that needs to be satisfied by the query condition during the index support may be set according to the actual situation of the database, and then whether the query condition of each base table satisfies the preset judgment condition is judged; if the query condition of one of the base tables meets the preset judgment condition, judging that the index supports the query condition, namely, the query condition is the query condition supported by the index; if the query condition of one of the base tables does not meet the preset judging condition, judging that the index does not support the query condition, namely, the query condition is the query condition which is not supported by the index.
Or the preset judging conditions which are required to be met by the query conditions when the index is not supported can be set according to the actual condition of the database, and then whether the query conditions of all the base tables meet the preset judging conditions is judged; if the query condition of one of the base tables meets the preset judging condition, judging that the index does not support the query condition, namely, the query condition is the query condition which is not supported by the index; if the query condition of one of the base tables meets the preset judging condition, judging that the index does not support the query condition, namely, the query condition is the query condition supported by the index.
By the setting mode of the embodiment, query condition deletion which is not supported by the index can be judged, so that the problem that corresponding data cannot be acquired when the index is carried out according to the combined index suggestion is avoided, and the aim of improving the reliability of acquiring the combined index suggestion of each base table is fulfilled.
In an embodiment of the present invention, when the query condition of each base table is determined according to the preset condition in step S221, the preset condition includes:
both ends of the condition are related to the current base table, the condition contains unstable functions, and the operator of the condition does not support indexes.
If the query condition meets any one of the preset conditions, judging the query condition as the condition that the index is not supported.
In the above conditions, if both ends of the conditions are related to the current base table, the current base table cannot be queried; when the condition contains an unstable function, the accuracy and reliability of the query result cannot be ensured; when the conditional algorithm does not support indexing, the accurate combined index suggestion cannot be obtained. Therefore, in this embodiment, the above condition is used as a query condition for determining whether there is index unsupported, so that the query condition that is not supported by the index is accurately identified, so as to improve the correctness and reliability of the obtained combined index.
In one embodiment of the present invention, the step S2 of obtaining the selectivity of each base table corresponding to the preset condition includes:
and obtaining the selectivity of the corresponding preset conditions according to the statistical information of each base table and the statistical information.
Because the selectivity of the preset condition is that the number of rows of the data columns corresponding to the preset condition is a proportion of the total number of rows of the data columns in the corresponding base table, in this embodiment, an optimizer may be used to obtain statistical information of the base table, where the statistical information includes the total number of rows of the data columns of the base table and the number of rows of the data columns corresponding to each preset condition, and then calculate the selectivity of the preset condition to which each data column belongs according to the statistical information.
Since the selection rate of the preset condition to which each data column belongs is affected when data is stored or deleted in the base table, in this embodiment, the selection rate of the preset condition to which each data column belongs is acquired sequentially each time the combined index suggestion is acquired.
In one embodiment of the present invention, the method for obtaining the combined index suggestion with the highest query efficiency in step S3 according to the selectivity of the preset condition corresponding to each base table is shown in fig. 4, and includes the following steps:
step S31: according to the sequence of the preset condition from small to large, respectively arranging data columns which need to participate in the combined index suggestion in each base table;
step S32: and generating the combined index suggestion of each base table according to the ordering result of the data columns needing to participate in the combined index suggestion in each base table, wherein the combined index suggestion is the combined index suggestion with the highest query efficiency of the corresponding base table.
When generating the combined index suggestion, the filtering condition can be pushed to the corresponding base table to filter out a large number of tuples in advance, so that larger benefits are brought to the subsequent connection condition. Therefore, in the present embodiment, when the combined index advice of each base table is acquired, the filtering condition and the connection condition of each base table are individually processed in order as a set. Taking one base table as an example, each filtering condition related to the base table is taken as a combination, and then the connection condition related to the base table is taken as a combination, so that a combination index suggestion is given.
In this embodiment, the following SQL statement is taken as an example:
select * from T where a=10 and b=20 and c=30
through the SQL statement, the data columns of the available basic table T participating in the combined index suggestion include a data column a, a data column b and a data column c, wherein the query condition corresponding to the data column a is a filter condition a=10, the query condition corresponding to the data column b is a filter condition b=20, and the query condition corresponding to the data column c is a filter condition c=30.
The base table T is set to have a 10% selectivity for the filtering condition a=10, a 30% selectivity for the filtering condition b=20, a 50% selectivity for the filtering condition c=30, and the result of the ranking of the data columns in the base table T that need to participate in the combined index suggestions is (a, b, c) in the order of the smaller selectivity, and the ranking of the generated combined index suggestions is also (a, b, c).
Assuming that the data in the base table T is 10000 rows, when (a, b, c), the number of comparisons in the first column is 10000, and the number of rows of the data in the resulting set is 10000×10% =1000; the number of comparisons in the second column is 1000, and the number of lines of data in the resulting set is 1000×30% =300; the number of comparisons in the third column is 300, and the number of lines of data in the resulting set is 300×50% =150. Therefore, the total number of comparisons when the combined index is proposed as (a, b, c) is 10000+1000+300=11300 times.
When the combined index suggests other ordering methods, for example, (c, b, a), the number of comparisons in the first column is 10000, and the number of lines of the data in the resulting set is 10000×50% =5000; the number of comparisons in the second column is 5000, and the number of lines of data in the resulting set is 5000×30% =1500; the number of comparisons in the third column is 1500, and the number of lines of data in the resulting set is 1500×10% =150. Thus, the total number of comparisons at the combined index recommendation (c, b, a) is 10000+5000+1500=16500.
It is known from the comparison that, although the final results obtained by the index combination suggestion (a, b, c) and the index combination suggestion (c, b, a) are the same, the index combination suggestion (a, b, c) reduces 5200 times of comparison than the combination index suggestion (c, b, a), and improves the working efficiency of data query.
Through the setting mode of the embodiment, the data columns which need to participate in the combined index suggestions in each base table can be arranged according to the selection rate of the corresponding preset condition of each base table, so that the combined index suggestion with the highest query efficiency of each base table is quickly obtained, and the working efficiency of data query is improved.
In the above embodiment, the data columns that need to participate in the combined index suggestion in each base table are arranged according to the selectivity of the preset condition, so as to obtain the combined index suggestion with the highest corresponding query efficiency. In other embodiments, other ways of obtaining the most query-efficient combined index suggestions for each base table may be employed.
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 830 according to one embodiment of the invention; fig. 6 is a schematic diagram of a computer device 900 according to one embodiment of the invention. The machine-readable storage medium 830 has stored thereon a machine-executable program 840, which when executed by a processor, implements the database combined index recommendation processing method of any of the embodiments described above.
The computer device 900 may include a memory 920, a processor 910, and a machine executable program 840 stored on the memory 920 and running on the processor 910, and the processor 910 implements the database combined index suggestion processing method of any of the embodiments described above when executing the machine executable program 840.
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 the embodiment, a machine-readable storage medium 830 can be any means 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 medium 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 invention 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 900 may be, for example, a server, a desktop computer, a notebook computer, a tablet computer, or a smartphone. In some examples, computer device 900 may be a cloud computing node. Computer device 900 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 900 may be implemented in a distributed cloud computing environment where remote processing devices coupled via a communications network perform tasks. 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 900 may include a processor 910 adapted to execute stored instructions, a memory 920 providing temporary storage for the operation of the instructions during operation. Processor 910 may be a single core processor, a multi-core processor, a computing cluster, or any number of other configurations. Memory 920 may include Random Access Memory (RAM), read only memory, flash memory, or any other suitable storage system.
Processor 910 may be connected by a system interconnect (e.g., PCI-Express, etc.) to an I/O interface (input/output interface) adapted to connect computer device 900 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 900 or may be a device externally connected to the computing device.
The processor 910 may also be linked by a system interconnect to a display interface suitable for connecting the computer device 900 to a display device. The display device may include a display screen as a built-in component of the computer device 900. The display device may also include a computer monitor, television, projector, or the like, that is externally connected to the computer device 900. Further, a network interface controller (network interface controller, NIC) may be adapted to connect the computer device 900 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.
By now it should be appreciated by those skilled in the art that while a number of exemplary embodiments of the invention have been shown and described herein in detail, many other variations or modifications of the invention consistent with the principles of the invention may be directly ascertained or inferred from the present disclosure without departing from the spirit and scope of the invention. Accordingly, the scope of the present invention should be understood and deemed to cover all such other variations or modifications.
Claims (10)
1. A database combined index suggestion processing method is characterized in that,
in a preset stage of data query, obtaining preset conditions of data columns participating in combined index suggestions in each base table;
obtaining the selectivity of each preset condition; and
and obtaining the combined index suggestion with highest query efficiency according to the selection rate and the data column.
2. The method of claim 1, wherein,
the preset stage is a query optimization stage.
3. The method of claim 1, wherein,
the obtaining the preset condition of the data column participating in the combined index suggestion in each base table comprises the following steps:
and acquiring the query conditions related to each base table from the query statement, and obtaining the preset conditions according to the query conditions.
4. The method for processing database combined index suggestion according to claim 3,
the obtaining the preset condition according to the query condition includes:
deleting the query conditions which are not supported by the index, and taking the query conditions supported by the index as the preset conditions.
5. The method of claim 4, wherein,
the deleting the query condition which is not supported by the index comprises the following steps:
if both ends of the query condition are related to the current table, contain unstable functions or operators and do not support indexes, the query condition is the condition that the indexes do not support.
6. The method of claim 1, wherein,
the obtaining the selectivity of each preset condition comprises the following steps:
and obtaining the statistical information of each base table, and calculating the selectivity according to each statistical information.
7. The method of claim 1, wherein,
the combined index suggestion with highest query efficiency is obtained according to the selection rate and the data column, and comprises the following steps:
and arranging corresponding data columns according to the sequence from the small selection rate to the large selection rate, and obtaining the combined index suggestion according to the ascending arrangement result.
8. The method of claim 1, wherein,
the preset condition is a filtering condition or a connecting condition.
9. A machine-readable storage medium having stored thereon a machine-executable program which, when executed by a processor, implements the database combinatorial index recommendation processing method of any one 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, when executing the machine executable program, implements the database combined index recommendation processing method according to any one of claims 1 to 8.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202311329424.8A CN117093611B (en) | 2023-10-16 | 2023-10-16 | Database combined index suggestion processing method, storage medium and computer device |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202311329424.8A CN117093611B (en) | 2023-10-16 | 2023-10-16 | Database combined index suggestion processing method, storage medium and computer device |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN117093611A true CN117093611A (en) | 2023-11-21 |
| CN117093611B CN117093611B (en) | 2024-03-19 |
Family
ID=88770305
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202311329424.8A Active CN117093611B (en) | 2023-10-16 | 2023-10-16 | Database combined index suggestion processing method, storage medium and computer device |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN117093611B (en) |
Citations (13)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20050165741A1 (en) * | 2003-12-24 | 2005-07-28 | Gordon Mark R. | System and method for addressing inefficient query processing |
| US20070073761A1 (en) * | 2005-09-29 | 2007-03-29 | International Business Machines Corporation | Continual generation of index advice |
| CN105740451A (en) * | 2016-02-03 | 2016-07-06 | 贵州大学 | Reference test based multimedia indexing method |
| WO2017067117A1 (en) * | 2015-10-21 | 2017-04-27 | 华为技术有限公司 | Data query method and device |
| CN108170775A (en) * | 2017-12-26 | 2018-06-15 | 上海新炬网络技术有限公司 | A kind of database SQL indexes dynamic optimization method |
| US20180307715A1 (en) * | 2017-04-20 | 2018-10-25 | Servicenow, Inc. | Index suggestion engine for relational databases |
| CN110909014A (en) * | 2018-09-14 | 2020-03-24 | 阿里巴巴集团控股有限公司 | Optimization suggestion generation and database query method, device, equipment and storage medium |
| CN112162983A (en) * | 2020-09-22 | 2021-01-01 | 北京人大金仓信息技术股份有限公司 | Database index suggestion processing method, device, medium and electronic equipment |
| CN114968972A (en) * | 2021-02-26 | 2022-08-30 | 腾讯科技（深圳）有限公司 | Index optimization method, device, equipment and computer readable storage medium |
| CN115048409A (en) * | 2022-06-17 | 2022-09-13 | 北京人大金仓信息技术股份有限公司 | Execution method of database connection operation, storage medium and computer device |
| CN116595044A (en) * | 2023-05-30 | 2023-08-15 | 北京人大金仓信息技术股份有限公司 | Optimization method, storage medium and equipment for database selectivity calculation |
| CN116719843A (en) * | 2023-05-31 | 2023-09-08 | 北京人大金仓信息技术股份有限公司 | Query method, storage medium and device for database system |
| CN116861373A (en) * | 2023-07-17 | 2023-10-10 | 北京傲韦科技有限公司 | Query selectivity estimation method, system, terminal equipment and storage medium |
-
2023
- 2023-10-16 CN CN202311329424.8A patent/CN117093611B/en active Active
Patent Citations (13)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20050165741A1 (en) * | 2003-12-24 | 2005-07-28 | Gordon Mark R. | System and method for addressing inefficient query processing |
| US20070073761A1 (en) * | 2005-09-29 | 2007-03-29 | International Business Machines Corporation | Continual generation of index advice |
| WO2017067117A1 (en) * | 2015-10-21 | 2017-04-27 | 华为技术有限公司 | Data query method and device |
| CN105740451A (en) * | 2016-02-03 | 2016-07-06 | 贵州大学 | Reference test based multimedia indexing method |
| US20180307715A1 (en) * | 2017-04-20 | 2018-10-25 | Servicenow, Inc. | Index suggestion engine for relational databases |
| CN108170775A (en) * | 2017-12-26 | 2018-06-15 | 上海新炬网络技术有限公司 | A kind of database SQL indexes dynamic optimization method |
| CN110909014A (en) * | 2018-09-14 | 2020-03-24 | 阿里巴巴集团控股有限公司 | Optimization suggestion generation and database query method, device, equipment and storage medium |
| CN112162983A (en) * | 2020-09-22 | 2021-01-01 | 北京人大金仓信息技术股份有限公司 | Database index suggestion processing method, device, medium and electronic equipment |
| CN114968972A (en) * | 2021-02-26 | 2022-08-30 | 腾讯科技（深圳）有限公司 | Index optimization method, device, equipment and computer readable storage medium |
| CN115048409A (en) * | 2022-06-17 | 2022-09-13 | 北京人大金仓信息技术股份有限公司 | Execution method of database connection operation, storage medium and computer device |
| CN116595044A (en) * | 2023-05-30 | 2023-08-15 | 北京人大金仓信息技术股份有限公司 | Optimization method, storage medium and equipment for database selectivity calculation |
| CN116719843A (en) * | 2023-05-31 | 2023-09-08 | 北京人大金仓信息技术股份有限公司 | Query method, storage medium and device for database system |
| CN116861373A (en) * | 2023-07-17 | 2023-10-10 | 北京傲韦科技有限公司 | Query selectivity estimation method, system, terminal equipment and storage medium |
Non-Patent Citations (1)
| Title |
|---|
| 周晓云 等: "一种SQL负载裁剪新方法的研究", 中国矿业大学学报, vol. 35, no. 02, pages 269 - 273 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN117093611B (en) | 2024-03-19 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN110688393B (en) | Query statement optimization method and device, computer equipment and storage medium | |
| CN110222071B (en) | Data query method, device, server and storage medium | |
| US20080091647A1 (en) | Tool and a method for customizing hint | |
| CN106815353A (en) | A kind of method and apparatus of data query | |
| CN114090695A (en) | Query optimization method and device for distributed database | |
| CN111125199A (en) | Database access method and device and electronic equipment | |
| CN117093611B (en) | Database combined index suggestion processing method, storage medium and computer device | |
| CN117076491A (en) | Data processing method, storage medium and equipment | |
| CN116610697A (en) | Query method, storage medium and device for database query statement | |
| CN116595044A (en) | Optimization method, storage medium and equipment for database selectivity calculation | |
| CN116628136A (en) | Collaborative query processing method, system and electronic equipment based on declarative reasoning | |
| CN116467310A (en) | Lock-free marking method for invalid index, storage medium and computer equipment | |
| CN115934760A (en) | Database query statement optimization method, storage medium and computer device | |
| CN115391346A (en) | Database aggregation index generation method, storage medium and computer equipment | |
| CN115374121A (en) | Database index generation method, machine-readable storage medium and computer equipment | |
| CN115757479A (en) | Database query optimization method, machine-readable storage medium and computer device | |
| CN107273293B (en) | Big data system performance test method and device and electronic equipment | |
| CN115391424A (en) | Database query processing method, storage medium and computer equipment | |
| CN114969046A (en) | Hash connection processing method, storage medium and equipment | |
| CN114547083A (en) | Data processing method and device and electronic equipment | |
| KR20160047239A (en) | The column group selection method for storing datea efficiently in the mixed olap/oltp workload environment | |
| CN117762980A (en) | Query optimization method, storage medium and computer equipment | |
| CN115809268B (en) | Adaptive query method and device based on fragment index | |
| CN117149821B (en) | Query optimization method, storage medium and computer equipment | |
| CN117290355B (en) | Metadata map construction system |
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 | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |