Introduction to mysql slow query and EXPLAIN

Preface: Today, database operations have increasingly become the performance bottleneck of the entire application, which is especially obvious for Web applications. Regarding the performance of the database, this is not just something that DBAs need to worry about, but this is something that we programmers need to pay attention to. When we design the database table structure and operate the database (especially SQL statements when looking up tables), we need to pay attention to the performance of data operations.

1. Enable slow query

1> 查看慢查询是否开启
   show variables like "%quer%";
   slow_query_log = ON  #已开启

2> Enable method: my.cnf directory configuration

  slow_query_log=on #是否开启
   slow_query_log_file=/opt/MySQL_Data/TEST1-slow.log #慢查询文件位置
   long_query_time=2 #查询超过多少秒才记录

2. EXPLAIN SELECT query appearing in slow query log

explain column explanation

table: Shows which table the data in this row refers to

type: This is an important column, showing what type of connection is used. The join types from best to worst are const, eq_reg, ref, range, index, all

possible_keys: Displays the indexes that may be applied to this table. If empty, no index is possible. You can choose an appropriate statement from the where statement for the relevant domain

key: The actual index used. If null, no index is used. Rarely, MySQL will select an index that is under-optimized. In this case, you can use use index (indexname) in the select statement to force the use of an index or use ignore index (indexname) to force MySQL to ignore the index

key_len: The length of the index used. Without losing accuracy, the shorter the length the better

ref: Shows which column of the index is used, if possible, a constant

rows: mysql thinks it must be checked The number of rows used to return the requested data

extra: Extra information about how MySQL parses the query. Example: using temporary and using filesort, which means that mysql cannot use the index at all, and the result is that the retrieval will be very slow

[Related recommendations]

1. Free mysql online video tutorial

2. MySQL latest manual tutorial

3. Those things about database design

The above is the detailed content of Introduction to mysql slow query and EXPLAIN. For more information, please follow other related articles on the PHP Chinese website!