explain analysis execution plan

You can use the EXPLAIN or DESC command to obtain information about how MySQL executes the SELECT statement, including how tables are connected and the order of connection during the execution of the SELECT statement.

Execution plan of query SQL statement:

explain select * from tb_item where id = 1;

 

field	meaning
id	The sequence number of a select query is a group of numbers, which indicates the order in which the select clause or operation table is executed in the query.
select_type	Indicates the type of SELECT. Common values include SIMPLE (SIMPLE table, i.e. no table connection or sub query), PRIMARY (PRIMARY query, i.e. outer query), UNION (the second or subsequent query statement in UNION), SUBQUERY (the first SELECT in sub query), etc
table	Table of output result set
type	Indicates the connection type of the table. The connection type with good performance to poor performance is (system -- > const ---- > eq_ref ---- -- > ref ---- > ref ---- > or _null ---- > index_merge -- > index_subquery ---- > range ---- > index ---- -- > all)
possible_keys	Represents the index that may be used when querying
key	Represents the index actually used
key_len	Length of index field
rows	Number of scan lines
extra	Description and description of implementation

Environmental preparation:

CREATE TABLE `t_role` (
  `id` varchar(32) NOT NULL,
  `role_name` varchar(255) DEFAULT NULL,
  `role_code` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_role_name` (`role_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `t_user` (
  `id` varchar(32) NOT NULL,
  `username` varchar(45) NOT NULL,
  `password` varchar(96) NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `user_role` (
  `id` int(11) NOT NULL auto_increment ,
  `user_id` varchar(32) DEFAULT NULL,
  `role_id` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_ur_user_id` (`user_id`),
  KEY `fk_ur_role_id` (`role_id`),
  CONSTRAINT `fk_ur_role_id` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_ur_user_id` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




insert into `t_user` (`id`, `username`, `password`, `name`) values('1','super','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','Super administrator');
insert into `t_user` (`id`, `username`, `password`, `name`) values('2','admin','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','system administrator');
insert into `t_user` (`id`, `username`, `password`, `name`) values('3','itcast','$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui','test02');
insert into `t_user` (`id`, `username`, `password`, `name`) values('4','stu1','$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa','Student 1');
insert into `t_user` (`id`, `username`, `password`, `name`) values('5','stu2','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','Student 2');
insert into `t_user` (`id`, `username`, `password`, `name`) values('6','t1','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','Teacher 1');



INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('5','student','student','student');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('7','teacher','teacher','teacher');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('8','Teaching administrator','teachmanager','Teaching administrator');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('9','administrators','admin','administrators');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('10','Super administrator','super','Super administrator');


INSERT INTO user_role(id,user_id,role_id) VALUES(NULL, '1', '5'),(NULL, '1', '7'),(NULL, '2', '8'),(NULL, '3', '9'),(NULL, '4', '8'),(NULL, '5', '10') ;

explain id

The id field is the sequence number of the select query and a group of numbers, indicating the order in which the select clause or the operation table is executed in the query. There are three situations:

1) The same id indicates that the order of loading the table is from top to bottom.

  2) Different IDs. The larger the id value, the higher the priority and the earlier it is executed.

  3) IDs are the same and different, and exist at the same time. The same id can be considered as a group, which is executed from top to bottom; In all groups, the higher the id value, the higher the priority and the earlier the execution.

  explain select_type

select_type	meaning
SIMPLE	A simple select query that does not contain subqueries or unions
PRIMARY	If any complex subquery is included in the query, the outermost query is marked with this ID
SUBQUERY	Subqueries are included in the SELECT or WHERE list
DERIVED	The sub queries contained in the FROM list are marked as DERIVED. MYSQL will recursively execute these sub queries and put the results in the temporary table
UNION	If the second SELECT appears after UNION, it is marked as UNION; If UNION is included in subquery of the FROM clause, outer SELECT will be marked as: DERIVED
UNION RESULT	Get the SELECT of the result from the UNION table

explain table

Show which table the data in this row is about

explain type

type	meaning
NULL	MySQL does not access any tables or indexes and returns results directly
system	The table has only one row of records (equal to the system table), which is a special case of const type and generally does not appear
const	It means that it can be found once through the index. const is used to compare the primary key or unique index. Because only one row of data is matched, it is very fast. If the primary key is placed in the where list, MySQL can convert the query into a constant light. const is used to compare all parts of a primary key or unique index with a constant value
eq_ref	Similar to ref, the difference is that the unique index is used. For the association query using the primary key, there is only one record found in the association query. Common in primary key or unique index scanning
ref	Non unique index scan that returns all rows that match a single value. In essence, it is also an index access that returns all rows (multiple) that match a single value
range	Retrieve only the rows returned for a given, using an index to select rows. After where, operations such as between, <, >, in appear.
index	The difference between index and ALL is that index only traverses the index tree, which is usually faster than ALL. ALL traverses the data file.
all	The entire table will be traversed to find matching rows

The result value is from best to worst, which is:

NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL


system > const > eq_ref > ref > range > index > ALL

Generally speaking, we need to ensure that the query reaches at least range level, preferably ref.

  explain key

possible_keys : Displays one or more indexes that may be applied to this table. 

key :  Index actually used, if NULL， No index is used.

key_len : Indicates the number of bytes used in the index. This value is the maximum possible length of the index field, not the actual length. The shorter the length, the better without losing accuracy.

explain rows

Number of scan lines.

explain extra

Other additional execution plan information is displayed in this column.

extra	meaning
using filesort	This shows that mysql uses an external index to sort the data instead of reading according to the index order in the table. It is called "file sorting", which is inefficient.
using temporary	Temporary tables are used to save intermediate results. MySQL uses temporary tables when sorting query results. Common in order by and group by; Low efficiency
using index	It indicates that the corresponding select operation uses the overlay index to avoid accessing the data rows of the table, which is efficient.