4. 查询性能优化

4.1 为什么查询速度会变慢

快速查询取决于响应时间
如果把查询看成是一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定的时间。如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行得更快(有时候需要修改一些查询，减少这些查询对系统中运行的其他查询的影响，这时候是在减少一个查询的资源消耗)。
查询的生命周期：
- 从客户端
- 到服务器
- 在服务器上：
  - 解析
  - 生成执行计划
  - 执行：最重要的阶段。包括了大量为了检索数据到存储引擎的调用及调用后的数据处理，包括排序，分组等。
- 返回结果给客户端
完成这些任务，需要在不同的地方花费时间。包括网络，CPU计算，生成统计信息和执行计划、锁等待(互斥等待)等操作，尤其是向底层存储引擎检索数据的调用操作，这些调用需要在内存操作、CPU操作和内存不足时导致的IO操作上消耗的时间。根据存储引擎的不同，可能还会产生大量的上下文切换以及系统调用。

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

查询性能低下最根本的原因是访问的数据太多。某些查询可能不可避免地需要筛选大量数据，但这并不常见。大部分性能低下的查询都可以通过减少访问的数据量进行优化。对于低效的查询，可以通过下面两个步骤分析：

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

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

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

典型案例：

查询不需要的记录：一个常见错误是误以为MySQL只会返回需要的数据，实际上MySQL确实先返回全部结果集再进行计算。最简单有效的办法是在这样的查询后面加上LIMIT。
多表关联时返回全部列：正确做法是只取需要的列
总是取出全部的列：
- 取出全部的列，会让优化其无法完成覆盖扫描这类优化，还会给服务器带来额外的资源消耗。要慎重。
- 但是这可以简化开发，提供相同代码片段的复用性，或者应用程序使用了某种缓存机制等其他有必要取出全部列的因素。如果清楚这么做的性能影响，也是可以考虑的。
重复查询相同的数据：建议初次查询的时候将这个数据缓存起来，需要的时候从缓存中取出。

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

对于MySQL，最简单的衡量查询开销的三个指标：响应时间、扫描的行数和返回的行数。没有哪个指标能够完美地衡量查询的开销，但它们大致反映了MySQL在内部执行查询时需要访问多少数据，并可以大概推算出查询运行的时间。这三个指标都会记录到MySQL的慢日志中，检查慢日志记录是找出扫描行数过多的查询的好办法。

响应时间：
- 响应时间=服务时间+排队时间。实际上没办法细分，目前还没有办法测量。
  - 服务时间：数据库处理这个查询真正花的时间。
  - 排队时间：服务器因为等待某些资源而没有真正执行查询的时间。
- 看到一个查询的响应时间的时候，要评估是否合理。概括地说，了解这个查询需要那些索引以及它的执行计划是什么，然后计算大概需要多少个顺序和随机IO，在用其乘以在具体硬件条件下一次IO消耗的时间，最后把这些消耗都加起来得到一个参考值。
扫描的行数和返回的行数
- 分析查询时，查看该查询扫描的行数是非常有帮助的。在一定程度上能够说明该查询找到需要的数据效率高不高。
- 不过这个指标还不够完美，因为并不是所有的行的访问代价都是一样的。
- 理想情况下扫描的行数和返回的行数应该是相同的。但实际情况下很少存在，比如关联查询。一般扫描的行数与返回的行数比率通常在1:1和10:1之间。
扫描的行数和访问类型
- 在评估查询开销的时候，需要考虑从表中找到某一行数据的成本。
- 访问类型(EXPLAIN语句中的type列，row列显示扫描的行数)：
  - ref
  - ALL(全表扫描)
- MySQL应用WHERE条件的方式：
  - 在索引中使用WHERE条件来过滤不匹配的记录，这是在存储引擎完成的。
  - 使用覆盖索引扫描(在EXTRA 列中出现了Using index)来返回记录，直接从索引中过滤不需要的记录并返回命中结果。这是在MySQL服务器完成的，但无须再回表查询
  - 从数据表中返回数据，然后过滤不满足条件的记录(在Extra列中出现Using Where)。这在MySQL服务器层完成，MySQL需要先从数据表读出记录然后过滤。
- 优化技巧：
  - 使用索引覆盖扫描，把所有需要的列都放到索引中，这样存储引擎无须回表获取对应的行就可以返回结果。
  - 改变库表结构。例如使用单独的汇总表
  - 重写这个复杂的查询，让MySQL优化器能够以更优化的方式执行这个查询。

4.3 重构查询方式

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

MySQL从设计上让连接和断开连接都很轻量级，在返回一个小的查询结果方面很高效。
MySQL内部每秒能够扫描内存中上百万行数据，相比之下，MySQL响应数据给客户端就慢得多了。
使用尽可能少的查询，但某些场景下将一个大查询分解为多个小查询是很有必要的。

4.3.2 切分查询

有时候需要对一个大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。
例如删除旧的数据，切分该查询可以尽可能小地影响性能，还可以减少MySQL复制的延迟。一次删除一万行数据一般来说是一个比较高效而且对服务器影响也最小的做法，如果是事务型引擎，很多时候小事务能够更高效。另外，每次删除后都暂停一会，能够将一次性的压力分散到一个很长的时间段中，就可以大大降低对服务器的影响，还可以大大减少删除时锁的持有时间。

4.3.3 分解关联查询

很多高性能的应用都会对关联查询进行分解。简单地，可以对每一个表进行一次单表查询，然后将结果在应用程序中进行管理

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 whre post.id in (123, 456);

优势：
- 让缓存效率更高。许多应用程序可以方便地缓存单表查询时对应的结果对象，这样可以减少查询时的条件。而对MySQL的查询缓存来说，如果关联中的某个表发生了变化，那么久无法使用查询缓存了，而拆分后，如果某个表很少改变，那么基于该表的查询就可以重复利用查询缓存结果。
- 执行单个查询可以减少锁的竞争。
- 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
- 查询本身效率也可能会有所提升。例如使用IN()代替关联查询，可以让MySQL按照ID顺序进行查询，这可能比随机的关联要更高效。
- 可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。从这点看，这样的重构还可能会减少网络和内存的消耗。
- 更进一步，这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。某些场景哈希关联的效率要高得多。
通过重构查询将关联放到应用程序中更加高效的场景：
- 当应用能够方便地缓存当个查询的结果
- 当可以将数据分布到不同的MySQL服务器上
- 当能够使用IN()的方式来代替关联查询
- 当查询中使用同一个数据表的时候

4.4 查询执行的基础

当向MySQL发送一个请求的时候，MySQL的工作流程：

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

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

客户端与服务器之间的通信协议是”半双工“的，这意味着在任何一个时刻，只能有客户端或服务器的其中一个发送数据。
- 限制：
  - 没办法进行流量控制，一旦一端开始发送消息，另一端要接收完整消息才能响应它。
- 客户端用一个单独的数据包将查询传给服务器。如果查询太大，服务器会根据配置max_allowed_packet拒绝更多数据并抛出相应错误。
- 服务器响应给用户的数据通常更多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整地接收整个返回结果。如果只取前面几条结果，或者接收几条结果后就直接断开连接，建议在查询中加上LIMIT限制。
- 客户端多数连接MySQL的库函数(如Java，Python)都可以获取全部结果集并缓存到内存中，还可以逐行获取需要的数据。默认一般是获得全部结果集并缓存到内存中。
  - MySQL通常需要等到所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接收全部结果并缓存通过可以减少服务器的压力，让查询早点结束而释放相应资源。
  - 当使用多数连接MySQL的库函数从MySQL获取数据时，其结果看起来都像是从MySQL服务器获取数据，而实际上都是从这个库函数的缓存获取数据。但是当返回一个很大的结果集时，库函数会很很多时间和内存来存储所有的结果集，如果能够尽早开始处理，就能大大减少内存消耗，这种情况下可以不使用缓存来记录结果而是直接处理。这样做的缺点是，对服务器来说，需要等到查询完成后才能释放资源，因此服务器的资源都是被这个查询占用。
- 查询状态，对一个MySQL连接或者说一个线程，任何时候都有一个状态。最简单办法使用SHOW FULL PROCESSLIST命令查看：
  - 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：线程可能在多个状态间传送数据，或者在生成结果集，或者在向客户端返回数据。

4.4.2 查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存的数据。
- 这个检查是通过一个大小写敏感的哈希查找来实现的。
- 查询与缓存中的查询即使只有一个字节不同，那也不会匹配缓存结果，这种情况下查询就会进入下一阶段的处理。
- 如果当前的查询恰好命中了查询缓存，那么在返回查询结果之前MySQL会检查一次用户权限。这也不需要解析查询SQL语句的，因为在查询缓存中已经存放了当前查询需要访问的表信息。如果权限没有问题，MySQL会跳过所有其它的阶段，直接从缓存中拿到结果并返回给客户端。

4.4.3 查询优化处理

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

语法解析器和预处理：
- 流程：
  - MySQL通过关键字将SQL进行解析，并生成一颗对应的”解析树“。MySQL解析器将使用MySQL语法规则验证和解析查询。例如：验证是否使用错误的关键词，或者使用关键词的顺序是否正确等，再或者它还会验证引号是否能前后正确匹配。
  - 预处理器则根据一些MySQL规则进一步检查解析树是否合法，例如，这里将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义。
  - 预处理器会验证权限。这通常很快，除非服务器上有非常多的权限配置。
查询优化器：
- 经过语法解析器和预处理后，语法树被认为是合法的，并将由优化器将其转化成执行计划。
  - 一条查询可以有很多执行方式，最后都返回相同的结果。
  - 优化器的作用就是找到这其中最好的执行计划。
- MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。
  - 最初，成本的最小单位是随机读取一个4K数据页的成本，后来成本计算公式变得更加复杂，并且引入了一些”因子“来估算这些操作的代价，比如当执行一次WHERE条件比较的成本。
  - 可以通过SHOW STATUS LIKE 'Last_query_cost';来查询当前会话的当前查询的成本，其值N为MySQL的优化器认为大概需要做N个数据页的随机查找才能完成当前的查询。
  - Last_query_cost根据一系列统计信息计算得来：每个表或索引的页面个数、索引的基数(索引中不同值的数量)、索引和数据行的长度、索引的分布情况。优化器在评估成本的时候并不考虑任何层面的缓存，它假设读取任何数据都需要一次磁盘IO。
- 导致MySQL选择错误的执行计划的原因:
  - 统计信息不准确。MySQL依赖存储引擎提供的统计信息来评估成本，但是有的存储引擎提供的信息可能偏差非常大。例如，InnoDB因为其MVVC的架构，并不能维护一个数据表的行数的精确统计信息。
  - 执行计划中的成本估算不等同于实际执行的成本。所以即使统计信息精确，优化器给出的执行计划也可能不是最优的。例如有时候某个执行计划虽然需要读取更多的页面，但是它的成本却更小，因为这些页面可能都是顺序读或者这些页面已经在内存中，它的访问成本将很小。
  - MySQL的最优可能和你想的最优不一样。你可能希望执行时间尽可能短，但是MySQL只是基于其成本模型选择最优的执行计划，而有些时候这并不是最快的执行方式。所以，这里我们看到根据执行成本来选择执行计划并不是完美的模型。
  - MySQL从不考虑其他并发执行的查询，这可能会影响到当前查询的速度。
  - MySQL也并不是任何时候都是基于成本的优化。有时也会基于一些固定的规则，例如，如果存在全文搜索的MATCH()子句，则在存在全文索引的时候就使用全文索引，即使有时候使用别的索引和WHERE条件可以远比这种方式要快，MySQL也仍然会使用对应的全文索引。
  - MySQL不会考虑不受其控制的操作成本，例如执行存储过程或者用户自定义函数的成本。
  - 优化器有时候无法去估算所有可能的执行计划，所以它可能错过实际上最优的执行计划。
- 优化策略：
  - 静态优化：直接对解析树进行分析，并完成优化。例如，优化器可以通过一些简单的代数变换将WHERE条件转换成另一种等价形式。静态优化不依赖于特别的数值，如WHERE条件中带入的一些常数等。静态优化在第一次完成后就一直有效，即使使用不同的参数重复执行查询也不会发生变化。可以认为这是一种”编译时优化“。
  - 动态优化：与上下文有关，也可能和其他很多因素有关，例如WHER条件中的取值，索引中条目对应的数据行数等。这需要在每次查询的执行时候都重新评估，甚至在其执行过程中也会重新优化，可以认为这是”运行时优化“。
- MySQL能够处理的优化类型：
  - 重新定义关联表的顺序：数据表的关联并不总是按照在查询中指定的顺序执行。决定关联的顺序是优化器很重要的一部分功能。
  - 将外连接转换为内连接：并不是所有的OUTER JOIN语句都必须以外连接的方式执行。例如WHERE条件，库表结构都可能会让外连接等价于一个内连接。
  - 使用等价变换规则：MySQL使用一些等价变换来简化并规范表达式。它可以合并和减少一些比较，还可以移除一些恒成立和一些恒不成立的判断。
  - 优化COUNT()、MIN()和MAX()：索引和列是否可为空可以帮助MySQL优化这类表达式。例如，要找到某一列的最小值，只需要查询B-Tree索引最左端的记录，MySQL可以直接获取，并在优化器生成执行计划的时候就可以利用这一点(优化器会将这个表达式作为一个常数对待，在EXPLAIN就可以看到"Select tables optimized away")。类似的，没有任何WHERE条件的COUNT(*)查询通常也可以使用存储引擎提供的一些优化(MyISAM维护了一个变量来存放数据表的行数)
  - 预估并转换为常数表达式：MySQL检测到一个表达式可以转换为常数的时候，就会一直把该表达式作为常数进行优化处理。例如：一个用户自定义变量在查询中没有发生变化、数学表达式、某些特定的查询(在索引列上执行MIN，甚至是主键或唯一键查找语句)、通过等式将常数值从一个表传到另一个表(通过WHERE、USING或ON来限制某列取值为常数)。
  - 覆盖索引扫描：当索引中的列包含所有查询中所有需要的列的时候，MySQL就可以使用索引返回需要的数据，而无须查询对应的数据行。
  - 子查询优化：在某些情况下可以将子查询转换成一种效率更高的形式，从而减少多个查询多次对数据的访问。
  - 提前终止查询：当发现已经满足查询的需求，能够立刻终止查询。例如使用了LIMIT子句，或者发现一个不成立的条件(立即返回一个空结果)。当存储引擎需要检索”不同取值“或者判断存在性的时候，例如DISTINCT，NOT EXIST()或者LEFT JOIN类型的查询，MySQL都会使用这类优化。
  - 等值传播：如果两个列的值通过等式关联，那么就可以把其中一个列的WHERE条件传递到另一个列上。
```
SELECT film.film_id
FROM sakila.film
    INNER JOIN sakila.film_actor USING(film_id)
WHERE film.file_id > 500;
-- 如果手动通过一些条件来告知优化器这个WHERE条件适用于两个表，在MySQL中反而让查询更难维护。
... WHERE film.file_id > 500 AND film_actor.film_id > 500;
```
  - 列表IN()的比较：不同于其它数据库IN()完全等价于多个OR条件语句，MySQL将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表的值是否满足条件，前者查询复杂度为O(n)，后者为O(log n)。对于有大量取值的情况，MySQL这种处理速度会更快。
数据和索引的统计信息：
- MySQL架构在服务器层有查询优化器，但却没有保存数据和索引的统计信息。因为统计信息由存储引擎实现，不同的存储引擎可能会存储不同的统计信息。
- MySQL查询优化器在生成查询的执行计划时，需要向存储引擎获取相应的统计信息。存储引擎则提供给优化器对应的统计信息，包括：每个表或索引有多少个页面、每个表的每个索引的基数是多少、数据行和索引的长度、索引的分布信息等。
MySQL如何执行关联查询：
- MySQL认为任何一个查询都是管理，而不局限于需要两个表的匹配，包括每一个查询，每一个片段(例如子查询，甚至基于单表的SELECT)
  - UNION查询的例子：MySQL先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表数据来完成UNION查询。该读取临时表结果的操作也是关联。
- MySQL关联执行的策略：对任何关联都执行嵌套循环关联操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找新配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。MySQL会尝试在最后一个关联表中找到所有匹配的行，如果最后一个关联表无法找到更多的行以后，MySQL返回到上一层关联表，看是否能够找到更多的匹配记录，依此类推迭代执行。
  - 从本质上来说，所有类型的查询都以同样的方式运行。例如，MySQL在FROM子句中遇到子查询时，先执行子查询并将其结果放到一个临时表(MySQL的临时表是没有任何索引的，UNION查询也一样)，然后将这个临时表当作一个普通表对待。简而言之，所有的查询类型都转换成类似的执行计划(在MySQL5.6和MariaDB有重大改变，两个版本都引入更加复杂的执行计划)
  - 不过不是所有的查询都可以转换。例如，全外连接就无法通过嵌套循环和回溯的方式完成，这是当发现关联表中没有找到任何匹配行的时候，则可能是因为关联是恰好从一个没有任何匹配的表开始。这大概也是MySQL并不支持全外连接的原因。
执行计划：
- MySQL生成查询的一颗指令树，然后通过存储引擎执行完成这颗指令树并返回结果，这点和其他生成查询字节码来执行查询的其它关系数据库不同。
- 最终的执行计划包含了重构查询的全部信息。可以对查询执行EXPLAIN EXTENDED后再执行SHOW WARNINGS看到重构的查询(和原查询有相同的语义，但是查询语句可能并不完全相同)
- 任何多表查询都可以用一棵树来表示，我们可以理解为一颗平衡树，但是MySQL的执行计划是一颗左侧深度优先的树。
关联查询优化器：
- MySQL优化器最重要的一部分就是关联查询优化，它决定了多个表关联时的顺序。通常多表关联的时候，可以有多张不同的关联顺序，而关联查询优化器则通过评估不同顺序的成本来选择一个代价最小的关联顺序。
- 有的时候，优化器给出的并不是最优的关联顺序，这时可以使用STRAIGHT_JOIN关键字来重写查询，让优化器按照你认为的最优关联顺序执行——绝大多数时候，优化器做出的选择都更为准确。
- 优化器会尽可能遍历没一个表然后逐个做嵌套循环计算每一颗可能的执行计划的树的成本，最后返回一个最优的执行计划。
  - 如果有N个表关联，那么需要检查n的阶乘种关联顺序。我们称之为所有可能的执行计划的”搜索空间“。如果搜索空间非常大，当需要关联的表超过optimizer_search_depth的限制，优化器会选择使用”贪婪“搜索的方式查找”最优“的关联方式。
- 优化器偶尔也会选择一个不是最优的执行计划。
- 有时，各个查询的顺序并不能随意安排，这时关联优化器可以根据这些规则大大减少搜索空间，例如，左连接、相关子查询。这是因为后面的表的查询需要依赖于前面表的查询结果，这种依赖关系通常可以帮助优化器大大减少需要扫描的执行计划数量。
排序优化
- 无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序。
- 文件排序：当不能使用索引生成排序结果的时候，MySQL需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘。
  - 如果需要排序的数据量小于”排序缓冲区“，MySQL使用内存进行”快速排序“操作。
  - 如果内存不够排序，MySQL会先将数据分块，对每个独立的块使用”快速排序“进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排序好的块进行合并，最后返回排序结果。
- 排序算法：
  - 两次传输排序(旧版本使用)：读取行指针和需要排序的字段，对其进行排序，然后在根据排序结果读取需要的数据行。这需要两次数据传输，即需要从数据表中读取两次数据，第二次读取数据的时候，因为读取顺序列进行排序后的所有记录，这会产生大量的随机IO，所以成本非常高。当使用的是MyISAM表的时候，成本可能会更高，因为MyISAM使用系统调用进行数据读取(非常依赖操作系统对数据的缓存)。不过这样做的优点是：排序的时候存储尽可能少的数据，让”排序缓冲区“中尽可能容纳更多的行数进行排序。
  - 单次传输排序(4.1后新版本使用)：先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果。有点事只需要一次顺序IO读取所有的数据，而无须任何的随机IO，缺点是如果需要返回的列非常大，会额外占用大量的空间，而这些列对排序操作来说是没有任何作用的。因为单条排序记录很大，所以可能会有更多的排序块需要合并。
  - 很难说以上两个算法效率更高，当查询需要所有的列的总长度不超过max_lenght_for_sort_data时，MySQL使用“单次传输排序”，可以通过参数选择来影响MySQL排序算法的选择。
- 进行文件排序的时候需要使用的临时存储空间可能会比想象的要大得多。原因在于MySQL排序时，对每一个排序记录都会分配一个足够长的定长空间来存放。
  - 这个定长空间必须足够长以容纳其中最长的字符串。如果是VARCHAR列则需要分配其完整长度，如果使用UTF-8字符集，则为每个字符预留三个字节。
- 在关联查询的时候排序：
  - 如果ORDER BY子句中所有列都来自关联的第一个表，那么MySQL在关联处理第一个表的时候进行文件排序。可以在EXPLAIN看到Extra字段有“Using filesort”
  - 除第一种场景，MySQL都会先将关联的结果放到一个临时表中，然后在所有的关联都结束后，再进行文件排序操作。用EXPLAIN可看到“Using temporary;Using filesort”。如果查询中有LIMIT的话，LIMIT也会在排序之后应用，所以即使需要返回较少的数据，临时表和需要排序的数据仍然非常大。
    
    5.6后版本在这里做了些改进：当只需要返回部分排序结果的时候，例如使用了LIMIT子句，MySQL不再对所有的结果进行排序，而是根据实际情况，选择抛弃不满足条件的结果，然后在进行排序。

4.4.4 查询执行引擎

在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询执行引擎则根据这个执行计划来完成整个查询。
- 这里的执行计划是一个数据结构，不同于其他关系数据库生成的字节码。
查询执行阶段不是那么复杂，MySQL只是简单地根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中，又大量的操作需要调用存储引擎实现的“handle API”接口来完成。
- 查询中的每一个表由一个handler实例来标识。实际上，MySQL在优化阶段就为每个表创建了一个handler实例，优化器根据这些实例的接口可以获取表的相关信息。
- 存储引擎的接口有着非常丰富的功能，但是底层接口却只有十几个，这些接口相互配合能够完成查询的大部分操作

并不是所有的操作都有handler完成。例如，当MySQL需要进行表锁的时候，handler可能会实现自己级别的、更细粒度的锁，如InnoDB就实现了自己的行基本锁，但这并不能代替服务器层的表锁。如果是所有存储共有的特性则由服务器层实现，如时间、日期函数、视图和触发器等。

4.4.5 返回结果给客户端

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

4.5 MySQL查询优化器的局限性

MySQL的万能“嵌套循环”并不是对每种查询都是最优的，但只对少部分查询不适用，我们往往可以通过改写查询让MySQL高效地完成工作。另外，5.6版本会消除很多原本的限制，让更多的查询能够已尽可能高的效率完成。

4.5.1 关联子查询

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

SELECT * FROM sakila.film
WHERE film_id IN(
    SELECT film_id FROM sakil.film_actor WHERE actor_id =1 );
-- MySQL对IN()列表中的选项有专门的优化策略，但关联子查询并不是这样的,MySQL会将相关的外层表压到子查询中，它认为这样可以高效地查找到数据行。也就是说，以上查询会被MySQL更改成：
SELECT * FROM sakila.film
WHERE EXISTS(
    SELECT film_id FROM sakil.film_actor WHERE actor_id =1
    AND film_actor.film_id = film.film_id);
-- 这时子查询需要根据film_id来关联外部表的film，因为需要film_id字段，所以MySQL认为无法先执行这个子查询。通过EXPLIAN可以看到子查询是一个相关子查询(DEPENDENT SUBQUERY)，并且可以看到对film表进行全表扫描，然后根据返回的film_id逐个进行子查询。如果外层是一个很大的表，查询性能会很糟糕。
-- 优化重写方式1：
SELECT film.* FROM sakila.film
    INNER JOIN sakil.film_actor USING(film_id) 
WHERE actor_id =1;
-- 优化重写方式2：使用函数GROUP_CONCAT()在IN()中构造一个逗号分割的列表。
-- 优化重写方式3，使用EXISTS()等效的改写查询：
SELECT * FROM sakila.film
WHERE EXISTS(
    SELECT film_id FROM sakil.film_actor WHERE actor_id =1
    AND film_actor.film_id = film.film_id);

如何用好关联子查询
- 并不是所有的关联子查询性能都很差，需要先测试再做出判断。很多时候，关联子查询是一种非常合理、自然、甚至是性能最好的写法。

4.5.2 UNION的限制

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

如果希望UNION的各个子句能够根据LIMIT只取部分结果集，或者希望能够先排好序再合并结果集的话，就需要在UNION的各个子句中分别使用这些子句。另外，从临时表取出数据的顺序是不一定的，如果要获得正确的顺序，还需要加上一个全局的ORDER BY 和 LIMIT

(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
(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;

4.5.3 索引合并优化

5.0及其新版本，当WHERE条件包含多个复杂条件的时候，MySQL能够访问当个表的多个索引以合并和交叉过滤来定位需要查找的行。

4.5.4 等值传递

某些时候，等值传递会带来一些意想不到的额外消耗。例如，有一个非常大的IN()列表，而MySQL优化器发现存在WHERE、ON或者USING的子句，将这个列表的值和另一个表的某个列相关联。
- 优化器会将IN()列表都复制应用到关联的各个表中。通常各个表因为新增了过滤条件，优化器可以更高效地从存储引擎过滤记录。但是如果列表非常大，则会导致优化和执行都会变得更慢。

4.5.5 并行执行

MySQL无法利用多核特性来并行执行查询，这点和其他关系型数据库不一样。

4.5.6 哈希关联

MySQL并不支持哈希关联——所有的关联都是嵌套循环关联。不过，可以通过建立一个哈希索引来曲线实现哈希关联。
如果使用的是Memory存储引擎，则索引是哈希索引，所以关联的时候也类似于哈希关联。

4.5.7 松散索引扫描

MySQL并不支持松散索引扫描，也就无法按照不连续的方式扫描一个索引。通常，MySQL的索引扫描需要先定义一个起点和终点，即使需要的数据只是这段索引中很少数的几个，MySQL仍需要扫描这段索引中每一个字段。
示例：假设我们有索引(a,b)，有以下查询SELECT ... FROM tb1 WHERE b BETEWEEN 2 AND 3;，因为只使用了字段b而不符合索引的最左前缀，MySQL无法使用这个索引，从而只能通过全表扫描找到匹配的行。

MySQL通过全表扫描找到需要的记录

了解索引结构的话，会发现还有一个更快的办法执行上面的查询。索引的物理结构(不是存储引擎API)使得可以先扫描a列第一个值对应的b列的范围，然后在跳到a列第二个只扫描对应的b列的范围，即松散索引扫描。这时就无须再使用WHERE过滤，因为已经跳过了所有不需要的记录。MySQL并不支持松散索引扫描

松散索引扫描

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

-- 在Extra字段显示“Using index for group-by”,表示使用松散索引扫描
EXPLAIN SELECT actor_id, MAX(film_id)
FROM sakila.film_actor
GROUP BY actor\G;

在MySQL很好地支持松散索引扫描之前，一个简单的绕过办法就是给前面的列加上可能的常数值。5.6之后的版本，关于松散索引扫描的一些限制将会通过“索引条件下推(index condition pushdown)”的方式解决

4.5.8 最大值和最小值优化

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

SELECT MIN(actor_id) FROM sakila.actor WHERE first_name = 'PENELOPE';
-- 因为在first_name上没有索引，MySQL将会进行一次全表扫描。如果MySQL能够进行主键扫描，那么理论上当MySQL读到第一个满足条件的记录，就是需要找到的最小值，因为主键是严格按照actor_id字段的大小顺序排列的。
-- 曲线优化办法：移除MIN()，然后使用LIMIT
SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY) WHERE first_name = 'PENNLOPE' LIMIT 1;
-- 该SQL已经无法表达它的本意，一般我们通过SQL告诉服务器需要什么数据，再由服务器决定如何最优地获取数据。但有时候为了获得更高的性能，需要放弃一些原则。

4.5.9 在同一个表查询和更新

MySQL不允许对同一张表同时进行查询和更新。这其实并不是优化器的限制，如果清楚MySQL是如何执行查询的，就可以避免这种情况。可以通过生成表来绕过该限制。

-- 符合标准的SQL，但是无法运行
mysql> UPDATE tbl AS outer_tbl
-> SET cnt = (
-> SELECT count(*) FROM tbl AS inner_tbl
-> WHERE inner_tbl.type = outer_tbl.type
-> );
-- 生成表来绕过该限制：
mysql> UPDATE tbl
-> INNER JOIN(
-> SELECT type, count(*) AS cnt
-> FROM tbl
-> GROUP BY type
-> ) AS der USING(type)
-> SET tbl.cnt = der.cnt;

4.6 查询优化器的提示(hint)

如果对优化器选择的执行计划不满意，可以使用优化器提供的几个提示(hint)来控制最终的执行计划。不过MySQL升级后可能会导致这些提示无效，需要重新审查。

部分提示类型：
- HIGH_PRIORITY和LOW_PRIORITY:
  
  告诉MySQL当多个语句同时访问某一个表的时候，这些语句的优先级。只对使用表锁的存储引擎有效，但即使是在MyISAM中也要慎重，因为这两个提示会导致并发插入被禁用，可能会导致严重降低性能
  - HIGH_PRIORITY：用于SELECT语句时，MySQL会将此语句重新调度到所有正在等待表锁以便修改数据的语句之前。实际上是将其放在表的队列的最前面，而不是按照常规顺序等待。用于INSERT语句，其效果只是简单地抵消了全局LOW_PRIORITY设置对该语句的影响。
  - 用于SELECT、INSERT、UPDATE和DELETE语句，让该语句一直处于等待状态，只要队列中还有需要访问同一个表的语句——即使那些比该语句还晚提交到服务器的语句。
- DELAYED：
  - 只对INSERT和REPLACE有效。
  - MySQL会将使用该提示的语句立即返回给客户端，并将插入的行数据放入到缓冲区，然后在表空闲时批量将数据写入。
  - 日志系统使用这样的提示非常有效，或者是其他需要写入大量数据但是客户端却不需要等待单条语句完成IO的应用。
  - 限制：并不是所有的存储引擎都支持；并且该提示会导致函数1255628无法正常工作
- STRAIGHT_JOIN:
  
  当MySQL没能正确选择关联顺序的时候，或者由于可能的顺序太多导致MySQL无法评估所有的关联顺序的时候，STRAIGNT_JOIN都会很有用。特别是在以下第二种情况，MySQL可能会花费大量时间在”statistics“状态，加上这个提示会大大减少优化器的搜索空间。
  
  可以先使用EXLPAN语句来查看优化器选择的关联顺序，然后使用该提示来重写查询，确定最优的关联顺序。但是在升级MySQL的时候，要重新审视这类查询。
  - 放置在SELECT语句的SELECT关键字之后：让查询中所有的表按照在语句中出现的顺序进行关联
  - 放置在任何两个关联表的名字之间：固定其前后两个表的关联顺序。
- SQL_SMALL_RESULT和SQL_BIG_RESULT：
  - 只对SELECT语句有效，告诉优化器对GROUP BY或者DISTINCT查询如何使用临时表和排序。
  - SQL_SMALL_RESULT告诉优化器结果集很小，可以将结果集放在内存中的索引临时表，避免排序操作
  - SQL_BIG_RESULT告诉优化器结果集可能会非常大，建议使用磁盘临时表做排序操作
- SQL_BUFFER_RESULT：
  - 告诉优化器将查询结果放入到一个临时表，然后尽可能地释放表锁。
- SQL_CACHE和SQL_NO_CACHE
  - 告诉MySQL这个结果集是否应该缓存在查询缓存中
- SQL_CALC_FOUND_ROWS：
  - 严格来说，并不是一个优化器提示，它不会告诉优化器任何关于执行计划的东西。
  - 让MySQL返回的结果集包含更多信息。
  - 查询中加上该提示MySQL会计算除去LIMIT子句后这个查询要返回的结果集的总数，而实际上只返回LIMIT要求的结果集
- FOR UPDATE和LOCK IN SHARE MODE
  - 也不是真正的优化器提示。
  - 主要控制SELECT语句的锁机制，但只对实现了行级锁的存储引擎有效。
  - 会对符合查询条件的数据行加锁。对于INSERT...SELECT语句是不需要这两个提示的，因为5.0及新版本会默认加锁。
  - InnoDB是唯一内置支持这两个提示的引擎。但是这两个提示会让某些优化无法正常使用，例如索引覆盖扫描。InnoDB不能在不访问主键的情况下排他地锁定行，因为行的版本信息保存在主键中。
  - 这两个提示经常被滥用，很容易造成服务器的锁争用问题，应该尽可能避免使用。
- USING INDEX、IGONRE INDEX和FORCE INDEX：
  - 告诉优化器使用或不使用哪些索引来查询记录(例如，在决定关联顺序的时候使用哪个索引)。
  - 5.1及新版本可以通过FOR ORDER BY和FOR GROUP BY来制定是否对排序和分组有效。
  - USING INDEX和FORCE INDEX基本相同。但是FORCE INDEX会告诉优化器全表扫描的成本会远远高于索引扫描，哪怕实际上该索引的用处不大。当发现优化器选择了错误的索引，或者因为某些原因(比如在不适用ORDER BY的时候希望结果有序)要使用另一个索引时，可以使用该提示。
5.0和更新版本新增用来控制优化器行为的参数：
- optimizer_search_depth:控制优化器在穷举执行计划时的限度。如果查询长时间处于"Statistics"状态，可以考虑调低
- optimizer_prune_level:默认打开，让优化器根据需要扫描的行数来决定是否跳过某些执行计划
- optimizer_swith:包含了一些开启/关闭优化器特性的标志位。

4.7 优化特定类型的查询

4.7.1 优化count()查询

count()的作用：
- 统计某个列值的数量，要求列值是非空的(不统计NULL)。如果在COUNT()的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数(而不是NULL)
- 统计行数。当MySQL确认括号内的表达式值不可能为空时，实际上就是统计行数。
  - 当使用COUNT()时，并不会像我们猜想的扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数。
  - 常见错误：在括号内指定了一个列却希望统计结果集的行数。
关于MyISAM的神话：
- 只有没有任何WHERE条件的count(*)，MyISAM的COUNT()函数才会非常快，因为此时无须实际地去计算表的行数。MySQL可以利用存储引擎的特性直接获得这个值。
- 如果MySQL知道某列col不可能为NULL值，那么内部会把count(col)优化成count(*)
- 当统计带有WHERE子句，MyISAM的COUNT()和其他存储引擎没有任何不同，就不会再有神话般的速度。

简单的优化

利用MyISAM在count(*)全表非常快的特性，来加速一些特定条件的查询。

-- 使用标准数据据worold
SELECT count(*) FROM world.city WHERE ID > 5;
-- 将条件反转，可很大程度减少扫描行数到5行以内
SELECT (SELECT count(*) FROM world.city) - COUNT(*) 
FROM world.city WHERE ID <= 5;

示例：假设可能需要通过一个查询返回各种不同颜色的商品数量

-- 使用SUM
SELECT SUM(IF(color = 'blue', 1, 0)) AS blue,SUM(IF(color = 'red', 1, 0)) AS red FROM items;
-- 使用COUNT，只需要将满足条件的设置为真，不满足设置为NULL
SELECT COUNT(color = 'blue' OR NULL) AS blue, COUNT(color = 'red' OR NULLASred FROM items;

使用近似值：
- 有时候某些业务场景并不要求完全精确的COUNT值，此时可以用近似值来代替。
- EXPLAIN出来的优化器估算的行数就是一个不错的近似值，执行EXPLAIN并不需要真正地去执行查询，所以成本很低。
- 例如：如果要精确统计网站在线人数，通常WHERE条件会很复杂，一方面需要过滤当前非活跃用户，另一方面还需要过滤系统中某些特定ID的默认用户，去掉这些约束条件对总数的影响很小，但却可能很好地提高该查询的性能。更进一步优化则可以尝试删除DISTINCT这样的约束来避免文件排序。这样重写的查询比精确查询要快得多，而返回的结果则几乎相同。
更复杂的优化：
- 通常来说，COUNT()都需要扫描大量的行(意味着要访问大量数据)才能获得精确的结果，因此是很难优化的。
- 优化方法：
  - 前面提到的方法
  - 在MySQL层面能做的只有索引覆盖扫描
  - 考虑修改应用架构，可以增加汇总表，或者类似Memcached这样的外部缓存系统。可能很快你就会发现陷入到一个熟悉的困境，”快速、精确和实现简单“，三者永远只能满足其二，必须舍掉其中一个。
4.7.2 优化关联查询
- 确保ON或者USING子句的列上有索引。
  - 在创建索引的时候就需要考虑关联的顺序。当表A和表B用列c关联的时候，入股优化器的关联顺序是B、A，那么就不需要在B表的对应列上创建索引。没有的索引智慧带来额外的负担。
  - 一般来说，除非有其他理由，否则只需要在关联顺序的第二个表的响应列上创建索引。
- 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程。
- 当升级MySQL的时候需要注意：关联语法、运算符优先级等其他可能会发生变化的地方。因为以前是普通关联的地方可能会变成笛卡儿积，不同类型的关联可能会生成不同的结果。

4.7.3 优化子查询

尽可能使用关联查询代替。这并不是绝对的，5.6及新版本或者MariaDB，可以直接使用子查询。

4.7.4 优化GROUP BY和DISTINCT

在很多场景下，MySQL都使用同样的办法优化这两种查询，事实上，MySQL优化器会在内部处理的时候相互转换这两类查询。它们都可以使用索引来优化，这也是最有效的优化办法。
当无法使用索引时，GROUP BY使用两种策略：使用临时表或者文件排序来做分组。可以通过提示SQL_BIG_RESULT和SQL_SMALL_RESULT来让优化器按照你希望的方式运行。
如果需要对关联查询做分组GROUP BY，并且是按照查找表中的某个列进行分组，那么通常采用查找表的标识列分组的效率会比其他列更高
- SELECT后面出现的非分组列一定是直接依赖于分组列，并且在每个组内的值是唯一的，或者是业务上根本不在乎这个值具体是什么。
- 在分组查询的SELECT中直接使用非分组列通常不是什么好主意，因为这样的结果通常是不定的，当索引改变，或者优化器选择不同的优化策略时都可能导致结果不一样。
如果没有通过ORDER BY子句显示地制定排序列，当查询使用GROUP BY子句的时候，结果集会自动按照分组的字段进行排序。如果不关心结果集的排序，而这种默认排序又导致了需要文件排序，则可以使用ORDER BY NULL，让MySQL不再进行文件排序。
优化GROUP BY WITH ROLLUP：
- 分组查询的一个变种就按要求MySQL对返回的分组结果在做一次超级聚合。可以使用WITH ROLLUP子句来实现这种逻辑，但可能会不够优化。
  - 很多时候，如果可以在应用程序中做超级聚合是更好的，虽然这需要返回给客户端更多的结果。
  - 也可以在FROM子句中嵌套使用子查询，或者是通过一个临时表存放中间数据，然后和临时表执行UNION来得到最终结果
  - 最好的办法是尽可能将WITH ROLLUP功能转移到应用程序中处理。

4.7.5 优化LIMIT分页

在系统中需要进行分页操作的时候，通常会使用LIMIT加上偏移量的办法来实现，同时加上合适的ORDER BY子句。如果有对应的索引，通常效率会很不错，否则，MySQL需要做大量的文件排序操作。
偏移量非常大的时候优化办法：
- 在页面中限制分页的数量
- 优化大偏移量的性能：
  - 尽可能使用覆盖索引扫描，而不是查询所有的列。例如使用延迟关联
  - 有时可以将LIMIT查询转换为已知位置的查询，让MySQL通过范围扫描获得对应的结果。
  - 使用”书签”记录上一次取数据的位置。
  - 使用预先计算的汇总表，或者关联到一个冗余表，冗余表只包含主键列和需要做排序的数据列。

4.7.6 优化SQL_CALC_FOUND_ROWS

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

设计方案1：将具体的页数换成“下一页”按钮，假设每页显示20条记录，那么使用LIMIT返回21条并只显示20条，如果第21条存在，那么显示“下一页”按钮，否则说明没有更多的数据，也就无须显示
设计方案2：先获取并缓存较多的数据，然后每次分页都从这个缓存中获取。
其他设计方案：有时候也可以考虑使用EXPLAIN的结果中的rows列的值来作为结果集总数的近似值(实际上Google的搜索结果总数也是个近似值)。当需要精确结果时，再单独使用COUNT(*)来满足需求，这时候如果能够使用覆盖索引则通常也会比SQL_CALC_FOUND_ROWS快得多。

4.7.7 优化UNION查询

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

4.7.8 静态查询分析

Percona Toolkit contains pt-query-advisor, a tool that parses a log of queries, analyzes
the query patterns, and gives annoyingly detailed advice about potentially bad practices
in them.

4.7.9 使用用户自定义变量

用户自定义变量是一个用来存储内容的临时容器，在连接MySQL的整个过程中都存在。在查询中混合使用过程化和关系化逻辑的时候，该特性非常有用。

使用方法：

SET @one       := 1;
SET @min_actor := (SELECT MIN(actor_id) FROM sakila.actor);
SET @last_week := CURRENT_DATE - INTERVAL 1 WEEK;
SELECT ... WHERE col <= @last_week;
-- 具有“左值”特性，在给一个变量赋值的同时使用这个变量
SELECT actor_id, @rownum := @rownum + 1 As rownum ...

无法使用的场景：
- 使用自定义变量的查询，无法使用查询缓存
- 不能在使用常量或者标识符的地方使用自定义变量，例如表名、列名和LIMIT子句中。
- 用户自定义变量的生命周期是在一个连接中有效，所以不能用它们来做连接中的通信。
- 如果使用连接池或者持久化连接，自定义变量可能让看起来毫无关系的代码交互(如果是这样，通常是代码或连接池bug)
- 在5.0之前的版本，是大小写敏感的，所以要注意代码在不同MySQL版本间的兼容性问题。
- 不能显示地声明自定义变量的类型。确定未定义变量的具体类型的时机在不同MySQL版本中也可能不一样。如果希望是整形/浮点/字符串，最好初始化时0/0.0/‘’。MySQL的用户自定义变量是一个动态类型，在赋值的时候会改变。
- MySQL优化器在某些场景下可能会将这些变量优化掉，这可能导致代码不按预想的方式运行。
- 赋值的顺序和赋值的时间点并不总是固定的，这依赖于优化器的决定。实际情况可能让人困惑。
- 赋值符号:=的优先级非常低，所以要注意，赋值表达式应该使用明确的括号。
- 使用未定义变量不会产生任何语法错误，如果没注意到这一点，非常容易犯错。

应用场景：

优化排名语句：

-- 查询获取演过最多电影的前10位演员，然后根据出演电影次数做一个排名，如果出演次数一样，则排名相同。
mysql> SET @curr_cnt := 0, @prev_cnt := 0, @rank := 0;
-> SELECT actor_id,
-> @curr_cnt := cnt AS cnt,
-> @rank := IF(@prev_cnt <> @curr_cnt, @rank + 1, @rank) AS rank,
-> @prev_cnt := @curr_cnt AS dummy
-> FROM (
-> SELECT actor_id, COUNT(*) AS cnt
-> FROM sakila.film_actor
-> GROUP BY actor_id
-> ORDER BY cnt DESC
-> LIMIT 10
-> ) as der;

避免重复查询刚刚更新的数据：

-- 在更新行的同时又希望获取得到该行的信息。虽然看起来仍然需要两个查询和两次网络来回，但第二个查询无须访问任何数据表，速度会快很多
UPDATE t1 SET lastUpdated = NOW() WHERE id = 1 AND @now := NOW();
SELECT @now;

统计更新和插入的数量

-- 使用了INSERT ON DUPLICATE KEY UPDATE的时候，想统计插入了多少行的数据，并且有多少数据是因为冲突而改写成更新操作。
-- 实现该办法的本质如下，当每次由于冲突导致更新时对变量@x自增一次，然后通过对这个表达式乘以0来让其不影响要更新的内容
INSERT INTO t1(c1, c2) VALUES(4, 4), (2, 1), (3, 1)
ON DUPLICATE KEY UPDATE
c1 = VALUES(c1) + ( 0 * ( @x := @x +1 ) );

确定取值的顺序

一个最常见的问题，没有注意到在赋值和读取变量的使用可能是在查询的不同阶段。

-- WHERE和SELECT是在查询执行的不同阶段被执行的，而WHERE是在ORDER BY文件排序操作之前执行。
mysql> SET @rownum := 0;
mysql> SELECT actor_id, @rownum := @rownum + 1 AS cnt
-> FROM sakila.actor
-> WHERE @rownum <= 1;
+----------+------+
| actor_id | cnt  |
+----------+------+
| 1        | 1    |
| 2        | 2    |
+----------+------+

尽量让变量的赋值和取值发生在执行查询的同一个阶段。

mysql> SET @rownum := 0;
mysql> SELECT actor_id, @rownum AS rownum
-> FROM sakila.actor
-> WHERE (@rownum := @rownum + 1) <= 1;

将赋值运距放到LEAST()，这样就可以完全不改变排序顺序的时候完成赋值操作。这个技巧在不希望对子句的执行结果有影响却又要完成变量复制的时候很有用。这样的函数还有GREATEST(), LENGTH(), ISNULL(), NULLIF(), IF(), 和COALESCE()。
```
-- LEAST()总是返回0
mysql> SET @rownum := 0;
mysql> SELECT actor_id, first_name, @rownum AS rownum
-> FROM sakila.actor
-> WHERE @rownum <= 1
-> ORDER BY first_name, LEAST(0, @rownum := @rownum + 1);
```

编写偷懒的UNION:

假设需要编写一个UNION查询，其第一个子查询作为分支条件先执行，如果找到了匹配的行，则跳过第二个分支。在某些业务场景中确实会有这样的需求，比如现在一个频繁访问的表中查找“热”数据，找不到再去另外一个较少访问的表中查找“冷数据“。(区分热冷数据是一个很好提高缓存命中率的办法)。

-- 在两个地方查找一个用户，一个主用户表，一个长时间不活跃的用户表，不活跃的用户表的目的是为了实现更高效的归档。
-- 旧的UNION查询，即使在users表中已经找到了记录，上面的查询还是会去归档表中再查找一次。
SELECT id FROM users WHERE id = 123
UNION ALL
SELECT id FROM users_archived WHERE id = 123;
-- 用一个偷懒的UINON查询来抑制这样的数据返回，当第一个表中没有数据时，我们才在第二个表中查询。一旦在第一个表中找到记录，就定义一个变量@found，通过在结果列中做一次赋值来实现，然后将赋值放在函数GREATEST中来避免返回额外的数据。为了明确结果来自哪一个表，新增了一个包含表名的列。最后需要在查询的末尾将变量重置为NULL，保证遍历时不干扰后面的结果。
SELECT GREATEST(@found := −1, id) AS id, 'users' AS which_tbl
FROM users WHERE id = 1
UNION ALL
SELECT id, 'users_archived'
FROM users_archived WHERE id = 1 AND @found IS NULL
UNION ALL
SELECT 1, 'reset' FROM DUAL WHERE ( @found := NULL ) IS NOT NULL;

用户自定义变量的其他用处：
- 不仅是在SELECT语句中，在其他任何类型的SQL语句中都可以对变量进行赋值。例如，可以像前面使用子查询的方式改进排名语句一样来改进UPDATE语句。
- 有时需要使用一些技巧来获得希望的结果。因为优化器可能会把变量当作一个编译时常量来对待，而不是对其进行赋值。将函数放在类似LEAST()这样的函数中通常可以避免这样的问题。另一个办法是在查询被执行前检查变量是否被赋值。
其他用法：
- 查询运行时计算总数和平均值
- 模拟GROUP语句中的函数FIRST()和LAST()
- 对大量数据做一些数据计算。
- 计算一个大表的MD5散列值
- 编写一个样本处理函数，当样本中的数值超过某个边界值的时候将其变为0
- 模拟读/写游标
- 在SHOW语句的WEHRE子句中加入变量值。

4.8 案例学习

4.8.1 使用MySQL构建一个队列表

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

原有处理方式不合适的原因：

随着对列表越来越大和索引深度的增加，找到未处理记录的速度会随之变慢。
一般的处理过程分两步，先找到未处理的记录然后加锁。找到记录会增加服务器的压力，而加锁操作则会让各个消费者进程增加竞争，因为这是一个串行化操作。

优化过程：

将对列表分成两部分，即将已处理记录归档或者存放到历史表，这样始终保证对列表很小。
找到未处理记录一般来说都没问题，如果有问题则可以通过使用消息方式来通知各个消费者。
- 可已使用一个带有注释的SLEEP()函数做超时处理。这让线程一直阻塞，直到超时或者另一个线程使用KILL QUERY结束当前的SLEEP。因此，当再向对列表中新增一批数据后，可以通过SHOW PROCESSLIST，根据注释找到当前正在休眠操作的线程，并将其KILL。可以使用函数GET_LOCK和RELEASE_LOCK()来实现通知，或者可以在数据库之外实现，如使用一个消息服务。
```
SELECT /* waiting on unsent_emails */ SLEEP(10000), col1 FROM table;
```

最后一个问题是如何让消费者标记正在处理的记录，而不至于让多个消费者重复处理一个记录。

尽量避免使用SELECT FOR UPDATE，这通常是扩展性问题的根源，这会导致大量的书屋阻塞并等待。不光是队列表，任何情况下都要避免。

可以直接使用UPDATE来更新记录，然后检查是否还有其他的记录需要处理。(所有的SELECT FOR UPDATE都可以使用类似的方式改写)

-- 该表的owner用来存储当前正在处理这个记录的连接ID，即由函数CONNECTION_ID()返回额ID，如果当前记录没有被任何消费者处理，则该值为0
CREATE TABLE unsent_emails (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -- columns for the message, from, to, subject, etc.
    status ENUM('unsent', 'claimed', 'sent'),
    owner INT UNSIGNED NOT NULL DEFAULT 0,
    ts TIMESTAMP,
    KEY (owner, status, ts)
);
-- 常见的处理办法。这里的SELECT查询使用到索引的两个列，理论上查找的效率应该更快。问题是，两个查询之间的“间隙时间”，这里的锁会让所有其他同一的查询全部被阻塞。所有这样的查询将使用相同的索引，扫描索引相同结果的部分，所以很可能被阻塞。
BEGIN;
SELECT id FROM unsent_emails
    WHERE owner = 0 AND status = 'unsent'
    LIMIT 10 FOR UPDATE;
-- result: 123, 456, 789
UPDATE unsent_emails
    SET status = 'claimed', owner = CONNECTION_ID()
    WHERE id IN(123, 456, 789);
COMMIT;
-- 改进后更高效的写法,无须使用SELECT查询去找到哪些记录还没有被处理。客户端的协议会告诉你更新了几条记录，所以可以直到这次需要处理多少条记录。
SET AUTOCOMMIT = 1;
COMMIT;
UPDATE unsent_emails
    SET status = 'claimed', owner = CONNECTION_ID()
    WHERE owner = 0 AND status = 'unsent'
    LIMIT 10;
SET AUTOCOMMIT = 0;
SELECT id FROM unsent_emails
    WHERE owner = CONNECTION_ID() AND status = 'claimed';
-- result: 123, 456, 789

最后还需处理一种特殊情况：那些正在被进程处理，而进程本身却由于某种原因退出的情况。

只需要定期运行UPDATE语句将它都更新成原始状态，然后执行SHOW PROCESSLIST，获取当前正在工作的线程ID，并使用一些WHERE条件避免取到那些刚开始处理的进程

-- 假设获取的线程ID有(10、20、30),下面的更新语句会将处理时间超过10分钟的记录状态更新成初始状态。
-- 将范围条件放在WHERE条件的末尾，这个查询恰好能勾使用索引的全部列，其它的查询也都能使用上这个索引，这样就避免了再新增一个额外的索引来满足其它的查询
UPDATE unsent_emails
    SET owner = 0, status = 'unsent'
  WHERE owner NOT IN(0, 10, 20, 30) AND status = 'cla
    AND ts < CURRENT_TIMESTAMP - INTERVAL 10 MINUTE;

该案例中的一些基础原则：

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

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

6.8.2 计算两点之间的距离

不建议使用MySQL做太复杂的空间计算存储，PostgreSQL在这方面是一个不错的选择。一个典型的例子是计算以某个点为中心，一定半径内的所有点。例如查找某个点附近所有可以出租的房子，或者社交网站中”匹配“附近的用户。

假设我们有如下表，这里经度和纬度的单位都是度：

CREATE TABLE locations (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(30),
  lat FLOAT NOT NULL,
  lon FLOAT NOT NULL
);
INSERT INTO locations(name, lat, lon)
  VALUES('Charlottesville, Virginia', 38.03, −78.48),
  ('Chicago, Illinois', 41.85, −87.65),
  ('Washington, DC', 38.89, −77.04);

假设地球是圆的，然后使用两点所在最大圆(半正矢)公式来计算两点之间的距离。现有坐标latA和lonA、latB和lonB，那么点A和点B的距离计算公式如下:

ACOS(
COS(latA) * COS(latB) * COS(lonA - lonB)
+ SIN(latA) * SIN(latB)
)

计算的结果是一个弧度，如果要将结果转换成英里或千米，则需要乘以地球的半径。

SELECT * FROM locations WHERE 3979 * ACOS(
  COS(RADIANS(lat)) * COS(RADIANS(38.03)) * COS(RADIANS(lon) - RADIANS(-78.48))
  + SIN(RADIANS(lat)) * SIN(RADIANS(38.03))
) <= 100;

这类查询不仅无法使用索引，而且还会非常消耗CPU时间，给服务器带来很大的压力，而且还得反复计算。

优化地方：

看看是否真的需要这么精确的计算。其实该算法已经有很多不精确的地方：
- 直线距离可能是100英里，但实际上它们之间的行走距离很可能不是这个值。
- 如果根据邮政编码来确定某个人所在的地区，再根据这个地区的中心位置计算他和别人的距离，这本身就是一个估算。
如果不需要太高的精度，可以认为地球是圆的。要想有更多的优化，可以将三角函数的计算放到应用中，而不要在数据库中计算。
看看是否真需要计算一个圆周，可以考虑直接使用一个正方形代替。边长为200英里的正方形，一个顶点到中心的距离大概是141英里，这和实际计算的100英里相差并不太远。根据正方形公式来计算弧度为0.0253(100英里)的中心到边长的距离：
```
SELECT * FROM locations
  WHERE lat BETWEEN 38.03 - DEGREES(0.0253) AND 38.03 + DEGREES(0.0253)
  AND lon BETWEEN −78.48 - DEGREES(0.0253) AND −78.48 + DEGREES(0.0253);
```

现在看看如何用索引来优化这个查询：

增加索引(lat, lon)，这样做的效果并不会很好，因为使用了范围查询。
使用IN()优化。

新增两个列，用来存储坐标的近似值FLOOR()，然后在查询中使用IN()将所有点的整数值都放到列表中:

mysql> ALTER TABLE locations
-> ADD lat_floor INT NOT NULL DEFAULT 0,
-> ADD lon_floor INT NOT NULL DEFAULT 0,
-> ADD KEY(lat_floor, lon_floor);

现在可以根据坐标的一定范围的近似值来搜索，这个近似值包括地板值和天花板值，地理上分别对应的是南北:

-- 查询某个范围的所有点，数值需要在应用程序中计算而不是MySQL
mysql> SELECT FLOOR( 38.03 - DEGREES(0.0253)) AS lat_lb,
-> CEILING( 38.03 + DEGREES(0.0253)) AS lat_ub,
-> FLOOR(-78.48 - DEGREES(0.0253)) AS lon_lb,
-> CEILING(-78.48 + DEGREES(0.0253)) AS lon_ub;
+--------+--------+--------+--------+
| lat_lb | lat_ub | lon_lb | lon_ub |
+--------+--------+--------+--------+
| 36     | 40     | −80    | −77    |
+--------+--------+--------+--------+
-- 生成IN()列表中的整数：
SELECT * FROM locations
  WHERE lat BETWEEN 38.03 - DEGREES(0.0253) AND 38.03 + DEGREES(0.0253)
  AND lon BETWEEN −78.48 - DEGREES(0.0253) AND −78.48 + DEGREES(0.0253)
  AND lat_floor IN(36,37,38,39,40) AND lon_floor IN(-80,-79,-78,-77);

使用近似值会让我们的计算结果有偏差，所以我们还需要一些额外的条件过滤在正方形之外的点，这和前面使用CRC32做哈希索引类似：先建一个索引过滤出近似值，在使用精确条件匹配所有的记录并移除不满足条件的记录。

事实上，到这时就无须根据正方形的近似来过滤数据，可以使用最大圆公式或者毕达哥拉斯定理来计算：

SELECT * FROM locations
  WHERE lat_floor IN(36,37,38,39,40) AND lon_floor IN(-80,-79,-78,-77)
  AND 3979 * ACOS(
  COS(RADIANS(lat)) * COS(RADIANS(38.03)) * COS(RADIANS(lon) - RADIANS(-78.48))
  + SIN(RADIANS(lat)) * SIN(RADIANS(38.03))
) <= 100;

这时计算精度再次回到使用一个精确的圆周，不过现在的做法更快。只要能够高效地过滤掉大部分的点，例如使用近似整数和索引，之后再做精确数学计算的代价并不大。只要不是使用大圆周的算法，否则速度会更慢。

该案例使用的优化策略:

尽量少做事，可能的话尽量不做事。这个案例中就不要对所有的点计算大圆周公式；先使用简单的方案过滤大多数数据，然后再到过滤出来的更小的集合上使用复杂的公式运算。
快速地完成事情。确保在设计中尽可能地让查询都用上合适的索引，使用近似计算来避免复杂计算。
需要的时候，尽可能让应用程序完成一些计算。

4.8.3 使用用户自定义函数

当SQL语句已经无法高效地完成某些任务的时候，如果需要更快的速度，那么C和C++是很好的选择。
案例：需要根据两个随机的64位数字计算它们的XOR值，来看这两个数值是否匹配。大约有3500万条的记录需要在秒级中完成。
- 经过简单的计算就知道，当前的硬件条件下，不可能在MySQL中完成。
- 编写一个计算程序，以一个后台程序的方式运行在同一服务器上，然后编写一个用户自定义函数，通过简单的网络通信协议和前面的程序进行交互。分布式运行该程序，可以达到在130毫秒内完成4百万次匹配计算。通过这样的方式，可以将密集型的计算放到一些通用服务器上，同时对外界完全透明，看起来是MySQL完成了全部的工作。

4.9 总结

如果把创建高性能应用程序比作是一个环环相扣的”难题“，除了前面介绍的schema、索引和查询语句设计之外，查询优化应该是解开”难题“的最后一步。

理解查询是如何被执行的以及时间都消耗在哪些地方，这依然是前面介绍的响应时间的一部分。再加上一些诸如解析和优化过程的知识，就可以额更进一步地理解上一章讨论的MySQL如何访问表和索引的内容了。这也从另一个维度理解MySQL在访问表和索引时查询和索引的关系。

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