WhereFieldin(...)是怎么执行的？

我们日常写 SQL 时，子查询应该算是常客了。MySQL 为子查询执行准备了各种优化策略，接下来我会写子查询各种优化策略是怎么执行的系列文章。

创新互联长期为近千家客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为通江企业提供专业的成都做网站、网站设计、外贸营销网站建设，通江网站改版等技术服务。拥有十年丰富建站经验和众多成功案例,为您定制开发。

本文以包含最简单的 in 条件的查询入手，介绍 where field in (8,18,88,...) 这种值都是常量的 in 条件是怎么执行的。

这虽然不是子查询，我们就把它当成子查询的邻居好了，用它作为子查询系列的开篇，算是离子查询又近了一步 ^_^。

本文内容基于 MySQL 8.0.29 源码。

正文

1、概述

MySQL 为了能让 SQL 语句执行得更快一点，费尽心思进行了各种优化。

where field in (8,18,88,...) 这种值都是常量的 in 条件，看起来已经是最简单的形式了，执行过程似乎也没有什么可以优化的，但 MySQL 还是对它进行了优化。

这种 in 条件会有 2 种执行方式：

  • 二分法查找
  • 循环比较

MySQL 会优先使用二分法查找方式执行，如果不满足条件，再退而使用循环比较方式。

2、二分法查找

判断 in 条件括号中的值和记录字段值是否匹配，相比于循环比较方式，二分法查找把时间复杂度从 O(N) 降为 O(logN)，大大减少了需要比较的次数，提升了 SQL 的执行效率。

（1）构造二分法查找数组

二分法查找虽好，但需要满足一定条件才能使用：

  • in 条件括号中的所有值都是常量，也就是说不能包含任何表中的字段、也不能包含系统变量（如 @@tmp_table_size）或自定义变量（如 @a），总之是不能包含任何可以变化的东西。
  • in 条件括号中所有值的数据类型必须相同。举个反例：where field in (1,8,'10') 这种既包含整数又包含字符串的 in 条件就是不行的。
  • in 条件括号中所有值的类型，以及字段本身的类型都不能是 json。

如果以上 3 个条件都满足，就具备使用二分法查找的基础了。

接下来我们看看判断上述 3 个条件是否满足的主要流程：

第 1 步，循环 in 条件括号中的每个值，看看是否都是常量，只要碰到一个不是常量的值，就结束循环。

bool Item_func_in::resolve_type(THD *thd){
  ......
  // 判断 in 条件字段本身是不是 json 类型
  bool compare_as_json = (args[0]->data_type() == MYSQL_TYPE_JSON);
  ......
  bool values_are_const = true;
  Item **arg_end = args + arg_count;
  for (Item **arg = args + 1; arg != arg_end; arg++) {
    // 判断 in 条件括号中的值是不是 json 类型
    compare_as_json |= (arg[0]->data_type() == MYSQL_TYPE_JSON);
    // 判断 in 条件括号中的值是不是常量
    if (!(*arg)->const_item()) {
      // in 条件括号中的值，只要有一个不是常量，就记录下来
      values_are_const = false;
      // @todo - rewrite as has_subquery() ???
      if ((*arg)->real_item()->type() == Item::SUBSELECT_ITEM)
        dep_subq_in_list = true;
      // 然后结束循环
      break;
    }
  }
  ......
}

上面代码里面还干了另一件事，就是判断 in 条件括号中的所有值，以及 in 条件字段是不是 json 类型。

args[0] 表示 in 条件字段，args[1~N] 是 in 条件括号中的值。

第 2 步，计算 in 条件括号中的所有值总共有几种数据类型。

bool Item_func_in::resolve_type(THD *thd){
  ......
  uint type_cnt = 0;
  for (uint i = 0; i <= (uint)DECIMAL_RESULT; i++) {
    if (found_types & (1U << i)) {
      (type_cnt)++;
      cmp_type = (Item_result)i;
    }
  }
  ......
}

第 3 步，基于前两步的结果，综合起来判断是否满足二分法查找的 3 个前提条件。

bool Item_func_in::resolve_type(THD *thd){
  ......
  /*
    First conditions for bisection to be possible:
     1. All types are similar, and
     2. All expressions in  are const
     3. No JSON is compared (in such case universal JSON comparator is used)
  */
  bool bisection_possible = type_cnt == 1 &&     // 1
                            values_are_const &&  // 2
                            !compare_as_json;    // 3
  ......
}

判断是否满足二分法查找的 3 个前提条件，逻辑就是上面这些了，不算太复杂。

MySQL 对于 where row(filed1,field2) in ((1,5), (8,10), ...) 这种 row 类型的 in 条件也会尽量使用二分法查找，本文内容不会涉及这些逻辑。

（2）填充数组并排序

要使用二分法查找，只满足 3 个前提条件不算完事，还要求 in 条件括号中的值必须是已经排好序的，接下来就该再往前推进一步了，那就是对 in 条件括号中的值进行排序。

排序流程分为 2 步：

第 1 步，依然是用一个循环，把 in 条件括号中的每个值都依次加入到数组中。

第 2 步，所有值都加入数组之后，对数组元素进行排序。

// items 就是用于存放 in 条件括号中所有值的数组
bool in_vector::fill(Item **items, uint item_count){
  used_count = 0;
  for (uint i = 0; i < item_count; i++) {
    // in 条件括号中的值加入数组
    set(used_count, items[i]);
    /*
      We don't put NULL values in array, to avoid erroneous matches in
      bisection.
    */
    // include this cell in the array.
    if (!items[i]->null_value) used_count++;
  }
  assert(used_count <= count);
  // 对数组元素进行排序
  resize_and_sort();

  // True = at least one null value found.
  return used_count < item_count;
}

不知道大家有没有这样的疑问：如果 in 条件括号中存在重复值，MySQL 会对数组中的元素去重吗？

答案是：MySQL 只会把 in 条件括号中的值原样加入数组，不会对数组中的元素去重。

到这里，使用二分法查找的准备工作都已完成，这些准备工作都是在查询准备阶段进行的。

（3）判断记录是否匹配 in 条件

server 层每从存储引擎读取到一条记录，都会判断记录是否和 in 条件匹配。

有了前面构造的有序数组，判断是否匹配的逻辑就很简单了，就是从读取出来的记录中拿到 in 条件字段的值，然后用有序数组进行二分法查找。

如果找到了，就说明记录和 in 条件匹配。

以 in 条件括号中所有值都是整数为例，二分法查找代码如下：

bool in_longlong::find_item(Item *item){
  if (used_count == 0) return false;
  packed_longlong result;
  // 从记录中读取 in 字段的值到 result
  val_item(item, &result);
  // 读取出来的记录中，in 字段值为 null 就不用二分法查找了
  if (item->null_value) return false;
  // 对于非 null 值进行二分法查找
  return std::binary_search(base.begin(), base.end(), result, Cmp_longlong());
}

3、循环比较

前面介绍过，使用二分法查找执行 in 条件判断是有前提条件的，如果不满足条件，那就只能退而使用原始的执行方式了。

原始执行方式就是循环 in 条件括号中的值，逐个和存储引擎读取出来的记录字段值进行比较。

只要碰到一个相等的值，说明记录和 in 条件匹配，就结束循环，这条记录需要返回给客户端。

如果一直循环到 in 条件括号中的最后一个值，都没有碰到和存储引擎读取出来的记录字段值一样的，说明记录和 in 条件不匹配，这条记录不需要发送给客户端。

longlong Item_func_in::val_int(){
  ......
  for (uint i = 1; i < arg_count; i++) {
    // in 条件括号中当前循环的值是 null，跳过
    if (args[i]->real_item()->type() == NULL_ITEM) {
      have_null = true;
      continue;
    }
    
    // 获取 in 条件括号中的值，用什么类型进行比较，记为 cmp_type
    Item_result cmp_type =
        item_cmp_type(left_result_type, args[i]->result_type());
    in_item = cmp_items[(uint)cmp_type];
    assert(in_item);
    if (!(value_added_map & (1U << (uint)cmp_type))) {
      // 把读取出来的记录的 in 字段值转换为 cmp_type 类型
      in_item->store_value(args[0]);
      value_added_map |= 1U << (uint)cmp_type;
      if (current_thd->is_error()) return error_int();
    }
    // 比较读取出来的记录的 in 字段值和当前循环的值是否相等
    const int rc = in_item->cmp(args[i]);
    // rc 就是比较结果，false 表示相等
    if (rc == false) return (longlong)(!negated);
    have_null |= (rc == UNKNOWN);
    if (current_thd->is_error()) return error_int();
  }
  ......
}

4、总结

不包含子查询的 in 条件，和存储引擎读取出来的记录字段值进行比较，有二分法查找、循环比较两种方式。

二分法查找虽然有 3 个条件限制，但实际上这些条件还是很容易满足的，所以，多数情况下都能够使用二分法查找以获得更高执行效率。

本文转载自微信公众号「一树一溪」，可以通过以下二维码关注。转载本文请联系一树一溪公众号。

当前标题：WhereFieldin(...)是怎么执行的？
文章转载：http://www.shufengxianlan.com/qtweb/news19/493269.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联

猜你还喜欢下面的内容

标签优化知识

同城分类信息

成都口碑营销公司　　 　食品包装设计　　 　成都法律咨询　　 　手机网站制作　　 　成都网站改版　　 　APP定制开发　　 　重庆托管服务器　　 　网站制作　　 　成都制作网站　　 　成都网站建设　　 　禾巨建站　　 　成都网站建设　　 　网站建设公司　　 　南充网站建设　　 　seo优化　　 　温江做网站公司　　 　四川服务器托管　　 　电信服务器托管　　 　企业网站建设　　 　成都app开发