Note If the FULL keyword is not used, you can view only the first 100 characters of the Info field in each record.

These states apply to the event scheduler thread, creating the thread that executes the scheduled event or terminates the scheduler.

These thread states occur on replicated slave libraries, but are associated with connected threads, not WITH I/O or SQL threads.

The sample table

DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (`id` INT(11) NOT NULL.5`name` VARCHAR(4) DEFAULT NULL,`update_time` DATETIME DEFAULT NULL.PRIMARY KEY (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1.'a'.'2017 ‐ ‐ 22 15:27:18 12'), (2.'b'.'2017 ‐ ‐ 22 15:27:18 12'), (3.'c'.'2017 ‐ ‐ 22 15:27:18 12');

DROP TABLE IF EXISTS `film`;
CREATE TABLE `film` (`id` INT(11) NOT NULL AUTO_INCREMENT,`name` VARCHAR(10) DEFAULT NULL.PRIMARY KEY (`id`),KEY `idx_name` (`name`)) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO `film` (`id`, `name`) VALUES (3.'film0'), (1.'film1'), (2.'film2');

DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor` (`id` INT(11) NOT NULL,`film_id` INT(11) NOT NULL,`actor_id` INT(11) NOT NULL,`remark` VARCHAR(255) DEFAULT NULL.PRIMARY KEY (`id`),KEY `idx_film_actor_id` (`film_id`,`actor_id`)
Copy the code

Query the SQL execution plan.

• Id column The ID column is the serial number of the select. As many as there are select, there are several ids, and the order of ids increases in the order in which the select appears.

  The larger the ID column is, the higher the execution priority is. If the IDS are the same, the execution is performed from the top down. If the ID is NULL, the execution is performed last.
  
  

• The select_Type column indicates whether the corresponding row is a simple or complex query.

MySQL stores subquery results in a temporary table, also known as a derived table

• The table column indicates which table the explain row is accessing

  • whenfromWhen there are subqueries in the clause, the table column isderivenNFormat that represents the current query dependencyid=NThe query is executed firstid=NThe query
  • When you haveunion, the value of the table column of the UNION RESULT is <union1,2>, 1 and 2 represent the IDS of select rows participating in the UNION.

• The type column represents the association type or access type, that is, how does MySQL decide to look for rows in the table, the approximate range of data row records

  System > const > eq_ref > ref > range > index > all Generally speaking, it is necessary to ensure that the query reaches the range level, and ref is the best.

• The possible_keys column shows which indexes the query might use to find it. Possible_keys may have columns while key is NULL when explaining possible_keys. In this case, it is because there is not much data in the table. Mysql thinks that the index is not helpful for the query and selects the full table query. If the column is NULL, there is no associated index. In this case, you can improve query performance by examining the WHERE clause to see if you can create an appropriate index, and then using Explain to see the effect.

• The key column shows which index mysql actually uses to optimize access to the table. If no index is used, the column is NULL. If you want to force mysql to use or ignore indexes in the possible_keys column, use force index and ignore index in the query

  Mandatory INDEX: EXPLAIN SELECT *FROM account_info FORCE INDEX(I_SEQ); EXPLAIN SELECT *FROM account_info IGNORE INDEX(I_SEQ);

• The key_len column shows the number of bytes used by mysql in the index. This value can be used to figure out which columns are used in the index.

  For example, the joint index idx_FILM_actor_id for film_actor consists of two int columns film_id and actor_id, and each int is 4 bytes. From key_len=4 in the result, it can be inferred that the query uses the first column, the film_ID column, to perform the index lookup. mysql> explain select * from film_actor where film_id = 2

Key_len is computed as follows

• If the field is allowed NULL, 1 byte is required to record whether the index is NULL
• The maximum length of the string is 768 bytes. If the string is too long, mysql will perform a process similar to the left prefix index to extract the first half of the string for indexing

• The ref column shows the columns or constants used by the table to find values in the index of the key column. Common examples are const (constant), field names (e.g. Film.id).
• The rows column is the number of rows that mysql estimates to be read and tested. Note that this is not the number of rows in the result set
• The Extra column displays additional information.

Added a PARTITIONS field that shows the partitions the query will access if the query is based on a partitioned table

This function is disabled by default. You need to manually enable it. You can perform the following operations to check whether the function is enabled on the current mysql server.

• profilingParameter values forOFF“Indicates that the function is not enabled.
• profiling_history_sizeParameter values for15Indicates that the query history of the last 15 times is recorded

mysql> show variables like '%profiling%';
Copy the code

mysql> show profiles;
Copy the code

• Query_IDIs the ID of the query statement executed
  
  

Mysql SHOW PROCESSLIST helps you understand the state changes of the primary and secondary replication threads in Mysql. Explain best practices and indexing. PDF