devbox
Li_阴宅
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

MySQL的EXPLAIN用法_mysql explain的使用语法

作者：Li_阴宅 | 2024-06-25 04:57:25

赞

踩

mysql explain的使用语法

引言：在处理大量数据的情况下，优化查询语句的性能是至关重要的。为了帮助开发人员优化查询，MySQL提供了一个非常有用的工具，即EXPLAIN。EXPLAIN是MySQL用于分析查询语句执行计划的命令。通过分析查询语句的执行计划，开发人员可以了解到查询是如何被MySQL引擎处理的，可以发现查询语句的性能瓶颈，进而进行优化。

image.png

一、EXPLAIN语法

  EXPLAIN语句的基本语法如下：

sql复制代码EXPLAIN SELECT * FROM table_name WHERE condition;

  其中，table_name是查询的表名，condition是查询的条件。需要注意的是，EXPLAIN只能用于SELECT查询语句。我们来看一下EXPLAIN的输出结果。执行上述命令后，MySQL会返回一张表格，包含多个字段信息，如下所示：

image.png

二、输出结果的含义

  执行EXPLAIN语句后，MySQL会返回一组结果，每一行表示查询语句的执行计划中的一个步骤。以下是输出结果中的一些重要字段的含义：

  1. id：表示查询语句中的每个步骤的唯一标识符。
  2. select_type：表示查询的类型，常见的类型包括SIMPLE、PRIMARY、UNION、SUBQUERY等。
  3. table：表示该步骤所涉及的表。
  4. partitions：表示该步骤所涉及的分区。
  5. type：表示访问表的方式，常见的类型包括ALL、index、range、ref、eq_ref、const等。
  6. possible_keys：表示可能被使用的索引。
  7. key：表示实际被使用的索引。
  8. key_len：表示索引的长度。
  9. ref：表示连接条件使用的索引。
  10. rows：表示在该步骤中需要扫描的行数目。
  11. filtered：表示在该步骤中过滤掉的行数比例。
  12. Extra：表示额外的信息，例如是否使用临时表、是否使用了文件排序等。

  通过分析EXPLAIN的输出结果，我们可以判断查询语句的性能瓶颈所在，并进行相应的优化。以下是一些常见的优化技巧：

  使用合适的索引：我们可以通过观察possible_keys和key字段来判断是否使用了合适的索引。如果possible_keys和key字段不同，说明查询语句可能需要优化索引的使用。

  显示可能应用在这张表中的索引，一个或多个。

  查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用.

image.png

  减少全表扫描：如果type字段为ALL，表示查询语句需要进行全表扫描，这是非常低效的操作。此时，可以尝试添加适当的索引来提高查询性能。

go复制代码type类型从快到慢：`system` > `const` > `eq_ref` > `ref` > `range` > `index` > `ALL`

  减少文件排序：如果Extra字段中显示Using filesort，表示查询语句需要进行文件排序，这会消耗大量的计算资源。可以通过添加合适的索引或使用覆盖索引来避免文件排序。

  减少临时表的使用：如果Extra字段中显示Using temporary，表示查询语句需要使用临时表来辅助计算，这会增加额外的开销。可以通过优化查询语句的逻辑或添加合适的索引来减少临时表的使用。

  优化连接操作：如果type字段为index、range、ref、eq_ref等，表示查询语句需要进行连接操作。可以通过添加合适的索引或优化连接条件来提高性能。

  其他参数:除了基本的语法和输出结果，EXPLAIN还支持一些其他参数，用于进一步优化查询语句的性能。常见的参数包括：

  1. vbnet复制代码format：用于指定输出格式，支持的格式包括traditional和json。默认值为traditional。
  2. extended：用于显示更详细的输出信息，包括扫描的行数、使用的索引类型等。默认值为OFF。
  3. analyze：用于在执行EXPLAIN之前自动执行ANALYZE TABLE语句。默认值为OFF。
  4. partitions：用于指定是否显示关于分区表的信息。默认值为OFF。

三、详细解读

（1）关于type详解

   system

  表中只有一行记录（系统表）。是const类型的一个特殊情况。（目前InnoDB已经没有，在MyISAM可以）。

  1. sql复制代码CREATE TABLE `user_innodb` (
  2.   `id` int NOT NULL AUTO_INCREMENT,
  3.   `name` varchar(32) NOT NULL,
  4.   PRIMARY KEY (`id`)
  5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  6. insert into user_innodb(name) values('tenmao');
  7.  
  8. CREATE TABLE `user_myiasm` (
  9.   `id` int NOT NULL AUTO_INCREMENT,
  10.   `name` varchar(32) NOT NULL,
  11.   PRIMARY KEY (`id`)
  12. ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
  13. insert into user_myiasm(name) values('tenmao');
  14.  
  15. mysql> explain select * from user_innodb;
  16. +----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
  17. | id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
  18. +----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
  19. |  1 | SIMPLE      | user_innodb | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
  20. +----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
  21. 1 row in set, 1 warning (0.01 sec)
  22.  
  23. mysql> explain select * from user_myiasm;
  24. +----+-------------+-------------+------------+--------+---------------+------+---------+------+------+----------+-------+
  25. | id | select_type | table       | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
  26. +----+-------------+-------------+------------+--------+---------------+------+---------+------+------+----------+-------+
  27. |  1 | SIMPLE      | user_myiasm | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
  28. +----+-------------+-------------+------------+--------+---------------+------+---------+------+------+----------+-------+
  29. 1 row in set, 1 warning (0.00 sec)

   const

  表中最多只有一行匹配的记录。一般用在主键索引或者唯一键索引上的等值查询（如果是多字段索引，则需要全匹配）。

  1. sql复制代码CREATE TABLE `user` (
  2.   `id` int NOT NULL AUTO_INCREMENT,
  3.   `name` varchar(32) NOT NULL,
  4.   `email` varchar(32) NOT NULL,
  5.   PRIMARY KEY (`id`),
  6.   UNIQUE KEY `idx_email` (`email`),
  7.   KEY `idx_name` (`name`)
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  9. insert into user(name, email) values('tenmao', 'tenmao@example.com');
  10. explain select * from user where id=1;
  11. explain select * from user where email='tenmao@example.com';

   eq_ref

  跨表join时，对于驱动表的每一行记录，被动表最多只会读取一行记录。跟单表查询不一样（system，const最快），在跨表查询中，eq_ref是最好的。

  1. sql复制代码CREATE TABLE `email_msg` (
  2.   `id` int NOT NULL AUTO_INCREMENT,
  3.   `email` varchar(32) NOT NULL,
  4.   `title` varchar(128) NOT NULL,
  5.   `content` text NOT NULL,
  6.   PRIMARY KEY (`id`),
  7.   KEY `idx_email` (`email`)
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  9. insert into email_msg(email, title, content) values('tenmao@example.com', 'email title', 'email content');
  10. mysql> explain select email_msg.* from email_msg join user using(email);
  11. +----+-------------+-----------+------------+--------+---------------+-----------+---------+--------------------------+------+----------+-------------+
  12. | id | select_type | table     | partitions | type   | possible_keys | key       | key_len | ref                      | rows | filtered | Extra       |
  13. +----+-------------+-----------+------------+--------+---------------+-----------+---------+--------------------------+------+----------+-------------+
  14. |  1 | SIMPLE      | email_msg | NULL       | ALL    | idx_email     | NULL      | NULL    | NULL                     |    1 |   100.00 | NULL        |
  15. |  1 | SIMPLE      | user      | NULL       | eq_ref | idx_email     | idx_email | 130     | aics_tim.email_msg.email |    1 |   100.00 | Using index |
  16. +----+-------------+-----------+------------+--------+---------------+-----------+---------+--------------------------+------+----------+-------------+

   ref

  等值查询，但是可能匹配大于1行记录。比如普通的非唯一索引，或者联合主键和联合唯一索引的左前缀匹配（非全匹配）。

  1. sql复制代码mysql> explain select * from user where name='tenmao';
  2. +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
  3. | id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
  4. +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
  5. |  1 | SIMPLE      | user  | NULL       | ref  | idx_name      | idx_name | 130     | const |    1 |   100.00 | NULL  |
  6. +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+

   ref_or_null

  与ref类似，但是条件中多了一个IS NULL判断。

  1. sql复制代码alter table user add address varchar(128) default null;
  2. alter table user add index idx_address(address);
  3. mysql> explain select * from user where address='hello' or address is null;
  4. +----+-------------+-------+------------+-------------+---------------+-------------+---------+-------+------+----------+-----------------------+
  5. | id | select_type | table | partitions | type        | possible_keys | key         | key_len | ref   | rows | filtered | Extra                 |
  6. +----+-------------+-------+------------+-------------+---------------+-------------+---------+-------+------+----------+-----------------------+
  7. |  1 | SIMPLE      | user  | NULL       | ref_or_null | idx_address   | idx_address | 515     | const |    2 |   100.00 | Using index condition |
  8. +----+-------------+-------+------------+-------------+---------------+-------------+---------+-------+------+----------+-----------------------+

   range

  范围查询，一般用在BETWEEN, LIKE, >, <等。

  1. sql复制代码mysql> explain select * from user where name like 'tenmao';
  2. +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
  3. | id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
  4. +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
  5. |  1 | SIMPLE      | user  | NULL       | range | idx_name      | idx_name | 130     | NULL |    1 |   100.00 | Using index condition |
  6. +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+

   index

  扫描整个索引，效率很低，仅仅因为辅助索引的空间比主键索引小，所以比ALL效率高一点。最常用的有SELECT COUNT(*)。

  1. sql复制代码mysql> explain select count(*) from user;
  2. +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
  3. | id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
  4. +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
  5. |  1 | SIMPLE      | user  | NULL       | index | NULL          | idx_email | 130     | NULL |    1 |   100.00 | Using index |
  6. +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

   ALL

  全表扫描，这种情况就需要优化了。

  3.2.使用示例

  我们首先创建一个自定义注解 @ValidName，用于校验字符串是否为有效的名称。

（2）select_type详解

   simple

  

 简单表，不使用union或者子查询，单表的select_type，不使用union和子查询。

image.png

  表连接查询的select_type，不使用union和子查询。

image.png

   primary

  主查询，外层的查询。

image.png

   subquery

   select、where之后包含了子查询，在select语句中出现的子查询语句，结果不依赖于外部查询（不在from语句中）。

image.png

   dependent subquery

   指在select语句中出现的查询语句，结果依赖于外部查询。

image.png

   derived

   派生表。

image.png

   union

   union result：union关键字会将数据结果进行去重，会使用一个临时表，临时表的记录会被标记为union result。

image.png

   union all

image.png

   dependent union

image.png

四、总结

  以上是关于MySQL的EXPLAIN用法的详细介绍。通过掌握EXPLAIN的语法和输出结果的含义，我们可以更好地理解和优化查询语句的执行计划，提高数据库的性能和效率。希望本文对你有所帮助！

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Li_阴宅/article/detail/755110
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号