Shulou(Shulou.com)06/01 Report--

Background:

In the work of database, the most common thing is slow sql optimization, but how can DBA get out of this complicated work and look it up in the database himself, or find a slow sql in other ways, and then throw it to the developer? DBA is following behind like a stepmother to promote development optimization, which is not only inefficient, but also affects the time consumed by DBA. DBA should get away from these daily tasks to dock the business, study new technologies, architectures, etc., and spend more time doing more valuable things.

Solution:

At present, the company's main business is on Aliyun's RDS, which provides an interface to access slow sql logs.

First, original data preparation

1. Write a Mini Program to extract the slow sql of all instances and store them in the table.

2. Tag all sql to facilitate later analysis.

3, output the execution plan of slow sql and store it in the table

Second, data analysis and slow sql processing flow

1. Analyze sql from several dimensions, such as sql execution time, number of times, number of rows scanned, sorting, index usage and so on, and output the analysis results.

2. Connect the processed slow sql and tapd project management system, and automatically assign each sql to the relevant developer.

3. Output the optimization result report and the weekly slow sql optimization of each project.

Three summaries:

1. In this way, the workload of DBA can be reduced.

2. Managing and tracking slow sql optimization through the platform will make the whole workflow clearer and more efficient.

3. Make the whole optimization work clearer through the report, mobilize the enthusiasm of development, so that the optimization work can be quantified.

4. Screenshots of code and result:

1. Pull and slow down sql:

2. The result generates Html.

3. Generate the report:

The results show:

1. Slow sql log:

2 Tapd work order:

3, report:

Nagging:

1, because there is a lot of code, so I just posted a little code casually.

2, just show some of the output

3, at present, it has not been made into a platform, because my front-end development is not very good, ! At present, it can be done like this, but it can also effectively improve work efficiency.

4, just throw a brick to attract jade here, share some of your own ideas with you, welcome to leave a message, look forward to Daniel's plan and guidance, thank you

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.