MySQL Slow query

Turn on slow query log

Add two configuration parameters under the [mysqld] line in the configuration file my.cnf or My.ini

Slow_query_log = on     #开启慢查询long_query_time=1       #设置慢查询时间 1   s Slow_query_log_file="d:/phpstudy/mysql/data/slow_query.log"    # If the slow query log save path does not specify Slow_query_log_file, a file with host name + ' slow '. Log is automatically generated log-queries-not-using- indexes = on   #开启记录没有使用索引查询语句

SQL view slow Query configuration

' slow_queries ' ;    #慢查询的次数 (default is slow query time 10s)  ' Long_query_time 　　' ; // can display the current slow query time　　　　set long_query_time=1 ; // can modify slow query time current session valid, permanent valid please modify the configuration file

Slow query logging to log

By default, a low version of MySQL does not log slow queries, you need to specify a slow query to log when you start MySQL

Bin\mysqld.exe–log-slow-queries=d:/abc.log [Low version mysql5.0 can be specified in My.ini]

The slow query log is placed under the MySQL installation directory/data/in this version of mysql5.0;

There are two ways to start slow queries against mysql5.5

①bin\mysqld.exe--safe-mode--slow-query-log

② configuration in My.ini file [mysqld] Add slow-query-log = on # turn on slow query this function

The directory can be specified by slow_query_log_file= "Log storage Location", which is stored by default in mysql5.5.19 my.ini datadir= "directory";

In mysql5.6, the default is to start the record slow query, My.ini which has a configuration item slow-query-log=1;

Slow Query SQL statements Explain analysis

Explain SQL statements;

The following information is generated:

ID: This is the query sequence number for select

Select_type: Represents the type of query.

Simple	Simple select query with no union and subquery
PRIMARY	Outermost select query
UNION	The second or subsequent select query in UNION that does not depend on the result set of an external query
DEPENDENT UNION	The second or subsequent select query in the UNION, dependent on the result set of the external query
Subquery	The first select query in a subquery that does not depend on the result set of an external query
DEPENDENT subquery	The first select query in a subquery, dependent on the result set of the external query
DERIVED	Used in cases where there is a subquery in the FROM clause. MySQL executes these subqueries recursively, putting the results in a temporary table.
Uncacheable subquery	The result set cannot be cached by the subquery and must be evaluated again for each row of the outer query.
Uncacheable UNION	Second or subsequent select query in UNION, which belongs to a non-cacheable subquery

Table: Tables for output result sets

Type: Represents the table's connection types

System	Table has only one row (= system table). This is a special case of the const connection type.
Const	Const is used to compare PRIMARY KEY with a constant value. Use System when the table that you are querying has only one row.
Eq_ref	Const is used to compare PRIMARY KEY with a constant value. Use System when the table that you are querying has only one row.
Ref	A connection cannot select a single row based on a keyword, and may find multiple rows that match the criteria. This is called ref because the index is compared to a reference value. This reference value is either a constant or a result value from a multi-table query in a list.
Ref_or_null	Like ref, but MySQL must find the null entry in the results of the initial lookup, and then make two lookups.
Index_merge	Indicates that the index merge optimization was used.
Unique_subquery	This type is used in some in queries instead of the regular ref:value in (the SELECT primary_key from single_table WHERE some_expr)
Index_subquery	This type is used in some in queries, similar to Unique_subquery, but is a non-unique index of the query: value in (The SELECT key_column from single_table WHERE some_ Expr
Range	Retrieves only the rows for a given range, using an index to select rows. The key column shows which index is used. Range can be used when comparing key columns with constants using =, <>, >, >=, <, <=, is NULL, <=>, between, or in operators.
Index	Full table scan, just scan the table by the index order instead of the line. The main advantage is to avoid sorting, but the overhead is still very large.
All	Worst case, full table scan from start to finish.

Possible_keys: The index that may be used when representing the query

Key: Represents the actual index used

Key_len: Length of index field

Ref: Shows which column of the index is being used and, if possible, a constant

Rows: Number of rows scanned (estimated number of rows)

Extra: Description and description of the performance

Use from DUAL or without any FROM clause in the no Tables:query statement

Using Filesort: When the order by operation is included in query and cannot be sorted by index,

Impossible WHERE noticed after reading const TABLES:MYSQL Query Optimizer

No results can be found by collecting statistical information

Using temporary: Some operations must use temporary tables, common GROUP by; ORDER by

Using Where: Without reading all the information in the table, only through the index can obtain the required data;

Note: The condition is a range value (so-called range), that is, the index after the range type query field is invalid.

When the order by field appears in the Where condition and the field has a fixed value or if there is a fixed return, the index of the field is used.

Slow Query related commands

 mysql> Show variables like  " %slow%   "  +---------------------+---------------------------------------+| variable_name | Value |+---------------------+---------------------------------------+| log_slow_queries | OFF | | Slow_launch_time | 2  | | Slow_query_log | OFF | | Slow_query_log_file | D:\phpStudy\MySQL\data\Admin-slow.log |+---------------------+---------------------------------------+ 

 mysql> Show variables like  " %long%   "  +---------------------------------------------------+-----------+| variable_name | Value |+---------------------------------------------------+-----------+| Long_query_time | 10.000000  | | Max_long_data_size | 1048576  | | Performance_schema_events_waits_history_long_size |  10000  |+---------------------------------------------------+-----------+ 

' %min% ' ; +--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| Ft_min_word_len          4     | | min_examined_row_limit   0     4096  |+------- -------------------+-------+

MySQL Slow query