从SQL Server到MySql(8): Mysql 的查询执

1. 客户端/服务器通信协议

• "半双工的": 同一时刻只能发生一个方向的数据发送.
  • 优势是简单快速
  • 限制: 无法进行流量控制.
    • 客户端用一个单独的数据包将查询传递给服务器, 接着就只能等待结果, 且必须完整的接收整个返回结果. 而不能在中途停止数据接收.
    • 所以, max_allowed_packet 参数, 以及查询语句中的limit 限制是特别重要的.
• Mysql 要等所有的数据都已经发送给客户端时才能释放这条查询锁占用的资源.
  • 接收全部结果并缓存
    • 能够让查询早点结束, 减少服务器压力.
    • 但当结果集很大时,会耗费更多的时间和内存.
  • 尽早开始处理结果集, 逐行获取需要的数据
    • 节省库函数处理查询所需的内存和时间.
    • 缺陷是会在和客户端交互的整个过程中占用服务器资源.
• Mysql 连接(线程) 的状态
  • sleep, query, locked, analyzing and statistics, coping to tem table, sorting table, sending data.

2. 查询缓存

• 在解析查询语句之前, 如果查询缓存是打开的, Mysql 会优先检查该查询是否命中了查询缓存中的数据.
  • 检查是通过一个对大小写敏感的哈希查找实现的.
  • 若有命中, 再检查用户权限.
  • 如果都没问题, 跳过后续阶段, 直接从缓存中拿结果并返回给客户端.

3. 查询优化处理

• 过程: 将SQL 转换成一个执行计划, 再依照该执行计划和存储引擎进行交互.
  • 子阶段: 解析SQL, 预处理, 优化SQL 执行计划.
  • 过程中任何错误都可能终止查询.
• 语法解析器和预处理
  • 解析器生成一颗"解析树".
  • 预处理器根据Mysql 规则进一步检查解析树是否合法.
• 查询优化器.

  • 一个查询有多种执行方式, 最后都返回相同的结果.

  • 优化器会从中找出最好的执行计划.

  • Mysql 使用基于成本的优化器. 评估成本时不考虑任何层面上的缓存.

    • 导致优化器选择错误的原因:
      • 统计信息不准确.
      • 成本估算不等于实际的执行成本(访问数据的成本不同).
      • 有时无法估算所有可能的执行计划.
      • 不会考虑不受其控制的操作的成本. 如执行存储过程和用户自定义函数的成本.
      • 有时是基于固定的规则而非成本进行的优化. 如存在Match()就一定会使用全文索引,即使别的索引会更快.
      • Mysql 的最优可能不是我们想要的最优. 我们想要的是最快的,而Mysql 只是基于成本上的最优.

  • Mysql 能够处理的优化类型

    • 重新定义关联表的顺序.
    • 将外连接转换为内连接.
    • 使用等价变化规则.
    • 优化Count, Min, Max 函数.
    • 预估并转换为常数表达式.
    • 覆盖索引扫描.
    • 子查询优化.
    • 提前终止查询.
    • 等值传播.
    • 列表IN() 的比较.
      • 多数数据库的In() 完全等同于多个OR 条件的子句, 其复杂度为O(n).
      • Mysql 会先排序,再二分查找确定列表值是否满足条件. 复杂度为O(log n).

  • �大多数情况下, 让优化器按自己的方式工作. 除非发现它进行了错误的优化, 并且知道原因时,再进行手工干预.

  • 数据和索引的统计信息.

    • 优化器存在于服务层, 而统计信息是由存储引擎构建并传递给优化器的.

  • Mysql 的关联查询.

    • Mysql 的概念中, 每个查询都是一次关联.
    • 会将一系列的单个查询结果放入一个临时表中,然后执行"嵌套循环关联" .

  • select t1.c1, t2.c2 from t1 
    inner join t2 Using (c3) where ...; 
    

// **************会被转换为下述的类似代码执行******************
outer_iter = iterator over t1 where...
outer_row = outer_iter.next
while outer_row
inner_iter = iterator oever t2 where ....
inner_row = inner_iter.next
while inner_row
....
inner_row = inner_iter.next
end
outer_row = outer_iter.next
end

    * 本质上, 所有类型的查询都以此类方式运行. 
    * `但全外连接是个例外, 因为它无法通过嵌套循环和回溯的方式完成. 所以Mysql 并不执行全外连接.`
      * full join :表中数据=内连接+左边缺失数据+右边缺失数据.
  * 执行计划
    * Mysql 并不会生成查询字节码来执行查询, 而是生成查询的一颗指令树, 然后通过存储引擎执行完成这颗指令数并返回结果. 
    * 最终的执行计划, 包含了重构查询的所有信息.
  * 关联查询优化器
    * 由于Mysql 嵌套循环方式的关联查询执行方式, 所以关联顺序变得非常重要.
    * 关联优化器会尝试在所有的关联顺序中选择一个成本最小的执行.
      * 但是, 当管理表过多时, 只能使用"贪婪" 搜索方式查找最优值.
      * 当10个表进行关联时,. 一共有3628800种不同的关联顺序.
  * 排序优化
    * 当无法使用索引进行排序时, Mysql 需要进行排序, 成为文件排序(可能在内存,硬盘中进行,根据数据量大小).
      * 内存不够时, 将数据分块, 对每个块使用"快速排序"并将结果存放在磁盘上, 最后进行merge.
    * 两种排序算法.
      * 两次传输排序(旧版本)
        * 读取行指针和需要排序的字段, 对其进行排序. 然后再更加排序结果读取所需要的数据行.
      * 单次传输排序(新版本)
        * 先读取查询需要的所有列, 然后再根据给定列进行排序, 最后直接返回排序结果.
    * `Mysql 进行文件排序时, 所需要使用的临时存储空间可能很大. 因�其对每个排序记录都会分配一个足够长的定长空间存放.`

### 4  查询执行引擎
* 经过了解析和优化阶段, 会生成执行计划(数据结构而非字节码). 查询执行引擎则根据这个执行计划来完成整个查询.
* 过程: 简单地根据执行计划给出的指令逐步执行.

### 5 返回结果给客户端
* 即使没有结果数据, 也会返回一些查询信息,如影响的行数.
* `增量, 逐步返回的过程. �开始生成第一条结果时,就逐步地进行返回.`
* 好处: 服务器无需存储太多结果. 让客户端尽快的得到了结果.