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 2,152 words and takes nine 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 describes how to use two commands to query the execution frequency of SQL statements. The two commands are:

show[session|global] status
Copy the code

and

show global status like 'Innodb_rows_%'
Copy the code

Today’s article will cover how to locate inefficient SQL execution.

You can locate inefficient SQL statements in the following two ways.

1, slow query log: slow query refers to that we can set certain standards, if the efficiency of executing SQL statements is lower than this standard, then even if the slow query is recorded in the slow query log, this function needs to be enabled to use. Use slow query logs to locate SQL statements that execute inefficiently

log-slow-queries[=file_name]
Copy the code

When the option starts, mysqld writes a log file containing all SQL statements that take longer than long_query_time seconds to execute.

Mysql.cnf = my.cnf

Log-slow-queries = /var/lib/mysql/mysql-slow.logCopy the code

Long_query_time indicates how long SQL execution is logged, in this case 2 seconds.

Log-slow-queries are set to write logs there. The value above indicates that slow query logs are written to the /var/lib/mysql.log file. If the value is empty, the system assigns host names to slow query logs and adds slow. If log-long-format is set, all queries that do not use indexes will also be logged.

In addition to enabling slow log query by modifying the configuration file, you can also start slow log query by using the mysql database:

set global slow_query_log=ON
set global long_query_time = 3600
set global log_querise_not_using_indexes =ON
Copy the code

This is a very useful log. It has little impact on performance (assuming all queries are fast) and highlights queries that need the most attention (missing indexes or indexes that are not optimally used).

2, show processList You can run the show processList command to view the threads in progress, including the status of the threads and whether the table is locked, to view the SQL execution status in real time. At the same time, some lock table operations are optimized.

You can see that id=13 indicates that you are executing the show ProcessList you just did.

The fields in the result queried by running the show processList command have the following meanings:

Id: indicates the value of Connection_id assigned by the system when a user logs in to mysql. You can use the Connection_id () function to query the value.

2. User: Displays the current User. If you are not root, this command displays only SQL statements within the user’s permission range.

3, Host: display the statement from which IP which port, can be used to trace the user of the statement problem;

4, db: display the current link data;

5. Command: displays the commands executed in the current link, including sleep, query, and connect.

6. Time: Displays the duration of the status, in seconds.

7, the state: State describes a state in which statements are executed. An SQL statement, for example, might need to go through states such as copying to TMP tables, sorting result, sending data, etc.

Info: Displays this SQL statement, which is an important basis for determining the statement in question

3 summary

You can query logs slowly and run the show processlist command to locate SQL statements with low efficiency. You can optimize SQL statements with low efficiency, as described in the following sections.

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

Check how often SQL statements are executed