高性能MySQL-查询性能优化

如有侵权，请联系～
如有错误，也欢迎批评指正～
本篇文章大部分是来自学习《高性能MySQL》的笔记

1、为什么查询这么慢

查询优化、索引优化、库表结构优化需要齐头并进，一个不落

快速查询真正重要的是响应时间。如果把查询当做一个任务，那么这个任务会由一系列的子任务组成，每个子任务都需要消耗一定的时间。优化查询其实就是优化子任务，要么消除一些子任务，要么减少某些子任务的执行次数，要么让子任务执行的更快。

在完成这些任务的时候，查询需要在不同的地方花费时间，包括网络、CPU计算、生成统 计信息和执行计划、锁等待(互斥等待)等操作，尤其是向底层存储引擎检索数据的调用 操作，这些调用需要在内存操作、CPU操作和内存不足时导致的I/O操作上消耗时间。

在每一个消耗大量时间的查询案例中，我们都能看到一些不必要的操作、某些操作被额外地重复了很多次、某些操作执行得太慢等。优化查询的目的就是减少和消除这些操作所花费的时间。

2、慢查询基础：优化数据访问

查询性能很差的最常见原因：访问的数据太多。大部分性能低下的查询都可以通过减少访问的数据量的
方式进行优化。对于低效的查询，我们发现通过下面两个步骤来分析总是很有效:

• 确认应用程序是否在检索大量且不必要的数据。访问了太多的行或者列
• 确认MySQL服务器层是否在分析大量不需要的数据行

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

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

• 查询了不需要的数据
• 多表联查的时候返回了所有的列
• 总是取所有的列：select *
• 重复相同的查询

2.2 MySQL是否存在扫描额外的记录

在确定查询只返回需要的数据以后，接下来应该看看查询为了返回结果是否扫描了过多的数据。对于MySQL，最简单的衡量查询开销的三个指标如下:响应时间、扫描的行数、返回的行数。

3、重构查询方式

优化查询的目标：找到获得实际需要的结果的替代方法。不一定和优化前的数据完全一致，可以是相同结果的等价形式。

3.1 一个复杂查询还是多个简单查询

设计查询的时候，一个需要考虑的重要问题：是否需要将一个复杂的查询转换为多个简单的查询。传统的实现认为：网络通信、查询解析和优化都是代价比较高的操作，所以让数据库做尽可能多的工作。
但是这对MySQL不适用，原因：

• MySQL从设计上就将连接和断开连接都变的很轻量，在返回一个小的查询结果方面很高效。
• 并且现代网络也快很多。
• 在某些版本的MySQL中，即使在一台通用服务器上，也能够运行每秒超 过10万次的简单查询。
• 在MySQL内部，每秒能够扫描内存中上百万行的数据，相比之下，MySQL响应数据给客户端就慢得多了。

所以将一个大查询分解为多个小查询是很有必要的。但也不是一个查询就能完成的非要转换为多个查询。

3.2 切分查询

对于一个大查询，分而治之，将一个大查询转换为多个小查询，每个小查询的功能完全一样，每次只返回一小部分结果。例如删除旧数据，不要一次删除所有数据，这样可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。需要注意：如果每次删除数据后， 都暂停一会儿再做下一次删除，也可以将服务器上原本一次性的压力分散到一个很长的时 间段中，可以大大降低对服务器的影响，还可以大大减少删除时锁的持有时间。

3.3 分解联接查询

很多高性能的应用都会对联接查询进行分解。即，对每个表进行单次查询，然后在应用程序中进行联接。这样做的好处：

• 让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。
• 减少锁竞争
• 在应用层做联接，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
• 可以减少对冗余记录的访问。在应用层做联接查询，意味着对于某条记录应用只需要查询一次，而在数据库中做联接查询，则可能需要重复地访问一部分数据。从这 点看，这样的重构还可能会减少网络和内存的消耗。

4、查询执行的基础

4.1 MySQL的客户端/服务器通信协议

MySQL的客户端和服务器之间的通信协议是“半双工”的，这意味着，在任何时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。
这种协议让MySQL通信变得简单快速，但是也从很多地方限制了MySQL。一个明显的限制是，这意味着没法进行流量控制。一旦一端开始发送消息，另一端要接收完整个消息才能响应它。
客户端用一个单独的数据包将查询传给服务器。这也是为什么当查询的语句很长的时候，参数max_allowed_packet就特别重要了。一旦客户端发送了请求，它能做的事情就只是等待结果了。
一般的服务器响应给用户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整地接收整个返回结果，而不能简单地只取前面几条结果， 然后让服务器停止发送数据。

4.2 查询状态

对于一个MySQL连接，或者一个线程，任何时刻都有一个状态，该状态表示了MySQL当 前正在做什么。有很多种方式能查看当前的状态，最简单的是使用SHOW FULL PROCESSLIST命令(该命令返回结果中的Command列，其就表示当前的状态)

Command	描述
Sleep	线程正在等待客户端发送新的请求
Query	线程正在执行查询或者正在将结果发送给客户端
Locked	在MySQL服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB 的行锁，并不会体现在线程状态中。
Analyzing and statistics	线程正在检查存储引擎的统计信息，并优化查询。
Sorting result	线程正在对结果集进行排序
Copying to tmp table [on disk]	线程正在执行查询，并且将其结果集复制到一个临时表中，这种状态一般要么是在做 GROUP BY操作，要么是在进行文件排序操作，或者是在进行UNION操作。如果这 个状态后面还有“on disk”标记，那表示MySQL正在将一个内存临时表放到磁盘上。

在一个繁忙的服务器上，通过查看线程状态可能会看到大量的不正常的状态，例如，statistics正占用大量的时间。这 通常表示，某个地方有异常了。

4.3 查询优化处理

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

查询优化器：
由优化器将其转化成查询执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并 选择其中成本最小的一个。最初，成本的最小单位是随机读取一个4KB数据页的成本，后来成本计算公式变得更加复杂，并且引入了一些“因子”来估算某些操作的代价，如执行一次WHERE条件比较的成本。优化器在评估成本的时候并不考虑任何层面的缓存带来的影响，它假设读取任何数据都需要一次磁盘I/O。

MySQL的查询优化器是一个非常复杂的软件，它使用了很多优化策略来生成一个最优的 执行计划。优化策略可以简单地分为两种，一种是静态优化，一种是动态优化。
静态优化可以直接对解析树进行分析，并完成优化。例如，优化器可以通过一些简单的代数变换将 WHERE条件转换成另一种等价形式。静态优化不依赖于特别的数值，如WHERE条件中带入的一些常数等。静态优化在第一次完成后就一直有效，即使使用不同的参数重复执行 查询也不会发生变化，可以认为这是一种“编译时优化”。
动态优化则和查询的上下文有关，也可能和很多其他因素有关，例如WHERE条件 中的取值、索引中条目对应的数据行数等。这需要在每次查询的时候都重新评估，可以认 为这是“运行时优化”。

MySQL可以优化的类型，但不限于：

优化类型	描述
重新定义联接表的顺序	数据表的联接并不总是按照在查询中指定的顺序进行。决定联接的顺序是优化器很重要的一个功能
将外联接转化成内联接	并不是所有的OUTER JOIN语句都必须以外联接的方式执行。诸多因素，例如 WHERE条件、库表结构都可能会让外联接等价于一个内联接。MySQL能够识别这一 点并重写查询，让其可以调整联接顺序。
使用代数等价变换规则	MySQL可以使用一些代数等价变换规则来简化并规范表达式。它可以合并和减少一些比较，还可以移除一些恒成立和一些恒不成立的判断。
优化COUNT()、MIN()和MAX()	索引和列是否可为空通常可以帮助MySQL优化这类表达式。例如，要找到某一列的 最小值，只需要查询对应B-tree索引最左端的记录，MySQL可以直接获取索引的第一 行记录。在优化器生成执行计划的时候就可以利用这一点，在B-tree索引中，优化器 会将这个表达式作为一个常数对待。
预估并转化为常数表达式	当MySQL检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数 进行优化处理。在优化阶段，有时候一个查询也能够转化为一个常数。select film.film.film_id,film_actor.actor_id from film join film_actor using film_id where film.film_id=1 第一步先从film表找到 需要的行。因为在film_id列上有主键索引，所以MySQL优化器知道这只会返回一行数 据，优化器在生成执行计划的时候，就已经通过索引信息知道将返回多少行数据了。因为 查询优化器已经明确知道有多少个值(WHERE条件中的值)需要做索引查询，所以这里 的表访问类型是const。在执行计划的第二步时，MySQL将第一步中返回的film_id列当作一个已知取值的列来处理。因为优化器清楚在第一步执行完成后，该值就会是明确的了。注意，正如在第一步中一样，使用film_actor字段对表的访问类型也是const。
索引覆盖	当索引中的列包含所有查询中需要使用的列的时候，MySQL就可以使用索引返回需 要的数据，而无须查询对应的数据行
子查询优化	MySQL在某些情况下可以将子查询转换为一种效率更高的形式，从而减少多个查询 多次对数据进行访问。
提前终止查询	在发现已经满足查询需求的时候，MySQL总是能够立刻终止查询。一个典型的例子 就是当使用了LIMIT子句的时候。除此之外，MySQL在其他几类情况下也会提前终 止查询，例如发现了一个不成立的条件，这时MySQL可以立刻返回一个空结果。
等值传播	如果两列的值可通过等式联接，那么MySQL能够把其中一列的WHERE条件传递到另一列上.select film.film.film_id,film_actor.actor_id from film join film_actor using film_id where film.film_id=1
列表IN()的比较	在很多数据库服务器中，IN()完全等同于多个OR条件的子句，因为这两者是完全等 价的。在MySQL中这点是不成立的，MySQL将IN()列表中的数据先进行排序，然后 通过二分查找的方式来确定列表中的值是否满足条件，这是一个O(logn)复杂度的 操作

MySQL如何执行联接查询
MySQL中使用的术语“联接”(对应英文为Join)的范围可能比你熟悉的更广泛。总的来 说，MySQL认为每一个查询都是联接——不仅是匹配两张表中对应行的查询，而是每一 个查询、每一个片段(包括子查询，甚至基于单表的SELECT)都是联接。
UNION查询：MySQL将一系列的单个查询结果放在一个临时表中，然后重新从临时表中读取数据完成UNION。

MySQL的联接执行策略很简单:MySQL对任何联接都执行嵌套循环联接操作，即 MySQL先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行， 依次下去，直到找到所有表中匹配的行为止。最后根据各个表匹配的行，返回查询中需要 的各列。MySQL会尝试在最后一个联接表中找到所有匹配的行，如果最后一个联接表无 法找到更多的行，MySQL返回到上一层次的联接表，看是否能够找到更多的匹配记录， 依此类推，迭代执行.

执行计划
MySQL不像其他其他关系型数据库生成字节码来执行查询，而是通过生成一个查询指令树，然后通过查询执行引擎执行完成这棵指令树并返回结果。

联接查询优化器
MySQL查询优化器最重要的一部分就是联接查询优化器，它决定了多个表联接时的顺 序。通常多表联接的时候，可以有多种不同的联接顺序来获得相同的执行结果。联接查询 优化器通过评估不同顺序时的成本来选择一个成本最低的联接顺序。

排序优化
无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序。
当不能使用索引生成排序结果的时候，MySQL需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘，不过MySQL将这个过程统一称为文件排序 (filesort)。
如果需要排序的数据量小于“排序缓冲区”，MySQL使用内存进行快速排序操作。如果内存不够排序，那么MySQL会先将数据分块，对每个独立的块使用“快速排序”进行排序， 并将各个块的排序结果存放在磁盘上，然后将各个排好序的块进行合并(merge)，最后 返回排序结果。

排序类型分两种：单次传输排序【一次全量数句读取，包含了所有的列，占用空间】和两次传输排序【第一次只读取主见和排序字段，第二次对排序的记录进行随机IO，性能差】。

在联接查询的时候如果需要排序，MySQL会分两种情况来处理这样的文件排序。如果 ORDER BY子句中的所有列都来自联接的第一个表，那么MySQL在联接处理第一个表的 时候就进行文件排序。如果是这样，那么在MySQL的EXPLAIN结果中可以看到Extra字段 会有“Using filesort”字样。除此之外的所有情况，MySQL都会先将联接的结果存放到一个 临时表中，然后在所有的联接都结束后，再进行文件排序。在这种情况下，在MySQL的 EXPLAIN结果的Extra字段可以看到“Using temporary;Using filesort”字样。如果查询中有 LIMIT的话，LIMIT也会在文件排序之后应用，所以即使需要返回较少的数据，临时表和 需要排序的数据量仍然会非常大。

查询执行引擎
MySQL只是简单地根据执行计划给 出的指令逐步执行。在根据执行计划逐步执行的过程中，有大量的操作需要通过调用存储 引擎实现的接口来完成，这些接口也就是我们称为“handler API”的接口。查询中的每一个表都由一个handler的实例表示。如果一个表在查询中出现了三次，服务器会创建三个 handler对象。前面我们有意忽略了这一点，实际上，MySQL在优化阶段就为每个表创建 了一个handler实例，优化器根据这些实例的接口可以获取表的相关信息，包括表的所有列名、索引统计信息等等。

将结果返回给客户端
MySQL将结果集返回客户端是一个增量且逐步返回的过程。例如，我们回头看看前面的联接操作，一旦服务器处理完最后一个联接表，开始生成第一条结果时，MySQL就可以 开始向客户端逐步返回结果集了。这样处理有两个好处:服务器端无须存储太多的结果， 也就不会因为要返回太多结果而消耗太多内存。另外，这样的处理也可让MySQL客户端第一时间获得返回的结果。结果集中的每一行都会以一个满足MySQL客户端/服务器通信协议的封包发送，再通过TCP协议进行传输，在TCP传输的过程中，可能对MySQL的封 包进行缓存，然后批量传输。

5、MySQL查询优化器的局限性

5.1 UNION的限制

有时，MySQL无法将限制条件从UNION的外层“下推”到内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。
如果希望UNION的各个子句能够根据LIMIT只取部分结果集，或者希望能够先排好序再合并结果集的话，就需要在UNION的各个子句中分别使用这些子句。例如，想将两个子查询结果联合起来，然后再取前20条记录，那么MySQL会将两个表存放到同一个临时表 中，然后再取出前20行记录:

(select actor_name from film_actor)
UNION ALL
(select user_name from user)
limit 20

(select actor_name from film_actor limit 20)
UNION ALL
(select user_name from user limit 20)
limit 20

5.2 并行执行

MySQL无法利用多核特性来并行执行查询。很多其他的关系数据库能够提供这个特性， 但是MySQL做不到。这里特别指出是想告诉读者不要花时间去尝试寻找并行执行查询的方法。

5.3 同一个表中查询和更新

MySQL不允许对一张表同时进行查询和更新。这其实并不是优化器的限制。

update film as out_film
set actor_count = (select count(*) from film as inner_filmwhere out_film.film_name = inner_film.film_name
);

6、优化特定类型的查询

6.1 优化count()查询

COUNT()是一个特殊的函数，有两种非常不同的作用:它可以统计某列的值的数量，也可以统计行数。在统计列值时要求列值是非空的(不统计NULL)。如果在COUNT()的括 号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数。COUNT()的另一个作用是统计结果集的行数。当MySQL确认括号内的表达式值不可能为 空时，实际上就是在统计行数COUNT(*)。

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

优化联接查询

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

6.2 使用WITH ROLLUP优化GROUP BY

with rollup是MySQL对返回的分组结果再做一次超级聚合，使用group by进行分组之后，with rollup会新增加一行对所有的分组数据再进行一次聚合。

6.3 优化limit和offset子句

在系统中需要进行分页操作的时候，我们通常会使用LIMIT加上偏移量的办法实现，同时 加上合适的ORDER BY子句。如果有对应的索引，通常效率会不错，否则，MySQL需要 做大量的文件排序操作。
一个非常常见又令人头疼的问题是，在偏移量非常大的时候，例如，可能是LIMIT 1000，20这样的查询，这时MySQL需要查询10020条记录然后只返回最后20条，前面10 000条记录都将被抛弃，这样的代价非常高。如果所有的页面被访问的频率都相同，那么 这样的查询平均需要访问半个表的数据。要优化这种查询，要么是在页面中限制分页的数量，要么是优化大偏移量的性能。

优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的行。然后根据需要做一次联接操作再返回所需的列【延迟联接】。在偏移量很大的时候，这样做的效率会有非常大的提升。

select film_id,film_name from film order by title limit 50,5;

修改为：

select film_id,film_name from film
join(select film_id from film order by title limit 50,5
) as temp
on film.film_id = temp.film_id;

它允许服务器在不访问行的情况下检查索引中尽可能少的数据，然后，一旦找到所需的行，就将它们与整个表联接，以从该行中检索其他列。
有时候也可以将LIMIT查询转换为已知位置的查询，让MySQL通过范围扫描获得对应的结果。LIMIT和OFFSET的问题，其实是OFFSET的问题，它会导致MySQL扫描大量不需要的行然后再抛弃掉。如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签 记录的位置开始扫描，这样就可以避免使用OFFSET。

6.4 优化SQL CALC FOUND ROWS

分页的时候，另一个常用的技巧是在LIMIT语句中加上SQL_CALC_FOUND_ROWS提示 (hint)，这样就可以获得去掉LIMIT以后满足条件的行数，因此可以作为分页的总数。 看起来，MySQL做了一些非常“高深”的优化，像是通过某种方法预测了总行数。但实际 上，MySQL只有在扫描了所有满足条件的行以后，才会知道行数，所以加上这个提示以 后，不管是否需要，MySQL都会扫描所有满足条件的行，然后再抛弃掉不需要的行，而 不是在满足LIMIT的行数后就终止扫描。所以该提示的代价可能非常高。

// 获取数据记录
mysql> select sql_calc_found_rows * from user limit 2;
// 获取总数，即select count(*) from user
mysql> select found_rows();

一个更好的设计是将具体的页数换成“下一页”按钮，假设每页显示20条记录，那么我们每 次查询时都是用LIMIT返回21条记录并只显示20条，如果第21条存在，那么就显示“下一 页”按钮，否则就说明没有更多的数据，也就无须显示“下一页”按钮了。

6.5 优化UNION查询

MySQL总是通过创建并填充临时表的方式来执行UNION查询，因此很多优化策略在UNION查询中都没法很好地被使用。经常需要手工地将WHERE、LIMIT、ORDER BY等 子句“下推”到UNION的各个子查询中，以便优化器可以充分利用这些条件进行优化(例 如，直接将这些子句冗余地写一份到各个子查询)。

除非你确实需要服务器消除重复的行，否则一定要使用UNION ALL，这一点很重要。如 果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。即使有ALL关键字，MySQL仍然会使用临时表 存储结果。事实上，MySQL总是将结果放入临时表，然后再读出，再返回给客户端，虽 然很多时候这样做是没有必要的(例如，MySQL可以直接把这些结果返回给客户端)。