高性能MySQL之查询性能优化

一.为什么查询速度会慢

　　MySQL在执行查询的时候有哪些子任务，哪些子任务运行的速度很慢?这里很难给出完整的列表。通常来说，查询的生命周期大致可以按照顺序来看:从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中“执行”可以认为是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。

二.慢查询基础：优化数据访问

　　查询性能低下最基本的原因是访问的数据太多。某些查询可能不可避免地需要筛选大量数据，但这并不常见。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询，我们发现通过下面两个步骤来分析总是很有效:

　　（1）.确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能是访问了太多的列。

　　（2）.确认MySQL服务器层是否在分析大量超过需要的数据行。

1.是否向数据库请求了不需要的数据

　　有些查询会请求超过实际需要的数据，然后这些多余的数据会被应用程序丢弃。这会给MySQL服务器带来额外的负担，并增加网络开销,另外也会消耗应用服务器的CPU和内存资源。

　　这里有一些典型案例:

（1）查询不需要的记录：

　　一个常见的错误是常常会误以为MySQL会只返回需要的数据，实际上MySQL却是先返回全部结果集再进行计算。我们经常会看到一些了解其他数据库系统的人会.设计出这类应用程序。这些开发者习惯使用这样的技术，先使用SELECT语句查询大量的结果，然后获取前面的N行后关闭结果集(例如在新闻网站中取出100条记录,但是只是在页面上显示前面10条)。他们认为MySQL会执行查询，并只返回他们需要的10条数据，然后停止查询。实际情况是MySQL会查询出全部的结果集，客户端的应用程序会接收全部的结果集数据，然后抛弃其中大部分数据。最简单有效的解决方法就是在这样的查询后面加上LIMIT。

（2）多表关联时返回全部列：
　　如果你想查询所有在电影AcademyDinosaur中出现的演员，千万不要按下面的写法

编写查询:

mysql> SELECT * FROM sakila.actor
-> INNER J0IN sakila. fi1m_ actor USING(actor_ id)
-> INNER J0IN sakila. film USING(film id)
-> WHERE sakila.film.title = ' Academy Dinosaur' ;
这将返回这三个表的全部数据列。正确的方式应该是像下面这样只取需要的列:
　　mysql> SELECT sakila.actor.* FROM sakila.actor...;

（3）总是取出全部列：
　　每次看到SELECT*的时候都需要用怀疑的眼光审视，是不是真的需要返回全部的列?很可能不是必需的。取出全部列，会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的I/O、内存和CPU的消耗。因此，一些DBA是严格禁止SELECT*的写法的，这样做有时候还能避免某些列被修改带来的问题。

（4）重复查询相同的数据

　　如果你不太小心，很容易出现这样的错误一不断 地重复执行相同的查询，然后每次都返回完全相同的数据。例如，在用户评论的地方需要查询用户头像的URL,那么用户多次评论的时候，可能就会反复查询这个数据。比较好的方案是，当初次查询的时候将这个数据缓存起来，需要的时候从缓存中取出，这样性能显然会更好。　　

2.MySQL是否在扫描额外的记录

　　对于MySQL，最简单的衡量查询开销的三个指标包含：响应时间、扫描的行数、返回的行数。

响应时间：响应时间是两个部分之和:服务时间和排队时间。服务时间是指数据库处理这个查询真正花了多长时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间一-可 能是等I/O操作完成，也可能是等待行锁，等等。遗憾的是，我们无法把响应时间细分到上面这些部分，除非有什么办法能够逐个测量上面这些消耗，不过很难做到。一般最常见和重要的等待是I/O和锁等待，但是实际情况更加复杂。所以在不同类型的应用压力下，响应时间并没有什么-致的规律或者公式。诸如存储引擎的锁(表锁、行锁)、高并发资源竞争、硬件响应等诸多因素都会影响响应时间。所以，响应时间既可能是一个问题的结果也可能是-一个问题的原因，不同案例情况不同。

扫描的行数和返回的行数：

扫描的行数和访问类型：

在评估查询开销的时候，需要考虑- -下从表中找到某一行数据的成本。MySQL有好几种访问方式可以查找并返回一行结果。有些访问方式可能需要扫描很多行才能返回一行结果，也有些访问方式可能无须扫描就能返回结果。在EXPLAIN语句中的type列反应了访问类型。访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。这里列的这些，速度是从慢到快，扫描的行数也是从小到大。你不需要记住这些访问类型，但需要明白扫描表、扫描索引、范围访问和单值访问的概念。如果查询没有办法找到合适的访问类型，那么解决的最好办法通常就是增加一一个合适的索引，这也正是我们前--章讨论过的问题。现在应该明白为什么索引对于查询优化如此重要了。索引让MySQL以最高效、扫描行数最少的方式找到需要的记录。

三.重构查询的方式

 　　在优化有问题的查询时，目标应该是找到一一个更优的方法获得实际需要的结果一而不一定总是需要从MySQL获取- -模一样的结果集。有时候，可以将查询转换一种写 法让其返回一样的结果,但是性能更好。但也可以通过修改应用代码，用另一种方式完成查询, 最终达到一样的目的。

1.一个复杂查询还是多个简单查询

　　设计查询的时候-一个需要考虑的重要问题是，是否需要将-一个复杂的查询分成多个简单的查询。MySQL从设计上让连接和断开连接都很轻量级,在返回一个小的查询结果方面很高效。现代的网络速度比以前要快很多，无论是带宽还是延迟。在某些版本的MySQL上，即使在-一个通用服务器上，也能够运行每秒超过10万的查询，即使是一个千兆网卡也能轻松满足每秒超过2000次的查询。所以运行多个小查询现在已经不是大问题了。

2.切分查询

　　有时候对于一个大查询我们需要“分而治之”，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。例如，我们需要每个月运行一次下面的查询:

　　mysql> DELETE FROM messages WHERE created < DATE_SUB(NON(),INTERVAL 3 MONTH);

那么可以用类似下面的办法来完成同样的工作:
rows_affected = o
do {
rows_affected = do_query(
"DELETE FROM messages wHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH)LIMIT10000")
} while rows_affected > o
一次删除一万行数据一般来说是一个比较高效而且对服务器影响也最小的做法（如果是事务型引擎，很多时候小事务能够更高效)。同时，需要注意的是，如果每次删除数据后，都暂停一会儿再做下一次删除，这样也可以将服务器上原本一次性的压力分散到一个很长的时间段中，就可以大大降低对服务器的影响，还可以大大减少删除时锁的持有时间。

3.分解关联查询

 

 四.查询执行的基础

　　当希望MySQL能够以更高的性能运行查询时，最好的办法就是弄清楚MySQL是如何优化和执行查询的。一旦理解这一点，很多查询优化工作实际上就是遵循一些原则让优化器能够按照预想的合理的方式运行。据图6-1，我们可以看到当向MySQL 发送一个请求的时候，MySQL 到底做了些什么:

 1.MySQL客户端/服务端通信协议

　　一般来说，不需要去理解MySQL通信协议的内部实现细节，只需要大致理解通信协议是如何工作的。MySQL客户端和服务器之间的通信协议是“半双工”的，这意味着，在任何一个时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。所以，我们无法也无须将一个消息切成小块独立来发送。

查询状态
　　对于一个MySQL连接，或者说一个线程，任何时刻都有一个状态，该状态表示了MySQL当前正在做什么。有很多种方式能查看当前的状态，最简单的是使用SHOW FULLPROCESSLIST命令（该命令返回结果中的Command列就表示当前的状态)。在一个查询的生命周期中，状态会变化很多次。MySQL官方手册中对这些状态值的含义有最权威的解释，下面将这些状态列出来，并做一个简单的解释。
sleep：线程正在等待客户端发送新的请求。

Query：线程正在执行查询或者正在将结果发送给客户端。

Locked：在MySQL服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中。对于MyISAM来说这是一个比较典型的状态，但在其他没有行锁的引擎中也经常会出现。

Analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划。

Copying to tmp table [on disk]：线程正在执行查询，并且将其结果集都复制到一个临时表中，这种状态一般要么是在做GROUP BY操作，要么是文件排序操作，或者是UNION操作。如果这个状态后面还有“on disk”标记，那表示MySQL正在将一个内存临时表放到磁盘上。

Sorting result：线程正在对结果集进行排序。

Sending data：这表示多种情况:线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据。

2.查询缓存

　　在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同，那也不会匹配缓存结果，这种情况下查询就会进入下阶段的处理。如果当前的查询恰好命中了查询缓存，那么在返回查询结果之前MySQL会检查一次用户权限。这仍然是无须解析查询SQL语句的，因为在查询缓存中已经存放了当前查询需要访问的表信息。如果权限没有问题，MySQL会跳过所有其他阶段，直接从缓存中拿到结果并返回给客户端。这种情况下﹐查询不会被解析,不用生成执行计划,不会被执行。

3.查询优化处理

 　　查询的生命周期的下一步是将一个SQL转换成一个执行计划，MySQL再依照这个执行计划和存储引擎进行交互。这包括多个子阶段:解析SQL、预处理、优化SQL执行计划。这个过程中任何错误（例如语法错误）都可能终止查询。

语法解析器和预处理：

　　首先，MySQL通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”。MySQL解析器将使用MySQL语法规则验证和解析查询。例如，它将验证是否使用错误的关键字，或者使用关键字的顺序是否正确等，再或者它还会验证引号是否能前后正确匹配。预处理器则根据一些MySQL规则进一步检查解析树是否合法，例如，这里将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义。下一步预处理器会验证权限。这通常很快，除非服务器上有非常多的权限配置。

查询优化器：

　　现在语法树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

数据和索引的统计信息：

　　MySQL架构由多个层次组成。在服务器层有查询优化器，却没有保存数据和索引的统计信息。统计信息由存储引擎实现，不同的存储引擎可能会存储不同的统计信息（也可以按照不同的格式存储统计信息)。某些引擎，例如Archive引擎，则根本就没有存储任何统计信息!因为服务器层没有任何统计信息，所以MySQL查询优化器在生成查询的执行计划时，需要向存储引擎获取相应的统计信息。存储引擎则提供给优化器对应的统计信息，巴怕:每个表或者索引有多少个页面、每个表的每个索引的基数是多少、数据行和索5长度、索引的分布信息等。优化器根据这些信息来选择一个最优的执行计划。在后面的小节中我们将看到统计信息是如何影响优化器的。

MySQL如何执行关联查询：

　　在MySQL中，每一个查询，每一个片段都可能是关联。

执行计划：

关联查询优化器：

排序优化：

4.查询执行引擎

　　存储引擎接口有着非常丰富的功能，但是底层接口却只有几十个，这些接口像“搭积木”一样能够完成查询的大部分操作。例如，有一个查询某个索引的第一行的接口，再有一个查询某个索引条目的下一个条目的功能，有了这两个功能我们就可以完成全索引扫描的操作了。这种简单的接口模式，让MySQL 的存储引擎插件式架构成为可能，但是正如前面的讨论，也给优化器带来了一定的限制。

5.返回结果给客户端

 

五.MySQL查询优化器的局限性

1.关联子查询

　　MySQL的子查询实现得非常糟糕。最糟糕的一类查询是WHERE条件中包含IN()的子查询语句。例如，我们希望找到Sakila数据库中，演员Penelope Guiness（他的actor_id为1）参演过的所有影片信息。很自然的，我们会按照下面的方式用子查询实现:

 

 

 MySQL会将查询改写成下面的样子：

 

 

 根据EXPLAIN的输出我们可以看到，MySQL先选择对file表进行全表扫描，然后根据返回的film_id逐个执行子查询。如果是一个很小的表，这个查询糟糕的性能可能还不会引起注意，但是如果外层的表是一个非常大的表，那么这个查询的性能会非常糟糕。当然我们很容易用下面的办法来重写这个查询:

 

 

 如何用好关联子查询：

　　并不是所有关联子查询的性能都会很差。如果有人跟你说:“别用关联子查询”，那么不要理他。先测试，然后做出自己的判断。

2.UNION的限制

　　如果希望UNION的各个子句能够根据LIMIT只取部分结果集，或者希望能够先排好序再合并结果集的话，就需要在UNION的各个子句中分别使用这些子句。例如，想将两个子查询结果联合起来，然后再取前20条记录，那么MySQL会将两个表都存放到同一个临时表中，然后再取出前20行记录:

(SELECT first_name，last_nameFROM sakila.actorORDER BY last_name)

UNION ALL

(SELECT first_name,last_nameFROM sakila.customerORDER BY 1ast_name)LIMIT 20;

这条查询将会把 actor中的200条记录和customer表中的599条记录存放在一个临时表中，然后再从临时表中取出前20条。可以通过在UNION 的两个子查询中分别加上一个LIMIT20来减少临时表中的数据

3.索引合并优化

4.等值传递

5.并发执行

6.哈希关联

7.松散索引扫描

8.最大值和最小值优化

9.在同一个表上查询和更新

MySQL不允许对同一张表同时进行查询和更新

 

六.查询优化器的提示

　　如果对优化器选择的执行计划不满意，可以使用优化器提供的几个提示(hint）来控制最终的执行计划。

 

七.优化特定类型的查询

1.优化COUNT()查询

COUNT()的作用：COUNT()是一个特殊的函数，有两种非常不同的作用:它可以统计某个列值的数量，也可以统计行数。在统计列值时要求列值是非空的（不统计NULL)。如果在COUNT()的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数。因为很多人对NULL理解有问题，所以这里很容易产生误解。如果想了解更多关于SQL语句中NULL的含义，建议阅读一些关于SQL语句基础的书籍。(关于这个话题，互联网上的一些信息是不够精确的。)COUNT()的另一个作用是统计结果集的行数。当MySQL确认括号内的表达式值不可能为空时，实际上就是在统计行数。最简单的就是当我们使用COUNT(*)的时候，这种情况下通配符*并不会像我们猜想的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数。我们发现一个最常见的错误就是，在括号内指定了一个列却希望统计结果集的行数。如果希望知道的是结果集的行数，最好使用COUNT(*)，这样写意义清晰，性能也会很好。

关于MyISAM的神话：一个容易产生的误解就是:MyISAM的COUNT()函数总是非常快,不过这是有前提条件的，即只有没有任何MHERE条件的COUNT(*)才非常快,因为此时无须实际地去计算表的行数。

简单的优化：

使用近似值：有时候某些业务场景并不要求完全精确的COUNT值，此时可以用近似值来代替。EXPLAIN出来的优化器估算的行数就是一个不错的近似值，执行EXPLAIN并不需要真正地去执行查询，所以成本很低。

更复杂的优化：通常来说，COUNT()都需要扫描大量的行（意味着要访问大量数据）才能获得精确的结果，因此是很难优化的。

2.优化关联查询

这里需要特别提到的是:
（1）确保ON或者USING子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列c关联的时候，如果优化器的关联顺序是B、A，那么就不需要在B表的对应列上建上索引。没有用到的索引只会带来额外的负担。一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引。
（2）确保任何的GROUP BY和 ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程。
（3）当升级MySQL 的时候需要注意:关联语法、运算符优先级等其他可能会发生变化的地方。因为以前是普通关联的地方可能会变成笛卡儿积，不同类型的关联可能会生成不同的结果等。

3.优化子查询

关于子查询优化我们给出的最重要的优化建议就是尽可能使用关联查询代替。

4.优化GROUP BY和DISTINCT

5.优化LIMIT分页

一个非常常见又令人头疼的问题就是，在偏移量非常大的时候，例如可能是LIMIT1000,20这样的查询，这时MySQL需要查询10 020条记录然后只返回最后20条，前面10 000条记录都将被抛弃，这样的代价非常高。考虑下面的查询：

 

 如果这个表非常大，那么这个查询最好改写成下面的样子：

 

 6.优化SQL_CALC_FOUND_ROWS

7.优化UNION查询

MySQL总是通过创建并填充临时表的方式来执行UNION查询。因此很多优化策略在UNION查询中都没法很好地使用。经常需要手工地将MHERE、LIMIT、ORDER BY等子句“下推”到UNION的各个子查询中，以便优化器可以充分利用这些条件进行优化（例如，直接将这些子句冗余地写一份到各个子查询)。除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL，这一点很重要。如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致对整个临时我的数据做唯一性检查。这样做的代价非常高。即使有ALL关键字，MySQL仍然会使用临时表存储结果。事实上，MySQL总是将结果放入临时表，然后再读出，再返回给客户端。虽然很多时候这样做是没有必要的（例如，MySQL可以直接把这些结果返回给客户端)。

8.使用用户自定义变量

用户自定义变量是一个用来存储内容的临时容器，在连接MySQL的整个过程中都存在。可以使用下面的SET和SELECT语句来定义它们:

 

 然后可以在任何可以使用表达式的地方使用这些自定义变量: