CN115757479A - Database query optimization method, machine-readable storage medium and computer device - Google Patents
Database query optimization method, machine-readable storage medium and computer device Download PDFInfo
- Publication number
- CN115757479A CN115757479A CN202211413619.6A CN202211413619A CN115757479A CN 115757479 A CN115757479 A CN 115757479A CN 202211413619 A CN202211413619 A CN 202211413619A CN 115757479 A CN115757479 A CN 115757479A
- Authority
- CN
- China
- Prior art keywords
- query
- statistical information
- statistical
- information table
- statement
- 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
Links
- 238000000034 method Methods 0.000 title claims abstract description 59
- 238000005457 optimization Methods 0.000 title claims abstract description 50
- 238000004458 analytical method Methods 0.000 claims abstract description 7
- 238000005070 sampling Methods 0.000 claims description 17
- 238000004364 calculation method Methods 0.000 claims description 15
- 230000006870 function Effects 0.000 claims description 8
- 238000012216 screening Methods 0.000 claims description 8
- 230000003068 static effect Effects 0.000 description 8
- 238000010586 diagram Methods 0.000 description 6
- 238000012545 processing Methods 0.000 description 3
- 238000005516 engineering process Methods 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
- 238000012423 maintenance 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
Images
Classifications
-
- 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
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention provides a query optimization method of a database, a storage medium and computer equipment. Wherein the method comprises the following steps: acquiring a query statement; performing syntactic and semantic analysis on the query statement to obtain a tree structure formed by query atomic operations of the query statement; traversing the tree structure, and determining a query operation identifier of each query atomic operation; inquiring corresponding statistical information in a pre-generated statistical information table according to the inquiry operation identifier; performing cost estimation by using the statistical information; and selecting an execution plan according to the result of the cost estimation. The scheme of the invention determines the statistical information by using atomic operation as a unit. Because the optimization efficiency is greatly improved.
Description
Technical Field
The present invention relates to database query optimization technologies, and in particular, to a database query optimization method, a machine-readable storage medium, and a computer device.
Background
The execution of the data query statement needs to pass through stages of an analyzer, an optimizer and an executor in sequence, and finally a query result can be returned. At present, in an existing relational database system, an execution cost is generally calculated according to statistical information at an optimizer stage, so that an optimal execution plan is selected.
The generation technology of the statistical information is divided into static sampling and dynamic sampling. Static sampling such as histogram, MCV (Most Common Value), etc., and data distribution on each column of the data table are collected. Due to a series of factors such as complexity of user data, errors of sampled data and incomplete calculation method, an existing database system cannot guarantee that an optimal execution plan can be generated 100% according to static sampled values. Dynamic sampling is statistical information required for acquiring query statements (such as SQL) in the execution process, and the acquired information is more accurate than static sampling, but the execution efficiency of the query statements is influenced due to extra sampling work.
In a real use environment, several or several types of SQL statements are slow to execute, so that the execution efficiency of the whole system is influenced. At this time, a Database Administrator (DBA) is required to specify efficient plans on site empirically or forcibly by Hint, which obviously requires a great deal of labor and time cost.
Disclosure of Invention
One object of the present invention is to solve the problem of low efficiency of existing database optimization for specific query statements.
A further object of the present invention is to address the problem that existing databases may not optimally execute a particular query statement selection.
It is a further object of this invention to improve the efficiency of database execution for specific query statements.
In particular, the present invention provides a query optimization method for a database, which comprises:
acquiring a query statement;
performing syntax semantic analysis on the query statement to obtain a tree structure formed by query atomic operations of the query statement;
traversing the tree structure, and determining a query operation identifier of each query atomic operation;
inquiring corresponding statistical information in a pre-generated statistical information table according to the inquiry operation identifier;
performing cost estimation by using the statistical information;
and selecting an execution plan according to the result of the cost estimation.
Optionally, before the step of obtaining the query statement, the method further includes:
acquiring a statistical information table creating statement, wherein the statistical information table creating statement comprises a specified optimization target statement;
creating a statement and creating a statistical information table according to the statistical information table;
decomposing the optimization target statement into a plurality of statistical atomic operations;
generating a statistical operation identifier according to each statistical atomic operation and collecting statistical information;
and storing the statistical operation identification and the statistical information of each statistical atomic operation in a statistical information table.
Optionally, the step of generating a statistical operation identifier according to each statistical atomic operation includes:
screening out constants contained in the statistical atomic operation, and converting the statistical atomic operation with the constants screened out into character strings;
and carrying out hash calculation on the character string by using a hash function, and taking the obtained hash value as a statistical operation identifier.
Optionally, the step of determining the statistical operation identifier of each query atomic operation comprises:
and screening out constants contained in the query atomic operation, converting the query atomic operation with the constants screened out into character strings, and performing hash calculation on the character strings by using a hash function to obtain hash values serving as query operation identifiers.
Optionally, the step of querying the corresponding statistical information in the pre-generated statistical information table according to the query operation identifier includes:
inquiring whether a statistical operation identifier consistent with the inquiry operation identifier exists in the statistical information table or not;
and if so, acquiring the statistical information of the statistical operation identifier consistent with the query operation identifier in the statistical information table.
Optionally, after the step of querying whether a statistical operation identifier consistent with the query operation identifier exists in the statistical information table, the method further includes:
and if the statistical operation identifier consistent with the query operation identifier does not exist in the statistical information table, performing cost estimation by using an initial cost estimation algorithm.
Optionally, the step of collecting statistical information according to each statistical atomic operation includes:
carrying out data sampling on a table related to an optimized target statement to obtain a sampling value;
and carrying out query statistics and/or cost statistics on the sampling values according to the statistical atomic operation to obtain statistical information.
Optionally, the statistics information table creation statement further includes a statistics information name; and is
After the step of creating the statistics table according to the statistics table creation statement, the method further comprises the following steps:
the statistical information name is stored in the statistical information table.
According to another aspect of the present invention, 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 query optimization of a database as any one of the above.
According to yet another aspect of the present invention, there is also provided 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 method for query optimization of a database of any of the above.
The query optimization method of the database obtains the atomic operation contained in the query statement through syntactic and semantic analysis, obtains the statistical information of the atomic operation through querying the query statistical information table, changes the mode that the existing database stores the statistical information by taking a column as a unit, and determines the statistical information by taking the atomic operation as a unit. In the optimization process, the cost estimation operation directly uses statistical information obtained in advance by atomic operation, so that the efficiency is greatly improved.
Further, the query optimization method for the database of the present invention creates the statistical information table according to the statements created according to the statistical information table, and optimizes the specified optimization target statements, so that the flexibility is high, and the query statements with low execution efficiency can be selectively optimized. The cardinality estimation and the cost estimation of each atomic operation are more accurate, and a foundation is provided for the database optimizer to select the optimal execution plan.
The above and other objects, advantages and features of the present invention will become more apparent to those skilled in the art from the following detailed description of specific embodiments thereof taken 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 in the drawings identify the same or similar elements or components. Those skilled in the art will appreciate that the drawings are not necessarily drawn to scale. In the drawings:
FIG. 1 is a schematic diagram of a method of query optimization of a database according to one embodiment of the invention;
FIG. 2 is a flow chart illustrating a process of generating statistical information in a query optimization method for a database according to an embodiment of the present invention;
FIG. 3 is a flow chart illustrating a process of using statistical information in a query optimization method for a database according to an embodiment of the present invention;
FIG. 4 is a schematic diagram of a machine-readable storage medium according to one embodiment of the invention; and
FIG. 5 is a schematic diagram of a computer device according to one embodiment of the invention.
Detailed Description
FIG. 1 is a schematic diagram of a query optimization method for a database according to one embodiment of the invention. The query optimization method for the database can generally include:
step S102, obtaining a query statement; the query statement is generally an SQL statement, and the method of this embodiment may optimize for SQL that is not efficient in execution.
And step S104, performing syntactic and semantic analysis on the query statement to obtain a tree structure formed by query atomic operations of the query statement. The atomic operation may be a single operation that cannot be interrupted during the execution of the statement, and in general, the query statement targeted by the method of the present embodiment includes a plurality of atomic operations.
And step S106, traversing the tree structure and determining the query operation identifier of each query atomic operation. An implementation of determining a statistical operation identification for a query atomic operation may include: and screening out constants contained in the query atomic operation, converting the query atomic operation with the constants screened out into character strings, and performing hash calculation on the character strings by using a hash function to obtain hash values serving as query operation identifiers.
And step S108, inquiring corresponding statistical information in a statistical information table generated in advance according to the inquiry operation identifier. For example, whether a statistical operation identifier consistent with the query operation identifier exists in the statistical information table can be queried; if yes, obtaining the statistical information of the statistical operation identification consistent with the query operation identification in the statistical information table. If the statistical operation identifier consistent with the query operation identifier does not exist in the statistical information table, an initial cost estimation algorithm may be used to perform cost estimation, for example, a static sampling manner such as histogram, MCV, or other dynamic sampling manner is used to perform cost estimation.
The statistical information table is created in advance according to the statistical information table creation statements, and attributes recorded by each tuple comprise atomic operation identifiers, statistical information names and statistical information contents. The statistical information table can obtain more accurate statistical information through the advance data acquisition and cost estimation.
Step S110, cost estimation is performed using the statistical information.
Step S112, selecting an execution plan according to the result of the cost estimation, and generally selecting an execution plan with the smallest execution plan to select an optimal execution plan, thereby achieving the purpose of SQL tuning.
In the query optimization method for the database according to the embodiment, the atomic operations included in the query statement are obtained by parsing the syntax semantics, and the statistical information of the atomic operations is obtained by querying the query statistical information table, so that the way of storing the statistical information in the existing database by taking the column as the unit is changed, and the statistical information is determined by taking the atomic operation as the unit. In the optimization process, the cost estimation operation directly uses statistical information obtained in advance by atomic operation, so that the efficiency is greatly improved.
The query optimization method for the database according to the embodiment creates the statistical information table according to the statements created according to the statistical information table, and optimizes the specified optimization target statements. The cardinality estimation and the cost estimation of each atomic operation are more accurate, and a foundation is provided for the database optimizer to select the optimal execution plan.
Before the step S102 of obtaining the query statement, the process of creating the statistical information table may include: acquiring a statistical information table creating statement, wherein the statistical information table creating statement comprises a specified optimization target statement; creating a statement and creating a statistical information table according to the statistical information table; decomposing the optimization target statement into a plurality of statistical atomic operations; generating a statistical operation identifier according to each statistical atomic operation and collecting statistical information; and storing the statistical operation identification and the statistical information of each statistical atomic operation in a statistical information table.
In the above step, the step of generating the statistical operation identifier according to each statistical atomic operation includes: screening out constants contained in the statistical atomic operation, and converting the statistical atomic operation with the constants screened out into character strings; and carrying out hash calculation on the character string by using a hash function, and taking the obtained hash value as a statistical operation identifier. The statistical operation identifier is generated by using a character string converted by the statistical atomic operation after the constant is screened out, and corresponds to the atomic operation per se. By using the hash value as the identifier, the efficiency of inquiring the statistical information table can be improved.
Accordingly, the step of determining the statistical operation identifier of each query atomic operation in step S106 may include: and screening out constants contained in the query atomic operation, converting the query atomic operation with the constants screened out into character strings, and performing hash calculation on the character strings by using a hash function to obtain hash values serving as query operation identifiers. The step of querying the corresponding statistical information in the pre-generated statistical information table according to the query operation identifier may be: inquiring whether a statistical operation identifier consistent with the inquiry operation identifier exists in the statistical information table or not; and if so, acquiring the statistical information of the statistical operation identifier consistent with the query operation identifier in the statistical information table. And if the statistical information table does not have the statistical operation identifier consistent with the query operation identifier, performing cost estimation by using an initial cost estimation algorithm.
The step of collecting statistical information according to each statistical atomic operation may include: carrying out data sampling on a table related to the optimization target statement to obtain a sampling value; and carrying out query statistics and/or cost statistics on the sampling values according to the statistical atomic operation to obtain statistical information. The statistical information may be a histogram or an MCV map, etc. In addition to counting the base value, namely the number of result lines returned by one atomic operation, the cost value can be further collected, and the cost calculation factor is corrected by recording the atomic operation cost of the sampled data.
The statistical information table creating statement can also comprise a statistical information name; and after the step of creating a statistics table according to the statistics table creation statement, further comprising: the statistical information name is stored in the statistical information table. The statistical information name can facilitate subsequent maintenance and data reading by a user.
The query optimization method for a database according to the present embodiment can be roughly divided into a process of generating statistical information (a process of creating a statistical information table) and a process of using statistical information (an optimization process of a query statement).
Fig. 2 is a flowchart illustrating a generation process of statistical information in a query optimization method for a database according to an embodiment of the present invention. The generation process of the statistical information may include the steps of:
in step S202, a statistics table creation statement is obtained, and this embodiment may define a create spl statistical command for inputting a request for creating a statistics table.
For the SQL statement that the generated execution plan is not efficient, if it is desired to automatically optimize the SQL statement, the embodiment may generate specific statistical information for the SQL statement by the following statistical information table creation statement:
Create SQL Statistic<stat_name>as<SQL>
in the statement for creating the statistical information table, the create SQL static command is used for defining the type of the created statement, the stat _ name is the name of the statistical information, the user can conveniently search the created statistical information through the stat _ name, and the SQL is the specified optimization target statement. The database system creates a system table for storing this type of statistical information. The format may be as shown in table 1.
TABLE 1
The optimization objective statement may be any SELECT query statement.
The following description will be given by taking, as an example, a query sentence consisting of SELECT from t1, t2 where t1.Id = t2.Id and t1.Score <20and t1.Id <20.
Step S204, decomposing the optimization target statement into a plurality of statistical atomic operations. After receiving a Create SQL static request, the database firstly carries out syntactic semantic analysis on the SELECT SQL in the request to generate a tree structure consisting of atomic operations. The tree is then traversed and each node is stored as an atomic operation in a linked list. The SQL for the example above can be decomposed into the following two atomic operations:
atomic operation 1: scan t1 (score <20and id <20);
atomic operation 2: t1 join T2 (T1. Id = T2. Id).
Step S206, the constants contained in the statistical atomic operation are screened out, and the statistical operation identifier is calculated correspondingly.
The list in atomic operation 1 and atomic operation 2 is usually removed, and the list is converted into a character string and then subjected to a hash operation (for example, MD5 operation) to obtain the operatorId.
Hash operation result of atomic operation 1: md5 value1: scan t1 (score <;
hash operation result of atomic operation 2: md5 value2: t1 join t2 (t 1.Id = t2. Id)
Step S208, determining statistical information according to the condition of each atomic operation, and storing the statistical information in a statistical information table.
The tables involved in SQL are sampled. For the above-mentioned example SQL, a certain amount (e.g. 10000 pieces, the specific amount is determined by the size of the table and the processing capacity of the database) of data is collected in the tables t1 and t2 respectively. Then, the two involved atomic operations 1 and 2 are respectively subjected to statistical information calculation so as to perform cardinality estimation.
When collecting the statistical information, the type of the atomic operation, such as scan, join, group, etc., may be first determined, and then data may be obtained from the sampled values for calculation according to the variable parameters of the operation.
For example, for Scan t1 (score <. For t1 join t2 (t 1.Id = t2. Id), statistics of columns (t 1.Id, t2. Id) in a multi-table are obtained. The statistical information may be a histogram or an MCV map, etc.
In addition to the statistical base value, namely the number of lines of results returned by an atomic operation, the statistical information can also collect a cost value, and the cost calculation factor is corrected by recording the atomic operation cost of the sampled data.
The statistical information is stored in a formatted form (such as JSON or XML) in a stat _ values single column of the sql _ statistical table, and can also be stored in a multi-column form.
The SQL processing of the database is divided into several phases: the parser parses SQL semantically to obtain an internal data structure tree structure Query, the optimizer transforms the Query to generate a Query with better execution efficiency, then cardinal number estimation and cost operation are carried out by using statistical information to calculate an optimal execution plan of each operation, and finally the execution plan obtains a result. In using statistical information for radix estimation and cost operations, the optimizer has in fact turned each operation into an atomic operation. Fig. 3 is a flowchart illustrating a process of using statistical information in a query optimization method for a database according to an embodiment of the present invention. The usage of the statistical information may comprise the steps of:
step S302, obtaining a query statement SQL, and carrying out syntax semantic analysis on the SQL to obtain a tree structure formed by query atomic operations of the query statement;
step S304, calculating the query operation identifier operatoriD according to the query atomic operation. The calculation process of querying the operation identifier operatorId is the same as the calculation process of obtaining the operatorId by performing hash operation (for example, MD5 operation) on the character strings of the atomic operation 1 and the atomic operation 2, and is not described herein again.
Step S306, inquiring in the statistical information table sql static according to the inquiry operation identifier operatoriD.
And step S308, under the condition of inquiring the result, carrying out radix estimation and cost estimation according to the constant parameters and the statistical information of the atomic operation. After the statistical information read in the stat _ values column, radix estimation and cost calculation are performed using the statistical information. For example, for Scan t1 (score <.
And step S310, under the condition that a result cannot be inquired, performing cardinality estimation and cost estimation according to an initial cost estimation algorithm of the database system.
The embodiment enables cardinality estimation and cost estimation of each atomic operation in SQL to be more accurate, and therefore the database optimizer can select an optimal execution plan. Thereby achieving the purpose of automatic SQL tuning.
The embodiment also provides a machine-readable storage medium and a computer device. Fig. 4 is a schematic diagram of a machine-readable storage medium 40 according to one embodiment of the invention, and fig. 5 is a schematic diagram of a computer device 50 according to one embodiment of the invention.
The machine-readable storage medium 40 has stored thereon a machine-executable program 41, the machine-executable program 41 when executed by a processor implementing the method of query optimization of a database of any of the embodiments described above.
Computer device 50 may include memory 520, processor 510, and machine executable program 41 stored on memory 520 and running on processor 510, and when processor 510 executes machine executable program 41, implements the query optimization method of the database of any of the embodiments described above.
It should be noted that the logic and/or steps represented in the flowcharts or otherwise described herein, such as an ordered listing of executable instructions that can be considered to implement 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, a machine-readable storage medium 40 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 machine-readable storage medium 40 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 40 may even be paper or another suitable medium upon which the program is printed, as the program can 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 should be understood that portions of the present invention may be implemented in hardware, software, firmware, or a combination thereof. In the above embodiments, 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 50 may be, for example, a server, a desktop computer, a notebook computer, a tablet computer, or a smartphone. In some examples, computer device 50 may be a cloud computing node. Computer device 50 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 50 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 computer system storage media including memory storage devices.
The computer device 50 may include a processor 510 adapted to execute stored instructions, a memory 520 providing temporary storage for the operation of the instructions during operation. Processor 510 may be a single core processor, a multi-core processor, a computing cluster, or any number of other configurations. Memory 520 may include Random Access Memory (RAM), read only memory, flash memory, or any other suitable storage system.
Processor 510 may be connected by a system interconnect (e.g., PCI-Express, etc.) to an I/O interface (input/output interface) suitable for connecting computer device 50 to one or more I/O devices (input/output devices). The I/O devices may include, for example, a keyboard and a pointing device, wherein the pointing device may include a touchpad or a touchscreen, among others. The I/O devices may be built-in components of the computing device 50 or may be devices that are externally connected to the computing device.
The processor 510 may also be linked through a system interconnect to a display interface suitable for connecting the computer device 50 to a display device. The display device may include a display screen as a built-in component of the computer device 50. The display device may also include a computer monitor, television, or projector, etc. externally connected to the computer device 50. In addition, a Network Interface Controller (NIC) may be adapted to connect computer device 50 to a network via 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), the internet, or the like. The remote device may be connected to the computing device through a network.
The flowcharts provided by this embodiment are not intended to indicate that the operations of the method are to be performed in any particular order, or that all the operations of the method are included in each case. Further, the method may include additional operations. Additional variations on the above-described method are possible within the scope of the technical ideas provided by the method of this embodiment.
Thus, it should be appreciated by those skilled in the art that while a number of exemplary embodiments of the invention have been illustrated and described in detail herein, many other variations or modifications consistent with the principles of the invention may be directly determined or derived from the disclosure of the present invention without departing from the spirit and scope of the invention. Accordingly, the scope of the invention should be understood and interpreted to cover all such other variations or modifications.
Claims (10)
1. A query optimization method for a database comprises the following steps:
acquiring a query statement;
performing syntax semantic analysis on the query statement to obtain a tree structure formed by query atomic operations of the query statement;
traversing the tree structure, and determining a query operation identifier of each query atomic operation;
inquiring corresponding statistical information in a pre-generated statistical information table according to the inquiry operation identifier;
performing cost estimation using the statistical information;
and selecting an execution plan according to the result of the cost estimation.
2. The query optimization method of database according to claim 1, wherein said step of obtaining query statements further comprises:
acquiring a statistical information table creating statement, wherein the statistical information table creating statement comprises a specified optimization target statement;
creating a statement according to the statistical information table to create a statistical information table;
decomposing the optimization target statement into a plurality of statistical atomic operations;
generating a statistical operation identifier according to each statistical atomic operation and collecting statistical information;
and storing the statistical operation identification and the statistical information of each statistical atomic operation in the statistical information table.
3. The query optimization method of database according to claim 2, wherein said step of generating a statistical operation identifier from each of said statistical atomic operations comprises:
screening out constants contained in the statistical atomic operation, and converting the statistical atomic operation after the constants are screened out into character strings;
and carrying out hash calculation on the character string by using a hash function, and taking the obtained hash value as the statistical operation identifier.
4. The query optimization method of the database according to claim 3, wherein the step of determining the statistical operation identification of each of the query atomic operations comprises:
and screening out constants contained in the query atomic operation, converting the query atomic operation subjected to the screening out of the constants into character strings, and performing hash calculation on the character strings by using the hash function to obtain hash values serving as the query operation identifiers.
5. The query optimization method of the database according to claim 4, wherein the step of querying the corresponding statistical information in the pre-generated statistical information table according to the query operation identifier comprises:
inquiring whether a statistical operation identifier consistent with the inquiry operation identifier exists in the statistical information table;
and if so, acquiring the statistical information of the statistical operation identifier consistent with the query operation identifier in the statistical information table.
6. The query optimization method of database according to claim 5, further comprising, after the step of querying whether there is a statistical operation identifier consistent with the query operation identifier in the statistical information table:
and if the statistical information table does not have the statistical operation identifier consistent with the query operation identifier, performing cost estimation by using an initial cost estimation algorithm.
7. The query optimization method of the database according to claim 2, wherein the step of collecting statistical information according to each of the statistical atomic operations comprises:
performing data sampling on the table related to the optimization target statement to obtain a sampling value;
and carrying out query statistics and/or cost statistics on the sampling value according to the statistical atomic operation to obtain the statistical information.
8. The query optimization method of a database according to claim 2,
the statistical information table creating statement also comprises a statistical information name; and is
After the step of creating a statement according to the statistical information table, the method further comprises:
and storing the statistical information name in the statistical information table.
9. A machine readable storage medium having stored thereon a machine executable program which when executed by a processor implements a method of query optimization of a database according to 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 run on the processor, and the processor when executing the machine executable program implements a method of query optimization of a database according to any one of claims 1 to 8.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211413619.6A CN115757479A (en) | 2022-11-11 | 2022-11-11 | Database query optimization method, machine-readable storage medium and computer device |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211413619.6A CN115757479A (en) | 2022-11-11 | 2022-11-11 | Database query optimization method, machine-readable storage medium and computer device |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN115757479A true CN115757479A (en) | 2023-03-07 |
Family
ID=85369753
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202211413619.6A Pending CN115757479A (en) | 2022-11-11 | 2022-11-11 | Database query optimization method, machine-readable storage medium and computer device |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN115757479A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116561374A (en) * | 2023-07-11 | 2023-08-08 | 腾讯科技（深圳）有限公司 | Resource determination method, device, equipment and medium based on semi-structured storage |
-
2022
- 2022-11-11 CN CN202211413619.6A patent/CN115757479A/en active Pending
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116561374A (en) * | 2023-07-11 | 2023-08-08 | 腾讯科技（深圳）有限公司 | Resource determination method, device, equipment and medium based on semi-structured storage |
| CN116561374B (en) * | 2023-07-11 | 2024-02-23 | 腾讯科技（深圳）有限公司 | Resource determination method, device, equipment and medium based on semi-structured storage |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN111522816B (en) | Data processing method, device, terminal and medium based on database engine | |
| CN110795455B (en) | Dependency analysis method, electronic device, computer apparatus, and readable storage medium | |
| US20150310129A1 (en) | Method of managing database, management computer and storage medium | |
| CN102073640A (en) | Method, system and server for testing structured query language (SQL) statements | |
| CN112579610A (en) | Multi-data source structure analysis method, system, terminal device and storage medium | |
| CN113515564B (en) | J2 EE-based data access method, device, equipment and storage medium | |
| CN118132587A (en) | Data analysis method, system, electronic equipment and storage medium based on natural language | |
| CN111125199B (en) | Database access method and device and electronic equipment | |
| CN114253995B (en) | Data tracing method, device, equipment and computer readable storage medium | |
| CN110580170B (en) | Method and device for identifying software performance risk | |
| CN115757479A (en) | Database query optimization method, machine-readable storage medium and computer device | |
| CN117931893A (en) | Data retrieval method and system based on distributed search engine | |
| CN117112603A (en) | Data blood margin determination method, device and medium based on structured query language | |
| CN115114325B (en) | Data query method and device, electronic equipment and storage medium | |
| CN117725058A (en) | Data blood-margin management system and data blood-margin management method | |
| CN117076491A (en) | Data processing method, storage medium and equipment | |
| CN116975116A (en) | Data condition screening method of big data analysis system | |
| CN116931951A (en) | SQL code checking method, medium, device and computing equipment | |
| CN113722337B (en) | Service data determination method, device, equipment and storage medium | |
| CN115374121A (en) | Database index generation method, machine-readable storage medium and computer equipment | |
| CN115757174A (en) | Database difference detection method and device | |
| CN115794826A (en) | Method and device for automatically integrating expression, generation and application of external prompt | |
| CN115391424A (en) | Database query processing method, storage medium and computer equipment | |
| CN115391346A (en) | Database aggregation index generation method, storage medium and computer equipment | |
| CN116955403B (en) | Method, device, computer equipment and medium for accelerating operation of data without mode |
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 | ||
| CB02 | Change of applicant information | ||
| CB02 | Change of applicant information |
Country or region after: China Address after: 100102 201, 2 / F, 101, No. 5 building, No. 7 Rongda Road, Chaoyang District, Beijing Applicant after: China Electronics Technology Group Jincang (Beijing) Technology Co.,Ltd. Address before: 100102 201, 2 / F, 101, No. 5 building, No. 7 Rongda Road, Chaoyang District, Beijing Applicant before: BEIJING KINGBASE INFORMATION TECHNOLOGIES Inc. Country or region before: China |