MySQL 查询优化器与 SQL 调试 （一）

1、MySQL 查询优化器与 SQL 调试 （一）

要想写出更好的 SQL，一些基础概念和 SQL 调试是必不可少的。下面我们来看下查询优化器给我们做了哪些优化，执行器真正执行的 SQL 语句是什么。

首先了解一些基础的概念。

1.1、MySQL 客户端与服务端的通信协议

MySQL 客户端与服务端的通信协议为 “半双工”，也就是在任何一个时刻，只能由服务端发送数据到客户端，或者反之，两个动作不能同时发生。这就导致了没法进行流量控制，一旦一端开始发送消息，另外一端要完整接收消息才能响应对方。如果发送的数据太长，MySQL 会拒绝接收更多的数据直接抛出异常，这时候可以通过设置 ‘max_allowed_packet’ 来调节可以发送的数据量大小，单位是 Byte。

而 MySQL 客户端接收服务端发送的数据可能由多个数据包组成。在接收数据的过程看起来像是客户端主动去拉数据，但实际上是服务端主动去推送数据到客户端，客户端无法主动的让服务端停止下来，只有当所有数据发送给客户端后才会释放这条查询所占用的资源。

1.2、查询 MySQL 服务端所有连接线程状态

对于客户端和服务端的通信都会通过线程去操作，而每个线程都会有属于它的状态，我们可以使用 show processlist 去查看相应线程的连接信息。

• Id

连接标识符。这个值来自于 INFORMATION_SCHEMA 数据库的 PROCESSLIST 表的 ID 值，通过 CONNECTION_ID() 函数返回。

• User

连接的用户（用户名）。system user 的值是指服务器生成的非客户端线程，用于处理内部的任务。比如：延迟行处理程序线程或副本主机上使用的 I/O 或 SQL 线程。对于 system user， Host列中未指定主机。unauthenticated user 指的是与客户端已经连接但是还未身份认证的线程。event_scheduler指的是监控调度事件的线程。

• Host

主机名（连接地址）（system user 没有主机的除外）。以 TCP/IP 连接的主机名通过host_name:client_port格式去显示更容易知道客户端正在做什么。

• db

线程默认的数据库，没有选择则显示 NULL。

• Command

客户端执行命令的类型，如果是 Sleep 则表示是空闲状态。

更多参数值请查阅官方文档：https://dev.mysql.com/doc/refman/8.0/en/thread-commands.html

• Time

线程处于当前状态的时间（以秒为单位）。对于 replica 线程，该值是最后一次同步事件到 replica host 之间的毫秒数。

• State

动作、事件或者状态。大多数状态流转的速度是非常快的，如果线程一直停留在一个状态，那么应该检查一下当前状态是否出现什么问题了。

更多状态值请查询官方文档：https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html

• Info

线程正在执行的语句，如果没有执行任何语句的话就是 NULL。

show processlist 只能查看 info 当中的前 100 个字符，如果想要查看完整的字符，请使用 show full processlist。

1.4、查询优化器

MySQL 使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。

如何查询 SQL 的执行成本呢？

我们可以使用 explain + FORMAT=JSON 的方式查看具体的执行计划：

explain FORMAT=JSON select * from alarm_record


{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.35"
    },
    "table": {
      "table_name": "alarm_record",
      "access_type": "ALL",
      "rows_examined_per_scan": 1,
      "rows_produced_per_join": 1,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.25",
        "eval_cost": "0.10",
        "prefix_cost": "0.35",
        "data_read_per_join": "2K"
      },
      "used_columns": [
        "id",
        "product_id",
		"..."
      ]
    }
  }
}

其中 query_cost 就是总的查询成本，大概要读 0.35 个数据页，而数据页的默认大小是 16 KB。

而导致 MySQL 选择错误的执行计划可能有以下原因（不一样全）：

• 统计信息不准确。

MySQL 依赖存储引擎提供的统计信息来估计成本，但是有的引擎给出的统计成本不一定准确，可能误差会比较大。

• 执行计划中的成本不等同于实际的执行成本。
• MySQL 中的最优可能不是我们想象中的速度最快。

因为 MySQL 只是基于其成本模型选择的最优执行计划，有时候选择的不一定是最快的。

• MySQL 从不考虑其他并发执行的查询，这可能会影响到当前的查询速度。
• MySQL 也不是任何时候都是基于成本的优化。
• MySQL 不会考虑不受其控制的操作成本。

1.4.1、查询优化器做了哪些优化

有以下部分优化内容：

• 重新定义关联表顺序

MySQL 会根据相关的嵌套循环算法找到扫描行数更少的表去重新定义关联顺序。但是有时候优化器给出的不是最佳的关联顺序，如果有超过 n 个表的关联，优化器需要检查 n 的阶乘种关联顺序，当这个变得非常大的时候，优化器不可能去检查每一种的关联成本，这时候优化器会选择 “贪婪” 搜索的方式查找 “最优” 的关联顺序。

当关联的表超过 optimizer_search_depth 配置的时候，就会选择 “贪婪” 的搜索模式了（show VARIABLES like 'optimizer_search_depth'）。

• 将外连接转换成内连接
• 使用登记变换规则

MySQL 会合并比较、移除一些恒成立和恒不成立的判断。

• 优化 count()、min()和max()

想要找到某一列的最小值，只需要查询对应 B-Tree 索引的最左端的记录，优化器在优化的时候会将这个表达式当成一个常数对待。如果 MySQL 使用了这种类型的优化，在 explain 中可以看到 “select tables optimized away”。

• 预估并转化为常数表达式
• 覆盖索引扫描
• 子查询优化

MySQL 某些情况可以将子查询转换成一种效率更高的形式，从而减少多个查询多次对数据进行访问。

• 提前终止查询

在发现已经满足查询需求的时候，MySQL 总能立即终止查询。

• 等值传播
• 列表 in() 的比较

MySQL 会将 in 列表中的数据先进行排序，然后通过二分查找来确定列表中的值是否满足条件，对于 in 列表中有大量取值的时候，MySQL 的处理速度会更快。

参考文档

• 高性能 MySQL 第三版
• MySQL 8.0 官方文档

我正在参与2023腾讯技术创作特训营第三期有奖征文，组队打卡瓜分大奖！