mysql和explain哪个好(MySQL EXPLAIN输出列的详细解释)
mysql和explain哪个好
MySQL EXPLAIN输出列的详细解释1. 简介
EXPLAIN语句提供有关 MySQL 如何执行语句的信息。
EXPLAIN与SELECT、DELETE、INSERT、REPLACE和UPDATE语句一起使用。
|
mysql> EXPLAIN SELECT * FROM employees WHERE emp_no = 10001; +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | employees | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set , 1 warning (0.00 sec) |
简单来讲,通过EXPLAIN可以分析出SQL语句走没走索引,走的是什么索引。
EXPLAIN为SELECT语句中使用的每个表返回一行信息,它按照 MySQL 在处理语句时读取它们的顺序列出了输出中的表。
MySQL 使用嵌套循环连接(Nested-Loop Join Algorithms)解析所有连接,这意味着 MySQL 从第一个表中读取一行,然后在第二个表,第三个表中找到匹配的行,依此类推。处理完所有表后,MySQL将通过表列表输出选定的列后回溯直到找到一个表,其中存在更多匹配的行。从该表中读取下一行,然后继续下一个表。
2.EXPLAIN 输出列
- MySQL版本 5.7.33
- Windows10 64位
从上图看到 EXPLAIN 的结果中,包括的表头id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra,这些字段的意思我们来学习然后通过实例进行了解一下。
2.1 id
SELECT 标识符,查询中 SELECT 的顺序号。如果该行引用其他行的并集结果,则该值可以为NULL。在这种情况下,表列显示类似<unionM,N>的值,以指示该行引用 id 值为 M 和 N 的行的并集。
id 值分三种情况:
id 相同,执行顺序由上至下
|
mysql> EXPLAIN ( -> SELECT * FROM employees emp -> LEFT JOIN dept_emp de ON emp.emp_no = de.emp_no -> LEFT JOIN departments dept ON dept.dept_no = de.dept_no -> WHERE emp.emp_no = 10001); + ----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + ----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------+ | 1 | SIMPLE | emp | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | de | NULL | ref | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | dept | NULL | eq_ref | PRIMARY | PRIMARY | 12 | employees.de.dept_no | 1 | 100.00 | NULL | + ----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------+ 3 rows in set , 1 warning (0.03 sec) |
id不相同,如果是子查询,id的序号会递增,id的值越大被执行的优先级越高
|
mysql> EXPLAIN SELECT * FROM employees emp -> WHERE emp.emp_no NOT IN ( SELECT de.emp_no FROM dept_emp de -> WHERE de.dept_no NOT IN ( SELECT dept_no FROM departments WHERE dept_name = 'Development' )); + ----+-------------+-------------+------------+-------+-------------------+-----------+---------+-------+--------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + ----+-------------+-------------+------------+-------+-------------------+-----------+---------+-------+--------+----------+--------------------------+ | 1 | PRIMARY | emp | NULL | ALL | NULL | NULL | NULL | NULL | 299468 | 100.00 | Using where | | 2 | SUBQUERY | de | NULL | index | PRIMARY | dept_no | 12 | NULL | 308493 | 100.00 | Using where ; Using index | | 3 | SUBQUERY | departments | NULL | const | PRIMARY ,dept_name | dept_name | 122 | const | 1 | 100.00 | Using index | + ----+-------------+-------------+------------+-------+-------------------+-----------+---------+-------+--------+----------+--------------------------+ 3 rows in set , 1 warning (0.00 sec) |
id相同和不相同都存在
如果id相同可以认为是一组,同一组id执行顺序由上至下,不同组之间,id值越大被执行的优先级越高。
|
mysql> EXPLAIN SELECT * FROM employees emp -> WHERE emp.emp_no IN ( SELECT de.emp_no FROM dept_emp de -> WHERE de.dept_no IN ( SELECT dept_no FROM departments WHERE dept_name LIKE '%Develop%' )); + ----+--------------+-------------+------------+-------+-----------------+-----------+---------+-------------------------------+--------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + ----+--------------+-------------+------------+-------+-----------------+-----------+---------+-------------------------------+--------+----------+----------------------------------------------------+ | 1 | SIMPLE | <subquery2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | NULL | | 1 | SIMPLE | emp | NULL | ALL | PRIMARY | NULL | NULL | NULL | 299468 | 0.00 | Using where ; Using join buffer (Block Nested Loop) | | 2 | MATERIALIZED | departments | NULL | index | PRIMARY | dept_name | 122 | NULL | 9 | 11.11 | Using where ; Using index | | 2 | MATERIALIZED | de | NULL | ref | PRIMARY ,dept_no | dept_no | 12 | employees.departments.dept_no | 38561 | 100.00 | Using index | + ----+--------------+-------------+------------+-------+-----------------+-----------+---------+-------------------------------+--------+----------+----------------------------------------------------+ 4 rows in set , 1 warning (0.01 sec) |
2.2 select_type
查询的类型,主要用来区别普通查询,联合查询,子查询等复杂查询。
包含SIMPLE、PRIMARY、UNION、DEPENDENT UNION、UNION RESULT、SUBQUERY、DEPENDENT SUBQUERY、DERIVED、MATERIALIZED、UNCACHEABLE SUBQUERY、UNCACHEABLE UNION
SIMPLE
简单的SELECT,不使用UNION或子查询。
|
mysql> EXPLAIN select * from employees where emp_no=10001; + ----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + ----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | employees | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | + ----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set , 1 warning (0.00 sec) |
PRIMARY
查询中若包含任何复杂的子部分,最外层的查询则被标记为PRIMARY
|
mysql> EXPLAIN SELECT * FROM employees emp -> WHERE emp.emp_no IN ( SELECT max (emp_no) FROM dept_emp); + ----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + ----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+ | 1 | PRIMARY | emp | NULL | ALL | NULL | NULL | NULL | NULL | 299468 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | + ----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+ 2 rows in set , 1 warning (0.00 sec) |
UNION
第二个或更靠后的 SELECT 语句出现在 UNION 之后,则被标记为 UNION
|
mysql> EXPLAIN ( SELECT emp_no,dept_no FROM dept_emp LIMIT 10) -> UNION -> SELECT emp_no,dept_no FROM dept_manager; + ----+--------------+--------------+------------+-------+---------------+---------+---------+------+--------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + ----+--------------+--------------+------------+-------+---------------+---------+---------+------+--------+----------+-----------------+ | 1 | PRIMARY | dept_emp | NULL | index | NULL | dept_no | 12 | NULL | 308493 | 100.00 | Using index | | 2 | UNION | dept_manager | NULL | index | NULL | dept_no | 12 | NULL | 24 | 100.00 | Using index | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | + ----+--------------+--------------+------------+-------+---------------+---------+---------+------+--------+----------+-----------------+ 3 rows in set , 1 warning (0.00 sec) |
DEPENDENT UNION
与 UNION 相同,它出现在 UNION 或 UNION ALL语句中,但是此查询受外部查询的影响
| UNION RESULT union_result Result of a UNION.
| SUBQUERY None First SELECT in subquery
| DEPENDENT SUBQUERY dependent (true) First SELECT in subquery, dependent on outer query
| DERIVED None Derived table
| MATERIALIZED materialized_from_subquery Materialized subquery
| UNCACHEABLE SUBQUERY cacheable (false) A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
| UNCACHEABLE UNION cacheable (false) The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)
总结
到此这篇关于MySQL EXPLAIN输出列的文章就介绍到这了,更多相关MySQL EXPLAIN输出列内容请搜索开心学习网以前的文章或继续浏览下面的相关文章希望大家以后多多支持开心学习网!
原文链接:https://juejin.cn/post/6960967935724781598?
- mysql死锁是什么原因(MySQL的MaxIdleConns不合理,会变成短连接的原因)
- mysql binlog如何查看(MySQL binlog_ignore_db 参数的具体使用)
- mysql总是报错error(MySQL 5.6主从报错的实战记录)
- mysql基础操作报告(gorm操作MySql数据库的方法)
- mysql怎么看脱机数据(MYSQL电话号码,身份证数据脱敏的实现)
- mysqldecimal类型转换(mysql中decimal数据类型小数位填充问题详解)
- mysql水平拓展的瓶颈(MySQL令人大跌眼镜的隐式转换)
- centos系统mysql安装及连接教程(CentOS8安装SQLServer2019的过程)
- mysql 高级用法(MySQL实现replace函数的几种实用场景)
- 跨服务器mysql数据迁移方案(从云数据迁移服务看MySQL大表抽取模式的原理解析)
- centos如何安装mysql8.0版本(Centos7下安装MySQL8.0.23的步骤小白入门级别)
- mysql查询分区语句(MySQL中 and or 查询的优先级分析)
- mysql触发器如何创建(MySQL 触发器的使用和理解)
- mysql添加注释视图(mysql创建表添加字段注释的实现方法)
- mysql索引b+树和b树(MySQL使用B+Tree当索引的优势有哪些)
- mysql高级变量查询(MySQL 使用自定义变量进行查询优化)
- 《极限挑战》深访都市夜归人,夜间打工者体验,黄磊录完憔悴了(极限挑战深访都市夜归人)
- Google 推出了一个游戏生成器,让不会编程的你也能自己设计游戏(推出了一个游戏生成器)
- 二胎家庭老大爱闹情绪,用这招很有效(二胎家庭老大爱闹情绪)
- 一个30岁男人外遇失败的全过程(一个30岁男人外遇失败的全过程)
- 《无敌破坏王2》 不聊彩蛋,聊聊我从动画里看到的现实那些事儿(无敌破坏王2不聊彩蛋)
- 《寄生虫》 三观不正 人类悲欢从来不相通,感同身受也并非本能(寄生虫三观不正)
热门推荐
- python的def命令参数(python ddt数据驱动最简实例代码)
- mysql触发器入门(MySQL中触发器和游标的介绍与使用)
- mysql子查询用法(MySQL 子查询和分组查询)
- C#中List和数组之间的转换
- yield方法可以使一个线程停止运行(Yii2.0框架模型添加/修改/删除数据操作示例)
- 使用react生命周期的常见情况(react+ts实现简单jira项目的最佳实践记录)
- php调试用什么工具好(调试php程序的简单步骤)
- 阿里云mysql配置升级注意事项(详解如何在阿里云上安装mysql)
- django中的limit用法(如何在Django中添加没有微秒的 DateTimeField 属性详解)
- mysql主从同步原理详解(MySQL双主主主架构配置方案)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9