Database Tuning Tutorial (iii) record slow query

first, find slow query
The last thing we do is to prepare for the occurrence of slow queries, which allows us to find slow queries and record them in a file ..

3. Record Slow queries

At this point we already have the cost of making the slow query happen. Execute the following statement and you will know what is slow! Check! Polling

Select Empno from emp where ename= ';

A query statement that obviously couldn't find the result was executed for almost 3 seconds.

At this point, the SQL statement should be recorded as a DBA, written in Notepad or in a notebook? Do not think too much, do not have to remember, MySQL provides a slow query log function, automatically help you record slow query statements.

1) Log The SQL of the slow query to the logs

First you want to open the slow query log file logger

Use

Show variables like ' slow% ';

You will find that by default the slow query logger shuts down

Use

Set global slow_query_log=on;

Open the

At this point you will find a log file named after your native name in the Data folder in the MySQL installation directory

The slow query operation is performed again at this time

Open log file discovery occurrence record

Later, you can only check the log file periodically to find the slow query statement.

Attention:

After finding the slow query statement, to confirm the slow query by repeatedly using the SELECT statement, note that only SELECT statements can be used, even if the original statement is delete or update, etc., also use Select Instead, because only select does not dirty the database

2) Another way to find slow query statements

If you use hibernate for the development of the Java EE, you can use the method.

in the page, when the response to an action is found to be slow, look at the Hibernate output SQL statement from the Eclipse console, which is a slow query statement.

at the end of this chapter, we will explain how to use MySQL's performance Analysis command explain in the next chapter.

Database Tuning Tutorial (iii) record slow query