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

It is believed that many inexperienced people don't know what to do about how to use DBA, an auxiliary tool for MySQL sentence optimization. therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Overview

Optimizing SQL is one of the common tasks of DBA. How to optimize a statement efficiently and quickly is a problem that every DBA often faces. For a DBA, it is necessary to master a language to match his work. Compared with the simplicity of shell and the elegance of perl, Python is a rigorous high-level language. It has many advantages, such as quick start, simple syntax, rich expansion, cross-platform and so on. Many people call it a "glue" language, through a large number of rich class libraries, modules, you can quickly build the tools you need.

Today, I mainly share a "MySQL statement optimization assistant tool" written by myself. Through this gadget, you can automatically call commands to push the above contents to DBA at one time, greatly speeding up the optimization process.

Let's introduce this gadget.

Environment

Module-MySQLDB

Module-sqlparse

Python version = 2.7.3

1. Content of the script

Because there is a lot of content in the script, it is truncated.

2. Call method

Python mysql_tuning.py-p tuning_sql.ini-s' your sql'

Parameter description:

-p specifies the profile name

-s specifies the SQL statement

3. Configuration file

Here, [database] describes the database connection information and [option] running configuration information.

4. Output description

1) title section

Contains the address information and data version information of the running database.

2) original SQL

The user executes the input SQL, which is mainly used for subsequent comparison of SQL rewriting. The statement is displayed using formatting.

3) system-level parameters

The script selection shows some parameters related to SQL performance. This part is written in the code, if you need to expand, you need to modify the script.

4) Optimizer switch

Here are some parameters related to the optimizer that can be artificially interfered with by adjusting them.

5) implement the plan

Is the output of the call to explain extended. If the result is too long, there may be a serial display problem (not resolved for the time being).

6) SQL rewritten by optimizer

Here you can determine whether the optimizer has made some kind of optimization for SQL (such as the processing of subqueries).

7) Statistics

Statistics for all tables and their indexes involved in the SQL statement are displayed here.

8) running status information

At the session level, the state before and after execution (SHOW STATUS) is compared, and the parts that have changed are displayed. It should be noted that because the status data is collected in a SELECT way, it will cause errors in individual indicators (such as Com_select).

9) PROFILE details

The details obtained by calling SHOW PROFILE.

10) PROFILE summary information

According to the resource consumption of PROFILE, the comparison of consumption in different stages (TOP N) is displayed, and the "bottleneck" is intuitively shown.

After reading the above, have you mastered how to use DBA, an auxiliary tool for MySQL sentence optimization? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.