SQL Audit Method, Device and Computer Equipment Based on Distributed Database

Abstract

This application relates to an SQL auditing method, device, and computer device based on a distributed database. The method includes: in response to an SQL request, obtaining a system resource data set, a database metric data set, and an SQL execution plan data set of each computing node; determining the SQL execution duration of each computing node based on the SQL auditing model configured for each computing node, as well as the system resource data set, the database metric data set, and the SQL execution plan data set of each computing node; and determining an audit result based on the SQL execution duration of each computing node. This method is applicable to a distributed database, can achieve real-time auditing, and has high reliability and accuracy in auditing.

Description

Technical Field

[0001] This application relates to the field of distributed databases, and particularly to an SQL auditing method, apparatus, and computer device based on a distributed database. Background Art

[0002] Structured Query Language (SQL) is a database query and programming language used to access data and query, update, and manage relational database systems. If there is SQL with performance issues in the system, it will affect the performance of the database. SQL auditing can detect SQL with performance issues.

[0003] Existing SQL auditing includes pre-audit and post-audit; pre-audit is to verify the correctness and standardization of SQL commands based on existing audit rules before the SQL is released, or to discover SQL performance issues through testing during the development and testing phases; post-audit is to analyze the SQL execution result data and audit the SQL through the execution result data. For a single database, the same operation usually produces the same result, and the existing SQL auditing methods are applicable to single databases.

[0004] However, for a distributed database involving multiple nodes and networks, in the case of increasing data or pressure, sharding splitting, merging, and scheduling are used to ensure storage balance and access load balance among each node. The execution performance of the same SQL statement varies greatly in different situations. The existing SQL auditing methods only rely on the specified audit rules and are not applicable to distributed databases. Summary of the Invention

[0005] Based on this, in view of the above technical problems, it is necessary to provide an SQL auditing method, apparatus, and computer device based on a distributed database that is applicable to distributed databases, can achieve real-time auditing, and has high reliability and accuracy in auditing.

[0006] An SQL auditing method based on a distributed database, the method comprising:

[0007] In response to an SQL request, obtaining a system resource data set, a database metric data set, and an SQL execution plan data set for each computing node;

[0008] Based on the SQL auditing model configured for each computing node, as well as the system resource data set, database metric data set, and SQL execution plan data set of each computing node, determining the SQL execution duration for each computing node;

[0009] Determining an audit result based on the SQL execution duration of each computing node.

[0010] In one embodiment, the system resource data set includes: the number of system processes, network load, and disk space usage rate;

[0011] The database metric data set includes: cache usage rate and number of queries per second;

[0012] The SQL execution plan data set includes: the number of rows affected by SQL, the number of SQL shards, and the SQL execution cost.

[0013] In one embodiment, determining the SQL execution duration of each computing node based on the SQL audit model configured for each computing node, as well as the system resource data set, database metric data set, and SQL execution plan data set of each computing node, includes:

[0014] Preprocess the system resource data set, database metric data set, and SQL execution plan data set of each computing node to obtain the preprocessed data set of each computing node;

[0015] Input the preprocessed data set of each computing node into the SQL audit model configured for each computing node respectively to obtain the SQL execution duration of each computing node.

[0016] In one embodiment, the preprocessing of the system resource data set, database metric data set, and SQL execution plan data set of each computing node to obtain the preprocessed data set of each computing node includes:

[0017] Clear the abnormal data in the system resource data set, database metric data set, and SQL execution plan data set of each computing node to obtain the first data set of each computing node;

[0018] Determine the weakly correlated data in the first data set of each computing node to obtain the second data set of each computing node;

[0019] Fill in the missing data in the second data set of each computing node to obtain the preprocessed data set of each computing node.

[0020] In one embodiment, the SQL audit model includes: a first-layer classification model and a second-layer regression model. The first-layer classification model includes: a lightweight gradient boosting model, a random forest model, an extreme tree model, and an adaptive boosting model in parallel; the inputting the preprocessed data set of each computing node into the SQL audit model configured for each computing node respectively to obtain the SQL execution duration of each computing node includes:

[0021] For any computing node, input the preprocessed data set of the any computing node into the lightweight gradient boosting model, random forest model, extreme tree model, and adaptive boosting model of the any computing node respectively to obtain the first feature, second feature, third feature, and fourth feature of the any computing node;

[0022] Based on the first feature, second feature, third feature, and fourth feature of the any computing node, and the second-layer regression model of the any computing node, determine the SQL execution duration of the any computing node.

[0023] In one embodiment, the SQL auditing model is a model obtained by training an integrated algorithm model based on a historical data set, and the historical data set includes: a historical system resource data set, a historical database metric data set, a historical SQL execution plan data set, and a historical slow log data set of multiple historical SQL requests.

[0024] In one embodiment, the determining the audit result based on the SQL execution duration of each computing node includes:

[0025] If the SQL execution duration of each computing node is less than the first threshold, determine that the audit result is passed;

[0026] If the SQL execution duration of any one of the computing nodes is greater than or equal to the first threshold, determine that the audit result is not passed.

[0027] An SQL auditing device based on a distributed database, the device includes:

[0028] A data acquisition module, configured to obtain a system resource data set, a database metric data set, and an SQL execution plan data set of each computing node in response to an SQL request;

[0029] An SQL execution duration prediction module, configured to determine the SQL execution duration of each computing node based on the SQL auditing model configured for each computing node, and the system resource data set, the database metric data set, and the SQL execution plan data set of each computing node;

[0030] An audit result determination model, configured to determine an audit result based on the SQL execution duration of each computing node

[0031] A computer device includes a memory and a processor, the memory stores a computer program, and when the processor executes the computer program, the following steps are implemented:

[0032] In response to an SQL request, obtain a system resource data set, a database metric data set, and an SQL execution plan data set of each computing node;

[0033] Determine the SQL execution duration of each computing node based on the SQL audit model configured for each computing node, as well as the system resource dataset, database metric dataset, and SQL execution plan dataset of each computing node;

[0034] Determine the audit result based on the SQL execution duration of each computing node.

[0035] A computer-readable storage medium, on which a computer program is stored, and when the computer program is executed by a processor, the following steps are implemented:

[0036] In response to an SQL request, obtain the system resource dataset, database metric dataset, and SQL execution plan dataset of each computing node;

[0037] Determine the SQL execution duration of each computing node based on the SQL audit model configured for each computing node, as well as the system resource dataset, database metric dataset, and SQL execution plan dataset of each computing node;

[0038] Determine the audit result based on the SQL execution duration of each computing node.

[0039] The above SQL audit method, device, and computer device based on a distributed database, in response to an SQL request, obtain the system resource dataset, database metric dataset, and SQL execution plan dataset of each computing node, and based on the SQL audit model configured for each computing node, as well as the system resource dataset, database metric dataset, and SQL execution plan dataset of each computing node, obtain the SQL execution duration of each computing node. Since the system resource dataset, database metric dataset, and SQL execution plan dataset are all real-time data when the computing node receives the SQL request, real-time auditing is achieved; predicting the SQL execution duration through the SQL audit model improves the accuracy. Each computing node is configured with an SQL audit model, meeting the requirements of the concurrency of the distributed database. Determining the audit result based on the SQL execution duration of each computing node, that is, the audit result comprehensively considers the SQL execution duration of each computing node, improving the reliability of the audit. Therefore, the above SQL audit method based on a distributed database is applicable to a distributed database, can achieve real-time auditing, and improves the reliability and accuracy of the audit. BRIEF DESCRIPTION OF THE DRAWINGS

[0040] Figure 1 It is a schematic flowchart of an SQL audit method based on a distributed database in an embodiment;

[0041] Figure 2 It is a schematic structural diagram of an SQL audit device based on a distributed database in an embodiment;

[0042] Figure 3 The internal structure diagram of a computer device in an embodiment. Detailed implementation manners

[0043] In order to make the objectives, technical solutions and advantages of the present application clearer and more understandable, the present application will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and are not used to limit the present application.

[0044] The SQL auditing method based on a distributed database provided by the present application can be applied to a server, where a distributed database is configured in the server, and the server can be implemented by an independent server or a server cluster composed of multiple servers.

[0045] In one embodiment, as Figure 1 shown, a SQL auditing method based on a distributed database is provided. In this embodiment, the method is exemplified by being applied to a server. In this embodiment, the method includes the following steps:

[0046] Step 101, in response to a SQL request, obtain the system resource data set, database metric data set, and SQL execution plan data set of each computing node.

[0047] Among them, the SQL request is used to perform operations such as querying, accessing data, and updating. The server receives a distributed task (SQL statement), divides the distributed task into multiple parallel SQL requests, and distributes them to multiple computing nodes for execution. Each computing node is each computing node among the multiple computing nodes that execute a distributed task.

[0048] The system resource data set includes: data of the server system when the computing node receives the SQL request, which is used to reflect the operation of the system; the database metric data set includes: metric data of the database when the computing node receives the SQL request, which is used to reflect the operation of the database; the SQL execution plan data set includes: data corresponding to the execution plan determined based on the SQL request when the computing node receives the SQL request, which is used to reflect the rationality of the SQL execution plan.

[0049] Step 102, based on the SQL auditing model configured for each computing node, as well as the system resource data set, database metric data set, and SQL execution plan data set of each computing node, determine the SQL execution duration of each computing node.

[0050] Among them, the SQL audit model is used to predict the SQL execution duration according to the data set for each computing node to execute the SQL request, and each computing node of the distributed database is configured with an SQL audit model. The SQL execution duration is used to reflect the performance of the SQL request.

[0051] Specifically, for any computing node that executes the SQL request, after receiving the SQL request, the computing node generates a distributed execution plan tree corresponding to the SQL request and determines the instance information of the connection, obtains the system resource data set, database metric data set, and SQL execution plan data set of the any computing node, and inputs the system resource data set, database metric data set, and SQL execution plan data set of the any computing node into the SQL audit model configured by the any computing node to obtain the SQL execution duration of each computing node.

[0052] Step 103, determine the audit result based on the SQL execution duration of each computing node.

[0053] Among them, the audit result includes the result of whether to execute the SQL request and the optimization suggestion for the SQL request.

[0054] Specifically, set the corresponding relationship, and determine the audit result corresponding to the SQL execution duration through the corresponding relationship. The audit result includes: passed or not passed. If the SQL execution duration meets the requirements, it is determined that the audit result is passed; if the SQL execution duration does not meet the requirements, it is determined that the audit result is not passed.

[0055] In the above SQL audit method based on a distributed database, in response to an SQL request, obtain the system resource data set, database metric data set, and SQL execution plan data set of each computing node, and based on the SQL audit model configured for each computing node, as well as the system resource data set, database metric data set, and SQL execution plan data set of each computing node, obtain the SQL execution duration of each computing node. Since the system resource data set, database metric data set, and SQL execution plan data set are all real-time data when the computing node receives the SQL request, real-time auditing is achieved; the SQL execution duration is predicted through the SQL audit model, improving the accuracy. Each computing node is configured with an SQL audit model, meeting the requirements of the concurrency of the distributed database. The audit result is determined based on the SQL execution duration of each computing node, that is, the audit result comprehensively considers the SQL execution duration of each computing node, improving the reliability of the audit. Therefore, the above SQL audit method based on a distributed database is applicable to a distributed database, can achieve real-time auditing, and improves the reliability and accuracy of the audit.

[0056] In one embodiment, obtaining the system resource data set, database metric data set, and SQL execution plan data set for each computing node in step 101 means that for any one of the multiple computing nodes involved in a distributed task, obtaining the system resource data set, database metric data set, and SQL execution plan data set when the any one of the computing nodes receives an SQL request.

[0057] The system resource data set includes: the number of system processes, network load, and disk space utilization rate.

[0058] Among them, when receiving a distributed task, the system will create several processes to handle the distributed task. The number of system processes reflects the process situation created by the system when the computing node receives an SQL request. The network load reflects the network occupancy situation when the computing node receives an SQL request. The disk space utilization rate reflects the disk occupancy situation when the computing node receives an SQL request.

[0059] In another implementation, the system resource data set further includes: memory utilization rate, I / O load, CPU utilization rate, and disk mount directory file system utilization rate; the memory utilization rate, I / O load, and disk mount directory file system utilization rate reflect the usage of system resources from different perspectives when the computing node receives an SQL request.

[0060] In another implementation, the system resource data set further includes: data such as the number of nodes, status, and storage capacity of each instance.

[0061] The system resource data set may include a combination of several system resource data listed above. The system resource data set is not limited to the data listed above, and other system resource data that have a great impact on the SQL execution duration can also be selected according to the system characteristics.

[0062] In one embodiment, the database metric data set includes: cache utilization rate and queries per second. Among them, the cache utilization rate can reflect whether the allocation of SQL queries is reasonable, and the queries per second (QPS) can reflect the running state of the database.

[0063] In another implementation, the database metric data set further includes: cache hit rate, transactions per second (TPS), and cache hit rate. The database metric data set further includes: table data volume, number of shards, number of read / write disks, current maximum connection number, etc.

[0064] The database metric data set may include a combination of several database metric data listed above. The database metric data set is not limited to the data listed above, and other database metric data that have a great impact on the SQL execution duration can also be selected according to the system characteristics.

[0065] In one embodiment, the SQL execution plan data set includes: the number of rows affected by the SQL, the number of SQL shards, and the SQL execution cost.

[0066] Among them, the number of rows affected by the SQL, the number of SQL shards, and the SQL execution cost can reflect the efficiency of SQL execution.

[0067] The SQL execution cost = IO cost + CPU cost + network communication cost; where the IO cost calculation is the cost generated by data reading and writing; the CPU cost is the cost generated by data memory within the node; the network communication cost is the communication cost generated by data transmission between nodes in the computer network, including the time for initializing communication between nodes and the time cost of data transmission. Most existing databases come with a cost calculation model, and when obtaining the execution plan, the SQL execution cost can be directly obtained through the cost calculation model.

[0068] In another implementation, the SQL execution plan data set further includes: the scale of the tables involved in the query, the SQL access type, and the SQL operator type.

[0069] The SQL execution plan data set may include a combination of several SQL execution plan data listed above. The SQL execution plan data set is not limited to the data listed above, and other SQL execution plan data that have a great impact on the SQL execution duration can also be selected according to the system characteristics.

[0070] In another embodiment, step 102 includes:

[0071] Step 201, preprocess the system resource data set, the database metric data set, and the SQL execution plan data set of each computing node to obtain the preprocessed data set of each computing node.

[0072] Among them, the preprocessing includes operations such as data cleaning, data filling, and feature field analysis. Specifically, if there are strongly correlated data in the above data sets, the strongly correlated data is removed; if there are outliers in the above data sets, the abnormal data is cleared; if there is missing data in the above data sets, the missing data needs to be filled.

[0073] Specifically, step 201 includes:

[0074] Step 301, clear the abnormal data in the system resource dataset, database metric dataset, and SQL execution plan dataset of each computing node to obtain the first dataset of each computing node.

[0075] Among them, the abnormal data is also outlier data, and abnormal data is significantly deviated data.

[0076] Specifically, through statistical analysis, abnormal data can be determined. For example, the abnormal data in the system resource dataset, database metric dataset, and SQL execution plan dataset of each computing node is determined by box plot analysis, or the maximum and minimum values are set to determine the abnormal data. The determined abnormal data is cleared to obtain the first dataset of each computing node. The first dataset includes: the system resource dataset, database metric dataset, and SQL execution plan dataset of each computing node after clearing the abnormal data.

[0077] Step 302, determine the weakly correlated data in the first dataset of each computing node to obtain the second dataset of each computing node.

[0078] Among them, the weakly correlated data is the data with weak correlation with other data in the first dataset. The second dataset includes the weakly correlated data in the first dataset.

[0079] Specifically, by drawing the scatter matrix of the first dataset, the correlation coefficients between multiple data in the first dataset are determined, and the data with a correlation coefficient less than the preset value is used as the weakly correlated data. The preset value can be set according to requirements. For example, the preset exponential value can be set to 0.9, or the preset value can be set to 0.8.

[0080] Step 303, fill in the missing data in the second dataset of each computing node to obtain the preprocessed dataset of each computing node.

[0081] Specifically, when obtaining the system resource dataset, database metric dataset, and SQL execution plan dataset of each computing node, some data may not be obtained. The missing data includes the data that is not obtained. The missing data belongs to numerical data, or character data, or categorical data; if the missing data belongs to numerical data, the mean filling method can be used for filling; if the missing data belongs to character data, or categorical data, the mode filling method is used for filling.

[0082] In one implementation, all numerical data in the preprocessed dataset is normalized, and the preprocessed dataset is replaced with the preprocessed dataset after normalization.

[0083] Step 202: Input the preprocessed data set of each computing node into the SQL audit model configured for each computing node respectively, and obtain the SQL execution duration of each computing node.

[0084] Among them, each computing node is configured with an SQL audit model, which can meet the requirements of the concurrency of the distributed database.

[0085] To ensure the accuracy of the model in the case of multi-dimensional feature data and avoid the adverse effects of random factors, the SQL audit model is implemented by combining multiple algorithm models using the Stacking integration algorithm. The SQL audit model includes: a first-layer classification model and a second-layer regression model. The first-layer classification model includes: a Light Gradient Boosting model (LightGBM model), a Random Forest model (RandomForest model), an Extra Trees model, and an Adaptive Boosting model (Adaboost model); to avoid overfitting, the second-layer regression model is a Logistic Regression model (logistics regression model). The Light Gradient Boosting model, Random Forest model, Extra Trees model, and Adaptive Boosting model all belong to strong classification models. The first-layer classification model is used to extract the features of the preprocessed data set, and the features extracted by the first-layer classification model are input into the second-layer regression model to obtain the SQL execution duration.

[0086] Specifically, Step 202 includes:

[0087] Step 401: For any computing node, input the preprocessed data set of the any computing node into the Light Gradient Boosting model, Random Forest model, Extra Trees model, and Adaptive Boosting model of the any computing node respectively, and obtain the first feature, second feature, third feature, and fourth feature of the any computing node.

[0088] Taking one computing node as an example for illustration, for computing node f1, the preprocessed data set of f1 is A. Input A into the SQL audit model s1 configured for f1. Specifically: Input A into the Light Gradient Boosting model, Random Forest model, Extra Trees model, and Adaptive Boosting model of s1 respectively. Output the first feature of A through the Light Gradient Boosting model of s1, output the second feature of A through the Random Forest model of s1, output the third feature of A through the Extra Trees model of s1, and output the fourth feature of A through the Adaptive Boosting model of s1. Each computing node executes according to the above process to obtain the first feature, second feature, third feature, and fourth feature of each computing node.

[0089] Step 402: Determine the SQL execution duration of any one of the computing nodes based on the first feature, second feature, third feature, and fourth feature of any one of the computing nodes, and the second-layer regression model of any one of the computing nodes.

[0090] Specifically, splice the first feature, second feature, third feature, and fourth feature of any one of the computing nodes to obtain a splicing result, input the splicing result into the second-layer regression model of any one of the nodes, and obtain the SQL execution duration of any one of the computing nodes through the second-layer regression model of any one of the nodes.

[0091] Taking a computing node as an example for illustration, for computing node f1, splice the first feature, second feature, third feature, and fourth feature of f1 to obtain a splicing result p1, and input p1 into the second-layer regression model of computing node f1 to obtain the SQL execution duration T1 of f1.

[0092] The SQL audit model in this application is obtained by training an integrated algorithm model based on a historical data set. The process of training the integrated algorithm model based on the historical data set to obtain the SQL audit model will be introduced later.

[0093] In one embodiment, step 103 includes:

[0094] Step 501: If the SQL execution duration of each computing node is less than the first threshold, determine that the audit result is passed;

[0095] Step 502: If the SQL execution duration of any one of the computing nodes is greater than or equal to the first threshold, determine that the audit result is not passed.

[0096] Among them, the first threshold can be set according to the system characteristics. The audit result being passed means that the SQL request can be executed, and the audit result being not passed means that the SQL request is not executed.

[0097] Specifically, if the SQL execution duration of any one of the computing nodes is greater than or equal to the first threshold, it can be further determined whether the SQL execution duration of each computing node is less than the second threshold, where the second threshold is greater than the first threshold.

[0098] If the SQL execution duration of each computing node is less than the second threshold, it is recommended to optimize the SQL statement corresponding to the SQL request. Recommending to optimize the SQL statement corresponding to the SQL request means that the SQL statement will not cause serious problems, but can still be further optimized; if the SQL execution duration of any one of the computing nodes is greater than the second threshold, it is further determined whether the SQL execution duration of each computing node is greater than the third threshold, where the third threshold is greater than the second threshold.

[0099] If the SQL execution duration of each computing node is less than the third threshold, manual review is performed. If the SQL execution duration of any computing node is greater than or equal to the third threshold, it is directly rolled back. The SQL request is not executed, and entering manual review will trigger manual review by the Database Administrator (DBA); direct rollback means that the SQL statement corresponding to the SQL request belongs to the SQL statement with the highest risk.

[0100] For example, record the SQL execution duration of each computing node as T = {T1, T2, …, Ti, …, Tn}. If T1, T2, …, Ti, …, Tn are all less than the first threshold, the audit result is determined to be passed; if any Ti in T1, T2, …, Ti, …, Tn is greater than the first threshold and T1, T2, …, Ti, …, Tn are all less than the second threshold, the audit result is determined to be not passed, and it is recommended to optimize the SQL statement; if any Ti in T1, T2, …, Ti, …, Tn is greater than the second threshold and T1, T2, …, Ti, …, Tn are all less than the third threshold, the audit result is determined to be not passed, and manual review is entered; if any Ti in T1, T2, …, Ti, …, Tn is greater than the third threshold, the audit result is determined to be not passed, and direct rollback processing is performed.

[0101] In another implementation, each time an SQL statement is received, the SQL audit method based on the distributed database is triggered, and the audit results of the same SQL statement do not affect each other.

[0102] Next, the process of training an integrated algorithm model based on a historical dataset to obtain an SQL audit model is introduced.

[0103] The SQL audit model is a model obtained by training an integrated algorithm model based on a historical dataset. The historical dataset includes: historical system resource datasets, historical database metric datasets, historical SQL execution plan datasets, and historical slow log datasets of multiple historical SQL requests.

[0104] Among them, the historical SQL requests are the actually processed SQL requests, and the historical system resource datasets, historical database metric datasets, historical SQL execution plan datasets, and historical slow log datasets of each historical SQL request are obtained.

[0105] The historical system resource dataset includes: historical system process number, historical network load, and historical disk space usage rate. It can also include: historical memory usage rate, historical I / O load, historical CPU usage rate, historical disk mount directory file system usage rate, the number of nodes and status of each historical instance, and several data in historical storage capacity.

[0106] The historical database metric data set includes: historical cache usage rate and historical QPS, and may also include: several data among historical cache hit rate, historical TPS, and historical cache hit rate.

[0107] The historical SQL execution plan data set includes: historical SQL affected row count, historical SQL shard count, and historical SQL execution cost, and may also include: several data among the scale of tables involved in historical queries, historical SQL access type, and historical SQL operator type.

[0108] The historical slow log data set includes: historical SQL execution duration, and may also include: several data among historical SQL execution result, historical SQL query optimization time, historical SQL statement retry count, historical SQL waiting time, historical SQL maximum memory usage during execution, and historical SQL maximum hard disk space usage during execution.

[0109] Specifically, the historical SQL execution result includes execution success or failure. The historical SQL execution result of successful execution is represented by the value "1", and the historical SQL execution result of failed execution is represented by the value "0". The historical data set is divided into a training data set and a test data set, which can be divided in a ratio of 8:2; the training data set is divided into a positive sample set and a negative sample set. The historical SQL execution duration in the positive sample set is less than a preset reference value, and the historical SQL execution duration in the negative sample set is greater than or equal to the preset reference value. And the number of the positive sample set and the negative sample set maintains a certain ratio to ensure the balance of sample data, and the ratio can be 1:1.

[0110] Preprocess the training data set to obtain a preprocessed training data set. The process of preprocessing the training data includes operations such as data cleaning, data filling, and feature field analysis. If there are strongly correlated data in the training data set, the strongly correlated data is removed; if there are outliers in the training data set, the abnormal data is cleared; if there is missing data in the training data set, the missing data needs to be filled. The process of preprocessing the training data set is the same as the preprocessing process in step 201. Therefore, the process of preprocessing the training data set can refer to the description in step 201.

[0111] The integrated algorithm model is a Stacking integrated algorithm model, and the integrated algorithm model includes: a first-layer initial model and a second-layer initial model. The first-layer initial model includes: an initial LightGBM model, an initial RandomForest model, an initial ExtraTrees model, and an initial Adaboost model; the second-layer initial model includes an initial logistics regression model.

[0112] The model result of the integrated algorithm model is the same as the model structure of the SQL audit model.

[0113] The training preprocessing data set is divided into 5 folds, and the initial LightGBM model, the initial RandomForest model, the initial ExtraTrees model, and the initial Adaboost model are each trained 5 times. Each time, 1 / 5 of the samples (1 / 5 of each fold) are reserved for testing after training. After training is completed, the test data is predicted. Each model in the first-layer initial model obtains 5 prediction results, and the 5 prediction results are averaged. The averages of each model are concatenated to obtain the training concatenated data, and the initial logistics regression model is trained through the training concatenated data. After training is completed, the SQL audit model is obtained.

[0114] In this embodiment, each computing node is configured with an SQL audit model, which meets the requirements of the concurrency of the distributed database; the system resource data set, the database metric data set, and the SQL execution plan data set are all real-time data when the computing node receives an SQL request, focusing on the application usage link, and real-time auditing is realized based on the real-time data of each computing node and the SQL audit model configured for each computing node, making up for the deficiencies of the current SQL audit system; the system resource data set, the database metric data set, and the SQL execution plan data set of each computing node are preprocessed, effectively reducing invalid data and improving the execution performance of SQL; the SQL audit model is implemented through the Stacking model, reducing the adverse effects of random factors and improving the model accuracy. The audit result comprehensively considers the SQL execution duration of each computing node, improving the reliability of the audit.

[0115] In one embodiment, as Figure 2 shown, a SQL audit device based on a distributed database is provided, including:

[0116] A data acquisition module, configured to acquire a system resource data set, a database metric data set, and an SQL execution plan data set of each computing node in response to an SQL request;

[0117] The SQL execution duration prediction module is used to determine the SQL execution duration of each computing node based on the SQL audit model configured for each computing node, as well as the system resource dataset, database metric dataset, and SQL execution plan dataset of each computing node.

[0118] The audit result determination model is used to determine the audit result based on the SQL execution duration of each computing node.

[0119] For the specific limitations of the SQL audit device based on a distributed database, reference can be made to the limitations of the SQL audit method based on a distributed database in the above text, which will not be elaborated here. Each module in the above SQL audit device based on a distributed database can be implemented in whole or in part through software, hardware, and their combination. The above modules can be embedded in the processor of the computer device in hardware form or be independent of it, or be stored in the memory of the computer device in software form, so that the processor can call and execute the operations corresponding to the above modules.

[0120] In one embodiment, a computer device is provided. The computer device can be a server, and its internal structure diagram can be as Figure 3 shown. The computer device includes a processor, a memory, and a network interface connected through a system bus. Among them, the processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program, and a database. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The network interface of the computer device is used to communicate with an external terminal through a network connection. When the computer program is executed by the processor, it implements a method for an SQL audit device based on a distributed database.

[0121] Those skilled in the art can understand that Figure 3 the structure shown in

[0122] In one embodiment, a computer device is provided, including a memory and a processor. A computer program is stored in the memory. When the processor executes the computer program, the following steps are implemented:

[0123] In response to an SQL request, obtain the system resource dataset, database metric dataset, and SQL execution plan dataset of each computing node;

[0124] Determine the SQL execution duration of each computing node based on the SQL audit model configured for each computing node, as well as the system resource dataset, database metric dataset, and SQL execution plan dataset of each computing node.

[0125] Determine the audit result based on the SQL execution duration of each computing node.

[0126] In one embodiment, a computer-readable storage medium is provided, on which a computer program is stored. When the computer program is executed by a processor, the following steps are implemented:

[0127] In response to an SQL request, obtain the system resource dataset, database metric dataset, and SQL execution plan dataset of each computing node;

[0128] Determine the SQL execution duration of each computing node based on the SQL audit model configured for each computing node, as well as the system resource dataset, database metric dataset, and SQL execution plan dataset of each computing node.

[0129] Determine the audit result based on the SQL execution duration of each computing node.

[0130] Those of ordinary skill in the art can understand that all or part of the processes of implementing the methods in the above embodiments can be completed by instructing relevant hardware through a computer program. The computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above methods. Among them, any reference to a memory, storage, database, or other medium used in the embodiments provided in the present application can include at least one of non-volatile and volatile memories. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, or optical memory, etc. Volatile memory can include random access memory (RAM) or external cache memory. By way of illustration and not limitation, RAM can be in various forms, such as static random access memory (SRAM) or dynamic random access memory (DRAM), etc.

[0131] The technical features of the above embodiments can be combined arbitrarily. For the sake of brevity of description, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, it should be considered as the scope described in this specification.

[0132] The above-described embodiments merely represent several implementation manners of the present application. The description thereof is relatively specific and detailed, but it should not be construed as a limitation on the scope of the invention patent. It should be noted that for those of ordinary skill in the art, without departing from the concept of the present application, several modifications and improvements can still be made, and these all fall within the protection scope of the present application. Therefore, the protection scope of the patent of the present application shall be subject to the appended claims.

Claims

1. A SQL auditing method based on a distributed database, characterized in that The method includes: In response to an SQL request, obtaining a system resource dataset, a database metric dataset, and an SQL execution plan dataset for each computing node, where the system resource dataset is used to reflect the running status of the system, and the database metric dataset is used to reflect the running status of the database; Preprocessing the system resource dataset, the database metric dataset, and the SQL execution plan dataset for each computing node to obtain a preprocessing dataset for each computing node; For any computing node, inputting the preprocessing dataset of the any computing node into a lightweight gradient boosting model, a random forest model, an extreme tree model, and an adaptive boosting model in the SQL audit model of the any computing node to obtain a first feature, a second feature, a third feature, and a fourth feature of the any computing node, where the lightweight gradient boosting model, the random forest model, the extreme tree model, and the adaptive boosting model belong to strong classification models; Concatenating the first feature, the second feature, the third feature, and the fourth feature of the any computing node, and inputting the concatenation result into a second-layer logistic regression model in the SQL audit model of the any computing node to determine the SQL execution duration of the any computing node; Determining an audit result based on the SQL execution duration of each computing node.

2. The method according to claim 1, wherein The system resource dataset includes: the number of system processes, network load, and disk space utilization rate; The database metric dataset includes: cache utilization rate and number of queries per second; The SQL execution plan dataset includes: the number of rows affected by the SQL, the number of SQL shards, and the SQL execution cost.

3. The method according to claim 1, characterized in that, The preprocessing the system resource dataset, the database metric dataset, and the SQL execution plan dataset for each computing node to obtain a preprocessing dataset for each computing node includes: Clearing abnormal data in the system resource dataset, the database metric dataset, and the SQL execution plan dataset of each computing node to obtain a first dataset for each computing node; Determining weakly correlated data in the first dataset of each computing node to obtain a second dataset for each computing node; Filling in missing data in the second dataset of each computing node to obtain a preprocessing dataset for each computing node.

4. The method according to claim 1, characterized in that, The SQL audit model is a model obtained by training an ensemble algorithm model based on a historical dataset, where the historical dataset includes: a historical system resource dataset, a historical database metric dataset, a historical SQL execution plan dataset, and a historical slow log dataset of multiple historical SQL requests.

5. The method according to claim 4, characterized in that The ensemble algorithm model is a Stacking ensemble algorithm model, and the ensemble algorithm model includes: a first-layer initial model and a second-layer initial model. The first-layer initial model includes: an initial lightweight gradient boosting model, an initial random forest model, an initial extreme tree model, and an initial adaptive boosting model; the second-layer initial model includes an initial logistic regression model.

6. The method according to claim 4, wherein The historical system resource data set includes: the number of historical system processes, historical network load, and historical disk space utilization rate; the historical database metric data set includes: historical cache utilization rate and historical QPS; the historical SQL execution plan data set includes: the number of rows affected by historical SQL, historical SQL shard count, and historical SQL execution cost; the historical slow log data set includes: historical SQL execution duration.

7. The method according to any one of claims 1 to 4, characterized in that Determining the audit result based on the SQL execution duration of each computing node includes: If the SQL execution duration of each computing node is less than the first threshold, determine that the audit result is passed; if the SQL execution duration of any one computing node is greater than or equal to the first threshold, determine that the audit result is not passed.

8. An SQL auditing device based on a distributed database, characterized in that, The device includes: A data acquisition module, configured to obtain the system resource data set, database metric data set, and SQL execution plan data set of each computing node in response to an SQL request, where the system resource data set is used to reflect the running status of the system, and the database metric data set is used to reflect the running status of the database; An SQL execution duration prediction module, configured to preprocess the system resource data set, database metric data set, and SQL execution plan data set of each computing node to obtain a preprocessed data set for each computing node; for any one computing node, input the preprocessed data set of the any one computing node into the lightweight gradient boosting model, random forest model, extreme tree model, and adaptive boosting model in the SQL audit model of the any one computing node to obtain the first feature, second feature, third feature, and fourth feature of the any one computing node, where the lightweight gradient boosting model, random forest model, extreme tree model, and adaptive boosting model belong to strong classification models; splice the first feature, second feature, third feature, and fourth feature of the any one computing node, and input the splicing result into the second-layer logistic regression model in the SQL audit model of the any one computing node to determine the SQL execution duration of the any one computing node; An audit result determination model, configured to determine the audit result based on the SQL execution duration of each computing node.

9. A computer device, comprising a memory and a processor, the memory storing a computer program, characterized in that, When the processor executes the computer program, it implements the steps of the method according to any one of claims 1 to 7.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by the processor, it implements the steps of the method according to any one of claims 1 to 7.

Images