07-MySql 慢查询分析-执行计划篇

MySql执行计划

• 使用EXPLAIN命令可以查看MySql优化器执行SQL的细节，便于分析查询语句或是表结构的性能瓶颈。

一、执行计划的作用

• 查看表的读取顺序。在多表关联查询时，可以看到查询每一张表的顺序
• 查看数据读取操作的操作类型。根据对应的操作类型我们可以知道数据库获取数据的方式，比如是扫描还是走索引。
• 查看索引信息。可以看到MySql可能会用到的索引和实际使用的索引。
• 查看扫描记录的数量。确定是否扫描了数据，扫描的数量是多少(判断是否索引覆盖)

二、使用方式

• 语法：EXPLAIN sql语句。比如

EXPLAIN select * from t_salary;

• 输出：

id	select_type	table	Type	possible_keys	keys	key_len	ref	rows	Extra
1	SIMPLE	t_book	ALL	PRIMARY				3	Using temporary; Using filesort
1	SIMPLE	t_role	ref	bookId,gangsId	bookId	8	dbtest.t_book.id	1
1	SIMPLE	t_gangs	ALL	PRIMARY				4	Using where; Using join buffer (Block Nested Loop)

三、含义分析

• 本小节的数据库数据描述在第四节有介绍

3.1 id

• id列包含一系列用于描述select查询的序列号，表示查询中执行select子句或操作表的顺序。根据id下面的2个原则可以判断出它们的执行顺序。

原则1：id大的比id小的先执行
原则2：id一样的，从上到下依次执行

3.2 select_type

• select_type表示查询类型，有下面几种值

select_type 值	含义
SIMPLE	简单的SELECT语句（不包括UNION操作或子查询操作）
PRIMARY	查询中最外层的SELECT（如两表做UNION或者存在子查询的外层的表操作为PRIMARY，内层的操作为UNION）
UNION	UNION操作中，查询中处于内层的SELECT（内层的SELECT语句与外层的SELECT语句没有依赖关系）
DEPENDENT UNION	UNION操作中，查询中处于内层的SELECT（内层的SELECT语句与外层的SELECT语句有依赖关系）
UNION RESULT	UNION操作的结果，id值通常为NULL
SUBQUERY	子查询中首个SELECT（如果有多个子查询存在）：
DEPENDENT SUBQUERY	子查询中首个SELECT，但依赖于外层的表（如果有多个子查询存在） 注意：会严重消耗性能
DERIVED	被驱动的SELECT子查询（子查询位于FROM子句）
MATERIALIZED	被物化的子查询

3.3 table

• 表名或者表别名

3.4 Type

• 不同的Type代表的含义不同，(有点类似于MongoDB执行计划里面的stage)。一般需要得保证查询达到range级别，最好能达到ref。

Type值	含义
system	表只有一行记录
const	索引一次找到
eq_ref	唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref	非唯一性索引扫描，返回匹配某个单独值的所有行
range	只检索给定范围的行,使用一个索引来选择行。(好的sql至少应该是range或者以上基本的type)
index	当查询的结果全为索引列的时候，虽然也是全部扫描，但是只查询的索引库，而没有去查询数据。(是全表扫描的一种，是扫描索引文件，不需要扫描数据文件)
ALL	Full Table Scan，将遍历全表以找到匹配的行

3.5 possible_keys

• possible_keys:可能使用的key。如果查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

3.6 keys

• 实际使用的索引。如果为NULL，表示没有使用索引

3.7 key_len

• key_len表示索引使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。
• key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

3.8 ref

• 显示索引的哪一列被使用了

3.9 rows

• 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

3.10 extra

• 包含不适合在其他列中显示但十分重要的额外信息。

值	含义
Using filesort	mysql无法通过索引进行排序(此时成为文件排序)，使用一个外部的排序索引
Using temporary	使用了临时表保存中间结果(mysql排序时使用临时表，常见于order by或者group by)
Using index	是否使用了索引
Using where	表示使用了where过滤
Using join buffer	使用了连接缓存
Impossible where	where子句的值总是false

四、示例

• 本节只对比较关键的几个字段做简单的示例

4.1 数据库

• 数据库包含3张表。

表名称	数据描述
t_book	保存书本信息
t_gangs	保存帮派信息
t_role	保存人物信息

• 人物通过2个外键和书本以及帮派表关联

4.2 示例一

• sql

-- 查询和“令狐冲”在一个帮派的其他人
EXPLAIN SELECT
	t_role.name
FROM
	t_role
WHERE
	t_role.gangsId = (
		SELECT
			t_role.gangsId
		FROM
			t_role
		WHERE
			t_role. NAME = "令狐冲"
	);

• 结果看到会先执行id为2的子查询部分；外层是PRIMARY，内层是SUBQUERY；使用的表是t_role；主查询的Type是ref表示返回匹配指定值的行，
  子查询为ALL因为子查询是查找名字为令狐冲的记录，但是name没有索引，因此是扫描全表去找记录；子查询没有使用索引，主查询使用了gangsId，
  因为gangsId是外键有索引；row字段因为子查询是全表扫描，表中包含13条记录，主查询因为已经找到了“令狐冲”对应的gangsId，主查询根据
  gangsId这个索引就找到了3条目标数据；Extra表示主查询和子查询都使用了where过滤。

4.3 示例二

• sql

-- 查询武力值比余沧海高的人
EXPLAIN SELECT
	t_role. NAME,t_role.power
FROM
	t_role
WHERE
	t_role.power > (
		SELECT
			t_role.power
		FROM
			t_role
		WHERE
			t_role. NAME = "余沧海"
	);

• 在name和powder上没有创建索引之前，执行计划是这样的。我们看到主查询和子查询都是ALL全表扫描，因此这个是不好的，我们前面提到至少都要是range级别，最好能达到ref。
  我们分析子查询条件是name，因此需要name的索引，主查询是需要name和power这两个字段，为了能够索引覆盖，我们在name和power建立联合索引，在查询。
  

• 在name和powder上创建联合索引之后，执行计划是这样的。我们看到子查询Type是ref代表返回匹配某个单独值的所有行，直接只查询一条记录就找到了目标记录，
  主查询则是扫描索引，但是却扫描了13条记录，为什么呢？我们看到主查询使用的是name_power的联合索引，但是查询条件实际上是power > xx ,这样的话是貌似无法直接命中记录。
  这里我们可以回顾B+数的特点，索引非叶子节点保存了name+power字段，但是我们的条件不是name而且power，因而这是看起来是索引覆盖了，时间上扫描了整颗索引树。

• 如果在上一步的基础之上，将select的t_role. NAME字段去掉，只查询power，那么就是下面的效果，我们看到主查询也是有了索引，因为主查询使用power这个索引去查数据，
  并且select的字段就是索引字段，做到了索引覆盖，只扫描了6条记录，是比较好的。

五、参考

• [1] MySQL创建数据表并建立主外键关系
• [2] mysql 查询优化 ~explain解读之select_type的解读
• [3] MySQL_执行计划详细说明
• [4] mysql的sql执行计划详解（非常有用）

六、附数据库信息

• SQL建表语句

/*
Navicat MySQL Data Transfer

Source Server         : 192.168.31.147
Source Server Version : 50639
Source Host           : 192.168.31.147:3306
Source Database       : dbtest

Target Server Type    : MYSQL
Target Server Version : 50639
File Encoding         : 65001

Date: 2019-06-19 16:29:28
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for t_book
-- ----------------------------
DROP TABLE IF EXISTS `t_book`;
CREATE TABLE `t_book` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL COMMENT '小说名称',
  `description` varchar(30) DEFAULT NULL COMMENT '小说描述',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='t_book 小说表';

-- ----------------------------
-- Records of t_book
-- ----------------------------
INSERT INTO `t_book` VALUES ('1', '倚天屠龙记', '射雕三部曲之三');
INSERT INTO `t_book` VALUES ('2', '射雕英雄传', '射雕三部曲之一');
INSERT INTO `t_book` VALUES ('3', '神雕侠侣', '射雕三部曲之二');
INSERT INTO `t_book` VALUES ('4', '笑傲江湖', '家喻户晓的武侠');
INSERT INTO `t_book` VALUES ('5', '天龙八部', '最好的武侠之一');

-- ----------------------------
-- Table structure for t_gangs
-- ----------------------------
DROP TABLE IF EXISTS `t_gangs`;
CREATE TABLE `t_gangs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `detail` varchar(20) DEFAULT NULL COMMENT '帮派描述',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='t_gangs 帮派表';

-- ----------------------------
-- Records of t_gangs
-- ----------------------------
INSERT INTO `t_gangs` VALUES ('1', '武当派', '位于武当山');
INSERT INTO `t_gangs` VALUES ('2', '少林派', '少室山');
INSERT INTO `t_gangs` VALUES ('3', '峨眉派', '峨眉山');
INSERT INTO `t_gangs` VALUES ('4', '全真教', '活死人墓对面');
INSERT INTO `t_gangs` VALUES ('5', '华山派', '位于华山');
INSERT INTO `t_gangs` VALUES ('6', '青城派', '四川青城山');
INSERT INTO `t_gangs` VALUES ('7', '嵩山派', '五岳剑派之首');
INSERT INTO `t_gangs` VALUES ('8', '丐帮', '天下第一大帮');
INSERT INTO `t_gangs` VALUES ('9', '大理段氏', '云南大理');

-- ----------------------------
-- Table structure for t_role
-- ----------------------------
DROP TABLE IF EXISTS `t_role`;
CREATE TABLE `t_role` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL COMMENT '姓名',
  `age` int(3) DEFAULT '0' COMMENT '年龄',
  `power` int(3) DEFAULT '0' COMMENT '武力',
  `spirit` int(3) DEFAULT '0' COMMENT '精神',
  `bookId` bigint(20) NOT NULL COMMENT '关联的书本id',
  `gangsId` bigint(20) NOT NULL COMMENT '关联的帮派id',
  PRIMARY KEY (`id`),
  KEY `bookId` (`bookId`),
  KEY `gangsId` (`gangsId`),
  KEY `name_power` (`name`,`power`),
  KEY `power` (`power`),
  CONSTRAINT `t_role_ibfk_1` FOREIGN KEY (`bookId`) REFERENCES `t_book` (`id`),
  CONSTRAINT `t_role_ibfk_2` FOREIGN KEY (`gangsId`) REFERENCES `t_gangs` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COMMENT='t_role 角色表';

-- ----------------------------
-- Records of t_role
-- ----------------------------
INSERT INTO `t_role` VALUES ('1', '张无忌', '20', '98', '90', '1', '1');
INSERT INTO `t_role` VALUES ('2', '张三丰', '110', '99', '95', '1', '1');
INSERT INTO `t_role` VALUES ('3', '成昆', '50', '88', '90', '1', '2');
INSERT INTO `t_role` VALUES ('4', '灭绝师太', '48', '85', '90', '1', '3');
INSERT INTO `t_role` VALUES ('5', '王重阳', '55', '97', '95', '2', '4');
INSERT INTO `t_role` VALUES ('6', '尹志平', '44', '78', '75', '3', '4');
INSERT INTO `t_role` VALUES ('7', '令狐冲', '23', '92', '90', '4', '5');
INSERT INTO `t_role` VALUES ('8', '风清扬', '90', '98', '95', '4', '5');
INSERT INTO `t_role` VALUES ('9', '余沧海', '46', '82', '80', '4', '6');
INSERT INTO `t_role` VALUES ('10', '岳不群', '48', '86', '88', '4', '5');
INSERT INTO `t_role` VALUES ('11', '左冷禅', '50', '91', '88', '4', '7');
INSERT INTO `t_role` VALUES ('12', '乔峰', '30', '93', '90', '5', '8');
INSERT INTO `t_role` VALUES ('13', '段誉', '20', '94', '88', '5', '9');

-- ----------------------------
-- Procedure structure for testtt_insert
-- ----------------------------
DROP PROCEDURE IF EXISTS `testtt_insert`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `testtt_insert`()
BEGIN
DECLARE i INT DEFAULT 1;
while i<10000
do
	insert into t_test(name,description) values(concat('name',i),CONCAT('description',i)); 
    set i = i+1;
end while;
commit;
end
;;
DELIMITER ;