高性能 MySQL 第四版（GPT 重译）（三）(1)https://developer.aliyun.com/article/1484348
MySQL 可以在满足查询或步骤时立即停止处理查询（或查询中的步骤）。明显的情况是LIMIT子句，但还有几种其他类型的早期终止。例如，如果 MySQL 检测到一个不可能的条件，它可以中止整个查询。你可以在以下示例中看到这一点：
mysql> EXPLAIN SELECT film.film_id FROM sakila.film WHERE film_id = −1; *************************** 1\. row *************************** id: 1 select_type: SIMPLE table: NULL partitions: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL filtered: NULL Extra: Impossible WHERE
这个查询在优化步骤中停止了，但 MySQL 也可以在其他一些情况下提前终止执行。当查询执行引擎识别到需要检索不同值或在值不存在时停止时，服务器可以使用这种优化。例如，以下查询找到所有没有任何演员的电影:⁷
SELECT film.film_id FROM sakila.film LEFT OUTER JOIN sakila.film_actor USING(film_id) WHERE film_actor.film_id IS NULL;
这个查询通过消除任何有演员的电影来工作。每部电影可能有很多演员，但一旦找到一个演员，它就会停止处理当前电影并移动到下一个，因为它知道WHERE子句禁止输出该电影。类似的“Distinct/not-exists”优化可以应用于某些类型的DISTINCT、NOT EXISTS()和LEFT JOIN查询。
等式传播
MySQL 识别查询将两列视为相等时，例如在JOIN条件中，并在等效列之间传播WHERE子句。例如，在以下查询中：
SELECT film.film_id FROM sakila.film INNER JOIN sakila.film_actor USING(film_id) WHERE film.film_id > 500;
MySQL 知道WHERE子句不仅适用于film表，也适用于film_actor表，因为USING子句强制两列匹配。
如果你习惯于另一个不能做到这一点的数据库服务器，你可能会被建议通过手动指定两个表的WHERE子句来“帮助优化器”，就像这样：
... WHERE film.film_id > 500 AND film_actor.film_id > 500
这在 MySQL 中是不必要的。它只会使你的查询更难维护。
IN()列表比较
在许多数据库服务器中，IN()只是多个OR子句的同义词，因为两者在逻辑上是等价的。但在 MySQL 中不是这样，它对IN()列表中的值进行排序，并使用快速二进制搜索来查看值是否在列表中。这在列表大小为 O(log n)时，而等效的OR子句系列在列表大小为 O(n)时（即对于大型列表来说要慢得多）。
前面的列表非常不完整，因为 MySQL 执行的优化比我们在整个章节中能够涵盖的要多，但它应该让您了解优化器的复杂性和智能。如果有一件事情您应该从这个讨论中记住，那就是不要试图预先聪明地超越优化器。您可能最终只是击败它或使查询变得更加复杂且难以维护，而没有任何好处。一般来说，您应该让优化器自行处理。
��然，优化器再聪明，有时候也不会给出最佳结果。有时候您可能了解一些优化器不知道的数据，比如由于应用逻辑保证为真的事实。此外，有时候优化器没有必要的功能，比如哈希索引；在其他时候，正如前面提到的，其成本估算可能更喜欢一个比另一个更昂贵的查询计划。
如果您知道优化器没有给出好的结果并且知道原因，您可以帮助它。一些选项包括向查询添加提示，重写查询，重新设计模式或添加索引。
表和索引统计信息
回想一下 MySQL 服务器架构中的各个层次，我们在图 1-1 中进行了说明。服务器层包含查询优化器，不存储数据和索引的统计信息。这是存储引擎的工作，因为每个存储引擎可能保留不同类型的统计信息（或以不同方式保留）。
因为服务器不存储统计信息，MySQL 查询优化器必须向引擎请求查询中表的统计信息。引擎提供优化器统计信息，例如每个表或索引的页数，表和索引的基数，行和键的长度，以及键分布信息。优化器可以使用这些信息来帮助它决定最佳执行计划。我们将在后面的章节中看到这些统计信息如何影响优化器的选择。
MySQL 的连接执行策略
MySQL 比您可能习惯的更广泛地使用连接这个术语。总之，它认为每个查询都是一个连接——不仅仅是从两个表中匹配行的每个查询，而是每个查询，无论是子查询还是甚至针对单个表的SELECT。因此，了解 MySQL 如何执行连接非常重要。
考虑一个UNION查询的示例。MySQL 将UNION执行为一系列单个查询，其结果被拼接到临时表中，然后再次读取出来。每个单独的查询在 MySQL 术语中都是一个连接，从结果临时表中读取也是如此。
MySQL 的连接执行策略曾经很简单：它将每个连接都视为嵌套循环连接。这意味着 MySQL 运行一个循环来查找表中的一行，然后运行一个嵌套循环来查找下一个表中的匹配行。直到在连接中的每个表中找到匹配行为止。然后根据SELECT列表中的列构建并返回一行。它尝试通过在最后一个表中查找更多匹配行来构建下一行。如果找不到任何匹配行，则回溯一个表并在那里查找更多行。它一直回溯，直到在某个表中找到另一行，然后在下一个表中查找匹配行，依此类推。
从版本 8.0.20 开始，不再使用块嵌套循环连接；取而代之的是 哈希连接。这使得连接过程的执行速度与以前一样快，甚至更快，尤其是如果其中一个数据集可以存储在内存中。
执行计划
MySQL 不会生成字节码来执行查询，就像许多其他数据库产品那样。相反，查询执行计划实际上是一个指令树¹⁰，查询执行引擎遵循该树以生成查询结果。最终计划包含足够的信息来重建原始查询。如果在查询上执行 EXPLAIN EXTENDED，然后是 SHOW WARNINGS，你将看到重建的查询。¹¹
任何多表查询在概念上都可以表示为一棵树。例如，可能可以像 图 8-2 所示的那样执行一个四表连接。
图 8-2. 多表连接的一种方式
这就是计算机科学家所说的平衡树。然而，这不是 MySQL 执行查询的方式。正如我们在前一节中描述的那样，MySQL 总是从一个表开始，并在下一个表中查找匹配的行。因此，MySQL 的查询执行计划总是采用左深树的形式，如 图 8-3 所示。
图 8-3. MySQL 如何连接多个表
连接优化器
MySQL 查询优化器中最重要的部分是连接优化器，它决定了多表查询的最佳执行顺序。通常可以以几种不同的顺序连接表并获得相同的结果。连接优化器估计各种计划的成本，并尝试选择成本最低的计划，以获得相同的结果。
这是一个查询，其表可以以不同的顺序连接而不改变结果：
SELECT film.film_id, film.title, film.release_year, actor.actor_id, actor.first_name, actor.last_name FROM sakila.film INNER JOIN sakila.film_actor USING(film_id) INNER JOIN sakila.actor USING(actor_id);
你可能会想到几种不同的查询计划。例如，MySQL 可以从 film 表开始，使用 film_actor 表中的 film_id 索引找到 actor_id 值，然后查找 actor 表的主键行。Oracle 用户可能会将其表述为“film 表是 film_actor 表的驱动表，film_actor 表是 actor 表的驱动表。” 这应该是有效的，对吧？现在让我们使用 EXPLAIN 看看 MySQL 想要如何执行查询：
*************************** 1\. row *************************** id: 1 select_type: SIMPLE table: actor partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 200 filtered: 100.00 Extra: NULL *************************** 2\. row *************************** id: 1 select_type: SIMPLE table: film_actor partitions: NULL type: ref possible_keys: PRIMARY,idx_fk_film_id key: PRIMARY key_len: 2 ref: sakila.actor.actor_id rows: 27 filtered: 100.00 Extra: Using index *************************** 3\. row *************************** id: 1 select_type: SIMPLE table: film partitions: NULL type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 2 ref: sakila.film_actor.film_id rows: 1 filtered: 100.00 Extra: NULL
这与前一段中建议的计划完全不同。MySQL 希望从 actor 表开始（我们知道这是因为它在 EXPLAIN 输出中首先列出），并按相反的顺序进行。这真的更有效吗？让我们看看。STRAIGHT_JOIN 关键字强制连接按查询中指定的顺序进行。这是修改后查询的 EXPLAIN 输出：
mysql> EXPLAIN SELECT STRAIGHT_JOIN film.film_id...\G *************************** 1\. row *************************** id: 1 select_type: SIMPLE table: film partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 1000 filtered: 100.00 Extra: NULL *************************** 2\. row *************************** id: 1 select_type: SIMPLE table: film_actor partitions: NULL type: ref possible_keys: PRIMARY,idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: sakila.film.film_id rows: 5 filtered: 100.00 Extra: Using index *************************** 3\. row *************************** id: 1 select_type: SIMPLE table: actor partitions: NULL type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 2 ref: sakila.film_actor.actor_id rows: 1 filtered: 100.00 Extra: NULL
这说明了为什么 MySQL 希望反转连接顺序：这样做将使其能够检查第一个表中的行数更少。¹² 在这两种情况下，它将能够在第二个和第三个表中执行快速的索引查找。不同之处在于它将不得不执行多少这样的索引查找。将 film 放在第一位将需要大约一千次探测（参见 rows 字段）到 film_actor 和 actor，即第一个表中的每一行都需要一次。如果服务器首先扫描 actor 表，它只需要对后续表进行两百次索引查找。换句话说，反转的连接顺序将需要更少的回溯和重读。
这是 MySQL 的连接优化器可以重新排列查询以使其执行成本更低的简单示例。重新排序连接通常是一种非常有效的优化。然而，有时不会得到最佳计划，对于这些情况，你可以使用 STRAIGHT_JOIN 并按照你认为最佳的顺序编写查询，但这样的情况很少见。在大多数情况下，连接优化器将胜过人类。
连接优化器试图生成具有最低成本的查询执行计划树。在可能的情况下，它检查所有子树的潜在组合，从所有单表计划开始。
不幸的是，对n个表进行连接将有n阶乘的连接顺序组合要检查。这被称为所有可能查询计划的搜索空间，并且增长非常快：一个包含 10 个表的连接可以以 3,628,800 种不同的方式执行！当搜索空间增长过大时，优化查询可能需要花费太长时间，因此服务器停止进行完整分析。相反，它会采用“贪婪”搜索等快捷方式，当表的数量超过optimizer_search_depth变量指定的限制时（如果需要，您可以更改该变量）。
MySQL 拥有许多启发式方法，通过多年的研究和实验积累而来，用于加速优化阶段。这可能是有益的，但也可能意味着 MySQL 可能（在极少数情况下）错过一个最佳计划并选择一个不太优化的计划，因为它试图避免检查每个可能的查询计划。
有时查询无法重新排序，连接优化器可以利用这一事实通过消除选择来减少搜索空间。LEFT JOIN是一个很好的例子，相关子查询也是（稍后会详细介绍子查询）。这是因为一个表的结果取决于从另一个表检索的数据。这些依赖关系帮助连接优化器通过消除选择来减少搜索空间。
排序优化
对结果进行排序可能是一个昂贵的操作，因此您通常可以通过避免排序或在较少行上执行排序来提高性能。
当 MySQL 无法使用索引生成排序结果时，它必须自行对行进行排序。它可以在内存中或磁盘上执行此操作，但无论如何，它总是将此过程称为文件排序，即使实际上并未使用文件。
如果要排序的值将适合排序缓冲区，MySQL 可以完全在内存中执行排序，使用快速排序。如果 MySQL 无法在内存中执行排序，则通过对值进行分块排序在磁盘上执行排序。它使用快速排序对每个块进行排序，然后将排序的块合并到结果中。
有两种文件排序算法：
两次遍历（旧）
读取行指针和ORDER BY列，对它们进行排序，然后扫描排序列表并重新读取行以输出。
两次遍历算法可能非常昂贵，因为它从表中读取两次行，第二次读取会导致大量随机 I/O。
单次遍历（新）
读取查询所需的所有列，按ORDER BY列对它们进行排序，然后扫描排序列表并输出指定的列。
它可能更有效率，特别是对于大型 I/O 受限数据集，因为它避免了从表中两次读取行，并将随机 I/O 交换为更多的顺序 I/O。然而，它有可能使用更多的空间，因为它保存每行的所有所需列，而不仅仅是用于排序行的列。这意味着更少的元组将适合排序缓冲区，文件排序将不得不执行更多的排序合并传递。
MySQL 可能为文件排序使用比您预期的更多的临时存储空间，因为它为将要排序的每个元组分配了固定大小的记录。这些记录足够大，可以容纳最大可能的元组，包括每个VARCHAR列的完整长度。此外，如果您使用 utf8mb4，MySQL 为每个字符分配 4 个字节。因此，我们曾看到过，优化不良的模式导致用于排序的临时空间比磁盘上整个表的大小大几倍。
当对连接进行排序时，MySQL 可能在查询执行过程中的两个阶段执行文件排序。如果ORDER BY子句仅涉及连接顺序中第一个表的列，MySQL 可以对该表进行文件排序，然后继续连接。如果发生这种情况，EXPLAIN在Extra列中显示“Using filesort”。在所有其他情况下，例如对连接顺序中不是第一个表的表进行排序，或者ORDER BY子句包含多个表的列时，MySQL 必须将查询结果存储到临时表中，然后在连接完成后对临时表进行文件排序。在这种情况下，EXPLAIN在Extra列中显示“Using temporary; Using filesort”。如果有LIMIT，它将在文件排序之后应用，因此临时表和文件排序可能非常大。
查询执行引擎
解析和优化阶段输出查询执行计划，MySQL 的查询执行引擎使用该计划来处理查询。该计划是一个数据结构；它不是可执行的字节码，这是许多其他数据库执行查询的方式。
与优化阶段相比，执行阶段通常并不那么复杂：MySQL 只需按照查询执行计划中给出的指令进行操作。计划中的许多操作调用存储引擎接口实现的方法，也称为处理程序 API。查询中的每个表都由处理程序的实例表示。例如，如果查询中的表出现三次，服务器将创建三个处理程序实例。尽管我们之前略过了这一点，但 MySQL 实际上在优化阶段早期创建处理程序实例。优化器使用它们获取有关表的信息，例如它们的列名和索引统计信息。
存储引擎接口具有许多功能，但只需要十几个“构建块”操作来执行大多数查询。例如，有一个操作用于读取索引中的第一行，另一个操作用于读取索引中的下一行。这对于执行��引扫描的查询已经足够了。这种简单的执行方法使得 MySQL 的存储引擎架构成为可能，但也带来了我们讨论过的优化器限制之一。
注意
并非所有操作都是处理程序操作。例如，服务器管理表锁。处理程序可能实现自己的较低级别锁定，就像 InnoDB 使用行级锁一样，但这并不取代服务器自己的锁定实现。如第一章中所述，所有存储引擎共享的内容都在服务器中实现，例如日期和时间函数、视图和触发器。
为执行查询，服务器只需重复指令，直到没有更多行可检查为止。
向客户端返回结果
执行查询的最后一步是向客户端发送响应。即使查询不返回结果集，也会向客户端连接发送有关查询的信息，例如它影响了多少行。
服务器逐步生成并发送结果。一旦 MySQL 处理完最后一个表并成功生成一行，它就可以并且应该将该行发送给客户端。这有两个好处：它让服务器避免在内存中保存行，而且意味着客户端尽快开始获取结果。¹³结果集中的每一行在 MySQL 客户端/服务器协议中以单独的数据包发送，尽管协议数据包可以在 TCP 协议层缓冲并一起发送。
MySQL 查询优化器的限制
MySQL 对查询执行的方法并非对于优化每种类型的查询都是理想的。幸运的是，MySQL 查询优化器做得不好的情况有限，通常可以更有效地重写这些查询。
UNION 的限制
MySQL 有时无法将UNION外部的条件“推入”到内部，这些条件可以用于限制结果或启用其他优化。
如果你认为UNION中的任何一个单独查询会受益于LIMIT，或者如果你知道它们将与其他查询组合后受到ORDER BY子句的影响，那么你需要将这些子句放在每个UNION部分中。例如，如果你将两个表UNION在一起，并将结果限制为前 20 行，MySQL 将把两个表存储到临时表中，然后从中检索出 20 行：
(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;
此查询将从actor表中存储 200 行，从customer表中存储 599 行到临时表中，然后从该临时表中获取前 20 行。您可以通过在UNION中的每个查询中多余地添加LIMIT 20来避免这种情况：
(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;
现在临时表将只包含 40 行。除了性能提升外，您可能需要更正查询：从临时表中检索行的顺序是未定义的，因此在最终LIMIT之前应该有一个整体ORDER BY。
等式传播
等式传播有时可能会产生意想不到的成本。例如，考虑一个巨大的IN()列表，优化器知道它将等于其他表的某些列，这是由于WHERE，ON或USING子句将列设置为相等。
优化器将通过将列表复制到所有相关表中的相应列来“共享”列表。这通常是有帮助的，因为它为查询优化器和执行引擎提供了更多实际执行IN()检查的选项。但是当列表非常大时，它可能导致优化和执行变慢。在撰写本文时，还没有这个问题的内置解决方法 - 如果这对您是个问题，您将不得不更改源代码。 （对大多数人来说这不是问题。）
并行执行
MySQL 无法在多个 CPU 上并行执行单个查询。这是一些其他数据库服务器提供的功能，但 MySQL 不支持。我们提到这一点是为了让您不要花费大量时间来尝试如何在 MySQL 上实现并行查询执行！
在同一表上进行 SELECT 和 UPDATE
MySQL 不允许您在从表中SELECT的同时对其运行UPDATE。这实际上不是一个优化器的限制，但了解 MySQL 如何执行查询可以帮助您解决问题。这是一个被禁止的查询示例，即使它是标准 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打开表之前打开并关闭表，因此查询现在将成功：
mysql> UPDATE tbl -> INNER JOIN( -> SELECT type, count(*) AS c -> FROM tbl -> GROUP BY type -> ) AS der USING(type) -> SET tbl.c = der.c;
优化特定类型的查询
在本节中，我们提供了如何优化某些类型查询的建议。我们在书中的其他地方已经详细介绍了大部分这些主题，但我们想列出一些常见的优化问题，以便您可以轻松参考。
本节中的大部分建议是与版本相关的，可能在未来的 MySQL 版本中不适用。服务器未来可能会自动执行这些优化的原因是没有理由的。
优化COUNT()查询
COUNT()聚合函数以及如何优化使用它的查询，可能是 MySQL 中前 10 个最被误解的主题之一。您可以进行网络搜索，找到更多关于这个主题的错误信息，我们不想去想。
在我们深入优化之前，重要的是您了解COUNT()的真正作用。
COUNT()的作用
COUNT()是一个特殊的函数，以两种非常不同的方式工作：它计算值和行。一个值是一个非NULL表达式（NULL是值的缺失）。如果你在括号内指定列名或其他表达式，COUNT()会计算该表达式具有值的次数。这对许多人来说很令人困惑，部分原因是值和NULL很令人困惑。如果你需要了解 SQL 中的工作原理，我们建议阅读一本关于 SQL 基础的好书。（互联网在这个主题上并不一定是准确信息的好来源。）
COUNT()的另一种形式简单地计算结果中的行数。这是 MySQL 在知道括号内表达式永远不会是NULL时所做的。最明显的例子是COUNT(*)，这是COUNT()的一种特殊形式，不会将*通配符扩展为表中的所有列的完整列表，正如你可能期望的那样；相反，它完全忽略列并计算行数。
我们经常看到的一个最常见的错误是在想要计算行数时在括号内指定列名。当你想知道结果中的行数时，应该始终使用COUNT(*)。这清楚地传达了你的意图，并避免了性能不佳。
简单的优化
一个常见的问题是如何在同一列中检索多个不同值的计数，只需一个查询，以减少所需的查询数量。例如，假设你想创建一个单一查询，计算每种颜色的物品数量。你不能使用OR（例如，SELECT COUNT(color = 'blue' OR color = 'red') FROM items;），因为这不会将不同颜色的计数分开。你也不能将颜色放在WHERE子句中（例如，SELECT COUNT(*) FROM items WHERE color = 'blue' AND color = 'red';），因为颜色是互斥的。以下是解决这个问题的查询：¹⁴
SELECT SUM(IF(color = 'blue', 1, 0)) AS blue,SUM(IF(color = 'red', 1, 0)) AS red FROM items;
这里有另一个等效的例子，但是不使用SUM()，而是使用COUNT()，并确保表达式在条件为假时没有值：
SELECT COUNT(color = 'blue' OR NULL) AS blue, COUNT(color = 'red' OR NULL) AS red FROM items;
使用近似值
有时候你不需要准确的计数，所以可以使用近似值。优化器在EXPLAIN中的估计行数通常很好用。只需执行一个EXPLAIN查询，而不是真实查询。
有时，准确的计数比近似值要低效得多。一位客户要求帮助计算网站上活跃用户的数量。用户计数被缓存并显示 30 分钟，之后重新生成并再次缓存。这本质上是不准确的，所以近似值是可以接受的。查询包括几个WHERE条件，以确保不计算非活跃用户或“默认”用户，这是应用程序中的特殊用户 ID。删除这些条件只会稍微改变计数，但使查询更有效。进一步的优化是消除一个不必要的DISTINCT以消除一个文件排序。重写后的查询速度更快，几乎返回完全相同的结果。
更复杂的优化
一般来说，COUNT()查询很难优化，因为它们通常需要计算大量行（即访问大量数据）。在 MySQL 本身内部进行优化的另一种选择是使用覆盖索引。如果这不够帮助，你需要对应用程序架构进行更改。考虑使用外部缓存系统，如memcached。你可能会发现自己面临熟悉的困境，“快速、准确和简单：选择其中两个。”
优化连接查询
实际上，这个主题在大部分书中都有涉及，但我们将提到一些重点：
- 确保在
ON或USING子句中的列上有索引。在添加索引时考虑连接顺序。如果您在列c上将表A和B连接，并且查询优化器决定以B，A的顺序连接表，则不需要在表B上索引该列。未使用的索引是额外的开销。通常情况下，只需要在连接顺序中的第二个表上添加索引，除非出于其他原因需要。 - 尽量确保任何
GROUP BY或ORDER BY表达式仅引用来自单个表的列，这样 MySQL 可以尝试为该操作使用索引。 - 在升级 MySQL 时要小心，因为连接语法、运算符优先级和其他行为在不同时间发生了变化。曾经是正常连接的东西有时会变成交叉乘积，这是一种返回不同结果甚至无效语法的不同连接类型。
使用 ROLLUP 优化 GROUP BY
分组查询的变体之一是要求 MySQL 在结果中进行超级聚合。您可以使用WITH ROLLUP子句实现这一点，但可能不如您需要的那样优化。使用EXPLAIN检查执行方法，注意分组是通过文件排序还是临时表完成的；尝试移除WITH ROLLUP，看看是否得到相同的分组方法。您可能可以通过我们在本节前面提到的提示来强制分组方法。
有时在应用程序中进行超级聚合更有效，即使这意味着从服务器获取更多行。您还可以在FROM子句中嵌套子查询或使用临时表保存中间结果，然后使用UNION查询临时表。
最好的方法可能是将WITH ROLLUP功能移至应用程序代码中。
优化 LIMIT 和 OFFSET
具有LIMIT和OFFSET的查询在进行分页的系统中很常见，几乎总是与ORDER BY子句一起使用。拥有支持排序的索引是很有帮助的；否则，服务器就必须进行大量的文件排序。
一个常见的问题是偏移量值很高。如果您的查询看起来像LIMIT 10000, 20，那么它将生成 10,020 行并丢弃其中的前 10,000 行，这是非常昂贵的。假设所有页面被均匀访问，这样的查询平均扫描一半的表。为了优化它们，您可以限制分页视图中允许的页面数量，或者尝试使高偏移量更有效。
提高效率的一个简单技巧是在覆盖索引上执行偏移，而不是完整行。然后，您可以将结果与完整行连接并检索所需的其他列。这可能更有效。考虑以下查询：
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子句的连接。
有时您还可以将限制转换为位置查询，服务器可以将其执行为索引范围扫描。例如，如果您预先计算并索引一个位置列，可以将查询重写为以下形式：
SELECT film_id, description FROM sakila.film WHERE position BETWEEN 50 AND 54 ORDER BY position;
排名数据提出了类似的问题，但通常将GROUP BY混入其中。您几乎肯定需要预先计算和存储排名。
LIMIT和OFFSET的问题实际上是OFFSET，它表示服务器正在生成和丢弃的行。如果使用一种类似游标的方式记住您获取的最后一行的位置，您可以通过从该位置开始而不是使用OFFSET来生成下一组行。例如，如果您想从最新的租赁记录开始向后工作进行分页，您可以依赖于它们的主键始终递增。您可以像这样获取第一组结果：
SELECT * FROM sakila.rental ORDER BY rental_id DESC LIMIT 20;
此查询返回租赁记录 16049 到 16030。下一个查询可以从那个点继续：
SELECT * FROM sakila.rental WHERE rental_id < 16030 ORDER BY rental_id DESC LIMIT 20;
这种技术的好处是，无论您分页到表的多远，它都非常高效。
其他替代方法包括使用预先计算的摘要或与仅包含主键和您需要的ORDER BY列的冗余表连接。
优化 SQL_CALC_FOUND_ROWS
另一种常见的分页显示技术是在带有LIMIT的查询中添加SQL_CALC_FOUND_ROWS提示，这样您就会知道没有LIMIT时会返回多少行。这里似乎有一种“魔法”发生，服务器预测它会找到多少行。但不幸的是，服务器并没有真正做到这一点；它无法计算实际未找到的行数。这个选项只是告诉服务器生成并丢弃其余的结果集，而不是在达到所需行数时停止。这是非常昂贵的。
更好的设计是将分页器转换为“下一页”链接。假设每页有 20 个结果，那么查询应该使用 21 行的LIMIT，并且只显示 20 个。如果结果中存在第 21 行，则有下一页，您可以呈现“下一页”链接。
另一种可能性是获取并缓存比您需要的更多行，比如 1,000 行，然后为连续的页面从缓存中检索它们。这种策略让您的应用程序知道完整结果集有多大。如果少于 1,000 行，应用程序就知道要呈现多少页链接；如果超过 1,000 行，应用程序只需显示“找到超过 1,000 个结果”。这两种策略比重复生成整个结果并丢弃大部分结果要高效得多。
有时您也可以通过运行EXPLAIN查询并查看结果中的rows列来估计结果集的完整大小（嘿，即使 Google 也不显示确切的结果计数！）。如果无法使用这些策略，使用单独的COUNT(*)查询来查找行数可能比SQL_CALC_FOUND_ROWS快得多，如果它可以使用覆盖索引。
优化 UNION
MySQL 总是通过创建临时表并填充UNION结果来执行UNION查询。MySQL 无法对UNION查询应用您可能习惯的许多优化。您可能需要通过手动“推送”WHERE、LIMIT、ORDER BY和其他条件（即从外部查询复制到UNION中的每个SELECT中）来帮助优化器。
始终使用UNION ALL很重要，除非您需要服务器消除重复行。如果省略ALL关键字，MySQL 会向临时表添加 distinct 选项，该选项使用完整行来确定唯一性。这是非常昂贵的。请注意，ALL关键字并不消除临时表。即使不是真正必要的情况下（例如，结果可以直接返回给客户端时），MySQL 也总是将结果放入临时表，然后再次读取它们。
摘要
查询优化是模式、索引和查询设计相互交织的拼图中的最后一块，以创建高性能应用程序。要编写良好的查询，您需要了解模式和索引，反之亦然。
最终，这仍然是关于响应时间和理解查询执行的方式，以便你可以推断时间消耗的位置。通过添加一些东西，比如解析和优化过程，这只是理解 MySQL 如何访问表和索引的下一步，我们在上一章中讨论过。当你开始研究查询和索引之间的相互作用时，出现的额外维度是 MySQL 如何基于在另一个表中找到的数据访问一个表或索引。
优化始终需要三管齐下的方法：停止做某些事情，减少做的次数，以及更快地完成。
¹ 如果应用程序与服务器不在同一主机上，网络开销最严重，但即使它们在同一台服务器上，MySQL 和应用程序之间的数据传输也不是免费的。
² 请参阅本章后面的“优化 COUNT()查询”了解更多相关内容。
³ Percona Toolkit 的pt-archiver工具使这类工作变得简单且安全。
⁴ 如果查询太大，服务器将拒绝接收更多数据并抛出错误。
⁵ 你可以通过SQL_BUFFER_RESULT来解决这个问题，稍后我们会看到。
⁶ 例如，范围检查查询计划会为JOIN中的每一行重新评估索引。你可以通过在EXPLAIN中的Extra列中查找“range checked for each record”来查看这个查询计划。这个查询计划还会增加Select_full_range_join服务器变量。
⁷ 我们同意，一部没有演员的电影很奇怪，但 Sakila 示例数据库中没有列出Slacker Liaisons的演员，它描述为“一部关于鲨鱼和一名学生在古代中国必须与鳄鱼见面的快节奏故事。”
⁸ 请参阅 MySQL 手册中关于版本特定提示的“索引提示”和“优化器提示”以了解可用的提示以及如何使用它们。
⁹ 正如我们后面所展示的，MySQL 的查询执行并不是那么简单；有许多优化措施使其变得复杂。
¹⁰ 你可以在语句之前使用EXPLAIN FORMAT=TREE …来查看这一点。
¹¹ 服务器从执行计划生成输出。因此，它具有与原始查询相同的语义，但不一定具有相同的文本。
¹² 严格来说，MySQL 并不试图减少它读取的行数。相反，它试图优化更少的页面读取。但是行数通常可以让你大致了解查询的成本。
¹³ 如果需要，你可以通过SQL_BUFFER_RESULT提示来��响这种行为。请参阅官方 MySQL 手册中的“优化器提示”了解更多信息。
¹⁴ 你也可以将SUM()表达式写成SUM(color = 'blue'), SUM(color ='red')。
第九章：复制
MySQL 内置的复制是在 MySQL 之上构建大型、高性能应用程序的基础，使用所谓的“横向扩展”架构。复制允许您将一个或多个服务器配置为另一个服务器的副本，使它们的数据与源副本同步。这不仅对高性能应用程序有用——它也是许多高可用性、可扩展性、灾难恢复、备份、分析、数据仓库等任务策略的基石。
在本章中，我们的重点不在于每个功能是什么，而在于何时使用它。官方 MySQL 文档在解释诸如半同步复制、多源复制等功能方面非常详细，您在设置这些功能时应参考此文档。
复制概述
复制解决的基本问题是在同一拓扑结构内保持数据库实例之间的数据同步。它通过将修改数据或数据结构的事件写入源服务器上的日志来实现这一点。副本服务器可以从源上的日志中读取事件并重放它们。这创建了一个异步过程，其中副本的数据副本在任何给定时刻都不能保证是最新的。副本延迟——实时和副本所代表的内容之间的延迟——没有上限。大型查询可能导致副本落后源几秒、几分钟，甚至几小时。
MySQL 的复制大多是向后兼容的——也就是说，新版本的服务器通常可以成为旧版本服务器的副本而无需麻烦。但是，旧版本的服务器通常无法作为新版本的副本：它们可能不理解新版本服务器使用的新功能或 SQL 语法，复制使用的文件格式可能存在差异。例如，您无法从 MySQL 5.6 源复制到 MySQL 5.5 副本。在从一个主要或次要版本升级到另一个主要或次要版本（例如从 5.6 到 5.7 或从 5.7 到 8.0）之前，最好测试您的复制设置。在次要版本内的升级，例如从 5.7.34 到 5.7.35，预计是兼容的；阅读发布��明以了解从一个版本到另一个版本的确切变化。
复制相对于扩展读取而言效果较好，您可以将读取定向到副本，但除非设计正确，否则它不是扩展写入的好方法。将许多副本连接到源只会导致写入在每个副本上执行多次。整个系统受限于最弱部分可以执行的写入数量。
以下是复制的一些常见用途：
数据分发
MySQL 的复制通常不会占用太多带宽，尽管后面会看到，基于行的复制可能比传统的基于语句的复制使用更多带宽。您还可以随时停止和启动复制。因此，它对于在地理上较远的位置（如不同的数据中心或云区域）维护数据副本非常有用。远程副本甚至可以与间歇性（有意或无意地）的连接一起工作。但是，如果您希望副本具有非常低的复制延迟，您将需要一个稳定的、低延迟的链接。
扩展读取流量
MySQL 复制可以帮助您在多个服务器之间分发读取查询，这对于读取密集型应用程序非常有效。您可以通过简单的代码更改进行基本的负载平衡。在小规模上，您可以使用简单的方法，如硬编码主机名或轮询 DNS（将单个主机名指向多个 IP 地址）。您还可以采取更复杂的方法。标准的负载平衡解决方案，如网络负载平衡产品，可以很好地在 MySQL 服务器之间分发读取。
备份
复制是一种有助于备份的有价值的技术。然而，副本既不是备份，也不是备份的替代品。
分析和报告
为报告/分析（在线分析处理，或 OLAP）查询使用专用副本是一个很好的策略，可以将该负载与您的业务需要为外部客户请求提供的服务隔离开来。复制是实现这种隔离的一种方式。
高可用性和故障转移
复制可以帮助避免使 MySQL 成为应用程序的单点故障。涉及复制的良好故障转移系统可以显著减少停机时间。
测试 MySQL 升级
常见做法是使用升级后的 MySQL 版本设置一个副本，并在升级每个实例之前使用它来确保您的查询按预期工作。
复制的工作原理
在深入了解设置复制的细节之前，让我们快速看一下 MySQL 实际如何复制数据。在这个解释中，我们涵盖了最简单的复制拓扑结构，一个源和一个副本。
从高层次上看，复制是一个简单的三部分过程：
- 源在其二进制日志中记录其数据的更改为“二进制日志事件”。
- 副本将源的二进制日志事件复制到自己的本地中继日志。
- 副本通过在中继日志中重放事件，将更改应用于自己的数据。
图 9-1 更详细地说明了复制的最基本形式。
这种复制架构将在副本上获取和重放事件的过程解耦，这使它们可以是异步的，即 I/O 线程可以独立于 SQL 线程工作。
图 9-1. MySQL 复制的工作原理
复制内部机制
现在我们已经为您复习了复制的基础知识，让我们深入了解它。让我们看看复制实际如何工作，看看它的优点和缺点，以及检查一些更高级的复制配置选项。
高性能 MySQL 第四版（GPT 重译）（三）(3)https://developer.aliyun.com/article/1484350
