【七千字】教你如何用MySQL分析查询语句Explain

╰半夏微凉° 2024-03-23 14:46 35阅读 0赞

## 分析查询语句：EXPLAIN ##

--------------------

## 1概述 ##

定位了查询慢的SQL之后，就可以使用EXPLAIN或者DESCRIBE工具做针对性的分析查询。两者使用方法相同，并且分析结果也是相同的。

MySQL中有专门负责SQL语句优化的优化器模块，主要功能是计算分析系统中收集到的统计信息，为客户端请求的Query提供它最优的执行计划（**它认为的最优数据检索方案毕竟是自动分析成的，所以不一定是DBA或者开发人员认为的最优方案**）

这个执行计划展示了接下来进行具体查询的方式，比如**多表连接的顺序**、**对每个表采用什么方式进行具体的查询**等等，MySQL提供的EXPLAIN语句可以用来查询某个查询语句的具体执行计划，根据EXPLAIN语句的输出项，可以有针对性地提升查询SQL的性能。

## 能查到什么？ ##

*  表的读取顺序
 *  数据读取操作的操作类型
 *  哪些索引可以被使用
 *  **哪些索引实际被使用**
 *  表之间的引用关系
 *  **每张表有多少行被优化器查询**

## 版本区别 ##

*  MySQL5.6.3之前只能使用EXPLAIN SELECT，之后可以使用EXPLAIN SELECT， UPDATE， DELETE
 *  5.7之前的版本，想要查看partitions（分区）和filtered需要使用EXPLAIN partitions、filtered，而5.7之后直接默认显示

## 数据准备 ##

## 创建表 ##

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

## 创建存储函数 ##

-- 函数返回随机字符串
    DELIMITER //
    
    CREATE FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET utf8mb4
    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 ;

首先要确保相信函数的变量  
log\_bin\_trust\_function\_creators为1

SELECT @@log_bin_trust_function_creators variable;
    
    SET GLOBAL log_bin_trust_function_creators = 1;

## 存储过程 ##

向s1、s2表添加数据的存储过程

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_string(6),
    		(min_num + 30* i + 5),
    		rand_string(6),
    		rand_string(10),
    		rand_string(5),
    		rand_string(10),
    		rand_string(10)
    	);
    	UNTIL i = max_num
    	END REPEAT;
    	COMMIT;
    END //
    DELIMITER;
    
    
    
    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 s1 VALUES(
    		(min_num + i),
    		rand_string(6),
    		(min_num + 30* i + 5),
    		rand_string(6),
    		rand_string(10),
    		rand_string(5),
    		rand_string(10),
    		rand_string(10)
    	);
    	UNTIL i = max_num
    	END REPEAT;
    	COMMIT;
    END //
    DELIMITER;

## 执行存储过程添加数据 ##

CALL insert_s1(10001, 10000);
    CALL insert_s2(10001, 10000);

## Explain的输出列 ##

![b9c1d093e654da833a63da6cc43c2c33.png][]

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

## 1 id ##

id，在一个大的查询语句中每个SELECT关键字都对应着一个唯一的id，所以有几个select关键字就会有几个id：

EXPLAIN SELECT * FROM s1

![5691db83c98a588c40d55b14a394c846.png][]

EXPLAIN SELECT * FROM s1 INNER JOIN s2

![338df29e90c88b9d353763f7a7644516.png][]

> 上面的两个SQL都只有一个select所以只有一个id

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

> 子查询有两个select，所以对应两个id1和2

![e26c8eeef055f314843f3573cdefbc97.png][]

**查询优化器可能会对涉及子查询的查询语句进行重写**：

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a')

![d305a3ef8de5c96de0409997cebd671f.png][]

> 优化器在看到子查询后判断能够变为多表连接以降低复杂度（O(n^2) -> O(n)）:
> 
> SELECT \* FROM s1, s2 ON s1.key1 = s2.key2 WHERE s2.common\_field = ‘a’
> 
> 重写后的sql变成了一个select，所以查询结果仍然是一个id

但是如果s2查的是key1，就会变成下面这样：

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE common_field = 'a')

![3a08d9f7857dc0eba01f145111ad8d07.png][]

**UNION去重**

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

![e72d139e8b1abfc5ecde30c9400d700c.png][]

[b9c1d093e654da833a63da6cc43c2c33.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/5aa212ab7075426bb97219ba2569a1cd.png
[5691db83c98a588c40d55b14a394c846.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/c6c4a0274a124fbaa023cca9cb1f7010.png
[338df29e90c88b9d353763f7a7644516.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/2d7b8cd02ad149238e8515fc933a5ab4.png
[e26c8eeef055f314843f3573cdefbc97.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/711f3434c8e9406db3a3a25dee1c8a6e.png
[d305a3ef8de5c96de0409997cebd671f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/fe7f6ece48ac4c89935e0a56c0206a30.png
[3a08d9f7857dc0eba01f145111ad8d07.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/8a510453565148f5a3363c453124adc3.png
[e72d139e8b1abfc5ecde30c9400d700c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/d326a59f01f44ba08ed90b84fcad6572.png