通過explain分析select語句的執行計劃

本文主要給大家介紹通過explain分析select語句的執行計劃，文章內容都是筆者用心摘選和編輯的，具有一定的針對性，對大家的參考意義還是比較大的，下面跟筆者一起了解下通過explain分析select語句的執行計劃吧。

explain分析執行計劃

通過以上步驟定位到有問題的sql語句以后我們可以通過explain來分析一下該select語句的執行計劃,包括該語句如何連接和連接的順序

Explain select * from user where id = 1;

這個命令可以顯示select * from user where id = 1這個查詢語句的執行計劃

Id:select查詢的序列號,表示select查詢表的順序。

Select_type:表示select的類型,常見的取值有SIMPLE(簡單表,即不用表連接和子查詢),PRIMARY(主查詢)即有子查詢的最外層查詢，UNION(union查詢中的第二個或后面的查詢語句),SUBQUERY（子查詢中第一個select）等

Table:查詢的表

Type:表連接的類型性能由好到差為:system->const->eq_ref->ref->ref_or_null->index_merge->index_subquery->range->index->all

possible_keys:查詢時可能用到的索引

key:實際使用的索引

key_len:索引字段的長度

rows:掃描行的數量

extra:執行情況的說明和描述

下面通過一個例子詳細說明一下：

創建用戶表，角色表，用戶角色對應表并插入數據

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','超級管理員');

insert into `t_user` (`id`, `username`, `password`, `name`) values('2','admin','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','系統管理員');

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','學生1');

insert into `t_user` (`id`, `username`, `password`, `name`) values('5','stu2','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','學生2');

insert into `t_user` (`id`, `username`, `password`, `name`) values('6','t1','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','老師1');

INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('5','學生','student','學生');

INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('7','老師','teacher','老師');

INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('8','教學管理員','teachmanager','教學管理員');

INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('9','管理員','admin','管理員');

INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('10','超級管理員','super','超級管理員');

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:

1)    id 相同表示加載表的順序是從上到下

explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id ;

在這里先加載t_role表再加載t_user,最后加載user_role

2)    id 不同id值越大，優先級越高，越先被執行

EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'))

這里t_user最先被加載然后是user_role,最后是t_role

3)    id 有相同，也有不同，同時存在。id相同的可以認為是一組，從上往下順序執行；在所有的組中，id的值越大，優先級越高，越先執行。

EXPLAIN SELECT * FROM t_role r , (SELECT * FROM user_role ur WHERE ur.`user_id` = '2') a WHERE r.id = a.role_id ;

explain 之 select_type：

  表示select的類型有以下取值：

SIMPLE:簡單的查詢也就是不包含子查詢和union

PRIMARY:若查詢中包含子查詢那么父查詢用該標志

SUBQUERY:表示子查詢

DERIVED:表示從from子查詢查詢出來的結果形成的臨時表中查詢

UNION:聯合查詢中的UNION后的select語句

UNIONRESULT:通過UNION聯合的語句

explain 之 table：

表示這個查詢是針對哪張表的

explain 之 type：

type表示訪問類型是很重要的指標可取值為：

NULL：MySQL不訪問任何表和索引，直接返回結果

例如: SELECT NOW();

System:表只有一行記錄，一般不會出現

Const:表示通過索引查詢，且只返回一條記錄，此處索引指的是主鍵索引和唯一索引。

Eq_ref:多表關聯查詢，并且查詢出來的數據只有一條

Ref:根據非唯一性索引查詢，查詢出來的結果有多條

Range:范圍掃描，where之后的between,>,<,=等等

Index:表示遍歷整個索引樹

All:會遍歷全表

查詢效率由高到低依次是: system > const > eq_ref > ref > range > index > ALL

一般來說我們需要讓它達到range或更高

explain 之 key

possible_keys:可能應用的索引，一個或多個

key:實際使用的索引，如果為空則沒有使用索引

key_len:索引中使用的字節數，為索引字段最大可能的長度，長度越短越好

explain 之 rows

掃描行的數量

explain 之 extra

表示其它額外執行的信息

using filesort：表示對沒有建立索引的字段進行排序。

using temporary：使用臨時表保存中間結果，常用語group by語句

using index:表示select操作使用了索引

看完以上關于通過explain分析select語句的執行計劃，很多讀者朋友肯定多少有一定的了解，如需獲取更多的行業知識信息 ，可以持續關注我們的行業資訊欄目的。