《高性能MySQL》读书笔记查询性能优化

一条SQL语句生命周期为：从客户端发送到服务端==》服务端进行语法分析==》生成执行计划==》执行==》给客户端返回结果

执行是整个过程中最主要的阶段，包含对存储引擎的调用获取数据以及调用后的数据处理，如排序、分组等

SQL语句的在执行主要会会受到CPU、内存、统计信息、执行计划、锁等待等因素的影响

那么SQL语句的优化就是分析出哪些因素导致时间消耗过多，并进行解决

一般来说，SQL****语句性能差主要是由于访问数据太多，大部分性能低下的查询都可以通过减少访问数据量的方式进行优化，可以通过两个步骤进行分析：

• 确定应用程序是否在检索大量且不必要的数据

  • 比如：

  • 查询多条数据但是分页显示只显示一部分数据

  • 返回全部的列

  • 重复查询相同的列（解决：缓存）

• 确定MySQL服务器层是否在分析大量不需要的数据行

⭐⭐⭐通过衡量查询开销的指标进行分析：

• 响应时间：服务时间（真正执行SQL语句的时间） + 排队时间（等待资源而没有在执行的时间）
  • 但是目前没办法将响应时间细分在这两部分时间上。判断响应时间是不是一个合理的值目前可以通过：了解这个查询需要哪些索引以及它的执行计划是什么，然后计算大概需要多少个顺序和随机I/O，再用其乘以在具体硬件条件下一次I/O的消耗时间。最后把这些消耗都加起来，就可以获得一个大概参考值来判断
• 扫描的行数和返回的行数
• 返回的行数和访问类型：explain中的type列表示访问类型，如全表扫描、范围扫描、索引扫描等

查询的条件能否使用到索引对于SQL的性能来说至关重要，从好到坏的有以下的方式：

• 在索引中使用where条件过滤不匹配的记录（存储引擎层完成）
• 使用索引覆盖扫描返回记录，直接在索引中过滤不需要的记录并返回命中的结果（服务层完成，但是后面有了索引下推后可以在存储引擎层完成）
• 从数据表中返回数据，然后过滤掉不满足的条件（服务层完成）

如果发现查询需要扫描大量的行但只返回少数行时，可以通过以下方法去优化：

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

优化有问题的查询时，可以将查询转换一种写法让其返回一样的结果，比如：

• 一个复杂的查询分成多个简单的查询
• 切分查询：对于一个大查询，我们需要“分而治之”，将大查询切分成小查询
  • 比如定期清除大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的DELETE语句切分成多个较小的查询可以尽可能小地影响MySQL的性能，同时还可以降低MySQL复制的延迟
• 分解联接查询：对每一个表进行一次单表查询，然后将结果在应用程序中进行联接

  • 优点：

  • 让缓存的效率更高

  • 执行单个查询可以减少锁的竞争

  • 在应用层做联接可以更容易对数据库进行拆分，更容易做到高性能和可扩展

  • 查询本身的效率也会提升

  • 减少对冗余记录的访问：应用层做联接查询，意味着对于某条记录应用只需要查询一次，而在数据库中做联接查询，则可能需要重复地访问一部分数据

对于一个MySQL连接，或者一个线程，任何时刻都有一个状态，该状态表示了MySQL当前正在做什么。可以使用**SHOW FULL PROCESSLIST**命令查看当前的状态：

• sleep：线程正在等待客户端发送新的请求
• query：线程正在执行查询或正在将结果发送给客户端
• locked：在MySQL服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中
• Analyzing and statistics：线程正在检查存储引擎的统计信息，并优化查询
• Copying to tmp table [on disk]：线程正在执行查询，并且将其结果集复制到一个临时表中。一般是在执行GROUP BY / 排序 / UNION操作。如果这个状态后面还有“on disk”标记，那表示MySQL正在将一个内存临时表放到磁盘上
• Sorting result：线程正在对结果集进行排序。

可以通过状态分析当前MySQL是否有异常

查询优化器

用于找到最好的执行计划

MySQL使用基于成本的优化器，会预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。最初，成本的最小单位是随机读取一个4KB数据页的成本，后来成本计算公式变得更加复杂，并且引入了一些“因子”来估算某些操作的代价，如执行一次WHERE条件比较的成本。可以通过查询当前会话的Last_query_cost的值来得知MySQL计算的当前查询的成本

成本是根据一系列的统计信息计算得来的：每个表或者索引的页面个数、索引的基数（索引中不同值的数量）、索引和数据行的长度、索引分布情况。优化器在评估成本的时候并不考虑任何层面的缓存带来的影响，它假设读取任何数据都需要一次磁盘I/O

会有很多种情况导致优化器选择错误的执行计划：

• 统计信息不准确
• 成本指标并不完全等同于运行查询的实际成本。例如，有时候某个执行计划虽然需要读取更多的页面，但是它的成本却更低。因为如果这些页面都是顺序读或者这些页面都已经在内存中的话，那么它的访问成本将很低。因为MySQL并不知道哪些页面在内存中、哪些在磁盘中，所以查询在实际执行过程中到底需要多少次物理I/O是无法得知的
• MySQL的最优可能和你想的最优不一样。你可能希望执行时间尽可能短，但是MySQL只是基于其成本模型选择最优的执行计划，而有些时候这并不是最快的执行方式
• MySQL从不考虑其他并发执行的查询，这可能会影响到当前查询的速度
• MySQL并不是任何时候都是基于成本的优化。有时也会基于一些固定的规则，例如，如果存在全文搜索的MATCH()子句，则在存在FULLTEXT索引的时候就使用全文索引。即使有时候使用其他索引和WHERE条件可以远比这种方式要快，MySQL也仍然会使用对应的全文索引
• MySQL不会考虑不受其控制的操作的成本，例如，执行存储函数或者用户自定义函数的成本
• 优化器有时候无法估算所有可能的执行计划，所以可能错过实际上最优的执行计划

⭐⭐⭐MySQL能够处理的优化类型：

• 重新定义联接表的顺序
• 将外联转换为内联
• 使用代数等价变换规则：MySQL可以使用一些代数等价变换规则来简化并规范表达式。它可以合并和减少一些比较，还可以移除一些恒成立和一些恒不成立的判断
• 优化count()\min()\max()：索引和列是否可为空通常可以帮助MySQL优化这类表达式。例如，要找到某一列的最小值，只需要查询对应B-tree索引最左端的记录，MySQL可以直接获取索引的第一行记录。在B-tree索引中，优化器会将这个表达式作为一个常数对待。如果MySQL使用了这种类型的优化，那么在EXPLAIN中就可以看到“Select tables optimized away”，表示优化器已经从执行计划中移除了该表，并以一个常数代替
• 预估并转换为常数表达式：当MySQL检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理
• 索引覆盖扫描
• 子查询优化：MySQL在某些情况下可以将子查询转换为一种效率更高的形式，从而减少多个查询多次对数据进行访问
• 提前终止查询：在发现已经满足查询需求的时候，MySQL总是能够立刻终止查询。比如limit时
• 等值传播：如果两列的值可通过等式联接，那么MySQL能够把其中一列的WHERE条件传递到另一列上
• 列表IN()的比较：在很多数据库服务器中，IN()完全等同于多个OR条件的子句，因为这两者是完全等价的。在MySQL中这点是不成立的，MySQL将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个O(logn)复杂度的操作，等价地转换成OR查询的复杂度为O(n)，对于IN()列表中有大量取值的时候，MySQL的处理速度将会更快
• ……

联接查询优化器

MySQL****的联接执行策略：MySQL对任何联接都执行嵌套循环联接操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。最后根据各个表匹配的行，返回查询中需要的各列。MySQL会尝试在最后一个联接表中找到所有匹配的行，如果最后一个联接表无法找到更多的行，MySQL返回到上一层次的联接表，看是否能够找到更多的匹配记录，依此类推，迭代执行。在MySQL 8.0.20版本之后，已经不再使用基于块的嵌套循环联接操作，取而代之的是哈希联接。这让联接操作性能变得更好，特别是当数据集可以全部存储在内存时。

联接优化器会尝试在所有的联接顺序中选择一个成本最低的来生成执行计划树：

如果可能，优化器会遍历每一个表，然后逐个做嵌套循环，计算执行每一棵可能的计划树的成本，最后返回一个最优的执行计划

不过，糟糕的是，n个表的联接可能有n的阶乘种联接顺序，我们称之为所有可能的查询计划的“搜索空间”

当搜索空间非常大的时候，优化器不可能逐一评估每一种联接顺序的成本。这时，优化器选择使用“贪婪”搜索的方式查找“最优”的联接顺序

当需要联接的表超过optimizer_search_depth的限制的时候，就会选择“贪婪”搜索模式了（optimizer_search_depth参数可以根据需要指定大小）。在MySQL这些年的发展过程中，优化器积累了很多“启发式”的优化策略来加速执行计划的生成

⭐⭐⭐排序优化

如果需要排序的数据量小于“排序缓冲区”，MySQL使用内存进行快速排序操作。如果内存不够排序，那么MySQL会先将数据分块，对每个独立的块使用“快速排序”进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排好序的块进行合并(merge)，最后返回排序结果

MySQL****有两种排序算法：

• 两次传输排序（旧版本）

  • 读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行

  • 需要进行两次数据传输，即需要从数据表中读取两次数据，第二次读取数据的时候，因为是读取排序列进行排序后的所有记录，这会产生大量的随机I/O，所以两次传输排序的成本非常高

• 单次传输排序（新版本）

  • 先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果

  • 只需要一次顺序I/O就可读取所有的数据，而无须任何的随机I/O

  • 可能占用更多空间，因为会保存查询中每一行所需要的列。这意味着更少的数据可以放入排序缓冲区，使得文件排序(filesort)操作必须执行更多的排序合并过程。

如果联接查询种需要排序：

• 如果ORDER BY子句中的所有列都来自联接的第一个表，那么MySQL在联接处理第一个表的时候就进行文件排序。在MySQL的EXPLAIN结果中可以看到Extra字段会有“Using filesort”字样
• 除此之外的所有情况，MySQL都会先将联接的结果存放到一个临时表中，然后在所有的联接都结束后，再进行文件排序。在这种情况下，在MySQL的EXPLAIN结果的Extra字段可以看到“Using temporary；Using filesort”字样。如果查询中有LIMIT的话，LIMIT也会在文件排序之后应用，所以即使需要返回较少的数据，临时表和需要排序的数据量仍然会非常大

查询优化器的局限性

UNION的限制

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

如果希望UNION的各个子句能够根据LIMIT只取部分结果集，或者希望能够先排好序再合并结果集的话，就需要在UNION的各个子句中分别使用这些子句

例如：

想将两个子查询结果联合起来，然后再取前20条记录，那么MySQL会将两个表存放到同一个临时表中，然后再取出前20行记

可以通过在UNION的两个子查询中分别加上一个LIMIT来减少临时表中的数据

但是从临时表中取出数据的顺序并不是一定的，所以如果想获得正确的顺序，还需要在最后的LIMIT操作前加上一个全局的ORDER BY操作

等值传递

某些时候，等值传递会带来一些意想不到的额外消耗。例如，考虑一列上的巨大IN()列表，优化器知道它将等于其他表中的一些列，这是由于WHERE、ON或USING子句使列彼此相等。优化器通过将列表复制到所有相关表中的相应列来“共享”列表。通常，因为各个表新增了过滤条件，所以优化器可以更高效地从存储引擎过滤记录。但是如果这个列表非常大，则会导致优化和执行都会变慢。在写作本书的时候，除了修改MySQL源代码，目前还没有什么办法能够绕过该问题（不过这个问题很少会碰到）

并行执行

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

在同一个表中查询和更新

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