SQL性能分析(二)

上一篇我们了解到,SQL语句的执行大部分是SELECT语句,而我们可以通过设置慢查询日志的时间阈值,来找出哪些SQL语句执行耗时超过该阈值。而实际情况下,存在一些SQL语句没有超过阈值,但是比较接近阈值的情况,这些SQL语句实际上也需要优化。这就需要用到我们接下来学习的内容。

  1.  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/descEXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

我们举例使用一次,可以看到如下图所示的许多参数:

图片

2)对图中所示的参数解析:

(1)id:select查询的序列号,表示查询中执行select子句或者是操作表的顺序(id相同,执行顺序从上到下;id不同,值越大,优先执行)。

为了理解通过id判断执行顺序,举例如下:

# 1. 查询学生信息,及选择课程名称# 分析:涉及3张表,student,course,stu_courseselect s.*, c.namefrom student s, course c, stu_course scwhere s.id = sc.studentid and c.id = sc.courseid;# 加上EXPLAIN,或者DESCdesc select s.*, c.namefrom student s, course c, stu_course scwhere s.id = sc.studentid and c.id = sc.courseid;

其执行效果如下图:

图片

从图中可以看到id相同,那么执行顺序就是从上到下,先执行s(student)表,再执行sc(stu_course)表,最后执行c(course)表。

接下来,演示id不同的情况:

# 2. 查询选择了课程 MySQL 的学生信息(子表查询)# 分析:1)查询课程 MySQL 的课程idselect c.id from course c where c.name = "MySQL";# 2) 查询选择了该课程id的学生idselect 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"));
# 那么,执行性能explainexplain 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

Like (0)
guozi的头像guozi
Previous 2024年6月4日
Next 2024年6月4日

相关推荐

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注