关于mysql:MySQL优化查询性能优化

后面介绍了如何设计最优的库表构造、如何建设最好的索引，这些对于高性能来说是必不可少的。但这些还不够——还须要正当的设计查问。如果查问写得很蹩脚，即便库表构造再正当、索引再适合，也无奈实现高性能。

分析单条查问的性能比拟罕用的形式有：

1. 慢查问日志
2. EXPLAIN 命令
3. SHOW PROFILE 命令

慢查问起因

是否申请了不须要的数据

有些查问会申请超过理论须要的数据，而后这些多余的数据会被应用程序抛弃。这会给 MySQL 服务器带来额定的累赘，并减少网络开销，另外也会耗费应用服务器的 CPU 和内存资源。一些典型案例如下：

查问不须要的记录

一个常见的谬误是经常会误以为 MySQL 会只返回须要的数据，实际上 MySQL 却是先返回全副后果集再进行计算。开发者先应用 SELECT 语句查问大量的后果，而后获取后面的 N 行后敞开后果集。他们认为 MySQL 会执行查问，并只返回他们须要的 10 条数据，而后进行查问。理论状况是 MySQL 会查问出全副的后果集，客户端的应用程序会接管全副的后果集数据，而后摈弃其中大部分数据。最简略无效的解决办法就是在这样的查问前面加上 LIMIT。

总是取出全部列

每次看到 SELECT * 的时候都须要用狐疑的眼光扫视，是不是真的须要返回全副的列？很可能不是必须的。取出全部列，会让优化器无奈实现索引笼罩扫描这类优化，还会为服务器带来额定的 I /O、内存和 CPU 的耗费。

当然，查问返回超过须要的数据也不总是好事。很多时候这种有点节约数据库资源的形式 能够简化开发，因为能进步雷同代码片段的复用性；如果应用了缓存机制，查问全部列并缓存，能够让更多的无关查问间接命中缓存，这显然是查问局部列很难做到的。

是否扫描了额定的记录

在 EXPLAIN 语句中的 type 列反馈了拜访类型。拜访类型有很多种，从全表扫描到索引扫描、范畴扫描、惟一索引查问、常数援用等。这里列的这些，速度是从慢到快，扫描的行数也是从小到大。如果查问没有方法找到适合的拜访类型，那么解决的最好方法通常就是减少一个适合的索引。

如果发现查问须要扫描大量的数据但只返回多数的行，那么通常能够尝试上面的技巧去优化它：

• 应用索引笼罩扫描，把所有须要用的列都放到索引中，这样存储引擎毋庸回表获取对应行就能够返回后果了。
• 扭转库表构造。例如应用独自的汇总表。
• 重写查问语句，让 MySQL 优化器可能以更优化的形式执行这个查问。

重构查问的形式

在优化有问题的查问时，指标应该是找到一个更优的办法取得理论须要的后果——而不肯定总是须要从 MySQL 获取截然不同的后果集。有时候，能够将查问转换一种写法让其返回一样的后果，然而性能更好。但也能够通过批改利用代码，用另一种形式实现查问，最终达到一样的目标。

一个简单查问还是多个简略查问

设计查问的时候一个须要思考的重要问题是，是否须要将一个简单的查问分成多个简略的查问。在传统实现中，总是强调须要数据库层实现尽可能多的工作，这样做的逻辑在于以前总是认为网络通信、查问解析和优化是一件代价很高的事件。

然而这样的想法对于 MySQL 并不实用，MySQL 从设计上让连贯和断开连接都很轻量级，在返回一个小的查问后果方面很高效。古代的网络速度比以前要快很多，无论是带宽还是提早。所以运行多个小查问当初曾经不是大问题了。

有时候，将一个大查问合成为多个小查问是很有必要的。别胆怯这样做，好好掂量一下这样做是不是会缩小工作量。不过，在利用设计的时候，如果一个查问可能胜任时还写成多个独立查问是不明智的。

切分查问

有时候对于一个大查问咱们须要“分而治之”，将大查问切分成小查问，每个查问性能齐全一样，只实现一小部分，每次只返回一小部分查问后果。

删除旧的数据就是一个很好的例子。定期地革除大量数据时，如果用一个大的语句一次性实现的话，则可能须要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查问。将一个大的 DELETE 语句切分成多个较小的查问能够尽可能小地影响 MySQL 性能，同时还能够缩小 MySQL 复制的提早。

合成关联查问

很多高性能的利用都会对关联查问进行合成。简略地，能够对每一个表进行一次单表查问，而后将后果在应用程序中进行关联。乍一看，这样做并没有什么益处，本来一条查问，这里却变成多条查问，返回的后果又是截然不同的。事实上，用合成关联查问的形式重构查问有如下的劣势：

• 让缓存的效率更高。许多应用程序能够不便地缓存单表查问对应的后果对象。另外，对 MySQL 的查问缓存来说，如果关联中的某个表产生了变动，那么就无奈应用查问缓存了，而拆分后，如果某个表很少扭转，那么基于该表的查问就能够反复利用查问缓存后果了。
• 将查问合成后，执行单个查问能够缩小锁的竞争。
• 在应用层做关联，能够更容易对数据库进行拆分，更容易做到高性能和可扩大。
• 查问自身效率也可能会有所晋升。应用 IN()代替关联查问，能够让 MySQL 依照 ID 程序进行查问，这可能比随机的关联要更高效。
• 能够缩小冗余记录的查问。在应用层做关联查问，意味着对于某条记录利用只须要查问一次，而在数据库中做关联查问，则可能须要反复地拜访一部分数据。从这点看，这样的重构还可能会缩小网络和内存的耗费。
• 更进一步，这样做相当于在利用中实现了哈希关联，而不是应用 MySQL 的嵌套循环关联。某些场景哈希关联的效率要高很多。

查问执行根底

当心愿 MySQL 可能以更高的性能运行查问时，最好的方法就是弄清楚 MySQL 是如何优化和执行查问的。一旦了解这一点，很多查问优化工作实际上就是遵循一些准则让优化器可能依照料想的正当的形式运行。

参照下图，咱们能够看到当向 MySQL 发送一个申请的时候，MySQL 到底做了些什么：

1. 客户端发送一条查问给服务器。
2. 服务器先查看查问缓存，如果命中了缓存，则立即返回存储在缓存中的后果。否则进入下一阶段。
3. 服务器端进行 SQL 解析、预处理，再由优化器生成对应的执行打算。
4. MySQL 依据优化器生成的执行打算，调用存储引擎的 API 来执行查问。
5. 将后果返回给客户端。

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

MySQL 客户端和服务器之间的通信协议是“半双工”的，这意味着，在任何一个时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时产生。所以，咱们无奈也毋庸将一个音讯切成小块独立来发送。

这种协定让 MySQL 通信简略疾速，然而也从很多中央限度了 MySQL。一个显著的限度是，这意味着没法进行流量管制。一旦一端开始产生音讯，另一端要接管残缺个音讯能力响应它。

查问状态

对于一个 MySQL 连贯，或者说一个线程，任何时刻都有一个状态，该状态示意了 MySQL 以后正在做什么。有很多种形式能查看以后的状态，最简略的是应用 SHOW FULL PROCESSLIST 命令（该命令返回后果中的 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

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

查问缓存

在解析一个查问语句之前，如果查问缓存是关上的，那么 MySQL 会优先查看这个查问是否命中查问缓存中的数据。这个查看是通过一个对大小写敏感的哈希查找实现的。查问和缓存中的查问即便只有一个字节不同，那也不会匹配缓存后果，这种状况下查问就会进入下一阶段的解决。须要留神的是，在 MySQL 8 中，查问缓存曾经废除。

生成执行打算

语法解析器和预处理

首先，MySQL 通过关键字将 SQL 语句进行解析，并生成一棵对应的“解析树”。MySQL 解析器将应用 MySQL 语法规定验证和解析查问，预处理器则依据一些 MySQL 规定进一步查看解析树是否非法，下一步预处理器会验证权限。

查问优化器

一条查问能够有很多种执行形式，最初都返回雷同的后果。优化器的作用就是找到这其中最好的执行打算。

MySQL 应用基于老本的优化器，它将尝试预测一个查问应用某种执行打算时的老本，并抉择其中老本最小的一个。最后，老本的最小单位是随机读取一个 4K 数据页的老本，起初（老本计算公式）变得更加简单，并且引入了一些“因子”来估算某些操作的代价，如当执行一次 WHERE 条件比拟的老本。能够通过查问以后会话的 Last_query_cost 的值来得悉 MySQL 计算的以后查问的老本。

这个后果示意 MySQL 的优化器认为大略须要做 1040 个数据页的随机查找能力实现下面的查问。这是依据一系列的统计信息计算得来的：每个表或者索引的页面个数、索引的基数（索引中不同值的数量）、索引和数据行的长度、索引散布状况。优化器在评估老本的时候并不思考任何层面的缓存，它假如读取任何数据都须要一次磁盘 I /O。

有很多种起因会导致 MySQL 优化器抉择谬误的执行打算，如下所示：

• 统计信息不精确。InnoDB 因为其 MVCC 的架构，并不能保护一个数据表的行数的准确统计信息。
• 执行打算中的老本估算不等同于理论执行的老本。所以即便统计信息精准，优化器给出的执行打算也可能不是最优的。例如有时候某个执行打算尽管须要读取更多的页面，然而它的老本却更小。因为如果这些页面都是程序读或者这些页面都曾经在内存中的话，那么它的拜访老本将很小。MySQL 层面并不知道哪些页面在内存中、哪些在磁盘上，所以查问理论执行过程中到底须要多少次物理 I / O 是无奈得悉的。
• MySQL 的最优可能和你想的最优不一样。你可能心愿执行工夫尽可能的短，然而 MySQL 只是基于其老本模型抉择最优的执行打算，而有些时候这并不是最快的执行形式。
• MySQL 从不思考其余并发执行的查问，这可能会影响到以后查问的速度。
• MySQL 不会思考不受其管制的操作的老本，例如执行存储过程或者用户自定义函数的老本。
• 前面咱们还会看到，优化器有时候无奈去估算所有可能的执行打算，所以它可能错过实际上最优的执行打算。

MySQL 的查问优化器是一个非常复杂的部件，它应用了很多优化策略来生成一个最优的执行打算。上面是一些 MySQL 可能解决的优化类型：

1. 从新定义关联表的程序。
2. 将外连贯转化成内连贯。
3. 应用等价变换规定。MySQL 能够应用一些等价变换来简化并标准表达式。它能够合并和缩小一些比拟，还能够移除一些恒成立和一些恒不成立的判断。
4. 优化 COUNT()、MIN()和 MAX()。
5. 预估并转化为常数表达式。当 MySQL 检测到一个表达式能够转化为常数的时候，就会始终把该表达式作为常数进行优化解决。
6. 笼罩索引扫描。
7. 子查问优化。
8. 提前终止查问。
9. 等值流传。
10. 列表 IN()的比拟。在很多数据库系统中，IN()齐全等同于多个 OR 条件的子句，因为这两者是齐全等价的。在 MySQL 中这点是不成立的，MySQL 将 IN()列表中的数据先进行排序，而后通过二分查找的形式来确定列表中的值是否满足条件，这是一个 O（log n）复杂度的操作，等价地转换成 OR 查问的复杂度为 O（n），对于 IN()列表中有大量取值的时候，MySQL 的处理速度将会更快。

下面列举的远不是 MySQL 优化器的全副，MySQL 还会做大量其余的优化。“不要自认为比优化器更聪慧”，让优化器依照它的形式工作就能够了。

当然，尽管优化器曾经很智能了，然而有时候也无奈给出最优的后果。如果可能确认优化器给出的不是最佳抉择，并且分明背地的原理，那么也能够帮忙优化器做进一步的优化。例如，能够在查问中增加 hint 提醒，也能够重写查问，或者从新设计更优的库表构造，或者增加更适合的索引。

查问执行引擎

在解析和优化阶段，MySQL 将生成查问对应的执行打算，MySQL 的查问执行引擎则依据这个执行打算来实现整个查问。这里执行打算是一个数据结构，而不是和很多其余的关系型数据库那样会生成对应的字节码。

返回后果给客户端

查问执行的最初一个阶段是将后果返回给客户端。即便查问不须要返回后果集给客户端，MySQL 依然会返回这个查问的一些信息，如该查问影响到的行数。

MySQL 将后果集返回客户端是一个增量、逐渐返回的过程。例如，对于关联操作，一旦服务器解决完最初一个关联表，开始生成第一条后果时，MySQL 就能够开始向客户端逐渐返回后果集了。

MySQL 查问优化器的局限性

关联子查问

MySQL 的子查问实现得十分蹩脚。最蹩脚的一类查问是 WHERE 条件中蕴含 IN()的子查问语句，MySQL 会将相干的外层表压到子查问中，它认为这样能够更高效率地查找到数据行，也即它会 将 IN 型子查问改写成 EXISTS 型关联子查问。如果外层的表是一个十分大的表，那么这个查问的性能会十分蹩脚。

对于 IN 型子查问，咱们能够应用联接和索引来优化。还能够应用函数 GROUP_CONCAT()在 IN()中结构一个由逗号分隔的列表来优化，当 IN()列表中的数据量比拟小的时候，性能会比联接更好。

不过，关联子查问也有适宜的场景。对于那些“反向”查问，即某些“没有”、“不存在”的问题，因为关联子查问只有匹配到内层查问就会立即进行并进行下一次的外层循环，所以往往它的效率相比其余类型的查问来说要好。

查问优化器的提醒

如果对优化器抉择的执行打算不称心，能够应用优化器提供的几个提醒（hint）来管制最终的执行打算。上面将列举一些常见的提醒，并简略地给出什么时候应用该提醒。通过在查问中退出相应的提醒，就能够管制该查问的执行打算。

STRAIGHT_JOIN

这个提醒能够搁置在 SELECT 语句的 SELECT 关键字之后，也能够搁置在任何两个关联表的名字之间。第一个用法是让查问中所有的表依照在语句中呈现的程序进行关联。第二个用法令是固定其前后两个表的关联程序。

当 MySQL 没能抉择正确的关联程序的时候，或者因为可能的程序太多导致 MySQL 无奈评估所有的关联程序的时候，STRAIGHT_JOIN 都会很有用。在前面这种状况，MySQL 可能会破费大量工夫在“statistics”状态，加上这个提醒则会大大减少优化器的搜寻空间。

SQL_SMALL_RESULT 和 SQL_BIG_RESULT

这两个提醒只对 SELECT 语句无效。它们通知优化器对 GROUP BY 或者 DISTINCT 查问如何应用长期表及排序。SQL_SMALL_RESULT 通知优化器后果集会很小，能够将后果集放在内存中的索引长期表，以防止排序操作。如果是 SQL_BIG_RESULT，则通知优化器后果集可能会十分大，倡议应用磁盘长期表做排序操作。

USE INDEX、IGNORE INDEX 和 FORCE INDEX

这几个提醒会通知优化器应用或者不应用哪些索引来查问记录（例如，在决定关联程序的时候应用哪个索引）。在 MyQL 5.1 和之后的版本能够通过新增选项 FOR ORDER BY 和 FOR GROUP BY 来指定是否对排序和分组无效。

优化特定类型的查问

优化 COUNT()查问

有时候某些业务场景并不要求齐全准确的 COUNT 值，此时能够用近似值来代替。EXPLAIN 进去的优化器估算的行数就是一个不错的近似值，执行 EXPLAIN 并不需要真正地去执行查问，所以老本很低。

很多时候，计算准确值的老本十分高，而计算近似值则非常简单。例如统计网站的以后沉闷用户数是多少，这个沉闷用户数保留在缓存中，过期工夫为 30 分钟，这个沉闷用户数自身就不是准确值，所以应用近似值代替是能够承受的。另外，如果要准确统计在线人数，通常 WHERE 条件会很简单，一方面须要剔除以后非沉闷用户，另一方面还要剔除零碎中某些特定 ID 的“默认”用户，去掉这些约束条件对总数的影响很小，但却可能很好地晋升该查问的性能。更进一步地优化则能够尝试删除 DISTINCT 这样的束缚来防止文件排序。这样重写过的查问要比原来的准确统计的查问快很多，而返回的后果则简直雷同。

优化关联查问

须要特地提到的是：

• 确保 ON 或者 USING 子句中的列上有索引。在创立索引的时候就要思考到关联的程序。当表 A 和表 B 用列 c 关联的时候，如果优化器的关联程序是 B、A，那么就不须要在 B 表的对应列上建上索引。没有用到的索引只会带来额定的累赘。一般来说，除非有其余理由，否则 只须要在关联程序中的第二个表的相应列上创立索引。
• 尽可能确保任何的 GROUP BY 和 ORDER BY 中的表达式只波及到一个表中的列，这样 MySQL 才有可能应用索引来优化这个过程。
• 当降级 MySQL 的时候须要留神：关联语法、运算符优先级等其余可能会发生变化的中央。因为以前是一般关联的中央可能会变成笛卡儿积，不同类型的关联可能会生成不同的后果等。

优化子查问

对于子查问优化咱们给出的最重要的优化倡议就是尽可能应用关联查问代替，尤其是 MySQL 5.6 版本之前，前面更新的版本能够酌情应用关联。

优化 GROUP BY 和 DISTINCT

在 MySQL 中，当无奈应用索引的时候，GROUP BY 应用两种策略来实现：应用长期表或者文件排序来做分组。对于任何查问语句，这两种策略的性能都有能够晋升的中央。能够通过应用提醒 SQL_BIG_RESULT 和 SQL_SMALL_RESULT 来让优化器依照你心愿的形式运行。

如果没有通过 ORDER BY 子句显式地指定排序列，当查问应用 GROUP BY 子句的时候，后果集会主动依照分组的字段进行排序。如果不关怀后果集的程序，而这种默认排序又导致了须要文件排序，则能够应用 ORDER BY NULL，让 MySQL 不再进行文件排序。也能够在 GROUP BY 子句中间接应用 ASC 或者 DESC 关键字，使分组的后果集按须要的方向排序。

优化 LIMIT 分页

在零碎中须要进行分页操作的时候，咱们通常会应用 LIMIT 加上偏移量的方法实现，同时加上适合的 ORDER BY 子句。如果有对应的索引，通常效率会不错，否则，MySQL 须要做大量的文件排序操作。

一个十分常见又令人头疼的问题是，在偏移量十分大的时候，例如可能是 LIMIT 10000,20 这样的查问，这时 MySQL 须要查问 10 020 条记录而后只返回最初 20 条，后面 10000 条记录都将被摈弃，这样的代价十分高。要优化这种查问，要么是在页面中限度分页的数量，要么是优化大偏移量的性能。

优化此类分页查问的一个最简略的方法就是尽可能地应用索引笼罩扫描，而不是查问所有的列，而后依据须要做一次关联操作再返回所需的列。对于偏移量很大的时候，这样做的效率会晋升十分大。思考上面的查问：

SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;

如果偏移量可能十分大，那么这个查问最好改写成上面的样子：

SELECT film.film_id, film.description
        -> FROM sakila.film
        -> INNER JOIN (
        ->       SELECT film_id FROM sakila.film
        ->       ORDER BY title LIMIT 50, 5
        -> ) AS lim USING(film_id);

这里的“提早关联”将大大晋升查问效率，它让 MySQL 扫描尽可能少的页面，获取须要拜访的记录后再依据关联列回原表查问须要的所有列。这个技术也能够用于优化关联查问中的 LIMIT 子句。

如果能够应用书签记录上次取数据的地位，也能够 在下次查问时间接从该书签记录的地位开始扫描，这样就能够防止应用 OFFSET。其余优化方法还包含应用事后计算的汇总表，或者关联到一个冗余表，冗余表只蕴含主键列和须要做排序的数据列。

分页的时候，另一个罕用的技巧是在 LIMIT 语句中加上 SQL_CALC_FOUND_ROWS 提醒（hint），这样就能够取得去掉 LIMIT 当前满足条件的行数，因而能够作为分页的总数。但加上这个提醒当前，不论是否须要，MySQL 都会扫描所有满足条件的行，而后再摈弃掉不须要的行，而不是在满足 LIMIT 的行数后就终止扫描。所以该提醒的代价可能十分高。

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

另一种做法是先获取并缓存较多的数据——例如缓存 1000 条——而后每次分页都从这个缓存中获取。这样做能够让应用程序依据后果集的大小采取不同的策略，如果后果集少于 1000，就能够在页面上显示所有的分页链接，因为数据都在缓存中，所以这样做性能不会有问题。如果后果集大于 1000，则能够在页面上设计一个额定的“找到的后果多于 1000 条”之类的按钮。这两种策略都比每次生成全副后果集再摈弃掉不须要的数据的效率要高很多。

有时候也能够思考应用 EXPLAIN 的后果中的 rows 列的值来作为后果集总数的近似值（实际上 Google 的搜寻后果总数也是个近似值）。当须要准确后果的时候，再独自应用 COUNT（*）来满足需要，这时如果可能应用索引笼罩扫描则通常也会比 SQL_CALC_FOUND_ROWS 快得多。

优化 UNION 查问

MySQL 总是通过创立并填充长期表的形式来执行 UNION 查问，因而很多优化策略在 UNION 查问中都没法很好地应用。常常须要手工地将 WHERE、LIMIT、ORDER BY 等子句“下推”到 UNION 的各个子查问中，以便优化器能够充分利用这些条件进行优化（例如，间接将这些子句冗余地写一份到各个子查问）。

除非的确须要服务器打消反复的行，否则就肯定要应用 UNION ALL，这一点很重要。如果没有 ALL 关键字，MySQL 会给长期表加上 DISTINCT 选项，这会导致对整个长期表的数据做唯一性查看。这样做的代价十分高。即便有 ALL 关键字，MySQL 依然会应用长期表存储后果。事实上，MySQL 总是将后果放入长期表，而后再读出，再返回给客户端。