MySQL之优化SELECT语句

1. MySQL性能提成优化概述

数据库性能取决于数据库级别的几个因素，例如表，查询和配置设置。这些软件结构导致在硬件级别执行CPU和I / O操作，您必须将这些操作最小化并使其尽可能高效。在研究数据库性能时，首先要学习软件方面的高级规则和准则，并使用时钟时间来衡量性能。成为专家后，您将了解有关内部情况的更多信息，并开始测量诸如CP

2. WHERE子句优化

本节讨论可以对处理WHERE子句进行的优化。这些示例使用SELECT语句，但是对和语句中的WHERE子句进行相同的优化。DELETEUPDATE注意由于有关MySQL优化器的工作正在进行中，因此此处未记录MySQL执行的所有优化。您可能会想重写查询以使算术运算更快，同时又牺牲了可读性。由于MySQL

3. 范围优化

range访问方法使用单个索引来检索包含一个或若干个索引值的时间间隔内表行的子集。它可以用于单部分或多部分索引。以下各节描述了优化器使用范围访问的条件。单部分索引的范围访问方法多部分索引的范围访问方法多值比较的等距范围优化跳过扫描范围访问方法行构造函数表达式的范围优化限制内存使用以进行范围优化单部分

4. 哈希联接优化

从MySQL 8.0.18开始，MySQL对任何查询都具有相等连接条件且不使用索引的查询使用哈希连接，例如：SELECT*FROM t1JOIN t2ON t1.c1=t2.c1;散列连接通常比在这种情况下要快，并且打算在这种情况下代替在MySQL早期版本中使用的块嵌套循环算法（请参阅块嵌套循环联接

5. 储存引擎下的优化

种优化提高了非索引列和常量之间直接比较的效率。在这种情况下，条件被“下推”到存储引擎进行评估。此优化只能由NDB存储引擎使用。对于NDB群集，此优化可以消除在群集的数据节点和发出查询的MySQL服务器之间通过网络发送不匹配的行的需求，并且可以将查询的使用速度提高5到10倍（在某些情况下）可以但不使用

6. 索引条件下推优化

索引条件下推（ICP）是针对MySQL使用索引从表中检索行的情况的一种优化。如果没有ICP，则存储引擎将遍历索引以在基表中定位行，并将其返回给MySQL服务器，该MySQL服务器将评估WHERE行的条件。启用ICP后，如果WHERE可以仅使用索引中的列来评估部分条件，则MySQL服务器会将这部分条件

7.嵌套循环联接算法

MySQL使用嵌套循环算法或其上的变体在表之间执行联接。嵌套循环加入算法块嵌套循环连接算法嵌套循环加入算法一个简单的嵌套循环联接（NLJ）算法一次从一个循环中的第一个表中读取行，然后将每一行传递给一个嵌套循环，该循环处理联接中的下一个表。重复此过程的次数与要连接的表的次数相同。假定将使用以下联接类型

8.嵌套联接优化（JOIN）

表达联接的语法允许嵌套联接。以下讨论引用了“ JOIN子句”中描述的联接语法。table_factor与SQL标准相比，的语法得到了扩展。后者仅接受table_reference，而不接受一对括号内的列表。如果我们将table_reference项目列表中的每个逗号都视为等效于内部联接，则这是一个保

9.外部联接优化

外部联接包括LEFT JOIN和RIGHT JOIN。MySQL实现如下：A LEFT JOIN Bjoin_specification表B被设置为依赖于表A以及所依赖的所有表A。表格A被设置为取决于条件B中使用的所有表格（除外）LEFT JOIN。该LEFT JOIN条件用于确定如何从table中

10.外部联接简化

FROM在许多情况下，查询子句中的表表达式都得到了简化。在解析器阶段，具有右外部联接操作的查询将转换为仅包含左联接操作的等效查询。在一般情况下，执行转换时要进行以下右连接：(T1,…)RIGHTJOIN(T2,…)ON P(T1,…, T2,…)成为以下等效的左联接：(T2,…)

11.多范围读取优化

当表较大且未存储在存储引擎的高速缓存中时，在辅助索引上使用范围扫描来读取行会导致对表的许多随机磁盘访问。通过磁盘扫描多范围读取（MRR）优化，MySQL尝试通过首先仅扫描索引并收集相关行的键来减少用于范围扫描的随机磁盘访问次数。然后对键进行排序，最后使用主键的顺序从基表中检索行。磁盘扫描MRR的动机

12.块嵌套循环和批处理密钥访问联接

在MySQL中，可以使用批处理键访问（BKA）联接算法，该算法同时使用对联接表的索引访问和联接缓冲区。BKA算法支持内部联接，外部联接和半联接操作，包括嵌套的外部联接。BKA的好处包括由于更有效的表扫描而提高了连接性能。此外，以前仅用于内部联接的块嵌套循环（BNL）联接算法得到了扩展，可以用于外部联

13.条件过滤

在联接处理中，前缀行是从联接中的一个表传递到下一个表的那些行。通常，优化器会尝试在连接顺序的早期放置前缀计数较低的表，以防止行组合的数量迅速增加。在某种程度上，优化器可以使用有关从一个表中选择并传递到下一个表的行的条件的信息，它可以更准确地计算行估计并选择最佳执行计划。如果不使用条件过滤，则表的前缀

14.恒定折叠优化

现在，常量和列值之间的比较（其中常量值超出范围或相对于列类型的类型错误）现在在查询优化期间而不是在执行过程中逐行处理一次。可以以这种方式处理的比较是>，>=，<，<=，<>/!=，=和<=>。考虑以下语句创建的表：CREATETABLE t(c TIN

15.IS NULL优化

MySQL能够执行在相同的优化，它可以使用。例如，MySQL可以使用索引和范围来搜索 with 。col_nameIS NULLcol_name=constant_valueNULLIS NULL例子：SELECT*FROM tbl_name WHERE key_col IS NULL;SELECT

16.通过优化排序

本节描述了MySQL何时可以使用索引满足ORDER BY子句，无法使用索引时使用的filesort操作，以及有关优化程序的执行计划信息ORDER BY。一个ORDER BY有和没有LIMIT可能以不同的顺序返回行，在讨论第8.2.1.19，“LIMIT查询优化”。使用索引满足ORDER BY使用文件

17.优化分组（GROUP BY）

满足GROUP BY子句的最通用方法是扫描整个表并创建一个新的临时表，其中每个组中的所有行都是连续的，然后使用该临时表发现组并应用聚合函数（如果有）。在某些情况下，MySQL可以做得更好，并且可以避免使用索引访问来创建临时表。使用索引的最重要先决条件GROUP BY是所有GROUP BY列均引用同一

18.DISTINCT优化

DISTINCTORDER BY在许多情况下，结合需要一个临时表。因为DISTINCT可能使用GROUP BY，所以了解MySQL如何处理不属于所选列的ORDER BYor HAVING子句中的列。请参见“ GROUP BY的MySQL处理”。在大多数情况下，DISTINCT子句可以视为的特殊情况G

19.LIMIT查询优化

如果从结果集中只需要指定数量的行，则LIMIT在查询中使用子句，而不是获取整个结果集并丢弃多余的数据。MySQL有时会优化具有子句和无子句的查询：LIMIT row_countHAVING如果仅使用来选择几行LIMIT，则在通常情况下，MySQL倾向于使用全表扫描，因此在某些情况下会使用索引。如果与

20.函数调用优化

MySQL函数在内部被标记为确定性或不确定性。如果给定参数固定值的函数可以为不同的调用返回不同的结果，则它是不确定的。不确定函数的示例：RAND()，UUID()。如果某个函数被标记为不确定的，则将WHERE针对每一行（从一个表中选择时）或行的组合（从多表联接中选择时）评估子句中对该函数的引用。My

21.窗口函数优化

窗口函数会影响优化器考虑的策略：如果子查询具有窗口功能，则禁用子查询的派生表合并。子查询始终是物化的。半连接并不适用于窗口功能优化，因为半连接适用于子查询WHERE和JOIN … ON，这不能包含窗口函数。优化器按顺序处理多个具有相同排序要求的窗口，因此可以跳过对第一个窗口之后的窗口的排序。优化

22.行构造器表达式优化

行构造函数允许同时比较多个值。例如，以下两个语句在语义上是等效的：SELECTFROM t1 WHERE(column1,column2)=(1,1);SELECTFROM t1 WHERE column1 = 1 AND column2 = 1;另外，优化器以相同的方式处理两个表达式。如果行构

23.避免全表扫描

当MySQL使用全表扫描解决查询时，列中的输出EXPLAIN显示。这通常在以下情况下发生：ALLtype该表是如此之小，以至于执行表扫描要比打扰键查找要快得多。对于少于10行且行长较短的表，这是很常见的。索引列的ONor WHERE子句中没有可用的限制。您正在将索引列与常量值进行比较，并且MySQL

本章说明如何优化MySQL性能并提供示例。

优化涉及多个级别的配置，调整和测量性能。根据您的工作角色（开发人员，DBA或两者的组合），您可以在单个SQL语句，整个应用程序，单个数据库服务器或多个联网数据库服务器的级别进行优化。有时您可能会很主动，并提前计划性能，而其他时候，您可能会在问题发生后对配置或代码问题进行故障排除。优化CPU和内存使用率也可以提高可伸缩性，从而使数据库能够处理更多负载而不会降低速度。

数据库应用程序的核心逻辑是通过SQL语句执行的，无论是通过解释器直接发出还是通过API在后台提交。本节中的调优指南有助于加快各种MySQL应用程序的速度。该准则涵盖了读写数据的SQL操作，一般SQL操作的幕后开销以及在特定方案（例如数据库监视）中使用的操作。

查询以SELECT语句的形式执行数据库中的所有查找操作。无论是实现动态网页的亚秒级响应时间，还是缩短时间以生成大量的隔夜报告，调整这些语句都是当务之急。

此外SELECT语句，进行查询调谐技术也适用于结构，如CREATE TABLE...AS SELECT，INSERT INTO...SELECT和WHERE在条款DELETE的语句。这些语句还有其他性能方面的考虑，因为它们将写操作与面向读取的查询操作结合在一起。

NDB Cluster支持联接下推优化，从而将符合条件的联接完整地发送到NDB Cluster数据节点，在该节点之间可以将其分布并并行执行。有关此优化的更多信息，请参见 NDB下推连接的条件。

优化查询的主要考虑因素是：

为了使慢速SELECT ... WHERE查询更快，首先要检查的是是否可以添加索引。在WHERE子句中使用的列上设置索引，以加快评估，过滤和最终检索结果的速度。为避免浪费磁盘空间，请构造一小组索引，以加快应用程序中使用的许多相关查询的速度。

对于使用联接和外键之类的功能引用不同表的查询，索引尤其重要。您可以使用该EXPLAIN语句来确定用于的索引SELECT。请参见“ MySQL如何使用索引”和“使用EXPLAIN优化查询”。

隔离和调整查询的任何部分，例如函数调用，这些过程会花费大量时间。根据查询的结构，可以对结果集中的每一行调用一次函数，甚至可以对表中的每一行调用一次函数，从而极大地提高了效率。

最小化查询中全表扫描的次数，尤其是对于大表。

通过ANALYZE TABLE定期使用该语句来使表统计信息保持最新，因此优化器具有构造有效执行计划所需的信息。

了解特定于每个表的存储引擎的调整技术，索引技术和配置参数。双方InnoDB并MyISAM有两套准则的实现和维持查询高性能。有关详细信息，请参见“优化InnoDB查询”和“优化MyISAM查询”。

您可以InnoDB使用“优化InnoDB只读事务”中的技术优化表的单查询事务。

避免以难以理解的方式转换查询，尤其是在优化程序自动执行某些相同转换的情况下。

如果使用基本准则之一不能轻松解决性能问题，请通过阅读EXPLAIN计划并调整索引，WHERE子句，连接子句等来调查特定查询的内部详细信息。（当您达到一定的专业水平时，阅读EXPLAIN计划可能是每个查询的第一步。）

调整MySQL用于缓存的内存区域的大小和属性。通过有效地使用InnoDB缓冲池，MyISAM键高速缓存和MySQL查询高速缓存，重复查询的运行速度更快，因为第二次及其后的结果是从内存中检索的。

即使对于使用缓存区域快速运行的查询，您也可能会进一步优化，以使它们需要更少的缓存，从而使您的应用程序更具可伸缩性。可伸缩性意味着您的应用程序可以处理更多的并发用户，更大的请求等，而不会导致性能大幅下降。

处理锁定问题，其中其他会话同时访问表可能会影响查询速度。

结语

如果这篇文章对您有所帮助，或者有所启发的话，求一键三连：点赞、评论、收藏➕关注，您的支持是我坚持写作最大的动力。