当前位置：首页 > 文章列表 > 数据库 > MySQL > 【MySQL 源码】UNION 比 UNION ALL 的性能差很多吗?

【MySQL 源码】UNION 比 UNION ALL 的性能差很多吗?

来源：SegmentFault 2023-01-11 10:57:19 0浏览收藏

在数据库实战开发的过程中，我们经常会遇到一些这样那样的问题，然后要卡好半天，等问题解决了才发现原来一些细节知识点还是没有掌握好。今天golang学习网就整理分享《【MySQL 源码】UNION 比 UNION ALL 的性能差很多吗?》，聊聊MySQL、源码、MySQL优化、C++、源码分析，希望可以帮助到正在努力赚钱的你。

原文地址: 【MySQL 源码】UNION 比 UNION ALL 的性能差很多吗?

欢迎访问我的个人博客: http://blog.duhbb.com/

引言

本文从源码角度分析了一下 MySQL 中 union 和 union all 的区别;得出了以下结论: union 和 union all 都会创建临时表, 但是又不太一样; 二者的查询计划不一样;union 默认会创建一个以返回列作为 key 的临时表, 所谓过滤就是将数据插入这个临时表; 临时表装数据的容器实际上是一个 unordered_set; 有一种存储引擎叫做临时表; union all 则是直接读取表的数据并返回给客户端, 不走临时表; union all 和 union 的场景还是得根据需要来判断, 如果没有 distinct 的需求话, 数据又不多, 可以考虑使用 union all.

Union 和 Union All 的区别

Union 和 Union All 之间的唯一区别是 Union All 不会删除重复的行或记录, 而是从所有表中选择满足您的具体查询条件的所有行并将它们组合到结果表中.

UNION 不适用于具有文本数据类型的列. 而 UNION ALL 适用于所有数据类型列.

MySQL 官方介绍

MySQL 官方文档在介绍 12.5 Non-Subquery UNION Execution 是这么说的:

非子查询联合 (non-subquery unions) 是在

void Query_expression::create_access_paths(THD *thd) {

  // 确定我们是否可以流式读取行, 即永远不需要将它们放到临时表中
  // 如果可以的话, 我们会首先物化 UNION DISTINCT blocks, 然后将剩余任何 UNION ALL block
  // 通过 AppendIterator 追加.
  //
  // 如果不能流式的话, 即每个块都必须进入临时表
  // 我们对于混合的 UNION ALL/DISTINCT 的策略有点不同
  // 详情见 MaterializeIterator.
  bool streaming_allowed = true;
  if (global_parameters()->order_list.size() != 0) {
    // If we're sorting, we currently put it in a real table no matter what.
    // This is a legacy decision, because we used to not know whether filesort
    // would want to refer to rows in the table after the sort (sort by row ID).
    // We could probably be more intelligent here now.
    streaming_allowed = false;
  }


  // 省略前面

  // 如果允许流式查询, 那么我们可以对 UNION ALL 的每个部分都做流式查询,
  // 而其他情况则都需要用到临时表.
  //
  // 处理我们需要物化的所有的 query block.
  // 这个可能是 UNION DISTINCT 的 query block 或者所有的 block.

  if (union_distinct != nullptr || ! streaming_allowed) {
    Mem_root_array<:queryblock> query_blocks =
        setup_materialization(thd, tmp_table, streaming_allowed);

  // 省略后面

光看代码感觉一头雾水, 还是 debug 一下吧.

  if (! simple_query_expression) {
    /*
      Check that it was possible to aggregate all collations together for UNION.
      We need this in case of UNION DISTINCT, to filter out duplicates using
      the proper collation.

      TODO: consider removing this test in case of UNION ALL.
    */
    for (Item *type : types) {
      if (type->result_type() == STRING_RESULT &&
          type->collation.derivation == DERIVATION_NONE) {
        my_error(ER_CANT_AGGREGATE_NCOLLATIONS, MYF(0), "UNION");
        return true;
      }
    }
    ulonglong create_options =
        first_query_block()->active_options() | TMP_TABLE_ALL_COLUMNS;

    if (union_result->create_result_table(thd, types, union_distinct != nullptr,
                                          create_options, "", false,
                                          instantiate_tmp_table))

这里执行的语句是:

select * from student union select * from student;

可以看到这里确实创建了临时表, 是在

bool Query_expression::prepare(THD *thd, Query_result *sel_result,

创建临时表调用的是这个方法:

/**
  Create a temp table according to a field list.

  Given field pointers are changed to point at tmp_table for
  send_result_set_metadata. The table object is self contained: it's
  allocated in its own memory root, as well as Field objects
  created for table columns. Those Field objects are common to TABLE and
  TABLE_SHARE.
  This function will replace Item_sum items in 'fields' list with
  corresponding Item_field items, pointing at the fields in the
  temporary table, unless save_sum_fields is set to false.
  The Item_field objects are created in THD memory root.

  @param thd                  thread handle
  @param param                a description used as input to create the table
  @param fields               list of items that will be used to define
                              column types of the table (also see NOTES)
  @param group                Group key to use for temporary table, NULL if
  none
  @param distinct             should table rows be distinct
  @param save_sum_fields      see NOTES
  @param select_options
  @param rows_limit
  @param table_alias          possible name of the temporary table that can
                              be used for name resolving; can be "".

  @remark mysql_create_view() checks that views have less than
          MAX_FIELDS columns.

  @remark We may actually end up with a table without any columns at all.
          See comment below: We don't have to store this.
*/

#define STRING_TOTAL_LENGTH_TO_PACK_ROWS 128
#define AVG_STRING_LENGTH_TO_PACK_ROWS 64
#define RATIO_TO_PACK_ROWS 2

TABLE *create_tmp_table(THD *thd, Temp_table_param *param,
                        const mem_root_deque &fields, ORDER *group,
                        bool distinct, bool save_sum_fields,
                        ulonglong select_options, ha_rows rows_limit,
                        const char *table_alias) {

其中有一段代码是这样的:

  } else if (distinct && share->fields != param->hidden_field_count) {
    /*
      Create an unique key or an unique constraint over all columns
      that should be in the result.  In the temporary table, there are
      'param->hidden_field_count' extra columns, whose null bits are stored
      in the first 'hidden_null_pack_length' bytes of the row.
    */
    DBUG_PRINT("info", ("hidden_field_count: %d", param->hidden_field_count));
    share->keys = 1;
    share->is_distinct = true;
    if (! unique_constraint_via_hash_field) {
      param->keyinfo->table = table;
      param->keyinfo->is_visible = true;
      param->keyinfo->user_defined_key_parts =
          share->fields - param->hidden_field_count;
      param->keyinfo->actual_key_parts = param->keyinfo->user_defined_key_parts;
      KEY_PART_INFO *key_part_info = share->mem_root.ArrayAlloc(
          param->keyinfo->user_defined_key_parts);
      if (key_part_info == nullptr) return nullptr;
      param->keyinfo->key_part = key_part_info;
      param->keyinfo->flags = HA_NOSAME | HA_NULL_ARE_EQUAL;
      param->keyinfo->actual_flags = param->keyinfo->flags;
      param->keyinfo->name = "";
      // keyinfo->algorithm is set later, when storage engine is known
      param->keyinfo->set_rec_per_key_array(nullptr, nullptr);
      param->keyinfo->set_in_memory_estimate(IN_MEMORY_ESTIMATE_UNKNOWN);


      /* 关键之处: 给我们想要返回的列搞一个整体的 distinct key */
      for (unsigned i = param->hidden_field_count; i fields;
           i++, key_part_info++) {
        key_part_info->init_from_field(table->field[i]);
        if (key_part_info->store_length > max_key_part_length) {
          unique_constraint_via_hash_field = true;
          break;
        }
      }
      table->key_info = param->keyinfo;
      share->key_info = param->keyinfo;
      share->key_parts = param->keyinfo->user_defined_key_parts;
    }
  }

感觉从这里似乎理解了 MySQL 是如何做过滤的了, 它会创建一个临时表, 然后给要返回的字段建一个

Hash_unique::Hash_unique(const Table &table, const KEY &mysql_index,
                         const Allocator &allocator)
    : Index(table, mysql_index),
      m_hash_table(INDEX_DEFAULT_HASH_TABLE_BUCKETS, Indexed_cells_hash(*this),
                   Indexed_cells_equal_to(*this), allocator) {}

Result Hash_unique::insert(const Indexed_cells &indexed_cells,
                           Cursor *insert_position) {
  std::pair<:iterator bool> r;

  try {
    // m_hash_table 就是个 unordered_set
    r = m_hash_table.emplace(indexed_cells);
  } catch (Result ex) {
    return ex;
  }

  auto &pos = r.first;
  const bool new_element_inserted = r.second;

  // 就是这里了, 判断是否插入成功
  if (! new_element_inserted) {
    return Result::FOUND_DUPP_KEY;
  }

  *insert_position = Cursor(pos);

  return Result::OK;
}

上面这个代码的路径是在: /Users/tuhooo/mysql-server/storage/temptable/src/index.cc

贴个图片纪念一下:

它喵的, 这个临时表保存记录其实就是用了一个

      /**
       *  @brief Attempts to build and insert an element into the
       *  %unordered_set.
       *  @param __args  Arguments used to generate an element.
       *  @return  A pair, of which the first element is an iterator that points
       *           to the possibly inserted element, and the second is a bool
       *           that is true if the element was actually inserted.
       *
       *  This function attempts to build and insert an element into the
       *  %unordered_set. An %unordered_set relies on unique keys and thus an
       *  element is only inserted if it is not already present in the
       *  %unordered_set.
       *
       *  Insertion requires amortized constant time.
       */
      template
    std::pair
    emplace(_Args&&... __args)
    { return _M_h.emplace(std::forward<_args>(__args)...); }

临时表其实是一种存储引擎.

本质上还是创建的查询计划不一样, 就是在下边这段代码了:

    m_root_iterator = CreateIteratorFromAccessPath(
        thd, m_root_access_path, join, /*eligible_for_batch_mode=*/true);
    if (m_root_iterator == nullptr) {
      return true;
    }

它来自于: sql_union.cc 中的 optimize 方法:

bool Query_expression::optimize(THD *thd, TABLE *materialize_destination,
                                bool create_iterators,
                                bool finalize_access_paths) {

查询计划

union all

mysql> explain select * from student union all  select * from student \G;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: UNION
        table: student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (6.53 sec)

ERROR: 
No query specified

mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                   |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `test`.`student`.`id` AS `id`,`test`.`student`.`name` AS `name`,`test`.`student`.`phone` AS `phone` from `test`.`student` union all /* select#2 */ select `test`.`student`.`id` AS `id`,`test`.`student`.`name` AS `name`,`test`.`student`.`phone` AS `phone` from `test`.`student` |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (3.24 sec)

union

mysql> explain select * from student union   select * from student \G;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: UNION
        table: student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: 
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Using temporary
3 rows in set, 1 warning (7.19 sec)

ERROR: 
No query specified

mysql> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                               |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `test`.`student`.`id` AS `id`,`test`.`student`.`name` AS `name`,`test`.`student`.`phone` AS `phone` from `test`.`student` union /* select#2 */ select `test`.`student`.`id` AS `id`,`test`.`student`.`name` AS `name`,`test`.`student`.`phone` AS `phone` from `test`.`student` |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (4.04 sec)

嘿嘿, 刚好又把我之前学的一点点