为什么查询速度会慢

查询的生命周期: 客户端->服务器->在服务器上进行解析, 生成执行计划->执行, 并返回结果给客户端

查询花费时间的地方: 网络, CPU计算, 生成统计信息和执行计划, 锁(互斥等待)等待, 尤其是想底层存储引擎检索数据的调用操作, 这些调用需要在内存操作, CPU操作和内存不足时导致的I/O操作上消耗时间; 根据存储引擎不同, 可能还会产生大量的上下文切换以及系统调用.

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

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

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

1. 查询不需要的记录. 好的方案是: 加上limit
2. 多表关联时返回全部列. 避免 select *
3. 总是取出全部列. 有时候获取全部列是为了增加复用性，比如缓存所有的数据，但要知道这样做的代价
4. 总是查询相同的数据. 好的方案是: 当初次查询的时候将这个数据缓存起来, 以后从缓存取.

MySQL是否在扫描额外的记录

在确定查询只返回需要的数据后, 接下来看查询为了返回结果是否扫描了过多的数据, 三个指标如下:

• 响应时间
• 扫描的行数
• 返回的行数

响应时间

响应时间 = 服务时间+ 排队时间
服务时间: 数据库处理这个查询真正花了多长时间.
排队时间: 服务器因为等待资源而没有真正执行查询的时间，一般最常见的等待是IO和锁等待.
可以估算查询的响应时间: 了解这个查询需要哪些索引以及它的执行计划是什么, 然后计算所需顺序和随机I/O的次数, 乘以一次I/O的时间, 最后把这些消耗都加起来得到大概参考值, 来评估响应时间是否合理.

扫描的函数和返回的行数

扫描的行数一般不同于返回的行数, 例如做关联查询时服务器必须要扫描多行才能返回一行.

扫描的行数和访问类型

扫描的行数比较:
全表扫描>扫描索引>范围访问>唯一索引查询>常数引用
通过explain命令查看rows可以预估出扫描行数:

image.png

删除索引后, 变为全表扫描:

image.png

• 从数据表中返回数据，过滤不满足条件的记录（extra 出现了 Using Where）。这在Mysql服务器层完成的，先从数据表读出记录然后过滤

如果发现大量的数据但只返回少数的行，通常可以尝试下面的技巧去优化它：
• 使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无须回表获取对应行
• 改变库表结构。例如使用单独的汇总表
• 重写这个复杂的查询，让MYSQL优化器能够以更优化的方式执行这个查询

重构查询的方式

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

目前很多公司都倾向于将复杂查询切分成多个简单查询.

切分查询

删除大量数据, 如果用一条语句一次完成, 可能需要锁住很多数据 占满整个事务日志, 耗尽系统资源, 阻塞很多小的但重要的查询. 用存储过程分批删除就好很多.

image.png

分解关联查询

下面的例子:

image.png

看起来是将一条变为了多条，且结果一致，但优势如下:
• 让缓存的效率更高
• 将查询分解后，执行单个查询可以减少锁的竞争
• 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展
• 查询本身效率有所提升
• 减少冗余记录的查询。在应用层做关联查询，某条记录只需查询一次，而在数据库中关联查询，则可能需要重复地访问一部分数据，重构可能会减少网络和内存的消耗
• 在应用中实现了哈希关联，而不是使用mysql的嵌套循环关联，某些场景哈希关联的效率高很多

查询执行的基础

image.png

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

MySQL客户端和服务器之间的通信协议是 "半双工"的，即Mysql客户端<-->服务器互相发送数据不能同时进行.

此外, MySQL不支持流控, 这意味着:

1. 客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置 max_allowed_packet 参数。但是需要注意的是，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。
2. 相反的，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用 SELECT * 以及加上 LIMIT 限制的原因之一。

客户端一般会缓存整个结果集, 也可以设置客户端参数使其不缓存直接处理结果集, 这可以节省客户端的内存资源, 坏处是导致服务器端查询资源不释放. 一种解决方法是: 服务器端hint SQL_BUFFER_RESULT, 这样服务器端的查询结果会放到一个临时表, 尽快释放表锁. 好处是这样无须在客户缓存结果,减少客户端内存消耗 ,还可以尽早释放对应表锁, 坏处是服务器端将消耗更多的内存.

查询状态

对每个MySQL连接或者说一个线程, 任何时刻都有一个状态, 该状态表示了MySQL目前在作甚吗. 使用SHOW FULL PROCESSLIST命令可以查看当前的状态

查询缓存

查询中即使只有一个字节不同, 也不会匹配缓存结果, MySQL8.0已经去除缓存.

查询优化处理

• 语法解析器和预处理
  通过关键字对sql语句进行解析，生成一颗'解析树'，mysql解析器开始验证关键字或顺序是否正确, 语法是否正确等.
  预处理器会检查数据表和列是否存在, 别名是否有歧义等.

• 查询优化器
  一条查询可以有很多种执行方式，最终都返回相同的结果，优化器的作用就是找到最好的执行计划.
  MySQL使用基于成本的优化器, 可以在查询某条语句后, 通过Last_query_cost评估该查询的成本.
  

  image.png

有很多原因导致MySQL优化器选择错误的执行计划:
• 统计信息不准确。Mysql依赖存储引擎提供信息来评估版本，但有的存储引擎不准确，比如 InnoDB因为期mvcc的架构，并不能维护一个数据表的行数的精确统计信息
• 执行计划中的成本估算不等同于实际执行的成本。比如 Mysql并不知道哪些页面在内存中，哪些在硬盘上
• Msyql认为的最优和我们不同，比如我们认为的是时间最快，mysql可能选择其他最优策略
• msyql不考虑其他并发执行的查询
• Mysql不是任何时候都基于成本的优化，有时也会基于固定的规则. 比如存在全文索引的MATCH（）子句，则存在全文索引就是用全文索引，即使其他索引更快，mysql还是会使用全文索引
• Mysql不会考虑不受其控制的操作成本，例如执行存储或者用户自定义函数的成本
• 优化器可能无法估算所有可能的执行计划，so 可能错过 实际上最优的执行计划

优化策略可以分为两种: 动态优化和静态优化
静态优化不依赖于特别的数值，第一次完成后一直有效，及时不同的参数重复执行查询也不会发生变化，可以认为是编译时优化
动态优化和查询的上下文相关，也可能和和很多其他因素相关，例如where条件中的取值 索引中条目对应的数据行数等等，每次查询的时候重新评估，可以认为这是运行时优化。
Mysql 对查询的静态优化只做一次，动态优化每次执行时都需要重新评估。

Mysql能够处理的优化类型

• 重新定义关联表的顺序
• 将外连接转化成内连接: 有时候外连接和内连接是等价的, mysql可能会重写查询
• 使用等价变换规则: 简化并规范表达式, 如 5=5 and a>5 简写为a>5
• 优化count() min() max(): 比如求最小值，可以直接使用B-Tree数的最左侧，最大就是最右侧的数据, 没有where条件的count(*) 也可以使用存储引擎提供的优化，在EXPLAIN中 可以看到 select tables optimized away, 表示优化器已经从执行计划中移除了该表, 以一个常数取代
• 预估并转化为常数表达式: 当mysql检测到一个表达式可以转化为常数的时候，就会一直把该表达式座位常数进行优化处理
• 覆盖索引扫描 当索引中的列包含我们需要的数据时，可以使用索引返回需要的数据，不用查询对应的数据行
• 提前终止查询 当已满足查询需求时，会提前终止查询，比如limit 或者 id=-1 等这种条件

• 等值传播: MySQL知道这里的WHERE子句不仅适用于film表, 而且对于film_actor表同样适用
  image.png
  
  

  *列表 IN() 的比较: 别的数据库中, IN 和 or 条件一样, 但在msyql中, 会先将数据进行排序, 通过二分查找的方式来确定列表的值是否满足条件, 这是一个O(log n) 的复杂度操作

数据和索引的统计信息

在服务层有查询优化器, 缺没有保存数据和索引的统计信息, 统计信息由存储引擎实现. 因此MySQL查询优化器在生成查询的执行计划时, 需要向存储引擎获取相应的统计信息.

MySQL如何执行关联查询

关联策略：用一个表中的一条，依次去遍历另外一张表的全部数据，如此循环，直到找到所有数据，叫嵌套循环关联，如:

image.png

本质上, MySQL对所有类型的查询都是上述方式, 即使子查询或者UNION也是:
MySQL在from子句中遇到子查询时, 先执行子查询并将其结果放到一个临时表中, 然后将这个临时表当做一个普通表对的. UNION查询也使用类似的临时表. 右外连接, MySQL改写为等价的左外连接.

执行计划

Mysql不会生成查询字节码来执行查询，Mysql生成查询的一颗指令树，通过执行引擎完成这课指令书并返回结果。对某个查询执行 EXPLAIN EXTENDED后，再执行SHOW WARNINGS就可以看到重构出的查询语句

image.png

关联查询优化器

关联查询优化器通过评估不同顺序时的成本选择一个代价最小的关联顺序，可以使用STRAIGHT_JOIN 关键字重写查询。当关联的表过多时，搜索空间的增持速度非常快，优化器不可能逐一评估每种关联顺序的成本，这叫做"贪婪"搜索方式,可能不是最优的.

排序优化

当不能使用索引生成排序结果时, mysql需要自己排序, 如果数据量小则在内存中进行, 数据量大就需要用磁盘. 不过这2种方式MySQL统一称为文件排序(filesort).

更具体的, 如果所需排序的数据量小于"排序缓冲区", MySQL使用内存进行"快速排序". 若内存不够用, 则MySQL先将数据分块, 对每个独立的块使用"快速排序", 然后各个块的排序结果放到磁盘上, 然后对排好序的块进行合并(merge), 最后返回排序结果.

Mysql有如下两种排序算法
• 两次传输排序(旧版本使用)
读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行，但可能产生大量随机I/O,所以两次数据传输的成本非常高，优点是在排序的时候存储尽可能少的数据.
• 单次传输排序（新版本使用4.1后）
直接读取查询所需要所有列，再根据给定列进行排序，直接返回结果，优点是只需要一次顺序I/O, 无须随机IO, 缺点是如果需要的列很多，就会额外占用大量的空间.

关联查询时的排序, 若所有列都来自关联的第一个表, 则效率会高些.