image.png

image.png

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

MySQL 客户端和服务器之间的通信协议是 “半双工” 的.

效果像是 HTTP 那种请求响应模式.

缺点:

  • 无法进行流量控制, 客户端必须完整地接收整个返回结果

查询状态

SHOW FULL PROCESSLIST 命令返回结果中的 Command 列表示当前 MySQL 连接/线程的状态.

状态解释:

image.png
image.png

6.4.2 查询缓存

6.4.3 查询优化处理

语法解析器和预处理

查询优化器

获取当前查询的成本:

image.png

有很多原因会导致 MySQL 优化器选择错误的执行计划:

image.png
image.png

优化策略的简单分类:

  • 静态优化, 代数变换
  • 动态优化, 考虑上下文

一些 MySQL 能够处理的优化类型:

  • 重新定义关联表的顺序
  • 将外连接转化成内连接
  • 使用等价变换规则
  • 优化 COUNT(), MIN(), MAX()
  • 预估并转化为常数表达式
  • 覆盖索引扫描
  • 子查询优化
  • 提前终止查询
  • 等值传播

不要自以为比优化器更聪明.

数据和索引的统计信息

MySQL 服务器层不存储统计信息, 交给存储引擎器实现.

MySQL 如何执行关联查询

  • MySQL 中每次查询都是一次 join, 而不仅仅出现两个表才 join
  • 关联策略是执行嵌套循环

示例:

image.png

伪代码:

image.png

改为外连接:

image.png

伪代码:

image.png
image.png

泳道图:

image.png

  • 临时表上是没有索引的
  • 全外连接无法通过嵌套循环和回溯方式完成

执行计划

多表查询可以使用一棵树表示:

image.png

MySQL 使用嵌套循环完成关联:

image.png

关联查询优化器

决定多个表关联时的顺序.

image.png

image.png

使用 STRAIGHT_JOIN 对比:

image.png

当关联的表过多时, MySQL 使用贪婪搜索的方式查找最优的关联顺序.

  • 关联表数量配置 optimizer_search_depth

启发式优化策略用于加速执行计划的生成.

排序优化

排序是个成本很高的操作.

  • MySQL 统一将内存排序和磁盘排序称为文件排序
  • 快速排序
  • 合并

排序算法:

  • 两次传输排序, 读取行指针和需要排序的字段 (先排序后回表查所需要的列)
  • 单次传输排序, 先读取所需要的列, 然后根据给定列进行排序, 最后直接返回结果

当查询需要所有列的总长度不超过 max_length_for_sort_data 时, MySQL 使用单次传输排序.

image.png

6.4.4 查询执行引擎

为了执行查询, MySQL 只需要重复执行计划中的各个操作, 直到完成所有的数据查询.

6.4.5 返回结果给客户端

  • MySQL 将结果集返回客户端是一个增量, 逐步返回的过程