上一篇我们了解到,SQL语句的执行大部分是SELECT语句,而我们可以通过设置慢查询日志的时间阈值,来找出哪些SQL语句执行耗时超过该阈值。而实际情况下,存在一些SQL语句没有超过阈值,但是比较接近阈值的情况,这些SQL语句实际上也需要优化。这就需要用到我们接下来学习的内容。
- profile详情
show profiles 能够在做SQL优化时帮助我们了解时间都耗费在哪里。通过have_profiling 参数,能够看到当前SQL是否支持profile操作:
SELECT @@have_profiling;
默认 profiling是关闭的,可以通过set语句在session/global级别开启profiling:
# 查看profiling状态,是否打开
SELECT @@profiling;
# 设置profiling,打开
SET profiling = 1;
# 再次查看profiling状态,此时为打开
SELECT @@profiling;
此时,去执行一系列的业务SQL操作,然后通过如下指令查看指令的执行耗时:
# 查看每一条SQL的基本耗时情况
show profiles;
# 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query <query_id>;
# 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query <query_id>;
通过profile,我们能够查看执行的SQL语句的耗时,但是并不能实际的判断SQL语句的性能。
2. explain执行计划
1)EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息,包括在SELECT 语句执行过程中,表如何连接和连接的顺序。其语法如下:
# 直接在SELECT 语句前加上关键字 explain/desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;
我们举例使用一次,可以看到如下图所示的许多参数:
2)对图中所示的参数解析:
(1)id:select查询的序列号,表示查询中执行select子句或者是操作表的顺序(id相同,执行顺序从上到下;id不同,值越大,优先执行)。
为了理解通过id判断执行顺序,举例如下:
# 1. 查询学生信息,及选择课程名称
# 分析:涉及3张表,student,course,stu_course
select s.*, c.name
from student s, course c, stu_course sc
where s.id = sc.studentid and c.id = sc.courseid;
# 加上EXPLAIN,或者DESC
desc select s.*, c.name
from student s, course c, stu_course sc
where s.id = sc.studentid and c.id = sc.courseid;
其执行效果如下图:
从图中可以看到id相同,那么执行顺序就是从上到下,先执行s(student)表,再执行sc(stu_course)表,最后执行c(course)表。
接下来,演示id不同的情况:
# 2. 查询选择了课程 MySQL 的学生信息(子表查询)
# 分析:1)查询课程 MySQL 的课程id
select c.id from course c where c.name = "MySQL";
# 2) 查询选择了该课程id的学生id
select sc.studentid from stu_course sc where sc.courseid = (第一步查到的id);
# 3)查询在studentid内的学生信息
select s.* from student s where s.id in (第二步查到的studentid);
# 组合起来
select s.* from student s where s.id in (select sc.studentid from stu_course sc where sc.courseid = (select c.id from course c where c.name = "MySQL"));
# 那么,执行性能explain
explain select s.* from student s where s.id in (select sc.studentid from stu_course sc where sc.courseid = (select c.id from course c where c.name = "MySQL"));
得到的信息,如下图所示:
通过上图可知,id越大越优先执行,即先执行c(course)表,接着执行sc(stu_course)表,再执行(subquery2)第二步子查询,最后再执行s(student)表。
(2)select_type
表示SELECT类型,常见的取值有 SIMPLE (简单表,不使用表连接,或子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含子查询)等。
(3)type
表示连接类型,性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all。此参数比较重要,我们在做sql优化时,尽量将类型往前的类型走。
- NULL:当查询给定值时,才出现;
- system:当查询系统表时出现;
- const:当根据主键,或者唯一索引查询时出现;
- eq_ref:根据连表查询,使用主键,或者唯一索引时出现;
- ref:根据非唯一性索引查询时出现;
- index:用了索引,但会遍历所有索引数列时出现。
- all:表示全表扫描,性能最低。
(4)possible_key
显示可能应用在这张表上的索引,一个或者多个。
(5)key
实际用到的索引,如果为NULL,则没有使用索引。
(6)key_len
表示索引中使用的字节数,该值为索引字段最大可能长度,并非实际使用长度,在不损失精度的前提下,长度越短越好。
(7)rows
MySQL认为必须要执行查询的行数,在InnoDB引擎表中,是一个估计值,可能并不准确。
(8)filtered
表示返回结果的行数占需要读取行数的百分比,filtered值越大越好。
(9)Extra
表示额外的,之前参数没有展示的,需要使用到的部分。
小结:本文主要介绍了profile详情,用于显示sql语句的耗时;和explain执行计划,用于具体查看sql语句的执行性能,及可优化的部分。
原创文章,作者:guozi,如若转载,请注明出处:https://www.sudun.com/ask/88700.html