Database optimization processing method and device, electronic equipment and storage medium

Abstract

The present disclosure provides an optimization processing method of a database, and relates to the technical fields of database optimization, data storage and the like. The specific implementation scheme is as follows: a plurality of query planning information of querying a database within a preset time length before a specified time is counted; and based on the plurality of query planning information and metadata of the database, an optimization suggestion of the database at the specified time is generated. The present disclosure can automatically generate an optimization suggestion of the database, effectively ensure the accuracy of the generated optimization suggestion of the database, and thus effectively improve the optimization processing efficiency of the database.

Description

Technical Field

[0001] This disclosure relates to the field of computer technology, specifically to the fields of database optimization and data storage, and particularly to a database optimization processing method, apparatus, electronic device, and storage medium. Background Technology

[0002] Databases, as a massive data storage structure, are widely used in data-driven business access and querying.

[0003] In database usage, query performance is a crucial indicator of database efficiency. When a database is created, its table structure is fixed. As business grows, the early structure may become inadequate to meet new business needs. Therefore, professional staff need to periodically analyze database query data to determine if optimization is necessary. If required, staff can manually modify the table structure to improve query performance. Summary of the Invention

[0004] This disclosure provides a method, apparatus, electronic device, and storage medium for optimizing database processing.

[0005] According to one aspect of this disclosure, a database optimization method is provided, comprising:

[0006] Statistically analyze multiple query plans for the database within a preset time period before a specified time.

[0007] Based on the multiple query planning information and the metadata of the database, optimization suggestions for the database at the specified time are generated.

[0008] According to another aspect of this disclosure, a database optimization processing apparatus is provided, comprising:

[0009] The statistics module is used to collect statistics on multiple query plans for the database within a preset time period before a specified time.

[0010] The suggestion generation module is used to generate optimization suggestions for the database at the specified time based on the multiple query planning information and the database metadata.

[0011] According to another aspect of this disclosure, an electronic device is provided, comprising:

[0012] At least one processor; and

[0013] A memory communicatively connected to the at least one processor; wherein,

[0014] The memory stores instructions that can be executed by the at least one processor to enable the at least one processor to perform the methods described above and any possible implementations.

[0015] According to another aspect of this disclosure, a non-transitory computer-readable storage medium is provided storing computer instructions for causing the computer to perform the methods described above and any possible implementation thereof.

[0016] According to another aspect of this disclosure, a computer program product is provided, comprising a computer program that, when executed by a processor, implements the aspects and any possible implementations described above.

[0017] According to the technology disclosed herein, database optimization suggestions can be automatically generated, and the accuracy of the generated database optimization suggestions can be effectively ensured, thereby effectively improving the efficiency of database optimization processing.

[0018] It should be understood that the description in this section is not intended to identify key or essential features of the embodiments of this disclosure, nor is it intended to limit the scope of this disclosure. Other features of this disclosure will become readily apparent from the following description. Attached Figure Description

[0019] The accompanying drawings are provided to better understand this solution and do not constitute a limitation of this disclosure. Wherein:

[0020] Figure 1 This is a schematic diagram based on the first embodiment of the present disclosure;

[0021] Figure 2 This is a schematic diagram according to the second embodiment of the present disclosure;

[0022] Figure 3 This is a schematic diagram according to the third embodiment of the present disclosure;

[0023] Figure 4 This is a schematic diagram according to the fourth embodiment of the present disclosure;

[0024] Figure 5 This is a block diagram of an electronic device used to implement the methods of the embodiments of this disclosure. Detailed Implementation

[0025] The exemplary embodiments of this disclosure are described below with reference to the accompanying drawings, including various details of the embodiments to aid understanding, and should be considered merely exemplary. Therefore, those skilled in the art will recognize that various changes and modifications can be made to the embodiments described herein without departing from the scope and spirit of this disclosure. Similarly, for clarity and brevity, descriptions of well-known functions and structures are omitted in the following description.

[0026] Obviously, the described embodiments are only some, not all, of the embodiments disclosed herein. All other embodiments obtained by those skilled in the art based on the embodiments of this disclosure without inventive effort are within the scope of protection of this disclosure.

[0027] It should be noted that the terminal devices involved in the embodiments of this disclosure may include, but are not limited to, smart devices such as mobile phones, personal digital assistants (PDAs), wireless handheld devices, and tablet computers; the display devices may include, but are not limited to, personal computers, televisions, and other devices with display functions.

[0028] Furthermore, the term "and / or" in this article is merely a description of the relationship between related objects, indicating that three relationships can exist. For example, A and / or B can represent: A existing alone, A and B existing simultaneously, or B existing alone. Additionally, the character " / " in this article generally indicates that the preceding and following related objects have an "or" relationship.

[0029] In existing technologies, database optimization involves professional staff periodically analyzing database queries to determine if optimization is needed. This process is time-consuming, labor-intensive, and has relatively poor accuracy.

[0030] Figure 1 This is a schematic diagram based on the first embodiment of the present disclosure; as shown Figure 1 As shown, this embodiment provides a database optimization method, which may specifically include the following steps:

[0031] S101. Statistically analyze multiple query plans for the database within a preset time period before a specified time.

[0032] S102. Based on multiple query planning information and database metadata, generate optimization suggestions for the database at a specified time.

[0033] The entity executing the database optimization processing method in this embodiment can be a database optimization processing device. This database optimization processing device can be located within the database or can be independent of the database, but it can access the database to perform database optimization processing.

[0034] In this embodiment, the query planning information for the database specifically refers to the query planning information applicable within the database, rather than external query requests. For example, it could be the final query planning information obtained by the database query system after receiving and optimizing an external query request. When used, the database query system queries the database based on this query planning information, retrieves the results corresponding to the query request, and returns them.

[0035] The preset time length before the specified time in this embodiment can be set according to needs. For example, the database can be optimized once a day or once a week, and the corresponding preset time length can be set to one day or one week. The specified time can be the current time or any time when the database needs to be optimized. Alternatively, the database optimization process can also be performed non-periodicly. For example, if the database query volume is large in a recent period, optimization suggestions for the database at the current time can be generated based on all query planning information within the preset time period before the current time. Subsequently, the database can be optimized based on the generated optimization suggestions for the current time.

[0036] In practical applications, when the preset time period is chosen reasonably, it can be assumed that the database access habits are generally the same within the preset time period before and after a specified time, and the frequency of access to data tables in the database is similar. For example, if table A in the database is frequently accessed within the preset time period before time t, it can be assumed that table A will still be frequently accessed for some time after time t. Therefore, based on multiple query plans and database metadata queried within the preset time period before time t1, optimization suggestions for the database at time t1 can be generated to assist in optimizing the database at time t1, thereby improving the database's query performance after time t1.

[0037] In this embodiment, the database metadata may include the database's structural information, such as the number of tables, column information in each table, and the order relationship between columns in the tables. It may also include table indexes. Furthermore, the database metadata may include the type of storage medium. If the database is divided into at least two partitions for storage, it may also include the type of storage medium corresponding to each partition, etc. The type of storage medium may include hard disk drives (HDDs) and solid-state drives (SSDs). HDDs are general-purpose mechanical hard drives, while SSDs have faster read and write speeds compared to HDDs.

[0038] In practice, the database optimization processing device can learn about the query situation and query performance within a preset time period based on multiple query planning information before a specified time, and combine the database metadata to generate optimization suggestions for the database at the specified time, so as to optimize the database structure and improve the database query performance.

[0039] The database optimization method in this embodiment generates optimization suggestions for the database at a specified time by statistically analyzing multiple query planning information of the database within a preset time period before a specified time. Based on the multiple query planning information and the database metadata, it generates optimization suggestions for the database at a specified time. Compared with the prior art, it can automatically generate database optimization suggestions without the need for manual time and effort to analyze query situations, and can effectively ensure the accuracy of the generated database optimization suggestions, thereby improving the optimization efficiency of the database.

[0040] Figure 2 This is a schematic diagram based on the second embodiment of the present disclosure; as shown Figure 2 As shown, the database optimization method of this embodiment, in the above... Figure 1 Based on the technical solutions of the illustrated embodiments, the technical solutions of this disclosure will be described in further detail. For example... Figure 2 As shown, the database optimization method in this embodiment may specifically include the following steps:

[0041] S201. Collect multiple query requests to the database within a preset time period before a specified time.

[0042] S202. Obtain multiple query plan information corresponding to multiple query requests. Each query plan information is obtained by the query optimizer based on the corresponding query request.

[0043] In other words, for each query request, the query optimizer can perform query planning based on that query request, obtaining a corresponding query plan. For multiple query requests, multiple query plan information can be obtained.

[0044] For example, the query planning information in this embodiment includes scanning information and filtering information. Scanning information can refer to the column information of the scanned database, including column identifiers in the tables of the scanned database. Filtering information can be considered as filtering conditions based on the scanning information; for example, filtering information can also include column identifiers in the tables of the database corresponding to the filtering conditions. Through the above scanning information and filtering information, the query planning information can be accurately represented.

[0045] In this embodiment, by following the steps described above, multiple query planning information for querying the database within a preset time period before a specified time can be obtained accurately and efficiently.

[0046] S203. Based on multiple query planning information and database metadata, a pre-trained suggestion generation model is used to generate optimization suggestions for the database at a specified time.

[0047] This suggestion generation model can employ a neural network model. Before use, multiple training samples are collected to train the suggestion generation model. For example, the training samples may include metadata of the training database, multiple training query planning information prior to the training time corresponding to the training database, and optimization suggestions for the training database corresponding to the labeled training time.

[0048] During training, the metadata of the training database from the training samples, along with multiple training query plans prior to the corresponding training time, are input into the suggestion generation model. This model analyzes the query plans from multiple queries, refers to the metadata of the training database, and predicts optimization suggestions for the training database at each training time. Then, based on the predicted optimization suggestions and the labeled optimization suggestions for the training database at each training time, a loss function is constructed, and gradient descent is used to adjust the model parameters. Using multiple training samples, the suggestion generation model is continuously trained in this manner until the loss function converges, the parameters of the suggestion generation model are determined, and thus the suggestion generation model is finalized.

[0049] Optionally, step S203 can also be replaced by the following implementation: based on multiple query planning information and database metadata, using pre-set optimization suggestion rules, generate optimization suggestions for the database at a specified time.

[0050] The optimization suggestion rules in this embodiment can adopt heuristic optimization suggestion rules, which can improve the accuracy of the generated optimization suggestions.

[0051] For example, heuristic optimization suggestion rules could specify that if the amount of data in a table scanned in multiple query plans is very large, such as exceeding a first preset data volume threshold, it could be suggested to move the table to a storage medium with faster read speeds. If the query plan filtering information requires filtering columns with large data volumes, the database optimization suggestions could include adding indexes to the filtered columns or adjusting the column order, etc.

[0052] Suppose there are three tables in the database: A, B, and C. Table A has two partitions: p1 and p2. Data in partition p1 is stored on an SSD, and data in partition p2 is stored on an HDD. Table B is stored on an HDD. Table C has three columns: c1, c2, and c3. Column c1 has an index (or the data is stored sorted by c1).

[0053] If we analyze query plans from the past week up to the current moment and determine that partition p1 in table A has been accessed 30 times and partition p2 in table A has been accessed 100,000 times, both exceeding a preset access threshold, and the metadata of the referenced database shows that the data for partition p2 is stored on an HDD, then the database optimization suggestion could be: It is recommended to store the data for partition p2 of table A on a faster storage medium such as an SSD. The preset access threshold can be set based on needs or experience; the specific value is not limited.

[0054] The example above uses the suggestion to change the storage medium type of a partition in a database table as an example of optimization recommendations. In practical applications, if the entire database is stored in an HDD, and the database has been accessed a very large number of times within a preset time period before the current moment, exceeding a preset access threshold, the generated optimization recommendations may include suggestions to change the storage medium type of the database.

[0055] Or, for example, if table B in the database has been accessed a very large number of times within a preset time period such as one week before the current moment, exceeding a preset access threshold, the generated optimization suggestions could include suggesting changing the storage medium of table B in the database to SSD to speed up the data reading speed in table B, etc.

[0056] If we statistically analyze multiple query plans from the past week up to the current time, and determine that column c1 of table C was accessed 100 times as a data filter condition and scanned 1 million times as a data result, while column C2 was accessed 1 million times as a data filter condition and scanned 100 times as a data result, then the generated optimization suggestions could include: removing the index on column c1, using column c2 as the sorting column, or creating an index on column c2.

[0057] Suppose we analyze multiple query plans from the past week prior to the current moment and determine that column c3 of table C was accessed more than 100,000 times as a data filter condition, exceeding a preset access threshold. Furthermore, analysis shows that changing the data storage distribution of column c3 can reduce the number of times it's used as a filter condition. The resulting optimization suggestion could include: adjusting the data storage distribution in column c3. For example, column c3 might store numerical data such as age or scores, and the data in column c3 might be stored chronologically or sorted according to other columns. If a large number of queries from the past week prior to the current moment retrieved data where the values ​​in column c3 exceeded the selected threshold, then we could suggest storing the data in column c3 of table C in numerical order. For example, it could be stored in descending order or ascending order. In this way, during filtering, the selected threshold in column c3 can be directly located, and data greater than or less than the threshold can be directly obtained based on that threshold. This reduces the scanning when filtering data in column c3, thereby improving the data query performance when column c3 is used as a data filtering condition after the current time.

[0058] Based on the above steps, it can be seen that the optimization suggestions for the generated database in this embodiment may include at least one of the following: suggestions for adjusting the column order of the database, suggestions for adding index tables, suggestions for adjusting the data storage distribution in the target columns, suggestions for changing the type of storage medium for storing the database, and suggestions for changing the type of storage medium for tables or partitions in the database. In practical applications, other optimization suggestions for adjusting the database structure may also be included, as long as the optimization suggestions can improve the database access performance; these will not be listed in detail here.

[0059] Specifically, generating suggestions for adjusting the column order in a database refers to suggestions for adjusting the order of columns in a specific table within the database. For example, query planning information is often deployed based on the order of columns. If the deployment is unreasonable, it may lead to inefficient scanning and filtering, resulting in poor query performance. Based on this, suggestions for adjusting the column order in a specific table within the database can be generated.

[0060] Regardless of which method is used, optimization suggestions for the database at a specified time can be generated accurately and efficiently.

[0061] Moreover, the optimization suggestions for the database generated by the above method at a specified time are very rich, comprehensive, and accurate, and can effectively support the optimization of database query performance.

[0062] S204. Display optimization suggestions for the database at a specified time.

[0063] S205. If it is determined that the database optimization suggestions include at least one of the following: suggestions to adjust the column order, suggestions to add index tables, and suggestions to adjust the data storage distribution in the target column, issue an inquiry message asking whether the database optimization suggestions should be executed.

[0064] For example, in this embodiment, the query message asking whether the user agrees to the action can be sent in the form of a button. Optionally, for each database optimization suggestion, two buttons, "Agree" and "Disagree," can be displayed for the user to choose from.

[0065] Alternatively, a message can be sent to the database administrator's account via SMS or email, asking whether they agree to implement the database optimization suggestions.

[0066] S206. Check whether the optimization suggestions for the database at the specified time are acceptable for execution; if they are acceptable for execution, proceed to step S207; otherwise, end.

[0067] S207. Based on the optimization suggestions of the database at a specified time, generate an execution command to execute the optimization suggestions of the database;

[0068] S208. Use the execution command to perform optimization operations on the database.

[0069] For example, in this embodiment, the execution command for generating optimization suggestions for the database can be a Data Definition Language (DDL) statement. For example, the DDL statement for adding an index can be ADD INDEX.

[0070] In this embodiment, the optimization suggestions for the database at a specified time can be generated online in real time with negligible time consumption. According to the above scheme of this embodiment, the database can be optimized in a timely manner by executing commands at the specified time or the next time, so as to effectively improve the query performance of the database after the specified time.

[0071] Furthermore, in this embodiment, if the optimization suggestion for the database at a specified time is to replace the database storage medium, this optimization suggestion requires manual processing, and the database optimization processing equipment cannot automatically change the time. However, when the optimization suggestion at a specified time includes at least one of the following: a suggestion to adjust the column order, a suggestion to add an index table, or a suggestion to adjust the data storage distribution in the target column, the corresponding optimization processing can be automatically executed by the database optimization processing device without manual adjustment. Therefore, when the corresponding optimization suggestion is approved for execution, the corresponding execution command can be directly generated, and the optimization operation can be performed based on the execution command.

[0072] Optionally, after generating optimization suggestions for the database at a specified time, professionals can manually complete the database optimization and upgrade based on these suggestions. Since the generated optimization suggestions are highly accurate, they can effectively improve the accuracy and efficiency of the database optimization process.

[0073] The database optimization method of this embodiment, through the above-described technical solution, can automatically generate optimization suggestions for the database at a specified time, and can effectively ensure the accuracy of the generated optimization suggestions. Furthermore, in this embodiment, an execution command to implement the optimization suggestions for the database at the specified time can also be automatically generated, and, with consent, the execution command is automatically adopted to perform optimization operations on the database. This highly intelligent implementation effectively improves the efficiency of database optimization.

[0074] Figure 3 This is a schematic diagram based on the third embodiment of this disclosure; as shown Figure 3 As shown, this embodiment provides a database optimization processing device 300, including:

[0075] The statistics module 301 is used to collect statistics on multiple query plans for querying the database within a preset time period before a specified time.

[0076] The suggestion generation module 302 is used to generate optimization suggestions for the database at the specified time based on the multiple query planning information and the metadata of the database.

[0077] The database optimization processing device 300 in this embodiment achieves the same implementation principle and technical effect as the above-mentioned related method embodiments by using the above-mentioned modules. For details, please refer to the description of the above-mentioned related method embodiments, which will not be repeated here.

[0078] Figure 4 This is a schematic diagram based on the fourth embodiment of the present disclosure; as shown Figure 4 As shown, this embodiment provides a database optimization processing device 400, including: the above-mentioned... Figure 3 The modules with the same name and function shown are: statistics module 401 and suggestion generation module 402.

[0079] like Figure 4 As shown, in the database optimization processing apparatus 400 of this embodiment, the statistics module 401 includes:

[0080] The acquisition unit 4011 is used to acquire multiple query requests that query the database within the preset time length before the specified time.

[0081] The acquisition unit 4012 is used to acquire the multiple query planning information corresponding to the multiple query requests, wherein each query planning information is obtained by the query optimizer based on the corresponding query request.

[0082] Optionally, in one embodiment of this disclosure, the query planning information includes scanning information and filtering information.

[0083] In one embodiment of this disclosure, a generation module 402 is proposed for:

[0084] Based on the multiple query planning information and the metadata of the database, optimization suggestions for the database at the specified time are generated using pre-defined optimization suggestion rules.

[0085] In one embodiment of this disclosure, a generation module 402 is proposed for:

[0086] Based on the multiple query planning information and the metadata of the database, a pre-trained suggestion generation model is used to generate optimization suggestions for the database at the specified time.

[0087] In one embodiment of this disclosure, a generation module 402 is proposed for:

[0088] Based on the multiple query planning information and the metadata of the database, generate at least one of the following suggestions for the specified time: adjusting the column order of the database, adding an index table, adjusting the data storage distribution in the target column, changing the type of storage medium for storing the database, and changing the type of storage medium for storing tables or partitions in the database.

[0089] Optionally, such as Figure 4 As shown, in one embodiment of this disclosure, the database optimization processing apparatus 400 further includes:

[0090] Display module 403 is used to display optimization suggestions for the database at the specified time.

[0091] The issuing module 404 is used to issue an inquiry message asking whether to agree to execute the database optimization suggestions if it is determined that the database optimization suggestions include at least one of the following: suggestions to adjust the column order of the database, suggestions to add index tables, and suggestions to adjust the data storage distribution in the target column.

[0092] The detection module 405 is used to detect whether the optimization suggestions of the database at the specified time are agreed to be executed.

[0093] Optionally, such as Figure 4As shown, in one embodiment of this disclosure, the database optimization processing apparatus 400 further includes:

[0094] The instruction generation module 406 is further configured to, if the optimization suggestion of the database at the specified time is agreed to be executed, generate an execution command for executing the optimization suggestion of the database based on the optimization suggestion of the database at the specified time;

[0095] The execution module 407 is used to perform optimization operations on the database using the execution command.

[0096] The database optimization processing device 400 in this embodiment achieves the same implementation principle and technical effect as the above-mentioned related method embodiments by using the above-mentioned modules. For details, please refer to the description of the above-mentioned related method embodiments, which will not be repeated here.

[0097] According to embodiments of this disclosure, this disclosure also provides an electronic device, a readable storage medium, and a computer program product.

[0098] Figure 5 A schematic block diagram of an example electronic device 500 that can be used to implement embodiments of the present disclosure is shown. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device may also represent various forms of mobile devices, such as personal digital processors, cellular phones, smartphones, wearable devices, and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely illustrative and are not intended to limit the implementation of the present disclosure described and / or claimed herein.

[0099] like Figure 5 As shown, device 500 includes a computing unit 501, which can perform various appropriate actions and processes based on a computer program stored in read-only memory (ROM) 502 or a computer program loaded from storage unit 508 into random access memory (RAM) 503. RAM 503 may also store various programs and data required for the operation of device 500. The computing unit 501, ROM 502, and RAM 503 are interconnected via bus 504. Input / output (I / O) interface 505 is also connected to bus 504.

[0100] Multiple components in device 500 are connected to I / O interface 505, including: input unit 506, such as keyboard, mouse, etc.; output unit 507, such as various types of monitors, speakers, etc.; storage unit 508, such as disk, optical disk, etc.; and communication unit 509, such as network card, modem, wireless transceiver, etc. Communication unit 509 allows device 500 to exchange information / data with other devices through computer networks such as the Internet and / or various telecommunications networks.

[0101] The computing unit 501 can be a variety of general-purpose and / or special-purpose processing components with processing and computing capabilities. Some examples of the computing unit 501 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special-purpose artificial intelligence (AI) computing chips, various computing units running machine learning model algorithms, a digital signal processor (DSP), and any suitable processor, controller, microcontroller, etc. The computing unit 501 performs the various methods and processes described above, such as the methods of this disclosure. For example, in some embodiments, the methods of this disclosure may be implemented as a computer software program tangibly contained in a machine-readable medium, such as storage unit 508. In some embodiments, part or all of the computer program may be loaded and / or installed on device 500 via ROM 502 and / or communication unit 509. When the computer program is loaded into RAM 503 and executed by the computing unit 501, one or more steps of the methods of this disclosure described above may be performed. Alternatively, in other embodiments, the computing unit 501 may be configured to perform the methods of this disclosure by any other suitable means (e.g., by means of firmware).

[0102] Various embodiments of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field-programmable gate arrays (FPGAs), application-specific integrated circuits (ASICs), application-specific standard products (ASSPs), systems-on-a-chip (SoCs), complex programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments may include implementations in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which may be a dedicated or general-purpose programmable processor, capable of receiving data and instructions from a storage system, at least one input device, and at least one output device, and transmitting data and instructions to the storage system, the at least one input device, and the at least one output device.

[0103] The program code used to implement the methods of this disclosure may be written in any combination of one or more programming languages. This program code may be provided to a processor or controller of a general-purpose computer, special-purpose computer, or other programmable data processing apparatus, such that when executed by the processor or controller, the program code causes the functions / operations specified in the flowcharts and / or block diagrams to be implemented. The program code may be executed entirely on a machine, partially on a machine, as a standalone software package partially on a machine and partially on a remote machine, or entirely on a remote machine or server.

[0104] In the context of this disclosure, a machine-readable medium can be a tangible medium that may contain or store a program for use by or in conjunction with an instruction execution system, apparatus, or device. A machine-readable medium can be a machine-readable signal medium or a machine-readable storage medium. A machine-readable medium can be, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination of the foregoing. More specific examples of machine-readable storage media include electrical connections based on one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination of the foregoing.

[0105] To provide interaction with a user, the systems and techniques described herein can be implemented on a computer having: a display device for displaying information to the user (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor); and a keyboard and pointing device (e.g., a mouse or trackball) through which the user provides input to the computer. Other types of devices can also be used to provide interaction with the user; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including sound input, voice input, or tactile input).

[0106] The systems and technologies described herein can be implemented in computing systems that include backend components (e.g., as a data server), or computing systems that include middleware components (e.g., an application server), or computing systems that include frontend components (e.g., a user computer with a graphical user interface or web browser through which a user can interact with embodiments of the systems and technologies described herein), or any combination of such backend, middleware, or frontend components. The components of the system can be interconnected via digital data communication of any form or medium (e.g., a communication network). Examples of communication networks include local area networks (LANs), wide area networks (WANs), and the Internet.

[0107] Computer systems can include clients and servers. Clients and servers are generally located far apart and typically interact via communication networks. Client-server relationships are created by computer programs running on the respective computers and having a client-server relationship with each other. Servers can be cloud servers, servers in distributed systems, or servers incorporating blockchain technology.

[0108] It should be understood that the various forms of processes shown above can be used to rearrange, add, or delete steps. For example, the steps described in this disclosure can be executed in parallel, sequentially, or in different orders, as long as the desired result of the technical solution disclosed in this disclosure can be achieved, and this is not limited herein.

[0109] The specific embodiments described above do not constitute a limitation on the scope of protection of this disclosure. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of this disclosure should be included within the scope of protection of this disclosure.

Claims

1. A database optimization method, comprising: Statistically analyze multiple query plans for the database within a preset time period before a specified time. The query planning information is obtained by parsing and optimizing the query request from outside the database; The query planning information includes scanning information and filtering information; Based on the multiple query planning information and the metadata of the database, an optimization suggestion for the database at the specified time is generated; The database optimization suggestions include at least one of the following: suggestions to adjust the column order of the database, suggestions to adjust the data storage distribution in the target column, suggestions to change the type of storage medium for storing the database, and suggestions to change the type of storage medium for storing tables or partitions in the database.

2. The method according to claim 1, wherein, This section provides statistical information on multiple query plans for database queries within a preset time period prior to a specified time, including: Collect multiple query requests that query the database within the preset time length prior to the specified time. Obtain the multiple query planning information corresponding to the multiple query requests, wherein each query planning information is obtained by the query optimizer based on the corresponding query request.

3. The method according to claim 2, wherein, The query planning information mentioned above includes scanning information and filtering information.

4. The method according to claim 1, wherein, Based on the multiple query planning information and the database metadata, optimization suggestions for the database at the specified time are generated, including: Based on the multiple query planning information and the metadata of the database, optimization suggestions for the database at the specified time are generated using pre-defined optimization suggestion rules.

5. The method according to claim 1, wherein, Based on the multiple query planning information and the database metadata, optimization suggestions for the database at the specified time are generated, including: Based on the multiple query planning information and the metadata of the database, a pre-trained suggestion generation model is used to generate optimization suggestions for the database at the specified time.

6. The method according to claim 1, wherein, After generating optimization suggestions for the database at the specified time based on the multiple query planning information and the database metadata, the method further includes: Display optimization suggestions for the database at the specified time. If it is determined that the optimization suggestions for the database include at least one of the following: suggestions to adjust the column order of the database, suggestions to add index tables, and suggestions to adjust the data storage distribution in the target column, an inquiry message is issued asking whether the optimization suggestions for the database are agreed to be executed. Check whether the optimization suggestions for the database at the specified time point are acceptable for execution.

7. The method according to claim 6, wherein, After detecting whether the optimization suggestions for the database at the specified time point are acceptable for execution, the method further includes: If the optimization suggestions for the database at the specified time are approved for execution, an execution command for executing the optimization suggestions for the database is generated based on the optimization suggestions for the database at the specified time. The database is optimized using the execution command.

8. A database optimization processing apparatus, comprising: The statistics module is used to collect statistics on multiple query plans for the database within a preset time period before a specified time. The query planning information is obtained by parsing and optimizing the query request from outside the database; The query planning information includes scanning information and filtering information; The suggestion generation module is used to generate optimization suggestions for the database at the specified time based on the multiple query planning information and the database metadata; The database optimization suggestions include at least one of the following: suggestions to adjust the column order of the database, suggestions to adjust the data storage distribution in the target column, suggestions to change the type of storage medium for storing the database, and suggestions to change the type of storage medium for storing tables or partitions in the database.

9. The apparatus according to claim 8, wherein, The statistics module includes: The acquisition unit is used to acquire multiple query requests that query the database within the preset time length before the specified time. The acquisition unit is used to acquire the multiple query planning information corresponding to the multiple query requests, wherein each query planning information is obtained by the query optimizer based on the corresponding query request.

10. The apparatus according to claim 9, wherein, The query planning information mentioned above includes scanning information and filtering information.

11. The apparatus according to claim 8, wherein, The suggestion generation module is used for: Based on the multiple query planning information and the metadata of the database, optimization suggestions for the database at the specified time are generated using pre-defined optimization suggestion rules.

12. The apparatus according to claim 8, wherein, The suggestion generation module is used for: Based on the multiple query planning information and the metadata of the database, a pre-trained suggestion generation model is used to generate optimization suggestions for the database at the specified time.

13. The apparatus according to claim 8, wherein, The device further includes: The display module is used to display optimization suggestions for the database at the specified time. The issuing module is used to issue an inquiry message asking whether to agree to execute the optimization suggestions for the database if it is determined that the optimization suggestions for the database include at least one of the following: suggestions to adjust the column order of the database, suggestions to add index tables, and suggestions to adjust the data storage distribution in the target column. The detection module is used to detect whether the optimization suggestions of the database at the specified time point are acceptable for execution.

14. The apparatus according to claim 13, wherein, The device further includes: The instruction generation module is further configured to, if the optimization suggestion of the database at the specified time is agreed to be executed, generate an execution command for executing the optimization suggestion of the database based on the optimization suggestion of the database at the specified time; An execution module is used to perform optimization operations on the database using the execution commands.

15. An electronic device comprising: At least one processor; as well as A memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor to enable the at least one processor to perform the method of any one of claims 1-7.

16. A non-transitory computer-readable storage medium storing computer instructions, wherein, The computer instructions are used to cause the computer to perform the method according to any one of claims 1-7.

17. A computer program product comprising a computer program that, when executed by a processor, implements the method according to any one of claims 1-7.

Images