学习MySQL如何优化查询速度

登录注册

学习MySQL如何优化查询速度

来源:网络
更新日期:2020-08-17

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

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

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

相关学习推荐：mysql视频教程

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

一、慢在哪

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

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';

3. 总是查询出全部列

每次看到select *的时候一定要用异样的目光来审视它，是不是真的需要返回全部数据列？

在大部分情况下，是不需要的。 select *会导致进行全表扫描，会让优化器无法完成索引扫描这类优化，过多的列还会为服务器带来额外的I/O、内存和CPU的消耗。即使真的需要查询出全部列，应该逐个罗列出全部列而不是*。

4. 重复查询相同的数据

如果你不太留意，很容易出现这样的错误：不断地重复执行相同的查询，然后每次都返回完全相同的数据。

例如，在用户评论的地方需要查询用户头像的URL，那么用户多次评论的时候，可能就会反复来查询这个数据。比较好处理方法是，在初次查询的时候将这个数据缓存起来，后续使用时直接从缓存中取出。

三、是否扫描了额外的记录

确定查询只查询了需要的数据以后，接下来应该看看查询过程中是否扫描了过多的数据。对于MySQL，最简单衡量查询开销的三个指标如下：

响应时间扫描的行数返回的行数

没有哪个指标能够完全来衡量查询的开销，但它们能够大致反映MySQL内部执行查询时需要访问多少数据，并可以大概推算出查询运行的实际。这三个指标都会记录到MySQL的慢日志中，所以检查慢日志记录是找出扫描行数过多查询的办法。

慢查询：用于记录在MySQL中响应时间超过阈值(long_query_time,默认10s)的语句，并会将慢查询记录到慢日志中。可通过变量slow_query_long来开启慢查询，默认是关闭状态，可以将慢日志记录到表slow_log或文件中，以供检查分析。

1. 响应时间

响应时间是两个部分之和：服务时间和排队时间。服务时间是指数据库处理这个查询真正花费了多长时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间，可能是等待I/O操作，也可能是等待行锁等等。

在不同类型的应用压力下，响应时间并没有什么一致的规律或者公式。诸如存储引擎的锁（表锁，行锁），高并发资源竞争，硬件响应等诸多因素都会影响响应时间，所以，响应时间既可能是一个问题的结果也可能是一个问题的原因，不同案例情况不同。

当你看到一个查询的响应时间的时候，首先需要问问自己，这个响应时间是否是一个合理的值。

2. 扫描的行数和返回的行数

在分析查询时，查看该查询扫描的行数是非常有帮助的，在此之上也能够分析是否扫描了额外的记录。

对于找出那些糟糕查询，这个指标可能还不够完美，因为并不是所有行的访问代价都是相同的。较短的行的访问速度相当快，内存中的行也比磁盘中的行的访问速度要快的多。

理想的情况下，扫描的行数和返回的行数应该是相同的。但实际上这种美事并不多，例如在做一个关联查询的时候，扫描的行数和对返回的行数的比率通常都很小，一般在1:1和10：1之间，不过有时候这个值也可能非常大。

3. 扫描的行数和访问类型

在评估查询开销的时候，需要考虑一下从表中找到某一行数据的成本。 MySQL有好几种访问方式可以查找并返回一行结果。这些访问方式可能需要访问很多行才能返回一条结果，也有些访问方式可能无需扫描就能返回结果。

在执行计划EXPLAIN语句中的type列反映了访问类型。访问类型有很多种，从全表扫描到索引扫描，范围扫描，唯一索引，常数索引等。这里列的这些，速度是从慢到快，扫描的行数也是从多到少。

如果查询没有办法找到合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引，这也是我们之前讨论索引的问题。现在应该明白为什么索引对于查询优化如此重要了。索引让MySQL以最高效，扫描行数最少的方式找到需要的记录。

如果发现查询扫描了大量的数据但只返回少数的行，通常可以尝试下面的技巧去优化它：

使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无需回表获取对应的行就可以返回结果了。
优化表结构。例如使用单独的汇总表来完成查询。重写复杂查询，让MySQL优化器能够以更优化的方式执行这个查询。

相关推荐：编程视频课程