Distributed database model generation and optimization method, device and system and medium

Abstract

The invention provides a distributed database model generation and optimization method, device and system and a medium, and the method comprises the steps: obtaining DDL information and a log file, carrying out the analysis processing of the DDL information and the log file, and obtaining K-V format DDL data and K-V log information; the DDL data in the K-V format are read into a Transform model, the DDL data in the K-V format are analyzed through the Transform model, and a basic physical model diagram is obtained; performing optimization analysis on the K-V log information to obtain business pressure information and optimization suggestions; and summarizing the basic physical model diagram, the business pressure information and the optimization suggestions to generate an optimization model diagram. According to the method, the DDL and the log information are preprocessed, the physical model diagram is generated by utilizing the Transform model, the service pressure information and the optimization suggestions are analyzed, and finally the model diagram with the optimization suggestions is output, so that the time cost can be remarkably reduced, the user experience can be improved, and the algorithm can be continuously optimized.

Description

Technical Field

[0001] The present invention relates to the field of distributed database technology, and in particular to a distributed database model generation and optimization method, device, system and medium. Background Art

[0002] The existing manual analysis business model generation and optimization system involves the time-consuming manual analysis of business models in the field of distributed databases, and the database model generation tool has a single result and cannot be combined with actual business SQL and logs, which has little effect on optimization scenarios. Distributed database usage scenarios involve a large number of business logic and objects. For database operation and maintenance and testers in business docking and optimization scenarios, they can only infer business logic by enabling full log capture SQL, and cannot sort out the business logic in a short time to give database models and optimization suggestions.

[0003] For existing database model generation tools, the input is table creation statements, and the output is a physical model diagram. The table structure generated in this scenario is independent and cannot establish business connections; nor can it be combined with actual business logs to find pressure distribution and performance bottlenecks. The current database model generation methods, such as MySQL-DATABENCH and POWERDESIGNER, generate physical models based on DDL for independent business SQL and actual business pressure, and only have a single display function. Summary of the invention

[0004] In view of the above-mentioned deficiencies in the prior art, the purpose of the present invention is to provide a distributed database model generation and optimization method. The present invention preprocesses DDL and log information, uses the Transformer model to generate a physical model diagram, analyzes business pressure information and optimization suggestions, and finally outputs a model diagram with optimization suggestions, which can significantly reduce time costs, improve user experience, and continuously optimize algorithms.

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

[0006] A first aspect of the present invention provides a distributed database model generation and optimization method, comprising the following steps:

[0007] Obtaining DDL information and log files, analyzing and processing the DDL information and log files respectively, to obtain KV format DDL data and KV log information;

[0008] The KV format DDL data is read into the Transfrom model, and the KV format DDL data is parsed by the Transfrom model to obtain a basic physical model diagram;

[0009] Optimize and analyze the KV log information to obtain business pressure information and optimization suggestions;

[0010] The basic physical model diagram, business pressure information and optimization suggestions are summarized to generate an optimized model diagram.

[0011] In one embodiment, the acquiring of DDL information and analyzing and processing the DDL information to obtain KV format DDL data include:

[0012] Acquire the DDL information, and perform data cleaning processing on the DDL information through a function algorithm;

[0013] The DDL information after data cleaning is converted into KV format to obtain KV format DDL data.

[0014] In one embodiment, the obtaining of the log file and analyzing and processing the log file to obtain KV log information includes:

[0015] Acquire the log information, perform identification and information extraction processing on the log information, and obtain relevant entries of the SQL statement;

[0016] The relevant items of the SQL statement are filtered by the keyword of the SQL statement to obtain KV log information.

[0017] In one embodiment, the step of reading the KV format DDL data into a Transfrom model and parsing the KV format DDL data through the Transfrom model to obtain a basic physical model diagram includes:

[0018] Input the KV format DDL data into the pre-trained Transfrom model;

[0019] The Transfrom model processes the K value to generate a block graph;

[0020] The Transfrom model processes the V value to generate an array;

[0021] A basic physical model diagram is obtained according to the block graphics and array.

[0022] In one embodiment, the optimizing and analyzing the KV log information to obtain the business pressure information and optimization suggestions includes:

[0023] Analyze the KV log information through an analysis algorithm to generate a unique identifier for the log file;

[0024] Match the SQL statement with the unique identifier according to the table name to obtain the execution time and execution count;

[0025] Divide the table pressure level according to the execution time and execution number, and generate business pressure information through the table and the table pressure level;

[0026] Optimization suggestions are obtained based on the table pressure level, unique identifier and business pressure information.

[0027] In one embodiment, the basic physical model diagram, business pressure information and optimization suggestions are aggregated to generate an optimization model diagram, including:

[0028] Summarize the basic physical model diagram, business pressure information and optimization suggestions;

[0029] The basic physical model diagram is annotated according to the summarized information to generate an optimized model diagram.

[0030] In one embodiment, after the basic physical model diagram, business pressure information and optimization suggestions are aggregated to generate an optimized model diagram, the method further includes:

[0031] Outputting the optimization model diagram to a user interface and displaying it;

[0032] Collect user suggestions for optimizing the model graph and send the suggestions to the log optimization module.

[0033] A distributed database model generation and optimization device, the device comprising:

[0034] An information processing module is used to obtain DDL information and log files, analyze and process the DDL information and log files respectively, and obtain KV format DDL data and KV log information;

[0035] A model generation module, used for reading the KV format DDL data into a Transfrom model, parsing the KV format DDL data through the Transfrom model, and obtaining a basic physical model diagram;

[0036] A log analysis and optimization module is used to optimize and analyze the KV log information to obtain business pressure information and optimization suggestions;

[0037] The model optimization module is used to summarize the basic physical model diagram, business pressure information and optimization suggestions to generate an optimized model diagram.

[0038] A distributed database model generation and optimization system, the system comprising at least one processor; and

[0039] a memory communicatively connected to the at least one processor; wherein,

[0040] The memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to perform the distributed database model generation and optimization method.

[0041] A non-volatile computer-readable storage medium stores computer-executable instructions, which, when executed by one or more processors, enable the one or more processors to execute the distributed database model generation and optimization method.

[0042] Beneficial Effects :The embodiments of the present invention disclose a distributed database model generation and optimization method, device, system and medium. Compared with the prior art, the present invention preprocesses DDL and log information, uses the Transformer model to generate a physical model diagram, analyzes business pressure information and optimization suggestions, and finally outputs a model diagram with optimization suggestions, which can significantly reduce time costs, improve user experience, and continuously optimize algorithms. BRIEF DESCRIPTION OF THE DRAWINGS

[0043] The present invention will be further described below with reference to the accompanying drawings and embodiments, in which:

[0044] Figure 1 A flow chart of a distributed database model generation and optimization method provided by an embodiment of the present invention;

[0045] Figure 2 Another flow chart of the distributed database model generation and optimization method provided by an embodiment of the present invention;

[0046] Figure 3 A schematic diagram of the functional modules of a distributed database model generation and optimization device provided by an embodiment of the present invention;

[0047] Figure 4 A schematic diagram of the hardware structure of a distributed database model generation and optimization system provided in an embodiment of the present invention. DETAILED DESCRIPTION

[0048] In order to make the technical problems, technical solutions and beneficial effects to be solved by the embodiments of the present invention more clearly understood, the present invention is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present invention and are not used to limit the present invention.

[0049] See also Figure 1 , Figure 1 This is a flow chart of an embodiment of the distributed database model generation and optimization method provided by the present invention, and the method specifically includes the following steps:

[0050] S100, obtaining DDL information and log files, analyzing and processing the DDL information and log files respectively, and obtaining KV format DDL data and KV log information;

[0051] S200, reading the KV format DDL data into a Transfrom model, parsing the KV format DDL data through the Transfrom model, and obtaining a basic physical model diagram;

[0052] S300, optimizing and analyzing the KV log information to obtain business pressure information and optimization suggestions;

[0053] S400: Summarize the basic physical model diagram, business pressure information and optimization suggestions to generate an optimized model diagram.

[0054] In this embodiment, DDL (data definition language) information and log files are extracted from the database management system to facilitate subsequent performance monitoring and optimization. Among them, DDL information usually contains key data definitions such as table structure, index, and field, while the log file records the details of the system runtime. The Druid tool can be used to parse MySQL's DDL to extract table names, field information, index information, etc., and convert them into data in KV (key-value) format. For example, table name, field name, field type, index name, etc. are used as keys, and their corresponding detailed information is used as values.

[0055] For log file processing, you can use log analysis tools such as Elasticsearch and Logstash for real-time analysis and processing. These tools can help extract business pressure information, such as request volume, response time, etc., and perform optimization analysis to obtain optimization suggestions. In this way, you can have a deeper understanding of the system's performance under different business pressures and identify potential performance bottlenecks.

[0056] Next, the DDL data in KV format is input into the Transformer model. The Transformer model is a deep learning model that can process sequence data, parse DDL data, and build a basic physical model diagram through the advantages of self-attention mechanism and parallel computing. At the same time, log analysis tools can also be used to conduct in-depth analysis of KV format log information to identify system performance during business peaks and potential performance bottlenecks, and provide business pressure information and optimization suggestions based on the analysis results.

[0057] Finally, the basic physical model diagram, business pressure information, and optimization suggestions are summarized to generate a comprehensive optimization model diagram. This model diagram can intuitively display the system architecture, performance bottlenecks, and recommended optimization measures, helping system administrators and developers understand the system status and guide subsequent optimization work. In this way, database and system performance can be effectively monitored and optimized.

[0058] In one embodiment, the acquiring of DDL information and analyzing and processing the DDL information to obtain KV format DDL data include:

[0059] Acquire the DDL information, and perform data cleaning processing on the DDL information through a function algorithm;

[0060] The DDL information after data cleaning is converted into KV format to obtain KV format DDL data.

[0061] In this embodiment, the DDL (Data Definition Language) information of the database is obtained, which is crucial for understanding the database structure and performing subsequent performance optimization. There are two main ways to obtain DDL information: a metadata database connection method and a file transfer method.

[0062] The metadata database connection method involves the system directly connecting to the metadata database that stores database structure and configuration information. The system tables in the metadata database contain rich metadata information, and the system obtains DDL information by reading these data dictionary tables. This method can obtain the latest database structure information in real time, ensuring the accuracy and timeliness of analysis.

[0063] The file transfer method allows users to transfer DDL information directly to the system in the form of files. This method provides users with flexibility, especially when they cannot directly access the metadata library. Users can share database structure information by exporting DDL files.

[0064] Regardless of the method used to obtain DDL information, data cleaning is required to remove unnecessary redundant information and retain key DDL information, such as table structure, field definition, index, etc. This step uses Python's pandas library to implement a custom data cleaning algorithm to ensure data accuracy and availability.

[0065] The cleaned data needs to be converted into a key-value (KV) format, which will make the data more structured and easier to process and analyze. Specifically, in the metadata database connection mode, the system processes the DDL information obtained from the metadata database through the Python-pandas function, converts it into JSON format, and then further converts it into KV format. In this process, redundant information in each row will be removed and filtered to ensure the simplicity and efficiency of the data.

[0066] For file transfer, if the user uploads DDL information in the form of a file, the system also uses a custom data cleaning algorithm constructed by Python-pandas functions to process this information, remove redundant information, and convert it into KV format. This processing not only improves the readability of the data, but also facilitates subsequent model generation and analysis.

[0067] After data cleaning and format conversion, the obtained KV format DDL data will be output to the model generation module. This module will use these structured data to generate the physical model diagram of the database, which is the basis for subsequent database optimization and analysis. By obtaining DDL information from different sources, removing unnecessary information through data cleaning algorithms, and converting the cleaned information into a structured KV format, the model generation module can further process this data to generate a physical model diagram of the database. This process not only improves the efficiency of data processing, but also provides strong support for database maintenance and optimization.

[0068] In one embodiment, the obtaining of the log file and analyzing and processing the log file to obtain KV log information includes:

[0069] Acquire the log information, perform identification and information extraction processing on the log information, and obtain relevant entries of the SQL statement;

[0070] The relevant items of the SQL statement are filtered by the keyword of the SQL statement to obtain KV log information.

[0071] In this embodiment, a log file is obtained from a database server or an application server, and the log file records the database operation information in detail, including the executed SQL statements. The purpose of analyzing these log files is to identify and extract entries related to SQL statements, and the log files are scanned line by line to find the part containing SQL statements. Specific rules or algorithms are used to identify SQL statements in the log, for example, by finding lines starting with specific keywords, such as SQL command keywords such as "SELECT", "INSERT", "UPDATE" or "DELETE".

[0072] The identified SQL statement entries will be filtered by the system to ensure that only information related to SQL operations is extracted. These filtered SQL-related contents need to be converted into key-value (KV) format to facilitate data structuring and subsequent processing and analysis. In the KV format, each SQL statement can be regarded as a key, and the detailed information related to it (such as execution time, number of rows affected, query conditions, etc.) can be used as values.

[0073] Regardless of whether the log information is obtained through metadata database connection or file transfer, data cleaning is required. This step can use Python's pandas library to implement a custom data cleaning algorithm. The purpose of data cleaning is to remove unnecessary redundant information and retain key DDL information, such as table structure, field definition, index, etc.

[0074] The cleaned data is converted into KV format, which makes the data more structured and easier to process and analyze. The specific conversion process can be processed by Python-pandas function, which converts the data into JSON format and then further converts it into KV format. In this process, redundant information in each row will be removed and filtered.

[0075] After identification, extraction, and format conversion, the resulting KV-formatted log information will be pushed to the log analysis and optimization module. This module will use this data for further analysis to identify business pressure points, performance bottlenecks, and generate optimization recommendations. In summary, this process involves obtaining log files from the server, identifying and extracting entries related to SQL statements, using keywords to filter out SQL-related content, and converting this content into a structured KV format so that the log analysis and optimization module can further analyze this data and provide a basis for database performance optimization.

[0076] In one embodiment, the step of reading the KV format DDL data into a Transfrom model and parsing the KV format DDL data through the Transfrom model to obtain a basic physical model diagram includes:

[0077] Input the KV format DDL data into the pre-trained Transfrom model;

[0078] The Transfrom model processes the K value to generate a block graph;

[0079] The Transfrom model processes the V value to generate an array;

[0080] A basic physical model diagram is obtained according to the block graphics and array.

[0081] In this embodiment, the DDL data that has been cleaned and converted to KV format is input into the pre-trained Transformer model. The DDL data includes key information such as the table structure and field definition of the database. The Transformer model processes the K value (i.e., the table name or the total name of the chart) to generate a block-shaped graph. Each block represents a table in the database, and the table name is marked on the block. Such a graphical representation helps to intuitively display the structure of the database. The V value (i.e., the detailed information of the table, such as field type, index, etc.) is converted into a JSON array. Each array element represents a row of data in the table, and each position in the row corresponds to a column of the table, so that the detailed information of the table can be displayed in a structured form.

[0082] Based on the squared graphics and arrays obtained from the processed K and V values, the Transformer model generates a basic physical model diagram. This model diagram not only shows the structure of each table in the database, but also shows the relationship between the tables, such as foreign key associations. Through extensive training, the Transformer model can optimize the speed and accuracy of generation. The pre-trained model can learn the feature representation of the image to generate new image samples. In this system, the model learns the feature representation of the database structure, so that it can generate an accurate database physical model diagram.

[0083] In addition to the processing of the Transformer model, the system may also include other optimization algorithms that work with the Transformer model to ensure that the generated physical model diagram is both accurate and meets actual business needs. For example, the performance and adaptability of the model can be further improved through continuous pre-training.

[0084] In summary, this process uses the powerful capabilities of the Transformer model to convert structured DDL data into intuitive graphical representations, generate basic physical model diagrams, and provide a powerful tool for database design, optimization, and maintenance. Through pre-training and continuous training optimization, the model can quickly and accurately generate physical model diagrams, improving the efficiency and quality of database management. This method not only improves the efficiency of data processing, but also provides strong support for database maintenance and optimization, ensuring the optimization of database performance.

[0085] In one embodiment, the optimizing and analyzing the KV log information to obtain the business pressure information and optimization suggestions includes:

[0086] Analyze the KV log information through an analysis algorithm to generate a unique identifier for the log file;

[0087] Match the SQL statement with the unique identifier according to the table name to obtain the execution time and execution count;

[0088] Divide the table pressure level according to the execution time and execution number, and generate business pressure information through the table and the table pressure level;

[0089] Optimization suggestions are obtained based on the table pressure level, unique identifier and business pressure information.

[0090] In this embodiment, an analysis algorithm is used to perform in-depth analysis on the log information in KV format, which records the execution details of SQL statements in detail, including key data such as execution time and execution count. Through these analyses, a unique identifier is generated for each SQL statement in the log file, called DigestId, which is used to identify and match SQL statements of the same type, no matter how many times they appear in the log.

[0091] According to the table name involved in the SQL statement, each SQL statement is matched with its corresponding DigestId, and the execution time and execution count of each SQL statement are recorded. This is done by parsing the SQL log and generating DigestId, which is part of the slow log analysis. Then, according to the SQL statement execution time and execution count of each table, the table is divided into stress levels. Tables with long execution time and high execution count will be classified as high stress levels.

[0092] Combined with the pressure level of the table, business pressure information is generated, including which tables are the bottleneck points of business pressure and the overall pressure situation of these bottleneck points. In addition, the relationship analysis between tables is also performed. If different tables match the same DigestId, it means that there is a business connection between them. These relationships are converted into KV format data for use by the model optimization module.

[0093] Furthermore, the business bottleneck analysis is to sum up and summarize the pressure level of the table, determine the business pressure bottleneck, and convert this information into KV format. In terms of optimization suggestion generation, for tables with high total pressure values, find the corresponding DigestId, and retrieve the SQL index and sharding information based on the DDL information of the table. Based on this information, determine whether the SQL is pushed down. If the association condition is not a sharding key, it is recommended to use the association condition as a distribution key. If the SQL condition does not contain an index key, it is recommended to use the query condition as an index key. These optimization suggestions are pushed to the model optimization module in KV format.

[0094] In summary, the log analysis and optimization module analyzes the log information in KV format, identifies the business pressure bottleneck, and proposes targeted optimization suggestions. These suggestions are output in KV format, providing the model optimization module with a basis for optimizing database performance. This method not only improves the efficiency of data processing, but also provides strong support for database maintenance and optimization, ensuring the optimization of database performance.

[0095] In one embodiment, the basic physical model diagram, business pressure information and optimization suggestions are aggregated to generate an optimization model diagram, including:

[0096] Summarize the basic physical model diagram, business pressure information and optimization suggestions;

[0097] The basic physical model diagram is annotated according to the summarized information to generate an optimized model diagram.

[0098] In this embodiment, the key task of the model optimization module is to effectively summarize the basic physical model diagram, business pressure information and optimization suggestions. This step involves integrating data from different sources and formats for unified processing and display. The basic physical model diagram shows the structure of the database in detail, including tables, fields, indexes, and relationships between tables, etc., providing a basic view for database design and optimization. Business pressure information includes statistical data such as the pressure level and execution time and number of executions of each table. This information reflects the pressure distribution of the database in actual operation. Optimization suggestions are based on business pressure information and SQL statement analysis, and may include index optimization, sharding strategy adjustment, etc., aimed at improving database performance and response time.

[0099] The model optimization module annotates the above-summarized information onto the basic physical model diagram to generate an optimized model diagram. This optimized model diagram not only shows the structure of the database, but also intuitively displays the business logic, pressure bottlenecks, and optimization suggestions. Specifically, the business relationship between the annotated tables can be represented on the model diagram in a graphical way, such as by connecting lines or color coding to represent the association between tables. When annotating the relationship between tables and pressure, the pressure level of each table is marked on the model diagram, and different colors or icons may be used to represent tables of different pressure levels. For the annotated tables and recommended sharding and index relationships, for each table, the recommended sharding key and index key are marked, as well as why these suggestions are necessary, which can provide intuitive guidance for database management and optimization.

[0100] In summary, the model optimization module generates an enhanced optimization model diagram by combining the basic physical model diagram with business pressure information and optimization suggestions. This model diagram not only shows the structure of the database, but also provides guidance for performance optimization, making database management and optimization more intuitive and efficient. In this way, database administrators and developers can quickly identify performance bottlenecks and potential optimization measures, thereby improving the overall performance and stability of the database.

[0101] In one embodiment, after the basic physical model diagram, business pressure information and optimization suggestions are aggregated to generate an optimized model diagram, the method further includes:

[0102] Outputting the optimization model diagram to a user interface and displaying it;

[0103] Collect user suggestions for optimizing the model graph and send the suggestions to the log optimization module.

[0104] In this embodiment, the core function of the system is to output the generated optimization model diagram to the user interface and display it intuitively. This model diagram integrates the structure of the database, business pressure information and optimization suggestions, providing users with a comprehensive view. After logging into the system, users can easily fill in database information or choose to manually upload table structure information. The system is designed with a user-friendly interface that allows users to upload business logs, including general logs and slow logs, to the system or a specified log path.

[0105] The background program can automatically obtain the database table metadata information, which is usually achieved by connecting to the database and querying the metadata database. After the user uploads the log information, just click the "Start Analysis" button, and the system will automatically perform an in-depth analysis of the uploaded log and generate an optimization model diagram. This process may involve complex data processing and analysis algorithms, but users do not need to worry about these complex background operations.

[0106] The generated optimization model graph can not only be printed and displayed on the user interface, but also provides a download option, allowing users to easily save the model graph for offline analysis or records. In addition, users can mark the optimization suggestions to indicate whether each suggestion is effective or useful. This feedback mechanism allows users to check or enter feedback directly on the user interface, enhancing user participation and system interactivity.

[0107] User feedback will be collected and sent to the log optimization module. This module will adjust and optimize the analysis algorithm based on user feedback to improve the accuracy of future analysis and the relevance of optimization suggestions. Through user feedback, the system can continuously optimize its analysis and suggestion generation capabilities, forming a closed-loop optimization process, and continuously improving user experience and system performance.

[0108] In summary, the output and feedback module is not only responsible for displaying the optimization model diagram to the user, but also for collecting user feedback and using this feedback for continuous improvement of the system. This interactive mechanism ensures that the system can be adjusted according to actual usage and user needs, improving the practicality and effectiveness of the system. Through this continuous feedback and optimization cycle, the system can better adapt to the ever-changing business needs and database environment, and provide users with more accurate and efficient database performance optimization services.

[0109] The embodiment of the present invention also provides a detailed process of a distributed database model generation and optimization method, such as Figure 2 As shown in the figure. The process consists of five main modules, which work together to ensure that the system can provide accurate and useful database model diagrams and continuously optimize its performance and functions according to the actual needs of users. The first is the input information preprocessing module, which is the first processing step of the system and is responsible for receiving and processing the DDL (data definition language) information and log information input by users. The task of this module is to convert these raw data into a structured format that can be further processed by the system. This may include operations such as data cleaning and format conversion to ensure the quality and consistency of the data. This step is the basis of the entire process and is crucial to the accuracy and effectiveness of subsequent steps. Next is the model generation module, which is responsible for converting the preprocessed DDL information into a physical model. This physical model describes the structure of the database in detail, including tables, fields, indexes, and the relationships between them. This module may use algorithms or pre-trained models (such as Transformer models) to generate this model diagram to ensure the accuracy and detail of the model diagram. The log analysis and optimization module is responsible for analyzing log information, identifying high-frequency SQL and slow SQL in units of tables, and generating corresponding optimization suggestions. It is also responsible for generating business relationships based on associated SQL statements and feeding this information back to the model optimization module. In addition, it will also modify its own optimization algorithm based on the feedback results of the model optimization module to improve the accuracy of the optimization suggestions. The model optimization module integrates the table relationships and optimization suggestions provided by the log analysis optimization module into the basic physical model diagram. In this way, the generated model diagram not only shows the structure of the database, but also contains information about business logic and performance optimization, making the model diagram more comprehensive and useful. Finally, there is the output interaction module, which is responsible for displaying the final optimized model diagram to the user and allowing the user to interact with the system. The model diagram it outputs contains business relationships and optimization suggestions, which users can view and download. In addition, it is also responsible for collecting user feedback on the optimization suggestions and pushing these feedback results to the log analysis optimization module so that the system can optimize and adjust itself according to user feedback. This process ensures that the system can provide accurate and useful database model diagrams and continuously optimize its performance and functions according to the actual needs of users. Through this closed-loop optimization mechanism, the system can continuously improve the accuracy of its analysis and suggestions, and provide users with more accurate and efficient database performance optimization services.

[0110] The embodiment of the present invention further provides a distributed database model generation and optimization device, the device comprising:

[0111] The information processing module 11 is used to obtain DDL information and log files, analyze and process the DDL information and log files respectively, and obtain KV format DDL data and KV log information;

[0112] A model generation module 12 is used to read the KV format DDL data into a Transfrom model, and parse the KV format DDL data through the Transfrom model to obtain a basic physical model diagram;

[0113] The log analysis and optimization module 13 is used to optimize and analyze the KV log information to obtain business pressure information and optimization suggestions;

[0114] The model optimization module 14 is used to aggregate the basic physical model diagram, business pressure information and optimization suggestions to generate an optimized model diagram.

[0115] like Figure 3 As shown, the information processing module 11, the model generation module 12, the log analysis and optimization module 13 and the model optimization module 14 are connected in sequence. The module referred to in the present invention refers to a series of computer program instruction segments that can complete specific functions, which is more suitable for distributed database model generation and optimization method execution process than the program. For the specific implementation method of each module, please refer to the corresponding method embodiment above, which will not be repeated here.

[0116] In one embodiment, the information processing module 11 includes:

[0117] A DDL information processing unit, used to obtain the DDL information and perform data cleaning processing on the DDL information through a function algorithm;

[0118] The format conversion unit is used to convert the DDL information after data cleaning into the KV format to obtain the KV format DDL data.

[0119] In one embodiment, the information processing module 11 includes:

[0120] A log information unit, used to obtain the log information, identify and extract information from the log information, and obtain relevant entries of the SQL statement;

[0121] The information filtering unit is used to filter the relevant items of the SQL statement through the keywords of the SQL statement to obtain KV log information.

[0122] In one embodiment, the model generation module 12 includes:

[0123] An information input unit, used for inputting the KV format DDL data into a pre-trained Transfrom model;

[0124] A K value processing unit, used for processing the K value by the Transfrom model to generate a block graph;

[0125] A V value processing unit, used for processing the V value by the Transfrom model to generate an array;

[0126] The model diagram generating unit is used to obtain a basic physical model diagram according to the block graphics and array.

[0127] In one embodiment, the log analysis and optimization module 13 includes:

[0128] A log information analysis unit, used to analyze the KV log information through an analysis algorithm to generate a unique identifier of a log file;

[0129] The identifier matching unit is used to match the SQL statement with the unique identifier according to the table name to obtain the execution time and execution count;

[0130] A business pressure information unit, used to divide the table pressure level according to the execution time and the execution number, and generate business pressure information through the table and the table pressure level;

[0131] The optimization suggestion unit is used to obtain optimization suggestions according to the table pressure level, unique identifier and business pressure information.

[0132] In one embodiment, the model optimization module 14 includes:

[0133] An information aggregation unit, used to aggregate the basic physical model diagram, business pressure information and optimization suggestions;

[0134] An information labeling unit is used to label the basic physical model diagram according to the summarized information to generate an optimized model diagram.

[0135] In one embodiment, the distributed database model generation and optimization device further includes an output interaction module:

[0136] A model diagram display unit, used to output the optimized model diagram to a user interface and display it;

[0137] The suggestion collection unit is used to collect users' suggestions on optimizing the model graph and send the suggestions to the log optimization module.

[0138] Another embodiment of the present invention provides a distributed database model generation and optimization system, the system comprising at least one processor; and

[0139] a memory communicatively connected to the at least one processor; wherein,

[0140] The memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to perform the distributed database model generation and optimization method.

[0141] One or more processors 110 and memory 120, Figure 4 A processor 110 is used as an example for description. The processor 110 and the memory 120 may be connected via a bus or other means. Figure 4 The example of connecting through bus is taken in the following.

[0142] The processor 110 is used to complete various control logics of the control system 10. It can be a general-purpose processor, a digital signal processor (DSP), an application-specific integrated circuit (ASIC), a field programmable gate array (FPGA), a single-chip microcomputer, an ARM (Acorn RISC Machine) or other programmable logic device, a discrete gate or transistor logic, a discrete hardware component, or any combination of these components. In addition, the processor 110 can also be any traditional processor, microprocessor or state machine. The processor 110 can also be implemented as a combination of computing devices, for example, a combination of a DSP and a microprocessor, a plurality of microprocessors, one or more microprocessors combined with a DSP and / or any other such configuration.

[0143] The memory 120, as a non-volatile computer-readable storage medium, can be used to store non-volatile software programs, non-volatile computer executable programs and modules, such as program instructions corresponding to the distributed database model generation and optimization method in the embodiment of the present invention. The processor 110 executes various functional applications and data processing of the control system 10 by running the non-volatile software programs, instructions and units stored in the memory 120, that is, implements the distributed database model generation and optimization method in the above method embodiment.

[0144] The memory 120 may include a program storage area and a data storage area, wherein the program storage area may store an operating system and an application required by at least one function; the data storage area may store data created according to the use of the control system 10, etc. In addition, the memory 120 may include a high-speed random access memory, and may also include a non-volatile memory, such as at least one disk storage device, a flash memory device, or other non-volatile solid-state storage device. In some embodiments, the memory 120 may optionally include a memory remotely arranged relative to the processor 110, and these remote memories may be connected to the control system 10 via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0145] One or more units are stored in the memory 120, and when executed by one or more processors 110, the distributed database model generation and optimization method in any of the above method embodiments is executed, for example, the above described Figure 1 The method comprises steps S100 to S400.

[0146] An embodiment of the present invention provides a non-volatile computer-readable storage medium, wherein the computer-readable storage medium stores computer-executable instructions, and the computer-executable instructions are executed by one or more processors, for example, to execute the above-described Figure 1 The method comprises steps S100 to S400.

[0147] As an example, non-volatile storage media can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable ROM (EEPROM), or flash memory. Volatile memory can include random access memory (RAM) as external cache memory. By way of illustration and not limitation, RAM can be obtained in many forms such as synchronous RAM (SRAM), dynamic RAM, (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), Synchlink DRAM (SLDRAM), and direct Rambus RAM (DRRAM). The disclosed memory components or memories of the operating environment described herein are intended to include one or more of these and / or any other suitable types of memory.

[0148] In summary, the present invention discloses a distributed database model generation and optimization method, device, system and medium, including: obtaining metadata information of data nodes, and sending the metadata information to a monitoring module; the monitoring module sends the path information in the metadata information to a file detection module; the file detection module detects whether there is a change operation on the path information, and if there is a change operation on the path information, the operation information of the path information is reported to the monitoring module; the monitoring module determines whether the operation information is an abnormal operation, and implements a fault handling strategy when the change operation is an abnormal operation. The present invention monitors the change operation information of the data nodes, and performs real-time fault handling operations when an abnormal operation occurs, thereby ensuring the stable operation of the database system.

[0149] Of course, those skilled in the art can understand that all or part of the processes in the above-mentioned embodiments can be implemented by instructing related hardware (such as a processor, a controller, etc.) through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium, and the computer program can include the processes of the above-mentioned method embodiments when executed. The storage medium can be a memory, a disk, a floppy disk, a flash memory, an optical storage device, etc.

[0150] It should be understood that the application of the present invention is not limited to the above examples. For ordinary technicians in this field, improvements or changes can be made based on the above description. All these improvements and changes should fall within the scope of protection of the claims attached to the present invention.

Claims

1. A distributed database model generation and optimization method, characterized in that: The steps include: Obtaining DDL information and log files, analyzing and processing the DDL information and log files respectively, to obtain KV format DDL data and KV log information; The KV format DDL data is read into the Transfrom model, and the KV format DDL data is parsed by the Transfrom model to obtain a basic physical model diagram; Optimize and analyze the KV log information to obtain business pressure information and optimization suggestions; The basic physical model diagram, business pressure information and optimization suggestions are summarized to generate an optimized model diagram.

2. The distributed database model generation and optimization method according to claim 1 is characterized in that: The acquiring of DDL information and analyzing and processing the DDL information to obtain DDL data in KV format include: Acquire the DDL information, and perform data cleaning processing on the DDL information through a function algorithm; The DDL information after data cleaning is converted into KV format to obtain KV format DDL data.

3. The distributed database model generation and optimization method according to claim 1 is characterized in that: The obtaining of the log file and analyzing and processing the log file to obtain KV log information includes: Acquire the log information, perform identification and information extraction processing on the log information, and obtain relevant entries of the SQL statement; The relevant items of the SQL statement are filtered by the keyword of the SQL statement to obtain KV log information.

4. The distributed database model generation and optimization method according to claim 1 is characterized in that: The step of reading the KV format DDL data into the Transfrom model and parsing the KV format DDL data through the Transfrom model to obtain a basic physical model diagram includes: Input the KV format DDL data into the pre-trained Transfrom model; The Transfrom model processes the K value to generate a block graph; The Transfrom model processes the V value to generate an array; A basic physical model diagram is obtained according to the block graphics and array.

5. The distributed database model generation and optimization method according to claim 1 is characterized in that: The optimizing and analyzing the KV log information to obtain business pressure information and optimization suggestions includes: Analyze the KV log information through an analysis algorithm to generate a unique identifier for the log file; Match the SQL statement with the unique identifier according to the table name to obtain the execution time and execution count; Divide the table pressure level according to the execution time and execution number, and generate business pressure information through the table and the table pressure level; Optimization suggestions are obtained based on the table pressure level, unique identifier and business pressure information.

6. The distributed database model generation and optimization method according to claim 1 is characterized in that: The basic physical model diagram, business pressure information and optimization suggestions are aggregated to generate an optimized model diagram, including: Summarize the basic physical model diagram, business pressure information and optimization suggestions; The basic physical model diagram is annotated according to the summarized information to generate an optimized model diagram.

7. The distributed database model generation and optimization method according to claim 1 is characterized in that: After the basic physical model diagram, business pressure information and optimization suggestions are aggregated to generate an optimized model diagram, the following steps are further included: Outputting the optimization model diagram to a user interface and displaying it; Collect user suggestions for optimizing the model graph and send the suggestions to the log optimization module.

8. A distributed database model generation and optimization device, characterized in that: The device comprises: An information processing module is used to obtain DDL information and log files, analyze and process the DDL information and log files respectively, and obtain KV format DDL data and KV log information; A model generation module, used for reading the KV format DDL data into a Transfrom model, parsing the KV format DDL data through the Transfrom model, and obtaining a basic physical model diagram; A log analysis and optimization module is used to optimize and analyze the KV log information to obtain business pressure information and optimization suggestions; The model optimization module is used to summarize the basic physical model diagram, business pressure information and optimization suggestions to generate an optimized model diagram.

9. A distributed database model generation and optimization system, characterized in that: The system includes at least one processor; and, 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, and the instructions are executed by the at least one processor so that the at least one processor can execute the distributed database model generation and optimization method described in any one of claims 1-7.

10. A non-volatile computer-readable storage medium, characterized in that: The non-volatile computer-readable storage medium stores computer-executable instructions, which, when executed by one or more processors, enable the one or more processors to execute the distributed database model generation and optimization method described in any one of claims 1-7.

Images