MySQL EXPLAIN语句的使用示例
来源:脚本之家
时间:2023-02-25 08:54:17 247浏览 收藏
本篇文章主要是结合我之前面试的各种经历和实战开发中遇到的问题解决经验整理的,希望这篇《MySQL EXPLAIN语句的使用示例》对你有很大帮助!欢迎收藏,分享给更多的需要的朋友学习~
在MySQL优化的环节上,我们首先需要知道的就是我们当前的这句SQL语句在实际的数据库中究竟是怎么执行的,才能谈要如何优化它。而在MySQL中,就给我们提供了模拟语句执行的一个非常好用的关键字:EXPLAIN。EXPLAIN可以用来查看SQL语句的执行效果,可以帮助选择更好的索引和优化查询语句,写出更好的优化语句。因此今天我们就来讲一讲这个关键字的一些基础的用法与应用。
一、使用方法
EXPLAIN的使用方法非常简单:
mysql> EXPLAIN SELECT * FROM user;
简单来说,就是在原有的SQL语句前面加上EXPLAIN关键字,或者说是在EXPLAIN关键字后跟这你要检查的SQL语句。
二、输出结果
EXPLAIN语句的输出结果才是我们想要的数据,也是我们分析的重点。
我们先来看看上面的语句所给到的对应的结果的形式:
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
EXPLAIN语句给到我们的数据总共有10列,接下来我们看一下一些在性能优化上有比较重要作用的数据列所代表的意思。
1.id
这个是select查询的序列号。
2.select_type
当我们的SQL语句是非select语句的时候(即delete,update...),这个字段的值就是对应的操作类型(delete,update...)。
mysql> EXPLAIN INSERT INTO user VAULES(2,'ahong','31');
此时的输出select_type就是我们对应的INSERT:
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | INSERT | user | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
而当SQL语句时select语句的时候,他就是对应的一些详细的select的类型,可以有如下几种:
SIMPLE:简单SELECT(不使用UNION或子查询等) PRIMARY:最外面的SELECT UNION:UNION中的第二个或后面的SELECT语句 DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询 UNION RESULT:UNION的结果。 SUBQUERY:子查询中的第一个SELECT DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询 DERIVED:导出表的SELECT(FROM子句的子查询)
下面就是一个最简单的SIMPLE查询的例子:
mysql> EXPLAIN SELECT * FROM user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
3.table
显示这一步操作所访问的数据是关于哪一张表的。
4.partitions
显示表所使用的分区,如果要统计十年公司订单的金额,可以把数据分为十个区,每一年代表一个区。这样可以大大的提高查询效率。
5.type
这是最重要的一列。显示了连接使用了哪种类别,有无使用索引。是分析查询性能的关键。
结果性能从优到差分别有以下的情况:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
而这几种情况所代表的意义如下:
- system、const: 可以将查询的变量转为常量. 如id=1; id为 主键或唯一键.
- eq_ref: 访问索引,返回某单一行的数据.(通常在联接时出现,查询使用的索引为主键或惟一键)
- ref: 访问索引,返回某个值的数据.(可以返回多行) 通常使用=时发生
- range: 这个连接类型使用索引返回一个范围中的行,比如使用>或
- index: 以索引的顺序进行全表扫描,优点是不用排序,缺点是还要全表扫描
- ALL: 全表扫描,应该尽量避免_
一般来说,得保证查询至少达到range级别,最好能达到ref,否则就可能会出现性能问题。
6.possible_key
显示查询语句有可能会使用到的索引列。取值可能为一个,多个或者null。
7.key
key列显示的是该查询语句实际使用的索引列。如为null,则表示没有使用索引。
展示一下possible_key和key的实际效果:
下面是一个在age列上建立索引的数据表,我们进行以下的查询
mysql> explain select * from user where age = 1;
会得到以下的结果:
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | user | NULL | ref | age | age | 5 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
8.key_len
显示的是当前的查询语句所使用的索引的长度。在不损失精确性的情况下,长度越短越好.
9.ref
引用到的上一个表的列。
10.rows
根据表的情况和查询语句的情况,MySQL会估算出返回最终结果所必须检查的行的数量。该列的值越大查询效率越差。
11.filtered
一个百分比的值,和rows 列的值一起使用,可以估计出查询执行计划(QEP)中的前一个表的结果集,从而确定join操作的循环次数。小表驱动大表,减轻连接的次数。
12.extra
关于MySQL如何解析查询的额外信息,主要有以下几种:
Extra中包含的值:
- using index: 只用到索引,可以避免访问表,性能很高。
- using where: 使用到where来过滤数据, 不是所有的where clause都要显示using where. 如以=方式访问索引。
- using tmporary: 用到临时表去处理当前的查询。
- using filesort: 用到额外的排序,此时mysql会根据联接类型浏览所有符合条件的记录,并保存排序关键字和行指针,然后排序关键字并按顺序检索行。(当使用order by v1,而没用到索引时,就会使用额外的排序)。
- range checked for eache record(index map:N): 没有好的索引可以使用。
- Using index for group-by:__表明可以在索引中找到分组所需的所有数据,不需要查询实际的表。explain select user_id from t_order group by user_id;_
理论要掌握,实操不能落!以上关于《MySQL EXPLAIN语句的使用示例》的详细介绍,大家都掌握了吧!如果想要继续提升自己的能力,那么就来关注golang学习网公众号吧!
-
246 收藏
-
471 收藏
-
111 收藏
-
424 收藏
-
459 收藏
-
475 收藏
-
266 收藏
-
273 收藏
-
283 收藏
-
210 收藏
-
371 收藏
-
- 前端进阶之JavaScript设计模式
- 设计模式是开发人员在软件开发过程中面临一般问题时的解决方案,代表了最佳的实践。本课程的主打内容包括JS常见设计模式以及具体应用场景,打造一站式知识长龙服务,适合有JS基础的同学学习。
- 立即学习 542次学习
-
- GO语言核心编程课程
- 本课程采用真实案例,全面具体可落地,从理论到实践,一步一步将GO核心编程技术、编程思想、底层实现融会贯通,使学习者贴近时代脉搏,做IT互联网时代的弄潮儿。
- 立即学习 507次学习
-
- 简单聊聊mysql8与网络通信
- 如有问题加微信:Le-studyg;在课程中,我们将首先介绍MySQL8的新特性,包括性能优化、安全增强、新数据类型等,帮助学生快速熟悉MySQL8的最新功能。接着,我们将深入解析MySQL的网络通信机制,包括协议、连接管理、数据传输等,让
- 立即学习 497次学习
-
- JavaScript正则表达式基础与实战
- 在任何一门编程语言中,正则表达式,都是一项重要的知识,它提供了高效的字符串匹配与捕获机制,可以极大的简化程序设计。
- 立即学习 487次学习
-
- 从零制作响应式网站—Grid布局
- 本系列教程将展示从零制作一个假想的网络科技公司官网,分为导航,轮播,关于我们,成功案例,服务流程,团队介绍,数据部分,公司动态,底部信息等内容区块。网站整体采用CSSGrid布局,支持响应式,有流畅过渡和展现动画。
- 立即学习 484次学习