MySQL enables slow query logging, mysql enables Logging

MySQL enables slow query logging, mysql enables Logging

In MySQL, the definition time of a slow query is specified by the MySQL built-in parameter long_query_time. The default value is 10 (unit: seconds ), we can use the show variables like 'long _ query_time '; command to view the information of this parameter variable:
The default value of long_query_time is 10 seconds.

However, in the process of program development, we believe that the definition time of slow query is not as long as 10 seconds, based on different needs of different projects, we generally set the definition time of slow queries to 1 ~ Within 5 seconds. We can use the command set long_query_time = seconds to set the value of the long_query_time variable.
Set long_query_time to 1 second.

After modifying the long_query_time parameter, we also need to enable MySQL to record slow query log information. Because, by default, MySQL does not record slow query log information. To record slow query logs, run the command line to enter the in directory of the MySQL installation directory (you can also add this directory to the path environment variable) and run the following command to restart MySQL:

# The part in [] is optional. file_name indicates the log file path # in MySQL 5.5 and later versions, run the following command to start it: mysqld -- show-query-log [= 1] [-- show-query-log-file = file_name] # in MySQL of lower versions such as 5.0 and 5.1, run the following command to start MySQL: mysqld -- log-slow-queries [= file_name]

In the preceding command, if no log file name is specified, the default log file name is host name-slow. log. If no file path is specified or the specified file path is not an absolute path, the log file is stored in the MySQL configuration file my. under the directory specified by datadir in ini.
In addition, you can configure the above command line startup command to the [mysqld] node in my. ini, so that you do not need to manually type the above command to start each time.

[Mysqld] # Set the definition time of slow query to 1 second long_query_time = 1 #5.0, 5.1 and other versions. Configure the following options: log-slow-queries = "mysql_slow_query.log" #5.5 and later versions are configured as follows: option slow-query-log = On slow_query_log_file = "mysql_slow_query.log"

Note: although the slow query name only contains "query", it does not only represent SELECT query operations, such as INSERT, UPDATE, DELETE, CALL, and other DML operations, as long as it exceeds the specified time, it can be called "Slow query" and will be recorded in the slow query log.

At this time, we need to use the EXPLAIN command to obtain details about the execution of the SELECT statement. In MySQL databases, the usage of the EXPLAIN command is very simple, as shown below:

Explain select statement

-- Example: explain select * FROM admin WHERE user_id = 1;

If it is in the Oracle database, we can also use the following SQL statement to obtain the execution plan information of the SELECT statement:

Explain plan for select statement -- FOR example: explain plan for select * FROM admin WHERE user_id = 1;

Here, we take MySQL as an example. Executing the above EXPLAIN command in the database will output the following results (user_id is the primary key column of the admin table ):

To further illustrate the meaning of the preceding execution plan information, we will execute the EXPLAIN command with formatting again.

EXPLAIN SELECT * FROM admin WHERE user_id = 1G;

(G indicates formatting the output result in MySQL), we will get the following information:

*************************** 1. row ***************************      id: 1 select_type: SIMPLE     table: admin     type: constpossible_keys: PRIMARY     key: PRIMARY   key_len: 1     ref: const     rows: 1    Extra:1 row in set (0.00 sec)

Next, we will introduce in detail the meaning of each field information in the above output result:

Id
SELECT identifier. This is the query sequence number of SELECT.
Select_type
SELECT type, which can be any of the following:
SIMPLE
Simple SELECT (do not use UNION or subquery ).
PRIMARY
Exclusive SELECT
UNION
The second or subsequent SELECT statement in UNION.
DEPENDENT UNION
The second or subsequent SELECT statement in UNION depends on the external query.
UNION RESULT
The result of UNION.
SUBQUERY
The first select in the subquery.
DEPENDENT SUBQUERY
The first select in the subquery depends on the external query.
DERIVED
SELECT (subquery of the from clause) of the exported table ).
Table
The table referenced by the output row.
Type
Join type. The following describes various join types, sorted by the best type to the worst type:
System
The table has only one row (= system table ). This is a special case of the const join type.
Const
A table can have at most one matching row, which will be read at the beginning of the query. Because there is only one row, the column value in this row can be considered as a constant by the rest of the optimizer. Const tables are fast because they are read only once!
Eq_ref
For each row combination from the preceding table, read a row from the table. This may be the best join type except the const type. It is used to join all parts of an index and the index is UNIQUE or primary key.
Ref
For each row combination from the preceding table, all rows with matching index values are read from this table. If the join only uses the leftmost prefix of the KEY, or if the KEY is not UNIQUE or primary key (in other words, if the join cannot select a single row based on the keyword), use ref. If the key used matches only a few rows, the join type is good. Ref can be used for indexed columns using the = or <=> operator.
Ref_or_null
The join type is like ref, but MySQL can search for rows containing NULL values. The optimization of this join type is often used in solving subqueries.
Index_merge
The join type indicates that the index merge optimization method is used. In this case, the key column contains the list of indexes used, and key_len contains the longest key element of the index used.
Unique_subquery
This type replaces the ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) IN the following form of IN subqueries ). Unique_subquery is an index lookup function that can replace subqueries completely, improving efficiency.
Index_subquery
The join type is similar to unique_subquery. The IN subquery can be replaced, but it is only applicable to non-unique indexes IN the following forms: value IN (SELECT key_column FROM single_table WHERE some_expr)
Range
Only retrieve rows in a given range and use an index to select rows. The key column shows the index used. Key_len contains the longest key element of the index used. In this type, the ref column is NULL. When using the =, <>,>, >=, <, <=, is null, <=>, BETWEEN, or IN operator, you can use range to compare the keyword columns with constants.
Index
The join type is the same as that of ALL except that the index tree is scanned. This is usually faster than ALL because index files are usually smaller than data files. MySQL can use this join type when querying only columns that are part of a single index.
ALL
Perform a full table scan for each row combination from the previous table. If the table is the first table without the const Mark, this is usually not good, and it is usually very bad. Generally, you can add more indexes instead of ALL, so that the rows can be retrieved Based on the constant values or column values in the preceding table.
Possible_keys
The possible_keys column specifies which index MySQL can use to find rows in the table. Note that this column is completely independent of the Order of the tables shown in the EXPLAIN output. This means that some keys in possible_keys cannot be used in the order of the generated table. If this column is NULL, there is no relevant index. In this case, you can check the WHERE clause to see if it references certain columns or columns suitable for indexing to improve your query performance. If so, create an appropriate index and use EXPLAIN again to check the query.
Key
The key column displays the keys (indexes) actually determined by MySQL ). If no index is selected, the key is NULL. To FORCE MySQL to USE or IGNORE the indexes in the possible_keys column, use force index, use index, or ignore index in the query.
Key_len
The key_len column displays the key length determined by MySQL. If the key is NULL, the length is NULL. Note that using the key_len value, we can determine the parts of MySQL that will actually use multiple keywords.
Ref
The ref column shows which column or constant is used together with the key to select rows from the table.
Rows
The rows column displays the number of rows that MySQL considers to be required for query execution.
Extra
This column contains detailed information about MySQL queries. The following describes the different text strings that can be displayed in this column:
Distinct
After MySQL finds 1st matching rows, it stops searching for more rows for the current row combination.
Not exists
MySQL can perform left join Optimization on queries. After finding one row that matches the left join standard, it no longer checks more rows for the preceding row combination in the table. The following IS an example of a query type that can be optimized as follows: SELECT * FROM t1 left join t2 ON t1.id = t2.id WHERE t2.id is null; assume that t2.id IS defined as not null. In this case, MySQL uses the value of t1.id to scan t1 and find the rows in T2. If MySQL finds a matched row in t2, it knows that t2.id will never be NULL and does not scan rows with the same id value in t2. In other words, for each line of t1, MySQL only needs to find it once in t2, regardless of the actual number of matched rows in t2.
Range checked for each record (index map :#)
MySQL does not find any indexes that can be used. However, if the column values from the preceding table are known, some indexes may be used. For each row combination in the preceding table, MySQL checks whether the range or index_merge access method can be used to obtain rows. This is not very fast, but it is much faster than executing a join without an index.
Using filesort
MySQL requires an additional pass to find out how to retrieve rows in order. You can browse all rows based on the join type and save the sorting keywords and row pointers for all rows matching the WHERE clause. Then the keywords are sorted and the rows are retrieved in the order of sorting.
Using index
You can use only the information in the index tree without further searching and reading the actual row to retrieve the column information in the table. This policy can be used when queries only use columns that are part of a single index.
Using temporary
To solve the query, MySQL needs to create a temporary table to accommodate the results. In typical cases, if a query contains the group by and order by clauses that can list columns according to different situations.
Using where
The WHERE clause is used to limit which row matches the next table or sends it to the customer. Unless you request or check ALL rows from a table, if the Extra value is not Using where and the table join type is ALL or index, the query may have some errors. If you want to make the query as fast as possible, you should find the Extra values of Using filesort and Using temporary.
Using sort_union (...), Using union (...), Using intersect (...)
These functions describe how to merge index scans for the index_merge join type.
Using index for group-
Similar to the Using index method for accessing tables, Using index for group-by indicates that MySQL has found an index and can be used to query all columns queried by group by or DISTINCT, instead of searching for the actual table by hard disk. In addition, indexes are used in the most effective way to read only a small number of index entries for each group.