MySQL SQL optimization explain analysis

Posted by xtian on Fri, 01 Oct 2021 20:48:40 +0200

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;

 

fieldmeaning
idThe 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_typeIndicates 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
tableTable of output result set
typeIndicates 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_keysRepresents the index that may be used when querying
keyRepresents the index actually used
key_lenLength of index field
rowsNumber of scan lines
extraDescription 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_typemeaning
SIMPLEA simple select query that does not contain subqueries or unions
PRIMARYIf any complex subquery is included in the query, the outermost query is marked with this ID
SUBQUERYSubqueries are included in the SELECT or WHERE list
DERIVEDThe 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
UNIONIf 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 RESULTGet the SELECT of the result from the UNION table

explain table

Show which table the data in this row is about

explain type

typemeaning
NULLMySQL does not access any tables or indexes and returns results directly
systemThe 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
constIt 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_refSimilar 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
refNon 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
rangeRetrieve only the rows returned for a given, using an index to select rows. After where, operations such as between, <, >, in appear.
indexThe difference between index and ALL is that index only traverses the index tree, which is usually faster than ALL. ALL traverses the data file.
allThe 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.

extrameaning
using filesortThis 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 temporaryTemporary 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 indexIt indicates that the corresponding select operation uses the overlay index to avoid accessing the data rows of the table, which is efficient.

Topics: Database MySQL SQL explain