通過explain分析select語(yǔ)句的執(zhí)行計(jì)劃

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

explain分析執(zhí)行計(jì)劃

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

Explain select * from user where id = 1;

這個(gè)命令可以顯示select * from user where id = 1這個(gè)查詢語(yǔ)句的執(zhí)行計(jì)劃

Id:select查詢的序列號(hào),表示select查詢表的順序。

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

Table:查詢的表

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

possible_keys:查詢時(shí)可能用到的索引

key:實(shí)際使用的索引

key_len:索引字段的長(zhǎng)度

rows:掃描行的數(shù)量

extra:執(zhí)行情況的說明和描述

下面通過一個(gè)例子詳細(xì)說明一下：

創(chuàng)建用戶表，角色表，用戶角色對(duì)應(yīng)表并插入數(shù)據(jù)

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

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

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

insert into `t_user` (`id`, `username`, `password`, `name`) values('5','stu2','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','學(xué)生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','學(xué)生','student','學(xué)生');

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','教學(xué)管理員','teachmanager','教學(xué)管理員');

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

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值越大，優(yōu)先級(jí)越高，越先被執(zhí)行

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 有相同，也有不同，同時(shí)存在。id相同的可以認(rèn)為是一組，從上往下順序執(zhí)行；在所有的組中，id的值越大，優(yōu)先級(jí)越高，越先執(zhí)行。

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:簡(jiǎn)單的查詢也就是不包含子查詢和union

PRIMARY:若查詢中包含子查詢那么父查詢用該標(biāo)志

SUBQUERY:表示子查詢

DERIVED:表示從from子查詢查詢出來的結(jié)果形成的臨時(shí)表中查詢

UNION:聯(lián)合查詢中的UNION后的select語(yǔ)句

UNIONRESULT:通過UNION聯(lián)合的語(yǔ)句

explain 之 table：

表示這個(gè)查詢是針對(duì)哪張表的

explain 之 type：

type表示訪問類型是很重要的指標(biāo)可取值為：

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

例如: SELECT NOW();

System:表只有一行記錄，一般不會(huì)出現(xiàn)

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

Eq_ref:多表關(guān)聯(lián)查詢，并且查詢出來的數(shù)據(jù)只有一條

Ref:根據(jù)非唯一性索引查詢，查詢出來的結(jié)果有多條

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

Index:表示遍歷整個(gè)索引樹

All:會(huì)遍歷全表

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

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

explain 之 key

possible_keys:可能應(yīng)用的索引，一個(gè)或多個(gè)

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

key_len:索引中使用的字節(jié)數(shù)，為索引字段最大可能的長(zhǎng)度，長(zhǎng)度越短越好

explain 之 rows

掃描行的數(shù)量

explain 之 extra

表示其它額外執(zhí)行的信息

using filesort：表示對(duì)沒有建立索引的字段進(jìn)行排序。

using temporary：使用臨時(shí)表保存中間結(jié)果，常用語(yǔ)group by語(yǔ)句

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

看完以上關(guān)于通過explain分析select語(yǔ)句的執(zhí)行計(jì)劃，很多讀者朋友肯定多少有一定的了解，如需獲取更多的行業(yè)知識(shí)信息 ，可以持續(xù)關(guān)注我們的行業(yè)資訊欄目的。