17.mysql高级_explain

最新推荐文章于 2023-04-03 17:07:32 发布

原创最新推荐文章于 2023-04-03 17:07:32 发布 · 118 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #sql #数据库

尚硅谷周阳讲面试题专栏收录该内容

19 篇文章

订阅专栏

本文深入探讨了MySQL的查询优化技巧，通过示例解释了如何利用EXPLAIN分析查询执行计划。内容包括创建表、插入数据以及不同ID值对查询顺序的影响，展示了子查询和连接查询的执行顺序与效率。通过对执行计划的分析，读者可以更好地理解查询优化的重要性。

建表

 CREATE TABLE t1(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id)); 
 CREATE TABLE t2(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id)); 
 CREATE TABLE t3(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
 CREATE TABLE t4(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));   
 INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000)));  
 INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000)));   
 INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000)));      
 INSERT INTO t4(content) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)));

id介绍
id相同，执行顺序由上至下

EXPLAIN select t2.* from t1,t2,t3
where t1.id = t2.id and t1.id = t3.id
and t1.content ='';

+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL             |    1 |   100.00 | Using where |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | atguigu_db.t1.id |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | t3    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | atguigu_db.t1.id |    1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

EXPLAIN select t2.* from t2 where id =(
	select id from t2 where id = (
		select t3.id from t3 where t3.content='')
	);

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | PRIMARY     | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
|  2 | SUBQUERY    | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
|  3 | SUBQUERY    | t3    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where                    |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+

explain select t2.* from 
(select t3.id from t3 where t3.content = '') s1, t2
where s1.id = t2.id;

id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行衍生表 = derived2 --> derived + 2 （2 表示由 id =2 的查询衍生出来的表。type 肯定是 all ，因为衍生的表没有建立索引）

+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE      | t3    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL             |    1 |   100.00 | Using where |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | atguigu_db.t3.id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+