目录
为什么查询速度会慢
在尝试编写快速的查询之前，需要清楚一点，真正重要是响应时间。如果把查询看作是 一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定的时间。如果要优化 查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数， 要么让子任务运行得更快。
MySQL在执行查询的时候有哪些子任务，哪些子任务运行的速度很慢？这里很难给出 完整的列表，但如果对查询进行剖析，就能看到查询所执行的子 任务。通常来说，查询的生命周期大致可以按照顺序来看：从客户端，到服务器，然后 在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中“执行“可以 认为是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存储引擎的调 用以及调用后的数据处理，包括排序、分组等。
在完成这些任务的时候，查询需要在不同的地方花费时间，包括网络，CPU计算，生成 统计信息和执行计划、锁等待（互斥等待）等操作，尤其是向底层存储引擎检索数据的 调用操作，这些调用需要在内存操作、CPU操作和内存不足时导致的I/O操作上消耗时间。 根据存储引擎不同，可能还会产生大量的上下文切换以及系统调用。
在每一个消耗大批时间的查询案例中，我们都能看到一些不必要的额外操作、某些操作 被额外地重复了很多次、某些操作执行得太慢等。优化查询的目的就是减少和消除这些 操作所花费的时间。
再次申明一点，对于一个查询的全部生命周期，上面列的并不完整。这里我们只是想说明： 了解查询的生命周期、清楚查询的时间消耗情况对于优化查询有很大的意义。有了这些 概念，我们再一起来看看如何优化查询。
如何找到慢查询
使用慢查询日志，一般分为四步：
开启慢查询日志。
设置慢查询阀值。
确定慢查询日志路径。
确定慢查询日志的文件名。
开启慢查询日志（默认是关闭的）：
mysql> set global slow_query_log = on;
Query OK, 0 rows affected (0.00 sec)
设置慢查询时间限制（查询时间只要大于这个值都将记录到慢查询日志中，单位：秒）：
mysql> set global long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)
确定慢查询日志路径：
mysql> show global variables like "datadir";
确定慢查询日志文件名：
mysql> show global variables like "slow_query_log_file";
慢查询query time设置小技巧：
线上业务一般建议把 long_query_time 设置为 1 秒，如果某个业务的 MySQL 要求比较高的 QPS，可设置慢查询为 0.1 秒。发现慢查询及时优化或者提醒开发改写。
一般测试环境建议 long_query_time 设置的阀值比生产环境的小，比如生产环境是 1 秒，则测试环境建议配置成 0.5 秒。便于在测试环境及时发现一些效率低的 SQL。
甚至某些重要业务测试环境 long_query_time 可以设置为 0，以便记录所有语句。并留意慢查询日志的输出，上线前的功能测试完成后，分析慢查询日志每类语句的输出，重点关注 Rows_examined（语句执行期间从存储引擎读取的行数），提前优化。
接下来在确定慢查询日志后可以通过：tail -n5 /data/mysql/mysql-slow.log 命令查看
这里对上方的执行结果详细描述一下：
tail -n5：只查看慢查询文件的最后5行
Time：慢查询发生的时间
User@Host：客户端用户和IP
Query_time：查询时间
Lock_time：等待表锁的时间
Rows_sent：语句返回的行数
Rows_examined：语句执行期间从存储引擎扫描的行数
上面这种方式是用系统自带的慢查询日志查看的，如果觉得系统自带的慢查询日志不方便查看，小伙伴们可以使用 pt-query-digest 或者 mysqldumpslow 等工具对慢查询日志进行分析
慢查询原因
一条 SQL 语句执行的很慢，那是每次执行都很慢呢？
网络
CPU
IO
上下文切换
系统调用
生成统计信息
锁等待时间
还是大多数情况下是正常的，偶尔出现很慢呢？所以我觉得，我们还得分以下两种情况来讨论。
1、大多数情况是正常的，只是偶尔会出现很慢的情况。
2、在数据量不变的情况下，这条SQL语句一直以来都执行的很慢。（一般就是因为访问数据过多，优化后还这样可以认为没有没有用到相应的索引）
偶尔很慢的情况
数据库在刷新脏页
当我们要往数据库插入一条数据、或者要更新一条数据的时候，我们知道数据库会在内存中把对应字段的数据更新了，但是更新之后，这些更新的字段并不会马上同步持久化到磁盘中去，而是把这些更新的记录写入到 redo log 日记中去，等到空闲的时候，在通过 redo log 里的日记把最新的数据同步到磁盘中去。
不过，redo log 里的容量是有限的，如果数据库一直很忙，更新又很频繁，这个时候 redo log 很快就会被写满了，这个时候就没办法等到空闲的时候再把数据同步到磁盘的，只能暂停其他操作，全身心来把数据同步到磁盘中去的，而这个时候，就会导致我们平时正常的SQL语句突然执行的很慢，所以说，数据库在在同步数据到磁盘的时候，就有可能导致我们的SQL语句执行的很慢了。
拿不到锁
这个就比较容易想到了，我们要执行的这条语句，刚好这条语句涉及到的表，别人在用，并且加锁了，我们拿不到锁，只能慢慢等待别人释放锁了。或者，表没有加锁，但要使用到的某个一行被加锁了，这个时候，我也没办法啊。
如果要判断是否真的在等待锁，我们可以用 show processlist这个命令来查看当前的状态哦，这里我要提醒一下，有些命令最好记录一下，反正，我被问了好几个命令，都不知道怎么写，呵呵。
访问数据过多
查询性能低下最基本的原因是访问的数据太多。某些查询可能不可避免地需要筛选大量 数据，但这并不常见。大部分性能低下的查询都可以通过减少访问的数据量的方式进行 优化。对于低效的查询，我们发现通过下面两个步骤来分析总是很有效：
确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但 有时候也可能是访问了太多的列。
确认MySQL服务器层是否在分析大量超过需要的数据行。
是否向数据库请求了不需要的数据
有些查询会请求超过实际需要的数据，然后这些多余的数据会被应用程序丢弃。这会给MySQL服务器带来额外的负担，并增加网络开销注，另外也会消耗应用服务器的CPU 和内存资源。
这里有一些典型案例：
查询不需要的记录 .
一个常见的错误是常常会误以为MySQL会只返回需要的数据，实际上MySQL却是先返回全部结果集再进行计算。我们经常会看到一些了解其他数据库系统的人会设计出这类应用程序。这些开发者习惯使用这样的技术，先使用SELECT语句查询大量的结果，然后获取前面的N行后关闭结果集（例如在新闻网站中取出100条记录，但是只是在页面上显示前面10条）。他们认为MySQL会执行查询，并只返回他们需要的10条数据，然后停止查询。实际情况是MySQL会查询出全部的结果集，客户端的应用程序会接收全部的结果集数据，然后抛弃其中大部分数据。最简单有效的解决方法就是在这样的查询后面加上LIMIT。
多表关联时返回全部列
如果你想查询所有在电影Academy Dinosaur中出现的演员，于万不要按下面的写法 编写查询：
总是取出全部列
每次看到SELECT*的时候都需要用怀疑的眼光审视，是不是真的需要返回全部的 列？很可能不是必需的。取出全部列，会让优化器无法完成索引覆盖扫描这类优化， 还会为服务器带来额外的I/O、内存和CPU的消耗。因此，一些OBA是严格禁止 SELECT*的写法的，这样做有时候还能避免某些列被修改带来的问题。
当然，查询返回超过需要的数据也不总是坏事。在我们研究过的许多案例中，人们 会告诉我们说这种有点浪费数据库资源的方式可以简化开发，因为能提高相同代码 片段的复用性，如果清楚这样做的性能影响，那么这种做法也是值得考虑的。如果 应用程序使用了某种缓存机制，或者有其他考虑，获取超过需要的数据也可能有其 好处，但不要忘记这样做的代价是什么。获取并缓存所有的列的查询，相比多个独立的只获取部分列的查询可能就更有好处。
重复查询相同的数据
如果你不太小心，很容易出现这样的错误--不断地重复执行相同的查询，然后每 次都返回完全相同的数据。例如，在用户评论的地方需要查询用户头像的URL, 那 么用户多次评论的时候，可能就会反复查询这个数据。比较好的方案是，当初次查 询的时候将这个数据缓存起来，需要的时候从缓存中取出，这样性能显然会更好。
MySQL是否在扫描额外的记录
在确定查询只返回需要的数据以后，接下来应该看看查询为了返回结果是否扫描了过多 的数据。对于MySQL, 最简单的衡量查询开销的三个指标如下：
- 响应时间
- 扫描的行数
- 返回的行数
没有哪个指标能够完美地衡批查询的开销，但它们大致反映了MySQL在内部执行查 询时需要访问多少数据，并可以大概推算出查询运行的时间。这三个指标都会记录到 MySQL的慢日志中，所以检查慢日志记录是找出扫描行数过多的查询的好办法。
响应时间
要记住，响应时间只是一个表面上的值。这样说可能看起来和前面关于响应时间的说法 有矛盾？其实并不矛盾，响应时间仍然是最重要的指标，这有一点复杂，后面细细道来。
响应时间是两个部分之和：服务时间和排队时间。服务时间是指数据库处理这个查询 真正花了多长时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时 间一可能是等I/O操作完成，也可能是等待行锁，等等。遗憾的是，我们无法把响应 时间细分到上面这些部分，除非有什么办法能够逐个测量上面这些消耗，不过很难做到。 一般最常见和重要的等待是I/O和锁等待，但是实际情况更加复杂。
所以在不同类型的应用压力下，响应时间并没有什么一致的规律或者公式。诸如存储引 擎的锁（表锁、行锁）、高并发资源竞争、硬件响应等诸多因素都会影响响应时间。所以， 响应时间既可能是一个问题的结果也可能是一个问题的原因，不同案例情况不同，除非 能够使用“单个查询问题还是服务器问题”一节介绍的技术来确定到底是因还 是果。
当你看到一个查询的响应时间的时候，首先需要问问自己，这个响应时间是否是一个 合理的值。实际上可以使用“快速上限估计“法来估算查询的响应时间，这是由TapioLahdenmaki和Mike Leach编写的Relational Database Index Design and the Optimizers (Wiley出版社）一书提到的技术，限于篇幅，在这里不会详细展开。概括地说，了解这 个查询需要哪些索引以及它的执行计划是什么，然后计算大概需要多少个顺序和随机I/O, 再用其乘以在具体硬件条件下一次I/O的消耗时间。最后把这些消耗都加起来，就可以获得一个大概参考值来判断当前响应时间是不是一个合理的值。
扫描的行数和返回的行数
分析查询时，查看该查询扫描的行数是非常有帮助的。这在一定程度上能够说明该查询 找到需要的数据的效率高不高。
对于找出那些“糟糕＂的查询，这个指标可能还不够完美，因为并不是所有的行的访问 代价都是相同的。较短的行的访问速度更快，内存中的行也比磁盘中的行的访问速度要 快得多。
理想情况下扫描的行数和返回的行数应该是相同的。但实际情况中这种“美事”并不多。 例如在做一个关联查询时，服务器必须要扫描多行才能生成结果集中的一行。扫描的行 数对返回的行数的比率通常很小，一般在1: 1和10:1之间，不过有时候这个值也可能非
最低0.47元/天 解锁文章
885
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
