高性能Mysql笔记-4查询性能优化

查询性能优化

本章将从查询设计的一些基本原则开始这也是在发现查询效率不高的时候首先需要考虑的因素。然后会介绍一些更深的查询优化的技巧，并会介绍一些** MySOL 优化器内部的机制**。我们将展示 MySQL 是如何执行查询的，你也将学会如何去改变一个查询的执行计划。最后，我们要看一下 MySOL 优化器在哪些方面做得还不够，并探索查询优化的模式，以帮助 MySQL 更有效地执行查询。

为啥查询速度慢

查询分为多个阶段，由多个子任务构成，每个都会有一些时间消耗，加起来就是总的响应时间。优化查询，就是优化查询子任务，要么加快它们的速度，要么减少它们的个数。
通常来说，查询的生命周期大致可以按照顺序来看 :从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中**“执行”可以认为是整个生命周期中最重要的阶段**，这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。
上列仅为概述，描述并不完整。

慢查询基础-优化数据访问

对于低效的查询，可以通过以下两步骤分析：

1. 确认应用程序是否在检索大量超过需要的数据。
2. 确认 MySOL 服务器层是否在分析大量超过需要的数据行。

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

查询过多的资源会带来额外的负担，增加资源的消耗。
以下有几个错误案例：

• 查询不必要的记录

不必要的查询大量的数据，不进行分页操作。最好是加上**limit**操作，限制返回的数据大小。

• 多表关联时返回全部列

多表查询时不要使用**select * ~**，而是需要什么数据返回什么数据，避免列的数量爆炸增长。

• 总是取出全部列

慎用**select * ~**，因为大部分的数据可能不是我们需要的。同时取出所有的列也无法使用覆盖索引，导致查询数据一定会回表查询。不过开发时使用也可以简化开发，一次获取多次使用，减少与数据库的I/O操作，需要在在代价和好处之间权衡。

• 重复查询相同的数据

不断查询一样的数据，这样会浪费很多资源，建议使用缓存技术进行缓存，避免过多的网络查询。

MySQL是否在扫描额外的记录

对于MySQL，最简单的衡量查询开销的三个指标如下:

1. 响应时间
2. 扫描的行数
3. 返回的行数

这三个指标可以大致反映查询的数据量大小。可以在慢日志中查询这些信息。

• 响应时间

响应时间是两个部分之:服务时间和排队时间。服务时间是指数据库处理这个查询真正花了多长时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间———可能是等I/0 操作完成，也可能是等待行锁，等等。一般最常见和重要的等待是 IO 和锁等待，但是实际情况更加复杂。

• 扫描的行数和返回的行数

理想情况下扫描的行数和返回的行数应该是相同的。但实际情况中这种“美事”并不多，例如在做一个关联查询时，服务器必须要扫描多行才能生成结果集中的一行。扫描的行数对返回的行数的比率通常很小，一般在 1:1 和10:1 之间，不过有时候这个值也可能非常非常大。

• 扫描的行数和访问类型

在EXPLAIN语句中的 type 列反应了访问类型。访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。这里列的这些，速度是从慢到快，扫描的行数也是从小到大。
一般MySQL 能够使用如下三种方式应用 WHERE 条件，从好到坏依次为 :

1. 在索引中使用 WHERE 条件来过滤不匹配的记录。这是在存储引擎层完成的。
2. 使用索引覆盖扫描 (在Extra列中出现了 Using index) 来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在 MySQL 服务器层完成的，但无须再回表查询记录。
3. 从数据表中返回数据，然后过滤不满足条件的记录(在 Extra列中出现 UsingWhere)。这在 MySQL 服务器层完成，MySQL 需要先从数据表读出记录然后过滤。

优化技巧：

1. 使用覆盖索引，避免回表操作。
2. 改变库的结构。例如使用汇总表。
3. 重写查询SQL，让mysql能更好的执行。

重构查询

有时可以将查询转换一种写法让其返回一样的结果，但性能更好。

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

MySOL 从设计上让连接和断开连接都很轻量级在返回一个小的查询结果方面很高效。现代的网络速度比以前要快很多，无论是带宽还是延迟。在某些版本的MySQL 上，即使在一个通用服务器上，也能够运行每秒超过 10万的查询，即使是一个千兆网卡也能轻松满足每秒超过 2000 次的查询。
所以如果在合适的情况下，可以将一个复杂的查询分解为多个简单查询，来加快速度。

切分查询

有时候对于一个大查询我们需要“分而治之”，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。
删除旧的数据就是一个很好的例子。定期地清除大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的 DELETE语句切分成多个较小的查询可以尽可能小地影响到整个系统。

分解关联查询

可以将一条多表关联查询拆分为多条简单查询，他有以下优势：

• 让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。
• 将查询分解后，执行单个查询可以减少锁的竞争。
• 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展，可以做到分库分表，缓解单库压力。
• 查询本身效率也可能会有所提升。这个例子中，使用 IN()代替关联查询，可以让MySQL 按照ID 顺序进行查询，这可能比随机的关联要更高效。我们后续将详细介绍这点。
• 可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次,而在数据库中做关联查询,则可能需要重复地访问一部分数据。从这点看这样的重构还可能会减少网络和内存的消耗。
• 更进一步，这样做相当于在应用中实现了哈希关联，而不是使用 MySQL 的嵌套循环关联。某些场景哈希关联的效率要高很多。

查询执行的过程基础

下面是向mysql发送一个请求，mysql内部的执行逻辑图。

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

上面的每一步都比想象的复杂。我们会看到在每一人阶段查询处于何种状态。查询优化器是其中特别复杂也特别难理解的部分。还有很多的例外情况，例如，当查询使用绑定变量后，执行路径会有所不同，我们将在下一章讨论这点

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

MySQL 客户端和服务器之间的通信协议是“半双工”的，这意味着在任何一个时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。所以，我们无法也无须将一个消息切成小块独立来发送。
这种协议让 MySQL 通信简单快速，但是也从很多地方限制了 MySQL。一个明显的限制是，这意味着没法进行流量控制。

查询状态

对于一个 MySQL连接，或者说一个线程，任何时刻都有一个状态，该状态表示了MySOL 当前正在做什么。有很多种方式能查看当前的状态，最简单的是使用 SHOW FULLPROCESSLIST命(该命令返回结果中的 Command列就表示当前的状态)。下面将这些状态列出来，并做一个简单的解释。

• 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 会检查一次用户权限。这仍然是无须解析查询 SQL 语句的，因为在查询缓存中已经存放了当前查询需要访问的表信息。如果权限没有问题，MySQL会跳过所有其他阶段，直接从缓存中拿到结果并返回给客户端。
但是同时也会带来一些额外消耗：

• 读查询在开始之前必须先检查是否命中缓存。
• 如果这个读查询可以被缓存，那么当完成执行后，MySQL 若发现查询缓存中没有这个查询，会将其结果存人查询缓存，这会带来额外的系统消耗。
• 这对写操作也会有影响，因为当向某个表写人数据的时候，MySQL 必须将对应表的所有缓存都设置失效。如果查询缓存非常大或者碎片很多，这个操作就可能会带来很大系统消耗(设置了很多的内存给查询缓在用的时候)。

查询优化处理

查询的生命周期的下一步是将一个** SQL 转换成一个执行计划**，MySQL再依照这个执行计划和存储引擎进行交互。这包括多个子阶段:**解析 SOL、预处理、优化 SOL 执行计划。**这个过程中任何错误 (例如语法错误)都可能终止查询。

语法解析器和预处理

语法解析器会将SQL语句进行解析，生成解析树。MySQL解析器将使用MySQL语法规则验证和解析查询。 十检查是否存在错误的关键字，例如select 、update等
预处理器则根据一些MySQL规则进一步检查解析树是否合法，例如，这里将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义。
下一步预处理器会验证权限。这通常很快，除非服务器上有非常多的权限配置

查询优化器

经过前面的解析和预处理，sql已经被认为是合法的了，并在此步骤优化器转为执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
有多种原因可以是优化器选择错误的执行计划：

• 统计信息不准确。MySQL 依赖存储引提供的统计信息来评估成本，但是有的存储引擎提供的信息是准确的，有的偏差可能非常大。例如，InnoDB 因为其MVCC的架构，并不能维护一个数据表的行数的精确统计信息。
• 执行计划中的成本估算不等同于实际执行的成本。所以即使统计信息精准，优化器给出的执行计划也可能不是最优的。可能是因为内存和顺序I/O的原因。
• MySQL的最优可能和你想的最优不一样。你可能希望执行时间尽可能的短，但是MySQL 只是基于其成本模型选择最优的执行计划，而有些时候这并不是最快的执行方式。
• MySQL从不考虑其他并发执行的查询，这可能会影响到当前查询的速度。
• MySQL也并不是任何时候都是基于成本的优化。有时也会基于一些固定的规则，例如，如果存在全文搜索的 MATCH() 子句，则在存在全文索引的时候就使用全文索引.即使有时候使用别的索引和 WHERE条可以远比这种方式要快，MySQL 也仍然会使用对应的全文索引。

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

1. 重新定义关联表的顺序

数据表的关联并不总是按照在查询中指定的顺序进行。决定关联的顺序是优化器很重要的。

2. 将外连接转化成内连接

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

3. 使用等价变换规则

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

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

MySQL可为优化索引和列是否空这类表达式。例如，要找到某一列的最小值，只需要查询对应 B-Tree 索引最左端的记录，MySQL 可以直接获取索引的第一行记录。在优化器生成执行计划的时候就可以利用这一点,在 B-Tree 索引中优化器会将这个表达式作为一个常数对待。如果 MySQL 使用了这种类型的优化，那么在EXPLAIN中就可以看到“Select tables optimized away”。

5. 预估并转化为常数表达式

当 MySQL 检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理。例如，一个用户自定义变量在查询中没有发生变化时就可以转换为一个常数。在优化阶段，有时候甚至一个查询也能够转化为一个常数。一个例子是在索引列上执行 MIN() 函数。其至是主键或者唯一键查找语句也可以转换为常数表达式。

6. 覆盖索引扫描

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

7. 子查询优化

覆盖索引扫描
当索引中的列包含所有查询中需要使用的列的时候，MySQL就可以使用索引返回需要的数据，而无须查询对应的数据行，在前面的章节中我们已经讨论过这点了。子查询优化
MySQL 在某些情况下可以将子查询转换一种效率更高的形式，从而减少多个查询多
次对数据进行访问

8. 提前终止查询

在发现已经满足查询需求的时候，MySQL 总是能够立刻终止查询。一个典型的例子就是当使用了 LIMIT子句的时候。除此之外，MySQL 还有几类情况也会提前终止查询，例如发现了一个不成立的条件，这时 MySOL 可以立刻返回一个空结果。从下面的例子可以看到这一点 : 在某些情况下可以将子查询转换一种效率更高的形式，从而减少多个查询多次对数据进行访问。

9. 提前终止查询

在发现已经满足查询需求的时候，MySQL 总是能够立刻终止查询。一个典型的例子就是当使用了 LIMIT子句的时候。除此之外，MySQL 还有几类情况也会提前终止查询，例如发现了一个不成立的条件，这时 MySQL 可以立刻返回一个空结果。

10. 等值传播

如果两个列的值通过等式关联，那么 MySQL能够把其中一个列的 WHERE条件传递到另一列上。例如，我们看下面的查询。

11. 列表 IN() 的比较

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

以上未列举完全所有的优化项。

数据和索引

因为服务器层没有任何统计信息，所以** MySQL 查询优化器在生成查询的执行计划时需要向存储引擎获取相应的统计信息**。存储引擎则提供给优化器对应的统计信息，包括每个表或者索引有多少个页面、每个表的每个索引的基数是多少、数据行和索引长度索引的分布信息等。优化器根据这些信息来选择一个最优的执行计划。在后面的小节中我们将看到统计信息是如何影响优化器的

MySQL 如何执行关联查询

MySQL中“关联”一词所包含的意义比一般意义上理解的要更广泛。总的来说，MySQL 认为任何一个查询都是一次“关联”一一并不仅仅是一个询需要到两个表匹配才叫关联，所以在 MySQL中，每一个查询，每一个片段 (包括子查询，甚至基于单表的 SELECT) 都可能是关联
MySQL 关联执行的策略很简单 : MySQL 对关联都执行套循环关联操作，即MySQL 先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行依次下去，直到找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。MySQL会尝试在最后一个关联表中找到所有匹配的行，如果最后一个关联表无法找到更多的行以后，MySQL 返回到上一层次关联表，看是否能够找到更多的匹配记录，依此类推迭代执行。

从本质上说，MySOL 对所有的类型的查询都以同样的方式运行。例如，MySOL在 FROM子句中遇到子查询时，先执行子查询并将其结果放到一个临时表中，然后将这个临时表当作一个普通表对待 (正如其名“派生表”)。

执行计划

和很多其他关系数据库不同，MySOL 并不会生成查询字节码来执行查询。MySOL 生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。如果对某个查询执行 EXPLAIN EXTENDED后，再执行 SHOW WARNINGS，就可以看到重构出的查询。

如我们前面章节介绍的,MySQL 总是从一个表开始**一直嵌套循环、回湖完成所有表关联，**所以，MySOL 的执行计划总是如图 6-4 所示，是一棵左测深度优先的树。

关联查询优化器

MySQL 优化器最重要的一部分就是关联查询优化，它决定了多个表关联时的顺序。通常多表关联的时候，可以有多种不同的关联顺序来获得相同的执行结果。关联查询优化器则通过评估不同顺序时的成本来选择一个代价最小的关联顺序。
优化器给出的并不是最优的关联顺序。这时可以使用 STRAIGHT JOIN关键字重写查询，让优化器按照你认为的最优的关联顺序执行一一不过老实说，人的判断很难那么精准。绝大多数时候，优化器做出的选择都比普通人的判断要更准确。
超过n个表的的关联需要检查n的阶乘种关联顺序，非常消耗性能，所以尽量减少表关联的数量。
有时，各个查询的顺序并不能随意安排，这时关联优化器可以根据这些规则大大减少搜索空间，例如，左连接、相关子查询。这是因为，后面的表的查询需要依赖于前面表的查询结果。这种依赖关系通常可以帮助优化器大大减少需要扫描的执行计划数量。

排序优化

排序是一个成本很高的操作，尽量应该避免排序。
最好的使用索引进行排序，当不能索引排序的时候，MySQL需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘，不过MySOL 将这个过程统一称为文件排序 (lesort)，即使完全是存排序不需要任何磁盘文件时也是如此。
如果需要排序的数据量小于“排序缓冲区”，MySQL 使用内存进行“快速排序”操作。如果内存不够排序，那么 MySOL 会先将数据分块，对每个独立的块使用“快速排字”进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排好序的块进行合并merge)，最后返回排序结果
MySOL 有如下两种排序算法 :

• 两次传输排序 (旧版本使用)

读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。需要两次次数据传输，多次随机I/O。

• 单次传输排序 (新版本使用

先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果。需要一次数据传输，一次随机I/O。
排序使用的临时空间必须容纳其中最长的字符串，所以在设计表的时候对字符的存储一定要仔细斟酌。
MySOL 5.6 在这里做了很多重要的改进。当只需要返回部分排序结果的时候，例如使用了LIMIT子句，MySQL 不再对所有的结果进行排序，而是根据实际情况，选择抛弃不满足条件的结果，然后再进行排序。

查询执行引擎

在解析和优化阶段，MySQL 将生成查询对应的执行计划，MySQL 的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构，而不是和很多其他的关系型数据库那样会生成对应的字节码。
底层的handler api接口大概只有几十个，但是通过叠加的方式能完成大部分查询工作。

返回结果给客户端

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

MySQL查询优化器的局限性

MySQL的万能“嵌套循环”并不是对每种查询都是最优的。不过还好，MySQL 查询优化器只对少部分查询不适用，而且我们往往可以通过改写查询让 MySQL 高效地完成工作。
以下将是对mysql的查询优化选择方案。

关联子查询

MySQL 的子查询实现得非常糟糕。最糟糕的一类查询是 WHERE条件中包含 IN()的子查询语句。可能会导致全表扫描。

一个优化的办法是使用函数 GROUP CONCAT()在 IN()中构造一个由号分隔的列表有时这比上面的使用关联改写更快。因为使用 IN() 加子查询，性能经常会非常糟，所以通常建议使用 EXISTS() 等效的改写询来获取更好的效率。下面是另一种改写 IN()加字查询的办法。


mysql在5.6版本加以了改进。

如何用好关联子查询

子查询不一定性能都很差，在某些时候，它的性能可能会优于使用过多的sql函数。

UNION的限制

如果希望** UNION的各个子句能够根据 LIMIT 只取部分结果集，或者希望能够先排好序再合并结果集的话，就需要在 UNION的各个子句中分别使用这些子句**。例如，想将两个子查询结果联合起来，然后再取前 20 条记录，那么MySOL 会将两个表都存放到同一个临时表中，然后再取出前 20 行记录:

当然每个子查询也可以加上limit限制返回的数据大小，同时也可以在全局加上order by来排序。类似下图：

索引合并优化

当where条件中包含多个复杂列需要查询时，可以通过复合索引和冗余索引进行过滤筛选，减少返回的数据行数。

等值传递

某些特殊情况时候，等值传递会带来一些意想不到的额外消耗。例如，有一个非常大的 IN()列表而MySQL优化器发现存在 WHERE、ON或者USING的子句，将这个列表的值和另一个表的某人列相关联，那么优化器会将 IN()列表都复制应用到关联的各个表中。一般条件因为索引等情况，查询也是很快的，但是如果表非常大的情况下，也会造成查询速度变慢。

并行执行

MySQL 无法利用多核特性来并行执行查询。很多其他的关系型数据库能够提供这个特性，但是 MySQL做不到。
不要白费力气去寻找这个功能呢。

哈希关联

在本书写作的时候（5.5之前，包括5.5），MySQL 并不支持哈希关联，MySQL的所有关联都是嵌套循环关联。不过，可以通过建立一个哈希索引来曲线地实现哈希关联。

松散哈希索引

由于历史原因，MySQL 并不支持松散索引扫描，也就无法按照不连续的方式扫描一个索引。通常，MySQL的索引扫描需要先定义一个起点和终点，即使需要的数据只是这段索引中很少数的几个，MySQL 仍需要扫描这段索引中每一个条目。
索引有（a，b）。下面这条sql无法使用索引，违背了最左匹配原则。


了解索引的物理结构的话，不难发现还可以有一个更快的办法执行上面的查询。索引的物理结构(不是存储引擎的 API) 使得可以先扫描 a列第一个值对应的 b列的范围，然后再跳到 a列第二个不同值扫描对应的 b列的范围。图6-6 展示了如果由 MySQL 来实现这个过程会怎样

MySQL 5.0之后的版本，在某些特殊的场景下是可以使用松散索引扫描的，例如，在一个分组查询中需要找到分组的最大值和最小值 :

想要使用索引加快查询的话，可以使用索引优化的方法，或者使用**in()**函数➕常量来匹配索引的规则。

最大值和最小值的优化

对于 MIN()和 MAX()查询，MySOL的优化做得并不好。如果查询的一个字段上没有索引，例如

那么这条语句就会扫描全表，查询最小的id。一种取巧的优化办法是，使用主键，加上where语句条件**limit 1**，就可以通过主键的有序自增取出第一条id最小数据。

在同一个表上查询和更新

MySQL不允许对同一张表同时进行查询和更新。但是这不是优化器的限制，可以通过另外一种方式避免。

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

查询优化器的提示（hint）

如果对优化器选择的执行计划不满意，可以使用优化器提供的几个提示(hint)来控制最终的执行计划。下面将列举一些常见的提示，并简单地给出什么时候使用该提示。

• HIGH PRIORITY 和 LOW PRIORITY

这个提示告诉 MySQL，当多个语句同时访问某一个表的时候，哪些语句的优先级相对高些、哪些语句的优先级相对低些。提高语句在表锁中的优先级，名如其意。
这两个提示只对使用表锁的存储引擎有效，千万不要在 ImnoDB 或者其他有细粒度锁机制和并发控制的引擎中使用。即使是在 MyISAM 中使用也要注意，因为这两个提示会导致并发插入被禁用，可能会严重降低性能。

• DELAYED

这个提示对 INSERT和 REPLACE有效。MySQL会将使用该提示的语立即返回给客户端，并将插人的行数据放入到缓冲区，然后在表空闲时批量将数据写人。日志系统使用这样的提示非常有效，或者是其他需要写人大量数据但是客户端却不需要等待单条语句完成I/0 的应用。这个用法有一些限制：并不是所有的存储引擎都支持这样的做法，并且该提示会导致函数 LAST INSERT ID() 无法正常工作。

• STRAIGHT JOIN

这个提示可以放置在 SELECT语的 SELECT关键字之后，也可以放置在任何两个关联表的名字之间。**第一个用法是让查询中所有的表按照在语句中出现的顺序进行关联。第二个用法则是固定其前后两个表的关联顺序。当MySOL 没能选择正确的关联顺序的时候，或者由于可能的顺序太多导致 MySQI无法评估所有的关联顺序的时候，STRAIGHT JOIN都会很有用。**在后面这种情况MySQL 可能会花费大量时间在“statistics”状态，加上这个提示则会大大减少优化器的搜索空间。

• SQL SMALL RESULT和 SQL BIG RESULT

这两个提示只对 SELECT语句有效。它们告诉优化器对 GROUP BY或者 DISTINCT查询如何使用临时表及排序。SQL SMALL RESULT告诉优化器结果集会很小，可以将结果集放在内存中的索引临时表，以避免排序操作。如果是 SQL BIG RESULT，则告诉优化器结果集可能会非常大，建议使用磁盘临时表做排序操作。

• SOL BUFFER RESUL

这个提示告诉优化器将查询结果放人到一个临时表，然后尽可能快地释放表锁。这和前面提到的由客户端缓存结果不同。当你没法使用客户端缓存的时候，使用服务器端的缓存通常很有效。

• SQL CACHE和 SQL NO CACHE

这个提示告诉 MySQL 这个结果集是否应该缓存在查询缓存中。

• SQL CALC FOUND ROWS

严格来说，这并不是一个优化器提示。它不会告诉优化器任何关于执行计划的东西它会让 MySQL 返回的结果集包含更多的信息。查询中加上该提示 MySQL 会计算除去 LIMIT子句后这个查询要返回的结果集的总数，而实际上只返回 LIMIT要求的结果集。可以通过函数 FOUND ROW() 获得这个值。

• FOR UPDATE和 LOCK IN SHARE MODE

这也不是真正的优化器提示。这两个提示主要控制 SELECT语句的锁机制，但只对实现了行级锁的在储引擎有效。使用该提示会对符合查询条件的数据行加锁。对于INSERT...SELECT语句是不需要这两个提示的，因为对于MySOL 5.0和更新版本会默认给这些记录加上读锁。

• USE INDEX、IGNORE INDEX FORCE INDEX

**这几个提示会告诉优化器使用或者不使用哪些索引来查询记录。**在 MySQL 5.1 和之后的版本可以通过新增选项 FOR ORDER BY和 FOR GROUP BY来指定是否对排序和分组有效。FORCE INDEX和USE INDEX基本相同，除了一点:FORCE INDEX会告诉优化器全表扫描的成本会远远高于索引扫描，哪怕实际上该索引用处不大。当发现优化器选择了错误的索引，或者因为某些原因 (比如在不使用 ORDER BY的时候希望结果有序)要使用另一个索引时，可以使用该提示。在前面关于如何使用 LIMIT高效地获取最小值的案例中，已经演示过这种用法

• optimizer search depth

这个参数控制优化器在穷举执行计划时的限度。如果查询长时间处于“Statistics状态，那么可以考虑调低此参数。

• optimizer prune level

该参数默认是打开的，这让优化器会根据需要扫描的行数来决定是否跳过某些执行计划。

• optimizer switch

这个变量包含了一些开启/关闭优化器特性的标志位。例如在 MySQL 5.1中可以通过这个参数来控制禁用索引合并的特性。

优化特定的查询

优化count()查询

COUNT()聚合函数，以及如何优化使用了该函数的查询，很可能是 MySQL 中最容易被误解的前 10 个话题之一。

count()作用

COUNT()是一个特殊的函数，有两种非常不同的作用: 它可以统计某个列值的数量，也可以统计行数。在统计列值时要求列值是非空的(不统计 NULL)。如果在 COUNT() 的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数。
COUNT()的另一个作用是统计结果集的行数。当 MySQL 确认括号内的表达式值不可能为空时，实际上就是在统计行数。最简单的就是当我们使用 COUNT(*) 的时候，它会忽略所有的列而直接统计所有的行数。
我们发现一个最常见的错误就是，在括号内指定了一个列却希望统计结果集的行数。如果希望知道的是结果集的行数，最好使用** COUNT(*)**，这样写意义清晰，性能也会很好

简单优化

在查询同一个列不同值的数量，减少查询的语句量，不使用group by函数的情况下，也可以使用 COUNT()而实现同样的目的，只需要将满足条件设置为真，不满足条件设置为 NULL即可，注意加上**or null**:

使用近似值

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

更复杂的查询

通常来说，COUNT()都需要扫描大量的行(意味着要访问大量数据)才能获得精确的结果，因此是很难优化的。除了前面的方法，在 MySQL 层面还能做的就只有索引覆盖扫描了。如果这还不够,就需要考虑修改应用的架构,可以增加汇总表。或者增加类似redis这样的外部缓存系统。可能很快你就会发现陷人到一个熟悉的困境，“快速，精确和实现简单”，三者永远只能满足其二，必须舍掉其中一个。

优化关联查询

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

优化子查询

关于子查询优化我们给出的最重要的优化建议就是尽可能使用关联查询代替，至少当前的MySQL版本需要这样。

优化 GROUP BY 和 DISTINCT

在很多场景下，MySQL都使用同样的办法优化这两种查询，事实上，MySQL优化器会在内部处理的时候相互转化这两类查询。它们都可以使用索引来优化，这也是最有效的优化办法。
在MySOL中，当无法使用索引的时候，GROUP BY使用两种策略来完成：使用临时表或者文件排序来做分组。对于任何查询语句，这两种策略的性能都有可以提升的地方。可以通过使用提示SOL BIG RESULT和SOL SMALL RESULT提示来让优化器按照你希望的方式运行。在本章的前面章节我们已经讨论了这点。
如果没有通过 ORDER BY子显式地指定排序列，当查询使用 GROUP BY子的时候，结果集会自动按照分组的字段进行排序。如果不关心结果集的顺序，而这种默认排序又导致了需要文件排序，则可以使用 **ORDER BY NULL**，让MySQL不再进行文件排序。也可以在GROUP BY子句中直接使用DESC或者ASC关键字,使分组的结果集按需要的方向排序

• 优化GROUP BY WITH ROLLUP

分组查询的一个变种就是要求 MySQL 对返回的分组结果再做一次超级聚合。可以使用**WITH ROLLUP**子句来实现这种逻辑，但可能会不够优化。可以通过 EXPLAIN来观察其执行计划，特别要注意分组是否是通过文件排序或者临时表实现的。然后再去掉 WITH ROLLUP子句看执行计划是否相同。也可以通过本节前面介绍的优化器提示来固定执行计划。最好的办法还是转移到应用端处理聚合后的数据。

优化limit分页

在系统中需要进行分页操作的时候，我们通常会使用** LIMIT加上偏移量的办法实现，同时加上合适的 ORDER BY子句，如果有对应的索引，通常效率会不错，否则，MySQL需要做大量的文件排序操作。
当分页数量非常多的时候，例如limit 100000，100，非常浪费性能，优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列**。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候，这样做的效率会提升非常大。考虑下面的查询 :

这里的“延迟关联”将大大提升查询效率，**它让 MySQL 扫描尽可能少的页面，获取需要访句的记录后再根据关联列回原表查询需要的所有列。**这个技术也可以用于优化关联查询中的 LIMIT子句。
如果知道具体位置，也可以直接加上范围查询，例如加上**>或者between**直接缩小范围。

如果要查最新的记录数（要求id单调自增），可以使用倒序查找，可以避免过深的的分页。

优化 SQL CALC FOUND ROWS

分页的时候,另一个常用的技巧是在LIMIT语中加上SOL CALC FOUND ROWS提示(hint)这样就可以获得去掉 LIMIT以后满足条件的行数，因此可以作为分页的总数，加上这个提示以后不管是否需要，MySQL都会扫描所有满足条件的行，然后再抛弃掉不需要的行，而不是在满足 LIMIT的行数后就终止扫描。所以该提示的代价可能非常高。

要么加设计，例如返回二十一条显示二十条，然后判断返回数量是否=21来显示下一页。或者上缓存。

优化 UNION 查询

MySQL 总是通过创建并填充临时表的方式来执行UNION查询。因此很多优化策略在UNION查询中都没法很好地使用。经常需要手工地将 WHERE、LIMIT、ORDER BY等子句“下推”到UNION的各个子查询中，以便优化器可以充分利用这些条件进行优化** (例如，直接将这些子句冗余地写一份到各个子查询)。**
除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL，这一点很重要。**如果没有 ALL关键字，MySOL 会给临时表加上 DISTINCT 选项，这会导致对整个临时表的数据做唯一性检查。**这样做的代价非常高。即使有 ALL关键字，MySOL仍然会使用临时表存储结果。

使用用户自定义变量

用户自定义变量是一个容易被遗忘的 MySQL特性，但是如果能够用好，发挥其潜力在某些场景可以写出非常高效的查询语句。在查询中混合使用过程化和关系化逻辑的时候，自定义变量可能会非常有用。

应用中难以使用，不推荐，了解为主：

• 使用自定义变量的查询，无法使用查询缓存
• 不能在使用常量或者标识符的地方使用自定义变量,例如表名、列名和 LIMIT 子句中
• 用户自定义变量的生命周期是在一个连接中有效，所以不能用它们来做连接间的通信。
• 如果使用连接池或者持久化连接，自定义变量可能让看起来毫无关系的代码发生交互。
• 不能显式地声明自定义变量的类型。
• MySQL 优化器在某些场景下可能会将这些变量优化掉，这可能导致代码不按预想的方式运行。
• 赋值的顺序和赋值的时间点并不总是固定的，这依赖于优化器的决定。实际情况可能很让人困惑，后面我们将看到这一点。
• 赋值符号 :=的优先级非常低，所以需要注意，赋值表达式应该使用明确的括号。
• 使用未定义变量不会产生任何语法错误，如果没有意识到这一点，非常容易犯错

不过，我们需要使用一些技巧来获得我们希望的结果。有时，优化器会把变量当作一个编译时常量来对待，而不是对其进行赋值。**将函数放在类似于 ****LEAST() **这样的函数中通常可以避免这样的问题。另一个办法是在查询被执行前检查变量是否被赋值。不同的场景下使用不同的办法

案例

通常实际遇到的问题会更麻烦，但还是需要学习。

使用MySQL构建一个队列表

使用MySQL 来实现队列表是一个取巧的做法，我们看到很多系统在高流量、高并发的情况下表现并不好。**典型的模式是一个表包含多种类型的记录:未处理记录、已处理记录正在处理记录等。一个或者多个消费者线程在表中查找未处理的记录，然后声称正在处理，当处理完成后，再将记录更新成已处理状态。**一般的，例如邮件发送、多命令处理评论修改等会使用类似模式
这里我们将总结一下这个案例中的一些基础原则：

• 尽量少做事，可以的话就不要做任何事情。除非不得已，否则不要使用轮询，因为这会增加负载，而且还会带来很多低产出的工作。
• 尽可能快地完成需要做的事情。尽量使用 UPDATE代替先 SELECT FOR UPDATE再UPDATE的写法，因为事务提交的速度越快，持有的锁时间就越短，可以大大减少竞争和加速串行执行效率。将已经处理完成和未处理的数据分开，保证数据集足够小。
• 某些查询是无法优化的，考虑使用不同的策略去实现。

有时，最好的办法就是将任务队列从数据库中迁移出来。Redis 就是一个很好的队列容器。

计算两点之间的距离

地理信息计算再次出现在我们的书中了。不建议用户使用 MySQL 做太复杂的空间信息存储–PostgreSQL在这方面是不错的选择，我们这里将介绍一些常用的计算模式一个典型的例子是计算以某个点为中心，一定半径内的所有点。
有如下表

这里经度和纬度的单位是“度”,通常我们假设地球是圆的,然后使用两点所在最大圆 (半正矢)公式来计算两点之间的距离。现在有标latA和lonA、latB和lonB，那么点A和点B的距离计算公式如下：

计算出的结果是一个弧度，如果要将结果的单位转换成英里或者千米，则需要乘以地球的半径，也就是3 959 英里或者 6 371 千米。假设我们需要找出所有距离 Baron 所居的地方 Charlottesville 100 英里以内的点，那么我们需要将经纬度带入上面的计算公式:

使用用户自定义函数

当 SOL 语句已经无法高效地完成某些任务的时候，这里我们将介绍最后一个高级的优化技巧。当你需要更快的速度，那么 C和C++是很好的选择。当然，你需要一定的 C或C++ 编程技巧,否则你写的程序很可能会让服务器崩溃。这和“能力越强,责任越大”类似

总结

如果把创建高性能应用程序比作是一个环环相扣的“难题”，除了前面介绍的** schema索引和查询语句设计之外，查询优化应该是解开“难题”的最后一步了。要想写一个好的查询，你必须要理解 schema 设计、索引设计等，反之亦然。
理解查询是如何被执行的以及时间都消耗在哪些地方**，这依然是前面我们介绍的响应时间的一部分。再加上一些诸如解析和优化过程的知识，就可以更进一步地理解上一章讨论的MysOL 如何访向表和索引的内容了。这也从另一个维度帮助读者理解MySOL在访问表和索引时查询和索引的关系
优化通常都需要三管齐下:不做、少做、快速地做。

如果这篇【文章】有帮助到你，希望可以给笔者点个赞👍，创作不易，感兴趣的也可以关注一下笔者，后续也会更新更多的内容，都会在后续慢慢更新。