Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to deeply reveal the DBbrain intelligent optimization engine, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

The principle and implementation of SQL optimization of DBbrain.

Preface

In the sharing of previous diagnosis days, we introduced how to use DBbrain to deal with database master-slave replication latency, high CPU utilization, and character set mismatch scenarios. This issue will share with you the SQL optimization principle and implementation of DBbrain. The main points are as follows:

1. Deeply reveal the architecture and principle of DBbrain intelligent optimization engine

2.DBbrain launches the industry's first SQL optimization comparison function

In order to make it easier for you to understand the usage scenario and design background of DBbrain's SQL optimization feature, let's first briefly talk about the relationship between poor SQL performance and database performance-we usually call the poor performance SQL slow SQL. Generally, we can set the slow_query_log parameter to ON to capture SQL statements whose execution time exceeds a certain value (controlled by the long_query_time parameter). In performance, it is understood that SQL takes too long to execute, but in a broad sense, SQL, which consumes too much resources and does not have good execution plans, also has potential dangers that affect database performance, perhaps just because resources are sufficiently idle (emergency upgrades can often temporarily cover up performance problems) or the amount of data is not large enough, so the execution time of these types of SQL is not too long. However, in specific scenarios, it will magnify its impact on database performance. Generally, 80% of the database performance problems are caused by SQL performance, so how to optimize SQL and the effect of SQL optimization has become the key factor to improve database performance. So the next step is to reveal how DBbrain's intelligent optimization engine optimizes SQL.

SQL Optimization suggestion based on rules and cost estimation

DBbrain's SQL optimization engine is database independent and avoids intrusion into the native database engine. Its main components include SQL parsing and validation, rule-based SQL rewriting, query condition selection / cost estimation, SQL clause checking and suggestion generator. In addition, the Connector component is responsible for interacting with the target database, synchronizing the configuration and table structure definition information required for SQL optimization, as well as SQL cost estimation.

1.SQL parsing and verification

It is responsible for parsing the input SQL statement, handing the library table involved in the extraction to the Connector component to obtain the table definition, and verifying the relevant field names, types and character sets (for performance reasons, DBbrain does not support MyISAM tables and views).

2.SQL rewriting

Database optimizers all have override components. Generally, before selecting the index and generating the execution plan, it transforms the original SQL statement without semantic difference, which makes the SQL statement more concise and convenient for subsequent components to choose the execution plan better. Execution plan selection is to try to choose the best execution path under the current given conditions, while SQL rewriting and adding appropriate index is to create better physical conditions for execution plan selection. The database itself has a certain rewriting function, so the SQL optimization recommendation also needs to identify these rules and associate the query conditions with the actual database table through transformation. Let's give an example to illustrate the principle of SQL rewriting: the execution path of SQL in the database is often quite different from the structure that developers are writing, such as the following SQL statement that developers see:

The view in the database is the following execution process:

The SQL optimization function of DBbrain is to help the database find the best execution path and optimize its execution path into a more concise and efficient view. From the perspective of SQL parsing, the query condition field "value" is associated with a, but an is only an alias for the subquery. From the point of view of no semantic difference, the query condition can be pushed down to the subquery and directly related to the library table dbbrain_1.

However, the rewriting function of the database itself is usually one-sided and the implementation is not perfect. In some specific scenarios, the display of change SQL statements can significantly improve execution performance. For example: push aggregation subquery under conditions, transform exists to join, conditional merging and so on. The biggest prerequisite for realizing SQL changes is that there is no semantic difference to ensure that the query results are correct. These changes are based on pre-set rules.

3. Selection degree calculation

Conditional selection calculation is the core of index recommendation, which determines the order of index fields and the selection of driving tables. The calculation of the selection of conditional fields depends on the statistical information of the table, and the database table needs to be sampled. DBbrain will extract 200-1000 pieces of data randomly by default. These data are the check code of the crc32 function to avoid obtaining the user's original data, and the user does not have to worry about the data security.

4. Conditional / clause check

According to the MySQL engine rules, identify the order by/ aggregate condition, project (output field), and make reasonable use of the index; at the same time, identify unreasonable conditions or use patterns, and prompt the user. For example, non-pre-like matching, numbers acting on character condition fields, and so on.

5.SQL optimization suggestion generation

SQL optimization recommendations include index optimization recommendations and SQL re-optimization recommendations.

Comparison of implementation plan before and after optimization and effect evaluation

The traditional manual optimization SQL extremely tests the knowledge reserve and actual combat experience accumulation of DBA. After optimization, the optimization effect of SQL can only be predicted through the change of explain, and most of the SQL optimization tools currently used by R & D and operation and maintenance can only get the optimization results based on theoretical analysis. So how can we accurately verify the effectiveness of SQL optimization? In most cases, it can only be judged by observing the appearance of business delay after execution (it can also be verified by more complex test environment changes and bypass traffic), but this verification method is not only inefficient. Moreover, there is a high risk to database performance before, during and after the change.

But this is a thing of the past, so don't worry about it now! After years of technical exploration and research and development, Tencent Cloud Database DBbrain team has finally launched the industry's first SQL performance optimization effect comparison feature based on execution cost analysis, which can predict the change effect before the change is implemented, so that users can predict the optimization effect of the change and feel more at ease to make the change according to the optimization recommendations. At the same time, through the feedback of the results of this kind of technology, we continue to optimize the accuracy of our own SQL optimization engine.

Under the premise of not changing the user database, the DBbrain intelligent optimization engine can evaluate the effectiveness of the SQL optimization recommendations. The SQL cost estimation engine plays a major role in this function. By analyzing the statistical information of SQL related database tables, OPTIMIZER_SWITCH configuration, and index field differentiation estimation, the overall standby cost of the optimized SQL statement is estimated. Let's show it through a real case of the current network:

1. Predict the optimization effect in advance.

Through the cost comparison, the DBbrain intelligent optimization engine directly shows the effect of 99.19% reduction after SQL optimization, and the optimization effect can also be further verified by comparing the execution plans before and after optimization.

2. Intelligent advice to save time and effort

The suggestion of SQL rewriting + adding index given by DBbrain intelligent optimization engine optimizes the performance of SQL.

3. Assist users to understand and optimize

In order to help users understand the optimization better, DBbrain also provides the display of table structure information, which makes the information more intuitive and clear.

Load evaluation and optimization based on full audit log

Generally speaking, the optimization of the actual system needs a global grasp, not just for a SQL statement. Although increasing the index can improve query performance, it will also increase disk overhead and reduce write performance. For different SQL statements, we may give repeated indexes for a table; at the same time, we also need to consider the impact of the execution frequency of SQL on the overall load of the system. Sometimes the number of scan rows in a single SQL is not high, but the high frequency will also become the main problem.

The evaluation and optimization of DBbrain is based on the load of the full audit log, calculate the number of SQL scan lines in real time, locate the main problem SQL statements and give optimization suggestions, so the optimization is overall and omni-directional.

New function express delivery

Finally, I would like to express the new features of DBbrain for you. DBbrain has officially released a new version with new features including:

1. Full support for read-only instances and disaster recovery instances, covering all performance diagnosis and optimization features

two。 Add a variety of master-slave replication failures, exceptions, hidden dangers troubleshooting and optimization

3. Comprehensively optimize the user experience, providing "user level" and "instance level" functions

User-level features: instance overview, instance management (instance list, alarm summary), full instance monitoring

Case-level functions: exception diagnosis, real-time session, slow SQL analysis, SQL optimization, spatial analysis, health report, audit log analysis.

4.SQL optimization capabilities continue to upgrade.

After reading the above, do you have any further understanding of how to reveal the DBbrain intelligent optimization engine in depth? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.