MySQL如何优化查询速度

前面章节我们介绍了如何选择优化的数据类型、如何高效的使用索引,这些对于高性能的MySQL来说是必不可少的。 但这些还完全不够,还需要合理的设计查询。 如果查询写的很糟糕,即使表结构再合理、索引再合适,也是无法实现高性能的。

谈到MySQL性能优化,查询优化作为优化的源头,它也是最能体现一个系统是否更快。 本章以及接下来的几章将会着重讲解关于查询性能优化的内容,从中会介绍一些查询优化的技巧,帮助大家更深刻地理解MySQL如何真正地执行查询、究竟慢在哪里、如何让其快起来,并明白高效和低效的原因何在,这样更有助于你更好的来优化查询SQL语句。

本章从“为什么查询速度这么慢”开始谈起,让你能够清楚的知道查询可能会慢在哪些环节,这样将有助于你更好的优化查询,做到 心中有数,高人一筹 。

一、慢在哪

真正衡量查询速度的是响应时间。 如果把查询看作是一个任务,那么它是由一系列子任务组成的,每个任务都会消耗一定的时间。 如果要优化查询,实际上要优化其子任务,那么消除其中一些子任务,那么减少子任务的执行次数,要么让子任务运行的更快。

MySQL在执行查询的时候,有哪些子任务,哪些子任务花费的时间最多? 这就需要借助一些工具,或者一些方法(如: 执行计划)对查询进行剖析,来定位发现究竟慢在哪。

通常来说,查询的生命周期大致大致可以按照顺序来看: 从客户端到服务器,然后在服务器上进行解析,生成执行计划,执行,并返回结果给客户端。 其中,“执行”可以认为是整个生命周期中最重要的阶段,这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理,包括排序、分组等。

在完成这些任务的时候,查询需要在不同阶段的不同地方花费时间,包括网络、CPU计算,生成统计信息和执行计划、锁等待等操作,尤其是向底层存储引擎检索数据的调用操作,这些调用需要在内存操作、CPU操作,还可能会产生大量的上下文切换以及系统调用。

在上述这些操作中,都会消耗大量的时间,其中会存在一些不必要的额外操作,其中有些操作可能被额外地重复执行了很多次、某些操作执行的很慢等等。 这也就是查询真正可能慢的地方, 优化查询的目的就是减少和消除这些操作所花费的时间

通过上面的分析,我们对查询的过程有了整体的了解,能够清楚的知道查询可能在哪些地方会存在问题,最终导致整个查询很慢,为实际查询优化提供方向。

换言之,查询优化可以从以下两个角度来出发:

  • 减少子查询次数
  • 减少额外、重复的操作

查询性能低下常见的原因是访问的数据太多。 在数据量小的时候,查询速度还不错,一旦数据量上来,查询速度将会发生巨变,让人抓狂、体验极差。 针对查询优化方面,可以从以下方面进行排查:

  • 是否查询了不需要的数据
  • 是否扫描了额外的记录

二、是否查询了不需要的数据

在实际查询中很多时候,会查询了实际需要的数据,然后这些多余的数据会被应用程序丢弃。 这对MySQL来说是额外的开销,同时也会消耗应用服务器的CPU和内存资源。
一些典型案例如下:

1. 查询不需要的记录

这是一个常见的错误,常常会误以为MySQL只会返回需要的数据,实际上MySQL却是先返回全部结果集再进行计算。

开发者习惯性的先使用SELECT语句查询大量的结果,然后由应用查询或者前端展示层再获取前面的N行数据,例如,在新闻网站中查询100条记录,但是只是在页面上显示前10条。

最有效的解决方法是需要多少记录就查询多少记录,通常会在查询后面加上LIMIT,即: 分页查询。

2. 多表关联时返回全部列

如果你想查询所有在电影Academy Dinosaur中出现的演员,千万不要按下面的方式来进行查询:

select * fromt actor a
inner join film_actor fa.actorId = a.actorId
inner join film f f.filmId = fa.filmId
where fa.title = 'Academy Dinosaur';

这样将会返回三张表的全部数据列,而实际需求是要查询演员信息,正确的写法应该是:

select a.* fromt actor a
inner join film_actor fa.actorId = a.actorId
inner join film f f.filmId = fa.filmId
where fa.title = 'Academy Dinosaur';

MySQL如何优化查询速度

扫一扫手机访问