韭菜生活
查询优化
编辑
2024-08-31
高性能 MySQL
00
请注意，本文编写于 82 天前，最后修改于 82 天前，其中某些信息可能已经过时。

目录

慢查询分析
检索了不需要的数据
MySQL在扫描额外的记录
重构查询的方式
一个复杂查询 vs 多个简单查询
切分查询
分解联接查询
查询执行的基础
MySQL的客户端/服务器通信协议
查询状态
查询优化处理
优化策略
MySQL 使用的优化
查询执行引擎
将结果返回给客户端
MySQL查询优化器的局限性
UNION的限制
等值传递
并行执行
在同一个表中查询和更新
优化特定类型的查询
优化COUNT()查询
优化联接查询
使用WITH ROLLUP优化GROUP BY
优化LIMIT和OFFSET子句
优化SQL CALC FOUND ROWS
优化UNION查询
参考

优化通常需要三管齐下：不做、少做、快速地做。

本文对应原书第8章-查询性能优化，该章节给出了 MySQL 内部对查询所使用优化的手段和特定类型的查询优化，推荐阅读原文。

慢查询分析

分析步骤

  1. 确认应用程序是否在检索大量且不必要的数据，包含行或列。
  2. 确认MySQL服务器层是否在分析大量不需要的数据行。

检索了不需要的数据

  • 查询了不需要的记录。先使用SELECT语句查询大量的结果，然后获取前面的N行后关闭结果集。
  • 多表联接时返回全部列
sql
-- 错误的写法
SELECT * FROM sakila.actor
	INNER JOIN sakila.film_actor USING(actor_id)
	INNER JOIN sakila.film USING(film_id)
WHERE sakila.film.title = 'Academy Dinosaur';
-- 正确的写法
SELECT sakila.actor.* FROM sakila.actor...;
  • 总是取出全部列
    • 无法使用索引覆盖，带来额外I/O、内存和CPU的消耗。
    • 但并不总是坏处，比如可以简化业务开发，因为能提高相同代码服用性。或者代码中使用了缓存，这比多个独立的只获取部分列的查询可能更好。
  • 重复查询相同的数据

MySQL在扫描额外的记录

最简单的衡量查询开销的三个指标

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

响应时间

响应时间是两部分之和

  • 服务时间，指数据库处理这个查询真正花了多长时间。
  • 排队时间，指服务器因为等待某些资源而没有真正执行查询的时间。
    • I/O等待
    • 锁等待

响应时间是多少才是合理的？实际上可以使用“快速上限估计”法来估算查询的响应时间。这个方法可以参考《Relational Database Index Design and the》，有中文版[数据库索引设计与优化][https://book.douban.com/subject/26419771//]。

扫描的行数和返回的行数

这两个指标不是那么准，因为并不是所有行的访问代价都相同，有的行短访问速度快，有的行在内存中比磁盘上的快。可以通过扫描的行数和返回的行数的比率来看查询效率，理想情况下是1:1，一般在1:1到10:1之间，也可能非常大。

扫描的行数和访问类型

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

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

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

重构查询的方式

一个复杂查询 vs 多个简单查询

在传统实现中，总是强调需要数据库层完成尽可能多的工作，这样做的逻辑在于以前人们总是认为网络通信、查询解析和优化是一件代价很高的事情。但是这样的想法对于MySQL并不适用，因为MySQL从设计上让连接和断开连接都很轻量，在返回一个小的查询结果方面很高效。

需要注意的是，如果在一个查询能够胜任时还将其写成多个独立的查询是不明智的。

切分查询

将大查询切分成小查询，每个查询的功能完全一样，只完成一小部分，每次只返回一小部分查询结果。

删除旧数据

如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的DELETE语句切分成多个较小的查询可以尽可能小地影响MySQL的性能，同时还可以降低MySQL复制的延迟。

分解联接查询

简单地说，可以对每一个表进行一次单表查询，然后将结果在应用程序中进行联接。

sql
-- 分解前
SELECT * FROM tag
	JOIN tag_post ON tag_post.tag_id=tag.id
	JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';
-- 分解后
SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id in (123,456,567,9098,8904);

用分解联接查询的方式重构查询有如下优势：

  • 让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。例如，上面查询中的tag mysql已经被缓存了，那么应用就可以跳过第一个查询。再例如，应用中已经缓存了ID为123、567、9098的内容，那么第三个查询的IN()中就可以少几个ID。
  • 将查询分解后，执行单个查询可以减少锁的竞争。
  • 在应用层做联接，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
  • 查询本身的效率也可能会有所提升。在这个例子中，使用IN()代替联接查询，可以让MySQL按照ID顺序进行查询，这可能比随机的联接要更高效。
  • 可以减少对冗余记录的访问。在应用层做联接查询，意味着对于某条记录应用只需要查询一次，而在数据库中做联接查询，则可能需要重复地访问一部分数据。从这点看，这样的重构还可能会减少网络和内存的消耗。

在有些场景下，在应用程序中执行联接操作会更加有效。

  • 当可以缓存和重用之前查询结果中的数据时
  • 当在多台服务器上分发数据时
  • 当能够使用IN()列表替代联接查询大型表时
  • 当一次联接查询中多次引用同一张表时

查询执行的基础

图8-1：查询执行路径

  1. 客户端给服务器发送一条SQL查询语句。
  2. 服务器端进行SQL语句解析、预处理，再由优化器生成对应的执行计划。
  3. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
  4. 将结果返回给客户端。

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

MySQL的客户端和服务器之间的通信协议是“半双工”的，这意味着，在任何时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。

这种通信的明显限制是，无法进行流量控制。一旦一端开始发送消息，另一端要接收完整个消息才能响应它。。在这种情况下，客户端若接收完整的结果，然后取前面几条需要的结果，或者接收完几条结果后就“粗暴”地断开连接，都不是好主意。这也是在必要的时候一定要在查询中加上LIMIT限制的原因。

MySQL通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接收全部结果并缓存通常可以减少服务器的压力，让查询能够早点结束、早点释放相应的资源。

查询状态

对于一个MySQL连接，或者一个线程，任何时刻都有一个状态，该状态表示了MySQL当前正在做什么。

最简单的是使用 SHOW FULL PROCESSLIST 命令来查看。

IdUserHostdbCommandTimeStateInfo
5event_schedulerlocalhostDaemon582652Waiting on empty queue
12root172.18.0.1:48670Sleep61
13root172.18.0.1:48674Sleep60
14root172.18.0.1:48682testQuery0init/* ApplicationName=DBeaver 24.1.3 - SQLEditor <Script.sql> */ SHOW FULL PROCESSLIST

一些状态含义

Sleep

线程正在等待客户端发送新的请求。

Query

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

Locked

在MySQL服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中。

Analyzing and statistics

线程正在检查存储引擎的统计信息，并优化查询。

Copying to tmp table [on disk]

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

Sorting result

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

查询优化处理

这包括多个子阶段：解析SQL、预处理、优化SQL执行计划。这个过程中产生的任何错误（例如，语法错误）都可能终止查询。在实际执行中，这几部分可能一起执行也可能单独执行。

语法解析器和预处理

  1. MySQL进行语法分析，生成解析树。
  2. 预处理器进行语义分析，比如检查数据表和数据列是否存在，别名等。
  3. 预处理器进行验证权限。

查询优化器

优化器将解析树转化成查询执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。可以通过查询当前会话的Last_query_cost的值来得知MySQL计算的当前查询的成本：

sql
-- 先执行某个 sql
-- 在执行下面的 sql 来查看成本
SHOW STATUS LIKE 'Last_query_cost';
| Variable_name   | Value       |
| ---             |   ---       |
| Last_query_cost | 1040.599000 |

这个结果表示，MySQL的优化器认为大概需要做1040个数据页的随机查找才能完成上面的查询。

有很多种原因会导致MySQL优化器选择错误的执行计划

  • 统计信息不准确。MySQL服务器依赖存储引擎提供的统计信息来评估成本，但是有的存储引擎提供的信息是准确的，有的偏差可能非常大。例如，InnoDB因为其MVCC的架构，并不能维护一个数据表的行数的精确统计信息。
  • 成本指标并不完全等同于运行查询的实际成本，因此即使统计数据是准确的，查询的成本也可能超过或者低于MySQL估算的近似值。例如，有时候某个执行计划虽然需要读取更多的页面，但是它的成本却更低。因为如果这些页面都是顺序读或者这些页面都已经在内存中的话，那么它的访问成本将很低。MySQL并不知道哪些页面在内存中、哪些在磁盘中，所以查询在实际执行过程中到底需要多少次物理I/O是无法得知的。
  • MySQL的最优可能和你想的最优不一样。你可能希望执行时间尽可能短，但是MySQL只是基于其成本模型选择最优的执行计划，而有些时候这并不是最快的执行方式。所以，这里我们看到的根据执行成本来选择执行计划并不是完美的模型。
  • MySQL从不考虑其他并发执行的查询，这可能会影响到当前查询的速度。
  • MySQL也并不是任何时候都是基于成本的优化。它有时也会基于一些固定的规则，例如，如果存在全文搜索的MATCH()子句，则在存在FULLTEXT索引的时候就使用全文索引。即使有时候使用其他索引和WHERE条件可以远比这种方式要快，MySQL也仍然会使用对应的全文索引。
  • MySQL不会考虑不受其控制的操作的成本，例如，执行存储函数或者用户自定义函数的成本。
  • 优化器有时候无法估算所有可能的执行计划，所以它可能错过实际上最优的执行计划。

优化策略

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

MySQL 使用的优化

下面列出的优化手段远不是MySQL优化器的全部。不要自以为比优化器更聪明。如果能够确认优化器给出的不是最佳选择，并且清楚背后的原理，那么也可以帮助优化器做进一步的优化。例如，可以在查询中添加 hint 提示，也可以重写查询，或者重新设计更优的库表结构，或者添加更合适的索引。

重新定义联接表的顺序

数据表的联接并不总是按照在查询中指定的顺序进行。

将外联接转化成内联接

并不是所有的OUTER JOIN语句都必须以外联接的方式执行。诸多因素，例如WHERE条件、库表结构都可能会让外联接等价于一个内联接。MySQL能够识别这一点并重写查询，让其可以调整联接顺序。

使用代数等价变换规则

MySQL可以使用一些代数等价变换规则来简化并规范表达式。它可以合并和减少一些比较，还可以移除一些恒成立和一些恒不成立的判断。例如，5=5 AND a>5将被改写为a>5。类似地，如果有(a<b AND b=c) AND a=5则会改写为b>5 AND b=c AND a=5。

优化COUNT()、MIN()和MAX()

索引和列是否可为空通常可以帮助MySQL优化这类表达式。例如，要找到某一列的最小值，只需要查询对应B-tree索引最左端的记录，MySQL可以直接获取索引的第一行记录。在优化器生成执行计划的时候就可以利用这一点，在B-tree索引中，优化器会将这个表达式作为一个常数对待。类似地，如果要查找一个最大值，也只需读取B-tree索引的最后一条记录。如果MySQL使用了这种类型的优化，那么在EXPLAIN中就可以看到“Select tables optimized away”。从字面意思可以看出，它表示优化器已经从执行计划中移除了该表，并以一个常数代替。

预估并转化为常数表达式

当MySQL检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理。

覆盖索引扫描

当索引中的列包含所有查询中需要使用的列的时候，MySQL就可以使用索引返回需要的数据，而无须查询对应的数据行。

子查询优化

MySQL在某些情况下可以将子查询转换为一种效率更高的形式，从而减少多个查询多次对数据进行访问。

提前终止查询

在发现已经满足查询需求的时候，MySQL总是能够立刻终止查询。一个典型的例子就是当使用了LIMIT子句的时候。

等值传播

如果两列的值可通过等式联接，那么MySQL能够把其中一列的WHERE条件传递到另一列上。

sql
SELECT film.film_id
FROM sakila.film
	INNER JOIN sakila.film_actor USING(film_id)
WHERE film.film_id > 500;
-- 如果使用的是其他的数据库管理系统，可能还需要手动通过一些条件来告知优化器这个WHERE条件适用于两个表
... WHERE film.film_id > 500 AND film_actor.film_id > 500

列表IN()的比较

在很多数据库服务器中，IN()完全等同于多个OR条件的子句，因为这两者是完全等价的。在MySQL中这点是不成立的，MySQL将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个O(logn)复杂度的操作，等价地转换成OR查询的复杂度为O(n)，对于IN()列表中有大量取值的时候，MySQL的处理速度将会更快。

表和索引的统计信息

MySQL架构由多个层次组成。在服务器层有查询优化器，却没有保存数据和索引的统计信息。统计信息由存储引擎实现，不同的存储引擎可能会存储不同的统计信息。

  • 每个表或者索引有多少个页面
  • 每个表的每个索引的基数是多少
  • 数据行和索引的长度是多少
  • 索引的分布信息等

MySQL如何执行联接查询

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

在MySQL 8.0.20版本之后，已经不再使用基于块的嵌套循环联接操作，取而代之的是哈希联接。这让联接操作性能变得更好，特别是当数据集可以全部存储在内存时。

执行计划

MySQL生成查询的一棵指令树，然后通过查询执行引擎执行完成这棵指令树并返回结果。任何多表查询都可以使用一棵树来表示。

多表联接的一种方式

flowchart BT
	tb1[表1] --> join1[联接]
	tb2[表2] --> join1
	tb3[表3] --> join2[联接]
	tb4[表4] --> join2
	join1 --> join3[联接]
	join2 --> join3

MySQL实现多表联接的方式

flowchart BT
	tb1[表1] --> join1[联接]
	tb2[表2] --> join1
	join1 --> join2[联接]
	tb3[表3] --> join2
	join2 --> join3[联接]
	tb4[表4] --> join3

联接查询优化器

MySQL查询优化器最重要的一部分就是联接查询优化器，联接查询优化器通过评估不同顺序时的成本来选择一个成本最低的联接顺序。

有的时候，优化器给出的并不是最优的联接顺序。这时可以使用STRAIGHT_JOIN关键字重写查询，让优化器按照你认为的最优的联接顺序执行。

n个表的联接可能有n的阶乘种联接顺序，我们称之为所有可能的查询计划的“搜索空间”。这时，优化器选择使用“贪婪”搜索的方式查找“最优”的联接顺序。实际上，当需要联接的表超过optimizer_search_depth的限制的时候，就会选择“贪婪”搜索模式了

有时查询不能重新排序，联接优化器可以利用这一点通过消除选择来减小搜索空间。左联接（LEFT JOIN）和相关子查询都是很好的例子，这是因为，一个表的结果依赖于另外一个表中检索的数据，这种依赖关系通常可以帮助联接优化器通过消除选择来减少搜索空间。

排序优化

无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序。

当不能使用索引生成排序结果的时候，MySQL需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘，不过MySQL将这个过程统一称为文件排序（filesort）。

  • 如果需要排序的数据量小于“排序缓冲区”，MySQL使用内存进行快速排序操作。
  • 如果内存不够排序，那么MySQL会先将数据分块，对每个独立的块使用“快速排序”进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排好序的块进行合并（merge）。

两次传输排序（旧版本使用）

读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。 这需要进行两次数据传输，即需要从数据表中读取两次数据，第二次读取数据的时候，因为是读取排序列进行排序后的所有记录，这会产生大量的随机I/O，所以两次传输排序的成本非常高。

单次传输排序（新版本使用）

先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果。

因为不再需要从数据表中读取两次数据，对于I/O密集型的应用来说，这样做的效率高了很多。另外，相比两次传输排序，这个算法只需要一次顺序I/O就可读取所有的数据，而无须任何的随机I/O。(不懂为啥只需要一次顺序I/O)

然而，这种方式可能占用更多空间，因为会保存查询中每一行所需要的列，而不仅仅是进行排序操作所需要的列。这意味着更少的元组可以放入排序缓冲区，使得文件排序（filesort）操作必须执行更多的排序合并过程。

联表并排序的执行过程

在联接查询的时候如果需要排序，MySQL会分两种情况来处理这样的文件排序。

如果ORDER BY子句中的所有列都来自联接的第一个表，那么MySQL在联接处理第一个表的时候就进行文件排序。如果是这样，那么在MySQL的EXPLAIN结果中可以看到Extra字段会有“Using filesort”字样。

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

查询执行引擎

相对于查询优化阶段，查询执行阶段不是那么复杂：MySQL只是简单地根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中，有大量的操作需要通过调用存储引擎实现的接口来完成，这些接口也就是我们称为“handler API”的接口。

存储引擎接口有着非常丰富的功能，但是底层接口却只有几十个，这些接口像“搭积木”一样能够完成查询的大部分操作。

将结果返回给客户端

执行查询的最后一个阶段是将结果返回给客户端。即使查询不需要给客户端返回结果集，MySQL仍然会返回这个查询的一些信息，如该查询影响到的行数。

MySQL将结果集返回客户端是一个增量且逐步返回的过程。这样处理有两个好处：服务器端无须存储太多的结果，也就不会因为要返回太多结果而消耗太多内存。另外，这样的处理也可让MySQL客户端第一时间获得返回的结果。

MySQL查询优化器的局限性

UNION的限制

有时，MySQL无法将限制条件从UNION的外层“下推”到内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。

如果希望实现下面的效果，需要在UNION的各个子句中分别使用这些子句

  • 希望UNION的各个子句能够根据LIMIT只取部分结果集
  • 希望能够先排好序再合并结果集的话

例如，想将两个子查询结果联合起来，然后再取前20条记录，那么MySQL会将两个表存放到同一个临时表中，然后再取出前20行记录：

sql
(SELECT first_name, last_name
 FROM sakila.actor
 ORDER BY last_name)
UNION ALL
(SELECT first_name, last_name
 FROM sakila.customer
 ORDER BY last_name)
LIMIT 20;

可以通过在UNION的两个子查询中分别加上一个LIMIT 20来减少临时表中的数据：

sql
(SELECT first_name, last_name
 FROM sakila.actor ORDER BY last_name
 LIMIT 20)
UNION ALL
(SELECT first_name, last_name
 FROM sakila.customer ORDER BY last_name
 LIMIT 20)
LIMIT 20;

在这里还需要注意一点：从临时表中取出数据的顺序并不是一定的，所以如果想获得正确的顺序，还需要在最后的LIMIT操作前加上一个全局的ORDER BY操作。

等值传递

如果忘了是啥意思，在本文中搜索“等值传播”。等值传递会带来一些意想不到的额外消耗。例如，考虑一列上的巨大IN()列表。如果这个列表非常大，则会导致优化和执行都会变慢。

并行执行

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

在同一个表中查询和更新

MySQL不允许对一张表同时进行查询和更新。下面是一段无法运行的SQL语句，尽管这是一段符合标准的SQL语句。这个查询会将表中每一行的c字段值更新为和该行的type字段值相同的行数量：

sql
mysql> UPDATE tbl AS outer_tbl
    -> SET c = (
    -> SELECT count(*) FROM tbl AS inner_tbl
    -> WHERE inner_tbl.type = outer_tbl.type
    -> );
ERROR 1093 (HY000): You can't specify target table 'outer_tbl'for update in FROM clause

可以使用生成表的形式来绕过上面的限制，因为MySQL只会把这个表当作一个临时表来处理。这执行了两个查询：一个是子查询中的SELECT语句，另一个是多表UPDATE查询，其中包含原表和子查询的联接结果。子查询会在UPDATE语句打开表之前就完成，所以下面的查询将会正常执行：

sql
mysql> UPDATE tbl
    -> INNER JOIN(
    -> SELECT type, count(*) AS c
    -> FROM tbl
    -> GROUP BY type
    -> ) AS der USING(type)
    -> SET tbl.c = der.c;

优化特定类型的查询

优化COUNT()查询

COUNT()的作用

  • 在统计列值时要求列值是非空的（不统计NULL）。如果在COUNT()的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数。
  • 当MySQL确认括号内的表达式值不可能为空时，实际上就是在统计行数。最简单的就是当我们使用COUNT(*)的时候，这种情况下通配符*并不会像我们猜想的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数。

如果要统计行数，请直接使用COUNT(*)。

简单优化

sql
SELECT SUM(IF(color = 'blue', 1, 0)) AS blue,SUM(IF(color = 'red', 1, 0))
AS red FROM items;
-- 使用 COUNT()
SELECT COUNT(color = 'blue' OR NULL) AS blue, COUNT(color = 'red' OR NULL)
AS red FROM items;

使用近似值

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

更进一步的优化则可以尝试删除DISTINCT这样的约束来避免文件排序。

更复杂的优化

通常来说，COUNT()查询需要扫描大量的行才能获得精确的结果，因此是很难优化的。除了前面提到的方法，在MySQL层面还能做的就只有索引覆盖扫描了。如果这还不够，那就需要考虑修改应用的架构，可以增加类似Memcached这样的外部缓存系统。

不过，可能很快你就会陷入一个熟悉的困境：“快速、精确和实现简单”。三者永远只能满足其二，必须舍掉一个。

优化联接查询

  • 确保ON或者USING子句中的列上有索引。
  • 确保任何GROUP BY和ORDER BY中的表达式只涉及一个表中的列，这样MySQL才有可能使用索引来优化这个过程。
  • 当升级MySQL的时候需要注意：联接语法、运算符优先级等其他可能会发生变化的地方。因为以前是普通联接的地方可能会变成笛卡儿积，不同类型的联接可能会生成不同的结果，甚至会产生语法错误。

使用WITH ROLLUP优化GROUP BY

分组查询的一个变种就是要求MySQL对返回的分组结果再做一次超级聚合。可以使用WITH ROLLUP子句来实现这种逻辑。最好的办法是尽可能地将WITH ROLLUP功能转移到应用程序中处理。

优化LIMIT和OFFSET子句

LIMIT和OFFSET通常用于分页，但是在偏移量非常大的时候效率不高。例如，可能是LIMIT 10000，20这样的查询，这时MySQL需要查询10020条记录然后只返回最后20条，前面10 000条记录都将被抛弃，这样的代价非常高。

优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的行。然后根据需要做一次联接操作再返回所需的列。

考虑下面的查询：

sql
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);

这种“延迟联接”之所以有效，是因为它允许服务器在不访问行的情况下检查索引中尽可能少的数据，然后，一旦找到所需的行，就将它们与整个表联接，以从该行中检索其他列。类似的技术也适用于带有LIMIT子句的联接。

有时候也可以将LIMIT查询转换为已知位置的查询，让MySQL通过范围扫描获得对应的结果。

sql
SELECT film_id, description FROM sakila.film
WHERE position BETWEEN 50 AND 54 ORDER BY position;

如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET。

sql
-- 首先使用下面的查询获得第一组结果：
SELECT * FROM sakila.rental
ORDER BY rental_id DESC LIMIT 20;
-- 假设上面的查询返回的是主键为16，049到16，030的租借记录，那么下一页查询就可以从16，030这个点开始：
SELECT * FROM sakila.rental
WHERE rental_id < 16030
ORDER BY rental_id DESC LIMIT 20;

其他优化办法还包括使用预先计算的汇总表，或者联接到一个冗余表，冗余表只包含主键列和需要做排序的数据列。

优化SQL CALC FOUND ROWS

在LIMIT语句中加上SQL_CALC_FOUND_ROWS提示（hint），这样就可以获得去掉LIMIT以后满足条件的行数，因此可以作为分页的总数。

但实际上，MySQL只有在扫描了所有满足条件的行以后，才会知道行数，所以加上这个提示以后，不管是否需要，MySQL都会扫描所有满足条件的行，然后再抛弃掉不需要的行，而不是在满足LIMIT的行数后就终止扫描。所以该提示的代价可能非常高。

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

另一种做法是先获取并缓存较多的数据。有时候也可以考虑使用EXPLAIN的结果中的rows列的值来作为结果集总数的近似值（实际上，Google的搜索结果总数也是一个近似值）。当需要精确结果的时候，再单独使用COUNT(*)来满足需求。

优化UNION查询

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

除非你确实需要服务器消除重复的行，否则一定要使用UNION ALL。 如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。

事实上，MySQL总是将结果放入临时表，然后再读出，再返回给客户端，虽然很多时候这样做是没有必要的（例如，MySQL可以直接把这些结果返回给客户端）。

参考

本文作者:jdxj

本文链接:

版权声明:本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！