Mysql的Explain使用

Explain

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MYSQL是如何处理SQL语句的,可以用来分析查询语句或是表的结构的性能瓶颈。

列名	描述
id	在一个大的查询语句中每个SELECT关键字都对应一个 唯一的id
select_type	SELECT关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法（重要）
possible_keys	可能用到的索引
key	实际上使用的索引（重要）
key_len	实际使用到的索引长度（重要）
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数（重要）
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息（重要）

数据准备

创建表

CREATE TABLE s1 (
	id INT AUTO_INCREMENT,
	key1 VARCHAR(100), 
	key2 INT, 
	key3 VARCHAR(100), 
	key_part1 VARCHAR(100),
	key_part2 VARCHAR(100),
	key_part3 VARCHAR(100),
	common_field VARCHAR(100), --普通字段
	PRIMARY KEY (id), --聚簇索引
	INDEX idx_key1 (key1), --普通索引
	UNIQUE INDEX idx_key2 (key2), --唯一索引
	INDEX idx_key3 (key3), --普通索引
	INDEX idx_key_part(key_part1, key_part2, key_part3) --联合索引
) ENGINE=INNODB CHARSET=utf8;

CREATE TABLE s2 (
    id INT AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    INDEX idx_key1 (key1),
    UNIQUE INDEX idx_key2 (key2),
    INDEX idx_key3 (key3),
	INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

设置参数 

创建函数，若报错，需开启如下命令：允许创建函数设置 ，因为默认情况下不信任对函数创建的影响Bin log文件

set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。

 创建函数

DELIMITER //
CREATE FUNCTION rand_string1 ( n INT ) 
	RETURNS VARCHAR ( 255 ) #该函数会返回一个字符串
BEGIN
	DECLARE
		chars_str VARCHAR ( 100 ) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
	DECLARE
		return_str VARCHAR ( 255 ) DEFAULT '';
	DECLARE
		i INT DEFAULT 0;
	WHILE
			i < n DO
			
			SET return_str = CONCAT(
				return_str,
			SUBSTRING( chars_str, FLOOR( 1+RAND ()* 52 ), 1 ));
		
		SET i = i + 1;
		
	END WHILE;
	RETURN return_str;
	
END // 
DELIMITER ;

创建存储过程

DELIMITER //
CREATE PROCEDURE insert_s1 (IN min_num INT (10),IN max_num INT (10))
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERT INTO s1 VALUES(
    (min_num + i),
    rand_string1(6),
    (min_num + 30 * i + 5),
    rand_string1(6),
    rand_string1(10),
    rand_string1(5),
    rand_string1(10),
    rand_string1(10));
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END //
DELIMITER ;

 创建往s2表中插入数据的存储过程：

DELIMITER //
CREATE PROCEDURE insert_s2 (IN min_num INT ( 10 ),IN max_num INT ( 10 )) 
BEGIN
	DECLARE i INT DEFAULT 0;
	SET autocommit = 0;
	REPEAT
 	SET i = i + 1;
		INSERT INTO s2 VALUES(
				( min_num + i ),
				rand_string1 ( 6 ),
				( min_num + 30 * i + 5 ),
				rand_string1 ( 6 ),
				rand_string1 ( 10 ),
				rand_string1 ( 5 ),
				rand_string1 ( 10 ),
				rand_string1 ( 10 ));
		UNTIL i = max_num 
	END REPEAT;
	COMMIT;
	
END // 
DELIMITER ;

调用存储过程  

s1表数据的添加：加入1万条记录：

CALL insert_s1(10001,10000); # id 10002~20001

s2表数据的添加：加入1万条记录：

CALL insert_s2(10001,10000);# id 10002~20001

table

查询的每一行记录都对应着一个单表

-- s1:驱动表  s2:被驱动表
EXPLAIN SELECT * FROM s1 INNER JOIN s2;
-- 驱动表和被驱动表是 优化器决定的，他认为哪个比较好就用哪个

id

正常来说一个select 一个id，id不同时，id的序号会递增，id的值越大优先级越高，则先被执行 

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';

总结:

• id如果相同，可以认为是一组，从上往下顺序执行

• 在所有组中，id值越大，优先级越高，越先执行

• 关注点：id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好

select_type

MySQL为每一个SELECT关键字代表的小查询都定义了一个称之为select_type的属性，意思是我们只要知道了某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色

 # 查询语句中不包含`UNION`或者子查询的查询都算作是`SIMPLE`类型
 EXPLAIN SELECT * FROM s1;
 
  #连接查询也算是`SIMPLE`类型
 EXPLAIN SELECT * FROM s1 INNER JOIN s2;

partitions (可略)

 代表分区表中的命中情况，非分区表，该项为NULL。一般情况下我们的查询语句的执行计划的partitions列的值都是NULL

type(重要)

 连接的类型，常⻅的类型有（性能从好到差）

 完整的访问方法如下：system > const > eq_ref > ref >fulltext > ref_or_null > index_merge >unique_subquery > index_subquery > range > index > ALL 

SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴 开发手册要求

system

当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是system

-- 这里如果是 innodb 会变成ALL,因为innodb系统不会存条数字段,MyISAM会存储这么一个字段
EXPLAIN SELECT * FROM t;

const

通过索引⼀次找到，通常在⽤主键或唯⼀索引时出现

 EXPLAIN SELECT * FROM s1 WHERE id = 10005;

eq_ref

⽤主键或唯⼀索引字段作为连接表条件 

explain select t1.*,t2.* from t1 join t2 on t1.id = t2.id;

 ref

⽤普通索引的字段作为连接表条件

 --当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是`ref`
 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

range

-- 如果使用索引获取某些`范围区间`的记录，那么就可能使用到`range`访问方法
EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');

-- 同上
EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';

index

-- 当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是`index`
EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';

索引覆盖，INDEX idx_key_part(key_part1, key_part2, key_part3） 这3个构成一个复合索引

key_part3 在复合索引里面，，查询的字段也在索引里面，干脆就直接遍历索引查出数据

ALL  

-- 这些访问方法中除了`All`这个访问方法外，其余的访问方法都能用到索引
EXPLAIN SELECT * FROM s1;

possible_keys和key

-- possible keys和key: 可能用到的索引 和 实际上使用的索引
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';

key_len(重要)

• key_len：实际使用到的索引长度(即：字节数)

• key_len越小 索引效果越好 这是前面学到的只是，短一点效率更高

• 但是在联合索引里面，命中一次key_len加一次长度。越长代表精度越高，效果越好

EXPLAIN SELECT * FROM s1 WHERE id = 10005;
-- 结果key_len =4

EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;
-- 结果key_len = 5,原因key2是int类型 unique 索引。。因为还可能有一个null值，所以 null占一个字段。4+1 = 5

ref

​ 当使⽤索引等值查询时，与索引作⽐较的列或常量

-- 类型是type =eq_ref ， 与 rapid_index.s1.id   比较
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

 

-- 与一个方法比较`func
 EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);

rows(重要)

​ 预计扫描的⾏数

 # 9. rows：预估的需要读取的记录条数
 # `值越小越好`
 # 通常与filtered 一起使用
 EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';

 

filtered

filtered 的值指返回结果的行占需要读到的行(rows 列的值)的百分比

Extra（重要）

​ Extra列是用来说明一些额外信息的，包含不适合在其他列中显示但十分重要的额外信息

Using where

当我们使用全表扫描来执行对某个表的查询，并且该语句的WHERE子句中有针对该表的搜索条件时，在Extra列中会提示上述额外信息。

No matching min/max row

当查询列表处有MIN或者MAX聚合函数，但是并没有符合WHERE子句中的搜索条件的记录时，将会提示该额外信息

Using index

当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用覆盖索引的情况下，在Extra列将会提示该额外信息。

比方说下边这个查询中只需要用到idx_key1而不需要回表操作：