【MySQL原理系列】- MySQL查询性能优化_use index(primary)-优快云博客

本文链接：https://blog.youkuaiyun.com/m0_68383079/article/details/145917123

【MySQL原理系列】- MySQL查询性能优化

文章目录

【MySQL原理系列】- MySQL查询性能优化

查询优化、索引优化、库表结构优化需要齐头并进，一个不落

一、查询的过程

所谓快速查询，真正重要是响应时间

它由一系列子任务组成，优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行得更快

通常查询的生命周期大致为：

从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端
其中“执行”是整个生命周期中最重要的阶段，这其中包括了大量为了到存储引擎检索数据的调用以及调用后的数据处理，包括排序、分组等
其他操作包括网络，CPU计算，生成统计信息和执行计划、锁等待，等
根据存储引擎不同，可能还会产生大量的上下文切换以及系统调用

二、慢查询基础：优化数据访问

查询性能低下最基本的原因是访问的数据太多

对于低效的查询，通过下面两个步骤来分析总是很有效：

确认应用程序是否在检索大量超过需要的数据，这通常意味着访问了太多行或太多列
确认MySQL服务器层是否在分析大量超过需要的数据行

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

有些查询会请求超过实际需要的数据，然后这些多余的数据会被应用程序丢弃。这会给MySQL服务器带来额外的负担，并增加网络开销，消耗应用服务器的CPU和内存资源

典型案例：

查询不需要的记录
- 使用SELECT语句查询大量的结果，然后获取前面的N行后关闭结果集
- 实际情况是MySQL会查询出全部的结果集，客户端的应用程序会接收全部的结果集数据，然后抛弃其中大部分数据
- 最简单有效的解决方法就是在这样的查询后面加上LIMIT
多表关联时返回全部列
总是取出全部列
- SELECT *会返回全部的列，很可能不是必需的
- 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的I/O、内存和CPU的消耗
- 但如果应用程序使用了某种缓存机制，获取超过需要的数据也可能有好处
重复查询相同的数据
- 比较好的方案是，初次查询时将可能重复查询的数据缓存起来，再需要时从缓存取

2.2 MySQL是否在扫描额外的记录

在确定查询只返回需要的数据后，那么查询为了返回结果是否扫描了过多的数据呢

**MySQL衡量查询开销的三个指标：**响应时间、扫描的行数、返回的行数

这三个指标都会记录到MySQL的慢日志中，检查慢日志记录是找出扫描行数过多的查询的好办法

响应时间
- 响应时间 = 服务时间 + 排队时间
- 服务时间指数据库处理这个查询真正花了多长时间
- 排队时间是指服务器因为等待某些资源的时间，可能是等I/O操作完成，也可能是等待行锁
- 难以判断不同应用下的响应时间是否合理
扫描的行数和返回的行数
- 两者比率在一定程度上能够说明该查询找到需要的数据的效率高不高
- 理想情况下扫描的行数和返回的行数应该是相同的
- 较短的行的访问速度更快，内存中的行也比磁盘中的行的访问速度要快得多
扫描的行数和访问类型
- MySQL有好几种访问方式可以查找并返回一行结果。有些访问方式可能需要扫描很多行才能返回一行结果，也有些访问方式可能无须扫描就能返回结果
- 访问类型有很多种，扫描表、扫描索引、范围访问和单值访问
- 有索引可用时访问类型是ref索引扫描，否则就是ALL全表扫描
- 一般MySQL能够使用三种方式应用WHERE 条件，从好到坏依次为：
  - 在索引中使用WHERE 条件来过滤不匹配的记录。这是在存储引擎层完成的
  - 使用索引覆盖扫描（在Extra 列中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的，但无须再回表查询记录
  - 从数据表中返回数据，然后过滤不满足条件的记录（在Extra 列中出现Using Where）。这在MySQL服务器层完成，MySQL需要先从数据表读出记录然后过滤

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

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

三、重构查询的方式

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

在传统实现中，总是强调需要数据库层完成尽可能多的工作

因为以前网络通信、查询解析和优化是一件代价很高的事情
但对于MySQL并不适用，MySQL连接和断开连接都很轻量级，且现代的网络速度比以前快得多，所以运行多个小查询现在已经不是大问题了
MySQL内部每秒能够扫描内存中上百万行数据，相比之下，MySQL响应数据给客户端就慢得多了；故在其他条件都相同的时候，使用尽可能少的查询更好，但有时也需要分解一个大查询为多个小查询

3.2 切分查询

有时候对于一个大查询我们需要“分而治之”，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果

删除旧的数据就是一个很好的例子。定期地清除大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询

3.3 分解关联查询

很多高性能的应用都会对关联查询进行分解

通过对每一个表进行一次单表查询，然后将结果在应用程序中进行关联

在很多场景下，通过重构查询将关联放到应用程序中将会更加高效，比如：

当应用能够方便地缓存单个查询的结果的时候
当可以将数据分布到不同的MySQL服务器上的时候
当能够使用IN() 的方式代替关联查询的时候
当查询中使用同一个数据表的时候

用分解关联查询的方式重构查询的好处：

许多应用程序能方便地缓存单表查询的结果，已经被缓存的查询结果，应用再需要时就可以跳过查询
另外，如果关联中的某个表发生了变化，那么就无法使用查询缓存了，而拆分后，如果某个表很少改变，那么基于该表的查询就可以重复利用查询缓存结果了
将查询分解后，执行单个查询可以减少锁竞争
在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展
换掉关联查询后，查询本身效率也可能会有所提升
减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据
这样相当于在应用中实现了哈希关联，而不是使用MySQL默认的嵌套循环关联。某些场景哈希关联的效率要高很多，尤其是在数据量大且没有索引的情况下
- 哈希关联（Hash Join）和 嵌套循环关联（Nested Loop Join）是常见的两种关联操作方式，而它们的效率在不同场景下存在显著差异
- 嵌套循环关联，对外层表中的每一行，在内层表中查找匹配的行。这意味着查询的时间复杂度通常是 O(M * N)，其中 M 和 N 是两个表的大小。因此，当表的数据量较大时，嵌套循环关联的查询代价会非常高，尤其是在没有索引的情况下
- 哈希关联通过为一个表的关联列构建哈希表，再根据另一个表的关联列快速查找匹配的行，极大地减少了逐行比较的次数。哈希关联的时间复杂度可以接近 O(M + N)，尤其是在没有索引的情况下，它的性能优势更加明显

四、查询执行的过程

MySQL执行查询的过程：

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

在这里插入图片描述

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

MySQL客户端和服务器之间的通信协议是“半双工”的，在任何一个时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生，所以无法也无须将一个消息切成小块来独立发送

这种协议让MySQL通信简单快速，但是也从很多地方限制了MySQL，如：没法进行流量控制。一旦一端开始发生消息，另一端要接收完整个消息才能响应它
一般服务器响应给用户的数据很多，由多个数据包组成；当服务器开始响应客户端请求时，客户端必须完整地接收整个返回结果，而不能简单地只取前面几条结果，然后让服务器停止发送数据
当使用多数连接MySQL的库函数从MySQL获取数据时，其结果看起来都像是从MySQL服务器获取数据，而实际上都是从这个库函数的缓存获取数据
- 但是如果需要返回一个很大的结果集的时候，这样做并不好，这种情况下可以不使用缓存来记录结果而是直接处理。这样做的缺点是，需要查询完成后服务器才能释放资源

查询状态：

在一个查询的生命周期中，MySQL连接的状态会变化很多次：

Sleep：线程正在等待客户端发送新的请求
Query：线程正在执行查询，或正在将结果发送给客户端
Locked：在MySQL服务器层，该线程正在等待表锁；对于MyISAM来说这是一个比较典型的状态；InnoDB的行锁，并不会体现在线程状态中
Analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划
Copying to tmp table [on disk]：线程正在执行查询，并将其结果集都复制到一个临时表中，这种状态一般是在做GROUP BY 操作，或是文件排序操作，或是UNION操作；如果有“on disk”标记，那表示MySQL正在将一个内存临时表放到磁盘上
The thread is：线程正在对结果集进行排序
Sending data：线程可能在多个状态之间传送数据，或在生成结果集，或在向客户端返回数据

4.2 查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的

查询和缓存中的查询即使只有一个字节不同，那也不会匹配缓存结果
如果当前的查询恰好命中了查询缓存，那么在返回查询结果之前MySQL会检查一次用户权限
如果可以直接从缓存中拿到结果并返回给客户端，则查询不会被解析，也不用生成执行计划

4.3 查询优化处理

查询的生命周期的下一步是将一个SQL转换成一个执行计划，MySQL再依照这个执行计划和存储引擎进行交互；这包括多个子阶段：解析SQL、预处理、优化SQL执行计划

4.3.1 语法解析器和预处理

首先，MySQL通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”

MySQL解析器将使用MySQL语法规则验证和解析查询，预处理器则根据一些MySQL规则进一步检查解析树是否合法，例如，检查数据表和数据列是否存在，解析名字和别名是否有歧义；下一步预处理器会验证权限

4.3.2 查询优化器

语法树被认为是合法的之后，由优化器将其转化成执行计划

MySQL使用基于成本的优化器，来找到尽可能最好的执行计划

优化策略分为两种，一种是静态优化，一种是动态优化
- 静态优化可以直接对解析树进行分析，并完成优化。静态优化只需要做一次，在第一次完成后就一直有效，即使用不同参数重复执行查询也不会发生变化，可以认为是一种“编译时优化”
- 动态优化和查询的上下文有关，例如WHERE 条件中的取值、索引中条目对应的数据行数等。动态优化需要在每次查询时都重新评估，可以认为是“运行时优化”
会导致MySQL优化器选择错误执行计划的原因：
- 统计信息不准确。MySQL依赖存储引擎提供的统计信息来评估成本，InnoDB因为其MVCC的架构，并不能维护一个数据表的行数的精确统计信息
- 执行计划中的成本估算不等同于实际执行的成本。MySQL层面并不知道哪些页面在内存中、哪些在磁盘上，所以查询实际执行过程中到底需要多少次物理I/O是无法得知的
- MySQL的最优可能和你想的最优不一样。你可能希望执行时间尽可能的短，但是 MySQL只是基于其成本模型选择最优的执行计划，而有些时候这并不是最快的执行方式
- MySQL从不考虑其他并发执行的查询，可能会对当前查询速度带来的影响
- MySQL有时也会基于一些固定的规则而非成本进行优化。例如，如果存在全文搜索的MATCH()子句，则在存在全文索引的时候就用它，即使使用别的索引和WHERE 条件远比这种方式快
- MySQL不会考虑不受其控制的操作的成本，例如执行存储过程或者用户自定义函数的成本
- 优化器有时候无法去估算所有可能的执行计划，所以它可能错过实际上最优的执行计划
MySQL优化器能够处理的优化类型：
- 重新定义关联表的顺序
  - 数据表的关联并不总是按照在查询中指定的顺序进行
- 将外连接OUTER JOIN 转化成内连接
- 使用等价变换规则
  - MySQL可以使用一些等价变换来简化并规范表达式，对于条件语句很有用
- 优化COUNT()、MIN()和MAX()
  - 如果有索引，则MIN()和MAX()可从索引两端获取，优化器会将其作为一个常数对待
  - 没有任何WHERE 条件的COUNT（*）查询，可以从表统计信息获取行数
- 预估并转化为常数表达式
  - 表达式，甚至查询都可能能转成常数
- 覆盖索引扫描
  - 当索引中包含查询需要的数据时，就无须再去访问对应数据行
- 子查询优化
  - MySQL在某些情况下可将子查询转换一种效率更高的形式，减少多个查询多次访问数据
- 提前终止查询
  - 在发现已经满足查询需求的时候，MySQL总是能够立刻终止查询，如LIMIT
  - 发现了不成立的条件，MySQL可以立刻返回一个空结果，此时查询是在优化阶段就已终止
- 等值传播
  - 如果两个列的值通过等式关联，那么MySQL能把其中一个列的WHERE 条件传递到另一列上
- 列表IN()的比较
  - 不完全等同于多个OR 条件的子句，因为等价地转换成OR 查询的复杂度为O（n ）
  - 而MySQL是将IN() 列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个O（log n ）复杂度的操作

4.3.3 数据和索引的统计信息

在服务器层有查询优化器，却没有保存数据和索引的统计信息，统计信息由存储引擎实现

MySQL查询优化器在生成查询的执行计划时，需要向存储引擎获取相应的统计信息

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

4.3.4 MySQL如何执行关联查询

对于UNION 查询，MySQL先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表数据来完成UNION 查询
当前MySQL关联执行的策略很简单：MySQL对任何关联都执行嵌套循环关联操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，然后回溯到上一个表，直到找到所有表中匹配的行为止
全外连接就无法通过嵌套循环和回溯的方式完成。这时当发现关联表中没有找到任何匹配行的时候，则可能是因为关联是恰好从一个没有任何匹配的表开始，传统的嵌套循环通过回溯只能处理匹配部分，但不能有效处理所有未匹配的数据

4.3.5 关联查询优化器

通常多表关联的时候，可以有多种不同的关联顺序来获得相同的执行结果

关联查询优化器则通过评估不同顺序时的成本来选择一个代价最小的关联顺序，来生成执行计划树

重新定义关联的顺序是优化器非常重要的一部分功能

4.3.6 排序优化

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

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

如果需要排序的数据量小于“排序缓冲区”，MySQL使用内存进行“快速排序”操作
如果内存不够排序，那么MySQL会先将数据分块，对每个独立的块使用“快速排序”进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排好序的块进行合并（merge）
两种排序算法：
- 两次传输排序（旧版本使用）
  - 读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取数据行
  - 这需要从数据表中读取两次数据，第二次读取数据的时候，因为是读取排序后的所有记录，会产生大量的随机I/O，所以数据传输成本非常高
  - 对于MyISAM表尤其如此，因为它使用系统调用进行数据的读取
- 单次传输排序（新版本使用）
  - 先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果
  - 不再需要从数据表中读取两次数据，但缺点是会额外占用大量的空间存列
MySQL在进行文件排序时使用的临时存储空间可能会比磁盘上的原表还大很多，因为MySQL在排序时，对每一个记录都会分配足够长的定长空间来存放，这个定长空间必须能容纳其中最长的字符串

4.3.7 执行计划

和很多其他关系数据库不同，MySQL并不会生成查询字节码来执行查询。MySQL生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果

任何多表查询都可以使用一棵树表示，如图是一个四表的关联操作：

在这里插入图片描述

但是MySQL不是按上图执行查询，而是从一个表开始一直嵌套循环、回溯完成所有表关联

在这里插入图片描述

查询的本意是平衡树，MySQL执行是按照左测深度优先的树

4.4 查询执行引擎

在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构，而不是其他关系型数据库生成对应的字节码

在根据执行计划逐步执行的过程中，有大量的操作需要通过调用存储引擎实现的接口来完成成，这些接口也就是我们称为“handler API ”的接口。查询中的每一个表由一个handler 的实例表示。实际上，MySQL在优化阶段就为每个表创建了一个handler 实例，优化器根据这些实例的接口可以获取表的相关信息，包括表的所有列名、索引统计信息等

4.5 返回结果给客户端

MySQL将结果集返回客户端是一个增量、逐步返回的过程。例如，对于关联操作，一旦服务器处理完最后一个关联表，开始生成第一条结果时，MySQL就可以开始向客户端逐步返回结果集了

这样有两个好处：
- 服务器端无须存储太多的结果，也就不会因为要返回太多结果而消耗太多内存
- 这样的处理也让MySQL客户端第一时间获得返回的结果

结果集中的每一行都会以一个满足MySQL客户端/服务器通信协议的封包发送，再通过TCP协议进行传输，在TCP传输的过程中，可能对MySQL的封包进行缓存然后批量传输

五、MySQL查询优化器的局限性

5.1 关联子查询

MySQL的子查询实现得非常糟糕。最糟糕的一类查询是WHERE 条件中包含IN() 的子查询语句

使用IN()加子查询，性能经常会非常糟，所以通常建议使用EXISTS() 等效的改写查询来获取更好的效率

有时子查询效果不一定差，应该用测试来验证对子查询的执行计划和响应时间的假设

5.2 UNION的限制

有时，MySQL无法将限制条件从外层“下推”到内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。如果希望UNION 的各个子句能够根据LIMIT 只取部分结果集，或者希望能够先排好序再合并结果集的话，就需要在UNION 的各个子句中分别使用这些子句

5.3 索引合并优化

当WHERE 子句中包含多个复杂条件的时候，MySQL能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行

5.4 等值传递

某些时候，等值传递会带来一些意想不到的额外消耗。例如，有一个非常大的IN() 列表，而MySQL优化器发现存在WHERE、ON 或者USING的子句，将这个列表的值和另一个表的某个列相关联。

那么优化器会将IN() 列表都复制应用到关联的各个表中。通常，因为各个表新增了过滤条件，优化器可以更高效地从存储引擎过滤记录，但是如果这个列表非常大，则会导致优化和执行都会变慢

5.5 并行执行

MySQL无法利用多核特性来并行执行查询

5.6 哈希关联

MySQL并不支持哈希关联，MySQL的所有关联都是嵌套循环关联

不过，可以通过创建自定义哈希索引来曲线地实现哈希关联

如果使用的是Memory存储引擎，则索引都是哈希索引，所以关联的时候也类似于哈希关联

5.7 松散索引扫描

MySQL最初并不支持松散索引扫描，也就无法按照不连续的方式扫描一个索引

MySQL 5.0之后的版本，在某些特殊的场景下是可以使用松散索引扫描的

例如，在一个分组查询中需要找到分组的最大值或最小值
不支持松散索引扫描时，MySQL的索引扫描需要先定义一个起点和终点，即使只需要这段索引中几个数据，仍需扫描这段索引中每一个条目，而不能跳过

例子：

假设在有索引（a，b）的表上做查询mysql> SELECT ... FROM tbl WHERE b BETWEEN 2 AND 3;

因为查询的条件是列b，跳过了a，故不能用这个索引，只能全表扫描

在这里插入图片描述

在MySQL支持松散索引扫描前，一个简单的绕过问题的办法就是给前面的列加上可能的常数值
但如果支持松散索引扫描，索引的物理结构使得可以先扫描a列第一个值对应的b列的范围，然后再跳到a列第二个不同值扫描对应的b列的范围，这样更快

在这里插入图片描述

5.8 最大值和最小值优化

对于MIN() 和MAX() 查询，MySQL的优化做得并不好

mysql>SELECT MIN(actor_id) FROM sakila.actor WHERE first_name='PENELOP'

假设主键是actor_id，因为在first_name 字段上并没有索引，因此MySQL将会进行一次全表扫描
理论上，MySQL如果进行主键扫描，那么当MySQL读到第一个满足条件的记录时，就是要找的最小值，因为主键是严格按照actor_id 字段的大小顺序排列的
但是MySQL这时只会做全表扫描
这时可以通过移除MIN()，然后使用LIMIT来优化查询

mysql>SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY)

->WHERE first_name = 'PENELOPE' LIMIT 1;

5.9 在同一个表上查询和更新

MySQL不允许对同一张表同时进行查询和更新

可以通过使用生成表的形式来绕过该限制

六、查询优化器的提示（hint）

如果对优化器选择的执行计划不满意，可以使用优化器提供的几个提示（hint），通过在查询中加入相应的提示，来控制最终的执行计划

HIGH_PRIORITY 和 LOW_PRIORITY

这个提示告诉MySQL，当多个语句同时访问某一个表的时候，语句优先级谁高谁低
只对使用表锁的存储引擎有效，在InnoDB或其他有细粒度锁机制和并发控制的引擎中无效
HIGH_PRIORITY 用于SELECT 语句时，MySQL会将此SELECT语句重新调度到所有正在等待表锁以便修改数据的语句之前；它还可以用于INSERT语句，用于抵消全局LOW_PRIORITY 设置对该语句的影响
LOW_PRIORITY 会让该语句一直处于等待状态，只要队列中还有需要访问同一个表的语句，在SELECT、INSERT 、UPDATE 和DELETE语句中都可以使用

DELAYED

这个提示对INSERT 和REPLACE 有效
MySQL会将使用该提示的语句立即返回给客户端，并将插入的行数据放入到缓冲区，然后在表空闲时批量将数据写入
适合日志系统或其他需要写入大量数据但客户端不需等待单条语句完成I/O的应用

STRAIGHT_JOIN

这个提示可以放在SELECT 语句的SELECT 关键字之后，也可以放置在任何两个关联表的名字之间
第一个用法是让查询中所有的表按照在语句中出现的顺序进行关联
第二个用法则是固定其前后两个表的关联顺序
加上这个提示会大大减少优化器的搜索空间

SQL_SMALL_RESULT 和 SQL_BIG_RESULT

只对SELECT 语句有效
用于告诉优化器对GROUP BY 或 DISTINCT 查询如何使用临时表及排序
SQL_SMALL_RESULT 告诉优化器结果集会很小，可将结果集放在内存中的索引临时表，以避免排序
SQL_BIG_RESULT 告诉优化器结果集会非常大，建议用磁盘临时表做排序

SQL_BUFFER_RESULT

用于告诉优化器将查询结果放入到一个临时表，并尽可能快地释放表锁
这和前面的由客户端缓存结果的DELAYED不同，其是使用服务器端的缓存

SQL_CACHE和SQL_NO_CACHE

用于告诉MySQL这个结果集是否应该缓存在查询缓存中

SQL_CALC_FOUND_ROWS

它会让MySQL返回的结果集包含更多的信息
查询中加上该提示MySQL会计算除去LIMIT 子句后这个查询要返回的结果集的总数，可以通过函数FOUND_ROW() 获得这个值

FOR UPDATE 和 LOCK IN SHARE MODE

这两个提示主要控制SELECT 语句的锁机制，但只对实现了行级锁的存储引擎有效
使用该提示会对符合查询条件的数据行加锁，唯一内置的支持这两个提示的引擎就是InnoDB
这两个提示滥用，很容易造成服务器的锁争用问题，应尽可能避免使用这两个提示

USE INDEX、IGNORE INDEX 和 FORCE INDEX

这几个提示会告诉优化器使用或者不使用哪些索引来查询记录
FORCE INDEX 和USE INDEX 基本相同，除了一点：FORCE INDEX会告诉优化器全表扫描的成本会远远高于索引扫描，哪怕实际上该索引用处不大
当发现优化器选择了错误的索引，或者因为某些原因（比如在不使用ORDER BY 的时候希望结果有序）要使用另一个索引时，可以使用该提示

在MySQL 5.0和更新版本中，新增了一些参数用来控制优化器的行为：

optimizer_search_depth

这个参数控制优化器在穷举执行计划时的限度

optimizer_prune_level

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

optimizer_switch

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

七、优化特定类型的查询

在前面分散地介绍了这些优化技巧，这里汇总一下

7.1 优化 COUNT() 查询

COUNT()函数的作用：

COUNT() 聚合函数有两种非常不同的作用：统计某个列值的数量，与统计行数
在统计列值时要求列值是非空的（无法统计NULL ），如果在COUNT() 的括号中指定了列或者列的表达式，则统计的就是这个表达式的结果数
当MySQL确认括号内的表达式值不可能为空时，实际上就是在统计行数
COUNT(*)：统计整个统计表或结果集中的所有行，不论列中有无NULL

COUNT(列名)：只统计某一列中非 NULL的行
一个容易产生的误解就是：MyISAM的COUNT() 函数总是非常快，不过这是有前提条件的
- 只有没有任何WHERE 条件的COUNT（*）才非常快，因为此时无须实际计算表的行数，可以利用存储引擎的统计信息直接获取
- 当统计带WHERE 子句的结果集行数，统计某个值的数量时，MyISAM的COUNT() 和其他存储引擎就没有任何不同，就不再有神话般的速度了

简单的优化

有时候可以使用MyISAM在COUNT(*) 全表非常快的特性，来加速一些特定条件的COUNT() 查询
一次查询想返回多个值可采用：mysql>SELECT COUNT(color = 'blue' OR NULL) AS blue, COUNT(color = 'red' OR NULL) AS red FROM items;

近似值优化

EXPLAIN 出来的优化器估算的行数就是一个不错的近似值，执行EXPLAIN 并不需要真正地去执行查询，所以成本很低。很多时候，计算精确值的成本非常高，而计算近似值则非常简单

更复杂的优化

通常COUNT() 都需要扫描大量的行（意味着要访问大量数据）才能获得精确的结果，因此很难优化
除了前面的方法，在MySQL层面还能做的就只有索引覆盖扫描了。如果这还不够，就需要考虑修改应用的架构，增加汇总表，或增加类似Memcached 这样的外部缓存系统

7.2 优化关联查询

确保ON 或者USING 子句中的列上有索引，在创建索引的时候就要考虑到关联的顺序
一般只需在关联顺序中的第二个表的相应列上创建索引，如：
- 当表A 和表B 用列c 关联时，如果优化器的关联顺序是B、A ，那么优化器会先扫描表B，然后基于关联条件 B.c = A.c 查找A表中的匹配行
- 那么就不需要在B表的对应列上建上索引，没有用到的索引只会带来额外的负担
确保任何的GROUP BY 和ORDER BY 中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程

7.3 优化子查询

子查询，也叫内嵌查询或嵌套查询，是指在一个 SQL 语句的另一个查询语句中嵌套的查询
- 子查询通常作为另一个查询的条件或者用于计算中间结果
- 子查询可以出现在 SELECT、FROM、WHERE、HAVING 等子句中
尽可能使用关联查询代替子查询
如果使用的是MySQL 5.6或更新的版本或MariaDB，则不必

7.4 优化GROUP BY 和 DISTINCT

DISTINCT：用于简单去重，删除查询结果中的重复行
GROUP BY：按某列进行分组，并对每个分组进行聚合操作。通常与聚合函数一起使用，如：SELECT class, COUNT(*) FROM students GROUP BY class; 会按班级分组，并统计每个班的学生数量

在很多场景下，MySQL都使用同样的办法优化这两种查询，使用索引来优化它们是最有效的优化办法，当无法使用索引的时候，GROUP BY 使用两种策略来完成：

使用临时表或者文件排序来做分组
如果需要对关联查询做分组（GROUP BY ），并且是按照查找表中的某个列进行分组，那么通常采用查找表的标识列分组的效率会比其他列更高
如果没有通过ORDER BY 子句显式地指定排序列，当查询使用GROUP BY 子句的时候，结果集会自动按照分组的字段进行排序。如果不关心结果集的顺序，而这种默认排序又导致了需要文件排序，则可以使用ORDER BY NULL ，让MySQL不再进行文件排序

优化GROUP BY WITH ROLLUP

分组查询的一个变种就是要求MySQL对返回的分组结果再做一次超级聚合
超级聚合指子查询是聚合，外部查询再对聚合后的结果进行一次更高层次的聚合
可以使用WITH ROLLUP 子句来实现这种逻辑，但可能会不够优化
很多时候，在应用程序中做超级聚合是更好的，虽然这需要返回给客户端更多的结果

7.5 优化LIMIT分页

分页操作 是在数据库查询中，将大量数据分成若干小块（页）并按页显示的一种方法

LIMIT + OFFSET 是实现分页的常用方法，但随着偏移量增大，查询效率会下降
优化方法：
- 索引可以帮助 MySQL 快速查找和排序数据，而不用再文件排序
- OFFSET 过大会导致MySQL扫描大量不需要的行然后再抛弃掉
- 如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET
- 还可以关联到一个冗余表，冗余表只包含主键列和需要做排序的数据列
- 延迟关联有时可以提升查询效率，它让MySQL扫描尽可能少的页面，获取需要访问的记录后再根据关联列回原表查询需要的所有列。这个技术也可以用于优化关联查询中的LIMIT 子句

7.6 优化UNION查询

UNION 是 SQL 中用于合并多个查询结果的操作符，它将两个或多个 SELECT 查询的结果集，（并消除重复的结果行，）合并为一个结果集

MySQL总是通过创建并填充临时表的方式来执行UNION 查询

经常需要手工地将WHERE、LIMIT、ORDER BY 等子句“下推”到UNION 的各个子查询中，以便优化器可以充分利用这些条件进行优化
例如，直接将这些子句冗余地写一份到各个子查询
除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL，如果没有ALL关键字，MySQL会给临时表加上DISTINCT 选项，这会导致对整个临时表的数据做唯一性检查，代价会非常高

7.7 静态查询分析

Percona Toolkit中的pt-query-advisor 能够解析查询日志、分析查询模式，然后给出所有可能存在潜在问题的查询，并给出足够详细的建议

7.8 使用用户自定义变量

用户自定义变量如果能够用好，在某些场景可以写出非常高效的查询语句。在查询中混合使用过程化和关系化逻辑的时候，自定义变量可能会非常有用

用户自定义变量是一个用来存储内容的临时容器，在连接MySQL的整个过程中都存在，可以使用SET和SELECT语句来定义它们，然后在任何可以使用表达式的地方使用

定义方法：

mysql>SET @one := 1;

mysql>SET @min_actor := (SELECT MIN(actor_id) FROM sakila.actor);

mysql>SET @last_week := CURRENT_DATE-INTERVAL 1 WEEK;

用户自定义变量的属性和限制：

使用自定义变量的查询，无法使用查询缓存
不能在使用常量或者标识符的地方使用自定义变量，例如表名、列名和LIMIT 子句中
用户自定义变量的生命周期是在一个连接中有效，所以不能用它们来做连接间的通信
如果使用连接池或者持久化连接，自定义变量可能因bug，而让看起来毫无关系的代码发生交互
在5.0之前的版本，是大小写敏感的，要注意代码在不同MySQL版本间的兼容问题
不能显式地声明自定义变量的类型
- 如果希望变量是整数类型，那最好在初始化时就赋为0，如果希望是浮点型则赋为0.0，如果希望是字符串则赋值为’’
- 其类型在赋值时会改变，是一个动态类型
MySQL优化器在某些场景下可能会将这些变量优化掉
赋值的顺序和赋值的时间点并不总是固定的，这依赖于优化器的决定
赋值符号:=的优先级非常低，所以赋值表达式应该用明确的括号

应用场景：

优化排名语句
- 用户自定义变量的一个重要特性是可以在给一个变量值的同时使用这个变量
- 可实现排序时增添一列排名列
- 但是变量赋值的时间可能和我们预料的不同
避免重复查询刚刚更新的数据
- 如果在更新行的同时又希望获得该行的信息，可使用用户自定义变量
统计更新和插入的数量
确定取值的顺序
- 使用用户自定义变量的一个最常见问题就是没有注意到在赋值和读取变量的时候可能是在查询的不同阶段。例如，在SELECT 子句中进行赋值然后在WHERE 子句中读取变量，可能就不在不同阶段
- 解决这个问题的办法是让变量的赋值和取值发生在执行查询的同一阶段，-> WHERE(@rownum :=@rownum+1)<=1;
编写偷懒的UNION
- 假设需要编写一个UNION 查询，其第一个子查询作为分支条件先执行，如果找到了匹配的行，则跳过第二个分支
- 在某些业务场景中确实会有这样的需求，比如先在一个频繁访问的表中查找“热”数据，找不到再去另外较少访问的表中查找“冷”数据，区分热数据和冷数据是一个很好的提高缓存命中率的办法
- 如：SELECT id FROM users WHERE id=123 UNION ALL SELECT id FROM users_archived WHERE id=123; 即使在users 表中已经找到了记录，上面的查询还是会去users_archived 中查找，可以定义一个变量@found，一旦在第一个表中找到记录，就在结果列中做一次赋值，然后将赋值放在函数GREATEST 中来避免返回额外的数据
其他用处
- 查询运行时计算总数和平均值
- 模拟GROUP 语句中的函数FIRST() 和LAST()
- 对大量数据做一些数据计算
- 计算一个大表的MD5散列值
- 编写一个样本处理函数，当样本中的数值超过某个边界值的时候将其变成0
- 模拟读/写游标
- 在SHOW 语句的WHERE 子句中加入变量值