Database optimization tutorial (3) Slow query of records and database optimization tutorial Query

Database optimization tutorial (3) Slow query of records and database optimization tutorial Query
1. Slow query found
In the previous section, we made data preparation for slow queries. This section allows us to find slow queries and record them to files.

3. Slow query of records

Now we have the cost of making slow queries happen. Run the following statement to know what a slow process is! Query! Consult!

[Plain]View plaincopy

1. Select empno from emp where ename = '';

A query statement that clearly cannot find the result was executed for nearly three seconds.

At this time, as a DBA, we should record this SQL statement, remember it in notepad or in a notebook? You don't have to think too much about it. Mysql provides the slow query log function to automatically record slow query statements.

1) record SQL statements for slow queries to logs

First you need to open the slow query log file Recorder

Use

[Plain]View plaincopy

1. Show variables like 'slow % ';

You will find that the logger for slow query is disabled by default.

Use

[Plain]View plaincopy

1. Set global slow_query_log = ON;

Open it

At this time, you will find that the log file named after your local name appears in the data folder under the mysql installation directory.

Then perform the slow query operation.

Opening a log file and discovering a record

In the future, you only need to regularly check the log file to find the slow query statement.

Note:

After finding a slow query statement, you must use the select statement repeatedly to confirm the slow query. Note that you can only use the select statement. Even if the original statement is delete or update, you must use the select statement instead, because only the select statement does not dirty the database.

2) another method for discovering slow query statements

If you use Hibernate for J2ee development, you can use this method.

Perform operations on the page. When the response of an operation is slow, view the Hibernate output SQL statement on the Eclipse console. This statement is a slow query statement.