Distributed database sql auditing and optimization suggestion method and system
By capturing and formatting SQL statements in a distributed database, and utilizing syntax parsing and SOAR optimization suggestions, the problems of high manpower investment and poor stability in distributed database monitoring and optimization are solved. This achieves efficient SQL auditing and optimization, improving system stability and work efficiency.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- FUJIAN FUJITSU COMM SOFTWARE CO LTD
- Filing Date
- 2022-12-13
- Publication Date
- 2026-06-19
AI Technical Summary
In existing technologies, monitoring and optimizing distributed databases requires a significant investment of manpower, and the varying levels of understanding and proficiency among developers regarding the characteristics of distributed databases lead to issues such as non-standard scripts and poor performance, which affect system stability.
SQL statements are captured on the client side and uploaded to the server for formatting. A syntax parser is used to extract key information, and distributed data metadata is combined for audit optimization. SOAR optimization suggestions are introduced to provide SQL statement rewriting and scoring, prevent the execution of statements with low scores, and introduce caching to reduce audit optimization time.
It improves SQL optimization efficiency, avoids problems such as reduced database availability and insufficient concurrency, enhances system stability and work efficiency, and reduces the difficulty of distributed database tuning.
Smart Images
Figure CN115934721B_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the fields of cloud computing and big data technology, and in particular to a method for auditing and optimizing distributed database SQL. Background Technology
[0002] The distributed database TeleDB is increasingly being used in various business systems. As a core component of the basic data support layer, the distributed database has a decisive impact on the performance of the entire platform and applications. High availability and high concurrency of the database are crucial for the stable daily operation of business systems. To ensure the stable and efficient operation of the distributed database, telecommunications companies typically assign dedicated personnel to monitor the database and audit and optimize the scripts.
[0003] Significant manpower is required to monitor the distributed databases of each system and to audit and optimize the scripts. The understanding and proficiency of developers regarding distributed database characteristics vary, resulting in scripts that are non-standard, unreasonable, and inefficient. Summary of the Invention
[0004] The purpose of this invention is to provide a method and system for auditing and optimizing distributed database SQL, to deepen researchers' understanding of the characteristics of distributed databases, improve SQL optimization efficiency, avoid database issues such as reduced availability, insufficient concurrency, and poor performance, and thus improve system stability.
[0005] The technical solution adopted in this invention is:
[0006] Methods for auditing and optimizing SQL in distributed databases include the following steps:
[0007] Step 1: Capture each SQL statement executed through the database interface via the client and upload it to the server, and format the SQL statements;
[0008] Step 2: The server uses a formatted SQL statement to query whether an audit result exists; if yes, proceed to step 6; otherwise, proceed to step 3.
[0009] Step 3: Use a syntax parser to extract the key information (table name, column name, relationships, conditions, etc.) from the SQL statement;
[0010] Step 4: Obtain distributed data metadata and combine it with key information to perform distributed database audit and optimization;
[0011] Step 5: Rewrite the SQL statement syntax and perform SOAR optimization to obtain a SQL statement score.
[0012] Step 6: The client obtains and displays the SQL score and audit results, and determines whether the SQL statement score matches the audit results; if yes, the SQL statement is allowed to execute; otherwise, the SQL statement is blocked from executing.
[0013] Furthermore, in step 2, the ZooKeeper TeleDB configuration information is obtained by listening to the ZooKeeper update metadata to force the update of Redis audit results and metadata; or the audit results are refreshed periodically to update Redis audit results and metadata.
[0014] Furthermore, in step 3, the SQL statement stream first passes through the lexical analyzer and becomes a token stream. The syntax analyzer generates a syntax tree based on the token stream and finally extracts the required key information based on the syntax tree.
[0015] Furthermore, key information includes table name, column name, relationships, and execution conditions.
[0016] Furthermore, the distributed data metadata in step 4 includes MySQL metadata, Redis audit results and their metadata.
[0017] Furthermore, the specific steps of step 5 are as follows:
[0018] Step 5-1: Provide SOAR-based optimization suggestions for each physically executed SQL statement in the distributed rule audit parsing.
[0019] Step 5-2: Rewrite the SQL statements that can be optimized to improve the execution efficiency of the SQL statements;
[0020] Step 5-3: Use the execution plan interpretation function to display the information in route2\explain more clearly, which helps to write better SQL statements;
[0021] Step 5-4: The SQL statement scoring assigns different security levels to different types of recommendations, with severity numbers sorted from low to high.
[0022] This distributed database SQL auditing and optimization suggestion system comprises a client, middleware, and server. The client binds to the underlying database interface to capture each SQL statement executed through the database interface and to obtain and display SQL scores and audit results. The middleware interacts with the server and includes ZooKeeper, Redis, and the distributed database. The server includes an SQL syntax tree parsing unit, a TeleDB auditing and optimization unit, a SOAR optimization unit, an execution plan interpretation unit, and an SQL scoring unit. The SQL syntax tree parsing unit uses Druid SQL Parser to parse the SQL's validity and syntax, laying the foundation for subsequent auditing and optimization. The TeleDB auditing and optimization unit obtains distributed database information through ZooKeeper and monitors rule updates in real time, parsing the SQL syntax and corresponding rules for auditing. The syntax rewriting suggestion unit provides rewriting suggestions for the audited SQL's syntax. The SOAR optimization unit includes heuristic rule suggestions, index optimization, and SQL rewriting suggestions. The execution plan interpretation unit interprets the route2, route, and explain execution plans. The SQL scoring unit scores each audited SQL statement to intercept SQL statements based on scoring rules.
[0023] Furthermore, the distributed database information includes TeleDB partitioning rules, partitioning fields, and global indexes.
[0024] This invention employs the above technical solutions to automatically audit, rewrite, and provide heuristic optimization suggestions for the distributed database TeleDB SQL; it interprets and scores the execution plans of SQL, rejecting the execution of SQL with low scores in the development environment; and it introduces caching to reduce the overall audit and optimization time, thereby improving work efficiency.
[0025] This invention extracts SQL from the underlying interface for intelligent automatic auditing, scores these SQL queries, intercepts low-scoring SQL queries from being executed in the development environment, partially rewrites them, and provides heuristic optimization suggestions to prevent problematic SQL queries from escaping to the production environment; it also integrates SOAR and TeleDB to reduce the difficulty of SQL tuning in distributed databases and improve work efficiency. Attached Figure Description
[0026] The present invention will be further described in detail below with reference to the accompanying drawings and specific embodiments;
[0027] Figure 1 This is a flowchart illustrating the distributed database SQL auditing and optimization suggestion method of the present invention;
[0028] Figure 2 This is a schematic diagram of the distributed database SQL audit and optimization suggestion system of the present invention;
[0029] Figure 3 This is a schematic diagram of the SQL syntax tree generation process. Detailed Implementation
[0030] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings.
[0031] like Figures 1 to 3 As shown in the figure, this invention discloses a method for distributed database SQL auditing and optimization suggestions, which includes the following steps:
[0032] Step 1: Capture each SQL statement executed through the database interface via the client and upload it to the server, and format the SQL statements;
[0033] Step 2: The server uses a formatted SQL statement to query whether an audit result exists; if yes, proceed to step 6; otherwise, proceed to step 3.
[0034] Step 3: Use a syntax parser to extract the key information (table name, column name, relationships, conditions, etc.) from the SQL statement;
[0035] Step 4: Obtain distributed data metadata and combine it with key information to perform distributed database audit and optimization;
[0036] Step 5: Rewrite the SQL statement syntax and perform SOAR optimization to obtain a SQL statement score.
[0037] Step 6: The client obtains and displays the SQL score and audit results, and determines whether the SQL statement score matches the audit results; if yes, the SQL statement is allowed to execute; otherwise, the SQL statement is blocked from executing.
[0038] Furthermore, in step 2, the ZooKeeper TeleDB configuration information is obtained by listening to the ZooKeeper update metadata to force the update of Redis audit results and metadata; or the audit results are refreshed periodically to update Redis audit results and metadata.
[0039] Furthermore, in step 3, the SQL statement stream first passes through the lexical analyzer and becomes a token stream. The syntax analyzer generates a syntax tree based on the token stream and finally extracts the required key information based on the syntax tree.
[0040] Furthermore, key information includes table name, column name, relationships, and execution conditions.
[0041] Furthermore, the distributed data metadata in step 4 includes MySQL metadata, Redis audit results and their metadata.
[0042] Furthermore, the specific steps of step 5 are as follows:
[0043] Step 5-1: Provide SOAR-based optimization suggestions for each physically executed SQL statement in the distributed rule audit parsing.
[0044] Step 5-2: Rewrite the SQL statements that can be optimized to improve the execution efficiency of the SQL statements;
[0045] Step 5-3: Use the execution plan interpretation function to display the information in route2\explain more clearly, which helps to write better SQL statements;
[0046] Step 5-4: The SQL statement scoring assigns different security levels to different types of recommendations, with severity numbers sorted from low to high.
[0047] like Figure 2 As shown, the distributed database SQL auditing and optimization suggestion system includes a client, middleware components, and a server. The client is bound to the underlying database interface to capture each SQL statement executed through the database interface and to obtain and display SQL scores and audit results. The middleware components interact with the server and include ZooKeeper, Redis, and the distributed database. The server includes an SQL syntax tree parsing unit, a TeleDB auditing and optimization unit, a SOAR optimization unit, an execution plan interpretation unit, and an SQL scoring unit. The SQL syntax tree parsing unit uses Druid SQL Parser to parse the SQL validity and syntax, laying the foundation for subsequent auditing and optimization. The TeleDB auditing and optimization unit obtains distributed database information through ZooKeeper and monitors rule updates in real time, parsing the SQL syntax and corresponding rules for auditing. The syntax rewriting suggestion unit provides rewriting suggestions for the audited SQL syntax. The SOAR optimization unit includes heuristic rule suggestions, index optimization, and SQL rewriting suggestions. The execution plan interpretation unit is used to interpret the route2, route, and explain execution plans. The SQL scoring unit scores each SQL statement after auditing to intercept SQL statements according to the scoring rules.
[0048] Furthermore, the distributed database information includes TeleDB partitioning rules, partitioning fields, and global indexes.
[0049] The specific principles of this invention will be explained in detail below:
[0050] This system, based on SOAR and TeleDB distributed database auditing and optimization suggestions, mainly consists of two parts: client-side SQL extraction and server-side SQL auditing and optimization. It is an intelligent SQL analysis system implemented based on SQL syntax trees, TeleDB usage guidelines, and SOAR, encompassing SQL capture, parsing, optimization, and suggestions. The client-side SQL extraction program includes modules for SQL extraction and uploading, enabling SQL statement capture, blocking problematic SQL execution, and displaying SQL optimization suggestions. The server-side SQL auditing program includes modules for general SQL syntax parsing, TeleDB-specific syntax parsing, TeleDB usage guideline auditing, SOAR optimization, statement rewriting suggestions, and SQL scoring, achieving automated SQL analysis and rewriting for distributed databases.
[0051] like Figure 3 As shown, the database audit and optimization suggestion function module mainly includes the following:
[0052] SQL syntax tree parsing: First, the stream of characters (SQL statements) is transformed into a token stream by the lexical analyzer. For example, SELECT A.ID, B.ID FROM A will become a token stream (SELECT, A, B, FROM, etc.). Next, the syntax parser generates a syntax tree based on the above token stream, and finally extracts the necessary information (e.g., column names, table names, conditions, and relationships) from the syntax tree.
[0053] TeleDB audit and optimization: Using ZooKeeper, we obtain table types (1. Global table, 2. Sharded table, 3. Single-sharded table, 4. Not set, 5. Sharded table + intra-database partitioning, 6. Intra-database partitioning, 7. Intra-database bucketing), sharding information, partitioning rules, partitioning fields, global indexes, and other Unified Access Layer (UDAL) characteristics, and monitor and update these rules in real time for subsequent optimization. The parsed SQL syntax tree is then audited and optimized against the corresponding rules.
[0054] Single table determination: Determine whether a broadcast statement is used based on table type, hint, sharding field, and global index.
[0055] Multi-table join judgment: - Global tables can be joined with all tables; single shards within the same physical database can be joined; shards with the same sharding rules and the same sharding region can be joined (sharded tables with different sharding keys cannot be joined, and tables with the same sharding key specifying different physical databases cannot be joined; intra-database sharding rules are the same as above, and intra-database rules also need to be judged).
[0056] Detailed rules for multi-table joins in sharded tables: If the first table is a global table, skip it and use the second table as the base table. Then, compare the base table with the subsequent tables (comparison tables). General rule: If the table types are the same and the physical databases are the same, the join is considered possible; otherwise, an error is displayed.
[0057] SOAR Optimization: Currently, SOAR can provide optimization suggestions based on heuristic rules, index optimization suggestions based on index optimization algorithms, and interpretations based on EXPLAIN information.
[0058] Heuristic rule suggestions: This is the core module of SOAR, consisting of rule code, risk level, rule summary, rule explanation, and SQL example. Each SQL statement is checked one by one by hundreds of heuristic rules, and finally merged with other optimization suggestions before being output.
[0059] Index optimization suggestions:
[0060] (1) Equivalence index optimization includes single-column equivalence query and multi-column equivalence query. For single-column equivalence query, an index is added to the equivalence column. For multi-column equivalence query, the granularity of each column is calculated, and the top N columns are added to the index in descending order (N is configurable).
[0061] (2) Non-equivalence query optimization includes single-column non-equivalence query and multi-column non-equivalence query. For single-column non-equivalence query, an index is added to the non-equivalence column. For multi-column non-equivalence query, the granularity of each column is calculated, and an index is added to the column with the largest granularity.
[0062] (3) Optimization of combined equivalence and non-equivalence queries: First, optimize the equivalence query by adding an index to the equivalence column, and then append the non-equivalence query optimization column to the equivalence column index.
[0063] (4) GROUP BY: Whether fields related to GROUP BY can be added to the index list depends on the conditions in the WHERE clause. When a WHERE condition is specified in the query, and the WHERE clause only contains equality queries, an index can be added to the GROUP BY fields. When no WHERE condition is specified in the query, an index can be added directly to the GROUP BY fields.
[0064] Add indexes in the order of GROUP BY;
[0065] If a constant is found in the GROUP BY field, a warning will be given when performing mathematical or function operations.
[0066] (5) ORDER BY Clause: Whether fields related to ORDER BY can be added to the index list depends on the conditions in the WHERE and GROUP BY clauses. When a query specifies a WHERE condition, and the WHERE clause contains only equality queries and there is no GROUP BY clause, an index can be added to the ORDER BY fields. When a query does not specify a WHERE condition, and there is no GROUP BY clause, an index can be added to the ORDER BY fields.
[0067] If multiple fields are specified in the same order, indexes are added according to the order in the ORDER BY clause; if multiple fields are specified in different orders, no indexes are added to any ORDER BY fields; if constants appear in ORDER BY fields, warnings will be given during mathematical or function operations. JOIN index optimization algorithms: LEFT JOIN adds an index to the right table; RIGHT JOIN adds an index to the left table; INNER JOIN adds indexes to both tables; the handling method for NATURAL is the same as the first three points; STRAIGHT_JOIN adds an index to the following table.
[0068] (6) Complex queries using SUBQUERY and UNION: SQL queries using SUBQUERY or UNION types with terms like IN and EXISTS should first be broken down into multiple independent SELECT statements. Then, based on the simple query index optimization algorithm described above, each individual SELECT query should be optimized. Indexes will not be added to the join columns of the SUBQUERY query at this time.
[0069] (7) Index Length Limit: Since index length is affected by database version and different configuration parameters, refer to the InnoDB limit. Here, the index length limit is defined as a configurable value, which users can set according to their actual situation. Configure the maximum length of each column index using `-max-index-bytes`, which defaults to 767 bytes. If the maximum length of a single column index is exceeded, the program will automatically add a prefix index (max-index-bytes / CHARSET_Maxlen) for that column. Configure the maximum length of multiple column indexes using `-max-index-bytes-percolumn`, which defaults to 3072 bytes. If the maximum length of multiple column indexes is exceeded, the ALTER statement generated by the program will specify the prefix index length of each column as N, which users can adjust themselves.
[0070] (8) Index deduplication: Check the steps, add indexes for fields that may be indexed in the query statement; enumerate all known indexes of all database tables used; determine whether all newly added indexes are duplicates of known indexes; determine whether there are index duplications among all newly added indexes; check the rules; RIGHT JOIN adds an index to the left table; INNER JOIN adds an index to both tables; refer to the previous section for the NATURAL handling method; STRAIGHT_JOIN adds an index to the subsequent table.
[0071] SQL rewriting suggestions (expandable):
[0072] • GROUP BY: If the GROUP BY statement does not specify an ORDER BY condition, it will result in unnecessary sorting. If sorting is not required, it is recommended to add ORDER BY NULL.
[0073] Original SQL: group by col
[0074] Rewritten: Suggest: group by col order by null
[0075] • TRUNCATE: It is recommended to change the DELETE operation without a WHERE condition to TRUNCATE.
[0076] Original SQL: DELETE FROM t
[0077] Rewritten: truncate table t
[0078] • HAVING: Rewrite the HAVING clause of the query as the query conditions in the WHERE clause;
[0079] Original SQL: select state,count(*)from t group by state having state in('ffcs','jf')order by state
[0080] After rewriting: select state,count(*)from t where state in('ffcs','jf')groupby state order by state asc
[0081] • Dml2select: Converts database update requests into read-only query requests, making it easier to execute EXPLAIN;
[0082] Original SQL:delete from t where a>100
[0083] Rewritten: select * from t where a>100
[0084] Or rewrite Union
[0085] Original SQL: select acct_id, prod_inst_id from t where bill_id=1or prod_inst_id=61001233
[0086] After rewriting: select acct_id, prod_inst_id from t where bill_id=1unionselect acct_id, prod_inst_id from t where prod_inst_id=61001233
[0087] Execution Plan Interpretation: route2\explain: Execution plan tools help us analyze the performance of our written SQL, such as whether indexes were used, temporary tables were used, file sorting was used, the number of rows scanned, and the execution order of each query. In addition to the fields displayed by explain, route2 also provides information specific to distributed databases, such as routing information, physical database name, the SQL executed on the physical database, whether the SQL was executed on the master or slave database, and the IP address and port of the executing database. However, the information displayed by execution plan tools is not easily understood and requires experienced developers to use it effectively. Execution plan interpretation can interpret the information from route2\explain, prompting us to write better SQL code.
[0088] • DATA_NODE information interpretation: Physical database name || Logical table name || Physical table name (acctdb_001 || ACCT_ITEM || ACCT_ITEM_B1);
[0089] Master / Slave: Execution between master and slave databases;
[0090] • Interpretation of SelectType information: simple: simple select (without using UNION or subqueries, etc.); primary: the outermost select query; subquery: the first select query in a subquery, which does not depend on the result set of the outer query.
[0091] • Type Information Interpretation: `all`: Full table scan; `ref`: The join cannot select a single row based on the keyword; it may find multiple rows that meet the conditions. It's called `ref` because the index is compared to a reference value. This reference value is either a number or the result of a multi-table query from a single table. Example: `SELECT * FROM tbl WHERE idx_col = expr;` `eq_ref`: The best possible join type besides `const`. It's used when all parts of an index are used in the join and the index is `UNIQUE` or `PRIMARY KEY`. For each index key, only one record in the table matches it. Example: `SELECT * FROM ref_table, tbl WHERE ref_table.key_column = tbl.column;`
[0092] Extra information interpretation: using filesort: When using order by / group by, if the content to be sorted cannot be sorted directly by the index, file sorting may be performed; using index: Using an index means that the index can cover all query fields, and there is no need to perform a table lookup, so it is efficient; using where: The fields currently being queried cannot be covered by the index, so a table lookup may occur, and the efficiency is lower than using index.
[0093] SQL Score: Different types of recommendations specify different security levels, with severity numbers ordered from lowest to highest. The maximum score is 100 points, deducting points down to 0. L0 only provides a recommendation and no points are deducted; L1 deducts 5 points, L2 deducts 10 points, and so on, increasing by 5 points for each level. When both L1 and L2 recommendations are provided, the deductions are cumulative, resulting in a total deduction of 15 points. Execution will be prohibited if the score is below 80.
[0094] This invention employs the above technical solutions to automatically audit, rewrite, and provide heuristic optimization suggestions for the distributed database TeleDB SQL; it interprets and scores the execution plans of SQL, rejecting the execution of SQL with low scores in the development environment; and it introduces caching to reduce the overall audit and optimization time, thereby improving work efficiency.
[0095] This invention extracts SQL from the underlying interface for intelligent automatic auditing, scores these SQL queries, intercepts low-scoring SQL queries from being executed in the development environment, partially rewrites them, and provides heuristic optimization suggestions to prevent problematic SQL queries from escaping to the production environment; it also integrates SOAR and TeleDB to reduce the difficulty of SQL tuning in distributed databases and improve work efficiency.
[0096] Obviously, the described embodiments are only a part of the embodiments of this application, not all of them. Without conflict, the embodiments and features in the embodiments of this application can be combined with each other. The components of the embodiments of this application described and illustrated herein can generally be arranged and designed in various different configurations. Therefore, the detailed description of the embodiments of this application is not intended to limit the scope of the claimed application, but merely to illustrate selected embodiments of this application. All other embodiments obtained by those skilled in the art based on the embodiments of this application without inventive effort are within the scope of protection of this application.
Claims
1. A method for auditing and optimizing distributed database SQL, characterized by: It includes the following steps: Step 1: Capture each SQL statement executed through the database interface via the client and upload it to the server, and format the SQL statements; Step 2: The server queries whether audit results exist using a formatted SQL statement; if yes, proceed to step 6. Otherwise, proceed to step 3; Step 3: Use a syntax parser to generate a syntax tree from the SQL statement, and extract key information including table name, column name, relationship, and execution conditions from the syntax tree; Step 4: Obtain distributed data metadata and combine it with key information to perform distributed database audit and optimization; Step 5 involves rewriting the SQL statement syntax and performing SOAR optimization to obtain a SQL statement score. The specific steps in Step 5 are as follows: Step 5-1: Provide SOAR-based optimization suggestions for each physically executed SQL statement in the distributed rule audit parsing. Step 5-2: Rewrite the SQL statements that can be optimized to improve the execution efficiency of the SQL statements; Step 5-3: Use the execution plan interpretation function to display the information in route2\explain more clearly, which helps to write better SQL statements; Step 5-4: The SQL statement scoring assigns different security levels to different types of recommendations, with severity numbers sorted from low to high. Step 6: The client obtains and displays the SQL score and audit results, and determines whether the SQL statement score matches the audit results; if so, the SQL statement is allowed to execute. Otherwise, prevent the execution of the SQL statement.
2. The distributed database SQL auditing and optimization suggestion method according to claim 1, characterized in that: In step 2, ZooKeeper's TeleDB configuration information is obtained by listening to ZooKeeper's update metadata, thereby forcibly updating the Redis audit results and metadata; or the audit results are refreshed periodically to update the Redis audit results and metadata.
3. The method of claim 1, wherein: The distributed data metadata in step 4 includes MySQL metadata, Redis audit results and their metadata.
4. A distributed database SQL audit and optimization suggestion system, employing the distributed database SQL audit and optimization suggestion method according to any one of claims 1 to 3, characterized in that: The system comprises a client, middleware components, and a server. The client is bound to the underlying database interface to capture each SQL statement executed through the database interface and to obtain and display SQL scores and audit results. The middleware components interact with the server and include ZooKeeper, Redis, and a distributed database. The server includes an SQL syntax tree parsing unit, a TeleDB audit optimization unit, a SOAR optimization unit, an execution plan interpretation unit, and an SQL scoring unit. The SQL syntax tree parsing unit uses Druid SQL Parser to parse the SQL's validity and syntax, laying the foundation for subsequent audit optimization. The TeleDB audit optimization unit obtains distributed database information through ZooKeeper and monitors rule updates in real time, parsing the SQL's syntax and corresponding rules for auditing. The syntax rewriting suggestion unit provides rewriting suggestions for the audited SQL's syntax. The SOAR optimization unit includes heuristic rule suggestions, index optimization, and SQL rewriting suggestions. The execution plan interpretation unit interprets the route2, route, and explain execution plans. The SQL scoring unit scores each SQL statement after auditing to intercept SQL statements based on scoring rules.
5. The distributed database SQL auditing and optimization suggestion system according to claim 4, characterized in that: Distributed database information includes TeleDB partitioning rules, partitioning fields, and global indexes.