Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”

Author’s other platforms:

| CSDN:blog.csdn.net/qq_4115394…

| the nuggets: juejin. Cn/user / 651387…

| zhihu: www.zhihu.com/people/1024…

| GitHub:github.com/JiangXia-10…

| public no. : 1024 notes

This article is about 1,445 words and takes 6 minutes to read

1 introduction

Early in the process of application development, due to the development of the relatively small amount of data is generally, so the development process is compared commonly pay attention to the realization of the function, but when completed an application or system, the rapid growth as the amount of production data, so many before written SQL statement will show a certain performance issues, the impact on the production will be more and more big, These improper SQL statements can become the bottleneck of the entire system performance, and must be optimized in order to pursue the ultimate performance of the system.

This article began the following several articles will be combined with their own work and learning knowledge record, when facing a SQL performance problem of the database, we should start from where to carry on the system analysis, so as to locate the problem as soon as possible, solve the problem.

2 the body

This article focuses on two commands, which are:

show[session|global] status
Copy the code

and

show global status like 'Innodb_rows_%'
Copy the code

After the MySQL client connection is successful, we can pass

show[session|global] status
Copy the code

Command to provide server status information.

This command displays session-level (current connection) statistics and global level (since last database startup) statistics using session or global parameters as required. If no, the session parameter is used by default. For example, the following statements can be used to see which SQL statements are being joined more frequently, such as query, update, insert, or delete operations. Generally speaking, it is mainly based on queries.

show status like 'Com_______'
Copy the code

Because I am a local database, so there is no SQL statement execution operation:

show global status like 'Com_______'
Copy the code

The above command displays global statistics since the last time the database was started. You can see that the query was executed 10 times.

And after the operation, we can see that the operation statistics also change:

After executing the UPDATE statement:

UPDATE JDBC. 'user' set username=' sun Wukong 'where id=1Copy the code

The number of rows affected by update changes with the following command:

The following command is executed for Innodb database engine:

show global status like 'Innodb_rows_%'
Copy the code

Com_% represents the number of % statements executed, and all storage engine table operations are counted.

Innodb_% is only for Innodb storage engine database, and the statistical algorithm is different from other databases.

There are several important parameters in the query result, they are: Insert, delete, update, select (innoDB’s equivalent is read), they correspond to the database operation add, delete, change query.

3 summary

Using the above commands, we can find out which operations the database is dominated by, which can provide some advice for the subsequent SQL optimization.

Other methods and steps for SQL tuning will be shared later.

Finally, if you think this article is good, you can recommend it to more people.

Today’s recommendation

MySQL Architecture

The execution order of SQL statements

Today we are going to talk about MySQL’s storage engine

SQL language: DDL, DML, DQL, DCL details

Python crawls taobao commodity information and generates Excel